How to delete duplicate records I'm able to find duplicate records using the following query, however, I'm not sure how to delete duplicates records, and ignoring any records that starts with 0 in starttime field.

SELECT `StartTime`, COUNT(`CallDetailRecordID`) AS cnt FROM `CallDetailRecord`
GROUP BY `StartTime` HAVING cnt > 1

Sample output

 StartTime     cnt
-------------  --------
        0        198
1340511506737         2
1340511958364         2
1340512141687         2
1340512191631         2
1340512244925         2
1340512670902         2

Using a DELETE JOIN
--
-- Collect all keys you intend to keep
--
DROP TABLE IF EXISTS CallDetailRecord_Keys;
CREATE TABLE CallDetailRecord_Keys
SELECT CallDetailRecordID FROM CallDetailRecord WHERE 1=2;
ALTER TABLE CallDetailRecord_Keys ADD PRIMARY KEY (CallDetailRecordID);
--
-- Collect all keys with 0 for StartTime
--
INSERT INTO CallDetailRecord_Keys
SELECT MinCallDetailRecordID FROM CallDetailRecord
WHERE StartTime = 0;
--
-- Collect the Minimum CallDetailRecordID for all nonzero StartTimes
--
INSERT INTO CallDetailRecord_Keys
SELECT MinCallDetailRecordID FROM
(SELECT StartTime,MIN(CallDetailRecordID) MinCallDetailRecordID
FROM CallDetailRecord WHERE StartTime > 0 GROUP BY StartTime) A;
--
-- Perform DELETE JOIN
--
DELETE A.* FROM CallDetailRecord A
LEFT JOIN CallDetailRecord_Keys B USING (CallDetailRecordID)
WHERE B.CallDetailRecordID IS NULL;

Test this against a copy of the table like this:
CREATE TABLE CallDetailRecord_Test LIKE CallDetailRecord;
INSERT INTO CallDetailRecord_Test SELECT * FROM CallDetailRecord;
--
-- Collect all keys you intend to keep
--
DROP TABLE IF EXISTS CallDetailRecord_Keys;
CREATE TABLE CallDetailRecord_Keys
SELECT CallDetailRecordID FROM CallDetailRecord_Test WHERE 1=2;
ALTER TABLE CallDetailRecord_Keys ADD PRIMARY KEY (CallDetailRecordID);
--
-- Collect all keys with 0 for StartTime
--
INSERT INTO CallDetailRecord_Keys
SELECT MinCallDetailRecordID FROM CallDetailRecord_Test
WHERE StartTime = 0;
--
-- Collect the Minimum CallDetailRecordID for all nonzero StartTimes
--
INSERT INTO CallDetailRecord_Keys
SELECT MinCallDetailRecordID FROM
(SELECT StartTime,MIN(CallDetailRecordID) MinCallDetailRecordID
FROM CallDetailRecord_Test WHERE StartTime > 0 GROUP BY StartTime) A;
--
-- Perform DELETE JOIN
--
DELETE A.* FROM CallDetailRecord_Test A
LEFT JOIN CallDetailRecord_Keys B USING (CallDetailRecordID)
WHERE B.CallDetailRecordID IS NULL;

If the CallDetailRecord_Test has the rows left over that you wanted, then this works for.
Give it a Try !!!
CAVEAT
Some would recommend doing a DELETE JOIN of a table against itself. I am not comfortable with that because some keys could disappear during query optimization and the rows would not be deleted as expected. I wrote about this 2.5 years ago in this post :  Problem with MySQL subquery


mysql: [ERROR] unknown variable 'default-authentication-plugin=mysql_native_password' When I installed MySQL 8.0, I selected sha256 password authentication method instead of legacy, When I tried to return to native password authentication it shows an error message:
mysql: [ERROR] unknown variable 'default-authentication-plugin=mysql_native_password'.
when I type (mysql -u root -p) command in Debian 10 Linux.

You need specify like this in my.cnf file :
Note: Make sure it is under [mysqld] section.
default_authentication_plugin=mysql_native_password

Keeping in mind only changing default_authentication_plugin won't allow user to login, because it will still use the old authentication plugin using which the user was create.
To change to the plugin to need to follow below steps :
Step1 : Instance must be enabled with the old plugin only using which the user is created. In your case the default plugin will sha256 password.
Step2 : Now re-create user with mysql_native_password plugin.
CREATE USER 'nativeuser'@'localhost'IDENTIFIED WITH mysql_native_password BY 'password';

Step3: Change my.cnf with new plugin & restart instance.
default_authentication_plugin=mysql_native_password


Does the SQL Server Backup Database Command also backup views from a Database? I have a backup job (SQL Server Agent job) which will iterate through all of our SQL Server databases in SSMS, invoke the  BACKUP DATABASE command to create a .bak file on a different server. This other server has its own job iterating through all  of the .baks and restoring them (with the RESTORE DATABASE command).

EDIT: The server running the RESTORE job restores the .baks into its own installation of SSMS

The jobs report success in the SQL Agent job history so I'm wondering whether or not these jobs should also backup and restore the Views in the databases (as they haven't seemed to) or if this is an indication that the scripts aren't quite working?

The backup command backs up views, stored procedures, user-defined functions and so on.To test:

Create a database
Create a table
Insert something in it
Create a view on this table
Restore the database with another name; or
Drop the database and restore the previous .bak


Is it possible to decouple row size estimation from maximum column width? In other words, is it possible to declare a column VARCHAR(n) and have the estimated size be something other than n/2?

An example use-case might be storing URLs - you expect most to be relative and less than 20 characters or so (eg "About/AboutUs"), but want to support the occasional long URL with lots of query string parameters.

No.

I don't believe this is possible.

In the case that the average length is greater than that calculated by the heuristic but still less than 4,000 bytes it is possible to massage this upwards with a CAST but even then you  might need some hacking about with the query syntax to encourage the CAST to happen before any memory consuming operator that depends on the estimated row size.

In the case that it is less the CAST to a shorter size obviously won't work without the possibility of truncating data.

The possible down side of a mis-estimate is that the memory grant may be calculated based on incorrect information.

I've wondered before why the statistics on string columns don't keep track of this. Maybe in the scale of things it simply isn't regarded as objectively that important.


How does MySQL/InnoDB caching work internally when you perform two similar, but not identical, select requests? I have some confusion about how the Innodb buffer pool works.

Let's say I have table with 15 records, if I execute: select ... between 1 to 10 then that result will cached in buffer(?).
My question is: if i execute select .... between 1 to 15 next time, then how it will fetch the records. Is it select 10 records from the buffer and 5 records from disk?

Your question, "How MySQL select request works internally" is a very broad topic. However, I can more or less give a not-completely-accurate, but simple overview to "what happens when you select records 1 to 10 and then, sequentially, 1-15" regarding caching.

First thing you must understand is that, functionally, MySQL can be divided into 2 separate layer, the "frontend" or SQL layer, and the ENGINE layer, which communicate just by a low-level (remember I am simplifying things) row-based api. You can have InnoDB or other pluggable engines to store rows (let's focus on only that one). Caching can happen at SQL or Engine level:

SQL Layer

At SQL layer there are several caching mechanisms, let's focus on query results caching, known as Query Cache. It allows to allocate certain amount of space in memory to return query results without going to the engine. It is a very high level method of caching, has to be consistent with the actual data stored, and its implementation is very simplistic, which has lead to many practical problems:

It requires a global lock for writes, which kills all parallelism and creates high contention under medium to high load, even for low number of writes
It can slow down misses
Evictions can also be harmful, and all results are invalidated as soon as a table is written, even if results would not be affected
If the same results are returned, but the query is slightly different, those are considered as 2 different queries, storing the same queries twice
It does not support partitioned tables, and clustering solutions such as Galera do not support it
Not all queries are cachable (e.g. undeterministic queries), and there will be a finite number of space for them

In your case, having executed 2 different queries, the query cache would be useless, and you would have 2 misses, following by 2 separate inserts of the results (assuming they are cachable, etc.)

Many people recommend disabling the query cache and using the extra memory for other buffers. The exact impact would depend on your particular workload (you should monitor hits/misses/evictions and query latency). If you still need query caching, you can opt to have something like memcache, closer to the application layer. And even MySQL integrates a plugin to run memcache as a frontend for InnoDB, as part of the server.

Separating the caching, you can control yourself the TTL of the cached results. There are many other mechanisms that can handle that, such as ProxySQL or implementing your own on the application.

InnoDB

InnoDB implements it's own complex caching system for faster row retrieval inside the buffer pool (its page cache), a much lower-level cache. In general, InnoDB tries to avoid (overpass) the filesystem cache and implement its own row cache system. The default page size is 16KB (although that can be changed) and it has the same structure on disk than in memory. A page should be large enough to store 2 data rows or more (a row can be larger than 8K, for variable-size fields, but that is another story). When a page is accessed on disk, it gets copied to the buffer pool. In fact, due to some optimizations, in many cases, not only a single page gets read, but multiple contiguous ones to reduce IO operations.

So when you read the first 10 rows on an empty buffer pool, maybe only those pages are are loaded into memory with the rows you want and probably some more; and in many cases, the full extend and the next one. All of this is subject to tuning and depends on the kind of access patterns you have.

When you read again, you will probably find the same rows already in memory, and thus you have reduced the disk I/O. If you are missing some pages, it will require a (or several) disk IO operation. This is normally seen when you run the same query twice and get a faster response. Although row caching is only a part of the speed up- you also have thread caching, table caching, dictionary caching, index caching ...(at both SQL and engine level).

However, it can happen that by the time you have done the second query, the rows have been evicted- the eviction follow a relatively complex algorithm and it is also highly tunable, but it is -simplifying it- a LRU (Least recently used) implementation, which means that if the other, most recently accessed data gets into memory, it can evict rarely-used data.

Here you have a step-by-step summary of how InnoDB buffer pool works:

https://www.slideshare.net/ossez/percona-server-with-xtra-db/23

Other engines can do things differently, for example, MyISAM and TokuDB have both index caching, but they both relay more heavily on the filesystem cache for data.


Failure with ongoing replication from SQL Server RDS using AWS DMS I'm trying to replicate from an SQL Server RDS database using DMS, and running into problems.

I am able to load all data from one table into the destination (~1M rows) as a single 'Migrate existing data' Task. When I run a second 'Replicate data changes only' Task, it runs for a few minutes and then fails.

No rows are copied into the destination by the second task before it fails.

These lines in the error log seem relevant:

  [SOURCE_CAPTURE ]W: Capture functionalities could not be set. RetCode:
  SQL_ERROR SqlState: 42S02 NativeError: 208 Message: [Microsoft][ODBC
  Driver 13 for SQL Server][SQL Server]Invalid object name
  'sysarticles'. Line: 1 Column: -1 (sqlserver_log_utils.c:1712)

  [SOURCE_CAPTURE ]E: Bad Envelope : , Lsn=00050649:000a3cb1:0166,operation=5,TxnId=0000:34915a92,Tablename=COMMIT,PageId=,slotId=0,timeStamp=2018-08-01T08:15:30.233,dataLen=0,
  LCX=99, , >Invalid data context / LCX Code encountered for TXN
  operation. [1020203] (sqlserver_log_processor.c:401)

The source database is Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)

