Set in mySQL a predefined order for SELECT queries without using the ORDER BY clause

Like you already found out, there is no (predefined) order in a result set unless you specify it in the ORDER BY clause.

Your only option would be, to rename the table and create a view on it.

RENAME TABLE your_table TO another_table_name;
CREATE VIEW your_table AS
SELECT * FROM another_table_name ORDER BY id DESC;

Test of course heavily if you run into problems, but I assume there shouldn't be any problems since the view would even be updatable and insertable like it says in this manual entry:

  For a view to be updatable, there must be a one-to-one relationship
  between the rows in the view and the rows in the underlying table.
  There are also certain other constructs that make a view nonupdatable.
  To be more specific, a view is not updatable if it contains any of the
  following:

  Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
  DISTINCT
  GROUP BY
  HAVING
  UNION or UNION ALL
  Subquery in the select list
  Certain joins (see additional join discussion later in this section)
  Reference to nonupdatable view in the FROM clause
  Subquery in the WHERE clause that refers to a table in the FROM clause
  Refers only to literal values (in this case, there is no underlying table to update)
  ALGORITHM = TEMPTABLE (use of a temporary table always makes a view nonupdatable)
  Multiple references to any column of a base table


Split a row into two for attendance seet

You can use the Table Value Constructor in a cross apply to generate two rows for each row in the source table.

declare  @T table
(
  ID int identity primary key,
  DutyIn datetime,
  DutyOut datetime,
  DutyInManual datetime,
  DutyOutManual datetime
);

insert into @T values
('2016-10-01T10:10:00', '2016-10-01T10:50:00', '2016-10-01T10:00:00', '2016-10-01T11:00:00');

select T.ID,
       S.DutyAuto,
       S.DutyManual
from @T as T
  cross apply (values(T.DutyIn, T.DutyInManual),
                     (T.DutyOut, T.DutyOutManual)) as S(DutyAuto, DutyManual);


SQL Agent Proxy - writing to logs is only allowed to jobs that are owned by sysadmin

I would work on educating your security team about jobs...they are server/instance level objects, not database level objects, so db_owner seems severely limited.

Ola recommends sysadmin for the job owners:

  Which permissions are needed for the SQL Server Maintenance Solution to work?

    If you are using SQL Server Agent, the jobs run under the SQL Server Agent service account that is a member of the sysadmin server role. If you are using a proxy account, I recommend that the account be a member of the sysadmin server role.

    If you are using another scheduler, I recommend that the scheduler run under an account that is a member of the sysadmin server role.

    If you need to have users execute the stored procedures ad hoc against specific databases, these permissions are needed:

    DatabaseBackup: sysadmin

    DatabaseIntegrityCheck: EXECUTE on dbo.DatabaseIntegrityCheck, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

    IndexOptimize: EXECUTE on dbo.IndexOptimize, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

Regarding the comment about the msdb filling up if you run against a table, when you install his jobs using his big script MaintenanceSolution.sql he installs a CommandLog Cleanup job that will clear out the tables used to log his job steps.


Complex constraint across all data in a table

Tested with Postgres 9.1 & 9.2. Most of it should work for 8.4 as well.

Simple case

Without intermediate states violating the constraint over the course of a single transaction, a partial UNIQUE index on a constant value does the job. Given this test case:

CREATE TEMP TABLE jobs(jobs_id int primary key, status text);
INSERT INTO jobs (jobs_id, status)
VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'G');

Use this index:

CREATE UNIQUE INDEX jobs_status_uni_idx ON jobs ((TRUE))
WHERE status in ('P', 'G');

Note the additional set of parenthesis around the value TRUE.

Test (one line at a time):

INSERT INTO jobs (jobs_id, status) VALUES (5, 'G'); -- fails
INSERT INTO jobs (jobs_id, status) VALUES (5, 'P'); -- fails

DELETE FROM jobs WHERE status = 'G';

INSERT INTO jobs (jobs_id, status) VALUES (5, 'P'); -- succeeds
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G'); -- fails

SQL Fiddle.

Related answer with more explanation:
PostgreSQL multi-column unique constraint and NULL values

Advanced case with DEFERRED constraint

  ideally this check would happen on commit

A partial unique index cannot be deferred and is always checked immediately. Therefore, continuing the example from above, this would fail:

BEGIN;
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G');  -- fails immediately!
DELETE FROM jobs WHERE status = 'P';
COMMIT;

Example is with explicit transaction handling. Your client may have automatic transaction handling (autocommit on or off).

If you need this to work, you need a DEFERRABLE constraint that is INITIALLY DEFERRED or SET CONSTRAINTS ALL | name  DEFERRED within the transaction.

I quote the manual here

  Non-deferred Uniqueness Constraints

  When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
  checks for uniqueness immediately whenever a row is inserted or
  modified. The SQL standard says that uniqueness should be enforced
  only at the end of the statement; this makes a difference when, for
  example, a single command updates multiple key values. To obtain
  standard-compliant behavior, declare the constraint as DEFERRABLE but
  not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
  significantly slower than immediate uniqueness checking.

More gory details about Index Uniqueness Checks in the manual.

However, a UNIQUE CONSTRAINT can only be defined for columns, not for expressions.
Add a redundant column for this purpose and keep it current with triggers. Like this:

ALTER TABLE jobs ADD COLUMN status_uni boolean;
UPDATE jobs set status_uni = TRUE WHERE status in ('P', 'G');  -- rest stays NULL

ALTER TABLE jobs ADD CONSTRAINT jobs_status_uni
UNIQUE(status_uni) DEFERRABLE INITIALLY DEFERRED;

The additional column is cheap, since it is filled with NULLs, an existing NULL bitmap should normally swallow it without the need for additional physical disk space.
NULL values do not violate a unique constraint per definition.

Create triggers to keep the column current at all times:

CREATE OR REPLACE FUNCTION trg_jobs_status()
  RETURNS trigger AS
$func$
BEGIN
NEW.status_uni = (NEW.status IN ('G', 'P') OR NULL);
RETURN NEW;
END
$func$  LANGUAGE plpgsql;

For all inserts:

CREATE TRIGGER insbef
BEFORE INSERT ON jobs
FOR EACH ROW EXECUTE PROCEDURE trg_jobs_status();

To optimize, only for relevant updates:

CREATE TRIGGER upbef
BEFORE UPDATE OF status, status_uni ON jobs
FOR EACH ROW EXECUTE PROCEDURE trg_jobs_status();

Test

Now your transactions go through, as long as the state is consistent at the end:

BEGIN;
-- SET CONSTRAINTS jobs_status_uni IMMEDIATE;  -- only for INITIALLY IMMEDIATE
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G');  -- check deferred
DELETE FROM jobs WHERE status = 'P';
COMMIT;  -- succeeds!

SQL Fiddle.


Legacy data migration to Always on Availability group database

my plan was to remove all the secondary replica from the availability group and then set the primary database offline.
  Restore the production database into a staging server, then complete the migration/conversion

This seems like a really reasonable plan.  You'd likely have lots of issues trying to load 100 GB+ of data to the AG quickly, especially if they are sync replicas.  So this avoids the network and redo bottlenecks you would have likely encountered.

  and then restore it back to the Production and syn all the secondary replicas

It looks like you're on SQL Server 2014, so this also makes sense - as your only option at this point would be to do "manual seeding" (restore the database and log on the secondary replica).

If you were on SQL Server 2016 or higher, I warn against using "automatic seeding" with such a large amount of data.


How do I limit Oracle to use specific ports?

You may want to visit the Connection Manager documenatation  here

I have used it to concentrate connections - we had a client that needed to access our database over a VPN. The firewall was only configured to open 1 port for all their connections. We were able to handle all their traffic over this 1 port using CMAN. Your mileage may vary though, we just used it for a handful of connections, if you are servicing dozens or hundreds of connections CMAN may run out of steam.

Your network guys should also check with the firewall vendor, there may be the ability for the firewall to open ports after the listener hands them off... So the firewall still has control over what gets opened and when after Oracle authenticates the connection. I don't know what this feature called as I am not heavy on the network side, but we had to use CMAN for some of our firewalls that did not handle this.


MySQL server feature gap on Windows and Linux

No, in terms of features there is no difference between Windows and Linux versions of mysql. There are certain configuration options that may make sense on one of them only, for example see identifier case sensitivity, that you have to consider when migrating databases between the 2 platforms. Mysql documentation has a section on limits on Windows platform that lists those differences that may cause real headaches.

Obviously, there may be performance differences and tuning differences, but those have nothing to do with features.


job send email to developers upon complexion and include DBAs if it fails

What you can do is, add a job step called for example mail dba.

In that job step you put the msdb.dbo.sp_send_dbmail

An example job step

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBA',
    @recipients = 'some_email@somedomain.com',
    @body = 'The Job Failed.',
    @subject = 'Automated Error Message' ;

Then you edit every step of the job to go to job step mail dba when it fails.


MySQL databases on their own separate Logical Volume

This may sound twisted but here it goes....

DON'T USE CREATE DATABASE

If you login as root and you do the following:

cd /var/lib/mysql
mkdir db1
chown -R mysql:mysql db1

you just created the database. The INFORMATION_SCHEMA database will dynamically register db1 in the INFORMATION_SCHEMA.SCHEMATA table as a database.

Since you want to create a volume for it, you can do this

cd /var/lib/mysql
mkdir db1
mount /dev/sdb db1
chown -R mysql:mysql db1

GIVE IT A TRY !!!

Bad News: Backing up /var/lib/mysql will not scoop up mount volumes. You will have to resort to doing mysqldumps. That should not be an issue if you have 5GB mounts.


Determine cause of "MySQL server has gone away"

I had a similar issue to this, and it turned out to be a connection time out. My program sets up a connection pool, and threads grab a connection from that pool when they need to make queries. Every morning when I would come in and try to run my program I would get the MySQL Server has gone away error. A lot of solutions I found said to increase the wait_timeout field in my.cnf, but I found in the C API you can enable automatic re-connection like this:

// set up your connection

// enable automatic reconnect
bool reconnect = 1;
mysql_options(&connection, MYSQL_OPT_RECONNECT, &reconnect);

