Improve UPDATE performance on big table I'm using Postgres 9.5 on Amazon RDS (2vCPU, 8 GB RAM).
I use pganalyze to monitor my performance.
I have around 200K records in the database.

In my Dashboard I see the following queries are taking 28 and 11 seconds in average to execute:

UPDATE calls SET ... WHERE calls.uuid = ?   telephonist 28035.41    0.01    100%    0.03%

UPDATE calls SET sip_error = ? WHERE calls.uuid = ? telephonist 11629.89    0.44    100%    0.69%

I already tried VACUUM, found and clean 7,670 dead rows.
Any ideas how to improve UPDATE performance? This is the query:

UPDATE calls SET X=Y WHERE calls.uuid = 'Z'

How can I improve the query above? Can I add another field? Example:

UPDATE calls SET X=Y WHERE calls.uuid = 'Z' AND calls.campaign = 'W'

The column uuid is not indexed.
https://www.tutorialspoint.com/postgresql/postgresql_indexes.htm suggests that indexes are not recommended for UPDATE operations.

CREATE TABLE public.calls (
    id int4 NOT NULL DEFAULT nextval('calls_id_seq'::regclass),
    callsid varchar(128),
    call_start timestamp(6) NOT NULL,
    call_end timestamp(6) NULL,
    result int4 DEFAULT 0,
    destination varchar(256),
    campaign varchar(128),
    request_data varchar(4096),
    uuid varchar(128) NOT NULL,
    status varchar(64),
    duration int4,
    recording_file varchar(256),
    recording_url varchar(256),
    recording_duration int4,
    recording_text varchar(4096),
    recording_download bool DEFAULT false,
    description varchar(4096),
    analysis varchar(4096),
    is_fax bool DEFAULT false,
    is_test bool,
    hangup_cause varchar(128),
    media_detected bool DEFAULT false,
    sip_callid varchar(256),
    hangup_cause_override varchar(256),
    is_blacklisted bool DEFAULT false,
    sip_error varchar(256),
    hangup_cause_report varchar(128),
    summary varchar(1024)
);

EXPLAIN ANALYZE
    SELECT * FROM calls
    WHERE calls.uuid='e2ce9eb4-v1lp-p14u-7kkk-lruy-e2ceaae46d';

Seq Scan on calls  (cost=0.00..16716.25 rows=1 width=3301)
                   (actual time=81.637..81.637 rows=0 loops=1)
  Filter: ((uuid)::text = 'e2ce9eb4-v1lp-p14u-7kkk-lruy-e2ceaae46d'::text)
  Rows Removed by Filter: 99970
Planning time: 0.482 ms
Execution time: 81.683 ms

Assuming the column uuid is supposed to be UNIQUE, this table definition should save some space and help performance:

CREATE TABLE public.calls (
   id serial PRIMARY KEY,
   result int4 DEFAULT 0 NOT NULL,
   uuid uuid UNIQUE NOT NULL  -- creates the index you need automatically
   call_start timestamp NOT NULL,
   call_end timestamp,  -- so this can be NULL?
   duration int4,
   recording_duration int4,
   callsid varchar(128),
   destination varchar(256),
   campaign varchar(128),
   request_data varchar(4096),
   status varchar(64),
   recording_file varchar(256),
   recording_url varchar(256),
   recording_text varchar(4096),
   recording_download bool DEFAULT false,
   description varchar(4096),
   analysis varchar(4096),
   is_fax bool DEFAULT false,
   is_test bool,
   hangup_cause varchar(128),
   media_detected bool DEFAULT false,
   sip_callid varchar(256),
   hangup_cause_override varchar(256),
   is_blacklisted bool DEFAULT false,
   sip_error varchar(256),
   hangup_cause_report varchar(128),
   summary varchar(1024)
);

The most important feature here is the UNIQUE constraint, which is implemented with a unique index, and an index is what you need more than anything else (like @ypercube already commented).

If uuid is not unique, create a plain btree index on it.

If uuid is not a valid uuid, leave it as character type (varchar or text), but still create that index.

Size and performance considerations for data type varchar vs. uuid:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

All my other suggested changes are minor improvements. Detailed explanation here:

Configuring PostgreSQL for read performance

If you don't need to enforce a particular maximum length I would just use text for all your character columns. But that has hardly any immediate effect on performance. Some of the columns might be converted to a more fitting type (with an actual performance benefit).

Any downsides of using data type “text” for storing strings?
Should I add an arbitrary length limit to VARCHAR columns?

Indexes and UPDATE

So why does that tutorial page say:

  When should indexes be avoided?
  [...]
  - Tables that have frequent, large batch update or insert operations.

That's misleading by omission.
You desperately need the one index on uuid to support the predicate for your update. All other indexes slow your update down, because they require additional work to keep them current after an update. So if you have no use for the PRIMARY KEY on id (to allow FK constraints to it for instance), you can drop that (and make uuid the PK instead - also indexed automatically).


Restricting users to COPY ONLY backups I am looking to deploy log shipping in my production environment. This will mean I will have to manage backup chains which could be disrupted by people taking backups to refresh development environments. If I have a failure and want to use the log backups to restore to a point in time I will also need the backup taken by the developer.

This wouldn't happen if the developers only used COPY ONLY backups. So my question is: Is there a way to restrict users to only be able to perform COPY ONLY backups?

You dont need to have them use COPY_ONLY. Only An intermediate LOG BACKUPS will break the LSN. What you can do is explicitly DENY BACKUP LOG to [user|group] privilege to developers or developer group. Alternatively, just create a ROLE and deny backup log to that role. So all the users in that role will inherit the permissions.

e.g.

USE test_kin
GO
CREATE ROLE [deny_log_backups]
GO
USE [test_kin]
GO
CREATE USER [Kin] FOR LOGIN [Kin]
GO
ALTER USER [Kin] WITH DEFAULT_SCHEMA=[dbo]
GO
use test_kin
GO
DENY BACKUP LOG TO [deny_log_backups]
GO
USE test_kin
GO
EXEC sp_addrolemember N'deny_log_backups', N'kin'
GO

Now test it :

backup database [test_kin]
to disk = 'C:\crap_test\kin_test_full.bak'
with compression, stats =10, init

---- ### success for FULL BACKUP

backup log [test_kin]
to disk = 'C:\crap_test\kin_test_log.log'

 --- $$$ ERROR MESSAGE

Msg 262, Level 14, State 1, Line 3
BACKUP LOG permission denied in database 'test_kin'.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.


Convert timestamp type during INSERT I have a table of raw data as timestamp with timezone, as well as other extraneous columns. I want to convert each timestamp to the interval difference between it and the earliest (smallest) timestamp value in the data set. Then I will call extract(epoch) on that interval to get its length in seconds in double precision. I have set extra_float_digits = 1 so I have 6 decimal place precision in the seconds (so microsecond precision).

I then want to multiply the return from extract(epoch) by 1 million to get the number of microseconds in double precision, which I want to then convert to bigint and save as a column in the table time_pyramid. Along with each record I want to store a 0 and a 1 with it to indicate a layer number and a count number, so time_pyramid will have 3 columns. My query looks like this:

INSERT INTO
  TIME_PYRAMID
SELECT
  0,
  CAST (time_converted AS bigint) AS "time",
  1
FROM (
  SELECT
    EXTRACT(EPOCH FROM ("time" - MIN("time"))) * 1000000 AS time_converted
  FROM
    TIME_RAW
  GROUP BY
    time
  ) x;

However, I get all 0's for my time value, so clearly something is not working. Do I need to reformulate the sub-query, or use another sub-query within the first sub-query? Am I trying to do too much work in those command statements? I haven't been able to get insight from other similar-looking questions, because they incorporate joins, whereas I am just processing and inserting from one single table to another. I don't have any WHERE clause or GROUP BY, and I could group by time, but it shouldn't eliminate any duplicates and would just be for the purpose of using "time" in an aggregate function. On the other hand,

select max("time") - min("time") from time_raw;

gives me an answer of "20:23:33.159866", which I can use to get an integer. So I know that the aggregate functions apply to this data (as it clearly has maximum and minimum timestamp values), I just don't know how to properly formulate the query to get the data I want.

Basically, you need a window function instead of the aggregation. Appending an OVER clause to an aggregate function makes it a window-aggregate function:

SELECT 0, EXTRACT(EPOCH FROM ("time" - MIN("time") OVER ()))::bigint * 1000000, 1
FROM   time_raw
GROUP  BY "time";

And since you want the minimum of the whole table, it's OVER () (nothing between the parentheses).

And you don't need a subquery. Fewer steps, right?

But what do you mean by "time_pyramid will have 3 columns".

Do you mean to create the table? Then use CREATE TABLE AS and apply aliases aliases to all columns in query:

CREATE TABLE time_pyramid AS
SELECT 0 AS layer_number
     , EXTRACT(EPOCH FROM ("time" - MIN("time") OVER ()))::bigint * 1000000 AS time_converted
     , 1 AS count_number
...

Or that's just a typo and you mean to insert into an existing table. Then append target columns to make your INSERT safer (or valid to begin with):

INSERT INTO time_pyramid (layer_number, time_converted, count_number)
SELECT 0, EXTRACT(EPOCH FROM ("time" - MIN("time") <b>OVER ()</b>))::bigint * 1000000, 1
...


Using group by with an array in oracle Is there a way to get an array column while using GROUP BY command?
I am trying to get geometry from oracle database as array of coordinates, but get an error when try to use command GROUP BY

SELECT A.C_ID,
       A.ID,
       COUNT(T.Text),
       LISTAGG(T.TEXT, '/// ') WITHIN GROUP (ORDER BY NULL) text,
       P.GEOMETRY.sdo_ordinates p_geom
FROM G_P_THEME A
LEFT JOIN G_POLYGON P on P.C_ID=A.C_ID and P.ID=A.ID
LEFT JOIN  G_P_TEXT T on P.C_ID=T.C_ID and P.ID=T.ID
WHERE (A.THEME_ID=440 OR A.THEME_ID=47)
Group by A.C_ID, A.ID
Having (COUNT(T.Text) > 1);

it returns an error

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:
*Action:

I know that this is because there is no reference to P.GEOMETRY in GROUP BY command if I add P.GEOMETRY.sdo_ordinates in GROUP BY, result will be

ORA-00932: inconsistent datatypes: expected - got MDSYS.SDO_ORDINATE_ARRAY
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 6 Column: 32

and if there is only P_GEOMETRY in GROUP BY

ORA-22901: cannot compare VARRAY or LOB attributes of an object type
22901. 00000 -  "cannot compare VARRAY or LOB attributes of an object type"
*Cause:    Comparison of VARRAY or LOB attributes of an object type
           was attempted in the absence of a MAP or ORDER method.
*Action:   Define a MAP or ORDER method for the object type.
Error at Line: 6 Column: 32

There is probably something that is needed to use in in SELECT to avoid using it in GROUP BY, but couldn't figure out what