I've tested this against a number of destination databases (MySQL, PostgreSQL, SQL Server) but always get the same result.

How can I get ongoing replication to work? Is there anywhere else I should look?

Need to disable SQL Server Change tracking and enable RDS Change Tracking.

Find any tables which are enabled for CDC with:

SELECT s.name as Schema_name, t.name AS Table_name, tr.* FROM sys.change_tracking_tables tr INNER JOIN sys.tables t on t.object_id = tr.object_id INNER JOIN sys.schemas s on s.schema_id = t.schema_id;

Then for each of those tables table run:

ALTER TABLE dbo.Products DISABLE CHANGE_TRACKING;

Disable CDC on the database:

alter database AdventureWorks set change_tracking = off

Enable RDS CDC on the database:

exec msdb.dbo.rds_cdc_enable_db 'AdventureWorks'

Enable RDS CDC for each table:

exec sys.sp_cdc_enable_table
   @source_schema           = N'dbo'
,  @source_name             = N'Products'
,  @role_name               = NULL;

Finally rerun your DMS tasks and they should work.


MySQL Replication using SSL I am in the process of replicating my database so i can have a  master slave configuration, one of the issues i have is with security i am basically generating my server/client keys and certificates using openssl i also generate my own CA key and certificate to self sign, i understand the issues with self signing certificates on a public website, but do you think this will be as a serious problem when used in replication?

It's a problem in public websites because you can't email your shiny new CA key to everybody on the Internet.  So instead by convention browsers ship with a small list of CAs (Verisign and friends) and we all buy certificates from them.

In database replication, you can visit both servers and install your new CA key.  As long as you trust you, and you don't want strangers to authenticate to your database, you're probably in the clear.


Add Secondary to Replica Set in MongoDB Windows I am trying to add the second replica set to primary.

Primary = 192.168.56.52:27017
Secondary = 192.168.56.136:27017

    # mongod.conf

    # for documentation of all options, see:
    #   http://docs.mongodb.org/manual/reference/configuration-options/

    # Where and how to store data.
    storage:
      dbPath: **mypath**\DBs
      journal:
        enabled: true
    #  engine:
    #  mmapv1:
    #  wiredTiger:

    # where to write logging data.
    systemLog:
      destination: file
      logAppend: true
      path:  **mypath**\mongod.log

    # network interfaces
    net:
      port: 27017
      bindIp: 0.0.0.0

    #processManagement:

    #security:
    #operationProfiling:

    replication:
      replSetName: configServers

    #sharding:

    ## Enterprise-Only Options:

    #auditLog:

    #snmp:
    #mp:

But when I try I add rs.add("192.168.56.136:27017")
It shows as picture .

Please help to get run replicaset. Thanks.
I run on windows and start replicaset with service .

As per the error message says, you have added the Primary node of the replica set as localhost. The rule is all nodes of the replica set should be localhost or all should be hostname/ip address.

If you run rs.status() from primary, you will see the primary replica set member as localhost. So when you try to add a secondary with rs.add("192.168.56.136:27017") the system will throw an error.

How to correct this?

conf = rs.conf()

update the hostname of primary member in conf variable

then run

rs.reConfig(conf)

Now add the secondary member.


Index strategy for a Queue Table, getting rid of filesort I am trying to get rid of a filesort operation (and preferably also optimize a bottleneck query). We are using an innodb mysql table as a queue to process various incoming business activities. It is not uncommon for the queue to have several hundred thousand pending items to process.

The selection query is as follows:

SELECT
    id,
    user,
    password,
    counter,
    idoc,
    idoc_type,
    company_id,
    host,
    job_id
FROM
    business_queue
WHERE
        finished IS NULL
    AND locked='0'
    AND created <= NOW()
    AND counter <= 5
    AND company_id = 2
ORDER BY
    business_object_priority DESC, created ASC, id ASC
LIMIT 1
FOR UPDATE

The table has the follwing index defined:

ALTER TABLE  `business_queue` ADD INDEX compound_selector(
   `finished` ,
   `locked`,
   `created`,
   `counter`,
   `company_id`,
   `business_object_priority` ) ;

Yet I have a feeling that it could be massively optimized, as currently filesort is used for sorting and it is kinda inefficient when we have a large pending queue. Is there a way to get rid of filesort in the above query completey?

Let's look at the WHERE clause

WHERE
        finished IS NULL
    AND locked='0'
    AND created <= NOW()
    AND counter <= 5
    AND company_id = 2
ORDER BY
    business_object_priority DESC, created ASC, id ASC

Since company_id and locked are static values, they should be the leading columns of the index. Your ORDER BY has business_object_priority DESC and created ASC. Because of this mixed order, there will always be a filesort to contend with.

Notwithstanding, I would rearrange the column order as follows

ALTER TABLE  `business_queue` ADD INDEX compound_selector_new (
    `company_id`,
    `locked`,
    `finished`
    `business_object_priority`) ;

Notice the following:

I removed created and counter because you have ranges specified in the WHERE clause.
I kept finished because you need all records with company_id=2 and locked=0 grouped with all finished columns that are NULL.

Although there will be a filesort, this should yield a faster collection of rows.

Give it a Try !!!


Best practice between using LEFT JOIN or NOT EXISTS Is there a best practice between using a LEFT JOIN or a NOT EXISTS format?

What is benefit to using one over the other?

If none, which should be preferred?

SELECT *
FROM tableA A
LEFT JOIN tableB B
     ON A.idx = B.idx
WHERE B.idx IS NULL

SELECT *
FROM tableA A
WHERE NOT EXISTS
(SELECT idx FROM tableB B WHERE B.idx = A.idx)

I am using queries within Access against a SQL Server database.

The biggest difference is not in the join vs not exists, it is (as written), the SELECT *.

On the first example, you get all columns from both A and B, whereas in the second example, you get only columns from A.

In SQL Server, the second variant is slightly faster in a very simple contrived example:

Create two sample tables:

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

Insert 10,000 rows into each table:

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

Remove every 5th row from the second table:

DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

Perform the two test SELECT statement variants:

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

Execution plans:

The second variant does not need to perform the filter operation since it can use the left anti-semi join operator.


How to limit rows in PostgreSQL update statement My table employees contain more than ten million data. i want to update 10k rows in commission_pct column where commission_pct is null and set zero(0) value.

in oracle I can easily solve this by using rownum.

update employees
set commission_pct=0
where commission_pct is null and rownum<=10000;

but postgresql does not support rownum.

how to solve this in postgresql?

You need to search for the desired rows with a subquery, and use the primary key of the table to relate those rows to the table in the UPDATE statement.

In general, rownum can be replaced with the row_number() window function (see, e.g., Using window functions in an update statement), but for this case, it is easier to just use limit:

UPDATE employees
SET commission_pct = 0
WHERE id IN (SELECT id
             FROM employees
             WHERE commission_pct IS NULL
             LIMIT 10000);


db_owner does not have access to Windows folders This is MSSQL Server 2008 R2 running on Windows Server 2008 Enterprise. Service Instance is running as 'Network Service'.

I have two logins, UserA and UserB. UserA is a sysadmin. UserB is a db_owner for database 'Demo'.

I login as UserA and can take a backup of Demo, no questions asked.

I login as UserB and try to take a backup of Demo. When clicking the 'Add' button to add a destination, and then the 'browse' button, I get the following error message:

G:\Backups

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

My question is:

After reading that folder access is based on the User Account that the SQL instance is running under (Network Service), why can the db_owner not browse to the specified folder?

Is there something I am missing?

Thanks

I searched for this error before posting and could not find a result. After posting, the answer came up as a related question:

Error while taking backup using SSMS

To quote from that question:

"Here is answer for my own question SSMS uses "xp_fixeddrives" to list the drives in PC. Same is used by SSMS when clicking on button which is used to select backup path or browse directory.

The error is generated because "exec xp_fixeddrives" doesn't return any records when executed by a non-sysadmin account.When you run the same using user having sysadmin permission then "exec xp_fixeddrives" returns the list of drives. So it is the problem with SSMS ,and it is not a problem with any permission to the folder or account under which SQL agent services runs etc.(I have tested the same)"

One thing to add is that this only affects local SQL logins and not Windows Authentication accounts.


How to convert a Top 1 subquery using outer table alias to Oracle? I have the following SQL Server query

select
    (select top 1 b2 from  BB b where b.b1 = a.a1 order by b2) calc,
    a1,
    a2
from AA a
where a2 = 2;

which I can rewrite using analytic functions

select
    (select b2 from
    (select
        row_number() over (order by b2) lfd,
     b2 from  BB b where b.b1 = a.a1
    ) as t where lfd = 1
    ) calc,
    a1,
    a2
from AA a
where a2 = 2;

but when I convert this to oracle

create table AA ( a1 NUMBER(10), a2 NUMBER(10) );
insert into AA values ( 1, 1);
insert into AA values ( 1, 2);
insert into AA values ( 1, 3);
insert into AA values ( 2, 2);

create table BB ( b1 NUMBER(10), b2 NUMBER(10) );
insert into BB values ( 1, 1);
insert into BB values ( 2, 4);
insert into BB values ( 2, 5);

select * from AA;
select * from BB;

select
    (select b2 from
    (select
        row_number() over (order by b2) lfd,
     b2 from  BB b where b.b1 = a.a1
    )  where lfd = 1
    ) calc,
    a1,
    a2
from AA a
where a2 = 2;

I get the following error

Error at line 5
ORA-00904: "A"."A1": invalid column name

you would perform the join first in Oracle:

SELECT a1, a2, b2
  FROM (SELECT a1, a2, b2,
               row_number() over(PARTITION BY a.a1 ORDER BY b.b2) lfd
           FROM AA a
           LEFT JOIN BB b ON b.b1 = a.a1
          WHERE a2 = 2)
 WHERE lfd = 1

The problem with your query is that currently a subquery in Oracle can't access a value from a parent query more than two level deeper.

You could also use a PL/SQL function that would contain the inner SELECT.


Mysql database replication with geo-distributed servers I am just a developer, who doesn't have deep knowledge in DBA. So need experts suggestions in this.
I have two  offline databases in two cities, I want to make a synchronization between two databases using a third database which will be stay alway online in a live server.