From the docs:

  The MySQL client library can perform an automatic reconnection to the server if it finds that the connection is down when you attempt to send a statement to the server to be executed. If auto-reconnect is enabled, the library tries once to reconnect to the server and send the statement again.

This gave me a hard time for a while as it was pretty hard to troubleshoot, so hopefully this is helpful to somebody else!

Source: C API Automatic Reconnection Control


How to inspect a view's structure in information schema in PostgreSQL?

information_schema.columns already includes views; you can join with information_schema.views if you need to filter to only columns that are part of a view.


The question is a unclear on how the tables are to actually related to each other.

Since the query you've posted is not working, it's difficult to tell which element of the logic contains the error, and with a column called "oldID" it's hard to understand the necessary join logic.

I have attempted to interpret and rewrite your query based on trying to understand it from the innermost query to the outermost query, it seems like this might be what you are trying to accomplish.

My table "ta" is doing the same thing as your table "ta" seems to be trying to do, and my table "a" is doing the same thing your table "a" seems to be trying to do.  They are reversed in their order of appearance in the query because, as I mentioned, if I'm understanding your query correctly, the original query seems to be written inside-out.

UPDATE Taxonomy t
  JOIN TaxonomyMapping ta ON ta.oldID = t.oldID
  JOIN CategoryMapping cm ON cm.language_ID = ta.oldID
  JOIN TaxonomyMapping a ON a.oldID = cm.en_id
   SET t.sourceId = a.id
 WHERE t.id > -1;

However, if your "TaxonomyMapping" and "Taxonomy" tables are an "exact copy" (containing the same data) then table "ta" is not necessary, and all you actually need is this:

UPDATE Taxonomy t
  JOIN CategoryMapping cm ON cm.language_ID = t.oldID
  JOIN TaxonomyMapping a ON a.oldID = cm.en_id
   SET t.sourceId = a.id
 WHERE t.id > -1;

In fact, it seems like you could even do it without a permanent copy.

UPDATE Taxonomy t
  JOIN CategoryMapping cm ON cm.language_ID = t.oldID
  JOIN (SELECT * FROM Taxonomy) x ON x.oldID = cm.en_ID
   SET t.sourceId = x.id
 WHERE t.id > -1;

That last example works because the server materializes a temporary copy of "Taxonomy" called table "x" before executing the rest of the query.  By doing that, it's able to update the table with data selected from elsewhere in the same table, which you normally can't do... but since a copy is technically a different table, it works.  The temporary copy is usually created in memory and then moved to disk if it turns out to be large, but either way, it's automatically deleted as soon as the query is finished.  The down-side is that the new table won't have any indexes so this approach won't infinitely scale... but should still be faster than your original correlated subquery.

Of course, none of these will work if I have failed to understand the necessary logic.


Is it possible to see blocker's host name or machine name using sp_WhoIsActive?

Yes, you simply have to correlate the blocking_session_id to the session_id of the blocker.  In the following output, you see that session 68 is blocked by session 66, so you simply have to find the data for session 66.

To find block leaders most quickly, be sure to use the following parameters so that the session blocking the most other sessions will be at the top:

@find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC'

Sample output will be as follows:

sp_whoisactive
      @output_column_list='[session_id][block%][host_name][login_name]'
    , @find_block_leaders = 1
    , @sort_order = '[blocked_session_count] DESC'

session_id    blocking_session_id    blocked_session_count    host_name      login_name
66            NULL                    1                       host1          user1
68            66                      0                       host2          user2

If all of the sessions are coming from an application server, which is quite common, then the host_name is simply going to be that application server and there is no way from the SQL Server side to see what actual client computer is associated with that connection from the application server.  If you want to track something like that down you would need to start logging relevant information at the application server, or, if you can figure it out by looking at the queries, use extended events or traces to see what queries are being run in that session.  No easy way about it unless the application already has the plumbing to turn on some associated logging.

See sp_whoisactive: Leader of the Block

Also of note is that the SQL client does not always set the host_name property, so if it's blank, it's simply because the application configured the SQL connection to omit that information.


mysql_upgrade on fresh MySQL/MariaDB installation

According to Bogdan Kecman who worked at Oracle:

  mysql_upgrade checks whether the tables are at the latest version, if not - upgrades them
  and then marks them as upgraded. On a fresh install, even though tables are on the latest version, they are not marked. So, mysql_upgrade marks them. It does nothing else. To avoid scanning them
  again when mysql_upgrade runs.

Sorry for lousy translation.


Can I show a relation - "Either in this table or in that " in ER diagram?

In the ER model,  this concept is known as Generalization/Specialization.  A diagram illustrating this is shown in the artcle linked to.

https://www.geeksforgeeks.org/generalization-specialization-and-aggregation-in-er-model/

A word of caution is in order, however.  Gen/spec is applied to Entities, not tables. The difference between entities and tables is in the level of abstraction.  If you want to think clearly about the subject matter,  and not just the database, it behooves you to keep the levels distinct in your mind.
An entity is an object in the subject matter whose descriptive data may be stored in a table.


Do MySQL sub-queries essentially use as much overhead as separate queries?

The amount of data you want could be fast to send over a wire, but what is mysql doing to process it and prepare it for transamission ???

Let's first look at the original query

SELECT name FROM
(
 SELECT * FROM `table`
 WHERE id IN (1,40,300)
 ORDER BY AnotherColumn
)

If id is a primary key, this should go fast. Yet, you do not need to say get all columns in a subquery, and then read on the name from that subquery. You could just craft the SQL as

SELECT * FROM
(
 SELECT name,AnotherColumn FROM `table`
 WHERE id IN (1,40,300)
)
ORDER BY AnotherColumn

or even better, no subquery at all

 SELECT name FROM `table`
 WHERE id IN (1,40,300)
 ORDER BY AnotherColumn

You could benchmark each of these and find negligible to nominal differences but nothing really to publish a Whitepaper over. If thousands of these queries were executed, a query digest program such as pt-query-digest or mk-query-digest could easily pick out a winner as to who is the fastest in terms of running time per call.

With regard to your original question, think of it in these terms:

If going to a movie costs $8/person and you had to bring three people to see a movie, which is cheaper ?

CASE01) taking three people to see a movie
CASE02) taking each person to see the movie at a different time

Well, do the math.

CASE01) $32, you and the three people
CASE02) $48, you (three times) and the three people

Obviously, 50% more overhead in CASE02.

If the puzzle involved

4 people, CASE01) $40 vs CASE02) $64
5 people, CASE01) $48 vs CASE02) $80
6 people, CASE01) $56 vs CASE02) $96
7 people, CASE01) $64 vs CASE02) $112
N people, CASE01) 8(N+1) vs CASE02) 16N

As N goes to infinity, the average overhead is doubled.

Calling three queries in a subquery or getting three rows in one subquery sure beats three separate queries anyday.


View created using Join Queries taking too much to load

Your view will join all the tables involved in the view definition in order to create the required view.

First, of course, you should check that your tables are properly indexed so that the joins are as efficient as possible when creating the view.

However, joining 25 tables with 30 inner joins is a relatively expensive undertaking, resulting in your case with the 45-50 seconds of delay before getting results.

A good general strategy is to break a big slow step into smaller faster steps. I would consider whether it is possible to create 4 or 5 temporary tables into which you can insert smaller data sets.  Then once the temporary tables exist you could join them together to get a final result.

This does require some design effort in creating the temporary tables so that the individual tables create the proper subsets of data for each query.

You can create a Materialized View which may work for you depending on your requirements.  Rather than explain the details please look at:

http://www.fromdual.com/mysql-materialized-views

You must write code to populate the materialized view and to refresh the data as needed.
If you must have the most current data, then you will need to write triggers to provide the needed updates.


The actual number of row of Lazy Spool is huge

Spool operator ( “Lazy” Spool in this case) will create a temporary table in the TempDB database, and store all data (32 rows in this case) returned from the Merge join.

Nested loop join executed 3967661 times (outer input) and did a join with table spool (holding 32 rows).  Out of 3967661 times 16 times the parameter of the join changed and had to reevaluate the inner result set (rebind) and 3967645 times inner result set was reusable. As I am not seeing the whole plan I believe 7935322 number came form the 16 reevaluating the inner data set.

In most cases creating an index will most likely get rid of the spool and make the query run faster.  Because you do not have to create the temporary table in tempdb.


How to Handle Adding 2nd Node to single MariaDB Galera "cluster" already in production?

The best way would be to use the xtrabackup-v2 SST method.  It is non-blocking for most of the transfer; there is a short blocking period at the end of the transfer when it has to get the latest data from the donor, but that is usually only a few seconds at most.  The other SST methods would need the donor to be offline for a much longer period since they aren't designed for online transfers.


Why is AWS RDS' maximum auto_explain.log_min_duration parameter 214783?

This is not specific to AWS RDS (well part of it specific to that), it is in the upstream code.

The setting is restricted to INT_MAX / 1000.  But I can't figure out why, as that setting is never multiplied by anything, much less 1000.  This seems like a bug to me.  You might want to report it to the PostgreSQL bugs mailing list.

In the meantime, I guess your option would be to set it to the max and then filter your log files.  It seems unlikely to have millions of queries each taking 40 minutes, so filtering it shouldn't be too onerous.

update

And soon after your report, it has been fixed in PostgreSQL for all supported versions.  It will be included in the next bugfix release, which is due out soon.  I don't know when Amazon is likely to both roll out the next bugfix release to RDS, and fix their own independent bug on the same subject matter.


Which MySQL queries are causing this error message?

This issue, Too Many Connection error occurs when the max_connections variable reaches its maximum threshold value.
Usually, the value for max_connections is defined at the configuration file. By increasing this variable value, this error will resolve.
To see the current value, try the following command:
mysql> show global variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 300   |
+-----------------+-------+
1 row in set (0.02 sec)

When the defined limit reaches, all users will get the error, this is not user specific. However, MySQL allows you to define the user specific values.
After login as root or user with SUPER privilege, you can run the below command to identify the currently processing queries:
mysql> show full processlist;