Restrictions on the GROUP BY Clause

  This clause is subject to the following restrictions:

  You cannot specify LOB columns, nested tables, or varrays as part of
  expr.
  The expressions can be of any form except scalar subquery expressions.
  If the group_by_clause references any object type columns, then the
  query will not be parallelized.

So here comes a cheap and dirty workaround, and no, I do not recommend using this in production.

drop table t1 purge;
create table t1(c1 SDO_ORDINATE_ARRAY);
insert into t1 values(SDO_ORDINATE_ARRAY(1, 1));
insert into t1 values(SDO_ORDINATE_ARRAY(1, 1));
insert into t1 values(SDO_ORDINATE_ARRAY(1, 2));
commit;

SQL> select c1 from t1;

C1
------------------------
SDO_ORDINATE_ARRAY(1, 1)
SDO_ORDINATE_ARRAY(1, 1)
SDO_ORDINATE_ARRAY(1, 2)

This obviously fails:

select c1, count(*) from t1 group by c1;
select c1, count(*) from t1 group by c1
                                     *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got MDSYS.SDO_ORDINATE_ARRAY

But this works:

select ora_hash(c1), count(*) from t1 group by ora_hash(c1);

ORA_HASH(C1)   COUNT(*)
------------ ----------
   111675277          1
   390137838          2

Using this:

select
  (
    select c1 from t1
    where ora_hash(c1) = group_by_sq.ora_hash_c1
    fetch first 1 row only
  ) as c1,
  group_by_sq.t1_count
from
(
  select ora_hash(c1) as ora_hash_c1, count(*) as t1_count
  from t1  group by ora_hash(c1)
) group_by_sq
;

C1                               T1_COUNT
------------------------------ ----------
SDO_ORDINATE_ARRAY(1, 2)                1
SDO_ORDINATE_ARRAY(1, 1)                2


Same code in multiple stored procedures I recently joined a company, and I just noticed that many of the stored procedures have the same section of code repeated throughout. I noticed because I was tasked with changing a small section of that code in every SP that it occurred :)

It is a fairly hefty chunk of code, about 30 lines. The code is part of an insert statement, and it basically joins 4 tables together with WHERE/AND conditions that do not really change from SP to SP. It looks similar to below:

...
...
FROM <TableOne>
  INNER JOIN <TableTwo> ON ...
    AND .....
    AND .....
  LEFT JOIN <TableThree> ON ...
    AND .....
    AND .....
WHERE .....
  AND .....
  AND .....
  AND MedicalPlanCode IN ('abc', 'def', 'ghi')

The only part that changes from SP to SP are the values ('abc', 'def', 'ghi')

There also can be different amounts of those values, so some SP's will have 2 values, others will have 5, etc...

Everything I think of changes that section of code to dynamic SQL, and I am not sure if that is worth it. However, the programmer in me hates this situation.

Should I try to implement some form of code reuse? Would it have a ROI? What are my options? I had to go through ~100 stored procedures, which took about an hour.

The 100 SP's are spread out over 20 or so different databases. I do have permissions to create a view.

This should work for you:

CREATE VIEW MyView AS
SELECT <colList>
FROM <TableOne>
INNER JOIN <TableTwo> ON ...
AND .....
AND .....
LEFT JOIN <TableThree> ON ...
AND .....
AND .....
WHERE .....
AND .....
AND .....

Then replace in Procs with:

...
FROM MyView
WHERE
MedicalPlanCode IN ('abc', 'def', 'ghi')


Dependency management by decoupling table name from table key If I share a Google Spreadsheet with someone, I will share a link like this:
https://docs.google.com/spreadsheets/d/1UL0Aii1aaErbUm6pdmZ_D9dsqx2p3BPZ5GHxz7TH37M/edit

This means that I am free to edit the name of the file (for example to "Book - absolute final version really7"), the Google Drive folder it is located in etc. etc. while the link will still work. Would it be possible to simulate something similar in a database? The problem I am trying to solve is the intense pain and risk I have to go through every time I need to rename a column, table, view or stored procedure in the database, or if I want to move it to a different schema. Because of this pain, I currently have all sorts of naming of all these things so you will see a primary key lookup looking like this in my database:

select thisIsAStoredProcedure(acolumn) from a_Table_Name where a_Table_Name_ID = 70;

Needless to say, it is impossible to get fluent in the naming of our database objects. Also, because we are very agile with releasing new updates (uptime is not much of a concern so fast is better than correct at first attempt), we keep adding more objects with wrong naming.

Postgres will help me out in some cases, but it will not fix references to a table inside a stored procedure and the same goes for any references from our any application, or configuration script.

What I am after is that if I write a stored procedure then the stored procedure will use the following while running:

select 1UL0Aii1aaErbU from aErbUm6pdm;

While what I see when I read the stored procedure in my IDE is this:

select a_column from a_table;

Another solution could be to add aliases, so after renaming a_table to b_table then querying a_table would still work, but all queries to the a_table would get logged and I could search for those queries that get run towards the old table. This would mean that there would be no risk involved in renaming a table. The same goes for columns, stored procedures etc. Does such a solution exist?

I realize that the first solution requires major work in the IDE but having such an IDE would mean that I could safely rename tables and columns at will.

Maybe a quick-fix solution to this would be a Mac/Windows hack where if any text editor presents the following text to me: 1UL0Aii1aaErbU then that will always get auto-replaced with a_column no matter where it is presented. I believe AutoHotKey.com could achieve this on Windows.

For tables and columns you could define views. The underlying objects can have whatever abstruse naming you choose. The views would make that human-friendly in the context it is used. When the design changes, create a new view with different names and you're off and running!

For programmables, such as functions and stored procedures, a wrapper presenting intelligible name could simply forward parameters to the underlying objects.

However, I do not think this is a great pattern. A relational table is more than a bucket of strongly typed variables. Columns also encode meaning. Once a column has been christened, say, iso_country_code, no other values should be permitted in that column just because they're two characters long. To repurpose columns makes a mockery of DRI. This is not how an RDBMS was designed to work.

Now, there are many fine dynamic schema persistence layers in existence. These were designed specifically to store items with variable semantics side-by-side. If this is your use-case, use one of these.


Computing Matching Data Points from Fuzzy Timestamps in Postgresql I have a table containing multiple time series of different types. The timestamps of cohesive points from different series do not match exactly (i. e. the difference can be up to an hour).

Schema

Below is the schema with two example series:

CREATE TABLE series (id integer, series_type integer, charttime timestamp,
                     value integer, PRIMARY KEY (id));
INSERT INTO series VALUES (1, 1, '2018-03-01 12:10:00', 40),
    (2, 1, '2018-03-01 13:25:00', 30), (3, 1, '2018-03-01 14:10:00', 50);
INSERT INTO series VALUES (4, 2, '2018-03-01 11:20:00', 2), (5, 2, '2018-03-01 12:15:00', 6),
    (6, 2, '2018-03-01 13:00:00', 7), (7, 2, '2018-03-01 13:45:00', 1);

id |series_type |charttime           |value |
---|------------|--------------------|------|
1  |1           |2018-03-01 12:10:00 |40    |
2  |1           |2018-03-01 13:25:00 |30    |
3  |1           |2018-03-01 14:10:00 |50    |
4  |2           |2018-03-01 11:20:00 |2     |
5  |2           |2018-03-01 12:15:00 |6     |
7  |2           |2018-03-01 13:45:00 |1     |
6  |2           |2018-03-01 13:00:00 |7     |

Goal

The goal is to select one series together with the closest datapoint from another series. For the example dataset the result should be:

charttime           |s1 |s2 |
--------------------|---|---|
2018-03-01 12:10:00 |40 |6  |
2018-03-01 13:25:00 |30 |1  |
2018-03-01 14:10:00 |50 |1  |

First working approach

My current approach is to select the best matching data point from the other series by a subquery:

SELECT l.charttime, l.value AS s1,
    ( SELECT r.value
      FROM series r
      WHERE ABS( EXTRACT( EPOCH FROM l.charttime - r.charttime ) / 3600 ) < 1
            AND r.series_type = 2
      ORDER BY ABS( EXTRACT( EPOCH FROM l.charttime - r.charttime )) ASC LIMIT 1
    ) AS s2
FROM series l
WHERE l.series_type = 1
ORDER BY l.charttime ASC

This does not seem to be the best approach as the dataset is quite huge and thus performing many subqueries slows down the query.

Second approach

A different idea is to self-join the table and filter for close data timestamps:

SELECT l.charttime, l.value AS s1, r.charttime, r.value AS s2
FROM series l, series r
WHERE abs(EXTRACT(EPOCH FROM l.charttime - r.charttime) / 3600) < 1
      AND l.series_type = 1 AND r.series_type = 2

charttime           |s1 |charttime           |s2 |
--------------------|---|--------------------|---|
2018-03-01 12:10:00 |40 |2018-03-01 11:20:00 |2  |
2018-03-01 12:10:00 |40 |2018-03-01 12:15:00 |6  |
2018-03-01 12:10:00 |40 |2018-03-01 13:00:00 |7  |
2018-03-01 13:25:00 |30 |2018-03-01 13:45:00 |1  |
2018-03-01 13:25:00 |30 |2018-03-01 13:00:00 |7  |
2018-03-01 14:10:00 |50 |2018-03-01 13:45:00 |1  |

The problem then are the duplicate data points. Grouping in the first column does not work as the best matching s2 cannot be selected.

Is there a better approach?

In your second approach, with the self join, you could remove duplicates using row_number(),

Partition by l.charttime, order by the time difference and filter for row_number = 1.

I think performance will be horrible, however. Because of the cartesian join this will be an O(size(series 1) x size(series 2)) operation.

Having both l.charttime and r.charttime inside functions may also be causing trouble. Try refactoring to (in pseudo code)

    r.charttime < l.charttime + 3600
and r.charttime > l.charttime - 3600

.. and see how the query plan looks. I presume there's an index on charttime. Without one no approach will be fast. Indeed, two partial indexes, one on Series 1 and one on Series 2 may be even better.


Getting past corrupted binary log "Error in Log_event::read_log_event():" I have a binary log that mysqlbinlog chokes on with the error in the title.

The file itself has much more activity after the cited position.

Doing some basic confirmation it's not all garbage by running it through the strings command shows theres legit traffic until the end of the file when it got rotated.

I've seen a similar post about using hexdump to get past an error related to event too large, but in my case mysqlbinlog chokes to continue to get further information.   I'm not familiar enough with the binary format to look for what might be a position of a next event it would recognize.

It gives a starting position it can't get past so I have a script running to basically mysqlbinlog --start-position=X incrementing X by one until it returns with a 0 exit code but that looks like it's going to take a month to completely get through everything at this rate.

I tested the POC of this idea on "good parts" by starting it at weird offsets and it returned correctly at the next one it found w/o error.

I'm running percona 5.6.20 for this instance.

