Get ip postgresql master from recovery.conf using non-superuser I try on PostgreSQL 9.3 Slave to get ip postgresql master from recovery.conf using non-superuser.

Created FUNCTION f_showfile

CREATE FUNCTION f_showfile(myfile text) RETURNS text AS $x$
BEGIN
RETURN pg_read_file(myfile, 0, 1000000);
END;
$x$ LANGUAGE PLPGSQL SECURITY DEFINER;

Added grant execution to f_showfile for non-superuser

GRANT EXECUTE ON FUNCTION public.f_showfile (text) TO the_user;

Get lines with primary_conninfo string from recovery.conf

SELECT *
FROM regexp_split_to_table(f_showfile('recovery.conf'),'\n') t(a)
WHERE a ~ '^ *primary_conninfo';

Output:

-----------------------------------------------------------------------------------------------------------------------------------------
 user=repmgr port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres host=1.1.1.1 application_name='postgresql-93'

But i cannot get host from recovery.conf

My error. How get ip postgresql master from recovery.conf using non-superuser?
Thank you

SELECT regexp_split_to_array
  (SELECT *
   FROM regexp_split_to_table(f_showfile('recovery.conf'),'\n') t(a)
   WHERE a ~ '^ *primary_conninfo'),
       ' ');
ERROR:  syntax error at or near "select"
LINE 1: select regexp_split_to_array(select * from regexp_split_to_t...

OR

SELECT *
FROM regexp_split_to_table
  (SELECT *
   FROM regexp_split_to_table(f_showfile('recovery.conf'),'\n') t(a)
   WHERE a ~ '^ *primary_conninfo'),
     ' ');
ERROR:  syntax error at or near "select"
LINE 1: select * from regexp_split_to_table(select * from regexp_spl...

IMHO and as I pointed out in my comments I think there is a missing parentheses in your command. Just after first SELECT regexp_split_to_array

I've tested on a postgres server (a turnkey VM) using a copy of a recovery.conf file, and this is the result:

root=# SELECT regexp_split_to_array (
  (SELECT *
   FROM regexp_split_to_table(f_showfile('recovery.conf.sample'),'\n') t(a)
   WHERE a ~ '^ *primary_conninfo'),
       ' ');
                                   regexp_split_to_array
--------------------------------------------------------------------------------------------
 {primary_conninfo,=,'user=repmrg,port=5432,sslmode=prefer,sslcompression=1,host=10.0.0.1'}
(1 row)

Using unnest:

root=# SELECT unnest(regexp_split_to_array (
  (SELECT *
   FROM regexp_split_to_table(f_showfile('recovery.conf.sample'),'\n') t(a)
   WHERE a ~ '^ *primary_conninfo'),
       ' '));
      unnest
------------------
 primary_conninfo
 =
 'user=repmrg
 port=5432
 sslmode=prefer
 sslcompression=1
 host=10.0.0.1'
(7 rows)

Filtering by host:

root=# SELECT *
root-# FROM   (SELECT unnest(regexp_split_to_array (
root(#                       (SELECT *
root(#                        FROM regexp_split_to_table(f_showfile('recovery.conf.sample'),'\n') t(a)
root(#                        WHERE a ~ '^ *primary_conninfo'), ' ')) as elem) T
root-# WHERE elem LIKE '%host%'
root-# ;
      elem
----------------
 host=10.0.0.1'
(1 row)

root=#


Restrict two specific column values from existing at the same time I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed.

I would appreciate any help creating a constraint that will enforce this.

CREATE TABLE example
(
  example_id    serial PRIMARY KEY,
  example_state CHAR(1) NOT NULL
);

ALTER TABLE example ADD CONSTRAINT
  example_constraint
CHECK (example_state = 'a' OR example_state = 'b' OR example_state = 'c');

One row per rare type
If you want to allow 0-n rows with type 'c' and only 0-1 rows for type 'a' and 'b' (each), you can combine a simple CHECK constraint with a partial unique index:
CREATE TABLE example (
  example_id    serial PRIMARY KEY,
  example_state "char" NOT NULL CHECK (example_state IN ('a', 'b', 'c'))
);

CREATE UNIQUE INDEX example_unique_exception_idx ON example (example_state)
WHERE example_state <> 'c'; -- column is not null;

Related answer:

PostgreSQL multi-column unique constraint and NULL values

An exlusion constraint would be a related concept, but it won't work with multiple entries for 'c'. You could use NULL instead of 'c' to make that work.

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

Also note the special data type "char" (in double-quotes!), which seems to be perfect for your simple purpose (unless it's just a simplification for the purpose of the question). It only occupies a single byte - as opposed to char(1), which needs at least 2 bytes on disk and 5 in RAM.
Singleton row with any rare type
To restrict to a single row overall that does not have type 'c', use a partial unique index on an expression:
CREATE UNIQUE INDEX example_single_exception_idx ON example ((example_state <> 'c'))
WHERE example_state <> 'c';


MySQL- if conditon fails (#1064 error) I have the following query:

SELECT
    CONCAT(user1.name, ' ', user2.name)
    IF(user1.salary < user2.salary) as userSalary
FROM `users` AS user1
LEFT JOIN users AS user2 ON user1.id = user2.id
ORDER BY user1.salary ASC

I am trying to find all users which have a lower salary than the next user (in the same table). The table has a simple structure:

Fields:

ID (PK) | name | age | address | salary

This is the error I am getting at the moment:

#1064 - 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 'IF(user1.sallary < user2.sallary) as userSallary
FROM `users` AS user1
LEFT JO' at line 3

What am I doing wrong here?

Please have a look at IF statement at MySql Docs

  IF search_condition THEN statement_list
      [ELSEIF search_condition THEN statement_list] ...
      [ELSE statement_list] END IF

Or at IF() function:

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL),
IF() returns expr2. Otherwise, it returns expr3.

Your sentence should be something similar to:

SELECT
    CONCAT(user1.name, ' ', user2.name)
    IF(user1.salary < user2.salary, user1.salary, user2.salary) as userSalary
FROM `users` AS user1
LEFT JOIN users AS user2 ON user1.id = user2.id
ORDER BY user1.salary ASC


What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint? I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-)

I would like to add a constraint with CASCADE DELETE rule however I don't know if it's possible for this case. Another option is to create an ON DELETE trigger, so...

My question is:
What would you prefer, CASCADE DELETE constraint rule (if it's possible here) or ON DELETE trigger ?
Or would you create a DB model for this simple case (imagine StackOverflow's voting scheme) different way ?

Why am I asking:
I've read somewhere that CASCADE DELETE constraint rules are internally nothing else than triggers and that some people here don't prefer the constraint rules at all. I know, quite vague question, but I would like to have my DB clean from a not existing relationship records.

The important thing:
I'm creating the application which should be able to run on the SQL Server as well as on MySQL database engines, but both should support all, the CASCADE DELETE constraint rules as well as ON DELETE triggers, I guess.

Thanks!

I don't think you will see a performance difference between trigger and using cascade. I would prefer ON CASCADE DELETE just because in my opinion it better describes model and reveals creator's intention. Also, if you later use one or another ORM, it will be able to build relationship between entities (surely, I'm talking about 'Data First' approach)if it's implemented through foreign key constraints, but not through the triggers.


Pull data from multiple tables in a view or function There are 3 tables from which I need to pull data, and print it in a specific way.

http://sqlfiddle.com/#!15/59481/8

Refer to this question to see how the query was produced. The version in the fiddle working partially, but I need to modify the query to produce a different output.

I have the table project_report which is storing data for keyword:

1. If a keyword has data for a particular day it will store it in `project_report`
2. A keyword might have no data to store for a particular day.
3. A keyword might store multiple rows of data for a particular day (the data is uniq)
4. A keyword might not have data for a particular day, but it might have for a different day.

I believe to solve my problem a DB function will be the best approach, because I would like to be able to specify a date and get all the data from project_reports for each keyword.

I don't know how to write this query, to produces the below results, also I don't know what will be better, to have a view or a db function, I think that with a view in order to select a specific date, I will need for each keyword and each date to produce those outputs. The other problem is that I would also like to use these results in a chart, which will take the data for a range, for that I think a view is better, then calling a function n times.

So if keyword_id: 1 has in the project_report:

ID: 1, keyword_id: 1, project_id: 1, position: 1, created_at: '2014-07-09'
ID: 2, keyword_id: 1, project_id: 1, position: 2, created_at: '2014-07-09'

For the selected date 2014-07-09 I should get (please see that position will be the smallest position instead of [1, 2]):

keyword_id: 1, project_id: 1, position: 1, yesterday_pos: 0, last_week_pos: 0, last_month_pos:0 created_at: '2014-07-09'

If keyword_id: 2 has in the project_report:

ID: 3, keyword_id: 2, project_id: 1, position: 3, created_at: '2014-07-09'
ID: 4, keyword_id: 2, project_id: 1, position: 4, created_at: '2014-07-08'
ID: 5, keyword_id: 2, project_id: 1, position: 5, created_at: '2014-07-08'
ID: 6, keyword_id: 2, project_id: 1, position: 4, created_at: '2014-07-03'

For the selected date 2014-07-09 I should get:

keyword_id: 2, project_id: 1, position: 3, yesterday_pos: 4, last_week_pos: 4, last_month_pos: 0 created_at: '2014-07-09'

If keyword_id: 3 has in the project_report:

ID: 7, keyword_id: 3, project_id: 1, position: 10, created_at: '2014-07-08'
ID: 8, keyword_id: 3, project_id: 1, position: 11, created_at: '2014-07-03'

For the selected date 2014-07-09 I should get:

keyword_id: 3, project_id: 1, position: 0, yesterday_pos: 10, last_week_pos: 11, last_month_pos: 0 created_at: '2014-07-09'

If keyword_id: 4 has in the project_report:

`nothing`

For the selected date 2014-07-09 I should get:

keyword_id: 4, project_id: 1, position: 0, yesterday_pos: 0, last_week_pos: 0, last_month_pos: 0 created_at: '2014-07-09'

I suggest a radically new approach with crosstab() from the additional module tablefunc. You need to install it once per database. Detailed instructions:

PostgreSQL Crosstab Query

SELECT k.name, keyword_id, project_id, now()::date AS the_date
     , t AS today, y As yesterday, w AS week, m AS month
FROM   crosstab(
   $$
   SELECT rn
        , pk.keyword_id
        , pk.project_id
        , d.created_at
        , COALESCE(pr.pos, 0) AS pos
   FROM  (SELECT *, row_number() OVER () AS rn FROM project_keyword) pk
   CROSS  JOIN (
      VALUES
         (now()::date)
       , (now()::date - 1)
       , (now()::date - 7)
       ,((now() - interval '1 month')::date)
     ) d(created_at)
   LEFT JOIN (
      SELECT keyword_id
           , project_id
           , created_at::date AS created_at
           , min(position) AS pos
      FROM   project_report
      GROUP  BY keyword_id, project_id, created_at::date
      ) pr USING (keyword_id, project_id, created_at)
   ORDER  BY pk.rn, d.created_at
   $$

  ,$$
   VALUES
         (now()::date)
       , (now()::date - 1)
       , (now()::date - 7)
       , ((now() - interval '1 month')::date)
   $$
   ) ct (rn int, keyword_id int, project_id int
       , t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);

Returns:

name    keyword_id   project_id   the_date     today   yesterday   week   month
Cheese  1            1            2014-07-11   1       1           1      0
Monitor 2            1            2014-07-11   2       2           2      0
Apple   3            1            2014-07-11   9       9           9      0
Apple1  4            1            2014-07-11   0       0           0      0
Iphone  5            1            2014-07-11   0       33          34     0

Explain

We need a single unique column per group for crosstab(). Since you don't have that per (keyword_id int, project_id) I am adding a surrogate key with row_number() to project_keyword.
JOIN that to a set of dates you are interested in (today, yesterday etc.). One row per date.
LEFT JOIN to project_reports to attach the position for each date if available. Default to 0 with COALESCE.
Cross-tabulate to get a single row with one position column per given date.
Finally JOIN to keyword just to add the name. Add the current date and remove rn from the result while being at it.

Aside: "name" is not a good name. I'd use something descriptive instead.


How to setup encryption on a column that joins two tables in two different databases What is the best method to encrypt a column that exists in two tables (BUID) in two different database on a 2012 server, at the end should be able to join on the encrypted columns between two tables across databases.

use testdb1

create table table1
(
    BUID number(10),
    ACCOUNT_BALANCE Money
)

use testdb2

Create table table2
(
    BUID number(10),
    CUSTOMER_FNAME varchar2(30),
    CUSTOMER_LNAME varchar2(30)
)

Basically want to encrypt the BUID in both tables and be able to crate a view  that joins across two databases.

create view vw_cust_balance
as
    select
        a.buid ,
        a.account_balance
        b.customer_fname,
        b.customer_lname
    from
        testdb1..table1 a
    inner join
        testdb2..table2 b on a.buid = b.buid

Can someone point me to a resource or example that resolves situation above?

The answer to your question depends on whether or not you empasize security over performance and potentially how many records we are talking about. You can create a secure solution that won't scale very well by creating the same symmetric key on both servers by using identical values for key_souce and identity_value for the CREATE SYMMETRIC KEY command. All server side encryption in SQL Server is non-deterministic, meaning that the same value encrypted twice with the same key will result in different encrypted values. Therefore, you won't be able to join the data without decrypting it. If the data is decrpted on each server before it's combined, then you will want to add an SSL Certificate directly to the SQL Server to encrypt at the transport level.

Alternatively, you could put the key on only one server and always perform a remote join on the server with the key after encrypting both tables with it. You would still want an SSL certificate on the instance if you require that the result is returned unencrypted.

Another idea would be to use an Extensible Key Management system, which is a hardware security module that offloads encryption processing and would allow the same key to be used by both systems.

Since server side encryption is non-deterministic, you won't be able to index it. There are two potential solutions to that issue. One is to create a hash on part of the original data and index that to help improve performance and the other would be to use an deterministic method for encryption that is either external or encapsulated in a CLR that is installed on both systems. Be careful with hashes because they are deterministic and can be reproduced easily, so avoid hashing the entire value. If anyone can use the  CLR or external encryption who is not authorized, then it's possible that a hash table can be created by running all possible values through the system and then it can be joined back to the original data.

To encrypt in SQL Server, you will also need to use varbinary columns instead of numerics.


How should permissions for anonymous users be modelled? I'm designing a web application. There will be users who log into the site, but also anonymous, non-authenticated users, i.e. any member of the public who accesses the site. Users will be assigned to groups, and those groups will be assigned permissions. Some site content will be accessible only to authenticated users, while other content may be marked as publicly accessible. I'm considering how best to model facts such as "this item is publicly accessible". Options that occur to me so far:

Create a special group that a special user "Anonymous" belongs to. Assign permissions to the "Anonymous" group, and pretend that unauthenticated users are all the Anonymous user.
On each content entity, include an attribute such as is_publicly_accessible.

Any thoughts on which is the right way to go? The first option feels like a more consistent approach to permissions, but the notion of some users and groups being special/fake/dummy doesn't feel right.

I'd go for option 1. With this approach there are no "hacks" in the app for anonymous users. The standard security model continues to apply with the only difference being that if you don't know who the user is (ie, they haven't authenticated) that you pretend they are the anonymous user, which in effect they are.

Option 1 is also the approach used by most (all?) web servers and file servers: they use an "anonymous" or "nobody" user.


Large Transaction Log Backups in Service Broker-enabled system We have a system that uses Service Broker for asynchronous messaging, with a fairly high throughput of messages.  We don't really care about the persistent features of SSB in this scenario -- the messages are pretty much just immediate notifications and if they are not processed immediately when the intended receiver application is up we end up just discarding any backed-up messages when the application restarts.

What we've noticed is that our database is manifesting very large transaction log backups, most of which are filled up with SSB transactions.  This is complicating our backup management and test restore scenarios, and generally just making life harder on the DBA side of the fence.

Are there any tips or tricks that can be used to minimize the transaction log overhead of SSB messages in the case where we don't really care about having them available for restore?

You could use a different database for your SSB load. It seems like your SSB data is transitory in nature. That would allow you to either switch to SIMPLE recovery model for that DB or throw away the log backups in case you need FULL for some other reason like mirroring.

I don't think you'll be able to reduce log usage for SSB operations. I'm interested to see other answers but usually log usage is not tunable for OLTP-style transactions. They are always fully logged. Minimal logging only applies to special cases.


How much time should a backup of a 100Gb database take using streaming server setup? I've installed barman on a dedicated server to backup all my postgresql servers. One of the server is now using 100Gb space and the instruction barman backup myserver takes 23 hours. I find it too long, but I have no reference points. Is it common? What should I investigate to improve such situation? Here are the settings for streaming the server

; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; Backup settings (via pg_basebackup)
; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
backup_method = postgres
;streaming_backup_name = barman_streaming_backup

; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; WAL streaming settings (via pg_receivexlog)
; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
streaming_archiver = on
slot_name = barman
;streaming_archiver_name = barman_receive_wal
;streaming_archiver_batch_size = 50

minimum_redundancy = 3
last_backup_maximum_age = '3 DAYS'
retention_policy = 'RECOVERY WINDOW OF 16 DAYS'

You are plausibly in the range where network bandwidth is limiting.  I get 9 hours versus your reported 23, but that could be accounted for by overhead, inefficiencies or competing network usage.

You can try turning on network compression, which requires changing the backup method, which in turn will require you to configure "ssh_command".

ssh_command=ssh postgres@192.168.0.15
backup_method = rsync
network_compression=true

Of course this will impose some CPU overhead on the server for it to do the compression.


Sql Insert into with duplicate key Ok so, I'm still a beginner in databases. I have this code:

$sql="
  INSERT INTO complaints_members
    (password, complaint)
  VALUES
    ('$mypassword','$submit')
  ON DUPLICATE KEY UPDATE
    complaint='$submit' ;
";

This simply updates my complaint in the existing entry. How can I insert a new entry with the same key, instead of updating the old one?

I'm not very familiar with the terms of SQL, so I'm sorry in advance.

Your SQL statement specifies the behavior you are seeing with ON DUPLICATE KEY UPDATE. This syntax is shorthand for logic that causes an insert only if the key does not exist, and an update of the row identified by that key if it does exist. See the MySQL documentation for more info.

Since ON DUPLICATE KEY UPDATE only operates on primary keys or unique indexes, you cannot insert another row with the same key anyway. If multiple records with the same identifier are required in the table, you must make that identifier something other than the primary key or remove the unique constraint on the index.


Huge 0.000001 and 0.000002 files and so on, in mysql data folder In mysql data folder unknown files were created like this:

This is my.ini content:

[client]
host= .
port= 3306
socket= "MySQL"

[mysql]
no-auto-rehash

[mysqld]

max_allowed_packet = 800M
thread_concurrency = 8
skip-external-locking
port=3306
basedir="c:/zpanel/bin/mysql/"
datadir="e:/hafez/bin/mysql/mysql5.5.24/data/"
character-set-server=utf8
default-storage-engine=MYISAM
max_connections=200
query_cache_size = 128M
table_cache=256
tmp_table_size=256M
thread_cache_size=8
myisam_max_sort_file_size=32G
myisam_sort_buffer_size=205M
key_buffer_size = 384M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 2M

innodb_fast_shutdown=0
innodb_additional_mem_pool_size = 16M
innodb_log_buffer_size = 8M
innodb_buffer_pool_size=1800MB
innodb_log_file_size = 256M
innodb_thread_concurrency = 16
innodb_lock_wait_timeout = 120
innodb_file_per_table

innodb_doublewrite = 0
log-bin = 0
innodb_support_xa = 0
innodb_flush_log_at_trx_commit = 0

tmpdir= "e:/hafez/bin/mysql/mysql5.5.24/tmp/"

enable-named-pipe
skip-federated
server-id = 1

default-time-zone   = "SYSTEM"
log_error           = "c:/zpanel/logs/mysql/mysql.err"
pid_file            = "mysql.pid"
general_log         = 0
general_log_file    = "c:/zpanel/logs/mysql/mysql.log"
slow_query_log      = 0
slow_query_log_file = "c:/zpanel/logs/mysql/mysql-slow.log"

[myisamchk]
key_buffer_size = 1024M
sort_buffer_size = 1024M
read_buffer = 8M
write_buffer = 8M

[mysqldump]
quick
max_allowed_packet = 16M

[mysqlhotcopy]
interactive-timeout

I recently added some innodb tables and these files appeared!

These files are in root mysql data folder(e:/hafez/bin/mysql/mysql5.5.24/data) and aren't in any database folder.

What are these files?

Is it safe to delete this?

These are your binary logs.
For some reason, your config file lists:

log-bin = 0

Did you mean by that to disable binary logs? Well, instead, you've simply named them as "0". Which is why you get all these files: 0.000001, 0.00002 etc.

If you want binary logging, better name them properly:

log-bin = mysql-bin

(this will make files named mysql-bin.000001, mysql-bin.000002 etc)

If you don't want binary logging, just comment the line:

#log-bin = mysql-bin

Typically, binary logs are desired: they are essential if you want to set up replication, or do logical point-in-time recovery.

If you do decide to disable them, and are convinced you do not need them, then you can also purge them:

mysql> PURGE MASTER LOGS TO '0.000025';

will delete all logs up to and excluding given log file.


SQL Server 2012 setup fails immediately (Error result: -2067529723) I am trying to install SQL Server Enterprise Core 2012 w SP1 64bit (as downloaded from the Microsoft Partner Program site) on a 64-bit Windows 2008 R2 SP1 server. When simply right-clicking on SETUP.EXE in the root and selecting Run as Administrator, seemingly nothing happens. When I run it from a Command Prompt (Run as Administrator), I get this:

  An error occurred during the SQL Server 2012 Setup operation.

  Error result: -2067529723
  Result facility code: 1220
  Result error code: 5

  For more information, review SQL Server 2012 Setup logs in your temp
  folder.

No log files were written anywhere. How can I find out what this error is trying to tell me and what I can do to resolve it?

I rebooted the VM, and I was then able to get the Setup to work as expected. I am disappointed that the error message did not provide any indication of what was wrong or what needed to be done to correct the situation, but rebooting seems to have resolved whatever the issue was.


Delete Trigger - Linking to a table with a PK made up of three columns I'm currently writing a number of "AFTER INSERT, DELETE" triggers that will be used to sync data between two identical tables, in different instances. The issue that I'm having right now, is getting the DELETE part working, due to how the table has been designed.

The table is called "MobileUsageReportPackageLog" and the PK is made up of the following columns, UserId, DeviceId, PackageNumber.

I'm now unsure how to link this table to the "deleted" table, to ensure that if a record is deleted, then the trigger also deletes the same record in the other table.

I have a feeling that I'm missing something simple, but if anyone has any suggestions or recommendations for a situation like this, I'd love to hear them.

Thanks in advance!

EDIT: I should mention that as a combination, all three column will be unique. As individual columns however, they will contain duplicate data.

Code as of now:

IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        DELETE murpl
            FROM [Audits].[dbo].[MobileUsageReportPackageLog] murpl
                JOIN deleted del ON murpl.[UserId] = del.[UserId]
                JOIN deleted del2 ON murpl.DeviceId = del2.[DeviceId]
                JOIN deleted del3 ON murpl.[PackageNumber] = del3.[PackageNumber]

CREATE TABLE A
 (
     ID1 INT,
     ID2 INT,
     ID3 INT,
     FOO INT,
     BAR INT,
     PRIMARY KEY (ID1, ID2, ID3)
 );

 CREATE TABLE B
 (
     ID1 INT,
     ID2 INT,
     ID3 INT,
     FOO INT,
     BAR INT,
     PRIMARY KEY (ID1, ID2, ID3)
 );
 GO

 CREATE TRIGGER TRG_A_INSDEL
 ON [A]
 AFTER INSERT, DELETE
 AS
 BEGIN

     /* Insert new rows */
     INSERT INTO B
         SELECT * FROM inserted;

     /* Delete those rows that match PK */
     DELETE B
     FROM
         B
     JOIN
         deleted d
         ON d.ID1 = B.ID1
         AND d.ID2 = B.ID2
         AND d.ID2 = B.ID2;
 END
 GO

 INSERT INTO A VALUES (1, 1, 1, 1, 1);
 INSERT INTO A VALUES (2, 2, 2, 2, 2);
 INSERT INTO A VALUES (3, 3, 3, 3, 3);
 GO

SELECT * FROM B;
GO

ID1 | ID2 | ID3 | FOO | BAR
--: | --: | --: | --: | --:
  1 |   1 |   1 |   1 |   1
  2 |   2 |   2 |   2 |   2
  3 |   3 |   3 |   3 |   3

DELETE FROM A WHERE ID1 >= 2;
GO

SELECT * FROM B;
GO

ID1 | ID2 | ID3 | FOO | BAR
--: | --: | --: | --: | --:
  1 |   1 |   1 |   1 |   1

db<>fiddle here


Which DB for 1 table with 100 Million rows per year? I've a questionnaire with 70 questions and need to store the answers.

Problem: it's 100 Million records per year.

I've experience with different types of storages, but never had to deal with these huge numbers. Now I'm afraid that every wrong decision might lead to big negative impact.

Information about the data:

I was thinking about 1 table with 70 columns
The columns are already defined and might be slightly adjusted after a while (+/- 10 columns)
The data type of each column is mainly Integer and String with mostly 2 chars, max. 10 chars.
No nested (tree) structures needed
No flexible data types needed
No joins needed

Data definition (pseudo-code)

COLUMN           |   TYPE   | MAX. LENGTH
-----------------------------------------
id               | Integer  | 10
questionnaire_id | Integer  | 10
answered_at      | Datetime | -
answered_by      | Integer  | 10
answer1          | Integer  | 2
answer2          | Integer  | 2
answer3          | Integer  | 2
answer4          | Integer  | 2
...
answer35         | String   | 2
answer36         | String   | 2
...
answer70         | String   | 2

Priorities:

Storing big data
Run standard aggregate functions (avg, min, max, count, ...), filter and sort in an acceptable time

Is there any best practice or a checklist to follow which reduces my options and therefore wrong decisions?

Thank you in advance!

Edit: normalized, inspired by Dave

# questionnaire
- id (PK, AI)

# questions
- id (PK, AI)
- questionnaire_id (FK)
- label

# submits
- id (PK, AI)
- questionnaire_id (FK)
- answered_by
- answered_at

# answers
- id (PK, AI)
- submit_id (FK)
- question_id (FK)
- value      // Integers only (strings are mapped: A => 1, B => 2)

With that table an no extra keys/indexes you have 160 bytes per row, so 100,000,000 rows per year is about 16GByte per year. Any decent DBMS (SQL Server, postgres, [YourFavouriteDBHere], ...) should be able to cope with that and (assuming proper indexing) query it efficiently if you have appropriate hardware/virtual resources. The extra space taken by keys and other indexes shouldn't balloon the space requirements much, if it does then it is likely that the structure is not optimal more generally.

So simply storing the data should not be a worry.

Some databases support compression, sparse tables, and other techniques, that will help considerably with this structure if space is your primary concern, but before considering them first ensure that this is in fact the structure that you need.

As others discuss in the comments, your current structure may not be optimal for the analysis that you need to perform, so you would need to edit your question to include such details if you need help there. A key thing in all database design is to consider your desired outputs as well as your inputs.

  Unfortunately I don't know every required select-query at the moment.

You must have some idea of the sort of reports you expect to run against the data. It doesn't need to be every query you'll ever run, or even close to, but optimising for some expected reports is far better than blindly ploughing in data in the hope that it might be optimal for something/anything that will eventually come up.

Where at all possible, do not design based purely on your inputs.

Without any idea of the outputs, perhaps yes: just throw the data into a table like that with indexes on the date at least as that is very likely to be one of the key ways you'll want to filter/partition/aggregate the data in reports, and transform the data into something else via ETL when you do know what analysis you need to perform. But if you have an idea of the outputs to start with, you might be able to avoid having to create and maintain two structures (one for active recording and one for reporting) and the process to transform data from one into the other. Of course, this two structure system might be optimal, but we simply can't tell you one way or the other without more detail.


Monitor SQL TCP traffic on the client I have a Windows 7 client that has a data driven application that connects to our SQL Server 2008 R2.
How can I view all the traffic between this software and the DB?
I would mainly like to see queries and responses.

Probably the simplest would be to run a server-side or client-side trace using SQL Server Profiler.

Another option might be to use a protocol analyzer, such as WireShark, or one of the many mentioned in this search result: https://duckduckgo.com/?q=tcp+protocol+analyzer&t=ffsb


How to set 'Enable identity insert' for all the tables at once during importing data in SQL Server? I am trying to copy a database from a remote server. I don't have access to its backups or file system. My destination database on my dev machine needs to get all the data plus the same tables structures including primary keys with identity settings and the rest of the constraints.

The Import/Export facility has the option to set the identity insert as shown below but I have to do this manually per table. I have over 300 tables to copy and most of them have identity primary keys. This is going to be very painful process doing the copy multiple times.

Is there a way to set this identity insert setting all at once for all the tables? Or some other reliable way of copying databases in such a setup?
I am using SSMS 7.3 against SQL Server 2014.

You can do all tables at once in the Import / Export wizard. On the Select Source Tables and Views Page you can select the tick box in the source bar, once selected you can select Edit Mappings and you'll see Enable Identity Insert at the bottom.


How can a connection for one DB block a connection to another DB in SQL Server? We have an interesting scenario.  For weeks, we have been experiencing 'outages' on SQL Server 2008 -- where our websites have slowed to a crawl and mass timeouts for all customers who have DB's living on that server.  When looking at SQL Server for answers -- we found that we had many SUSPENDED connections and lots of BLOCKING, via the BLKBY column from sp_who2.  To fix the issue, we would first save the sp_who2 output for later analysis, then kill all the blocked/suspended PID's.  This apparently freed things up and transactions were able to continue flowing normally.

After analysis, we found 2 things:

We were experiencing DEADLOCKS.  Connection 1 was blocked by Connection 2, but Connection 2 was also blocked by Connection 1.  SQL Server seemed unable to choose a victim so presumably queries started queuing up, waiting.
The PID's for the deadlocked queries were from DIFFERENT DB's!

Not sure if this matters, but -- the issues were solved after looking at performance counters and seeing that we essentially were suffering from severe MEMORY PRESSURE (PLE, Lazy Writes, etc..) so we doubled the RAM and issues went away.

We are at a loss in understanding how queries from seemingly UNRELATED DB's can block each other.  One theory is that both unrelated queries were waiting for the same PAGE in memory (cache)??  Any insight is welcome!

So there's a couple of ways that this can happen.

The queries in the blocking database reference objects within the blocked database.
The queries are waiting for tempdb to allocate pages and one query is blocking another query.
There is memory pressure and queries are waiting for memory to be allocated so they have to wait their turn in the memory allocator.

If you download and put sp_whoisactive on the server it'll give you a lot more information about why things are blocking if this happens again.

Given your note about the memory pressure you probably were suffering from either #2 or #3 on my list above.  Odds are it was memory not being able to be allocated fast enough, but it's possible that because you didn't have enough memory stuff was spilling to tempdb faster than tempdb to do "stuff".


Connection to MySQL fails via PHP while command-line works fine I have a script to install MySQL 5.7 and run tests on Travis CI (running Ubuntu 12).

The script contains these lines, to install MySQL in non-interactive mode, with no password:

add-apt-repository 'deb http://repo.mysql.com/apt/ubuntu/ precise mysql-5.7-dmr'
apt-get update

echo mysql-community-server mysql-community-server/root-pass password "" | debconf-set-selections
echo mysql-community-server mysql-community-server/re-root-pass password "" | debconf-set-selections

DEBIAN_FRONTEND=noninteractive apt-get -o Dpkg::Options::=--force-confdef -o Dpkg::Options::=--force-confold -q -y install mysql-server libmysqlclient-dev

The install runs smoothly, and using the mysql command-line utility, I can connect to the server using no password:

mysql --user=root -e "SELECT VERSION();"

+-----------+
| VERSION() |
+-----------+
| 5.7.8-rc  |
+-----------+

Now if I try to connect using PHP PDO:

new PDO('mysql:host=localhost', 'root', '');

I get the following error:

  SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO)

I tried to allow root to connect from any host:

GRANT ALL ON *.* to root@'%' IDENTIFIED BY '';
FLUSH PRIVILEGES;

Without success.

What can be the problem?

Found the answer in the MySQL manual, Pluggable Authentication:

  As of MySQL 5.5.7, the server authenticates clients using a plugin. Selection of the proper account row from the mysql.user table is based on the user name and client host, as before, but the server authenticates the client by determining from the account row which authentication plugin applies for the client.

The MySQL package creates a root user using the auth_socket authentication plugin, so only connections via a socket are permitted, and the username of the Linux account is checked as well.

The mysql command uses a socket by default, and runs as root in my script, so it was allowed to connect.

According to the PHP manual, a PDO MySQL connection to localhost also uses a socket; but PHP is run as an unprivileged user, so the connection is refused by the auth_socket plugin.

I fixed my script by adding this line:

mysql --user=root -e \
"UPDATE mysql.user SET plugin='mysql_native_password'; FLUSH PRIVILEGES";

And it now works fine.


INSERT INTO with UNION Alias ERROR This question is derived from the following post UNION query not working

Fails:

INSERT INTO AssetControl
SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;

Works:

INSERT INTO AssetControl
SELECT U.* FROM
(SELECT * FROM CSVImport
UNION
SELECT * FROM AssetTemp) AS U

I don't understand why an alias U needs to be created in order for the records to be successfully input into the AssetControl table. Can anyone shed some light on this?

The short answer is: Access is weird ;)

Long answer is that Access has certain restrictions in the SQL syntax it supports. It requires parentheses around some constructions, for example when more than 2 tables are joined.

I think the same applies here. It's not that an alias is required, it's that (Access) syntax requires parentheses in INSERT INTO tableA SelectQueryB; when SelectQuery is a UNION query, even though the SQL standard does not require them. I think[1] that this works, too (and doesn't need an alias):

INSERT INTO AssetControl
(SELECT * FROM CSVImport
UNION
SELECT * FROM AssetTemp) ;

[1]:  It appears that this is not allowed either.

Your solution works because it's equivalent to the standard use of INSERT .. SELECT:

INSERT INTO AssetControl
SELECT U.*
FROM SomeTable AS U ;

where SomeTable is replaced by a derived table:

(SELECT * FROM CSVImport
UNION
SELECT * FROM AssetTemp)


MIN, DATEADD, DATEPART, DATEDIFF functions cause table scan A performance issue has been brought to my attention.

It appears an application executes a query from time to time that takes nearly 3 minutes to execute successfully.

The column is of data type 'datetime, not null'

The column is the clustered index of the table.

The query does not use this index however, it uses a non-clustered index of another column that just happens to include this datetime column.  The other column is not referenced in this query.

Example query:

SELECT MIN(DATEADD(second, DATEPART(second, [tablename].[DateAndTime]),
    DATEADD(minute, DATEDIFF(minute, 0, [tablename].[DateAndTime]), 0))) AS
    [Mindate],
    MAX(DATEADD(second, DATEPART(second, [tablename].[DateAndTime]),
    DATEADD(minute, DATEDIFF(minute, 0, [tablename].[DateAndTime]), 0)))
    AS [Maxdate]
FROM [dbo].[tablename] [tablename]
GROUP BY ();

the resulting output looks like this.

Min Date                  Max Date
2015-04-01 00:00:00.000   2015-10-05 11:56:27.000

The only thing I can think its doing this is because the query has to peer inside each row and pick apart the date into sections, it has no choice but to go through every row to do this.

I am hoping there is some way the query could be re-written so that it would simply just use the index to bring back the min and max values in the DateAndTime column and maybe format the output last?

Try this approach instead (assuming the point of all the dateadd/datediff operations are just to set the millisecond component to 0). The query was adapted to the AdventureWorks database:

SELECT
  CONVERT(DATETIME, CONVERT(CHAR(19), MIN(OrderDate), 121)),
  CONVERT(DATETIME, CONVERT(CHAR(19), MAX(OrderDate), 121))
FROM Sales.SalesOrderHeader GROUP BY ();

The trick is to find the rows before you start messing with the column values applying all kinds of formatting and date manipulation. This results in a much more efficient plan as long as there is a supporting index on the OrderDate column (and no worse than your current query if there is no supporting index):

Yes, they are still scans, and there are two instead of one. But the impact on actual execution is undeniable:


How do I automatically deliver the SQL Server built in reports? When you right click on a number of items in the object explorer, it gives you the option for a reports menu. Various reports may exist depending on which type of item is selected. Yet, there does not appear to be any built-in functionality for automated delivery of these reports. It is hidden away somewhere?

My real question : Is there any way to set some of these up to be automatically delivered through email? The reports I am targeting are the "Job Steps Execution History", and "Top Jobs" reports for SQL Server agent. I am using SQL Server 2005/2008

No, there is no scheduling capability in SSMS for the SSMS reports. This is by design, if the limitations stated here apply to standard reports too (SQL Server Manageability Team Blog)

However, (I haven't tried this) the reports are available to use in a full SSRS install. See another page "on the same blog as above", attached zip file


How to handle versions of an Entity in Database Design I am working on a new project. The domain logic of the project is as follows:

A user uploads a file (Document) and gives a title, then another user comes along and (s)he also tries to upload a file with the same title. This is OK providing that the body of the document has differences.

How can I design my domain model so that my web application that will later consume the data can inform the user viewing a particular document "hey there is another version of this document".

Here is a simple ERD of what I have, but that obviously does not perform what I want above.

I would greatly appreciate any help and advice.

Thanks

You could keep them all in the same table, simply with a column to track version numbers:

documents
---------
  id (part of PK)
  version_number (part of PK)
  title
  file
  creator_id (FK to users.id)
  version_date (the date that this version is created - not necessary, but good to have)
  file

Your application will need to be capable of handling updating the version number of a document when a new record is added but it is a new version of an existing document. You also need to ensure that any document metadata (such as Title) changes are propagated to all related documents. You could do this by allowing document ID to not be unique, and the primary key is a combination of document ID and version number. Otherwise, it could be impossible to tell which record to update.

Another way would be to have a stub for document metadata and a separate table for all versions:

documents
---------
  id (PK)
  title
  creator (FK to users.id)
  (other metadata)

document_versions
-----------------
  id (PK)
  document_id (FK to documents.id)
  document_version_number
  file
  (other fields)

The second option is a slightly more complicated structure, but will give you better referential integrity because all version must be linked to a document stub by a foreign key.

Personally, I'd go with the second version.


Using On-Premise SQL Data Warehouse Data for OLTP applications Quick Information: Our EDW group has created On-Premise SQL Data Warehouse by collecting information from several systems. These SQL DW databases are made up of:

SQL Version: SQL Server 2016 (SP2)
Partitioned by: Year
Total Database Size: 10TB
For HA: Utilizing Always On (1-Primary & 1-Secondary)
Environment: VMWare
Change Tracking Enable on Couples of tables under EDW

EDW developers have created Views and StoredProcedures for end user (i.e. BI Analytics, Application...) to consume the data, this way, whatever changes or modification EDW group is doing to underlying objects on their end is not directly affecting end user. Application Developer (i.e. OLTP App) group who are consuming these Views/Storedprocedures, so they are not allowed to make changes to these EDW objects nor are allow to create adh-hoc queries from OLTP application, as it is also very critical for EDW group to get their load times done under certain hours. We are looking at these Views and Stored procedures, to see if we can tune them for performance.

So far whatever I have read online I see that, it is not a good practice to utilized EDW for OLTP load as, OLTP application are chatty and they mostly need is recent data and not so much of historical data.

Question: What are your all suggestions on, how can we utilize our current EDW data for these OLTP applications, so that, we are not sacrificing Load Times for Performance and vice-versa?

Can you have your cake and eat it, too?

nope.

But there are some things you can do. I've worked with this EHR software called Nextgen. It has an OLTP database that never deletes/archives any data, so it's also the data warehouse.

Short story: it sucks.

less-short story: You can keep things kinda-not-slow if you cluster every table by some kind of date, so the most recent data is on top.

You also have to pay attention to DUI's, making sure they don't lock tables for too long at a time. I would sometimes resort to cursors, which made the overall duration of the DUI longer, but reduced the time of each individual lock.

I also avoided triggers, using jobs running every 5-15 minutes instead, whenever possible.

Here's the script I used to get the whole database at once:

    /*this script will find all the heaps in the database that have the columns you specify, then index them on those columns and write a record to jm_clusterize
    It was written for sql server enterprise edition, taking advantage of online indexing, so if you have trouble, get rid of 'with (online = on)
    You should run this script after hours. It will *try* to do the index online, but if it can't, it will do the index offline, which will lock the table for a
    few seconds/minutes
    */

    declare @table varchar(255),
    @sqlcreate nvarchar(max),
    @sqlDrop nvarchar(max),
    @column1 varchar(255),
    @column2 varchar(255),
    @column3 varchar(255)

    /*specify the coulumns you want to cluster on here. You always want to cluster on the create timestamp first, and nextgen is pretty good
    at having a create_timestamp on every table, but you will want to cluster some tables on seq_no, or uniq_id, then patient demographics tables will need to
    use person_id first. I'm on the fence on clustering on 3 columns - it may be faster, it may be slower.

    */
    set @column1 = 'create_timestamp'
    set @column2 = 'enc_id'
    --set @column3 = 'seq_no'

    --drop table jm_clusterize
    if not exists (select name from sys.objects where name = 'jm_clusterize')
    begin
    create table jm_clusterize (table_name varchar(255), column1 varchar(255), column2 varchar(255), column3 varchar(255), create_statement nvarchar(max),
    drop_statement nvarchar(max), create_timestamp datetime, modify_timestamp datetime)
    end

    declare c cursor for

    --This is the query that will show you all the heaps that could be clustered on the columns you pick
    SELECT TBL.name AS TableName
    FROM sys.tables AS TBL
         INNER JOIN sys.schemas AS SCH
             ON TBL.schema_id = SCH.schema_id
         INNER JOIN sys.indexes AS IDX
             ON TBL.object_id = IDX.object_id
                AND IDX.type = 0 -- = Heap
                inner join sys.columns sc1 on tbl.object_id = sc1.object_id
                inner join sys.columns sc2 on tbl.object_id = sc2.object_id
                --inner join sys.columns sc3 on tbl.object_id = sc3.object_id
                INNER JOIN
        sys.partitions p ON idx.object_id = p.OBJECT_ID AND idx.index_id = p.index_id
    INNER JOIN
        sys.allocation_units a ON p.partition_id = a.container_id
        where sc1.name = @column1
        and  sc2.name = @column2
        --and sc3.name = @column3
        group by TBL.name
    ORDER BY SUM(a.total_pages) desc, TableName

    open c

    fetch next from c into @table

    while @@fetch_status = 0
    begin

    set @sqlcreate = 'begin try create clustered index inx_jm_clstr on ' + @table + ' (' + @column1 + ','
    + @column2
    --+ ',' + @column3
    + ') with (online = on) end try
    begin catch create clustered index inx_jm_clstr on ' + @table + ' (' + @column1 + ','
    + @column2
    --+ ','+ @column3
    + ') end catch'

    set @sqlDrop = 'drop index inx_jm_clstr on ' + @table

    insert into jm_clusterize (table_name, column1, column2, column3, create_statement, drop_statement, create_timestamp, modify_timestamp)

    select @table, @column1, @column2, @column3, @sqlcreate, @sqlDrop, current_timestamp, current_timestamp

    --comment out the line below and it won't actually create the index
    exec sp_executesql @sqlcreate

    fetch next from c into @table
    end

    close c

    deallocate c

    select * from jm_clusterize
    where column1 = @column1
    and column2 = @column2
    and column3 = @column3


PostgreSQL replication and large import I am tasked with making large PostgreSQL imports to master instance, and then replicate it across the nodes as read-only data.

Still, the CMS system has to be able to write data and replicate across all of them.

I can use the latest version of PostgreSQL server. There are just few tables in single database.

I havent been working with PostgreSQL for 5 years, so I was wondering if this is doable the same way as with MySQL, that I have the DM and so on?

The thing is, that I need to run it in two data centres, so ideally it would be close to:

2 master PostgreSQL instances, which are doing writes from CMS and imports
10 slave PostgreSQL instances doing only reads.

Is there any preferred approach in doing this for PostgreSQL?

This sounds like an ideal candidate for streaming replication with hot standby.

Alternatives are listed on the Replication wiki page.


How could I improve the performance of this MySQL query? This query takes a long time to run, is there be a way I could rewrite it to make it faster?

SELECT DISTINCT r.racedate, re.trainerid
FROM raceentry re
INNER JOIN race r ON r.raceid = re.raceid
WHERE re.trainerid IS NOT NULL
AND re.Trainer30in90Starts IS NULL
AND re.trainerid IN (
    SELECT DISTINCT trainerid
    FROM pp_entries
)
AND re.raceid IN (
    SELECT re2.raceid
    FROM raceentry re2
    INNER JOIN race r2 ON r2.raceid = re2.raceid
    WHERE re2.horsename = re.horsename
    ORDER BY r2.racedate DESC
)
ORDER BY r.racedate DESC, re.trainerid LIMIT 500

This is what it displays when I run the EXPLAIN on it:

1   SIMPLE  pp_entries  NULL    range   IX_pp_Entries_1,trainerid               trainerid   5   NULL    4835    46.66   Using where; Using index; Using temporary; Using f...
1   SIMPLE  re          NULL    ref     PRIMARY,IX_RaceEntry,ix_RaceEntry_Horse,RaceEntry2...   RaceEntry_trainerID     5   thefigures_dbo.pp_entries.trainerid     10  10.00   Using where
1   SIMPLE  r           NULL    eq_ref  PRIMARY,IX_Race     PRIMARY     17  thefigures_dbo.re.RaceID    1   100.00  NULL
1   SIMPLE  r2          NULL    eq_ref  PRIMARY,IX_Race     PRIMARY     17  thefigures_dbo.re.RaceID    1   100.00  Distinct
1   SIMPLE  re2         NULL    eq_ref  PRIMARY,IX_RaceEntry,ix_RaceEntry_Horse,RaceEntry2...   PRIMARY     49  thefigures_dbo.re.RaceID,thefigures_dbo.re.HorseNa...   1   100.00  Using index; Distinct

Thanks guys, used a combo of the suggestions and ended up with this query

SELECT DISTINCT r.racedate, re.trainerid
FROM raceentry re
INNER JOIN race r ON r.raceid = re.raceid
WHERE re.trainerid IS NOT NULL
AND re.Trainer30in90Starts IS NULL
AND re.trainerid IN (
    SELECT trainerid
    FROM pp_entries
)
LIMIT 300

The biggest improvement seemed to be removing the 2nd IN() clause suggested by Oreo

Try using EXISTS instead of IN, it tends to take good shortcuts when filtering your main records.

Also, you had a redundant ORDER BY within your IN filter, I hope the MySQL optimiser got rid of that for you, but you might as well remove that from the code since is has no effect on your results.

Actually, looking closer at your last IN filter, it seems pretty redundant, as it's joining receentry to itself, using horsename and raceid?

Try the below:

SELECT DISTINCT r.racedate, re.trainerid
FROM raceentry re
JOIN race r ON r.raceid = re.raceid
WHERE re.trainerid IS NOT NULL
    AND re.Trainer30in90Starts IS NULL
    AND EXISTS (
        SELECT 1
        FROM pp_entries ppe
        WHERE re.trainerid = ppe.trainerid
        )
ORDER BY r.racedate DESC, re.trainerid
LIMIT 500


About changing data path by database Is there any way to change physical path by database?

For example :

I have 2 databases in server.

database1 will be located in /data/database1/

database2 will be located in /data/database2/

Is this possible ?

thanks in advance.

If you are only running one mysqld instance and want to physically store databases on different disks, here is an approach:

Let's say you

datadir is /data
Two databases

database1
database2

you have a Disk Volume mounted on /data1
you have a Disk Volume mounted on /data2

All MYISAM

cp /data/database1/* /data1
cp /data/database2/* /data2
chown -R mysql:mysql /data1
chown -R mysql:mysql /data2
mv /data/database1 /data/database1_old
mv /data/database2 /data/database2_old
ln -s /data1 /data/database1
ln -s /data2 /data/database2
chown -R mysql:mysql /data/database1
chown -R mysql:mysql /data/database2

After this, when you run SHOW DATABASES; make sure the tables in database1 and database2 are fully accessible. If they are, you could then do

DROP TABLE database1_old;
DROP TABLE database2_old;

All InnoDB or Mixture of InnoDB/MyISAM

MYSQL_CONN="-uroot -ppassword"
MYSQLDUMP_OPTIONS="${MYSQL_CONN} --single-transaction --routines -triggers"
mysqldump ${MYSQLDUMP_OPTIONS} database1 > database1.sql
mysqldump ${MYSQLDUMP_OPTIONS} database2 > database2.sql
mysql ${MYSQL_CONN} -ANe"DROP DATABASE database1; DROP DATABASE database2;"
chown -R mysql:mysql /data1
chown -R mysql:mysql /data2
ln -s /data1 /data/database1
ln -s /data2 /data/database2
chown -R mysql:mysql /data/database1
chown -R mysql:mysql /data/database2
mysql ${MYSQL_CONN} -Ddatabase1 < database1.sql
mysql ${MYSQL_CONN} -Ddatabase2 < database2.sql

CAVEATS

Please test this on a staging server
Please make backups before doing this
Please test full accessibility (SELECT queries)
Heads up on InnoDB: This may not produce better performance for .ibd files sitting on a disk volume apart from ibdata1. I wrote about this before

Feb 06, 2012 : MySQL tables/databases into different disks
Aug 07, 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)?


Understanding the archive_cleanup_command In the postgresql.conf file for PostgreSQL version 13, the archive_cleanup_command comment explains the command in the following way:

#archive_cleanup_command = ''         # command to execute at every restartpoint.

The documentation here and here have no mention of a 'restartpoint'. This raises the following questions:

What is a restartpoint?
For example: is restartpoint just the same word for a checkpoint? Do the two mean the exact same thing?
When is a restartpoint created?
For example: if the restartpoint is just a checkpoint then the check point will be created every 5mins or whatever the setting for checkpoint_timeout is in postgresql.conf file.
When is the archive cleanup command run?
For example: The archive cleanup command is run every time the archive_timeout (set in the postgresql.conf file) is reached. If the archive timeout is set to 1hr, then the archive_cleanup_command runs every 1hr.

A good way to find such information is to go to the index of the manual and look for "restartpoint". Which indeed is listed there and leads to WAL configuration

In archive recovery or standby mode, the server periodically performs restartpoints, which are similar to checkpoints in normal operation: the server forces all its state to disk, updates the pg_control file to indicate that the already-processed WAL data need not be scanned again, and then recycles any old log segment files in the pg_wal directory. Restartpoints can't be performed more frequently than checkpoints in the master because restartpoints can only be performed at checkpoint records. A restartpoint is triggered when a checkpoint record is reached if at least checkpoint_timeout seconds have passed since the last restartpoint, or if WAL size is about to exceed max_wal_size. However, because of limitations on when a restartpoint can be performed, max_wal_size is often exceeded during recovery, by up to one checkpoint cycle's worth of WAL. (max_wal_size is never a hard limit anyway, so you should always leave plenty of headroom to avoid running out of disk space.)


mysql query - how to delete duplicates based on highest int? I'm running a reverse auction website and I need to delete the duplicate bids and keep only the highest amount . lastPriceBid_rounded is the "amount". The duplicate is defined by the userId.

+----------------------+--------------+------+-----+-------------------+-------------------+
| Field                | Type         | Null | Key | Default           | Extra             |
+----------------------+--------------+------+-----+-------------------+-------------------+
| lastPriceBid         | varchar(125) | YES  |     | NULL              |                   |
| dateBidded           | varchar(125) | YES  |     | NULL              |                   |
| owner                | varchar(65)  | YES  |     | NULL              |                   |
| status               | int(11)      | YES  |     | 0                 |                   |
| userId               | varchar(125) | YES  |     | NULL              |                   |
| siteId               | int(11)      | YES  |     | NULL              |                   |
| lastPriceBid_rounded | int(11)      | YES  |     | NULL              |                   |
| from_nickname        | varchar(125) | YES  |     | NULL              |                   |
| id                   | int(11)      | NO   | PRI | NULL              | auto_increment    |
| time_updated         | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update         |
|                                                                        CURRENT_TIMESTAMP |
+----------------------+--------------+------+-----+-------------------+-------------------+

So far I've tried

DELETE n1
FROM offers n1, offers n2
WHERE n1.lastPriceBid_rounded < n2.lastPriceBid_rounded
AND n1.userId = n2.userId;

but it doesn't seem to work well. I still have some duplicates (I see that when I use SELECT DISTINCT).

Why does the DELETE Query not work ?

Whenever a table is used against itself in a query other than SELECT, rows tend to disappear during the optimization phase. I wrote about that back on Feb 22, 2011 : Problem with MySQL subquery

  What can you do then ?

Create a new offers table to see what the table would look like if the DELETE were actually done:

DROP TABLE IF EXISTS offers_after_delete;
CREATE TABLE offers_after_delete LIKE offers;
INSERT INTO offers_after_delete
SELECT B.* FROM
(SELECT userId,site,MAX(lastPriceBid_rounded) highest_amount
FROM offers GROUP BY userId,site) A
INNER JOIN offers B USING (userId,site);

Please note that I based the delete operation on a distinct user per unique site. Now, browse through offers_after_delete. If the table looks correct to you, then delete like this:

DROP TABLE IF EXISTS offers_after_delete;
CREATE TABLE offers_after_delete LIKE offers;
INSERT INTO offers_after_delete
SELECT B.* FROM
(SELECT userId,site,MAX(lastPriceBid_rounded) highest_amount
FROM offers GROUP BY userId,site) A
INNER JOIN offers B USING (userId,site);
ALTER TABLE offers RENAME offers_old;
ALTER TABLE offers_after_delete RENAME offers;
DROP TABLE offers_old;

Please rehearse this on a DB Staging Server.

Give it a Try !!!


MongoDB new secondary replica set node has much smaller data folder (dbPath) I have a 3 node replica set that's been online since mid december. Last Friday (Jan 24), we added a 4th node in our disaster recovery data center.

We used the automatic initial sync procedure as our data set is relatively small. No problems here, after a while it's status switched to secondary and rs.printSlaveReplicationInfo() tells me everyone is in sync with little lag.

A coworker asked me to confirm that the new node was up to date and my instinct was to compare the size of the data folders (dbPath).

The new node's dbPath is ~1.88GB, but the average size for the other nodes is ~3.7GB. Why is the new node's dbPath smaller?

Thanks!

Update:
The real question is, how do I figure out if a new node of the Replica Set is fully synced?

Running rs.printSlaveReplicationInfo() on the Primary is not helping me figure whether the new node contains a full copy of the replica set.

Running rs.printReplicationInfo() is not giving me what I'm looking for either.

The answer lies in db.stats(); by comparing the number of objects and dataSize, of the primary and the new node, the new node is smaller.

However, when comparing the new node with another secondary, the values very closely match! Real question answered! Except for that dbPath size difference.


Restore a database backup on another server without sysadmin account I need to restore my own database backup on another server.

On both servers I have the same user account name with limited privileges (eg WebUser).

On the target server I can't use a sysadmin account, so how can I restore my backup and give access to limited user account WebUser?

Of course I can't use SQL Server Management Studio but only code in my app.

I've tried with an account with this server role

[serveradmin]
[diskadmin]
[dbcreator]

I can restore the backup but can't access it nor give permissions on the database.

I think your main issue is that after restoring the database to your new target user, that your account "WebUser" is effectively orphaned.

You should have your DBA change the SID for WebUser (and any other user that matters) to be the same as in your source server.

Note that this will require dropping the user and re-creating it in the target server.  And any other databases will need to be fixed as well.

But this will allow you to restore the database from the source server, to the target server and not have to worry about permissions when you get done.


SQL Server 2012 Reporting Services: "Unable to connect to the remote server" I am trying to install SQL Server 2012 Reporting Services on Windows Server 2008, but all the web interface says after adding the feature and confirming all the default settings in Reporting Services Configuration Manager is

  Error: Unable to connect to the remote server

In the logfile there is this message:

  System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:80

This worked just few months ago on the same system.

I had a similar problem when installing SSRS for a SCOM installation.  It was configured with SSL but when I removed this I started getting the same error as above. Solution was to change the SecureConnectionLevel in rsreportserver.config to 0.

https://connect.microsoft.com/SQLServer/feedback/details/686214/removing-ssl-cert-in-config-manager-doesnt-properly-modify-rsreportserver-config


MySQL Server consuming too much memory Currently I have the following happening :

And :

When i run the query i get about 68 entries with most Time set at 0

show full processlist;

What I'm doing wrong ?
How do i free some memory ?
Is it normal to have swap frozen like that ? I tried to modify the swappiness without success.

echo 10 > /proc/sys/vm/swappiness

my.cnf file

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

log-warnings    = 2
log-error       = /var/log/mysqld.log

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /data/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

#
# * Fine Tuning
#
key_buffer_size         = 16M
max_allowed_packet      = 128M
thread_stack            = 192K
thread_cache_size       = 64
table_open_cache        = 3000
#open_file_limit         = 10000
join_buffer_size        = 128k
# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 3000
wait_timeout           = 150
interactive_timeout    = 30

innodb_buffer_pool_size        = 25G
#  The preferred value size for the log_file_size is 25% of the innodb_buffer_pool_size.
innodb_log_file_size           = 1G
#innodb_log_buffer_size         = 512M

innodb_buffer_pool_instances   = 10
tmp_table_size                 = 256M
max_heap_table_size            = 256M
innodb_flush_log_at_trx_commit = 2
query_cache_limit              = 64M
query_cache_size               = 256M

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 1
max_binlog_size         = 100M

[mysqldump]
quick
quote-names

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

It seems that you misconfig the mysqld and that the service is reserving more memory than the server actually has (VIRT is 37G, but the server has 32G). Please have a look at the doc. I don't know your my.cnf but you should start by setting the innodb_buffer_pool_size to 24G and restart the service.


Does increasing the tempdb file size require a restart I want to alter tempdb as such (i am increasing the size)

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp3', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp4', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 10240KB )
GO

Will this require me to restart SQL server?

Increasing the size does not require a restart.

A small example on my dev environment

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 4096MB )
GO
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 4096MB )
GO

From:

To:

If you are decreasing the size with MODIFY FILE it would require a restart.
If you want to decrease the size immediately, you would have to start shrinking (not ideal).


Oracle Optimizer refuses to use my index I have a relatively simple query that SELECT data in a table.

SELECT l.columnRecovered FROM schema.TABLE l
WHERE (column1 = :a or (:a is null AND column1 is null))
AND (column2 = :b or (:b is null AND column2 is null))
AND (column3 = :c or (:c is null AND column3 is null));

This generates the following plan :

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   309 (100)|          |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   309   (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((("COLUMN2"=:3 OR ("COLUMN2" IS NULL AND :4 IS NULL)) AND
              ("COLUMN1"=:1 OR ("COLUMN1" IS NULL AND :2 IS NULL)) AND ((:6 IS NULL
              AND "COLUMN3" IS NULL) OR "COLUMN3"=:5)))

On this table, I have a UNIQUE INDEX that contains all those 3 columns.
But the optimizer prefer doing a FULL SCAN on the table rather than using the INDEX.

Where things become to go strange, is when I try this query without my binds but by replacing the values directly in the query.
Of course the SQL_ID changes, but this time the optimizer decides to use the INDEX and goes from 1104 gets to 6 (which is of course a great optimization).

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE         |     1 |    56 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | TABLE_IDX     |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("COLUMN3"='DATA3' AND "COLUMN2"='DATA2' AND "COLUMN1"='DATA1')

Statistics are up to date and there are no histograms on this table.

Any way to make the optimizer use that index without modifying the query ?

I think the optimizer thinks that the possibility to have 3 NULLs might happen so this disable him from using the index is it right ?

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    56 |   199   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   199   (2)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("COLUMN2" IS NULL AND "COLUMN1" IS NULL AND "COLUMN3" IS
              NULL)

The optimizer does not use your index, not because it does not want to, but becasue it can not. For a query like that the index does not cover all possible outcomes.

When :a, :b, :c are all NULL, and you are searching for rows where column1, column2, column3 are all NULL, those rows are not indexed by a (column1, column2, column3) b-tree index, so it can not be used for retreiving the matching rows.

Adding NOT NULL constraints may help, but if you have NULL values, that is of course not possible (and I am assuming you have NULL values, otherwise why would you construct a query like this).

You could also try creating an index that includes a constant value, so even those rows will be indexed, where the indexed columns are NULL. So instead of an (column1, column2, column3) index, you could try creating a (column1, column2, column3, 'A') index.

The best solution in my opinion is however to have different queries generated whenever a bind variable was not assigned a value. Queries like the above are results of trying to handle multiple cases with a single query. This may seem to be elegant and compact, and yes, I know, generating different queries based on the presence of parameters can be cumbersome, but I have seen this kind of laziness causing severe performance issues countless times.

Yes, Oracle has some tricks such as NVL optimization, OR-expansion, for handling such predicates. Those work perfectly in Powerpoint and training materials, with simple examples, but in reality, with complex queries they can not be always used.


Updating a table with a not-joined query I'm using Access and have a task that I want automated using an query.

I have a table (IncentiveAssignments) that lists out all of the Gift Card numbers (GiftCode, all are unique) I currently have along with the people I've given them to (PersonID, all are unique).  Not all of the GiftCodes have been assigned a PersonID yet.  There's also a primary key autonumber and some other irrelevant fields.

I also have a query (UnassignedPeople) that lists out all of the people (PersonID) that are currently not listed in IncentiveAssignments but need to be where there is an IncentiveAssignments.GiftCode with a null value in IncentiveAssignments.PersonID.

So this is my IncentiveAssignments table:

AutoNum  GiftCodes  PersonID
-------  ---------  --------
1        abcd       12
2        efgh       34
3        ijkl       .
4        mnop       .
5        qrst       .

And this is UnassignedPeople:

PersonID
--------
56
78

I want to create a query that will update IncentiveAssignments to be:

AutoNum  GiftCodes  PersonID
-------  ---------  --------
1        abcd       12
2        efgh       34
3        ijkl       56
4        mnop       78
5        qrst       .

Does anyone have any idea how I can do this?  I'm currently just copy-pasting what's in the query into the table, but I'd much rather it be automated that way I don't worry about human error.  It seems like my primary issue is that there's nothing to join the table and query together.

Much appreciated.

You can either run the following as many times as it takes to get zero rows updated:

UPDATE IncentiveAssignments SET
  PersonID = (SELECT MIN(PersonID) FROM UnassignedPeople up WHERE NOT EXISTS (SELECT 1 FROM IncentiveAssignments WHERE PersonID = up.PersonID)
WHERE AutoNum = (
  SELECT MIN(AutoNum)
  FROM IncentiveAssignments
  WHERE PersonID IS NULL);

And then run the following to clear all assigned persons:

DELETE FROM UnassignedPeople up
WHERE EXISTS (SELECT 1 FROM IncentiveAssignments WHERE PersonID = up.PersonID);

Alternatively, you can use a couple of transient tables:

CREATE TABLE TmpAssignments (
  TmpID AUTOINCREMENT,
  AutoNum int);

CREATE TABLE TmpPersons (
  TmpID AUTOINCREMENT,
  PersonID int);

INSERT INTO TmpAssignments (AutoNum)
SELECT AutoNum
FROM IncentiveAssignments
WHERE PersonID IS NULL;

INSERT INTO TmpPersons (PersonID)
SELECT PersonID
FROM UnassignedPeople up
WHERE PersonID NOT EXISTS (SELECT 1 FROM IncentiveAssignments WHERE PersonID = up.PersonID);

UPDATE IncentiveAssignments SET
  PersonID = (SELECT ta.AutoNum, tp.PersonID
              FROM TmpAssignments ta
              JOIN TmpPersons tp ON (tp.TmpID = ta.TmpID)
              WHERE ta.AutoNum = IncentiveAssignments.AutoNum)
WHERE PersonID IS NULL;

DELETE FROM IncentiveAssignments ia
WHERE EXISTS (SELECT 1 FROM IncentiveAssignments WHERE PersonID = ia.PersonID);

DROP TABLE TmpPersons;
DROP TABLE TmpAssignments;


Why the term "relation(al)"? In english, we might talk about the relation between, say, Bob and Tim. Perhaps they're cousins. The term "relation" in this context makes sense to me.

In the context of relational databases, I understand what the term refers to, but I don't understand why it is used. I figure that understanding why it is used will help me to better understand the field, so I'd like to understand why it is used.

Why is, for example, a Person, considered to be a "relation"? In english, a relation is a noun that describes how two entities are associated. It doesn't refer to the entities themselves. In the context of relational databases, "relation" refers to the entities themselves. Why?
I understand that relational model came after the hierarchical and network models (ex. parent, neighbor). But in those models, the entities also have relations to one another. So why call this model the relational model? Is there a more specific phrase/term? Or maybe we should say that all three models are relational models, but the hierarchical and network models are specific types of relational models?
What if we have standalone entities that don't relate to one another. Say, Person, Door, and Tree. Is the term "relation(al)" still applicable?

(Perhaps this should be multiple questions. I figured that the answers are highly related - perhaps there's just one answer - so I figured it'd make sense for this to be a single question. If I'm wrong, let me know and I'll create separate questions instead.)

Edit: This diagram may be useful to visualize that a relation is relating different domains to one another:

First of all, I highly recommend the scientific paper in which Dr. Edgar Frank Codd published the relational framework to the general public in 1970, i.e., A Relational Model of Data for Large Shared Data Banks. There, in section 1.1, “Introduction”, Dr. Codd himself states that:

  This paper is concerned with the application of elementary relation theory to systems which provide shared access to large banks of formatted data.

  © Association for Computing Machinery. Communications of the ACM, Volume 13, Issue 6 (pp. 377-387), June 1970.

So, yes, the terms relation and (hence) relational come from a mathematical background. Dr. Codd —who, apart from his academic and research credentials, had about 20 years of first-hand experience in computing and information processing— envisioned the enormous advantages of applying the relation (an abstract construct, naturally) in the field of data administration.

I am not a mathematician but, basically speaking, a relation is an association between sets, a set being a collection of elements (this external resource gives a definition of mathematical relation that may help to understand it from a different perspective). When working with the aid of a SQL database management system (DBMS for brevity), a well-known approximation of a relation is a table, in which case the association takes place between the types of its columns. Evidently, in SQL platforms that do offer DOMAIN support (e.g., Firebird and PostgreSQL), the association occurs between the domains fixed for the columns of the table in question; see the sections below for significant details.

In that respect, I am going to cite again Dr. Codd, who in section 1.3, “A Relational View of Data”, asserts that:

  The term relation is used here in its accepted mathematical sense. Given sets S1, S2, ⋯ , Sn, (not necessarily distinct), R is a relation on these n sets if it is a set of n-tuples each of which has its first element from S1, its second element from S2, and so on.1 We shall refer to Sj as the jth domain of R. As defined above, R is said to have degree n. Relations of degree 1 are often called unary, degree 2 binary, degree 3 ternary, and degree n n-ary.

  1 More concisely, R is a subset of the Cartesian product S1 × S2 × S3 ⋯ × Sn.

  © Association for Computing Machinery. Communications of the ACM, Volume 13, Issue 6 (pp. 377-387), June 1970.

And I agree with other answers in that it is very relevant to point out that Dr. Codd made some adaptations to the mathematical relation in order to get the most out of it regarding data management, and they are explained in the paper referred to before and throughout his extensive bibliography.

Relation and relationship

A situation worth bringing up is that, when dealing with these subjects, there may arise confusion due to the similarities that exist regarding the everyday (non-mathematical, non-technical) definitions of the terms relation and relationship —which, as a non-native English speaker, I find particularly understandable—.

The entity-relationship view and the relational model

Other factor that I think may as well cause confusion (and is closely associated with the technical connotations of the two terms brought up above) is that, when learning to design databases, a student or practitioner is typically first introduced to the methodology proposed by Dr. Peter Pin-Shan Chen in the entity-relationship view of data (published in 1976), which suggests two different implements (i.e., the entity and the relationship) to delineate a conceptual schema, and then, only after the definition of said schema is stable, the student or practitioner is introduced to relational terms and instruments (e.g., the relation) when declaring the logical layout of the pertinent database. Within the conceptual frame of reference, relationship holds connotations that are much more closer to the everyday sense of the word.

Then, perhaps, that circumstance also adds to the relation and relationship issue —but the sequence of firstly defining the conceptual schema and subsequently declaring the corresponding logical design is of course quite appropriate, as I will detail in the following sections—.

Responses to each of your subquestions

I consider that having included those three subquestions is really pertinent because they establish a broader context for the post, so they should not be overlooked. In this way, apart from exclusively addressing why the terms relation and relational are used (which certainly is very significant and is the title of the post, but it is not the entire post), said subquestions can assist in comprehending more of the scope of the relation and the relational model when one is involved in a whole information management project (quite relevant since this a site about database administration) and is therefore working at different levels of abstraction. In this manner, I am going to share my take on those particulars below.

Subquestion no. 1

  Why is, for example, a Person, considered to be a "relation"? In English, a relation is a noun that describes how two entities are associated. It doesn't refer to the entities themselves. In the context of relational databases, "relation" refers to the entities themselves. Why?

Conceptual level

In a given business environment, Person can be considered an entity type depending on how the people who work there (business experts and database designers) conceptualize it. And, yes, in that business environment, there may be different properties of interest with respect to the Person entity type, e.g., Name, BirthDate, Gender, etc.

Moreover, the Person entity type may hold certain relationship (or association or connection) types with itself or other entity types; e.g., Person may be associated with an entity type named UserProfile, which in turn may have its own properties of interest, let us say, Username and Password.

But, (a) the entity types, (b) their corresponding properties, (c) the relationship types between entity types and (d) the relationships between the properties themselves are notions that “belong to” the particular business environment in which they are deemed of significance. They are devices used by database designers that work closely with business experts in order to define a context-specific conceptual schema, at the design phase.

Thus, at the conceptual level we basically work with the structure of the ideas that arise in the real world’s segment of interest, i.e., (1) prototypes of things and (2) prototypes of relationships between prototypes of things, we do not work with (3) relations —employing this last term in the sense of the relational framework of data—.

Logical level

After Person was precisely delineated as an entity type at the conceptual level, and if one wants to implement a relational database that conveys the meaning of Person and all the concepts associated with it, then the facts about entities of that type can be managed by virtue of a mathematical relation at the logical level, and take advantage of the science based operations that can be performed on that abstract construct (i.e., define it, constrain it and manipulate it).

Yes, one can name a certain relation Person when defining the logical arrangement of a database, but that does not transform the “real world” concept of Person into a relation, one approaches it as such because of the benefits that are obtained when managing information about it, e.g., applying relational algebra operations on it to derive new relations (and therefore one is deriving “new” information). Said benefits become more evident taking into account the fact that the entities of a certain type make up a set, and the values of a certain property make up a set too.

And, yes, as mentioned in preceding paragraphs and in other answers as well, one of the paramount aspects of a relation is the connection that exists between its domains —that are typically used to represent the properties of entity or association types that are part of a conceptual schema—. For example, let us say that we have declared the following (ternary) relation:

Salary (PersonNumber, EffectiveDate, Amount)

…and let us suppose that, in the business environment in question, the tuple —which (i) stands for a particular entity, i.e., an instance of an entity type from the applicable conceptual schema, and (ii) whose SQL counterpart is a row—

Salary (x, y, z)

…would carry the meaning

“The Salary payed to the Person identified by PersonNumber x on EffectiveDate y corresponds to the Amount of z”.

Accordingly —to describe things in an approximate manner—, the connection between the three domains is of prime importance, they are all related (and, yes, a unary relation would involve one domain only). The connection among all the values of a certain domain is very significant too, as they constitute a set of a precise type. Also, the contents of each tuple of the Salary relation must fit in the structure of the assertion illustrated above.

Conceptual-level relationships and logical-level relations

As demonstrated, I have now dealt with database management at two different levels of abstraction, namely conceptual and logical —and there is yet a lower level known as the physical one, which in SQL DBMSs typically involves, e.g., indexes, pages, extents, etc.—.

So, in accordance with the notions explained before, at the logical level one works exclusively with (a) mathematical relations, where (b) the conceptual relationships or associations are represented by (c) the values contained in the tuples of such mathematical relations, and said values are usually delimited via FOREIGN KEY constraints so that they can represent the applicable relationships accurately.

And, yes, associative entities, i.e., instances of relationship types with a many-to-many (M:N) cardinality ratio, can be conveyed by way of the tuples of a single mathematical relation —with the corresponding constraints declared appropriately, of course—.

Subquestion no. 2

  I understand that relational model came after the hierarchical and network models. But in those models, the entities also have relations to one another. So why call this model the relational model? Is there a more specific phrase/term? Or maybe we should say that all three models are relational models, but the hierarchical and network models are specific types of relational models?

Network and hierarchical DBMSs preceded their formal theoretical support

It is opportune to point out that the theoretical support around the hierarchical and the network approaches was, in fact, created in terms of previously existing DBMSs, with the aim of, among other aspects, testing and establishing the soundness of (1) said kinds of software and (2) the linked data management practices —an upside-down phenomenon, from my point of view—.

Incomplete in comparison with the relational framework

That being said, although there are hierarchical and network DBMSs that predate the relational model, and even when Dr. Codd referred to each of those approaches as a “model”, none is defined as such in the same way that the relational framework is. The relational paradigm provides scientific constructs for the (i) definition, (ii) restriction and (iii) manipulation of data, and the hierarchical and network approaches lack full theoretical support to cover all of the three sorts of constructs previously mentioned.

Network and hierarchical features

Also, as stated before, the entity and relationship types are conceptual-level devices, they do not belong to the hierarchical or network approaches, each of which offers particular mechanisms to represent said aspects:

The network paradigm entails two devices for data representation, i.e., nodes and arcs (and that characteristic of course implies two different kinds of data manipulation operations) which, when contrasted with the relational model that (as per the information principle) requires only one construct (the relation), makes evident the needless complexity that working in a network fashion involves. For instance, given that it resorts to two representation instruments, the network approach imposes an impractical query bias that hinders data manipulation.
For its part, the hierarchical view proposes representing the data by way of (physical!) files made up of records (which in turn consist of fields) organized in a three-like arrangement; i.e., one parent record chained with possibly many child counterparts via pointers, which produces a physical access path with regard to data manipulation. This approach is also unfavourable because it presents a entanglement among conceptual and physical aspects, so the changes in the physical storage arrangements require a reorganization of the data structures, which in turn demands changes in the concerning data manipulation operations.

As shown, the hierarchical and network views impose their constructs on the data to be managed, whereas the relational model proposes administering the data elegantly in its natural structure by means of sets of associated facts (from which n subsequent types of sets, not anticipated at the design phase, can be derived and so on!).

The relational model does not have sub models

And, quite important, neither the hierarchical nor the network views are specific types of relational models, they are simply other paradigms that someone may follow to (a) build DBMSs and to (b) create databases, but please bear in mind that the hierarchical and network approaches are considered obsolete for decades now.

Subquestion no. 3

  What if we have standalone entities that don't relate to one another. Say, Person, Door, and Tree. Is the term "relation(al)" still applicable?

Yes, it is perfectly applicable if one is (1) managing information about those entity types by dint of adapted mathematical relations and (2) performing the applicable relational operations at the logical level in a certain database administered with the support of a given relational DBMS.

It does not matter if, at the conceptual level, said entity types hold no relationship types with other entity types (and it is worth noting that an entity type can have a relationship of one-to-zero-one-or-many cardinality ratio with itself), and thus one is not conveying nor enforcing any relationship between the values of the tuples of the relations under consideration.


Choosing shard key and friendly URL Ids for my MongoDB I have decided to use MongoDB as my Database for a web application. However, I have some difficulties to get started and I hope that you can help me out with a few questions.

I am developing my application in ASP.NET and with MongoDB as the back-end.  I intend to start with a single server + 1 replication but wanted to built it right so I won't have problem sharding the database in the future if I have to.

One of my biggest problems is choosing the right shard key and friendly URLs for my website.

I have a folders collection and files as embedded collection inside the folders collection.  Each user can create any number of folders and add files to it. Each folder belongs to one user. I wanted to know what is the best shard key for this type of collection?
 Many queries will query by the user, getting the folder and its items by querying the folders collection by its unique id. I will also use the id in the URL to get the folder and its filers: ex. mywebsite.com/folder/[the_id_of_the_folder]

I will also will use paging in my application, so I need to query the data (also in a sharded environment) and get for example: the last 10 records, page 2 with 10 records - all ordered by the last time the were inserted/updated

So my first question is what is the best shard key to use for a single machine, but considering that I will have to shard in the future
Does the shard key has to be the primary unique id of the document in MongoDB?
How can I generate more user friendly URLs - I prefer a numerical value instead of GUID (is there option to convert it?)

Help will be very appreciated, as I am stuck and can continue until I solve this.

Before you go further you need to answer a few questions

how do you represent files within folders into the database
how do you represent folders
do you have relations between folders (parent -> child)
how often do you expect to create folders and files
how often do you update existing files into folders and what is the number of files you update

Based on your answers you can have a write optimized schema or a read optimized schema. Write optimized is a schema that contains many entries that are very small
or you can use built in operators like $inc over a collection. Read optimized is generally a larger collection like the one you described, into your scenario you could have very easy something like this (assuming all folders are at the same level)

{ "userid" : "email or id",
  [
     { "folder1" : [ "file1", "file2"] },
     { "folder2" : [ "file3", "file4"] },
  ]
}

But with this schema it gets quite complicated if you need to link a folder to a parent folder ... But is obvious that the userid is the shard key.


SQL Data types and SmallInt/TinyInt vs Regular Integer What are the SQL Server benefits of using proper data types?

Example:

SmallInt/TinyInt vs Regular Integer
Decimal(2) vs float
Varchar(100) vs char(100)

Will it really matter these days, with modern computing?

Thanks,

What are the SQL Server benefits of using proper data types?

If you use the right datatype your database will better match your model and is more likely to be efficient both in terms of space & speed. You are at risk of this question being closed as "too broad" because it can be quite a wide subject.

To touch your more specific points:

  SmallInt/TinyInt vs Regular Integer

Smaller types take less space so more rows fit in a page resulting in less space taken both on disk and in memory. Of course, if you choose too small a type your application will fail. Consider also BIGINT for when values larger than 2,147,483,648 (or smaller than -2,147,483,647) might be needed.

The difference is multiplied up when you consider indexes: the larger types end up in any index that covers that covers the columns too.

  Decimal(2) vs float

There is a huge difference here. DECIMAL (and its synonym NUMERIC) are actually scaled integers that are guaranteed to have the precision you specify. Floating point numbers have their uses but be very careful with them as they are only approximations due to rounding issues converting between binary and decimal forms - the most common example is 0.1+0.2 will usually not equal 0.3. See http://floating-point-gui.de/ amongst many other references for details and run this to see the effect first hand:

SELECT CAST(0.1 AS FLOAT        ) + CAST(0.2 AS FLOAT        ), CASE WHEN CAST(0.1 AS FLOAT        ) + CAST(0.2 AS FLOAT        ) = 0.3 THEN 'OK' ELSE 'oops' END
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)), CASE WHEN CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)) = 0.3 THEN 'OK' ELSE 'oops' END

Essentially: don't use FLOAT unless you know it is what you need.

  Varchar(100) vs char(100)

Variable-length types are far more efficient space wise, but less efficient to process CPU-wise. If you use (MAX) they are likely to be stored off-page which is less efficient again and stops compression taking effect if you use that on your tables. If you use row or page compression your fixed length types become variable length anyway but you keep the built-in validation of length which may be useful for your model.

  Will it really matter these days, with modern computing?

Short answer: Yes.
Long answer: Yyyyyyyyeeeeeeeeeeeeesssssssssssss!

The considerations can be a little different with modern CPUs - they can be much faster relative to storage and network transfer than they used to be so it is more often a good optimisation to save space at the expense of extra CPU time, but correct type choice very much still matters.

Though type choice matter most for matters other than optimisation: correct types better model your data which should be your first priority.


Working on a blog creation website; should I be creating a new table for each blog, or keep everything on the same table? So I'm working on this blog creation website as a side project. I don't actually expect it to become anything, but I want to make it as efficient as possible, even if just for practice.

The idea is that people can make accounts and then make as many blogs as they want, and post whatever they want on those blogs. My question is that should all of those blog posts be stored on the same table, or should I be creating separate tables for each new blog created? I don't know if there are limits or downsides to having hundreds or thousands of different tables, but I know that having everything on one table will slow down the selecting of posts for one specific blog, for example. This issue also applies to comments; are all comments on the site stored in one place, with an ID of what post they're about, or are they stored on separate tables depending on what blog they're on or something else. Or is there some other, better way that I should be using(without having to program a new database structure from scratch)?

I'm using PHP/MySQL for the backend. Any ideas would be much appreciated!

More than a few thousand tables or databases is a bad idea in MySQL.  This because of MySQL's dependence on the filesystem, and most OS's can get bogged down when a directory has "too many" entries.

Hence, I vote for a single Blogs table.  You will probably need few other tables to round out the app.  Probably they will all be in a single database, but that does not matter much.