SQL Trigger- Updating count So I need to create a trigger that creates a new evidence number for each case. For instance, case #1 can have evidence #'s 1,2,3,4 and so on. Case #2 can also have evidence #'s 1,2,3, etc.

So I have a "Case" table (CaseID as PK) and a "Evidence" Table (EvidenceNum as PK and CaseID as FK and associated attributes)

So everytime I search for a specific caseID, I want a new "EvidenceID" column to populate. Such as Evidence item #1, #2, and so on. So these numbers can be repeated for each case. Hence why this is not the primary key. EvidenceNum is primary key but that wont be seen by the end user. Any help with this??

I would implement this using a stored procedure instead of a trigger.  Use a separate key table to store the last used evidence number for each case.

I mocked up a minimally viable complete example.

Drop the objects from tempdb if they already exist, so we can modify the code as required.

USE tempdb;

IF OBJECT_ID(N'dbo.AddCase', N'P') IS NOT NULL
DROP PROCEDURE dbo.AddCase;
IF OBJECT_ID(N'dbo.AddEvidence', N'P') IS NOT NULL
DROP PROCEDURE dbo.AddEvidence;
IF OBJECT_ID(N'dbo.EvidenceKeys', N'U') IS NOT NULL
DROP TABLE dbo.EvidenceKeys;
IF OBJECT_ID(N'dbo.Evidence', N'U') IS NOT NULL
DROP TABLE dbo.Evidence;
IF OBJECT_ID(N'dbo.Cases', N'U') IS NOT NULL
DROP TABLE dbo.Cases;
GO

Create a Cases and Evidence table, along with an EvidenceKey table to store the incrementing Evidence Number.

CREATE TABLE dbo.Cases
(
    CaseID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Cases
        PRIMARY KEY CLUSTERED
) ON [PRIMARY];

CREATE TABLE dbo.Evidence
(
    EvidenceID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Evidence
        PRIMARY KEY CLUSTERED
    , CaseID int NOT NULL
        CONSTRAINT FK_Evidence_CaseID
        FOREIGN KEY
        REFERENCES dbo.Cases(CaseID)
    , EvidenceNum int NOT NULL
    , CONSTRAINT UQ_EvidenceNum
        UNIQUE (CaseID, EvidenceNum)
);

CREATE TABLE dbo.EvidenceKeys
(
    CaseID int NOT NULL
        CONSTRAINT PK_EvidenceKeys
        PRIMARY KEY CLUSTERED
    , MaxEvidenceNum int NOT NULL
);
GO

Create a procedure used to add a new Case.  You'd need to add parameters to this such as the Case Name, date, etc.

CREATE PROCEDURE dbo.AddCase
(
    @CaseID int OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Cases TABLE
    (
        CaseID int NOT NULL
    );
    INSERT INTO dbo.Cases
    OUTPUT inserted.CaseID
    INTO @Cases (CaseID)
    DEFAULT VALUES;
    SELECT @CaseID = CaseID
    FROM @Cases;
END
GO

Create a procedure to add Evidence.  Again, this is only a proof-of-concept, so you'd need to add parameters to deal with the actual evidence item details.

CREATE PROCEDURE dbo.AddEvidence
(
    @CaseID int
    , @EvidenceID int OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @MaxEvidences TABLE
    (
        MaxEvidenceNum int NOT NULL
    );
    SET @EvidenceID = NULL;
    UPDATE dbo.EvidenceKeys
    SET MaxEvidenceNum += 1
    OUTPUT inserted.MaxEvidenceNum
    INTO @MaxEvidences(MaxEvidenceNum)
    WHERE dbo.EvidenceKeys.CaseID = @CaseID;
    SELECT @EvidenceID = MaxEvidenceNum
    FROM @MaxEvidences;
    IF @EvidenceID IS NULL
    BEGIN
        INSERT INTO dbo.EvidenceKeys (CaseID, MaxEvidenceNum)
        VALUES (@CaseID, 1);
        SET @EvidenceID = 1;
    END
    INSERT INTO dbo.Evidence (CaseID, EvidenceNum)
    VALUES (@CaseID, @EvidenceID);
END;
GO

Insert some sample data:

DECLARE @CaseID int;
DECLARE @EvidenceID int;

EXEC dbo.AddCase @CaseID OUT;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;

EXEC dbo.AddCase @CaseID OUT;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;

Each execution of dbo.AddEvidence will increment the value in the dbo.EvidenceKeys table for the given @CaseID in a single atomic operation, reducing the chance for locking to become a problem.

SELECT *
FROM dbo.Cases c
    INNER JOIN dbo.Evidence e ON c.CaseID = e.CaseID

Results from the select above:

╔════════╦════════════╦════════╦═════════════╗
║ CaseID ║ EvidenceID ║ CaseID ║ EvidenceNum ║
╠════════╬════════════╬════════╬═════════════╣
║      1 ║          1 ║      1 ║           1 ║
║      1 ║          2 ║      1 ║           2 ║
║      1 ║          3 ║      1 ║           3 ║
║      2 ║          4 ║      2 ║           1 ║
║      2 ║          5 ║      2 ║           2 ║
║      2 ║          6 ║      2 ║           3 ║
╚════════╩════════════╩════════╩═════════════╝

Since obtaining the maximum EvidenceKey value for any given CaseID, and updating the dbo.EvidenceKeys table, occurs in a single atomic statement, the opportunity for deadlocks is vastly reduced, without the need for locking hints.

To test this design, I ran the following code.  The first piece creates 100 "cases", each with 3 rows of "Evidence".  Then, in 3 separate sessions, the 2nd piece of code inserts 100,000 rows into the Evidence table, randomly assigning each evidence row to a randomly chosen case.  No deadlocks occurred, and the process took under 1 minute on my old, slow, dev workstation.

DECLARE @loop int = 0;
DECLARE @CaseID int;
DECLARE @EvidenceID int;
WHILE @loop < 100
BEGIN
    EXEC dbo.AddCase @CaseID OUT;
    EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
    EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
    EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
    SET @loop += 1;
END
GO

This piece should run in 3 (or more) separate sessions:

DECLARE @loop int = 0;
DECLARE @CaseID int;
DECLARE @EvidenceID int;
WHILE @loop < 100000
BEGIN
    SET @CaseID = (SELECT TOP (1) CaseID FROM dbo.Cases ORDER BY CRYPT_GEN_RANDOM(10));
    EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
    SET @loop += 1;
END


Postgresql improve select performance/parallelism in huge table I have a huge table (> 3000 million rows, totaling 3 TB) using Postgres 9.5 defined as follows:

CREATE TABLE user_events
(
    user_id VARCHAR NOT NULL,
    datetime BIGINT NOT NULL,
    field1 VARCHAR,
    field2 VARCHAR,
    field3 VARCHAR,
    field4 VARCHAR,
    field5 VARCHAR,
    CONSTRAINT user_events_user_id_datetime_pk PRIMARY KEY (user_id, datetime)
);

alter table user_events set (autovacuum_vacuum_cost_delay=10);

I need to query this table roughly 2000 times per second but I'm not being able to parallelize enough (I'm reaching approx half that).

Using the following prepared statement, I get a reasonable (I suppose) response time:

PREPARE getact AS select * from user_events where user_id = $1 order by datetime limit 1000;

explain analyze EXECUTE getact('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx');

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.70..1495.10 rows=367 width=878) (actual time=0.028..0.044 rows=4 loops=1)
   ->  Index Scan using user_events_user_id_datetime_pk on user_events  (cost=0.70..1495.10 rows=367 width=878) (actual time=0.026..0.034 rows=4 loops=1)
         Index Cond: ((user_id)::text = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::text)
 Execution time: 0.073 ms

Some additional notes:

The content wasn't loaded in an orderly manner so datetime won't be
physically ordered increasingly but from now on it will.
The table is mostly append only
We expect the table to grow at most a %50 more in the long term.

At this point I'm looking for any configuration suggestion. I know I had scaling options like a readonly replica or sharding, but would like to avoid that as much as possible for now.

Would executing CLUSTER work in this case? It would seems like the most part of the cost is in the index scanning, so I'd guess it won't, but I don't know (and testing it is really expensive).

Some more information:

There are multiple threads writing (mostly appending) at the same rate that needs to be read
Though the times described with the prepared statement are real, I've seen results ranging from < 1 ms to > 100ms

