Enforce referential integrity between two groups of data I have two sets of records:

A collection of rules.
A collection of actions to perform based on those rules.

What I'm trying to model is something like this:

Multiple rules can be grouped together to form a composite rule.
Multiple actions can be grouped together to form a composite action.
When a rule(set) matches the input the corresponding action(s) should be performed.

Both the set of rules and the set of actions change very infrequently (once a year, if that). Because the data is pretty much read-only I came up with the following design.

A short note:
I explictly avoided adding extra lookup tables to define the group relationships, in the hope that doing so would both simplify and speed up my SELECT query (it will be run often). I'm open to the suggesting of adding them, but even with them in a design I'm still struggling to get the referential consistency I'm after:

CREATE TABLE Rules
(
  ID INT NOT NULL IDENTITY(1,1),
  GroupID INT NOT NULL,

  ... columns required by a rule

  CONSTRAINT PK_Rules_ID PRIMARY KEY (ID),
  CONSTRAINT UK_Rules UNIQUE(GroupID, other columns...)
)

CREATE TABLE Actions
(
  ID INT NOT NULL IDENTITY(1,1),
  GroupID INT NOT NULL,

  ... columns required by an action

  CONSTRAINT PK_Actions_ID PRIMARY KEY (ID),
  CONSTRAINT UK_Actions UNIQUE(GroupID, other columns...)
)

CREATE TABLE RuleToActionMappings
(
  RuleGroup_ID INT NOT NULL,
  ActionGroup_ID INT NOT NULL

  -- Note: These constrains are invalid, but I wished they weren't!
  CONSTRAINT FK_RTAM_RuleGroupID_ON_Rules_ID FOREIGN KEY (RuleGroup_ID) REFERENCES Rules(GroupID) ON DELETE CASCADE,
  CONSTRAINT FK_RTAM_ActionGroupID_ON_Actions_ID FOREIGN KEY (ActionGroup_ID) REFERENCES Actions(GroupID) ON DELETE CASCADE
)

In which I expect to insert multiple entries into both the Rules and Actions tables grouped by a common GroupID value. I could later query these tables in the following manner:

SELECT Actions.* FROM Actions
JOIN RuleToActionMappings As M ON M.ActionGroup_ID = Actions.GroupID
JOIN Rules ON M.RuleGroup_ID = Rules.GroupID
WHERE Rules.SomeColumn = 'SomeValue'

The problem with this design is there's no way to place the foreign key constraints I want/need on the RuleToActionMappings table, and the Rules and Actions tables are relying on a convention rather than something more concrete to ensure uniqueness.

Thanks @Joel Brown, your comment pointed me in the direction I needed to go. This is what I ended up with, which satisfies all of my requirements:

CREATE TABLE RuleGroups
(
    ID INT NOT NULL IDENTITY(1,1)
    CONSTRAINT pk_rulegroups PRIMARY KEY (ID)
)

CREATE TABLE Rules
(
    ID INT NOT NULL IDENTITY(1,1),
    GroupID INT NOT NULL,

    RequiredKeyInSourceData INT not null,
    RequiredValue int null

    CONSTRAINT pk_rules primary key (ID),
    CONSTRAINT fk_rules_groupID_on_rulegroups_ID FOREIGN KEY (GroupID) REFERENCES RuleGroups(ID) ON DELETE CASCADE
)

CREATE TABLE ActionGroups
(
    ID INT NOT NULL IDENTITY(1,1)
    CONSTRAINT pk_actiongroups PRIMARY KEY (ID)
)

CREATE TABLE Actions
(
    ID INT NOT NULL IDENTITY(1,1),
    GroupID INT NOT NULL,

    SomeColumn INT,
    SomeOtherColumn INT
    -- ... more columns

    CONSTRAINT pk_actions primary key (ID),
    CONSTRAINT fk_actions_groupID_on_actiongroups_ID FOREIGN KEY (GroupID) REFERENCES ActionGroups(ID) ON DELETE CASCADE
)

CREATE TABLE RuleToActionMappings
(
    RuleGroup_ID INT NOT NULL,
    ActionGroup_ID INT NOT NULL

    CONSTRAINT pk_mappings PRIMARY KEY (RuleGroup_ID, ActionGroup_ID),
    CONSTRAINT uc_mappings_rulegroup_ID UNIQUE (RuleGroup_ID),
    CONSTRAINT uc_mappings_actiongroup_ID UNIQUE (ActionGroup_ID),
    CONSTRAINT fk_rulegroup_ID_on_rulegroups_ID FOREIGN KEY (RuleGroup_ID) REFERENCES RuleGroups(ID) ON DELETE CASCADE,
    CONSTRAINT fk_actiongroup_ID_on_actiongroups_ID FOREIGN KEY (ActionGroup_ID) REFERENCES ActionGroups(ID) ON DELETE CASCADE
)

How I'm using it:
Get the rule groups from the DB and match the input data against them:

select Rules.GroupID, rules.RequiredKeyInSourceData, rules.RequiredValue from Rules

Then in the application code:

partition the rules into sets (by GroupID)
match the sets against the input data

stop when a complete set matches or all rulesets have been analyzed
exec a select query to find the corresponding action set

select Actions.* from Actions
join RuleToActionMappings as m on m.ActionGroup_ID = Actions.GroupID
where m.RuleGroup_ID = @MatchedRuleGroupID


How to identify database users and their owned schemas before dropping server logins? While deleting logins, there might be a possibility that there are users in databases along with chances that they own schemas in those databases.

Is there a way to find this out with a single select?

This is the warning message I get:

  Deleting server logins does not delete the database users associated
  with the logins.  To complete the process, delete the users in each
  database.  It may be necessary to first transfer the ownership of
  schemas to new users.

The query below will return the schema owner information.  You can change the JOIN to sys.database_principals to a LEFT JOIN to also return schema owners that are not logins, such as database roles and users without a login.

SELECT
      sp.name AS OwningLogin
    , dp.name AS OwningUser
    , s.name AS SchemaName
FROM sys.schemas AS s
JOIN sys.database_principals AS dp ON
    dp.principal_id = s.principal_id
JOIN sys.server_principals AS sp ON
    sp.sid = dp.sid;


AUTO_CREATE_STATISTICS set to OFF in tempdb It has come to my attention that a number of my databases (2008 & 2012) do not have the setting AUTO_CREATE_STATISTICS set to ON for the tempDB.
In the Microsoft documentation it is set to ON as default.
Does turning the setting off have any performance enhancements/reductions?
What are the implications of turning this OFF?
Will any temporary tables or internal objects be suffering?
Thanks

http://searchsqlserver.techtarget.com/tip/SQL-Server-tempdb-best-practices-increase-performance

You can further increase tempdb performance by disabling the auto update stats, which will save your tempdb some work. Usually objects created in the tempdb are fairly small and, as such, the statistics will not reach the threshold that causes the statistics to update automatically. By disabling the setting, you stop SQL Server from having to check to see if it needs to update the statistics. However, you'll need to manually update statistics if you occasionally use large temporary objects.

You can also set the auto create statistics option to false. That, too, will increase tempdb performance. By disabling the setting, you stop SQL Server from having to produce the statistics when the object is first created.

Settings should be changed with care. Depending on the kind of load you place on your tempdb, changing settings could adversely impact system performance. You should test the settings in a non-production environment before they are deployed to the production environment. After deployment, monitor these changes closely to ensure the system is running at peak performance.


SQLServer Row level security - Enforce from within database Have a multi-tenant app running on a single SQL Server 2016 db. I have a RLS (row level security ) predicate on customer_id (unique for each tenant) and the column exists in all tables.

CREATE FUNCTION dbo.[RLSPredicate] (@CustomerId bigint)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS RLSPredicateResult WHERE (@CustomerId =
CAST(SESSION_CONTEXT(N'customer_id') AS bigint))

GO

 -- two table example
 CREATE SECURITY POLICY [dbo].[RLSPolicy]
 ADD FILTER PREDICATE [dbo].[RLSPredicate] ([customer_id]) ON [dbo].[user],
 ADD FILTER PREDICATE [dbo].[RLSPredicate] ([customer_id]) ON [dbo].
  [user_email]
  WITH (STATE = ON, SCHEMABINDING = ON)
  GO

  -- Sample Query
  EXEC sp_set_session_context @key=N'customer_id', @value='1231312'
  select top 1000 * from [user] where customer_id = 1231312

With the above design, EXEC sp_set_session_context always needs to be executed before any user query hits the database, however we have had cases where the application team forgot to add code EXEC sp_set_session_context in new projects.

The problem is that the there is no way to guarantee that queries coming to the db always have EXEC sp_set_session_context in them. The queries come in all forms, EXEC, sp_executesql, adhoc etc.

I would like to know if there is any way to redesign the filter predicate so that RLS is enforced from within the database and remove dependency of running EXEC sp_set_session_context before every query.

There are more than 500K customer_id values.

I think you should use a login trigger and SET CONTEXT_INFO which quoting BoL

  Associates up to 128 bytes of binary information with the current session or connection.

Then you can retrieve the data by calling the function with a select

i.e. SELECT CONTEXT_INFO()

You can then use the data recalled from the connection with Row Level Security to implement the filtering you desire.

If you are running SQL Server 2016 or later then you should use the more modern session_context() function instead - see Phase out CONTEXT_INFO() in SQL Server 2016 with SESSION_CONTEXT() by Aaron Bertrand.


Rman not replacing logs with same name on networked drive Historically, rman saved logs by replacing files of the same name so that the most recent set was always there, and logs from backup of older than the most recent set simply went away.

Rman is successfully saving the log files to the mapped network drive location, but it only saves the first go round of the assorted backup type running through the Windows Scheduler.

If I move the first set of log files to an archive directory, rman stores up another full set. Using full backups, Incremental Level 0 and Incremental level 1 backups.

Previously, when rman was saving to a location on the server of the target database, it would always simply replace one set of backup logs with a newer version of the same type, since they (of the same type) would all have the same name.

Can one place the date (_d%) or some other data into the name so that the names will never be duplicated? That is not what I really want, but it would be far better than not getting logs, or having to remember to archive logs each rotaion to get new logs saved.

