Best query to exclude existing friends from query I have a User table

/ id_user / username / ..

friends table (pair AB and BA):

/ friend_of / friend_to

and a friend_request table (used for pending friend requests)

/ id_req/ sent_from / sent_to

I would like to have a little search for new friend feature (autosuggestion style) and I would like NOT to show in the list friends that are ALREADY in my friends list OR being in a friend request pending.

The idea behind the query would be to show the username of user NOT in the friend_to column and NOT in the sent_to column.

I came up with these two flavors:

select id_user,username
from public.user u
where
username like '%tom%'
and
u.id_user NOT IN (SELECT sent_to FROM friend_request where sent_from = 288)
AND
u.id_user NOT IN (SELECT friend_to FROM friends where friend_of=288);

OR

select id_user,username
from public.user u
left join friend_request fr
on
u.id_user=sent_to AND fr.sent_from=288
left join friends f
on
u.id_user = f.friend_to AND f.friend_of=288
where username like '%tom%' AND sent_to IS NULL and friend_to IS NULL;

Both of these produce the same result. But I would like some advice on which one is a better choice performance and bets practice wise?

NOT IN is typically the slowest option. LEFT JOIN / IS NULL is more promising. Or NOT EXISTS:

Select rows where value of second column is not present in first column

Query

Just your query, formatted:

SELECT u.id_user, u.username
FROM   "user" u
LEFT   JOIN friend_request fr ON fr.sent_to = u.id_user
                             AND fr.sent_from = 288
LEFT   JOIN friends f ON f.friend_to = u.id_user
                     AND f.friend_of = 288
WHERE  u.username LIKE '%tom%'
AND    fr.sent_to IS NULL
AND    f.friend_to IS NULL;

Or, a variant with NOT EXISTS anti-semi-joins:

SELECT u.id_user, u.username
FROM   "user" u
WHERE  NOT EXISTS (
   SELECT 1 FROM friend_request
   WHERE  sent_to = u.id_user
   AND    sent_from = 288
   )
AND    NOT EXISTS (
   SELECT 1 FROM friends
   WHERE  friend_to = u.id_user
   AND    friend_of = 288
   )
WHERE  u.username LIKE '%tom%';

Indexes

The most important factor for performance. An ideal setup would have:

A trigram GIN or GiST index on "user".username. You need the additional module pg_trgm. Instructions - and Gin or GiST?

How is LIKE implemented?

If index-only scans are possible, append the otherwise useless id_user. You need the additional module btree_gin (or btree_gist respectively):

Inner join using an array column

Then:

CREATE INDEX usr_trgm_idx ON "user" USING gin (username gin_trgm_ops, id_user);

Multicolumn indexes (or a UNIQUE / PK constraint) on (sent_from, sent_to) and (friend_of, friend_to) - with columns in that order. The reversed order would work, too, but not quite as good. Each query checks for a single friend_of combined with many different friend_to, which is faster with the suggested column order, because this way tuples are much rather localized on the same or a few index pages.

That said, the proof of the pudding is still in the eating.
Get the final verdict from EXPLAIN (BUFFERS, ANALYZE) in your production database.

Aside

Do not call your table user, that's a reserved word.


Auditing DDL with MySQL We have a MySQL database on an IBM i (as/400) that has many people with admin access. We would like to audit who is making changes, and when they are made. On SQL Server we would use DDL Triggers, but those don't exist on MySQL. There is a counter proposal to do this via CREATE EVENT, but that is also unimplemented.

What options do I have to monitor DDL? Most people are using MySQL admin. I'm using MySQL Workbench, and inside the network, and inside the network all parties have direct access to the MySQL database because Microsoft Access accesses it via linked tables.

If you have a license for enterprise edition, upgrade to 5.5 or 5.7 and use the audit log plugin.


Error java.sql.SQLException: ORA-01555 Hey I am running a select * from  query against ORACLE database. So is there a way we can avoid this kind of error.

The query is fetching a large amount of data. It can take weeks to get that amount of that.

java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 50 with name "_SYSSMU50_1322772922$" too small

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1018)
at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:291)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:213)

This may be helpful: http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm

Sorry, I know this belongs in the comments section, but SO won't let me comment until my rating is above 50 :(.


SQL Server - Select most recent record from each group when performance is critical I run a SQL Server 2016 database where I have the following table with 100+ millions rows:

StationId | ParameterId |       DateTime       | Value
    1     |      2      | 2020-02-04 15:00:000 |  5.20
    1     |      2      | 2020-02-04 14:00:000 |  5.20
    1     |      2      | 2020-02-04 13:00:000 |  5.20
    1     |      3      | 2020-02-04 15:00:000 |  2.81
    1     |      3      | 2020-02-04 14:00:000 |  2.81
    1     |      4      | 2020-02-04 15:00:000 |  5.23
    2     |      2      | 2020-02-04 15:00:000 |  3.70
    2     |      4      | 2020-02-04 15:00:000 |  12.20
    3     |      2      | 2020-02-04 15:00:000 |  1.10

This table has a clustered index for StationId, ParameterId and DateTime, in this order, all ascending.

What I need is, for each unique pair StationId - ParameterId, return the most recent value from DateTime column:

StationId | ParameterId |       LastDate
    1     |      2      | 2020-02-04 15:00:000
    1     |      3      | 2020-02-04 15:00:000
    1     |      4      | 2020-02-04 15:00:000
    2     |      2      | 2020-02-04 15:00:000
    2     |      4      | 2020-02-04 15:00:000
    3     |      2      | 2020-02-04 15:00:000

What I'm doing now is the following query, which takes around 90 to 120 seconds to run:

    SELECT StationId, ParameterId, MAX(DateTime) AS LastDate
    FROM  MyTable WITH (NOLOCK)
    GROUP BY StationId, ParameterId

I've also seen many posts suggesting the following, which takes 10+ minutes to run:

    SELECT StationId, ParameterId, DateTime AS LastDate
    FROM
    (
       SELECT StationId, ParameterId, DateTime
       ,ROW_NUMBER() OVER (PARTITION BY StationId,ParameterIdORDER BY DateTime DESC) as row_num
       FROM  MyTable WITH (NOLOCK)
    )
    WHERE row_num = 1

Even in the best case (using GROUP BY clause and MAX aggregate funcition), the execution plan doesn't indicate an Index Seek:

I wonder if there's a better way to perform this query (or to build the index) in order to achieve better execution time.

If you have a small-enough number of (StationID, ParameterID) pairs, then try a query like this:

select StationID, ParameterID, m.DateTime LastDate
from StationParameter sp
cross apply
  (
     select top 1 DateTime
     from MyTable
     where StationID = sp.StationID
      and ParameterID = sp.ParameterID
     order by DateTime desc
  ) m

To enable SQL Server to perform a lookup, seeking the latest DateTime for each (StationID,ParameterID) pair.

With only a Clustered Index on (StationID, ParameterID, DateTime), there's no way for SQL Server to discover the distinct (StationID, ParameterID) pairs without scanning the leaf level of the index, and it can find the largest DateTime while it's scanning.

Also at 100M+ rows, this table might be better as a Clustered Columnstore instead of a BTree Clustered Index.


TRUNC in oracle leaves minutes I have a doubt with the use of TRUNC function in Oracle. As read in the documentation:

  The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt

I tried this code on my Oracle client:

SELECT
    TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH:MM:SS AM') "Today's Date and Time"
from DUAL;

But all what I get is this: 10/26/2017 12:10:00 AM

It isn't supposed to be 10/26/2017 00:00:00 AM?

By the way, the SYSDATE is this: 10/26/2017 10:10:05 AM

Thanks in advance

Your format string is not correct.

HH:MM:SS should be HH:MI:SS.

Also, when using the 12 hour format, midnight is 12:00:00 AM, not 00:00:00 AM.


How to index an property object in MongoDB? I've the following sample document:

{
    "_id" : ObjectId("556e177ca43da29e040041a7"),
    "user_id" : NumberLong(24),
    "request_datetime" : ISODate("2015-06-02T15:52:12.000-05:00"),
    "total_amount" : 102.62,
    "status" : NumberLong(1),
    "adfields" : {
        "27" : "413703562015060200132",
        "28" : "S003POS4",
        "29" : "HAYLEYS",
        "30" : "1"
    }
}

The problem is that i need to index the "adfields" property, but it doesn't work with a "multikey" index type, neither using "single field index".
I tried to index the adfields property using a single field index, but when i make a query for an specific value of adfields.27 it doesn't use the index.
I can't make an single field index for adfields.27 because those numbers are created dynamically.

Do you have any idea of how to do this?
Thanks a lot.

I was able to make this work after altering your model. Hopefully this still fulfills the use case you are targeting. To bypass the dynamic nature of your fields, I created a field named "field" and a field named "value". This allows me to create a multikey index on known fields.

db.myColl.insert(
{

"user_id" : NumberLong(24),
"request_datetime" : ISODate("2015-06-02T15:52:12.000-05:00"),
"total_amount" : 102.62,
"status" : NumberLong(1),
"adfields" : [
    {field:"27", value : "413703562015060200132"},
    {field:"28", value: "S003POS4"},
    {field:"29", value: "HAYLEYS"},
    {field:"30", value: "1"}
    ]

})

Then I executed a find against this for db.myColl.find({"adfield.field":"30"}) for a before picture of the query. I also used db.myColl.explain().find() to analyze it.