I realize this report might be lacking in information needed to answer the question so I'm happy to edit with comment requests as needed.

Well much to my surprise the script I mentioned did find a good starting position and I was able to successfully recover a large portion of the binary log after the problem point.

Not the most elegant script but in case it's of use to anyone else.  Note it is very slow restarting a new process to scan byte by byte but got me results.

And yes I recognize the potential of infinite looping by never finding anything, might want to add additional check based on start position vs filesize

#!/bin/bash
BINLOG=$1
START=$2

RET=1
while [ $RET -eq 1 ]; do
    # This is relying on the mysqlbinlog found in your path
    # You'll want to make sure this resolves to a binary version matching the mysqld that generated the binlog
    mysqlbinlog --start-position=$START $BINLOG > /dev/null 2>&1
    RET=$?

    START=$(( START + 1 ))
    if [[ $(( START % 1000 )) -eq 0 ]]; then
        echo $START
    fi
done
GOODSTART=$(( START -1 ))

echo "FOUND GOOD START AT $GOODSTART"

# Saving it to a file in case youre running it overnight, script is very slow.
echo $GOODSTART > ${BINLOG}.goodstartposition


Dropped Index during Database Engine Tuning Advisor I was analyzing a query using "Database Engine Tuning Advisor". During this time the database started to get blocking, long running queries and ended by restarted the instance. When I checked after that I found a few drop index commands had been executed.

drop index [dbo].[Profile].[_dta_index_Profile_7_1563152614__K15_K1]

Why did this happen? The query I was tuning was not on profile table!
How can I check if this index was already in the system before or was created during the analysis?

The indexes that were created and dropped by DTA were most likely hypothetical indexes - ones that DTA creates while running to perform its analysis and should (but doesn't always) delete once done.

These hypothetical indexes can be created even if you weren't looking at tuning that specific table! I'd recommend you query some of the system DMVs directly to find out about missing indexes. This avoids the pesky behavior of DTA and provides some more granular insights into your index problems.

You can use the below query from SQL Server Central to detect any remaining hypothetical indexes from DTA that should be removed.

/*
      -----------------------------------------------------------------
      Hypothetical Indexes
      -----------------------------------------------------------------

      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

      -----------------------------------------------------------------
*/
-- Set database context
USE master;
GO

-- Declare variables
DECLARE@EmailProfile VARCHAR(255)
DECLARE@EmailRecipient VARCHAR(255)
DECLARE@EmailSubject VARCHAR(255)

-- Set variables
SET@EmailProfile = 'DBA'
SET@EmailRecipient = 'Chris@SQLServer365.com'
SET@EmailSubject = 'ALERT - Hypothetical Indexes found on ' + @@SERVERNAME

-- Drop temporary table if exists
IF OBJECT_ID('tempDB.dbo.#HypotheticalIndexDropScript') IS NOT NULL
    DROP TABLE #HypotheticalIndexDropScript;

-- Create Temporary Table
CREATE TABLE #HypotheticalIndexDropScript
    (
      DatabaseName VARCHAR(255) ,
      HypotheticalIndexDropScript VARCHAR(4000)
    );

INSERT  INTO#HypotheticalIndexDropScript
        EXEC sp_msforeachdb 'USE [?]; SELECT  DB_NAME(DB_ID()), ''USE '' + ''['' + DB_NAME(DB_ID()) + ''];'' + '' IF  EXISTS (SELECT 1 FROM sys.indexes  AS i WHERE i.[object_id] = '' + ''object_id('' + + '''''''' + ''['' + SCHEMA_NAME(o.[schema_id]) + ''].'' + ''['' +  OBJECT_NAME(i.[object_id]) + '']'' + '''''''' + '')'' + '' AND name = '' + '''''''' + i.NAME + '''''''' + '') ''
       + '' DROP INDEX '' + ''['' + i.name + '']'' + '' ON '' + ''['' + SCHEMA_NAME(o.[schema_id]) + ''].'' + ''['' + OBJECT_NAME(o.[object_id]) + ''];'' AS HypotheticalIndexDropScript
FROM    sys.indexes i
        INNER JOIN sys.objects o ON o.[object_id] = i.[object_id]
WHERE is_hypothetical = 1'

-- Check for hypothetical indexes
IF EXISTS ( SELECT  1
            FROM    #HypotheticalIndexDropScript )
    BEGIN
        DECLARE@tableHTML NVARCHAR(MAX);
        SET@tableHTML = N'' + N'
' + N'
'
            + N'DatabaseName	'
            + N'HypotheticalIndexDropScript	' + N'
'
            + CAST(( SELECT td =DatabaseName ,
                            '' ,
                            td = HypotheticalIndexDropScript ,
                            ''
                     FROM   #HypotheticalIndexDropScript
                   FOR
                     XMLPATH('tr') ,
                         TYPE
                   ) AS NVARCHAR(MAX)) + N'
';

            -- Email results
        EXECmsdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,
            @recipients = @EmailRecipient, @subject = @EmailSubject,
            @body =@tableHTML, @body_format = 'HTML';
    END
    GO


Status for a service broker queue Looking at the sys.service_queues DMV, I see that there are columns called "is_enqueue_enabled" and "is_receive_enabled".  However, in the "ALTER QUEUE" DDL, there only seems to be a "STATUS" that can be toggled.  Similarly, in the SMO for a queue, there seems to be only a "IsEnqueueEnabled" property.  My question is: will it always be the case that is_enqueue_enabled and is_receive_enabled have the same value?  If not, how does one set them independently?

The original design called for separate enqueue_enabled (allow SEND) and dequeue_enabled (allow RECEIVE). The added complexity (as SSB wouldn't already be complex enough!) did not warrant the benefits, so in the end it was settled for a less granular 'queue enabled'. The metadata though was intentionally left split in order to enable the separation in future. Now, even if there is no plan to split the enable/disable in SEND/RECEIVE, the view has to stay like that because of back compat issues.

From your point of view: the two values should always be in sync (both 0 or both 1). Altering the enabled/disabled status on the queue should change both values at once.


Debugging why postgres is snowballing with system cpu usage I am having an issue where my postgres database which run on a dedicated machine is getting hammered with kernal level cpu usage. This essentially takes down the web service that run on this database.

This is a dual 6 core machine with 128 GB of ram.

max_connections = 609
shared_buffers = 23923MB
work_mem = 78MB
maintenance_work_mem = 4784MB
wal_buffers = 16MB
checkpoint_segments = 10
effective_cache_size = 71769MB

I have hit a wall in debugging this issue trying to correlate this with any cron tasks or heavy work that utilizes the db.

When I am looking at htop while this is occurring I see a lot of process like:

CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) authentication
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) authentication
CPU 99-100% postgres: user database ip(pid) idle
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) SELECT
CPU 99-100% postgres: user database ip(pid) startup
...

pg_stat_activity is not swamped with more queries than normal or queries that take a particularly long time to run when this issue is not occurring. All queries during an occurrence are taking 10's of seconds to process each. Even queries that just check to see if the table exists or not.

I would love it if I could be pointed in a good direction to further debug this issue.

It has been about 24 hours now since I have implemented the change that fixed this, so I think it is safe to call. I will update this however if this kind of spike happens again.

In the system logs we were getting this message and I had my data center replace the RAM in the machine.

Mar  3 18:40:24 hubble kernel: sbridge: HANDLING MCE MEMORY ERROR
Mar  3 18:40:24 hubble kernel: CPU 6: Machine Check Exception: 0 Bank 5: 8c00004000010092
Mar  3 18:40:24 hubble kernel: TSC 0 ADDR 1087cc7780 MISC 20400e0e86 PROCESSOR 0:206d7 TIME 1393900824 SOCKET 1 APIC 20
Mar  3 18:40:24 hubble kernel: sbridge: HANDLING MCE MEMORY ERROR
Mar  3 18:40:24 hubble kernel: CPU 6: Machine Check Exception: 0 Bank 5: 8c00004000010092
Mar  3 18:40:24 hubble kernel: TSC 0 ADDR 1087cc7780 MISC 20400e0e86 PROCESSOR 0:206d7 TIME 1393900824 SOCKET 1 APIC 20
Mar  3 18:40:24 hubble kernel: EDAC MC1: CE row 9, channel 0, label "CPU_SrcID#1_Channel#3_DIMM#0": 1 Unknown error(s): memory read on FATAL area : cpu=6 Err=0001:0092 (ch=2), addr = 0x1087cc7780 => socket=1, Channel=3(mask=8), rank=0

After the RAM replacement, the issue has not occurred since.

Also the IOwait in the CPU graph has gone a way since the ram change. Previously it was my assumption that his was just the profile of the machine since I had more than a year of uptime with this IOwait being detected my CPU profiling.


Why is a Full Database Backup (as opposed to a level 0 backup) required in Oracle after switching to ARCHIVELOG mode? The Oracle 12c 2 Day DBA manual states:

  While whole database backups can be an important element in your
  overall backup strategy, they are also a required step in some
  situations, such as when you enable or disable ARCHIVELOG mode.