Note: even if you get Too Many Connections error, you can still login to server, using the root or user with SUPER privilege.  MySQL by default will consider maximum allowed connection as max_connection + 1 for SUPER user to fix this issue.
By increasing this variable value, this issue will be resolved.
mysql> SET global max_connections = 1000;

If you wish this changes to be permanent, edit the configuration file (my.ini), and change the value for max_connections variable under [mysqld] section. This approach requires a server restart.
[mysqld]
max_connections = 1000;

Changing this variable value to a higher value in a system with limited resource may lead to performance issues related to memory. Check this page for how to fix MySQL Too Many Connections and other workarounds. I hope this answer will help you.


Can I avoid a join for my select procedures?

You can use variables:

delimiter //
CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hashid int,
  _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1))
begin
  START TRANSACTION;

  SELECT @fn:=`first_name`, @ln:=`last_name`, @hid:=`hashID` FROM MYDB.person WHERE <condition>;

  SELECT @id:=`id`, @s:=`id_status`, @cs:=`criminal_status` FROM MYDB.id_crimes WHERE `hashID` = @hid;

  SELECT @sid:=`stateID`, @sids:=`stateID_status`, @dob:=`dob` FROM MYDB.dob_state WHERE `hashID` = @hid;

  SELECT @ct:=`create_time`, @ut:=`update_time` FROM MYDB.timestamps WHERE `hashID` = @hid;

  COMMIT;

  SELECT @fn AS `first_name`, @ln AS `last_name`, @hid AS `hashID`,
         @id AS `id`, @s AS `id_status`, @cs AS `criminal_status`,
         @sid AS `stateID`, @sids AS `stateID_status`, @dob AS `dob`,
         @ct AS `create_time`, @ut AS `update_time`;
end//
delimiter ;

Edit:

You can also use SELECT <columns,...> INTO <variables,...>:

You can use variables:

delimiter //
CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hashid int,
  _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1))
begin
  START TRANSACTION;

  SELECT `first_name`, `last_name`, `hashID` INTO @fn, @ln, @hid FROM MYDB.person WHERE <condition>;

  SELECT `id`, `id_status`, `criminal_status` INTO @id, @s, @cs FROM MYDB.id_crimes WHERE `hashID` = @hid;

  SELECT `stateID`, `stateID_status`, `dob` INTO @sid, @sids, @dob FROM MYDB.dob_state WHERE `hashID` = @hid;

  SELECT `create_time`, `update_time` INTO @ct, @ut FROM MYDB.timestamps WHERE `hashID` = @hid;

  COMMIT;

  SELECT @fn AS `first_name`, @ln AS `last_name`, @hid AS `hashID`,
         @id AS `id`, @s AS `id_status`, @cs AS `criminal_status`,
         @sid AS `stateID`, @sids AS `stateID_status`, @dob AS `dob`,
         @ct AS `create_time`, @ut AS `update_time`;
end//
delimiter ;


Il looks like you (or someone else) deleted the file n°5 BEFORE you were putting DB in noarchivelog.
You have to restore this file from a previous backup and recover it to the time you stopped the DB for your operation.


Transactional replication of Date column from Sql Server 2012 SP4 to Azure Sql Database

converting my comment to answer ...

Since you confirmed that the db compatiblity is 90 which is sql 2005

Run

SELECT name, compatibility_level
FROM sys.databases WHERE name = 'YOURDBName'

and then you can use ALTER DATABASE [YOURDBName] SET compatibility_level = 110 -- 2012 version


How to change a script task version in SSIS?

I believe I have the same issue, and here is the work around that got over my issue.

Details first:

I have sql server 2016 (13.0.4411.0)
SSISDB has the schema version (13.0.1601.5)
I am using an integration services package created in visual studio
The script component (Which states it is 2015 C#) has the path:
C:\Program Files (x86)\Microsoft SQL
Server\130\DTS\Binn\VSTA14_IS_ST_CS_Template.vstax

If I deploy to the catalog through SSMS, it won't let me execute the package through the integration services catalog (due to the message experienced by Zach - version 15.0 not supported).

The workaround:

If I deploy the package through Visual Studio to the 'Integration Services Catalog' on the required instance, this message goes away and the project runs successfully. This is not ideal as we should be able to deploy through SSMS, but meant that the project can progress.


How to save an interval of years in postgresql

If possible, just store the first value

It doesn't look like you need to store two numbers at all.

If these are like tax years, i.e. "2014/2015 financial year", you only need to consistently store one part.

If you store 2014, your app knows it's the academic year beginning in 2014. So it can display "2014/2015 Academic Year" with no ambiguity.

If you really need a range

If you actually need ranges and your sample data doesn't express that, e.g. you might need to store 2012/2015, then two int2 columns would be a good choice. You'd use CHECK constraints to limit the range to a reasonable value for an academic year and to enforce that the lower bound is less than the higher bound, as @Neil has already showed.

Alternately you could use the new-ish and PostgreSQL-specific int4range type:

SELECT int4range(2014, 2015);

per the documentation on range types. This offers you lots more operators at the cost of being fiddlier to work with for basic queries and from client apps.

You can even make a DOMAIN over it to confine its range.

CREATE DOMAIN academicyearrange AS int4range
  CONSTRAINT must_be_sane_year
  CHECK ( lower(VALUE) BETWEEN 1900 AND 2100
      AND upper(VALUE) BETWEEN 1900 AND 2100 );

e.g.

regress=> SELECT '[1421,2247)'::academicyearrange;
ERROR:  value for domain academicyearrange violates check constraint "must_be_sane_year"

regress=> SELECT int4range(1421,2247)::academicyearrange;
ERROR:  value for domain academicyearrange violates check constraint "must_be_sane_year"

(Before range types I would've suggested a domain over a composite type, but int4range is better in every way than a composite type).

Most likely all this is unnecessary - if you don't need variable length ranges, i.e. it's always someyear/thenextyear, you should just store the first year.


Monitor source SQL code of inserts into table

You haven't included version or edition info, so if you're running SQL Server 2016 SP1 or higher (or Enterprise Edition for earlier versions), try using SQL Server Audit to trace this information.

You can set up a Database-level Audit to capture INSERT action on that table, then query the audit files to identify the calling application and statements.

SQL Audit: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017

Audit Action Groups: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-2017

Also, Profiler and Extended Events should be able to capture this information for you. Which trace events are you capturing?

If you use the template TSQL_Replay trace and filter it appropriately, you should be able to see the calling code, including the explicit values passed to the stored proc or INSERT statement.


Parallel Sequential Scan on SELECT statement, but not on SELECT ... INTO

Quote from the manual

  The planner will not generate them for a given query if any of the following are true

  The query writes any data or locks any database rows. If a query contains a data-modifying operation either at the top level or within a CTE, no parallel plans for that query will be generated. This is a limitation of the current implementation which could be lifted in a future release.

As you are inserting rows into a table, the underlying query will never use parallelism, at least not in current version (10) and earlier.


Options for creating a read-only replica of a DB2 database

If you wish to use Express-C, your best options are probably either mirroring the disk and logs using an OS utility (I believe best done on Unix) or through taking backups and copying them to the standby server and then restoring them.

HADR won't work with Express-C. And I'm not sure that pureScale will either (although I can't say with 100% certainty.)

Do note that not every feature from higher editions of DB2 (such as Enterprise) won't work on Express-C. Multidimensional Clustering Tables (MDCs) or BLU for example. So if your client uses those features someone is going to have to pay for a license for another server.

I guess I would also question what data may be in that production server that a client wishes to allow you to have? Or that you wish to have? That is pretty dangerous. Developers should have limited access to production data. It should be specific and timed. Otherwise a copy with scrambled data should be restored to a test or dev environment (still preferably on their equipment). A good DBA shouldn't just let someone have access to production unless they have signed in blood so to speak. I'd go so far as to say a DBA shouldn't really even be able to see the data in production. I digress.


creating PKs for existing tables - altering column constraints

My expertise is on DB2 for i, not really LUW. That said, I will still offer my thoughts.

I don't think you want your ID field to have a default of zero and say GENERATED BY DEFAULT.

Since this column is giving you so much trouble and is one you added, I might be tempted to drop it, and start from scratch, if practical.

I believe the commonly recommended definition for this field would be

ALTER TABLE tablename
  ADD COLUMN orm_id   INTEGER   NOT NULL
                                GENERATED ALWAYS AS IDENTITY
                                CONSTRAINT tablename_pk PRIMARY KEY;

But someone wiser may adjust this advice.


Please note that your date field actually isn't a date field but a string field (you can see this by the type icon next to the value). It seems that in the export it gets converted to a simple string. I hope this helps!

(Disclaimer: I work for the company who makes Studio 3T)


Which are the options I have to create a structured log of all INSERT, UPDATE and DELETE from all tables of my PostgreSQL database?

You would use logical decoding. You could choose an existing logical decoding plugin like wal2json, or you can write your own.
Note that you will only get data modifications that way, DDL statements won't be decoded. It is not absolutely required, but strange things can happen if your tables have no primary key.
If your goal is not to capture the changes but to log all statements that modify anything, set log_statement = 'ddl'.


Whats the best practice when using Amazon Redshift as a cache

I would not recommend using redshift as a cache. Have you considered Redis?

Redshift saves all data to disk in 1MB blocks, in an order established by your sortkey, and distributed between nodes based on your distkey. When ever you create, update, delete you are appending data to the last blocks of the database. These blocks that hold all the new changes are not sorted until you vaccume the database.

Hence, when you use redshift as a cache you are very quickly fragmenting your data, making your queries slow.


Oracle 12c has a Performance Hub which includes Monitored SQL in historical mode.  Although I haven't tested it, and based on some open bugs I doubt it works 100% of the time.

For 11g, or when the Performance Hub is not available or not working, I have created the open source program Historical SQL Monitoring (hist_sql_mon):

Here's an example of how to use it:

select hist_sql_mon.hist_sql_mon(
    p_sql_id            => '2ssrz4j1m39wx',
    p_start_time_filter => date '2014-09-25',
    p_end_time_filter   => sysdate - interval '1' day)
from dual;

The primary output is a CLOB containing an execution plan with a count and distinct count of events.

-------------------------------------------------------------
|Id  | Operation              | Name   | Rows  | Cost (%CPU)| Event (count|distinct count)
-------------------------------------------------------------
|  0 | SELECT STATEMENT       |        |       | 83031 (100)|
|  1 |  SORT AGGREGATE        |        |     1 |            |
|  2 |   HASH JOIN RIGHT OUTER|        |  8116K| 83031   (4)| Cpu (25|25)
|  3 |    INDEX FULL SCAN     | I_USER2|   155 |     1   (0)|
|  4 |    NESTED LOOPS OUTER  |        |  8116K| 82993   (4)| Cpu (1|1)
|  5 |     NESTED LOOPS OUTER |        |  8116K| 10702  (24)| Cpu (2|2)
...

The functions also print the SQL statement to DBMS_OUTPUT. The bind variables are replaced with hard-coded values so the query can run anywhere. This can help with debugging or creating your own queries.

----------------------------------
--Historical SQL Monitoring Report
----------------------------------
--Execution plans and ASH data, where there are some samples for a plan_hash_value.
select
    --Add execution metadata.
    case
        when plan_table_output like 'Plan hash value: %' then
...


How to create a table with data from other tables in Postgres?

From the PostgreSQL documentation:

CREATE TABLE new_table AS
SELECT * FROM ab1_2;

Replace the * with the field names (with alias if you need to change the name) you need. You can put in the other 5 tables as UNION:

CREATE TABLE new_table AS
SELECT * FROM ab1_2
UNION
SELECT * FROM ab3
UNION
SELECT * FROM ab4
UNION
SELECT * FROM ab5
UNION
SELECT * FROM ab6;

In the second one only the field names are needed. No use to give them an alias.

Or write 5 SQLs in the form:

INSERT INTO new_table
SELECT * FROM other_table;


In SQL Developer, SET SERVEROUTPUT ON SIZE UNLIMITED sets the buffer to the maximum value 1000000 instead of unlimited (tested with 12.1.0.2.161018 database and SQL Developer 4.1.5.21). You can confirm this easily with SQL tracing.

As an alternative, the below really sets the the buffer to unlimited:

begin
  dbms_output.enable(null);
end;
/


GMT timezone in Mysql timestamp literal value

In MySQL any date/time values inserted into timestamp are automatically converted and stored as UTC.

To quote MySQL documentation:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions.

So the answer to your question would be to set the time_zone value for your connection to the database by setting the session variable called time_zone before inserting/updating the value into the database and do the same before fetching the value back from DB. By doing so, the conversion to the actual value of date/time to reflect the Timezone will be automatically handled by MySQL.

This is how you can set the session Timezone on MySQL Client:

mysql> SET time_zone = timezone;

To set global Timezone on MySQL Client:

mysql> SET GLOBAL time_zone = timezone;

There will be ways to do this in the Programming Language you are using.

More details from MySQL manual here:

https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html


You've asked a subjective question, but you'll find that many of us will say that the best practice is to have only one instance per node if it's a production cluster. If you have two instances, then many of us will say you need 3 nodes: one node for instance1, one node for instance2, one node is passive and is ready to be used if either of the other two nodes go down.

Brent Ozar recently blogged about this instance stacking topic. There are benefits and drawbacks to instance stacking. One such benefit is licensing and one such drawback is performance tuning.

Disclaimer: I work for Brent Ozar Unlimited. The referenced article is from our web site and is written by my boss.

SQL Server has supported mount points since SQL Server 2005, so drive letters didn't limit the number of instances even ~12 years ago unless you got really crazy with tons of instances in a cluster.

I have never heard that there's a 5 instance per node best practice for 2008 R2. I have had 4 instances on one node in a test environment, but the databases were tiny and very little utilization. I did not have to worry about performance tuning on that server at all.

Now if you were to change your question to ask what is the maximum number of instances you should have on a node, then you might get a different answer. But that answer is going to be dependent on the hardware of the server and workload of the instances. I still say 1 instance for each node though if it's a production cluster.


It seems that your MySQL server is configured as a slave server in a master - slave replication setup. If all you want is for the error messages to go away, then you can simply do:

STOP SLAVE;

And to remove the slave configuration:

RESET SLAVE; (Use RESET SLAVE ALL; on MySQL 5.5.16 and later versions.)

Also edit /etc/my.cnf (and any included .cnf files inside it) to remove any lines starting with master- and replicate-.

While the above is sufficient to prevent the server from attempting further replication, you can also restart mysqld to remove slave status information available from commands such as SHOW SLAVE STATUS\G.


Export Oracle tables based on owner + all users from database

Data Pump export/import is what you're looking for. You can use this to export your schema definitions and load them into the target database, specifying which type of objects you require.

You can select specific types of objects using the INCLUDE parameter, so to just export tables you would:

expdp include=table ...

Provided you run the export/import as a user with sufficient privileges, this will also create the users on the target when you import.

Full documentation for Oracle 10.2:

Data Pump Export

Data Pump Import


Still wrong to start the name of a user stored procedure with sp_?

This is fairly easy to test yourself. Let's create two very simple procedures:

CREATE PROCEDURE dbo.sp_mystuff
AS
  SELECT 'x';
GO
CREATE PROCEDURE dbo.mystuff
AS
  SELECT 'x';
GO

Now let's build a wrapper that executes them a number of times, with and without the schema prefix:

CREATE PROCEDURE dbo.wrapper_sp1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC sp_mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_sp2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC dbo.sp_mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC dbo.mystuff;
      SET @i += 1;
    END
END
GO

Results:

Conclusions:

using sp_ prefix is slower
leaving out schema prefix is slower

The more important question: why would you want to use the sp_ prefix? What do your co-workers expect to gain from doing so? This shouldn't be about you having to prove that this is worse, it should be about them justifying adding the same three-letter prefix to every single stored procedure in the system. I fail to see the benefit.

Also I performed some pretty extensive testing of this pattern in the following blog post:

http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix


Error configuring mongodb replicaset with inmemory

Ok what you need to do is to change:

    mongo "${ssl_args[@]}" --eval "rs.initiate({'_id': '$replica_set', 'members': [{'_id': 0, 'host': '$service_name'}]})"

to

    mongo "${ssl_args[@]}" --eval "rs.initiate({'_id': '$replica_set', 'writeConcernMajorityJournalDefault': false, 'members': [{'_id': 0, 'host': '$service_name'}]})"

The data still doesn't persist.


Yeah, SSRS can be a little cumbersome with its handling of empty/null values. Try adding an explicit isnothing() before the yellow option:

=Switch(Fields!DaysToExpiration.Value > 31, "Green",
    Fields!User_Certification_Status.Value = 8, "Firebrick”,
    Fields!User_Certification_Status.Value = 13, “Orange”,
    Fields!User_Certification_Status.Value = 11, “Orange”,
    Fields!User_Certification_Status.Value = 10, “Orange”,
    Fields!User_Certification_Status.Value = 12, “Orange”,
    IsNothing(Fields!DaysToExpiration.Value), "Gray",
    Fields!DaysToExpiration.Value <= 30, "Yellow",
    1=1, "Gray")


This happened in SSMS 2017.  There was a pop-under "Add table to view" dialog that I didn't know about.
When I went to close SSMS it warned me about the dialog (which I could not close/cancel), then gave me an ugly exception about protected memory.
After restarting SSMS it all worked again.


Reloading .sql file from java into PostgreSQL - Windows

This is probably not the best way to do what you want to do.  The "proper" way would probably be to execute the SQL commands in your file via jdbc.

That said, that's a long way from where you are now, and we can probably make what you are trying to do work.

The likely problem is that there is a space in the path to psql.exe.

When you use that particular format of exec, java uses a vanilla string tokenizer to split your string into an array, each entry being separated by a space.  So it's going to be trying to execute a command "c:/Program" and pass it parameters, the first of which is "Files/PostgreSQL/9.2/bin/psql.exe" and, um, that isn't going to go so well.

Use exec (String[]) instead:
http://docs.oracle.com/javase/6/docs/api/java/lang/Runtime.html#exec(java.lang.String[])

This method doesn't apply the tokenizer, because it's accepting an array, so it doesn't think it needs to.

Try this:

String path = "H:/Data/samplescript_postgres.sql";
final String [] cmd = { "C:/Program Files/PostgreSQL/9.2/bin/psql.exe",
       "-U", "postgres",
       "-d", "sampledb",
       "-f", path
     };

try {
    reloadProcess = Runtime.getRuntime().exec(cmd);
    if(null!=reloadProcess){
        if(reloadProcess.waitFor()==0){
            System.out.println("Reloaded");
        }
    }
} catch (IOException e) {
    e.printStackTrace();
} catch (InterruptedException e) {
    e.printStackTrace();
}

You can see that I've created the array manually.  For readability, I've put the option "-U" and the value "postgres" on the same line, but they're separated by a comma.


As far as your asking about both size and speed, yes, Russell's right. You're going to have to actully fill a table with sample data, and test representative queries to see how they perform.

For the size issues, you can't always calculate the exact size for the indexes, but see the MySQL documentation for Data Type Storage Requirements and the appropriate documentation for the storage engine you're using. for information about row overhead and some guidance for estimating indexes.

(for instance, you're going to hit the 2**32 limit on rows in MyISAM, so you'll have to build with big tables support)

As for if you'll need multiple databases -- you shouldn't.  If you need to spread it across multiple tables, you can always use partitioning, which I think should also help if you're pushing up against file system limits.


Is there a limit to the number of objects that can be dropped and recreated (frequently) in SQL Server?

Looks like the object_ids increase rapidly and roll over frequently.  EG
declare @i int = 0
while @i < 10000
begin
  drop table if exists t
  create table t(id int)

  set @i += 1
  if @i % 100 = 0
  begin
    declare @objectid int = object_id('t')
    RAISERROR ('object_id %i', 0 , 1, @objectid) WITH NOWAIT
  end
end

outputs
object_id 834515239
object_id 287037291
object_id 1887042991
object_id 1339565043
object_id 792087095
object_id 244609147
object_id 1844614847
object_id 1297136899
object_id 749658951
object_id 202181003
object_id 1802186703
object_id 1254708755
object_id 707230807
object_id 159752859
object_id 1759758559
object_id 1212280611
object_id 664802663
object_id 117324715
object_id 1717330415
object_id 1169852467


what is MDL? (e.g. 'MDL semantics' or 'MDL locks') Probably Galera related

It stands for "MetaData Locking" seeing the sources of MySQL https://dev.mysql.com/doc/dev/mysql-server/8.0.0/mdl_8h.html

It's a way to keep an ACID database by avoiding concurrent access to the same data. The idea is that the metadata of a table are "locked" when a query inside a transaction uses this table. Doing so, another transaction will not be allowed to do DDL queries on these locked tables.

For instance, if you do a START TRANSACTION then a SELECT * FROM t in one client application, then the metadata of the table t are locked. No other client will be able to do a ALTER TABLE t ... because it would mess with the table structure while the 1st client transaction is still opened.

More infos in MySQL doc https://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html


This appears to give you what you want.  I added logic where the ENTITLEMENTS are being generated to eliminate rows that are null or blank and wrapped that section in an isnull.

--demo setup
drop table if exists staff_user_data
go
CREATE TABLE staff_user_data (
  EMPLOYEEID INTEGER,
  USERNAME VARCHAR(7),
  ISPRIMARY VARCHAR(1),
  ENTITLEMENTS VARCHAR(31)
);

INSERT INTO staff_user_data
  (EMPLOYEEID, USERNAME, ISPRIMARY, ENTITLEMENTS)
VALUES
  ('123456', 'BSmith', 'Y', 'ac-les-all-full;ac-haw-all-full'),
  ('123456', 'BSmith', 'N', 'ac-sco-all-full'),
  ('987654', 'ZJones', 'N', NULL),
  ('987654', 'ZJones', 'Y', 'ac-trn-std-full'),
  ('987654', 'ZJones', 'N', ''),
  ('456789', 'RTaylor', 'Y', '');

--solution
SELECT
    USERNAME
    ,ENTITLEMENTS = isnull((
        SELECT DISTINCT ';' + ENTITLEMENTS
        FROM [staff_user_data] STS
        WHERE STS.[EMPLOYEEID] = STP.[EMPLOYEEID]
        and ENTITLEMENTS is not null and ENTITLEMENTS <> ''
        FOR XML PATH('')),'')
FROM [staff_user_data] STP
WHERE ISPRIMARY = 'Y'

 USERNAME | ENTITLEMENTS                                     |
|----------|--------------------------------------------------|
| BSmith   | ;ac-les-all-full;ac-haw-all-full;ac-sco-all-full |
| ZJones   | ;ac-trn-std-full                                 |
| RTaylor  |                                                  |


Issue with 2016 developer edition install

Given this image, I can imagine you are selecting to install every single feature during setup:

I would start with just the set of features you need, since selecting everything - especially if you don't actually need everything - creates a whole bunch of dependencies for little gain.

You can always go back and add features as you realize you need them.


Is Postgres ignoring my function cost annotation?

I believe the costs refer to different units of measurement. The cost when used in functions is the estimated execution cost in units of cpu_operator_cost, whereas the EXPLAIN plan costs are units of disk page fetches (seq_page_cost).

For function cost details, see http://www.postgresql.org/docs/current/static/sql-createfunction.html

For EXPLAIN plan costs, see http://www.postgresql.org/docs/current/static/using-explain.html


mysql large number of opened tables -- what does this mean and how can I reduce?

table_open_cache is the setting that is most likely to get the host off your back.

SHOW VARIABLES LIKE '%open%';  And how long had the system been up when the STATUS was captured?  (Need "per second", not absolute counts.)

Provide me with ram size, SHOW VARIABLES and SHOW GLOBAL STATUS; I will critique dozens of things.

Are any of the tables PARTITIONed?  (Each 'partition' is effectively a separate 'table'.)

Also, rethink your 300x59; those are moderately large numbers.

VARIABLES & STATUS Analysis

Observations:
Version: 5.6.17-log;
4 GB of RAM;
Uptime = 145d 22:45:14;
You are not running on Windows;
Running 64-bit version;
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

key_buffer_size -- lower to 20M; your 400M is wasting RAM.

table_open_cache seems to be doing fine.  So, I don't see the need in increasing open file limit.  (Nor is there any harm.)

A lot of table scans, etc.  Suggest you lower long_query_time to 1 and turn on the slow_query_log.  Then we can look in the slowlog for which queries need improvement -- either via composite indexes or other techniques.

expire_logs_days is currently 1.  That gie you only one day to discover that something is broken in replication and/or binlogs before you lose info.

Remove most or all OPTIMIZE TABLE calls.

Details and other observations

( Opened_tables ) = 0.15/second -- This is a reasonably low and quite respectable rate.  After all, you have been up for 20 weeks, and this is a 'counter'.  Point out the 20 weeks to the Hosting provider.  table_open_cache is number of entries in a cache for "Opened" tables.  Perhaps there is a flurry of opening during backup, but no opening the rest of the time?  As for "I do have table_definition_cache set to 4096 as I wasn't able to run queries on views until I increased it." -- Do your VIEWs touch hundreds or thousands of tables?  (Yikes!)

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (400M - 1.2 * 4180 * 1024) / 4096M = 9.6% -- Percent of RAM wasted in key_buffer.
-- Decrease key_buffer_size.

( Key_blocks_used * 1024 / key_buffer_size ) = 4,180 * 1024 / 400M = 1.0% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size to avoid unnecessary memory usage.

( innodb_buffer_pool_size / _ram ) = 2,306,867,200 / 4096M = 53.7% -- % of RAM used for InnoDB buffer_pool -- For only 4GB of RAM, this is probably OK, unless you have other applications in the same server.

( table_open_cache ) = 4,096 -- Number of table descriptors to cache
-- Several hundred is usually good.  However, you have a lot of tables, so, this may be OK, or even too small.

( table_open_cache_instances ) = 1 -- Suggest changing to 8.

Table_open_cache_overflows and _misses are each less than 0.1/sec. -- the table_open_cache is big enough.

( Open_tables / table_open_cache ) = 100% -- But, since you have been Up for several months, this is not necessarily bad.

( (Com_show_create_table + Com_show_fields) / Questions ) = (153314 + 9291657) / 543294681 = 1.7% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor.

Query cache -- various STATUS values indicate that it is pretty good as it stands.

( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 10,616,310 / (10616310 + 13112655) = 44.7% -- Percent of temp tables that spilled to disk
-- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.

( Com_rollback / Com_commit ) = 646,634 / 1329015 = 48.7% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic

( Select_scan ) = 16,557,535 / 12609914 = 1.3 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 16,557,535 / 57256550 = 28.9% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 1587 - 1526 ) / ( 1587 + 1526 ) = 2.0% -- Are you closing your prepared statements?
-- Add Closes.

( binlog_format ) = MIXED -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.

( expire_logs_days ) = 1 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.

( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( max_connect_errors ) = 10,000 -- A small protection against hackers.
-- Perhaps no more than 200.

( Connections ) = 28,367,185 / 12609914 = 2.2 /sec -- Connections
-- Increase wait_timeout; use pooling?

( Max_used_connections / max_connections ) = 167 / 410 = 41% -- Suggest lowering max_connections to, say, 200.

( innodb_io_capacity_max ) = 800 -- This is rather low, but perhaps OK.

( report_port ) = 1 -- This is probably bad, but I have no experience to be more specific.  Most people leave it at 3306.

Com_create_db every 4 minutes and Com_drop_db every 2 minutes -- Very high; what's going on?

Com_optimize = 4.9/hour -- OPTIMIZE is virtually useless, especially on InnoDB.


Copy package in Production DB to Test DB with dependencies?

In SQL Developer you can right-click on an object (table, package, procedure, etc) and there will be an option for 'Quick DDL'. Open that and you have options on what to do with the ddl, including saving to a file.  That's better than select/ctl-C/ctl-v, but there is still a better way since you know what you want.

You can use expdb (DataPump export) to export the ddl for the selected objects.  That will create a binary .dmp file that is, essentially, all of the DDL you want.  You will need to read up on the options for limiting what gets exported or you very well could get the entire database, including data.  You then use impdp (DataPump import) to process that dmp file and create/load your objects.  Even when you limit the objects that get exported, you can specify whether or not to include the data. I leave it as an exercise for the student to study the relevant documentation at https://docs.oracle.com/database/121/SUTIL/GUID-501A9908-BCC5-434C-8853-9A6096766B5A.htm#SUTIL2877

Another method you might consider if you JUST want the DDL, without the DML to load tables, would be to write a script using the DBMS_METADATA.GET_DDL procedure, documented at https://docs.oracle.com/database/121/ARPLS/d_metada.htm#ARPLS66872

Now, you may say "Can't you just give me the script, getting this DDL is a distraction from my real job."  But you are just starting your career and time spent learning to read the docs, learning that these tools even exist, and learning how to use them is time well invested.  When I was just starting my career (as a COBOL programmer in 1981) I would take reference manuals home with me for bed-time reading.  It served me all, as within 3 years I was the lead tech on a project, directing the work of several programmers with more experience but less inate curiosity about how things really worked and about learning new things about the tools they thought they already knew.


Can SQL Server Always Encrypted be used for temporary tables?


select rr.review_id
from review_rating rr
inner join review rw on rr.review_id = rw.id
group by review_id
having MOD(COUNT(*),6)=0


Yes, you can have a FOREIGN KEY constraint that references a column with a UNIQUE constraint.

The syntax error you get is because you didn't provide a datatype for the column. It should be the same type as the referenced column: username varchar(50).

Another issue is the syntax for foreign constraints. If you put them "inline", i.e immediately after the column definition, you skip the FOREIGN KEY and have only REFERENCES:

-- Don't do this in MySQL !!!!
CREATE TABLE profileimage
( imageid int  NOT NULL PRIMARY KEY,
  username varchar(50)
     REFERENCES member (username)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
  imagepath varchar(255) NOT NULL
) ENGINE=InnoDB ;

or you put them separately, with the FOREIGN KEY keyword:

-- Do this in MySQL
CREATE TABLE profileimage
( imageid int NOT NULL PRIMARY KEY,
  username varchar(50),
  imagepath varchar(255) NOT NULL,
  FOREIGN KEY (username)
     REFERENCES member (username)
     ON DELETE CASCADE
     ON UPDATE CASCADE
) ENGINE=InnoDB ;

Big surprise: Both syntaxes work in MySQL with a catch: the first is parsed for syntax validity (and it passes) but the foreign key is not created! (you get a warning)

The second syntax works fine.


You can do something like this:

DECLARE @i INT = 1
DECLARE @totalvars INT = 5
DECLARE @newvar VARCHAR(50) = ''

WHILE @i < @totalvars

BEGIN

SET @newvar = 'DECLARE @newvar' + CONVERT(VARCHAR(50), @i) + ' = ?;'

SET @i += 1

RAISERROR (@newvar, 0, 1) WITH NOWAIT;

END

So we're all clear, this won't create variables for you unless it's part of a larger block of dynamic SQL. You're probably better off using a temp table. But on the off chance you're using more dynamic SQL, you could use a loop like this to append the variable declarations just fine.


My Galera Cluster does not connect across AWS regions

In wsrep_node_address on Amazon EC2, you must use the global DNS name, instead of the internal IP address.


SQL Server 2008 R2 index rebuild fails with severity 17

My experience is that it is always going to do an online rebuild in the filegroup on which the index lives.  It has to map the existing index and hold enough space for, essentially, one copy.

You should only be getting the error when an index which is too large to hold mappings (the copy) is rebuilt - for instance, one time it may be fragmented enough to qualify in Ola's script and the next time it may not be.

There is a great article http://technet.microsoft.com/en-us/library/ms179542(v=sql.105).aspx  which I had to read several times when running into disk space issues with indexes.


Is it correct way having status or Summary columns in parent table or should choose another precedure?

Question 3: Is this best practice? is there any better solution for this type of problems.

"Best Practice" might be dependent on the environment.  The recommendation would be to try and figure out why a VIEW is "slow".  If you can't make it faster, then your solution(s) seem to be fine (after considering the other Business Requirements).

  Question 1: Is this a correct version, (am I denormalized my tables?)

In your original design, the JobActions table would be a Slowly Changing Dimension (SCD) Type 2 type of data.

In your other design, the additional columns (in the Jobs table) would be akin to SCD Type 4 type of data.

Your model looks fine (to me).

  Question 2: Should I use trigger for this type of jobs or should update from inside my code?

Let's start with "should the update be placed inside your Application Code"? IMO - no.  All applications should follow the same process when working with the same set of data.  The only way to ensure that is to have the update logic within the database.

Should it be in a trigger? This answer may be highly opinionated.

On one hand, it could be easy to implement the required update logic in the trigger. On the other hand, implementing the delete logic might be difficult/impossible.

Alternative

Place the code in a Stored Procedure.

That is: Follow the Model-View-Control (MVC) philosophy

hide your data Model from the application
the application fetches the data from a View
all DML manipulation is Controlled via Stored Procedures

In your example, you don't really need to create a VIEW on the tables (thus hiding your data Model).  You just need to prevent (through GRANT/REVOKE) the application from performing DML outside of the Stored Procedures.


Loading a table with input from two different input files

Create a table (t1) with columns matching the columns of one file.  Load that file into it.

Create another table (t2) with columns matching the columns of the other file.  Load that file into it.

Now create the main table (if it does not already exist).  Load it with something like

INSERT INTO main (a,b,c,d,e)
    SELECT t1.x, t2.y, t1.z, t1.w, t2.v  -- in whatever order is correct
        FROM t1
        JOIN t2 ON ... -- explain how the two tables (files) need to be paired up.


Trigger To Find Deleting Job Agent History

Here is the code to create an audit table and an audit trigger containing the information you are requesting. I have tested this and it gives me the value SQLAgent - Job Manager under the column AppName. This logs consistently when jobs run and it deletes out data in my history. (I changed it to keep 2 rows in the history to force it to delete to test.)

So the function APP_NAME() is really what you were looking for.

You can find more information related to this by BateTech on Stack Exchange.

CREATE TABLE msdb.dbo.HistoryPurgeReport
(PurgeID INT Identity(1, 1) PRIMARY KEY, [instance_id] [int] NOT NULL, [job_id] [uniqueidentifier] NOT NULL,
[step_id] [int] NOT NULL, [step_name] [sysname] NOT NULL, [sql_message_id] [int] NOT NULL,
[sql_severity] [int] NOT NULL, [message] [nvarchar](4000) NULL, [run_status] [int] NOT NULL,
[run_date] [int] NOT NULL, [run_time] [int] NOT NULL, [run_duration] [int] NOT NULL,
[operator_id_emailed] [int] NOT NULL, [operator_id_netsent] [int] NOT NULL, [operator_id_paged] [int] NOT NULL,
[retries_attempted] [int] NOT NULL, [server] [sysname] NOT NULL, UpdatedBy NVARCHAR(128), UpdatedOn DATETIME,
AppName nvarchar(255), HostName nvarchar(255), ContextInfo nvarchar(255))
GO

USE MSDB

CREATE TRIGGER trg_HistoryPurgeReport ON msdb.dbo.sysjobhistory
AFTER DELETE
AS
BEGIN
    INSERT INTO HistoryPurgeReport ([instance_id], [job_id], [step_id], [step_name], [sql_message_id], [sql_severity],
    [message], [run_status], [run_date], [run_time], [run_duration], [operator_id_emailed], [operator_id_netsent],
    [operator_id_paged], [retries_attempted], [server], UpdatedBy, UpdatedOn, AppName, HostName, ContextInfo)
    SELECT [instance_id], [job_id], [step_id], [step_name], [sql_message_id], [sql_severity],
    [message], [run_status], [run_date], [run_time], [run_duration], [operator_id_emailed], [operator_id_netsent],
    [operator_id_paged], [retries_attempted], [server], SUSER_SNAME(), getdate(), APP_NAME(), HOST_NAME(), CONTEXT_INFO()
    FROM deleted
END
GO


If you want to shrink ibdata1, so that it should only contain the metadata, You may try these steps

To shrink ibdata1 once and for all you must do the following:

MySQLDump all databases into a SQL text file (as bkp_all_db.sql)
Drop all databases (except mysql schema)
Stop MySQL
/etc/init.d/mysql stop
Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Now remove ibdata1, so that there should only be the mysql schema in /var/lib/mysql

rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

Start MySQL instance, This will recreate ibdata1, ib_logfile0 and ib_logfile1 at 1G each

service mysqld start
Reload bkp_all_db.sql into mysql (import). ibdata1 will grow but only contain table metadata

http://mysqlrockstar.blogspot.in/2014/07/mysql-ibdata1-file-shrink.html


Is it possible to "bind" sp_executesql to the calling procedure, as recorded in the Query Store?

I think that embedding comments is currently the best way to distinguish different pieces of dynamic SQL when viewed from Query Store / Extended events / Trace - unfortunately.
I've had plenty of runtime errors with dynamic sql & temp tables so I don't think it does ENSURE temp table validity & stability.
You can use @@nestlevel to see how many SPs / functions are currently in the call stack, but I think you want more information than that. You can use error_procedure() - but only in a catch block, sounds like what you want is Microsoft to add a function current_procedure() or calling_procedure() that will tell you the name of either the current routine or the routine that called this one - maybe add a request here ? :- https://feedback.azure.com/forums/908035-sql-server


Update one instance of Sql Server 2008 R2 Standard to SP2?

Yes, you can choose which instance(s) you wish to apply the service pack. They'll all be checked by default; as will the client tools / shared features (there is only one "instance" of those):

You can re-run the installer later to update any that you didn't update this time; the ones that are already at SP2 will not be selectable.

On this VM, I have 2008 R2 RTM (no patches whatsoever) and 2008 R2 with Service Pack 2 and CU9, installed and running side by side with no issues:

Now, why you want to have a mix of patched and unpatched instances on a single server might be a different question...


referntial integrity issue with two tables cant delete a record

You have a foreign key from payment details to customers.

This is a declarative referential integrity constraint which enforces that you can’t have a row in payment details (‘child’ row) for which the customer is is not in customers (‘parent’).

The idea is to maintain data consistency - if you had an ‘orphan’ child row without a ‘parent’ Customer, you would never know which customer made the payment.
If you want to delete the customer and his payments, you can either delete the payments first:

DELETE FROM payment_details where customer_id = 1

Or you can define the foreign key as a cascading one in which case the engine will delete it for you:

CONSTRAINT customer_idfk1 FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE


There is a SQL command that will show OPEN transactions. (DBCC OPENTRAN)

http://msdn.microsoft.com/en-us/library/ms182792.aspx

Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information


RMAN restore of primary database - impact on standby database

You should be able to use Flashback Database to get the standby back to the point in time (i.e. SCN) to which the primary was restored (Flashback Database is a requirement for automated failover).  If you can do this, then Data Guard should be able to wire things back up together again.
Have a look at these:

http://www.br8dba.com/resetlogs-on-primary-where-standby-in-place/
http://oracle-help.com/dataguard/flashback-database-after-resetlogs-in-data-guard/

If in doubt, though, or if you don't have Flashback Database enabled, then trash the standby and recreate it from scratch.


Get complete value of varchar(max) field - SQL Server 2005

If the issue you are having is Truncation you can use this XML approach that gets around most of the issues with XML entitisation.

declare @VeryLongText nvarchar(max) = '';

SELECT top 100 @VeryLongText = @VeryLongText + '

' + OBJECT_DEFINITION(object_id)
FROM sys.all_objects
WHERE type='P' and is_ms_shipped=1

SELECT LEN(@VeryLongText)

SELECT @VeryLongText AS [processing-instruction(x)] FOR XML PATH('')

PRINT @VeryLongText /*WILL be truncated*/

Make sure that the "XML data" limit in SSMS is set sufficiently high!


select for update gives error on indexed column

SELECT FOR UPDATE tends to do row level locking in a deep way we tend to take for granted: Locking the corresponding index entries as well.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Reasons for occasionally slow queries?
Trouble deciphering a deadlock in an innodb status log
Will these two queries result in a deadlock if executed in sequence?

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data at the index level.

You also have special cases of SELECT where you can lock specific rows on demand.

Look at this issue from another angle, "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT?" The answer to that can be Yes under a certain condition: If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.


In which situations is TokuDB higher performance?

Unless your WordPress site is huge, you are unlikely to see much performance improvement in switching to TokuDB, if any at all. TokuDB's 20x performance advantage is for an indexed insertion workload, where the secondary indexes do not fit in memory.

You will be using less space on disk with TokuDB's compression.


Should Service Broker use SQL or Windows Authentication?

Service Broker relies on certificates which is shared between databases. No other info for the user is shared.
Service Broker provides 2 distinct types of security -

Dialog security — Encrypts messages in an individual dialog conversation and verifies the identities of participants in the dialog. Dialog security also provides remote authorization and message integrity checking. Dialog security establishes authenticated and encrypted communication between two services.
Transport security — Prevents unauthorized databases from sending Service Broker messages to databases in the local instance. Transport security establishes an authenticated network connection between two databases.

Its imperative to understand difference between authentication and encryption. Authentication will let the service identify the identity of the user trying to connect to the service whereas encryption will ensure that the messages sent over network are encrypted during transmission.
Service broker supports :

Windows Authentication : Depending on how your network is configured, it will use Kerberos or NTLM protocols to authenticate Service Broker Endpoints when endpoints are in the same windows domain or between trusted domains.

Certificate Based Authentication : This authentication is used when the servers involved are members of unrelated domains and the default windows authentication is not possible.
Refer to : How does Certificate based Authentication work ? and Remus's answer here.

So, depending on how your servers are configured, you can choose Windows Authentication (default) or Cert based authentication.
BOL has a good article on Identity and Access Control (Service Broker)


How to identify the column used to partition a table from the Postgres system catalogs

I'm not a PostgreSQL pro, but digging a bit I've founded a solution that perhaps can help you. (Works only for version 10 or above)

First I've slightly modified you table by adding two columns to the partition definition (just to show you the final result):

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (city_id,logdate);

This is my solution:

select
    par.relnamespace::regnamespace::text as schema,
    par.relname as table_name,
    partnatts as num_columns,
    column_index,
    col.column_name
from
    (select
         partrelid,
         partnatts,
         case partstrat
              when 'l' then 'list'
              when 'r' then 'range' end as partition_strategy,
         unnest(partattrs) column_index
     from
         pg_partitioned_table) pt
join
    pg_class par
on
    par.oid = pt.partrelid
join
    information_schema.columns col
on
    col.table_schema = par.relnamespace::regnamespace::text
    and col.table_name = par.relname
    and ordinal_position = pt.column_index;

schema                      | table_name  | num_columns | column_index | column_name
:-------------------------- | :---------- | ----------: | -----------: | :----------
fiddle_ctcqwfrzpcyngmgnqkdy | measurement |           2 |            1 | city_id
fiddle_ctcqwfrzpcyngmgnqkdy | measurement |           2 |            2 | logdate

db<>fiddle here

pg_partitioned_table

  The catalog pg_partitioned_table stores information about how tables
  are partitioned.

Unnesting partattrs you can get column index of each row involved in the partition. Then you can join information_schema.columns just to retrieve the name of every column.

+-----------+------------+---------------------+-------------------------------------------------------------|
| partattrs | int2vector | pg_attribute.attnum | This is an array of partnatts values that indicate          |
|           |            |                     | which table columns are part of the partition key.          |
|           |            |                     | For example, a value of 1 3 would mean that the first       |
|           |            |                     | and the third table columns make up the partition key.      |
|           |            |                     | A zero in this array indicates that the corresponding       |
|           |            |                     | partition key column is an expression, rather than a simple |
|           |            |                     | column reference.                                           |
+-----------+------------+---------------------+-------------------------------------------------------------|


More tables or More records performance wise

Both of these schema anti-patterns have been repeatedly refuted:

Do not have multiple 'identical' tables.

Do not spread an array across columns.

So have one table with lots of rows:
    sites
       id
       url
       region
       value
       date_created
       tech_category

Use suitable composite indexes for efficiency.  (We need to see your SELECTs to advise further.)
Use "pivoting" to make the 4 region columns.  (Or >4 when you increase the number of regions.)
As for "performance"...  If you don't have a billion rows, performance is probably not a problem.  When the table gets that big, we can discuss "summary tables".


Why is this datetime index not being used?

SQL server can definitely do an index seek and then a lookup, even when you don't cover the query.

The optimizer has no idea what values you have in your variables (that is how variables work). So it has to guess the selectivity. You can look at the actual execution plan and see how many rows it guesses. Apparently it guesses so many rows so it decides that it is better to do a table scan (cl ix scan).

If you add OPTION(RECOMPILE) at the end of the query, you should see a different estimated selectivity, and potentially usage of the index (all based on what selectivity you have in the end).

Also, if you have literals (values are known) or stored procedure parameters (values are sniffed), you'll see how it will estimate differently.


Can I write a Select query to update the data too

This is not possible, at least not with plain JDBC.

But, an ORM might give you what you are looking for. However, the use of ORMs is controversial. Have a look at https://stackoverflow.com/questions/452385/what-java-orm-do-you-prefer-and-why for an interesting collection of opinions. You can find a few example ORMs in there too.


After Update/Insert trigger sometimes fails to do its job

While testing with random data the above trigger worked perfectly for insertion.
But as Mat said in the comment, while updating the DOCNUMBER column of INT_DOC the after update trigger failed to update that value in DOC_IDS table.
Here is the output of the sample test:

SQL> INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT)
VALUES('BRTYRARA1',to_date('12.01.2015','DD.MM.YYYY'),'Ellie Johnson','YetAnotherSubject');

1 row created.

SQL> select * from doc_ids;

no rows selected

SQL> update int_doc set confirmed=1,docnumber=1 where docid='BRTYRARA1';

1 row updated.

SQL> select * from doc_ids;

DOCID
----------
1

SQL> INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT,confirmed)
VALUES('AFFXRARA1',to_date('12.07.2015','DD.MM.YYYY'),'Jay Hons','YetAnotherSubject',1);

1 row created.

SQL> select * from doc_ids;

DOCID
----------
1

SQL> select docnumber from int_doc;

DOCNUMBER
----------
1

SQL> update int_doc set docnumber=2 where docid='AFFXRARA1';

1 row updated.

SQL> select * from doc_ids;

DOCID
----------
1
2

SQL> select docnumber from int_doc;

DOCNUMBER
----------
1
2

SQL> update INT_DOC set docnumber = 3 where docnumber = 1;

1 row updated.

SQL> select docnumber from int_doc;

DOCNUMBER
----------
3
2

SQL> select * from doc_ids;

DOCID
----------
1
2

Here i got the DOCNUMBER 3 in INT_DOC table but not in the DOC_IDS table.


In general you can run any SP as a query by getting rid of the "CREATE PROCEDURE" part and defining all of the parameters as variables.

For example

CREATE PROCEDURE test (@var1 int, @var2 int) AS
PRINT @var1
PRINT @var2

Would become

DECLARE @var1 int
DECLARE @var2 int
PRINT @var1
PRINT @var2

You might also ask them if it is ok if you create a "DBA" database for code like this and put the SP there.  That way the SP is on the instance and will run correctly, but is still segregated from the rest of their databases.


The simplest way to do this is give IUSR_[computer name] (where that is your web server machine) read/write access to the relevant database (use the datereader and datawriter builtin roles, DON'T use dbo).

If you are using stored procedures, you will need to grant execute permissions to  IUSR_[computer name] as well.

In addition, eet you connection string to use a Trusted connection:

"Data Source=myServer;Initial Catalog=myDataBase;Integrated Security=SSPI;"


Why does BETWEEN use a btree index but "element is contained by" range operator (<@) does not?

An index can only support operators that belong to its operator class.
SELECT ao.amoplefttype::regtype,
       op.oprname,
       ao.amoprighttype::regtype
FROM pg_opfamily AS of
   JOIN pg_am AS am ON of.opfmethod = am.oid
   JOIN pg_amop AS ao ON of.oid = ao.amopfamily
   JOIN pg_operator AS op ON ao.amopopr = op.oid
WHERE am.amname = 'btree'
  AND ao.amoplefttype = 'timestamptz'::regtype;

       amoplefttype       | oprname |        amoprighttype
--------------------------+---------+-----------------------------
 timestamp with time zone | <       | date
 timestamp with time zone | <=      | date
 timestamp with time zone | =       | date
 timestamp with time zone | >=      | date
 timestamp with time zone | >       | date
 timestamp with time zone | <       | timestamp without time zone
 timestamp with time zone | <=      | timestamp without time zone
 timestamp with time zone | =       | timestamp without time zone
 timestamp with time zone | >=      | timestamp without time zone
 timestamp with time zone | >       | timestamp without time zone
 timestamp with time zone | <       | timestamp with time zone
 timestamp with time zone | <=      | timestamp with time zone
 timestamp with time zone | =       | timestamp with time zone
 timestamp with time zone | >=      | timestamp with time zone
 timestamp with time zone | >       | timestamp with time zone
(15 rows)

There is no <@ operator in there, so a B-tree index cannot support this operator.
GIN indexes can support <@, but not with the constant on the right side.
You will have to rewrite the query and use BETWEEN.
Remark: There is not a fundamental reason why it is like that, it is just how indexes work in PostgreSQL. It is even possible to write an optimizer support function that would do exactly that, but your use case is so exotic that PostgreSQL doesn't want to spend optimizer time and development effort on it.


\u0087 (END OF SELECTED AREA) and \u0081 are control characters in the LATIN-1 block. Displaying them as \u0087\u0081 is in fact the convention, and it seems to be what your terminal is doing.

Can you show us the Perl script to insert the data into the database? My assumption is that Perl is inserting non-utf8 data because working with utf8 in perl is a PITA. That PostgreSQL assumes it's utf8 and that the terminal has no idea of how to display it, becuase it's

not really utf8, just marked up as utf8.
or, you have legitimate control characters encoded in your utf8.

Be careful if you used utf8::upgrade or the like which sets the flag without decoding from the original format.

Advice

Update DBD::Pg, DBD::mysql

First update both DBD::Pg, and DBD::mysql

cpan DBD::Pg DBD::mysql

Bug in DBD::mysql

Reviewing change logs, DBD::mysql has always had a bug, you may even want to install the development version (DBD-mysql-4.041_01)..

2016-12-12 Patrick Galbraith, Michiel Beijen, DBI/DBD community (4.041_1)
* Unicode fixes: when using mysql_enable_utf8 or mysql_enable_utf8mb4,
  previous versions of DBD::mysql did not properly encode input statements
  to UTF-8 and retrieved columns were always UTF-8 decoded regardless of the
  column charset.
  Fix by Pali Rohár.
  Reported and feedback on fix by Marc Lehmann
  (https://rt.cpan.org/Public/Bug/Display.html?id=87428)
  Also, the UTF-8 flag was not set for decoded data:
  (https://rt.cpan.org/Public/Bug/Display.html?id=53130)

Who knows what the hell they're doing there. Unicode is complex and it's not surprising that MySQL developers and users don't understand it.

Unsetting assumptions

I would unset pg_enable_utf8 and mysql_enable_utf8. Pretend these options exist. You're force-setting a utf8 flag on the internal variable. You probably don't want to do that. You clearly can't ensure everything is utf8, or it'd be working properly. Time to remove assumptions. Not create more assumptions.

We need a sample case. You claim foreign characters are displaying as Unicode escape sequences, but you only present control-characters (which can't be displayed). Perhaps you can simply this whole thing, and dump the table pg_dump -t <Table> or copy the problematic rows into a temp table and dump that. Then we can review that data and see what we've got.


Is there a reason I should not set my default data location to google drive when setting up PostgreSQL?

There are two good reasons not to use Google drive for your data directory: data integrity and performance.

The second is obvious because of the latency.

For the first: Google drive is a file server. I am sure that it does not offer the guarantees required by a relational database, particularly that you can reliably sync data to disk. Even a Windows file share does not offer these guarantees.


Maximum concurrent connections to SQL Server Express 2017 installed on Windows Server 2016 Essentials

@@MAX_CONNECTIONS

The @@MAX_CONNECTIONS function will according to docs:

  Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured.

So the above will give us some value the SQL Server is or was limiting the number of connection to.

On most servers this will likely return the default value of 32767.

Another two ways to access the value of the setting are as follows:

SELECT value FROM sys.configurations
WHERE name = 'user connections'

Or

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE
GO
EXEC sp_configure @configname = 'user connections';
GO
EXEC sp_configure 'show advanced option', '0';
GO
RECONFIGURE
GO

Why would I want to change this option

As I would suggest is the case with most advanced options, if you're asking the question it's unlikely you need to. The use cases are likely far between, but in general this setting can be used to limit the impact users are having on a server in a very high level sense.


MySQL:- Join two SELECT queries including join and aliases also

It would be better to simplify the query without nested levels of subqueries. You already have as simple table (t1) joined to a derived table (t2). Join them further to the other query2 as a derived table (lets call it t3 in lack of a better name):

SELECT
    t1.*,

    t2.received_quantity,
    t2.ordered_quantity,
    t2.unit_cost,

    t3.open_quantity
FROM
        item_master AS t1
    INNER JOIN
        (
        SELECT item_master_id,
           SUM(received_quantity) AS received_quantity,
           SUM(ordered_quantity)  AS ordered_quantity,
           SUM(unit_cost)         AS unit_cost
        FROM material_line_item
        GROUP BY item_master_id
        ) AS t2
    ON t1.id = t2.item_master_id

    INNER JOIN
        (
        SELECT item_master_id,
               SUM(open_quantity) AS open_quantity
        FROM closed_stock
        GROUP BY item_master_id
        ) AS t3
    ON t1.id = t3.item_master_id ;

If there are items in the item_master table that don't appear in one or both the other two tables, you might want to change these INNER joins to LEFT joins.


I think that in this two lines "server" means: connection to PostgreSQL managed by pgbouncer. "Server connection can be reused" means that established connection to postgreSQL is not closed but kept open and will be used by another client. In this context I understand that "server is released back" to the pool means the same thing: the connection to PostgreSQL is kept open and will be used by another client.

This is just the way connexion pooling works.


I have deleted Oracle listener from windows services

Test case deleting  Listener and creating service manually

C:\>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 04-AUG-2019 02:58:56

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                04-AUG-2019 02:57:36
Uptime                    0 days 0 hr. 1 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\XXX\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\>sc query type=service | findstr Oracle*
SERVICE_NAME: OracleXETNSListener
DISPLAY_NAME: OracleXETNSListener
SERVICE_NAME: OracleServiceXE
DISPLAY_NAME: OracleServiceXE

I'll stop listener and delete the service

C:\>sc delete OracleXETNSListener
[SC] DeleteService SUCCESS

C:\>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 04-AUG-2019 03:00:31

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error

Creating service manually

C:\>sc create OracleXETNSListener binpath=C:\oraclexe\app\oracle\product\11.2.0\server\BIN\tnslsnr.exe
[SC] CreateService SUCCESS

C:\>lsnrctl start

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 04-AUG-2019 03:01:20

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
System parameter file is C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Log messages written to C:\oraclexe\app\oracle\diag\tnslsnr\XXX\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                04-AUG-2019 03:01:22
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\XXX\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Optional:Once the service is created go to services and configure service to auto start


Create smaller row from alias and preserve column names

Unfortunately, a ROW() expression does not preserve column names. Use a subselect instead.
And json_agg() is simpler and faster for the task:

SELECT u.id, u.name
     , json_agg((SELECT x FROM (SELECT s.name, s.created) x)) AS sites
FROM   remodel.users      u
JOIN   remodel.user_sites us ON us.user = u.id
JOIN   remodel.sites      s  ON s.site = us.id
GROUP  BY u.id;  -- id must be the PK

The question has been answered before - with detailed explanation and links:

Select columns inside json_agg

To preserve column names in a plain array (not JSON or hstore), you need to use a registered row type for the row, else column names are lost in any case.
Either you already have a matching row type, or you register one (temporarily):

CREATE TEMP TABLE tmp_site(name text, created timestamptz)  -- use your data types!

Then:

SELECT u.id, u.name
     , array_agg((s.name, s.created)::tmp_site) AS sites  -- or whatever you do with s
FROM   remodel.users      u
JOIN   remodel.user_sites us ON us.user = u.id
JOIN   remodel.sites      s  ON us.site = s.id
GROUP  BY u.id

Details in the linked answer.


ORA-07445: An unexpected error: core dump

It's a bug. See Metalink document 382576.1.

The bug was fixed in Oracle 10.2.0.4, so upgrade to that release (preferably 10.2.0.5).

There are workarounds, but I'm not sure if it's ok to reproduce Metalink documents on this site.


Pros and cons of having one table per entity vs. having a shared table with an extra column indicating the entity type

The only thing I'll add is that I find it easier to split later than combine later. Depending on what type of information you are looking at about Boats or Cars and how many points you care about and what you are doing with the data (To @JackDouglas's point about needing more specifics to give specific advice) maybe you won't need them combined, maybe you will. If you aren't sure and can design with them combined and are leaning that way, it is worth a try and testing knowing that someday you may have to split them out. Won't make refactoring easy, but IMO it would be easi*er* than trying to combine the data, if only by a little.


SQL server suddenly backups are slow and async_io_completion shows suspended for long time

Check for log file fragmentation

use slow_database
go

select count(*) from sys.dm_db_log_info ( db_id() )

if this number is high (>10,000s) then maybe the log file fragmentation is the culprit


How to read multiple record from xml to insert in table in SQL?

You need to remove the / at the start of the value() function. i.e. this one Listing.value('(/.
You also need to remove the CROSS JOIN that occurs because you are using old style JOIN syntax between ListingTable(Listing) and TempTable(ReportData). You can do that by referencing the PCMReportData slightly differently.

Based on your XML schema, you should then get values e.g.

DECLARE @XMLRESULT XML;

SET @XMLRESULT = '<Results ReplyCode="0" ReplyText="Operation Successfull">
                    <Report PropertyType="ResidentialProperty">
                    <Listings>
                    <Listing>
                      <ListingID>759</ListingID>
                      <PCMReportData>
                        <Listing_StreetAddress>My Street Address</Listing_StreetAddress>
                      </PCMReportData>
                    </Listing>
                    <Listing>
                      <ListingID>852</ListingID>
                    </Listing>
                    </Listings>
                    </Report>
                    </Results>';

SELECT
  Listing.value('(ListingID/text())[1]', 'varchar(50)') AS ListingID,
  Listing.value('(PCMReportData/Listing_StreetAddress/text())[1]', 'varchar(50)') AS Address
FROM @XMLRESULT.nodes('/Results/Report/Listings/Listing') AS ListingTable(Listing);

You're doing a CROSS JOIN between ListingTable(Listing) and TempTable(ReportData). Therefore the 6 results, multiplied by the 6 inner results = 36.


SQL Server 2008 : force user to change password at next login

If you are creating a new login you have that option of " enforcing users to change the password at next login" from windows policies.
Therefore for To force a user to change password at next login, the sql login should be created with "Enforce Password policy" checked.

If you go back into this newly created login later, and look at the boxes, only 2 options are available to be checked. The “User must change password at next login” is grayed out.

  In order to access the box and force a password change, you need to
  change the password. The reason is that if the account is compromised,
  the hacker should not be the one to set a new password. The security
  model assumes the administrator can contact the legitimate owner
  offline and give them the new password.

You can refer here for same as well.