Database design question: Tags with values(duration) linked to them

Microsoft SQL Server guy here. Not sure if I'm understanding how you are capturing your events, but an approach I've used is to capture a start time and an end time, then calculate the duration after the fact. If a person is manually starting and ending an event, then that would be my approach.
As far as associating tags to events, a join table would address the issue of multiple tags on a single event.

/****** Object:  Table [dbo].[event_tags]
CREATE TABLE [dbo].[event_tags](
    [id] [int] NOT NULL,
    [event_id] [int] NOT NULL, --Foreign key to table events' primary key
    [tag_id] [int] NOT NULL --Foreign key to table tags' primary key
)


Finding the longest entries for a given column (nvarchar(100))

You can achieve this with this code snippet:

SELECT TOP 1 LEN(column), column
FROM yourTable
ORDER BY LEN(column) DESC

Just replace column with your column which holds the nvarchar(100)


Removing unwanted NULL results when using Right join

This is a job for INNER JOIN, not RIGHT JOIN.

Inner joins give results only when there is a match in both tables.  Right and left joins give results for all rows in either the right-side table or the left-side table with NULL values in columns from the joined table when there is no match.

Here's a an example to help visualize it:

mysql> select * from a;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.01 sec)

mysql> select * from b;
+------+
| id   |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.00 sec)

With the INNER JOIN, only rows with matching values of the "id" column in both tables are returned:

mysql> select a.id, b.id from b inner join a on (a.id = b.id);
+------+------+
| id   | id   |
+------+------+
|    3 |    3 |
|    4 |    4 |
+------+------+
2 rows in set (0.00 sec)

With the right join, all the rows from table "a" are returned, with NULL for the values from table "b" when there is no matching row in "b":

mysql> select a.id, b.id from b right join a on (a.id = b.id);
+------+------+
| id   | id   |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    1 | NULL |
|    2 | NULL |
|    5 | NULL |
+------+------+
5 rows in set (0.00 sec)

If you really, really insist on using RIGHT JOIN for this, suppress the rows with NULL values by using WHERE.  Example:

mysql> select a.id, b.id from b right join a on (a.id = b.id) WHERE b.id is not null;
+------+------+
| id   | id   |
+------+------+
|    3 |    3 |
|    4 |    4 |
+------+------+
2 rows in set (0.00 sec)


Force standby de-synchronisation for script testing

Just disable sending redo to the standby on the primary (replace with actual dest number):

SQL> alter system set log_archive_dest_state_2=defer scope=memory;

To re-enable (replace with actual database name):

DGMGRL> enable database orcl_sb


How to avoid/prevent Aborted Connections in mysql

All aborted connections tells you is the number of aborted connections.  It doesn't tell you why they didn't connect.

Do you have some kind of monitoring system or network scanning system in place?  e.g. Nagios, Spiceworks?  They will/can make a connection to the database network socket, then either not authenticate or fail to authenticate because they haven't been configured correctly.

These would cause the value of aborted connections to rise every time they attempt a connection, which could be very frequently.


You have to join tables like that: (not an actual query)

UPDATE City AS w
  JOIN Cars AS z ON z.id = w.car_id -- some real condition needed
   SET w.status='good'
 WHERE z.car_id=123
   AND w.city_id IS NULL
   AND w.country IS NULL
   AND w.person_id=789
;

Show your tables for more specific code.


PostgreSQL: How find functions that use sys_eval

You can't find where sys_eval is used in our existing code, because this function does not exist on a clean system (unless you have gone out of your way to make it) so your code would have no reason to use it (unless of course your code-base was also hacked and had stuff inserted into it).  The function was created by the hacker as one of the steps of hacking your database.  The hacker could have called it anything he wanted.  He probably got the code here or similar, and then was too lazy to obfuscate the names.

If you are looking for where it is on the poisoned system, look in pg_proc.  But this system needs to be nuked from orbit, so there is probably no point in looking.  The changes you will make (on your clean system) to pg_hba, the firewall, and the database users (including the database superusers, which is where the hack happened, not just the ordinary users) should prevent them from exploiting the new system, but there is no way of telling how many trojans and backdoors were installed in the old one.


MySQL provides an Employee Database for instruction and learning purposes:

Database on GitHub: https://github.com/datacharmer/test_db

Instructions and Information: https://dev.mysql.com/doc/employee/en/


Unable to connect to localhost with mysql utilities

OK, so my suspicions were correct.
The python scripts of mysql utilies use the default "my_print_defaults" utility to get the credentials from a login path.
But the my_print_defaults call returns the password as a bunch of stars, unless you call it with -s...
So I modified the python script mysql/utilities/common/my_print_defaults.py to call the my_print_defaults executable with -s to get the password too. And now it works.


How to sync MySQL's time with system time without mysql restart?

This solution worked for me, I hope it will work for you too:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-10-26 15:13:16 |
+---------------------+
1 row in set (0.07 sec)

mysql> SET GLOBAL time_zone = 'SYSTEM';
Query OK, 0 rows affected (0.07 sec)

mysql> show global variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | SYSTEM              |
+------------------+---------------------+
2 rows in set (0.18 sec)

mysql>

You need SUPER privilege to set this configuration value on run time to avoid system restart.


Don't put your database files on the root of any drive. All kinds of things go wrong here, especially if you have UAC enabled.

Create a subfolder, like H:\Data or H:\Log, and put the files there, making sure the SQL Server service account has read/write permissions. Or use the actual instance data/log folders, which should already be set up permissions-wise. You can change those, too, if they're currently in a not-so-optimal place (like somewhere on C: <shudder>).

(Also, your CREATE DATABASE command is missing a LOG ON specification, dictating where you should put your log file.)


Get lines of pg_hba.conf with trust method from user with minimal privileges

You are nearly there, just have to process the output from pg_read_file() as it returns each line of pg_hba.conf as a single value.  The following query will do the trick:

WITH file (line) AS (
    SELECT regexp_split_to_array(a, '\t+')
      FROM regexp_split_to_table(pg_read_file('pg_hba.conf'), '\n') AS t(a)
     WHERE a ~ '^[^#].*trust\s*$'
)
SELECT line[1] AS type,
       line[2] AS database,
       line[3] AS username,
       CASE WHEN array_length(line, 1) = 4 THEN NULL ELSE line[4] END AS range,
       CASE WHEN array_length(line, 1) = 4 THEN line[4] ELSE line[5] END AS method
  FROM file;

This might not work in all cases that are possible in pg_hba.conf, though - you can tweak this query if you need to.

As you already noticed, pg_read_file() can be run only by superusers.  This is the very reason why you need SECURITY DEFINER on your function.  In order to make it work, you have to create the function as a superuser.  (Note that changing owner after a non-superuser created the function doesn't work.  If this is your case, drop the function first an create it anew, as a superuser this time.)


SQL 2005 query all databases and output to one table

select  *,DB_NAME() as DB_NAME
into    mytable
from    sys.all_objects
where   1=2

DECLARE @command varchar(1000)
SELECT @command =

'USE [?] select *,  DB_NAME() as DatabaseName
from sys.all_objects where type=''p'' and is_ms_shipped=0 '

insert into mytable  EXEC sp_MSforeachdb @command

Using table variable

declare  @mytable table (
    [name] [sysname] NOT NULL,
    [object_id] [int] NOT NULL,
    [principal_id] [int] NULL,
    [schema_id] [int] NOT NULL,
    [parent_object_id] [int] NOT NULL,
    [type] [char](2) NULL,
    [type_desc] [nvarchar](60) NULL,
    [create_date] [datetime] NOT NULL,
    [modify_date] [datetime] NOT NULL,
    [is_ms_shipped] [bit] NULL,
    [is_published] [bit] NULL,
    [is_schema_published] [bit] NULL,
    [DB_NAME] [nvarchar](128) NULL
    )

DECLARE @command varchar(1000)
SELECT @command =

'USE [?] select *,  DB_NAME() as DatabaseName
from sys.all_objects where type=''p'' and is_ms_shipped=0 '

insert into @mytable  EXEC sp_MSforeachdb @command

select * from @mytable


Data center from scratch which way to go

Currently I am running my business on 2x SQL servers which have 38 GB of ram at all

As well as the replication strategy to improve availability the though the replica taking over if the primary fails, you must factor in a god backup strategy - in what follows I'm going to assume that you already have this (if not, you need to factor it into your costings and a really good backup solution for your business critical data is not something you should try buy/create cheaply).

If you are happy with this arrangement in terms of resilience and general performance, and you are just upgrading the servers mainly to replace old hardware, then go with option 1 and upgrade the current design. Remember that if you build a different infrastructure you may run into problems you've never seen before and if you do they may turn up at the least convenient time for your business. If you don't have the knowledge/experience to fix things fast (and it isn't available in anyone else in the company you can call on) this could be very expensive in terms of business lost due to downtime and paying an expert to fly in and help.

  Do I need SSDs beside plenty of RAM

That very much depends on your applications write patterns. Given you have ~80Gb of data you might find all your common working set could be held in RAM most of the time so for read heavy loads you will see little or no difference between the SSDs and spinning metal for day-to-day operations as the drives won't be touched for most reads. That data held in memory will be lost in some circumstances (such as when SQL server is shut down for what-ever reason) and it will take a while for the working set to get loaded and at this point the SSDs will help quite noticeably. For writes data will still of course need to hit the disk, so for write heavy loads the SSDs will help.

Do remember that Standard Edition will only use up to 64Gb of memory per instance (see http://msdn.microsoft.com/en-us/library/cc645993.aspx#CrossBoxScale) so you may not see all that 96Gb get used in either box.

  I now have 1 main database (80GB-DATA and 20GB of SQL Server log)
  I can have at TWO SQL servers 8 X 250 GB ssd SATA disc
  or HP storage with 10~14 pieces of 600GB 10K SAS disc in storage

Am I reading this wrong, or are you expecting your data size to increase a fair bit over the active service life of the new hardware? In RAID10 those SSDs will give you ~1TB of space which is considerably bigger than the ~100Gb you are using now, and that large collection of spinning metal even more so (3Tb+ unless you configure a redundancy level over 2:1), so you might be over-spending here for your near-/mid-term needs.


SQL Server 2012 Agent hangs at a particular time of the day

We had similar issues on one of our clusters and found that "Allow the computer to turn off this device to save power" was turned on for the primary NIC. Id try turning that setting  off on any/all NICs. I posted as "a possible" answer so I could post the picture:


Appropriate multi-language schema, or overkill?

We've done a lot of this, and (administrative) users were allowed to fix the translations live.  (You still might want a caching layer, but I'm totally down with driving this with a real database and not resource files - it gives you a ton of power to query and find things which need to be translated, etc).  I think your schema is probably fine, so I'll just pass on some stuff we learned in the hope that it's useful.

One thing you have left out is phrases with insertion points.  In the example below, the order is reversed and the language is still English, but this could very easily be two different languages - pretend this is just two languages who normally put things in a different order.

Hello, <username> you have <x> points!

You've got <x> points to spend, <username>!

In our pre-.NET, we had a routine which did the insertion so the phrases would look like this:

Hello, {0:username} you have {1:points} points!

You've got {1:points} points to spend, {0:username}!

This would obviously simply be used in your code as <%= String.Format(phrase, username, points); %> or similar

Which helped the translator a bit.  But .NET String.FOrmat doesn't support commenting within the format string, unfortunately.

As you say, you would not want to handle that in your php with locale awareness or meta phrases.

So what we had was a master phrase table:

phraseid, english, supplemental info

and a localized table:

phraseid, localeid, translation

You've also assumed with INNER JOINS that the localized versions exist - we tended to leave them out until they were translated, so that query of yours would end up returning nothing at first (not even the default)

If a translation didn't exist, ours defaulted to English, then fellback to code-provided (in case the database didn't have the ID, and it was also clear from the code what phrase identifier "TXT_LNG_WRNNG_INV_LOW" was actually trying to get) - so the equivalent of this query is what we used:

SELECT COALESCE(localized.translation, phrase.english, @fallback)
FROM DUAL
LEFT JOIN phrase
    ON phrase.phraseid = @phraseid
LEFT JOIN localized
    ON localized.phraseid = phrase.phraseid
    AND localized.localeid = @localeid

Obviously, you might get all the things at one time using your page system.

We tended not to link things to the page because they were re-used a lot between pages (and not just in page-fragments or controls), but that's certainly fine.

In the case of our Windows-native apps, we used reflection and a mapping file from control to translation tag so that translation did not require re-compiles (in pre-.NET apps we had to tag the controls using the Tag or other special properties).  This is probably a little more problematic in PHP or ASP.NET MVC, but possible in ASP.NET where there is a full-blown server-side page model.

For testing, you can obviously query to find missing translations very easily.  To find places which need to be tagged, translate the entire phrase dictionary using pig-latin or Klingon or something like replace every non-space character with ? - the English should stand out and let you know that some naked plaintext has crept into your HTML.


How to set up foreign key constraints referencing more than one table

It looks like you are not aware that foreign key can be based on several columns.

So, yes, add a column ReportID to ScheduledReportParameters and include it in two foreign keys to enforce the constraints.

One foreign key between ScheduledReportParameters and ScheduledReports using two fields (ScheduledReportID, ReportID).

Second foreign key between ScheduledReportParameters and ReportParameters using two fields (ReportParameterID, ReportID).

Full script

Reports

CREATE TABLE [dbo].[Reports](
    [ReportID] [int] NOT NULL,
    [ReportName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Reports] PRIMARY KEY CLUSTERED
(
    [ReportID] ASC
))

ReportParameters

CREATE TABLE [dbo].[ReportParameters](
    [ReportParameterID] [int] NOT NULL,
    [ReportID] [int] NOT NULL,
    [ParameterName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ReportParameters] PRIMARY KEY CLUSTERED
(
    [ReportParameterID] ASC
))
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_ReportParameterID_ReportID]
ON [dbo].[ReportParameters]
(
    [ReportParameterID] ASC,
    [ReportID] ASC
)
GO

ALTER TABLE [dbo].[ReportParameters]  WITH CHECK ADD
CONSTRAINT [FK_ReportParameters_Reports] FOREIGN KEY([ReportID])
REFERENCES [dbo].[Reports] ([ReportID])
GO

ALTER TABLE [dbo].[ReportParameters] CHECK CONSTRAINT [FK_ReportParameters_Reports]
GO

ScheduledReports

CREATE TABLE [dbo].[ScheduledReports](
    [ScheduledReportID] [int] NOT NULL,
    [ReportID] [int] NOT NULL,
    [ScheduleName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ScheduledReports] PRIMARY KEY CLUSTERED
(
    [ScheduledReportID] ASC
))
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_ScheduledReportID_ReportID]
ON [dbo].[ScheduledReports]
(
    [ScheduledReportID] ASC,
    [ReportID] ASC
)
GO

ALTER TABLE [dbo].[ScheduledReports]  WITH CHECK ADD
CONSTRAINT [FK_ScheduledReports_Reports] FOREIGN KEY([ReportID])
REFERENCES [dbo].[Reports] ([ReportID])
GO

ALTER TABLE [dbo].[ScheduledReports] CHECK CONSTRAINT [FK_ScheduledReports_Reports]
GO

ScheduledReportParameters

CREATE TABLE [dbo].[ScheduledReportParameters](
    [ScheduledReportParametersID] [int] NOT NULL,
    [ScheduledReportID] [int] NOT NULL,
    [ReportID] [int] NOT NULL,
    [ReportParameterID] [int] NOT NULL,
    [ParameterValue] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ScheduledReportParameters] PRIMARY KEY CLUSTERED
(
    [ScheduledReportParametersID] ASC
))
GO

ALTER TABLE [dbo].[ScheduledReportParameters]  WITH CHECK ADD
CONSTRAINT [FK_ScheduledReportParameters_ReportParameters]
FOREIGN KEY([ReportParameterID], [ReportID])
REFERENCES [dbo].[ReportParameters] ([ReportParameterID], [ReportID])
GO

ALTER TABLE [dbo].[ScheduledReportParameters]
CHECK CONSTRAINT [FK_ScheduledReportParameters_ReportParameters]
GO

ALTER TABLE [dbo].[ScheduledReportParameters]  WITH CHECK ADD
CONSTRAINT [FK_ScheduledReportParameters_ScheduledReports]
FOREIGN KEY([ScheduledReportID], [ReportID])
REFERENCES [dbo].[ScheduledReports] ([ScheduledReportID], [ReportID])
GO

ALTER TABLE [dbo].[ScheduledReportParameters]
CHECK CONSTRAINT [FK_ScheduledReportParameters_ScheduledReports]
GO

It is necessary to have unique index on two columns that are used by the foreign key. I added explicit indexes in this example (IX_ReportParameterID_ReportID and IX_ScheduledReportID_ReportID). You can also add the second column into the primary key of the corresponding table.

Sample data

INSERT INTO [dbo].[Reports]
    ([ReportID]
    ,[ReportName])
VALUES
    (1,'R1'),
    (2,'R2');

INSERT INTO [dbo].[ReportParameters]
    ([ReportParameterID]
    ,[ReportID]
    ,[ParameterName])
VALUES
    (1,1,'R1P1'),
    (2,1,'R1P2'),
    (3,2,'R2P3'),
    (4,2,'R2P4'),
    (5,2,'R2P5');

INSERT INTO [dbo].[ScheduledReports]
    ([ScheduledReportID]
    ,[ReportID]
    ,[ScheduleName])
VALUES
    (1,1,'S1');

Testing constraints

Add parameter values for report 1:

INSERT INTO [dbo].[ScheduledReportParameters]
    ([ScheduledReportParametersID]
    ,[ScheduledReportID]
    ,[ReportID]
    ,[ReportParameterID]
    ,[ParameterValue])
VALUES
    (1,1,1,1,'R1 P1 V1'),
    (2,1,1,2,'R1 P2 V2');

(2 row(s) affected)

Try to add value for the parameter that doesn't belong to the report (parameter 3 belongs to report 2, not 1:

INSERT INTO [dbo].[ScheduledReportParameters]
    ([ScheduledReportParametersID]
    ,[ScheduledReportID]
    ,[ReportID]
    ,[ReportParameterID]
    ,[ParameterValue])
VALUES
    (3,1,1,3,'V');

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ScheduledReportParameters_ReportParameters". The conflict occurred in database "AdventureWorks2014", table "dbo.ReportParameters".
The statement has been terminated.

Try to add parameter value for the wrong scheduled report (while parameter 3 belongs to report 2, schedule 1 is not for report 2):

INSERT INTO [dbo].[ScheduledReportParameters]
    ([ScheduledReportParametersID]
    ,[ScheduledReportID]
    ,[ReportID]
    ,[ReportParameterID]
    ,[ParameterValue])
VALUES
    (3,1,2,3,'V');

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ScheduledReportParameters_ScheduledReports". The conflict occurred in database "AdventureWorks2014", table "dbo.ScheduledReports".
The statement has been terminated.

Missing parameter values

Please note, that this schema doesn't prevent missing values for parameters. For example, you can delete value V2:

DELETE FROM [dbo].[ScheduledReportParameters]
WHERE [ScheduledReportParametersID] = 2;

(1 row(s) affected)

Thus now the schedule S1 has value only for parameter P1 and doesn't have value for parameter P2.


Scalar UDFs don't have to kill performance when they return a constant value. You can save off the function value to a local variable or parameter and use that variable in your query

CREATE FUNCTION dbo.RETURN_SPECIAL_VALUE() RETURNS INT
AS
BEGIN
    RETURN 1;
END;

GO

DECLARE @special_value INT = dbo.RETURN_SPECIAL_VALUE();

-- example query that uses special value
SELECT TOP (5) @special_value, number
FROM master..spt_values;

On the other hand, if the constant is used in the WHERE clause the estimates may be better using the literal. Unless an OPTION (RECOMPILE) is used, which may be excessive.


'sp_releaseschemalock' causing me a blocking issue?

sp_releaseschemalock is an undocumented external proc which simply releases the schema lock:

As per our past experience on this, we witnessed the same but never had the issue with sp_releaseschemalock being a culprit.

Kindly check the complete details when checking the blocking. That is ,you should not completely ignore the one that is getting blocked rather than completely dependent on blkd_by_spid, as blkd_by shows sp_releaseschemalock as culprit.

Also would like you to refer this

  You should check your linked server configurations and make sure they
  are "collation compatible" checked. Otherwise the entire remote table
  is returned to the local calling server to execute the WHERE clause.

As mentioned, you need to go through the settings for the query getting executed via linked server because the primary server wont have the required statistical information when fetching the same from other server through its linked query.


After Restoring Log Shipping to Secondary Server, First Stored Procedure Execution is Slow

There are a few possible things going on here, here's a non-exhaustive list:

the execution plan cache is cleared by the log restore, so plans will need to be recompiled the first time. If your plans have long compile times, this could explain the difference.  You didn't mention exactly how long the delay is on the first run compared to the subsequent runs

this one seems like the least likely - you can see your plan compilation time in actual execution plan XML

the buffer pool is also cleared during the restore, so all data has to be read from disk on the first execution

if this is the case, you'll likely see high PAGEIOLATCH* waits during the initial run if you check wait stats

Some things you could do to mitigate this are

"warm up" the buffer cache (by reading all data from important tables into memory using SELECT COUNT(*) FROM dbo.YourTable),
"warm up" the proc cache by running all the critical stored procedures as a post-restore step

Providing us with a "fast" and "slow" example of an execution plan could help us track down exactly which thing is happening.

If you are on SQL Server 2012 or newer, then it's possible that sync stats updates are causing the delay.  These "readable secondary stats" get created in TempDB, since the log shipping secondary is read-only.  You can read more about that here (the article is about AGs, but the same thing applies in this scenario):

AlwaysOn: Making latest statistics available on Readable Secondary, Read-Only database and Database Snapshot

If this is the problem causing your slowdown, then one solution would be to find those stats, and then create them in the production database, so that they are up-to-date and available after the restore.  You can look for temp stats with this query:

SELECT * FROM sys.stats WHERE is_temporary = 1;

Based on the wait stats you provided, and the fact that the plans are the same, this is pretty conclusively due to the buffer pool being cleared by the log restore.

On a normal run, you get 12,768 ms (almost 13 seconds) of IO waits.
On the first run, you get 411,129 ms (almost 7 minutes) of IO waits.

The SELECT COUNT(*) approach you tried may not have helped due to different indexes being used by the actual procedure vs the COUNT(*) query.  You have a few options here:

Go through each execution plan and make note of the indexes being used, and then pull those indexes into memory as a post-restore step - using index hints this time (SELECT COUNT(*) FROM dbo.YourTable WITH (INDEX (IX_Index_Being_Used_By_Proc)))
Go through the process of scripting out a process to run each procedure as a post-restore step (this seems a bit easier than option 1)
Tune the queries so that they don't need to do so many reads (not sure how feasible this is)
Speed up the I/O subsystem - get faster disks, local SSDs, more channels to the SAN, etc (this is probably the hardest and most expensive option


Does bool_and short-circuit function calls with arguments?

It depends on whether the funciton is IMMUTABLE or not. All tests done on PostgreSQL 9.5.

CREATE FUNCTION f_expensive_volatile ()
RETURNS bool AS $$
  SELECT true
  FROM pg_sleep(1)
$$ LANGUAGE SQL
VOLATILE;

CREATE FUNCTION f_expensive_stable ()
RETURNS bool AS $$
  SELECT true
  FROM pg_sleep(1)
$$ LANGUAGE SQL
STABLE;

CREATE FUNCTION f_expensive_immutable ()
RETURNS bool AS $$
  SELECT true
  FROM pg_sleep(1)
$$ LANGUAGE SQL
IMMUTABLE;

Now we create sample data.

CREATE TABLE f AS
SELECT 1 AS id, *
FROM generate_series(1,100) AS gs(x);

Now on the IMMUTABLE one, note Execution time: 0.138 ms,

EXPLAIN ANALYZE
  SELECT id
  FROM f
  GROUP BY id
  HAVING bool_and( f_expensive_immutable() );

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 HashAggregate  (cost=2.50..2.51 rows=1 width=4) (actual time=0.083..0.083 rows=1 loops=1)
   Group Key: id
   Filter: bool_and(true)
   ->  Seq Scan on f  (cost=0.00..2.00 rows=100 width=4) (actual time=0.015..0.030 rows=100 loops=1)
 Planning time: 1001.328 ms
 Execution time: 0.138 ms
(6 rows)

And on the STABLE one...  note me not waiting around

EXPLAIN ANALYZE
  SELECT id
  FROM f
  GROUP BY id
  HAVING bool_and( f_expensive_stable() );

And also this exemplifies me not waiting around...

EXPLAIN ANALYZE
  SELECT id
  FROM f
  GROUP BY id
  HAVING bool_and( f_expensive_volatile() );

With arguments

CREATE FUNCTION f_expensive_immutable (int)
RETURNS bool AS $$
  SELECT true
  FROM pg_sleep(1)
$$ LANGUAGE SQL
IMMUTABLE;

EXPLAIN ANALYZE
  SELECT id
  FROM f
  GROUP BY id
  HAVING bool_and( f_expensive_immutable(id) );

Yes, it seems that optimization is lost if you provide arguments


Creating a Linked Server that Points to Itself

Turns out that I was able to get it working with different parameters.

EXEC master.dbo.sp_addlinkedserver
    @server = N'LinkedServerName',
    @srvproduct=N'',
    @provider=N'SQLNCLI',
    @provstr=N'DRIVER={SQL Server};Server=(local)\InstanceName; Initial Catalog=DBNAME;uid=user;pwd=password;'


Did you try your idea?  What were your results?  Yes, you can create a synonym as:

CREATE SYNONYM [SRAPP.OPub.dbo.ADE]
FOR SQLProd1.OPub.dbo.ADE
GO

But you cannot use a synonym for a linked server.  Also, as of SQL Server 2016 the following limitation exists:

  Four-part names for function base objects are not supported.

Recommendation:  Drop and Recreate the Linked Server 'SRAPP'.  You can help yourself by scripting out the existing definition. Drop the existing linked server and recreate it using the new server name.

As of SQL Server 2016 synonyms can only be defined for:

Assembly (CLR) Stored Procedure
Assembly (CLR) Table-valued Function
Assembly (CLR) Scalar Function
Assembly Aggregate (CLR) Aggregate
Functions Replication-filter-procedure
Extended Stored Procedure SQL
Scalar Function
SQL Table-valued Function
SQL Inline-table-valued Function
SQL Stored Procedure
View
Table (User-defined)

Yes, there are many things that we can conceive about synonyms, but they do not yet exist.


In slow_log table sql_text column is BLOB

In 5.7 ; column sql_text is changed to BLOB... Hence, you need to use
select CONVERT(sql_text USING utf8  ) from mysql.slow_log ;


Why MySQL query is too much slow with sending data status?

Change LEFT JOIN to JOIN.

Change

AGAINST ('Vectro Electro' IN BOOLEAN MODE)

to

AGAINST ('+Vectro +Electro' IN BOOLEAN MODE)

Have this index on tbl_artists:  FULLTEXT(artist_name).

I assume that tbl_songs has PRIMARY KEY(id).

Assuming that tbl_songs_artist is a many-to-many mapping table, follow the tips in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Is this a typo?  tbl_songs.ids

Use ENGINE=InnoDB.

Don't comment out the setting for innodb_buffer_pool_size.

Not more than 1% of RAM for these:

max_heap_table_size
tmp_table_size

("Sending status" is useless info.)


SQL Server 2008 R2 differential backups much larger than expected

A small transaction log doesn't mean the diff won't be small though.  A diff backup looks at the physical page level since SQL Backups backup database pages.  When even 1 bit of data is changed in a page any point after a full backup, a flag is made active so the DB engine knows to back it up if a diff is taken.

Transaction logs largely record the minimal data needed to get the data within the pages back to how they were prior, thus it could be much smaller.  If you only changed 1 bit of data you are going to only reflect that change in your log.  When the diff backup runs, the entire 8k page is backed up.

Thus a Diff backup will ALWAYS backup the entire page if even 1 bit is changed on there.  A transaction log will record what's needed to roll that page back to the prior transactionally consistent state thus in this case it could be tiny, but the diff backup will still backup the entire page.

If you want more info check out the Backup Internals video on the Technet MCM site:
https://technet.microsoft.com/en-us/dn912438#mcm-readiness

Do you have any logging?  If not check out your default trace.  It'll at least tell you some minimal information.  If you don't have any logging, this would be a great time to start.


How to get MIN and MAX date from table data

Here's a simple method. We use a CTE to get the first and last INSERT_DATEs for each job; then, join to the main table to retrieve the estimated completions dates from those first and last loads:

WITH JobFirstLast (job, first_load, last_load, load_count) AS
     (SELECT job,
             MIN(INSERT_DATE) as first_load,
             MAX(INSERT_DATE) as last_load,
             COUNT(job) as load_count
        FROM ktimov.press_schd
       GROUP BY job
     )
SELECT j.job
      ,f.EST_COMPLETION as first_est_completion
      ,l.EST_COMPLETION as last_est_completion
      ,j.load_count - 1 as times_changed
  FROM JobFirstLast j
         LEFT  JOIN ktimov.press_schd f ON (j.job = f.job AND j.first_load = f.INSERT_DATE)
         LEFT  JOIN ktimov.press_schd l ON (j.job = l.job AND j.last_load = l.INSERT_DATE);

NOTE: I'm assuming that the first load is simply establishing the initial values, and that only subsequent loads are counted as changes. If you really want the total number of loads, change j.load_count - 1 to j.load_count.


DR site SQL Server DB server not need Quorum Disk?

If Production site SQL DB Servers are down, will Windows Server Failover Cluster
  be Online or Offline ?

If you have a setup that relies on a quorum disc, this must be available - ergo offline. This is a non recommended setup. Whoever set it up did not bother to validate it makes sense.

Geographically dispersed clusters are a proven technology (start at https://technet.microsoft.com/en-us/library/cc757840(v=ws.10).aspx) but their setup was seriously bothered here.

"The quorum data must be synchronously replicated between the sites. To ensure that the Server cluster guarantees of consistency are met, the cluster database must be kept consistent across all nodes. If the quorum disk is replicated across the sites, it MUST be replicated synchronously."

Simple like that.

If i were you, I would start retiring the error prone outdated approach and work towards AOAG with a read only replica offsite.

https://www.simple-talk.com/sql/database-administration/sql-server-2012-alwayson/

This eliminates the shared storage even locally (which IS a problem - shared storage means a dying server crashing the database structure takes down the cluster). The DR scenario is a complicated one, though. You likely end up having to have the quorum disc replicated.


Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error

There are two system views called sysindexes - one owned by SYS and one owned by DBO. You likely want the the one owned by SYS:

SELECT z.name FROM sys.sysobjects z JOIN sys.sysindexes ON (z.id = i.id) WHERE type = 'U'


Keep H2 in-memory database between connections

Set DB_CLOSE_DELAY=-1
By default, an in-memory database in H2 is discarded after the connection closes.
To override this default behavior, set DB_CLOSE_DELAY to a negative one. You can do so either:

When making a connection with a JDBC path
By calling a SQL command

JDBC path
To keep the database in existence despite the connection closing, add an element to your JDBC path.  The database will continue to live on in memory until your app exits. So you can make successive connections to the same continuing database.
Tip: Notice the SEMICOLON (not COLON) between the database name and this element.
Connection conn =
    DriverManager.getConnection(
        "jdbc:h2:mem:example_db;DB_CLOSE_DELAY=-1"  // Set `DB_CLOSE_DELAY` to `-1` to keep in-memory database in existence after connection closes.
    )
;

If you want to explicitly call for the default behavior, set 0. For a number of seconds before discarding the database after the last connection closes, pass a positive integer ( >=1 ).
This feature is covered in the manual in the section on In-Memory Database in the last paragraph.

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1.

SQL command
This setting can also be manipulated at runtime via the SET DB_CLOSE_DELAY command, passing an integer.
To quote that documentation:

SET DB_CLOSE_DELAY int
Sets the delay for closing a database if all connections are closed. The value -1 means the database is never closed until the close delay is set to some other value or SHUTDOWN is called. The value 0 means no delay (default; the database is closed if the last connection to it is closed). Values 1 and larger mean the number of seconds the database is left open after closing the last connection.
If the application exits normally or System.exit is called, the database is closed immediately, even if a delay is set.
Admin rights are required to execute this command, as it affects all connections. This command commits an open transaction in this connection. This setting is persistent. This setting can be appended to the database URL: jdbc:h2:test;DB_CLOSE_DELAY=-1
Example:
SET DB_CLOSE_DELAY -1


This simple, single table query does a filesort, why?

ORDER BY a  ASC, b  ASC and
ORDER BY a DESC, b DESC
can avoid "filesort" (if they can use INDEX(a,b)).  But if you mix ASC and DESC, it cannot avoid a sort pass.

And, no, you cannot say INDEX(a ASC, b DESC).  Or rather, you can say it, but DESC is ignored in the index definition.

Note that "filesort" may or may not actually touch disk.  In many cases, the sort can be done entirely in RAM.  What you are probably concerned about is the need to scan the entire table when you want only the first 'page'.

(This seems to be a watered down case.  If you would like to present the real schema, there might be other tips.)

Suggestion:  Use DESC for both fields and hint to the user that the dates were really different when he sees two titles in DESC order.

Since you seem to be doing "pagination", see how to remember where you left off as an optimization for subsequent pages.


Why do I need to cast NULL to column type?

This is an interesting finding.  Normally, a NULL has no assumed data type, as you can see here:

SELECT pg_typeof(NULL);

 pg_typeof
───────────
 unknown

This changes when a VALUES table comes into the picture:

SELECT pg_typeof(core) FROM (
    VALUES (NULL)
) new_values (core);

 pg_typeof
───────────
 text

This behaviour is described in the source code at https://doxygen.postgresql.org/parse__coerce_8c.html#l01373:

 /*
  * If all the inputs were UNKNOWN type --- ie, unknown-type literals ---
  * then resolve as type TEXT.  This situation comes up with constructs
  * like SELECT (CASE WHEN foo THEN 'bar' ELSE 'baz' END); SELECT 'foo'
  * UNION SELECT 'bar'; It might seem desirable to leave the construct's
  * output type as UNKNOWN, but that really doesn't work, because we'd
  * probably end up needing a runtime coercion from UNKNOWN to something
  * else, and we usually won't have it.  We need to coerce the unknown
  * literals while they are still literals, so a decision has to be made
  * now.
  */

