Postgres 12: Inserting array of composite types, composite type has an array field. Malformed array literal, unexpected end of input I'm having an issue with inserting a simple array of integers into my table in Postgres via a procedure. The array of integers is declared within a composite type and I'm trying to insert an array of these types via the procedure.
Just to put this out there: this code works without the array of numbers (i.e. if I were to just remove the variable as_ids from the table, composite type, and procedure, the code runs the way its supposed to. for some reason it just doesn't work with the array).
The table
CREATE TABLE user_ans(
ans_id INT GENERATED ALWAYS AS IDENTITY,
fq_id INT NOT NULL,
p_id INT NOT NULL,
j_id INT NOT NULL,
u_id INT NOT NULL,
d_id INT,
ans TEXT NOT NULL,
as_ids INT[] NOT NULL,
created TIMESTAMP,
updated TIMESTAMP
);

The composite type
CREATE TYPE new_ans_obj AS (
    ans TEXT,
    as_ids INT[],
    created TIMESTAMP,
    d_id INT,
    fq_id INT,
    j_id INT,
    p_id INT,
    u_id INT
);

The procedure
CREATE OR REPLACE PROCEDURE add_ans_arr (
    new_ans new_ans_obj[]
)
    LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO user_ans(ans, as_ids, created, d_id, fq_id, j_id, p_id, u_id)
    SELECT * from unnest(new_ans);
    COMMIT;
END;$$;

Attempts
Command I use:
call add_ans_arr((ARRAY['(sdf,{2002,2005},2021-01-19T21:48:28.997Z,1001,7001,3,6001,1)', '(sdf,{2002,2005},2021-01-19T21:48:29.626Z,1001,7002,3,6001,1)'])::new_ans_obj[]);

Output:
ERROR:  malformed array literal: "{2002"
LINE 1: call add_ans_arr((ARRAY['(sdf,{2002,2005},2021-01-19T21:4...
                                   ^
DETAIL:  Unexpected end of input.

Command I use:
call add_ans_arr((ARRAY['(sdf,(ARRAY[2002,2005])::int[],2021-01-19T21:48:28.997Z,1001,7001,3,6001,1)', '(sdf,(ARRAY[2002,2005])::int[],2021-01-19T21:48:29.626Z,1001,7002,3,6001,1)'])::new_ans_obj[]);

Output:
ERROR:  malformed array literal: "(ARRAY[2002"
LINE 1: call add_ans_arr((ARRAY['(sdf,(ARRAY[2002,2005])::int[],2021...
                                ^
DETAIL:  Array value must start with "{" or dimension information.

Post obvious attempts
I've also tried different variations of the two above commands (using parenthesis, putting the size of the array in, etc), but with similar outputs. I've read other posts about inserting arrays into Postgres, as well as the documentation and it seems like I'm using the right syntax (although probably not)?
I was thinking that the array of integers might be conflicting with the 'unnest' function, so I rewrote the procedure to following, but still the same problem pops up:
CREATE OR REPLACE PROCEDURE add_ans_arr (
    arr new_ans_obj[]
)
    LANGUAGE plpgsql
AS $$
DECLARE
    ans_obj new_ans_obj;
BEGIN
    FOREACH ans_obj IN ARRAY arr
    LOOP
        INSERT INTO user_ans(ans, as_ids, created, d_id, fq_id, j_id, p_id, u_id)
        VALUES (ans_obj.ans, ans_obj.as_ids, ans_obj.created, ans_obj.d_id, ans_obj.fq_id, ans_obj.j_id, ans_obj.p_id, ans_obj.u_id);
    END LOOP;
    COMMIT;
END;$$;

I'm able to individually insert these objects into the table:
insert into user_ans(ans, as_ids, created, d_id, fq_id, j_id, p_id, u_id) VALUES ('lala', '{2002}', '2021-01-19T21:48:29.626Z', 1001, 7002, 3, 6001, 1);

But I don't really understand why the above method isn't working? I've tried putting the single quotes into the my first command, but I just get a syntax error. Also I don't want to mess too much with the
ARRAY['','']::ans_obj[]

structure because it works for the rest of my other procedures, and this structure is generated by my server. Although I'm open to change it the server-side code if there's something horribly wrong with this structure, but again - it works for the rest of my stored procedures.
Any direction/help would be greatly appreciated.


NTEXT,Indexing, and lob logical reads We have an query that would benefit from a covering index but one of the columns that would be returned is a NTEXT data type.  I know this can't be added to the index and I also know it would be good to convert the column to nvarchar(max) however as this is a 3rd party app (its Microsoft Dynamics!) we are at present unable to do this.

The issues looks to be all the lob logical reads that are being performed as part of a RID lookup.  Is there any way of speeding this up?

Are you allowed to add computed columns in the table or create a materialized view?

You could add a computed column and then index that:

ALTER TABLE <table>
   ADD ntext_as_nvarchar_max AS CAST(ntext_column AS NVARCHAR(MAX))  ;

CREATE INDEX index_name
  ON <table> (<index_columns>) INCLUDE (ntext_as_nvarchar_max) ;

The issue with this approach is that your queries will have to reference the new computed column (ntext_as_nvarchar_max) and not the original column for the index to be used.

Test at SQL-Fiddle


Error parsing YAML config file: yaml-cpp I'm using the following command (Windows machine) with the MongoDB shell version: 3.0.7:

mongod --config "G:\NodeApps\mongod.cfg" --install

Contents of mongod.cfg file are given below:

systemLog:
    destination: file
    path:"G:\NodeApps\data\log"
storage:
    dbPath:"G:\NodeApps\data"

Getting the below error:

Error parsing YAML config file: yaml-cpp: error at line 4, column 8: illegal map value
try 'mongod --help' for more information

Similar questions doesn't have the solutions for this.

What I've already tried:

I'm using spaces (not tabs)
I'tried by saving the file in ASCII format, as It was mentioned in one of the posts that Mongod config file shouldn't be saved in non-ACSII format. Not even in UTF-8.

Please help me with this.

The error messages indicate the specific line and column where the YAML parser is having an issue with your configuration file, but if you're not familiar with the format it can be difficult to work out what is expected.
Two sets of changes are required to make your config valid YAML:

Add a "space" between the systemLog.path and storage.dbPath keys and their values
YAML requires a space between key/value pairs, so reports: "error at line 4, column 8: illegal map value".

Remove the double quotes from your path values
YAML interprets backslashes inside quoted strings as introducing an escape character, so reports: "error at line 3, column 16: unknown escape character". As an alternative, you could also leave the path quoted but either escape the backslashes (\\) or use forward slashes.

The following config should work (assuming "G:\NodeApps\data\" has the correct directory and file permissions):
systemLog:
    destination: file
    path: G:\NodeApps\data\log
storage:
    dbPath: G:\NodeApps\data

There are several online testers for YAML syntax that can be useful to troubleshoot problems (eg: YAML Lint).


A weak entity with many to many relationship with its owner SQL I have this patient table which is an owner entity:

CREATE TABLE PATIENT
(
    FILE_NO NUMBER(4) PRIMARY KEY,
    NAME VARCHAR2(25),
    SEX VARCHAR2 (7),
    BLOOD_GROUP VARCHAR2(3),
    ALLERGIES VARCHAR (30),
    BIRTH_DATE DATE,
    INSURANCE_COMPANY VARCHAR (30)
);

and the appointment table is the weak entity

CREATE TABLE APPOINTMENT
(
    APP_NO NUMBER(4),
    FILE_NO NUMBER (4),
    FOREIGN KEY (FILE_NO) REFERENCES PATIENT(FILE_NO),
    CONSTRAINT APPOINTMENT_PK PRIMARY KEY (APP_NO,FILE_NO),
    DATE_TIME TIMESTAMP,
    DOCTOR_NAME VARCHAR2(25),
    DEPARTMENT_NAME VARCHAR2(25)
);

they have a many to many relationship. Is this a correct table for the relationship?

CREATE TABLE APPOINTED_TO
(
    APP_NO NUMBER(4),
    FILE_NO NUMBER (4),
    CONSTRAINT APPOINTED_TO_PK PRIMARY KEY (APP_NO,FILE_NO),
    CONSTRAINT APP_TO_FK FOREIGN KEY (APP_NO,FILE_NO)
        REFERENCES APPOINTMENT(APP_NO,FILE_NO)
);

My teacher insists I make a third table for the relationship. I can't take out the FILE_NO from the Appointment table because it's a weak entity.

It didn't make sense to me that's why I wanted to ask for an outside opinion.

The FOREIGN KEY is not correct. You must create 2 of them. One the PATIENT table for the FILE_NO field and one on the APPOINTMENT table for the APP_NO field.

PAT_TO_FK FOREIGN KEY APT_PATIENT_FK (FILE_NO) REFERENCES PATIENT(FILE_NO)
APP_TO_FK FOREIGN KEY APT_APPOINTMENT_FK (APP_NO) REFERENCES APPOINTMENT(APP_NO)


Is running a query on a view any faster or slower than running the raw query itself? Is there performance reasons to use views?

I realize this answer might be different per database. I'm using MS SQL but am curious about any database really. Does a view in general cause a performance optimization over its raw sql query equivalent?

Only thing I can think of is the select string size itself sent to the server... which is almost never a factor that anyone cares about.

Creating view will not give you any performance gain but may help you with abstraction of underlying object and management of object level security for your users.
Using Index view can definitely help you by preparing data (aggregate, computation etc) before hand but you need to be careful as explained in detail here.


How to outer join multiple tables to retrieve uncommon records I'm trying to do an outer join in MySQL to identify records not common to 3 tables. Here's my current effort http://sqlfiddle.com/#!2/02b92/30

I'm trying to retrieve the records not common to all three tables: 2,C,c (tbl2) 6,E,c (tbl3). Instead, I'm getting back the one common record: 1,A,c.

Thanks

SELECT *
FROM tbl1 A
LEFT OUTER JOIN tbl2 B
ON(
  A.a = B.a
  AND A.b = B.b
  AND A.c = B.c
)
RIGHT OUTER JOIN tbl3 C
ON(
  B.a = C.a
  AND B.b = C.b
  AND B.c = C.c
)
WHERE A.a is not null;

If you're not dead set upon using OUTER JOIN a way to get the expected result is

SELECT a, b, c
FROM   (SELECT 'tbl1' name, a, b, c
        FROM   tbl1
        UNION ALL
        SELECT 'tbl2' name, a, b, c
        FROM   tbl2
        UNION ALL
        SELECT 'tbl3' name, a, b, c
        FROM   tbl2) d
GROUP BY a, b, c
HAVING COUNT(DISTINCT name) < 3

otherwise you need to use FULL OUTER JOIN a type of join not supported by MySQL, but it can be emulated with a composition like (Full Outer Join in MySQL)

SELECT * FROM tbl1
LEFT JOIN tbl2 ON t1.id = t2.id
UNION
SELECT * FROM tbl1
RIGHT JOIN tbl2 ON t1.id = t2.id


CLOB vs Varchar2 performance in this case? From java application, I need to store the string which can be more than 4000 characters. I just need to store it. Then person having DB access can query and view it. That column won't be accessed from java application. Also I do not have any requirement to index it.

My understanding is varchar2 is better is in performance than CLOB when we there is a need to access the CLOB datatype or there is need to index it. Is that correct ?

My question - is there any impact in term of performance when we just need to store it and access it in sql developer throigh query ?

Pre-12c: varchar2 is limited to 4000 bytes. If you need to store more data in a field, go for CLOB.

From 12c: varchar2 is limited to 4000 bytes, or 32767 bytes after enabling Extended datatypes. If you need to store more data in a field, go for CLOB. With Extended datatypes, varchar2 is stored as a LOB internally, where "short" fields go to the table segment, while "long" fields go to the LOB segment.

Both types can be indexed (Oracle Text).

LOBs are designed to be written once, then treat as read-only data. DML operations on existing LOB data typically perform worse than on varchar2.

LOBs support deduplication (requires the Advanced Compression option).

Depending on the use-case, varchar2 can perform better or same.

But based on what you wrote above (read-only data, column is not accessed or searched), CLOB should be fine, and you do not need to fiddle with Extended Datatypes or care about the limitations of varchar2.


SQL Server- Limit to Concurrent table queries? In SQL Server, or RDBMS systems in general, is there a limit to the number of concurrent queries that can be made against a table?  How is it that N number of users can access the same physical data at the same time?

Update

Actually, more generally, forgetting about limits, how is it that two users can read from a table at the same time. Can someone explain this from a technical perceptive?  If data is stored in a physical space on a disk, how is it that two users / processes can query that same physical data at the same time?

How is it that N number of users can access the same physical data at the same time?

This is done using transactions (honors ACID properties) & concurrency models. SQL server supports Optimistic concurrency as well as Pessimistic concurrency (default concurrency model) which inturn uses different isolation levels (read committed is the default isolation level ) and locking mechanism to allow multiple users access the data at the same time.

Refer to : SQL Server Transaction Locking and Row Versioning Guide


voluntarily produce an error with DBCC CheckDb Would anyone know how I can make DBCC CheckDB produce an error?
I'm testing a script and need DBCC Checkdb in the simplest mode to return an error, also while trying to repair and when trying to go into single user mode...

The simple dbcc checkdb command thant I'm using is part of a batch file and is as follows:

OSQL -S MYPC -E -d MyDB -l 10 -Q "DBCC CHECKDB(AG_DB_STORESQL)" -b -o c:\MyFolder\MSSQL_DB_CHECK.Log

If no errors are found the script ends.  If errors are found, the script tries to fix them with the REPAIR_REBUILD option and if that fails the script tries to fix them with REPAIR_ALLOW_DATA_LOSS option.  Obviously, before trying to Repair the script attempts to put the DB in Single user mode (which is also error trapped)

So All I'm looking for is a way to cause DBCC CheckDB to return an error.

Would anyone know how I can make DBCC CheckDB produce an error?

Use the lethal dbcc writepage ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool]) ON a TEST (NON-PROD) fully sandbox environment.

  The purposes of DBCC WRITEPAGE are:

  To allow automated testing of DBCC CHECKDB and repair by the SQL Server team.
  To engineer corruptions for demos and testing.
  To allow for last-ditch disaster recovery by manually editing a live, corrupt database.

Apart from "The Guru" Paul Randal, @SteveStedman has a good series of Database Corruption Challenge (with solution - but refer it later once you try to address it by yourself).


Microsoft SQL Server 2008 - Catching user role changes with SQL Server Profiler? I'm trying to figure out why one of my SQL server users keeps losing sysadmin role when I uninstall a particular program. Can I use the SQL Server Profiler to track down the action that causes this to happen? If so which events do I need to check off on the Events Selection screen?

The "Security Audit>Audit Add Login to Server Role Event" will capture role drops as well, not just role adds.  However, that one alone might not give you the information you're looking for, depending on what you need (such as the specific statement executed if necessary).  So you could also add the Stored Procedure>RPC: Completed event, and if you want to get really granular add the SP: StmtCompleted as well.

An alternative could also be to use a server-level DDL trigger.  I use a trigger on the ADD_SERVER_ROLE_MEMBER event so I can catch if one of my admins decides to throw someone into the sysadmin role without my knowledge.  In my case I have an admin database with a table designed to store DDL event info and I insert a record for this type of event, among others.  The full list of events you can create a trigger on is here: http://msdn.microsoft.com/en-us/library/bb522542.aspx and there are good basic examples of how to use these events in T-SQL in the trigger body here: http://msdn.microsoft.com/en-us/library/ms175941.aspx


fix `mysqldump: Error 2013: Lost connection` on "cloud" server I have 6 months of backups left to perform and a single day can take up 200MB in raw mysqldump --extended-insert=false --complete-insert form.  If I try to dump more than a day worth of data I get mysqldump: Error 2013: Lost connection to MySQL server during query when dumping tabletableat row: 657925 (the row number changes).

my mysqldump query is mysqldump -h dbName.db.id.hostedresource.com -u 'user_name' -P 3306 -p'password' --net_buffer_length=4096 --single-transaction --extended-insert=false --complete-insert=TRUE --no-create-info --compact --insert-ignore --quick --where="start>='2016-01-13 00:00:00' AND start<'2016-01-15 00:00:00'" dbName table names

same issue exists with --extended-insert=true or --extended-insert=false and since it's a cloud server I have no access to the server or it's admin account to change buffer or cache settings.  Is there any hope of escaping this mindless drudgery within the next week!

In my case it helped to stop Apache, do mysqldump and then start Apache.

Nothing else helped, no options that were recommended. Nothing (and I searched all posts). I have low 512M memory and maybe this is also the cause.


SQL Server Express to SQL Server Standard upgrade? The database size on our SQL Server Express database is around 10GB. I checked our database size and it said 38GB.

Do I need to upgrade to SQL Server Standard edition?
Is it expensive to upgrade?
Would the web hosting company be able to provide us with the license?

Our website has been very slow and unreliable lately

Do I need to upgrade to SQL Standard Edition?
  Our website has been very slow and unreliable lately

Depends.

If your sites are still working then that implies that SQL server hasn't stopped accepting new data. I would expect exceptions along the lines of "Could not allocate space because the '<FG_NAME>' filegroup is full" if you were hitting the 10Gb limit already.

The 10Gb is per database not per server, so if your 38Gb is from several databases you may not have hit the limit yet. Also it doesn't count transaction log files so you might have massive log files (because your backup and other maintenance plans are not suitable for your recovery model for instance) that are responsible the majority of the space consumed.

  Is it expensive to upgrade?

This is not the right place for licensing/pricing information - such information changes regularly so any answer would quickly become obsolete and therefore incorrect. For current absolute prices consult your preferred software dealer(s) or the manufacturer directly. For a view on what is expensive and what isn't consult your accountant!

  Would the web hosting company be able to provide us with the license?

Depends. You'll have to ask them that. Some will, some won't. We don't even know what sort of hosting arrangement you have so we wouldn't be able to help even if we were the right people to ask.


Simple Oracle query hangs depending on WHERE clause Not used to using Oracle, but we have a large database where a non-unique query like

SELECT * FROM employees where department = 'HR'

is working, results list up no problem.

But when I do

SELECT * FROM employees where employeeID = '3HVtxO-F3004728F87EF61E'

the oracle database query is hanging (I am expecting one record to come back, and I am certain it exists because I copy pasted the employeeID from another query).

For the columns, only difference between the two is:

department is VARCHAR2(25)  NULL
employeeID is VARCHAR2(50)  NULL

Also department would have many matches while employee ID would most likely have 1 (I can't see that it is particularly defined to be unique in the table definition).

Other table characteristics:

not actually an employee table, it has work related data so I renamed it here...
contains more than a million rows and a few dozen columns
a bit of an old database, not sure who designed it at work ages ago, could have database integrity / indexing issues?
a regular select * from employees with no where clause would not work either because it freezes up around the half a million rows found mark.

Any idea why this could be happening? Should I design my query better?  How would you recommend diagnosing some issue related to problems with the database itself?   Trying to get a hang of this oracle database but a bit awkward to get used to since a simple query which should return one item is hanging.  Thank you.

Update 1:
Responding to comments, there is no indexing for this column.  Fortunately this is an outdated table which will not be in use at all, with a new version being made which will have indexed columns, so I think this was one issue.

Regarding the plan for the second query, it looks like:

Plan hash value: 123724717

------------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |    12 |  4416 |  2376K  (1)| 07:55:19 |       |       |
|   1 |  PARTITION RANGE ALL|          |    12 |  4416 |  2376K  (1)| 07:55:19 |     1 |    14 |
|*  2 |   TABLE ACCESS FULL | [Employee|    12 |  4416 |  2376K  (1)| 07:55:19 |     1 |    14 |
------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / Employees@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EmployeeID"='3R5MMN-0F9345L9IK8A349A043')

Column Projection Information (identified by operation id):
-----------------------------------------------------------
-- The list of all the columns follows.

My guess is that this is just a really awefully designed table and not designed to have gotten so big, or for someone to actually use a lot :)  For practical purposes, I will move on to more recent incarnations of this table which will have indexing and hopefully that will fix it.

following the advice of a_horse_with_no_name,

  Please show us the execution plan for the second query –
  a_horse_with_no_name 4 hours ago

I have posted the relevenat parts of the execution plan... however I can't make heads or tails of what is wrong with it from that....

I followed Mat's advice and guess that lack of indexing is the issue.  Because there is no indexing, it must be as Matt says, that this simple query is doing a full table scan, which could take longer than the few minutes I have been waiting for the query to complete...

Useful link about the concept of indexing: http://www.orafaq.com/node/1403

  Is that column indexed? Otherwise you'll get a full table scan, which
  can take a while if the table is big. – Mat 7 hours ago

  select index_name, column_name from dba_ind_columns where index_owner
  = 'THE_SCHEMA' and table_name = 'YOUR_TAB' order by index_name will get you all the indexed columns. Look at the explain plan to see what
  it is doing (look into set autotrace or use a GUI that has that
  feature, like sqldeveloper) – Mat 7 hours ago

Thanks for your help guys.  Let me know if I might be wrong in that assumption, or if you might have additional insight a_horse_with_no_name....


How can I model a medical scenario in an entity-relationship diagram? I am a self-taught frontend developer that is trying to go full-stack, so I am trying to teach myself data modelling by means of working on some exercises out of a database management book.
In this way, I have started creating an entity-relationship diagram (ERD), for a medical scenario, that is shown bellow:

I am not quite finished as I am having some issues converting some of the relevant business rules into ERD notation. Also, when reading the business rules concerning the associations keep in mind that the entity types Physician, Order, Diagnosis, Treatment, and Patient are needed (because in the book they are stated as business rules and I would like to understand that specific model), and although they could probably be modeled in a different way, I am trying to understand this specific model.
Business scenario description

Physicians diagnose any number of Conditions affecting a Patient, and a Diagnosis may apply to many Patients.

The involved hospital records the following information: date and time of diagnosis, diagnosis code, and description.

Physicians may order and perform any number of Services and Treatments for a Patient, or may not perform any Treatment.

A Treatment or Service may be performed on any number of Patients, and a Patient may have Treatments performed or ordered by any number of Physicians.

For each Treatment or Service rendered, the hospital records the following information: Physician ordering the Treatment, treatment date, treatment time, and results.

Entity Type Definitions

Patient. A person who is either admitted to the hospital or is registered as an outpatient. Each patient has an identifier, the medical record number (MRN), and a name.

Physician A member of the hospital medical staff who may admit patients to the hospital and who may administer medical treatments. Each physician has a physician ID and name.

Diagnosis. A patient's medical condition diagnosed by a physician. Each diagnosis has a diagnosis has a diagnosis ID/code and diagnosis name. Example: 1032, STAPH FOOD POISIONING.

*Treatment. Any test or procedure ordered by and/or performed by a pysician for a patient. Each treatment has a treatment ID/treatment code and treatment name using standard codes. Procedure codes are used for diagnostic and therapeutic procedures. Example: 1244, Chest X-Ray.

Order. Any order issued by a physician for treatment and/or services such as diagnostic tests (radiology) and therapeutic procedures (physical therapy), or drugs (perscriptions). Each order has an order ID, order date, and order time.

In this manner, I was wondering if someone could look at this and derive the same ERD from the business rules I have or if I messed up the translation.
My considerations

The specific issue I can not seem to wrap my head around is the existence of multiple relationships between the entity types.
First of all, I do not even know what kind of cardinalities to put on regarding the relationship between Order and Treatment.

Another issue for me is the multiple relationships between Patient, Diagnosis and Physician. Also, the same issue with Physician, Patient, and Treament.
Are these perhaps suppose to be ternary degree relationships, and (or) contain weak entity types to make this more understandable? Like Order and Treatment, or Treatment and Diagnosis I could see one depending on another as a Treatment in my mind would depend on the Diagnosis (correct me if I am wrong).

Consideration about multiple relationships between entities

In real life, Patients and Physicians interact in several ways. The most obvious one is that you go to see your doctor when you're ill (that's one kind of relationship between Patient and Physician). But you, as a Patient, may refer one Physician to a friend of yours (who is also ill, and, as such a Patient), because you liked how it works. This is a ternary relationship involving two Patients (the referer, and the refered) and one Physician.

Non-binary relationships

Not all relationships are between two tables (such as in the previous case). Another instance: a Diagnosis is performed by a Physician to a Patient (and, you could add: at some time, in some place, after x, y and z tests, etc. as attributes to this relationship). Having one ternary relationship is not the same as having two relationships Physician makes Diagnostic and Patient diagnosed Diagnostic. It you ever only visited one doctor, no doubt about who diagnosed. As soon as you visit two doctors... who diagnosed what is much less clear.

This is not answering all your questions, but hope it helps.


Two tables with the same primary keys Can two tables have the same primary keys?

For instance:

Yes. This is a common scenario when implementing 0..1 relationships.


How do you restore (or check) only the extended properties in a MS SQL Server .Bak file? We store the version number of our DBs as an extended property. As part of the automated build process, I want to be able to check that the "empty" DB that is part of the build set matches the version number of the executables being built.

I understand that I can't just read the *.bak file directly. I already query the (restored) DB to get that property. However, as part of the build process it seems silly to restore the entire DB just to get this one piece of information.

Is there a simple way to get just the extended properties without restoring the whole DB?

There's really no other way that I can think of than restoring the database.  The best option would be to have versioning stored elsewhere so that you don't have to go through all this trouble just to get a version number.

What you could do is have a little configuration file and a small application that performs the backups for you, and stores a key/value pair of filename/version.  That way you will have a reference as to what backup file is for which database version.  An easy application to write and it would save you a ton of time with restoring/querying just to find out this bit of data.


How can I list all the tablespaces without getting into each instance? I got around 98 instances in a single AIX server. I need to list all the tablespaces, their path and usage. Is there a way I can do that without getting into each instance, connecting into the database etc?

I don't have anywhere to test this at the moment, but either of these might work.

If you have su privileges to all instance owner users:
db2ilist | while read inst; do su - $inst db2pd -alldbs -tablespaces; done

If you have sufficient privileges on all instances yourself:
db2ilist | while read inst; do env DB2INSTANCE=$inst db2pd -alldbs -tablespaces; done


Replacement for Pragma Restrict references Up to Oracle 11.1 you could mark a package as only reading from the database by adding
PRAGMA RESTRICT_REFERENCES( DEFAULT, WNDS);

This has now been deprecated.  I would like to enforce read only on some of the packages.

it would help ensure that junior developers don't add new functionality that writes 'just because'
it makes granting execute on read only packages to report users cleaner

Is there any substitute that can enforce that a package only reads from the database?

I have several poor answers. I hope one of them works for you.

Moving the packages to another schema may be an option. You might then grant that other schema a bare minimum set of privileges. This would get ugly for name resolution, though.
Auditing the code by searching the schema might be another option:

SELECT * FROM dba_source
WHERE schema = 'ME'
  AND name LIKE 'MYPROC'
  AND (UPPER(text) LIKE '%UPDATE %'
       OR UPPER(text) LIKE '%INSERT %'
       OR UPPER(text) LIKE '%DELETE %');

Defining the package as AUTHID CURRENT_USER would scale back the permissions at runtime to the calling user's permissions. This solution has its own can of worms.


Dynamic temporary index creation In my undergrad databases course, my professor mentioned that some DBMS software is so advanced that it can detect when a long-running query would benefit from an index. The DBMS can then create that index for the duration of the query to increase performance.

However, I don't think that's possible, and some initial Googling seems to agree with me. Does anyone know of a DBMS that actually implements this strategy?

Hardly "so advanced"; this is quite a common feature. Your professor is behind the times! :-) SQLite is one of the simplest databases, yet see section 11 on this page.

  Since the cost of constructing the automatic index is O(NlogN) (where
  N is the number of entries in the table) and the cost of doing a full
  table scan is only O(N), an automatic index will only be created if
  SQLite expects that the lookup will be run more than logN times during
  the course of the SQL statement


Can a SQL Server backup device hold both data and log backups? If I create a backup device - i.e. using the SP:

EXEC sp_addumpdevice 'disk', @bakName, @bakPath;

Can I write both data and log backups to it? Or is it better practice to have different devices for data and log files...

You can write all 3 backup types to a backup device, since a backup device is simply a logical location pointer: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/define-a-logical-backup-device-for-a-disk-file-sql-server.
Full, Differential, and T-Log backups can all be written to the device. Think of it as an "alias" or "shortcut" to the backup path.

Further info based on the comments...

The backup device is really just an alias, so you don't have to type out "\server\drive\folder\subfolder...". Now, the different types of backups,

full = .bak

diff = .bak

t-log = .trn
would likely (NOTE: LIKELY) be in different folders/subfolders. You can store them all in the same folder, but I have found in the past that trying to differentiate between a full and a diff can be problematic, even if you name them differently. I always create the following folder structure for backups:

System

Full

Differential

TransactionLog

This way, I can easily write the backups to the correct location, and even more importantly, I can move/copy/purge these files easily based on this file structure. It's much easier in a restore situation to know where all the pertinent files will be located, rather than have to search through hundreds (or thousands) of files in a single folder.
So, in conclusion, for ease of administration, I would highly recommend creating a backup device for full backups (up to you whether you want to separate these out between system and user databases), one for differential backups and one for transaction log backups.
Also, I know it's common (even Ola's Maintenance Solution does this) to create a top level folder based on the instance name, then next level folder is the database name, and under that is each type of backup. This is a pet peeve of mine (yes, personal bias) because then traversing this structure is unnecessarily complicated. But, make sure to create something that will best fit your needs--and remember that the reason for backups is to recover from a problem...so, don't add to that problem by struggling to find the backups you need.


Add array table constraints, empty or null if other column is false I'm adding constraint on an existing table jobs:

DROP TABLE IF EXISTS jobs CASCADE;
DROP TYPE IF EXISTS job_state;
CREATE TYPE job_state AS ENUM ( 'UNSTARTED', 'STARTED', 'FAILED', 'COMPLETED' );
CREATE TABLE jobs(
status job_state NOT NULL DEFAULT 'UNSTARTED',
error_message text[]
);

ALTER TABLE jobs
ADD CONSTRAINT error_message_is_null_if_failed_is_false
   CHECK ( status IN ('UNSTARTED', 'STARTED', 'COMPLETED') AND ( error_message IS NULL OR error_message = '{}' ));

What I want is

INSERT INTO jobs VALUES ('UNSTARTED', null);
INSERT INTO jobs VALUES ('STARTED', null);
INSERT INTO jobs VALUES ('FAILED', null); -- I would like to be able to do this

I know this error_message != '{}' OR error_message IS NOT NULL is not written properly. But I'm stuck finding a way to write it.

The constraint should be written using an OR like (NOT A) OR B

ALTER TABLE jobs
ADD CONSTRAINT error_message_is_null_if_failed_is_false
   CHECK ( NOT ( status IN ('UNSTARTED', 'STARTED', 'COMPLETED')) OR ( error_message IS NULL OR error_message = '{}' ));

INSERT INTO jobs VALUES ('UNSTARTED', null); -- no error
INSERT INTO jobs VALUES ('STARTED', null); -- no error
INSERT INTO jobs VALUES ('FAILED', null); -- no error
INSERT INTO jobs VALUES ('COMPLETED', null); -- no error

INSERT INTO jobs VALUES ('UNSTARTED', '{}'); -- no error
INSERT INTO jobs VALUES ('STARTED', '{}'); -- no error
INSERT INTO jobs VALUES ('FAILED', '{}'); -- no error
INSERT INTO jobs VALUES ('COMPLETED', '{}'); -- no error

INSERT INTO jobs VALUES ('UNSTARTED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('COMPLETED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('STARTED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('FAILED', '{"error message"}'); -- no error


Connection refused when trying to set up replication I set up replication.  I've done this on two servers so far, and it's worked.  Third time unlucky, as it were.

The production server is CentOS 6.4, and the replication server is CentOS 6.5.  The prod box will join its little brother soon as a 6.5 box, on 3 January - date of the change.  But anyway, the MySQL server is 5.1.71 on both servers.

On the server, I edit the /etc/my.cnf file:

log-bin=mysql-bin
server-id=1

... I create the user :

CREATE USER 'repli'@'%.example.com' IDENTIFIED BY 'blagblah';

.. and then ...

GRANT REPLICATION SLAVE ON *.* TO 'repli'@'%example.com' IDENTIFIED BY 'blagblah';

A mysqldump is performed every morning, so I grab the dump and rsync it over to the slave.  I get the master status, the filename and the position in the log.

On the slave, I edit the /etc/my.cnf file:

server-id=2

I then restart the server, but (is this where I have gone wrong?), I just start it from /etc/init.d/.

I login to the mysql instance and execute:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='server1.example.com', MASTER_USER='repli', MASTER_PASSWORD='blagblah', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=24112816;
START SLAVE;

When I check slave status, however, it tells me that there is an error connecting to the master.

So I start to wonder whether the fact that I didn't start the server with 'skip-slave-start' might be responsible.  So I shut down the server, and execute:

sudo /usr/bin/mysqld_safe --skip-slave-start

... and this is where it starts to get weird.

mysql -u root -pXXXXXXXX

And the server tells me ..

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Excuse me?  This is the slave server, and the box on which five minutes earlier, I had being typing commands.

Where have I gone wrong?

After you import the dump file from the master, your new root password on the slave will stop being valid on the slave not later than, but sometimes as late as, the first time you restart it, because the mysql.user table is going to be overwritten... the new root password after restart on the slave is going to be the same as the root password from the master (assuming the master has a SUPER user called "root").

The workaround is either using the master's root password (assuming it has a root@localhost account) or is to keep your connection open to the slave while restoring the dump file, and then after the restore is done, modify the mysql.user table to have the values you want, before disconnecting and restarting.  FLUSH PRIVILEGES; after making the change, then try logging in from an additional connection to verify that it's going to work, then restart the slave.

If you RESET SLAVE; before the restart, you shouldn't need to --skip-slave-start.


Database is happily available even during log growth? I’ve been creating a process to go through all our databases and analyze the log structure, backup, shrink, and resize appropriately taking operational requirements and VLF sizing/amount into account. My code is working beautifully, but I encountered something in testing today I wasn’t expecting.

I have a good sized DB with a 104GB log and around 900 VLFs. I backed up the log and shrank down to next to nothing during a period of low activity. I then began to grow it back out to it’s original size in 8GB iterations, keeping VLFs around 512MB. During regrowth (somewhere between 48GB and 56GB), I was curious about DB availability and started running random SELECTS against different tables. Results returned without issue. I then created a table in the DB and looped a bunch of inserts.

I was surprised when the log continued to grow and the table creation and inserts completed without issue or delay. I was under the impression the DB would somehow be locked down, blocking DDL and DML, until regrowth completed. Of course, considering the circular nature of the log, I can see how this would be a false assumption. But I can’t find anything on the web one way or the other.

Can someone shed some light or point me int he right direction on what is going on? Thanks!

I ended up solving my own question with the below code:

    --Using the AdventureWorks2012 database in Full mode with log shrunk to 64MB
    --setup test table
    USE AdventureWorks2012
    go

    IF OBJECT_ID('TestTbl') IS NOT NULL
        DROP TABLE TestTbl
    GO

    CREATE TABLE TestTbl
    (
          RowID          INT IDENTITY(100,100)
        , RightNow       DATETIME
        , SomeField      VARCHAR(255)
        , SomeOtherField VARCHAR(255)
        , CONSTRAINT PK_TESTTBL_ROWID_RIGHTNOW
            PRIMARY KEY CLUSTERED (RowID, RIGHTNOW)
            WITH (IGNORE_DUP_KEY = OFF)
    )
    GO

    --grow the log out to roughly 50GB and write to the test table in between each grow
    INSERT TestTbl
    VALUES (GETDATE(),'GROWING ITERATION 1','8193 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 8193)

    INSERT TestTbl
    VALUES (GETDATE(),'GROWING ITERATION 2','16386 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 16386)

    INSERT TestTbl
    VALUES (GETDATE(),'GROWING ITERATION 3','24579 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 24579)

    INSERT TestTbl
    VALUES (GETDATE(),'GROWING ITERATION 4','32772 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 32772)

    INSERT TestTbl
    VALUES (GETDATE(),'GROWING ITERATION 5','40965 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 40965)

    INSERT TestTbl
    VALUES (GETDATE(),'GROWING ITERATION 6','49158 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 49158)

    --in a separate session insert to the test table, i did this after the first 8GB was grown
    INSERT TestTbl
    VALUES (GETDATE(),'SomeValue'+CAST(DATEPART(MILLISECOND,GETDATE()) AS VARCHAR(25)), 'SomeOtherValue'+CAST(DATEPART(MICROSECOND,GETDATE()) AS VARCHAR(25)))
    GO 50000

    --monitor growth iterations
    SELECT * FROM TestTbl WITH (NOLOCK)
    WHERE SomeOtherField LIKE '%MB%'

    --monitor log
    SELECT DB_NAME(database_id) DatabaseName,name LogName,size*8/1024 SizeInMB
    FROM sys.master_files
    WHERE database_id = DB_ID('AdventureWorks2012')
        and type_desc = 'LOG'

    DBCC LOGINFO ('AdventureWorks2012') WITH NO_INFOMSGS

    USE AdventureWorks2012
    GO

    SELECT
     [Current LSN],
     [Transaction ID],
     [Operation],
     [Transaction Name],
     [Description],
     [CONTEXT],
     [AllocUnitName],
     [Page ID],
     [Slot ID],
     [Begin Time],
     [End Time],
     [Number of Locks],
     [Lock Information]
    FROM sys.fn_dblog(NULL,NULL)

    --modify the rowIDs to span a growth iteration and observe short pause and continue
    SELECT * FROM TestTbl
    WHERE RowID BETWEEN 239500 AND 241000


Query to find plan details for a specific stored procedure, searching by name I'm trying to write a query to find information about query stats and the query plan for a specific stored procedure, but I can't manage to find the right DMV or query to look for a specific stored procedure.

So far I have:

select
    qs.sql_handle
    , qs.statement_start_offset
    , qs.statement_end_offset
    , qs.plan_handle
    , execution_count
    , st.text
    , substring(st.text, (qs.statement_start_offset/2)+1,
        ((case qs.statement_end_offset
            when -1
                then datalength(st.text)
            else
                qs.statement_end_offset
            end - qs.statement_start_offset) / 2 + 1)) as [Filtered text]
    , qp.query_plan
from sys.dm_exec_query_stats as qs
    cross apply sys.dm_exec_sql_text (qs.sql_handle) as st
    cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
where st.text like '%myProcedure%'
order by qs.sql_handle
    , execution_count desc

But this query doesn't return any information. Ideally I would replace the st.text like condition with something like object_name(procedure_id) = 'myProcedure', but I can't find the right way to do so.

Is there a better way to do so?

I could replace the condition in the WHERE clause with a qs.sql_handle = 0x000004004040400..etc. but how can I find out the sql_handle for the procedure? (this information isnt' found in sys.objects or in sys.procedures).

Any information is greatly appreciated.

Update:

I've executed the procedure (exec myProcedure ...) in a test environment and now it is returned by the above query and so I can get the plan handle to use the where sql_handle = 0x0034300.., but I'd like to run the same query from above in production, without having to run the procedure in production (just to find out its plan handle).

This is the reason why I'm trying to write the query in this way, to get the information based on name, not sql_handle (just because in prod I don't know what is its sql_handle).

Given that the sys.dm_exec_query_stats DMV returns data for the entire instance, you need to be able to get object Schema and name info across all Databases. Using ST.objectid = OBJECT_ID(N'dbo.ProcedureName'), as has been suggested, is error-prone since it will only resolve the name relative to the database in which the query is being executed:

If the object name does not exist in that Database, then it will return NULL, which will filter out all rows.
If the object name does exist in that Database but you are actually wanting that object name from a different database, then you will get an object_id value that might be correct, but could just as well point to a different object and might return one or more incorrect rows.
If an object_id value is returned, whether correct or incorrect, that particular value can exist across multiple databases and so you might get multiple rows back for different objects in different databases.

At the very least, you would need to provide a fully-qualified object name (i.e. three-part name). However, even if you do use OBJECT_ID(N'DatabaseName.SchemaName.ObjectName') the query still has the potential to run into problem #3 noted above since that object_id, even being correct, can exist across multiple Databases. So you need a second condition using the DB_ID() function to narrow down to the intended Database.

WHERE st.[objectid] = OBJECT_ID(N'DatabaseName.SchemaName.ObjectName')
AND   st.[dbid] = DB_ID(N'DatabaseName')

HOWEVER, my preference is to use the OBJECT_NAME and OBJECT_SCHEMA_NAME functions as they both accept an optional 2nd parameter for database_id. The advantage here is that you can use them in the SELECT clause when you are not narrowing down to one specific object, so that you can see the object names for all of the rows in the DMV. It also allows for filtering down to the same object across multiple Databases, which is useful when you have the same Stored Procedure in multiple Databases and want to see rows for it across all Databases, even though it likely has different object_id values across those Databases.

Hence, you could optionally add the following to the SELECT clause (I use this when Cross Applying sys.dm_exec_sql_text to DMVs such as sys.dm_exec_requests, etc):

DB_NAME(st.[dbid]) AS [DatabaseName],
OBJECT_SCHEMA_NAME(st.[objectid], st.[dbid]) AS [SchemaName],
OBJECT_NAME(st.[objectid], st.[dbid]) AS [ObjectName]

And you would then update your query to have the following WHERE clause:

WHERE OBJECT_NAME(st.[objectid], st.[dbid]) = N'my_proc_name'
AND   OBJECT_SCHEMA_NAME(st.[objectid], st.[dbid]) = N'dbo' -- or whatever schema
-- AND  DB_NAME(st.[dbid]) = N'DatabaseName' -- optionally narrow down to specific DB


How to recover a DB with a missing .mdf file? I have a DB and when I detach it I cannot find its .mdf file. I only have .ndf and .ldf files in my DATA folder. Also, I do not have backup file...

How to recover my DB?
Can I do something without the .mdf file?

P.S. On this server, I have more DBs and most them have .mdf files. Only a few are missing their .mdf files. Why is that?

Technically, you don't have to have an MDF file. Your database and log files can be named any extension - MP3, PDF, ZIP, you name it. (Sometimes I play terrible jokes on my coworkers.)

If you want to check the file names before you detach the databases, run this query in the database you want to examine:

SELECT * FROM sys.database_files

You can also right-click on the database in SSMS and click Properties, then look at the files from there.


Does the "don't use errors for flow control" axiom apply to postgres? In traditional programming there is an axiom that states, "do not use errors for flow control". A general example is to throw an error then catch the error instead of using an ordinary conditional statement or break statement. This is harmful because the application has to unwind the call stack and invoke some relatively expensive exception handling logic versus simply handling a conditional statement.

I am working with a postgres system where a user is calling a function in postgres that throws an error instead of returning no rows when a condition is not met. The condition is roughly a "this input value does not exist, nothing to do" as opposed to a truly exceptional case.

Does postgres incur runtime costs when an error is thrown in a similar manner traditional programming? In other words is using exceptions as flow control in postgres harmful to performance or just sloppy?

Can't tell, from the top of my head, the exact sequence of events in case of an exception. But I can tell you that it is comparatively expensive and should be avoided unless needed. An exception (error) causes the surrounding transaction to be rolled back, unless trapped.

But to trap an error, you need an EXCEPTION clause in a plpgsql block (in a function, procedure or a DO statement). And it is even expensive to have an EXCEPTION clause in a plpgsql function block to begin with (before any exceptions are raised), because such a block effectively forms a subtransaction (that can potentially be rolled back) incurring substantially more overhead. Consequently, the manual warns:

  A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.


Changing Auto-Growth Setting to MBs vs Log Drive Huge Occupancy These are our PROD Databases current settings and total information about log file utilization.

We are using SQL Server AOAGs 2014

Auto-Growth I am planning to set 512 MB

Recently we have seen huge T-Log drive utilization (close to 70 GB) each time Index Maintenance task runs (Re-build Index Task Online and Offline).

Our Log Drive Total Capacity is 300 GB (Expanded 100 GB and now 400 GB Total)

Now I am planning for below two changes :

I am planning to change the auto-growth setting to MBs from %.
Removed Maintenance Task (Rebuild Index Task) which is created from maintenance plans option.
Now I deployed Ola Hallengrens Index Optimization (Image below) and scheduled the SQL Job to run every Sunday @1:00 AM.

My Concerns and expert suggestions is highly appreciated:

Will the above changes help my Disk utilization to be at least bit lower than before. Because expanding drive each time is becoming burden for us.
What else should I keep in mind along with this to optimize Log Drive Occupancy to be lower.

Please see current settings :

PROD Server 1 :

Ola Hallengrens Code for Index Optimization:

Basics of Transaction Log files

Transaction Log files (TLog) are like a notepad with pages that hold all the information on anything that has changed during the day. The TLog (notepad) has a beginning and an end when it is opened. The TLog (notepad) will also know where it was before based on the LSN (log sequence number) (notepad: Post-IT Index). If deletes/inserts/updates are finished they are marked as COMMITed and (can be) removed from the TLog (notepad) when a backup occurs which results in a CHECKPOINT being set in the TLog file. In the notepad analogy the old notes get removed from where they were and because your notepad is always the same size, the removed pages get replaced with fresh white empty pages; be it at the beginning or at the end for your notepad. Why? Because your notepad only has space for 100 pages. The removed notepad pages are then stored in your central storage (binder).

Following is a simplistic view of the TLog file.

TLog after database has come online

<begin>

LSN 22 : CHECKPOINT
LSN 23 : Delete page content 1:123456 <== This is where your Post-IT index is

<end>

Transaction Logs are Circular

Equally important: The TLog is circular in nature (round robin), so if modifications can't be stored at the end of the TLog and there is free space at the beginning, the transaction will be stored there.

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'

LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -> 2
<end>

As you can see in above example the transactions were written to the TLog and circled around in order to log further changes.

TLog Full, Now What?

Now if the TLog is full it has the following options:

crash the database because of insufficient space
Expand if the TLog allows for growth

Let's follow on with the simplistic model of my TLog file and the situation where the TLog file has to grow. Two transactions modify data.

Before

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'

LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -> 2
<end>

LSN 28 is logged for the first transaction

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -> 'Peter'
LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -> 2
<end>

TLog has to grow

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -> 'Peter'
LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -> 2

<end>

LSN 29 is logged for the second transaction

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -> 'Peter'
LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -> 2
LSN 29 : Delete page content 5:444532

<end>

How Does This Relate to Your Questions?

You should have the basics now, so let's have a look at how this all relates to your questions.

If your application is modifying data or if the maintenance task is modifying data all modifications have to be stored in the TLog file. If the TLog has to grow because there is insufficient free space in the file and if the growth settings permit, then the TLog file will grow.

Yes, But You Changed Things

  Will the above changes help my Disk utilization to be at least bit lower than before. Because expanding drive each time is becoming burden for us.

percent (%) or MB growth?

Changing the growth settings from % to MB is a good thing to do, but it doesn't eliminate the need for the database to store transactions somewhere, if the space in the TLog is all used up.

Additional reading:

SQLSkills | Category : Transaction Log (SQLSkills)
The Transaction Log (SQL Server) (Microsoft Docs)
10 most important SQL Server transaction log myths (SQLShack)

Removing Maintenance Plans

This can reduce the need for the TLog file to grow because INDEX REORGANIZE and INDEX REBUILD are no longer performed. But, ...

Adding Ola Solution

... adding the Index Optimisation Jobs from Ola will perform Index maintenance too. So you've just replaced Index Maintenance with Index Maintenance. No real gains.

Ok, So What Do You Have to Do Now?

  What else should I keep in mind along with this to optimize Log Drive Occupancy to be lower.

Adequate TLog file sizing

Don't try and shrink the TLog file too much. It will grow again. Instead try sizing the TLog file according to your observations, allow for a little growth and monitor how full the TLog file is.

Frequent TLog Backups

If you have a look at my simplistic model, you may have noted that the TLog didn't have a lot of CHECKPOINTS logged. If the database has to handle a lot of transactions modifying data, then shortening the interval between TLog backups can help keep the TLog from growing, because a TLog backup creates a CHECKPOINT which allows the data to be written to the database. Let's add an example using my over simplistic model.

Additonal Backup

A backup is performed and logged in the TLog and during the backup a user modifies data:

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -> 'Peter'
LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -> 2
LSN 29 : Delete page content 5:444532
LSN 30 : CHECKPOINT
LSN 31 : Change page content 5:333333 from 'bad' -> 'good'
<end>

We now have everything in the database (up to LSN 30) and the SQL Server could free up the TLog to look like this:

<begin>

LSN 30 : CHECKPOINT
LSN 31 : Change page content 5:333333 from 'bad' -> 'good'
<end>

Great, the TLog can now continue logging modifications at the beginning again.

Optimal Virtual Log File Sizing (advanced topic)

Having an adequate VLF size (not too big, not too small) can help keep TLogs at bay.

Additional reading:

Important change to VLF creation algorithm in SQL Server 2014 (SQLSkills)
Transaction Log VLFs – too many or too few? (SQLSkills)

VLF are like subsets of pages in my simplistic model or you could look at them as groupings of pages. Having to many pages in the group (VLF) can be good or bad depending on a couple of factors. Please read up on them in the linked articles. Basically it boils down to:

Having long running transactions can block a lot of VLFs which in turn can result in TLog growth.
Having too many VLFs can have a negative impact on SQL Server performance.

Additional Resources

Why Does the Transaction Log Keep Growing or Run Out of Space (DBA.SE)
Switching to Simple Recovery - shrinking transaction logs (DBA.SE)
SIMPLE or FULL recovery model for databases? (DBA.SE)
Large number of VLF and shrinking the log (DBA.SE)
How important are transaction log VLFs for performance? (DBA.SE)


Copy files twice using xp_cmdshell When I run this command

exec xp_cmdshell 'copy C:\---\cmdshell1\a23.txt C:\---\cmdshell2';

It copies a23.txt file from cmdshell1 to cmdshell2 folder.

When I run the same command second time it does not copy the same a23.txt file to folder cmdshell2 for second time.
So after running xp_cmdshell command multiple times I only get one file in cmdshell2 which was generated the first time.
My question is, how to write this command in order to copy the a23.txt file from cmdshell1 folder into cmdshell2 the amount of times I ran the query(if I have run the above query 3 times, I want to have 3 copies of a23.txt file in cmdshell2).

Here is an example of building a dynamic xp_cmdshell command that includes a timestamp on the file

declare @Cmd varchar(100)
set @Cmd = 'copy C:\test\a23.txt C:\test\a23-' + replace(replace(convert(varchar(19),sysdatetime()),' ','-'),':','-') + '.txt'
print @cmd
exec xp_cmdshell @cmd;


Grouping by overlapping arrays, transitively, without duplicates I've found:

Group by array overlapping
https://stackoverflow.com/a/23650080/5419599

However, I'm having trouble putting it to use in my case.

I have a table like so (the real myid values are hashes, but simplified here for illustration):

create temp table a (myid text, ip inet);
insert into a (myid, ip)
values
  ('0a', '10.10.1.1'),
  ('0a', '10.10.1.2'),
  ('0a', '10.10.1.3'),
  ('0b', '10.10.1.2'),
  ('0b', '10.10.1.4'),
  ('0c', '10.10.1.5'),
  ('0d', '10.10.1.3'),
  ('0e', '10.10.1.6'),
  ('0e', '10.10.1.7'),
  ('0f', '10.10.1.8'),
  ('0f', '10.10.1.9'),
  ('10', '10.10.1.9'),
  ('11', '10.10.1.10'),
  ('12', '10.10.1.11'),
  ('12', '10.10.1.4'),
  ('1a', '10.10.1.2'),
  ('1a', '10.10.1.4'),
  ('1e', '10.10.1.11'),
  ('1f', '10.10.1.12'),
  ('23', '10.10.1.12');

The result I can't work out how to produce is:

         ids         |                         ips
---------------------+------------------------------------------------------
 {0a,0b,0d,12,1a,1e} | {10.10.1.1,10.10.1.2,10.10.1.3,10.10.1.4,10.10.1.11}
 {0c}                | {10.10.1.5}
 {0e}                | {10.10.1.6,10.10.1.7}
 {0f,10}             | {10.10.1.8,10.10.1.9}
 {11}                | {10.10.1.10}
 {1f,23}             | {10.10.1.12}

The logic here is that any ids with ips in common are grouped together, transitively.  For instance, 0a has an ip in common with 0b; 0b has one in common with 12; 12 has one in common with 1e, and so forth.

There are tens of thousands of rows, no specific limit on how many ips for any given id, and no specific limit on how many ids may show any given ip.

I know how to aggregate by ip, or aggregate by id, but doing both transitively is giving me trouble.  I tried a recursive CTE but I couldn't seem to get it right, and I'm not sure if that was the right approach in the first place.  (If I could first group by id and then group by overlapping array of ips, and avoid duplicates in the aggregation, I would be all set, but there may be a better approach.)

Is there a way to produce the above results with standard SQL?  Or at least with standard Postgres?  (I'm using 9.6.6.)

Here is a failed attempt.  (It is a legal query that does return results, but not the desired results.)  It fails because:

It includes intermediate results rather than replacing them with later results, and
It doesn't sort the array concatenations, so it includes each result multiple times.  This is also a hugely slow query for the actual dataset I am working with, since it returns up to n! times each result.

Here is the query:

with recursive b as (
  select
    array[myid] as ids,
    array_agg(ip) as ips
  from a
  group by myid
), c as (
  select
    ids,
    ips
  from b
  union
  select
    b.ids || c.ids,
    b.ips || c.ips
  from
    b
    join c on
      (not b.ids && c.ids)
      and (b.ips && c.ips)
)
select * from c
;

One of the key parts of Jack Douglas's solution in Group by array overlapping is the | (pipe) operator used on arrays in the recursive part of the recursive t CTE like this:

...
select t.id, a.id, t.clst | a.clst
...

This operator concatenates two arrays suppressing duplicate items. The reason that answer cannot be directly applied to your setup is because apparently the | operator is defined for int arrays only, while you need a way to perform the same operation on inet arrays.

You can do that by treating the arrays as row sets. If you notice, what the | operator produces is effectively a union of two sets. Therefore, if you unnest both arrays, union them and aggregate the combined set back as an array, you will get the same result. So, this expression,

t.clst | a.clst

can be replaced with a correlated subquery:

(
  select
    array_agg(sub.n)
  from
    (
      select unnest(t.clst)
      union
      select unnest(a.clst)
    ) as sub (n)
)

Yes, the substitution is quite unwieldy in comparison, but it does the job, and that is something to start with.

Adapting the solution to your example (and adding a bit of white space to the original code), the complete query would look like this:

with recursive
  cte_a as
  (
    select
      myid,
      array_agg(distinct ip) as ip
    from
      a
    group by
      myid
  )
, cte_t (myid, pmyid, ip) as
  (
    select
      myid,
      myid,
      ip
    from
      cte_a

    union all

    select
      t.myid,
      a.myid,

      (  /* this is the replacement expression */
        select
          array_agg(sub.n)
        from
          (
            select unnest(t.ip)

            union

            select unnest(a.ip)
          ) as sub (n)
      )

    from
      cte_t as t
      join cte_a as a
        on a.myid <> t.pmyid and t.ip && a.ip and not t.ip @> a.ip
  )
, cte_d as
  (
    select distinct on (myid)
      myid,
      ip
    from
      cte_t
    order by
      myid,
      cardinality(ip) desc
  )
select
  array_agg(myid),
  ip
from
  cte_d
group by
  ip
;

You can test the query in this demo at db<>fiddle.uk.

Note also that Jack's word of caution probably applies to your situation as well:

  Bear in mind that this is unlikely to perform well on millions of rows.


How to Get the First in and Last out in night shift? I already have the Query for Getting the Day shift but I'm Having a problem in the night shift, Which is this query.

SELECT user_id,
Min(verify_date) as First_IN,
Max(verify_date) as Last_Out
from tbl_attendance group by user_id,verify_date

But this query doesn't give the night shift so i tried another Query Which is:

SELECT user_id,verify_date,
Lead(verify_date,-1) OVER(PARTITION BY user_id order by verify_date desc)
from tbl_attendance

This Query Give's The past attendance but i don't know how do i get rid the extra attendance in my Biometrics (Or the Double tap in Biometrics) the Biometric is used as the door so i need to get the first in and last out.

And also I'm having a problem with my SQL Fiddle The Date Time is maybe broke it alway's give me this output 2019-10-17T07:55:00Z.
See this DB Fiddle

Desired Output :

User_id     in_time             out_time
71      17/10/2019 7:55     null
13      16/10/2019 18:44    17/10/2019 7:05
8       16/10/2019 19:47    17/10/2019 7:05
70      16/10/2019 18:25    17/10/2019 7:01
31      16/10/2019 19:02    17/10/2019 6:34
66      17/10/2019 6:19     null
64      16/10/2019 11:49    16/10/2019 22:05
36      16/10/2019 7:42     16/10/2019 19:03
34      16/10/2019 6:26     16/10/2019 19:03
79      16/10/2019 9:25     16/10/2019 19:02
76      16/10/2019 7:02     16/10/2019 19:02
13      16/10/2019 7:05     16/10/2019 18:44
71      16/10/2019 7:02     null

For dayshift use

SELECT DISTINCT
       user_id,
       DATE(verify_date) the_date,
       MIN(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) min_datetime,
       MAX(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) max_datetime
FROM tbl_attendance
ORDER BY user_id, the_date;

For nightshift use

WITH cte AS (SELECT user_id,
                    verify_date,
                    LEAD(verify_date) OVER (PARTITION BY user_id ORDER BY verify_date) lead_date
             FROM tbl_attendance)
SELECT *
FROM cte
WHERE DATE(verify_date) != DATE(lead_date)
ORDER BY user_id, verify_date;

fiddle


Parameterized query creating many plans I have some queries that are parameterized, but they are still creating a new execution plan each time. I am using SQL Server 2016.

Queries are like:

(@P1 varchar(1043),@P2 varchar(6))
UPDATE table
SET FILEDATA=@P1
WHERE FILEID=@P2

This query is not using the already generated execution plan from the cache, rather it is creating a new plan each time.

The length of @P1 is different in all of those.
You're getting different plans because you're not explicitly setting parameter lengths in your code.
Any difference in strings, no matter how minor, will generate a new plan. Data type sizes, spaces, comments, all of them will cause new entries in the plan cache. Here's a demo video illustrating the problem.
The solution is to declare your variable size as the same length as the data type of the column it's being inserted into. This will give you predictable plan reuse.
The way you do this will vary based on how the insert is being performed. If you're using stored procedures, you can update the parameters to use the same length as the columns. If you're using .NET's AddWithValue() method (which is evil), use one of the APIs that allows you to set the parameter length.


Sum all unique values from previous dates Let's say, for simplicity sake, I have the following table:
id      amount     p_id     date
------------------------------------------------
1       5          1        2020-01-01T01:00:00
2       10         1        2020-01-01T01:10:00
3       15         2        2020-01-01T01:20:00
4       10         3        2020-01-01T03:30:00
5       10         4        2020-01-01T03:50:00
6       20         1        2020-01-01T03:40:00

Here's a sample response I want:
{
 "2020-01-01T01:00:00": 25, -- this is from adding records with ids: 2 and 3
 "2020-01-01T03:00:00": 55  -- this is from adding records with ids: 3,4,5 and 6
}

I want to get the total (sum(amount)) of all unique p_id's grouped by the hour.
The row chosen per p_id is the one with the latest date. So for example, the first value in the response above doesn't include id 1 because the record with id 2 has the same p_id and the date on that row is later.
The one tricky thing is I want to include the summation of all the amount per p_id if their date is before the hour presented. So for example, in the second value of the response (with key "2020-01-01T03:00:00"), even though id 3 has a timestamp in a different hour, it's the latest for that p_id 2 and therefore gets included in the sum for "2020-01-01T03:00:00". But the row with id 6 overrides id 2 with the same p_id 1.
In other words: always take the latest amount for each p_id so far, and compute the sum for every distinct hour found in the table.

Look for
WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY date DESC) rn
              FROM source_table
              WHERE date_trunc('hour', date) = @needed_datetime_truncated_to_hour )
SELECT @needed_datetime_truncated_to_hour, SUM(amount)
FROM cte
WHERE rn = 1;


How are deadlocks detected and reported in an RDBMS? I was given this essay type question during an interview but did not get the job. The full question was as follows:

How are deadlocks detected and reported in an RDBMS? What are the transaction owner and application developer responsible for ensuring in both detection and prevention scenarios?

In SQL Server there is a separate thread that periodically (default 5 seconds, lower interval if a deadlock has just been detected) checks a list of waits for any cycles. I.e. it identifies the resource a thread is waiting for, then it finds the owner of that resource and recursively finds which resource that thread is in turn waiting for, thereby identifying threads that are waiting for each others resources.

If a deadlock is found then a victim is chosen to be killed using this algorithm:

Identify threads that are not unkillable (e.g. a thread that is rolling back a transaction is unkillable).
Find the thread with the lowest deadlock priority.
Chose the one that is cheapest to roll back, i.e. the one that has done the least work so far.

You can find more information about SQL Servers deadlock detection here:
http://msdn.microsoft.com/en-us/library/ms178104.aspx

The transaction owner/application developer is responsible for minimizing the risks of deadlocks occurring, and to do that they should:

Make sure to keep transactions as short as possible. E.g. don't show a login form after starting a transaction and wait for user input, instead gather all the info you need and then run the transaction.
Use the lowest possible isolation level, e.g. don't set serializable when you just want to temporarily show some values to the user. Please note that setting correct isolation level is a science in itself and out of scope in this answer.
If you are the victim of a deadlock, i.e. you get error# 1205, then re-run your transaction transparently to your user. Since the other, competing, transaction has now hopefully acquired the resources it was waiting for and finished, it is unlikey that you will encounter the same deadlock again.


How to restore .bak only if it's good I'm using Tsql script to restore several databases.

RESTORE DATABASE XXX_M
   FROM DISK = 'C:\XXX.bak'
   WITH FILE = 1, replace
GO

Sometimes the .bak file comes corrupted (not data corrupted ).

I want to know if there is a way to check if the file is not corrupted then restore else don't.
I've tried to use @@rowcount after restore VERIFYONLY but it's not good.

RESTORE VERIFYONLY
   FROM DISK = 'C:\xxx.bak'
if @@ROWCOUNT !=0
print 'false'
else
print 'good'

Any other ideas?

Thanks.

Checking @@ROWCOUNT isn't valuable here because RESTORE VERIFONLY doesn't return any results. It either prints status messages or error messages. So, a simple TRY/CATCH should do:

BEGIN TRY
  RESTORE VERIFYONLY ...
  RESTORE DATABASE   ...
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
END CATCH

If the VERIFYONLY fails, it will raise an exception, and skip the RESTORE.

Of course, there are still going to be scenarios where VERIFYONLY will succeed but the RESTORE will fail (for example, something swoops in and takes all your disk space).


Postgres ts_headline inconsistent behaviour I have just stumbled over a weird behaviour in postgres ts_headline function (used for highlighting fulltext search results).
First I thought the simple dictionary does not play with highlighting as the below example shows (there should be <b> tags):

# SELECT ts_headline('simple', 'This is artificial text', to_tsquery('artificial'));
       ts_headline
-------------------------
 This is artificial text
(1 row)

But a different word works fine...

# SELECT ts_headline('simple', 'some Word in', to_tsquery('Word'));
 ts_headline
---------------------
 some <b>Word</b> in
(1 row)

Does anybody have an explanation for this behaviour?

I noticed my mistake.
The to_tsquery function inside also needs to know which dictionary to use (aka use the simple one). By default it uses english stemming which produces this result:

SELECT to_tsquery('artificial');
 to_tsquery
------------
 'artifici'

which of course can not be found in the text converted by simple dictionary.
So the correct query would have been:

# SELECT ts_headline('simple', 'This is artificial text', to_tsquery('simple', 'artificial'));
          ts_headline
--------------------------------
 This is <b>artificial</b> text
(1 row)


Filling in missing dates in record set from generate_series() Consider:

with days as (select day::date
from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day
)
select 'Inspections'::text as data_label,
count(i.reporting_id) as daily_count, d.day as date_column
from days d
left join inspection i on i.close_case_date = d.day
group by d.day
order by d.day

This returns a set that looks like this:

data_label | daily_count | date_column
Inspections    1           01/01/13
Inspections    2           01/02/13
Inspections    4           01/04/13
Inspections    8           01/06/13

Note the 1 and 2 day gaps in the record set. I need to generate a set with those values filled in with 0 like so:

data_label | daily_count | date_column
Inspections    1           01/01/13
Inspections    2           01/02/13
Inspections    0           01/03/13
Inspections    4           01/04/13
Inspections    0           01/05/13
Inspections    8           01/06/13

How would I do this? I am not much of a Database Developer and am new to Postgres, so I am not sure where to start and can not find anything on how to accomplish this objective.

This is some kind of misunderstanding. The query in your question already returns what you are asking for. I only changed minor details:
SELECT text 'Inspections'       AS data_label
     , count(i.close_case_date) AS daily_count
     , d.day                    AS date_column
FROM  (
   SELECT generate_series(timestamp '2013-01-01'
                        , timestamp '2013-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
LEFT   JOIN inspection i ON i.close_case_date = d.day
GROUP  BY d.day
ORDER  BY d.day;

About generating a series of dates:

Generating time series between two dates in PostgreSQL

Minor points

date '2013-01-01' + interval '1 year - 1 day' is better than date '2013-01-01' + 365 to also cover leap years.

Using a cheaper subquery. No need for a CTE.

Why count(i.reporting_id)? To just count rows in i, use count(i.close_case_date), which we already join to, so it cannot be NULL. count() only counts non-null values. If reporting_id can be NULL, you run the risk of not counting those rows.

Alternatively, move the cast, so we can use the set-returning function as table expression directly:
SELECT text 'Inspections'       AS data_label
     , count(i.close_case_date) AS daily_count
     , d.day::date              AS date_column
FROM   generate_series(timestamp '2013-01-01'
                     , timestamp '2013-01-01' + interval '1 year - 1 day'
                     , interval  '1 day') AS d(day)
LEFT   JOIN inspection i ON i.close_case_date = d.day::date
GROUP  BY d.day
ORDER  BY d.day;


SQL Server generate self-signed SSL certs performance concern? Read this msdn article and doesn't seem to say if it keeps using the same self-signed cert after one is automatically generated.

If SQL Server generates a new SSL cert upon every connection (anyone confirm this?) or every time someone logs in, then does this cause any noticeable performance issue?

The self-signed certificate is NOT generated upon every connection/login. So no performance hit here.

Source: http://technet.microsoft.com/en-us/library/ms189067%28v=sql.105%29.aspx


Are there any schema design changes that can take a design from 4NF into a different 4NF design? My thought/contention is that once a database schema is in 4NF it by definition can only be changed into something less than 4NF (Denormalisation, 3NF etc) and that there is no alternative 4NF design for it to take.

That is, once a design is in 4NF there is no design choice/change that will take it to some other different 4NF design.

Is this correct (or are there any cases where 2 different 4NF designs are put up for comparison or converted between)?

I dont think so except for the fact that there is a  5NF, which describes a design where your joins are only on the candidate keys.

Many "4NF" designs meet this criteria, but not all, and it is definitely something you can change a 4NF "into" to be be more normalized.


Find jobs and dependencies run between two timestamps I would like to know what jobs ran and dependencies (what triggered them) between two timestamps or dates.

I have been looking in all the oracle views. I cannot find anything like this in the dba_scheduler_jobs view.

There is no guaranteed method for this. The closest you can get to this is: DBA_SCHEDULER_JOB_RUN_DETAILS, but:

logging for scheduler jobs can be disabled, so it does not necessarily contain all jobs runs
there is no information about what started the job

Also, there is no history for DBMS_JOB jobs.


Transfer Oracle 10g Database From Linux to Windows Can anyone tell me how to restore Oracle 10g Database backup created in Linux to the Oracle 10g version for windows...

Restore should not be an issue. Both Linux and Windows are little endian so you are OK there. Are you using rman? You can always recreate the database on Windows and perform an export/import to the new database.
Ideally you want to just restore using RMAN directly to the Windows server.
Check out Chapter 4 of Oracle® Database Backup and Recovery Advanced User's Guide or even go with a duplicate or standby database depending on your needs, downtime time frame and licensing.
Please provide more information about your situation with respect to your backup type and tools.


Unable to save and error on DBCC CHECKDB I try to save my database ≈ 260 Mb

I have this error :

  System.Data.SqlClient.SqlError : failure read function incorrect

So I try :
DBCC CHECKDB ('myDatabase') PHYSICAL_ONLY

I have this error :

  Msg 8921, Level 16, State 1, Line 1 Stop control. An error occurred
  while collecting the facts. Insufficient space in tempdb or
  inconsistency of a system table. Check out the previous errors.

I have same error without PHYSICAL_ONLY but the rest seems normal

DBCC CHECKDB ('myDatabase') WITH ESTIMATEONLY :

  9357520 Kb => 9 357,52 Mb

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

  free space in MB : 30 389 Mb

DBCC SQLPERF(LOGSPACE)

  tempbd :
  - Log size : 13 299 Mb
  - Space Used (%) : 0.01%

I do not know why this does not work

Edit : When I try xcopy (database offline) I get this error :

  target volume does not support symbolic links

is it a physical or a virtual server? Please make a chkdsk on your filesystem where the database are stored.

Please also try a BACKUP DATABASE with CONTINUE_AFTER_ERROR:

BACKUP DATABASE AdventureWorks2012
 TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
   WITH CHECKSUM, CONTINUE_AFTER_ERROR;
GO

More information about this here:

https://docs.microsoft.com/de-de/sql/relational-databases/backup-restore/specify-if-backup-or-restore-continues-or-stops-after-error

Regards,
Björn


Error when dropping InnoDB database I am trying to drop a database in mysql, but every time I get this error

ERROR 2013 (HY000): Lost connection to MySQL server during query

even logged in as root.

Originally, I had a corrupted table that I could not recreate, hence I decided to recreate the db. I have innodb file per table, not sure if that matters.

How can I drop this database?

I am afraid you cannot drop the database.

The problem comes the table that is corrupt. It's not necessarily the .ibd file. It is, more than likely, the data dictionary entry within ibdata1 (See pictorial representation of ibdata1).

I wrote about this back on Mar 02, 2012 : InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)

Although tedious, here is what you can do to save the other tables in that corrupt database bad_db:

STEP 01) USE bad_db

STEP 02) Run SELECT COUNT(1) FROM tablename; for every table in bad_db. Collect all table names that do not crash mysqld when you attempt to run the count.

STEP 03) Run mysqldump -u... -p... bad_db table1 table2 ... tableN > bad_db_goodtables.sql
for all the tables that did not crash mysqld.

If you are going to use a different database name, then reload bad_db_goodtables.sql into that new database.

If you need the database to be the same name, BAD NEWS !!! You will have overhaul ibdata1. There are more steps:

STEP 04) mysqldump all the other databases into separate dump files

STEP 05) Perform InnoDB Infrastructure Cleanup (See my Oct 29, 2010 StackOverflow Post)

STEP 06) Reload all the other mysqldumps good databases.

Give it a Try !!!


Postgresql permissions on schemas to groups I want to have several users able to create schemas and tables. This schemas (and tables) should be available to all. I would like to setup the DB, with all permissions in place (and future permissions using ALTER DEFAULT PRIVILEGES). Users shouldn't have to change any permissions afterwards.

The schemas are created using a client software, QGIS in this case. So, users are only able to issue CREATE SCHEMA newschemaname.

The problem is, even if the users belong to the same group, they are not able to see each other schemas.

Unable to access other's schemas

I'm using two roles for testing. Users admin1 and admin2, both belonging to grp_admin:

CREATE ROLE grp_admin NOLOGIN;
-- one user should be able to create db and extensions; these permissions are not inherited
CREATE ROLE admin1 WITH PASSWORD 'test2k19' LOGIN inherit SUPERUSER CREATEDB CREATEROLE;
GRANT grp_admin TO admin1;
CREATE ROLE admin2 WITH PASSWORD 'test2k19' LOGIN inherit;
GRANT grp_admin TO admin2;

As admin1, I created a new database, and granted some permissions (and also permission for future objects):

CREATE DATABASE people;
\c people

-- To create schemas and tables
GRANT ALL PRIVILEGES ON DATABASE people TO grp_admin;

ALTER DEFAULT PRIVILEGES GRANT select, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO grp_admin;
ALTER DEFAULT PRIVILEGES GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO grp_admin;
ALTER DEFAULT PRIVILEGES GRANT EXECUTE ON FUNCTIONS TO grp_admin;

If, as user admin1 I create a new schema and table, the other admin2 has no access to it:

create schema archive;
create table archive.vip ( thename varchar);
insert into archive.vip values ('Fernão de Magalhães');

As admin2:

select * from archive.vip;
ERROR:  42501: permission denied for schema archive
LINE 1: select * from archive.vip;

Able to access other's schemas

If I use authorization grp_admin when create the schema, it works. As user admin1:

create schema future authorization grp_admin;
create table future.vip ( thename varchar);
insert into future.vip values ('Robot 5126');

As user admin2:

select * from future.vip;
  thename
------------
 Robot 5126

The problem

Since users are creating schemas with  QGIS (a client application), issuing just CREATE SCHEMA newschemaname, users are not able to share each other schemas and tables.

How can I have this second admin2 accessing the admin1 created schemas? (Without issuing GRANT permissions after each schema created).

You forgot to set default privileges for schemas:

ALTER DEFAULT PRIVILEGES GRANT CREATE, USAGE ON SCHEMAS TO grp_admin;


trying to get more rows from the table than there actually exist I was not sure about a descriptive Title of this question so I put there what i thought to be the best sentence.

I have a table like this:

StudentID, Month, Year, Present
-------------------------------
1, 1, 2016, P
1, 12, 2016, P
1, 4, 2017, P

I want to write SQL statement, without using cursor, to return a resultset like the below one:

Required output:

1, 1, 2016, P -- <-- it is 'P' in the table
1, 2, 2016, A
1, 3, 2016, A
1, 4, 2016, A
1, 5, 2016, A
1, 6, 2016, A
1, 7, 2016, A
1, 8, 2016, A
1, 9, 2016, A
1, 10, 2016, A
1, 11, 2016, A
1, 12, 2016, P -- <-- it is 'P' in the table
1, 1, 2017, A
1, 2, 2017, A
1, 3, 2017, A
1, 4, 2017, P -- <-- it is 'P' in the table
1, 5, 2017, A
1, 6, 2017, A
1, 7, 2017, A
1, 8, 2017, A
1, 9, 2017, A
1, 10, 2017, A
1, 11, 2017, A
1, 12, 2017, A

is this possible through one SQL statement, without using cursors.

I am working on SQL Server 2000 database.

declare @t table(StudentID int, Month int, Year int, Present char(1));
    insert into @t
    select 1, 1, 2016, 'P' union all
    select 1, 12, 2016, 'P' union all
    select 1, 4, 2017, 'P';

    select y.StudentID,
           y.y, m.m,
           isnull(t.Present, 'A') as P
    from
    (
    select StudentID, year as y
    from @t
    group by StudentID, year
    ) y
    cross join

    (
    select number as m
    from master..spt_values
    where type = 'p' and number between 1 and 12
    )m
    left join @t t
       on t.StudentID = y.StudentID and t.year = y.y  and t.month = m.m;

Explanation: we need as much rows as the number of students per number of years per 12 month, so I use cross join of distinct pairs student, year and 12 months.

As we have SQL Server 2000, we cannot use Row_number() to generate 12 values, niether VALUES, so we can join to a number table (I used spt_values even if I personally have a number table until 10000000) or we could generate in other ways, for example like this :

select 1 as m union all select 2...union all select 12

And finally join the presence list to a generated 'calendar' for every student


Google Cloud Spanner UNIQUE Index Consistency As far as I understand Google Cloud Spanner is globally consistent and also fast.

Let's say I have a table with a unique username field.

User A in USA inserts user johndoe to a US Spanner node
User B in China inserts user johndoe to a Chinese Spanner node

Does one of them fail?
If not, how fast are the queries? Or do they both succeed but later on decide that one of them is a duplicate? Or do writes with UNIQUE fields go to 1 server?

Thanks!

First, there currently isn't an option to run Cloud Spanner across continents, even though Google's internal Spanner can. The answer however is still the same, just latencies on writes, etc are different in a Regional (Multi-zone) and Global (Multi-Continent) configuration.

The trick here is that Spanner does not insert into "the node", but instead tries to reach consensus across a majority of nodes. Since we always run an odd number of nodes, one of them will reach consensus first and block the other one. This transaction will be committed, then the other transaction will fail as the row already exists.

Eric Brewer gives a good overview of how consistency is achieved in this situations in this recent article: Spanner, TrueTime &
The CAP Theorem


Are there ways to only replace SQL Server stored procedures when the definition has changed? I know the following two patterns to write scripts for creating stored procedures, which can be executed repeatedly without throwing errors.
if object_id('my_proc') > 0 drop procedure my_proc
go
create procedure dbo.my_proc as Print 'This is not a dummy';

and the other which preserves permissions
if object_id('my_proc') is null
    EXEC ('create procedure dbo.my_proc as Print ''This is a dummy''');
go
ALTER PROCEDURE dbo.my_proc as Print 'This is not a dummy';

I guess when the procedure exists and its hash code is the same as that of the new version, than there would be no need to drop and recreate or alter the procedure.
My problem is that HashBytes is limited to a maximum of 8000 bytes and I can't use it generally like
if object_id('my_proc') is null
    EXEC ('create procedure dbo.my_proc as Print ''This is a dummy''');
go
if object_Id('my_proc') > 0
and
(
Select HashBytes('MD5',definition) MD5
from sys.sql_modules m
join sysobjects o on o.id = m.object_id
where o.name = 'my_proc'
) <> 0x9028A1B9D93AC7592EC939CCABF9D3DE
begin
    print 'definition has changed';
    EXEC ('ALTER PROCEDURE dbo.my_proc as Print ''This is not a dummy''');
end

For procedures whose definition are longer than 4000 Characters. Any proposals to handle these cases in a similar way?
Edit:
It is not only that I want to avoid flushing of cached plans.
I also have to cope with different customers having different versions of a stored procedure where I only want to replace one of these variants by a newer version.

Permissions shouldn't be assigned to the stored procedure

Your stored procedure should sit in a schema, permissions are on the schema (granted to roles). This way, new objects in that schema have permissions automatically. No single object should have permissions

So either way would be OK.

See these other DBA questions:

Set permissions on all objects for SQL users
What standard should I follow when naming tables and views?
Schema design - best practices?

FYI, object_id is signed int so use "does not equal"

if object_id('my_proc') <> 0 drop procedure my_proc

Edit, nvarchar(max) is comparable directly

if object_Id('my_proc') <> 0
    drop proc ThenewProc
GO
CREATE PROC ThenewProc
...
GO

IF object_definition(object_id('my_proc')) <>
         object_definition(object_id('ThenewProc')) <> 0
BEGIN
    drop procedure my_proc
    drop proc ThenewProc
END
GO
CREATE PROC my_proc
...
GO


Oracle JSON ADT in Collection Why is the table() operator not working?

create or replace type tmp_011040_t2 as
  table of (JSON_OBJECT_T) NOT PERSISTABLE;

declare
  l_objects tmp_011040_t2;
begin
  l_objects := tmp_011040_t2();
  l_objects.extend(1);
  l_objects(l_objects.last) := JSON_OBJECT_T.parse('{"a":"1","b":"1"}');
  l_objects.extend(1);
  l_objects(l_objects.last) := JSON_OBJECT_T.parse('{"a":"2","b":"2"}');
  for i in (select * from table(l_objects) ) loop -- ERROR!!!
      null;
  end loop;
end;

-- ORA-40573: Invalid use of PL/SQL JSON object type.

$ oerr ora 40573
40573, 00000, "Invalid use of PL/SQL JSON object type."
// *Cause:  An attempt was made to use a PL/SQL JavaScript Object Notation
//          (JSON) object type in an invalid context.
// *Action: Use PL/SQL JSON object types only inside PL/SQL.

Example:

set serveroutput on
declare
  l_objects tmp_011040_t2;
  l_index pls_integer;
begin
  l_objects := tmp_011040_t2();
  l_objects.extend(1);
  l_objects(l_objects.last) := JSON_OBJECT_T.parse('{"a":"1","b":"1"}');
  l_objects.extend(1);
  l_objects(l_objects.last) := JSON_OBJECT_T.parse('{"a":"2","b":"2"}');
  l_index := l_objects.first;
  while (l_index is not null)
  loop
    dbms_output.put_line(l_objects(l_index).get_string('a'));
    l_index := l_objects.next(l_index);
  end loop;
end;
/

1
2

PL/SQL procedure successfully completed.


How to recover the innodb when mysql data dir moved from one system to another system? I have transfer the mysql data dir from one linux box to another box. When i start the mysql server. I can find that the innodb based DB are corrupted. So, may i know how to recover the corrupted innodb

There are basically two ways to move mysql data from one DB server to another

OPTION 1 : Use mysqldump

mysqldump produces a logical representation of the mysql data. After dumping data as follows

mysqldump -uroot -ppassword --all-databases --routines --triggers --flush-privileges | gzip > /root/MySQLData.sql.gz

Next, scp the /root/MySQLData.sql.gz over to /root of the new DB server

Then, on the other DB, delete everything in /var/lib/mysql except /var/lib/mysql/mysql. In other words, make sure when you do ls -l /var/lib/mysql, you should only see the mysql subfolder in it.

Finally, load the data in the new DB Server

gzip -d < /root/MySQLData.sql.gz | mysql -uroot -ppassword -A

OPTION #2 : Copy /var/lib/mysql folder over to /var/lib/mysql on the new machine

This entails copying the physical representation of the data. You can tar gzip that folder and copy it as you have already done. If mysql cannot startup on the target server, check the COMMON NEEDS first, then run rm -f /var/lib/mysql/ib_logfile* and try restarting mysql on the target DB server again

COMMON NEEDS OF BOTH OPTIONS

Make sure of the following:

Copy the /etc/my.cnf of the source DB server over to the targat DB server
The same version of MySQL is on both servers
There is enough space on the mount point of /var/lib/mysql on the target DB server
Run chown -R mysql:mysql /var/lib/mysql on the target DB server

CAVEAT

If the DB on the source DB server has only .frm files, ib_logfile0, ib_logfile1, and ibdata1, chances are you have innodb_file_per_table disabled. It is very important that you have the exact same innodb setting in /etc/my.cnf on the target DB server as you had on the source.

Hope these help !!!


How to serialize on SQL server metadata? I tried to find an answer in Kalen Delaney's SQL Server Concurrency but there is no mention of metadata.

The problem is how to ensure that only one of these reads and index creates executes from one connection before the other connection does the same .

if not exists (select * from sysindexes with (XLOCK) where name = 'IND_audittrail_date_time')
CREATE CLUSTERED INDEX IND_audittrail_date_time ON audittrail(date_time)

-- because we get

  java.sql.SQLException: The operation failed because an index or
  statistics with name 'IND_audittrail_date_time' already exists on
  table 'audittrail'.

Query:

if not exists (select * from sysindexes with (XLOCK) where name = 'IND_audittrail_date_time')

Reading Metadata Access, Isolation Levels, and Lock Hints
I found this

  SQL Server does not guarantee that lock hints will be honored in
  queries that access metadata through catalog views, compatibility
  views, information schema views, metadata-emitting built-in functions

Is there an accepted way how this is done? Thank you.

In this case, if you cant serialize this in the application code you can use try/catch or an applock

begin tran
if APPLOCK_TEST ('public','idx_create','exclusive') = 1
 begin
  EXEC sp_getapplock @Resource = 'idx_create',
                     @lockmode = 'Exclusive',
                     @DbPrincipal='public'
   print 'I can do it'

  -- EXEC sp_releaseapplock @Resource='idx_create', @DbPrincipal='public'
  -- scope is transaction see comment below
   commit
 end
else rollback ;