My problem is like this,
Location A database is in New York.
Location B database is in Canada.
And they are in local machine, so no connection between them. Now I need to show the changes of Location A's to Location B and Location B's to Location A. Yeah this can be solve by MASTER-MASTER replication. Also i have found a way to solve autoincrement indexes issue, by separating index numbers in different servers.

But I need another live server C where Location A's and Location B's change will reflect. So some users need not to access Location A or Location B for looking into changes. I do not also want's to use RING replication. because if one goes down then rest of the RING will be broken.

In summery I want to make the replication like this

Need help about replication process.

Thanks in advance,

Nixon

One solution that could work is a Galera cluster with one node in read-only mode. (See e.g. here for info about geo-distributed Galera cluster.) Galera cluster is a (virtually) synchronous multi-master solution. MariaDB and Percona provide this technology, as does Galera themselves. There is one problem: Since you would be writing to more than one node, there is a higher likelihood of experiencing deadlocks / certification failures, including on commit. (See this article for an explanation.) However, this is something your application could handle if you take care when developing it.

Be sure to review the Galera cluster limitations before you decide on this technology. Some of the most notable requirements are: A Linux OS, it only supports the InnoDB storage engine, and all tables must have an explicit primary key.

MySQL Group Replication is a similar technology, but this wouldn't be suitable for geo-distributed database nodes as in your use-case.


How to count the number of times an attribute appears in an inner query? I have this query that I am trying to write out:

  Which students (names) have taken more than one course from the same professor?

I have a students, course, and professor table. This is the ER diagram:

I have 4 tables:

Students (student_id is the PK)
Courses (course_id is the PK and professor_id is the FK to the Professor Table)
Takes_courses (student_id and course_id is the PK)
Professor (professor_id is the PK)

My plan:

Select student_name
From Student
Where student_id > Some (Select *
                         From Courses Natural Join Takes_Courses
                         Group By professor_id)

I am not able to run this query above. How am I supposed to count the number of student_id appears in the inner query?

Something like this.

The below lists everything: which student took which course  taught by which professor.

select
  s.student_id, c.course_id, p.professor_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
;

But we do not care about courses. The below lists which student is taught by which professor(s). (student_id, course_id) is unique, and 1 course is taught by 1 professor, so the same (student_id, professor_id) values appear multiple times, if the student attended multiple courses taught by the same professor.

select
  s.student_id, p.professor_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
;

We do not care about professor_id and we need to list students taught by more than 1 course.

select
  s.student_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
group by
  s.student_id, p.professor_id
having
  count(*) > 1
;

This may list the same student multiple times because a student can take 2 courses from a professor and 3 courses from another professor, so list 1 student only once:

select
  distinct s.student_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
group by
  s.student_id, p.professor_id
having
  count(*) > 1
;


Replication or Sharding for a huge table? I have a table that I'm pretty sure in the very near future (within a year) will reach 14 million rows, and I'm trying to scale out my web app before hitting any bottleneck. I'm in dilemma whether I should use sharding or replication?

What is a good strategy for my web app?

EDIT:

Table structure in which I'll have millions of rows:

    id mediumint(8) unsigned NO  NULL  auto_increment
    user_id  smallint(5) unsigned NO  NULL
    jqci_id  mediumint(8) unsigned NO   NULL
    selected_answer  enum('a','b','c','d')  NO  NULL

Mysql replication won't address performance issues on large tables.  It just allows you to have another copy you might use for failover, backup or reporting in cases of heavy hitting queries.

Your table schema is tiny.  Even with 14 million rows you could fit that in ram on even modest hardware.

All the same, if you want to look at sharding you could read up on mysql partitioning

You say you expect 14 million rows; you're dangerously close to the max value for your medium PK there.  Just go head and make that an int instead of worrying about the extra byte/entry.


How to create column in DB with default value random string Can I create column in DB table (PostgreSQL) which have default value random string, and how ?

If is not possible, please let me know that.

The solution is (for PGSQL):

alter TABLE users ADD column register_key text NOT NULL default md5(random()::text);


Is there a way to input field information by cvs file? I am importing a new database. I am asked to input field information in MS Access manually. Is there a simpler way? For example, import a cvs version field information into the following

I found how to copy structure only for a access database table:

  To copy the table structure, follow these steps:

  Right-click the existing table name in the Database Window of the original database and click Copy.
  Close the database Window and open your new database.
  Under Objects, click Tables. Then, right-click the database Window and click Paste.
  Enter a name for the new table, choose Structure Only, and then click OK.

Maybe this helps.

Source:

http://www.techrepublic.com/article/copy-an-existing-table-structure-into-a-new-access-database/


Deleted sql server windows authentication user I had deleted a system user "sysuser" from the server. I had previously used this user to login to SQL Server through "Windows Authentication" mode.

Now, I tried to create a new user with the same username and password but am unable to login to the SQL Server again.

I deleted the user in the server from control panel. Then I log in to SQL Server using windows authentication but cannot log in.  I tried to add back the user with same password using control panel. I tried to log in SQL Server using windows authentication and the re-created user but still cannot log in.

When you add a Windows Authentication login to SQL Server, the login gets created using the Windows Security ID, or SID, of the Windows user to create the login.

The name of the login is irrelevant, since authentication occurs via the SID.

If you delete and re-create a Windows user that has a login in SQL Server, the newly created Windows user will have a different SID than the old user, and hence will not be linked to the SQL Server login.  In order to have the new Windows user login to SQL Server, you need to create a new SQL Server login for the user via the CREATE LOGIN T-SQL statement, as in:

CREATE LOGIN <login_name>
FROM WINDOWS;


How to avoid SQLCODE=-968, SQLSTATE=57011 "The file system is full" error with merge statement When doing the following merge (tempchoices is a temporary table created with DECLARE GLOBAL TEMPORARY TABLE tempchoices LIKE CHOICES NOT LOGGED IN importtemp ON COMMIT PRESERVE ROWS) all my diskspace gets used and I receive the "The file system is full" error.

MERGE into CHOICES ar
USING (SELECT * from session.tempchoices ) ac
ON ar.CHOICE_NAME = ac.CHOICE_NAME AND ar.CHOICE_LIST_NAME =
'account' AND ar.LOCALE = 'de_CH'
WHEN MATCHED and ar.DISPLAY_NAME != ac.DISPLAY_NAME THEN
UPDATE SET DISPLAY_NAME = ac.DISPLAY_NAME, DEPENDENT_VALUE =
ac.DEPENDENT_VALUE
WHEN NOT MATCHED THEN
INSERT (CHOICE_LIST_NAME, CHOICE_NAME, DISPLAY_NAME, VALUE, LOCALE,
SORT_ORDER, DEPENDENT_VALUE)
VALUES (ac.CHOICE_LIST_NAME, ac.CHOICE_NAME, ac.DISPLAY_NAME, ac.VALUE,
ac.LOCALE, ac.SORT_ORDER, ac.DEPENDENT_VALUE)

How can I avoid this error?

Resizing the disk is not an option.

What I have tried is setting the DB2_OPT_MAX_TEMP_SIZE option but with no observable result.

Both tables contain about 600'000 entries.

DB2 Version is 10.5 and I observe this both on Linux and Windows servers.

Assuming the merge statement needs to use some amount of temp table size, you should increase the file system size where the related user temporary tablespace resides.

To check the path of user temporary tablespace,

check db2diag message that may indicate which file system is related.
And also, db2pd -db <dbname> -tablespace

You may add new user temp tablespace on new path that has enough space for the same page size of related tables.


purchasing item by client - how to ensure that user has enough funds? I have to tables:

users (id serial, coins integer not null default 0)
purchases (id serial, price integer not null, user_id)

When new row is inserted into purchases table I want to subtract amount from user.coins column and ensure that user.coins >= 0 (so user had enough founds to make a purchase).

I want to do it in AFTER INSERT trigger:

UPDATE users SET coins = coins - NEW.price WHERE id = NEW.user_id;
RETURN NULL;

My question is how to ensure that user won't spend more coins than he posses? My first idea is to just make CHECK constraint on users table like this CHECK (coins >= 0) - will that work with multiple concurrent inserts on users table?

You've got to be really careful in cases like these. The default transaction isolation level PostgreSQL uses is READ COMMITTED, and in that mode you can easily get nasty cases where two transactions are performing checks like this:

-- You will get READ COMMITTED by default.
BEGIN;

-- Make sure user has enough coins.
-- DANGER Nasty race condition in READ COMMITTED!
SELECT coins FROM users WHERE id = 123;

-- Just to make the race condition visible...
SELECT pg_sleep(10);

-- We think we have enough coins, go ahead and perform the UPDATE
UPDATE users SET coins = ... WHERE id = 123;

-- And since we think we've deducted the coins, record the purchase
INSERT INTO purchases ... ;

COMMIT;

If you want to see the race condition in action, run that same SQL through two psql sessions at the same time. You'll see that both sessions will think that the user has enough coins to purchase an item, and will both record a purchase. (Note, the exact same problem would exist with a CHECK constraint on the users table with READ COMMITTED transactions.)

How to fix this race condition? Read up on transaction isolation levels, you probably want to be using SERIALIZABLE for important accounting code like checking account balances and recording purchases. Another option is to use  SELECT ... FOR UPDATE to obtain a row-level lock on, say, the users table while you're checking the account balance, which will ensure that two transactions can't be performing that same delicate check at the same time.


PostgreSQL for running Tests (Durability is not important) Our Django tests are getting slow, and a lot of time is spend in cursor.execue().
There are roughly two ways now: Rewrite the tests to mock the ORM, or make PostgreSQL faster.
This question is about "make PostgreSQL faster".
What can I do to improve PostgreSQL speed if D (Durability) of ACID is not important. Everything could be kept in RAM.
Constraint: This question is about what you can do without modifying the Django/Python/SQL code.

use UNLOGGED tables throughout

set shared_buffers big enough to contain the whole database

if you have bigger queries, increase work_mem

have enough RAM to contain shared_buffers plus work_mem times the number of client connections

Typically, you can gain most by tuning the queries that use most of the time.


Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE I am trying to have a score for entire section of SELECT statements

SELECT *,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword')
UNION
SELECT *,MATCH(`content`) AGAINST('keyword') as `score` FROM `table2` WHERE MATCH(`content`) AGAINST('keyword')
UNION
SELECT *,MATCH(`text`) AGAINST('keyword') as `score` FROM `table3` WHERE MATCH(`text`) AGAINST('keyword')

In such case scores are per table + they are not ordered by relevance

But I tried this method, which is working but is not worth of production

SELECT * FROM (
    SELECT *,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword')
    UNION
    SELECT *,MATCH(`content`) AGAINST('keyword') as `score` FROM `table2` WHERE MATCH(`content`) AGAINST('keyword')
    UNION
    SELECT *,MATCH(`text`) AGAINST('keyword') as `score` FROM `table3` WHERE MATCH(`text`) AGAINST('keyword')
) as `combined` ORDER BY `score` DESC

