Table creation and sample data script:

CREATE TABLE #Slot
(
    [Day]   tinyint NOT NULL,
    [Time]  time(0) NOT NULL,
    Venue   char(4) NOT NULL,
    Free    smallint NOT NULL CHECK (Free > 0),

    PRIMARY KEY ([Day], [Time], Venue)
);

INSERT #Slot
    ([Day], [Time], Venue, Free)
VALUES
    (1, '08:30', 'RM 1', 10),
    (1, '08:30', 'RM 2', 10),
    (1, '10:30', 'RM 1', 20),
    (1, '10:30', 'RM 2', 20),
    (2, '08:30', 'RM 1', 10),
    (2, '08:30', 'RM 2', 10),
    (2, '10:30', 'RM 1', 30),
    (2, '10:30', 'RM 2', 10);

CREATE TABLE #Booking
(
    [Day]   tinyint NOT NULL,
    [Time]  time(0) NOT NULL,
    Venue   char(4) NOT NULL,
    [User]  varchar(10) NOT NULL
);

INSERT #Booking
    ([Day], [Time], Venue, [User])
VALUES
    (1, '08:30', 'RM 1', 'Jill'),
    (1, '08:30', 'RM 2', 'Jill'),
    (1, '08:30', 'RM 1', 'Jack'),
    (1, '08:30', 'RM 1', 'Mary'),
    (1, '08:30', 'RM 2', 'Mary'),
    (1, '08:30', 'RM 2', 'Jill'),
    (2, '10:30', 'RM 1', 'Ken'),
    (2, '10:30', 'RM 1', 'Ken');

Query:

SELECT
    s.[Day],
    s.[Time],
    s.Venue,
    s.Free,
    Used =
    (
        SELECT COUNT_BIG(*)
        FROM #Booking AS b
        WHERE
            b.[Day] = s.[Day]
            AND b.[Time] = s.[Time]
            AND b.Venue = s.Venue
    )
FROM #Slot AS s
ORDER BY
    s.[Day],
    s.[Time],
    s.Venue;

Output:

╔═════╦══════════╦═══════╦══════╦══════╗
║ Day ║   Time   ║ Venue ║ Free ║ Used ║
╠═════╬══════════╬═══════╬══════╬══════╣
║   1 ║ 08:30:00 ║ RM 1  ║   10 ║    3 ║
║   1 ║ 08:30:00 ║ RM 2  ║   10 ║    3 ║
║   1 ║ 10:30:00 ║ RM 1  ║   20 ║    0 ║
║   1 ║ 10:30:00 ║ RM 2  ║   20 ║    0 ║
║   2 ║ 08:30:00 ║ RM 1  ║   10 ║    0 ║
║   2 ║ 08:30:00 ║ RM 2  ║   10 ║    0 ║
║   2 ║ 10:30:00 ║ RM 1  ║   30 ║    2 ║
║   2 ║ 10:30:00 ║ RM 2  ║   10 ║    0 ║
╚═════╩══════════╩═══════╩══════╩══════╝


Cascading PostgreSQL triggers between three tables I have a ‘test’ PostgreSQL 12 schema, defined with initial tables:
begin;
drop schema if exists test cascade;
create schema test;

set search_path = test,public;
create table jobs (
  job_id serial primary key,
  job_type_lu text);

create table phases (
  phase_id serial primary key,
  job_id_fk int references jobs(job_id),
  phase_type_lu text);

create table phase_steps (
  phase_step_id serial primary key,
  phase_id_fk int references phases(phase_id),
  step_type_lu text);

commit;

‘jobs’ are composed of one or more ‘phases’, which in turn have multiple ‘phase_steps’
What I want to accomplish is to fire two triggers when a new job is inserted into the ‘jobs’ table. I have these two triggers defined:
create or replace function test.setup_phases () returns trigger as $$
    begin
        insert into test.phase_steps (job_id_fk,phase_type_lu)
            values
                (new.jobs_id,'RESEARCH: JOB_INPUT'),
                (new.jobs_id,'RESEARCH: WATER'),
                (new.jobs_id,'RESEARCH: OTHER'),
                (new.jobs_id,'RESEARCH: REVISION_ATEAM'),
                (new.jobs_id,'RESEARCH: REVISION_CLIENT');
                return new;
end;
$$ language plpgsql;

create trigger setup_test_phases after insert on test.jobs
for each row execute procedure test.setup_phases();

create or replace function test.setup_phase_steps() returns trigger as $$
    begin
        if (new.phase_type_lu = 'RESEARCH')
        then
            insert into test.phase_steps (phase_id_fk,step_type_lu)
                values
                    (new.phase_id,'RESEARCH: JOB_INPUT'),
                    (new.phase_id,'RESEARCH: WATER'),
                    (new.phase_id,'RESEARCH: OTHER'),
                    (new.phase_id,'RESEARCH: REVISION_ATEAM'),
                    (new.phase_id,'RESEARCH: REVISION_CLIENT');
                    return new;
        end if;

        if (new.phase_type_lu = 'SURVEY')
        then
            insert into test.phase_steps (phase_id_fk,step_type_lu)
                values
                    (new.phase_id,'SURVEY: SCHEDULING'),
                    (new.phase_id,'SURVEY: FIELDING'),
                    (new.phase_id,'SURVEY: REVISION_ATEAM'),
                    (new.phase_id,'SURVEY: REVISION_CLIENT');
                    return new;
        end if;
end;
$$ language plpgsql;

create trigger setup_test_phase_steps after insert on test.phases
for each row execute procedure test.setup_phase_steps();

If I input data directly for just the phases (therefore invoking only the ‘setup_test_phase_steps’ trigger, e.g.,
insert into test.phases (phase_type_lu)
    values
        ('RESEARCH'),
        ('SURVEY');

The correct results are obtained:

However, if I enter input directly into the ‘jobs’ table (which then will invoke the ‘setup_test_phases’ AND the ‘setup_test_phase_steps’ triggers):
insert into test.jobs (job_type_lu)
values
('NT-TYP');
I get this error:

I’m obviously misunderstanding cascading triggers, but what?

To resolve your problem, I did the following (all of the code below can be found on the fiddle here):
I changed the formatting somewhat - it helps me think - I try to base my SQL coding standards on this, but that's, as Fr. Jack would say, an ecumenical matter (if you're not a Fr. Ted fan, don't worry...).
The crux of your problem is that you have a cascading chain of FOREIGN KEYs from job to phase to phase_step - which is good - helps keep track of what work goes with what contract &c.
The problem is with your code here:
create or replace function test.setup_phases () returns trigger as $$
    begin
        insert into test.phase_steps (job_id_fk,phase_type_lu)
            values
                (new.jobs_id,'RESEARCH: JOB_INPUT'),

You have setup_phases, BUT underneath that, you have insert into test.phase_steps (job_id_fk,phase_type_lu) - you "skip" the INSERT INTO phases step - so your chain of FOREIGN KEYs is broken.
Now, I imagine that your work flow is something like this:
You receive a job - the first phase of which is normally RESEARCH, so I created the phase table as follows (job remains unchanged):
CREATE TABLE phase
(
  phase_id      SERIAL PRIMARY KEY,
  job_id_fk     INTEGER references job (job_id),
  phase_type_lu TEXT DEFAULT 'RESEARCH',

  CONSTRAINT phase_type_ck CHECK (phase_type_lu IN ('RESEARCH', 'SURVEY'))
);

So, then I did this:
CREATE OR REPLACE FUNCTION setup_phase_bis()
RETURNS TRIGGER AS
$$
BEGIN
  INSERT INTO phase (job_id_fk) VALUES
    (NEW.job_id);
    RETURN NEW;
END;
$$ LANGUAGE PLpgSQL;

and:
CREATE TRIGGER setup_phase AFTER INSERT ON job
FOR EACH ROW EXECUTE PROCEDURE setup_phase_bis();

So, now, when I create a job, it sets up a RESEARCH phase. You missed this in your code. What happens now when I INSERT a job is the following:
INSERT INTO job (job_type_lu) VALUES ('NT-TYP');

and:
SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;

Result:
job_id  job_type_lu
1   NT-TYP
phase_id    job_id_fk   phase_type_lu
1   1   RESEARCH
phase_step_id   phase_id_fk step_type_lu

Note: no phase steps because I haven't done that yet.
So then I copied your code - with the notable exception (pardon the pun...) of the lines (see fiddle):
  IF NEW.job_id_fk IS NULL THEN
    RAISE EXCEPTION 'You must supply a job_id for this phase...';
  END IF;

Not having this provokes the error message you are receiving...
So, now, when a job is inserted, the job cascades to (RESEARCH) phase and the phase cascades to the appropriate steps - as follows:
INSERT INTO job (job_type_lu) VALUES ('XXXXX');

And:
SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;

Result:
job_id  job_type_lu
1   NT-TYP
2   XXXXX
phase_id    job_id_fk   phase_type_lu
1   1   RESEARCH
2   2   RESEARCH
phase_step_id   phase_id_fk step_type_lu
1   2   RESEARCH: JOB_INPUT
2   2   RESEARCH: WATER
3   2   RESEARCH: OTHER
4   2   RESEARCH: REVISION_ATEAM
5   2   RESEARCH: REVISION_CLIENT

So, we have our cascade.
Now (again, making assumptions about your workflow) - if the client is happy with the research phase, they'll want to progress to the SURVEY phase and so you will wish to enter that into the system but not as a new job (hope I'm OK so far... :-) )
So, now we do this:
INSERT INTO phase (phase_type_lu) VALUES ('SURVEY');

BUT, there's no associated job_id, so, thanks to the EXCEPTION, this fails as follows:
ERROR:  You must supply a job_id for this phase...
CONTEXT:  PL/pgSQL function setup_phase_steps() line 5 at RAISE

But, if we do this:
INSERT INTO phase (job_id_fk, phase_type_lu) VALUES (2, 'SURVEY');

we get:
SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;

Result:
job_id  job_type_lu
1   NT-TYP
2   XXXXX
phase_id    job_id_fk   phase_type_lu
1   1   RESEARCH
2   2   RESEARCH
4   2   SURVEY
phase_step_id   phase_id_fk step_type_lu
1   2   RESEARCH: JOB_INPUT
2   2   RESEARCH: WATER
3   2   RESEARCH: OTHER
4   2   RESEARCH: REVISION_ATEAM
5   2   RESEARCH: REVISION_CLIENT
6   4   SURVEY: SCHEDULING
7   4   SURVEY: FIELDING
8   4   SURVEY: REVISION_ATEAM
9   4   SURVEY: REVISION_CLIENT