(Yes, PostgreSQL source code is relatively easy to understand and most places, thanks to excellent comments.)

The way out, however, might be the following.  Let's say you are always generating VALUES that match all columns of a given table (see the second note below for other cases).  From your example, a small trick could possibly help:

SELECT (x).* FROM (VALUES ((TRUE, NULL, 1234)::fields)) t(x);

 active │ core │  id
────────┼──────┼──────
 t      │      │ 1234

Here you use row expressions casted to the table's type, and then extracting them back to a table.

Based on the above, your UPDATE could look like

UPDATE fields AS t set active = (x).active, core = (x).core
FROM ( VALUES
           ((true, NULL, 3419)::fields),
           ((false, NULL, 3420)::fields)
     ) AS new_values(x) WHERE (x).id = t.id;

Notes:

I removed the double quotes for better human readability, but you can keep them as they help when generating (column) names.
if you need only a subset of the columns, you can create custom types for this purpose.  Use them the same way as you would above (where I use the type automatically created with the table, holding the row structure of the latter).

Look at the whole thing working on dbfiddle.


Potential issues migrating from SQL Server 2014 to 2012 by scripting tables and data?

You shouldn't have any problems scripting your database out and running it on the new database.  There isn't any data for foreign keys to affect.  After that you will have to be more careful to load the data in the correct order.  Lookup tables first, then data tables etc.

