EAV is a solution when the values to be stored cannot be identified during design-time but are captured at run-time. It sounds like your situation is slightly different - you know all the values to be captured but not how to store them.

One relational approach is to use entity inheritance. This is what you describe with generic_input and potato_input etc. Since each crop will have exactly one row in the generic table and exactly one row in a single specific table this inheritance can be flattened into a view for consumption by the models.

If you don't know until run-time which values each model will consume you can introduce a model_attribute table to list each model's requirements. Use dynamic SQL to construct the SELECT from the above flattened view.

The data could be stored in general-purpose columns. The input table then becomes create table input(crop, value_1, value_2, value_3, .. value_n). Further meta-data maps crops to columns' meanings e.g. "for wheat value_1 is planting date; for poppies value_1 is flowering date". This design is good if you really like logic puzzles and debugging.

It is only a small step to unpivot that table to get input(crop, value_number, value). Then substitue the column meanings for value_number to give input(crop, attribute, value) and you have EAV.

My experience with EAV is that it is really rubbish for queries with predicates ("select planting_date where flowering_date = 1st August") but are fine for retrieving all values for a thing ("select * from EAV where entity_id = xyz"). It's left to the application to make sense of the bucket of values that are returned, but it will do a better job than SQL.

EAV is a dynamic schema solution. Others that are better supported by DBMS are XML and JSON. These allow each row of a table to hold values that other rows don't. Depending on the DB software the JSON/XML can be parsed as part of the SQL query. You don't say, but maybe your models accept inputs in one of these formats, too? Of the two JSON is more compact and XML has a richer parsing & query ecosystem.


http://sqlfiddle.com/#!9/c88a4/8

select t1.id, t1.t, time_to_sec(t1.t) s
from times t1
left join times t2 on t1.id = t2.id
  and time_to_sec(t1.t) > time_to_sec(t2.t)
  and abs(time_to_sec(t1.t) - time_to_sec(t2.t)) <= 20
where t2.id is null
group by t1.t, t1.id

uses "groupwise-max" method with dynamicaly defined group. Seems to work on your testing data.


When you issue a WAITFOR command, the task will start having a wait_type of WAITFOR.  This is a benign wait type and can be ignored.

Likewise, when this task enters the suspended state it will get off of the scheduler (processor) so it won't be taking up worker time.  We can see this with a simple example:

use AdventureWorks2012;
go

waitfor delay '00:10:00';
go

In another window you can run the following:

select
    session_id,
    command,
    status,
    wait_type
from sys.dm_exec_requests
where command = 'waitfor';

My results are as follows:

session_id  command   status      wait_type
54          WAITFOR   suspended   WAITFOR

As for your question of how this will reflect in sys.dm_os_wait_stats, upon the WAITFOR wait type's completion, it will then increment the WAITFOR type in sys.dm_os_wait_stats for the duration that the task was waiting for:

select *
from sys.dm_os_wait_stats
where wait_type = 'waitfor';

But as said previously, the WAITFOR wait type is benign and can generally be ignored.


You should not be concerned on how SQL Server stores the data. For now, you should use the TIME data type and if you want the 12 hour format, then convert it in the front end application or during SELECT statement using CAST() or CONVERT()


MySQL replication : Query partially completed on the master (error on master: 1053)

If the query was OPTIMIZE TABLE table_name, there is no worry about data inconsistency.
You can run SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; to skip the error on the slave, and re-run the optimization on each server by itself if needed. Use LOCAL so the statement is not replicated:

OPTIMIZE LOCAL TABLE table_name


Providing SQL access to flat file (proprietary) database

Possible solutions:

Use SSIS package registered as a view.
Downside of this option is that any where conditions are not pushed down to SSIS, so dotNet will have to read all the data. Of course you can create multiple views - like last day only, aggregated values, etc.
Walkthrough: Publish an SSIS Package as a SQL View
Use a CLR Table-Valued Functions
This way you can pass parameters to underlying dotNet code
Use PolyBase.
This would require a lot of added infrastructure and will allow you to split processing the file to multiple servers (or in Azure).

On the other hand I don't think that compression you are using will be much better than columnstore compression, so I would advise to reevaluate storing this in SQL Server itself.


Storing html in mysql text fields, or include files? (html formatted articles)

I would recommend you put as little HTML in your database as possible. One very popular design pattern for websites is the Model-View-Controller pattern.

Using that pattern your data (author, title, categories, article content, etc.) would be stored in the database without HTML tags and be called the Model.  Then the structure of the HTML page would be stored in a file called the View.  The Controller would be in charge of accepting the HTTP requests, requesting the appropriate data from the database, and merging/sending that data with/to the view.

Any common web programming platform should be able to accommodate this pattern.


How to set password for PostgreSQL database with a script?

From the documentation:

  createdb is a wrapper around the SQL command CREATE DATABASE.
  There is no effective difference between creating databases via this
  utility and via other methods for accessing the server.

This means that you can write a script which issues a command like

psql -f createdb.sql -v passwd=$passwd -v user=$user

Where createdb.sql contains something like

CREATE ROLE :user WITH LOGIN PASSWORD :passwd;
CREATE DATABASE mydb OWNER :user;
GRANT {some privileges on the newly created database and/or its objects};

This means that you will have a user for every database created.

(At the same time I have a mild suspicion that your problem is what riouj answered.)


Given the link from your question, look at the table mysql.proxies_priv
mysql> show create table mysql.proxies_priv\G
*************************** 1. row ***************************
       Table: proxies_priv