Above code is disliked because scores there are per table, they are joined and ordered. A bad approach.

So I tried to MATCH() AGAINST() for data in TOP LEVEL SELECT as well this. (DIDN'T WORK)

SELECT *,MATCH(`data`) AGAINST('keyword') as `good_score` FROM (
        SELECT *,`result` as `data`,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword')
        UNION
        SELECT *,`content` as `data`,MATCH(`content`) AGAINST('keyword') as `score` FROM `table2` WHERE MATCH(`content`) AGAINST('keyword')
        UNION
        SELECT *,`text` as `data`,MATCH(`text`) AGAINST('keyword') as `score` FROM `table3` WHERE MATCH(`text`) AGAINST('keyword')
    ) as `combined` ORDER BY `good_score` DESC

Above statement is perfect for me, but it does not work because data column is created on-the-fly and it does not support to have a FULLTEXT INDEX.

My question is how to proceed to get my engine working.

Can you somehow make data a FULLTEXT
Is there any way to make it work other than IN BOOLEAN MODE that does not support scores
Is there an approach to this entire topic that would make it work? Creating a temporary table does not solves this, 50% rule of MATCH() AGAINST() makes a query return 0 results, but there are many
Maybe there is a something little that I miss?
Creating a VIEW does not work either, MySQL does not support INDEX-es on VIEWs.
Maybe it is a good idea to use IN BOOLEAN MODE and create scoring manually?

I have been working for this issue for more than two days. So I kindly ask for some help.
Thanks.

Perhaps you could record the following from the three(3) tables

table name
the column from the table name
FULLTEXT index on the column

Here is the code:

DROP TABLE IF EXISTS combined_data;
CREATE TABLE combined_data
(
    source_table VARCHAR(64),
    source_id INT NOT NULL,
    data TEXT NOT NULL,
    FULLTEXT (data)
) ENGINE=MyISAM;
--
ALTER TABLE combined_data DISABLE KEYS;
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table1',id,`result` FROM table1 WHERE MATCH(`result`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table2',id,`content` FROM table1 WHERE MATCH(`content`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table3',id,`text` FROM table1 WHERE MATCH(`text`) AGAINST('keyword');
--
ALTER TABLE combined_data ENABLE KEYS;

Now you can run a single query against the one table

SELECT *,MATCH(`data`) AGAINST('keyword') as `good_score`
FROM combined_data
ORDER BY `good_score` DESC;

Give it a Try !!!


How to index cube style queries with changing aggrigations I've got a query that emulates the CUBE function.
This is many different aggregations unioned together.
select x,y,z, sum(a) from tbl group by x,y,z
union all
select x,'all',z, sum(a) from tbl group by x,z
union all
select 'all',y,z, sum(a) from tbl group by y,z
union all
select x,'all','all', sum(a) from tbl group by x
etc.

Unfortunately, this is really slow. Everything is distribution aligned. Analysing the estimated query plans shows that a columnstore index always uses hash match to perform this operation. A clustered index will allow one of the queries to use a stream aggregate, although I was expecting a few more ( see plan below)

I was hoping that the below index:
clustered index (x,y,z)

would enable stream aggrigate for the below groupings

x,y,z
x,y
x

Is there something else I'm overlooking?

I am an Oracle developer. Relating to how CI is implemented in Oracle, here are a few pitfalls to avoid.

Ensure that the table tbl is small enough to avoid overflow segments.
The table tbl must be ideally used only for bulk Inserts and Selects. Updates, Deletes and interspersed Inserts will cause page split.
If there are other non clustered indexes on tbl, ensure that those indexes do not end up with huge ClusteringFactor post converting tbl to CI.
Sharding tbl might help provided it does not affect other indexes and SQL performance.


See entire string from a lengthy text field in pgAdmin? Using pgAdmin, how can I see the entire string value for a row when the value is very long?

For example, the value may be comma-separated values (CSV) content with hundreds or thousands of characters. Furthermore, the value may contain newline characters.

When doing queries in pgAdmin’s SQL tool, the results pane only shows a few dozen characters, and then suppresses the display of any more. Is there some way to see all of the text value?

Any way to export the results of a query to a text file or a text window perhaps?

Open pgAdminIII,
Select your database,
Use the Magnifying Glass button, i.e. "Execute arbitrary SQL queries"
Instead of running your query using the green triangle  "Execute Query", choose
the button two to the right of that - i.e. "Execute Query, write result to file".
Choose your destination, then you can view arbitrary length text in your chosen file destination.
You can also use psql which will show text, but will scroll if that's any use. Also, there would be the possibility of scripting your query output using different delimiters.


Query to get queue position for each group I have a queue table with the following sample data:

id  company location
1   acme    new york
2   acme    philadelphia
3   genco   st.louis
4   genco   san diego
5   genco   san francisco
6   acme    miami

I want to query for the queue position of each company group to show where in line each company is (imagine acme has 1,000 rows before 500 Genco starts and then acme has 5,000 more after the Genco records process).  Result I would want would look like the following:

queuePositionId company
1    acme
3    genco
6    acme

I played around with ranking and grouping but the fact that a company group can be multiple times anywhere in the queue kept messing with my aggregates.  I also tried a dense_rank but couldn't figure out the ordering.  Any ideas?

This is a Gaps and Islands question. See here for more details on problems like this.

This should do what you need:

-- Generate test data
DECLARE @Companies TABLE
(
    ID INT
    ,Company NVARCHAR(100)
    ,Location NVARCHAR(100)
);

INSERT @Companies
SELECT *
FROM    (VALUES (1, 'acme', 'new york')
                ,(2, 'acme', 'philadelphia')
                ,(3, 'genco', 'st.louis')
                ,(4, 'genco', 'san diego')
                ,(5, 'genco', 'san francisco')
                ,(6, 'acme', 'miami')
        ) AS CompanyLocations(ID, Company, Location);

-- Find company positions
;WITH cte_Companies
AS
(
    SELECT ID
           ,Company
           ,CASE
              WHEN LAG(Company) OVER(ORDER BY ID) = Company
              THEN 1
              ELSE 0
            END AS CompanyPosition
    FROM @Companies
)

SELECT ID, Company
FROM cte_Companies
WHERE CompanyPosition = 0

UPDATE
Andriy noted that my solution was a SQL Servre 2012+ solution. The following code should work for versions down to 2005.

-- Generate test data
DECLARE @Companies TABLE
(
    ID INT
    ,Company NVARCHAR(100)
    ,Location NVARCHAR(100)
);

INSERT @Companies
SELECT *
FROM    (VALUES (1, 'acme', 'new york')
                ,(2, 'acme', 'philadelphia')
                ,(3, 'genco', 'st.louis')
                ,(4, 'genco', 'san diego')
                ,(5, 'genco', 'san francisco')
                ,(6, 'acme', 'miami')
                -- Further test data
                ,(7, 'genco', 'London')
                ,(8, 'genco', 'Portsmouth')
        ) AS CompanyLocations(ID, Company, Location);

-- Find company positions

SELECT ID, Company
FROM @Companies c1
WHERE NOT EXISTS    (
                        SELECT *
                        FROM @Companies c2
                        WHERE c1.Company = c2.Company
                        AND c1.ID - 1 = c2.ID
                    )


"invalid transaction termination" in Postgres when trying to update a value using a function I'm new to postgres. I defined a function like this:
CREATE OR REPLACE FUNCTION increment_count()
  RETURNS TRIGGER LANGUAGE plpgsql
  AS $$
   BEGIN
     UPDATE posts SET count=count+1 WHERE pid=NEW.pid;
   COMMIT;
   END;
  $$;

This gives me error invalid transaction termination

You can not commit in a trigger, and you forgot the return statement.
CREATE OR REPLACE FUNCTION increment_count()
  RETURNS TRIGGER LANGUAGE plpgsql
  AS $$
    BEGIN
      UPDATE posts SET count=count+1 WHERE pid=NEW.pid;
      return null;
    END;
  $$;

In case of a BEFORE trigger, "return NEW" should be used!


Limit the RAM memory usage in MongoDB I am using MongoDB-2.6.1. Is there any way to limit the RAM memory usage?

For example: Out of 4 GB RAM, let MongoDB use till 3 GB (<= 3 GB).

If you are running on Linux, you can use control groups to limit MongoDB memory as shown in the following article:

Easy Steps to Limit Mongodb Memory Usage by Ramakanta Sahoo

On Windows, a similar technique using the Windows System Resource Manager is described in:

Limit MongoDB memory use on Windows without Virtualization by Simon Green


Send email from SQL Server I need to send email from SQL Server SP. Until now I used sp_send_cdosysmail. Is it better to use sp_send_dbmail? And the main question - which mail server is used by sp_send_dbmail - is it configured somewhere?

Database mail depends on what server, profile is configured in Database Mail
Ref: Configure Database Mail

as for using sp_send_dbmail, see sp_send_dbmail (Transact-SQL)

This db mail functionality is available from SQL Server 2005.

See also these example database mail configuration scripts by Dan Guzman.


I cannot get my data source to connect to an SQL Server named instance using SSRS Report Builder I have a report in SSRS and I want to update the data source to use a named instance instead of the default instance. The server host will remain the same.

I thought this would be easy. I simply changed this:

clever-sql1

to this:

clever-sql1\MSSQLSERVER02

When I click "Test Connection" in the Connection Properties, the test succeeds.

Next, I click "OK" and "OK" again to close the Connection properties. I can see my connection string has been generated, and I click "Test Connection" again, but this time, the connection fails.

These are the things I have tried to fix the problem:

Checked the SQL configuration and made sure named pipes and TCP are enabled
Can confirm that remote connections are possible via the management studio
Disabled the firewalls on my PC and the server
I discovered that the named instance does not use default SQL port, but uses port 8000 (which is open on the firewalls). I tried specifying the port number in the connection string like this:

Data Source=clever-sql1\MSSQLSERVER02,8000;Initial Catalog=cleverdataout

This generated a different error:

I discovered that the named instance does not use default SQL port, but uses port 8000 (which is open on the firewalls). I tried specifying the port number in the connection string like this:

Yes, but the named instance ports are dynamic and managed internally by SQL browser service which use the port number 1434. In your case port# 8000 should not be used in connection string.
However, Please follow any one of following scenario to troubleshoot the issue:
Scenario 1. If you want use named instance in connection string:

make sure SQL Browser service running where SQL named instance installed.
Open 1434 (SQL Browser) port in firewall (where SQL service running)
Make sure you are able to do telnet to the SQL host on port number 1433 and 1434
Your connection string looks like: Data Source=clever-sql1\MSSQLSERVER02;Initial Catalog=cleverdataout

Scenario 2. If you want use port number in connection string:

Change SQL port from configuration manager

2. Restart sql service (named instance)
3. Open the port 1435 (configured in step:1) at windows firewall
4. Make sure you are able to do telnet to the host on custom port 1435 (configured in step:1)
5. Your connection string looks like: Data Source=clever-sql1,1435;Initial Catalog=cleverdataout
In any scenario, verify the user (used in connection string) have appropriate access on SQL server


Understanding comparison operators on columns Consider this dummy_data:

number

1

2

3

4

5

I want to return all the rows from dummy_data where column number is below a certain value.
Came up with this:
WITH
  dummy_data AS
  (
    (SELECT 1 AS number) UNION (SELECT 2) UNION (SELECT 3) UNION (SELECT 4) UNION (SELECT 5)
  )
  , some_selection AS
  (
    (SELECT 3 AS number) -- in my use case, this query might return nothing...
    UNION
    (SELECT 0 AS number) -- ...so I'm using the union to 0 to sort of coalesce.
  )
SELECT
  *
FROM
  dummy_data
  , some_selection
WHERE
  dummy_data.number < some_selection.number
;

Which returns:

number
number

1
3

2
3

So this seems to work like I intended, but I don't understand enough what's going on to know if I can rely on this code.
Can I be guaranteed that the comparison is always based on the max value of some_selection.number? Or should I add an intermediate step to explicitly extract max(some_selection.number)?
i.e. is the comparison based on the whole column or just on a single semi-random cell?
i.e. I'm worried the comparison might at times be performed on < 0 which would return very different results.
It's probably in the Postgres manual somewhere, but I could not find the relevant section.

What you have done is performed a CROSS JOIN and then filtered on the result of that CJ! What a CROSS JOIN does is combine every field in the first table with every field in the second and is not frequently very useful - but not always unproductive!
Take the first small example:  (a PostgreSQL fiddle for all of the code in this answer is available here)
CREATE TABLE x (y INT, z TEXT); INSERT INTO x VALUES (1, 'h'), (2, 'i');
CREATE TABLE a (b INT, c TEXT); INSERT INTO a VALUES (8, 'r'), (9, 's');

Then:
SELECT x.y, x.z, a.b, a.c FROM x, a;

Result:
y   z   b   c
1   h   8   r
1   h   9   s
2   i   8   r
2   i   9   s

The systax below (called ANSI join - also, see the wiki):
SELECT * FROM x CROSS JOIN a;

is perhaps better because you're being explicit about your requirements (see fiddle).
Things are perhaps easier to understand if you eliminate the extraneous SQL from your statement above. In this case, I specifically mean your use of the COMMON TABLE EXPRESSION (CTE - aka the WITH clause) - instead, I'd do the following:

create the tables separately as follows:
 CREATE TABLE dat (d_num INT NOT NULL);
 INSERT INTO dat VALUES (1), (2), (3);

and
CREATE TABLE rand (r_num INT NOT NULL);
INSERT INTO rand VALUES (-23), (5), (3), (1);

So, now perform the CROSS JOIN with no filter. Just a note - you should be using ANSI joins - see this article from the excellent oracle-base site.
SELECT
  d.d_num AS dn,
  r.r_num AS rn
FROM dat d, rand r
ORDER BY dn, rn;

Which can be rewritten:
SELECT
  d.d_num AS dn, r.r_num AS rn FROM dat d
CROSS JOIN rand r
ORDER BY dn, rn;

Result:
dn     rn
 1    -23
 1      1
 1      3      <<---- rn > dn
 1      5      <<---- rn > dn
 2    -23
 2      1
 2      3      <<---- rn > dn
 2      5      <<---- rn > dn
 3    -23
 3      1
 3      3
 3      5      <<---- rn > dn

12 rows

So, now we filter (keeping ANSI syntax):
SELECT
  d.d_num AS dn, r.r_num AS rn
FROM dat d
CROSS JOIN rand r
WHERE d.d_num < r.r_num
ORDER BY dn, rn;

Result:
dn  rn
 1   3
 1   5    -- <<--- note 5 = MAX(r_num)
 2   3
 2   5    -- <<--- note 5 = MAX(r_num)
 3   5    -- <<--- note 5 = MAX(r_num)

 5 rows

And we can see that these 5 rows correspond to the 5 rows from the original CROSS JOIN.
So, to answer your question:

Can I be guaranteed that the comparison is always based on the max
value of some_selection.number? Or should I add an intermediate step
to explicitly extract max(some_selection.number)?

Your SQL will pick every number combination where ANY number in the first field is less than ANY number in the second field. This will pick any number that is less than the MAX() of field 2, but also any other combination where field 2 is greater than field 1.
If you want to just pick ANY number in the first table is less than the MAX() of the second table, you need to further refine your filter using a sub-SELECT as follows:
SELECT
  d.d_num AS dn, r.r_num AS rn
FROM dat d
CROSS JOIN rand r
WHERE d.d_num < r.r_num
AND r.r_num = (SELECT MAX(r_num) FROM rand)  <<--- New condition!
ORDER BY dn, rn;

Result:
dn  rn
 1   5
 2   5
 3   5

And we can see that this result corresponds to the 3 records where 5 is the rn above.
A few words of advice:

make your initial SQL as simple as possible - using the WITH clause to generate data (IMHO) made your SQL more complex and didn't contribute to understanding.

use table and column aliases to make your SQL more readable.

when testing any of your solutions, always try to use weird outlier values (see -23 above) - I had 17 as well as 5, but the number of results of the CJ started to become too large - I just needed one value that was greater than all the values in the dat table.

EVERY SQL statement should (I would say MUST) have an ORDER BY clause - leaving this out is one of the biggest factors leading to confusion with results and what record in table A corresponds to what record in table B. I believe that in this case, using ORDER BY helps comprehension!

you used a variable called NUMBER in your example - you should never use SQL keywords as table or column names. This avoids confusion with error messages, aids with debugging and makes any query more portable - different systems escape keywords in different ways!

always include your server (and version) in your questions - for this case, the SQL would have worked for any server, but answers can frequently depend on these factors.

persist with the ANSI syntax (do a search for sql ansi joins versus old style join) - it seems strange at first, but is worth the effort!

p.s. bon courage avec ton anglais - qui n'est pas mauvais du tout d'ailleurs!


Do MySQL GTIDs get created for all transactions or just those that occur after enabling GTIDs? I have a MySQL database that I'm going to start replicating, and I plan on using GTIDs to do so. The slave will be a brand new virtualized server located overseas.

My question is 2-fold:

When I enable GTIDs on the existing master, will GTIDs be created for all previous transactions? Or will they only be created for transactions posted after enabling GTIDs?
If yes, can I simply let the slave replay all of the GTIDs at its own pace?(meaning I don't have to sync the slave before enabling replication)

I don't care how long it takes the slave to sync (within reason, of course. 2 weeks is probably the max).

I'll be testing this out on some dummy dev servers come Monday or Tuesday, but I figured I'd ask here first.

Version and DB Info:

Master: MySQL 5.6.19 on Windows Server 2008R2 (EoL, I know...), but will be upgraded to 5.6.37 before starting replication and will then be upgraded to 5.7.x sometime thereafter.

log_bin for this server is currently OFF.

Slave: MySQL 5.7.19 on Ubuntu Server 16.04 LTS (verified compatible)
Size: It's probably considered a small database, totalling only about 330GB and something like 2.5 billion rows.

Previous Research

I couldn't find anything answering this question in the GTID docs nor in the entire replication docs.

So far, I've gone through and enabled both binary-log based replication GTID-based replication on various dev servers, but they 1) are very small/empty; 2) had log_bin enabled from the start; or 3) were synced before replication was enabled.

As I expected, GTIDs will not be created for pre-existing data.

This makes sense because, especially without the binlogs, how would the server know what transactions were used to generate the data?


For SQL Server 2012 where is better way to restrict rows (WHERE or JOIN)? I still don't get it - is it better for performance to restrict data inside WHERE clause or inside JOIN clause. Which is the better way? I've read a long time ago that there is no difference, but I am still not sure. I am speaking about INNER JOIN - in which case the result is the same.

EXAMPLE:

SELECT * FROM A JOIN B ON (A.X = B.Y) WHERE (A.Z = 5);

vs

SELECT * FROM A JOIN B ON (A.X = B.Y) AND (A.Z = 5);

The best way is the one that matches your team's coding style. If you work alone then the best way is the one that you find to be the most readable. There is currently no performance difference for the queries in the question.

You can use the undocumented trace flag 8606 to see this for yourself. Here's the code that I'm testing with:

CREATE TABLE #A (X INT NOT NULL, Z INT NOT NULL, PRIMARY KEY (X));

CREATE TABLE #B (Y INT NOT NULL, Z INT NOT NULL, PRIMARY KEY (Y));

SELECT *
FROM #A A
JOIN #B B ON (A.X = B.Y)
WHERE (A.Z = 5)
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

SELECT *
FROM #A A
JOIN #B B ON (A.X = B.Y) AND (A.Z = 5)
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

Not only do the queries have the exact same plan:

They also have the same internal input tree:

In other words, SQL Server rewrites the queries to be the same before optimization occurs. Therefore, there will not be a performance difference.


Postgres Incremental Backup (Continuous Archiving WAL) I need to take incremental backup of Postgres DB.
I need to capture (backup of) daily changes on a database. So that I can restore DB with any particular data I want.

As per Continuous Archiving, I am doing below steps:

I configured postgresql.conf, and added wal_level, archive_mode and archive_command
Restart server
Added some data
pg_start_backup('label');
Take backup of data dir
pg_stop_backup();
Added more data
Take backup of wal dir and pg_xlog dir
Replace backup data (from step 5) in data dir
Start server
Copy pg_xlog (from step 8) in current pg_xlog dir
Added recovery.conf and added restore_command
Restart DB

Now I want data which I added in step 7. (I had taken backup of WAL file and pg_xlog at that time). So I consider this data as a backup of a particuar day. And I want to restore my database with this date's data.

Now If I replace those files ie. WAL and pg_xlog and recreate recovery.conf and restart db, I do not get the data in step 7, still old one.

Please let me know if I am missing anything.

In order to restore a backup, you need to have the base archive of all the data files, plus a sequence of xlogs. An "incremental backup" can be made, of just some more xlogs in the sequence. Note that if you have any missing xlogs, then recovery will stop early.

So it's not clear here exactly what you've done, unless you changed the level of detail you're mentioning part way through your list. When you make a copy of more segments that have been put into the archive directory after adding more data, you need to ensure that all the data has been archived: using pg_start_backup and pg_stop_backup usually does this for you, but you don't mention it the second time. You need to at least do a pg_switch_xlog to have the current xlog segment immediately archived.

If you think that recovery is not consuming enough xlog segments, look at the recovery log to see if it tried to take them all. And have your recovery command make some sort of mark on which xlog files were taken.


Format timezone to +-#### instead of +-## I am curious if there is a way to configure Postgres (9.1+) to format the TZ part of a "timestamp with time zone" as +-####, just like PHP's date.format method does using "O".

PHP Docs:

  O | Difference to Greenwich time (GMT) in hours | Example: +0200

As requested an example:

create table example (time1 timestamp with time zone);
insert into example values ('2000-01-01 00:00:00+0200');
select * from example;
         time1
------------------------
 1999-12-31 23:00:00+01
(1 rows)

-- How can I return the time as it was inserted (with it's timezone).
select time1 at time zone 'CEST' from example;
      timezone
---------------------
 2000-01-01 00:00:00

-- This is the correct time,
-- but I would like to attach the timezone to it
-- and return '2000-01-01 00:00:00+0200'

Thanks

The to_char function, as the first choice to format dates, is quite limited with time zones. According to Template Patterns for Date/Time Formatting in the documentation we have TZ and tz and they'll output a time zone name, not a offset against GMT.
You may use extract(timezone_hour from timestamptz_expression) to get the offset as a signed number of hours and extract(timezone_minutes from timestamptz_expression) to get the number of minutes that comes with it. This offset is documented as:

The time zone offset from UTC, measured in seconds. Positive values
correspond to time zones east of UTC, negative values to zones west of
UTC. (Technically, PostgreSQL uses UT1 because leap seconds are not
handled.)

Be aware that there are two conflicting conventions for the sign, east of Greenwich or west of Greenwich. You want to check that the sign is what you're expecting, otherwise invert it.
Another word of caution related to your edit:

How can I return the time as it was inserted (with it's timezone).

Postgres will never know the offset that the timestamp had when it was inserted, it's lost on insertion. What is returned to a SQL client is always the current offset depending on its  current time zone. In order to keep the original time zone, you'd need to store it in an additional column.


Affect of Shrinking Database before Transaction log backup I am newbie in the world of Database administration, so excuse my stupid question :)

I have the following maintenance jobs scheduled through SQL Server Management Studio :

Full DB Backup at 2 am, 10 am & 6 Pm daily (after every 8 hours).
Transactional log backup at 2:30 am daily.
Shrink job at 9:30 am & 9:30 pm daily.

I have run the Shrink job at about 1 AM manually, after that the scheduled Transaction Log Backup got failed with following error

  Code: 0xC002F210     Source: Back Up Database (Transaction Log)
  Execute SQL Task     Description: Executing the query "BACKUP LOG
  [PRODDB] TO  DISK = N'S:\CAS Backup\Log..." failed with the following
  error: "BACKUP LOG cannot be performed because there is no current
  database backup.  BACKUP LOG is terminating abnormally.". Possible
  failure reasons: Problems with the query "ResultSet" property not
  set correctly parameters not set correctly or connection not
  established correctly.  End Error  DTExec: The package execution
  returned DTSER_FAILURE (1).  Started:  03:01:12  Finished: 03:01:12
  Elapsed:  0.359 seconds.  The package execution failed.  The step
  failed.,00:00:00,0,0,,,,0

I need to understand was manually running Shrink Job before transaction log backup failed the later at 2:30 AM??

Your maintenance plan should not contain any shrink tasks anyway. These cause fragmentation by moving pages around the file and likely the database will just need to grow back to its original size shortly anyway.

But this isn't causing your error.

  BACKUP LOG cannot be performed because there is no current database
  backup.

The transaction logs would need to be restored after a compatible backup has been restored. This is telling you that there is no such backup as one has never been taken since the database has been in full or bulk logged recovery mode.

It doesn't look like step 1 was run successfully for that database, or possibly someone temporarily changed the recovery mode for that database to simple and broke the log chain so you must take another full backup.


How do I create fractal index using tokudb engine? I install MariaDb and configured it to use TokuDb Engine.

I need to use Fractal-Index Built-in on TokuDb Engine.

I have create my index as follow:

ALTER TABLE `table__name` ADD INDEX ( `column` );

I Do this following TokuDb wiki.

When i go on phpmyadmin as index type i see "BTREE".

Need I specify type on creation of index? What should I put as type?

The BTREE you see is just a label. If "show create table table_name;" displays engine=TokuDB then you can be sure that Fractal Tree indexes are in use.


Easy way to have non-tech users modify database? We're in the process of engineering a BI solution.

One problem we have is that sadly a lot of HR information (start date, department, department change date, work hours, etc etc) -- seems to me maintained manually in an Excel spreadsheet. Needless to say it lacks standardization, validation, is subject to errors, lack of completeness, etc. Not good.

I'm not sure the most straightforward way to improve this. We currently use HR software that lacks an API. And I'm not even certain it contains all the necessary data fields, or input validation.

In an ideal world, I could just demand we find new software that has an API and data validation. But I don't see that happening. But maybe that's the only solution.

Otherwise, I'm trying to think of a simple solution where users can view the current database (id, employee name, start date, department, etc) ... and possibly either make direct changes, or at least propose changes to be approved. Some kind of input that at least has basic checks and validations, timestamps changes and who made them, etc.

I know this is a pretty open-ended question, but what is a possible straightforward solution?

Ideally I'd like to not have to piece together some application from scratch, at least not too much.

Can't be the first business here trying to track department headcounts and costs month to month.

At the end of the day, a lot of this stuff does have to entered by a human at some point. If someone changes departments, that's largely an abstraction. A computer system may not know if someone went from working to marketing and is now working in sales. A human being will have to enter this change somewhere (ideally a dedicated application, but not necessarily) -- the person, the change, the effective date. What's really needed is better validation controls and business rules that either an application, I myself, or another developer, can impose.

For instance --- non duplication of employee IDs. (both not giving Mark and John the same ID, nor giving Mark two different IDs). An employee only working associated/ under 1 department at a given time. Easy views and management of changes (aka the leave date was incorrect, change history and timestamp of updates would be useful). Maybe some decent payroll/ accounting software can be leveraged. I mean, this isn't reinventing the wheel, I wonder how mature/ Fortune 500 companies manage this. Probably dedicated software, but eh.

The problem is the database needs this information. Currently it receives it from a garbage Excel spreadsheet. I'm trying to find ways to make sure the information is not garbage. This needs to be done in the form of a restriction, or alert, to the end user before the garbage is submitted down the chain. At the very least maybe I can send them an Excel sheet that has its own validation in cells, but that's comically unsuitable long term.

If you run SQL Server Enterprise edition it comes bundled with Master Data Services which sounds like it would do what you want. It was originally intended to merge multiple sets of data and come out with a golden master but if you think about it that process can be applied to almost any data work.

It's non-cloud so safe for internal use. It has a web application used to define and edit the schema and relations, and also for users to view/enter/edit existing data (they can also do the latter through an Excel add-on which is pretty cool).

Using the same web app you can also set rudimentary business rules about data checking, set up data export options (views), and there are other options for initially loading data using T-SQL.

Data changes have lineage information and can be held back until approved by someone authorized. So there's a little workflow component possible, though I haven't seen that in use much.

For schema changes, it has some versioning/lineage capability, but I'm not 100% sure if you can see who made the change and/or if you can separate doing the change to the model from authorising a deployment of it. It has granular security, and it has a Metadata model to retain this information, but Microsoft have been on/off about whether they're deprecating that future or not.


How to only restore a specific filegroup? I am using SQL Server 2008R2 (but I can upgrade if necessary)

I have a database with multiple schemas and each schema is on a different filegroup (I wish I could just backup/restore a schema but that doesn't seem to be possible).

The recovery model is full.

I am performing backups of the filegroups.

How do I perform a filegroup to a previous state (without affecting the data on other filegroups?).
When I try to restore a file group I need to restore the transaction log which adds all changes since the last backup.

Is what I am asking even possible?

A filegroup restore can be done on a database in FULL recovery model.  There are requirements and consequences of the restore.

You must be using Enterprise Edition.
The primary filegroup must be the first restore.
Filegroups that are not yet restored remain offline.

Start reading at this topic in MSDN:  http://msdn.microsoft.com/en-us/library/ms177425(v=sql.105).aspx

Read the topic: "Piecemeal Restore Under the Full Recovery Model"


Query from 1st domain and insert in 2nd domain I have data in one of my websites. I want to print these data in other site. These two are in same server. At present, I have to export MySQL data and import into other then do this code to insert in 2nd domain.

I want to link directly so that I can make data request from 1st website and show in 2nd website.

I know for this to happen, I need to enable bind-address = 127.0.0.1 to ip address of server and I have changed this.

After that in first website, I have granted permission for that table using this command:

GRANT select ON `database-name.tableA`.* TO 'userB'@'localhost';

but I am getting this error:

  Failed to connect to MySQL:
  Access denied for user 'userA'@'localhost' to database 'database-name.tableA'

The code I am running from website B:

databaseconnect.php

    <?php
    $con=mysqli_connect("localhost","UserA","passwordA","databaseA");

  if (mysqli_connect_errno($con))
  {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
    ?>

main.php

      <?
                    include('/home/website2/public_html/databaseconnect.php');
                    $res2 = $con->query("SELECT * FROM `website1_tableA`  where status='0' order by id asc limit 5");
                        while($result = $res2->fetch_assoc()){
                        $cont=$result['content'];
                        $tit=$result['title'];
                            echo $cont;

            }

            ?>

Your GRANT statement seems incorrect.

GRANT select ON `database-name.tableA`.* TO 'userB'@'localhost';

This grants the SELECT privilege on all tables (*) in a database called "database-name.tableA".

The correct statement would be this:

GRANT select ON `database-name`.`tableA` TO 'userB'@'localhost';

Since "database-name.tableA" isn't a valid database, userA can't possibly have permission to access it.  When a user tries to grant a permission to another use, the first user must have the privilege they are trying to give to another user, and the first user must have the GRANT OPTION.

You may be asking, "why doesn't the error message tell me there's no such database?"

The reason is because of security.  If you don't have permission to access an object, then you also, in most cases, don't have permission to know whether that object exists.  If the error message told you the object didn't exist, it would be giving you information that you aren't entitled to know.


Comparison and creating new column Table A

    > - Product#|Customer|state|Type
      Glasses |mr. Xyz | MN |Instore
      Glasses |mr. abc | MN |Instore
  perishables|mr. abc12| TN |Instore
 N-perishables|mr. abc122| GN |Instore

Table B

> - Product#|Name|state|Type
  Glasses |mr. fff | MN |online
  Glasses |mr. abc | MN |online
perishables|mr. abc12 | TN |online
 Non-perishables|mr. abc122 | GN |online

    `

Trying to create new column and then do comparing?
Condition, if the type is in-store they are never new customer, if they are online they could be new customer only if their record do not exist in In-store Type.

o/p

Type|customer| New Customer?
Instore|mr. Xyz  |No
Instore|mr. abc  |No
Instore|mr. abc12|No
Instore|mr. abc122|No
Online| mr.fff    |Yes -"as it's record is not available  in-store type, hence it will be labelled as new customer"

Query tried:

select
       `Customer`,
    CASE
    WHEN `customer` in (select `Customer` from `Table B`)  THEN 'Yes'
    ELSE 'NO'
END AS 'new customer'
FROM Table A`

Please give me optimized query, as its huge data in millions.

IN ( SELECT ... ) is often an inefficient way to design a query.  There are two alternatives below.  Also, your IN was asking to fetch the entire array; this is probably causing terrible performance.  You only need to check for the "existence" of one customer.

Note that each formulation checks for one "customer" in Table B and does not fetch the entire table (as is done with your IN ( SELECT ... )).

Two approaches:

SELECT  Customer,
        IF(b.customer IS NOT NULL, 'Yes', 'No') AS "new customer"
    FROM  `Table A` AS a
    LEFT JOIN  `Table B` AS b USING(customer);

SELECT  Customer,
        IF(EXISTS (
                SELECT  1
                    FROM  `Table B`
                    WHERE  customer = a.customer ) , 'Yes', 'No'
          ) AS "new customer"
    FROM  `Table A` AS a;

LEFT implies that the row may be missing from Table B.  This is exactly what you want in order to produce "No".

EXISTS is a "semi-join".  It stops when it finds success; this can be handy if there could be multiple matching rows (which is not your case).


Split date range into indiviual records I have a table called staff_leave, and I want to split date range to days per row.
is it possible. I am a newbie please take it easy .

like: there is a date range 01/01/2013 - 05/01/2013

and i want to split it to 5 days and store each day in each single row of another table.

CREATE TABLE IF NOT EXISTS `staff_leave` (
  `id_staff_leave` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id_staff` int(11) NOT NULL,
  `leave_type_id_leave_type` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `joining_date` date NOT NULL,
  `is_half_day` date DEFAULT NULL,
  `approved` int(11) DEFAULT NULL,
  `approved_date` date DEFAULT NULL,
  PRIMARY KEY (`id_staff_leave`),
  UNIQUE KEY `id_staff_leave_UNIQUE` (`id_staff_leave`),
  KEY `fk_staff_leave_staff1` (`staff_id_staff`),
  KEY `fk_staff_leave_leave_type1` (`leave_type_id_leave_type`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

I am assuming the following:

This is a one time job you want to get done
You want another table, in almost identical format to the former, where each row in the original table is duplicated many times in the new table, as many times as there are days between start_date and end_date.

Here's a way to do so:

First, create a table with all possible dates (actually you can use an integer tally table as described above, but I will simplify for purpose of this demonstration):

CREATE TABLE all_dates(dt DATETIME PRIMARY KEY);

INSERT into the above table all dates possible in your system (e.g. 1970-01-01 and up to 2199-12-31, whatever). I mentioned my assumption is that this is a one time job, so this table can be later dropped.

Also create your new exploded table:

CREATE TABLE IF NOT EXISTS `staff_leave_exploded` (
  `id_exploded` int unsigned AUTO_INCREMENT
  `id_staff_leave` int(11) NOT NULL,
  `staff_id_staff` int(11) NOT NULL,
  `leave_type_id_leave_type` int(11) NOT NULL,
  `single_date` date NOT NULL,
  `joining_date` date NOT NULL,
  `is_half_day` date DEFAULT NULL,
  `approved` int(11) DEFAULT NULL,
  `approved_date` date DEFAULT NULL,
  PRIMARY KEY (`id_exploded`),
  KEY `id_staff_leave` (`id_staff_leave`),
  KEY `fk_staff_leave_staff1` (`staff_id_staff`),
  KEY `fk_staff_leave_leave_type1` (`leave_type_id_leave_type`)
) ENGINE=InnoDB;

And now

INSERT INTO staff_leave_exploded SELECT
  NULL, id_staff_leave, staff_id_staff, leave_type_id,
  dt,
  joining_date, is_half_day, approved, approved_date
FROM
  staff_leave JOIN all_dates ON (all_dates.dt BETWEEN start_date AND end_date)
;


Object name 'Clean Up History' in use when trying to complete the maintenance plan wizard in SQL Server 2008 R2 I am trying to create a maintenance plan on a instance running SQL Server 2008 R2 SP1 (no CU's installed).

When completing the wizard I get the following error:

  The object name cannot be changed from
  "{1E1746D3-B671-4799-8F61-7EE6117257C2}" to "Clean Up History" because
  another object in the collection already uses that name. Use a
  different name to resolve this error.
  ({1E1746D3-B671-4799-8F61-7EE6117257C2})

I've checked both [msdb].[dbo].[sysjobsteps] and [msdb].[dbo].[sysjobs] and neither contain the name "Clean Up History".

The server is being backed up by Microsoft DPM. Could it be that DPM inserts a cleanup job somewhere that I don't see?

The job is "owned" by a maintenance plan in the following table, and I don't believe the names have to match. So you may want to check the following location:

SELECT name,id,description
  FROM msdb..sysssispackages
  WHERE name = N'Clean Up History';

So you could rename that one, but it may cause problems later (some automated process may need to match on that name, or simply re-create it if it doesn't find it). So a much safer approach would be to use a different name for your own maintenance plan...


Correlated subquery and join: still the same exectution plan? I have a correlated subquery like this (from BOL):

SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
GO

When I rewrite this query using joins

select c.LastName, c.FirstName, e.BusinessEntityID, d.Bonus
from Person.Person as c
    inner join HumanResources.Employee as e on e.BusinessEntityID = c.BusinessEntityID
    inner join Sales.SalesPerson as d on d.BusinessEntityID = c.BusinessEntityID
where Bonus = 5000.00

And look the actual execution plan, it looks exactly the same in both queries. Why? I was thinking that correlated subquery is much slower because of the nested loop and the execution plan looks different? Is it because there is not much data in these tables?

The two queries are logically identical and do produce the same plan. The simplification phase of the Query Optimizer handles this.

They're identical because of the constraints that are on the tables - foreign keys, uniqueness, nullability...


Secure postgresql database encryption According to this  answer, it's not possible to have a fully secure database encryption with pgcrypto.

Now, 2 years later (in postgresql 9.3)  is there any new extension or new method to have a secure database encryption, or doing this on the application server is still the only way to be sure that someone with database access cannot decrypt the data?

There have been no significant enhancements in pgcrypto, and there's no functionality to allow functions to be marked sensitive such that queries that use them don't get logged. That'd fill one security hole and create another anyway.

So long as you don't actually store the key in the DB, you're not inherently giving the DB the ability to decrypt the data. You're just increasing the risk of key exposure through logs, network protocol captures (if not SSL), pg_stat_activity, etc. If you trust both the DB server and app server, this may not actually matter as much. Nonetheless, I still think it's better that the DB never see the decryption keys at all.

See also:

https://stackoverflow.com/q/12614523/398670
https://stackoverflow.com/q/21095026/398670
https://stackoverflow.com/q/8054503/398670
https://stackoverflow.com/q/12329652/398670
https://stackoverflow.com/q/12329652/398670


One-to-many to multiple tables What's the normal way to design a relationship where every entry in table A is connected(1:n) to either an entry in table B or table C?

Or specifically: There are a tables for characters, movies, episodes and TV show season. One character is connected to either 1) a movie or 2) a show(in addition to n:n to season(season regular) and episode)

x can be the id for both a movie and a show.

Two identical tables(tv_characters and movie_characters)?

Is it better with two primary keys(one will always be either "movie" or "tv")?

Use a cross reference table to link characters to media, as in:

CREATE TABLE dbo.Characters
(
    CharacterID INT NOT NULL
        CONSTRAINT PK_Characters
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CharacterName VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.MediaTypes
(
    MediaTypeID INT NOT NULL
        CONSTRAINT PK_MediaTypes
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , MediaTypeName VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.Media
(
    MediaID INT NOT NULL
        CONSTRAINT PK_Media
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , MediaName VARCHAR(100) NOT NULL
    , MediaTypeID INT NOT NULL
        CONSTRAINT FK_Media_MediaTypeID
        FOREIGN KEY
        REFERENCES dbo.MediaTypes(MediaTypeID)
);

CREATE TABLE dbo.CharactersMedia
(
    MediaID INT NOT NULL
    , CharacterID INT NOT NULL
    , CONSTRAINT PK_CharactersMedia
        PRIMARY KEY CLUSTERED
        (MediaID, CharacterID)
);

INSERT INTO dbo.MediaTypes (MediaTypeName)
VALUES ('TV Show')
    , ('Movie');

INSERT INTO dbo.Characters (CharacterName)
VALUES ('Don Juan')
    , ('Don Johnson');

INSERT INTO dbo.Media (MediaName, MediaTypeID)
VALUES ('Miami Vice', 1)
    , ('Don Juan', 2);

INSERT INTO dbo.CharactersMedia (MediaID, CharacterID)
VALUES (1, 2)
    , (2, 1);

SELECT m.MediaName
    , c.CharacterName
    , mt.MediaTypeName
FROM dbo.Media m
    INNER JOIN dbo.CharactersMedia cm ON m.MediaID = cm.MediaID
    INNER JOIN dbo.Characters c ON cm.CharacterID = c.CharacterID
    INNER JOIN dbo.MediaTypes mt ON m.MediaTypeID = mt.MediaTypeID;


What happens if sys.aud$ is full in Oracle? I am not seeing this in the documentation, but what happens when the Oracle 10.2 audit trail is full? Does it just not making any more audit records? Overwrites the oldest records? Shuts down the database? And is there any way I can change that?

Yes, I know 10.2 is not supported, but I don't have control over that. And yes there are procedures in place that should never need me to find out first-hand. But in cases of emergency failure of the auditing operations, what is the default behavior if the audit trail becomes full?

Your database essentially freezes and does not allow any new connections or transactions that would be writing into the audit logs.  Your only bet would be to connect directly on the server, e.g. "sqlplus / as sysdba" and then add space (or truncate it if auditing is not important to that database).

For this reason we have scripts to backup aud$ and truncate it regularly.  We don't want horrible freezing going on in production...

You also want to move aud$ away from SYSTEM, something like this:

alter table sys.aud$ move tablespace aud_data;
alter table sys.aud$ move lob (sqlbind) store as (tablespace aud_data);
alter table sys.aud$ move lob (sqltext) store as (tablespace aud_data);
alter index sys.I_AUD1 rebuild tablespace aud_idx;


Can Postgres restrict usage of the SET and SHOW commands? Is it possible to restrict a role from being able to set arbitrary session variables, or from being able to view them?

select current_setting('x');

show x;

Even after I've switched roles to a one with lesser permissions, I'm able to run both of the above and retrieve a value I might want hidden from that role. I think that it might be possible to lock down the first statement (that's a built-in function, is it not?), but I can't find anything in the documentation about the latter statement concerning grants or permissions.

There are no session parameters in PostgreSQL; SET changes the value of a global parameter for the current session.  If you use it to set a custom parameter (with a name containing .) that does not yet exist, a so-called placeholder parameter is created. There is no way to restrict permissions to view such a placeholder parameter.

What you can do is write a module in C that creates a real parameter. That module has to have a _PG_init function that calls DefineCustomStringVariable (if it is a string you want). That module would then be added to shared_preload_libraries, so that the parameter is defined when the server starts.

Such a parameter can be defined as GUC_SUPERUSER_ONLY, which means that only a superuser (or a member of the pg_read_all_settings role) can see its value.


Database Tuning Advisor Index Recommendations How does DTA make an index recommendation? What is the technology behind that? If we can use same on our side then we can shorten the whole process of Tuning recommendation:

creating trace file using SQL Profiler
Use that trace file in DTA
Then look for recommendations
Follow the recommendations & see the results in the Query Execution Plan.

I just wanted to know if it is there any way to create a predictable index.

How does DTA make an index recommendation? What is the technology behind that?

You can find the answers in a detailed paper on the Database Tuning Advisor published by Microsoft (click on the PDF link on that page to download the complete document).

The missing index DMVs are not a good substitute for a full DTA analysis. The DMVs show only very limited information opportunistically gathered during query optimization. DTA performs a much more thorough analysis, though you should not automatically create every index it suggests. You need to assess each recommendation on its merits.


SQL Server 'Create Database' statement. How to inherit autogrowth settings? I am using SQL Server 2008 R2 and creating databases through continuous deployment.

In our system, the default 1Mb/10% autogrowth settings in SQL Server work badly with our data. Particularly as we have a legacy application that prevents us from changing the schema much. We would like the database settings to be configured at the instance level so we can change it for staged deployments.

I have read in several places that default settings for a new db are based on the settings for 'model', but it appears this only works by clicking new database in SQL Management studio UI and not from a script e.g. CREATE DATABASE [MyDb].

msdn.microsoft.com/en-us/library/ms186388(v=sql.105).aspx

sqlservercentral.com/Forums/Topic1065073-391-1.aspx

https://stackoverflow.com/questions/8828557/possible-to-configure-database-autogrowth-settings-at-the-instance-level/8828604#comment15586568_8828604

Has anyone actually got this to work with a create script? Is there another way I can set autogrowth per server instance?

For SQL Server 2005+ You can use the following:

SELECT FILE_ID, type, type_desc,
CAST( CAST( (CASE WHEN size < 128 THEN 128 ELSE size END) * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB' AS SIZE,
CASE max_size WHEN -1 THEN 'UNLIMITED' ELSE CAST( CAST( max_size * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB' END  AS MAXSIZE,
CASE is_percent_growth
    WHEN 0 THEN CAST( CAST( growth * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB'
    WHEN 1 THEN CAST( growth AS VARCHAR(30) ) + '%'
END AS FILEGROWTH
FROM sys.master_files
WHERE database_id = DB_ID('model')
GO

This will retrieve File definitions for Model Database.
Later on You can use it either in CREATE DATABASE or ALTER DATABASE.

SSMS actually uses SMO to retrieve these parameters and then creates a Script which looks like:

CREATE DATABASE [aaa] ON  PRIMARY
( NAME = N'aaa', FILENAME = N'D:\Data\aaa.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'aaa_log', FILENAME = N'D:\Logs\aaa_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

If Your Application is creating the Database and You can't modify the application -
then You have to use ALTER DATABASE after creation is done:

ALTER DATABASE [aaa] MODIFY FILE ( NAME = N'aaa', MAXSIZE = 102400KB )
GO
ALTER DATABASE [aaa] MODIFY FILE ( NAME = N'aaa_log', FILEGROWTH = 10240KB )
GO

This requires at least ALTER DATABASE Permission
(implied by ALTER ANY DATABASE Server Permission)
Obviously, You can generate that script using the first code sample (FROM sys.master_files).

I wouldn't use sysaltfiles for SQL Server 2008R2 because it is Deprecated.

Good luck,
Roi


Table partition in aws MYSQL RDS master slave setup we are looking to do a table partition in MYSQL RDS running 5.6, table size is large and takes around 6 hours to partition. Can we partition on rds slave and then later promote it to master. is this do able, if not what is best way to partition this large table

First, let's discuss what benefit you will get from partitioning.  Please provide SHOW CREATE TABLE (with the partitioning).  Partitioning, in itself, does not provide any performance benefits.  Let's see the query/queries that you think will benefit.

pt-online-schema-change is likely to be a way to convert to partitioning with virtually no downtime.  But check carefully; there may be restrictions on partitioning.

The manual says nothing about whether you can have replication between a non-partitioned on the master and a partitioned table on the slave.  The silence of the manual worries me; there could be issues.


Help required to design a database Firstly I would like to apologies if this is not the correct place to ask this question but I know how good community spirit is around here from my time in Stack Overflow.
I am very new to the world of databases but I am looking to create an application that will use a database in the background to hold data.The application is going to be an action plan creator and will hold a lot of data but I have no idea how to design my database. Therefore I am looking for some help.
I will be using MySQL for the database and Java for the application.
Design:
I tried to type out how I would like the design to be but ended up confusing myself so guessed that I would have no luck explaining on here. Therefore, here is an image of what I will be trying to achieve for each action plan:

I hope this helps but to try and explain further, each action plan would have one of these (I am aware that I would have to run some kind of expression to get this) and each time the action number would start from 1. Although I have only added 3 updates in the example above, each action number would have to be able to expand to theoretically unlimited amount.
Reading the tutorials I have spotted so far, I know that this requires some kind of relational database. However, I am not able to get my head around how to set this all up.
I know this is a BIG ask but I would be very grateful for any advise anyone has to offer and I am eager to learn more.

The best first thing you can do is to look around at Open Source offerings in the same area as your own interest. I'm not exactly sure what you mean by "action plan creator" (clarify?). The beauty of Open Source is that you can run the stuff, see what you like and then open up the hood and see how it's done for the applications that appeal to you. Don't forget the KISS and YAGNI principles (Keep It Simple Stupid and You Ain't Gonna Need It). Don't be overambitious on your first pass and don't forget that you'll (very probably :-) ) have to throw the first app away anyway (Fred Brooks).


How to get information about the current cluster configurations with list of datacenters? Is any way to get information about the current cluster configurations with a list of datacenters?

I have tried to fetch this list from system.local using cqlsh with setting the consistency level equal to ALL, but it shows me only one datacenter.

Basically I want to check that all datacenters which I have created are constituting the one cluster.

As far as i understand your question, you can run nodetool status which shows all the data center including all their belonging nodes.

$ nodetool status
Datacenter: DC1
===============
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address        Load       Tokens  Owns (effective)  Host ID                               Rack
UN  <IP>   1.7 TB     256     7.2%              <Host ID>  RAC1

Datacenter: DC2
===============
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address        Load       Tokens  Owns (effective)  Host ID                               Rack
UN  <IP>   1.7 TB     256     7.2%              <Host ID>  RAC1


SQL Server 2016 compress function details - external decompression According to SQL Server 2016 docs COMPRESS and DECOMPRESS methods are just a blackbox - you put the data in and after some magic it gets compressed or decompressed. The problem is that I need to find a way on how to decompress this data outside SQL Server - using Snowflake or Azure Data Factory preferably. Does anyone please have a clue on how to approach this topic? This difficult in particular due to lack of detailed docs on the compression method/algorithm used.

According to SQL Server 2016 docs COMPRESS and DECOMPRESS methods are
just a blackbox

I don't know what documentation page you are referring  to but the  COMPRESS reference states:

This function compresses the input expression, using the GZIP
algorithm. The function returns a byte array of type varbinary(max).

You can then decompress using a GZip library in application code. PowerShell example, using .NET objects:
$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=.;Integrated Security=SSPI;Initial Catalog=tempdb")
$connection.Open()
$command = New-Object System.Data.SqlClient.SqlCommand("SELECT COMPRESS('this is gzip compressed data') AS GzipData;", $connection)
$gzipBytes = $command.ExecuteScalar()
$connection.Close()

$memoryStream = New-Object IO.MemoryStream
$memoryStream.Write($gzipBytes, 0, $gzipBytes.Length)
[void]$memoryStream.Seek(0,0)

$gzipStream = New-Object IO.Compression.GZipStream($memoryStream, [IO.Compression.CompressionMode]::Decompress)
$streamReader = New-Object IO.StreamReader($gzipStream)
$decompressedData = $streamReader.ReadToEnd()
Write-Host $decompressedData


Odoo Postgres Databases synchronization I have to deploy odoo with postgres database on amazon cloud. That i can do by simply setting up EC2 server and setting uo odoo on it. In case if internet in down, I want to be able to access same services and already saved data offline as well. For that I plan to install odoo with postgres database on my local machine (in my office) as well. Now I can access odoo services from anywhere (using cloud) when there is internet available. But in case if internet is down, I must be able to use locally installed odoo to get same services. For that purpose I need two things

Both databases should be exact replicas of each other.
On recovering from internet disconnectivity, I want the changes made in my local ( office) database reflect in the database at amazon cloud.
I am new to this stuff, kindly suggest the best possible approach (architecture) in this scenario.

I'm currently evaluating a similar setup and there are a couple of solutions.

What you're looking at here is basically a master-master replication scenario, where both databases are writable and changes in each are replicated to the other.

There is Bucardo, which is a postgresql-only replication system written in Perl and there are a number of tutorials for setting it up, check the Odoo community forums, too.

Then there's SymmetricDS, which does the same for a number of different databases and is written in Java.

Since your're dealing with master-master replication, you have to account for the occasion of both databases changing the same rows in the same table at the same time, so you'll have to configure conflict-resolution strategies (basically which change wins, but it can be a lot more involved like calling an external handler to figure out what to do).


Replication in same server within different databases. Mysql 5.6 What I have :

a windows machine with one instance of MySQL 5.6 Server.
two database named test and test2.
Test database has a table called activity with columns id and class

What I need :

replication of test.activity table to test2 database with a condition that Test.activity.class = 'B'.

I know how to replicate with in different server. But not for same server within different database.I have checked this link , but it does not give enough information.

Cause it's on the same server, why don't you use a trigger for this?

If you just wanna replicate data you can add a triger on update or on insert or both whatever you need. This way you can use your condition easily. Master/Slave replication would be better for different servers.

You can look here for mysql Trigger examples.
And here is a sample for your case.