When I have tried placing name switches in rman logs, I found it just did not save the logs. so I must not have been doing it right, if it is possible. If someone could help me learn how to order rman log names, i would appreciate it.

None of this has any effect on the actual backups, they are working great.

Thank you....

If logging with RMAN, you'd have to set the filename yourself - there's no wildcard.  But there is an APPEND option.

spool log to 'my_log_file' append

Similarly, invoking RMAN with the APPEND option causes it to append to any log file specified.

Our backup scripts do the log maintenance at the OS level:

export LOGPREFIX=$ORACLE_ADMIN/$ORACLE_SID/rman/log/my_backup_script
export MYLOG=${LOGPREFIX}.$$   # add the pid to the log file name
rman log=${MYLOG} append <<EOF
... various RMAN commands
EOF

#Perform error checking and notification
fgrep -i RMAN- ${MYLOG}
if [ $? -eq 0 ]
then # we had a failure - email me
   mail -s "$0 : failure for `date +%m/%d/%y`" me@myself_and_i.com < ${MYLOG}
fi
# add the incremental log to the permanent log
cat ${MYLOG} >> ${LOGPREFIX}.log
# clean up the incremental log
/bin/rm -f ${MYLOG}


Documenting where stored procs are called from I've been wishing lately that we documented what all places stored procedures are called from. It would have to be pretty painless to do though... i.e. doing it in excel wouldn't be very good.

Seems like this might be a common desire (probably even a requirement for some companies) so I'm wondering if there are any tools/applications/add ins out there already that facilitate this?

EDIT: To be clear, I'm not really looking for tools that help me identify what needs to be documented (although I would still love to hear about any you think I may be unaware of), but rather a tool that helps me do the documenting.

SQL Server has the sys.sql_dependencies table.

In SSMS you can right-click an object and choose "dependencies". My subjective experience is that this can be incomplete.

References from application code may be amenable to a simple search, depending on how it's constructed.


Scheduling a procedure with DBMS_SCHEDULER. PLS-00103: Encountered the symbol “NULL” when expecting one of the following: ; I can't schedule a job to run.
I made a procedure that writes CSV files and it works when I execute it.
Then I tried to make a job to schedule it:
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'AUTOMATE_CSV_EXPORT',
    job_type => 'STORED_PROCEDURE',
    job_action => 'EXPORT_ALL_LYS_FOR_TABLEAU',
    number_of_arguments => 0,
    start_date => '20-AUG-20 2.05.00PM',
    repeat_interval => 'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=23;',
    end_date => NULL,
    enabled => FALSE,
    auto_drop => FALSE,
    comments => 'hello i am a comment');

  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'AUTOMATE_CSV_EXPORT',
    attribute => 'logging_level',
    value => DBMS_SCHEDULER.LOGGING_RUNS);

  DBMS_SCHEDULER.enable(
    name => 'AUTOMATE_CSV_EXPORT');
END;
/

It didn't run at the scheduled time, but it did when I ran it like this:

I tried to edit the start time in the wizard and it gives me this error:

I scheduled it to run every 2 minutes starting a while ago, and it hasn't run yet. It's definitely enabled.

Troubleshooting
TLDR: scheduler works!!! The only thing that doesn't work is me putting in my email to get notifications.

Am i running it in the same schema I created it in?

I changed the job to run every 2 minutes.

^^^ so it is working? But I don't see any CSVs in the destination folder. I changed the start date to NULL like the code below and it worked! I have CSVs!

Testing a scheduled job:
test:

create or replace procedure test_print
is
begin
    dbms_output.put_line('Current date and time is : ' ||
    to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM'));
end;

set serveroutput on;

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'JOB_TEST',
    job_type => 'STORED_PROCEDURE',
    job_action => 'TEST_PRINT',
    number_of_arguments => 0,
    start_date => NULL,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=2;',
    end_date => NULL,
    enabled => FALSE,
    auto_drop => FALSE,
    comments => 'Testing job runs');

DBMS_SCHEDULER.enable(name => 'JOB_TEST');
END;
/

select job_name,output,status,owner
from user_scheduler_job_run_details where  job_name='JOB_TEST';

results:

The thing that still doesn't work is notifications via email. When that's the only thing i edit, i get this error:

Most probably you are creating objects in different schemas,verify created jobs and job details in current schema with these views user_scheduler_jobs and user_scheduler_job_run_details
Simple procedure scheduled to run every 2 minutes
  SQL>create or replace procedure test_print
      2  is
      3  begin
      4  dbms_output.put_line('Current date and time is : ' || to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM'));
      5* end;
    SQL> /

    Procedure created.

    SQL> exec test_print();
    Current date and time is : 20-08-2020 09:22:59 PM

    PL/SQL procedure successfully completed.

    SQL> BEGIN
      2             DBMS_SCHEDULER.CREATE_JOB (
      3              job_name => 'JOB_TEST',
      4              job_type => 'STORED_PROCEDURE',
      5              job_action => 'TEST_PRINT',
      6              number_of_arguments => 0,
      7              start_date => NULL,
      8              repeat_interval => 'FREQ=MINUTELY;INTERVAL=2;',
      9              end_date => NULL,
     10              enabled => FALSE,
     11              auto_drop => FALSE,
     12              comments => 'Testing job runs');
     13
     14
     15
     16
     17
     18              DBMS_SCHEDULER.enable(
     19               name => 'JOB_TEST');
     20  END;
     21  /

  SQL> select job_name,output,status from user_scheduler_job_run_details
       2  where  job_name='JOB_TEST';

JOB_NAME   OUTPUT                                             STATUS
---------- -------------------------------------------------- ---------------
JOB_TEST   Current date and time is :20-08-2020 09:30:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:32:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:34:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:36:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:38:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:40:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:42:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:44:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:46:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-08-2020 09:48:05 PM   SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 09:50:05 PM  SUCCEEDED -- Modified date format in procedure
JOB_TEST   Current date and time is :20-AUG-2020 09:52:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 09:54:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 09:58:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 10:02:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 10:04:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 09:56:05 PM  SUCCEEDED
JOB_TEST   Current date and time is :20-AUG-2020 10:00:05 PM  SUCCEEDED

18 rows selected.

SQL> select job_name,last_start_date,last_run_duration from user_scheduler_jobs
  2  where job_name='JOB_TEST';

JOB_NAME   LAST_START_DATE                                    LAST_RUN_DURATION
---------- -------------------------------------------------- ------------------------------
JOB_TEST   20-AUG-20 10.08.05.070000 PM US/CENTRAL            +000000000 00:00:00.009000

Edit:- Just observation you were able to run job manually but not running from scheduler implies job and procedure are not in the same schema.


Oracle Support Tips & Tricks How can My Oracle Support (Metalink) be used effectively?

What are your secret hints to this system? What saved you the most time?

Don't overlook the "weird error lookup" tools. Most of the generic errors in oracle (i.e. ora-00600) have a lookup tool in Metalink that given the right parameters from the dump may actually help you more than you would realize.


E/R diagram for sports league database I am attempting to create a data model for a sports league

There are two entities in my domain. The first entity is a Manager and the second is a Team.

A Manager has

personnel id
first name
last name
birth date
birthplace

A Team has one Manager

If these are the design constraints, then how can we connect these two constraints?

The unique identifier for Manager would be the personnel id.  That identifier would migrate to Team as a column and thus refer back to Manager.  This will ensure each Team can be managed by one and only one Manager.  This constraint would be a Foreign Key constraint.  The constraint on Manager, which specifies the personnel id as the key, would be called a Primary Key constraint.


In Sql Server what is the difference between VAS and Buffer Pool? My understanding:

In Sql Server,

Buffer Pool is where all the Data/Proc/Log Cache, etc are located.
Virtual Address Space is a logical location that refers to physical locations (either in RAM or page file).

But this statement is confusing to me:

  "..MultiPage allocation happens in the VAS Area and Single page
  allocation comes from Buffer Pool..."

What is the difference between Buffer Pool and VAS?

No buffer pool and VAS are not the same thing. In SQL Server direct physical memory access is not allowed any memory request which comes is first mapped to process VAS and then if SQL Server find memory free it would map this VAS address to physical memory and then memory becomes committed.

Bufferpool is physical memory VAS is virtual memory.

VAS

VAS is Total amount of Virtual address space visible to process. Total VAS in windows OS is divided into VAS for OS process and VAS for application. SQL Server runs as an application. For 64 bit system total VAS is 16TB. So now both SQL sever and Windows process gets VAS of 8TB.

Whenever a new process wants to read data or write data into memory  it would reference memory in its VAS region so the new process would see VAS of 8TB and then it would be mapped to physical memory. This physical meory would be your buffer pool

Buffer pool

A buffer is an 8 KB page in memory, the same size as a data or index page you can consider buffer as a frame which holds data and index pages when they are brought from disk to memory.   SQL Server buffer manager manages the task of reading data pages into buffer pool and also writing it to disk. It is a reserved memory store for SQL Server and by default if you do not set value for it it will take as much memory as possible. Buffer pool only allocates memory to requests which requires less than 8 KB pages this feature changed in SQl Server 2012 where buffer pool has no meaning its just consumer. It is easy to allocate small contiguous amount of memory than large contiguous amount( Large contiguous amount might not be free or present to allocate).

32 Bit system

In 32 bit system SQL Server has VAS of 2G. If system is WOW it would have VAS of 4 G. So this becomes limitation for process which requires huge amount of continuous memory and such memory is not satisfied from buffer pool but outside buffer pool.

  "..MultiPage allocation happens in the VAS Area and Single page allocation comes from Buffer Pool..."

This statement is only true for SQL Server 2005 to 2008 R2. Page allocation >8KB is not done by buffer pool. The allocator which does page allocation for buffer pool is single page allocator while allocator which does allocation for memory pages >8KB is multi page allocator and  memory to this is allocated outside of buffer pool.

For SQL Server 2012 there is Any page allocator which does allocation for both pages <=8KB and pages >=8KB


Creator of a MySQL table automatically granted all table permissions? I recently read it in SilberSchatz book that in MySQL the creator of a relation/view is the owner and granted all permissions on the relation/element including the power to transfer these grants to other users.

I tested this on MySQL myself:

Logged in root account

mysql -u root -p

I created a new user test_user.

create user test_user identified by ''

Then I gave grant to test_user for creating new tables on whole database "testing" which already exists

grant CREATE on testing.* to test_user

Then I logged into test_user account

mysql -u test_user -p

I created a new table TABLE1

create table table1(id int auto_increment,name varchar(20), primary key (id));

But when I insert data into this table it is giving error.

insert into table1 values(1,'alankrit');

  ERROR : INSERT command denied to user 'test_user' for table 'table1'

When I do give test_user INSERT grant from root then I can insert data into table1.

The question is that test_user is creating table TABLE1 so why is he not getting INSERT grant on this table on its own as emphasised in this book.

Secondly, do we need to ask root to grant us permissions on everything we create on our own? Wouldn't this be problematic? How will this work?

Seems Silber Schatz doesn't have it fully explained,

Because CREATE doesn't implicitly imply INSERT.

For a database level grant ALL is usually used.


Mysql temp table insert from json can't handle null values The MySql-8 JSON_TABLE function allows you to handle JSON data as if it was a table.

I'm trying to use it to populate a temporary table using the generated table but I believe I'm hitting a bug.

When a null value is inserted into an INT NULL column, the following error is thrown : Invalid JSON value for CAST to INTEGER from column quantity at row 1

Here is the offending code

DROP TABLE IF EXISTS json_temp_table;
CREATE TEMPORARY TABLE json_temp_table (
  item_id int NOT NULL PRIMARY KEY,
  model_number varchar(100),
  quantity int NULL
)
ENGINE = INNODB
SELECT
  json_tb.item_id,
  json_tb.model_number,
  json_tb.quantity
FROM JSON_TABLE
(
'[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
, "$[*]"
COLUMNS
(
item_id int PATH "$.item_id",
model_number varchar(100) PATH "$.model_number",
quantity int PATH "$.quantity"
)
) json_tb;
SELECT
  *
FROM json_temp_table

Is there a way to make json-generated tables insert nulls into temp tables ?

The weird part is the select statement works fine without the insert. Also, temp tables by themselves can definitely handle null int values. I don't understand why those two don't mix.

The solution is to ommit the property which you want nulled.

After searching for a while I found this bug report from over a year ago that explains how a json string that contains the value NULL is actually a value and not a null.

{"item":1,"quantity":4} --> item = 1, quantity = 4

{"item":1,"quantity":null} --> item = 1, quantity = 'null' ( Some kind of unfriendly json null )

{"item":1} --> item = 1, quantity = NULL ( Your good old sql null ! )

After seeing this, I thought "there's gotta be a way to cast this unfriendly json null into a nice null". I tried many things which worked, but none of them were insertable into a temporary table.

This was my final attempt which should work, based on what I stated above. I manage to detect if the value is null using an in-line IF statement, then I try to cast an actual SQL NULL into a SIGNED int. I demonstrate how you can select the values but that it's impossible to insert them for some reason.

The code itself :

DROP TABLE IF EXISTS json_temp_table;
CREATE TEMPORARY TABLE json_temp_table (
  item_id int NOT NULL PRIMARY KEY,
  model_number varchar(100),
  quantity int NULL
)
ENGINE = INNODB;

INSERT INTO json_temp_table
SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM (
SELECT
  json_tb.item_id,
  json_tb.model_number,
  json_tb.quantity,
  if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null'
FROM JSON_TABLE
(
'[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
, "$[*]"
COLUMNS
(
item_id int PATH "$.item_id",
model_number varchar(100) PATH "$.model_number",
quantity int PATH "$.quantity"
)
) json_tb) x;

Executed on the CLI mysql client

mysql> DROP TABLE IF EXISTS json_temp_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TEMPORARY TABLE json_temp_table (
    ->   item_id int NOT NULL PRIMARY KEY,
    ->   model_number varchar(100),
    ->   quantity int NULL
    -> )
    -> ENGINE = INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM (
    -> SELECT
    ->   json_tb.item_id,
    ->   json_tb.model_number,
    ->   json_tb.quantity,
    ->   if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null'
    -> FROM JSON_TABLE
    -> (
    -> '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
    -> , "$[*]"
    -> COLUMNS
    -> (
    -> item_id int PATH "$.item_id",
    -> model_number varchar(100) PATH "$.model_number",
    -> quantity int PATH "$.quantity"
    -> )
    -> ) json_tb) x;
+---------+--------------+----------+
| item_id | model_number | quantity |
+---------+--------------+----------+
|       1 | MFJA53       |        4 |
|       2 | HSRHJN5      |     NULL |
|       3 | FAFAF1       |      345 |
+---------+--------------+----------+
3 rows in set, 1 warning (0.00 sec)

mysql> INSERT INTO json_temp_table
    -> SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM (
    -> SELECT
    ->   json_tb.item_id,
    ->   json_tb.model_number,
    ->   json_tb.quantity,
    ->   if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null'
    -> FROM JSON_TABLE
    -> (
    -> '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
    -> , "$[*]"
    -> COLUMNS
    -> (
    -> item_id int PATH "$.item_id",
    -> model_number varchar(100) PATH "$.model_number",
    -> quantity int PATH "$.quantity"
    -> )
    -> ) json_tb) x;
ERROR 3156 (22018): Invalid JSON value for CAST to INTEGER from column quantity at row 1
mysql>


How to quickly startup/shutdown Oracle 11? I am wondering what is the quickest way to properly startup/shutdown a Oracle DB daemon (Oracle 11.2, installed on a test machine).

I need it for C/C++ programs that use the OCI/Pro*C API.

I want this because I am used to the startup speed of PostgreSQL, and because the daemon runs in a virtual machine which is only started (on-demand) for test cases.

Currently I script it like this - startup:

sqlplus /nolog <<EOF
connect / as sysdba
startup
quit
EOF
lsnrctl start
emctl start dbconsole

And shutdown:

emctl stop dbconsole
lsnrctl stop
sqlplus /nolog <<EOF
connect / as sysdba
shutdown
quit
EOF

This works - programs work as expected - but this procedure is quite slow.

The Oracle DB runs on CentOS 6.3, it is the free (as-in-beer) available 'standard version'.

You can use the dbstart/dbshut scripts which come with an Oracle install. They are available under $ORACLE_HOME/bin.

After a fresh install you have to edit the /etc/oratab file:

# cat /etc/oratab
# format: $ORACLE_SID:$ORACLE_HOME:N|Y
my_sid:/home/juser/app/juser/product/11.2.0/dbhome_1:N
# sed -i 's/:N$/:Y/' /etc/oratab
# grep my_sid /etc/oratab
my_sid:/home/juser/app/juser/product/11.2.0/dbhome_1:Y

Then you can use the scripts like this:

$ whoami
juser
$ dbstart $ORACLE_HOME
$ # execute DB jobs ...
$ dbshut $ORACLE_HOME

dbstart brings all up which is needed for Pro*C/OCI programs.

Using dbstart/dbshut is an improvement above the custom method mentioned in the question:

method                time    called tools
―――――――――――――――――――――――――――――――――――――――――――――――――――――
dbstart              5.7 s    lsnrctl, sqlplus
dbshut               5.7 s    lsnrctl, sqlplus
custom startup      27.9 s    lsnrctl, sqlplus, emctl
custom shutdown     31.0 s    lsnrctl, sqlplus, emctl

(times on a Core i7/2.8GHz system, slow spinning hard disk.)

How dbstart/dbshut work

A dbstart $ORACLE_HOME$ call is basically equivalent to:

$ lsnrctl start
$ echo -e 'connect / as sysdba\nstartup\nquit'| sqlplus /nolog

And a dbshut $ORACLE_HOME$ is basically equivalent to:

$ lsnrctl stop
$ echo -e 'connect / as sysdba\nshutdown\nquit'| sqlplus /nolog

(you can verify if everything is shutdown via ps aux | grep 'tnsl\|ora')

Note that the order of the commands is important. That means when lsnrctl start is executed after the sqlplus-startup command then the Pro*C/OCI program still complains about an unavailable TNS-listener.

And this is exactly the problem with the command sequence in the question - where the emctl start just workarounds the wrong order because it fixes the TNS-listener setup part.

Also note that for executing Pro*C/OCI programs the EMCTL service is not needed.


Has my 'cluster' operation succeeded? I ran this command:

cluster verbose address using address_locality_lookup;

And got this result:

INFO:  clustering "dba.address" using sequential scan and sort
INFO:  "address": found 0 removable, 29323623 nonremovable row versions in 1139047 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 26.00s/8286.70u sec elapsed 8342.01 sec.
CLUSTER
Time: 9366585.421 ms

Does that indicate that clustering succeeded (ie all the rows are now in the order indicated by the address_locality_lookup index)?

What do 'removable', and 'nonremovable' indicate?

Yes, this means it succeeded.  Normally (when the client settings are like that), you get back an echo of the command executed, like CLUSTER in this case.

In newer PostgreSQL versions, CLUSTER and VACUUM FULL do mostly the same (in fact, to my knowledge, they share the same code), the only difference being the ordering by the former.  What you see are typical output of the different VACUUM commands, describing that there were no stale rows in your table.

These can be either old versions of some existing rows, replaced by an UPDATE or deleted ones, which in both cases hadn't been removed by a previous VACUUM operation.  These are marked as 'removable', as opposed to some which may still be referenced by some open transactions.  When the transactions are closed, these will also be removable.


For merge replication, do I need to schedule index maintenance on all sites or just publisher? I have a very fragmented database that uses merge replication with remote sites. Do I need to schedule index maintenance (reorganize / rebuild) for all sites? Or just on publisher copy?

The somewhat sledgehammer approach and "best practice" answer would be yes run index maintenance on all sites.

With that said, Index Fragmentation is only truly an issue if it's causing you a noticeable performance penalty. You don't "have to" perform maintenance if there's no real value in doing so.

The performance of each database/instance in the replication topology could be evaluated independently to determine if index maintenance would prove to be beneficial. High fragmentation as a metric in isolation does not necessarily mean that index maintenance would result in a noticeable performance improvement.

By taking into consideration the size of the Index, query data access patterns, storage configurations and query response times, you can establish if index maintenance would prove beneficial and deliver a more refined index maintenance strategy. Assuming it's worth the effort......


RMAN deletes all conrtolfile autobackups We have Oracle 18c and RMAN with:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+FRA';

We have incremental backups also. There was no 'backup current controlfile' in a backup script, but control file was backed up every time we did backup.
Could someone tell why "DELETE OBSOLETE" deletes all copy of autobackup controlfiles (except lastest one) when they still needed for database recovery? Like, WTF? If controlfile is redundant why RMAN does not delete related backup set, for example. Can't get it. And yes - we have tons of so called "backups" with no controlfile.
TSPITR fails with:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/29/2020 17:39:13
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore

Hopefully this has been fixed in more recent versions but I had a very nasty experience with this on an 11gR2 training course, many moons ago.

"... REDUNDANCY 1 ... "

... tells Oracle to keep a single backup copy of each file.
It does not tell Oracle to keep a single, consistent (i.e. recoverable) set of backup files!
An entire class of trainees happily built shiny, new databases and then took a backup of them.
So far, so good.
Much later in the day, then got to the exercise of taking another backup, trashing their database and restoring it.
Problem was, the backup ran out of disk space part way through, leaving an inconsistent and unrecoverable set of files - a mish mash of the earlier backup and the new one.
"REDUNDANCY 2" would have prevented this, retaining the previous, recoverable set of backup pieces.
As would more disk space, obviously.
I'd recommend using a RECOVERY WINDOW instead.  It will fall more naturally in line with your [company's] Recovery Strategy.