Create Table: CREATE TABLE `proxies_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Proxied_host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Proxied_user` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `With_grant` tinyint(1) NOT NULL DEFAULT '0',
  `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User proxy privileges'
1 row in set (0.00 sec)

mysql>

Since it is a MyISAM table, you could just truncate the table.
In case you need it back, make a backup of it and truncate it after.
ALTER TABLE mysql.proxies_priv RENAME mysql.proxies_priv_backup;
CREATE TABLE mysql.proxies_priv LIKE mysql.proxies_priv_backup;

Then, go restart mysql
service mysql restart

A far simpler way would be to use the REVOKE command:
REVOKE PROXY ON user
    FROM user [, user] ...

Due to the warning, you would have to restart mysql to make sure the warning does not come back.
Give it a Try !!!


pgAdmin III can show information about all connections by using the Tools > Server Status. This option shows a window with four panes:

Acitivity (showing all connections, with PID, application name, database, user, etc). In this pane, you can select one connection and either Cancel the query or Stop the backend by means of two little orange and red buttons. These correspond to pg_cancel_backend and pg_terminate_backend PostgreSQL function calls.
Locks
Prepared transactions
LogFile

pgAdmin III, when choosing a Database Cluster from the Object Navigator, will show, under the Statistics tab, all connections with their running queries.

pgAdmin 4, on the other hand, does not have a Tools > Server Status menu. Instead, it's got a Dashboard tab, which has the same functionality, except for three things:

The Sessions tab (equivalent to the Sessions pane in pgAdmin III) does not have any buttons. So, the functionality to cancel a backend is by means of the GUI is not there.
THe LogFile functionality is not present. I assume this is due to the new architecture of pgAdmin 4, that works as if you were remotely connecting through a web server, which is supposed to not have access to local files, but only to the database. The database itself writes on the log files, but it does not read from them.
There is also a Configuration tab, shown when selecting the database cluster, showing all values from postgresql.conf (through database calls, I assume).

The statistics tab is also present, with euivalent functionality.

NOTES: Checked on pgAdmin III v 1.22.2  and pgAdmin 4 v 1.4


Navicat Premium doesn't show/list tables in postgres database schema

Ran into this same problem today after upgrading to Postgres 12. An update of Navicat to the current latest version (12.1.27) solved the issue for me.


The concept of cardinality does not change whether it is used in reference to the Oracle database or some other SQL database. Cardinality is the size of a set of some elements. From the mathematics point of view a set contains only distinct elements. When you reference values of a certain column as a set its cardinality is the number of unique values. When you reference rows returned by an operation they all comprise the result set (each has a unique row ID, to put it simply) and therefore the result set cardinality is the number of rows returned.


You can easily do that without using PIVOT. Your data:

with data as
(
  select 1 as lot_no, 1 as mcid, 1 as info2, 111 as info3, date'2015-01-01' as dat from dual union all
  select 1, 1, 2, 222, date'2015-01-02' from dual union all
  select 2, 2, 1, 333, date'2015-01-03' from dual union all
  select 2, 2, 2, 444, date'2015-01-04' from dual
)
select * from data;

    LOT_NO       MCID      INFO2      INFO3 DAT
---------- ---------- ---------- ---------- -------------------
         1          1          1        111 2015-01-01 00:00:00
         1          1          2        222 2015-01-02 00:00:00
         2          2          1        333 2015-01-03 00:00:00
         2          2          2        444 2015-01-04 00:00:00

Now the result:

with data as
(
  select 1 as lot_no, 1 as mcid, 1 as info2, 111 as info3, date'2015-01-01' as dat from dual union all
  select 1, 1, 2, 222, date'2015-01-02' from dual union all
  select 2, 2, 1, 333, date'2015-01-03' from dual union all
  select 2, 2, 2, 444, date'2015-01-04' from dual
)
select
  lot_no,
  mcid,
  max(decode(info2, 1, info3, null)) as info21,
  max(decode(info2, 2, info3, null)) as info22,
  max(dat)
from
  data
group by
  lot_no,
  mcid
;

    LOT_NO       MCID     INFO21     INFO22 MAX(DAT)
---------- ---------- ---------- ---------- -------------------
         1          1        111        222 2015-01-02 00:00:00
         2          2        333        444 2015-01-04 00:00:00

This is simply a GROUP BY and picking the need value for INFO21 and INFO22 by transforming unneeded values into NULL, then keeping the needed value with MAX (because we need to use an aggregate function here).

(Section below added by question originator)

While trying to understand the above answer, I found it useful to study the query below as an intermediate step.

with data as
(
  select 1 as lot_no, 1 as mcid, 1 as info2, 111 as info3, date'2015-01-01' as dat from dual union all
  select 1, 1 , 2, 222, date'2015-01-02' from dual union all
  select 2, 2 , 1, 333, date'2015-01-03' from dual union all
  select 2, 2 , 2, 444, date'2015-01-04' from dual
)
select
  lot_no,
  mcid,
  decode(info2, 1, info3, null) as info21,
  decode(info2, 2, info3, null) as info22,
  --max(dat)
  dat
from
  data
;

This gives,

LOT_NO  MCID    INFO21  INFO22  DAT
1   1   111     1 Jan 15
1   1       222 2 Jan 15
2   2   333     3 Jan 15
2   2       444 4 Jan 15

This is before the "group-by" used in the answer above.

It's also useful that null values are automatically excluded, by ORACLE aggregate functions like MAX. Makes it easy to ignore them when merging rows.


At my prodding @buckley already provided a query from which you can derive information. I'll add that you can capture snapshots of this DMV periodically so that you capture things even if they have been pushed out of the cache since their last use. I won't get into how to maintain an archive of DMV data here; maybe a good blog post in the making.

Once you have the data (or just looking at the current plans that are cached), some common hints you can derive from the XML returned by sys.dm_exec_query_plan.query_plan:

    <QueryPlan      NonParallelPlanReason="MaxDOPSetToOne"
    ---- this query used OPTION (MAXDOP 1) ^^^^^^^^^^^^^^
    ---- or perhaps was forced using server maxdop settings or resource governor

    <IndexScan Ordered="0" ForcedIndex="0" ForceScan="1" NoExpandHint="0">
    ---- this query used WITH (FORCESCAN) ------------^

    <IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="1">
    ---- this query used WITH (NOEXPAND) ------------------------------^

    <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="1" ...>
    ---- this query used WITH (FORCESEEK) ------------------------------------^

    <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="1"
    ---- this query uses WITH (FORCESEEK(index(col))) ----------^-------------^

    <IndexScan      Ordered="0" ForcedIndex="1" ForceScan="0" NoExpandHint="0">
    ---- this query used WITH (INDEX = ...) -^

I am NOT an XML guy so I have no clue how to tell you to extract that information dynamically. (More on this below.)

I can't see any way to determine whether isolation has been set (at the batch level or using things like WITH (NOLOCK)) - you might have to:

capture those in the act by polling sys.dm_exec_requests (though transaction_isolation_level really only tells you about things like SET TRANSACTION ISOLATION LEVEL, not table-level hints, since you can apply NOLOCK only to some tables in the query), or
rely on parsing of the query text itself from sys.dm_exec_sql_text (noting that this can yield false positives if, say, NOLOCK is commented out, or you have a table name like dbo.SubmitNoLocks).

I also can't see any difference in the plan for query hints like OPTION (FORCE ORDER) or OPTION (FAST n), so likewise these may also need to rely on parsing the query text as opposed to relying on the plan XML.

EDIT

Since you can get a non-XML version of the plan from an alternate DMF, sys.dm_exec_text_query_plan, you don't necessarily have to become an XML guru to perform this parsing. For example, the following will capture all query plans in the cache that used WITH (INDEX...), WITH FORCESEEK[(...)], WITH FORCESCAN, WITH (NOEXPAND) or WITH (NOLOCK):

SELECT t.[text]--, qp.query_plan
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle, 0, -1) AS qp
WHERE
 t.[text] NOT LIKE '%dm_exec%' -- to keep this query out of result
 AND
 (
  qp.query_plan LIKE '%ForceSeek="1"%'
  OR qp.query_plan LIKE '%Forcescan="1"%'
  OR qp.query_plan LIKE '%NoExpandHint="1"%'
  OR qp.query_plan LIKE '%ForcedIndex="1"%'
  OR t.[text] LIKE '%NOLOCK%'
 )
 -- t.[dbid] = DB_ID() -- to limit results, but may be too exclusionary
;

As identified earlier, this could produce false positives for queries like this:

SELECT * FROM dbo.table /* WITH (NOLOCK) */
SELECT * FROM dbo.RenoLocksmiths;

But outside of parsing the query text for NOLOCK, for all the plan attributes - since the XML gets entitized and characters like quotes get escaped - it will not return false positives for queries like this:

SELECT * FROM dbo.table /* Forceseek="1" */
SELECT * FROM dbo.[WITH (FORCESEEK)];


Page Writes too high but normal values on PLE and BCHR?

Page write is just an indicator of logical writes as well as physical writes.
Logical write is an operation that happens when the page is being written in the buffer(dirty buffer) and physical write is an operation that happens when the same dirty buffer is converted to clean buffer and is being written on the physical disk.

Definitely, this is not a single metric to judge memory pressure. You need to combine different metrics for coming to a conclusion.

But in your case, it doesn't seem to be a problem as your page life expectancy is quite high which indicates a page will remain in the buffer for around 37k secs.
So you don't need to worry about memory pressure I think.


How to convert rows into columns and query on them in Mysql?

You can try this kind of "solution":

http://sqlfiddle.com/#!9/d33a95/18

It is not an "pivot-table" solution, but maybe it solves your problem - if I have understood it correctly.

Here are the tables / data used:

CREATE TABLE `patients` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
);
CREATE TABLE `controls` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Text` VARCHAR( 50 ) NOT NULL ,
`Type` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
);
CREATE TABLE `controlvalues` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`ControlID` INT NOT NULL ,
`Value` VARCHAR( 50 ) NOT NULL ,
`PatientID` INT NOT NULL ,
PRIMARY KEY ( `ID` )
);
INSERT INTO `patients` (`Name`) VALUES ('Ara'), ('Sada'), ('Pada'), ('Lada');
INSERT INTO `controls` (`Text`, `Type`) VALUES ('age', 'textbox'), ('alergy', 'checkbox');
INSERT INTO `controlvalues` (`ControlID`, `Value`, `PatientID`) VALUES
  (1, '23', 1),
  (2, 'true', 1),
  (1, '25', 2),
  (2, 'false', 2),
  (1, '23', 3),
  (2, 'false', 3),
  (1, '23', 4),
  (2, 'true', 4);

And the query:

SELECT PatientID, COUNT(PatientID) AS PatCount, p.Name
FROM controlvalues cv
LEFT JOIN patients p ON p.ID = cv.PatientID
WHERE ((ControlID = 1 AND Value = '23')
   OR (ControlID = 2 AND Value = 'true'))
GROUP BY PatientID
HAVING PatCount = 2
;


select values from a table where column values should be different

Well, condition2 seems to cover condition1 and condition3 too. What you probably need would be something like:

;WITH CTE AS
(
    SELECT  *,
            N = RANK() OVER(PARTITION BY cpt, cpt4 ORDER BY eff_date DESC)
    FROM dbo.YourTable
)
SELECT *
FROM CTE
WHERE N = 1;

This should cover everyone of your conditions.


Call Powershell script from another Powershell script

I solve my case using $PSScriptRoot, I hope my answer can help somebody more

The final code ends:

param(
[String] $pathfolder, #variable used to get the actual location of the folder from where is execute the PS1 file.
[Parameter(Position=0, Mandatory=$true)]
[string] $svrinstance,
[Parameter(Position=1, Mandatory=$true)]
[string] $dbuser,
[Parameter(Position=2, Mandatory=$true)]
[string] $dbusupassw,
[Parameter(Position=3, Mandatory=$true)]
[string] $dbname
)

$sqlinstance    = $svrinstance
$userdb         = $dbuser
$passdb         = $dbusupassw
$namedb         = $dbname

try
{

 Write-Output "Baseline Script to Set Up Confirmation Manager Database"
 $pathfolder = Split-Path -Parent $MyInvocation.MyCommand.Definition

 if($pathfolder -ne $null)
 {
    Write-Host $pathfolder
    $psfile = $PSScriptRoot + ".\FileStreamAccess_Validation.ps1"
    .$psfile $sqlinstance $userdb $passdb

 }

}
catch
{

}


I am trying to understand the use of composite primary key or two primary keys in a table

It comes in handy especially when combining with foreign keys ...

imagine having 3 tables

T1
ID, data1, data2
T2
ID, data1, data2
T3
T1id, T2id, data3, data4

the ID column for T1 and T2 is obviously primary key - but what about table T3 - we could define another ID column to make a primary key .. OR just define the combo of T1id and T2id as the primary key ... but this depends on what data you hold in your tables and what relationship they contain
but especially when T3 was used to store a M:N (many : many) relation it makes sense to use a composite primary key consisting of T1id and T2id.


How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?

You can easily tell the dynamic SQL execute in a specific database by dynamically building a [database].sys.sp_executesql command:

USE your_database;
GO

CREATE PROCEDURE dbo.DatabaseNameOptional
    @db sysname = NULL
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max) = N'SELECT DB_NAME(); /* other stuff */'

  DECLARE @exec nvarchar(770) = COALESCE(@db, DB_NAME())
    + N'.sys.sp_executesql';

  -- alternatively, just leave DB_NAME() out of it:

  --DECLARE @exec nvarchar(770) = COALESCE(@db, N'')
  --  + N'sys.sp_executesql';

  EXEC @exec @sql;
END
GO

Try it out:

USE your_database;
GO

EXEC dbo.DatabaseNameOptional;
GO  -- output = your_database

EXEC dbo.DatabaseNameOptional @db = N'master';
GO  -- output = master

USE tempdb;
GO

EXEC your_database.dbo.DatabaseNameOptional;
GO  -- output = your_database

EXEC your_database.dbo.DatabaseNameOptional @db = N'master';
GO  -- output = master

In the execution context of the procedure, though, no, I don't think there's any way to determine where the call originated from (or to run in that context). That's the benefit of using a system-marked procedure in master - if that's the functionality you want, you need to decide if "putting objects in master" is ickier than "not getting what I want."


Your if condition should actually almost always be false, unless you have an entry where schedule_id = 0.

It may be surprising, but in MySQL you can write something like this:

root@localhost:(none) > select 1=1=1;
+-------+
| 1=1=1 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

root@localhost:(none) > select 1=1=2;
+-------+
| 1=1=2 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

So in your case you actually don't compare anything in your if condition, there is just a query which returns a number. And the if condition is true whenever it's not 0.

The solution here is to use parantheses.

IF((SELECT COUNT(Slot_ID) FROM smart_scheduler1.work_schedule where Employee_ID = uid AND Schedule_ID = S_ID) = 0) THEN

Or you could do

IF NOT EXISTS(SELECT Slot_ID FROM smart_scheduler1.work_schedule where Employee_ID = uid AND Schedule_ID = S_ID) THEN

I personally like to use (NOT) EXISTS very much, not only because it's so easily readable, but EXISTS stops as soon as an entry is found and doesn't continue to search till the very end, although the exact number is of no interest.

If the exact number matters, you can of course also use variables like this:

SELECT @my_variable := COUNT(Slot_ID) FROM smart_scheduler1.work_schedule where Employee_ID=uid AND Schedule_ID=S_ID
IF(@my_variable = 0) THEN

or

DECLARE my_variable INT;
SELECT COUNT(Slot_ID) INTO my_variable FROM smart_scheduler1.work_schedule where Employee_ID=uid AND Schedule_ID=S_ID
IF(my_variable = 0) THEN

You can read about the differences of these two kinds of variables here and here.


Which tables to create according to relations

It isn't necessary to have a surrogate primary key on your clubs_chains table.

