You should go with the second approach.  All of the string concatenation and type conversion just adds unnecessary CPU overheard to the query.

I can try this out on the Stack Overflow schema.  I'm going to query the user's table, and create a supporting nonclustered index on the date field:

CREATE NONCLUSTERED INDEX IX_CreationDate ON dbo.Users (CreationDate);

Here's an example of the first approach:

SELECT
    CAST(YEAR(u.CreationDate) AS NVARCHAR(4))
        + '-'
        + CAST(MONTH(u.CreationDate) AS NVARCHAR(2)),
    COUNT(*)
FROM dbo.Users u
GROUP BY
    CAST(YEAR(u.CreationDate) AS NVARCHAR(4))
        + '-'
        + CAST(MONTH(u.CreationDate) AS NVARCHAR(2));

And the plan and stats:

Table 'Worktable'. Scan count 0, logical reads 0
Table 'Workfile'. Scan count 0, logical reads 0
Table 'Users'. Scan count 1, logical reads 675

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

Compare that to the second approach:

SELECT
    YEAR(u.CreationDate), Month(u.CreationDate),
    COUNT(*)
FROM dbo.Users u
GROUP BY YEAR(u.CreationDate), Month(u.CreationDate);

Here are the plan and stats for that one:

Table 'Worktable'. Scan count 0, logical reads 0
Table 'Workfile'. Scan count 0, logical reads 0
Table 'Users'. Scan count 1, logical reads 675

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

As you can see, the string concat / cast approach uses slightly more CPU.  Everything else is the same.

By the way, the dbo.Users table only has about 300,000 rows in it.  Going through this same exercise on the dbo.Posts table, which has about 3,700,000 rows in it, the query goes parallel, and the CPU savings for the second approach become much more significant.

So not only is the 2nd approach better in terms of CPU in the general case, it also appears to scale better as data size increases.


Encountered Error: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression"

Try below. Check your SELECT and GROUP BY statements always with the ORA-00979: not a GROUP BY
Here some other usages of LISTAGG in case you need them.
  SELECT RMSTMP_PNG.ota_activity.EQP_ID,
         RMSTMP_PNG.ota_activity.MODIFIED_DATE,
         RMSTMP_PNG.ota_activity_lotinfo.ID,
         LISTAGG (RMSTMP_PNG.ota_activity_lotinfo.KEY)
            WITHIN GROUP (ORDER BY RMSTMP_PNG.ota_activity_lotinfo.KEY)
            AS "names"
    FROM RMSTMP_PNG.ota_activity
         INNER JOIN RMSTMP_PNG.ota_activity_rescreen
            ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID
         INNER JOIN RMSTMP_PNG.ota_activity_lotinfo
            ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID
   WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE BETWEEN '01-Jan-2020'
                                                   AND '30-Sep-2020'
GROUP BY RMSTMP_PNG.ota_activity_lotinfo.ID,
         RMSTMP_PNG.ota_activity.MODIFIED_DATE,
         RMSTMP_PNG.ota_activity.EQP_ID
ORDER BY RMSTMP_PNG.ota_activity.MODIFIED_DATE ASC

As your intended output try below. I did not know what which id you need and where is the VALUE column comes from. I know you can manage though.
 SELECT RMSTMP_PNG.OTA_ACTIVITY.EQP_ID AS ID,
         LISTAGG (RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.KEY || ':' || VALUE, ',')
            WITHIN GROUP (ORDER BY RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE)
            AS "names"
    FROM RMSTMP_PNG.OTA_ACTIVITY
         INNER JOIN RMSTMP_PNG.OTA_ACTIVITY_RESCREEN
            ON RMSTMP_PNG.OTA_ACTIVITY.ID = RMSTMP_PNG.OTA_ACTIVITY_RESCREEN.ID
         INNER JOIN RMSTMP_PNG.OTA_ACTIVITY_LOTINFO
            ON RMSTMP_PNG.OTA_ACTIVITY.ID = RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.ID
   WHERE RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE BETWEEN '01-Jan-2020'
                                                   AND '30-Sep-2020'
GROUP BY RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.ID
ORDER BY RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE


Complex MySQL query through relational tables

Looks like straightforward addition of INNER JOIN for every condition will do

SELECT id
FROM profile p

INNER JOIN profile_attribute_values pav1 ON p.id = pav1.profile_id
  AND pav1.attribute_id = '58'
INNER JOIN profiles p1 ON p1.id = pav1.parent_profile_id
  AND p1.`name` = 'Douglas-Connelly'

INNER JOIN profile_attribute_values pav2 ON p.id = pav2.profile_id
  AND pav2.attribute_id = '26'
INNER JOIN profiles p2 ON p2.id = pav2.parent_profile_id
  AND p2.`name` = 'Brekke LLC'

INNER JOIN profile_attribute_values pav3 ON p.id = pav3.profile_id
  AND pav3.ne_attribute_id = '35' AND pav3.val = 'ASA'

INNER JOIN profile_attribute_values pav4 ON p.id = pav4.profile_id
  AND pav4.ne_attribute_id = '38' AND pav4.val = 'Stanton'


Can't create a server role using GUI in SQL Server 2014 Express