That isn't a huge issue since you can get the order down in test before trying it in production.  Where you may have your biggest problems is in any code/features that you are using that are new to 2014.  Follow the link and review the new features and search for them.  After that it's just testing, testing, testing.  Try it out in test and see what happens.  Document your changes, change them in production to remove any issues, and then start your move.


How can I rearrange these two queries to get the second column from inside the IN()

#Inner Join both the tables:

SELECT random.id, `where`, `who`, COUNT(*)
FROM cards
JOIN random
ON cards.who=random.name

#I assume you want all the values from `who` column from table:cards, so there's no need to use WHERE again.


The EXPLAIN is about as good as it will get.  "Range" is good for the WHERE.  The ORDER BY, though not mentioned, should be handled as well.  And it should stop at 50 (again, no clue in EXPLAIN).

10.00 percent of the table is an estimate based on virtually no information; ignore it.

"5.7 clustered" is a contradiction.  Do you mean "NDB Cluster"?  Or a Galera-based cluster?

Indexing a flag (status) is usually useless.

More on indexing .

Back to the question of 15 seconds.  I agree that the time is unreasonably large.  Here are some partial explanations...

Something else was busy in the system.
The bulky TEXT column was store off-record, necessitating an extra disk hit.  Granted, 50 disk hits should take less than a second.
The Posts may be scattered around; that is, not stored consecutively.  Again, maybe 50 disk hits and another fraction of a second.