Our SURVEY phase steps have been propagated with the correct FOREIGN KEYs. So, all is rosy in the garden? I ran a final test at the bottom of the fiddle (not shown for brevity), and all appears OK!
For future reference, it would be great if you could provide appropriate fiddles with your questions and also if you could give us the version of PostgreSQL that you are running - help us to help you!
p.s. welcome to the forum!


How to structure Tables with changing status without duplicating data I have a table called Inquiry
it will contain many - Services, Commodities & Invoices.

Then later, You can change it to an Appointment which will contain the same Services, Commodities & Invoices of its Inquiry parent.

The problem is how do i model the relationship without duplicating data for Services, Commodities & Invoices that is already present in Inquiry table?

NOTE: Appointment could be created without creating an Inquiry.

Below is what i currently have in mind but i think its not flawless.

Assuming that your Inquiry table has a ID column, you can add a column in the Appointment table which accepts NULL and is a foreign key to the Inquiry table. Like so:

CREATE TABLE Appointment
(
    Inquiry INT NULL REFERENCES Inquiry(Id)
);

This way you can lookup the additional details when you need to without duplicating data which is a key component of obtaining third-normal-form.

EDIT

Because an Appointment can be created without an Inquiry I would recommend abstracting the Services, Commodities & Invoices to a separate table which has its own key, which you can then reference from both the Inquiry and Appointment like so:

CREATE TABLE OrderDetails
(
    ID INT NOT NULL PRIMARY KEY,
    -- other columns
);
CREATE TABLE Inquiry
(
   ID INT NOT NULL PRIMARY KEY,
   OrderDetail INT NULL
       REFERENCES OrderDetails(Id)
);
CREATE TABLE Appointment
(
    ID INT NOT NULL PRIMARY KEY,
    Inquiry INT NULL REFERENCES Inquiry(Id),
    OrderDetail INT NOT NULL REFERENCES OrderDetails(Id)
);

EDIT based on Comment

As you want to ensure that the Appointment will always have the same OrderDetail as the Inquiry you could change your model to this:

CREATE TABLE Inquiry
(
    Id INT NOT NULL PRIMARY KEY,
    -- other columns
);
CREATE TABLE OrderDetails
(
    Id INT NOT NULL PRIMARY KEY,
    Inquiry INT NULL REFERENCES Inquiry(Id)
);
CREATE TABLE Appointment
(
    Id INT NOT NULL PRIMARY KEY,
    OrderDetail INT NOT NULL,
    FOREIGN KEY (OrderDetail)
      REFERENCES OrderDetails(Id)
);

Your other alternative is to implement the check within the logic layer that creates the Appointment and ensure that it only ever creates it using the same Order Detail that the Inquiry has, if the Appointment was based off an Inquiry.


Indexing a PK GUID in SQL Server 2012 My developers have setup their application to use GUID's as PK for pretty much all of their tables and by default SQL Server has setup the clustered index on these PK's.

The system is relatively young and our biggest tables are just over a million rows, but we're taking a look at our indexing and want to be able to scale quickly as it may be needed in the near future.

So, my first inclination was to move the clustered index to the created field which is a bigint representation of a DateTime. However, the only way I can make the CX unique would be to include the GUID column in this CX but order by created first.

Would this make the clustering key too wide and would it boost performance for writes? Reads are important too, but writes are probably a bigger concern at this point.

The primary problems with GUIDs, especially non-sequential ones, are:

Size of the key (16 bytes vs. 4 bytes for an INT):  This means you're storing 4 times the amount of data in your key along with that additional space for any indexes if this is your clustered index.
Index fragmentation: It is virtually impossible to keep a non-sequential GUID column defragmented because of the completely random nature of the key values.

So what does this mean to your situation?  It comes down to your design.  If your system is simply about writes and you have no concern about data retrieval, then the approach outlined by Thomas K is accurate.  However, you have to keep in mind that by pursuing this strategy, you're creating many potential issues for reading that data and storing it.  As Jon Seigel points out, you will also be occupying more space and essentially having memory bloat.

The primary question around GUIDs is how necessary they are.  Developers like them because they ensure global uniqueness, but it's a rare occasion that this kind of uniqueness is necessary.  But consider that if your maximum number of values is less than 2,147,483,647 (the maximum value of a 4 byte signed integer), then you're probably not using the appropriate data type for your key.  Even by using BIGINT (8 bytes), your max value is 9,223,372,036,854,775,807.  This is typically enough for any non-global database (and many global ones) if you need some auto-incrementing value for a unique key.

Finally, as far as using a heap versus a clustered index, if you are purely writing data a heap would be most efficient because you are minimizing overhead for inserts.  However, heaps in SQL Server are extremely inefficient for data retrieval.  My experience has been that a clustered index is always desirable if you have the opportunity to declare one.  I have seen the addition of a clustered index to a table (4 billion+ records) improve overall select performance by a factor of 6.

Additional information:

GUIDs as PRIMARY KEYs and/or the clustering key - Kimberly L. Tripp
Disk space is cheap... - Kimberly L. Tripp (hat tip Zane )
GUID vs INT Debate – Microsoft SQL Server Tips & Tricks


SQL Server 2008R2 Logon failed The SS error log indicates the following at a frequency of 2 to 3 times per hour:

  Logon failed for login 'NT AUTHORITY\NETWORK SERVICE' due to trigger execution. [CLIENT: ]

There is no logon trigger. How do I discover what trigger is causing the problem since the error log does not say?

(This is on a Win 7 Pro computer running SS 2008r2 developer.)

Have you considered using a trace to audit logins and/or triggers.

in case you find a trigger using this particular login the following post - Login failed due to trigger execution underlines the security context (see Microsoft answers) in which logon triggers are executed.

Hope this helps.


How To Add Days And Time For Instructor Schedule Problem

I need to make a database design (ERD) to display a schedule for every instructor, including the following:

day of course (e.g. Saturday or Monday)
time it will give course (period from 12 PM TO 03 PM)
what course it will give (C# or SQL)

Example

Instructor Martin gives course C#, Monday and Saturday, for period 12 pm - 03 pm, and course SQL on Sunday and Wednesday for period 12 pm - 03 pm.

Details

I designed the following tables:

Instructors table (InstructorID, InstructorName)
Courses table (CourseID, CourseName)
Instructors_courses table (instcourseID, InstructorID, CourseID)

Relationship between Instructors table and Courses table is many to many so I do another table Instructors_Courses.

Questions

How to represent days and times for every course added

Can I add table for time and table for days and make relation with Instructors_courses table by adding day id and time id (one to many)

OR do it programming from user interface.
course start date and course end date these two fields how to represent in table Instructors_courses.

I can added but it will repeated with every course are this correct or what.

Try making the instructor_course table like this

CREATE TABLE instructor_course (
    InstructorCourseID int IDENTITY (1,1) CONSTRAINT pk_instructor_course PRIMARY KEY CLUSTERED NOT NULL,
    DayName char(10) NOT NULL,
    CourseID int CONSTRAINT fk_CourseID_instructor_course REFERENCES Course(CourseID) NOT NULL,
    InstructorID ...
    StartTime time NOT NULL,
    EndTime time NOT NULL,
    StartDate date NOT NULL,
    EndDate date NULL);

Then you could also add some constraint to the table if you want to enforce a rule that two courses can't be timetabled at the same time. If a course has multiple sessions during the week each will be a row in this table.


Two different databases on the same RAC cluster We have an Oracle 11gR2 RAC cluster with 4 nodes.  2 nodes are dedicated to one database that's devoted one application and 2 nodes are dedicated to a completely separate database that serves a completely different application.  They got all put on the same cluster.  Why?  Nobody really knows anymore.

We're looking at changing hardware and OS's and such on two of the nodes for one of the applications.  Since they're in the same cluster, we're concerned about how to do this without effecting the other two nodes (or if it can even be done).  We're installing two much newer nodes on this database with much newer OSs and I'm not sure if it will even let us add it to the cluster with the newer OS.

As such, we'd like to split the cluster into two clusters?  Is there any easy way to do this?  They share one ASM instance, but all the files are obviously separate within that instance.

We just did the same thing.

install software in new machines and configure different cluster
copy tnsnames.ora, orapwSID files to new machines and prepare pfile
you may need to add SID to /etc/oratab file on new machines
shutdown database on old machines
dismount ASM diskgroups on old machines
present LUNs to new machines
mount diskgroups on new machines
start database on new machines
configure database, instances, services using srvctl on new machines

Now you can unconfigure and remove old machines from old cluster. Of course Oracle version on new machines has to be the same as on old machines. Or it is possible to do upgrade right away on new machines.

You should test procedure of course. There are quite a few possible problems on the way.

The idea is that Oracle DB does not store anything in "cluster". All the data is in datafiles, controlfiles, redo logs and spfile. Which is stored on ASM can can be mounted on another server.


Can not re-set [log_reuse_wait] after log back up I have a SQL Server 2005 database in full recovery mode with the following stats:

name XX
state    0
state_desc   ONLINE
size 2620152
max_size -1
growth   128

Log File

name XX_Log
state    0
state_desc   ONLINE
size 517832
max_size 268435456
growth   64000

Log file stats

Database Name    XX
Log Size (MB)    4045.555
Log Space Used (%)   0.7804911
Status   0

So I would like to reduce the size of the log - I think my target should be 50MB? with growth of 50MB - so I did a full back up - followed by log back up, twice. Without success

Having done some reading I check my  [log_reuse_wait]  and got a 2 result - back up log.
So first question why is that, when I have just done two backups?

I have looked for open transactions and got the message

  No active open transactions. DBCC execution completed. If DBCC
  printed error messages, contact your system administrator

And I have checked the Activity monitor and looked at Blocked Header and can not find at 1 in any session - I believe that indicates a long running transaction

Finally all I know is when I run DBCC LOGINFO I get a return of 390 lines which I believe indicates too high a VLF count?

I believe the count too high from the articles I have read (but maybe not fully understood) the articles are transaction-log-vlfs-too-many-or-too-few  and a-busy accidental-dbas-guide-to-managing-vlfs

I appreciate that I could take the database offline and deal with the problem as detail here and other places, but I think the log file will just grow again and this is our main production database so downtime is too be avoided if possible

What do people suggest as next steps please?

Thanks
Ian

You have a couple actual questions here, so let's address each one individually.

To answer the question of why the log_reuse_wait won't update, this field (along with other metadata within SQL Server) will only update after certain events occur.  With this field, it will update after a database checkpoint occurs.  To force this after taking a log backup, do the following:

BACKUP LOG [foo] TO DISK=N'X:\Backups\foo_20141008120000.trn';
CHECKPOINT;

The checkpoint will occur and the field in sys.databases will update.

For why your log file won't shrink, this is a MUCH larger question.  You should read this question/answer by Mike Walsh on why your log file is growing and how best to manage it.  I also provide some more detail in this answer.

Regarding VLFs, I would strongly recommend you not worry about that right now.  Yes, you can see a performance impact over having to many VLFs (and understanding what a VLF is and how it works is fundamental for understanding the log file), but managing that should only be addressed when you're regularly managing your log with backups and it's not growing out of control.

Finally, as for the size of your log, this is a very open ended question.  It very much depends on how much activity your database sees along with the scope of that activity.  Chances are that you will shrink your log file, only to see it grow again to accommodate your regular database activity.  Before considering shrinking your log file, you need to understand what's making it grow and whether or not you're running regular scheduled log backups against it.


Difference between the given queries I am required to retrieve the list of pno ( product_id ) for the products which were purchased by two customers. For this, I have been given two relations which are ( along with the attributes ) as follows.

odetails(ono,pno,qty)
orders(ono,cno,eno,received,shipped)

Here ono is the order_id, pno is the product_id, qty is the quantity of the product in stock, cno is the customer_id of the customer who is purchasing the product, eno is the employee_id who is selling the product, received is the date on which the customer received the product and shipped is the date when the product was shipped. ono is the primary key of orders and foreign key of odetails. ono, pno, qty form the primary key of odetails.

One query is:-

select distinct d.pno
from odetails d,orders o1,orders o1
where d.ono=o1.ono
  and d.ono=o2.ono
  and o1.cno<o2.cno;

Another query is:

select distinct d1.pno
from odetails d1,odetails d2,orders o1,orders o1
where d1.ono=d2.ono
  and d1.ono=o1.ono
  and d2.ono=o2.ono and o1.cno<o2.cno;

Could you please tell me what is the difference between these two queries? Both of them seem to work in the same way except the fact the second query seems to be doing extra and useless work of comparing two odetails when they are not even required to do so.

Consider the following odetails data:

ono   pno
---------
1     1
1     2

If you join it on itself by ono, like

SELECT pno
FROM odetails d1 JOIN odetails d2 ON d1.ono = d2.ono;

then you will get four rows instead of two.  So the two queries are different in their meaning even though they return the same data (thanks to the DISTINCT keyword).

(There is a possibility for similar problem with joining the orders table twice.  As you did not mention the exact requirements for this query, one can't be sure if even the first query is correct.  (Acknowledgment to ypercube for mentioning this.))


Show processlist / Max concurrent connections seem to max out at 131 When I put my database under a load test (basically just a basic write and basic read from our app), and run show processlist; in the middle of it, there is always 131 rows in the set and that's it. I'm running a master slave with 16GB of memory on each on a Joyent Percona instances which is based on Solaris 10. They are configured to each use up to 8GB memory and 5000 max concurrent connection. What could be limiting it to 131?

I once wrote about the kind of things to tweak when seeing output from mysqltuner.pl : Understanding mysqltuner Recomendations w/ Unused Catalogs

Here is the kind of output it generates

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.61
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 6M (Tables: 30)
[--] Data in InnoDB tables: 359M (Tables: 1206)
[!!] Total fragmented tables: 21

-------- Security Recommendations  -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@localhost' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9d 15h 35m 57s (30K q [0.036 qps], 9K conn, TX: 7M, RX: 1M)
[--] Reads / Writes: 50% / 50%
[--] Total buffers: 322.0M global + 5.4M per thread (15 max threads)
[OK] Maximum possible memory usage: 402.6M (6% of installed RAM)
[OK] Slow queries: 0% (0/30K)
[OK] Highest usage of available connections: 13% (2/15)
[OK] Key buffer size / total MyISAM indexes: 2.0M/921.0K
[OK] Key buffer hit rate: 100.0% (19K cached / 8 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 6 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 34% (10 open / 29 opened)
[OK] Open file limit used: 0% (19/2K)
[OK] Table locks acquired immediately: 99% (10K immediate / 10K locks)
[!!] InnoDB data size / buffer pool: 359.3M/256.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)
    innodb_buffer_pool_size (>= 359M)

From the output, you should keep watch on

Maximum possible memory usage: 402.6M (6% of installed RAM)
Total buffers: 322.0M global + 5.4M per thread (15 max threads)
Highest usage of available connections

If you see the per thread value, remember that is is made up of

join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd buffer_size

added together and then multiplied by the max_connections. That can have a bearing out how much memory can be consumed. Since you are hitting the 131 connection limit, perhaps you can multiple the per thread value by 131. Once you do, look back at how much RAM is consumed. Remember to reason on the fact that you are in a VM environment. From the OS point of view, you could be swapping or be the victim of process "steal" as seen in %st in top.

Keep in mind that RAM becomes a premium commodity in a VM, and MySQL will adhere to it without question.

I hope this wild brainstorming of mine helps ...


SQL log directory has too much dump file My SQL Server 2014 is automatically creating SQL dump file. It is generating automatically to take all the spaces and hangs over the database.

  Error: 8310, Severity: 16, State: 1.
  Cannot create (or open) named file mapping object 'Global\SQL_110_MEMOBJ_24_SQL14_0'. SQL Server performance counters are disabled.
  Error: 3409, Severity: 16, State: 1.
  Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.
  Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)

With Reference sql server 2008 automatically generate stack dump

Set 'msdb' to single user mode and run the following command

  DBCC CHECKDB('msdb', REPAIR_REBUILD)

Reset 'msdb' to multiuser mode the same process for your error database.
This resolved our problem.


Can I delete SQLServer2017Media folder from my C drive? I have installed SQL server 2017 on my laptop, because am running out of disk space I would like to know is it okay if I delete the SQLServer2017Media folder (takes 1.6 GB) now that set up us complete and application is running fine?
Installation folder is C:\Program Files\Microsoft SQL Server, but SQLServer2017Media folder is also in my C drive C:\SQLServer2017Media and taking up 1.61 GB.

is it okay if I delete the SQLServer2017Media folder (takes 1.6 GB) now that set up us complete and application is running fine?

Yes it is totally safe to delete it, this would not affect running SQL Server application. But sometimes during un-installation later or may be future installation or adding feature you may need the whole installation file. So my suggestion would be to copy it to pendrive and keep it safe if you "really" do not want to keep it on desktop or any other drive on your PC. Plus would it not be possible to move it to other drive on your PC ( ofcourse if you have one then) ?


MySQL Store Procedure - Setting AUTO_INCREMENT value I am in trouble coding a store procedure as shown below

CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12))
BEGIN

DECLARE `_rollback` BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
DECLARE _refID INT DEFAULT 0;

START TRANSACTION;

SELECT MAX(imp_id) + 1 INTO _refID FROM temp_imp;

INSERT INTO filenamedt (filename, filedate) VALUES (CAPIfilename, now());

INSERT INTO imp__description (imp_id, imp_description) (SELECT imp_id, TRIM(descricaoProduto) FROM temp_imp);

TRUNCATE TABLE temp_imp;

ALTER TABLE temp_imp AUTO_INCREMENT = _refID;

IF `_rollback` THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;
END

The main issue is that MySQL Workbench outputs that there is an error at line:

ALTER TABLE temp_imp AUTO_INCREMENT = _refID;

But I can't figure out what is going on.

The fact is that: I am loading my database from hundreds of files and data is being stored in many different tables.

After I load each file, I trucate temporary table, because it is faster than use delete, so I load another file. When I trucate table, MySQL start from 0 the auto increment number, but I need last int number to use it again as I am using it as a primary key.

Does someone can help me?

Use a prepared statement

DELIMITER $$
CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12))
BEGIN

DECLARE `_rollback` BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
DECLARE _refID INT DEFAULT 0;

START TRANSACTION;

SELECT MAX(imp_id) + 1 INTO _refID FROM temp_imp;

INSERT INTO filenamedt (filename, filedate) VALUES (CAPIfilename, now());

INSERT INTO imp__description (imp_id, imp_description) (SELECT imp_id, TRIM(descricaoProduto) FROM temp_imp);

TRUNCATE TABLE temp_imp;

SET @sql = "ALTER TABLE temp_imp AUTO_INCREMENT =  ?";
PREPARE stmt1 FROM @sql;
SET @a = _refID;
EXECUTE stmt1 USING @a;
DEALLOCATE PREPARE stmt1;
IF `_rollback` THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;
END$$
DELIMITER ;

If you are using workbench and create Stored procedure you don't need the DELIMITER


Unique Non Clustered Column in Partitioned table We have partitioned one of our SQL Server tables on a Clustered Index datetime column, we have a Primary key Non-clustered index column with Identity data type and this column is setup as a non-aligned index. There is one more column xyz which is UniqueIdentifier datatype and xyz column also has Unique Non-clustered index.

The problem is most of our queries use the XYZ column to retrieve data as XYZ is guid and unique.

At this point we are not sure what will be the impact of setting XYZ column (uniqueIdentifier)  with an Aligned Index strategy vs Non-aligned Index strategy.

You cannot have an unique constraints backed by an aligned index (or plain unique non-clustered indexes) unless you add the partitioning column to the the unique expression. So if you have partitioned your table on column [datetime] then your unique constraint (or the unique index) must be ([datetime], [xyz]). Since more often than not this is not acceptable, the alternative are to:

remove the unique constraint from the data model (ie. accept that duplicates can occur)
keep the non-aligned index, with all switch-in/switch-out issues and performance problems


Instead of Trigger Continue with operation after custom logic? I do not think what I am trying to do is possible, but I could not find any documentation on this. I have an INSTEAD OF UPDATE, INSERT, DELETE that always inserts data into a history table before performing the DML operation. So I created triggers that performed my insert into the history table and then I created 3 IF statements to figure out if the original operation was an INSERT UPDATE or DELETE operation to continue the original operation.

Is there a way to perform my history insert and then tell SQL Server to continue with the original INSERT, UPDATE or DELETE without writing those myself? See my code  below. We are using the standard edition of SQL Server, so no CDC to help with logging changes. Thank you.

    SET NOCOUNT ON;

    -- ========================================================
    -- STEP 1: ARCHIVE. Get the records that are being inserted
    -- or deleted and put them in the history table
    -- ========================================================

    -- INSERT INTO HISTORY TABLE HERE

    -- ========================================================
    -- STEP: CONTINUE. After archiving, perform the requested
    -- manipulation
    -- ========================================================

    IF(EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted))
    BEGIN
      -- Perform insert into production table
    END

    IF(EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted))
    BEGIN
      -- Perform update on production table
    END

    IF(NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted))
      -- Perform delete on production table