Then I created the index via     db.myColl.createIndex({"adfield.field"1,"adfield.value":1"}).

I executed the find from above and sure enough I see an IXScan and isMultiKey is true in the explain() output.

Again; hopefully the alteration to your model still keeps with the spirit of what you are attempting to accomplish.

You can read more details about mutlikey via http://docs.mongodb.org/manual/core/index-multikey/ .


Deciding what columns to use for partitioning I am having trouble deciding what fields of my table to base the partition. Currently there are a total of 8 indexes on the table out of a total of 14 columns. The problem is the indexes are taking up too much space 2 to 1 compared to the data and we need to reduce the size of the DB, and at least maintain current speed. 3 of the indexes have a significantly lower cardinality (in the teens) than the rest (in the millions). There are over 40 million rows in the table.

The index data type looks something like this:

datetime
smallint(6) (low cardinality)
varchar(20) (low cardinality)
int(15)
int(15)
varchar(100)
varchar30
char(1) (low cardinality)

and a few others. All the indexes are non unique and of type BTREE.

I created a test table and removed the indexes with the lowest cardinality but the queries take a bit longer. I'd like suggestions on two aspects:

What indexes should be kept and, which should be removed.
What columns should my partitioning be based on.

The table definition:

Create Table: CREATE TABLE `mytable` (
  `date` datetime DEFAULT NULL,
  `time` smallint(6) DEFAULT NULL,
  `source` varchar(20) DEFAULT NULL,
  `b` int(15) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `ttype` char(1) DEFAULT NULL,
  `ag` char(1) DEFAULT NULL,
  `direction` char(1) DEFAULT NULL,
  `mode` char(1) DEFAULT NULL,
  `username` varchar(30) DEFAULT NULL,
  `name` varchar(15) DEFAULT NULL,
  `authentication` char(1) DEFAULT NULL,
  `authenticated_id` varchar(75) DEFAULT NULL,
  `status` char(1) DEFAULT NULL,
  KEY `date` (`date`),
  KEY `time` (`time`),
  KEY `b` (`b`),
  KEY `name` (`name`),
  KEY `username` (`username`),
  KEY `source` (`source`),
  KEY `b2` (`b`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Always have an explicit PRIMARY KEY for any InnoDB table.  If practical, use a "natural" key -- a column, or combination of columns, that is unique.  Otherwise, add an AUTO_INCREMENT.

Learn about "composite indexes" -- this is using more than one column in an index.  It is often a good optimization.  It is not the same as having an index on each column.

It is OK for the indexes to take up more space than the data.  A quick survey showed 1/4 of my tables have that.  Having good indexes improves performance far more than the bulkiness hurts performance.

Each Index is updated for each row INSERTed, so having too many indexes hurts INSERTs (some).

You mentioned partitioning; did you mean "PARTITION BY ..."?  Don't do it unless you have a particular purpose.  I know of only 4 use cases; see my blog.

Do not bother to index low-cardinality columns (flags, enums, etc).  They won't be used.  If you have a status flag that is 10/90, then an index on just that column would be used for the 10, but not for the 90.  A composite index starting with that column may be always useful when all the columns (in the index) are in the WHERE clause.

The most important performance setting for InnoDB is to set innodb_buffer_pool_size to about 70% of available RAM.

It is usually a bad idea split DATE and TIME into two fields.  Look at how messy your SELECTs have to be.

How to create good indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Show us some of the SELECTs; we can advise further.


Select pseudorandom row from a very small partial index in Postgres? I have a huge table, with a tiny partial index on <0.1% of the rows satisfying a certain condition (one column is null). I would like to pseudorandomly select a row that satisfies this condition. Can I use the partial index that already exists to speed this up, and if so, how?

Shouldn't be a problem, just put the WHERE condition of your partial index into the WHERE condition of your query.

create table foobar as select id, case when random()<0.001 then NULL else random() END as nullable, random() as z from generate_series(1,1000000) f(id);
create index on foobar (id ) where nullable is null;
select * from foobar
   where nullable is null order by random() limit 1;

This does have to read all the indexed rows, so there is only fast it can be.  It also has to "sort" all the rows, but since the sort knows about the LIMIT it is not a full sort and so is not really Nlog(N).

If you are willing to augment your table with a column of random numbers (which I had already done in my example, just for filler at the time), then you can do better.

create table foobar as select id, case when random()<0.001 then NULL else random() END as nullable, random() as z from generate_series(1,50000000) f(id);
create index on foobar (z) where nullable is null;

with rand as (select random()/1.0005 as rand)
select foobar.* from foobar,rand where nullable is null
  and z between rand and rand+ 0.0005
order  by random() limit  1;

You would have to tune the constant 0.0005 with knowledge of how many indexed rows there are.  If you make it too small, there is the possibility that the range will contain not rows and so you get no result (you could then retry it), and if you make it too large you use more time than necessary.

Without changing the table, then I think you can't do much better than reading all of the indexed tuples; without being willing to compromise on the quality of the randomness.


Select one record from LAG Query I'm having difficulty getting data from a query using the LAG method. The issue is that I only need one record from the entire set pulled down, but when I filter out that record in the WHERE clause it prevents the LAG method from using the entire dataset for its comparisons, and just returns NULL. For example, I have this set of records in my DB:

All I want is the previous status to "Finished". But when I run this query, it only returns NULL because there is only one record the LAG method is working with:

SELECT
    s.StatusName, LAG(s.StatusName) OVER (ORDER BY cs.CustomerId, cs.CustomerStatusId) as PreviousStatus
FROM
    #CustomerStatus as cs
    left join #Status as s on s.StatusId = cs.StatusId
WHERE
    cs.StatusId = 3

ORDER BY
    cs.CustomerStatusId

If I remove the WHERE clause, it pulls the dataset below correctly, but I only want the third record:

Is there a way for me to grab only that one record from the results?

If you would like to work with my sample data, here is the Rexter link:

Rexter - LAG

It is happening because of the row being filtered before the LAG() function executes, making it unable to get the previous results.

To work around this, we could use a CTE or a subquery to execute the LAG() function before applying the filter.

Using a CTE

;WITH CTE  AS
(
SELECT
    s.StatusName, LAG(s.StatusName) OVER (ORDER BY cs.CustomerId, cs.CustomerStatusId) as PreviousStatus, cs.StatusId,cs.CustomerStatusId
FROM
    #CustomerStatus as cs
    left join #Status as s on s.StatusId = cs.StatusId
)
SELECT StatusName ,PreviousStatus
FROM CTE
WHERE
StatusId = 3
ORDER BY
    CustomerStatusId;

Result

StatusName  PreviousStatus
Finished    Started

Rextester

With a subquery

SELECT outerquery.StatusName ,outerquery.PreviousStatus
FROM (
    SELECT
    s.StatusName, LAG(s.StatusName) OVER (ORDER BY cs.CustomerId, cs.CustomerStatusId) as PreviousStatus, cs.StatusId,cs.CustomerStatusId
    FROM
    #CustomerStatus as cs
    left join #Status as s on s.StatusId = cs.StatusId
     ) as outerquery
WHERE
outerquery.StatusId = 3
ORDER BY
    outerquery.CustomerStatusId;

Result

StatusName  PreviousStatus
Finished    Started

Rextester

The ORDER BY CustomerStatusId; might not be needed if you only need one record


Slow running aggregate of aggregate query I am looking for the total balance for all accounts that had a negative balance as of a specific point in time.  I've already got the code below, and it works, but works horribly.

The transactions table contains 20 million rows. The query below takes about 30 seconds which isn't bad, but there's a second part in that I need to repeat @report_date for the first of every month since the inception of the database, which balloons the execution time to ~30 minutes.

This is on MSSQL2008 and I'm not getting any missing indexes warnings on the execution plan, but I suspect my problem is still with the indexes so I'm intentionally leaving them off of here.

CREATE TABLE transactions(
    transaction_id int,
    account_id int,
    department_id int,
    location_id int,
    post_date date,
    amount money
    );

SELECT
    t2.department_id,
    t2.location_id,
    SUM(t2.credit_balances)
FROM
    (
    SELECT
        t1.department_id,
        t1.location_id,
        t1.account_id,
        SUM(t1.amount) as credit_balances
    FROM
        transactions t1
    WHERE
        t1.post_date < @report_date
    GROUP BY
        t1.department_id,
        t1.location_id,
        t1.account_id
    HAVING
        SUM(t1.amount) < 0
    ) t2
GROUP BY
    t2.department_id,
    t2.location_id;

The table contains 38 distinct department_id,location_id combinations, and 4.5 million distinct department_id,location_id,account_id.

As you are repeating this query for multiple months then you will be continually re-aggregating the same rows.

For example the rows in the first month will always be brought back by the t1.post_date < @report_date criteria so will be re-processed for every month.

To avoid this I'd probably consider working through it in an iterative way a month at a time from the start. Dependent on the volatility of historic data I might also consider storing the pre-calculated results in the database rather than re-calculating these each month.

To calculate this at run time you could create a temporary table with the following structure.

CREATE TABLE #balance
  (
     department_id   INT NOT NULL,
     location_id     INT NOT NULL,
     account_id      INT NOT NULL,
     balance_to_date MONEY NOT NULL,
     PRIMARY KEY (department_id, location_id, account_id)
  );

You could also consider adding the following index on your transactions table

ALTER TABLE transactions
  ADD post_date_year_month AS (10000 * YEAR(post_date) + MONTH(post_date))

CREATE INDEX ix
  ON transactions(post_date_year_month, department_id, location_id, account_id)
  INCLUDE (amount)

Then extract a month at a time from transactions and merge into #balance (with a when matched then increment, when not matched insert).

The leading post_date_year_month column means that as long as you write the query sargably the extraction of each month can be done efficiently and the extracted rows for a month will be ordered by department_id, location_id, account_id making a merge join against #balance possible without a sort.

Whilst that could benefit this particular query you'd need to assess the utility of this index against your overall workload.

Then calculate the department_id, location_id totals from #balance (can leverage the PK order to avoid a sort) and store those somewhere and move onto the next month.

(Or possibly instead of #balance you could use a "temporary" permanent table balance and create an indexed view on that to avoid the separate explicit aggregation step and just copy the values straight from that before moving on)


Compare possible null values in one query Question is pretty short and simple: I have following query:

SELECT *
FROM CTE

Now I want to get all rows with only maximum value of some field.

SELECT *
FROM CTE
WHERE NodeId = (SELECT MAX(NodeId) FROM CTE)

But maximum value can be null, so this query doesn't work, and I should write:

SELECT *
FROM CTE
WHERE NodeId = (SELECT MAX(NodeId) FROM CTE)
OR (SELECT MAX(NodeId) FROM CTE) is null

So I should write another OR do return true if current value is equal to maximum or maximum is null (we shouldn't check for NodeId is null because it defenitly is).

Is there any way to simplify this script?

Your 2nd query seems correct and does what you want.

Another option would be to use the TOP .. WITH TIES but this works only because NULL values are treated as the lowest in SQL Server.

SELECT TOP (1) WITH TIES t.*
FROM cte AS t
ORDER BY t.NodeId DESC ;

If you had MIN() instead of MAX() and you tried the TIES with ASC instead of DESC, the above would fail miserably.


copydb, shutdown commands are not supported in ApsaraDB for MongoDB. Any Alternate Available List of below mentioned commands are not supported on ECS - ApsaraDB for MongoDB.

copydb
clone
clean
shutdown
repairDatabase
repairCursor

Is there alternate available for the same ?

I appreciate any assistance with this.

The commands you've listed are administrative commands that you should expect to be limited when using a managed Database-as-a-Service provider. I would anticipate further restriction for commands that are either subsumed by the managed platform functionality (for example, replica set configuration) or likely to introduce performance/stability issues. For comparison, MongoDB Atlas also has a list of unsupported commands for shared or dedicated clusters.

In the event your deployment gets in state where admin help is needed, the provider's support staff can presumably escalate as required.

For context on the specific commands you've listed:

clone and copydb are officially deprecated since MongoDB 4.0 but also discouraged in older server versions. These commands do not produce point-in-time snapshots of the source database and will introduce performance and concurrency problems (such as blocking foreground index builds). Use mongodump and mongorestore instead.
clean is an internal command with no end user utility.
shutdown doesn't make sense for a managed service where you don't have direct access to restart a MongoDB process. It is possible the provider's UI or API provides an option to restart or suspend a member of your deployment but reasonable not to have direct access.
repairDatabase and  repairCursor should only be used as a last resort to salvage data if you don't have a viable backup for your deployment. This is definitely a scenario where you'd be working with the provider's support team to get your deployment back online with the most reasonable approach.


LOAD DATA statement returns a syntax error a fraction of the time MySQL 5.5.32, MyISAM

A LOAD DATA INFILE statement usually executes and returns normally, succeeding at loading the data. On rare occasion it fails, returning a generic syntax error, always indicating the same character as the problem spot.

ERROR 1064 (42000) at line 36: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to
use near 'ull, @field_name)'

I have no reason to believe there is a syntax error because the large majority of the time it succeeds, and it always succeeds on MySQL 5.5.19 and other versions. So all of these details might be red herrings. But, for what it's worth, that line in the statement is

field_name = if(@field_name='None', null, @field_name);

The question is whether this is a known bug, or a theoretically explainable behavior, and not what the syntax error is. Are there conditions in a database, or perhaps a file or filesystem, that could generate the same generic syntax error inconsistently but persistently like this?

See if this works as a workaround:

field_name = NULLIF(@field_name, 'None');

Suggest upgrading to 5.6, though I doubt if something this obscure is fixed.

Also, you should change from MyISAM to InnoDB.


What can I do with VIEW DEFINITION permissions that I can't do with ALTER permissions? I have created a new user which has ALTER permissions on my database:
CREATE LOGIN [testuser] WITH PASSWORD = '...';
CREATE USER [testuser] FOR LOGIN [testuser];
GRANT ALTER TO [testuser];

To my surprise, ALTER does not imply VIEW DEFINITION:
SELECT HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'ALTER'); -- yields 1
SELECT HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'VIEW DEFINITION'); -- yields 0

And, indeed, according to the documentation, VIEW DEFINITION is only implied by CONTROL. So, apparently, VIEW DEFINITION includes some rights that ALTER doesn't.
Can someone give me an example of what can be done with VIEW DEFINITION permissions which cannot be done with ALTER permissions?
I did some tests (read column default values, script database object definitions), and I was unable to find an example myself - everything I tried "just worked" with ALTER permissions.

VIEW DEFINITION is a way to explicitly GRANT or DENY metadata visibility, but it's not the only way a user gets metadata visibility.
Users implicitly get metadata visibility without the VIEW DEFINITION permission on objects they own or have some other permission on:

The visibility of metadata is limited to securables that a user either
owns or on which the user has been granted some permission. For
example, the following query returns a row if the user has been
granted a permission such as SELECT or INSERT on the table myTable .

Metadata Visibility Configuration
And by default the public role has VIEW DEFINITION.  So while your user does not have the VIEW DEFINITION permission, ALTER on the database implies ALTER on all the objects, which allows the user to view the metadata of the objects.


Duplicate results from query trying to find cross-indexed multiple tables I working with a database of several hundred schemas with 250 tables and thousands of records. PGAdmin III 1.22 as my only tool. A GUI interface to the database is not robustly written and occasionally cross-indexes some entries. I am trying to write a query that shows a list of the cross-indexed items and display their LABEL to the users rather than the ID. This allows the user to use the GUI program to fix the issue with out requiring postgresql knowledge.

Here's the query at this point:

SET search_path="SCHEMA_NAME";
SELECT
  e1.label,
  c1.label,
  h1.contentsid,
  h1.group
FROM
  hostdata as h1
INNER JOIN hostdata as h2
  on h1.group = h2.group
  and h1.contentsid = h2.contentsid
  and h1.id != h2.id
INNER JOIN class as c1
  on t1.classid = c1.id
INNER JOIN element as e1
  on t1.elementid = e1.id
ORDER BY
  t1.group ASC,
  t1.contentsid ASC,
  c1.label ASC,
  e1.label ASC;

The cross-indexing occurs when the GROUP and CONTENTSID fields are both duplicated in two or more records in the HOSTDATA table. It's OK to have one duplicated as long as both fields are not the same. If it matters, the GROUP field is text.

What I am trying to do is compare the records in HOSTDATA to each other, list those that match another record in the GROUP and CONTENTSID fields, and then list them along with the LABELs from the CLASS and ELEMENT tables.

The above query works perfectly as long as there are only two matches, but as soon as the number of matches exceeds that the results multiply - 8 matches results in 7 returns per record - one for each match - 8 times 7 instead of just a list of 8. This is obviously because the first INNER JOIN returns multiple true conditions.

Performance is not an issue. I haven't yet come up with a way to re-arrange the sort and query to return the desired truncated output.

Never mind. I re-wrote it and this seems to work;

SET search_path="SCHEMA_NAME";
SELECT e1.label, c1.label, h1.contentsid, h1.group
FROM hostdata as h1
INNER JOIN (
    SELECT contentsid, group
    FROM hostdata
        GROUP BY contentsid, group
    HAVING COUNT(id) > 1
    ) dup
    ON h1.group = dup.group AND h1.contentsid = dup.contentsid
INNER JOIN class as c1
  on h1.classid = c1.id
INNER JOIN element as e1
  on h1.elementid = e1.id
ORDER BY t1.group, t1.contentsid;


How to create two same name databases in two different MySQL users? I am using MySQL 5.5 running on Ubuntu 12.04.

I have created two different users as 'root' and 'abc' and I want to create 'Items' database in this both user accounts but I want this two databases separated.

Now my problem is that I have created 'Items' database in root user with 10 tables and when I am trying to create a new user 'abc' then 'Items' database comes automatically in that new user with 10 tables and that is what I don't want to do.

How I can create 2 same name databases in different user accounts with different tables in both DBs?

Users on MySQL are sharing the same context in terms of databases. So if you are creating a database for one user, it will be also visible to user2.

You could solve this issue by prefixing your tables for each user, e.g. abc_item1 and setting correct privileges via grant for it -- so only one user is allowed to use this table.

Another way would be to just create different databases for each user -- which is I think the MySQL way of doing such things.


Need suggestion on Back up Strategy Our client has agreed to an RPO of one day data loss. So I am going to change back up strategy to eliminate space constraints.

Database is in full recovery mode.

Existing backup plan:-

Once daily we are taking a full back up of the database.

Thinking to change like below:-

Every week, on Sunday or Saturday we are going to take a full back up of the database.
And planning to take transaction backup or differential backup once every day on the database.

This will avoid space constraint for me.

If we take transaction log back up then will the RTO be larger, since we are taking daily only one transaction backup?

Anyways we need to perform transaction log backup to make free space in log files.

So can you people suggest which back up type will be good, either differential or transaction log?

If I am going in the wrong direction please suggest a backup strategy based on my RPO.

Thanks.

For clarification

RPO = Recovery Point Objective; the point in time that you will be restoring to in the case of data loss.
RTO = Recovery Time Objective; the duration it will take to restore to the time defined in RPO.

You might be able to answer your question yourself if I supply you with ample information and give you some ideas to ponder.

Recovery Point Objective (RPO)

Is the RPO defined by business really feasible? Will the business survive with an RPO of one day (or 24 hours)?

Recovery Time Objective (RTO)

Can you bring your database(s) back online to a point-in-time defined by the RPO? Please don't forget: The RPO is a duration. If your database crashed as 2pm in the afternoon then you have to be able to restore the data at least to 2pm in the afternoon the day before. (And not just to 6pm the night you took the backup).

Transaction Log Backup: Yes / No?

Seeing as you now know that RPO and RTO can be relevant for your backup concept, I can now answer your question about the database's Transaction Log. A differential and/or full backup of the database will not free the transaction log, if the database is in FULL recovery model.

Reference: Misconceptions around the log and log backups: how to convince yourself (SQLSkills.com, Paul Randall)

You must perform a BACKUP LOG .... to CHECKPOINT the Transaction Log and allow it to free up space should it be required.

Existing Backup Concept

If your RPO is for 24 hours data loss and the RTO is 4 hours (for one database system), how will you fair if you have to rebuild 10 systems or 20 systems? Will you be able to restore all 20 databases to a point-in-time with the given RPO of 24 hours and an RTO of 4 hours?

Possible solution

Perform a BACKUP DATABASE ... once a week (e.g. Sunday)
Perform a BACKUP DATABASE ... WITH DIFFERENTIAL... every other day, except e.g. Sunday
Perform a BACKUP LOG ... every other hour
Store data in a different location other that your main database(s), or even in an off-site location (geographical redundancy)

This can help ensure that you can restore to the RPO requested by your company's Business Continuity (BC) team and Information Technology Service Continuity (ITSC) team.

You will also have to somehow guarantee that the database backups (FULL, DIFF and TLOG) are stored in a location that will not be affected by a down-time/data-loss in your current location (e.g. separate data centre, off-site data centre).

When storing data off-site, ensure that you can still guarantee the RTO if you have to calculate in some additional time for data copy operations or restore over slower network connections.

References

Recovery point objective (Wikipedia)
Recovery time objective (Wikipedia)
The difference between RTO and RPO (Techadvisory.org)


Which query execution will be faster In the following queries, which query execution will be faster. When I tried executing them in SQLWorkbench, each time query execution time is varying.

Also, can you guys suggest some better tools for analyzing the query execution time?

Indexes are present on id's column in both the tables. The rows scan on both the tables is the same for these 2 queries.

SELECT DISTINCT sng_id FROM table1 WHERE id in (SELECT DISTINCT id from table2 WHERE LOWER(comment) LIKE '%home%')

SELECT DISTINCT sng_id FROM table1 AS a JOIN table2 AS b ON a.id = b.id WHERE LOWER(b.comment) LIKE '%home%'

In normal circumstances the JOIN is the structure that would be recommended for performance reasons. However, it looks like you're attempting some kind of search functionality.
Btree indexes don't work well with the kind of filtering you're implementing:

WHERE LOWER(b.comment) LIKE '%home%'

Using a function on the data (LOWER) will render any index in place useless as well as the wildcard filtering %string%. The query will likely run okay on small tables, but will suffer the longer it gets more rows that are scanned. This access type is eased with Fulltext indexing and relative matching rather than table scanning.

You might also try using a dedicated search product with inverted indexing like Elasticsearch, Solr or Sphinx.


Know when MySQL server was slow shutdown? I use MySQL 8.0.

How I can know when MySQL server was slow shutdown?

I start mysql server

/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my.cnf --user=mysql

I shutdown mysql server

/usr/local/mysql/bin/mysqladmin --defaults-file=/usr/local/mysql/my.cnf -pmypassword shutdown

Maybe this information is written in log?

Priviet Юрий

You question puzzled me a bit first. The answer to your question was too easy and this is dangerous...

About time - was, is and will be

How can I find out if the database "was" shutdown slowly? This is quite easy: Look into your MariaDB/MySQL Error Log and there you will find a log sequence similar to the following:

  2020-03-30T08:03:36.928017Z 0 [System] [MY-010910] [Server] /home/mysql/product/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.

Ups! There are no more "shutting down ..." messages like in MySQL 5.7:

  2020-03-30T08:04:49.898254Z 0 [Note] Giving 1 client threads a chance to die gracefully
  2020-03-30T08:04:49.898266Z 0 [Note] Shutting down slave threads
  2020-03-30T08:04:51.898389Z 0 [Note] Forcefully disconnecting 1 remaining clients
  2020-03-30T08:04:51.898433Z 0 [Warning] bin/mysqld: Forcing close of thread 115  user: 'enswitch'
  2020-03-30T08:04:51.898512Z 0 [Note] Event Scheduler: Purging the queue. 0 events
  2020-03-30T08:04:51.924644Z 0 [Note] Binlog end
  2020-03-30T08:04:51.938518Z 0 [Note] Shutting down plugin 'ngram'
  ...
  2020-03-30T08:04:53.296239Z 0 [Note] Shutting down plugin 'binlog'
  2020-03-30T08:04:53.296805Z 0 [Note] bin/mysqld: Shutdown complete

So you cannot find out, when shutdown started any more and thus you cannot say how long it took. So MySQL messed it up somehow in 8.0. Too much clean-up!

If you want to get the old behaviour you can stop MySQL 8 as follows:

  SQL> SET GLOBAL log_error_verbosity = 3;
  SQL> SHUTDOWN;

or just add the variable to your MySQL configuration file (my.cnf).

Then you will find the old shutdown sequence in your error log as before:

  2020-03-30T08:13:55.071627Z 9 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.19).
  2020-03-30T08:13:55.178119Z 0 [Note] [MY-010067] [Server] Giving 1 client threads a chance to die gracefully
  2020-03-30T08:13:55.178210Z 0 [Note] [MY-010117] [Server] Shutting down slave threads
  ...
  2020-03-30T08:13:56.588574Z 0 [System] [MY-010910] [Server] /home/mysql/product/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.

If you want to know where your MySQL Error Log File is located you can find it like this:

  SQL> SHOW GLOBAL VARIABLES LIKE 'log_error';
  +---------------+---------------------------------------------+
  | Variable_name | Value                                       |
  +---------------+---------------------------------------------+
  | log_error     | /home/mysql/database/mysql-80/log/error.log |
  +---------------+---------------------------------------------+

Typical locations are: /var/lib/mysql/.log or /var/log/mysqld.log or /var/log/mysql/mysqld.log or similar.

Now about the "is"

When you are currently shutting down MySQL it is already to late to find it out. Because you cannot connect to the database any more to change the settings and you do not see anything in the MySQL Error log. Possibly you can look at the error log with stat and you can see when the last message was written to it to find the start of the shutdown.

  shell> stat error.log
    File: error.log
    Size: 29929           Blocks: 64         IO Block: 4096   regular file
  Device: 801h/2049d      Inode: 5373953     Links: 1
  Access: (0640/-rw-r-----)  Uid: ( 1001/   mysql)   Gid: ( 1001/   mysql)
  Access: 2020-03-30 10:13:59.491446560 +0200
  Modify: 2020-03-30 10:13:56.587467485 +0200
  Change: 2020-03-30 10:13:56.587467485 +0200
   Birth: -

Symptoms for a working shutdown is either heavy writing to disk (iostat -xk 1) or heavy swapping in (vmstat). You have to wait until finished. Some brave people use a kill -9 in such a case, if they have InnoDB only and if they know exactly what they are doing and how much time a following crash recovery will take.

And finally about the question "how long will the shutdown take" (will be)

This is not so easy to predict. It depends on several things:

How many memory blocks of your database are swapped out.
How many pages are dirty and must be written to disk.
How fast is your I/O system (IOPS).

How much Swap must be swapped in you can find here: https://fromdual.com/mariadb-and-mysql-swap-analysis

The number of dirty pages (pages modified but not written to disk yet) you can find with:

  SQL> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

And about the IOPS you have to ask your hardware spec sheet.

I hope with this answer I have covered all your possibly questions about shutting down MySQL 8.0?


Sum duration for multiple records I have a table with fallowing columns:

id  device_id (string) used_at (datetime) app_id (string) duration (int)
1   device-1           2018-07-20         chrome          30
2   device-1           2018-07-19         chrome          1
3   device-2           2018-07-19         chrome          2
4   device-2           2018-07-20         facebook        100
5   device-1           2018-07-20         chrome          20
6   device-3           2018-07-20         chrome          10
7   device-1           2018-07-20         facebook        5

I need to get list of ten most used app_id for specified device-1 and used_at this list should have duration sum

So I should get for device-1 and uset_at >= 2018-07-18:

device-1 chrome duration (51)
device-1 facebook duration (5)

Anyone can suggest sql ?

I am not exactly sure what you are asking for in this post.

Here is what I came up with

CREATE TABLE device_table
(
    id INT PRIMARY KEY NOT NULL auto_increment
  , defice_id VARCHAR(20)
  , used_at DATETIME
  , app_id VARCHAR(30)
  , duration INT
);

INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-20', 'chrome', 30);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-19', 'chrome', 1);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-2', '2018-07-19', 'chrome', 2);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-2', '2018-07-20', 'facebook', 100);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-20', 'chrome', 20);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-3', '2018-07-20', 'chrome', 10);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-20', 'facebook', 5);