I am surprised that it took more than one second, even after allowing for uncached blocks.

Run the query a second time (to counteract the caching); how long does it take?

Another thing to check -- how big is innodb_buffer_pool_size and how big is RAM?  A tiny VM could be part of the problem.  The buffer_pool being so big that swapping occurs could actually explain 15 seconds.


How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

Ouch you have innodb_file_per_table off

I wrote articles on this before. You will have to convert the InnoDB infrastructure.

Oct 29, 2010 : My Original Post in StackOverflow
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

Once you do this, anytime you do OPTIMIZE TABLE to an InnoDB, it will actually shrink.

If you do not perform the InnoDB infrastructure cleanup, the ibdata1 file will actually grow in size. To make matter worse, I think mysqltuner.pl is using a weird algorithm for determining InnoDB fragmentation. I tend to ignore it.

I check for fragmentation differently.

Back on April 19, 2012 I answered a question similar to this: How do you remove fragmentation from InnoDB tables?.

Here is what I said in that earlier post:

  After running the above steps, how can you determine what tables need
  to be defragmentated? It is possible to find out, but you will have
  script it.

  Here is an example: Suppose you have the table mydb.mytable. With
  innodb_file_per_table enabled, you have the file
  /var/lib/mysql/mydb/mytable.ibd

  You will have to retrieve two numbers

  FILESIZE FROM OS : You can ascertain the filesize from the OS like
  this

ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'

  FILESIZE FROM INFORMATION_SCHEMA : You can ascertain the filesize from
  information_schema.tables like this:

SELECT (data_length+index_length) tblsize FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';

  Just subtract INFORMATION_SCHEMA value from OS value and divide the
  difference by the INFORMATION_SCHEMA value.

From there you would decide what percentage deems it necessary to defrag that table. In other words, if there is a big difference between the filesize of the .ibd file and the number from INFORMATION_SCHEMA.TABLES, go ahead with OPTIMIZE TABLE. Regardless of the filesize, please don't worry if the fragmentation is less than 1%.


Want to create an SQL function that removes table row duplicates

That's a big first step for a self-identified newbie. If I read your question correctly, you're looking to create reusable code that will delete duplicates from an arbitrary table. I'm not going to show you how to do that because it's a) dangerous and b) non-trivial. But here's how I'd do it for one table:

with cte as (
   select row_number() over (partition by col_a, col_b order by col_a) as [rn]
   from tbl
)
delete cte
where [rn] <> 1

This code assumes that you have a table called tbl and you're using *col_a* and *col_b* together to determine uniqueness. What's going on here is that you're essentially enumerating all of the rows within a given set that would be considered duplicate and deleting all but the first.


Status show SSL is in use even though I did not set up certificate on the client side?

REQURE SSL only requires a TLS connection. Use REQUIRE X509 to enforce a TLS client certificate, or better, use REQUIRE SUBJECT or REQUIRE ISSUER to ensure that is the identical certificate that matches the one issued.


EXCEPT operator query to differentiate between new and changed rows

You could use the technique from this post.

--Demo setup
set nocount on
drop table if exists #T1
drop table if exists #T2
Create table #T1 (Id int, OtherData varchar(10))
Create table #T2 (Id int, OtherData varchar(10))

insert into #T1(Id,OtherData) values(1,'NewRow')
insert into #T1(Id,OtherData) values(2,'ChgRowB')

insert into #T2(Id,OtherData) values(2,'ChgRowA')

-----------------------------------------------
--The actual query
-----------------------------------------------

-- NEW
SELECT A.*, 'NEW'  AS 'CHANGE_TYPE'
FROM #T1  A
LEFT JOIN #T2  B ON B.ID = A.ID
WHERE B.ID IS NULL
UNION
-- MODIFIED
SELECT B.*, 'MODIFIED'  AS 'CHANGE_TYPE'
FROM (
        SELECT * FROM #T1
        EXCEPT
        SELECT * FROM #T2
    ) S1
INNER JOIN #T2  B ON S1.ID = B.ID;

| Id | OtherData | CHANGE_TYPE |
|----|-----------|-------------|
| 1  | NewRow    | NEW         |
| 2  | ChgRowA   | MODIFIED    |


SQL query using CTE not working after database migration

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

The obvious reason for this error is that the query needed to do recursion of more than 100 levels. So you can try increasing the default of 100 to higher (say 1000) or to no limit with OPTION (MAXRECURSION 0).

Another possibility is to have "bad data" (i.e. cycles in the id - parent_id adjacency list relationship) and not well written recursive CTE that falls into an infinite loop.

For the specific query, a cycle on the id - parent ids, say (AccountId, ParentAccountId) with  values (1,2), (2,3), (3,1) will cause an infinite recursion loop.

I suggest you check the data in the table for cycles in the id - parent_id relationship and correct the query to avoid infinite recursion in any case this happens again.


MDF file full - cannot find the source of its giant size

Identify the top 10 biggest tables/indexes with this as a starter:

SELECT TOP (10) s.name, o.name, ps.row_count,
  used_bytes = ps.used_page_count * 8192,
  reserved_bytes = ps.reserved_page_count * 8192
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.objects AS o
ON ps.[object_id] = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.is_ms_shipped = 0
ORDER BY reserved_page_count DESC;

SQL Server doesn't just occupy 87 GB for nothing - the space is being used somewhere. Maybe it's in a system table:

SELECT TOP (10) s.name, o.name, ps.row_count,
  used_bytes = ps.used_page_count * 8192,
  reserved_bytes = ps.reserved_page_count * 8192
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.all_objects AS o -- change this to all_objects
ON ps.[object_id] = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
-- WHERE o.is_ms_shipped = 0 -- comment this out
ORDER BY reserved_page_count DESC;

Would also be curious to see what size the backup is, and what happens if you try shrink the data file.


Here is one thing I quickly see is missing : Each [mysqld] needs to have its own datadir. No two mysqld groups can share the same datadir. You need to allocate different datadirs in different folders and make sure you run "chown -R mysql:mysql (datadir folder)" on all datadirs.

Your /etc/my.cnf should look something like this:

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = root
password   = ***

[mysqld]
port       = 3306
datadir  = /var/lib/mysql1
socket   = /var/run/mysql/mysql.sock

[mysqld2]
port       = 3307
datadir  = /var/lib/mysql2
socket   = /var/run/mysql/mysql2.sock

Check out this link from the Pythian Group on how to start and stop the individual DB services.

From my answer, you should see that there must be a complete copy of the data in /var/lib/mysql1, /var/lib/mysql2, etc.


Why my BEFORE UPDATE trigger takes values from the NEW record?

Use record type OLD to access the old values as you do NEW to access the new values when updating a record in a table.

Refer: http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html