EXPLAIN (ANALYZE, BUFFERS) (slowest) result

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM user_events WHERE user_id = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX';

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using user_events_user_id_datetime_pk on user_events  (cost=0.70..1495.10 rows=367 width=878) (actual time=3.336..1162.936 rows=1518 loops=1)
   Index Cond: ((user_id)::text = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'::text)
   Buffers: shared hit=5 read=1532
 Planning time: 0.071 ms
 Execution time: 1164.926 ms

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM user_events WHERE user_id = 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy';

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using user_events_user_id_datetime_pk on user_events  (cost=0.70..1495.10 rows=367 width=878) (actual time=18.890..953.809 rows=1269 loops=1)
   Index Cond: ((user_id)::text = 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy'::text)
   Buffers: shared hit=5 read=1282 dirtied=107
 Planning time: 0.105 ms
 Execution time: 955.590 ms
(5 rows)

Although I didn't reach an answer for the issue (due to time constraints), I think that the most useful tip to remark is:

Use EXPLAIN (ANALYZE, BUFFERS) ... on many queries (with different values) to try to understand what is happening. In my case, this put back CLUSTER as a possible optimization option.

What I would do if I had the time to try to optimize the table:

Change the indexed values for the smallest possible. In my case, that would mean changing the user_id from VARCHARto UUID.
Execute CLUSTER to reduce the number of pages fetched.


Where do you find the citext module in postgres 9.3 on ubuntu 14.04? So I'm trying to use citext, but it appears to be MIA from ubuntu 14.04 postgres install.  Where can you find this mythical beast called citext for postgresql 9.3?

project_test=> CREATE TABLE "customers_addresstype" (
project_test(>     "id" serial NOT NULL PRIMARY KEY,
project_test(>     "customer_id" integer NOT NULL REFERENCES "customers_customer" ("id") DEFERRABLE INITIALLY DEFERRED,
project_test(>     "display" citext NOT NULL,
project_test(>     UNIQUE ("customer_id", "display")
project_test(> )
project_test-> ;
ERROR:  type "citext" does not exist
LINE 4:     "display" citext NOT NULL,
                      ^
project_test=> CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA ext;
ERROR:  could not open extension control file "/usr/share/postgresql/9.3/extension/citext.control": No such file or directory

$ ls /usr/share/postgresql/9.3/extension
plpgsql--1.0.sql  plpgsql.control  plpgsql--unpackaged--1.0.sql

$ ls /usr/lib/postgresql/9.3/lib
ascii_and_mic.so     euc_cn_and_mic.so   latin2_and_win1250.so  pgxs               utf8_and_big5.so      utf8_and_euc_jp.so   utf8_and_gbk.so        utf8_and_sjis2004.so
cyrillic_and_mic.so  euc_jp_and_sjis.so  latin_and_mic.so       plpgsql.so         utf8_and_cyrillic.so  utf8_and_euc_kr.so   utf8_and_iso8859_1.so  utf8_and_sjis.so
dict_snowball.so     euc_kr_and_mic.so   libpqwalreceiver.so    tsearch2.so        utf8_and_euc2004.so   utf8_and_euc_tw.so   utf8_and_iso8859.so    utf8_and_uhc.so
euc2004_sjis2004.so  euc_tw_and_big5.so  pg_upgrade_support.so  utf8_and_ascii.so  utf8_and_euc_cn.so    utf8_and_gb18030.so  utf8_and_johab.so      utf8_and_win.so

I don't see in the repository and locate -i citext finds nothing.

Turns out the answer was on Stack overflow. And Daniel commented on it right about the time I was installing it.

sudo apt-get install postgresql-contrib-9.3

https://stackoverflow.com/a/22833812/147562


Monitor FOR loop progress in postgres I have a Postgres loop as follows, which I am running from the command line via psql:
DO
$do$
BEGIN
   FOR i IN 1..20000
   -- update some rows
   LOOP
END LOOP;
END;
$do$;

Is there any way to access the value of the counter i to track the progress of the loop? I've looked in the pgAdmin dashboard with no success.

By adding raise notice. It will keep printing value of i -- so that you will always have idea about loop progress.
    DO
    $do$
    BEGIN
    FOR i IN 1..20000
    -- update some rows
    raise notice 'Current Progress Value i:%',i;
     LOOP
    END LOOP;
    END;

Or more better way.

Create a sql file  : vi loop.sql  -- add your loop into this file with raise notice.

Run as a job: psql -U username -d database_name -a -f loop.sql &>progress_log

Keep monitoring latest id : tail progress_log

Note: One more tip- I think you need to initialize i - need to include a declare section. Like below:(sample)
    DO
    $do$
    DECLARE
    i int8;
    BEGIN
    -----your code--
    END;


SQL Auditing Functionality Can someone tell me what the cause of this alert is that's captured by our SQL Audit trace we have setup using the built-in auditing functionality? This alert is generated from time to time in the audit log and the audit log has not been disabled or the server has not restarted.

<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data">
  <session>
    <![CDATA[EDW Audit$A]]>
  </session>
  <action>event enabled</action>
  <startup_type>automatic</startup_type>
  <object><![CDATA[audit_event]]></object>
</action_info>

You need to set action disabled which is given in values.

<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[TestServerAudit$A]]></session><action>event disabled</action><object><![CDATA[audit_event]]></object></action_info>

For more information, see SQL Server Audit Log


mongodb wont read config file using upstart The problem started where mongo wouldn't start because of low disk space. So I enabled the small files directive in the config file. Now it starts if I run:

/usr/bin/mongod --config /etc/mongod.conf

and it starts

service mongod start

and it fails from the same error about disk space, leading me to believe that it's just not reading the config file.

i added an echo statement to the upstart script and i see this:

Starting database: mongodstart-stop-daemon --background --start --quiet --pidfile /var/run/mongod.pid --make-pidfile --chuid mongodb --exec  /usr/bin/mongod  -- --config /etc/mongod.conf

Config file: https://gist.github.com/macdabby/3ee375be72ab3a3f11a7

Upstart script: https://gist.github.com/macdabby/192891a1ae690d4ec57c

Debian wheezy and mongodb 3.0.1

thanks in advance!

It seems as though it was reading the config file but reading it in a different way that it was ignoring a certain directive. I tried setting smallfiles to true which worked when starting manually. But in service mode I got it to work by disabling journaling. Not sure if this is a bug.


Query Quill-Delta JSON array in Postgres I need to store text in Quill-Delta format, which is in the form of a JSON array, e.g.

-----------------------------------------------------------------
| id | title | content                                          |
-----------------------------------------------------------------
| 10 | Hello | [ { "insert": "Hello" }, { "insert": "World" } ] |
| 20 | Image | [ { "insert": { "image": "data:image/j..." } } ] |
-----------------------------------------------------------------

Then, I need to query it, e.g. select where text contains "hello world" such as:

if I query content for "hello world" I get row 10;
if I query content for "insert" I get no results;
if I query content form "image" I get no results.

In practice, it should search only the insert key of array objects, where the value is a JSON string (no other types).

Can I achieve this storing data in Postgres? I read about full-text search over JSONB, but I couldn't come up with a working example.

I've come up with a function and an index; this is the function, which takes alle the insert JSON-String keys and builds up a tsvector from them:

create or replace function public.fts_quill_delta(lang regconfig, delta text) returns tsvector as
$$
begin
return to_tsvector(lang, string_agg(value->>'insert', ' ')) from jsonb_array_elements(delta::jsonb) where jsonb_typeof(value->'insert') = 'string';
end;
$$
language 'plpgsql' immutable;

this is the index:

create index content_delta_fts on my_table using gin(fts_quill_delta(lang, content));

Then you can query the index with:

select from my_table where fts_quill_delta(lang, content) @@ 'hello & world'

Note that I added a lang column, used by the FTS engine to use correct I18N settings.


The classic way to realize many to many relation is with intermediate table.
Like
Products(ID) - ProductImages(ProductID, ImageID) - Images(ID)
What is the reason to make these "sets"?


How to only update part of a column based on conditions? This is a continuation of Updating a table with duplicate id's while avoiding " single-row subquery returns more than one row" , however i felt the question was too different to follow up in comments

So when i run the query:

update table1
set column1 = (SELECT distinct color
                FROM table2
                Where table1.id = table2.id
                and rownum = 1
              )

Everything where the conditions above does not apply returns null, however i would like to keep the value of the fields who do not get a new color

The create table:

CREATE TABLE table1
id varchar2 (20) [NULL]
color varchar2 (20) [NULL]

CREATE TABLE table2
id varchar2 (20) [NULL]
color varchar2 (20) [NULL]

where for example table 1 has 2 rows that are:

id: 123, color: blue
id: 124, color: red

and table 2 has 2 rows that are:

id: 124, color: green
id: 125, color: brown

i would then like to update table1 with table 2 so that table one has the 2 rows:

id: 123, color: blue
id: 124, color: green

I don't fully understand the question, but I believe what you're looking for is a MERGE statement.  A rough example from memory:

MERGE INTO table1 t1
USING table2 t2
ON (t1.id=t2.id)
WHEN MATCHED THEN UPDATE SET
    t1.color = t2.color
WHEN NOT MATCHED THEN INSERT
    (t1.ID, t1.color) VALUES (t2.ID, t2.color);

If you don't want to override the colors, you can simply remove the 'WHEN MATCHED' part.


Can I setup several mariadb clusters, each for a schema I'm planning to use MariaDB Cluster, as a way to sync several databases from different peripherals (raspberry pis).
The peripherals don't share data, but all data should be shared with the main server.

Is there a way to keep the same database, but create a cluster with 2 nodes for each schema ?

Thanks

If the Pis are the source of the data, use "multi-source" replication from them to MariaDB.

Then have 3 nodes for the cluster (2 is mostly useless), and have all the data for both databases on all the nodes.

Or, more simply, the 2 Pis replicate to a single Master (no cluster).


Why/when does the SQL Server query optimizer use EQ_ROWS? I'm trying to understand how exactly the statistics work.

I've got a clustered index on my date. I can see a convert_implicit is executed when I run the following query.

Don't know why it's doing that.

select date
from sales
where date = '20141204'

Statistics say:

Rows: 23818426
Rows Sampled : 216564
All density : 0,0008880995

So the density vector (that this query is not using) : 21153,13

Now for '20141204' my histogram is showing the following

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
2014-10-30  86723,38    38235,36    3   28770,91
2014-12-04  110856,1    36372,89    16   6902,33
2014-12-18   73389,8    68144,39    6   12180,92

I have my problem now understanding, why the optimzer chooses EQ_ROWS with the value of 36372,89 for my estimated rows. The actual rows are 9472.

I've freshly updated my statistics.

So now I'm wondering, if my histogram is just wrong because the I've not scanned all the rows with my statistics, but just 216564.

Sure enough when I query for 2014-12-05, I'll get the expected 12180,9 for estimated rows.

Why is the optimizer using EQ_ROWS for 2014-12-04?

And as for the convert_implicit - why is it converting? I thought the syntax 20141204 is a standard notation. Can we avoid the conversion in any way?

My best guess is, the optimizer uses EQ_ROWS every time the predicate equals (hence the EQ) the value in the sampled histogram.


Failure Of Backup Due To Limit Exceeded For Recovery Files Since Cannot Reclaim Disk Space [RMAN-03009,ORA-19809,ORA-19804] While I try to take incremental backup level 0 by using the command

  RMAN> backup incremental level 0 database plus archivelog;

, encountered this error.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/27/2013 15:58:
05
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 4327473152 limit

When I went through some forums, I found a lot of solutions, but when I checked with their situations, it doesn't match.

One solution was to increase the db_recovery space. So I checked the space and got this result.

db_recovery size

 SQL> show parameter db_recovery;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 4127M

 Space Usage

SQL> select SPACE_USED,SPACE_LIMIT from  v$recovery_file_dest;

SPACE_USED SPACE_LIMIT
---------- -----------
 800155136  4327473152

 Available Space

 SQL>SELECT
NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,
'999,999,999,999') AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT      SPACE_AVAILABLE  PERCENT_FULL
---------------- ---------------- ------------
/u01/app/oracle/fast_recovery_area
   4,327,473,152    3,488,460,800         19.4

 From RMAN

RMAN> show all;

RMAN configuration parameters for database with db_unique_name AGGE are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_agge.f'; # default

What should I do to resolve this problem? Do I have to increase the recovery space even after I am having this much space?

Using ORACLE 11G R2 in RHEL5

The db_recovery_file_dest includes a variety of files, including online AND archived redo log files; take a look at the Oracle Backup and Recovery User's Guide chapter 5 to find out the best solution; disabling db_recovery_file_dest is not a good idea.  You can try

RMAN> report obsolete;

RMAN command to see what can be deleted. Then do a delete obsolete to delete them. Do you have any guaranteed restore points defined?

RMAN> list restore point all;

If you see any results with type GUARANTEED, if they are no longer needed, remove them:

SQL> DROP RESTORE POINT before_app_upgrade;

Is flashback database enabled?


Mysql Order , Select highest value item and update to another table i have list in data_1 table. i want to select highest value item in one group and update it to another table. can i do that with 1 query?

example : Select "Group B Leader Jane" and update it to other table with coulmns

Table 1 : data_1

id(ai) - group - name - serial - point
52        A      Jhon    A54V     14
53        A      Arthur  B896     11
54        B      Jane    RT3A     14
55        B      Kim     RF31     13

After UPDATE -> Table 2 : data_2

id(ai) - name - text1 - text2
8        Jane   RT3A      14

In this case you can use INSERT INTO ... SELECT FROM ... ORDER BY DESC LIMIT 1:

INSERT INTO data_2 (name, text1, text2)
SELECT name, serial, CAST(point AS varchar(10))
FROM data_1
ORDER BY point DESC
LIMIT 1


#1041 - Out of memory issue in mysql I am using Amazon RDS db.m1.medium instance. It has at least 40-50 database and atleast 10k table. For last one year it was working fine, but today I was altering one table It started giving error. I am using InnoDB format.

When I create a table or alter a table I get this error:

  #1041 - Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap

How do I resolve this?

PROBLEM #1
Your major problem is having at least 10000 tables
Here is the issue I see
RDS Instance was All InnoDB
How many file handles are open if you have 10000 InnoDB tables ?

One file handle for the .frm file
One file handle for the .ibd file

This would be a maximum of 20000 open file handles
RDS Instance was All MyISAM
How many file handles are open if you have 10000 MyISAM tables ?

One file handle for the .frm file
One file handle for the .MYD file
One file handle for the .MYI file

This would be a maximum of 30000 open file handles
PROBLEM #2
You are using m1.medium. That only has 3.75 GB RAM.
PROBLEM #3
Having 10000 tables can be very memory intensive because of the amount of metadata to maintain
See my old posts about INFORMATION_SCHEMA's memory consumption

Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory?
Apr 21, 2014 : Adding new tables -- memory usage increases

PROBLEM #4
Your Production Server probably has too many open DB Connections that are dormant but consuming memory (See my post How costly is opening and closing of a DB connection?)
ANALYSIS
Your problem could simply be having too many open tables at the moment you are trying to create a table (which needs 2-3 file handles) or an alter table (which needs 2-3 file handles). I have seen this happen to me trying to create a partitioned table and running out of file handles. I wrote about this in my old post What are the possible risks involved in partitioning large database tables? where I raised the ULIMIT on the OS (bare metal server) to address it. I know changing ULIMIT does not help you because you are in RDS.
SUGGESTIONS
Simply run
mysql> FLUSH TABLES;

and it will close all tables that have open files handles.
If you prefer to close specific open tables, you can list the open tables with
mysql> SHOW OPEN TABLES FROM <database>;

Then, you must close the tables explicitly like this
mysql> FLUSH TABLES db1.tb1,db2,tb2,db3.tb3;

After running FLUSH TABLES;, then you can run CREATE TABLE or ALTER TABLE.
You may also need upgrade to another server model with much more RAM.
You could reduce the number of tables but archiving the data and removing inactive databases. This will reduce RAM consumed for the INFORMATION_SCHEMA.
You should also close all your DB Connections that are not being pooled.
GIVE IT A TRY !!!


Is "ETL vs ELT" just "tables vs views"? TL;DR; Is the difference between ETL and ELT just whether or not the transformed data is a concretion vs abstraction before being loaded into the warehouse?
So forgive me the broadness of the subject title, as it glosses over details, but I'm trying to understand how ETL and ELT differ in a definitive way. They seem very similar, to the point that you could describe either as ELTL. In both cases you start with raw data and end up with concrete transformed data in tables. It seems to be what happens in between is the difference, which can take many forms depending on the number/complexity of transformations from start to end state.
From what I know of ETL, the steps are as follows:

Extract the data from source systems and load into raw tables
Transform the data from raw tables into staging tables
Load the data from the staging tables into your warehouse

In some definitions of ELT I've seen, the steps seem to be:

Extract the data from source systems and load into raw tables
Transform the data notionally from the raw tables by creating views that represent the staging tables
Load the data from the staging views into your warehouse

But then I look at other articles about ELT, and it seems to be:

Extract the data from source systems and load into raw tables
Transform the data notionally from the raw tables by creating views that represent the staging tables
Congratulations, no more hard duplication of data from raw to transformed state, everything is views! Users just fire off their query and views nested n-levels deep instantly* return data!

*Results may vary, depending on whether you have millions of dollars to throw at cloud processing and/or large-scale quantum computing becomes available

No.
What you describe are all variants of ELT.
The difference between ETL and ELT is in where you do the "T". The "traditional" ETL flow would implement the "T" (data transformation) outside the DBMS, using a specialized tool like DataStage, Informatica, Talend, etc. The data transformed to the target model would then be simply loaded into the destination DBMS tables.
The ELT flow differs in that it is the destination database engine that performs the "T"; you load raw data into staging tables in the target database, then use the DBMS tools to "massage" them into the final form and copy to the destination tables. It is an appropriate alternative when the source and target data type impedance is relatively low and the DBMS in question has the tools and capacity to do the job.


How can I script administrator permissions on a SSAS server? I am an administrator for a bunch of SSAS Instances.

Sometimes I need to install or migrate SSAS instances to different machines or even different AD domains (we have a couple of different AD domains in a trusted relationship).

Same as with sql server, I need to be able to export and import logins and permissions between instances.

Also, as part of maintenance plan I like to save logins and permissions as well as backup ssas databases.

I would to script the administration logins via script either T-SQL or Powershell so that I can automate it.

at the moment, because I was in a rush I did it in this way:

Connect to the original server as administrator and script all the permissions into a mdx file.
Copy this script and run it on the target server.

How can I script administrator permissions on a SSAS server?

I highly recommend you check out Analysis Management Objects (AMO) - I use it to perform all maintenance tasks in SSAS such as creating Roles, Permissions, Databases and Models.

I prefer to use C# but there are plenty of examples online of using the same classes in PowerShell. You need to make sure you use the correct classes based on the type of install that you have (multidimensional or tabular). It looks like you have Tabular in your example above so please refer to the Microsoft.AnalysisServices.Tabular Namespace primarily.

This is how I would perform some tasks in c# using AMO:

// Copy Roles

var sourceServer = new Server();
var targetServer = new Server();

sourceServer.Connect("DEV1"); // replace with IP address, DNS etc...
targetServer.Connect("DEV2");

foreach (Role sourceRole in sourceServer.Roles)
{
    var targetRole = new Role();
    sourceRole.CopyTo(targetRole);
    targetServer.Roles.Add(targetRole);
}

targetServer.Update();

// Backup Database
foreach (Databse db in sourceServer.Databases)
{
    db.Backup($"{db.Name}.abf"); // or whatever file you want to back it up to.
}

You can use c# in maintenance plan script tasks or you can create standalone PowerShell scripts using the same classes. Once you get your head around the object model its pretty easy to do anything that you need.


Is it possible to check a date in SQL? I'm trying to write a Microsoft SQL Server stored procedure which takes an account ID, and returns an authentication ticket and whether the ticket is expired.

Something like this:

SELECT Authentication.ticket, Authentication.TicketExpires < getDate()
FROM Authentication WHERE Authentication.accountID = @id

The purpose is to return a row with two columns - the ticket, and a bit field (0 or 1) telling me whether the ticket is expired. This bit field isn't part of the table, the ticketExpires column is SMALLDATETIME.

Does anyone know the correct syntax for this?

I think this achieves what you're looking for. Hope this helps.

 SELECT
      Authentication.Ticket
      , CAST(CASE
           WHEN Authentication.TicketExpires < GetDate() THEN 1
           ELSE 0
      END AS bit) AS 'IsExpired'
 FROM Authentication
 WHERE Authentication.accountID = @id


pgadmin / postgresql - no pg_hba.conf entry for host I am trying to use pgAdmin to connect to a Postgresql server hosted by Elephantsql. However, I keep getting the error no pg_hba.conf entry for host:

no pg_hba.conf entry for host "173.18.54.553", user "db_user", database "db_name", SSL on FATAL: no pg_hba.conf entry for host "xxx.xx.xxx.xxx", user "hcexhbhx", database "postgres", SSL off

After doing some research, I edited my pg_hba.conf file:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            trust
    host    all             all             173.66.143.244/24       md5 (new entry)
    host    all             all             0.0.0.0/0               md5 (new entry)
    # IPv6 local connections:
    host    all             all             ::1/128                 trust
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     uzrrrrd                                trust
    #host    replication     uzrrrrd        127.0.0.1/32            trust
    #host    replication     uzrrrrd        ::1/128                 trust

And I added a line to my postgres.conf file:

listen_addresses='*'

And I restarted postgresql before attempting to connect again.
However, I'm still getting the no pg_hba.conf entry for host error.

Does anyone know what I might be doing wrong?

Thank in advance!

You're editing the wrong pg_hba.conf, connect locally with psql and run this

SHOW hba_file;

Then from outside edit that file. Perhaps you have two versions of PostgreSQL or you built your own. Or, you're restarting the wrong PostgreSQL. Perhaps make sure you're restarting the right one by connecting, again locally over psql, and running

SELECT pg_reload_conf();

Make sure you're editing the pg_hba.conf on the SERVER you're trying to connect to and not the CLIENT. (per @joanolo)


SQL Server 2012, rebuild not lowering avg fragmentation I have a script identifying what indices to rebuild.

select
    'alter index ' + name + ' on ' + @dbname + '.dbo.' + OBJECT_NAME(a.object_id) + ' rebuild;'
from sys.dm_db_index_physical_stats (DB_ID(@dbname), NULL, NULL, NULL, NULL) AS a
inner join sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent > 30

Which, e.g generates:

alter index FooIndex on FooDb.dbo.FooTable rebuild;

But after I execute the alter statement the index still has a high fragmentation value and if I execute it again it don't get any lower (50%). Any input on what could be wrong would be highly appreciated.

UPDATED: I manually increased the size of the DB which managed to lower the fragmentation on some of the indices, but still not all. Still have a couple around 50%.

//Daniel

Is it a small index? Small indexes will always show high levels of fragmentation. You can read more at Ola Hallengren's site which has a highly rated SQL Server maintenance package - look at the PageCountLevel setting, which specifies that Microsoft recommends leaving indexes under 1000 pages - with the link.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


Can two SQL “Always on” nodes have different datafiles position on disk? I have two Always on instances SQL server 2014 configured on two servers (primary read/write, secondary only sync). First instance has 13 db and the second has only one. I need to move all the transaction log datafiles from a disk to a new one. All the transction logs reside in a disk (for example D: on both nodes).
I would like to stop the sync from the secondary, move the transaction log position (for example from D: to E:) and then resync all.
In this way I will have a primary node working with transaction log on old D: and secondary node working on a new E: file system.
Can someone help me to understand if SQL Always on can work with different datafiles paths on the two nodes?
Second question (if the first is feasible): all the services will continue to run on the first node. When I will try to resync the secondary node, how much confidence can I have to achieve the activity without reconstructing the secondary from a backup?
[UPDATE]
Try to answer the below question for more detail about my second question.
I have those steps in my mind (but I don't know if they are correct)
Preliminary status: SERVER1 primary node. SERVER2 secondary. Two instances running.
One DB at a time cycle those three steps:

Stop sync on SERVER2 (SERVER1 working).
Move the transaction log from the old position to the new one on SERVER2 (It should be something like detach-update config-move datafiles-attach)
Start Sync

After all do this two:

Failover both instances to SERVER2
Do the same on SERVER1

The above steps should guarantee that all the instances continue to work without service outage (in my mind).
I'm trying to figure out how much safer it can be to perform those steps instead of creating a new disk and adding new tranlog datafiles on it without moving the old one (only stopping autogrowth).
Thanks so much

Having different file paths for secondary databases in an Always On AG setup is possible but not recommended, as shown by below quote from the microsoft docs:

We recommend that, if possible, the file path (including the drive
letter) of a given secondary database be identical to the path of the
corresponding primary database

Source
If you want to do it  anyway, there are restrictions and caveats as mentioned here:

If the file path (including the drive letter) of a secondary database
differs from the path of the corresponding primary database, the
following restrictions apply:

New Availability Group Wizard/Add Database to Availability Group Wizard: The Full option is not supported (on the Select Initial Data
Synchronization Page page),
RESTORE WITH MOVE: To create the secondary databases, the database files must be RESTORED WITH MOVE on each instance of SQL
Server that hosts a secondary replica.
Impact on add-file operations: A later add-file operation on the       primary replica might fail on the secondary databases. This
failure    could cause the secondary databases to be suspended.
This,in turn,    causes the secondary replicas to enter the NOT
SYNCHRONIZING state.


Why we can backup postgresql database backup without password? I want to backup my PostgreSQL database with pg_dump command that takes a lot of parameters.
One of them is --no-password.

Why this parameter is provided?

I mean if anyone can backup database, so what about security?

Read documentation on this: "If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail." This parameter doesn't mean that you can connect (and dump) without knowing password, it is just help for (backup) scripts where entering passwords isn't possible by hand.


SQL Agent not Running Please Help. I have been trying this for the past 3 days.

I need to send an alert mail when the SQL Server Agent is not running. That is, there can be instances where SQL Server is is running, but Agent is not running. I need to set up the same as startup procedure to check whether the agent is disabled or not when the service starts.I had already made the procedure and set up the same as start up procedure. I had also made a job based on the same, but had not scheduled.

Thanks in advance. Please help

Instead of having SQL Server monitor one of its subprocesses, why don't you have a script at the operating system level - WMI, PowerShell, what have you - periodically poll the services to make sure Agent is running? This seems a lot more foolproof than a startup procedure (which only runs when the SQL Server service starts, and potentially will finish before Agent starts, so it's not going to catch many scenarios - like when Agent suddenly fails or is forcibly shut down without touching the SQL Server service).

A job is absolutely not the way to do this. How can a job tell you that SQL Server Agent is not running? Catch-22. That's like your car driving itself to go get gas when it is out of gas.

Also, creating a watchdog for the Agent service seems way too reactive to me. Have you looked into why the service isn't starting or is getting shut down? The Windows event logs should have information about this.


How to scale my SQL Server 2008? My DB is SQL Server 2008 and I want to scale up the system by 5 fold (maybe more in the future).

The system currently has 95% read & 5% write from front end. And from backend actual data is bulk loaded using ETL.

I looked in to options like

Scaling with shared database (using SAN)
SODA (new caching mechanism)
Clustering in SQL Server 2008 R2

but all either allows only read or duplicate the existing data.

Can someone point me in the right direction (replication of data would be my last but one option).

Thanks
JC

Thanks all for the suggestions.

Looks like i am left with increase the hardware and for better performance at this point of time.

SAN setup would become more administrative work because the updates from data load would happen atleast once a day and we need to bring down the servers on SAN every time. And there is also some updates happening from front end.

Other thing i am planning to do is to divide the data on to two servers based on regions, which will reduce the load on each server to almost half.

To take a different approach, what is driving your read workload - Is it the application, or do you have a bunch of reports hanging off the system that are driving the load?  Depending on the nature of your workload you might be able to re-shuffle your hardware or push some of it off onto other machines.

A couple of ideas:

If you have a lot of read workload driven by reports (as opposed to application screens), consider moving those off onto a replicated server (more than one if necessary).  More extremely, if you have the option of doing this, you could create a data mart specificually tuned for the reporting.  Obviously this might depend on how up to date the data has to be.It should be possible to do this without having to interfere in the application code, so it's not as an extreme approach as one might think.  If you need it to be up to date you could do something with change data capture at the expense of extra complexity.
If your DB server is I/O bound but you have plenty of spare CPU capacity (or maybe you can upgrade the CPUs) you might get some mileage from placing some or all of your database on SSDs.

Without looking into the technical specifics of your problem it's hard to see what can actually be done with the workload.


How do I choose the right job owner? This morning I had three failed jobs (no biggy) used for custom reporting

DOMAIN\USER is no longer with the company.

  Error Message

  The job failed.  Unable to determine if the owner
  (DOMAIN\USER) of job Email Import Impacting Issues has server access
  (reason: Could not obtain information about Windows NT group/user
  'DOMAIN\USER', error code 0x534. [SQLSTATE 42000] (Error 15404)).

I've changed the owner to [sqlAdmin], which is one of our sqlAdmin owner accounts.

No, is not a good idea. There is a possible security issue underneath when using sa as owner of the job.

All jobs are ran through SQL Server Agent and it sets the security context for job execution based on the role of the user owning the job. So, if you use sa then you know what it will happen. By default, SQL Server Agent executes job steps under the SQL Server Agent service account irrespective of job ownership, or under the context of a proxy account. The exception to this rule is T-SQL job steps, which execute under the security context of the job owner. If the job owner is a member of the sysadmin role, then the job step executes in the context of the SQL Server Agent service account.

So, using sa as the job owner will cause all T-SQL job steps to execute as the SQL Agent service account, which is a system administrator account. A better option is to set a non-sysadmin account as the job owner, and explicitly grant only the required database permissions to this account.

So, what I would do is to create a specific user on the DOMAIN and give that user the needed rights to execute those jobs. And that user should not be a person in the company as you could have same issue in the future, if that person goes away and the account is closed...same error again.


Speed up creation of Postgres partial index I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4.

My data is completely static, so I am able to insert all data, then create all indexes.

In this 1.2TB table, I have a column named run_id that cleanly divides the data. We've gotten great performance by creating indexes that cover a range of run_ids. Here's an example:

CREATE INDEX perception_run_frame_idx_run_266_thru_270
ON run.perception
(run_id, frame)
WHERE run_id >= 266 AND run_id <= 270;

These partial indexes give us the desired query speed. Unfortunately, the creation of each partial index takes about 70 minutes.

It looks like we are CPU limited (top is showing 100% for the process).
Is there anything I can do to speed up the creation of our partial indexes?

System specs:

18 core Xeon
192GB RAM
12 SSDs in RAID
Autovacuums are turned OFF
maintenance_work_mem: 64GB (Too high?)

Table specs:

Size: 1.26 TB
Number of rows: 10.537 Billion
Typical index size: 3.2GB (there is a ~.5GB variance)

Table definition:

CREATE TABLE run.perception(
id bigint NOT NULL,
run_id bigint NOT NULL,
frame bigint NOT NULL,
by character varying(45) NOT NULL,
by_anyone bigint NOT NULL,
by_me bigint NOT NULL,
by_s_id integer,
owning_p_id bigint NOT NULL,
obj_type_set bigint,
seq integer,
subj_id bigint NOT NULL,
subj_state_frame bigint NOT NULL,
CONSTRAINT perception_pkey PRIMARY KEY (id))

(Don't read too much into the column names -- I've obfuscated them somewhat.)

Background info:

We have a separate team onsite that consumes this data, but really there are only one or two users. (This data is all generated via a simulation.) Users only start analyzing the data once inserts are finished and indexes are completely built. Our main concern is reducing the time required to generate usable data, and right now the bottleneck is index creation time.
Query speed has been completely adequate when using partials. In fact, I think we could increase the number of runs that each index covers, and still maintain good-enough query performance.
My guess is that we will have to partition the table. We are trying to exhaust all other options before taking that route.

BRIN index

Available since Postgres 9.5 and probably just what you are looking for. Much faster index creation, much smaller index. But queries are typically not as fast. The manual:

  BRIN stands for Block Range Index. BRIN is designed for handling very
  large tables in which certain columns have some natural correlation
  with their physical location within the table. A block range is a
  group of pages that are physically adjacent in the table; for each
  block range, some summary info is stored by the index.

Read on, there is more.
Depesz ran a preliminiary test.

The optimum for your case: If you can write rows clustered on run_id, your index becomes very small and creation much cheaper.

CREATE INDEX foo ON run.perception USING brin (run_id, frame)
WHERE run_id >= 266 AND run_id <= 270;

You might even just index the whole table.

Table layout

Whatever else you do, you can save 8 bytes lost to padding due to alignment requirements per row by ording columns like this:

CREATE TABLE run.perception(
  id               bigint NOT NULL PRIMARY KEY
, run_id           bigint NOT NULL
, frame            bigint NOT NULL
, by_anyone        bigint NOT NULL
, by_me            bigint NOT NULL
, owning_p_id      bigint NOT NULL
, subj_id          bigint NOT NULL
, subj_state_frame bigint NOT NULL
, obj_type_set     bigint
, by_s_id          integer
, seq              integer
, by               varchar(45) NOT NULL -- or just use type text
);

Makes your table 79 GB smaller if none of the columns has NULL values. Details:

Configuring PostgreSQL for read performance

Also, you only have three columns that can be NULL. The NULL bitmap occupies 8 bytes for 9 - 72 columns. If only one integer column is NULL, there is a corner case for a storage paradox: it would be cheaper to use a dummy value instead: 4 bytes wasted but 8 bytes saved by not needing a NULL bitmap for the row. More details here:

How do completely empty columns in a large table affect performance?

Partial indexes

Depending on your actual queries it might be more efficient to have these five partial indices instead of the one above:

CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 266;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 267;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 268;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 269;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 270;

Run one transaction for each.

Removing run_id as index column this way saves 8 bytes per index entry - 32 instead of 40 bytes per row. Each index is also cheaper to create, but creating five instead of just one takes substantially longer for a table that's too big to stay in cache (like @Jürgen and @Chris commented). So that may or may not be useful for you.

Partitioning

Based on inheritance - the only option up to Postgres 9.5.
(The new declarative partitioning in Postgres 11 or, preferably, 12 is smarter.)

The manual:

  All constraints on all children of the parent table are examined
  during constraint exclusion, so large numbers of partitions are likely
  to increase query planning time considerably. So the legacy inheritance
  based partitioning will work well with up to perhaps a hundred partitions;
  don't try to use many thousands of partitions.

Bold emphasis mine. Consequently, estimating 1000 different values for run_id, you would make partitions spanning around 10 values each.

maintenance_work_mem

I missed that you are already adjusting for maintenance_work_mem in my first read. I'll leave quote and advice in my answer for reference. Per documentation:

  maintenance_work_mem (integer)

  Specifies the maximum amount of memory to be used by maintenance
  operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 64 megabytes (64MB). Since only one of these
  operations can be executed at a time by a database session, and an
  installation normally doesn't have many of them running concurrently,
  it's safe to set this value significantly larger than work_mem. Larger
  settings might improve performance for vacuuming and for restoring
  database dumps.

  Note that when autovacuum runs, up to autovacuum_max_workers times this memory may be allocated, so be careful not to set the default
  value too high. It may be useful to control for this by separately
  setting autovacuum_work_mem.

I would only set it as high as needed - which depends on the unknown (to us) index size. And only locally for the executing session. As the quote explains, a too-high general setting can starve the server otherwise, because autovacuum may claim more RAM, too. Also, don't set it much higher than needed, even in the executing session, free RAM might be put to good use in caching data.

It could look like this:

BEGIN;

SET LOCAL maintenance_work_mem = 10GB;  -- depends on resulting index size

CREATE INDEX perception_run_frame_idx_run_266_thru_270 ON run.perception(run_id, frame)
WHERE run_id >= 266 AND run_id <= 270;

COMMIT;

About SET LOCAL:

  The effects of SET LOCAL last only till the end of the current
  transaction, whether committed or not.

To measure object sizes:

Measure the size of a PostgreSQL table row

The server should generally be configured reasonably otherwise, obviously.


SQL Agent Jobs - What Permissions Required We have a application that allows a user to view a list of SQL Agent jobs and create new ones.

However, the application displays a form asking for user credentials.
The user is a member of the SQLAgentOperatorRole in msdb.

I tested by making the user a member of sysadmin and this works.

Obviously I do not want to make that user sysadmin so what permission level am I missing?

SQLAgentUserRole allows users to create jobs and to manage jobs that they create.

The SQLAgentOperatorRole allows users all the rights assigned to the SQLAgentReaderRole along with the permissions necessary to execute local jobs that they do not own.

Member of SQLAgentReaderRole role allow the user to see any SQL Agent job located on the server instance, even the jobs in which that user does not own. The user can see the job, along with any history saved to the job. However, the group is not allowed to execute the jobs


Undo Deleted Records My Database has Full BackUp Plan on Every Morning on 01:00 Am. Today I came to work and Deleted all of the Records in a table by mistake in sql server management Studio by running this command

delete * from Table1 where 10=10

instead of

delete * from Table1 where Id=10

this happened around  13:00 pm. worktime starts from 8:00 am . is it possible to undo the deleted Records? if I want to recover it from the backup file, I loses the records entered between 8:00 and 13:00. what is the best solution for this problem?

I read this post Recover deleted records but it was not the same as mine. and I read it was a duplicate post. but again i couldn't find the the main post for this problem.

That's called a Major Whoopsie.

Assuming you didn't use transactions around the delete you are stuck with scanning the log files and redoing all the INSERT and UPDATE queries. If you don't have a query log then it's manual entry time.

If it's any consolation the Government of Alaska had a similar issue a while ago, further compounded by the backup tapes not being readable. You've probably lost a morning, they lost the whole thing.


Design concerns for using 3x bit or one char(1) or one integer in table for holding status of item I need to extend my existing table with status column.
I am in doubt how to do that in most appropriate way regarding of future use of that status and performances of existing table.

My table is around 3,7 MIL records, and it is heavy read and write table.
Table represent stock (warehouse) of items (goods). It holds floats for many kind of prices and amounts, primary key is composite by item_id and warehouse_id.

Here is how I see ways to implement adding status of an item.

To add three more columns for statuses, each one column will represent some of statues,
Something like 'allow_sell','allow_orders','allow_returns'
I like this way because it uses BITs, (is it faster than varchar, or integer ?) and easy to understand what each status represents and which combination of statuses are seted on an item. In this way

To add one integer column called status_id and to add one more table statuses_of_item where I can hold description of each status and add statues as many as I wish.

To add one varchar(10) column where status will be written as dresption ('denay_all','allow_all', 'allow_orders_wo_sels' etc)

Have in mind that status is part of business logic and it uses only internal, Representation of statues are not displayed in SIMPLE WAY, statues not shown to users, it is only way how system should handle some situation in different places.

I think that 70% of items inside table will have default (same) status.
My database system is MS Sql Server 2008R2.

How I should design my table to include stautes ?

In my experience, trying to encode multiple data points into a single column always ends up being more trouble than it's worth. Sure, it seems cool and clever to use BITWISE operators, but there are many things that go wrong and it won't always be efficient to test those bits without cumbersome and unintuitive workarounds. It's the same reason we stay away from storing comma-separated lists, JSON strings etc. in a single column - eventually you care about viewing or filtering on those distinct bits which you now have to extract, sometimes expensively.

With the information I have, my vote is for three separate BIT columns. They will still collapse to similar storage patterns as a single column with the three bits on/off, and can be made more efficient individually and across the board in several ways, including:

data compression
sparse columns
filtered indexes (e.g. WHERE allow_returns = 1)

Someone else advocated for three CHAR(1) columns. These do not benefit from storage collapse and also require a check constraint, making them less than ideal in my mind.

Now, my answer might change if you say, "well, what if I might add 15 other attributes in the future?" I certainly don't think it's wise to build the columns this way if they're not relatively static - changing the schema (and therefore all of the code and interfaces to it) for every new or changed attribute is going to be a royal pain. So in that case you might want to consider EAV - where the attributes are not part of the metadata but part of the data. There are a lot of objections to EAV, mostly around performance and the difficulty in enforcing constraints (in this case unlikely to be an issue if all of these attributes are either on or off), but it worked quite well for us at my previous job. You might model it like this:

CREATE TABLE dbo.Attributes
(
  AttributeID TINYINT PRIMARY KEY,
  Name VARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.ItemAttributes
(
  ItemID INT NOT NULL
    FOREIGN KEY REFERENCES dbo.Items(ItemID),
  AttributeID TINYINT NOT NULL
    FOREIGN KEY REFERENCES dbo.Attributes(AttributeID),
  Status BIT NOT NULL,
  PRIMARY KEY(ItemID, AttributeID)
);

And again, you can have filtered indexes here to make certain queries much more efficient, such as (imagine the AttributeID for "allow returns" is 10):

CREATE INDEX optAllowReturns ON dbo.ItemAttributes(ItemID)
  WHERE AttributeID = 10 AND Status = 1;

If you have certain attributes that are not on/off (for example, three states of manufacture or shipping), you can change the Status column to:

Value TINYINT NOT NULL

This can double as an on/off value for attributes that are represented that way, and as tri- or more-state value for attributes that require more than simple on/off. You can also reflect which type is which in the metadata of the dbo.Attributes table.


1 big merge publication or N small ones? Scenario

We have two servers, each one with its own Microsoft SQL Server

Serv_Home
Serv_Web

In both servers, a local program works with its own local database and inserts/updates the tables. The data is shared between the 2 servers, and for this purpose we have several replicas. Serv_Home is the master server (the distributor of the publications).

A specific table must be copied from Serv_Home to Serv_Web (not viceversa), so the publication is a Snapshot.

For all the rest, a Merge publication is used. We decided to create 5 Merge publications, one for each group of tables which are more or less related. The reason why we have 5 instead of a big single one is simply convenience. Everything works fine.

Lately though, we are experiencing a high CPU usage in Serv_Home (like 50% sometimes) due to the sqlservr.exe process.

Question

Is it better to have a big single Merge publication instead of N small ones? Could this be a performance bottleneck?

I know that the best way is to try it myself, but we are in a production environment and it would be messy to redo it all. Some documentation or good reasoning would be appreciated.

I've been looking for an answer to no avail.

However, I've found a place where some advantages are mentioned:

Easier to maintain.
Multiple data streams going to the subscription database at once (hopefully).
If the merge fails for a publication, only this publication has to be fixed and reinitialized.

So I think there would be no performance improvement having just 1 big one publication. On the contrary, if the data streams are parallel, we can even get a better performance when splitting the merge in N small publications.


sql expiry date use Trigger OR Schedule a Job I have this table :

Now, i like the way when the date expires. EndTime < GETDATE()

expire field is true.

What method should I use? use a trigger to true or a schedule job that run before run any  query  in this table?

something like below should get you started ...

CREATE TABLE [dbo].[patientInfo]
(
id int,
patientid int,
  starttime datetime,
  endtime datetime,
  doctorid int
)
GO

ALTER TABLE patientInfo ADD
    expire AS CASE WHEN endtime < GETDATE()THEN 1 ELSE 0 END
GO

    declare @now datetime=getdate()

INSERT into patientInfo (id, patientid, starttime, endtime, doctorid)
SELECT 1,1,@now,@now-1,1
UNION ALL SELECT 2,2,@now,@now+1,2
UNION ALL SELECT 3,3,@now, @now+2,3
UNION ALL SELECT 4,4,@now, @now-2,4

SQLFiddle here


Change Permissions of MSSQL Server generated files I am routinely taking a BAK file and restoring it. As a result of this action, an MDF file is generated from it by MSSQL.

My problem is that after the detaching the database I need to be able to move the MDF file programmatically and the user that is going to do this needs to not be in the Administrators group or to be the Default SQL server account. Is it possible to configure MSSQL server to that when it creates an MDF file such as this the permissions of the file include a group of my own arbitrary choosing?

It seems logical that this an MSSQL server setting somewhere, that there is a list of permissions that get added to files when they are created but I am having a great deal of trouble getting Google to distinguish between this case and other similar but unrelated cases.

By default .mdf file permissions are granted to Administrators Group and SQL Service Account when a database is restored.


Write concern in mongodb All,

Lets say devoper sets the write concern as Majority in MongoDB

As a DBA can i have the access to change the write concern of whole cluster or only the developers has the rights to do through coding ?

In replica sets we can do the change through conf settings but i beleive it wouldn't override the default one

Write concern can be set differently for every DML operation and that overrides the default write concern value. So, if default value is majority, developer can choose w:0 or even w:all for update operation, if he or she wants to do so.


UPSERT - Is there a better alternative to MERGE or @@rowcount? I was wondering if you have encountered a T-SQL command similar to the concept of UPSERT?  Performing INSERT|UPDATE operations using options (1) or (2) seems overly complex and error prone.

OBJECTIVE

To ensure that the desired record (in this case employee_id 1) is up-to-date WITHOUT having to having to essentially write the same query twice.

CONTEXT

table name: employee
employee id: has a primary key, and identity proerty is set to true

OPTIONS

execute a SQL UPDATE... check @@rowcount = 0 and @@error = 0... execute SQL INSERT if required

con: you effectively have to write the same query twice, once as an
insert, once as an update
con: more code = more time typing
con: more code = more room for error

https://stackoverflow.com/questions/1106717/how-to-implement-a-conditional-upsert-stored-procedure "Update using @@rowcount"

execute a SQL MERGE

con: you effectively have to write the same query twice, once as an insert, once as an update
con: more code = more time typing
con: more code = more room for error

http://technet.microsoft.com/en-us/library/bb510625.aspx "T-SQL Merge"

execute a SQL UPSERT (feature does not exist)

pro: you define the data-to-table relationship once (let SQL Server worry about whether or not it is an INSERT or an UPDATE)
pro: less code = faster implementation
pro: less code = lower probability

UPSERT EXAMPLE

UPSERT employeee (employee_id, employee_number, job_title, first_name, middle_name, surname,  modified_at)
VALUES (1, '00-124AB37', 'Manager', 'John', 'T', 'Smith', GetDate());

if employee_id 1 does not exist: MS SQL executes a INSERT statement
if employee_id 1 exists: MS SQL executes and UPDATE statement

I think the simple answer to this is no. MERGE was Microsoft's answer to the more convoluted UPSERT logic. And you didn't even list the worst approach:

IF (SELECT COUNT ... ) > 0
    UPDATE
ELSE
    INSERT

I just threw up in my mouth a little typing that, but it's actually the one I see most often.

In any case, if MERGE is not flexible or powerful enough for you, I suggest you submit a feature request to Microsoft at http://connect.microsoft.com/sql/ and explain, thoroughly, your business case. As long as you stick to real advantages of your proposed syntax over MERGE, you've got my vote. If you hang too much on the "error-prone" part, I'm not as likely to buy in. Why? Because you can fat-finger any statement.

That said, I don't think there's anything anyone here can do for you specifically. You should investigate the potential problems with MERGE:

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/


Limit for HASH JOINs I have a very complex query (who hasn't :-)). In this query is a JOIN to a table like this one (of course the original has some more columns):

CREATE TABLE dbo.tbl_detail (id bigint PRIMARY key,
                   descr varchar(300),
                   txt varchar(MAX),
                   descr_p varchar(300)
                  );

There are two joins to this table in the query:

LEFT JOIN dbo.tbl_detail td1 ON td1.id = main.detail_id
LEFT JOIN dbo.tbl_detail td2 ON td2.id = main.second_id
The query returns ~200k rows.
td1 outputs all columns
td2 outputs all columns except [txt] (varchar(max))

Problem:

td2 uses an HASH JOIN
but td1 uses an NESTED LOOKUP (over 200k rows; no statistic problem, since the estimates are correct)
when I remove the [txt] column from the td1 output it uses a HASH JOIN too
when I alter the column to VARCHAR(5000) (or smaller) it uses a HASH JOIN
when I alter the column to VARCHAR(8000) it uses the NESTED LOOKUP again
OPTION (HASH JOIN) or LEFT HASH JOIN would work, but produces an extrem slow query plan

Question:
Why does it not always use the HASH JOIN? Is there a length limit (per column or sum of all output columns)?

PS: Microsoft SQL Server 2014 (SP2) Developer Edition

It's important to remember that the query optimizer doesn't choose each join individually and independently from everything else going in on the query. Joins have different properties which means that a different join type may be better or worse depending on other joins or operations in the plan. I can generate test data that shows behavior similar to what you see here, but the reproduction depends on available memory for the server.

Put 10k rows each into two tables:

CREATE TABLE dbo.tbl_detail (
    id bigint PRIMARY key,
    descr varchar(300),
    descr_p varchar(300),
    txt_max varchar(MAX),
    txt_5k varchar(5000)
);

INSERT INTO dbo.tbl_detail WITH (TABLOCK)
SELECT TOP (10000)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('DESCR', 60)
, REPLICATE('descr_p', 40)
, NULL
, NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE TABLE dbo.OUTER_TABLE (
    id bigint primary key,
    id2 bigint
);

INSERT INTO dbo.OUTER_TABLE WITH (TABLOCK)
SELECT TOP (10000)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

Here is the query to test:

SELECT o.*, d.id, d.descr, d.descr_p
FROM OUTER_TABLE o
LEFT OUTER JOIN tbl_detail d ON o.ID2 = d.ID
ORDER BY o.ID;

If I add d.txt_5k to the SELECT list I naturally get a hash join:

However, if I add d.txt_max to the SELECT list I naturally get a loop join:

The key here is that a loop join preserves the order of the outer table. That means that a nested loop join can avoid an explicit sort for some queries. Queries with a hash join may need to do the sort. The cost of the sort depends on (among other things) the estimated size of the data. The query optimizer estimates the size of the data based on the estimated number of rows and the data types. With the VARCHAR(5000) column I get an estimated size of 27 MB and with the VARCHAR(MAX) column I get an estimated size of 42 MB. On my machine the 42 MB sort is expected to spill to disk so that makes the hash join plan much more expensive than the loop plan for the VARCHAR(MAX) column.

We can see this more clearly by using hints to force the different plans so we can compare them. Here's the comparison for the VARCHAR(5000) query:

And the comparison for the VARCHAR(MAX) query:

Of course, this may not be why you're seeing the behavior that you're seeing for your particular query. I just wanted to provide an example of why changing a data type could change the join type. I'm going to assume that there's a problem caused by the loop join and that you really need it to be hash join. The OPTION (HASH JOIN) hint may not be a good workaround because it will force every join to be a hash join in the query. The LEFT HASH JOIN hint may not be a good workaround because it implies a FORCE ORDER hint which means that the query optimizer will not be able to change the join order. Perhaps you can move the problematic part of the query to a temp table and apply whatever hints are needed to the smaller query. It's really hard to say much without more information.

You could also try a FORCESCAN hint to encourage the hash join, although I'm loathe to recommend it. Be sure to test carefully:

SELECT o.*, d.id, d.descr, d.descr_p, d.txt_max
FROM OUTER_TABLE o
LEFT OUTER JOIN tbl_detail d WITH (FORCESCAN) ON o.ID2 = d.ID
ORDER BY o.ID
OPTION (MAXDOP 1);


MongoDB Ops Manager - CPU Time - No data available We have 5 groups and enabled on all groups monitoring agent. We use Ops Manager 1.6.2.251.

All metrics get collected and are available in a nice graph.
CPU Time is not available on all groups. Our VMs are OpenStack based with RHEL7.

Howto debug that? Found nothing relevant to CPU time in /opt/mongodb/mms/logs

You must have munin-node installed, configured and reachable (on port 4949) by the MMS agents in order for CPU and other system data to be collected.  This is all covered in the documentation.


Improving performance of insert..on duplicate key update You know the feeling when you think you've had the best idea but then realise you might not know what you're talking about? This is one of those moments, and I'm hoping my idea can be confirmed or refuted.

I have a database table that saves activity in the form of "date, sender, recipient, action count", that counts how many interactions one player of the game (which you just lost :p) gives to another on a given day.

Right now, it uses insert..on duplicate key update (with the unique key being (date, sender, recipient) and while it works I have noticed that it causes deadlock errors during periods of intense activity.

I have fixed deadlock errors in other parts of the game by avoiding "insert..select from", "insert ignore" and "insert..on duplicate key update" and this appears to be one of the few, if not the only one left.

My new idea is as follows:

Attempt update
If the number of affected rows is zero...

Perform insert

Essentially it's become "update..on not found insert", and done manually to try and avoid locking rows.

Caveats I've thought of:

Race conditions are not an issue because the client-side code already includes batching operations, and a given user should therefore only be sending one request of this type at a time. If the client bypasses this code (never trust the client!) then the worst case is their displayed numbers will be lower than expected.
This may execute two queries instead of one, but I believe an update matching zero rows is basically a no-op.

That's about it really. Am I missing anything here or should this solve my issues?

Any approach will lock something.  Even your SELECT.  You cannot avoid it.  The best approach is to minimize the locks.

Normalization

Let's look at the bigger picture.  If the purpose of this is "normalization", then perform the the upsert outside the main transaction.  This prevents many possible deadlocks and speeds up the processing.

If you are doing "batch normalization", see my tips here .  Those two statements (not unlike your 2-steps) are designed to efficiently copy multiple items into a normalization table, and do it without "burning" AUTO_INCREMENT ids.  Again, do them with autocommit=1, not inside of some bigger transaction.

If the main transaction eventually rolls back, then the worst that could happen is that you inserted something unused in the normalization table.  This is not harmful.

INSERTing 100 rows in a single statement is typically about 10 times as fast as using 100 1-row INSERTs.  This speed, alone, helps avoid deadlocks.

Regardless of what prevention measures you take, deadlocks will occur.  Write your code to re-run the entire transaction, as this is usually the
'right fix'.

Likes, upvotes, etc

If the purpose of the UPDATE is a high-volume 'increment', then it may be best to move the counter out of the main table into its own table (together with a minimal PK).  That way, "regular" processing is not being held up by "Likes".


Passing Arguments from INSERT / UPDATE to Trigger I need a help with a trigger, this trigger I need to pass a value that comes from the queue column in the asterisk.queue table.

CREATE TRIGGER gerarconf
AFTER INSERT OR UPDATE
ON asterisk.fila
FOR EACH ROW
EXECUTE PROCEDURE asterisk.registra_gerar_conf_fila('FILA','XXXXX');

In place of XXXX I need to pass the value of the queue column that came from the insert or update of the asterisk.queue table

Use NEW or OLD depending if you need the inserted or updated record.
Postgres documentation

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:
NEW
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is unassigned in statement-level triggers and for DELETE operations.
OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is unassigned in statement-level triggers and for INSERT operations.

CREATE TRIGGER gerarconf
AFTER INSERT OR UPDATE
ON asterisk.fila
FOR EACH ROW
EXECUTE PROCEDURE asterisk.registra_gerar_conf_fila('FILA',NEW.column);


How can I include more query in one query? These are my tables:

tbl_answers =>
  aid
  qid
  answer
  uid
  dateposted
  emailnotify
  namedisplay
  status
  isbestanswer

tbl_questions =>
  qid
  question
  detail
  mcid
  cid
  uid
  answercount
  dateposted
  status
  showname
  emailnotify
  question_type_id

I woud like to update those question that has got more answers than it's counted in:
tbl_questions =>  answercount

How can I update the rows which have got more questions than it's counted? (Without php loop)

This is how I'm trying now:

UPDATE tbl_questions
  JOIN (
    SELECT tbl_questions.qid, COUNT(*) AS n
    FROM tbl_questions JOIN tbl_answers ON tbl_answers.qid = tbl_questions.qid
    WHERE answercount = "0"
    GROUP BY id
  ) AS T USING (tbl_answers.qid)
SET answercount = n
WHERE n > 0

IT says me You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.qid) SET answercount = n WHERE n > 0' at line 7

Wouldn't this be sufficient for your update, since the subquery providing the update value evaluates to 0 if there aren't any answers for your question.

UPDATE tbl_questions q
SET q.answercount = (SELECT COUNT(*) FROM tbl_answers a WHERE a.qid = q.id)
WHERE q.answercount = 0


How can I divide COUNT by 6? So, probably spelled my question wrong, anyway.
I'm trying to run a test scenario where some values are duplicated, so they should be 6 but if duplicated they are 12 or 18 and so on,

Currently I'm finding them by
    having COUNT(*) > 6

but I also want to make sure that the return result would be only those that can be divided by 6 , like 12/6, 18/6 would be returned, but something like 15/6 would not, so I suppose its 'whole' numbers - integer.

select rr.review_id
from review_rating rr
inner join review rw on
rr.review_id = rw.id
group by review_id
having COUNT(*) > 6

So, I only want to filter the results whose count can be divided by 6 and the result would be an integer/whole number.

select rr.review_id
from review_rating rr
inner join review rw on rr.review_id = rw.id
group by review_id
having MOD(COUNT(*),6)=0


Mysql LOAD DATA INFILE 100,000 csv records perfect BUT 300,000 records - 0 rows inserted The following query imports 100,000 perfectly, but larger files nothing happens.  No errors, no rows loaded.

Any clues you can offer are appreciated.  I spent hours looking into max file and memory limits and need help.  Thanks!

LOAD DATA LOCAL INFILE 'C:\\temp\\masterplay\\bigone.csv'
INTO TABLE mytable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(`MARK`,`SERIAL NUMBER`, ...thirty some columns...)

0 rows inserted. (Query took 2.2385 sec)

The above performs perfectly and FAST on 100,000 records, does nothing on 350,000 records.  When the CSV is broken down to 100,000+ records, the import works.

Example CSV file size is 314,946 records. Average 551 characters per row. Max row length is 575. Versions: MYSQL 5.6.17, PHP 5.5.12 WAMPSERVER 2.5, Windows 10 Pro 16 GB RAM. Unable to determine failure mode, so no reference to cutoff on file.

Per suggestions below, tried same SQL on other versions and Linux, same problem.

UPDATE:

Ideas below focused me on the source CSV files.  Thus far, I discovered resaving the CSV (even though it checks good using CSVed application and appears identical in name and content) somehow fixes the file so that it will import.  I have hundreds of large files to process and yet do not understand what the difference is.

The files are comma separated with no text delimiters.  After I re-save the file, it still appears to be identical in content but roughly 300K larger file size.  Does a CSV file type have a header or meta data that makes a difference??

Files checked with hex editor and answer revealed.

My CSVs differed in that rows either terminated with:

  Hex 0D CARRIAGE RETURN (\r), or

  Hex 0D CARRIAGE RETURN (\r) AND 0A LINE FEED (\n)

This is the line of code:

LINES TERMINATED BY '\r\n'

or

LINES TERMINATED BY '\r'

In my case, taking out the \n took care of it - either CSV imports perfectly.

If this line does not match the CSV, you get the silent treatment.  The SQL import will run without error yet no rows will import.


Implementation of an employee and customer scenario in database So, I have a scenario of a movie theatre and in this scenario employee can be a customer too... if I create two entities, customer and employee ... and make emp_id of employee a foreign key in customer then it makes employee a customer too.

Customer: cust_id, Name, age, emp_id
Employee: emp_id, Name, age

But when I do this, data of employee gets repeated in customer. What should I do?

I would suggest creating the ERD a bit differently, like this:

Users (contains data for both customers and employees, and their shared attributes such as name and age)
Roles (this would contain a list of roles that define a person's set of permissions. "Customer" and "Employee" could be two such roles)
UserRoles (this would be a many-to-many relationship table connecting Users to Roles. In other words: A User can have one or more Roles)

If there's no need for additional roles other than Customer and Employee, and also if the permissions set of a Customer are always contained within that of an Employee, and also if an Employee doesn't have attributes that a Customer doesn't, then I would even rather add a column "is_employee" to the Users table and this would make both Roles and UserRoles unnecessary.

If, on the other hand, Customers and Employees are generally very different entities with different attributes (other than, let's say, name and age), then I'd suggest that you'd create 3 tables:

Users
Customers
Employees

The Users table would contain the shared attributes only (i.e. name and age).
The Customers and Employees tables would have their own unique attributes that are not shared, and both would have a foreign key "user_id" linking to Users.

If we're talking conceptually, what you'd want to do, generally, is create some sort of entity that would be used by both Customers and Employees (Users in my example), and thus avoid duplicating the same attributes in more than one table.

This is rather basic normalization methodology.

If you're unfamiliar with it, I'd strongly recommend you would study it a bit:

https://www.google.com/search?q=database+normalization+tutorial


How to select multiple columns between columns in Oracle SQL? A question that was asked among a few developers, now deferred to DBA experts:

Is there a way to select multiple columns within a table instead of SELECT'ing columns piecemeal?  For example, let's say example table contains 26 columns respectively titled by letters (e.g. A, B, C, etc.).  Suppose I would like to retrieve all rows from columns H, I, J, K, L, M, N, O, P- is there a more efficient way to begin the SELECT statement of the following query?

SELECT H, I, J, K, L, M, N, O, P FROM table;

Perhaps something like so?

SELECT H-P FROM table;

Thank you!

This is a crazy idea and would makes future maintenance really tricky.

It is not possible to guarantee that column order will never change.  There are plenty of situations where column order changes could potentially be beneficial.  It may happen accidentally (update the wrong database) or it may be done for performance reasons (e.g. moving mostly NULL columns to the end).

Even if it is possible - don't do it.


Install SP 3 on SQL Server 2008, where merge replication is running I have a SQL Server 2008 and I should install Service Pack 3 for it.

On my server there are several databases replicated, and mobile devices sync data via merge replication.

Can problems occur, if I install SP3, especially with my mobile devices (where SQL CE 3.5 is running).

I can't update all of the mobile devices.

You should be able to apply the SP3 without dropping any merge replication publications.  The order of upgrade should be to upgrade (1) distributor, then (2) publisher, then (3) subscribers.

The SP3 is for SQL Server 2008, not the SQL CE 3.5 mobile tools.  Since you are running merge replication then I assume that you have SQL CE 3.5 SP2.  So all should be well.

However:  Do you have a test system and test mobile devices?  If so, test your upgrade in the test system first. I always like to deal with suprises in a test system before applying changes to Production.


Release MySQL Memory and Performance Tunning I am new to the MySQL database. I have recently installed MySQL 5.7 and configured the my.ini file. Please find the below copy of the file.

The server is a dedicated server for MySQL database and the configuration of the server is:

OS = Windows 8.1 (64 bit),
RAM = 16GB (this can be automatically extended up to 32GB),
Processor = Intel Xenon 2.30 GHz (4 processors),
Size of the MySQL database = approx. 100GB,
Concurrent connections = Maximum up to 100

My questions are:

I have initially allocated 12GB RAM to Innodb buffer pool. I can allocate up to 24GB to Innodb buffer pool, so that, MySQL can use extra memory in case of heavy load. I want MySQL to release extra memory after completion of query executions/processes. Is there any way we can do it?
Do we need to make any changes in the my.ini file if any variable(s) are configured wrongly and also to improve the performance of the database?

my.ini file:

character-set-server=utf8,
default-storage-engine=INNODB,
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION",
max_connections=301,
query_cache_size=0,
table_open_cache=2000,
tmp_table_size=333M,
thread_cache_size=10,
myisam_max_sort_file_size=100G,
myisam_sort_buffer_size=4G,
key_buffer_size=8M,
read_buffer_size=64K,
read_rnd_buffer_size=256K,
innodb_flush_log_at_trx_commit=1,
innodb_log_buffer_size=16M,
innodb_buffer_pool_size=12G,
innodb_io_capacity = 2000,
innodb_read_io_threads = 64,
innodb_log_file_size=128M,
innodb_thread_concurrency=0,
innodb_write_io_threads = 64,
innodb_autoextend_increment=64,
innodb_buffer_pool_instances=8,
innodb_concurrency_tickets=5000,
innodb_old_blocks_time=1000,
innodb_open_files=300,
innodb_stats_on_metadata=0,
innodb_file_per_table=1,
innodb_checksum_algorithm=0,
back_log=80,
flush_time=0,
join_buffer_size=256K,
max_allowed_packet=4M,
max_connect_errors=100,
open_files_limit=4161,
query_cache_type=0,
sort_buffer_size=256K,
table_definition_cache=1400,
binlog_row_event_max_size=8K,
sync_master_info=10000,
sync_relay_log=10000,
sync_relay_log_info=10000,
explicit_defaults_for_timestamp=1,
innodb_lock_wait_timeout=120,
transaction-isolation=READ-COMMITTED,
innodb_support_xa=0,
autocommit=1,
event_scheduler=ON

Update:

We are on the Cloud Network. The default RAM on the server is 16 GB. There is a facility to increase (up to 32 GB RAM) or decrease the RAM automatically based on OS load.

For example, if I allocate 20GB RAM to INNODB_BUFFER_POOL_SIZE then MySQL server can use the memory up to 20GB during the heavy load/pick hours. However, I want MySQL to release the memory up to 16GB during the non-pick hours. So that, we cannot charged for 20GB RAM (i.e. for extra 4GB RAM during non-pick hours).

MySQL will not release global buffers after they are used (innodb_buffer_pool, key_buffer_size, query_cache_size, etc). It will release memory taken by per-thread buffers (join_buffer_size, sort_buffer_size, etc) but only after the thread is removed and not in the thread_cache.
The innodb_buffer_pool_size can be tuned dynamically in 5.7, but it is a very invasive operation:

When initiating a resizing operation, the operation does not start until all active transactions are completed. Once the resizing operation is in progress, new transactions and operations that require access to the buffer pool must wait until the resizing operation finishes. The exception to this rule is that concurrent access to the buffer pool is permitted while the buffer pool is defragmented and pages are withdrawn during an operation to decrease buffer pool size. A drawback of allowing concurrent access is that it could result in a temporary shortage of available pages while pages are being withdrawn.

Is the additional buffer pool worth it? This query will give you an idea of the current maximum memory that will be used by the InnoDB buffer pool:
SELECT ENGINE, SUM(DATA_LENGTH)/1024/1024/1024 as DATA_LENGTH_IN_GB,
    SUM(INDEX_LENGTH)/1024/1024/1024 AS INDEX_LENGTH_IN_GB,
    SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as TOTAL_IN_GB
FROM information_schema.TABLES
WHERE ENGINE IN ("InnoDB")
    AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
GROUP BY ENGINE
ORDER BY DATA_LENGTH DESC;

However, that is the total InnoDB size of data+indexes. The working set is what is going to matter during peak times, so this will include your most active tables/rows. If your working set already fits in memory, allocating more is not going to buy much for you. If it doesn't fit in memory, you're going to want to increase the buffer pool regardless of peak/non-peak traffic.
I would recommend reading the internal details section of the MySQL documentation of the InnoDB Buffer Pool as a great primer.
As for the rest of your configuration, the only thing I will say is that your temp_table_size=333M is not doing what you think it is, and is likely too high.
First:

The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values.

This means, with max_heap_table_size defaulting to 16MB, the internal memory tables are going to be converted to on disk temporary files long before reaching 330MB.
Second, you might have increased temp_table_size to avoid temporary on disk table usage. But, there are scenarios where MySQL can never use in-memory temporary tables:

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

Presence of a BLOB or TEXT column in the table

Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes for binary strings or 512 characters for nonbinary strings. (Before MySQL 5.7.3, the limit is 512 bytes regardless of string type.)

Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used

The SHOW COLUMNS and the DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.

I would recommend setting temp_table_size and max_heap_table_size to 32M or 64M (max) and then examining queries for some of the conditions mentioned above. If you can't avoid on-disk temporary tables, hopefully your cloud provider puts the tmpdir on SSD.


SQL Server Docker image on Mac: Storing a Database I am having reasonable success running SQL Server for Linux on Docker for Mac, as per the instructions on https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-docker. The article also notes that:

  Volume mapping for Docker-machine on Mac with the SQL Server on Linux image is not supported at this time.

This, of course, makes it difficult to do any more than really simple testing on the Mac if you have to re-install the database every time you start up Docker.

Has anybody a suggested strategy for maintaining a database beyond the current session?

This is a known issue: mapping volumes on Macs isn't supported yet. You can follow that Github issue for more news, like when it's fixed.

Until then, no, people aren't doing anything more than simple testing with that combination of tools (Mac, Docker, Linux, SQL Server).

If you need to do real, productive development on a platform that's supported today, use:

The current supported version of SQL Server (2016, as of this writing)
Running on a currently supported OS (Windows, as of this writing)
If you insist on using a Mac as your hardware layer (as I do too), then the easiest way to bridge these technologies is a hypervisor like VMware Fusion or Parallels


Create a check constraint across multiple schemas in PostGIS I'm a little new to this so apologies up front if my vocabulary is incorrect.

I'm trying to create a check constraint that determines whether a new point is located within an existing polygon. The issue is that the new point is created in one schema, and the existing polygon geometry is located in another schema.

I believe I need to create a function using
ST_Within(NEW.geom, existing.geom) and then call that function as a constraint. However, I don't know how to deal with the syntax when using schema-qualified tables, and also be able to use NEW.geom in the function.

Any help is greatly apreciated!

You need a trigger that runs on UPDATE and INSERT (when a row would otherwise be modified). As a-horse-with-no-name said in a comment:

  Not possible. A check constraint can only validate column values of one row in one table.