Does mongo *really* need a dedicated volume, if I need to back it up? TLDR; What's the easiest way to automate a mongodb backup of a 20-gig DB?
We run mongo on the same KVM (VPS) as the web app; the server runs Debian.
We/I don't need/want to backup the entire fs on a daily basis, but definitely the Mongodb database.
I don't really want to put Mongo in a separate volume, it complicates things and increases likelihood of downtime, especially when I need to expand the volume in question. However, using the LVM to create snapshots seems like the easiest (actually, the only) way forward.
I've been told that when oplog is in use hot snapshots won't be corrupt.
I found this guide demonstrating how to set up this backup method, but it's almost 10-years out of date. Is there an easier way?
I have read everything I could find, several times, on the man-pages.
Thanks for reading :)

Yes, hot snapshot is easy and fast way to do backup... But of course there is also mongodump. Here is my crontab entry. Every hour, overriding current date backup.
SHELL=/bin/bash
DATEVAR=date +%d

31 * * * * /usr/bin/mongodump --host localhost -o /dbdata/dump$($DATEVAR) --gzip --oplog >> /dev/null 2>&1


How to find number of user connections to an Oracle database between a specific time period? I want to find how many users established connections to the oracle 11g database in a 10 minute time interval. Please help

That is what we have auditing for. Enable auditing, for example:

alter system set audit_trail=db scope=spfile;

You need to restart the database for this setting to take effect.

Audit logins:

audit create session;

If you created your database with DBCA, both the above are automatically enabled in 11.2.

Then you can query and browse logins in DBA_AUDIT_SESSION.


Creating Stored Procedure in local db that references tables in linked server I currently have a local server linked to a remote server.

Is it possible to create a stored procedure within the local server, but querying data from tables within the linked server. I am aware there may be performance issues, but I am reluctant to create the sp on the linked server as it would require obtaining permissions to do so.

I believe my problem is syntax but I cannot identify exactly what,

USE [LOCALDB]  --my local database or should I reference linked server db here
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[mystoredprocedure]
    DECLARE @linkedserv NVARCHAR(150)
    DECLARE @linkeddb NVARCHAR(150)

    SET @linkedserv = 'HOSTNAME\SERVER';
    SET @linkeddb = 'remotedb';

    SELECT (CASE LTRIM(RTRIM([COLUMN1]))
        WHEN '' Then ''
        WHEN 'THIS' THEN 'THAT'
        WHEN 'NOW' THEN 'NEVER'
        ELSE 'OTHER' END) [Options]
    INTO #TempTable
    FROM @linkedserv.@linkeddb.dbo.TableOnLinkedServer --is this an issue?
    FULL OUTER JOIN OtherTableOnLinkedServer ON TableOnLinkedServer.COUMN1 =
    OtherTableOnLinkedServer.COLUMN0

The linked servers db and it's tables are queried multiple times throughout the sp so finding a shorthand way of referencing them would be great,

Cheers

You can't parameterize entity names into a T-SQL statement. In order to do this you need to (a) create the #temp table first, and (b) use dynamic SQL. Here is one approach:

CREATE PROCEDURE [dbo].[mystoredprocedure]
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @exec nvarchar(256),
    @linkedserv nvarchar(150),
    @linkeddb   nvarchar(150),
    @sql        nvarchar(max);

  SELECT @linkedserv = N'HOSTNAME\SERVER',
         @linkeddb   = N'remotedb';

  SELECT @exec = QUOTENAME(@linkedserv) + N'.'
               + QUOTENAME(@linkeddb) + N'.sys.sp_executesql';

  CREATE TABLE #TempTable([Options] varchar(64));

  SELECT @sql = N'SELECT (CASE LTRIM(RTRIM(ot.[COLUMN1]))
        WHEN ''''     THEN ''''
        WHEN ''THIS'' THEN ''THAT''
        WHEN ''NOW''  THEN ''NEVER''
        ELSE ''OTHER'' END) AS [Options]
    FROM dbo.TableOnLinkedServer AS t
    FULL OUTER JOIN dbo.OtherTableOnLinkedServer AS ot
      ON t.COLUMN1 = ot.COLUMN0;';

  INSERT #TempTable([Options]) EXEC @exec @sql;

  SELECT * FROM #TempTable;
END
GO

You could do it all inside dynamic SQL, so if knowing the columns up front is a challenge, there is a way. But it's messy, assuming you actually need a #temp table, because then everything you do with #TempTable has to be done inside the dynamic SQL.

CREATE PROCEDURE [dbo].[mystoredprocedure]
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @exec nvarchar(256),
    @linkedserv nvarchar(150),
    @linkeddb   nvarchar(150),
    @sql        nvarchar(max);

  SELECT @linkedserv = N'HOSTNAME\SERVER',
         @linkeddb   = N'remotedb';

  SELECT @exec = QUOTENAME(@linkedserv) + N'.'
               + QUOTENAME(@linkeddb) + N'.sys.sp_executesql';

  SELECT @sql = N'SELECT (CASE LTRIM(RTRIM(ot.[COLUMN1]))
        WHEN ''''     THEN ''''
        WHEN ''THIS'' THEN ''THAT''
        WHEN ''NOW''  THEN ''NEVER''
        ELSE ''OTHER'' END) AS [Options]
    INTO #TempTable
    FROM dbo.TableOnLinkedServer AS t
    FULL OUTER JOIN dbo.OtherTableOnLinkedServer AS ot
      ON t.COLUMN1 = ot.COLUMN0;

  SELECT * FROM #TempTable;';

  EXEC @exec @sql;
END
GO

Please look at the obligatory Erland Sommarskog article on dynamic T-SQL to ensure you don't end up creating an embarrassing SQL Injection vulnerability.


Understanding two NICs requirement for SQL fail over cluster I am trying to make an SQL Server 2012 failover cluster. I have two DB machines. I understand that both machines need 2 NICs each. In my organization the IP scheme assigned is something like 192.168.1.X.

So I want to know if I assign 1 IP to each NIC, will that be enough? Like assigning 192.168.1.50, 192.168.1.51, 192.168.1.52 and 192.168.1.53?

Or two of the NICs in each machine have to have some private network scheme which will allow them to communicate directly?

First, there's no SQL Server 2013, but the good news is that the version number of SQL Server doesn't matter. It's more based on Windows failover cluster networking, which is explained here in a 3-part series by the Microsoft folks:

http://blogs.technet.com/b/askcore/archive/2010/02/12/windows-server-2008-failover-clusters-networking-part-1.aspx

And then additional info in part 4:

http://blogs.technet.com/b/askcore/archive/2010/04/15/windows-server-2008-failover-clusters-networking-part-4.aspx

Short story - you need some network redundancy, but you don't have to get it with multiple IP addresses. Instead, you get it with network teaming software.


Is using multiple unique constrains on a single table considered bad design? I was looking at PostgreSQL's INSERT INTO .. ON CONFLICT (..) DO UPDATE .. syntax and realized, you cannot do multiple unique constraint checks with it. I mean, you either refer to a composite unique index by the column names ON CONFLICT (Name, Symbol) (if the unique index is defined for these two columns), or you use the primary key. If you define two separate unique indexes for the columns, you can only check for one.

CREATE TABLE student
    (Id int primary key, Name varchar(50), Symbol varchar(50),
      CONSTRAINT col1_unique UNIQUE (Name),
      CONSTRAINT col2_unique UNIQUE (Symbol)
    );

INSERT INTO student
    (Id, Name, Symbol)
VALUES
    (1, 'John', 'J'),
    (2, 'David', 'D'),
    (3, 'Will', 'W');

INSERT INTO student
    (Id, Name, Symbol)
VALUES
    (4, 'Jeremy', 'J')
   on conflict(Name) DO UPDATE
   set Name = 'Jeremy';

Could throw an error, saying J is a duplicate. However, this example is simply a bad design, because the Symbol should be in another table and be connected to the student table via a one to many relationship. Which is why I am wondering, maybe PostgreSQL's on conflict was designed this way, because you can ALWAYS restructure the tables in a way, where there is only a single unique index. Is it true or there is an another reason?

Example fiddle: http://www.sqlfiddle.com/#!17/9c0ce

There is always Sixth, or Domain Key, Normal Form. Here each non-key column becomes it own table. So 3NF table T(Key, Col1, Col2, ..) becomes T1(Key, Col1), T2(Key, Col2) etc. Those new tables which require uniqueness can have it declared.

I think having multiple unique constraints on a table is perfectly OK, however. Take for example a table of countries. This would have, say, an ID, the name, the ISO code, the capital city, and some others. Each of those first four will be unique. Moreover, if we want our system to rely on each being unique I believe we should define unique constraints on each. This enforces truths about the data on which all consumers can rely.