SQL Server 2008 Database Restore Error - Cannot open backup device 15105

OS Error code 3 is ERROR_PATH_NOT_FOUND: The system cannot find the path specified. With a path starting with Z: my pshyhic powers tell me it is a mapped drive. Drive mapping is session scoped and I'm convinced your service session does not see Z:. Mapping a drive to be seen by a service is, for all practical purposes, impossible.

Use a valid path. Use UNC names like \\server\share. Consider the security implication of impersonation and delegation when accessing remote shares.


t-sql to create tables using a declared variable

You'll need to employ a little semi-advanced dynamic T-SQL for this task.

First, I'll create a small MCVE in tempdb.  In future, please create this part yourself when asking questions here :-)

SET NOCOUNT ON;
USE tempdb;
IF SCHEMA_ID(N'Table') IS NOT NULL
BEGIN
    DROP TABLE [Table].Columns;
    DROP TABLE [Table].Tables;
    DROP SCHEMA [Table];
END
GO
CREATE SCHEMA [Table]
CREATE TABLE [Tables]
(
    TableID int NOT NULL
        CONSTRAINT Tables_pk
        PRIMARY KEY CLUSTERED
    , TableName sysname NOT NULL
    , [Enabled] bit NOT NULL
)
CREATE TABLE [Columns]
(
    ColumnID int NOT NULL
        CONSTRAINT Columns_pk
        PRIMARY KEY CLUSTERED
    , TableID int NOT NULL
        CONSTRAINT Columns_TableID
        FOREIGN KEY
        REFERENCES [Tables](TableID)
    , ColumnName sysname NOT NULL
    , ColumnType nvarchar(150) NOT NULL
    , Nullable bit NOT NULL
    , [Enabled] bit NOT NULL
);
GO

Here, I'm create a single row in [Table].Tables, with two columns a, and b.

INSERT INTO [Table].Tables (TableID, TableName, [Enabled])
VALUES (1, N'a', 1);

INSERT INTO [Table].Columns (ColumnID, TableID, ColumnName, ColumnType, Nullable, [Enabled])
VALUES (1, 1, N'b', N'nvarchar(30)', 1, 1)
    , (2, 1, N'c', N'int', 0, 1)
GO

This is the "interesting" dynamic T-SQL code.  It cross joins to sys.databases so that a CREATE TABLE statement is generated for every database on the instance.  I'm using WHERE database_id > 5 because I don't have an instance with more than 337 databases - so you'll need to modify that to suit your needs.

DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT N'USE ' + QUOTENAME(d.name) + N';CREATE TABLE '
    + QUOTENAME(t.TableName)
    + N'( ' + STUFF((SELECT N', '
        + c.ColumnName
        + N' ' + c.ColumnType
        + N' '
        + CASE WHEN c.Nullable = 1 THEN N'NULL'
            ELSE N'NOT NULL'
          END
    FROM [Table].Columns c
    WHERE t.TableID = c.TableID
        AND c.[Enabled] = 1 FOR XML PATH (N'')), 1, 2, N'') + N');'
FROM [Table].Tables t
    CROSS JOIN sys.databases d
WHERE t.[Enabled] = 1
    AND d.database_id > 5;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

The code will "print" a CREATE TABLE statement for each database, which you could simply copy-and-paste into a new query window to actually execute.  You could replace the PRINT @cmd; with EXEC sys.sp_executesql @cmd; to automatically run each CREATE TABLE statement, however I wouldn't suggest doing that until you're certain this works the way you expect.

Notice, the code above includes the Nullable column - that wasn't in your example code.  It's important to declare the nullability for every column when creating a table.

Also, please don't use reserved words for table names; i.e. don't call a table "Table".  It's frustratingly hard to type and hard to read later.


Issue with Maximo KPI Returning "0" Instead of a Calculated %

I'll add an answer since I believe you can improve the efficiency of your query by doing 1 scan instead of 2:

select 1.0*count(case when ACTFINISH < TARGCOMPDATE then wonum end)
     / count(wonum)
from workorder
where (status = 'CLOSE' or status = 'COMP')
  and (woclass = 'WORKORDER' or woclass = 'ACTIVITY')
  and upper(crewid) = '17D02'
  and istask = 0
  and siteid = 'INDPLS'
  and worktype = 'PM'
  and (targcompdate >= dateadd(month,datediff(month,1,getdate())+0,0))
  and (targcompdate <= dateadd(month,datediff(month,1,getdate())+1,0)-.001)

I guess the original problem is integer division, i.e 1/4=0 while 1.0/4=0.25. If you want the result as a percent, multiply with 100.0 instead of 1.0:

select 100.0*count(case when ACTFINISH < TARGCOMPDATE then wonum end)
     / count(wonum)
from workorder
where (status = 'CLOSE' or status = 'COMP')
  and (woclass = 'WORKORDER' or woclass = 'ACTIVITY')
  and upper(crewid) = '17D02'
  and istask = 0
  and siteid = 'INDPLS'
  and worktype = 'PM'
  and (targcompdate >= dateadd(month,datediff(month,1,getdate())+0,0))
  and (targcompdate <= dateadd(month,datediff(month,1,getdate())+1,0)-.001)


From a purely relational point of view (prior to sixth normal form), I don't see any need to move a set of columns out into a separate table, just because they are frequently null.

As a trivial example, consider a customer account table with an end date as one of the columns - until the customer closes their account, the end date will be NULL. You are therefore likely to have a large number of NULL values in the end date column, yet it would be a bad choice to move this out into a separate table.

However, as an implementation issue, there may be good reasons to split out certain fields into separate tables. For example, consider a customer address table; certain addresses may require complicated delivery instructions, yet the vast majority are not likely to require them. In such circumstances, it would make sense to have a separate table for address delivery instructions, even if it has the same key (customer address ID) as the main customer address table.

(As a side note, some experts on relational theory - such as Chris Date and Fabian Pascal - are opposed to allowing NULLs within relational databases at all.)


Database name not found but I can connect using IBM Data Studio. What happened?

Ahh....That has to do with profiles. Root was not set up by default to have DB2 libraries set up in its profile, so it doesn't recognize the DB2 executables. This is not to say root can't do what you are asking, you would just have to browse to the /instance/bin folder to find the executables you are after. Of course, then you have to worry about whether or not root has permission to connect....

But anyway, you might find it easier to sudo over to the id you want to use to connect (possibly even the instance) sudo - myid. For sure the instance will have DB2 set up in its profile. Your other ID you may have to set it up. Here is an answer I provided before where I showed someone how to set up DB2 for their profile.

I would recommend that you not give root DB2 access through its profile.


There's a way to enforce that Table1.id3 only can seted to values in Table4.id3 where Table4.id2 = Table1.id2?

It seems to me like you could just set a foreign key constraint directly to Table4.

alter table Table1
add constraint your_constraint_name
foreign key (id2, id3) references Table4 (id2, id3);

You might have to jump through a hoop or two to prevent a cycle in the constraints.


Running pg_dump on a hot standby server?

AFAIK, running pg_dump on a hot standby is one of the major things standbys are useful for. It's perfectly safe, though it isn't perfectly reliable - the dumps can fail if the standby aborts the transaction when it's falling too far behind the master.

The only thing you really need to watch is to make sure the standby is current and is keeping up. If the standby lost its connection to master and fell too far behind, you don't want to be merrily backing up a three week out-of-date standby.

You will need to allow the standby to fall quite far behind the master during the backup, since it'll otherwise have to cancel your pg_dump transaction in order to continue replaying WAL. See the documentation on hot standby, particularly the "handling query conflicts" section, and the max_standby_archive_delay and max_standby_streaming_delay parameters.

Note that the master must be willing to keep enough WAL archives to allow the slave to catch up again.


Could once `max_standby_archive_delay` and `max_standby_streaming_delay` with value -1 can cause replication stop on heavy loads?

Yes, that is the idea. In the case of a replication conflict PostgreSQL has only two options:

cancel the query
delay the application of replicated changes.

Setting max_standby_streaming_delay to -1 will delay replication indefinitely long.

There are ways to reduce replication conflicts:

Set hot_standby_feedback = on to remove replication conflicts caused by VACUUM. The price you are paying is that long running queries on the standby can bloat your tables.
Don't have any statements like DROP TABLE, TRUNCATE, ALTER TABLE and similar in your workload that cause ACCESS EXCLUSIVE locks.
In PostgreSQL v12, you can set

ALTER TABLE atable SET (vacuum_truncate = off);

to disable autovacuum truncation (which also causes a short ACCESS EXCLUSIVE lock). In older versions, there is only the crude and undocumented workaround of setting old_snapshot_threshold to something else than the default value.

This is all complicated and may have undesired effects, so the best advice is this: If you want a standby that does not lag more than necessary, but you also want to run long running queries on a standby server, you should use two standby servers, one for each of these conflicting purposes.


Syntax to not write single statement to binlog?

If you do the following:

connect to mysql
perform SET sql_log_bin=0;
execute the query (INSERT, UPDATE, or DELETE)
close the DB connection

then there is no need to SET sql_log_bin=1; afterwards
As an alternative, you may want to try this:

open a second DB Connection
set the SET sql_log_bin=0; (this done once in the lifetime of the second DB connection)
keep that second DB Connection open
use only this second DB connection for writes that are not be recorded in the binary logs

Give it a Try !!!
UPDATE 2012-01-06 14:55 EDT
Here is something interesting you may not have realized: Only those SUPER privilege can set one's own session to disable binary logging.
According to the MySQL Documentation on SUPER:

The SUPER privilege enables an account to use CHANGE MASTER TO, KILL
or mysqladmin kill to kill threads belonging to other accounts (you
can always kill your own threads), PURGE BINARY LOGS, configuration
changes using SET GLOBAL to modify global system variables, the
mysqladmin debug command, enabling or disabling logging, performing
updates even if the read_only system variable is enabled, starting and
stopping replication on slave servers, specification of any account in
the DEFINER attribute of stored programs and views, and enables you to
connect (once) even if the connection limit controlled by the
max_connections system variable is reached.
To create or alter stored functions if binary logging is enabled, you
may also need the SUPER privilege, as described in Section 18.7,
“Binary Logging of Stored Programs”.

Having users with SUPER privilege gives them additional fire power that they may not need or may want to abuse.


Why is this column order in my non-clustered index better for my query?

The first index does look like a better fit for the query. Please provide the actual execution plans.

I would try using window functions instead of the four correlated subqueries. Or a single correlated subquery (with OUTER APPLY) and see which of the two indexes is used.
Both ideas are to coerce the optimizer to use a single index scan to gather the rolling sums instead of 4 (that both your plans do).

It would also be worth checking and comparing the two execution plans, when asking for all the columns and when asking for only the columns in the index:

using window functions:

-- window functions
SELECT
    -- m.*,
    movie_title, start_date_time,
    price, quantity,

    SUM(CASE WHEN price = 0 THEN quantity ELSE 0 END)
        OVER
        (PARTITION BY movie_title
         ORDER BY start_date_time
         RANGE BETWEEN UNBOUNDED PRECEDING
                   AND CURRENT ROW
       ) AS [Admissions Free],
    SUM(CASE WHEN price > 0 THEN quantity ELSE 0 END)
        OVER
        (PARTITION BY movie_title
         ORDER BY start_date_time
         RANGE BETWEEN UNBOUNDED PRECEDING
                   AND CURRENT ROW
       ) AS [Admissions Paid],
    SUM(CASE WHEN price > 0 THEN quantity * price ELSE 0 END)
        OVER
        (PARTITION BY movie_title
         ORDER BY start_date_time
         RANGE BETWEEN UNBOUNDED PRECEDING
                   AND CURRENT ROW
        ) AS [Admissions Revenue],
    SUM(quantity)
        OVER
        (PARTITION BY movie_title
         ORDER BY start_date_time
         RANGE BETWEEN UNBOUNDED PRECEDING
                   AND CURRENT ROW
        ) AS [Total Admissions]
FROM
    [movies] AS m ;

*: If there is a UNIQUE constraint on (movie_title, start_date_time), then you could use ROWS instead of RANGE for the window frames (it's usually more efficient). From the comments, there is no such constraint and there could be many rows with same title and datetime, so RANGE is required above.

using OUTER APPLY:

-- using OUTER APPLY
SELECT
    -- m.*,
    m.movie_title, m.start_date_time,
    m.price, m.quantity,

    c.[Admissions Free],
    c.[Admissions Paid],
    c.[Admissions Revenue],
    c.[Total Admissions]
FROM
    [movies] AS m
    OUTER APPLY
    ( SELECT
          SUM(CASE WHEN i.price = 0 THEN i.quantity ELSE 0 END)
              AS [Admissions Free],
          SUM(CASE WHEN i.price > 0 THEN i.quantity ELSE 0 END)
              AS [Admissions Paid],
          SUM(CASE WHEN i.price > 0 THEN i.quantity * i.price ELSE 0 END)
              AS [Admissions Revenue],
          SUM(i.quantity)
              AS [Total Admissions]
      FROM [movies] AS i
      WHERE i.movie_title = o.movie_title
        AND i.start_date_time <= o.start_date_time
    ) AS c ;

This index may be a little better than the first one:

(
    movie_title ASC,
    start_date_time ASC
)
INCLUDE (price, quantity)


It's old code, but it checks out:

SELECT
    user_name(member_principal_id) as memberName
FROM
    sys.database_role_members
WHERE
    user_name(role_principal_id) = 'db_owner'

So, in full:

    DECLARE @roleName varchar(20) = 'MF_Pilots'
    DECLARE @user varchar (20) = 'Rey'
SELECT CASE WHEN EXISTS
    (SELECT
    user_name(member_principal_id) as memberName
FROM
    sys.database_role_members
WHERE
    user_name(role_principal_id) = @roleName
AND
    user_name(member_principal_id) = @user)
    THEN 1
    ELSE 0
    END

That'll do what you want. Stick it in a function and profit.

Taken from https://stackoverflow.com/questions/3361778/get-list-of-all-database-users-with-specified-role


From the documentation, you can see there's a @body_format option.

  [ @body_format= ] 'body_format' Is the format of the message body. The
  parameter is of type varchar(20), with a default of NULL. When
  specified, the headers of the outgoing message are set to indicate
  that the message body has the specified format. The parameter may
  contain one of the following values: TEXT HTML Defaults to TEXT.

Set it to HTML.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Mod',
    @from_address = 'modis@modisglobal.com',
    @recipients= 'rsmith@gmail.com',
    @subject= 'Test Email',
    @body = @body1,
    @body_format = 'HTML'


MySQL InnoDB transactions with lot of delete instructions

this file ibdata1 also known as system tablespace, which consistent of undo log even if you have seperated tables into different files;

from your process I found it is a really huge transaction, which will produce drampatic quantitiy of undo logs, till MySQL 5.5, undo log space can not be shrink or adjust automatically or manually.

so there is a simple way to prevent you from the huge undo log:

commit your transaction every 300 delete statements.

each next transaction will recycle the undo log space.


In the sqlite3 command-line shell, this output format can be selected with .mode column.


Is there way to have logical connection in MongoDB?

If your client applications are connecting directly to MongoDB, they will typically use a connection string which varies depending on the type of deployment:

Standalone - mongod  hostname (and optionally, port)
Replica set - includes one or more mongod hosts in the replica set as a "seed list" to discover the current configuration
Sharded cluster - includes one or more mongos hosts to connect to the sharded cluster

There isn't a direct equivalent of the "logical connection" abstraction. However, since you are already connecting via mongos, it sounds like you have a sharded cluster which does abstract the connection details for the underlying shards. All mongos connecting to the same cluster have to use the same --configdb list.

If you deploy a mongos per application host, each application can connect to the mongos on localhost. Alternatively you could have a fixed set of shared mongos and expose these via DNS cnames that resolve to "local" mongos for that application or data centre.

If you want to further lockdown per-client access, each client application could authenticate using different credentials which limit their access/permissions per-database. MongoDB 2.6 adds further granularity including collection-level access. For more information see Security Concepts in the MongoDB manual.


Can't rely on INFORMATION_SCHEMA.view_definition?

As Erik mentioned in the article he linked, INFORMATION_SCHEMA is a deprecated schema from the classic days of SQL Server 2000. You should read the article he linked as it goes through the reasons it's deprecated, and cites Microsoft's docs that support those reasons.
You can instead use the following query that leverages the sys.objects view to get the definition of a View:
SELECT m.[definition] AS ObjectDefinition
FROM sys.objects o
INNER JOIN sys.sql_modules m
    ON m.[object_id] = o.[object_id]
WHERE o.[object_id] = OBJECT_ID('dbo.YourViewName')
    AND o.[type] = 'V';

Or the function OBJECT_DEFINITION() to also get the definition as follows:
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.YourView')) AS ObjectDefinition;

One caveat to doing it either way, is it'll contain the last script used to modify the object. So if the View was altered since it's creation, it'll start with the ALTER VIEW statement otherwise it'll have the CREATE VIEW statement.


GUIDs only really cause performance problems for a couple of reasons.

First their size.  You are using a 16 byte identifier where an int(4 bytes) or even bigint(8 bytes) is much smaller.  This doesn't really matter so much on a wide table or short table.  If your table is fairly narrow then the percent increase is of course greater.  A 2000 byte width vs a 2012 byte width is really not that big a deal where a 20 vs 32 is obviously a greater difference.  And if your table is going to be large, millions or even 100's of millions of rows, then that 8-12 bytes you are adding is going to make a more obvious difference than say a table with only 1000 rows.  On the other hand if you need that portability then you may be willing to accept the space issues.

Next are the issues that only occur if the GUID is part of the clustered index.  If the GUID is the clustered index then the GUID will be included the leaf level pages of all non-clustered indexes.  So to compare, a table with an integer clustered index will only add 4 bytes to the leaf page while the GUID will add 16.  This can add up over time particularly with multiple indexes.  It also will of course increase the number of reads to use that index since you can fit less entries per page.  The other problem GUIDs have with a clustered index is that they are not typically in order.  This means potential page splits during inserts.  The link you provided mentioned that this isn't a huge increase over the write times in Azure however.  Typically you could use use NEWSEQUENTIALID() to generate the values as it returns a value greater than any previous value generated by NEWSEQUENTIALID() on that computer since windows was started however it is not supported by Azure.

In my opinion if you are already going to have a CreatedAtUTC column then go ahead and make it the clustered index and use the GUID as your primary key.  Also here is a link I found to a blog by the Azure team on the subject.  Of course he self processes to love GUIDs so he is somewhat biased.  Also another benefit of using your CreatedAtUTC as the clustered index is that it doesn't change.  This means minimal row moves.


If A and B are candidate keys, then AB is candidate key or not ?

The union between A and be is a superkey.

(A candidate key is a superkey but a superkey could be not a candidate key)

Moreover, remember that a candidate key is a particular type of superkey, so you shouldn't worry about if it is "both" key and superkey.


What are the performance implications of running multiple smaller DBs instead of a single larger DB on a server?

As others have indicated in the comments, it's difficult to form an answer to this question without an understanding of the application. It depends, it really really does.

The nature of the question (and answer) also changes on the basis of the physical environments i.e. multiple databases on a single server or spread across several? Is a customer “typical” or do some consume a disproportionate percentage of server resources? In 3 years, will there be 50 customers or 50000?

That said, let’s have a crack at it.

  What would the performance implications be if we consolidated the
  customers into a single DB?

Potential gains

Reduction in plan cache memory. If you have 5 of the same database you have 5 copies of every execution plan.
Improved buffer pool utilisation. Similar to above, any common data that you have in each database exists in the buffer pool for each database.
Improved cpu/memory utilisation. Consolidating multiple servers to a single server removes the overhead of the operating system on each host.
Possibly improved IO utilisation. Combining the smaller arrays allocated to each server might result in improved overall throughput by having higher capacity to deal with peaks.

Potential risks

$$$. Multiple low spec servers are typically cheaper than an all mighty powerhouse. Spreading the load across utility servers can be cheaper.
Expansion flexibility. When you have all customers on that all mighty powerhouse server and it runs out of steam, upgrading is difficult and complex.
Locking/blocking/deadlocks. Any deficiencies in the database and application design are likely to be magnified in a single host environment.

From your comments, it sounds like this is early days for your software and company. So, I’d be looking for ways to maximise flexibility and minimise capital expenditure.

Install an expandable iSCSI array. With separate storage, you can expand both raw GB capacity and IOPS in isolation from the servers.
Make your customers data portable. Might be difficult to retrofit but if you can migrate a customer’s data from one system to another, you can move them between systems to better balance workloads.
Tier your customers. Allocate each customer to a tier depending on their usage, perhaps 3 tiers initially. Analyse typical usage patterns for these tiers of customers and allocate them to server resources accordingly. It may be that you can accommodate 200 tier 3 customers on a single box, or 50 tier 2, or 10 tier 1.

Maybe virtualisation would be a better fit. Maybe cloud would work. Maybe hybrid cloud. Honestly, it really does depend. Call in an expert to help, they could save you a fortune.


Yes, you can run multiple clients on one machine. It's mainly a case of making sure you set your ORACLE_HOME correctly before starting any work.

But you can talk to 10 DBs using the 11 client happily, so that would also work for you.


Try this:

SELECT
  DAY(time_stamp) AS Day,
  COUNT(time_stamp) as Tot,
  SUM(CASE WHEN origin = 's' THEN 1 ELSE 0 END) as Syd,
  SUM(CASE WHEN origin = 'n' THEN 1 ELSE 0 END) as Norr
FROM (select time_stamp, 'n' as origin from ink_norr union all select time_stamp, 's' as origin from ink_syd) x
GROUP BY DAY(time_stamp)
ORDER BY 1;


How to get only those docs which has all the match fields in mongoose

so here I got one solution, which is perfectly apt for my need:

db.getCollection('survey').aggregate([
    { "$match": { "results.active": false } },
    { "$redact": {
        "$cond": {
            "if": {
                "$allElementsTrue": {
                    "$map": {
                        "input": "$results",
                        "as": "result",
                        "in": { "$eq": [ "$$result.active", false ] }
                    }
                }
            },
            "then": "$$KEEP",
            "else": "$$PRUNE"
        }
    }}
])


It seems you want to keep you PHP code simple and make the SQL code more complicated, while it should be otherwise.

Example: lets say that
 - user A chooses: "color = 'red', size = 'Medium', category = any"
 - user B chooses "color = any, size = any, category = 'Tshirts'"

The conditions could be respectively as simple as:

-- userA
WHERE color = $color AND size = $size
-- or the equivalent (if columns are NOT NULL):
WHERE color = $color AND size = $size AND category = category

and for

-- userB
WHERE category = $category
-- or the equivalent (if columns are NOT NULL):
WHERE color = color  AND size = size  AND category = $category

I don't think it's so difficult to write PHP code that produces those conditions when a user selects some "any", no matter how that "any" is coded in your PHP/web form.

But if you really want to pass that to MySQL, you can do something like this. Pass the "any" as a fixed value (say 'any') and use:

WHERE (color    = $color    OR $color    = 'any')
  AND (size     = $size     OR $size     = 'any')
  AND (category = $category OR $category = 'any')


SQLite - Remove duplicates across multiple columns

WITH RECURSIVE cte AS
( SELECT c1, c2, c3, 1 level
  FROM test
UNION ALL
  SELECT cte.c1, t2.c2, GREATEST(cte.c3, t2.c3), level+1
  FROM cte
  JOIN test t1 ON cte.c2 = t1.c2
  JOIN test t2 ON t1.c1 = t2.c1
  WHERE t2.c3 != cte.c3
    AND level < ( SELECT MAX(c3)
                  FROM test )
UNION ALL
  SELECT t2.c1, cte.c2, GREATEST(cte.c3, t2.c3), level+1
  FROM cte
  JOIN test t1 ON cte.c1 = t1.c1
  JOIN test t2 ON t1.c2 = t2.c2
  WHERE t2.c3 != cte.c3
    AND level < ( SELECT MAX(c3)
                  FROM test )
)
SELECT cte.c1, cte.c2, MAX(cte.c3) c3
FROM cte
JOIN test ON (cte.c1, cte.c2) = (test.c1, test.c2)
GROUP BY c1, c2
HAVING c3 = MAX(test.c3);

fiddle


Errcode: 13 means Access denied.  Assuming MySQL is trying to create a file in /tmp, which is 777 I guess this is incorrect configuration of either selinux or apparmor (on Debian systems).

Try to disable selinux

    echo 0 >/selinux/enforce

If it doesn't help, stop apparmor (if you run Debian or Ubuntu)

    # /etc/init.d/apparmor stop
    # /etc/init.d/apparmor teardown


Chained MySQL replication from Master->Master replicated pair

To answer my own question, the "fix" is to change binlog_format to STATEMENT. It was previously MIXED.

Not entirely sure of the reason behind this - MySQL should be intelligent when using MIXED mode about what is replicated as statement and what is replicated as row.


Oracle: Not able to get value from All_Synonyms in PL/SQL

You shouldn't need any special privilege to view the ALL_* views.

If the procedure is owned by and is running as USER1 then it could be that USER1 has not been granted access to any synonyms.

If the procedure is owned by a user other than USER1 it could be that that user hasn't been granted access to any synonyms.

It all comes down to who's credentials are actually being used to run the procedure - yours, USER1, procedure owner - and what synonyms or grants to synonyms that user has.


SSIS slow performance to insert short rows

There are a lot of things that come into play in regards to the performance difference.

1) is hardware\Instance configuration (RAM, Disk IO speed, Disk Size, Block Size, SQL-server memory configurations, Other software and tasks running)