The combination of the two foreign keys in clubs_chains is adequate for the primary key.

You can use a foreign key constraint to ensure that your clubs_chains_paymethod table references an existing record in clubs_chains using the compound primary key.  This might be helpful since it would allow more direct joins between clubs_chains_paymethod and your clubs and chains tables, without sacrificing any referential integrity.

On the other hand, some people just love using a surrogate primary key, even on intersection tables.  If that is your data modelling style, then it's OK too.


Shyamev,

Just to make sure the database selected should be a user and not any system databases.

But in many cases we have seen this even if its user database is selected and the option of log shipping is missing.

Solution: Re-install SSMS with the Management Tools - Complete option checked.

Refer to below link:

https://social.msdn.microsoft.com/forums/sqlserver/en-US/01a0ab24-5555-4b55-b399-a866a5b09d1d/transaction-log-shipping-option-is-missing


Using PL/PerlU with a non system perl lib

Can't you just push it into @INC before you use it? At the first line in your func? I'm pretty sure that should work.

PostgreSQL will only use the perl it's built against. You'd have to rebuild it (or at least plperl) against your custom perl.


Is there a way other than WKT to insert geometry to SQL Server?

Microsoft doesn't support input from foreign formats. However, you can use GDAL's ogr2ogr to convert foreign formats representing Simple Features to a MSSQL import

DGN is supported by the opensource GDAL

DWG requires building against Teigha which while open source is not, afaik, free. However, they provide a File Converter. You can find more information about Teigha fiasco/political situation here, but there is explicit mention of support with Teigha in the GDAL docs,

OGR supports reading most versions of AutoCAD DWG when built with the Open Design Alliance Teiga library. DWG is an binary working format used for AutoCAD drawings. A reasonable effort has been made to make the OGR DWG driver work similarly to the OGR DXF driver which shares a common data model. The entire contents of the .dwg file is represented as a single layer named "entities".

More info:

Formats GDAL supports our of the box here
Download a copy of GDAL for proprietary overpriced operating systems here.

To ease upgrading in the future to Ubuntu, you can install ogr2ogr with only
sudo apt-get install gdal-bin


Scale MySQL trigger for thousands of inserts per seconds

Don't use a TRIGGER.  Instead, gather a minute's worth (say) of data in a staging table.  Then do the desired updates en masse.  If you need the user to get up-to-the-second information (instead of just up-to-the-minute), have the user's query do that final update also.

To phrase it differently, summarization is much more efficiently done in bulk.

Your statements are confusing on the scale of the problem:  "thousands of inserts per second" versus "a row is added to table token every second".  Which is it?

If serious scaling of inserts and processing, see my blog on staging tables.  That discusses "ping ponging" of a pair of staging tables in order for the speed to be self-adjusting.

To discuss your situation further, please provide SHOW CREATE TABLE and some more details.


Does firing off an indefinite WAITFOR increase the log file size?

Any active open transaction will pin the log, preventing truncation and eventually causing growth. If you start a transaction, write to the log and then wait forever in hope a message will eventually awake you, you just pinned the log and cause it to grow.

Lately I started recommending people to shun the WAITFOR in activated procedure, along with the loop. Just issue a RECIEVe and be done, let the activation mechanism loop for you (it does) and don't WAITFOR, just plain RECEIVE.

The WAITFOR flavor of RECEIVE creates a savepoint internally. This generates log (at least 3 log records) and does indeed pin the log in place while waiting. Having a long WAITFOR timeout (or worse, an infinite one) would be a very bad practice.


Scope_Identity() returns null after inserting first row in table

If you call the procedure multiple times, those are different scopes, so SCOPE_IDENTITY() is expected to be null. And you need to be careful about concepts - if you call the procedure multiple times, how is the second invocation really going to be sure that "the last generated value" was from the previous invocation from that process, vs. some other concurrent call to the same procedure?

Consider table-valued parameters so that you only have to call the stored procedure once, and you can stop tracking if this call is inserting the "first" row and worrying about multiple users colliding:

CREATE TYPE dbo.TVPLabStagingDetails AS TABLE
(
   FirstName NVARCHAR(100),
   LastName  NVARCHAR(100)
);

Now the procedure becomes:

ALTER PROCEDURE dbo.[Insert_Header_Details_Tables]
    @FileName [varchar](50)    = null,
    @VendorName [varchar](250) = null,
    @Names dbo.TVPLabStagingDetails READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.LabStagingHeader([FileName], [VendorName])
    SELECT @FileName, @VendorName;

  DECLARE @ProcessID INT = SCOPE_IDENTITY();

  INSERT dbo.LabStagingDetails([ProcessID], [LastName], [FirstName])
    SELECT @ProcessID, LastName, FirstName FROM @Names;
END

If that kind of change is not possible, push back; if that also fails, then you'll need to use an output parameter so that the client side can pass in @ProcessID on subsequent calls. But really that is the least efficient way to do this.

Some other tidbits:

Always specify schema prefix
Use semi-colons to terminate statements
Wrap your procedure body with BEGIN/END and use SET NOCOUNT ON;
Do not use IDENT_CURRENT()


Should I record ID numbers in a table where I record who look whom profile page

I think your two choices here are:

make the (visitorID,visitedprofileID) combination a primary key
add a recordID as primary key

If the table is InnoDB (or it will be in the future for Foreign Key usage), I would go with the second option. Given the way InnoDB handles indexing by adding the primary key to secondary indexes for lookup, if you ever wanted to add an index on datetime, the single field would result in a smaller index.


Pick either TEXT or Json, based on the needs.
TEXT (up to 64KB) or MEDIUMTEXT (up to 16MB) plus FULLTEXT indexing is useful for very efficient seraching for "words" in unstructured prose.  There are limits on what a "word" is -- in length and in avoiding "stop words" such as "the".
JSON is for structured text, usually key-value pairs, possibly arrays and associative arrays.  But the search capabilities are limited.  What specific version of MariaDB (or MySQL) do you expect to use?
To help you further, we really need some insight into the type of data and application.


Short answer: start SQL Server with the -m flag from a local admin account.

Step-by-step instructions here:


Error Code: 1215. Cannot add foreign key constraint

I suspect you have the direction of the foreign key relationship wrong. If dipartament.numero_dip is the primary key, then you should reference it from prestito, not the other way around:
CREATE TABLE DIPARTIMENTO(
NUMERO_DIP SMALLINT(2),
NOME_DIP VARCHAR(50) NOT NULL,
INDIRIZZO_DIP VARCHAR(50),

PRIMARY KEY(NUMERO_DIP)
);

CREATE TABLE PRESTITO(
MATRICOLA_U INT,
CODICE_LIB SMALLINT(4),
DATA_P DATE,
DATA_R DATE,
N_DIP SMALLINT(2),
PRIMARY KEY(CODICE_LIB),

FOREIGN KEY(MATRICOLA_U) REFERENCES UTENTI(MATRICOLA),
FOREIGN KEY(CODICE_LIB) REFERENCES LIBRI(CODICE_L),
FOREIGN KEY(N_DIP) REFERENCES DIPARTIMENTO(NUMERO_DIP)
);


Can you have more than 65536 sessions on an Oracle RAC database?

Each Oracle instance in a RAC configuration maintains its own resources and sessions; you would be able to have up to 65536 sessions for each node in the cluster.
https://docs.oracle.com/database/121/RACAD/GUID-59DEC066-5743-4EDE-9A02-E8896005F658.htm#RACAD818


App Engine - Datastore ; Exclude columns on backup

That wouldn't be a backup of your table; you wouldn't be able to recover the table because the structure of the resulting table is different, and you wouldn't be able to recover the data either.

It is possible to save only the result of a selection into a file, e.g.

mysql -e "select column1,column2 from table" -u user -pxxx mydatabase > partialbackup.txt

If the reason for this is that you have a table containing a very large amount of data, only part of it being truly important, you should reconsider your database structure: divide the important data and additional data to separate tables and dump the important table more frequently.


Why is script with " xp_cmdshell " in comment failing with a transport level error?

There is a connect item discussing (well discussing...) exactly this issue, so it appears to be a bug.

Text 'xp_cmdshell' causes transport-level error: semaphore timeout period has expired from remote system

It appears that the string causes problems, not only in comments but also anywhere it's used as an exact string so even

SELECT 'xp_cmdshell'

results in the same error according to the bug report.

So it appears that the exact string xp_cmdshell is indeed the problem, unfortunately the connect item is closed as "not reproducible".
That being said, I can't reproduce it (on a local connection) on my SQL 2014 (12.0.2000.8, Windows NT 6.3  (Build 9600: )) test system either, so the operating system is probably relevant. Executing the query from a local management studio vs over the network might also be relevant as noted in the connect item.

So you could either work around it by changing the exact text (as you did) or try to reopen the connect item or file a bug report through Microsoft Support.


Database design, many rows or many columns?

MySQL has limits, such as 1017 columns in an InnoDB table (as of 5.6.9).

If you are not filtering or sorting on most of the columns, put them in a JSON string and put that string in a TEXT (or maybe MEDIUMTEXT) column.

A key-value store can lead to lots of JOINs.  The limit is 61.

I'm confused -- Do you have 1000 sets of 7000 parameters?  Or what?  If it is a single set of parameters, then a single key-value table makes some sense.


Can't create indexes on really large table!

One thing you can try is to temporarily set the recovery model to Simple.

Alternatively, you can tell the create index command to use tempdb.  Since tempdb is typically in the Simple recovery mode.

CREATE INDEX ...
WITH (SORT_IN_TEMPDB = ON);


Select one of multiple columns into a new column Ask Question

You want nested CASE statements.  You haven't full described the problem, we can't give you a full answer.  One branch would list 'foot' as the first priority, and other as the last priority (or maybe not consider it at all, or consider it an error) Something like:

CASE WHEN speed > 2 then
  CASE WHEN bike then 'bike'
  CASE WHEN car then 'car'
  ...
  CASE when foot then 'foot and/or error'
  ELSE 'none'
  END
ELSE
  CASE when foot then 'foot'
  CASE WHEN bike then 'bike'
  CASE WHEN car then 'car'
  ...
  ELSE 'none'
  END
END

You can't ignore a row from the select-list, so you probably need to write logic in two places.  In the WHERE clause, to ignore rows with a true in more than one non-foot column, plus a CASE expression like the above in the select list, (or in the SET list if doing an UPDATE).


How to choose a good value for MySQL relay-log-space-limit?

That would depend on four(4) factors

How much diskspace you are willing to have for relay logs
If you have expire_logs_days set to a nonzero value on the Master
How quickly you react to any monitoring of Relay_Log_Space from SHOW SLAVE STATUS\G (See my Feb 09, 2012 post Programatically testing slave status - What fields in SHOW SLAVE STATUS indicated slave status is OK / in ERROR? on relay_log_space_limit)
How quickly you react to broken replication