Database is read-only I got this error. How can I resolve it?

  Failed to update database "msdb" because the database is read-only.
  Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
  Processed 184 pages for database 'BakupDB', file 'BakupDB' on file 1.
  Processed 2 pages for database 'BakupDB', file 'BakupDB_log' on file 1.

Sorry for the bad question.

I also get this error:

  Unable to open the physical file "C:\Users\geert\Desktop\C#proj\App_Data\ASPNETDB.MDF". Operating system error 32: "32 (failed to retrieve text for this error. Reason: 15105)". Cannot attach the file 'C:\Users\geert\Desktop\C#proj\App_Data\ASPNETDB.MDF' as database 'ASPNETDB'.

This is my DB:

The backup program is trying to update msdb database but cannot because either the database has only read permissions or the user running the program does not have permissions to write to the database.


Update does not working while select returns right results I am trying to execute the query below:

UPDATE(
  SELECT Ord.Completed
  FROM Product P
  INNER JOIN Order1 Ord
  ON Ord.OrderID = P.OrderID
  WHERE EXISTS(
          SELECT *
          FROM Delivery D
          WHERE Delivery.Delivered = 0 AND P.ProductID = Delivery.DeliveryID
        )
)
SET Completed= 'NO';

If I run the SELECT part only, without UPDATE it returns the right results. When I try to execute the UPDATE query I get

"ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 2 Column: 250"

Any ideas?

Please try this:

UPDATE(
  SELECT Ord.OrderID, Ord.Completed
  FROM Order1 Ord
  WHERE EXISTS(
          SELECT *
          FROM Product P
            INNER JOIN Delivery D
              ON Delivery.Delivered = 0 AND P.ProductID = Delivery.DeliveryID
          WHERE Ord.OrderID = P.OrderID
        )
)
SET Completed = 'NO' ;

or without a derived table:

UPDATE Order1 Ord
SET Completed = 'NO'
WHERE EXISTS(
        SELECT *
        FROM Product P
          INNER JOIN Delivery D
            ON Delivery.Delivered = 0 AND P.ProductID = Delivery.DeliveryID
        WHERE Ord.OrderID = P.OrderID
      );


MySQL replication with no connection I configured MySQL replication between a Master and a Slave throught VPN.
Everything works fine, when connection between them is down, and i make some changes to the Master (like insert a row), then when the connection comes back any operation is performed on the Slave.

This is the SLAVE and MASTER status before the connection down problem.

**SLAVE STATUS**

Slave_IO_state
Waiting for master to send event
Master_log_file
mysql-bin.000001
Read_master_log_pos
600
Connect_retry
60
Relay_master_log_file
mysql-bin.000001
Relay_log_file
mysql-relay-bin.000002
Relay_log_pos
824
Slave_IO_running
Yes
Slave_SQL_running
YES
Seconds_behind_master
0

**MASTER STATUS**

File
mysql-bin.000001
Position
600

Then while the connection down i insert a row in a master's table.
The connection comes back and what is see is

**SLAVE STATUS**

Slave_IO_state
Waiting for master to send event
Master_log_file
mysql-bin.000001
Read_master_log_pos
600
Relay_master_log_file
mysql-bin.000001
Relay_log_file
mysql-relay-bin.000002
Relay_log_pos
824
Slave_IO_running
Yes
Slave_SQL_running
YES
Seconds_behind_master
0

**MASTER STATUS**

File
mysql-bin.000001
Position
**887** <-- JUST THIS CHANGED

So i don't see any change in the Slave Table.
And after this, with the connecton up, the replication doesn't work.
To let it work I have to do in the slave:

CHANGE MASTER TO MASTER_LOG_POS=887

But with i still don't see al the changes made on the Master when the connection was down.

UPDATE
After connection down, when it comes back, to get the replication on the slave i have to restart it with:

STOP SLAVE;
START SLAVE;

I am still confused.

The solution was to add slave-net-timeout lover like 10 seconds in the ini conf file of the slave and put master-repl=0 that means infinite retryes. So in this case if the connection goes down the slave will notice it after 10 seconds and it will update the db looking in the log file of the master when it comes back online.


Can't create file /var/lib/mysql/user.lower-test MySQL database has been problem free for a few months. Today when I checked for upgradeable packages using the Synaptic Package Manager it came up with the various MySQL components (-common,  -client, -server, etc). I therefore decided to upgrade (I forgot to note the installed version but think it was 5.6.21) to 5.6.25-1-ubuntu2.0.Afterwards when I went to query the database I got the message:-

Error occured: Can't connect to MySQL server on '127.0.0.1' (111)

I then found the server was not running. In the log file I fond that the message:-

Can't create file /var/lib/mysql/user.lower-test

I have spent an hour or so searching on line and although the issue has been reported previously I have not seen it in the context of a 'post-update' issue so unsure of my next step.

This problems seems to permission issues or change the mysql data direcory to other location then /var/lib/mysql.

  Try to run with sudo

Looks like /var/lib/mysql is owned by the mysql user and the group is set to mysql as well.

$ sudo chown -R mysql /var/lib/mysql
$ sudo chgrp -R mysql /var/lib/mysql
$ sudo chmod 755 /var/lib/mysql

Have you checked that the directory /var/lib/mysql does actually exist and corresponds to your data directory? If it does not, then you probably must specify otherwise the datadir parameter under the mysqld section

[mysqld]
datadir=/var/lib/mysql

Then you can chose between configuring apparmor to allow MySQL to read\execute\modify the new data directory and sub directories or, at your very own risk, you can remove apparmor. You may need to be root to do the following:

/etc/init.d/apparmor stop
/etc/init.d/apparmor teardown
update-rc.d -f apparmor remove
apt-get purge apparmor
reboot


Can I limit access to postgresql 8.4 databases via ODBC on the server side? I have a bit of a tricky problem at the moment that I could really use some help to solve. I'll do my best to explain it but please feel free to ask questions :-)

We have a postgresql 8.4 database that our users typically connect to via a graphical interface that was written in Python many years ago.

Recently some users have been connecting to this database via ODBC and manipulating data via Microsoft Access. This has led to a number of accidental deletions of records and all sorts of troubles, so we wish to stop this from happening.

Here's the real clincher - Some users use MS Access via ODBC for reporting purposes, and we would like them to be able to continue doing that. So we would like to be able to restrict database access via ODBC to only "SELECT" queries.

Does anyone know if this can be achieved on the server side? I know that there is a "readonly" option in the ODBC driver settings on windows, but unfortunately many users have administrator access on their windows machines, so whatever security measures we put in place have to be done on the server side.

ODBC is a client tool and does not affect the way that Postgresql verifies access.   I don't know if its any different for a postgresql server on windows, but in linux, there is a file called pg_hba.conf. This file is usually located in the data directory of the server install.  In Linux, you typically find it in /var/lib/pgsql/data/.

This file allows you to define which ip addresses can access your postgresql server and how they may access it.  Types of access include trust (no password), md5 (password), etc.

So for example, if your access users are on say 192.168.0.10 and you want them to use a password you would add this entry.

# TYPE  DATABASE    USER        IP-ADDRESS    IP-MASK             METHOD
host    all         access_user         192.168.0.10    255.255.255.255     md5

In postgresql you would then have to create the user and give him the ability to login.

1. psql -d nameofdatabase
2. CREATE ROLE access_user WITH LOGIN PASSWORD 'jw8s0F4';
3. GRANT SELECT ON nameoftable TO access_user;

Good luck


Json Column configuration I am using mariadb and I will create a table with two columns and an int primary key.
This table will start with about 3.000.000 rows and will be added approximately 30.000 rows per day.
each column will have 200 to 700 characters.
There will be  some searches against these two columns either as text or json query.
I am thinking of make these column as mediumtext data type and also set full text index.
I am quite new to this and I don't know, Is that a correct approach ?

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.


How to order some rows one way, and the rest another way? I am trying to sort rows from a table in PostgreSQL 10.12:
CREATE TABLE example (
 id  serial PRIMARY KEY,
 val text NOT NULL,
 x   integer NOT NULL,
 y   integer NOT NULL
);

INSERT INTO example (val,x,y)
VALUES
  ('First Value (This should be first order)',7,2)
, ('Second Value (This should be second order)',6,3)
, ('Third Value (This should be third order)',5,4)
, ('Seventh Value (And This should be last order)',4,1)
, ('Sixth Value (This should be sixth order)',3,5)
, ('Fifth Value (This should be fifth order)',2,6)
, ('Fourth Value (This should be fourth order)',1,7)
;

First three result rows should be ordered by x desc, and the rest should be ordered by y desc.
I tried this query but it is ordering only by y:
SELECT * from (SELECT * from example order by x desc fetch first 3 rows only) foo
UNION
SELECT * from example order by y desc;

But it sorts by only y. And I need to get the result without UNION.
Here is an SqlFiddle.

This returns the top 3 rows according to ORDER BY x DESC and appends the rest sorted by y DESC:
WITH cte AS (TABLE example ORDER BY x DESC LIMIT 3)
TABLE cte
UNION ALL
(  -- parentheses required
SELECT e.*
FROM   example e
LEFT   JOIN cte USING (id)
WHERE  cte.id IS NULL
ORDER  BY e.y DESC
);

TABLE is just shorthand for SELECT * FROM.
UNION ALL makes sure that the two derived tables are just appended without trying to eliminate (non-existent) duplicates and thereby messing with the sort order.
Parentheses are required so that ORDER BY only applies enclosed SELECT, not to the complete set of rows.
See:

Combine multiple SELECT statements

Is order preserved after UNION in PostgreSQL?

Related:

Combining 2 SELECT queries and printing the results in PostgreSQL

Is there a faster way to get UNION ALL behavior in Postgres?

Without UNION
SELECT e.*
FROM   example  e
LEFT   JOIN (
   SELECT id, x FROM example ORDER BY x DESC LIMIT 3
   ) c USING (id)
ORDER  BY c.x DESC NULLS LAST, e.y DESC;

Same result.
The LEFT JOIN results in c.x being NULL except for the top 3 chosen rows. So the first ORDER BY items c.x DESC NULLS LAST only sorts the first 3 rows and leaves the rest unsorted. The second ORDER BY item e.y DESC sorts the rest as desired.
About NULLS LAST:

Sort by column ASC, but NULL values first?

db<>fiddle here
If the table is not trivially small, you should have and index on (x) and another one on (y)