SELECT device_id
     , app_id
     , CONCAT('duration (', CAST(SUM(duration) AS CHAR), ')')
FROM device_table
-- remove filter to include everything else
WHERE device_id = 'device-1'
-- if you want top ten for "device-1" then comment out the date filter
AND   used_at >= '2018-07-18'
GROUP BY device_id
       , app_id
-- and uncomment the following line
--LIMIT 10
;

Could you explain your question a little more so the rest of us can understand what you are trying to accomplish here?

If you want the top 2 of any and all devices then use this:

SELECT device_id
     , app_id
     , CONCAT('duration (', CAST(sum(duration) AS CHAR), ')')
FROM device_table
GROUP BY device_id
       , app_id
ORDER BY duration DESC
LIMIT 2 -- change this to how many you want
;


Is it possible to set auto-increment-increment by 10 for only one table? Is it possible to add auto-increment-increment by 10 for only one table in mysql?

I have a testing db that has a lot of tables that do not abide by the auto-increment-increment of 10.  I won't want to mess up my testing rig.

The production server has auto-increment-increment of 10.

According to the MySQL documentation, you can't set custom auto-increment value for a specific table.

  It is not possible to restrict the effects of these two variables to a single table; these variables control the behavior of all AUTO_INCREMENT columns in all tables on the MySQL server.