If Slave_IO_Running is No
If Slave_SQL_Running is No

With Relay_Log_Space reaching relay_log_space_limit no log rotation occurs until every SQL statement in the oldest relay is processed.

What if the SQL thread breaks due to some SQL error? You need to resolve the error with the number of days set by expire_logs_days. If this is missed, when replication is fixed, the SQL that was downloaded to relay logs but rotated away from the Master will not make it the Slave. If not fixed, relay logs just grow because the IO thread would still be up and collect new SQL from the Master.

What if the IO thread breaks due to some communication error? This could be more critical if the last relay log gets logically corrupted (a possibility). Once Communication between Master and Slave is known to be OK, you must get

Relay_Master_Log_File (like mysql-bin.000128)
Exec_Master_Log_Pos (like 82323232)

from SHOW SLAVE STATUS\G. Use these commands to cleanup

STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000128',
MASTER_LOG_POS=82323232;
START SLAVE;

This will erase all relay logs and start over with a fresh set.

EXAMPLE

I have a DB Hosting client that generates a full 1GB binary log every 18 minutes (Yes, 80GB a day). Since client has expire_logs_days = 3, client has 240 GB a day.

Picture the above scenarios and trying to deal with them without have the right alerts in place or enough diskspace of relay logs. This gets more complicated since with Circular Replication because binary logs and relay logs share the same space by default. Perhaps stored binary logs on a separate disk can help mitigate some diskspace issues.

RECOMMENDATION

If you can get to fix replication errors quickly or if diskspace is at a premium, you could set relay_log_space_limit low (something like 10G)

You may want to determine how much space you crank for binary logs in one hour and set relay_log_space_limit to something like 12 hours worth.


IntelliSense gets confused occasionally.

Try refreshing the IntelliSense cache by pressing CTRL-SHIFT-R or hit "Edit -> IntelliSense -> Refresh Local Cache" from the menu-bar to see if the underline goes away.

Alternately, try running CREATE LOGIN [dh\demo_Data_Admin] FROM WINDOWS WITH DEFAULT_DATABASE=[master] - if the login already exists, this will return an error.  If no error is thrown, the login didn't already exist, and has now been created.

You can check if the login exists with this query:

SELECT *
FROM sys.server_principals sp
WHERE sp.name = 'dh\demo_Data_Admin';

If the login exists, a row will be returned with details about the login.


Instead of leaning on a relational database to perform these statistical calculations on time-series data, I'd suggest that you move this math and post-processing work outside of the database into a client application.

Using a scripting language like Python or Ruby, you can incremental solve the problem by querying for "chunks" of data over a fixed-width period of time, compute an intermediate statistical summary, and then combine the results across multiple chunks, as you loop over the whole history.  Some statistical measures are hard to combine across chunks, but something like Avg() only needs sum() and count() per chunk, O(1) vs. O(chunksize), so chunk-merging may scale well.


Error "The publication '[Name]' does not exist." running sp_article_validation

Ensure you're on the 'publication' DB so USE [publicationDBName] and then execute the SP.

Otherwise, try various combinations such as the below for the NAME variable you're passing to the SP as an argument to the SP in case it's related to it not liking the preceding 'N which indicates that the subsequent string is in Unicode, thereby passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT.

SET @p = NAME
SET @p = [NAME]


Delete all records in tables with reference to another reference

You just have to traverse the relationship backwards, deleting from the last child first, then the next child, and so on.

Let's say you want to delete all the data for a specific customer, you could have this procedure:

CREATE PROCEDURE dbo.ObliterateACustomer
  @delete_me int
AS
BEGIN
  SET NOCOUNT ON;

  DELETE dbo.customer_contact_phone
    WHERE contact_id IN
    (
       SELECT contact_id
         FROM dbo.customer_contact
         WHERE customer_id = @delete_me
    );

  DELETE dbo.customer_contact WHERE customer_id = @delete_me;

  DELETE dbo.customer_address WHERE customer_id = @delete_me;

  DELETE dbo.customer WHERE customer_id = @delete_me;
END
GO

For the delete from a relation, there are several ways to do it. IN is arguably the most straightforward, but you can also delete from a join:

DELETE p
FROM dbo.customer_contact_phone AS p
INNER JOIN dbo.customer_contact AS c
  ON c.contact_id = p.contact_id
  WHERE c.customer_id = @delete_me;

Or using EXISTS:

DELETE p
FROM dbo.customer_contact_phone AS p
WHERE EXISTS
(
  SELECT 1 FROM dbo.customer_contact AS c
  WHERE c.contact_id = p.contact_id
    AND c.customer_id = @delete_me
);

You could even use MERGE, but I'm not going to show you how, because I think it's often a terrible idea. :-)


Unknown,AppDomain 66 (master.sys[runtime].65) is Marked for Unload Due to Memory Pressure

Memory architecture was changed in SQL Server 2012 such that there was little need to worry about the MemToLeave setting anymore, especially if using 64-bit SQL Server. And, starting with SQL Server 2016 (which you are using), SQL Server is only available in 64-bit (see the "Note" at the top of the "What's new in Database Engine - SQL Server 2016" page). So, no, don't worry about MemToLeave.

Correct, "Memory Pressure" errors are not specific to SQLCLR. Those errors are not telling you the cause of memory pressure, but instead what is being impacted by there being memory pressure (which I doubt there is any possible way to truly have insight into the cause of anyway — I mean, if there are 10 processes taking up memory, which combination is really the cause? it's not necessarily what is taking up the largest chunk as that might be entirely valid). Memory pressure impacts other areas as well that might not show up in the error log, such as flushing the plan cache and/or buffer pool (i.e. data pages loaded into memory).

There are several built-in features that use SQLCLR, a partial list being the following:

Datatypes:

HierarchyID
Geography
Geometry

Functions:

FORMAT
PARSE
TRY_PARSE
AT TIME ZONE (starting in SQL Server 2016)
COMPRESS (but not UNCOMPRESS ; starting in SQL Server 2016)

Features:

Change Data Capture
Dynamic Management Framework
Replication
Policy-Based Management
Master Data Services
SSISDB (the "Fuzzy Lookup" feature)

One or more of those (or perhaps one that I did not list above) is what is being affected in your system. There are two clues, both within the (master.sys[runtime].78) part of that info, that tell us this:

the database being master (assuming you would never, ever load custom assemblies into master ;-))
the "owner" (i.e. AUTHORIZATION) of the assembly being sys (we cannot assign ownership of assemblies to either sys or INFORMATION_SCHEMA as neither of those principals has an SID). If you want to see the owner for each assembly, execute the following:

SELECT asm.[name] AS [Assembly],
       USER_NAME(asm.[principal_id]) AS [Owner],
       USER_SID(asm.[principal_id]) AS [OwnerSID]
FROM   sys.assemblies asm;

What you can do is:

You mention that this error shows up "occasionally", but have not exactly defined how often this really is. Once an hour is different than once a day which is different than once per week. Memory pressure happens, so unless this is more than a few times per day, I wouldn't spend too much time worrying about it.
Add more memory (either physically and/or allowing SQL Server to use more of the system memory if you currently have SQL Server constrained to a lesser amount)
Analyze what is consuming memory outside of SQL Server to see if there are unnecessary processes on the server that can be offloaded to other servers, or perhaps shut-off entirely (i.e. are there other services being used, and/or remote desktop sessions that are then running SSMS, etc that are using lots of memory?)


Enabling LOAD DATA LOCAL INFILE in mysql

If the MySQL Debian-7 minimal cannot use local_infile, look around all the make files used for compiling to see if it is disabled by default or if local_infile is enabled for the Debian-7.

Before taking that kind of time, please run the following:

SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = 'ON';
SHOW GLOBAL VARIABLES LIKE 'local_infile';

It should echo the following:

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL local_infile = 'ON';
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

If it still says 'OFF', then look deep within the compiler settings to enable it.

If it sets to 'ON', you are OK.

Please note I did not say

SET GLOBAL local_infile = 1;

I said use

SET GLOBAL local_infile = 'ON';

The option local_infile is Boolean, not numeric.

If setting this in my.cnf

[mysqld]
local_infile=ON

and restarting mysql does not work either,  you will have to start up mysql with something like this:

echo "SET GLOBAL local_infile = 'ON';" > /var/lib/mysql/init_file.sql
chown mysql:mysql /var/lib/mysql/init_file.sql
service mysql stop
service mysql start --init-file=/var/lib/mysql/init_file.sql
rm -f /var/lib/mysql/init_file.sql

or perhaps adding this to my.cnf

[mysqld]
init-file=/var/lib/mysql/init_file.sql

then restart mysql.

Give it a Try !!!


get base urls with different "endings" after the last slash

SELECT DISTINCT t1.*
FROM testtable t1, testtable t2
WHERE t1.field2 = t2.field2
  AND t1.urlTest != t2.urlTest
  AND SUBSTRING(REVERSE(t1.urlTest) FROM LOCATE('/', REVERSE(t1.urlTest)))
     =SUBSTRING(REVERSE(t2.urlTest) FROM LOCATE('/', REVERSE(t2.urlTest)))

fiddle


How to know what are the necessary log files for a backup in DB2?

By default an online backup places the logs necessary to rollforward to the point of the end of the backup inside of the backup file. You can extract the logs from the backup file and then ROLLFORWARD to the end of them. Use the LOGTARGET clause of the RESTORE command to extract the logs to a location (I have tended to restore them to a location other than my logs directory.) Then use the OVERFLOW LOG PATH clause of the ROLLFORWARD command to point the ROLLFORWARD command to the logs you just extracted.

Example:

db2 RESTORE DATABASE TST1 FROM '/db2/backup' TAKEN AT 20150826131633 TO '/db2/data' INTO TST2 LOGTARGET '/tmp/extractedlogs'

db2 ROLLFORWARD db TST2 TO END OF LOGS AND COMPLETE OVERFLOW LOG PATH ('/tmp/extractedlogs')


How to reset object permissions to default?

The only way I can think of is directly modifying the system catalog tables.

To remove all permissions from s.tab, you can

UPDATE pg_class
SET relacl = NULL
WHERE oid = 's.tab'::regclass;

But then you also have to remove the dependencies between the table and all roles which had privileges on the table:

DELETE FROM pg_shdepend
WHERE classid = 'pg_class'::regclass
  AND refclassid = 'pg_authid'::regclass
  AND objid = 's.tab'::regclass
  AND deptype = 'a';

Needless to say, this is very dangerous, and I cannot guarantee that I didn't forget anything…