Copy postgis layer from S3 to Heroku I have a dump of a postgis layer (layer.dump), which I am trying to add to my heroku database (mydatabase). The postgis layer is stored on S3 (https://s3.amazonaws.com/layer.dump). I would like to add the layer to to the heroku database and previously used heroku pgbackups:restore DATABASE 'https://s3.amazonaws.com/layer.dump'. However, the new heroku pg:backups restore 'https://s3.amazonaws.com/layer.dump' DATABASE deletes all data from the target database before restoring the backup (https://devcenter.heroku.com/articles/heroku-postgres-backups). Is there still a way to only restore a single table and leave the remaining tables in the database untouched?

According to the link to a Heroku discussion here: Howto: Restore a single table to an existing database, the way your're doing it is always a destructive action.

A better solution proposed is to dump the table locally, and import the data remotely.

You could do a single dump of the table using a command like this:

pg_dump -U <myuser> <mydb> -t <mytable> -f <my_table>.sql

And then try to restore it like this:

psql -U <myuser> <mydb> -h <my_heroku_url> -f <my_table>.sql

If you need a more intricate restore, I would recommend using pg_dump from the originating database with the -Fc flag, like this:

pg_dump -U <myuser> <mydb> -Fc -f <my_db>.dump

And then using the pg_restore utility to restore just the parts you need.

Further documentation for pg_restore is here: http://www.postgresql.org/docs/9.4/static/app-pgrestore.html

And for pg_dump: http://www.postgresql.org/docs/9.4/static/app-pgdump.html

Hope that helps. =)


MariaDB (RDS) going temporarily offline (with memory drop) We have an issue with a database of ours on RDS running MariaDB 10.4.8 on a t3-large instance type.
For the past week or so, it's been sporadically inaccessible with no errors I can find. During the minute or two that it's down I can't run 'show process list' it just hangs. It fixes itself and is back working within a few minutes. When it comes back it looks like it has a lot of connections and queries to catch up on.
Changed params from default:
ft_min_word_len 1
innodb_buffer_pool_size {DBInstanceClassMemory*17/20}
innodb_ft_enable_stopword   1
innodb_ft_min_token_size    1
log_bin_trust_function_creators 1
max_statement_time  30
sql_mode    NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Freeable Memory:

Swap space:

Performance Insights:

If there any way to find out what's causing it. Is it not enough free memory? What's a normal threshold to be running to?

Enable the slow query log to see what is going on: https://mariadb.com/kb/en/slow-query-log/
Running a fulltext index with a minimum token length of 1 also slows things down, if not necessary: https://mariadb.com/kb/en/innodb-system-variables/#innodb_ft_min_token_size


Does SQL server 2008 log instance level security permission changes I just recently logged onto one of our Test environments to find i did not have permissions, turns out my instance security Login had been disabled

I have a feeling towards whom may have done this but i need to see if i can see this in the logs,

Is it possible to see when and possibly whom altered these permissions in the SQL logs somewhere and if so how

Unfortunately being a test environment the only database on the instance has probably been overwritten a number of times in the meantime.

for clarity i want to know whom disabled the login

If I'm not mistaken the default trace contains property changes to logins which should include an account being disabled. An article that walks though the various information you can get from the default trace.

EDIT

OK, already confirmed not much at all is captured with manipulating logins on an instance. So unless you had something already capturing the information like SQL Server Audit (if on 2008 or higher), there is nothing natively logged by SQL Server to let you know who did it.

If you happen to be logging successful logins to that instance you can determine who was logged in that had the permissions but that is the closest you will likely get.


How much RAM should you allocate a MySQL replication slave? Our MySQL master is allocated 24GB of RAM (innodb_buffer_pool_size = 24GB), but was curious if there was some standard amount to allocate to a replication slave?