(This was asked recently and I'm going to close this as a duplicate.)

This is a known bug in Management Studio, and has existed since at least SQL Server 2012. Unlike other missing things in Management Studio (like SQL Server Agent), this has nothing to do with the version or edition of Management Studio you're using, but rather the edition of SQL Server that Object Explorer has connected to. Here's confirmation from one of the lead SSMS devs:

You will need to use T-SQL for this functionality until they fix it (which I think after 2016 SP1 they'll need to do a full audit of any instance-specific filtering or restrictions).


Query for orders that contain a product without using sub-query

I have no idea about efficiency* but a view like this:

CREATE VIEW
    Orders_with_a_product_View AS
SELECT
    p.ProductID, o.*
FROM
    dbo.Product AS p
  JOIN
    dbo.[Order] AS o
      ON EXISTS
         ( SELECT 1
           FROM dbo.OrderDetail AS od
           WHERE od.OrderID = o.OrderID
             AND od.ProductId = p.ProductId
         ) ;

would allow you to use it with:

SELECT *
FROM Orders_with_a_product_View
WHERE ProductID = X ;

(*Regarding efficiency: Hopefully the optimizer will "push-down" the condition and not do a cross join of all products and orders.)

It might be better to follow a simpler road, like this:

CREATE VIEW
    Order_Product_IDs AS
SELECT DISTINCT
    ProductID, OrderID
FROM
    dbo.OrderDetail ;

and the utilize it with:

SELECT o.*
FROM dbo.Order_Product_IDs AS op
  JOIN dbo.[Order] AS o
    ON o.OrderID = op.OrderID
WHERE op.ProductID = X ;


Trigger in trigger or trigger before table creation

There are several problems here.

The syntax of your dynamic create trigger statement is invalid.
The select from dual and definition of NAZWA_TABELI for ora_dict_obj_name is unnecessary.
The outer trigger will fire for every table, though it only needs to for one.
The dynamic SQL could be a constant but is not.
An early return could be used but is not.
The If statement could encompass the sq0 definition, but does not.
The insert will break if a new column nullable column is added to pstepien.
You are creating a trigger to do what normal code should do.
You are creating a trigger inside a trigger.

I recommend you create your table and in the package where you have the statement:

INSERT INTO Account...

Add the other insert right beside it.

INSERT INTO Account...
INSERT INTO Pstepien...


Mutually exclusive many-to-many relationships

There is a way to implement this declaratively only without changing your current setup much, if you agree to introduce some redundancy to it. What follows can be considered a development on RDFozz's suggestion, although the idea fully formed in my mind before I read his answer (and it is different enough to warrant its own answer post anyway).

Implementation

Here is what you do, step by step:

Create a containerTypes table along the lines of the one suggested in RDFozz's answer:

CREATE TABLE containerTypes
(
  id int PRIMARY KEY,
  description varchar(30)
);

Populate it with pre-defined IDs for each type. For the purpose of this answer, let them match RDFozz's example: 1 for plants, 2 for animals, 3 for bacteria.
Add a containerType_id column to containers and make it non-nullable and a foreign key.

ALTER TABLE containers
ADD containerType_id int NOT NULL
  REFERENCES containerTypes (id);

Assuming the id column is already the primary key of containers, create a unique constraint on (id, containerType_id).

ALTER TABLE containers
ADD CONSTRAINT UQ_containers_id_containerTypeId
  UNIQUE (id, containerType_id);

This is where the redundancies begin. If id is declared to be the primary key, we can rest assured it is unique. If it is unique, any combination of id and another column is bound to be unique as well without additional declaration of uniqueness – so, what is the point? The point is that by formally declaring the column pair unique we let them be referable, i.e. to be the target of a foreign key constraint, which is what this part is about.
Add a containerType_id column to each of the junction tables (containers_animals, containers_plants, containers_bacteria). Making it a foreign key is completely optional. What is crucial is to make sure the column has the same value for all rows, different for each table: 1 for containers_plants, 2 for containers_animals, 3 for containers_bacteria, according to the descriptions in containerTypes. In each case you can also make that value the default to simplify your insert statements:

ALTER TABLE containers_plants
ADD containerType_id NOT NULL
  DEFAULT (1)
  CHECK (containerType_id = 1);

ALTER TABLE containers_animals
ADD containerType_id NOT NULL
  DEFAULT (2)
  CHECK (containerType_id = 2);

ALTER TABLE containers_bacteria
ADD containerType_id NOT NULL
  DEFAULT (3)
  CHECK (containerType_id = 3);

In each of the junction tables, make the pair of columns (container_id, containerType_id) a foreign key constraint referencing containers.

ALTER TABLE containers_plants
ADD CONSTRAINT FK_containersPlants_containers
  FOREIGN KEY (container_id, containerType_id)
  REFERENCES containers (id, containerType_id);

ALTER TABLE containers_animals
ADD CONSTRAINT FK_containersAnimals_containers
  FOREIGN KEY (container_id, containerType_id)
  REFERENCES containers (id, containerType_id);

ALTER TABLE containers_bacteria
ADD CONSTRAINT FK_containersBacteria_containers
  FOREIGN KEY (container_id, containerType_id)
  REFERENCES containers (id, containerType_id);

If container_id is already defined to be a reference to containers, feel free to remove that constraint from each table as no longer necessary.

How it works

By adding the container type column and making it participate in the foreign key constraints, you prepare a mechanism preventing the container type from changing. Changing the type in the containers type would be possible only if the foreign keys were defined with the DEFERRABLE clause, which they are not supposed to be in this implementation.

Even if they were deferrable, changing the type would still be impossible because of the check constraint on the other side of the containers—junction table relationship. Each junction table allows only one specific container type. That not only prevents existing references from changing the type but also prevents addition of wrong type references. That is, if you have a container of type 2 (animals), you can only add items to it using the table where type 2 is allowed, which is containers_animals, and would be unable to add rows referencing it to, say, containers_bacteria, which accepts only type 3 containers.

Finally, your own decision to have different tables for plants, animals, and bacteria, and different junction tables for each entity type, already makes it impossible for a container to have items of more than one type.

So, all these factors combined ensure, in a purely declarative way, that all your containers will be homogeneous.


Copy/move data from one cassandra server to another

This is how I did by moving data from one VM to another in azure. Didn't test this in kubernates.

Ex:- Below is process to migration 4 node old cluster to 4 node new cluster.

OLD CLUSTER (node A) => NEW CLUSTER (node A),
OLD CLUSTER (node B) => NEW CLUSTER (node B),
OLD CLUSTER (node C) => NEW CLUSTER (node C),
OLD CLUSTER (node D) => NEW CLUSTER (node D),

On old cluster, enable incremental backups on each node.
On old cluster, take snapshots on each node.
On old cluster, take backup of schema.
On old cluster, take backup of tokens (tokens of each node).
Push snapshots and incrementals to azure blob (az copy).
On new cluster, import schema.
On new cluster, import tokens (like OLD CLUSTER (node A) => NEW CLUSTER (node A)).
On new cluster, import snapshots (like OLD CLUSTER (node A) => NEW CLUSTER (node A)). Copy files direct to table folder.
On new cluster, import incrementals. Copy files direct to table folder.
On new cluster, do rolling restart of nodes.

Cassandra version should be same on old and new cluster. You can automate this using scripts.


First off, to correct the comment: the profiler does not write to the local database, it writes to whatever database you are profiling in the system.profile collection.  So if you were reading from database foo and had profiling turned on, the collection would be foo.system.profile.  You could turn on profiling for the local database, and hence have the profiler write there of course, but it is not a default of any kind.

Then, if you have the profiler enabled, regardless of where it writes too, all writes require a write lock whether they are to the system.profile collection or anywhere else. Just because it is getting input internally does not exclude the system.profile collection from following the same rules as any other collection on a database.

If you turn the profiler off I would expect the lock to disappear.  There may still be the occasional blip in the global lock percentage in the stats but that is essentially just background noise.


Tool for identifying stored procs with deprecated syntax?

Run Upgrade advisor or you can run server side trace to Identify deprecated SQL Server code -By Aaron Bertrand.

It will show you - what will break when you upgrade to new version of sql server and change the compatibility mode to the upgraded version.

Also, when upgrading from SQL Server 2000 to 2012, consider using both the older 2008 R2 Upgrade Advisor and the new SQL 2012 Upgrade Advisor, as this will provide a more complete picture of issues you may need to fix.


Connection string for connecting mongo database with SSL enabled

It depends on which driver you are using.
https://docs.atlas.mongodb.com/driver-connection/#driver-examples


POSTGRES 9.4: Function parameter as empty array

Your p_par3 is defined as par_type which is not the declaration for an array. You need to define it as par_type[] if that should be a parameter that is an array.

The following works for me:

CREATE OR REPLACE FUNCTION fnc_calc(
    p_par1 text,
    p_par2 text DEFAULT NULL::text,
    p_par3 par_type[] DEFAULT '{}'::par_type[]
)
RETURNS integer AS
$$
  select 42;
$$
language sql;

Also: default[] is wrong - your whole statement will not work.


Postgres 11 - *prioritized* where clause in group

Booleans can be ORDERED BY just like other types, with false coming before true when no direction is given.

You could use this in a DISTINCT ON, or in a LATERAL join.

SELECT DISTINCT ON (identity), *
from cafes
ORDER BY identity, is_closed DESC NULLS last
       , is_illegal DESC NULLS last


How to Find Pages/Sec from sys.dm_os_performance_counters

The page you reference talks about monitoring a system running SQL Server, but it does not make any implicit or explicit promise that Memory:Pages/sec will be available specifically in the DMV sys.dm_os_performance_counters. This is more about general system monitoring using performance monitor, 3rd party tools, etc.

The DMV only exposes a specific subset of counters that are SQL Server-specific (according to someone, since it can be subjective). Memory:Pages/sec is, at least currently, not one of them - primarily because it measures for the whole operating system, not for the current instance of SQL Server. You can see what counters are available simply by querying the DMV. If the DMV is empty, Erik's link is a good one.

You can also look directly at the perf-{instance}sqlctr.ini in your instance's Binn folder. You may be tempted to hack the ini file to stuff your desired counters into the DMV, but I haven't tried this, don't recommend it, and am sure you will be on your own in terms of support. Better solution is to use a different method to monitor performance counters (both those you can get from the DMVs and those you can't).


Is it possible to perform clustering when short of free space on disk?

yes postgresql would roll the transaction back.

Almost all commands in Postgresql are run in transaction so if something goes wrong the command will be rolled back.

If the Postgresql system tables and WAL are on the same disk do not run this command if disk space is this limited.  Postgresql would panic and shutdown until space is freed up.

A Few things to keep in mind,

Postgresql will not keep the table in that order so if the table is heavily updated. The physical sort order will be lost very quickly due to MVCC
Have to run the cluster command on a regular bases to keep the table's physical order which recreates the limited disk space problem.
The advantage for clustering is for read only queries that select record set in the order of the cluster.

If disk space is limited and the table is updated/inserted regularly in a short period of time the table and indexes will grow to a point the cluster can not be rerun and vacuum will not be able run

looks like its time to start planning on adding disk space and moving these tables to new TableSpaces


100 TeraBytes Capacity Database - Resources and Time Estimates

First impressions

Depending on your performance requirements, 100TB is a fairly aggressive data volume.  If you want Oracle, you should check out their Exadata systems.  Also, take a look at the offerings from Netezza or Teradata.  With that volume of selects you might want to look at an OLAP based front end or at least fairly aggressive use of materialised views and query rewrite.  You won't get 500 table scans/sec out of anything.For stuff with less stringent latency requirements you might want to consider a larger number of data marts to provide the reporting capacity to your user community.  In this case, SQL Server and SSAS might be an option for the data marts as the licensing on a larger number of servers will be cheaper than trying to do the same with Oracle.
See (1).  Conventional hardware on a shared-disk architecture is likely to be slow on this size data set.
NO! If anybody suggests NFS give them a good kicking.  Either direct attach storage or a multiple controller SAN with lots of mid-range controllers.  Think in terms of maybe a couple of dozen MD3000 series controllers or something similar - if you don't go for a purpose built 'big data' platform.
Get a storage specialist with experience in PB range data warehouse platforms.  You're probably up for a significant ETL development job, and a lot of testing work if you have to meet a stiff SLA.
24x7 on a data warehouse is ambitious at the best of times.  Is this an operational reporting platform?  Perhaps you might elaborate on your requirements a bit.
Sphincter-puckeringly expensive, and dependent on your performance requirements.  Last I saw (a couple of years ago) Netezza used to quote $20,000/TB for TwinFin systems, making your platform $2m for 100TB plus the cost of your redundant server and backup hardware.  Exadata is, I believe, a bit cheaper, but I don't have any pricing to hand.Take a look at Netezza, Exadata and a Teradata platform for comparison, and costings for Ab Initio as an ETL tool.

This is a fairly aggressive set of requirements - 24x7 on a data warehouse isn't normally done and the data volumes are big enough to put you in the realm of a 'big data' platform.  If you have an operational reporting requirement then you should look carefully at what that is.  Keep it separate from your analytics unless you have a specific reason (e.g. a low-latency market data feed) not to.  Mixing operational and analytic requirements on the same platform is bad mojo.

I think you really need to get in specialists to evaluate your requirements.  Without a closer look at what you are trying to achieve all I can give is some empirical suggestions about what to do or not to do.


the answer is there MySQL can not write to the directory you are pointing inside my.cnf, and it dose not have the right permissions. to fix this problem make sure the user MySQL has owner privileges on the directory that my.cnf is pointing for example lets say the base_dir is located under /MySQL , data_dir is located under /MySQL/data and the socket is located under /tmp in this example you need to make sure /MySQL and /MySQL/data are owned by MySQL user, for /tmp since its a default directory for Linux user MySQL should have read,write and execute privileges. also you need to make sure the partition that contain the data_dir is not full "contain enough free space"


Best design for a many:many relationship issue

The solution you propose will work, though it requires you to get the value of belongs_to_type for a record to determine which table to join to. This could get messy.

I've never tried this, but I think something like this might work:

a
-----
  id (PK)
  contect_Ref_id (FK to contact_refs.id)

b
-----
  id (PK)
  contect_Ref_id (FK to contact_refs.id)

c
-----
  id (PK)
  contect_Ref_id (FK to contact_refs.id)

contact_refs
------------
  id (PK)

contact_ref_emails
------------------
  id (PK)
  contact_Ref_id (FK to contact_refs.id)
  email_id (FK to emailAddresses.id)

contact_ref_twitterAccts
------------------------
  id (PK)
  contact_Ref_id (FK to contact_refs.id)
  twitterAcct_id (FK to twitterAccounts.id)

emailAddresses
--------------
  id (PK)

twitterAccounts
---------------
  id (PK)

So now each entity has a reference to a contact_Reference. The contact reference is used in the many-to-many tables so that each entity references a single contact reference, and the contact references can refer to many email accounts or twitter accounts.


How many nodes can you install SSRS on when using basic availability groups?

A SSRS scale-out deployment is an Enterprise Edition ONLY feature.

I don't like providing link-only answers, but this alternative approach outlined by Rodney Landrum, is much too long to copy/paste into an answer, and I suspect his blog entry will be around for quite some time as it's hosted by Red-Gate.

Long story short, you can't approach this via a scale-out deployment, but there is an alternative solution available if you don't mind some additional administration and automation required to make it happen.


How to disable duplicate entry checks in MySQL workbench?

If you're bringing the data into an otherwise empty table, then drop the primary key. You'll be able to import the data as is, clean it up, make sure that the primary key column(s) are now unique, then put the primary key back in place.

I would not recommend bringing the data into a table with other data directly, as you know the data does need to be cleaned up. Import the "dirty" data into a staging table, clean it up, decide how you need to merge it into the existing table (as in, if there is duplication in keys between the data in the target table and the "dirty" data, which data should be kept, and which should be discarded; this may differ on a record-by-record basis).

Note: The links on merging data may work, if you already know which table's data should be kept in all cases; however, it sounds like the data coming in did not have a properly enforced primary key, and thus the incoming data may have multiple records with the same primary key value. I'm not certain if all those options work as desired in that case.


Trying to optimize a MySQL query that has sub selects

Try runnning the LIMIT 50 against the client table earlier
SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
(SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = c.identity AND cll.type = 2) as loginCount,
(SELECT COUNT(identity) FROM ClientLog clv WHERE clv.clientID = c.identity AND clv.type = 10) as viewCount
FROM (SELECT * FROM client LIMIT 50) c;

or gather the client keys and join a little differently
SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
(SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = clientkeys.identity AND cll.type = 2) as loginCount,
(SELECT COUNT(identity) FROM ClientLog clv WHERE clv.clientID = clientkeys.identity AND clv.type = 10) as viewCount
FROM
(SELECT identity FROM client LIMIT 50) clientkeys INNER JOIN client c USING (identity);

You have to control the subselects by giving them as little data as possible. That's why I suggested running LIMIT 50 before joining to the correlated subqueries.
UPDATE 2012-06-15 12:18 EDT
Since this is a full join, remove the LIMIT 50 from your original query:
SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
(SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = c.identity AND cll.type = 2) as loginCount,
(SELECT COUNT(identity) FROM ClientLog clv WHERE clv.clientID = c.identity AND clv.type = 10) as viewCount
FROM (SELECT * FROM client) c;

and make sure ClientLog has a decent index for the correlated subqueries
ALTER TABLE ClientLog ADD INDEX clientID_type_index (clientID,type);

Give it a Try !!!


Migrating SSRS 2008 to 2016 - ReportSync cannot see Data Sources

Thanks to McNets for the heads up on the map DataSources option. To solve the problem I had to:

Load up both servers as per the image in the question
Select Tools > Map DataSources
I was getting a runtime error at this point this was stopped by creating a datasource on the target server and clicking the "Load" buttons again in ReportSync
The resulting window shows all the source server datasources with a dropdown list next to each allowing selection of the target server data sources
Recreated all source server data sources on target server (including all the details - connection strings, authentication etc...
Using the map datasources option, assign each source server data source to the relevant target server data source
Click set
Click sync at the bottom of the main window

The test report I created now opens


How to perform query operation on another query result

SELECT SUM(CASE WHEN (approved=1) AND (deleted_at IS NULL) THEN `count` END) approved,
       SUM(CASE WHEN (approved=0) AND (deleted_at IS NULL) THEN `count` END) not_approved,
       SUM(CASE WHEN deleted_at IS NOT NULL THEN `count` END) deleted
FROM (your subquery) source


I would try that:

drop all indexes
rebuild the clustered index
run dbcc
create index
run dbcc

I would also check the hardware (disk) for inconsistencies.


Speedup MYSQL(8.0.13) Restore from .sql file

innodb_buffer_pool_size should be closer to 1G rather than 8M
also increase innodb_log_file_size to 256M as the default 48M is a bit slow.
A innodb_log_buffer_size of 48M would help rather than a very small 1M.


First of all, the GUC max_connections - a setting in postgresql.conf. It can only be set at server start.
Heroku obviously limits this to 20 for the "Starter Tier" and 500 for the "Production Tier".
The maximum number is not limited by Postgres itself, but by available system resources. Typically, performance degrades with too many concurrent connections, so even if you can set max_connections = 1000, it's probably unwise. Idle sessions don't matter much. But concurrently active sessions compete for resources, the bottleneck typically being I/O.
Here is a blog with instructions by someone who did.


sys.dm_exec_procedure_stats DMV contents contradict BOL

Just working from Paul's comment. The key difference between the statement in Books Online and your interpretation:

  It says: ... one row per cached stored procedure plan.

  You read: ... one row per cached stored procedure.

You can check which plan attributes are different and thus leading to different copies of the plan by looking at the contents of the attributes DMV, ordering by attribute name. You should see at least one attribute where two rows for the same text have different values in the value column.

SELECT t.[text], pa.attribute, pa.value
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa
WHERE LOWER(t.[text]) LIKE N'%create%procedure%procedure_name%'
  AND LOWER(t.[text]) NOT LIKE N'%sys.dm%'
  AND pa.is_cache_key = 1
ORDER BY t.[text], pa.attribute;

Typically, you will see differences in one of these two attributes:

set_options

This is due to runtime differences in settings like ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, etc. I talk about this in the following post:

Multiple Plans for an "Identical" Query

user_id

This isn't really the user running the query, but rather it is due to two different users having different default schemas (the value is actually schema_id), and at least one of them is calling the stored procedure without the schema prefix (SQL Server caches a different plan because the search path is different - it must check the default schema first). I talk about this in the above post as well as the following:

Bad habits to kick : avoiding the schema prefix

(Also, be sure you are looking at the overarching plan for the procedure, not two plans for two completely different statements within the stored procedure, e.g. WHERE p.objtype = N'Proc'.)


How to reproduce "resource busy and acquire with NOWAIT specified or timeout expired " error on local machine in oracle

Not quite clear what you want, this is a generic example of ORA-00054.
Session 1:
SQL> create table t1 (c1 number);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL>

Session 2:
SQL> truncate table t1;
truncate table t1
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL>


importing myisam 5.0 database into a 5.5 innodb server

A mysqldump will attach ENGINE=MyISAM and ENGINE=InnoDB to every CREATE TABLE statement.

You should be fine.

WARNING : There is one gotcha !!!

The mysql database itself (where the grant tables reside) have different column layouts between versions of mysql.

You can dump all databases from MySQL 5.0 EXCEPT the mysql database.

It is much better to port the mysql grants using pt-show-grants.

Here is my personal emulation of what pt-show-grants does

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql


You also need a table with customers. The customers table needs to be CROSS JOINed with the calendar table to get the list of all possible dates per customer.

This SQL Fiddle demonstrates the query that gets the desired result. Please note that your database schema may have a table with customers, and you may not need a customers CTE I have created. Just use your existing customers table to CROSS JOIN it with the calendar table.

Also, as you use SQL Server 2008, it is about time you've learned CTE. It is much easier to use them than the temp tables. However, do not overuse them. Do not use them when performance is very important. Please read this question to better understand when to use CTE vs Temp tables

Another note: when truncating times from the datetime columns do not use CONVERT(VARCHAR(10), c.CalendarDate, 112). It is about the slowest possible method. Read this for more info

MS SQL Server 2008 Schema Setup:

CREATE TABLE Table1
    ([OrderID] int, [Date] DATETIME, [CusID] int)
;

INSERT INTO Table1
    ([OrderID], [Date], [CusID])
VALUES
    (33942, '6-21-2014', 6005),
    (34059, '6-20-2014', 4003),
    (53333, '6-23-2014', 6005),
    (59234, '6-23-2014', 4003)
;

Query 1:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @EndDate = GETDATE()
SET @StartDate = DATEADD(Year, -1, @EndDate)

;with calendar as (
  SELECT CAST(@StartDate AS DATE) CalendarDate
  UNION ALL
  SELECT DATEADD(day, 1, CalendarDate) FROM calendar
  WHERE CalendarDate < @EndDate
  ),
customers as (
  SELECT DISTINCT CusID FROM Table1
  )
SELECT a.CusID, c.CalendarDate, OrderID
FROM customers a CROSS JOIN Calendar c
LEFT JOIN Table1 t
ON CAST(c.CalendarDate AS DATE) = CAST(t.[Date] AS DATE) AND a.CusID = t.CusID
WHERE CAST(c.CalendarDate AS DATE) BETWEEN '6-20-2014' and '6-23-2014'
ORDER BY 1, 2
OPTION (MAXRECURSION 0)

Results:

| CUSID | CALENDARDATE | ORDERID |
|-------|--------------|---------|
|  4003 |   2014-06-20 |   34059 |
|  4003 |   2014-06-21 |  (null) |
|  4003 |   2014-06-22 |  (null) |
|  4003 |   2014-06-23 |   59234 |
|  6005 |   2014-06-20 |  (null) |
|  6005 |   2014-06-21 |   33942 |
|  6005 |   2014-06-22 |  (null) |
|  6005 |   2014-06-23 |   53333 |


Transfer datas from Access to PostgreSQL

Install the PosgresQL ODBC driver and then use that to register Postgres tables in Access. Issuing the SQL you have given in Access will then transfer data from Access to PosgresQL.


Try the following:

SELECT   ART.NEWS_ARTCL_ID, ART.NEWS_ARTCL_TTL_DES,
         ART.NEWS_ARTCL_CNTNT_T, ART.NEWS_ARTCL_PUB_DT,
         ART.NEWS_ARTCL_AUTH_NM, ART.NEWS_ARTCL_URL,
         ART.MEDIA_URL, ART.ARTCL_SRC_ID, SRC.ARTCL_SRC_NM,
         MED.MEDIA_TYPE_DESCRIP, LIK.TOTAL
FROM     RSKLMOBILEB2E.NEWS_ARTICLE ART,
         RSKLMOBILEB2E.MEDIA_TYPE MED,
         RSKLMOBILEB2E.ARTICLE_SOURCE SRC,
         (SELECT   AR1.NEWS_ARTCL_ID, COUNT(*) TOTAL
          FROM     RSKLMOBILEB2E.NEWS_ARTICLE AR1,
                   RSKLMOBILEB2E.NEWS_LIKES LK1
          WHERE    AR1.NEWS_ARTCL_ID      = LK1.NEWS_ARTCL_ID) LIK
WHERE    ART.MEDIA_TYPE_IDENTIF = MED.MEDIA_TYPE_IDENTIF
AND      ART.ARTCL_SRC_ID       = SRC.ARTCL_SRC_ID
AND      ART.ARTCL_SRC_ID       = 1
AND      ART.NEWS_ARTCL_ID      = LIK.NEWS_ARTCL_ID
ORDER BY ART.NEWS_ARTCL_PUB_DT;


I believe you can refer to ola.hallengren. SQL server Backup scripts.
As stated on how to use for SQL server Express:

SQL Server Express has no SQL Server Agent. Therefore, the execution
of the stored procedures must be scheduled by using cmd files and
Windows Scheduled Tasks. Follow these steps.

Download MaintenanceSolution.sql.
Execute MaintenanceSolution.sql. This script creates the stored    procedures that you need.
Create cmd files to execute the stored procedures; for example:    sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType
= 'FULL'" -b -o C:\Log\DatabaseBackup.txt
In Windows Scheduled Tasks, create tasks to call the cmd files.
Schedule the tasks.
Start the tasks and verify that they are completing successfully.

Now you can modify this easily as per you're needs.
Note: In MaintenanceSolution, you can separately only download SQL server Backup scripts individually.


PostgreSQL 11 Windows 10 Truncates All Tables on Shutdown

Solution

  Remove the UNLOGGED option from all table create commands

Result

  The data no longer disappears during system shutdown or reboot

I had been using CREATE UNLOGGED for a long time, as I found that in earlier versions of PostgreSQL, it made a HUGE impact in the execution time of my SQL scripts. In one case, it dropped from 15 minutes to around 45 seconds. Every once in a great while, like once or twice a year, I would find the tables had emptied out. I would just go ahead and reload the datasets, never really thinking more about it.

But when I migrated my datasets to PostgreSQL 11.x, I found that the tables emptied out every time I shut down the laptop. Thanks to the comments above, I learned that the UNLOGGED option was indeed the culprit here. I removed that option from all the table creation commands, and the data no longer disappears during reboot.

Added Benefit

  I did not find any appreciable decline in performance while doing this i PostgreSQL 11.x!


There is no direct way to output the the CONTEXT line with a user-defined exception. This option is not implemented (yet) in PostgreSQL 9.1.
However, I found a ...
Workaround
... that should perform flawlessly. Call another function that raises the error (warning, notice, ..) for you. Optionally with custom message and CONTEXT. Works with PostgreSQL 9.0 or later:
CREATE OR REPLACE FUNCTION f_raise(_lvl text = 'EXCEPTION'
                                 , _msg text = 'Default error msg.')
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   CASE upper(_lvl)
      WHEN 'EXCEPTION' THEN RAISE EXCEPTION '%', _msg;
      WHEN 'WARNING'   THEN RAISE WARNING   '%', _msg;
      WHEN 'NOTICE'    THEN RAISE NOTICE    '%', _msg;
      WHEN 'DEBUG'     THEN RAISE DEBUG     '%', _msg;
      WHEN 'LOG'       THEN RAISE LOG       '%', _msg;
      WHEN 'INFO'      THEN RAISE INFO      '%', _msg;
      ELSE RAISE EXCEPTION 'f_raise(): unexpected raise-level: "%"', _lvl;
   END CASE;
END
$func$;

COMMENT ON FUNCTION f_raise(text, text) IS 'Raise error or given level with msg and context.
Call from inside another function instead of raising an error directly
  to get plpgsql to add CONTEXT (with line number) to error message.
$1 .. error level: EXCEPTION | WARNING | NOTICE | DEBUG | LOG | INFO
$2 .. error message';

Use the function to raise an error like this:
CREATE OR REPLACE FUNCTION test_err(text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- do stuff

   IF TRUE THEN  -- some condition here?
      -- instead of raising error like this:
      -- RAISE EXCEPTION 'unexpected parameter: "%"', $1;
      PERFORM f_raise('EXCEPTION', 'My message "' || $1 || '"');
   END IF;
END
$func$;

Call:
SELECT test_err('wrong parameter');

The function comes with default values, that kick in when missing in the call. In combination with named parameters, you can do pretty much anything. Examples:
SELECT f_raise();
SELECT f_raise('WARNING');
SELECT f_raise(_msg := 'boohoo');
SELECT f_raise(_lvl := 'WARNING');


How to install UTF8 locale on PostgreSQL?

PostgreSQL 8.4 supports only one locales that is selected in installation time. You need to rerun initdb statement with different locales (but only one can be used) or you can migrate to 9.x version, that supports more locales.


What's a good way to model user authorization to hierarchical data?

A long time ago, in 2000, I had a similar question, and posted on Google Groups. I received an awesome response from a guy called Joe Celko, and it will fit right into your problem. I've been using this methodology ever since, and it has great performance, and works really well

https://groups.google.com/forum/#!search/pascaljr/microsoft.public.sqlserver.programming/knQ5WXIDfeo/E0vfeC2kbTsJ

It uses two integer values, a left and a right value, to store the hierarchy. I've made some adaption to the table and procs over the years, but the essence remains the same.


How to change the SQL Server Backup file (.bak) file default icon?

On Windows 10

Right click on the .bak file
Choose properties
On the General tab, click the Change... button next to Opens with:

Under Other Options choose More apps

Scroll to the bottom and click Look for another app on this PC

Go the location of Ssms.exe.  Mine was located here:
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio
Choose Ssms.exe
Click the Open button and then the Save button

It should now be associated with SQL Server Management Studio and have the icon you'd like


Precautions for moving a SQL cluster from an old to a new datastore

I would:

Run DBCC CHECKDB on all databases prior to the move to ensure no consistency errors.
Take full SQL Server Backups of all user and system databases. Use native backup or your preferred third party tool.
When taking the backup, ensure it is verified.
Run DBCC CHECKDB on all databases post the move to ensure no consistency errors.


Shrinking data file to save disk space before migration

you can read about this here - https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/
and about when shrinking is a must what can you do here - https://www.brentozar.com/archive/2020/07/what-if-you-really-do-need-to-shrink-a-database/
When we shrink our database, it introduces both external and internal fragmentation, it causes blocking, it causes transaction log growth while it runs. Index fragmentation goes high. When we rebuild the index, the database size goes back up. Hence causing the endless cycle of shrink-rebuild.
Assuming you have the downtime in place, and if I must do a shrink to move the DB, I would script out all indexes and drop them. run checkpoint multiple times, take log backup,  Shrink log file then shrink the database, take full backup and restore this backup to new location. run the index script to create them again and let it grow in new server. Caution: creating index on a 30TB is going to be a pain.
There could be a better way of doing this, I would be happy to keep an eye on other answers.


1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

I think this problem due to strict mode enabled in your MySQL version. Kindly disable strict mode and try again.
To disable follow How to turn on/off MySQL strict mode in localhost (xampp)?
STRICT_TRANS_TABLES is responsible for setting MySQL strict mode.
To check whether strict mode is enabled or not run the below sql:
SHOW VARIABLES LIKE 'sql_mode';

If one of the value is STRICT_TRANS_TABLES, then strict mode is enabled, else not. In my case it gave
+--------------+------------------------------------------+
|Variable_name |Value                                     |
+--------------+------------------------------------------+
|sql_mode      |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION|
+--------------+------------------------------------------+

Hence strict mode is enabled in my case as one of the value is STRICT_TRANS_TABLES.
To disable strict mode run the below sql:
set global sql_mode='';

[or any mode except STRICT_TRANS_TABLES. Ex: set global sql_mode='NO_ENGINE_SUBSTITUTION';]
To again enable strict mode run the below sql:
set global sql_mode='STRICT_TRANS_TABLES';

If you don't what to disable strict_mode then you have to change your sql query to follow sql standard.
like this..
SELECT
   aggregated_column,
   aggregated_column,
   aggregation_function(column),
   aggregation_function(column),
   aggregation_function(column)
FROM
  TABLE
WHERE [CLAUSE]
GROUP BY [GROUP BY FIELD]
WHILE [CLAUSE]
ORDER BY

for your query..
SELECT
  contact_id,
  min(mail_sent),
  min(datetime),
  max(datetime),
FROM `orders`
WHERE `mail_sent`='No'
  AND `datetime` < DATE_SUB(NOW(), INTERVAL 15 MINUTE)
GROUP BY `contact_id`
ORDER BY `datetime` ASC;

Let me know if that helps.


Can sub-selects change in one single query in a read committed transaction?

No, that's not possible.

A single query (and even a query with many sub-queries is considered a single query) sees a consistent state of the database as it was when that query started.

It's not even necessary to use explicit transaction control as you did.


Connecting to database engine again when a new query or running view

It's like SSMS is trying to connect to the server again every time I run something or even when clicking new query.

That's because it is. That's how it works, each connection is a new connection. The initial connection dialog you see if for object explorer. Each open "new query" tab is its own connection.

  How do I stop this prompt appearing?

It's appearing because there is either very high network latency or the pre-login/login times are long. Almost always it's due to very bad latency/undersized vms. Check your network round trip time to the server and check that the server is under heavy load or undersized.

Adding in Aaron's comments which are also on point:

  Could also be a not-very-efficient logon trigger or an expensive
  resource governor classifier function on that server (connecting via a
  local SSMS on the VM might rule this out). The error log might yield
  some clues too, or an extended events session that captures
  connection/log in information.


I very much doubt it will ever do that.  PostgreSQL doesn't yet implement index skip scans, and if it did they would only be effective if there were only a handful of different actors.

It is possible, but not very likely, to use the index for something other than an INLJ.  If the role field is very wide (causing the cast table to be much larger than the index on it), and role is not in your select list, and the table is kept well vacuum, then it might decide to index-only-scan the index rather than seq scan the cast table as part of a merge join or hash join.

(By the way, cast is not a valid table name as it is a reserved word)


How to terminate BCP output with specific string AND newline?

Use the ASCII codes of the characters you want to match (without the 0x prefix).

For example to match A (char hex 41) + LineFeed (char hex 0A) do this:

-r "410A"

This seems to be undocumented. See my blog here for more info: http://kejser.org/databases/bulk-insert-with-linux-line-endings/


How to execute sqlplus by root using sudo in linux?

Not an Oracle error.

Sure you can put the above variables in .bash_profile of oracle, but when you use sudo like above, that will not be executed.

[root@o71 ~]# tail -3 /home/oracle/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/dbhome_1
export ORACLE_SID=ETALON
export PATH=$ORACLE_HOME/bin:$PATH
[root@o71 ~]# sudo -u oracle sqlplus / as sysdba
sudo: sqlplus: command not found

One of the many ways to work around this:

[root@o71 ~]# sudo -u oracle bash -c '. ~/.bash_profile; sqlplus / as sysdba'

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Oct 28 10:52:43 2018
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL>

I am not a fan of using .bash_profile for setting the Oracle environment, I suggest using oraenv or a custom script that is not executed automatically.


Two nullable columns one required to have value

A version of JD Schmidt's answer, but without the awkwardness of an extra column:

CREATE TABLE foo (
  FieldA INT,
  FieldB INT
);

DELIMITER //
CREATE TRIGGER InsertFieldABNotNull BEFORE INSERT ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
CREATE TRIGGER UpdateFieldABNotNull BEFORE UPDATE ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
DELIMITER ;

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL); -- gives error
UPDATE foo SET FieldA = NULL; -- gives error


MySQL replica is lagging without resource saturation

Single-threading is very likely to be the cause of getting "behind".

A Master can execute multiple transactions at the same time.  But a single-threaded Slave is limited to one transaction at a time.  This is the old, simple, way of doing Replication.

Newer replication techniques are more complex.  Depending on what version of MySQL you are running, multi-threading may be limited to "one thread per database", thereby not involving much extra complexity.

(I, too, need at least A and B, plus RAM size.)

Find some of the slowest DML queries; there may be ways to speed them up.  Also, do you have a lot of Reads on the Replica?  If so, it could be adding to the sluggishness.

Analysis

Observations:

Version: 5.6.41-log
30 GB of RAM
Uptime = 6d 07:36:37
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Increase innodb_io_capacity, currently 200, to 1000.  (Check AWS documentation on this topic.)

Assuming you have SSDs, turn off innodb_flush_neighbors.

Is some other process purging binlogs?  (cf expire_logs_days = 0)

Ignoring errors seems dangerous.  (cf binlog_error_action)

The traffic heavily leaning toward INSERTs; is this normal?  What do the INSERTs look like?  Single-row inserts?  Multi-row?  Other?  We may be able to tweak them to avoid some of the slave lag.

Related to that, please discuss the apparent 275 transactions per second.

Since the main thrust of the Question is about slave lag, let's also approach this from the viewpoint of "slow queries".  Set long_query_time=1 and turn on the Slow Log.  (AWS may have a web page laying out details.)  Do this for both the Master and the slave.  I prefer these ancillary settings, especially for the "slave lag" situation:

log_queries_not_using_indexes = OFF
log_slow_admin_statements = ON
log_slow_slave_statements = ON

Details and other observations:

( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((3078624 + 150107660) ) / 545797 = 280 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size (now 22548578304)?

( Innodb_buffer_pool_pages_flushed ) = 150,107,660 / 545797 = 275 /sec -- Writes (flushes)
-- Increase innodb_buffer_pool_size (now 22548578304)?

( Innodb_os_log_written ) = 318,630,569,472 / 545797 = 583789 /sec -- This is an indicator of how busy InnoDB is.
-- Very busy InnoDB.

( Innodb_log_writes ) = 150,500,027 / 545797 = 275 /sec

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 318,630,569,472 / (545797 / 3600) / 2 / 128M = 7.83 -- Ratio
-- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 545,797 / 60 * 128M / 318630569472 = 3.83 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 134217728). (Cannot change in AWS.)

( Innodb_dblwr_writes ) = 6,573,796 / 545797 = 12 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.

( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 4568524 + 150107660 ) / 545797 / 200 = 141.7% -- If > 100%, need more io_capacity.
-- Increase innodb_io_capacity (now 200) if the drives can handle it.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( max_connections ) = 2,496 -- Maximum number of connections (threads). Impacts various allocations.
-- If max_connections (now 2496) is too high and various memory settings are high, you could run out of RAM.

( character_set_server ) = character_set_server = latin1
-- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.  Caution:  there can be index definition problems with utf8mb4 (but utf8 does not have these problems).

( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue

( (Queries-Questions)/Queries ) = (303268906-1169727)/303268906 = 99.6% -- Fraction of queries that are inside Stored Routines.
-- (Not bad if high; but it impacts the validity of some other conclusions.)

( Created_tmp_disk_tables ) = 570,444 / 545797 = 1 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size (now 16777216) and max_heap_table_size (now 16777216).
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / Questions ) = 570,444 / 1169727 = 48.8% -- Pct of queries that needed on-disk tmp table.
-- Better indexes / No blobs / etc.

( Created_tmp_disk_tables / Created_tmp_tables ) = 570,444 / 729780 = 78.2% -- Percent of temp tables that spilled to disk
-- Maybe increase tmp_table_size (now 16777216) and max_heap_table_size (now 16777216); improve indexes; avoid blobs, etc.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (150283122 + 50516 + 10949 + 0) / 150328440 = 1 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan ) = 778,039 / 545797 = 1.4 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 778,039 / 1129934 = 68.9% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (150283122 + 10949 + 0 + 0 + 50516 + 0) / 545797 = 275 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( binlog_error_action ) = binlog_error_action = IGNORE_ERROR -- What to do if the binlog cannot be written.
-- IGNORE_ERROR is the default for backwards compatibility, but ABORT_SERVER is recommended.

( expire_logs_days ) = 0 -- 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.
(Not relevant if log_bin (now ON) = OFF)

( innodb_autoinc_lock_mode ) = 1 -- Galera: desires 2 -- 2 = "interleaved"; 1 = "consecutive" is typical; 0 = "traditional".
-- Galera desires 2; 2 requires BINLOG_FORMAT=ROW or MIXED

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

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

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show.
-- It can be helpful in the slowlog to see writes that could be interfering with Slave reads.

( Aborted_connects / Connections ) = 2,404 / 4995 = 48.1% -- Perhaps a hacker is trying to break in? (Attempts to connect)

( thread_cache_size / Max_used_connections ) = 32 / 17 = 188.2%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

Abnormally small:

(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.00752

Abnormally large:

Binlog_cache_use = 275 /sec
Com_begin = 275 /sec
Com_commit = 275 /sec
Com_commit + Com_rollback = 275 /sec
Com_flush = 12 /HR
Com_insert = 275 /sec
Com_kill = 3 /HR
Com_purge = 12 /HR
Com_show_grants = 0.66 /HR
Com_show_slave_status = 60 /HR
Com_slave_start = 0.0066 /HR
Connection_errors_peer_address = 0.0066 /HR
Handler_commit = 1377 /sec
Handler_commit/Questions = 642
Handler_delete = 330 /sec
Handler_prepare = 1101 /sec
Innodb_buffer_pool_pages_misc = 135,294
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 9.8%
Innodb_buffer_pool_write_requests = 9230 /sec
Innodb_data_fsyncs = 300 /sec
Innodb_data_writes = 563 /sec
Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 275 /sec
Innodb_data_written = 9595901 /sec
Innodb_dblwr_pages_written = 275 /sec
Innodb_os_log_fsyncs = 276 /sec
Innodb_os_log_pending_fsyncs = 1
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 1,002.1MB
Innodb_pages_written = 275 /sec
Innodb_rows_deleted = 330 /sec
Innodb_rows_deleted + Innodb_rows_inserted = 669 /sec
Prepared_stmt_count = 4
Ssl_accepts = 1,019
Ssl_finished_accepts = 1,019
Ssl_session_cache_overflows = 340
Ssl_used_session_cache_entries = 128
performance_schema_max_cond_instances = 12,884
performance_schema_max_socket_instances = 5,012
performance_schema_max_thread_instances = 5,092

Abnormal strings:

Slave_running = ON
innodb_fast_shutdown = 1
log_output = TABLE
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
read_only = ON
relay_log_recovery = ON
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
time_zone = UTC


Your bind-address needs to be 0.0.0.0 or at the very least the IP address of the interface you will be connecting to from your remote machine.

If you set it to 127.0.0.1 only loopback connections from the server can be made.


The big problem here is the join between Helper1 and Sanity that causes multiple duplicates, so with larger result sets this is not going to perform well. It is possible to rewrite the update statement so that it does what you need using a CASE expression, rather than a switch.

UPDATE s
SET itemname =
        CASE s.itemname WHEN 'purple flower' THEN 'lilac'
                        WHEN 'red flower' THEN 'rose'
                        WHEN 'yellow flower' THEN 'daisy'
             ELSE s.itemname
        END
FROM dbo.Sanity s
    JOIN dbo.Helper1 h ON h.cID = s.cID
    JOIN dbo.SSL12 ss ON ss.LBS = h.LBS ;

Which leaves the data in the Sanity table as:

cID     itemname
----    --------
2222    lilac
2222    foghorn
2222    socks
4212    iguana
4212    camel
8132    red curtains


TSQL 2012 deleted table in INSTEAD OF and AFTER triggers?

Very easy to test.

CREATE TABLE T(X INT);

GO

INSERT INTO T
SELECT TOP 10 1
FROM sys.all_objects;

GO

CREATE TRIGGER TR1 ON T INSTEAD OF DELETE AS DELETE TOP (8) FROM T;

GO

CREATE TRIGGER TR2 ON T AFTER DELETE AS SELECT COUNT(*) FROM DELETED;

GO

DELETE FROM T;

DROP TABLE T;

  Answer 8

This is the correct behaviour. The after trigger should reflect the rows that were actually deleted. If you had an audit trigger recording 10 deletions when only 8 occurred this would be clearly incorrect.


How do I copy SQL Server Data Discovery and Classification rules and suppressed recommendations to another database?

I found the Data Discovery and Classification recommendation suppressions in sys.extended_properties with the property name sys_data_classification_recommendation_disabled.
Copying these extended properties to another database successfully suppresses the recommendations.
I wrote the following SQL to be run in a database in which classification rules and suppressions have already been set. It code-generates the SQL commands needed to migrate suppression rules from one database to another. The script it generates is idempotent, checking for the existence of the properties before setting them.
Do be mindful that you might have permission to read the properties, but not write them.
;with Suppress (SchemaName, TableName, ColumnName)
AS
(
select QUOTENAME(schema_name(t.schema_id)) as SchemaName, QUOTENAME(t.Name) as TableName, QUOTENAME(c.Name) as ColumnName
from sys.extended_properties p
join sys.tables t on p.major_id = t.object_id
join sys.all_columns c on c.object_id = t.object_id and p.minor_id = c.column_id
where p.name = 'sys_data_classification_recommendation_disabled'
)
select Script =
N'IF NOT EXISTS (
    SELECT NULL
    FROM SYS.EXTENDED_PROPERTIES
    WHERE [major_id] = OBJECT_ID(''' + s.SchemaName + N'.' + s.TableName + ''')
        AND [name] = N''sys_data_classification_recommendation_disabled''
        AND [minor_id] = (
            SELECT [column_id]
            FROM SYS.COLUMNS
            WHERE [name] = ''' + s.ColumnName + '''
                AND [object_id] = OBJECT_ID(''' + s.SchemaName + N'.' + s.Tablename + ''')
            )
    )
EXEC sys.sp_addextendedproperty @name=N''sys_data_classification_recommendation_disabled'', @value=N''1'' , @level0type=N''SCHEMA'',@level0name=N''' + s.SchemaName +
''', @level1type=N''TABLE'',@level1name=N''' + s.TableName + ''', @level2type=N''COLUMN'',@level2name=N''' + s.ColumnName + ''';'
from Suppress s

This next script code-generates the SQL commands needed to migrate the actual classification rules (not recommendation suppressions) from one database to another.
The ADD SENSITIVITY CLASSIFICATION command is blissfully idempotent by nature. You do not have to check for existence before running it a second time.
select 'ADD SENSITIVITY CLASSIFICATION TO ' + s.schema_name + '.' + s.table_name + '.' + s.column_name + ' WITH (LABEL=''' + s.label + ''', INFORMATION_TYPE=''' + s.information_type + ''', RANK=' + s.rank_desc + ');'
from
(
SELECT
    cast(schema_name(O.schema_id) as nvarchar(200)) AS schema_name,
    cast(O.NAME as nvarchar(200)) AS table_name,
    cast(C.NAME as nvarchar(200)) AS column_name,
    cast(information_type as nvarchar(200)) as information_type,
    cast(label as nvarchar(200)) as label,
    rank,
    cast(rank_desc as nvarchar(200)) as rank_desc
FROM sys.sensitivity_classifications sc
    JOIN sys.objects O
    ON  sc.major_id = O.object_id
    JOIN sys.columns C
    ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id
) as s
order by s.schema_name, s.table_name, s.column_name


Not sure why code is failing when CONCAT_NULL_YIELDS_NULL is set to OFF

Try changing your procedure to:

ALTER PROCEDURE dbo.ProcedureName
  @Params ...
AS
BEGIN
  SET NOCOUNT ON;
  SET CONCAT_NULL_YIELDS_NULL ON;

  ... the rest of your code ...
END
GO

You might also want to get in touch with the vendor of the software, as changing this setting to OFF has been deprecated since SQL Server 2005, and soon the setting will have no effect or cause an error:

http://technet.microsoft.com/en-us/library/ms143729(v=sql.90).aspx


Backfill new column with values based on foreign key

You can use a correlated subquery to update all rows at once.
To determine the ordinal value, just count how many other rows for the same user come before this one when you sort them:

UPDATE devices
SET seat_ordinal = (SELECT COUNT(*)
                    FROM devices AS d2
                    WHERE d2.user_id  = devices.user_id
                      AND d2.id      <= devices.id);    -- some arbitrary sort order


nvarchar(max) in Included Columns of NC index

Yes, rebuilding that index will probably be a lot slower.

Baseline it will be an entire extra copy of this data on your disks.  If the column is updated frequently, then it could cause the index to fragment more quickly.  And the more data is in the index, the longer it will take to drop and recreate ("rebuild").

It's generally not recommended to add large data types as included columns in a nonclustered index.  It causes fewer rows to fit on a page (or pointers to off row storage), which causes more pages to have to be read whenever you query this index.  And as you can imagine it will require more RAM to keep these index pages in memory.

You would want to do performance testing to make sure that the benefits of this included column (presumably to avoid a key lookup?) outweigh the costs mentioned above.

Of course, the level of impact will depend greatly on how much data is actually stored in this column in practice.

You're right that included columns are treated differently, in that they don't affect the order that index data is stored.  But they are still included at the leaf level of the index, so they will definitely cause your rebuilds to take longer.  Included columns will generally cause less overhead than key columns when it comes to updates, since they won't cause the rows to be shuffled around if the order changes.  But rebuilds are really just dropping and recreating the whole index.


how to create a data type and make it available in all Databases?

There is no way to do that with table types. You will need to replicate that in all databases. But, if you want this table type available while creating a new database, you can just add it in the model database.
There are a couple restrictions to using table-valued parameters. You can't even use table-valued parameters across databases. Check some details here.


You do not have privileges to manipulate with the users!

According to comments in this Github issue report for phpMyAdmin, you probably need to set this option:

$cfg['Servers'][$i]['DisableIS'] = true;

in config.inc.php. Though it sounds like this is a workaround for a bug, so may be fixed in later releases or not an issue in earlier releases.


how to downgrade an unknown (2005) interface name from SSIS 12 to 05

In SQL Server 2005, there is an IDTSConnectionManager90.  It comes with the note "this interface supports the SQL Server 2005 infrastructure and is not intended to be used directly from your code."  A very similar note is provided for IDTSConnectionManager100.

These two are not 100 percent identical, of course.

However, these are SQL Server version specific components and are intended to be used through the ConnectionManager class.  For 2005 see:

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.connectionmanager(v=sql.90).aspx

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.connectionmanager_members(v=sql.90).aspx


Moving away from JSONB

JSONB is not the right tool for every job. I'm going to fix your schema because everyone here is about to cry.

Here we use an ENUM type and timerange

CREATE TYPE dow AS ENUM (
  'Monday', 'Tuesday', 'Wednesday', 'Thursday',
  'Friday', 'Saturday', 'Sunday'
);

CREATE FUNCTION time_subtype_diff(x time, y time)
RETURNS float8 AS $$
  SELECT EXTRACT(EPOCH FROM (x - y))
$$ LANGUAGE sql
STRICT IMMUTABLE;

CREATE TYPE timerange AS RANGE (
    subtype = time,
    subtype_diff = time_subtype_diff
);

CREATE TABLE available (
  user_id  int,  -- REFERENCES users,
  dow      dow,
  trange  timerange,
  PRIMARY KEY (user_id, dow)
);

now you can be sane (and we support minutes!)

CREATE TABLE users(user_id) AS VALUES (1::int);

INSERT INTO available VALUES
  ( 1, 'Monday', '[13:42,16:12]' );

SELECT *
FROM users AS u
JOIN available AS a USING (user_id)
WHERE dow = 'Monday' AND a.trange @> '[14:00,15:00]';


Both errors indicate that you need more archive log to complete media recovery and bring your data files to a consistent state.

If the datafiles are newer than the redo data you have available, the the alternative is to grab an older backup of the datafiles and work from there with the redo data you do have from the latest backup run. A common strategy is to continuously send archive logs to a backup destination between backups, and in that way minimizing the loss of data leading up to the next scheduled backup.

If you would rather aim to get an independently consistent backupset, you need to capture all the datafiles as well as all of the redo generated while backup was running. I would suggest something like this sequence of backup operations:

run{
backup database ;
sql 'alter system switch logfile';
sql 'alter system archive log current';
backup archivelog all ;
}

Note how you would first complete the database backup, then force a switch to the next log group before triggering an archive process and finally start backing up the archived logs. Those logs now have a much better chance of containing the redo data you need to run media recovery on files restored from this backup set.

UPDATE:

In your current situation, if this really is all the data you have to work with, I think you should still be able to bypass the ORA-1547 by pretending to do a manual incomplete media recovery and then open with resetlogs.

STARTUP FORCE MOUNT
RECOVER DATABASE UNTIL CANCEL;
CANCEL;
ALTER DATABASE OPEN RESETLOGS;


How to make SQL Server Agent job history less verbose?

Perhaps you need to uncheck this:

The job step properties screen allows you to prevent the output from the job being recorded in the job history.


Avoid using complicated strings in Joins in SQL Server?

You don't need to use the ProductKey column as the key column in DimProduct. You can create a surrogate key that is a composite key made up of DimProductID that is an IDENTITY column and increments per each row of the SCD, and a ProductID column that is unique per each ProductKey. Both would be INT (most likely, unless you will never have very many unique products, in which case I might opt for SMALLINT for ProductID which gives you 65k values to use).

This approach allows you to have both the ability to reference any particular version of a product (using the IDENTITY column, or both as both will probably work with indexes better), and the ability to aggregate over all versions of a particular product using just the non-IDENTITY column.

In either case, you generally don't need to be JOINing on the ProductKey column / value.

When you do need to filter or JOIN on ProductKey, assuming that there is no functional difference between upper-case and lower-case letters, you should force everything to be upper-case and use a binary Collation (any Collation ending in _BIN2, not _BIN, though most likely Latin1_General_100_BIN2). Most people do not consider the Collation being used for string columns that contain alpha-numeric codes where there is no purpose, in any way, for having case (or Kana or width or accent insensitivity) and no purpose in evaluating complex linguistic rules, such as which character sequences equate to other characters in other languages. Non-binary Collations have A LOT of complex rules that they apply, and those take time. Using a binary Collation will be much faster as it just compares the bytes, which is why you need to force upper-case just in case someone enters in an alpha character in lower-case.

So for this, make sure to set the column's Collation to use Latin1_General_100_BIN2 when creating the table. If the table has already been created, use ALTER TABLE ... ALTER COLUMN to set the Collation so that it will be used by default.


Is there appreciable overhead from having a large proportion of empty table partitions in SQL Server?


Counting Number of Database Connections on a Page

One would hope that the answer is "1".

I don't think there is a way to get that answer easily...

Turn on the "general log".  Make sure that that page is loaded (and moved away from) at least once.  Turn off the general log.

Now look at the log.  Part of the info is a "connection number".  Try to decipher which queries apply to your page load and which came from others.  You may need help from the Apache log to see which pages were loaded, when.  If the general log is too 'busy', try again again.

You may be surprised (even appalled) at list of queries that show up.  It might be more than just "number of connections" that is 'inefficient'.  Start a new Question with the list; let's poke at it to see what could be improved.  (And tag it with [wordpress], too.)


Balancing the size of oracle datafiles for a tablespace

If a tablespace has multiple datafiles, Oracle manages what goes into what datafile. You have no control over this. If you have enough diskspace to do so, I suggest the following data-juggling-opperation:

Create a new tablespace with a datafile big enough to hold all the data of your source tablespace.
Move all the tables, indexes and other objects to this new tablespace.
Resize the datafiles of your old tablespace how you would like them.
Move all tables, indexes and other objects back to the resized tablespace.
Rebuild all your indexes
Drop the temporary tablespace you created.

Sidenote. If you had used MAXSIZE on your datafiles you would probably not have been in this situation right now... I tend to set MAXSIZE to a conservative size, since I rather run out of datafile size, than out of diskspace...

Some tools to get you going

Create DML statements to move your tables around

select distinct 'ALTER TABLE ' || owner || '.' || segment_name || ' MOVE TABLESPACE target_tablespace;'
from dba_segments
where tablespace_name = 'old_tablespace'
and segment_type = 'TABLE';

Create DML statements to move your indexes around

select distinct 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD TABLESPACE target_tablespace;'
from dba_indexes
where tablespace_name = 'old_tablespace'
and index_type != 'LOB';

Resizing datafiles

ALTER DATABASE DATAFILE 'D:\ORADATA\file.DBF' RESIZE xxxMB MAXSIZE xxxMB;

Rebuilding indexes

ALTER INDEX <SCHEMA>.<INDEX> REBUILD;


Merge replication SQL Server 2008 publisher with SQL Server 2012 subscriber

No.  With Merge, a Subscriber can be any version no later than the Publisher version.  This is covered in Using Multiple Versions of SQL Server in a Replication Topology.


First off, your PRIMARY KEY spanning two varchar(2000) columns seems extremely expensive. If you use your PK for anything else I suggest a surrogate PK (use a serial column) and add a UNIQUE constraint to enforce uniqueness on (cid, name, synonym).

If one of your varchar columns actually uses the maximum length you would exceed the maximum size for an index entry. See:

Character varying index overhead & length limit

I guess what you want is this, because it would make sense:

SELECT DISTINCT ON (c.name)
       c.name, min(s.synonym) AS min_synonym, s.cid
     , ts_rank(s.tsv_syns, c.lexemes, 16) AS rnk
     , count(*) AS ct
FROM   synonyms_all_gin_tsvcolumn s
JOIN   cmap5                      c ON c.lexemes @@ s.tsv_syns
GROUP  BY c.name, rnk, s.cid
ORDER  BY c.name, rnk DESC, ct DESC;

I use explicit [INNER] JOIN with attached join condition replacing your CROSS JOIN plus WHERE clause. It's generally considered superior (easier to read and debug). I also use rnk as column name to avoid the basic function name rank as identifier.
Group the results per c.name that have the same rnk and s.cid, take min(s.synonym) (for lack of definition in the question), and count(*) the peers per group.
Reduce to one row per c.name with DISTINCT ON (Postgres specific extension of SQL standard DISTINCT), taking the highest rank first and, within same rank, the highest peer count. See:
Select first row in each GROUP BY group?

Combining GROUP BY and DISTINCT ON this way in one query level is possible since DISTINCT or DISTINCT ON are applied after GROUP BY.


In normal situation, mongodb uses always all available memory!!

However, you can limit mongo's memory using cgroup.

Here is presentation what may help too.


Getting sql statement to refer to more than 2 tables?

I appears that some of your syntax is not entirely correct in the question, however it may just be a typo.  It appears also that you are attempting to use the old method of joining tables.

I noted also that you are having to return records from StaffName where there are no matches (nulls?) with the other tables.  To still join, and to show only blanks where there are no matches, you will need to use an outer join.  For simplicity, I have used only LEFT joins in the example below.

Also, note that I have returned the appropriate fields in your select list, referencing the joined tables, so as to get the result as shown in your requirement

select s.name, ig.Income_Range, el.Educational_Level, c.Country
from StaffName s
    left join Country c on s.Income_Group = c.ID
    left join IncomeGroup ig on s.Educational_Level = ig.id
    left join EducationalLevel el on s.Country = el.id

To better understand left joins, here is an excerpt from BOL.

  Using Left Outer Joins

  Consider a join of the Product table and the ProductReview table on their ProductID columns. The results show only the products for which reviews have been written. To include all products, regardless of whether a review has been written for one, use an ISO left outer join. The following is the query:

USE AdventureWorks2008R2;
GO
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
    LEFT OUTER JOIN Production.ProductReview pr ON p.ProductID = pr.ProductID

  The LEFT OUTER JOIN includes all rows in the Product table in the results, whether or not there is a match on the ProductID column in the ProductReview table. Notice that in the results where there is no matching product review ID for a product, the row contains a null value in the ProductReviewID column.

I suspect your statement regarding Primary Keys and Foreign Keys are purely to say that we needn't worry about the effect it may have on your joins.  You may have your reasons for it, but please remember that having proper Primary Keys are fundamentally important to any database.


Delete more than 50K records in SQL table

Create a temp table, put in your users and use that in your delete statement:

CREATE TABLE delete_users (user_id ... primary key);
INSERT INTO delete_users (user_id) values (...),(...)...;
DELETE FROM  [dbo].[Table] x
WHERE EXISTS (
    SELECT 1 FROM delete_users y WHERE x.user_id = y.user_id
);
DROP TABLE delete_users;


Geographic Hierarchies in a OLAP Cube / Data Warehouse

To answer at least some of this:

Area codes do cross state boundaries in rare cases. There is a tiny bit of the Oregon 541 area code that is shared with California:

https://en.wikipedia.org/wiki/Area_codes_541_and_458

Zip codes do very rarely cross state boundaries, or have really odd zip code assignments compared to their geographical assignments. One example being 65733 in Arkansas and Missouri, where a few remote places are served by the 65733 Missouri Zip code:

https://en.wikipedia.org/wiki/ZIP_code#Secondary_regional_prefixes_.28123xx.29_and_local_ZIP_codes_.2812345.29

New Zealand used to have only counties, when they abolished provinces in the 1800's, and now a lot of them have merged into districts. Denmark also changed from counties to regions as well, without any provinces. Norway and Hungary are two more examples of a hop from country to county. There's a lot of fascinating pages on Wikipedia regarding counties and other administrative divisions:

https://en.wikipedia.org/wiki/County

There are certainly odd cases that would be hard to fit into the hierarchies you've outlined above. China, for example, has prefectural level cities that are below a province and above a county in their administrative division hierarchy. I'm not sure how much effort you would want to go through to make these generic by country, though.

https://en.wikipedia.org/wiki/Administrative_divisions_of_China

https://en.wikipedia.org/wiki/Table_of_administrative_divisions_by_country

https://en.wikipedia.org/wiki/Administrative_division

Hope that helps answer your question. =)


How to properly change "innodb_buffer_pool_size" value for mysql?

Changing innodb_buffer_pool_size doesn't need you to nuke ib_logfiles. This could easily be the reason for you had to restore the database (*).

Just stop mysql -> change the settings in my.cnf -> start mysql.

  (*) ib_logfiles are holding recovery data, pages which were not yet
  checkpoint to the tablespace. If you remove them without making sure
  you have a clean shutdown you risk losing data and corrupting your
  database.


Using xtrabackup-v2 as SST method in MariaDB Galera cluster

The two scripts (or maybe just the last one, but you need both) /usr/bin/wsrep_sst_common and /usr/bin/wsrep_sst_xtrabackup-v2 are broken in 10.2.13, but you can get fixed versions from bug ticket MDEV-15254.


Should i expect to execute of 160billion iteration from oracle 11g?

We use SDO_RELATE function which is very fast against SDO_GEOM_RELATE. But SDO_REALTE has no parameter as buffer. We use SDO_GEOM.SDO_BUFFER for giving 2cm buffer to the target object. Now it takes one hour for intersection all records with each other.
Here is working code;

BEGIN
for curs in (select * from ORAHAN t) loop
 for curs2 in (select *
           from ORAHAN t2
          where SDO_RELATE(t2.geoloc,SDO_GEOM.SDO_BUFFER(curs.geoloc,0.2,0.02,'UNIT=M') ,
          'mask=ANYINTERACT') = 'TRUE'
            and t2.mi_prinx <> curs.mi_prinx) loop
  Insert INTO ORAHANCROSSES
   values
   (curs.Mip, curs.Startmi, curs2.Mip, curs2.Startmi);
  commit;
 end loop;
end loop;
END;


To use SQL Server Management Studio 2012 with Team Foundation Server (TFS) source control, you need to first install the Team Foundation Server MSSCCI Provider 2010 32-bit or Team Foundation Server MSSCCI Provider 2012 32-bit.

Then you will be able to select TFS as one of the source control providers under: Tools > Options > Source Control > Plug-In Selection.

Note that one of the pre-requisites to using the MSSCCI Provider is that you also have Visual Studio 2010 Team Explorer (the TFS client) installed on your machine. This is because it installs the TFS Object Model assemblies that the MSSCCI Provider uses to talk to TFS.


Rollback database to old one without backup

The short answer is "No".

If you have restored from backup then you have overwritten your database.

The only way to get your database back is to restore from a suitable backup.

If your database is in full recovery mode and you have the last full backup and subsequent transaction logs you may be able to do a point in time recovery to  a point more acceptable than when the last full backup was taken.

The REPLACE option is just a safeguard against accidentally overwriting the existing database with a backup taken from a different database. The restore will not be allowed in that case otherwise.


Best Practice for Handling Table with Large Amount of Transaction Data

You should specify which database system and version you're using as this will affect your actual choices available for how you structure and manage the data.
You mentioned large in your context means 100,000 new records a day, so let's plan for something an order of magnitude bigger, 1 million records a day. In a month that's 30 million new records, in a year that's ~360 million records. While 360 million records is starting to get a little beefy, it's by no means something unmanageable or needing special treatment in most modern database systems.
It's going to depend on the actual queries you're running, the concurrency of your server with writes vs reads, and the acceptable runtime for reporting off the data, but 360 million records in a year shouldn't scare you with modern RDBMS. I've managed tables with 10s of billions of records in them, and reported directly off them usually querying for about 1 million records at a time in under 10 seconds on fairly modest hardware.
If you want to reduce the concurrency of reporting then you can look into archiving the older data (i.e. anything older than the last one month of data) or migrating it into a data warehouse. Depending on the type of querying being done, and the database system you're using, you might have features available like columnstore indexing and filtered indexes that could help you improve reporting directly from the table itself without the need to archive.
Correctly architecting your schema and indexing your data will go the longest of ways in improving your reporting capabilities.
Finally, as Jonathan Fite mentioned, partitioning won't directly improve your querying performance but it can be used to improve your management of the table because you can smartly divide your data into partitions that allow you to manage the less active data while the more active partitions are in use. You'll see improvements in reduced concurrency when you need to do things like archiving your data (through partition swapping) or index maintenance.

If your estimate of 100,000 rows per day is accurate, and you don't anticipate any meaningful growth from that over the next few years in actuality, then you're only looking at 36 million rows a year. I wouldn't even think about partitioning or archiving at that point. Regular indexing on a well architected schema should provide you very quick query runtimes. But I personally like to plan for a magnitude more of data then I actually expect, like above.


How to monitor mysql percentage disk utilization?

Four years ago, I addressed this type of situation in StackOverflow : How can you determine how much disk space a particular MySQL table is taking up?. My post supplies queries to find out how much logical space a table consumes.

For InnoDB, data and index pages for an individual are lumped together in a single tablespace file (if innodb_file_per_table is enabled) or lumped together with the data and index pages of other InnoDB tables inside ibdata1 (if innodb_file_per_table is disabled). For the sake of optimism, I'll assume you have innodb_file_per_table enabled.

In order to look for percentage usage of a table, you would have to check the physical usage of the table in OS, query the database for the logical usage, and divide the logical by physical and multiply by 100.

Let's assume the following situation

Your datadir is /var/lib/mysql
You have a table called mydb.mytable
You have innodb_file_per_table enabled
You don't know if the table is MyISAM or InnoDB
You don't use other storage engines

Here is what you need for an individual table's space usage

GIVEN_DB=mydb
GIVEN_TB=mytable
DATADIR=/var/lib/mysql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Get Logical Space of the Table
#
SQL="SELECT data_length+index_length FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${GIVEN_DB}' AND table_name='${GIVEN_TB}'"
LS=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
#
# Get Physical Space of Table
# Whether InnoDB or MyISAM
#
cd ${DATADIR}/${GIVEN_DB}
PS=0
for X in `ls -l ${GIVEN_TB}.[Mi][Yb][DId]|awk '{print $5}'` ; do (( PS += X )) ; done
(( DF = PS - LS ))
NUM="${LS}00"
(( PCT = NUM / PS ))
echo "Unused Bytes for ${GIVEN_DB}.${GIVEN_TB} : ${DF}"
echo "Pct(%) for ${GIVEN_DB}.${GIVEN_TB} : ${PCT}"

UPDATE 2015-08-14 13:14 EDT

Your second question

  What if databases/tables are created on different mountpoints. Is there a way to monitor available space for mysql instance then ?

My script can monitor an individual table's space usage regardless of its mount point.

What you are asking for requires two things

List of databases
Script to cycle through each database and get physical and logical sizes

Let's say you have a file called /root/dbmounts that looked like this

db1
db2
db3

Each entry in this file is assumed to be on a different mount point.

You need the script to cycle through them:

DBMOUNT_FILE=/root/dbmounts
DATADIR=/var/lib/mysql
SQL="SELECT SUM(data_length+index_length)"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema=DATABASE()"
for DBMOUNT in `cat ${DBMOUNT_FILE}`
do
    FOLDER=${DATADIR}/${DB}
    cd ${FOLDER}
    AVAILABLE_SPACE=`df -B 1 .|tail -1|awk '{print $3}'`
    PCT_USED=`df -B 1 .|tail -1|awk '{print $4}'`
    PHYSICAL_SPACE=`df -B 1 .|tail -1|awk '{print $2}'`
    LOGICAL_SPACE=`mysql ${MYSQL_CONN} -D${DB} -ANe"${SQL}"`
    FRAGMENTATION_PCT=`echo ${LOGICAL_SPACE}00/${PHYSICAL_SPACE}|bc`
    echo "-------------------------------"
    echo "Database ${DB}"
    echo "Logical : ${LOGICAL_SPACE}"
    echo "Physical : ${PHYSICAL_SPACE}"
    echo "Fragmentation Pct: ${FRAGMENTATION_PCT}"
    echo "Pct Space Used : ${PCT_USED}"
    echo "Available : ${AVAILABLE_SPACE}"
    echo "-------------------------------"
done

If every database is on a different mount point, you do not need the dbmounts file

DBLIST=/tmp/dblist.txt
DATADIR=/var/lib/mysql
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql',performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLIST}
SQL="SELECT SUM(data_length+index_length)"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema=DATABASE()"
for DBMOUNT in `cat ${DBLIST}`
do
    FOLDER=${DATADIR}/${DB}
    cd ${FOLDER}
    AVAILABLE_SPACE=`df -B 1 .|tail -1|awk '{print $3}'`
    PCT_USED=`df -B 1 .|tail -1|awk '{print $4}'`
    PHYSICAL_SPACE=`df -B 1 .|tail -1|awk '{print $2}'`
    LOGICAL_SPACE=`mysql ${MYSQL_CONN} -D${DB} -ANe"${SQL}"`
    FRAGMENTATION_PCT=`echo ${LOGICAL_SPACE}00/${PHYSICAL_SPACE}|bc`
    echo "-------------------------------"
    echo "Database ${DB}"
    echo "Logical : ${LOGICAL_SPACE}"
    echo "Physical : ${PHYSICAL_SPACE}"
    echo "Fragmentation Pct: ${FRAGMENTATION_PCT}"
    echo "Pct Space Used : ${PCT_USED}"
    echo "Available : ${AVAILABLE_SPACE}"
    echo "-------------------------------"
done


No connection could be made because the target machine actively refused it

GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'11.22.33.44' IDENTIFIED BY 'password';

What this means is that you lack privileges connecting to the mysql database from the remote host. What you basicly need to do is to grant those privileges with the above statement.


Possibility on huge MySQL database replication

Plan B:  Stop MySQL; copy the disk.

But first, turn on binlogging so that you will be able to sync up after you get the Slave going.


Postgresql on Windows and custom extensions

Apparently, I somehow got confused about extensions.

It appeared that everything is much simpler: I just copied compiled dll to postgresql lib folder and everything worked.

Detailed information about how to compile PostgreSQL extensions on Windows can be found here:
https://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows/

I'd like to add a note about PGDLLEXPORT. For extensions to work you should add PGDLLEXPORT before two procedures: _PG_init and _PG_output_plugin_init. So code will look like:

PGDLLEXPORT extern void     _PG_init(void);
PGDLLEXPORT extern void     _PG_output_plugin_init(OutputPluginCallbacks *cb);


New Analyst SQL Optimization - Multiple Spools

Have you considered created a permanent table in the / a database with clustered index creation (and appropriate NCIs)? You can then simply query that table and allow for statistics to build up - presumably and only with a very limited understanding of your actual context, if the data is transactional, you can build up that historical data and re-use it for different use cases.
B


Clarification on MySQL innodb_flush_method variable

Here is an explanation on how fdatasync() works vs how fsync() works
fdatasync() flushes all data buffers of a file to disk (before the system call returns). It resembles fsync() but is not required to update the metadata, such as access time. Applications that access databases or log files often write a tiny data fragment (e.g., one line in a log file) and then call fsync() immediately in order to ensure that the written data is physically stored on the harddisk. Unfortunately, fsync() will always initiate two write operations

one write operation for the newly written data
one write operation in order to update the modification time stored in the inode

If the modification time is not a part of the transaction concept, then fdatasync() can be used to avoid unnecessary inode disk write operations.
In English, O_DSYNC is faster than O_DIRECT since O_DIRECT calls fsync() twice (one for logs and one for data) and fsync() verifies data writes via two write operations. Using O_DSYNC calls fdatasync() and fsync(). You can think of fdatasync() as doing an asynchronous fsync() (not verifying data).
Looking at the numbers, O_DSYNC does four write ops, two of which are verified, while fsync() does four write operations, all being verified afterwards.
CONCLUSION
O_DSYNC

faster than O_DIRECT
Data may/may not be consistent due to latency or an outright crash

O_DIRECT

more stable
data consistent
naturally slower

I hope this answer helps, and I hope I didn't make things worse for you.


You can't use utl_file, since it has a maximum line length of 32767.
It's possible that sqlplus has a similar limit. If you are tying to
move data to a different Oracle database you can use data pump. You
can eve use datapump on a materialized view. If you want to migrate
to a non Oracle database you can use Perl or heterogeneous services
to create a database connection to mySQL, SQL Server, etc. Then you
won't have to worry about the line length.

UTL_FILE


Listing databases gives 'ERROR: invalid byte sequence for encoding “UTF8”: 0xe5 0xc6 0xf5'

Quick fix: change client encoding to match content
I got this working by changing the client encoding to match the server encoding. Changing the client encoding did the trick, as it prevented Postgres from encoding utf8 text when the encoding was cp1252. (I have no idea what the Collate stuff is.)
mydb=> \encoding
WIN1252

mydb=> \l
ERROR:  invalid byte sequence for encoding "UTF8": 0xe5 0x6c 0x5f

mydb=> \encoding utf8;

mydb=> \l
                                                      List of databases
   Name    |    Owner    | Encoding |           Collate           |            Ctype            |      Access privileges
-----------+-------------+----------+-----------------------------+-----------------------------+-----------------------------
 mydb      |     myadmin | UTF8     | Norwegian Bokml_Norway.1252 | Norwegian Bokml_Norway.1252 | =Tc/myadmin            +
           |             |          |                             |                             | myadmin=CTc/myadmin+
           |             |          |                             |                             | my=c/myadmin
 postgres  | postgres    | UTF8     | Norwegian Bokml_Norway.1252 | Norwegian Bokml_Norway.1252 |
 template0 | postgres    | UTF8     | Norwegian Bokml_Norway.1252 | Norwegian Bokml_Norway.1252 | =c/postgres                +
           |             |          |                             |                             | postgres=CTc/postgres
 template1 | postgres    | UTF8     | Norwegian Bokml_Norway.1252 | Norwegian Bokml_Norway.1252 | =c/postgres                +
           |             |          |                             |                             | postgres=CTc/postgres

Why (an excursion into raw byte territory)
This took a while to dig into, but I used the tip from the comments section that \l is actually equivalent to a SQL query on the pg_catalog.pg_database. By switching which columns to list I was able to find the column that was proving problematic and by showing it as raw bytes I could pinpoint exactly where:

So it was the datcollate column that was proving problematic. According to the docs, this column is of type name and is LC_COLLATE for this database. Obviously there is something that escapes the eye, since there is nothing in the UTF8 encoded output that is "non-ascii", but the output is a bit weird, since the locale name has some chars which seems to have disappeared: the "å" character in "Norwegian Bokml_Norway.1252" is missing (should be "...Bokmål..."). Is it just a coincidence?
Let's compare outputs of standard ascii with the output above:
$ echo -n Bokml | hexdump -C
00000000  42 6f 6b 6d 6c                                    |Bokml|
00000005

If you compare this with the raw output above you see that the psql output has an additional byte: e5 between 6d and 6c. This is not showing.
So this is how å is encoded in my UTF8 terminal:
$ echo -n Bokmål | hexdump -C
00000000  42 6f 6b 6d c3 a5 6c                              |Bokm..l|
00000007

$ printf "%b" "\xc3\xa5"
å

So å uses two bytes (c3a5) in UTF8, while above it is just one byte. Can it be å in WIN1252?
$ printf "m%b" "\xe5"
m�

$ printf "%b" "\xe5" |  iconv -f cp1252 -t utf8
å

Indeed, it was! So the problem is that the locale name is encoded in the internal postgres database as WIN1252, which seems to not be handled correctly when the server encoding is set to UTF8, as the client seems to assume that the data it receives will be all valid utf8.
Bonus: VIEW query issue fixed
Coincidentally, this also fixed another, related issue I was fixing, which happened when trying to list contents from a VIEW that contained UTF8 Norwegian characters:
mydb=> select * from conversation_message limit 20;
ERROR:  character with byte sequence 0xf0 0x9f 0x91 0x8d in encoding "UTF8" has no equivalent in encoding "WIN1252"


Home rolled gridview search results shows rows that are supposed to be ignored

@Mark is absolutely right. AND has higher precedence, so what your query is really saying is:

WHERE record_number >= 500 AND (column_a LIKE '%something%')

OR

column_c LIKE '%something else%'

OR

column_e LIKE ...

What you want it to say is:

WHERE record_number >= 500

AND

column_a LIKE '%something%' OR column_c LIKE '%something else%' OR ...

The way you do this is through parentheses. So:

WHERE record_number >= 500
AND
(column_a LIKE '%something%' OR column_c LIKE '%something else%' OR ... )

However I also agree with Mark's idea that this is wrong on many levels. Why have you hard-coded the query to not care about the first 499 rows? What happens if the user doesn't enter a parameter for one of the columns? Do you expect this to perform well when your table gets large and you are essentially forcing scans to find all of the matching rows?


version controll application/system for mysql databases

Liquibase is probably the most popular version control system for MySQL.  It's free and open-source.


ORA-00823: Specified value of sga_target greater than sga_max_size dba

I solved it as follows:

Open the pfile and set sga_target=0 and save it.
After :

create spfile from pfile;
create pfile from spfile;

Now start the database :

SQL> startup

ORACLE instance started.
Total System Global Area 5133828096 bytes
Fixed Size                  8929496 bytes
Variable Size             956305192 bytes
Database Buffers         4160749568 bytes
Redo Buffers                7843840 bytes
Database mounted.
Database opened.