One of the considerations implementing multiple databases is different RTP/RPO requirements.  It seems this is the case in your situation because the RPO is different.

Consider creating a separate databases for the ETL tables and historical data.  Backup the historical data database as needed to meet your RPO.  The ETL database can be backed up infrequently (e.g. only after schema changes), or not at all if you can simply recreate the database script.

Personally, I would split the database only if the size of ETL tables are disproportionally larger than the historical data.  The additional complexity might not be worth the effort, especially with backup compression.  If the ETL tables can be memory optimized, you have the option to specify SCHEMA_ONLY durability for those tables as long as you can run the ETL reset process after an instance restart.  That would avoid the additional backup overhead and allow you to keep all in a single database.


Long term storage of sp_server_diagnostics results

No, but as MS recommends, you should save this off either to an external file via Extended Event or log it to a table.

Taken from sp_server_diagnostics:

-- Create the Event
CREATE EVENT SESSION [diag]
ON SERVER
           ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)
           ADD TARGET [asynchronous_file_target] (set filename='c:\temp\diag.xel');
GO
ALTER EVENT SESSION [diag]
      ON SERVER STATE = start;
GO

-- Read Output
SELECT
    xml_data.value('(/event/@name)[1]','varchar(max)') AS Name
  , xml_data.value('(/event/@package)[1]', 'varchar(max)') AS Package
  , xml_data.value('(/event/@timestamp)[1]', 'datetime') AS 'Time'
  , xml_data.value('(/event/data[@name=''component_type'']/value)[1]','sysname') AS Sysname
  , xml_data.value('(/event/data[@name=''component_name'']/value)[1]','sysname') AS Component
  , xml_data.value('(/event/data[@name=''state'']/value)[1]','int') AS State
  , xml_data.value('(/event/data[@name=''state_desc'']/value)[1]','sysname') AS State_desc
  , xml_data.query('(/event/data[@name="data"]/value/*)') AS Data
FROM
(
      SELECT
                        object_name as event
                        ,CONVERT(xml, event_data) as xml_data
       FROM
      sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\*.xel', NULL, NULL, NULL)
)
AS XEventData
ORDER BY time;

Alternatively you can log it to a table:

CREATE TABLE SpServerDiagnosticsResult
(
      create_time DateTime,
      component_type sysname,
      component_name sysname,
      state int,
      state_desc sysname,
      data nvarchar(max)
);
INSERT INTO SpServerDiagnosticsResult
EXEC sp_server_diagnostics;


Check if property exists in an object inside array in JSONB field in Postgres

Use an EXISTS in which you use jsonb_array_elements() to get the arrays' elements and check ->'d'->'f' for NULL.

SELECT *
       FROM jsonData
       WHERE EXISTS (SELECT *
                            FROM jsonb_array_elements(data->'c')
                            WHERE value->'d'->'f' IS NOT NULL);

SQL Fiddle


Creating a report that fills in when the user makes a data selection from a combobox?

There are many ways to filter and produce reports based on user input within Access.  Access is flexible enough that to explain even one approach can either require entire tutorials or be too specific to be generally useful.  A lazy response would be to simply mark this question as too broad, but similar questions have been asked repeatedly on Stack Exchange forums (i.e. Stack Overflow), but with incomplete answers, so I think it justifies at least some direction here.

First, search the web for something like "access report from combobox" and you should get links to various websites and video tutorials that have plenty of examples and hints.

A vary loose outline of how I would go about this:

The general technique should be to base the report on a query that references a ComboBox control on a Form.

For example, a Form named Vendor Detail with a ComboBox control named Vendor Name could be referred to in the Report query like ... WHERE MyTable.Vendor = [Forms]![Vendor Detail]![Vendor Name] (This is of course only a snippet of the complete query SQL.)
DO NOT place the ComboBox on the report itself, even though Access will allow you to do this.  Such attempts will likely lead to unexpected, buggy behavior.
This technique requires that the Form remain open with a valid value selected in the ComboBox.  If the Report were to be opened directly without the Form, it would display an "ugly" prompt asking for the [Forms]![Vendor Detail]![Vendor Name] value.  You could optionally type in a value and continue producing the report, but that is not very user friendly.

A Form can include a button--alongside the ComboBox--that opens and/or refreshes the report when a different Vendor is selected.

The button event can execute either a Macro or a VBA code to open the report.  The details for this are beyond the scope of this answer, but can be found by searching the web and/or Stack Overflow.
The report can either be displayed on screen as a preview before printing, or it can be printed directly.

The other details of the report creation or the query it is based on are also beyond the scope of this answer, but there are no other special requirements, so all other aspects can be handled like any other Access Report.


SSDT Schema Comparison for Stored Procedure signed by a Certificate

Before getting into any specifics about Certificate creation and module signing, we can at least verify whether or not the two instances have the same certificate. Run the following on both instances:

SELECT [thumbprint], [cert_serial_number], [sid],
       [issuer_name], [subject],
       [expiry_date], [start_date]
FROM   sys.certificates
WHERE  [name] = N'cert_name';

If the Certificates are the same, then all of those fields will have identical values across both instances (or both databases on the same instance). But you mainly just need to check the thumbprint field since it is an SHA-1 hash of the certificate. If that value is different between the two locations, then you aren't dealing with the same certificate.

You say that

  the Certificate was created in each instance from the same script

but do not mention exactly how the Certificates are being created. They can be generated in SQL Server (this is known as "self-signed") by using the following syntax:

CREATE CERTIFICATE [cert_name]
  ENCRYPTION BY PASSWORD = 'certpassword' -- this is optional but best to use
  WITH SUBJECT = 'cert_subject';

This method will generate a new private key (each time it is run) and encrypt that with the password specified in the ENCRYPTION BY PASSWORD clause else encrypted by the database master key (which is why it is better to specify the password).

Or, you can create them by providing an existing Assembly, File, or Binary literal:

CREATE CERTIFICATE [cert_name]
FROM
   ASSEMBLY assembly_name
 | EXECUTABLE FILE = 'path_to_DLL'
 | FILE = 'path_to_file'
   WITH PRIVATE KEY ( FILE = 'path_to_private_key' )
 | BINARY = 0x......
   WITH PRIVATE KEY ( BINARY = 0x..... )

The FILE and BINARY methods allow for optionally specifying a private key file or binary literal. So if using one of those methods along with specifying the private key, or if using the signed ASSEMBLY, then creating a certificate this way should provide you with the same private key each time.

So how do you get the FILE or BINARY values?

FILE:

Once you have created the initial Certificate, export it (including the private key!) using the following:

BACKUP CERTIFICATE [cert_name]
  TO FILE = 'path\to\cert_file.cer'
  WITH PRIVATE KEY
  (
    DECRYPTION BY PASSWORD = 'cert_password',
    FILE = 'path\to\key_file.pvk',
    ENCRYPTION BY PASSWORD = 'file_password'
  );

And in the new place:

CREATE CERTIFICATE [cert_name]
  FROM FILE = 'path\to\cert_file.cer'
  WITH PRIVATE KEY
  (
    FILE = 'path\to\key_file.pvk',
    DECRYPTION BY PASSWORD = 'file_password',
    ENCRYPTION BY PASSWORD = 'cert_password'
  );

BINARY: (only available in SQL Server 2012 and newer)

Once you have created the initial Certificate, get the certificate and private key values using the following:

SELECT CERTENCODED(CERT_ID('cert_name')) AS [CertificateBinary],
       CERTPRIVATEKEY(
                      CERT_ID('cert_name'),
                      'temp_password',
                      'cert_password'
                     ) AS [PrivateKeyBinary];

And in the new place:

CREATE CERTIFICATE [cert_name]
  FROM BINARY = 0x{CertificateBinary}
  WITH PRIVATE KEY
  (
    BINARY = 0x{PrivateKeyBinary},
    DECRYPTION BY PASSWORD = 'temp_password',
    ENCRYPTION BY PASSWORD = 'cert_password'
  );