However, this value has both global and session level effect. You may set it for session level and can perform required tests. And the changes will not persist after restarting your database server.

Reference:
auto_increment_increment


Is there an easy way to delete all records in multiple tables (but not every table)? So I "fixed" an issue the other day, which has caused a snowball of new issues (imagine that). I have a large group of tables that serve as Temp tables for many import/export processes that flow to other systems. Each table is labeled "TO_xxxx". I want to clear all records from each "TO_" table. Is there a way to structure a delete statement to clear all records from only "TO_" tables?

Is it as easy as

Delete from TO_%

or is there more to it? There's about 80 "TO_" tables total, so I figured I'd post here before manually going through everything. If you can help, I would appreciate it. Thanks.

System is SQL Server 2016.

Borrowing from this answer, which drops tables with a common prefix, you could use something like this

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DELETE FROM '
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id]
    WHERE t.name LIKE 'to[_]%';

PRINT @sql;
-- EXEC sp_executesql @sql;

The above example prints the delete commands.  When you are ready to actually run it, uncomment the EXEC


Getting the last data update time of a specific table in Oracle How can I get the last update time of table, i.e. at what time the last operation (insert/update/delete) was performed on a table in oracle?

I have tried using:

SELECT MAX(SCN_TO_TIMESTAMP(ORA_ROWSCN)) from mytablename;

But this gives me an ORA-08181 error ("specified number is not a valid system change number").

You should change the order of MAX and SCN_TO_TIMESTAMP

SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) from mytablename;

This might still fail if it has been long time since table has been modified, but should work for active tables.


How to break down RELATIONAL data into HIERARCHY data structure? I have a query that returns the following dataset:

RELATIONid MAPid  D1id   D2id  D3id
4999       4999   626    1250    7
5000       5000   626    1250    8

For the next step, I need to bind those datasets into treeview (hierarchy structure). I need to transform this dataset into the following:

Nodeid   ParentNodeid  Header
626       null           D1
1250      626            D2
7         1250           D3
8         1250           D3

How I can achieve those structure from the original dataset?

I have one more favor to ask, the data structure a (little bit) more complex than the previous one. Let's say I have sample dataset like this:

RELATIONid MAPid  D1id   D2id  D3id
4999       4999   626    1250    7
5000       5000   626    1250    8
5001       5001   627    1300    10
5002       5002   627    1300    12
5003       5003   628    1400    15