2) Business of the server, If their are concurrent operations that it your tasks is awaiting resource access to.

3) Query Optimization.  Perhaps you are doing a full table scan on the 58 bytes but the 2MB is fully contained with in an index.  also perhaps there is more repetitive data in the 2MB.  If you have built the Select Statements in T-SQL copy them into SSMS and look at the execution plans.

Perhaps there are differences between 2012 and 2014 but I haven't seen huge performance differences in moving my packages to 2014 yet.


Speed up PostgreSQL logical decoding plugin

See this comment from fmgr.h:

/* These are for invocation of a function identified by OID with a
 * directly-computed parameter list.  Note that neither arguments nor result
 * are allowed to be NULL.  These are essentially fmgr_info() followed by
 * FunctionCallN().  If the same function is to be invoked repeatedly, do the
 * fmgr_info() once and then use FunctionCallN().
 */

extern Datum OidFunctionCall0Coll ...

This should be way cheaper than SPI.


Restore specific MSSQL backup from media set using AWS rds_restore_database

Seems from the documentation that the procedure doesn't support specifying which file within a .bak you're actually trying to restore. The native RESTORE DATABASE command allows you to specify this using FILE = .
I think the solution in your case is to always back up any database to a single .bak file (name the backup file uniquely, and always use WITH INIT when backing up). Combining multiple backups into a single file doesn't have a single benefit that I can think of, especially combining multiple backups of different databases.
To get to the backup you want, you may have to download the multi-file backup file, restore it on your own instance using RESTORE DATABASE ... WITH FILE =, back that up to a new, single-backup .bak file, and upload it back to AWS. But I would consult with AWS support in case they have any other (easier) options.


Does Cassandra CQL provide a command to generate an execution plan?

You can enable tracing of the CQL queries, and see how it's executed. In CQLSH it's done with command TRACING ON;, after you finish set TRACING OFF;.

This blog post provides more detailed description of it.


NEXT SYSDATE +1 means the materialized view will be automatically refreshed every day at the same time when the view is created.

In order to refresh the materialized view every 6 hours use NEXT SYSDATE +6/24

DISABLE QUERY REWRITE indicates that the materialized view is not eligible to be used by the query optimizer to rewrite sql queries on the base tables.


How can I prevent Pentaho DI/Spoon from deadlocking?

Noob error!  Pentaho "hops" are not sequential; they will all run simultaneously.

The correct way to handle this type of process - read data, write data, and then manipulate the data in SQL - is to put the read/write in a transformation by itself, and then put that transformation into a job with the SQL procedure(s) following the transformation.


Should DB_RECOVERY_FILE_DEST_SIZE be smaller than the respective partition size?

10 GB as 10240 MiB?

10 GB as 10000 MB?

Is it 10 GB because of the rounding, but the actual size is 10227 MiB? Or 10213 MiB?

Setting db_recovery_file_dest_size to 10G means the size of FRA will be 10*1024*1024*1024 = 10737418240 bytes. As long as that is less than or equal to the available space in the filesystem (not partition!), it should be fine.

I usually set db_recovery_file_dest_size a bit smaller than the amount of free space in the filesystem or ASM diskgroup if I get a dedicated filesystem or diskgroup for it.


Oops, I forgot to put the code I used, sorry about that. Here is the code that worked for me:

  SELECT

v_FullCollectionMembership.Name as [Computername],
v_Collection.Name as [Collections With Maintenance Windows],
v_ServiceWindow.Description as [Maintenance Description],
v_ServiceWindow.StartTime as [Maintenance Window Start Time],
v_ServiceWindow.Duration as [Maintenance Window Duration],
v_GS_OPERATING_SYSTEM.Caption0 as [Operating System]

FROM

v_ServiceWindow
Join
v_Collection on v_Collection.CollectionID = v_ServiceWindow.CollectionID
Join
v_FullCollectionMembership on v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID
Join
v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID = v_FullCollectionMembership.ResourceID
Join
v_R_System on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID

WHERE

(v_GS_OPERATING_SYSTEM.Caption0 Like '%Server%')
or
(v_FullCollectionMembership.Name Like 'WN%')

ORDER BY

[Computername]


If you can manually change the encoding (to one that includes the Byte Order Mark) and have it work such that no characters are lost, then the file itself is fine.

It seems that the tablediff utility does not allow for specifying the encoding type of the output file, so you are left with two options:

Configure SSIS to specify that script as being UTF-8 rather than relying upon the default encoding (which is probably ANSI). If that doesn't work, trying reading the script as "Unicode" (which is really UTF-16 Little Endian, which is how Windows & SQL Server store strings).
Find a command-line utility that can convert the file encoding to UTF-8 (either unspecified or with BOM). This is certainly not the preferred approach ;-).


In MySQL's InnoDB, you may have to start using SavePoints.
SAVEPOINT
Sets a named transaction savepoint with a name of identifier. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set.
ROLLBACK TO SAVEPOINT
Rolls back a transaction to the named savepoint without terminating the transaction. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.) Savepoints that were set at a later time than the named savepoint are deleted.
RELEASE SAVEPOINT
If the ROLLBACK TO SAVEPOINT statement returns the following error, it means that no savepoint with the specified name exists:

ERROR 1305 (42000): SAVEPOINT identifier does not exist

The RELEASE SAVEPOINT statement removes the named savepoint from the set of savepoints of the current transaction. No commit or rollback occurs. It is an error if the savepoint does not exist.
CAVEAT
All savepoints of the current transaction are deleted if you execute a COMMIT, or a ROLLBACK that does not name a savepoint.
A new savepoint level is created when a stored function is invoked or a trigger is activated. The savepoints on previous levels become unavailable and thus do not conflict with savepoints on the new level. When the function or trigger terminates, any savepoints it created are released and the previous savepoint level is restored.
EPILOGUE
Given this explanation of using SavePoints, you answer may be as simple as issuing a SavePoint in appropriate places:
START TRANSACTION;
SQL to Add User
SAVEPOINT addedsuersalready
SQL to Add Other Things
SAVEPOINT addedsuersalready
SQL to Add Additional Things
COMMIT;

This may help mitigate some issues with ROLLBACKs.
Why do most CMS products not allow for such mechanisms? Most CMS products use the dreaded MyISAM storage engine.
For example, Drupal and WordPress usually install MyISAM and plugins usually expect MyISAM tables. I have written posts about recommending the switch to InnoDB.

Apr 22, 2011 : Wordpress Database Slow - should I switch to InnoDB?
Apr 15, 2011 : What would be the optimal MySQL configuration for a Drupal 7 site?

What's even worse is that more recent plugins for both WordPress and Drupal still make table changes and leave them as MyISAM. Any CMS that uses FullText Indexes cannot convert tables from MyISAM to InnoDB in MySQL 5.5 and prior. MySQL 5.6 just become GA on Feb 05, 2013 and does support FullText Indexing. Give MySQL 5.6 some maturation time before diving in.


Compare a date withing range of date fields in mysql

If you allow partial overlapping (not full include only as your code did) then you must compare (start1 with end2) and (start2 with end1):

where leave_end_date >= '2019-09-01'
  and leave_start_date <= '2019-09-31'

If you need the length of overlapping period then you need in

select datediff(greatest(leave_start_date, '2019-09-01'),
                least(leave_end_date, '2019-09-31'))


Postgres: Is migrating old records to separate table and creating new aggregated records a good strategy to improve search speed?