(I'm assuming that a "whole" database backup is the same as a "full" database backup as mentioned in the Concepts section.)

In the section "Enabling Archiving of Redo Log Files" the manual states:

  You cannot use backups from before the switch to ARCHIVELOG mode to
  restore and recover the database to a point in time after the switch.
  Thus, if you do not immediately make a backup after switching, then
  you are running your database without a valid backup.

Is the first excerpt of the manual wrong? Is the requirement that you need -any- type of backup? Or does it really need to be a full backup, as opposed to a level 0 backup? If so, why the difference?

You cannot use backups from before the switch to ARCHIVELOG mode to restore and recover the database to a point in time after the switch.

Looking at this quote it makes sense. If you enable archivelog on a database where it was not enabled, then any backup that you have before archive log was enabled can't be used to do a point in time recover, since you have no archive logs to rolll forward from the backup. You just have the backups and can restore to whatever warm backups that you have.

If you take a database that has archive log enabled and disable archive log, then going forward you can't do point in time recover. You can only restore to whatever backups that you have. But any warm backup that you have from when the database was in archive log can be restored and rolled as far forward as you have archive logs.

As far as I know both a warm full backup and incremental level 0 backup are full backups and you should be able to use either to do a full point in time restore provided that the databases were in archive log mode during the backup. Probably the biggest difference between a full backup and an incremental level 0 backup is that the incremental level 0 backup will update the block change tracking file, but I'm not sure that a full hot backup will do that.

But if you want to know for certain take a database that is not in archive log mode, put it in archive log mode, do an incremental level 0 backup, drop the database and restore it. You will know for certain then.


Query keeps on giving me 'unknown column' issue I've researched quite a lot for the past few days about this error that I'm getting but I cannot really fix it so I was hoping somebody could help me. The query keeps on telling me:

ERROR 1054 (42S22): Unknown column 'updated_at' in 'field list'

This is the query:

SELECT  TIME_TO_SEC(SUM(TIMEDIFF(updated_at, created_at))) AS total FROM
        ( SELECT  DISTINCT date(l.created_at),
                ( SELECT  MIN(created_at)
                    FROM  logs
                    WHERE  updated_at BETWEEN l.created_at AND l.updated_at
                ) AS TIME_ENTER,
                ( SELECT  MAX(updated_at)
                    FROM  logs
                    WHERE  created_at BETWEEN l.created_at AND l.updated_at
                ) AS TIME_EXIT
            FROM  logs l
        ) AS whatever
    WHERE  updated_at <> CONVERT(updated_at USING ASCII)
    GROUP BY  date(created_at);

and the description of my table would be:

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| job_id      | int(10) unsigned | NO   | MUL | NULL    |                |
| timecard_id | int(10) unsigned | NO   | MUL | NULL    |                |
| event_id    | int(10) unsigned | NO   | MUL | NULL    |                |
| duration    | int(11)          | YES  |     | NULL    |                |
| gap         | int(11)          | NO   |     | 0       |                |
| created_at  | timestamp        | YES  |     | NULL    |                |
| updated_at  | timestamp        | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

I tried looking for unprintable characters but I didn't succeed

Any help would be appreciated, thanks in advance!

The problem is that your select in the from part:

SELECT DISTINCT date(l.created_at),
(SELECT MIN(created_at) FROM logs WHERE updated_at BETWEEN l.created_at AND l.updated_at) AS TIME_ENTER,
(SELECT MAX(updated_at) FROM logs WHERE created_at BETWEEN l.created_at AND l.updated_at) AS TIME_EXIT
FROM logs l

does not return the field updated_at.


Postgresql perl : "NOTICE: elseif should be elsif at line ..." It's not the first time I get this strange NOTICE when adding perl functions in Postgresql :

NOTICE:  elseif should be elsif at line 11.

This happens at CREATE FUNCTION as well as when using the function.
Of course I tried to change with elsif but then it fails with syntax error.

Any advice would be appreciated (even if I can cope with that NOTICE)

This is my code:

CREATE FUNCTION hds_updatewebservers_products(character varying)
   RETURNS boolean
AS
$_$
   my $product_ref = @_[0];
   # Launch update
   $checkexitcode = system ("sudo -u dbliveud /home/dbliveud/scripts/maj/launch-all.sh " . $product_ref . " ");
   if ($checkexitcode > 0) {
     # Error while updating
     return false;
   } elseif {
     # Ok update done
     return true;
   }
$_$
LANGUAGE plperlu STRICT;

I can't reproduce your error, it works fine on my PostgreSQL 9.5 on Linux. What version and platform are you running?

Note that your function can be exploited really badly, e.g.

select hds_updatewebservers_products('1; rm -rf /')

Why are you using elseif? Why not just else? See perl's documentation on elseif

Finally, you can simply (and elegantly):

CREATE FUNCTION hds_updatewebservers_products(character varying) RETURNS boolean AS $_$
  my $product_ref = @_[0]; # Launch update

  $product_ref =~ /^\w$/ or die 'Invalid product ref!'; # added for safety

  $checkexitcode = system ("sudo -u dbliveud /home/dbliveud/scripts/maj/launch-all.sh " . $product_ref . " ");

  # positive value means error while updating, otherwise update done OK
  return !($checkexitcode > 0);
$_$ LANGUAGE plperlu STRICT;


How to summate weekly totals from a postgres date range type per user I have the following table:

Schedules
+----+---------+------------+-------+--------------------------+
| id | user_id | project_id | hours | date_range               |
+----+---------+------------+-------+--------------------------+
|  1 |       1 |          1 |     4 | [2019-05-01, 2019-05-08) |
+----+---------+------------+-------+--------------------------+
|  2 |       2 |          1 |     8 | [2019-04-20, 2019-05-20) |
+----+---------+------------+-------+--------------------------+
|  3 |       1 |          2 |     2 | [2019-05-05, 2019-05-12) |
+----+---------+------------+-------+--------------------------+
| ...etc       |            |       |                          |
+--------------+------------+-------+--------------------------+

What I was hoping to accomplish with SQL was to get a summation of total hours per week given a specific date range input, for example date_start: 2019-05-05, date_end: 2019-05-11.

For the week of 2019-05-05 to 2019-05-11, any Schedule that has a date_range that has overlapping dates would get summated by project_id and by user_id.

Example: Row 1 from the above table has a date_range of [2019-05-01, 2019-05-08) so it would have 2019-05-05, 2019-05-06, 2019-05-07 (2019-05-08 omitted because exclusive )) dates that match the date_start/date_end criterion.

Since the hours are 4 for that row, the given total would be 4 * n days = 12 hours total

The date_start/date_end can be arbitrarily large, so say it was over a year range, every date_range would be added by week over the given range.

Potential expected output (open to suggestions):

| week_start |  week_end  | project_id | user_id | total_hours |
| 2019-05-05 | 2019-05-11 |          1 |       1 |          12 |
| 2019-05-05 | 2019-05-11 |          1 |       2 |          56 |
| etc ...    |            |            |         |             |

I'm not quite sure how to structure a query to generate matching date_rage days and multiply them out per user.

Can someone point me in the right direction?

Build week borders table (week_start, week_end) in the range from MIN(date_start) to MAX(date_end) in CTE. Then cross-join it with your table, select only overlapped pairs and calculate values you need. - akina


SSRS Report Server Database Locations I've recently inherited management of a large number of SSRS environments.  I'm attempting to generate a list of all the database server/instance/names for each of the SSRS databases (not the data sources).

These are stored in the rsreportserver.config files, but they are encrypted and I'm not sure how to extract them.

Is there an easy way to get this information from a large number of instances?

If there is a way to do it via command line, I can easily push the command to all servers and collect the output.

Microsoft Assessment and Planning Toolkit should do the trick.

  MAP performs a detailed analysis of hardware for migration to Windows 8, Windows 7, Windows Server 2012, Windows Server 2008 R2, SQL Server 2012, SQL Server 2008 R2, Microsoft Office 2010, and Office 365.

It specifically mentions SQL 2012 at some point, but I guess the same would work for 2008 R2 aswell:

  Discovery of SQL Server 2012 databases, instances, and selected characteristics.

And here it says:

  Detailed reporting:
  Covers specific SQL Server instances that include
  component name, version, edition, and more. Includes wide-ranging
  details of databases and server instances that can be used for
  consolidation.


Permission Denied Copy I am a beginner learning how to load data and I can't figure out why I can't load this data into postgres.  I am using postgres 8.4 on MAC OS.

copy tname from '/Users/Shared/folder/file.cxv' DELIMITERS ',' CSV;

  ERROR: could not open file "/Users/Shared/folder/file.csv" for
  reading: Permission denied.

I checked and have reading/writing privileges for this file.

in general, you should use \copy where you can in psql instead of COPY since that wraps COPY FROM STDIN.  Where you want to copy from a file, you can wrap that yourself in your own application (specifics dependent on language but see the Pg docs).

The alternative is to put files somewhere that the Postgres user can access them, like /tmp/ and COPY from there.  Of course this means the files must be present on whatever system the db server is running on, rather than the system the client is running on if using the previous paragraph's methods.  Chances are your problem is either the immediate directory the file is in or a parent directory.  Keeping these in a spool directory which is more general will help manage this problem.


Strategies for summary data in parent record based on detail data? I have parent-child relationship as shown below. I need to find the first order date for each customer. Obviously, writing a query to do this is fairly straight-forward but it does require reading all the records in CustomerOrders.

I've considered the following optimization approaches:

Add a FirstOrderDate field to CustomerMaster. Program the application to set the value when the customers orders for the first time.
Same as #1 but use a trigger in the database instead of using the application.
Some other possibility that is better than the first two.

What is the best approach to handling this?

CustomerMaster
    CustomerID
    Customer Name
    .
    .
    .

CustomerOrders
    OrderID
    CustomerID
    OrderDate
    .
    .
    .

As for performance, there are 9.7 million records right now. The query takes 34 seconds. The data is growing all the time. 34 seconds is too slow right now and I expect it to be double that in two years