Right now the slave is just used to take dumps without locking tables on master, and in theory be used as an emergency master if something happens (hasn't come up in the last 18mo). If master fails I could always quickly allocate the slave 24GB, but how much does a slave really need to just sit there replicating?

The amount of memory should be the same, the config should be the same (except config that pertain to Master / Slave only).

Reason is when you fail over, you don't want to spend time trying to find out what value should be there or not. I would even create 2 my.cnf files: my.cnf and my_failover.cnf (prepare my.cnf on Slave in case master fails. File can contain binlog=1 (turn on bin log).

All you should need to do is:

mv my.cnf mybk.cnf, my my_failover.cnf my.cnf
Restart Mysql.

Then your slave will start login.

Regarding caching queries, well, all Select statement running on Master won't replicate to Slave. If your Master is 80% Select / 20% write, it should not be a problem.

If you want to cache your Select from Master, turn on Slow Query log for query > 1 sec then apply that to Slave. You could do that once a day (in morning).

Chances are, soon you'll be using your slave for reporting.

You could also use Percona Playback.


Database design for ~ 2.7B rows per year / 26.000 data points every five minutes The current situation

We're currently looking into a new product that will send device data back to us to interpret.

These are the numbers we're looking at:

Devices will most likely send data every 5 minutes
26.000 devices by the end of next year

26.000 INSERTS every 5 minutes. We will most likely have little control over the interval, so chances are that these 26.000 INSERTS are not evenly spread across these 5 minutes.
~ 2.733.120.000 data entries per year

Each packet will be JSON-formatted, with a size between 300 - 500 bytes.
We'er expecting about 8.000 new devices every year.

We currently manage several databases for our internal systems, but have little experience with volumes like this. We use AWS Aurora right now, which, in theory, should support 100.000 INSERTS p/s.

How will this data be used?

The data will primarily be used to create reports in our customer portals:

Real time reports of device metrics
Historical reports, IE.:

How did device stats look on Feb 2th, 2019?
What did week 12 look like?
Give me a summary of January's metrics
Show a graph of a specific column sum, grouped by month

The problem

To be quite honest, I find it pretty hard to make a solid choice, considering I don't have any hands-on experience with data-volumes like this.

Our current stack

We use a combination of AWS EC2 machines and an AWS Aurora cluster to manage our data. The ideal solution would be AWS-oriented.

Infrastructure I am considering:

Option #1: To keep things simple, storing everything into Aurora directly could be a good solution.

Option #2: But, to create a separation of our "real time" data and interpreted data, perhaps, something like this is better.

The actual questions

Is a MySQL-compatible database management system, like Aurora, suitable for something like this?
The incoming data will be used to generate "realtime" daily, weekly, monthly and yearly reports, aggregated per device. Would it be advisable to create separate tables for these different "perspectives" to make querying the data easier, or am I overcomplicating things and should I just store the measurements into one table?
Should we look into table partitioning?
Is there anything else that I did not mention, but we should look into?

If all of this is too vague, please let me know so that I can clarify the issue.

Would love to hear your thoughts.

I work for an IoT company and have recently implemented something similar (device sensor data readings) using a Kinesis stream, Spot Instances and DynamoDB.

The Spot Instances were a cost saving step to replace the Lambdas, which were processing the stream data and bulk inserting to DynamoDB, they were costing too much.  We changed the Lambdas to EC2s and then to Spots to save cash.

I would suggest trying the Lambdas to get data from SQS and put it in a DB, but for scale and cheapness please look at DynamoDB for storage. The downside of DynamoDB is that you have to know your query paths before you build the table but as you are using an API you will probably know what those are.


How to condition on one column with different values and print-out paired values in next column in multiple columns? I have a sample date in 'myTable' table, including one column which represents IDs and second column represents their paired values (something like below sample)

myTable

IDs    values
0      A+
1      A
1      D
2
0      A
2      C
1
3      C

I need to get result as below,

0IDs    1IDs    2IDs    3IDs
A+      -       -       -
-       A       -       -
-       D       -       -
-       -       *       -
A       -       -       -
-       -       C       -
-       *       -       -
-       -       -       C

Here you go...

Data & structure:

create table mytable
(
ids number,
vals varchar(5)
);

insert into mytable values(0, 'A+');
insert into mytable values(1, 'A');
insert into mytable values(1, 'D');
insert into mytable values(2, NULL);
insert into mytable values(0, 'A');
insert into mytable values(2, 'C');
insert into mytable values(1, NULL );
insert into mytable values(3, 'C');

Test:

SQL> select NVL(case when ids=0 then vals else '-' end,'*') as x0IDs,
  2         NVL(case when ids=1 then vals else '-' end,'*') as x1IDs,
  3         NVL(case when ids=2 then vals else '-' end,'*') as x2IDs,
  4         NVL(case when ids=3 then vals else '-' end,'*') as x3IDs
  5  from mytable;

X0IDS X1IDS X2IDS X3IDS
----- ----- ----- -----
A+    -     -     -
-     A     -     -
-     D     -     -
-     -     *     -
A     -     -     -
-     -     C     -
-     *     -     -
-     -     -     C

8 rows selected.

SQL>


Oracle Enterprise Manager read-only access for non-administrative users Can access to OEM be restricted to the point where you can allow a non-admin user access to it but they can't modify any objects but can still view performance data, alerts, sessions, etc? I am a former DBA now developer and I don't have an environment where I can go verify this myself.

You allow a non-admin user access to the Performance area of OEM by granting them the OEM_MONITOR role. However, be aware that this role does have a few additional priviliges that you may not want to provide. In that case I would create a similar role to OEM_MONITOR but with a reduced set of privileges.


Preventing database access after application license key has expired I'm fairly new to this so forgive me if this is a basic question.

I have an application that uses a MS SQL database as the back-end. Like most
applications, a user is prevented from using this application if the license key has expired. I would like to know if there is a way to also prevent users from accessing the database (via SSMS) when the key has expired?

Thank you in advance for your assistance!

If the person has a valid MS SQL Server username and password, they will be able to access the database.

You could arrange your app so that the username/password they use for the app won't allow them to accesss the SQL Server database. In any case, what's the problem if your users wish to use Crystal (or whatever) to access what is, after all, their own data?

What I mean is that surely your app provides some extra service (for which they are paying you) - after that, what they do with their data is their business.

You could warn them that any modifications to data may invalidate/corrupt their data with respect to your application (i.e. you won't support them for that), but personally, I would be rather annoyed to be told by a vendor that I couldn't run ad-hoc reports against my own data!

[EDIT]

What you could do is keep any user whose privileges allow them to modify the data "secret", but give them a username/password with SELECT privileges and that way everybody's happy - they have access to their data and you can rest easy in the knowledge that users can't mess up your app.


Deadlock occurring on database, SQL Server 2008R2 Last few days I had major problems with my production site, turns out deadlock occurred, every half an hour maybe 50-60 of them. To do a quick fix in order to keep the site running I set : ALLOW_SNAPSHOT_ISOLATION ON

I believe my log file will grow faster than before now?

Are there different types of isolation and is it possible to set the isolation to just 2 tables which causes the deadlock?

::Update::

::2013-03-21 15:09::

Can I query the process ID's involved in the deadlock? select object_name() returns null always

Enabling snapshot isolation on a production server without testing the application first is rather a high risk strategy, but if you seem to have got away with it, congratulations.

The side effects of snapshot isolation tend to be to do with tempdb rather than the transaction log, see for instance http://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.110%29.aspx.

The best thing to do is to look at the code that is accessing those two tables, and see if you can correct the problem there.


Delete cascade produce scan intead of index seek I have two tables in my database.

TableA : 1427877 rows
TableB : 339939000 rows

TableB has a foreignkey column in TableB (TableB.foreignId). It has Delete On Cascade ON.

The problem is when I delete a row in TableA.

DELETE TableA WHERE id = @id

The cascade reference, produce an index scan on TableB (300Million rows). While exits a non-clustered index on foreignId column.

I test with a procedure

CREATE OR ALTER PROCEDURE #tempDelete(@id INT) AS DELETE TableA WHERE id = @id;

After cleaning cache plan. The plan is the same, index scan.

If I did

DELETE TableB WHERE ForeignId = @id

It use an index seek on 'indexB'

Plan for both query

I checked this post about missing index. I think the index are rigth.

Also this post about correct data type. foreignId is Int, the same that use on my .Net client and also in the store procedure.

How I could fix the Delete operation? The plan is same for production and development.

Thanks,

Every index on TableB have ForeignId as first column.
Indexc is the only that has ForeignId as second column.

You may want to look in the table sys.foreign_keys for the foreign key joining the tables, and see if the index has the value is_not_trusted = 1.

This query, originally from BrentOzar.com, will show you which foreign keys in your system are not trusted:

SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0;
GO
SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.check_constraints i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0;
GO

Note: I recommend at least subscribing to Brent Ozar's emails - lots of interesting information coming out of that website, and their Blitz queries are really helpful.


How do I name a query table in mysql? Let's assume I do a very simple query selecting 2 columns out of the 5 columns in a table. How do I name the query table for future use?
Sorry for asking something that sounds so obvious but have looked for answer everywhere and could not find any.

A view would allow you to group columns from different tables so you can view that data all at once.
An example would be if you had a SALES view to report on
you would create the SALES VIEW by joining a Customer table so you can see who purchased the item, you would also include a Inventory, or Items table so you can see the details of the item purchase.

So next time you need to query which customer bought which item, you would just query against the view that would contain all the columns you need


Ibdata usage and Recommendations? I have a Production server

On November 5,2011 the ibdata size was 100G.
In Approximate 3 months it is increased to 200G.So it has just doubled its size.So it is a Huge Data.Currently lvm is 251G.

I Have approximately all tables with Innodb.I am not using innodb per table.
I have my idbata on lvm.So How much i should increase it for future use..?

Or any other Best way to Handle the Scenario.

You need to stop using InnoDB (with innodb_file_per_table off) and LVM snapshots as soon as possible. Here is why:
I have a monitoring system which uses MySQL as its database. It has innodb_file_per_table off as well. In a space of 6 months, it grew to 1.3TB.
My employer's company cannot have downtime for this monitoring system.
I attempted to create a Slave for this monster database. I ran an rsync of /var/lib/mysql to the Slave server. Its first pass took 42 hours (That's not a typo, 1 day 18 hours). The second pass took 84 hours (3 days 12 hours) and was only 15% done (220GB copied). As you can see, I abandoned the second rsync.
The problem stemmed from ibdata1 being 1.3TB.
What is inside ibdata1 when innodb_file_per_table is OFF?

Table Data Pages
Table Index Pages
Table Metadata (Tablespace ID Management)
MVCC Data (for Transaction Isolation, ACID Compliance)

In a write-heavy environment, all records of these four types are being read and/or written. The rsync experienced a nightmare trying to get the ibdata1 coalesced enough to write changes to the Slave server. According to my Senior Linux Engineer counterparts, an LVM snapshot would not fare any better.
Your only recourse is to start using innodb_file_per_table.
For you to see this need further, please run this query (should take 5-10 min for you)
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

Using the totals for InnoDB, compare it to the size of ibdata1. You will find that there may be some a difference of 30GB of less. I ran than query against ibdata1 in my monitoring system and had only 27GB for table metadata and new MVCC info. Steady growth would always occur on this file.
Here is what you can do: Convert all your InnoDB to use innodb_file_per_table. I wrote a post in StackOverflow on how to do that. Not only will you be able to have easch table exist in a separate file, you will be able to keep ibdata1 rate of growth down to a bare minimum.
UPDATE 2012-02-22 13:00 EST
When executing the CleanUp of InnoDB, make sure you eliminate the making of multiple ibdata files. your setting should be the default:
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend


Error when sending email with attachment using SQL Server login I'm trying to send email using msdb.dbo.sp_send_dbmail  by a SQL Server login.

It works fine if I'm not attaching anything. But if I attach anything I get the following error:

"The client connection security context could not be impersonated. Attaching files require an integrated client login"

so I mapped that SQL login to a credential that is a local Windows account that I'm sure it has full access to the file I want to attach, as follows:

however, I'm still getting the same error.

What is wrong? am I missing something? how can I fix it? how can I allow non-sysadmin SQL Server logins to send emails with attachment?

Note that:

The file is local and not on a network drive
I have Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)   Mar 19 2015 12:32:14   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Update:

I have also tried to wrap sp_send_dbmail into a stored procedure the EXECUTE AS the windows account which has all the permission on the file, it can execute the wrapping stored procedure, and it can execute successfully sp_send_dbmail with attachement ... I've tried something like:

EXECUTE AS LOGIN='MyMachine\SQLMailUser'

 EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLMail',
    @recipients = 'myEmail@myDomain.xyz',
    @body ='some text in email body',
    @subject = 'some text for the subject',
    @file_attachments='D:\test\test.txt',
    @body_format ='HTML';

but same error :(

As per books online:

  Database Mail uses the Microsoft Windows security context of the
  current user to control access to files. Therefore, users who are
  authenticated with SQL Server Authentication cannot attach files using
  @file_attachments. Windows does not allow SQL Server to provide
  credentials from a remote computer to another remote computer.
  Therefore, Database Mail may not be able to attach files from a
  network share in cases where the command is run from a computer other
  than the computer that SQL Server runs on.

I was able to reproduce this using my AD account.  While running following code remotely I get the error.  Running the same code by logging into the host was successful.

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'taiob.ali@company.com',
@file_attachments = 'd:\FailedLogins_0_131397600848650000.xel',
@subject = 'testing file attachment'

Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1 [Batch Start Line 0]
    The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

Now if that is not your case (meaning the file is on a network share) CREDENTIAL should work.

According to this CREDENTIAL did not work. But the 2nd answer did work though it poses security risk.

There is a connect item about this and here is a comment by Aaron Bertrand which supports the answer I refered above.

  If it is indeed a case of reduced privileges, you can work around this
  by routing mail through a wrapper procedure that uses EXECUTE AS (or
  is signed). Personally, I think that all of your send mail calls
  should go through a single wrapper procedure anyway (makes
  modifications, profile changes, etc. much easier to propagate).


SQL Server AUDIT/event_time I am using SQL Server 2012 Audit and when I look through the captured events I can see that event_time does not show the correct time. Actually it shows 5 hours earlier than time on the server. Do you have any ideas how can I fix that ?

The audit data uses UTC time. See this link for suggestions to adjust your query.

SELECT DATEADD(mi, DATEPART(TZ, SYSDATETIMEOFFSET()), 'HERE goes your date')


SQL don't display ORDER BY column I'm quite new to SQL programming, so i apologize in Advance for all the irrelevant Information and stupid Questions :)

I have a database in Microsoft SQL Server Management Studio.

I select data from multiple tables in the database according to different criterias. I join these datas toigether with UNION. Because UNION has its own order, I also created a column in each SELECT statement called filtering, and then at the end of the UNION I ORDERed the data BY this column> filtering.
Then I insert my results into a table I have previously created. Until now everything functions perfectly.
Then I close the statement, and start a new one with basically almost the same information. (Only the weekday changes from 21 to 22). I also insert these records into the new table, and there I have my "error".

Becuase of the ORDER BY column (filtering) these records are inserted into row 4, 5 and 6, instead of 1, 2 and 3.

Is there a waz to correct this, for example not to display the filtering column, or JOIN by the filtering column?

As I said I'm quite new to SQL (started learning it about about 2 Weeks ago) any help is highly appreciated!

My code and the results

DELETE FROM [Test].[dbo].[Übersicht_Woche_RAP]
INSERT INTO [Test].[dbo].[Übersicht_Woche_RAP] (W21, id)
SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
ORDER BY filtering;

INSERT INTO [Test].[dbo].[Übersicht_Woche_RAP] (W22, id)
SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
UNION
SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
UNION
SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
ORDER BY filtering

The variant with direct separate SELECTs:

WITH
cte21 AS (SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
          UNION
          SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
          UNION
          SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week,
          BuchDatum) = 21)
         ),
cte22 AS (SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
          UNION
          SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
          UNION
          SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week,
          BuchDatum) = 22)
         )
INSERT INTO [Test].[dbo].[Übersicht_Woche_RAP] (W21, W22, id)
SELECT cte21.Rüsten, cte22.Rüsten, cte21.filtering
FROM cte21
JOIN cte22 ON cte21.filtering = cte22.filtering

But you can create more compact version by create cte AS (SELECT 21 weeknumber UNION SELECT 22) and cross apply it to formalized version of your query (add GROUP BY and wrap values with MAX(), or use PIVOT).