END

Posting this as an answer to address the core question:

  Is there a way to perform my history insert and then tell SQL Server to continue with the original INSERT, UPDATE or DELETE without writing those myself?

No, it is not possible to have an INSTEAD OF trigger just perform the original DML for you. This is why they are called INSTEAD OF and not BEFORE - they replace the original action with whatever logic you want to perform, which may or may not include DML against the original target table, depending on the scenario (you may want to prevent the insert instead of rolling it back, you may be migrating to a new table and using this as an interim solution, you may have distributed or otherwise not-directly-updateable views, etc).

If you feel you have a valid use case for INSTEAD OF, then you will have to manually code the DML they're replacing. I don't think you do, so, as the others have suggested, use an AFTER trigger.


is there a way to know all filters applied to one publication with query (SQL Server 2014)? I'm looking for a way to get all filters applied to all my publication... I know that I can right click properties -> Filter Rows but is there a way to view them with a query?

You can use the table sysaticle but if you don't find it, there is an alternative table sysmergearticles it has all the filters in column subset_filterclause.


How to reference categories table in products table? I am using modified preorder traversal algorithm to store product categories in database (like image shown above). But i don't understand how to reference it in my products table. Here is my products table

product
id |  name        | supplier         | price | description
--------------------------------------------------------------------------------------
1  |  T shirts    | Rana Enterprises | 200   | Run faster than light!
2  |  Shoes       | RS Shirts        | 100   | If you're going to San Francisco...

This is my categories table

Tell me how to link my categories table to products table ?

I see from your diagram and from the columns named lft and rgt that you are using the nested sets technique for organizing the categories into a tree.  So far so good.

Now all you need to do is add a foreign key to the products table that references category id in the categories table.  You might as well call it "category_id", same as the column it references.  The benefit of declaring it as a foreign key is that the DBMS will now prevent the introduction of products with an invalid category_id.  It's up to you in the application to provide a valid category_id at product insert time.

All of this is very elementary relational database design.  If it's covering material you already know, then please reword the question to make it clear just where you need an answer.


How do I determine why a MongoDB document is failing validation? How do I determine why a MongoDB document insert is failing validation? All I get back is a writeError that says "Document failed validation", which isn't very helpful.

(This happens often, and I'd like to understand how to properly debug these, rather than ask for help with a specific example.)

As at MongoDB 3.2, there is no feedback on the reason document validation failed: the overall validation expression currently evaluates as either True ("OK") or False ("Document failed validation"). Validation behaviour can be adjusted with validationAction (error/warn) and validationLevel (strict/moderate/off) configuration options, but this does not provide any further context for validation failures.

If you want to have more detailed feedback, the recommended approach would be to add validation logic into your application rather than relying solely on server-side checks. Even with server-side validation, many checks are best done in application business logic to minimize round trips to the database server and provide more responsive feedback to the end user.

For example, user input for a web app (required fields, field formats, ...) should be validated in the browser before being submitted to your application or attempting to insert/update in the database.

However, it does make sense to validate at multiple levels to ensure data quality and some context to diagnose validation failures would be very useful. There is a relevant open feature request you can watch/up-vote in the MongoDB issue tracker: SERVER-20547: Expose the reason an operation fails document validation.

For more information you may also be interested in Document Validation - Part 1: Adding Just the Right Amount of Control Over Your Documents. This highlights some of the general pros & cons of document validation as at MongoDB 3.2, and includes a reference table for the outcome based on validationAction and validationLevel configuration options.


DDL statements being commited within transaction I am working on a script that involves a lot of structural changes, and like to return to a "base" condition while I work on what all needs to be changed, so I have my changes wrapped in a begin/rollback transaction.

This works just fine IF there are no errors, but when I make a mistake (attempting to drop a column that doesn't exist or which has a FK constraint, etc), the rollback does not work.  So, objects that I have renamed with sp_rename or columns that I have dropped, have the wrong name/don't exist.

Assuming that this is documented/correct behavior, where is it documented?  Probably just my eyes glazing over...but I can't recall/find anything that says a failure causes a commit.

SQL Server does not auto-commit an explicit transaction.

What might be happening in you case is that the transaction is aborted and rolled back, but your script continues, executing the remainder without a wrapping transaction. This is particularly likely if you have GO statements in your script.

To implement cross batch error handling you can do something like this:

BEGIN TRAN
 CREATE TABLE #OriginalTransaction(i INT);
 BEGIN TRY
   -- do stuff here
 END TRY
 BEGIN CATCH
   IF(@@TRANCOUNT>0)ROLLBACK;
 END CATCH;

GO
 IF(OBJECT_ID('tempdb..#OriginalTransaction') IS NOT NULL)
 BEGIN
  BEGIN TRY
    -- do more stuff here
  END TRY
  BEGIN CATCH
    IF(@@TRANCOUNT>0)ROLLBACK;
  END CATCH;
 END;

GO
 IF(OBJECT_ID('tempdb..#OriginalTransaction') IS NOT NULL)
 BEGIN
   COMMIT;
 END;

GO
 IF(@@TRANCOUNT>0)ROLLBACK;
GO
 IF(OBJECT_ID('tempdb..#OriginalTransaction') IS NOT NULL)
 BEGIN
   DROP TABLE #OriginalTransaction;
 END;


How to handle variations of the same data point? I have a database with three tables:

books,
authors and
books_authors

books_authors is a “junction” table (i.e., it represents a conceptual level many-to-many relationship), so if it has a row with book_id=1 and author_id=5, it means that author 5 is an author of book 1.

Now, the problem is that author names are sometimes written differently depending on the book. For example, in book 1 the name of author 5 is written “洋子”, but in book 2 the name of author 5 is written “ようこ”.

Sometimes these are variations of the same name  (ようこ = 洋子) but sometimes they are completely different (e.g., an author changes his or her name after getting married.)

What's the best way to retain these name variations? Should I enter separate rows for the values “洋子” and “ようこ”, and then link them with another ID? Or should I make an array to keep all the variations in a single row?

Additional information

In response to some clarification requests made via comments, I am going to explain several significant aspects in order to contextualize the scenario more fully:

Authors can actually have six separate name values (First, First-reading, Last, Last-Reading, Full, Full-Reading) but the only one required is Full. The rest "can be" null.
An Author does not have a, say, "base name" in the business domain of interest.
I retain Author Names only when they are associated with a Book.
Authors can be Artists, Editors, etc. I use (what I call) the "junction" table to keep track of which Role they play for each Book.
I think that, usually, multiple Name Variations represent different values of the same domain, as in the case of “ようこ=洋子”, but I believe that sometimes they are different domains (e.g., as in the situation mentioned above, when an Author changes their Last Name after getting married).
I want to manipulate the corresponding tables via SELECT operations that, e.g., include the column(s) containing the Authors Names as (a) condition(s) in the WHERE clauses.
The full properties of interest regarding an Author are just the six Name (what I call) "fields", their Variants, and the Books with which they are associated.
The property whose values uniquely identify an Author instance is only the ID, as far as I can tell.

Note: I'm using Postgres but this question could apply to other relational database management systems.

I may just be repeating @jeffmc's answer, but I would add an author_names table that has a one-to-many relationship with authors, and then make your junction table (books_authors) a junction of books and author_names. (For clarity you may want to rename it books_author_names.) This way you are able to group authors by all their various identities/pseudonyms while being to tell exactly which moniker was used in the publication of a specific book.

I think this is a more precise way of doing it than using a time-based structure, as an author may use multiple identities simultaneously (e.g., Stephen King published both as Richard Bachman and Stephen King for several years).


How do I create a user-defined aggregate function? (Part 2) I'm refering to this question:

How do I create a user-defined aggregate function?

My table contains Zero-Values so I have tried the example of ypercubeᵀᴹ but I got the message that

(NOT EXISTS (SELECT 1 FROM testmult WHERE wert = 0))

is a boolean and it can't be muliplied with

EXP(SUM(LOG(wert)))

  (Error: "boolean * double precision")

So how can I solve it?

Use MIN() instead of trying to invent PROD().  That way, any 0 will lead to a zero result (assuming all the values are non-negative).


High Unrestored log on secondary database mirror server I need your help in understanding/resolving one of the issues we are witnessing today.

We have a high value of unrestored log on secondary side where value keep ranging from 5-10 GB as monitored.

Simultaneously we have a job to create snapshot on this mirror [for reporting] server every 30 minutes which completes within a minute when unrestored is within MB's but keep running for 2-3 hours with high value of unrestored log

The database here is 10 TB and OLTP with DB in high PERformance mode.

Could someone explain why the snapshot job is running that long or how to find it and fix high unrestored value?

Or could it be Vice-versa and i am thinking wrong as mirror causing snapshot to run that long?

We have a high value of unrestored log on secondary side where value keep ranging from 5-10 GB as monitored.

That's definitely a problem even in high performance (async) mode.

Simultaneously we have a job to create snapshot on this mirror [for reporting] server every 30 minutes which completes within a minute when unrestored is within MB's but keep running for 2-3 hours with high value of unrestored log
Could someone explain why the snapshot job is running that long or how to find it and fix high unrestored value?

Creating a snapshot of a database causes the snapshot to run recovery - just as if you were to start up any database in SQL Server. Since there is 10 GB of log to go through... it's going to take a while to run recovery. Pair that with adding of more log to an already behind database and you have yourself a great recipe for never catching up and always being behind.
From BOL: Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created (transactions in the database are not affected).


Can Oracle Named User Licenses be reused? If I have 20 Named users for the month of January, then come February and those users do not retain access but are replaced by 20 new users.

Would I need 40 Named users for this use case or would 20 be sufficient?

In general, you can move named user licenses around so long as at any given point in time you can identify the 20 humans (or non-human licensed entities) that have access to the system.  In your case, so long as there is never a point where all 40 users have access to the system, it sounds like you would be OK.

Of course, as with any licensing question, you would really need to talk with Oracle Sales and/or the folks that manage your licenses to get a definitive answer.  Unlike technical suggestions, licensing suggestions can't really be tried out and verified in your environment before implementation.  And if you get audited and it is determined that there is some issue with how you've managed your licenses, explaining that some guy on the internet told you it was OK is a career-limiting maneuver.


SQL Server Schemas ownership I'm currently learning SQL Server 2008 and have come across schemas and there is one thing I don't understand about them that they have to have an owner; why can't they own themselves?

My background is mostly with Java and it sounds like a schema is like a package which does not have an owner.

Every object in SQL Server has to be owned by someone. Ownership is used to control permissions. For example, any object that you own you have direct access to without it having to be granted to you. An other example is a procedure that has access to any object owned by the same owner. This allows someone with only access to the procedure to execute it even if it is accessing objects that are owned by someone else.

A schema is a grouping mechanism that most other RDBMSs dont have. (If an other RDBMS refers to a "schema" it means something more like what we call a "database".) Schemata allow to group objects of different types together into logical units within a single database. By default, the ownership of the schema is passed through directly to all objects in that schema. If you change the owner of the schema, the owner of all objects within changes too. (this can be changed on an object by object basis however.)

On the other end, you can grant access to a schema which in turn grants access to all objects within. This makes managing permissions a lot simpler.

Be aware, that you should always fully qualify every object with schemaname.objectname. The reason for this is two fold:

Performance: SQL Server tries to figure out the schema of an object that was referenced without schema specification based on different settings and rules. This burns precious cpu cycles unnecessarily.
While rare, it can lead to security and other access problems because the wrong object might be selected by SQL Server if multiple objects with the same name exist in different schemata.


How do I have RMAN treat my restored data files like they are originals? I am planning a migration for this ancient-but-critical Oracle database from one Windows Server 2003 cluster to another.

The database has many data files and they are all getting new paths on the destination cluster. The only paths that are staying the same are for the backup files.

The migration plan I have is as follows:

Backup the database on the source cluster using our nightly RMAN backup script:

run {
    allocate channel Channel1 type disk format 'F:\backup\folder\%d_%T_%s_%U.bak';
    allocate channel Channel2 ...

    backup check logical ( database );
    backup check logical ( archivelog all  not backed up 2 times );
    backup check logical ( current controlfile );
}

allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate - 2' backed up 2 times to disk;
delete obsolete device type disk;

Our RMAN retention policy on both clusters is set to redundancy 2, and the control file is being used as the recovery catalog.
Copy the backup files to F:\backup\folder on the destination cluster.
Restore the database on the destination cluster using this RMAN script:

run {
    allocate channel Channel1 type disk;
    allocate channel Channel2 ...

    set until sequence <sequence number> thread 1;

    SET NEWNAME FOR DATAFILE 'Old\Path\FILE01.DBF' TO 'New\Path\FILE01.DBF';
    SET NEWNAME FOR DATAFILE ...

    restore database;
}

Update the data file paths in the control file, recover the database to the appropriate sequence, and open it with resetlogs.

This works great with the following exception:

When I ask RMAN to delete obsolete files on the destination cluster, it names all of the just-restored data files as obsolete!

RMAN> delete obsolete device type disk;
...
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Datafile Copy        1      02-JUL-12          New\Path\FILE01.DBF
...

Well, RMAN is correct to recognize these data files as copies.

However, since this is a database migration, these copies are intended to be the new originals, so I certainly don't want RMAN to delete them as part of its maintenance!

Is it possible to tweak this migration process so that RMAN treats the restored database as the new original?


Moving from SQL 2005 [SQL_Latin1_General_CP1_CI_AS] to 2008 - will I lose any features by using 'backwards compatibility' We're moving from SQL 2005 [Instance and DB have collation of SQL_Latin1_General_CP1_CI_AS]  to SQL 2008 [which defaults to Latin1_General_CI_AS].

I completed a SQL 2008 R2 installation, and used default Latin1_General_CI_AS collation, with the restoration of the database still on SQL_Latin1_General_CP1_CI_AS. The excepted problems occurred - the #temp tables where in  Latin1_General_CI_AS whilst the db was in  SQL_Latin1_General_CP1_CI_AS and this is where I am now - I need advice on the pitfalls now please.

On installation of SQL 2008 R2, I have the option on installation to use 'SQL Collation, used for backwards compatibility' where I have the option to select the same collation as the 2005 database : SQL_Latin1_General_CP1_CI_AS.

This will allow me to not have problems with #temp tables, but are there pitfalls?
Would I lose any functionality or features of any kind by not using a "current" collation of SQL 2008?
What about when we move (e.g. in 2 years ) from 2008 to SQL 2012? Will I have problems then?
Would I at some point be forced to go to Latin1_General_CI_AS?
I read that some DBA's script complete the rows of complete databases, and then run the insert script into the database with the new collation - I'm very scared and wary of this - would you recommend doing this?

First of all, apologies for such a long answer, as I feel that still there is a lot of confusion when people talk about terms like collation, sort order, code page, etc.

From BOL :

  Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types such as char and varchar dictate the code page and corresponding characters that can be represented for that data type. Whether you are installing a new instance of SQL Server, restoring a database backup, or connecting server to client databases, it is important that you understand the locale requirements, sorting order, and case and accent sensitivity of the data that you will be working with.

This means that Collation is very important as it specifies rules on how character strings of the data are sorted and compared.

Note: More info on COLLATIONPROPERTY

Now Lets first understand the differences ......

Running below T-SQL :

SELECT *
FROM::fn_helpcollations()
WHERE NAME IN (
        'SQL_Latin1_General_CP1_CI_AS'
        ,'Latin1_General_CI_AS'
        )
GO

SELECT 'SQL_Latin1_General_CP1_CI_AS' AS 'Collation'
    ,COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'CodePage') AS 'CodePage'
    ,COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'LCID') AS 'LCID'
    ,COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'ComparisonStyle') AS 'ComparisonStyle'
    ,COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'Version') AS 'Version'