it's better to use partitioning, e.g. one table per month. You can then truncate archive tables which is an instant operation which frees disk space, or move them to a tablespace on a cheaper device and/or replace them with an aggregate row in an aggregates table. The table seen by your app will usually be a view of the union of the monthly tables. You need careful index design though.

You could also calculate a rolling (cumulative) sum in real time with a trigger.
On the other hand doing deletes and updates on the same table will not reduce the on-disk size of your db and may impact performance. But if that is an issue depends on the specifics: writes/selects/updates/sec, row size, drive hardware, indexing, memory etc. You don't mention any specifics.


Removing the limit 1 in  the sub query fixed the problem. More discussion on S.O.


How to use a record variable in the from clausule

Short answer (edited)

You can write FROM (select p_country.*) some_alias instead of FROM unnest(array[p_country])

Question code with minimal corrections: https://rextester.com/UGKJR74935

Original answer (with many usefull suggestions)

Looks like you want something like this:

create function calculate_info(p_country country)
   returns calculate_info_type
   language SQL as
$SQL$
   select case when has_prefix then iso||'-'||prefix else iso end as super_code,
          case when has_prefix then name else name||' without phone prefix' end as super_name
   FROM (
      select (p_country).*, p_country.prefix is not null as has_prefix
   ) as c
$SQL$;

But you don't really need the from at all:

create function calculate_info(p_country country)
   returns calculate_info_type
   language SQL
as
$SQL$
   select concat_ws(',', p_country.iso, p_country.prefix) super_code,
          case
            when p_country.prefix is not null then p_country.name
            else p_country.name||' without phone prefix'
          end as super_name;
$SQL$;

concat_ws will return only the value from the iso field if prefix is null.

You don't need a lateral to use that function:

select iso, prefix, name, (calculate_info(country)).*
from country

Your updated example: https://rextester.com/GVLK52206


MySQL: Should I keep a running total column in one table instead of summing a second table?

SELECT username, email, achID, SUM(points)
FROM users
NATURAL JOIN userAchievements
NATURAL JOIN achievements
WHERE username = 'johnb'
GROUP BY username, email, achID
WITH ROLLUP
HAVING GROUPING(username) + GROUPING(email) + GROUPING(achID) IN (0, 3)

fiddle

  is there a way to get the output like this? 3 columns "username | email | SUM(points)" with 1 row of "johnb | john@aol.com | 15"

SELECT username, email, SUM(points)
FROM users
NATURAL JOIN userAchievements
NATURAL JOIN achievements
WHERE username = 'johnb'
GROUP BY username, email;


This is just Standard SQL join syntax with the optional parentheses removed:

SELECT *
FROM tableC
LEFT JOIN
(
    TableB
    RIGHT JOIN TableA
        ON TableA.ID = TableB.ID
)
ON TableB.TypeID = TableC.TypeID

If you don't like the syntax generated by the SSMS view designer (which is buggy and rarely updated anyway), simply write the views by hand using regular T-SQL.

Some people refer to this syntax style as 'nested join syntax'. Example blog post.


Is this application creating stored procedures on the fly?

sys.dm_exec_sql_text presents the full text of the procedure including it's CREATE PROCEDURE command.

To get the actual query that was executed, you'll need to join to sys.dm_exec_query_stats, where the start/end offset values of the procedure's SQL are stored.

SELECT
    deqs.execution_count,
    deqs.last_execution_time,
    SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                        ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                              ELSE deqs.statement_end_offset
                            END - deqs.statement_start_offset)/2)+1) AS statementtext
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest


How to use a variable date when running a db2 command in a shell script?

Shell quotes typically delimit arguments or escape other special shell characters from shell interpretation. You can contain (and therefore escape) different kinds of shell-quotes within other kinds based on various rules:

"''''" - a soft-quoted string can contain any number of hard-quotes.
"\"" - a \ backslash can escape a "soft-quote within a "soft-quoted string.
In this context a \\backslash also escapes itself, the \$expansion token, and \newlines as noted below, but is otherwise treated literally.
"${expand} and then some" - a soft-quoted string can contain an interpreted shell $expansion.
'"\' - a 'hard-quoted string can contain any character other than a 'hard-quote.
\ - an unquoted backslash will escape any following character for literal interpretation - even another backslash - excepting a \newline.
In a \\newline case both the \ backslash and the \newline are completely removed from the resulting interpreted command.
${parameter+expand "$parameter"} - quotes resulting from a shell expansion almost never serve as delimiter markers excepting a few special cases. I won't venture to describe these further here.

Test:

root@onare:/home/onare# echo $day0_T1
2015-07-16 14:10:44
root@onare:/home/onare# db2="something '${day0_T1}'"
root@onare:/home/onare# echo $db2
something '2015-07-16 14:10:44'
root@onare:/home/onare#


PostgreSql - Search cost with compound selection

The cost of performing an SQL query depends on many factors. The way to really know is to ask the database to actually perform the query and measure it:

That would be done with the EXPLAIN command:

EXPLAIN ANALYZE VERBOSE SELECT * FROM my_table WHERE Name='Tom' AND age=18;

The database will output the execution plan that it decided that was the best (with the given configuration settings), together with the estimated and actual costs and row counts.

If the query is asumed to take a long running time, or you just want to know which is the execution plan that the database would execute, you can just use

EXPLAIN SELECT * FROM my_table WHERE Name='Tom' AND age=18;

This will just output estimated costs and number of rows.

For your specific case:

If the database doesn't have an index on columns Name nor age, it will be forced to perform a complete sequential scan (that is, it will have to look all the rows in the table, and take only the ones where the condition Name='Tom' and age=18 is met).

If the database has one index on either the Name or the age columns, and the number of rows in the table is big, and the number of rows where Name is 'Tom' or age is 18 is (according to the statistics collected by it) is comparatively low, it will use this index to fetch only the rows where one of the conditions is met, and check whether the other condition is also met.

If the database has one multi-column index containing both columns as the first two (or the only two), such as (age, Name, ...) or (Name, age, ...) it will be able to fetch the rows by finding the rows it must retrieve by just using this index.

What the database actually does (by means of its query planner/optimizer) is: check all (or a reasonable number of) the possible execution plans to retrieve the information; make an educated guess at how long it will take to execute each of them, and choose the fastest (cheapest in terms of cost). Some parameters in the database configuration may further limit the plans which can be considered.


How to prevent a column from repeating in the final result when using the LISTAGG function

You want to see employee name out of aggregated professions list - so you must concatenate it to aggregating expression, not to separate profession in arguments.
If do this directly
select cust_number,
       '['||MAX(cust_name)||':'||listagg(cust_profession,',')  within group (order by cust_number)||']' Cust_profession
from CUSTOMER_PROFESSION
group by cust_number;

then cust_name become non-grouped, and Oracle produces an error "ORA-00979: not a GROUP BY expression".
This can be solved either by wrapping this column with aggregated function, for example, MAX():
select cust_number,
       '['||MAX(cust_name)||':'||listagg(cust_profession,',')  within group (order by cust_number)||']' Cust_profession
from CUSTOMER_PROFESSION
group by cust_number;

or by adding this column into grouping expression (preferred):
select cust_number,
       '['||cust_name||':'||listagg(cust_profession,',')  within group (order by cust_number)||']' Cust_profession
from CUSTOMER_PROFESSION
group by cust_number, cust_name;

fiddle


mysql Wrong bytesec: 0-0-0 at linkstart: 0

MY CONDOLENCES
MyISAM only caches index pages in the MyISAM Key Buffer. MyISAM never caches data. Notice how the messages from CHECK TABLE is only mentioning the datafile (that's the .MYD file) .
Since MyISAM data is fully reliant on the OS, rows of data are cached only in the OS. The MyISAM tables that are corrupt had open file handles when the crash happened (doesn't matter if mysqld crashed or the OS that crashed)
Recovery of the data is not possible.
FUTURE CONSIDERATIONS
Switching to InnoDB
In the future, please use InnoDB for all tables. InnoDB has a higher tolerance for crashes because it has transaction logs and double write buffers to recover data that was committed but not yet written as well as rollback segments and undo logs for rolling back uncommitted transactions.
Still Want to Use MyISAM

You should backups of the MyISAMs regularly
You should keep binary logs enabled, even if you don't use Replication. That way, you could use backups of the MyISAM tables and use the binary logs to replay INSERTs, UPDATEs, and DELETEs up to the point before the crash.
You should setup MySQL Repication so that the Slaves have good copies of the same tables

Give it a try!!!


Sum Up values in a query based on other information

Here is a solution that uses analytics to prevent having to do two scans of mv_aeven.  It does require more CPU to save the disk access and is a bit more complex, so you will have to weigh it accordingly.

SELECT * FROM
(
  SELECT a.eventnum, sum(a.num_cust), sum(a.out_minutes), a.off_dts, max(a.restore_dts)
     , a.cause, a.feeder, a.DEV_NAME
  FROM mv_outage_duration a
  WHERE a.DEV_NAME = 'T007F12127'
  GROUP BY a.eventnum, a.off_dts, a.cause, a.feeder, a.DEV_NAME
) a
INNER JOIN
  (
  SELECT Num_1, xpers, weather_code, completion_remarks FROM
     (
     SELECT Num_1, xpers, weather_code, completion_remarks, rev_num
        , max(rev_num) OVER (PARTITION BY Num_1) max_rev
     FROM mv_aeven
     )
  WHERE rev_num = max_rev
  ) d
ON d.Num_1 = a.eventnum;

SQL Fiddle (Includes column type definitions).


Cluster network name resource 'Cluster Name' failed registration

I have come across this issue with my dev environment usually when during the setup of the cluster, i skip the warning for network binding. When you run a cluster validation, do you receive any warnings or errors on the network.
Anyways this link fix my issue. @Amr provided the solution to issue.
https://social.technet.microsoft.com/Forums/ie/en-US/c77c0b69-1f9d-4467-a0dd-6844e87e2d13/cluster-name-failed-to-update-the-dns-record?forum=exchange2010

Cause:

The cluster name resource which has been added to the DNS prior to setup active passive cluster ( or any type) need to be updated by the Physical nodes on behalf of the resource record itself. When the active node owns the resources it want to update the A record in the DNS database and DNS record which was created won’t allow any authenticated user to update the DNS record with the same owner

Solution:

Delete the existing A record for the cluster name and re-create it and make sure select the box says “Allow any authenticated user to update DNS record with the same owner name “Don’t worry about breaking anything , this has “ZERO” impact to cluster simply delete the A record and re-create as it is suggested here.

http://amradmin.wordpress.com/2011/01/27/event-id-1196-1119-dns-operation-refused-cluster-servers/


Handling case-insensitive queries with case-sensitive data

SQL text and string data has a concept called "collation". Collations define how character sets compare and sort. Almost every database out there has the concept of "case-sensitive collation" vs. "case-insensitive collation". The collation used for comparison, search and sort can be changed on-the-fly (at a significant runtime cost usually) on a per-query basis. More often the collation is chose during development and defined at the database/table/column level and then the engine will use the chosen collation to do search/comparison/sorting of strings/text.

The specifics of how you define the collation to use will depend on your platform of choice.