Unable to get SQL Server 2016 CTP3 service to start on Windows 10 During install of MS SQL Server 2016, on a developer/DBA workstation client PC which is joined to a domain, the installation using all logon and other installer defaults, completes with failures, and a very generic message that just means that the main SQL Database Engine Service (MSSQLSERVER) did not start. Starting it manually from services.msc, one sees this equally generic error:

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer.
For more information, review the System Event Log. If this is a
non-Microsoft service, contact the service vendor, and refer
to service-specific error code -2146885628.

In the system event viewer (eventvwr.msc) there are several failures:

#1. (1500) User Profile Service error for user NT SERVICE\MSSQLSERVER: Windows
cannot log you on because your profile cannot be loaded. Check that
you are connected to the network, and that your network is
functioning correctly.

#2. (17826) Could not start the network library because of an internal error in
the network library. To determine the cause, review the errors
immediately preceding this one in the error log.

#3. (17182) TDSSNIClient initialization failed with error 0x80092004,
 status code 0x1. Reason: Initialization failed with an infrastructure
 error. Check for previous errors. Cannot find object or property.

#4. (17182)  TDSSNIClient initialization failed with error 0x80092004, status
code 0x80. Reason: Unable to initialize SSL support. Cannot find
object or property.

#5. (17190) Initializing the FallBack certificate failed with error
code: 1, state: 20, error number: 0.

There are hundreds of kilobytes of log files, but no clear output in any of them to indicate a root cause.

This appears to be a variant of the SSL-init-failure startup failure case, but I had this same error with CTP 2.3, and was hoping that the SQL Server installer would help me figure out why none of the Windows 10 PCs in my local office domain will accept and run MS SQL 2016 (or MS SQL 2014) without hitting some variation of these SQL errors.

SystemConfigurationCheck_Report.htm contains no errors, everything is green checkmarks, and no errors were reported during the actual installation. There were no other MS SQL services/instances on the machine previously, but there is a Visual Studio 2015 and MS SQL localdb.

Is this an SQL error, or some local profile error, or something else? I tried this in SQL 2016 CTP 2.3  and 2.4, and had the same issue.  I decided to wait until SQL 2016 CTP 3, which is now out, but I have the same issue.

It seems to me that on windows 10, with the sql service logged on as NT SERVICE\MSSQLSERVER everything SHOULD just work. But it does not.

This question really boils down to "Why does MS SQL pick a service login account named NT SERVICE\MSSQLSERVERand then fail to ensure that this account is valid, and can work with SSL, or has a local profile that is valid, or whatever else sqlsrvr.exe needs to run?", and given that the installer default behaviour is unsuitable even for a DBA or SQL Developer's own private workstation (a non production environment), what should one do instead?

Interesting. Glad you got the SQL service to start. It should be okay to leave it as NT AUTHORITY\LOCALSERVICE for testing CTP3 on your workstation. In general for production servers on a domain it is best to provision and use domain service accounts for the SQL Server and Agent services - see https://www.simple-talk.com/sql/database-administration/provisioning-a-new-sql-server-instance-%E2%80%93-part-one/ .


How do I list all tables in all schemas owned by the current user in Postgresql? I can list all tables in all schemas using

> \dt *.*

but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by me in the public schema and any schemas I've defined.

I'm hoping to find a way to do this without having to explicitly add schemas to the search path as I create them as described here:

https://stackoverflow.com/a/12902069

EDIT:

Based on the accepted answer, I've created the following View:

create view my_tables as
select table_catalog, table_schema, table_name, table_type
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema');

And now the following command gives me what I wanted:

select * from my_tables;

This will list all tables the current user has access to, not only those that are owned by the current user:

select *
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema')
and table_schema not like 'pg_toast%'

(I'm not entirely sure the not like 'pg_toast%' is actually needed though.)

I you really need the owner information, you probably need to use pg_class and related tables.

Edit: this is the query that includes the owner information:

select nsp.nspname as object_schema,
       cls.relname as object_name,
       rol.rolname as owner,
       case cls.relkind
         when 'r' then 'TABLE'
         when 'm' then 'MATERIALIZED_VIEW'
         when 'i' then 'INDEX'
         when 'S' then 'SEQUENCE'
         when 'v' then 'VIEW'
         when 'c' then 'TYPE'
         else cls.relkind::text
       end as object_type
from pg_class cls
  join pg_roles rol on rol.oid = cls.relowner
  join pg_namespace nsp on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog')
  and nsp.nspname not like 'pg_toast%'
  and rol.rolname = current_user  --- remove this if you want to see all objects
order by nsp.nspname, cls.relname;


Does restoring database clear buffer cache and stored proc cache on our development server, we refresh database (restore from production backup) every week. my question is does restore db clear its buffer cache/stored procedure cache from sql memory? Does it have the same/similar effect of  DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS

Thanks.

Yes. The buffer and plan cache associated with the database before restore are not related to the database post-restore, so they will be cleared. To all intents and purposes the restored database is a completely separate, unrelated entity. There is nothing in cache that can be reused.


Are many two column indexes a bad smell? I have table that contains failures. (15000 rows + on a abstract embedded system)

A failure has the following fields:

id PK BIGINT
errorNumber INT
source VARCHAR
raisedAt BIGINT
clearedAt BIGINT
cleared (computed column for sorting purposes = clearedat>0) BOOL

My first use case Is that I want to display all failures, the uncleared ones first.

Select * from FAILURES ORDER BY CLEARED asc, raisedAt desc;

Second use case is by a synchronization job. I want to delete all synchronized and cleared failures, there for all failures with an id smaller X and cleared

DELETE FROM FAILURES WHERE ID <? AND CLEARED;

Third use case is that I want to pick a specific uncleared failure.

SELECT * FROM FAILURES WHERE SOURCE=? AND ERRORNUMBER=? AND NOT CLEARED;

This would lead me to the following indexes:

CREATE INDEX CLEARED_RAISEDAT_SORTED_FAILURES ON FAILURE_MANAGER.FAILURES (cleared asc,raisedat desc);
CREATE INDEX CLEARED_ID_FAILURES ON FAILURE_MANAGER.FAILURES (cleared asc,id desc);
CREATE INDEX CLEARED_SOURCE_SORTED_FAILURES ON FAILURE_MANAGER.FAILURES (cleared asc,SOURCE desc,ERRORNUMBER desc);

I sometimes still struggle with index definition. It seems odd to me that I have 3 different indexes, which all only differ in the second column. Relative to my described uses cases, are these indexes feasible or does this create unnecessary overhead? Are there any check up questions that I can ask my self to figure if this might be the correct setting?

I hope for you that most failures are cleared.  In that case, when you query for cleared failures, the index will hurt performance.  If you'd put that column in the second place, on the other hand, it might be benificial.  When you're looking for not cleared - if those are rare - the index will help.


Save SSMS tabs with timestamp on a daily basis? Is there a way to automate SSMS to automatically save down all current open tabs into a folder with a timestamp?

Whilst I'd like to save every single useful ad-hoc query ever written with a nice understandable name, I often don't have time and this would be rather useful

Ideas?

Actually I think that you simply nedd statement logging. Try ssmstoolspack.

In meantime, my add-in SSMSBoost does a lot of logging as well: editor contents history, tabs history, execution statements history...


TDE prep: key/certificate backup for restores I am working in a dev environment to better understand TDE encryption. I have it working along with backups and restores on another server. Had a few questions, I know I need to backup the certificate with the corresponding private key.

USE master;
GO
BACKUP CERTIFICATE Test
TO FILE = 'C:\Test.cer'
WITH PRIVATE KEY
(FILE = 'C:\Test.pvk',
ENCRYPTION BY PASSWORD = 'Example12#')

These need to be moved/restored on the new server in the event of a failure. Is there anything else that I need to backup from the source server that would be needed in the event of needing to restore to another server?

Also any suggestions on private key storage ? My thought at the moment is to backup the certificate, private key, and password to a KeePass database that is backed up separately and replicated off-site.

That leaves the question though of where to backup the KeePass private key to ?

If you want to restore on a different server, you should be able to do so with the certificate, private key and database backup file(s).

When a certificate is created in any database in SQL Server, it is part of an encryption hierarchy. The certificate in the master database itself only contains a public key, which needs no protection, however, the master database will also contain a separate but mathematically related private key which does need to be protected. The method of protecting the private key is to encrypt it using the database master key you created in the master database prior to creating the certificate. The next layer in the encryption hierarchy is that the DMK is encrypted by the service master key. There is only one SMK on the system and it is in the master database.

Even though you don't need the DMK and SMK to restore an encrypted backup to another server, I would back up these two keys anyway as it makes recovery much more flexible.

When you restore the backup encryption certificate to the master database, what happens behind the scenes is that the private key is read from the file, decrypted using the password you provide in the restore command, then encrypted using the database master key and saved. As you know, the private key from the certificate can then be used to decrypt the Database Encryption Key in the backup file and successfully restore the database.

I don't have a specific recommendation for storing the certificate and key backup files, but they do need to be available to you and whoever does the disaster recovery in your organization.


When are Archived Redo Logs generated? We have an instance in Oracle 10g. Only one schema is used for a web application (Internet-facing), which is only user for reading information, no adding or modifying data.  The rest of schemas are not used.

Archived Redo Logs is activated.  What is strange for us is that archived files are generated every day, including Sundays and holidays, when nobody comes to work.

I thought Archived Redo Logs are generated when inserting, deleting, or updating data, never because of select statements.

Is that correct?

Oracle itself will generally generate redo in the process of running various background jobs.  For example, by default in 10g, there is a job that gathers statistics on objects.  That will generate redo.  By default, 10g has a job that gathers AWR information as well every hour.  That will generate redo as well.  There are a number of other background jobs (checkpointing, updating the SCN to timestamp mapping table, etc.) that are going to be running as well which will be generating redo on an ongoing basis just as a matter of course.

If you run the query

select current_scn, systimestamp
  from v$database

in a (from a user standpoint) idle database, you'll note that the current_scn is incrementing every few seconds.  That is Oracle doing various sorts of housekeeping.  That housekeeping will generate a small amount of redo each time.  Over the course of a full day, it's pretty common that this would add up to enough for a couple of archived logs even if your system is otherwise pretty idle.