From the following dataset, we have 3 MainParent: 626, 627, 628 and the transformation (cross apply) output expectation will be like this:

Nodeid   ParentNodeid  Header
626       null           D1
1250      626            D2
7         1250           D3
8         1250           D3
627       null           D1
1300      627            D2
10        1300           D3
12        1300           D3
628       null           D1
1400      628            D2
15        1400           D3

Please note that the data are ordered per ParentNode followed by its node data.

A CROSS APPLY would seem perfect for the job:

SELECT
  v.Nodeid,
  v.ParentNodeid,
  v.Header
FROM
  dbo.atable
  CROSS APPLY
  (
    VALUES
    (D1id, NULL, 'D1'),
    (D2id, D1id, 'D2'),
    (D3id, D2id, 'D3')
  ) AS v (Nodeid, ParentNodeid, Header)
;

For every row of the source dataset, CROSS APPLY produces three, using the VALUES row constructor, explicitly specifying which column of the original set goes into which new column.

Now, the above will return duplicates if some pairs in your source repeat. You can suppress them with DISTINCT:

SELECT DISTINCT
  v.Nodeid,
  v.ParentNodeid,
  v.Header
...

In order for the transformed set to follow the order of D1id ASC, D2id ASC, D3id ASC, you could include those columns in the output and use them for sorting:

SELECT DISTINCT
  t.D1id,
  t.D2id,
  t.D3id,
  v.Nodeid,
  v.ParentNodeid,
  v.Header
FROM
  dbo.atable AS t
  CROSS APPLY
  (
    VALUES
    (t.D1id, NULL  , 'D1'),
    (t.D2id, t.D1id, 'D2'),
    (t.D3id, t.D2id, 'D3')
  ) AS v (Nodeid, ParentNodeid, Header)
ORDER BY
  t.D1id ASC,
  t.D2id ASC,
  t.D3id ASC
;

The reason you have to include them in SELECT is because, when you have DISTINCT, you may only sort by columns in the SELECT clause. Naturally, the result set will include the three extra columns as well. If you do not want them in the output, you can use the above as a derived table: your outer SELECT would pull only the three required columns and sort by the other three:

SELECT
  Nodeid,
  ParentNodeid,
  Header
FROM
(
  SELECT DISTINCT
    t.D1id,
    t.D2id,
    t.D3id,
    v.Nodeid,
    v.ParentNodeid,
    v.Header
  FROM
    dbo.atable AS t
    CROSS APPLY
    (
      VALUES
      (t.D1id, NULL  , 'D1'),
      (t.D2id, t.D1id, 'D2'),
      (t.D3id, t.D2id, 'D3')
    ) AS v (Nodeid, ParentNodeid, Header)
) AS s
ORDER BY
  D1id ASC,
  D2id ASC,
  D3id ASC
;

Alternatively, you could use GROUP BY instead of DISTINCT and thus return the sorted three-column set without a derived table:

SELECT
  v.Nodeid,
  v.ParentNodeid,
  v.Header
FROM
  dbo.atable AS t
  CROSS APPLY
  (
    VALUES
    (t.D1id, NULL  , 'D1'),
    (t.D2id, t.D1id, 'D2'),
    (t.D3id, t.D2id, 'D3')
  ) AS v (Nodeid, ParentNodeid, Header)
GROUP BY
  t.D1id,
  t.D2id,
  t.D3id,
  v.Nodeid,
  v.ParentNodeid,
  v.Header
ORDER BY
  t.D1id ASC,
  t.D2id ASC,
  t.D3id ASC
;


Best name to name a table The project I am currently working request me to name the tables to something like: df_cmn_aum_xx_typ_cur instead of something like courses.

Are there some known standards or best practices to recommend my bosses to abandon this convention?

Always name your table and column with meaningful words.  Abbreviations are subjective. It may save few bites but very difficult to comprehend.


What minimum permissions and/or roles must a SQL Server login possess to be able to grant database db_datareader role to other logins? To delegate the ability for a security admin to add new SQL logins to the db_datareader role within a database, what are the least permissions and/or roles the security admin must possess?

If this were a permission then you could use WITH GRANT OPTION, but it's not.

Assuming that you want to allow someone, or anyone in a particular Database-level Role, to perform this operation but not allow them to assign other Roles, then there is no permission or Role for that.

However, you can achieve this fine-grained "permission" via Module Signing. The minimum permission anyone needs is EXECUTE on a Stored Procedure that you write to do nothing more than add a Login or Role to that specific Database Role.

This is similar to what I outlined in the following answer:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

except in your particular case it is even easier since no server-level permissions are needed. Just do the following:

Create a Stored Procedure that has an input parameter for the Principal name, and simply adds that Database Principal to the db_datareader Database-level Role
Create a Certificate in that same DB
Create a User, in that same DB, from that Certificate
Add the Certificate-based User to the db_owner Database-level Role
Sign the Stored Procedure (from Step 1) with that Certificate
Grant any User who should be able to do this EXECUTE permission on that Stored Procedure

For the sake of completeness:

Technically speaking, you could add EXECUTE AS 'dbo' to the CREATE PROCEDURE statement, but that uses Impersonation which has issues of its own, and is less secure than the Module Signing approach I described above.


SSIS while upgrading SQL server I want to upgrade SQL server from 2005 to 2012 (I'm thinking of in-place upgrade).
Today I have several SSIS packages that works with other servers.
The SQL version in those servers is mainly 2005.

My question is what happen to the SSIS when i'm upgrading?

Do i need to upgrade the SSIS as well?

Could the SSIS work without me upgrading it?

Are there any compatibility issues with the integration server? can 2012 work with 2005 (and the opposite - because replication have compatibility problems)

I'm not a big fan of the SSIS so my goal is to touch it as less as i can so it will keep on working.

Unfortunately you need to migrate your 2005 packages to 2012 using Data Tools. Depending on the complexity of each one this task will be more or less complicated. Here some useful links that I have used in the past:

https://devinknightsql.com/2012/05/16/upgrading-packages-to-ssis-2012/

https://sqldusty.com/2012/03/19/upgrade-2008-ssis-packages-to-2012-like-a-boss/

https://blogs.msdn.microsoft.com/mattm/2008/03/22/package-upgrade-wizard/


Where does mysql write temporary tables? MySQL 5.1.x | Windows Server 2008

Data Directory is on a different partition.

I have an innodb database (innodb_file_per_table is set) that I'm trying to optimize and I don't appear to have enough space.  When I run the optimize command, I see:

foodb.foo_table
note      : Table does not support optimize, doing recreate + analyze instead
error     : The table '#sql-123_foo' is full
status    : Operation failed

My understanding is that when I run OPTIMIZE TABLE, it's essentially running ALTER TABLE behind the scenes and that usually needs to make a copy of the table.  My question is, where is the table being created?

From the MySQL manual for 5.1:

..
On Windows, Netware and OS2, MySQL checks in order the values of the TMPDIR, TEMP, and TMP environment variables. For the first one found to be set, MySQL uses it and does not check those remaining. If none of TMPDIR, TEMP, or TMP are set, MySQL uses the Windows system default, which is usually C:\windows\temp.
..
ALTER TABLE creates a temporary copy of the original table in the same directory as the original table.

In newer MySQL versions it gets a bit more complicated to explain because there is also an in-place fast ALTER TABLE, but for 5.1 the answer to your question is simply "in the table's directory".


How do transactions help resilience to system failure? I understand transactions were developed to meet two requirements:

Concurrent DB access
Resilience to system failures

I understand how the concurrency issue is motivated; to overcome the database consistency problem.

However, I have a question:  How do transactions help system resilience to failures?

If I understand your question, the "resilience to system failure" is handled by the server to ensure that the transaction either:

Succeeds in committing the transaction.

Or:

Ensures that a non-committed transaction rolls back to the previous state.

In other words, the transaction completes successfully or else (in a failure) all the in-flight changes are undone since the transaction is incomplete.


How can I optimize this query over this logging table? I am trying to optimize a table layout for a table that logs events.

The log table contains three relevant columns: Timestamp, ItemId, LocationId
Each row means that at a given time, a certain item has been seen at a certain location.

2017-01-01 10:00    Item A has been seen at location 1
2017-01-01 10:01    Item A has been seen at location 1
2017-01-01 11:00    Item B has been seen at location 1
2017-01-01 11:01    Item B has been seen at location 2
2017-01-01 11:02    Item A has been seen at location 2
2017-01-01 11:03    Item B has been seen at location 1

There are about 100 different locations, 20.000 new items per day, a million events per day, and 14 days of logs.

Now I need to run queries on this data, such as:

Which items were at location '1' at time '2017-01-01 11:00'
(= which items have been seen at location 1 before time '2017-01-01 11:00', and were not seen elsewhere after being seen at 1 but before '2017-01-01 11:00'

To get this data I can execute

SELECT DISTINCT  ItemId
FROM events e1
WHERE LocationId = 1
  AND e1.TimeStamp < '2017-01-01 11:00'
  AND NOT EXISTS (SELECT 1 FROM events e2
                  WHERE e2.LocationId <> e1.LocationId
                    AND e2.ItemId = e1.ItemId
                    AND e2.TimeStamp >= e1.TimeStamp
                    AND e2.TimeStamp <'2017-01-01 11:00')

Currently, this query takes about 15 seconds, when there is zero load on the database. The goal is to execute this query in less that 100ms, with heavy load. I don't think this is possible with the current design.

I have an index on item and location, and a clustered index on timestamp

Is there a table layout that would allow me to perform this query more efficiently?

Or is there a query that would work with the existing table?

You could try a different query:

SELECT ItemID
  FROM (SELECT ItemID
              ,ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY TimeStamp DESC) rn
              ,LocationId
          FROM events
         WHERE TimeStamp < '2017-01-01 11:00'
       ) e1
  WHERE LocationId = 1
    AND rn = 1
;

No promises that this will do any better (it could actually be worse); it's just a different approach.

Also - if it makes sense, you may want to put a lower bound on the possible TimeStamp values; if you can ignore everything more than 12 hours prior to the time you're looking for, that could eliminate a large number of rows.


Incompatibility between unixODBC and Firebird ODBC driver I'm currently developing a application based in Rcode, ODBC and Firebird.

For that, first I've installed firebird server and unixODBC (both 64 bits since my machine OS is 64-bits). I tried with 32-bits software aswell since Ubuntu multiarch allow it, but that caused another problem (doesnt matter in this subject).

Created database, using firebird isql function (/opt/firebird/bin/isql), and made some fake tables with integer, float, text columns.

I can connect to it using unixODBC isql function (after properly programming DSN and Driver in odbc.ini / odbcinst.ini), but theres a problem:

I can't select tables containing text columns, it gives error std::bad_alloc

After some research, I've discovered slencheck function from unixODBC, using it on my driver returns

slencheck: sizeof(SQLLEN) == 4
slencheck: driver manager and driver differ!!!

How can I modify unixODBC SQLLEN so it became compatible with 64-bits Firebird ODBC driver?

I can't select text columns because of SQLLEN or there's more to this?
I think this odbc's github issue is related to mine

I came to a solution, if anyone is interested:

For any reason, firebird ODBC 64-bits driver downloaded from oficial site comes with SQLLEN = 4 bytes. After some help, I managed to compile driver from source code, and the result seems to work with 8 bytes SQLLEN.

In the compile proccess, I had a BUILD_DRIVER_VERSION error, modifying line 139 (SetupAttributes.h), adding spaces between each number fixed it.


Based on the question at this time, I assume you have a list of dates and times, and you want to assign a sequential number to them, with the same number assigned to all values where the previous value is no more than 10 minutes less than the current value, as shown in the desired results.

Let's assume your table of date time values is myDtTm, and the column name is theDateTime

SELECT theDateTime
      ,SUM(TimeDiff) OVER (ORDER BY theDateTime)
  FROM (
        SELECT
               theDateTime
              ,CASE WHEN COALESCE(DATEDIFF( minute
                                           ,LAG(theDateTime, 1)
                                             OVER (ORDER BY theDateTime)
                                           ,theDateTime
                                          ), 100000) > 10 --< this is the max time diff
                 THEN 1
                 ELSE 0
               END as TimeDiff
          FROM myDtTm
       ) sq
 ORDER BY theDateTime
;

This dbfiddle.uk link shows this in action, providing precisely the results shown in the question.

Explanation:

In the sub-query, we use the window function LAG to compare each date and time to the one before it, and figure out how many minutes' difference there is. We assign 1 to TimeDiff for the current date if the difference more than the target (ten minutes), and 0 otherwise. (Note: we get back NULL for the first row, because there is no previous row at that point; if you have NULL datetime values in your data, you may need to change this).

Then, we simply do a rolling total (SUM of TimeDiff over our datetime value gives us a total of TimeDiff for this row and all previous rows).

Note: I took the idea from this DBA.SE question; the differences were notable enough that I don't think this is a duplicate.


I need help migrating data through databases I have a web page with some data about the users. Recently, I made a complete change and start developing a new portal and of course a new DB structure. My question : What is the best way to migrate my data from my old DB to my new DB ? My old DB, and my new DB have similar but different structures, both are in SQL SERVER 2008 R2. Thanks.

It depends on how different the various tables are.  Without knowing how different the tables are between the databases, I would probably expect a combination of scripts and data compare-import tools such as Red Gate's Data Compare tool.

Using Red Gate's Data Compare, you can work around minor differences in table structures.

For splitting tables or significant changes within a table, you may be better off writing an import script from a local copy of your old database or a live connection to the old database.


Get total size for all Postgres or Greenplum tables including partitions Is there an easy way to get the total size of all tables including partitions? I've written this query for Greenplum, but it's a bit round-a-bout:

with part_sz as (
  select ts.sotdschemaname sch
       , coalesce(ps.sopaidparenttablename,ts.sotdtablename) tbl
       , coalesce(ps.sopaidpartitiontablesize,ts.sotdsize) part_size
  from gp_toolkit.gp_size_of_table_disk ts
  left join gp_toolkit.gp_size_of_partition_and_indexes_disk ps
  on ts.sotdschemaname = ps.sopaidpartitionschemaname
  and ts.sotdtablename = ps.sopaidpartitiontablename
)
, tbl_sz as (
  select sch, tbl, sum(part_size) total_size
  from part_sz
  group by sch, tbl
)
select sch, tbl, total_size
from tbl_sz
order by total_size desc

Greenplum is built on Postgres 8.2, so something using that feature set would be ideal, but feel free to post anything using newer Postgres versions for the benefit of other readers.

Here is what i have, its also a little bit roundabout and not testet in PG8.2
(not supported anymore ;) ) but it shows your schema.tablename,PrettySize (first Select) and  partitioned Tables are grouped to one (ParentTablename) (second Select). its maybe not the best solution (Tuning welcome)