I would suggest doing it in the application rather than in the database with a trigger, only because it's not integral to the structure of the database; rather it's for performance reasons and I don't think doing it in the database will actually improve performance of the query that's running (it might slightly improve performance of the actual insert/update statements, but that's something that profiling could tell you better than I).

Doing it in the application would also give you more control of performing this operation optionally. It sounds like you would want this to always happen, but I have seen situations where testing would have been made easier if such logic were moved out of triggers so they could be turned on and off for testing purposes.


Service privileges for Patch Download? (Oracle 11gR2) I first asked this question on stackoverflow, but someone pointed out that this might be the appropriate site. Hope I am doing this right.

To perform tests in advance of an upgrade from Oracle 11gR2 to 12c on our production systems, I tried to update the database of a developer virtual machine (Windows 7). The current Oracle Database version of this old developer machine is 11.2.0.1.0.
The setup file of Oracle 12c then told me to patch to 11.2.0.2, 11.2.0.3 or 11.2.0.4 first. So I tried to find and download the Patch to 11.2.0.4 for Windows x64. The official Oracle page only lists 11.2.0.1, so I did a quick search that lead me to this page.

I am used to be forced to login to download stuff from Oracle, but the second page tells me I need special support privileges. Seriously? Oracle allows me to setup a developing machine with their enterprise editions but I need support privileges to download a patch to the most recent version of Oracle 11?

Did I miss something? How am I supposed to test the upgrade process on my developing machine(s)?

Thanks for any help!

Short version: install the 12c software, create an empty database, and transfer the data with Data Pump. This way you can skip 11.2.0.1+ versions.

Long version: OTN Developer License Terms

In this, there is the following:

  No Technical Support
  Unless Oracle support for the Programs, if any,
  is expressly included in a separate, current support agreement between
  You and Oracle, Oracle’s technical support organization will not
  provide technical support, phone support, or updates to You for the
  Programs provided under this Agreement.

So no, you can not download updates officially with the OTN Developer License.

A valid support contract is needed for that. Actually a support contract costs extra on top of the database license, so there are companies that choose not to pay for it (and not to install patches...).


MySQL v5.1.73 - can the binlog_format betweem Master & slave be different? We are in the process of testing MySQL replication before using it in production. The MySQL is being setup by Puppet in our case and I noticed that the binlog_format setting is different between master and slave.

On master:

mysql> show variables like 'binlog_format' \G
*************************** 1. row ***************************
Variable_name: binlog_format
        Value: ROW

On slave:

mysql> show variables like 'binlog_format' \G
*************************** 1. row ***************************
Variable_name: binlog_format
        Value: STATEMENT

Once in a while, the replication breaks with an error similar to the following:

  Last_SQL_Error: Could not execute Delete_rows event on table
  test.pruning; Can’t find record in ‘pruning’, Error_code: 1032;
  handler error HA_ERR_KEY_NOT_FOUND; the event’s master log
  mysql-bin.000002, end_log_pos 1448

We don't know for sure whether this was caused due to the different binlog_format setting between the master & slave.

Could the above error potentially be caused by this inconsistent setting? Should I change the slave to "row" based format? It is hard to reproduce the error so I am looking for the best practice we should be following and we are very new to MySQL.

The Error Message you received

  Last_SQL_Error: Could not execute Delete_rows event on table test.pruning; Can’t find record in ‘pruning’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000002, end_log_pos 1448

This message can come up when doing a DELETE query with row-based replication. Unfortunately, it is one of the disadvantages to row-based replication:

  Disadvantages of row-based replication

  RBR can generate more data that must be logged. To replicate a DML statement (such as an UPDATE or DELETE statement), statement-based replication writes only the statement to the binary log. By contrast, row-based replication writes each changed row to the binary log. If the statement changes many rows, row-based replication may write significantly more data to the binary log; this is true even for statements that are rolled back. This also means that taking and restoring from backup can require more time. In addition, the binary log is locked for a longer time to write the data, which may cause concurrency problems.

I have addressed something similar (error 1032 in my answer to
MySQL master master replication, safe to delete rows on both masters?) where doing a DELETE query was the issue.

In your case, since the Slave is using STATEMENT, the row change is expected to be a precise match of PRIMARY KEYs between Master and Slave. On many occassions, it may not be the case. That is why error 1032 exists.

To play it safe,

use binlog_format=ROW on both Master and Slave
use binlog_format=MIXED on both Master and Slave
use binlog_format=STATEMENT on both Master and Slave
You could experiment with Master MIXED and Slave ROW. At the very least, avoid STATEMENT on the Slave if the Master is not STATEMENT.


Aggregate count over variable number of json keys How can one get a list of json objects like this:

[
    {"band": "beatles", "bass": 1, "drums": 1, "guitar": 2},
    {"band": "metallica", "bass": 1, "drums": 1, "guitar": 2, "vocals": 1}
]

from this data on Postgresql 9.4?

drop table if exists people;
create temp table people(pk serial primary key, band character varying(15), fname text, tag character varying(15));

insert into people(band, fname, tag)
values
('beatles', 'john', 'guitar'),
('beatles', 'paul', 'bass'),
('beatles', 'ringo', 'drums'),
('beatles', 'george', 'guitar'),
('metallica', 'james', 'vocals'),
('metallica', 'lars', 'drum'),
('metallica', 'kirk', 'guitar'),
('metallica', 'robert', 'bass'),
('metallica', 'marty', 'guitar');

--Summary table
select band, tag, count(*)  as ct from people  group by band, tag order by band, tag

This is my closest attempt:

SELECT band, tags from (
    select band, row_to_json(row(tag, count(*))) as tags from people group by band, tag
) t

-- Which leads me towards:
SELECT band, array_agg(tags) from (
    select band, row_to_json(row(tag, count(*))) as tags from people group by band, tag
) t
GROUP BY t.band;

--This is close, but from *{f1:v1, f2:v2}* I need *{v1:v2}*

Here is how I arrived to that one:

select array(SELECT row_to_json(r) from (
    SELECT band, array_agg(tag) from people group by band
    ) r
)

select row_to_json(r) from (
    SELECT band, array_agg(tag), count(*) from people group by band
    ) r;

select row_to_json(r) from (
    SELECT band, json_agg(tag), sum(ct) from (
        select band, tag, count(*)  as ct from people  group by band, tag
    ) x group by x.band
) r;

The only one thing which I not found is the function/operator for concatenation of JSON objects, so lets create it:

create function json_concat(in json, in json) returns json as $$
  select
    (
      left($1::text, length($1::text)-1) || ', ' ||
      right($2::text, length($2::text)-1)
    )::json;
$$ immutable language sql;

After this the final query would be:

with t as (
  select band, tag, count(*) as ct
  from people
  group by band, tag
  order by band, tag)

select json_agg(x)
from (
  select json_concat(json_build_object('band', band), json_object_agg(tag, ct)) as x
  from t
  group by band) tt;

and result is

[{"band" : "metallica",  "bass" : 1, "drums" : 1, "guitar" : 2, "vocals" : 1 }, {"band" : "beatles",  "bass" : 1, "drums" : 1, "guitar" : 2 }]


SQL Server: Look for column headers I'm reviewing a database server, the problem is that there are about 40 databases with each hundreds of tables. I'm looking for tables with a column name containing a specific word.

Is there a way I can do a search to identify these tables?

You want to be able to query sys.columns in each database. To do this, try:

select 'union all select ''' + name + ''' as dbname, s.name + ''.'' + o.name + ''.'' + c.name collate database_default as colname
        from ' + quotename(name) + '.sys.columns as c
        join ' + quotename(name) + '.sys.objects as o
        on o.object_id = c.object_id
        join ' + quotename(name) + '.sys.schemas as s
        on s.schema_id = o.schema_id
        where c.name like ''%name%''  collate database_default '
from sys.databases;

Then copy the column you get back into a new window, strip the leading "union all", and run it. :)


How to store tax for a Product when tax rate for a Product can change and tax-exempted Products Product

id
name
price

Tax

id
name
rate

Product_Tax

Product.id
Tax.id
effective date
expiry date

Is this a good design?

How to store Products that are tax-exempted?

Also, how to handle the case when the value of Tax changes for example VAT rate changes from 20% to 30% --> Should I store the value of rate in the Product_Tax table in that case

Don't put tax exempt entries in the Product_Tax table.

Design is good (already endorced by me).


Casting fails on subquery result I'm in a bit of a pickle, I have to perform a query on a database with rather poor data quality. I am trying to evaluate a field which contains a date, but which was defined as a nvarchar. I thought I would be able to cast the value to an integer but this fails as there also non-date values in the date field (random letters and signs,etc...).

What I decided to do is as follows:

SELECT q.foo, cast( q.bar as int)
FROM ( SELECT foo,bar FROM table where ISNUMERIC(bar) = 1) as q
WHERE q.bar > 20140401

But this still crashes stating conversion failed on the nvarchar value '.       '. I expected that all values would  be castable to int as they were numeric?

You can wrap a CASE inside the CAST(), so the CAST() is only performed on valid numeric values - the other ones will result in NULL unless you put an ELSE in there somewhere.

Also, your conversion error probably stems from the WHERE clause, where q.bar is implicitly converted to int in order to compare it to 20140401 (which is an int).

This will probably solve your problem:

SELECT q.foo, CAST((CASE WHEN q.bar NOT LIKE '%[^0-9]%' THEN q.bar END) AS int)
FROM ( SELECT foo,bar FROM table) as q
WHERE q.bar > '20140401';


My MongoDB Atlas Operations graph has doubled in the last few hours. How do I go about debugging this? I noticed this graph in my MongoDB Atlas dashboard:

I'm not sure what is causing the increased Disk IOPS and Operations counts... Can I use the oplog to figure out what is causing this massive increase in operations?

My MongoDB Atlas Operations graph has doubled in the last few hours.
  How do I go about debugging this?

As per MongoDB documentation here You can use a third-party application to view and analyze performance metrics that Atlas collects about your cluster.

At this time, you can either build a monitoring integration using the Atlas API or integrate Atlas with Datadog.

Build Monitoring Integrations with Atlas API

You can build a monitoring integration using the Atlas API monitoring and logs endpoints.

Integrate Atlas with Datadog

You can configure Atlas to send metric data about your project to your Datadog dashboards.

  Note: Datadog integration is only available on M10+ clusters.

For further your ref here


MySQL 5.5 to 5.6 Upgrade. Will data remain the same? We are considering to upgrade to MySQL 5.6 this summer. I would like to know if our data will be changed in any way once we perform the upgrade?

I've been reading and I found that mysql_upgrade upgrades the system tables only. I tested it and it seems to be true. But I just want to make sure that our data will remain the same after the upgrade. I don't want to have problems with our apps after we do the upgrade. Thanks!

As noted, mysql_upgrade will change the system tables to include any new columns required.

MySQL 5.6 includes support for microseconds in DATETIME and TIMESTAMP, and as such uses a different format on disk for storage.  Conversion to the new format does not happen as part of mysql_upgrade, but will happen on  ALTER TABLE or OPTIMIZE TABLE and in which case you will not be able to start MySQL 5.5 and use this data directory.


Failover timeout problem I'm looking for an additional bit of help with regard to some problems I'm looking at for one of my company's clients. Basically I have two related problems with regard to a 2-node active/active cluster hosting an instance of 2008 R2 on each instance.

One of the instances, normally on node#2, is at RTM. And when failing over to node #1 there is a time-out bringing the SQL Server service on-line, however once the time-out has, er, 'timed out', the service can actually be brought on-line OK manually. And looking at the Log there are errors of the form

  ‘Login failed ..... Reason: Server is in script upgrade mode…..’.

At first I thought this was the result of a failed attempt at installing SP1 however I'm now not so sure. SP1 has definitely been installed on both nodes and the other instance, normally on node #1, is at SP1. I assume this was following the recommendation to install the service pack on the 'inactive' node, failing over and repeating the process. However I'm having difficulty interpreting the installation logs to see if just one instance has been updated or both, with one failing. So I was hoping someone could help me with that in terms of what log file I should be looking at.

Also, what might be the meaning of the 'script upgrade mode' errors? Does this indeed sound like a failed SP1 upgrade or is something else at play here? Curiously the problem only occurs in one direction - from node #2 to node #1. When failing back to node #2 the SQL Server service comes back on-line without the need for any manual intervention.

Had forgotten about updating this one after getting help from Microsoft Support. Turns out that the build number on one nodes was slightly different to the other due to a Security Patch that had only been applied to one node, possibly due to a Windows Update.


What reasons can lock an Oracle account There could be a lot of reasons why an Oracle account can be locked. When an account is locked and the users is asking for our help, following could be the possible cause of account lock:

A user with appropriate privileges locks an account manually (aka ALTER USER user_name ACCOUNT LOCK)
Password parameters from profile

User exceeds FAILED_LOGIN_ATTEMPTS
PASSWORD_LIFE_TIME is exceeded, or both that and PASSWORD_GRACE_TIME is exceeded.

Are there any other places we should see, or any other ways an account could get locked?

Provided audit trail is turned on, then I prefer to use the following to help track down login failures (which is usually the cause of locked accounts):

select * from dba_audit_trail where returncode in (1017, 28000) order by timestamp desc;

returncode is the ORA- error that would be returned from the database: 1017 is "invalid usercode or password" and 28000 is "account is locked".

This view provides valuable information such as the time the login failures occurred, the computer where the error originated from, as well as the OS user.

If you do not have audit_trail turned on, which you can verify with:

show parameter audit_trail;

then obviously, the above query will not help with past login failures, but you may want to consider turning it on to help with future failures.  Also, keep in mind that this change requires a bounce of the database to go into effect.

You can refer to this document in order to set this environment variable to a value that is appropriate to your environment: http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams017.htm

You would set this parameter as follows:

alter system set audit_trail='DB' scope=spfile;

Keep in mind that if you do turn on audit_trail, consider periodically clean out older records out of the audit table or the table can grow to be pretty unwieldy.  There is an informative blog that talks about that here: http://www.oradba.ch/2011/05/database-audit-and-audit-trail-purging/

As for another possible cause:

If you are running in a distributed environment (multiple databases connected by database links), then proxy database links (null user/password) could be your problem since such database links attempt to login to the destination database with the credentials that are passed from the source database.

If the credentials are different between the source and the destination, ORA-1017 is returned which counts against the login attempts on the destination DB (provided the usercode was the same, and just the passwords are different).

In addition, if the source DB user is logged in via a proxy user, then proxy DB links will ALWAYS fail with ORA-1017 regardless of whether or not the passwords match.


Updating indexes and stats script I am having the following errors with the script below

  Msg 102, Level 15, State 1, Line 44
  Incorrect syntax near '?'.

  Msg 319, Level 15, State 1, Line 47
  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Can anyone help to resolve the issue, it also does not seem to loop around each db

DECLARE @command    NVARCHAR(MAX)
CREATE TABLE #worktable
(
    [Database]  SYSNAME
    ,SchemaName SYSNAME
    ,ObjectName SYSNAME
    ,StatsName  SYSNAME
    ,ColName    SYSNAME
    --,Command  VARCHAR(500)
)

exec [master].[sys].sp_MSForEachDB @command1="use [?]"
INSERT INTO #worktable
    SELECT
        '[?]'                                               AS [Database]
        ,sch.[name]                                     AS [SchemaName]
        --,OBJECT_NAME(o.[object_id],DB_ID(''[?]''))            AS [ObjectName]
        ,o.[Name]                                       AS [ObjectName]
        ,s.name                                             AS [StatsName]
        ,c.name                                             AS [ColName]
        --,''COMMAND''                                      AS [Command]
        FROM sys.stats AS s

    INNER JOIN sys.stats_columns AS sc
        ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id

    INNER JOIN sys.objects o
    INNER JOIN sys.schemas AS sch
        ON o.schema_id = sch.schema_id
        ON s.[object_id] = o.[object_id]
        AND o.is_ms_shipped = 0

    INNER JOIN sys.columns AS c
        ON sc.object_id = c.object_id AND c.column_id = sc.column_id

select * from #worktable
drop table #worktable

exec [MASTER].[sys].sp_MSForEachDB
INSERT INTO #worktable
    SELECT  'Test'      AS [Database]
        ,OBJECT_SCHEMA_NAME(sch.name ,DB_ID(''[?]''))   AS [SchemaName]
        ,OBJECT_NAME(ss.[object_id],DB_ID(''[?]''))     AS [ObjectName]
        ,ss.[name]                          AS [StatsName]
        ,''UPDATE STATISTICS ''+''[?]''+''.''+OBJECT_SCHEMA_NAME(ss.[object_id],DB_ID(''[?]''))+''.''+OBJECT_NAME(ss.[object_id],DB_ID(''[?]''))+'' ''+ss.[name]+'' WITH FULLSCAN;'' AS Command

    FROM [?].sys.stats ss

    CROSS APPLY [?].sys.dm_db_stats_properties(ss.[object_id],ss.stats_id) sp

    WHERE   (           (sp.last_updated < DATEADD(hh,-6,GetDate()))
            OR      (sp.[modification_counter] > (0.1*sp.[rows]))
            OR      (sp.[rows_sampled] <> sp.[rows])
        )

    ORDER BY sp.[rows] ASC -- does the small stuff first, makes the difference earlier'

DECLARE cmdlist CURSOR FOR SELECT Command FROM #worktable

-- Open the cursor.
OPEN cmdlist

-- Loop through the partitions
WHILE (1=1)
   BEGIN
        FETCH NEXT FROM cmdlist
           INTO @command

        IF @@FETCH_STATUS < 0 BREAK

        --EXEC (@command);

        PRINT N'Executed: ' + @command
    END;

CLOSE cmdlist
DEALLOCATE cmdlist

DROP TABLE #worktable
GO

My honest opinion is that - Don't reinvent the wheel as there is cost to reinvent it.

Highly suggest you to use Ola's index and stats maintenance solution.

It is very flexible and is highly awarded and tested. Also it does not use any of ms_foreach stuff that you have in your script.


AES_DECRYPT Requires Cast? I'm trying to use the AES_ENCRYPT and AES_DECRYPT functions. I've found an answer to my initial question which was how to decrypt the encrypted string, https://stackoverflow.com/questions/16556375/how-to-use-aes-encrypt-and-aes-decrypt-in-mysql. I'm not sure why the char(50) is needed though, nor what to do since my string can be longer than 50 characters. Should I use char(65535)?

This returns the encrypted string as expected:

SELECT 'test' as original,
AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))) as encrypted,
CAST(AES_DECRYPT(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))), UNHEX(SHA2('This Is Cool',512))) as char(50)) as decrypted

this does not:

SELECT 'test' as original,
AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))) as encrypted,
AES_DECRYPT(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))), UNHEX(SHA2('This Is Cool',512))) as decrypted

To resume the situation for people who don’t want to follow the link to the original question on SO: the OP is querying a Mysql DB via an unspecified interface which (sensibly) refuses to show the values stored in VARBINARY fields with encrypted data, and (rather dumb-mindedly) still shows a BLOB label for the value returned by calling AES_DECRYPT on them. The answer given on SO was to cast those results as CHAR(50), which from a DB perspective makes little sense, but in this way the interface shows the results as text and everybody is happy.

The question here is: if I have to use CHAR(50), how can I handle longer values?

My answer is: you don’t say which interface you are using (your screenshots are not enough for me to recognize it), but I’d bet that if you cast as CHAR instead of CHAR(50) it still works and you have no size limits that you should care of. So:

SELECT 'test' AS original,
        AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))) AS encrypted,
        CAST(AES_DECRYPT(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))),
                                             UNHEX(SHA2('This Is Cool',512)))
             AS CHAR) AS decrypted;

However, this is an interface question, not a DB engine question, and it is rather strange. I suspect the interface settings have been changed from the ones used when asking the first question, because now the strings which the interface thinks are binary are automatically displayed as HEX. Giving the first query to a commandline interface does not produce the results shown here. To get them from a commandline interface I have to write the second field as HEX(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512)))); similarly, the supposedly wrong result of the second query is simply HEX('test') instead of 'test' because the interface does not know it is actually readable text. Casting it to CHAR should work.

Please be aware that all those conversions the interface is forcing you to perform are not needed when data is exchanged between different parts of your application.


Performance of a Trigger vs Stored Procedure in MySQL A post here on DBA.StackExchange (What are the best practices for triggers to maintain a revision number on records?) has spawned an interesting question (at least, interesting to me) regarding performance in MySQL.

The context is that we want to insert a record in a table for each row that is updated.  Before the row is updated, we want to store off a previous value and then increment one of the columns (a "version" column).

If we do this inside a trigger, it works out nicely.  For MySQL, the triggers are row by row, so it would be an easy solution.  Select the data currently in the table, insert it into the logging table, and the update the "version" column in the new data.

However, it's possible to move this logic to a stored procedure. If you do that, you're performing the insert, then incrementing the "version" column in the table.  The entire thing would be set based.

So, when it comes to performing this insert, would this be more performant to use the set-based stored procedure approach or a trigger based approach?

This question is for MySQL (since it has row-by-row triggers), although it could apply to other row-by-row trigger DBMSs.

For the sake of simplicity, triggers are the way to go for implementing any kind of tracking of database changes. However, you need to be aware of what happens under the hood when you use triggers.

According to MySQL Stored Procedure Programming, page 256 under the head "Trigger Overhead" says the following:

  It is important to remember that, by necessity, triggers add overhead
  to the DML statement to which they apply. the actual amount of overhead
  will depend upon the nature of the trigger, but --- as all MySQL
  triggers execute FOR EACH ROW --- the overhead can rapidly accumulate
  for statements that process large numbers of rows. You should
  therefore avoid placing any expensive SQL statements or procedural
  code in triggers.

An expanded explanation of trigger overhead is given on pages 529-531. The conclulding point from that section states the following:

  The lesson here is this: since the trigger code will execute once
  for every row affected by a DML statement, the trigger can easily
  become the most significant factor in DML performance. Code inside the
  trigger body needs to be as lightweight as possible and -- in
  particular -- any SQL statements in the trigger should be supported by
  indexes whenever possible.

Not mentioned in the book is another factor when using triggers: When it comes to audit logging, please be aware of what you log data into. I say this because should you choose to log to a MyISAM table, each INSERT into a MyISAM table produces a full table lock during the INSERT. This can become a serious bottleneck in a high-traffic, high-transaction environment. Additionally, if the trigger is against an InnoDB table and you log changes in MyISAM from within the trigger, this will secretly disabled ACID compliance (i.e., reduce block transactions to autocommit behavior), which is cannot be rolled back.

When using triggers on InnoDB tables and logging changes

The table you log to is also InnoDB
You have autocommit turned off
You setup START TRANSACTION...COMMIT/ROLLBACK blocks thoroughly

In this way, audit logs can benefit from COMMIT/ROLLBACK as would main tables.

Concerning using stored procedures, you would have to painstakingly call the stored procedure at every point of DML against the table being tracked. One could easily miss logging changes in the face of tens of thousands of lines of application code. Placing such code in a trigger eliminates finding all those DML statements.

CAVEAT

Depending on how complex the trigger is, it can still be a bottleneck. If you want to reduce bottlenecks in audit logging, there is something you can do. However, it will require a little infrastructure change.

Using commodity hardware, create two more DB Servers

Distribution master (DM) to relay audit info only
MySQL Server for Auditing (AU)

This will server to reduce write I/O on the main database (MD) due to audit logging. Here is how you can accomplish it:

Step 01) Turn on binary logging in the main database.

Step 02) Using an inexpensive server, setup MySQL (same version as MD) with binary logging enabled. This will be DM. Setup replication from MD to DM.

Step 03) Using a second inexpensive server, setup MySQL (same version as MD) with binary logging disabled. Setup each audit table to use --replicate-do-table. This will be AU. Setup replication from DM to AU.

Step 04) mysqldump the table structures from MD and load it into DM and AU.

Step 05) Convert all audit tables in MD to use the BLACKHOLE storage engine

Step 06) Convert all tables in DM and AU to use the BLACKHOLE storage engine

Step 07) Convert all audit tables in AU to use the MyISAM storage engine

When done

DM will replicate from MD and record stuff in its binary log only
With --replicate-do-table filter on all audit tables, AU will replicate from DM

What this does is store audit info on a separate DB server and also reduce any write I/O degradation that MD would normally have.


Where can I learn about MySQL master/slave, master/master, or clustering setups? What are the best resources you've read or watched about setting up master/slave or clustering for database replication?

I don't know much about this. I have a database that is getting rather large and would like to make sure that if something goes wrong, I at least have some basic database infrastructure going on so I can recover data.

I'm using MySQL. I'm guessing that I do just as many reads as writes.

Forgive me for sounding somewhat biased, but you can learn about setting up Master/Slave and Master/Master Replication right here in the DBA StackExchange.

Here are some of my posts

May 26, 2012 : Trying to understand SHOW SLAVE STATUS\G
Jan 05, 2012 : MySQL Replication Error
Dec 23, 2011 : How to make MySQL replication reliable?
Dec 19, 2011 : Best way to setup master to multi master replication
Feb 23, 2011 : MySQL Replication - Introduce new Slave to replication

I also have posts concerning

MySQL and DRBD Clustering
Percona XtraDB Cluster

Click here to see all MySQL Replication posts in the DBA StackExchange (not just mine)

There is only one thing I cannot give advice on : MySQL Cluster

While I have answered some questions, there are three MySQL Cluster Experts here

Frazer Clement
Mat Keep
Andrew Morgan

Feel free to post your MySQL Replication questions or simply search through the already posted questions. You may find that what you are looking for is already answered.


Database: what design to have a “document table” referencing others tables I have some tables which can have 0..* documents.

How can I design my data model to avoid having a specific document table for each of my tables? Feel free to suggest by editing the data model

Its pretty easy to do this but I wouldn't recommend it.  To implement a single "document" table for all possible documents and parents, you just abstract the id into a "parent_id" column, and add a "parent_table_name" column.

There are a lot of issues with this approach.  Some are:

Referential Integrity can no longer be enforced by the DBMS.
Query expression is now more complex.
Surrogate keys of a single numeric type are required for all the parents, even if that isn't the best choice for a given parent.
You cannot show the relationships visually on an ERD.  Instead, you now just have table boxes with no lines as there are no enforceable relationships.

The perceived simplicity of a single table with an abstract reference is seductive but ultimately proves to be the opposite of simple due to the above issues.  I think the appeal results from a misapplication of the principle of cohesion.  The cohesion here is really between the documents and the entity which they are about, not their structural similarity in that each table stores a "document."  Applying the principle of cohesion with respect to structure instead of functional meaning can get you into some other ill-advised practices such as OTLT and EAV.

My recommendation is to stick with the design you already have.  It is correct and doesn't need "improved."


Find the closest bar, when searching for a drink Problem: Find the closest bar that serves the drink(s) I'm looking for.

Here you can find the generated MySQL code http://pastebin.com/5Uc2ewUW

The API Request that interacts with this problem will have these parameters

query, String, ideally the drink name
lng, double, the starting longitude
lat, double, the starting latitude
range, integer, max distance in meters (with a default value)

The query parameter may select more than one drink (think about searching for "Vodka").

What would be a good strategy to write a SQL query with good performance?

I'm not very expert, but my idea is to

SELECT bars in the range
SELECT from drink__bars where bar_id is in the previous select result
JOIN the drinks table to get drinks data

How do I set the order based on the distance?

Any suggestion is appreciated!

set @x = 15;
set @y = 25;

select sqrt(pow(b.lat-@y,2) + pow(b.lng-@x,2)) as distance,  b.name, d.name
from `drinks` d
 join `drink__bars` db on d.id = db.drink_id
 join  `bars` b on b.id = db.bar_id
where d.`name` = 'Beer'
order by distance asc;

Full sqlfiddle, with some data. Use your preferred distance function to replace the plain one I used.

I would recommend against having Id on many-to-many relations. And prefer NOT NULL always, unless you have a very strong case to allow NULLs (you don't).


Transfer datas from Access to PostgreSQL I'm trying to insert some data from Access to PostgreSQL database, but it's not a simple query, because it's a nested query.

For example :

rst.Open "INSERT INTO car(id, model, color, type) SELECT (id, model, color, type) FROM t_car"

Where rst is my recordset, and t_car is my Access table.

I know it should look like to a Postgresql request and t_car has nothing to do here, but I don't know how can I specify to this request :

  go take informations from my Access table and insert these to my Postgres.

So I'm little bit lost with it, does someone can help me?

Install the PosgresQL ODBC driver and then use that to register Postgres tables in Access. Issuing the SQL you have given in Access will then transfer data from Access to PosgresQL.


Excessive sort memory grant Why is this simple query granted so much memory?

-- Demo table
CREATE TABLE dbo.Test
(
    TID integer IDENTITY NOT NULL,
    FilterMe integer NOT NULL,
    SortMe integer NOT NULL,
    Unused nvarchar(max) NULL,

    CONSTRAINT PK_dbo_Test_TID
    PRIMARY KEY CLUSTERED (TID)
);
GO
-- 100,000 example rows
INSERT dbo.Test WITH (TABLOCKX)
    (FilterMe, SortMe)
SELECT TOP (100 * 1000)
    CHECKSUM(NEWID()) % 1000,
    CHECKSUM(NEWID())
FROM sys.all_columns AS AC1
CROSS JOIN sys.all_columns AS AC2;
GO
-- Query
SELECT
    T.TID,
    T.FilterMe,
    T.SortMe,
    T.Unused
FROM dbo.Test AS T
WHERE
    T.FilterMe = 567
ORDER BY
    T.SortMe;

For an estimated 50 rows, the optimizer reserves almost 500 MB for the sort:

This is a bug in SQL Server (from 2008 to 2014 inclusive).

My bug report is here.

The filtering condition is pushed down into the scan operator as a residual predicate, but the memory granted for the sort is erroneously calculated based on the pre-filter cardinality estimate.

To illustrate the issue, we can use (undocumented and unsupported) trace flag 9130 to prevent the Filter from being pushed down into the scan operator. The memory granted to the sort is now correctly based on the estimated cardinality of the Filter output, not the scan:

SELECT
    T.TID,
    T.FilterMe,
    T.SortMe,
    T.Unused
FROM dbo.Test AS T
WHERE
    T.FilterMe = 567
ORDER BY
    T.SortMe
OPTION (QUERYTRACEON 9130); -- Not for production systems!

For a production system, steps will need to be taken to avoid the problematic plan shape (a filter pushed into a scan with a sort on another column). One way to do this is to provide an index on the filter condition and/or to provide the required sort order.

-- Index on the filter condition only
CREATE NONCLUSTERED INDEX IX_dbo_Test_FilterMe
ON dbo.Test (FilterMe);

With this index in place, the desired memory grant for the sort is only 928KB:

Going further, the following index can avoid the sort completely (zero memory grant):

-- Provides filtering and sort order
-- nvarchar(max) column deliberately not INCLUDEd
CREATE NONCLUSTERED INDEX IX_dbo_Test_FilterMe_SortMe
ON dbo.Test (FilterMe, SortMe);

Tested and bug confirmed on the following builds of SQL Server x64 Developer Edition:

2014   : 12.00.2430 (RTM CU4)
2012   : 11.00.5556 (SP2 CU3)
2008R2 : 10.50.6000 (SP3)
2008   : 10.00.6000 (SP4)

This was fixed in SQL Server 2016 Service Pack 1. The release notes include the following:

  VSTS bug number 8024987
  Table scans and index scans with push down predicate tend to overestimate memory grant for the parent operator.

Tested and confirmed fixed on:

Microsoft SQL Server 2016 (SP1) - 13.0.4001.0 (X64) Developer Edition
Microsoft SQL Server 2014 (SP2-CU3) 12.0.5538.0 (X64) Developer Edition

Both CE models.


Large sequence cache in Oracle There was a situation of 108,368,168 executions along with 7,373s elapsed time for "update seq$ set increment$=:........" statement while loading massive(i.e. populating million rows) data in the table. It means - it definitely spending some fraction of the time during each call to acquire the latch to be able to update the data dictionary when a new set of values need to be generated.

I diagnose the situation and found that it was happening because of Sequence NOCACHE and started to increase its CACHE value until it's not included in the SQL ordered by CPU/Elepsed in AWR report, finally it's settled when CACHE value set to 100000. Now there is no query in AWR related to Sequence and concluded that CACHE value is given positive performance gain.

Can anyone tell me what are the items I can check in Oracle to confirm such a large sequence cache is not back fired in the database.

No it wont backfire. If there is confusion, you can read the manual to see the effect of caching. It means first time you select from sequence, it will cache next 999999 values in sga. It also means, for next 999999 sequence nextval calls you will not be updating seq$ table.

The downside of this is, if your instance or db crashes, those cached (and unused) values are lost. So if you need to have sequential values with no sequence loss, this will not be helpful. Along with caching, you perhaps want to use ORDER option to ensure sequences will be distributed in a ORDERED manner.


Moving PostgreSQL database to new server I had PostgreSQL 8.3 running on a windows machine. The hard drive recently became unbootable, but the data is fine. We got a new machine and I'm re-installing Postgres. The new installer only has version 8.4 and a variety of 9.x releases.

Would I be able to install 8.4 and move the data over with no problems, or do I need to find the 8.3 installer. How about version 9?  Is the migration as simple as moving the old "C:\Program Files\PostgreSQL\8.3\data" to the exact same place on the new directory.

Thanks,
Kevin

Upgrading a major version of PostgreSQL (i.e. 8.3 to 8.4) requires a database dump and restore - simply copying in data from an older version will most likely not work.

There are still some copies of 8.3 that you can download from EnterpriseDB to get you going. However it would still be best to upgrade to the later version afterwards if possible.


Is there any danger in granting SUPER privileges to a user? I had problems in restoring procedures in my db, so what I did was to grant SUPER privileges to my dbuser.

This user is the same user used in my web application, so I would like to know if there is any danger in granting SUPER privileges to a user.

It is always a bad idea to give a user more access than it needs to perform its duties.

  This user is the same user used in my web application

If your application is hacked due to a bug in your code or one of the libraries/modules that you are using, the attacker may end up being able to take action as this user - so if this user has privileges that allow the complete destruction of the application database and other databases on the same server then a successful attacker gets these dangerous privileges too.

An application user should have the absolute minimum privileges that it needs to act on behalf of normal tasks required by the application. For larger systems it is not uncommon to have multiple application users for different parts of the system each with restricted rights that block them from interacting with unrelated objects in the database. Other privileges that allow management of the database and server more generally should be give only to separate administration users.

If you are in a situation where an application user needs administrative privileges then it is recommended that you try refactor your design to remove such need (or limit it as much as is possible).

Lookup the Principal of Least Privilege (i.e. https://en.wikipedia.org/wiki/Principle_of_least_privilege) for more information about why this is a good idea generally.


Need Another MongoDB Binary? I have a three-node MongoDB test replica running normally. I want to add an Ops Manager to it also residing on these three nodes. Do I need to install another set of MongoDB binary for the Ops Manager's databases in addition to the current replica?

No you don't need. Ops-manager just add there DB where all configuration information and collected data is kept.


SQL Developer auto trace does not show cascading deletes I'm trying to understand the performance characteristics of SQL statement that does DELETE cascade on 4 linked tables.

When I do the auto tracing of the statement it only shows me the DELETE statement executed on the table. It does not show details of deletes done on other tables.

Why doesn't SQL Developer show cascades in tracing?

Autotrace in SQL Developer gets you the v$sql_plan of your query and the session stats accumulated during the execution of your query - it does not show any recursive SQL that may get executed.

To see that you'd actually want to trace your session.


Can you restore purged MySQL binary logs? I have two situations I need some clarification.

1) Let's say I made a backup of all the MySQL binary logs before I purged a single binary log file (the oldest one, possibly the very first one).

  mysql-bin.000001

Can I restore this single binary log from the backup I made? Just copy and paste it from my backup to the MySQL data directory and I can use it again as if it was never deleted in the first place?

2) Another situation, let's say I purged every bin log except the current one but after a month or two I needed to restore something from the middle of the purged bin logs like:

  mysql-bin.000099

Can I just restore this single file from backup and do whatever it is I need to get back from this bin single bin log file? Or I would need to restore everything before I could use that file?

The binlogs contain a chronological list of the changes made to all the tables.  There is no "snapshot" in them, only insert, deleted, etc statements.

You can look at any one of the files with the mysqlbinlog program.

"get back from this bin single bin log file" -- What do you imagine you might want to get?


Covering Index Question I have the following query

Select  Pt.PRODID, PT.INVENTREFID,  Inv.ItemName, PT.ItemID, Configid,  Pt.QTYSCHED,
PT.DLVDATE,  Pt.CREATEDDATETIME, pt.SCHEDEND,
 CASE Left(PT.INVENTREFID, 3)
     WHEN 'SJB' THEN ST.SALESNAME
     WHEN 'WJB' THEN 'Sub - Assembly'
     ELSE 'Stock'
  END as CustomerName
from Dynamicsv5Realtime.dbo.PRODTABLE PT
Join Dynamicsv5Realtime.dbo.Inventdim ID
On PT.InventdimId = ID.InventdimID
and PT.Dataareaid = ID.dataareaid
Join Dynamicsv5Realtime.dbo.INVENTTABLE Inv
On Inv.itemid = PT.ItemId
Left Join Dynamicsv5Realtime.dbo.SALESTABLE ST
ON ST.SalesId = PT.INVENTREFID
and ST.Dataareaid = PT.dataareaid
where pt.PRODSTATUS in(2,3,4)
and PT.DATAAREAID = 'AJB'
Order by 7

when I was looking to improve it I came up with the following index

CREATE NONCLUSTERED INDEX [INDEX_2]
ON [dbo].[PRODTABLE] ([INVENTREFID],[DATAAREAID],[INVENTDIMID],[PRODSTATUS])
INCLUDE ([PRODID],[ItemID],[QTYSCHED],[DLVDATE],[CREATEDDATETIME],[SCHEDEND])

However this was not as efficient as the following which the missing index DMV came up with

CREATE NONCLUSTERED INDEX [INDEX_1]
ON [dbo].[PRODTABLE] ([DATAAREAID],[PRODSTATUS])
INCLUDE ([ITEMID],[QTYSCHED],[DLVDATE],[SCHEDEND],[INVENTREFID],[PRODID],[INVENTDIMID],    [CREATEDDATETIME])

What I am wondering is when is it more efficient to put columns in the index itself rather than in the Include part of the index?

May thanks

Very generally and non-technically, you should consider using the column as part of the index key if you are going to use that column in the WHERE clause (or otherwise filter/seek), and INCLUDE if it is merely being used to avoid a lookup (I like to call this "coming along for the ride"). Of course there are always exceptions:

you can't always include columns in the key due to size;
it may not matter due to cardinality; or,
it might not affect the plan due to where you would have to put it in the key to allow the index to continue satisfying other query semantics.

There are also cases where an INCLUDEd column can satisfy a filter just fine, and plenty of cases where changing the index might help this specific query but it might wreak havoc on the rest of your workload. You should always test index changes against an entire workload representing as much of a full business cycle as possible, rather than rely on the missing indexes DMV or your own analysis which, it seems, is focused on a single query.

I'm sure Paul White will come along with a much more elaborate and technically accurate response.


I am trying to run this code and I get an error at line 11 CREATE TABLE IF NOT EXISTS `tblproduct` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(255) NOT NULL,
  `image` text NOT NULL,
  `price` double(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_code` (`code`)
)

INSERT INTO `tblproduct` (`id`, `name`, `code`, `image`, `price`)
VALUES
(1, '3D Camera', '3DcAM01', 'product-images/camera.jpg', '1500.00'),
(2, 'External Hard Drive', 'USB02', 'product-images/external-hard-drive.jpg', '800.00'),
(3, 'Wrist Watch', 'wristWear03', 'product-images/watch.jpg', '300.00');

CREATE TABLE IF NOT EXISTS `tblproduct` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(255) NOT NULL,
  `image` text NOT NULL,
  `price` double(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_code` (`code`)
)  <<<<------- put semi-colon here.

Your problem here is with the last line of your CREATE TABLE -  it should be ); and not just the bracket i.e. ) on its own! It's the sort of error that's very difficult to spot if you're trying to concentrate on the code - it's driven me mad a couple of times, I just couldn't see it! p.s. welcome to the forum!


Open Windows Causing Locks After Sproc has Completed I had a query that was being blocked. Ran sp_whoisactive @ get_locks=1 and identified the blocking query and it's owner. The result indicated the table had been locked for over four hours. The developer stated it was a stored procedure that she executed and it completed in 21 seconds. She still had the SSMS window open for the stored procedure. As soon as she closed the window the lock was released. I've never seen a completed stored procedure retain the lock simply because the window had not been closed. I've researched, but have been unable to find anything similar to this issue. The stored procedure basically Selects records into a temp table and then UPDATES another table and sets a flag if there is a bad address detected.
Is this standard behavior? Is there a setting that can be changed so this does not happen?
It's SQL Server 2019. SSMS v 18.

Is this standard behavior?

No.  There was either a transaction started before the stored procedure ran, or the session was running with SET IMPLICIT_TRANSACTIONS ON, which can be set to default for SSMS query windows.
In any case if the session was blocking with no query running, it had an open transaction.


SSAS where are olap cubes stored Getting started on creating data cubes with SSAS. I understand the various types of OLAP options ROLAP, HOLAP and MOLAP. However, when creating a data cube using SSAS (at least for 2008 version) I do not see where we choose this option. Would it be during the install of the server?

Where is the data stored when a data cube is created?

Dimensions in Microsoft SQL Server Analysis Services support either ROLAP or MOLAP.

Open the dimension in BIDS 2008, select the root dimension node

and change this in the properties window.

For fact data you open the cube and can similarly set the storage mode by selecting a measure group and altering the storage mode to one of HOLAP/ROLAP/MOLAP in its properties window.

But this only sets the default for new partitions. You still need to go onto the partitions tab of the cube designer and set any existing partitions to the desired configuration.


Can't find any tables after psql dump import from pgexercises.com I'm trying to import the database used for the PostgreSQL exercises at pgexercises.com.

I ran the following command as recommended, and it went through without errors and even showing Inserts with the right number of rows:

psql -U cornelius -f clubdata.sql -d postgres -x

I can connect to the database created with psql exercises. However, I get Did not find any relations with \dt.

When rerunning the import command, I get errors saying that the tables already exist.

What's going on? Where are my tables?

Here's a subset of the dump file:

--
-- PostgreSQL database dump
--
CREATE DATABASE exercises;
\c exercises
CREATE SCHEMA cd;

-- Dumped from database version 9.2.0
-- Dumped by pg_dump version 9.2.0
-- Started on 2013-05-19 16:05:10 BST

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- TOC entry 7 (class 2615 OID 32769)
-- Name: cd; Type: SCHEMA; Schema: -; Owner: -
--

SET search_path = cd, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 171 (class 1259 OID 32818)
-- Name: bookings; Type: TABLE; Schema: cd; Owner: -; Tablespace:
--

CREATE TABLE bookings (
    bookid integer NOT NULL,
    facid integer NOT NULL,
    memid integer NOT NULL,
    starttime timestamp without time zone NOT NULL,
    slots integer NOT NULL
);

INSERT INTO bookings (bookid, facid, memid, starttime, slots) VALUES
(0, 3, 1, '2012-07-03 11:00:00', 2),
(1, 4, 1, '2012-07-03 08:00:00', 2),
(2, 6, 0, '2012-07-03 18:00:00', 2),
(3, 7, 1, '2012-07-03 19:00:00', 2),
(4, 8, 1, '2012-07-03 10:00:00', 1),
(5, 8, 1, '2012-07-03 15:00:00', 1),
(6, 0, 2, '2012-07-04 09:00:00', 3),
(7, 0, 2, '2012-07-04 15:00:00', 3),
(8, 4, 3, '2012-07-04 13:30:00', 2),
(9, 4, 0, '2012-07-04 15:00:00', 2);

--
-- TOC entry 2196 (class 2606 OID 32822)
-- Name: bookings_pk; Type: CONSTRAINT; Schema: cd; Owner: -; Tablespace:
--

ALTER TABLE ONLY bookings
    ADD CONSTRAINT bookings_pk PRIMARY KEY (bookid);

CREATE INDEX "bookings.memid_facid"
  ON cd.bookings
  USING btree
  (memid, facid);

ANALYZE;

The solution is: Schemas, SQL for namespace.

The third line in the exercise dump is: CREATE SCHEMA cd;

As a result, the tables are accessible by prepending cd. in front of table names.

Running \dt * with wildcard also shows all tables present, no matter the namespace. This is a good thing to run when \dt gives no result even though one expects that it should have.


Change database login default schema I have a problem. I have a Windows user [domain\user] on my instance that has access to some database. I want to change the default schema for that login. I can do this when I create some test login and user in the database but not for my Windows user:

ALTER USER [MFG\giorgi.nakeuri] WITH DEFAULT_SCHEMA=[TBBS]

...results in:

  Cannot alter the user 'MFG\giorgi.nakeuri', because it does not exist
  or you do not have permission.

But when I am trying to add it:

CREATE USER [MFG\giorgi.nakeuri] FOR LOGIN [MFG\giorgi.nakeuri]

I get:

  The login already has an account under a different user name.

When I observe login in properties it has a check on the database and both user and default schema are dbo. But I can not change any of those from that window. I am connected to instance with this windows user and login server role is all: public, securityadmin, serveradmin, setupadmin, sysadmin.

It looks like based on your error that the user you are trying to create a login for already has a login in that database with another name, so you are effectively "double mapping" them.

If you have access to SSMS you can see what their mapping is by using the Object Explorer and navigating the following path:
Security -> Logins -> Double click on the username

This should pop up a GUI, you can see the "User Mapping" in the top left hand corner, this should show you to whom the user is mapped.