If you are using SQL Server 2012 or newer, then the BINARY method would be the easiest since it is completely self-contained (i.e. doesn't rely upon any external files).

PLEASE NOTE:

Regardless of which method above is chosen, the initial Certificate creation only needs to happen once, and probably needs to be done manually so that the backup file(s) or binary literals can be incorporated into the SSDT project.
If the private key is being used for creating the Certificate in the target database, then there is no need for the WITH SIGNATURE clause of ADD SIGNATURE since it is only used when the private key was removed or not used in the creation of the Certificate.
In this case (i.e. all of those fields in sys.certificates matching and using the private key in the CREATE CERTIFICATE), signing modules in two different locations that have the exact same definition (as in: byte-by-byte the same), will produce the same signature
If the private key is not being used for creating the Certificate, or has been removed (yes, you can do that), then you need to specify the WITH SIGNATURE clause and pass in the binary literal of the signature that was generated by the same private key and same module definition from a location that at least at one point did have the private key.
This is a more secure set up because it only allows for signing modules that you provide the signatures for. If a Certificate does not have a private key, it can still be used to check the validity of signed modules, but it cannot be used to sign new or changed modules (which is kinda cool).

BUT, if:

the Certificates in both locations are identical, and
the schema compare didn't flag the module definitions as being different, and
ADD SIGNATURE is being used in the destination database using the WITH SIGNATURE clause,
the binary literal being used is the same as found in the source database via:

SELECT OBJECT_NAME(major_id), crypt_property
FROM   sys.crypt_properties;

then you need to check the OBJECT_DEFINITION for that module in both locations. They need to be identical, including casing, etc. You can do a SELECT CONVERT(VARBINARY(MAX), OBJECT_DEFINITION(OBJECT_ID(N'{module_name}'))) just to be sure. It is possible that the schema comparison is ignoring differences in comments and/or the casing of the CREATE keyword at the beginning of the definition and/or some white-space, etc.

Please see the following MSDN pages for details:

CREATE CERTIFICATE
BACKUP CERTIFICATE
CERTENCODED
CERTPRIVATEKEY
CERT_ID
ADD SIGNATURE
sys.certificates
sys.crypt_properties


WITH
cte AS ( SELECT DISTINCT
                Month,
                MAX(CASE WHEN [Order] IS NOT NULL
                         THEN Month
                         END) OVER (ORDER BY Month
                                    ROWS BETWEEN UNBOUNDED PRECEDING
                                             AND 1 PRECEDING) prev
         FROM test )
SELECT Month, [Order], Amount
FROM test
WHERE [Order] IS NOT NULL
UNION ALL
SELECT t1.Month, t2.[Order], t2.Amount
FROM test t1
JOIN cte ON t1.Month = cte.Month
JOIN test t2 ON t2.Month = cte.prev
WHERE t1.[Order] IS NULL
ORDER BY Month, [Order];

fiddle with some explanations


Is it normal for tempdb TPS to be higher than all other databases combined?

For poorly designed databases and queries, tempdb will often become very active. Since it is a shared resource between all databases on the instance - dont be surprised to see a high transaction count there.

As Mark also comments, snapshot isolation can have a strong effect on tempdb too.


Something like the following should be good enough to get you started.  It should be quite lightweight as well.

DECLARE @Name       NVARCHAR( MAX ),
        @SQL        NVARCHAR( MAX );

DECLARE @t_BindingErrors TABLE
(
    ViewName        NVARCHAR( MAX ),
    ErrorMessage    NVARCHAR( MAX )
);

DECLARE c CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT  '[' + ss.name + '].[' + so.NAME + ']'
    FROM    sys.schemas ss
    INNER JOIN sys.objects so
        ON  ss.schema_id = so.schema_id
    WHERE   so.type = 'V';
OPEN    c;
FETCH NEXT FROM c
INTO    @Name;

WHILE ( @@FETCH_STATUS = 0 )
BEGIN
    SET @SQL = '
        DECLARE @i  INTEGER;
        SELECT  TOP ( 0 ) @i = 1
        FROM    ' + @Name + ';';
    BEGIN TRY
        EXECUTE dbo.sp_executesql @statement = @SQL;
    END TRY BEGIN CATCH
        INSERT INTO @t_BindingErrors
        VALUES ( @Name, ERROR_MESSAGE() );
    END CATCH;

    FETCH NEXT FROM c
    INTO    @Name;
END;

CLOSE   c;
DEALLOCATE c;

SELECT  *
FROM    @t_BindingErrors;


How to restore database with ib_log0, ib_log1 and ibdata1 files in MySQL

The important part of the error log shows your instance is not able to open() the ibdata1 file (for reading and writing).

First to check in such situation are user rights. In Windows another possibility is the read-only file attribute which can block writes too.


Try adding this setting to your configuration:

innodb_flush_log_at_trx_commit=0


You are probably using a 3rd party package.  And it probably does a "USE" at the beginning of every connection, possibly much more often.

The frequency of SHOW commands also smells like a sloppy 3rd package that can't remember what it is working with.

Another possibility is a monitoring package.  However, I would expect to see even more SHOWs.

One way to track things down is to turn on the "general log" for a short while, then look at that log to see which "user" is doing what.


How do I construct a LINESTRING by aggregating Points (ST_MakeLine) in MariaDB?

Because MariaDB (and MySQL) lack all spatial aggregates, the only method is currently to create the WKT, and force MySQL to parse that WKT.

SELECT ST_AsText(
  ST_GeomFromText(CONCAT(
    'LINESTRING (',
    GROUP_CONCAT(CONCAT(ST_X(geog),' ',ST_Y(geog)) SEPARATOR ','),
    ')'
  ))
)
FROM foo
ORDER BY id;
-- returns LINESTRING (0 0,1 1,2 2)

The inside part

GROUP_CONCAT(CONCAT(ST_X(geog),' ',ST_Y(geog)) SEPARATOR ',')

Creates the point list, and then we just wrap that in LINESTRING ( $pointlist ) and feed that to ST_GeomFromText()

ℹ There are type-specific versions of ST_GeomFromText, but at least in MariaDB they seem to do nothing different, except add confusion.


Baffled with MySQL LEFT JOIN query that I have to speed it up

Your query does not do what you think it does. It results in much more rows than you want to because of the cross join. The only reason you haven't noticed it, is that the excessive number of rows produced are not really inserted in the 3rd table due to duplicate keys which trigger either the INSERT IGNORE or the ON DUPLICATE KEY UPDATE

(And you should really keep one of these two, there is absolutely no reason to have both IGNORE and ON DUPLICATE KEY UPDATE. MySQL will either ignore the duplicate keys or try to update the existing row. Does it make sense to ignore someone and talk to him at the same time?)

You probably need:

FROM contacts c
  LEFT JOIN dnc_contacts d
    ON  d.contact = c.contact
    AND d.group_id = 21
WHERE c.phonebook_id = 70
  AND d.contact IS NULL

Try the above (just SELECT first, without inserting), to see if it gives you the wanted results.

An index on dnc_contacts (group_id, contact) would help with efficiency.


Error when executing "create pfile from spfile"

ORA-27077: too many files open

Based on that error I guess you have reached the limit of maximum open files.

You can check the soft and hard limits with ulimit -Sn and ulimit -Hn. Configure it in /etc/security/limits.conf or a seperate file under /etc/security/limits.d/. Check the list of actual open files with lsof -u oracle (if the user running the instance is oracle).


Error during CSSCAN of Oracle11 database

This is known issue for Oracle Database version 11.2 or higher where XDB is installed. My Oracle Support says this can be ignored and has been fixed in 11.2.0.2

Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) (Doc ID 745809.1)


Restore Error event id 17066 and 3624 while restoring diff backup

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB

This error can be caused by transient, timing-related errors, or by in-memory or on-disk data corruption.

This message also says that the backup set which you are trying to restore as differential backup is actually not consistent and SQL Server cannot bring database to a logical point if you try to apply this backup. The assertion checks are either SQL Server bugs or something corrupt in database.

You can bring database online and can run regular dbcc checkdb command and post the output

You need to also check your differential backup is consistent and you can do that by below command

restore verifyonly from disk='backup_location\backup.bak'

run above command and paste the result in question

  I am unable use dbcc checkdb as the database in restoring db.Issues happens only in three instance where there is totally 6 instance. In other instance restore is working fine.

Since database is in recovery mode you cannot run any command on it that is why every command you run on it fails

Lastly can you also add output of

sp_readerrorlog


Does a view need its own foreign key constraints?

A View is a logical table that is based on one or more physical tables.  If there are foreign key relationships in the underlying tables, then they will be manifested in the view.  Views are entirely dependent on the tables they are derived from, so trying to add foreign keys to them is not possible.


MySQL Group Replication multi-primary - add new member

If you want to add a new member to current online Group Replication, You don't need to stop or restart any of nodes.

For doing this just config new member(s3) like current members(s1,s2) but add new IP at the end of these two variables

loose-group_replication_ip_whitelist = "x.x.x.x,y.y.y.y,z.z.z.z"
loose-group_replication_group_seeds = "x.x.x.x:33061,y.y.y.y:33061,z.z.z.z:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_start_on_boot=off

After that Start new member and execute these

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';

Then Install group_replication and start group_replication

After the new member joined the group open my.cnf of s1,s2 server and update loose-group_replication_ip_whitelist and loose-group_replication_group_seeds to this:

loose-group_replication_ip_whitelist = "x.x.x.x,y.y.y.y,z.z.z.z"
loose-group_replication_group_seeds = "x.x.x.x:33061,y.y.y.y:33061,z.z.z.z:33061"

But you Don't need to restart these nodes(s1,2) just update my.cnf


Aborted connection (Got an error reading communication packets) MySQL on Amazon RDS

Since you tried the above steps, your sticking point seems to be the one you can't alter.

I wrote a post back in August 2012 (Local database vs Amazon RDS), mentioning how the number of DB connections and the Buffer Size are fixed and immutable for each in RDS server model. I also mentioned that the Transactions Logs are fixed at 128M, no matter what RDS server model you use.

SUGGESTION: Be kind to yourself. Migrate the DB from RDS to EC2. Then, you have the necessary flexibility to change the InnoDB Infrastructure. Consequently, you will have to write your own backups and setup replication by hand.

I know you said going to EC2 is your last resort. Since you cannot tune InnoDB effectively in an RDS environment, EC2 is the next logical step.


How does Oracle close and unmount a database?

It helps to understand what 'mount mode' means:

Before DB is mounted, instance is up (SGA and other memory structures are allocated but DB and its files etc have not been verified yet. In mount mode, the controlfile will be read, it will make sure all logfiles/datafiles etc are available and ready to go. This is essential maintenance. Then, after mounting, you open the db during which db will do additional checks including to see if any recovery is needed. Once these are done, DB is available for use.

So, during shutdown, once close is issued, Oracle will start stopping processes in a specific order. Then it flushes modifications data to tables, updates column usage metrics, bunch of internal tables, audits etc.

Then DB dismounts, i.e. opposite of mount, it checks for pending disk operations, completes them and then starts closing files, tells ASM to dissociates diskgroup from this instance. After this, archival process, logwriter stops.

So, when file headers are updated, controlfile is updated and file io stops the db is dismounted. All that remains are memory structures. once they are destroyed, DB instance is fully shutdown.

I am fairly certain someone can give you even more gritty details, but this should help you.


Unstructed data field: Query all values from array of objects by key

Grab the elements from the json array with json_array_elements and reassemble them with json_agg:

select id, json_agg(cars) from (
  select
    id,
    json_array_elements(data->'personalVehicle')->>'model' as cars
  from t
) subq
group by id

Output:

1   ["Toyota", "Honda"]
3   ["Chevrolet", "Ford"]
2   ["Renault", "Citroën"]

Here's a fiddle.


Differntial back up: what happens to existing data?

Since you'd have to apply the differential backup's previous full backup, the end result is identical to if you'd just taken a full backup, made changes, then restored the backup you'd just taken - any changes made would be lost.

There are, however, tools and techniques which would allow you to perform partial restores, so you could limit the application of a differential backup to just objects which had not been modified by your changes.

Since you can't apply a differential to a database that has been recovered ( as in, restored from a full backup without the NO_RECOVERY option or otherwise WITH RECOVERY'd thereafter ) and you can't modify a database that is offline ( in this case, in recovery would be the offline state in question ), trying to do so would not only negate any changes that were made, but require a complete full restore to be applied again before the newly arrived differential could be applied.

More information regarding the reason behind your copy process ( DR redundancy system, development environment, read-only reporting server, etc ) would be needed to make a recommendation on a suitable technique for your specific problem.


Using postgres with logstash - log filename is causing issues

I figured it out, you can just use wildcards in the logstash-forwarder config. :p So far that seems to be working, but I'll keep an eye on it to make sure it rotates properly.


I've run into this before and have used 2 different approaches.
If the process should only run if files exist, I've written a c# script to set a flag 'DataExists' so I can evaluate and take different paths within the SSIS package.  The other is I have a dummy file on the directory and when I build the expression in the foreach container I use variables that will use that dummy file name/location if nothing else exists.   Hope that helps.


There are lots of pitfalls.

Simply adding PARTITIONing will not improve performance.
Change most of the indexes if you add PARTITIONing.
Don't use unless you have a least a million rows
Only BY RANGE() is useful.
It is essentially useless to PARTITION BY RANGE(the-primary-key)
There are only 4 use case for partitioning.
A PARTITIONed table is likely to be bigger than the un-partitioned equivalent.
UNIQUE and FOREIGN KEY are virtually unavailable.