--get all tables sizes and group partitioned tables by PartenTablename
SELECT nspname || '.' || relname AS "relation",
       pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
LEFT OUTER JOIN pg_inherits I ON (C.relfilenode = I.inhrelid OR C.relfilenode = I.inhparent)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    AND I.inhrelid is null
UNION ALL
select nspname || '.' || relname AS "relation",
       pg_size_pretty(sum(pg_relation_size(inhrelid))) AS "total_size"
FROM pg_inherits  I
LEFT JOIN pg_class C ON (C.oid = I.inhparent)
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
GROUP BY  nspname,relname
ORDER BY 1,2


data compression does not show when scripting index on ssms when I right click on SSMS and choose to script the create index:

I get this script, without the data_compression:

USE [APCore]
GO

/****** Object:  Index [i_agencyId]    Script Date: 07/11/2019 12:18:13 ******/
CREATE UNIQUE NONCLUSTERED INDEX [i_agencyId] ON [agy].[agency]
(
    [agencyId] ASC
)
INCLUDE (   [countryCode]) WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [NONCLUSTERED_INDEXES]
GO

But when specifically checking for the data compression using teh query below

use APCore
go

SELECT i.name, p.data_compression_desc
 FROM sys.partitions P
 INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id
 WHERE P.data_compression > 0 and I.name = 'i_agencyId';

I get the correct information.

I am using this SSMS :

Is there a way to make ssms include data compression on the index definition when scripting?

Make sure your scripting options are set correctly for Data Compression


Updating column having index causing Deadlock My update statement:

Update XyzTable set state ='second' where pgmNumber = 'prog123' and state = 'first'

Both state and pgmNumber columns has individual index.

Deadlock is happening even if above query running concurrently  with different pgmNumber.

There is single row for each pgmNumber

What would be the best way to resolve the issue

What would be the best way to resolve the issue

You should create a compound index (unique if appropriate) on (pgmNumber, state). The index only on state is useless because of the low cardinality of that domain.

Generally only one index can be used for executing statements like yours, and in your scenario: "Both state and pgmNumber columns has individual index" it causes locks on more rows than necessary, thus leading to deadlocks.


Find active values of SQLNET.ORA parameters on server How can I find the active values of SQLNET.ORA parameters on the server?

I know how to find the SQLNET.ORA file but there's no guarantee the values in that file are actively used.  Parameter changes may require restarting the listener and sometimes even restarting the database.  For debugging I want a way to be 100% certain how the parameters are set, without restarting anything.

This is similar to this question, but I am looking for any method to find the current values.  It doesn't have to be a SQL query.

Here's what I've tried so far that leads me to believe it's impossible:

Tracing does not generate that data.
There does not appear to be anything in the data dictionary.
There's no information in the manual or support.oracle.com.
Offered a bounty but no takers. (But I'll offer and award another bounty if anyone can answer this.)

Parameters set in the sqlnet.ora file are not exposed in any data dictionary table.  You can't write a SQL query to find them.

Source:
https://community.oracle.com/thread/2550107?start=0&tstart=0


SSMS 2014 does not launch Graphical Execution Plan from clicking hyper-linked query plan - ShowPlanXML I am working with an older version of Microsoft SQL Server Management Studio 2014. I run the following query:

SELECT TOP 10 Q.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan( qs.plan_handle ) Q
WHERE Q.dbid = DB_ID( 'Northwind' )

When I click on one of the hyperlinked query_plans (ShowPlanXML=...), SSMS 2014 opens the link in a new text window in XML format. As I understand it, SSMS is supposed to open the link as a graphical execution plan.

Is there any way to configure SSMS to automatically open the ShowPlanXML link as a graphical execution plan without third party software and without updating the version I have of SSMS 2014?

You need to ensure the System Registry contains the correct file-extension-mapping for .sqlplan files.

Open the Registry Editor, navigate to the following key:

Computer\HKEY_CLASSES_ROOT\.SQLPlan

Confirm the (Default) value contains something like:

ssms.SQLPlan.14.0

(the version number might be different for SSMS 2014).

This is a screenshot of Registry Editor showing the key and value:

You'll also need to ensure the following value is set correctly:

When I say "correctly", I mean appropriately for your system.  If this approach doesn't work, I'd try simply re-installing SSMS 2014.  Of course, I'd actually recommend installing the latest version of SSMS, v18, which is available for free directly from Microsoft.


How to resolve red invalid icon for Informatica mapping I'm having a difficult time resolving a red "invalid" icon within Informatica.  I've done all possible scenarios for checking in / checking out this object (mapping).  It currently does not have any dependencies.  How can I fix this?

This was due to an incomplete mapping because my expression transformation didn't have output ports (only input ports) just before the target definition.


No Maintenance Plans Folder in SQL Studio 2008 R2 A client has SQL Server 2008 R2 installed on a Windows 2003 R2 32-bit server.

Under Management in SQL Studio 2008 R2, there is no maintenance plans folder. It is not under Management --> Legacy either, as it would be if there were legacy-mode databases on the server. I am certain this is SQL Server 2008 R2 and not Express. I have checked the version and verified the splash screen when studio starts are not Express. I am logging in as the SQL account sa, and the account does have the sysadmin server role.

Lastly, right clicking on a database in SSMS and navigating to "Tasks" does not show any Maintenance Plan option.

Clearly, I'm missing something, and Google is coming up empty considering the scenario I've outlined. Any ideas? Thanks.

EDIT: select @@version returns "Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)   Apr 22 2011 11:57:00   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services on Windows NT 5.2  (Build 3790: Service Pack 2)"

Express Edition (the free version of SQL Server) doesn't have maintenance plans because it has no SQL Server Agent.  Agent is the constantly-running-scheduler that kicks off jobs like maintenance plans.


How unique are MongoDB _id fields? I am trying to create a schema for documents and collections for a MongoDB database I am creating. I have read a few documents describing the _id field that is generated by each document upon creation.

I have also read this article about implementing One-to-N relationships in Mongo and have decided that my case lies in the "One-to-Many" category. As a result I am going to reference multiple other documents in multiple other collections by their _id field from my "current" collection. From what I can tell in the article this seems that it will work just fine. Though I faintly remember reading an article saying that _id fields are not guaranteed to be unique over different collections, but they are within collections (forgive me, I cannot find a link to it).

So for example say I have three collections for a company that sells Apples and Oranges; Orders, Apples, and Oranges. Orders holds documents about orders for Apples and Oranges. Apples and Oranges holds data about various types of those fruits.

In a Orders documents I might have the fields

{
    ...
    "OrangesOrdered" :
    [
        { "Amount" : 2, "OrangeID" : "OrangeObjectID12"}
    ]
    ...
    "ApplesOrdered" :
    [
        { "Amount" : 4, "AppleID" : "AppleObjectID65"}
    ]
    ...
}

Where OrangeObjectID12 is the _id field of a specific type of Orange and AppleObjectID65 is the _id field of a specific type of Apple.

Is it possible that these two _id could ever be the same?

Strictly speaking, _id fields are as unique as you make them.  As you have discovered, the default values for _id are ObjectIDs but you can populate the field with any data you wish.

Therefore, for example, you could use a UUID as I have done in this sharding example and then the chances of a collision across collections would essentially be whatever the collision chance is of your generation method and content. A crude version of this _id override can also be seen in this gist (something I used for testing GUIDs some time ago)

In terms of ObjectIDs themselves, the topic has been covered pretty well already (here and here for example) and can be inferred from the documentation you linked.  Basically, there is a chance of collision, particularly for ObjectIDs generated by the same host within the same second at a high volume but for most use cases that is not a concern.

Finally, with respect to any database level guarantee of uniqueness across collections, there essentially isn't one.  Uniqueness is enforced by the index, and there is no option for an index to span multiple collections, hence uniqueness is only guaranteed within a given collection.


Oracle: calling udf that modifies the data. How to do it right? Say I have a function :

create or replace
function test_function return integer
as PRAGMA AUTONOMOUS_TRANSACTION;
t_test integer :=1;
begin
 update test1 set id = id+1 ; // table test1 has, for simplicity sake, just 1 column,id
 commit;
 return (t_test);
end;

There are at least two ways I can execute it (so function successfully updates test1) :
1. SELECT test_function FROM dual;
2. SELECT 1 FROM dual WHERE test_fucntion =1;

Which one is the right ?

Also, I can do
3. SELECT 1 FROM dual
   INNER JOIN (SELECT test_function FROM dual)a ON (1=1)
which doesn't show an error, but also doesn't update the table.

I'm really curious if it's intended behavior; I don't see why update fails...
I'm using Oracle 10 if it matters.

Thanks.

In #1 and #2, something uses the output of test_function, but in #3 nothing uses it. The optimizer knows this and does not call test_function. Since test_function isn't called, the side effect of updating the table does not happen.

This is definitely intended behavior.

I rewrote your #3 to the following, and test_function got called (the UPDATE happened). The reason is that the result of test_function was needed for output.

SELECT 1, tf FROM dual
INNER JOIN (SELECT test_function as tf FROM dual)a ON (1=1)
;

However, generally speaking relying on the side effect of a function to do real work from inside a SQL query is not a good idea. The optimizer is always looking for a reason to do as little work as possible, and you might be surprised sometimes to learn that the optimizer sees the necessity of your function differently than you do.


Trigger actions on database creation or attach On our dev environment we need to run a bunch of actions when some databases are created or attached. Actions like cleaning up some tables, reseeding some others, changing emails, etc. I was trying to use DDL trigers and actually found here a question similar to mine. Starting from that code I'm trying a slightly different action but can't manage to even get started.

The following code, taken from the question I mentioned, I just added a condition to check db name and if corresponds to the one I need, then do some action. Problem is that is no running the SET statement inside the IF BEGIN..END. The last SELECT using fn_listextendedproperty doesn't return any data.

IF EXISTS (SELECT NULL FROM sys.server_triggers WHERE name = 'ddl_trig_database')
BEGIN
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
END
GO

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
    DECLARE
        @DatabaseName NVARCHAR(128)
        , @CreatedBy NVARCHAR(128)
        , @CreatedDate NVARCHAR(23)
        , @SQL NVARCHAR(4000);

    SELECT  @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)');

    IF @DatabaseName = N'asd'
    BEGIN
        SET @SQL = '
            USE ' + @DatabaseName + ';
            EXEC sys.sp_addextendedproperty @name = N''Owner'', @value = N''' + @CreatedBy + ''';
            EXEC sys.sp_addextendedproperty @name = N''StartDate'', @value = N''' + @CreatedDate + ''';';
        EXEC (@SQL);
    END;
GO

CREATE DATABASE asd;
GO

SELECT name, value
FROM asd.sys.fn_listextendedproperty(default, default, default, default, default, default, default)

DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

DROP DATABASE asd;
GO

You didn't copy the full example and are concatenating NULL into the SQL command string which causes it to be NULL.  If you do this:

IF EXISTS (SELECT NULL FROM sys.server_triggers WHERE name = 'ddl_trig_database')
BEGIN
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
END
GO

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
    DECLARE
        @DatabaseName NVARCHAR(128)
        , @CreatedBy NVARCHAR(128)
        , @CreatedDate NVARCHAR(23)
        , @SQL NVARCHAR(4000);

    SELECT
        @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)')
        , @CreatedBy = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(128)')
        , @CreatedDate = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','NVARCHAR(23)');

    IF @DatabaseName = 'MyTestDatabase'
    BEGIN

    SET @SQL = '
        USE ' + @DatabaseName + ';
        EXEC sys.sp_addextendedproperty @name = N''Owner'', @value = N''' + @CreatedBy + ''';
        EXEC sys.sp_addextendedproperty @name = N''StartDate'', @value = N''' + @CreatedDate + ''';';

    -- PRINT @SQL;
    EXEC (@SQL);
    END

GO

CREATE DATABASE MyTestDatabase;
GO

SELECT name, value
FROM MyTestDatabase.sys.fn_listextendedproperty(default, default, default, default, default, default, default)

DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

DROP DATABASE MyTestDatabase;
GO

It works correctly on my system.


Empty SQL Server 2000 LDF File I have a 1 GB .LDF for a test database which I want to shrink right down. I am happy to delete its contents if needed.

How can I do this for SQL Server 2000?

I have tried the following to no huge avail:

BACKUP LOG Database_TEST with truncate_only
DBCC SHRINKFILE (Database_TEST_Log, 1000)

The second argument to DBCC SHRINKFILE is target size, in MEGABYTES. So to paraphrase your code into English, you are saying:

  "SQL Server, please take this 1 GB file, and shrink it to 1000 MB.

  P.S. 1000 MB = 1 GB. So, don't do anything."

You should try a smaller target size, e.g.:

DBCC SHRINKFILE (Database_TEST_Log, 1);

If the file doesn't seem to shrink, run the commands again (or issue a CHECKPOINT and run the shrink command again, if the recovery model is SIMPLE).

If the file still doesn't seem to shrink, it is probably because there is a long-running transaction. You can find out what this is using the following command against this database:

DBCC OPENTRAN;

Of course, when you do things like BACKUP WITH TRUNCATE_ONLY I hope you don't care about this database or want to be able to recover to a point in time prior to this operation, or at least that once you shrink this log file you will resume proper log management and backup procedures. Also, if you shrink the file to a minimum size, the next time a user causes log to be generated, they'll have to wait for file to grow. This is especially true for log files, which can't benefit from instant file initialization like data files can, and can be particularly annoying when the autogrow size is inappropriate and/or the log is not on a fast disk (like SSD).

Please have a good read of this question and its answers:

Why Does the Transaction Log Keep Growing or Run Out of Space?


Normal form for a relation where the number of tuples having the same value in a column is the same for all the possible values in that column Is there a normal form for a relation that would permit the following table :

id | alpha
----------
1  | a
1  | b
1  | c
2  | g
2  | u
2  | k
.
.
.
n  | x
n  | y
n  | z

But not this table :

id | alpha
----------
1  | a
1  | b
2  | g
2  | u
2  | k
.
.
.
n  | x
n  | y

How would you populate such a relation? If you required 3 records per id the relation could not exist, as when it is first created this criteria will not be met (until the relation is populated with records).

I think this would have less to do with normal form and more to do with database constraints. Even then, I think it would only allow a MAX of n records with the same id, but not a minimum. Resulting in 0-n records per id.

What problem are you solving? Perhaps some fresh eyes could guide you toward a new approach.


How to properly secure MySQL database? We have a web application based on the famous triad apache + php + mysql which we sell to our customers and gets installed on their servers.

Currently, we are using MySQL 5.1.41 which has only a single user registered, root, with is password and access allowed only from localhost.

The problem is, if someone creates its own mysql installation, and then copies our database from its original location to that independent installation, s/he would be able to access its content.

Is there a way to prevent also this kind of improper access to our webapp database? Can it be encorder MySQL-side or must it be something in our own application?

I normally do not endorse any one specific product, but in this case I will make an exception.
I have personally evaluated a product called Gazzang. It can encrypt data in such a way that when transported to another server, the data is unreadable unless the encryption key is copied and verified on that external server. I have tried this and copied /var/lib/mysql on another server that did not have the encryption key. The only SQL command that worked with the database encrypted is SHOW DATABASES;. Nothing else worked.
There are some hoops to jump through to get an entire /var/lib/mysql folder encrypted. Once done, you never have to worry about data encryption. Perhaps you could start like this:

Installation MySQL on a DB Server
Apply Gazzang's ezncrypt program against the empty /var/lib/mysql
Load a mysqldump into it

The beautiful part of my evaluation was that the MySQL data was fully accessible by standard DB Connections. Gazzang does not shield normal access protocols. You must take up responsibility for securing passwords:

June 6, 2012 : Limiting database security
Feb 17, 2012 : MySQL : Why are there "test" entries in mysql.db?
Feb 17, 2012 : What is the mysql.db table used for?
Jan 19, 2012 : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
Sep 28, 2011 : Weird MySQL Users Been Created (eg. bug115166_10073) and not by me
Sep 11, 2011 : Is this a normal set of MySQL privileges?

EPILOGUE
As for the physical data itself, this worked during the 30-day evaluation. Please evaluate it for yourself. I will leave it to you to investigate production case studies on Gazzang.
UPDATE 2015-09-29 12:26 EDT
Cloudera purchased and deprecated Gazzang.
Cloudera Data Encryption is still available.