UNION ALL

SELECT 'Latin1_General_CI_AS' AS 'Collation'
    ,COLLATIONPROPERTY('Latin1_General_CI_AS', 'CodePage') AS 'CodePage'
    ,COLLATIONPROPERTY('Latin1_General_CI_AS', 'LCID') AS 'LCID'
    ,COLLATIONPROPERTY('Latin1_General_CI_AS', 'ComparisonStyle') AS 'ComparisonStyle'
    ,COLLATIONPROPERTY('Latin1_General_CI_AS', 'Version') AS 'Version'
GO

The results would be :

Looking at above results, the only difference is the Sort Order between the 2 collations.But that is not true, which you can see why as below :

Test 1 :

--Clean up previous query
IF OBJECT_ID('Table_Latin1_General_CI_AS') IS NOT NULL
    DROP TABLE Table_Latin1_General_CI_AS;

IF OBJECT_ID('Table_SQL_Latin1_General_CP1_CI_AS') IS NOT NULL
    DROP TABLE Table_SQL_Latin1_General_CP1_CI_AS;

-- Create a table using collation Latin1_General_CI_AS
CREATE TABLE Table_Latin1_General_CI_AS (
    ID INT IDENTITY(1, 1)
    ,Comments VARCHAR(50) COLLATE Latin1_General_CI_AS
    )

-- add some data to it
INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('Kin_Tester1')

-- Create second table using collation SQL_Latin1_General_CP1_CI_AS
CREATE TABLE Table_SQL_Latin1_General_CP1_CI_AS (
    ID INT IDENTITY(1, 1)
    ,Comments VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS
    )

-- add some data to it
INSERT INTO Table_SQL_Latin1_General_CP1_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_SQL_Latin1_General_CP1_CI_AS (Comments)
VALUES ('Kin_Tester1')

--Now try to join both tables
SELECT *
FROM Table_Latin1_General_CI_AS LG
INNER JOIN Table_SQL_Latin1_General_CP1_CI_AS SLG ON LG.Comments = SLG.Comments
GO

Results of Test 1:

Msg 468, Level 16, State 9, Line 35
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

From above results we can see that the we cannot directly compare values on columns with different collations, you have to use COLLATE to compare the column values.

TEST 2 :

The major difference is performance, as Erland Sommarskog points out at this discussion on msdn.

--Clean up previous query
IF OBJECT_ID('Table_Latin1_General_CI_AS') IS NOT NULL
    DROP TABLE Table_Latin1_General_CI_AS;

IF OBJECT_ID('Table_SQL_Latin1_General_CP1_CI_AS') IS NOT NULL
    DROP TABLE Table_SQL_Latin1_General_CP1_CI_AS;

-- Create a table using collation Latin1_General_CI_AS
CREATE TABLE Table_Latin1_General_CI_AS (
    ID INT IDENTITY(1, 1)
    ,Comments VARCHAR(50) COLLATE Latin1_General_CI_AS
    )

-- add some data to it
INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('kin_tester1')

-- Create second table using collation SQL_Latin1_General_CP1_CI_AS
CREATE TABLE Table_SQL_Latin1_General_CP1_CI_AS (
    ID INT IDENTITY(1, 1)
    ,Comments VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS
    )

-- add some data to it
INSERT INTO Table_SQL_Latin1_General_CP1_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_SQL_Latin1_General_CP1_CI_AS (Comments)
VALUES ('kin_tester1')

--- Create Indexes on both tables

CREATE INDEX IX_LG_Comments ON  Table_Latin1_General_CI_AS(Comments)
go
CREATE INDEX IX_SLG_Comments ON  Table_SQL_Latin1_General_CP1_CI_AS(Comments)

--- Run the queries

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_Latin1_General_CI_AS WHERE Comments = 'kin_test1'
GO

--- This will have IMPLICIT Conversion

--- Run the queries

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_SQL_Latin1_General_CP1_CI_AS WHERE Comments = 'kin_test1'
GO

--- This will NOT have IMPLICIT Conversion

The reason for Implicit conversion is because, I have my database & Server collation both as SQL_Latin1_General_CP1_CI_AS and the table Table_Latin1_General_CI_AS has column Comments defined as VARCHAR(50) with COLLATE Latin1_General_CI_AS, so during the lookup SQL Server has to do an IMPLICIT conversion.

Test 3:

With the same set up, now we will compare the varchar columns with nvarchar values to see the changes in the execution plans.

-- run the query

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_Latin1_General_CI_AS WHERE Comments =  (SELECT N'kin_test1' COLLATE Latin1_General_CI_AS)
GO

-- run the query

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_SQL_Latin1_General_CP1_CI_AS WHERE Comments = N'kin_test1'
GO

Note that the first query is able to do Index seek but has to do Implicit conversion while the second one does an Index scan which prove to be inefficient in terms of performance when it will scan large tables.

Conclusion :

All of the above tests shows that having right collation is very important for your database server instance.
SQL_Latin1_General_CP1_CI_AS is a SQL collation with the rules that allow you to sort data for unicode and non-unicode are different.
SQL collation wont be able to use Index when comparing unicode and non-unicode data as seen in above tests that when comparing nvarchar data to varchar data, it does Index scan and not seek.
Latin1_General_CI_AS is a Windows collation with the rules that allow you to sort data for unicode and non-unicode are same.
Windows collation can still use Index (Index seek in above example) when comparing unicode and non-unicode data but you see a slight performance penalty.
Highly recommend to read Erland Sommarskog answer + the connect items that he has pointed to.

  This will allow me to not have problems with #temp tables, but are there pitfalls?