More:  http://mysql.rjweb.org/doc.php/partitionmaint
SELECT  col
    FROM  PartitionedTable
    WHERE  id >= 6000
      AND  id  < 8000
    ORDER BY  col DESC
    LIMIT  1;

Sure, there is some partitioning pruning.  That is followed by a range scan in each picked sub-table.  Scanning a BTree is possibly faster without the pruning on top of it.
INDEX(key) has (silently) id tacked on the end.  So, this is an "index range scan" with or without partitioning.  But partitioning slows it down because it must do a sort if the results are not in a single partition.  (Change that id range to see how much worse things get.)
"Test case error": SELECT MAX(col) .. WHERE id ... cannot be optimized, with or without partitioning.  Furthermore, the Optimizer in 5.5 performs the query more efficiently.
I ran your queries on several versions of MySQL/MariaDB.  For the first query:
Partitioned:       0.5 to 1.7 ms
Not partitioned:   0.4 to 0.6 ms

Need I say more?


I couldn't include [Intranet].[dbo].[ResourceDevelopementCompletedCategories] in my test query below but hopefully I have provided enough code for you to understand the syntax you require.

create table #robschneider
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,event1 varchar(100)
  ,uuid int
)

Insert Into #robschneider Values ('cycle', '1'), ('run', '2'), ('walk', '3')

SELECT  ID
        ,uuid
        ,COALESCE(cycle, 0) AS [Cycle]
        ,COALESCE(run, 0) AS [Run]
        ,COALESCE(Walk, 0) AS [Walk]
FROM
(
    Select
    [rs].[ID],
    [rs].[uuid],
    [rs].[event1],
    CASE when [rs].[uuid] is not null then '1' else '0' end As [Exists]
    from #robschneider rs
) src
pivot
(
    MAX([Exists]) For [event1] IN ([cycle], [run], [walk])
) piv

UPDATE
I have added the COALESCE statements to add the 0 values when event1 doesn't exist.


Asynchronous commit on Postgres over iSCSI with BBU storage

It is my understanding that it's alright to turn synchronous_commit off if your server has a local, battery backed up storage controller.

Actually, that's kind of backwards. Turning synchronous_commit off is unsafe no matter what, in that it permits the database to lose recently committed transactions if it crashes. This is true with, or without, BBU or crash-safe SSD storage.

What synchronous_commit = off does is allows you to trade durability for speed when you're on storage where flushes are expensive so you want to batch them up and want to avoid client latency while apps wait for flushes. It has much less effect on storage where flushes are fast, since you do less waiting for commits to happen - so it's pretty much all downside and little benefit.

In general, you should not set synchronous_commit off globally. As the documentation advises, you should SET LOCAL synchronous_commit = off in specific transactions that you don't need to be durable, leaving it enabled otherwise. That way you might lose transactions you're not so fussed about, but not the ones that're making important changes.

If you can't afford potential loss of transactions that clients think are committed at all, you may instead want to consider a commit_delay, which pauses to try to batch a few commits together before flushing. This can produce a throughput improvement on I/O subsystems with really slow flushes without sacrificing durability.

You may also want to consider using UNLOGGED tables for specific tables you can afford to lose in a crash. If Pg crashes while the table is dirty it'll truncate it and you'll have to re-populate it, but there'll be no wider database corruption.

If anyone tells you to turn fsync off - please don't. It should be called eat_my_data = on, and is totally unsuitable for anything except throw-away instances where you can easily reconstruct the lot after a crash.


I have created 2 table variables in an attempt to match your environment.
I have added an UNION ALL to your original query, one query selects all other months but december, and the second selects only december.

there are other ways to do this, but in any way, it looks like an expensive query.

here we go:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

declare @JDT1 table(

ContraAct char(6) primary key clustered,
Account int,
RefDate datetime,
credit money,
debit money

)

declare @OACT table(
AcctCode int primary key clustered,
GroupMask int
)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311202',1,'20151231', 10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311203',1,'20151231', 10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311204',2,'20151231', 10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311205',2,'20151231',10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311206',1,'20151031', 10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311207',1,'20151031', 10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311208',2,'20151031', 10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311209',2,'20151031',10,7)

insert into @OACT(AcctCode,GroupMask)
values (2,4)

/* Profit for the Period 13 */
SELECT * FROM
(
        SELECT MONTH(T0.RefDate) AS Month,
              '311999' AS 'SAP Code',
              'Period Profit' AS 'Description',
              SUM (T0.[Credit] - T0.[Debit]) AS 'Amount',
              '311200' AS 'FatherNum',
              12 AS 'Group',
              'Liabilities' [Liabilities]
        FROM @JDT1 T0
       WHERE (T0.RefDate
              BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0)
              AND '20151231')
        AND T0.[ContraAct] <> '311201'
        AND MONTH(T0.RefDate) = 12
        GROUP BY T0.RefDate

        UNION ALL

        SELECT MONTH(T1.RefDate) AS Month,
              '311999' AS 'SAP Code',
              'Period Profit' AS 'Description',
              SUM (T1.[Credit] - T1.[Debit]) AS 'Amount',
              '311200' AS 'FatherNum',
              12 AS 'Group',
              'Liabilities' [Liabilities]
        FROM @OACT T0
             INNER JOIN @JDT1 T1
                     ON T0.[AcctCode] = T1.[Account]
        WHERE (T1.RefDate
               BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0)
                   AND '20151231')
        AND T0.GroupMask > 3
        AND MONTH(T1.RefDate) <> 12
        GROUP BY T1.RefDate

) AS q
PIVOT
(
SUM(Amount)
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

the results, you can compare october and december.


What would be the most efficient indexes to make to speed up these 4 queries?

Without more information the answer is "it depends" as what is optimal will vary depending on the distribution properties of A, B, and C (are any of them unique per row, that is are any of them your primary key or the subject of a unique index? or do they contain many duplicate values and if so how are those values clumped?), their types & sizes, and so on. Also, how wide is the table: does it have many more columns in addition to these three and/or columns containing a lot of data (such as long strings)?

General answers where possible:

  if I make an index on (A,B,C) that will speed up query 1 and 2

Correct.

  but I don't think it will do anything for query 3 or 4.

It could help query 3. The query planner it like to be able to use that index because it starts with A and that is one of the columns filtered by in query 3. It may chose not to depending on the selectivity of A and properties of the data int he other columns, the table more generally, and in more complex queries the other objects.

If the table is quite wide, that is it has noticeably more data per row than that held in these three columns, then this index may also help query 4: in the absence of any other useful indexes for this query the query planner may chose to scan the index on A,B,C to find the matching rows instead of scanning the whole table which could mean reading many more pages.

  indexes on (A, C) and (B, C), or is there a more efficient way to do this?

If your goal is query speed only then those two indexes may be optimal for these queries. An index on just C would probably (again, depending on the data in C and the other columns) be useful for both queries 3 and 4 as the planner can index-seek using the where clause on C then partial scan to find the rows matching the clause on B or A. This, depending on the sizes of your columns, will probably save disk space & working memory and speed up inserts and updates, but is likely to be less optimal for selects then the two wider indexes.

Getting more advanced: your choice of clustering settings could be significant (see http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index), particularly as you are using SELECT *, though Oracle is a little less flexible than SQL server (where any index could be the clustering key) on this matter.

To try it out for yourself, throw some realistic data into a copy of the table, and use EXPLAIN PLAN (see https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm) to see what indexes it chooses to use for your queries and other performance counters to see what wall-clock effect this has.


There is no such thing as X_$KSPPI. It is X$KSPPI. You have a synonym pointing to a non-existent object.

That synonym does not exist in Oracle databases, that was created by someone or something else.

Your attempt to desc sys.x$ksppi; fails if you do that any other user than SYS.

If your database could not access parameters, you would have a much bigger problem with more obvious symptoms.


Duplicating rows but incrementing the ID each time

If the id column has the AUTO_INCREMENT property, then it's pretty straight-forward. Don't include it in the insert list:

INSERT INTO tableX (store_id, column3, column4, ...)
SELECT 2, column3, column4, ...
FROM tableX
WHERE store_id = 1 ;

This will insert as many rows as there are with id = 1.

If you want to do this for multiple store_id values (not only for 2), i.e. for example for 10 store ids, inserting 10N rows where N is the number of rows with id = 1, you can extend to:

INSERT INTO tableX (store_id, column3, column4, ...)
SELECT s.store_id, t.column3, t.column4, ...
FROM tableX AS t
  CROSS JOIN
    ( SELECT 2 AS store_id UNION ALL
      SELECT 3 UNION ALL
      ---
      SELECT 11
    ) AS s
 WHERE t.store_id = 1 ;


Performance impact of having gaps in Identity Column in SQL Server 2005

I don't see how gaps in your identity values could have A N Y negative impact on your performance.

After all - the values are still all 4-byte integer, they're still as optimal for JOINs as anything can be ..... and even if you have gaps, the IDENTITY values are ever-increasing....

Of course, if you physically deleted rows from your clustered index, then there will be "holes" in your pages (the "Swiss Cheese" problem :-) ) - but those would be fixed by a nightly database maintenance that you certainly do have in place, don't you??

And even if you do occasionally delete a row from your table, the impact on your index fragmentation will be marginal.


Need help designing table with list of IDs to store

Do not use a list of ids. You will regret it. Use junction tables.

DDL:

create table organization (
  organization_id bigserial primary key,
  name text not null
);

create table individual (
  individual_id bigserial primary key,
  email_address varchar(255) not null
);

create table organization_member (
  organization_id bigint references organization(organization_id),
  individual_id bigint references individual(individual_id),

  primary key (organization_id, individual_id)
);

create table email_message (
  email_message_id bigserial primary key,
  send_at datetime not null,
  subject varchar(80),
  body text
);

create table email_message_organization (
  email_message_id bigint references email_message(email_message_id),
  organization_id bigint references organization(organization_id),

  primary key (email_message_id, organization_id)
);

DML:

-- add an org:

insert into organization (name) values ('Acme, Inc');

-- add an individual:

insert into individual (email_address) values ('user@example.com');

-- associate individual w org:

insert into organization_member values (1,1);

-- add an email message:

insert into email_message (send_at, subject) values ('2013-07-31 12:12:12', 'test');

-- associate an email message with an org:

insert into email_message_organization values (1, 1);

-- when it's time to send the email, get the right people:

select
e.subject,
e.body,
i.email_address
from individual i
join organization_member om using (individual_id)
join email_message_organization emo using (organization_id)
join email_message e using (email_message_id);

-- send the emails, record that they were sent in another table