PostgreSQL "size of temporary files" I've imported data into a new database (about 600m rows of timestamp, integer, double).
I then created some indexes and tried to alter some columns (got some out of space issues), the database is vacuumed.

Now pgAdmin III tells me that the "Size of temporary files" is 50G~+.

What are these temporary files? are these like SQL Server transaction log?
How can I get rid of them, it seems the database is much bigger than it should (the total size of the database is 91 GB)

Using Posgres 9.4.1 on a Windows 2012 server.

A screenshot of the database statistics tab:

I found nothing in the pgAdmin documentation, but the source code reveals the query behind these entries (added for Postgres 9.2+):
It boils down to:
SELECT temp_files AS "Temporary files"
     , temp_bytes AS "Size of temporary files"
FROM   pg_stat_database db;

And the Postgres manual has details for pg_stat_database:

tmp_files bigint  Number of temporary files created by queries in
this database. All temporary files are counted, regardless of why the
temporary file was created (e.g., sorting or hashing), and regardless
of the log_temp_files setting.
temp_bytes bigint     Total amount of data written to temporary files
by queries in this database. All temporary files are counted,
regardless of why the temporary file was created, and regardless of
the log_temp_files setting.

Note that these values do not contribute to the size of your database. But they indicate that your setting for work_mem may be too low, so that many sort operations spill to disk (which is very slow as compared to just RAM).
Related:

Optimize simple query using ORDER BY date and text

To actually compact the size of your database:

VACUUM returning disk space to operating system

To measure size:

Measure the size of a PostgreSQL table row

Aside: WAL (Write Ahead Log) would be equivalent in Postgres for the transaction log in SQL Server. Nice explanation in this related answer on SO:

Why do SQL databases use a write-ahead log over a command log?


PostgreSQL: changing password for a user is not working I installed PostgreSQL on EC2 machine and now I want to change the password of user postgres
I do

$ sudo -u postgres psql
psql (9.1.5)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'newpasswd';
ALTER ROLE

Then I exit the shell and try to login with new password

$ psql -U postgres -W
Password for user postgres:
psql: FATAL:  Peer authentication failed for user "postgres"

My PostgreSQL version is

$ psql --version
psql (PostgreSQL) 9.1.5
contains support for command-line editing

What is that I am doing wrong?

Thank you

UPDATE
I made changes in pg_hba.conf and this is how it looks now

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Then I restarted the postgres

$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.1 database server                                                                                [ OK ]

I tried logging in again, but failed

$ psql -U postgres -W
Password for user postgres:
psql: FATAL:  Peer authentication failed for user "postgres"

Like willglynn said, it's probably your pg_hba.conf file.

If you have the following line:

local   all    all     peer

then change it to:

local   all    all     md5

That should then let you login with your new password (assuming that you correctly supply it) :)


Is there a way to determine what kind of data is sitting in the Redo Queue for an AlwaysOn AG Secondary Replica? Do any of the DMVs or is there another way to expose what kind of data is in the Redo Queue that's in process of being synced up to a secondary replica? (E.g. is it table data and which table, or is it index changes and which indexes, etc?)

The short answer is no, there is not a DMV that contains this information.  It would be a significant amount of overhead for SQL Server to track what table was associated with each log record that had been received but not committed.

As you can read in Reading the transaction log in SQL Server – from hacks to solutions, you can read the log records with a function, but it's certainly not something that you would want to do in production just to have some interesting information.  Doing something like this on every log record in real time would add a tremendous amount of I/O load on a busy server, and you wouldn't want it to be done unless it solved a significant problem.

Although the type of information you're looking for would be interesting, you might get a better answer if you can state why you want to see this information--there may be a better solution.


Are these extra Index fields needed? I've designed a table as below for users who register a quiz:
CREATE TABLE `registered_quiz` (
 `rq_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` smallint(5) unsigned NOT NULL,
 `iq_id` smallint(5) unsigned NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `is_registered` char(0) DEFAULT NULL,
 `delivered` char(0) DEFAULT NULL,
 `final_point` float NOT NULL DEFAULT '0',
 `total_rank` smallint(5) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`rq_id`),
 UNIQUE KEY `user_id_2` (`user_id`,`iq_id`),
 KEY `user_id` (`user_id`),
 KEY `iq_id` (`iq_id`),
 KEY `date` (`date`),
 CONSTRAINT `registered_quiz_ibfk_1` FOREIGN KEY (`iq_id`) REFERENCES `inja_quiz` (`iq_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `registered_quiz_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `karbar` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

And I have a query as below:
SELECT final_point,total_rank,rq_id FROM registered_quiz WHERE user_id=1111 AND final_point<>0 AND total_rank<>0

Do I need the part  KEY user_id (user_id) ?
I was wondering if  UNIQUE KEY `user_id_2` (`user_id`,`iq_id`) would be enough!

Do I have to index final_point and total_rank to get faster select results just because they are in the where clause?

Provided you have an index on user_id (or a compound index with user_id as the first column) and a user does not have to many rows, you don't have to index final_point and total_rank.


How does performance of SQL Server CE and SQL Server Express compare? We have an application that runs on netbooks. It currently uses a SQL Server CE 3.5 database, and we are thinking of upgrading to SQL Server Express.

Just wonder if there are any benchmarks to compare the performance of SQL Server CE and SQL Server Express? Would it be too intensive for an atom netbook?

We are using SQL Server CE 3.5 and we are thinking of moving to SQL Server Express 2012.

UPDATE:
Why are we wanting to upgrade? Because we will now be using merge replication with the SQL Server Express database.

Also we don't want to limit ourselves. We might use stored procedures and triggers on the database because with the replication solution we will be redesigning parts of our system.

For your case and reasons to upgrade

merge replication is available in SQL Server CE 3.5

Stored procedures, views, triggers is not available in SQL Server CE 3.5

for a basic comparison between the 2 editions in your case working on a net book

1.
SQL Server CE 3.5 can Runs as in-process with application
SQL Server Express runs as a service

2.
SQL CE supports maximum size of 4GB
SQL Express can go up to 10GB

3.
SQL CE Doesn't Support FILESTREAM
SQL Express Supports FILESTREAM

Procedural T-SQL - Select Case, If, features is not supported in SQL CE
Procedural T-SQL - Select Case, If, features is supported in SQL Express

5.
Distributed transactions is not supported in SQL CE but supported in SQL express

Native XML, XQuery/XPath is not supported in SQL CE but supported in SQL express

7.
Stored procedures, views, triggers is not supported in SQL CE but supported in SQL express

Role-based security is not supported in SQL CE but supported in SQL express

9.
concurrent connections supported in SQL CE is limited to 100 but unlimited in sql express

this is a useful blog focused on sql CE

for the comparison between the 2 in your specific environment you can use these tools

SQL LOAD TEST
SQL STRESS TEST TOOL

and check this post on SO about sql statment performance
https://stackoverflow.com/questions/1555471/what-tools-are-available-to-test-sql-statement-performance


replace user defined functions (UDF) and tabled value functions(TVF) Are there techniques to replace

UDF
tabled value functions
with something else?

or techniques to make them acceptable? (fast, multithreading and with good estimations)

No easy way unless you are on SQL2017 for table valued functions and not UDFs.
However on SQL 2019 scalar UDFs May perform little better but there are still lots of catches involved
By default parallelism is inhibited while using the UDFs within code, mind you the function part itself can go parallel though.
Your best part if not on latest sql server version would be to inline the function. That is simply copying the work of function inside your code rather than calling function from the code.


Capturing SQL calls sent to a remote server from application First of all, I'm not well versed in SQL anything at all. Closest I've ever needed to get was storing and retrieving data from a local SQLite db.
In essence I think I have a simple problem but it's hard to orient yourself when everything is new.
My main tool at work is an ERP software, which is basically a front end to an SQL db.
Problem I have with it is that it's very clumsy and doesn't allow automation of even the most basic tasks.
What I want to do, is bypass the front-end completely and interact directly with the db to automate most of my tasks with python.
I can connect to the database just fine from python environment, but the schema is gigantic, there's no way I'll be able to find whatever it is I might be looking for.
So I need to capture the call front-end sends when I click a button (telling it to display specific set of data) to use that call as a guide.
Basically, how can I, an SQL noob, capture calls that a desktop application sends to a remote server?
Edit 1: My job is mostly analytical, so all of my automation will be for retrieval, analysis and visualization. I'm not very likely to mess anything up.
Edit 2: Tried running a Profiler and got the message:
"In order to run a trace against SQL Server you must be a member of sysadmin fixed server role or have the ALTER TRACE permission."
I'm not a sysadmin, don't have an alter trace permission and reeeally don't feel like asking for it :D

Depending on your SQL version, you could run a Query Trace or an Extended Event to capture the TSQL being run by your app.


Create a certificate with a private key Reading this answer I have a question about it:

How can I create a certificate with a private key in my app database?

CREATE CERTIFICATE, see the first example Creating a self-signed certificate:

use <mydb>;
go

CREATE CERTIFICATE Shipping04
   ENCRYPTION BY PASSWORD = 'mysecretpassword'
   WITH SUBJECT = 'Used to signed procedure usp_foo',
   EXPIRY_DATE = '20181231';
GO

When you sign with it you need to specify again the password:

ADD SIGNATURE TO [usp_foo]
  BY CERTIFICATE <certname> WITH PASSWORD 'mysecretpassword';

See Signing an activated procedure for an example.


Should roles without login have the connect permission on a database? I want to create a read-only role in PostgreSQL and use it to assign it to multiple new users, who should have read-only access. It makes sense to me to also grant the read-only permissions to the role on the target DB. My concern is, whether the role, created with 'NOLOGIN' should have the 'CONNECT ON DATABASE' permission. Or is it better for some reason to grant the connect permission only to individual users with logins?
The role:
CREATE ROLE role_readonly WITH
    NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Permissions:
# should this be here?
GRANT CONNECT ON DATABASE db_name TO role_readonly;
#
GRANT USAGE ON SCHEMA public TO role_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO role_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO role_readonly;

It is fine to grant CONNECT on a database to a NOLOGIN role. The effect is that all members of the role are allowed to connect to the database. That is independent from the question whether the role can login or not.
Note that by default, everybody (PUBLIC) is allowed to connect to a database. You would have to REVOKE that privilege to make the above privilege meaningful.