See my answer above.

  Would I lose any functionality or features of any kind by not using a "current" collation of SQL 2008?

It all depends on what functionality/features you are referring to. Collation is storing and sorting of data.

  What about when we move (e.g. in 2 years ) from 2008 to SQL 2012? Will I have problems then? Would I at some point be forced to go to Latin1_General_CI_AS?

Cant vouch ! As things might change in and its always good to be inline with Microsoft's suggestion + you need to understand your data and the pitfalls that I mentioned above. Also refer to this and this connect items.

  I read that some DBA's script complete the rows of complete databases, and then run the insert script into the database with the new collation - I'm very scared and wary of this - would you recommend doing this?

When you want to change collation, then such scripts are useful. I have found myself changing collation of databases to match server collation many times and I have some scripts that does it pretty neat. Let me know if you need it.

References :

Storage and Performance Effects of Unicode
Working with Collations
Collation and Unicode Support
Code Page Architecture


(AWS) Mysql memory alert threshold(for monitoring) and innodb_buffer_pool_size I recently upgraded my AWS RDS(mysql) from 5.6.23 to 5.7.11.

innodb_buffer_pool_size is automatically set as 75% of RAM by AWS. (I think it's maybe a recommendation)

The problem is the available RAM(FreeableMemory in AWS) slowly but steadily decreasing. It's never going up.. now at 66%..2 days after rebooting.

First I thought it's kind of bug at 5.7.11(it never happened before) and memory leaking but maybe it's not.

What if.. mysql is storing query to cache for the performance.. much more aggressively than 5.6.x..?

So my question is, what is the proper number of memory threshold for the monitoring?

If innodb_buffer_pool_size is set as 75% of RAM, memory threshold is set as 25% of RAM?

Please notice me for any further information needed.

With the buffer_pool set at 75% of RAM, it will grow until it consumes that much of RAM.  But there is a lot of other things in RAM -- the OS, MySQL code, other caches, buffers, etc.  The real goal (for a server running only MySQL) is to consume 100% of RAM, but no more.

The buffer_pool is the biggest item that can be tuned.  75% is a good number (assuming you have at least 4GB of RAM).  Only in extreme cases (or you fiddle with other tunables), will you blow out 100%.

Using more memory than you have is really bad because it leads to "swapping".  But MySQL is not coded to understand that.  It assumes, for example, that the entire buffer_pool can be accessed without any disk I/O.  Disk I/O is a performance killer.

I don't know what AWS means by FreeableMemory, but 66% seems fine.  Even 25% seems fine.  5% would seem dangerously close to swapping, and I would worry.  None of the buffer_pool should be "freeable"; it sounds like the buffer_pool has not yet grown to its stated max.


Enable autocomplete in an sqlite3 interactive shell I am using sqlite3 on a machine where I can use tab completion (ie .read abc<tab> will autocomplete to .read abcdefghij.db. I would like to know how to enable this on my personal machine.

Edit

Both machines are ubuntu linux and the shell is bash. I am referring to autocompletion in the sqlite interactive prompt.

Question was ultimately answered on superuser.

The solution is to compile sqlite3 with readline support.


Effect of over allocating shared_buffers I have a 9.6 PostgreSQL RDS instance that I am tending to, but did not initially configure, and have been struggling with two different symptoms:

the system (a db.r3.2xlarge w/ 61GB memory) would experience "cliffs" in freeable memory, dropping 20-30GB almost instantaneously, followed by -
an auto-recovery event after some amount of time (could be minutes, could be hours).

On investigation, I found shared_buffers set abnormally high, actually exceeding physical memory on the machine. I have reset to a more sane level, but would like some more insight into the expected behavior.

The workload is modest - about a dozen or so concurrent users, manually triggering some large queries (e.g., aggregates on 300+ million row tables). My guess is that those queries trigger the cliffs. My question is about the after effects:

Would over-allocating shared_buffers explain the stability problems? E.g., PostgreSQL tries to allocate more buffer than the OS has left, the OS says "nope" and things go south from there.
Is shared_buffer memory ever released back to the OS? For example, I would frequently see a couple idle backends consuming 20-30GB, terminate them, but never see a rise in freeable memory.

Would over-allocating shared_buffers explain the stability problems? E.g., PostgreSQL tries to allocate more buffer than the OS has left, the OS says "nope" and things go south from there.

When the OS says "nope" it's in the system log, and likely the PostgreSQL error log. Just open it up and look. The process should also not start if a bigger allocation is requested than SHMMAX. But that value may not be set properly -- it may be higher than the physical memory.

  On investigation, I found shared_buffers set abnormally high, actually exceeding physical memory on the machine. I have reset to a more sane level, but would like some more insight into the expected behavior.

shared_buffers doesn't have to fit into RAM. It'll just page. This could be very bad for performance, but I don't know if it would cause the symptoms seen. "Stability problems" are usually hard to define. A bottleneck that reduces memory speed to disk speed can cause all kinds of bad behavior.


database setup for posts table with multiple types Struggling with how to set this up for speed and scalability.

So basically I have a bunch of posts that depending on what type of post it is, it could have different input values that I would need to store. So what I am thinking of is storing the common values together in a "posts" table and the storing the Type specific values all in separate Type specific tables... Is that scalable? especially when I want to pull say ALL posts?

what I am thinking below:

Tables:

posts:
id, type_id, commonValue1(varChar), commonValue2(int), commonValue3(boolean), commonValue4(boolean)

Type1:
id, post_id, Type1SpecValue1(varChar), Type1SpecValue2(int), Type1SpecValue3(boolean)

Type2:
id, post_id, Type2SpecValue1(int), Type2SpecValue2(varChar)

Type3:
id, post_id, Type3SpecValue1(varChar), Type3SpecValue2(int), Type3SpecValue3(int), Type3SpecValue4(boolean), Type3SpecValue5(varChar)

This will obviously expand as time goes on and I will add more types ... any ideas? The other thought was I just create different Type tables with all the values in them (even the common values) and then just have a posts table for when I need to pull multiple types? thoughts on how to set this up?

---Update---

Just to clarify those specific values could be int, varchar etc. or else I would just do a many to many with items table but I don't think that will work with different field types.

You may want to consider using a single JSON field on your posts table to store all the miscellaneous details in a JSON object. You can have as many as you want, including arrays and nested objects.

At this time MySQL is not great at indexing and accessing/manipulating JSON data, but it does provide a few functions like JSON_OBJECT, JSON_ARRAY, JSON_SET, JSON_INSERT, JSON_REMOVE, JSON_REPLACE, JSON_MERGE, and JSON_EXTRACT, and surely future version will have even greater support, such as indexing.

MariaDB has also Dynamic Colums and also good (possibly better) support for JSON data than MySQL.


SQL Server Replication, is it possible to have "upload only" syncronization on some articles? Within SQL Server's merge replication, I know how to make publication's article download-only (either with update restrictions on subscriber database or not) - @subscriber_upload_options parameter in sp_addmergearticle, or corresponding choices in the SSMS's GUI.

What I would like to achieve though is the reverse behavior on some articles. So that records that originate on the subscriber's side travel up to the publisher during syncronization, but not the other way around. And that updates made on the publisher are not sync'ed back to the subscriber, while updates made on subscriber do go up to the publisher. I.e. exactly reverse of the download-only articles.

Is this possible to achieve? Either with "standard" configuration options of the replication or with some manual hacks?

You can add a filter like: 0 = 1, and the article becomes upload only


Converting XML to MySQL tables I have a large XML file in the following format and I need to convert this file into a MySQL table. Please let me know how can I accomplish this?

~~~~~~~~~~

<host starttime="1392325468" endtime="1392325486"><status state="up" reason="user-set"/>
<address addr="192.168.0.2" addrtype="ipv4"/>
<hostnames>
</hostnames>
<ports><extraports state="filtered" count="2">
<extrareasons reason="no-responses" count="2"/>
</extraports>
<port protocol="udp" portid="22"><state state="open|filtered" reason="no-response" reason_ttl="0"/><service name="ssh" method="table" conf="3"/></port>
<port protocol="udp" portid="123"><state state="open|filtered" reason="no-response" reason_ttl="0"/><service name="ntp" method="table" conf="3"/></port>
</ports>
</host>

~~~~~~~~~~

For MySQL, you might use use the ExtractData function.

If you are also using Windows (it needs Net Framework) then this old and outdated tool might still do the job.

http://xmltodb.sourceforge.net/

I have used it in the past and it converts the XML file to plain INSERT commands. For simple XML files it actually works. Of course the INSERT commmands will need some tweaking (change " or ') according to the db flavor but 90% of the actual job is done with the tool.

Commercial tools like XMLSpy http://www.altova.com/xmlspy/database-xml.html have a similar functionality (you may try the trial editions).

You can even try importing the file to MS Excel and then export to CSV for importing into the MySQL database. MS Excel has a nice feature XML maps that can map XML attributes and entities to columns. I tested it and it will easily work with a well formatted XML like yours.


Why wont SSMS remember my tables when I connect to the server I have the same problem everytime I connect to SQL server through the SSMS tool.  If I have been working on a script and close my laptop for a period of time, when I come back and re-connect it doesnt recognise my tables in the database I am working on.  Usually I have to right click on the table name and select top 1000 rows for it to be remembered in the cache.  Its a workaround which I know I dont need to do as I am missing something or not doing something correctly.

Any tips?

You can refresh the IntelliSense cache with the key shortcut:

  ctrl+shift+r

Your query window might have lost its connection as well - just:

  Right click on the query window > Connection > Connect

As a little extra: This stackOverflow post discusses custom keyboard shortcuts in ssms - there's loads of custom shortcuts you can setup for anything you might need (in ssms the menu is Tools > Options > Keyboard)


SHA-256 hashing in oracle 11g How can i encrypt or hash a password inside a trigger using sha-256 so that i can store the hashed value in another table using that trigger? Also how can I decrypt it??

Unfortunately DBMS_CRYPTO in Oracle 11.2 only supports SHA1 (documentation link), which is 160-bit. .

DBMS_CRYPTO in Oracle 12.1 supports SHA2 HASH_SH256 (documentation link), which does what you require.

There are some free implementations of SHA2 just a google away. This blog post, for example.

As for decrypting a hashed password? I don't think you understand hashing. Hashing is 1-way, unless you brute-force it, or use Rainbow Tables. To check if a password is correct, you would hash it and compare it to the stored hash.


creating buckets in oracle sql I saw another post where they showed you how to do this with a case statement and had you define your buckets in advance. how do I make it dynamic?

I know about the width_bucket and ntile functions. I am having trouble getting them to do what I want.

I created the following table to get counts of duplicate names

create table name_dupe as
select name,count(*) mycount
from mynametable
group by name
having count(*) > 1

I now want to find out how many duplicate names fit into certain counts. For example:
How many names have between 101 and 200 duplicates, 201 and 300. 301 and 400 and so on. I may want to change the bucket ranges.

How do I do this? I think its with width_bucket and not ntile, but I can't get it to work. This is what I tried.

select distinct mycount,dupes
from (
select mycount,width_bucket(mycount,200,300,30) dupes
from name_dupe
)
where dupes > 0
order by mycount

Sample output:

mycount dupe
------- ----------
    887         31
    909         31
    993         31

You were getting close.  WIDTH_BUCKET does not allow a subset of the ranges to be specified, you must specify the start and end of the whole range and how many buckets you want that range divided into.  In the case of 1-100, 101-200, 201-300, 301-400, & 401-500 your start and end are 1 and 500 and this should be divided into five buckets.  This can be done as follows:

SELECT WIDTH_BUCKET (mycount, 1, 500, 5) Bucket FROM name_dupe;

Having the buckets we just need to count how many hits we have for each bucket using a group by.  This comes together with the above as follows:

SELECT Bucket*100 - 99 "Start", Bucket*100 "End", Count(Bucket) "Duplicates In Range"
FROM
(
   SELECT WIDTH_BUCKET (mycount, 1, 500, 5) Bucket FROM name_dupe
)
GROUP BY Bucket ORDER BY Bucket;

There are some tradeoffs for creating the name_dupe table.  To avoid them you could combine your first query with this one as follows:

SELECT Bucket*100 - 99 "Start", Bucket*100 "End", Count(Bucket) "Duplicates In Range"
FROM
(
    SELECT WIDTH_BUCKET (count(name), 1, 500, 5) Bucket FROM mynametable
    GROUP BY name
    HAVING count(name) > 1
)
GROUP BY Bucket ORDER BY Bucket;

Finally, here is another solution avoiding the use of WIDTH_BUCKET, which was apparently added in 9.2.

SELECT rownum*100 - 99 "Start", rownum*100 "End", d2 "Duplicates In Range" FROM
(
SELECT trunc((dupes+99)/100), count(trunc((dupes+99)/100)) d2 FROM
   (
      SELECT name, count(name) dupes FROM mynametable GROUP BY name
      HAVING count(name) > 1 ORDER BY 2
   )
GROUP BY trunc((dupes+99)/100) ORDER BY 1
);

Test Data:

--drop table mynametable;
create table mynametable as (
   select 'a' name from dual connect by level <= 1
   UNION ALL
   select 'b' name from dual connect by level <= 100
   UNION ALL
   select 'c' name from dual connect by level <= 101
   UNION ALL
   select 'd' name from dual connect by level <= 120
   UNION ALL
   select 'e' name from dual connect by level <= 200
   UNION ALL
   select 'f' name from dual connect by level <= 250
   UNION ALL
   select 'g' name from dual connect by level <= 300
   UNION ALL
   select 'h' name from dual connect by level <= 400
);

create table name_dupe as (
   select name,count(*) mycount from mynametable
   group by name having count(*) > 1
   );


finding the path to start postgres On my local machine, when I want to start the postgres server (postgres was installed via Homebrew), I do this

pg_ctl -D /usr/local/var/postgres -l logfile start

I just installed postgres on a ubuntu vps (without Homebrew and not on OSX obviously) and the files are obviously set up a little differently, however, based on what I did on the local machine to start pg, I tried to find the path to postgres folder on the vps. From my home/michael directory, I backed up two levels cd ../../ to a folder which contains these folders

bin   etc         lib         mnt   root  selinux  tmp  vmlinuz
boot  home        lost+found  opt   run   srv      usr
dev   initrd.img  media       proc  sbin  sys      var

Inside etc, there's a postgresql inside of which there's a 9.1 folder, inside of which is main that has all these files

environment  pg_hba.conf    postgresql.conf
pg_ctl.conf  pg_ident.conf  start.conf

Based on how I start pg on my local machine, I'm assuming pg_ctl.conf is the file I'm looking for. I tried to start pg this way

 pg_ctl -D /etc/postgresql/9.1/main -l logfile start

but I get the response

-bash: pg_ctl: command not found

I'm assuming that I have to start the server once before launch of the app and then let it run, but I can't get it started.

Update

Once I finished installing postgres, I ran this command

sudo service postgresql restart

I'm not sure if that starts postgres once and I never need to start it again (hence this question is moot), or if that was just something I do at install and then start it everytime I use it as I do on my local machine.

When you install a program in Linux it is most often configured to just work. So you would do service postgresql start to start it, service postgresql stop to stop, and service postgresql status to check if it's working etc.

You may need to set it up that it will start automatically on boot. How to do this depends on your system version, so you may need to Google it. On my system it is chkconfig postgresql on to enable, and chkconfig postgresql off to disable.

If you want to find where is data directory of a running postgres then you can use sudo su postgres -c "psql -c 'show data_directory'". You can find Postgres configuration files, logs and data there.


Logical consistency-based I/O error I am using SQL Server 2008 R2. My server got accidentally shutdown. After restarting it, I am not able to access my database. I am getting the following error for a few tables:

  SQL Server detected a logical consistency-based I/O error: incorrect
  checksum (expected: 0xd1723da7; actual: 0xab6a6efb). It occurred
  during a read of page (1:69592) in database ID 16 at offset
  0x00000021fb0000 in file 'H:\BACK UP\SQL17052014\databasse.mdf'.
  Additional messages in the SQL Server error log or system event log
  may provide more detail. This is a severe error condition that
  threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

I tried DBCC CHECKDB. It was giving error in one page. I tried to repair that page but not able to do so.

Can any one help me solve this problem?

If indeed it is only one page that is affected, you should be able to perform a page level restore to get the database back. Full details are here:
http://www.sqlpassion.at/archive/2015/10/13/how-to-perform-a-page-level-restore-in-sql-server/

This does assume that you have a recent full backup of the database.


Mongo HDD disk grows to fast when insert / deleting data My Mongo version is 2.6.9

I have a application what is inserting, reading and removing data from Mongo. Thats all working fine, however the disk space increate exponentially. My HDD is around 120 GB, and mongo consumes about: 100GB.

I checked the collection what was consuming so much data, i couldnt do a repair database because of disk space. So i remove my database completely.

mongodump -d db
echo 'db.dropDatabase()' | mongo db
mongorestore /root/bashscript/backups/dump/db

I reimported all the old documents, except from the collection what was consuming so much data. I recreated this collection with compact and power of 2 sizes (because that should do the trick)

Now when i add new data in this collection (around 200.00 docs), the result is the same, the collection is using 80GB of data.

What am i doing wrong? Am i using Mongo DB wrong? Is it not a good idea to use mongo db in my situation?
I don't understand why mongo is consuming so much disk space.

Well. This depends on the below factors. Working Dataset, padding factor and fragmentation.

You will need to check which of the below things are not working for you.

1) Padding: As you said you implemented padding, can you let me know the value of padding factor of your mongo?

2) Fragmentation leaves some holes in your data files when they are moved around. MongoDB tries to fill holes whenever possible. Again it depends on your data files, if the new data files which are supposed to be accommodated in the existing holes are larger than the hole size, it can't be fit.
So, compact and usePowerOf2Sizes options come into our rescue.

Can you let me know the frag value of your collection?

db.(collection).stats().storageSize

It gives the sum of size of the collection + total index size

If you are on MMAPv1, you can switch to WiredTiger storage engine and test how fragmentation is working. You will need to upgrade to 3.0 to plugin to avail this storage engine.

If all the above options doesn't work, we are left with a tough option. Your application has to minimize fragmentation by pre-allocating the max size of a document and ensuring document size growth is managed correctly.


Is it possible to convert existing users from SQL Server authentication to Windows (AD) authentication? (SQL Server 2008 R2) We have just undergone a bare-metal restore of an SBS 2003 server running our SQL database and are in the final stages of configuration before going live again.

Previously, we had 20 users accessing this database via SQL authentication. This wasn't ideal, as users had multiple sets of credentials to remember-but the person who set it up decided to do it this way. (Any ideas why someone would choose this over AD authentication?)

I am wondering if there is any way to move from using SQL user accounts to AD credentials? If so, what would be the best practice method for achieving this with an existing database?

Any advice would be much appreciated!

The security concept of SQL Servers differentiate between login and user. A login is at the SQL Server instance level and can be mapped to different users for different databases, as long as the database user is a SQL user and not a Windows user.

You could create the Windows login and the user (both having the same name) followed by script that copies the permissions from the SQL user to the Windows user. (Search for "sql server clone user permissions").


How can I join two tables of an Oracle database containing all rows (not the intersection) I have a Oracle database with a table t1 with:

cod | text1
-----------
1   | aaa
2   | bbb
3   | ccc

table t2 with:

cod | text2
-----------
2   | abc
4   | mlc
5   | asc

how can I optain something like:

cod | text1 | text2
-------------------
1   | aaa   |
2   | bbb   | abc
3   | ccc   |
4   |       | mlc
5   |       | asc

If I try to do:

select * from t1
inner join t2 on t1.cod = t2.cod

doesn't works, it gives me only the intersection or something.

I know that I should test my SQL script as well, but I don't have time to make a test before leaving for work!  I can make a SQL Fiddle later, but for now it seems that no one has quite answered the question correctly.

SELECT COALESCE(t1.cod, t2.cod) cod, t1.text1, t2.text2
FROM t1 FULL OUTER JOIN t2 ON t1.cod = t2.cod
ORDER BY cod;

If you want to test the performance, use the link  to the SQL Fiddle below!

SQL Fiddle

EDIT:

So, a commentor below asked why I used COALESCE.  It's because of the FULL OUTER JOIN.  In this case, you are asking the query to return rows WHERE t1.cod = t2.cod, but the FULL OUTER JOIN ensures that NULL entries are returned whenever there is no match.

In these cases, you can't know if a particular cod will be in table t1, table t2, or in both, so by using COALESCE you ensure that so long as a one of the tables has the field cod (which is guaranteed, since you did a FULL OUTER JOIN), this will be filled in.

WARNING: One of the alternative posted responses is an incorrect solution.

Applying that erroneous query

select t1.cod, t1.text1, t2.text2 from t1 full outer join t2 on t1.cod=t2.cod ORDER BY cod;

yields NULLs in the cod fields if the cod was only present in table t2.

See this SQL Fiddle for confirmation of the error.

However, applying my query with COALESCE will yield the desired results.

Use this SQL Fiddle, to confirm.


Optimizing slow SELECT query (postgresql 9.6.5) In an app called Links, users post interesting content in a forum like interface, and others post replies or comments under this publicly posted content.

These posted replies are saved in a postgresql table named links_publicreply (postgresql 9.6.5 DB).

One query run on the table keep on cropping up in the slow_log (greater than 500ms). This relates to showing the most-recent 25 public replies accumulated under a given piece of shared content.

Here's a sample from the slow log:

LOG:  duration: 1614.030 ms  statement:

 SELECT "links_publicreply"."id",
       "links_publicreply"."submitted_by_id",
       "links_publicreply"."answer_to_id",
       "links_publicreply"."submitted_on",
       "links_publicreply"."description",
       "links_publicreply"."abuse",
       "auth_user"."id",
       "auth_user"."username",
       "links_userprofile"."id",
       "links_userprofile"."user_id",
       "links_userprofile"."score",
       "links_userprofile"."avatar",
       "links_link"."id",
       "links_link"."description",
       "links_link"."submitter_id",
       "links_link"."submitted_on",
       "links_link"."reply_count",
       "links_link"."latest_reply_id"
FROM   "links_publicreply"
       INNER JOIN "links_link"
               ON ( "links_publicreply"."answer_to_id" = "links_link"."id" )
       INNER JOIN "auth_user"
               ON ( "links_publicreply"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  "links_publicreply"."answer_to_id" = 8936203
ORDER  BY "links_publicreply"."id" DESC
LIMIT  25

Here are the explain analyze results of the said query: https://explain.depesz.com/s/pVZ5

According to that, ~70% of the query time seems to be taken up by index scan. But to an accidental DBA like myself, it isn't immediately obvious what optimization I can do to make this more performant. Perhaps a composite index on links_publicreply.answer_to_id, links_publicreply.id?

It would greatly help me learn if a domain expert can furnish guidance + intuition on solving this class of problem.

P.s. \d links_publicreply is:

                                      Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
 category        | character varying(20)    | not null
 seen            | boolean                  | not null
 abuse           | boolean                  | not null
 device          | character varying(10)    | default '1'::character varying
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

Firstly, trying to reduce the number of calling the index. This filter answer_to_id = 8936203 returns 14740 rows which used to check on the other tables. However you need only top 25 of id.
What if you LIMIT 25 then JOIN the other tables.

WITH tmp_links_publicreply AS (
   SELECT ...
   FROM links_publicreply
   WHERE  "links_publicreply"."answer_to_id" = 8936203
   ORDER  BY "links_publicreply"."id" DESC
   LIMIT  25
)
SELECT
FROM tmp_links_publicreply t
JOIN ...
JOIN ...

The query above would work correctly if you have the constraint between links_publicreply and 2 tables links_link and auth_user. Why? Supposed that, you LIMIT 25, then found nothing when JOIN because there is no rows in links_link related to answer_to_id = 8936203.

Then, creating the new index on answer_to_id, id DESC.

Note: WITH (query above) called Common Table Expressions


Azure SQL Database finding latest available restore point From reading books online it is clear that there are two types of retention for Azure SQL Database.

Backups for point-in-time restore
Backups for long-term retention

When it comes to restore there are multiple options.

Point-in-time restore
Deleted database restore
Geo-restore

When I think of Geo-Restore the latest restore point cannot be same as Point in-time restore as backup has to be copied in a different region/data center.

  There is a delay between when a differential backup is taken and when
  it is geo-replicated to an Azure blob in a different region. This
  delay can be up to an hour, so, if a disaster occurs, there can be up
  to one hour data loss.

To find the latest restore point for Geo backup I can use Get-AzureRmSqlDatabaseGeoBackup command.

Question:

How can I find out LastAvailableBackupDate for point in-time restore?

You can use Get-AzureRmSqlDatabaseRestorePoints to get the earliest possible restore point for the db.  The output from this command is below:

ResourceGroupName        :
ServerName               :
DatabaseName             : testdb
Location                 : East US
RestorePointType         : CONTINUOUS
RestorePointCreationDate :
EarliestRestoreDate      : 2/23/2019 1:53:30 AM
RestorePointLabel        :

Unfortunately, this command does not give you the latest restore point.

According to Microsoft the transaction log backups generally occur every 5 - 10 minutes.  I attempted to do a test restore with the recovery point 2 minutes behind the current time and got the following error:

  Choose a restore point between earliest restore point and latest backup time which is 6 minute before current time.


Extract week number from character varying column where dates are in float values as well as in datetimestamp as well I have a table in which the closure_date which is represented as a character varying column. Some of the dates are saved as "43684.5708564815" and also as "2019-05-24 18:51:17". Now I need to extract the week number from the column. I tried the following the query :

 SELECT closure_date,
 extract('week' from timestamp '1899-12-30' + interval '1 day' * cast(closure_date as  double
 precision)) as closure_week,
 FROM <table_name>
 LIMIT 10000

This query runs fine for values like  43684.5708564815 however result into an error while trying to extract the week_number from "2019-05-24 18:51:17" stating :

 ERROR:  invalid input syntax for type double precision: "2019-05-24 18:51:17"
 SQL state: 22P02

How to handle the error? Any suggestions?

You can use a CASE expression with a regex to test if the string is formatted like an ISO date or not:

SELECT closure_date,
       case
         when closure_date ~ '[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9:]{8}'
           then extract(week from closure_date::timestamp)
         else
           extract('week' from timestamp '1899-12-30' + (interval '1 day' * cast(closure_date as double precision)))
        end as closure_week
FROM <table_name>

The ~ operator compares the closure_date with the regular expression to see if it matches that pattern.

Note that if you have different formats, this could still fail and you need to add more checks.

If you have the chance, you should really change that column to be a timestamp rather than keeping this badly formatted string.


Logical Replication to different schema name We have PostgreSQL 11 database with structure one database one schema (public).

dbname : production_one
schema : public

dbname : production_two
schema : public

I would like to set up a logical replication, so that it will have structure One database, multiple schema as below:

dbname : prod_slave
schema : prod_one, prod_two

is it possible to do logical replication as above?

I have successful replication using streaming replication, but the situation need us to use logical replication as above.

This does not seem to be possible with built-in logical replication: https://www.postgresql.org/docs/current/logical-replication-subscription.html

  The tables are matched between the publisher and the subscriber using
  the fully qualified table name. Replication to differently-named
  tables on the subscriber is not supported.

Since it uses fully qualified names, a different schema would count as a different name, and is not supported.

pub/sub is just a user friendly wrapping around a more general facility, you might be able to create decoding plugins to give you finer control.  I doubt that doing so is for the faint of heart, though.


Why does the SHOW FULL COLUMNS command show the Type field in lower case? When I run SHOW FULL COLUMNS, it shows the Type column in lower case, such as text and varchar(255).  I thought these type names were supposed to be upper case (TEXT and VARCHAR), per https://dev.mysql.com/doc/refman/8.0/en/string-types.html.

I suggest it is sloppiness on the part of the implementor.

Keywords are not case sensitive.  A common convention, except by some SHOW commands, is to use uppercase.

Column names are not case sensitive.

Table and database names may be case sensitive, primarily dependent on the OS.


SQL Server Installer appears to be resetting MSDB Recovery Mode to Simple Everytime we upgrade a SQL Server installation (service pack or cumulative update), msdb is changed from FULL recovery mode to SIMPLE recovery mode.

I understand that the msdb database doesn't change much and it is common to have it in SIMPLE, but I'm wondering why the installer is changing it.  It is causing errors in my backup software.

Here is an extract from the installation logs:

2020-02-15 03:06:38.25 spid8s      Starting execution of MSDB.SQL
2020-02-15 03:06:38.25 spid8s      ----------------------------------
2020-02-15 03:06:38.35 spid8s      Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2020-02-15 03:06:38.35 spid8s      Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2020-02-15 03:06:38.56 spid8s      Checking the size of MSDB...
2020-02-15 03:06:39.41 spid8s
2020-02-15 03:06:39.41 spid8s      Setting database option TRUSTWORTHY to ON for database 'msdb'.
2020-02-15 03:06:39.41 spid8s      Setting database option RECOVERY to SIMPLE for database 'msdb'.
2020-02-15 03:06:39.46 spid8s      ----------------------------------
2020-02-15 03:06:39.46 spid8s      Finished execution of MSDB.SQL
2020-02-15 03:06:39.46 spid8s      ----------------------------------
2020-02-15 03:06:39.46 spid8s      -----------------------------------------
2020-02-15 03:06:39.46 spid8s      Starting execution of MSDB_VERSIONING.SQL
2020-02-15 03:06:39.46 spid8s      -----------------------------------------
2020-02-15 03:06:39.47 spid8s      -----------------------------------------
2020-02-15 03:06:39.47 spid8s      Finished execution of MSDB_VERSIONING.SQL
2020-02-15 03:06:39.47 spid8s      -----------------------------------------

This is already mentioned in the documentation at Microsoft site as below:

  By default, msdb uses the simple recovery model. If you use the backup
  and restore history tables, we recommend that you use the full
  recovery model for msdb. For more information, see Recovery Models
  (SQL Server). Notice that when SQL Server is installed or upgraded and
  whenever Setup.exe is used to rebuild the system databases, the
  recovery model of msdb is automatically set to simple.

You need to change the recovery model after doing any installation or service pack as per your requirement.

Hope above clarifies your concern.


How does PostgreSQL compare strings? I'm running Postgresql 9.6 on Ubuntu 16.04. The collation order is en_ZA.UTF-8. I'm puzzled by how Postgres compares strings:

test=> select 'b' > 'B';
 ?column?
----------
  f

test=> select 'ba' > 'B';
?column?
----------
  t

test=> select 'b' = 'B';
?column?
----------
  f

test=> select 'ba' > 'C';
 ?column?
----------
 f

The response to the second query doesn't make sense to me.

That's just how your locale defines sort order. Obviously  upper case letters act as tiebreakers if the string is otherwise identical - then they sort after lower case equivalents. But 'ba' still sorts after 'B' (and 'BA' sorts after 'b').

Compare to results without collation rules:

SELECT   'b' > 'B'
      , 'ba' > 'B'
      , 'ba' > 'C'
      , 'b'  > 'B' COLLATE "C"
      , 'ba' > 'B' COLLATE "C"
      , 'ba' > 'C' COLLATE "C";

 ?column? | ?column? | ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------+----------+----------
 f        | t        | f        | t        | t        | t

(My current collation setting German_Germany.1252 happens to behave just like your en_ZA.UTF-8.)