You should use the Party model for representing individuals and organizations, which is not what I used, but you should still look it up.


Ok it's not an anwer just a better formated comment because you are wrong in some way, I has writen the below example.

DECLARE @tcal table(TagIndex smallint,TagName nvarchar(20),Total decimal(18,2)) ;

insert into @tcal
values
 (45,null,284.45)
,(45,null,233.97)
,(45,null,240.17)
,(45,null,81.39)
,(45,null,67.55)
,(45,null,0.00)
,(45,null,71.57)
,(45,null,72.91) ;

select  sum(CAST(Total AS decimal(18,2)))  FROM @tcal where TagIndex>44 ;
select  sum(Total)  FROM @tcal where TagIndex>44 ;

and it works. So you are filtering your data wrong or did some mistake.

My guess is your data is missing the first row because it's tag = 44.

(Remember in SQL > <> >=  =))


Conceptual Data Model match MySQL script?

You could enfoce your (1,4) cardinality, eg.

create table tbl2
    (idTbl1  int references tbl1,
     Tbl2Id int check (Tbl2Id in (1,2,3,4)) enforced,
     constraint tbl2_PK primary key (idTbl1,Tbl2Id))
;


-- ----------------------------
            -- Table structure for adresses
            -- ----------------------------
            DROP TABLE IF EXISTS `adresses`;
            CREATE TABLE `adresses` (
            `adress_id` int(11) NOT NULL AUTO_INCREMENT,
            `adress_adress` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
            `adress_adressnumber` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
            `adress_zipcode` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
            `adress_city` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
            `adress_postbus` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
            `adress_country` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
            `adress_building` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
            `adress_unit` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
            `company_id` int(11) DEFAULT NULL,
            `addresss_role` tinyint(4) DEFAULT NULL COMMENT 'Role 1 for company may be and 2 for comapny postal, feel free',
            PRIMARY KEY (`adress_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

            -- ----------------------------
            -- Records of adresses
            -- ----------------------------
            INSERT INTO `adresses` VALUES ('1', '1324234', '', '', '', null, '', null, null, '1', '2');
            INSERT INTO `adresses` VALUES ('2', '67856785678', '', '', '', null, '', null, null, '1', '1');

            -- ----------------------------
            -- Table structure for companies
            -- ----------------------------
            DROP TABLE IF EXISTS `companies`;
            CREATE TABLE `companies` (
            `company_id` int(11) NOT NULL AUTO_INCREMENT,
            `company_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
            `company_adress_id` int(11) NOT NULL,
            `company_postadress_id` int(11) NOT NULL,
            PRIMARY KEY (`company_id`),
            KEY `company_adress_id` (`company_adress_id`,`company_postadress_id`),
            KEY `company_adress_id_2` (`company_adress_id`,`company_postadress_id`),
            KEY `company_postadress_id` (`company_postadress_id`),
            CONSTRAINT `address` FOREIGN KEY (`company_adress_id`) REFERENCES `adresses` (`adress_id`) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT `postal_address` FOREIGN KEY (`company_postadress_id`) REFERENCES `adresses` (`adress_id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

            -- ----------------------------
            -- Records of companies
            -- ----------------------------
            INSERT INTO `companies` VALUES ('1', 'sdfgsdf', '1', '2');


How to update multiple rows without using JSON

I want to answer myself and close this question. Because I think I solved this problem. I appreciated James's comment for gave me an idea.
In my current solution looks like this.

UPDATE `accessable_menu` SET `is_active` = 'no' WHERE `user_role_id` = $USER_ROLE_ID;

INSERT INTO `accessable_menu`(`user_role_id`, `menu_id`, `is_active`)
VALUES (inputs..., 'yes')
ON DUPLICATE KEY UPDATE `is_active` = 'yes';

DELETE FROM `accessable_menu` WHERE `user_role_id` = $USER_ROLE_ID AND `is_active` = 'no';

Update old rows field is_active = 'no' and insert new values on duplicate key update with is_active = 'yes'

Then delete all rows didn't updated. It might be better performance than delete all rows and insert again. Because it deletes only few rows.


Extended event to track the logins from a specific login, but that login is filtering on a an active directory group

You cannot filter on the AD group.
This previous question explains how the access management works with AD groups:
How to tell which windows group login I used when logging in via windows authentication


As per W3Resource documentation MySQL OR operator
MySQL OR operator compares two expressions and returns TRUE if either of the expressions is TRUE.

Syntax:

OR, ||

When more than one logical operator is used in a statement, OR operators perform after AND operator. The order of evaluation can be changed by using parentheses.

The operator returns 1 when both operands are a non-NULL and one of them is nonzero and returns 0 when both operands are non-NULL and one of them is zero and returns NULL when one operand is NULL and other is zero and return 1 also when one is NULL and another operand is nonzero and NULL also when both operands and NULL.

Example

The following MySQL statement satisfies the condition - "both operands are a non-NULL and one of them is nonzero", so it returns 1.

SELECT 5 || 5;

MySQL> SELECT 5 || 5;
+--------+
| 5 || 5 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Example of MySQL OR operator with at least one (zero) 0

The following MySQL statement satisfies the condition - "both operands are non-NULL and one of them is zero", so it returns 1.

SELECT 5 || 0;

MySQL> SELECT 5 || 0;
+--------+
| 5 || 0 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Example of MySQL OR operator with at least one NULL operand

The following MySQL statement satisfies the condition - "one operand is NULL and other is zero", so it returns NULL.

SELECT 0 || NULL;

MySQL> SELECT 0 || NULL;
+-----------+
| 0 || NULL |
+-----------+
|      NULL |
+-----------+
1 row in set (0.01 sec)

Example of MySQL OR operator with NULL and non-zero operand

The following MySQL statement satisfies the condition - "one operand is NULL and other is non-zero", so it returns 1.

SELECT 5 || NULL;

MySQL> SELECT 5 || NULL;
+-----------+
| 5 || NULL |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)


Postgresql Date Function Returning Range In Readable Format

CREATE OR REPLACE FUNCTION readable_date_range(DATERANGE)
RETURNS TEXT AS $$

  SELECT CASE WHEN to_char(LOWER($1), 'YYYY-MM') = to_char(UPPER($1), 'YYYY-MM')
              THEN to_char(LOWER($1), 'FMMonth FMDDth to ') || to_char(UPPER($1), 'FMDDth YYYY')
              WHEN EXTRACT(year FROM LOWER($1)) = EXTRACT(year FROM UPPER($1))
              THEN to_char(LOWER($1), 'FMMonth FMDDth to ') || to_char(UPPER($1), 'FMMonth FMDDth YYYY')
              ELSE to_char(LOWER($1), 'FMMonth FMDDth YYYY to ') || to_char(UPPER($1), 'FMMonth FMDDth YYYY')
              END;

$$ LANGUAGE SQL STRICT IMMUTABLE;

Let's look at some output:

[[local]:5432] user =# SELECT readable_date_range('[2019-01-01,2019-05-03)');
     readable_date_range
─────────────────────────────
 January 1st to May 3rd 2019
(1 row)

Time: 63.280 ms
[[local]:5432] user =# SELECT readable_date_range('[2019-01-01,2019-01-03)');
   readable_date_range
─────────────────────────
 January 1st to 3rd 2019
(1 row)

Time: 0.668 ms
[[local]:5432] user =# SELECT readable_date_range('[2019-01-01,2022-01-03)');
         readable_date_range
──────────────────────────────────────
 January 1st 2019 to January 3rd 2022
(1 row)

Time: 4.563 ms

You could quite easily modify that to take two values instead.


In real terms, how much effect does a bad fillfactor have on performance?

This is only a partial answer to your question, because you don't provide enough information on how the database will be used.  I also think this question may be more appropriate on the database administrators site.

For instance, if the data is loaded nightly and the database is only used during the day for querying, then you want the fill factor to be 100%.  That will be the most efficient approach for querying, minimizing the number of pages.

Page splitting in indexes occurs when a new record lands on a page that is full.  The page is split into two pages, which are then, roughly, 50% full.  Once the page is split, there is more space for those values.

If the data is being updated with multiple updates or inserts per second, then page splitting is unnecessary overhead.  However, on indexes, this will probably happen the first time a new record lands on an index page.

Note that page splitting is going to happen anyway, once pages are filled.  This would happen anyway, regardless of the fill factor.  The major question is "start-up" time, because initially basically all inserts/updates will result in page splits.  But this should quickly dampen.

The overhead also depends on the characteristics of the implementation.  If the indexes fit into memory, or if your disk system is solid-state disk, then the overhead might not be noticeable at all.

My guess is that this is not the most important problem, unless consistency of performance when the new database first goes online is paramount.


Database design for premium subscription service

Remove the isPremium column has it can be computed from other database information.

Create a view over Subscribers_Table with something like:

CREATE VIEW premium_users AS
    SELECT DISTINCT UserId FROM Subscribers_TABLE
        WHERE date('now') BETWEEN StartDate and endDate
        AND planType='premium';


How do you manage a list for particular object. for eg. user comments on specific object(user post etc.)

As the comments indicate, you need to understand normalization.  Without too much theory, when you want a list for a particular object in a database here are some possibilities:

Create a new column for each additional list item.  If your list has a small concrete length this may be a viable option, however for most lists n items today becomes n+1 tomorrow and your table begins to have an unmanageable number of columns that require extra coding for joining and filtering.
Create a new table for each additional list.  If the lists are independent then this is the way to go, however, if the lists are the same, then creating and maintaining a new list table for each and every object becomes unmanageable.  Additionally, queries crossing objects require UNIONs on multiple tables.  These queries could become lengthy and have to be modified with each additional object.
Create a normalized table for this type of list.  If the list structure is the same for each object then a single table can be used to store any number of list items for any number of objects.  The tables definition should be defined and scoped.

For your particular question you have defined the list item as a comment, so you will simply need additional columns to indicate what object (post?) it goes with and what comment number it is.  You may also want to have columns indicating who made the comment, when it was made, and from where it was made.

Here is how this could look:

Post    CommentNumber  Comment
------  -------------  --------------------------
098763  1              Comment 1 for post 098763.
...
142525  1              Comment 1 for post 142525.
142525  2              Comment 2 for post 142525.
...
200526  1              Comment 1 for post 200526.


What can I do to monitor "out of shared memory" issues?

To monitor postgres logs you can navigate to cd "/usr/local/var/postgres" where you will find the server.log file.

If you want to watch the log in action you can pass the following command into the terminal tail -f server.log

Run your application in the background and if there is an issue occurring in postgres the server.log will catch it.

In my case, I ran RSpec in the background as that's where I saw the original "out of shared memory" message occur. The postgres log picked it up when it happen.