Advice for database implementation We need to configure a database for our NGO. We have to save information about the children and several pictures and documents for each of them.
A friend is currently saving the data of over 70 kids in folders and subfolders of her device and it's becoming a mess. She asked me if I could digitalize it and I am not sure how I want to implement it.
We need the database and some kind of frontend where my friend can easily add, change or display information in the database without any IT knowledge. It would also be beneficial if we could connect our website to that database to display some pictures of the kids.
I've read about Wordpress and their database, is that a good solution? Or should I create our own MySQL database and a simple webpage where my friends has access to it?
I'm kinda new to databases but I really want to implement something that is better than our current storage situation. We are currently using a simple PHP, HTML, CSS, JavaScript website: www.kencef.de. A friend and I are the only developers and aren't that experienced yet. Should we switch to Wordpress?

... save information about the children and several pictures and documents ...
... A friend is currently saving the data of over 70 kids in folders and subfolders on her device ...
... We need the database ... frontend ... easily add, change or display informations ... without any IT-knowledge ...

I don't say this very often, but in this case, I don't think you can - or should - avoid it.
Hire somebody that knows what they're doing, especially in the fields of Data Security, Web Application Security, and who knows their way around the General Data Protection Regulations (GDPR) - at the very least.
The rules and regulations around Data Protection and Security and, more importantly still in your application, Safeguarding, etc., are Legion and you DO NOT want to get on the wrong side of them.
Question: What's does your Privacy Notice say?
If your answer is "What's a Privacy Notice", then you should not be touching this problem with a barge-pole!


How to write a SQL query to find the range from column values? I have some fields in my database which contain values like 2483-5802 and 1245-3325.

Now I have a user input and I have to find it from this column.
Example if I have to search 2000 which will come in range 1245-3325.

So how I can write a query to find that 2000 belongs to 1245-3325 this range?

As follows (replace yourtable and each of the two instances of rangefield):

SELECT *
FROM `yourtable`
WHERE ? BETWEEN CAST(`rangefield` AS int) AND CAST(MID(`rangefield`, LOCATE('-', `rangefield`)+1) AS int);


Column must appear in the GROUP BY clause error even though it DOES appear there I have a table that looks like this:

order_number | key_a | key_b | key_c | key_d | quantity
-------------|-------|-------|-------|------------------
14400        | 2     | 3     | 4     |    54 | 40
14000        | 2     | 3     | 4     |    87 | 13

I'm trying to run the following query:

select order_number,key_a,key_b,key_c,sum(quantity) from my_table
      group by (order_number,key_a,key_b,key_c);

Yet I'm getting the error:

 ERROR:   column "my_table.order_number" must appear in the GROUP BY clause or be used in an aggregate function

Since the column very clearly IS present in the GROUP BY clause,
I am very confused.

I also tried explicitly naming the column as my_table.order_number as shown in the error message, but no dice.

I figured it out. It's the damn parentheses around the columns in the GROUP BY clause. The correct query is:

select order_number,key_a,key_b,key_c,sum(quantity) from my_table
      group by order_number,key_a,key_b,key_c;


Database design for object attributes Currently I am trying to create a database design that is long lasting. But I hit a point where I not know how to continue.

I have a object table in my database. That tables stores one unique object (With a incremental ID). However for the application I am creating attributes about that object are required (extra information so a sort of metadata for that object).

These are now five different attributes per object. But I already know in the upcoming future they want more attributes added to a object so they can get more information about it.

If it would have stayed with those five attributes a would create extra columns in the object table.

But now I am thinking to create seperate attributes table where I store that data. And I was thinking in the sorts of:

Table: attributes

Columns: object_id, object_type, object_data. Is this the best practice or what are your thoughts on the matter?

You could create tables for Attributes and its values. A simplified schema below:

CREATE TABLE [dbo].[AttributesList](
    [Id]            INT IDENTITY(1,1) NOT NULL,
    [ObjectType]    VARCHAR(32) NOT NULL,  -- wich objects have the attribute
    [AttributeCode] VARCHAR(32) NOT NULL,  -- short attr identifier
    [Kind]          INT NOT NULL,          -- kind of the attribute's value
    [Name]          VARCHAR(80) NOT NULL
)

CREATE TABLE [dbo].[AttributesValues](
    [Id]            INT IDENTITY(1,1) NOT NULL,
    [ObjectId]      INT NOT NULL,  -- object.object_id
    [Kind]          INT NOT NULL,          -- AttributesList.Id
    [Code]          VARCHAR(32)  NULL,  -- for short strings
    [Flag]          INT          NULL,  -- for int values
    [Info]          VARCHAR(250) NULL,  -- for long strings
    [Amount]        MONEY        NOT NULL  -- for money
)

You'll be able to add as many attributes for some ObjectType as you need, and store their values in AttributesValues table in a column that depends on [AttributesList].Kind value.


How do I send an array of strings to PG from PHP without the former becoming confused? I want to do this, but for more than one string:
SELECT * FROM table WHERE label <> $1

That selects all records from table where the label column is different from a single string. It works.
But now I want to do the same, but instead of just checking a single string, I have an array of strings in PHP, [ 'ignore string 1', 'ignore string 2', 'ignore string 3' ],  which I wish to have excluded.
If I just send an array of strings to that same query for the $1, it gives errors. I've tried a lot of things now. I even attempted:
SELECT * FROM table WHERE label NOT IN $1

But that doesn't work for an application-provided array of strings.
Do I simply need to do ::array to convert it or something? I couldn't get it to work either.
Yes, it's most common to have a different table in the database with "ignore phrases", but in this case, I need to provide a list of such strings as an array from the application.

Use instead:
SELECT * FROM table WHERE label <> ALL ($1);

Then you can pass an actual array for $1 - while (NOT) IN expects a list of values. See:

How to use ANY instead of IN in a WHERE clause with Rails?

If NULL values can be involved on either side, either query is problematic. You may want the form:
SELECT * FROM table WHERE (label = ANY ($1) IS NOT TRUE);

See:

Why does my SELECT query not return null values?


Mysterious merge replication problems creating a subscription We have a merge publication which had been mostly working OK for a long time (years). Recently an article was added, we were informed the snapshot was out of date, so we regenerated it. Then we got error messages during initialization and found in the verbose error logs this problem:

  Msg 1934, Level 16, State 1, Procedure DDLTrigger_Index, Line 19
  INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

...fast forward to now. In experiments we found that yes it is possible to create a merge publication on the publisher and subscribe on our subscriber server, but only if using a different database. Went through this just to rule out permissions/etc and to determine it was truly the subscriber db that was the problem.

So, it seemed logical to remove replication from afflicted DB in hopes of cleaning out any buried metadata mess:

sp_removedbreplication @dbname = 'sickDB' @type = 'all'

So far so good. However now, I get the exact same error, but much earlier in the process. Specifically, when executing:

/****** Begin: Script to be run at Subscriber ******/
use [sickDB]
exec sp_addmergepullsubscription
@publisher = N'publisher',
 @publication = N'publication',
 @publisher_db = N'goodDB',
  @subscriber_type = N'Local',
   @subscription_priority = 0,
    @description = N'hope this works', @sync_type = N'Automatic'

(above edited slightly).

Is there some remaining prob lurking in the metadata? The original theory on the ANSI_PADDING stuff was there were in fact some tables in the subscriber DB w/ computed columns thus that seemed related. However some flipping of ANSI_PADDING settings (and Googling) didn't really lead to a conclusive answer.

Thanks so much for reading and let me know of any other info I can provide that may help.

Oh yeah, publisher and subscriber are both SQL Server 2005.

I think you should check that in your database you have some ddl trigger which can switch off 'ANSI_PADDING'. If in the trigger body you will find something
like i.e. SETANSI_NULLS OFF it could be your error.

If it is i.e. trigger which is monitoring that users are removing or adding i.e. indices.
That will be possible it is acting when you creating your pull subscription and replication internals are trying to add replication triggers on your subscriber database.

In this case you need to disable trigger, create pull subscription and enable trigger.

More you can find on this link. It is a bug in ms sql server 2005

http://connect.microsoft.com/SQLServer/feedback/details/334194/ansi-padding-options-in-replication-snapshot

J


SQL Row concatenation with XML PATH and STUFF giving aggregate sql error I am trying to query two tables and get results like the following:

Section    Names
shoes      AccountName1, AccountName2, AccountName3
books      AccountName1

The tables are:

CREATE TABLE dbo.TableA(ID INT, Section varchar(64), AccountId varchar(64));

INSERT dbo.TableA(ID, Section, AccountId) VALUES
(1 ,'shoes','A1'),
(2 ,'shoes','A2'),
(3 ,'shoes','A3'),
(4 ,'books','A1');

CREATE TABLE dbo.TableB(AccountId varchar(20), Name varchar(64));

INSERT dbo.TableB(AccountId, Name) VALUES
('A1','AccountName1'),
('A2','AccountName2'),
('A3','AccountNAme3');

I saw a few questions answered saying to use "XML PATH" and "STUFF" to query the data to get the results I am looking for, but I think there is something missing.  I have tried the below query and get the error message:

  Column 'a.AccountId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I don't have it in the SELECT clause of either query, but I assume the error is because AccountId is not unique in TableA.

Here is the query I am currently trying to get working correctly.

SELECT section, names= STUFF((
    SELECT ', ' + Name FROM TableB as b
WHERE AccountId = b.AccountId
FOR XML PATH('')), 1, 1, '')
FROM TableA AS a
GROUP BY a.section

Sorry, I missed a step in the relationship. Try this version (though Martin's will work as well):

SELECT DISTINCT o.section, names= STUFF((
    SELECT ', ' + b.Name
    FROM dbo.TableA AS a
    INNER JOIN dbo.TableB AS b
    ON a.AccountId = b.AccountId
    WHERE a.Section = o.Section
    FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM dbo.TableA AS o;

An approach that is at least as good, but sometimes better, is switching from DISTINCT to GROUP BY:

SELECT o.section, names= STUFF((
    SELECT ', ' + b.Name
    FROM dbo.TableA AS a
    INNER JOIN dbo.TableB AS b
    ON a.AccountId = b.AccountId
    WHERE a.Section = o.Section
    FOR XML PATH, TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, '')
FROM dbo.TableA AS o
GROUP BY o.section;

At a high level, the reason DISTINCT applies to the entire column list. Therefore for any duplicates it has to perform the aggregate work for every duplicate before applying DISTINCT. If you use GROUP BY then it can potentially remove duplicates before doing any of the aggregation work. This behavior can vary by plan depending on a variety of factors including indexes, plan strategy, etc. And a direct switch to GROUP BY may not be possible in all cases.

In any case, I ran both of these variations in SentryOne Plan Explorer. The plans are different in a few minor, uninteresting ways, but the I/O involved with the underlying worktable is telling. Here is DISTINCT:

And here is GROUP BY:

When I made the tables larger (14,000+ rows mapping to 24 potential values), this difference is more pronounced. DISTINCT:

GROUP BY:

In SQL Server 2017, you can use STRING_AGG:

SELECT a.section, STRING_AGG(b.Name, ', ')
    FROM dbo.TableA AS a
    INNER JOIN dbo.TableB AS b
    ON a.AccountId = b.AccountId
    WHERE a.Section = a.Section
    GROUP BY a.section;

The I/O here is almost nothing:

But, if you're not on SQL Server 2017 (or Azure SQL Database), and can't use STRING_AGG, I have to give credit where credit is due... Paul White's answer below has very little I/O and kicks the pants off of both of the FOR XML PATH solutions above.

Other enhancements from these posts:

Grouped Concatenation in SQL Server
Grouped Concatenation : Ordering and Removing Duplicates
Comparing string splitting / concatenation methods

Also see:

Bad habits to kick : avoiding the schema prefix


Validate database backup For Innodb tables Every fortnight, I take MySQL database dump from slave and then perform following steps as backup validation.

Number of Tables in every database.
Validate Schema of tables.
Validate random records for important tables.
An approximate(as its a backup) count of records in the table (only for MyISAM)

I am able to complete the activity. However since most of the tables are in InnoDB, I am not able to validate count properly. What solution can be there as count(*) is too slow in innodb?

Would this work for you?

SELECT database_name, table_name, last_update, n_rows
FROM mysql.innodb_table_stats;

immediately after DR?  To allow verification and avoid the long COUNT(*).


Postgres: Getting the "partition number" instead of row_number Figure a table with the following data, how to exclude 'alone' values?

id; data
(1, 'foo'),
(2, 'foo'),
(3, 'foo'),
(4, 'bar'),
(5, 'bar');
(6, 'jak');

I try with this:

select id, data, row_number() over (partition by data)
from t;

 -- RESULT
id  data    row_number
4   bar     1
5   bar     2
1   foo     1
2   foo     2
3   foo     3
6   jak     1

 -- EXPECTED (exclude partition with one row)
id  data    row_number
4   bar     1
5   bar     2
1   foo     1
2   foo     2
3   foo     3

row_number() gives an index to rows in each partition.

How to have the number of rows by partition? Some kind of WHERE partition_count > 1.

fiddle

Use count as a window function:

SELECT id, data
FROM (SELECT id, data,
             count(*) OVER (PARTITION BY data) AS c
      FROM t) AS q
WHERE c > 1;


Undo tablespace full, how to free it My undo tablespace is full after some modifications. Las column is % of space used.

xxx_undo_01.dbf UNDO    2147483648  16777216    2048    2032    16  0,78    99,22

Now i cannot execute any query according to this. How can i free the undo tablespace ?

Thanks in advance.

You can execute below query.

DROP TABLESPACE xxx_undo_01 INCLUDING CONTENTS AND DATAFILES;


Correcting the imported latin1 data to be utf8 I restored my drupal site database from a backup but after i finished i discovered that the Imported data was symbols like this Ù‚ÙˆØ§Ù†ÙŠÙ† ÙƒØ±Ø© because the backup was taken using latin1 charset but my tables and database are utf8

I want a way to convert this data to utf8 so the are stored as letters and words in my database not symbols

How can i do this ?

You can restore your database using this command

mysql -u someuser -p --default-character-set=utf8 somedatabase < backup.sql

This overrides the default client character set.

I would also make sure that my tables are also in utf8.

So when you open up your backup file with a text editor, you should see utf8 as the DEFAULT CHARSET

CREATE TABLE `sometable` (
  `c1` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

If you are still having problems, check your backup and make sure that the actual data being restored is correctly formatted.  If you had a character like é for example, it should show up as é when you view it in a text editor and not as &eacute; or some unreadable symbol.

One additional note.  utf8_general_ci and latin1_swedish_ci are collations and not character sets.   Character sets like UTF8 can have multiple collations, some are case sensitive _cs and others are case insensitive _ci.  Collations set rules for character comparison and sorting.

http://en.wikipedia.org/wiki/Collation


SQL Server in memory temp table disk usage I've activated in-memory tables on my databases and use it only for table variables. However I notice that the disk space used by the in memory folder keeps growing and growing. I was wondering if there was a way to delete those files as there should be no persisted data in them.

Thanks in advance.

Based on this post by Michael Swart (provided by Aaron Bertrand), this seems to be the normal behavior of Hekaton.


Can't use Fuzzy Lookup in BIDS A coworker of mine was having an issue with BIDS. He's doing a simple fuzzy lookup and is only looking against two rows (For Testing Purposes). The weird thing is that when he tries to execute a command line prompt appears then vanishes and then the package hangs.

So I had him send me the package and decided to run it myself and found the exact same thing happening?

It will essentially sit like that forever. No error messages or anything. The last message it gives me is this.

[SSIS.Pipeline] Information: Execute phase is beginning.

Now this might be better placed on Superuser or something because I have a hunch as to what the problem is. Our computers are locked down to a frustrating extent. There are several issues I've run into with botched installs and software not working correctly.

If you have any ideas for what's happening or how to fix it let me know.

Update: Tried installing this patch to no avail. Doesn't work in 32 bit or 64 bit or as Admin.

Also followed the output and the end is this. SSIS package "Package1 (1) (1).dtsx" finished: Canceled.

Something similar happened to my BIDS after I installed Visual Studio '15 on my dev machine.  There are registry overwrites when a newer version of BIDS/SSDT/Business Intelligence Templates are installed causing failures in older version.  I fixed this by setting up a vm on my local that I only use BIDS on.
Why Can't SQL Server and Visual Studio Get Along?


SELECT is using a non-PK Index instead of the PK In the [dbo].[Programs] table, the column [Id] is the PrimaryKey (not par of a composite key). There are also quite a few other indexes on that table.

When I'm running this simple query, SELECT [Id] FROM [dbo].[Programs], here is the execution plan:

My question is: Why is it not just using the PK index instead?

Performance is not an issue as that table has 23 rows, but I just find it odd, and I want to understand why SqlServer is right, and why I'm wrong to assumed it would be better.

It has nothing to do with primary key, except that the primary key is typically clustered.

In your case SQL Server is not using the clustered index because, by definition, the clustered index includes all of the columns in the table. Since you only want Id, it is using a skinnier index that satisfies your query simply because it's less work to do so, and even if your table only has a single column, it's still going to choose the non-clustered index.

If I ask you to get me a beer from the fridge, your choices are to:

wheel over the entire fridge
bring over the case
bring over a single beer

In your case, 1. is using the clustered index, 2. is using some wide index, and 3. is using an index that only contains Id.

A clustered index is simply not always the best choice for an operation, much like a Ferrari is not the car you always want to use for a task (racing someone vs. towing a yacht, for example).


Query optimization [Oracle] The Query:

select JOIN_RESULT.batch_id as BATCH_ID,
       JOIN_RESULT.unique_doc_id as DOCUMENT_ID,
       JOIN_RESULT.brand as BRAND,
       JOIN_RESULT.message_date_time as UPLOAD_DATE,
       decode(JOIN_RESULT.recordstatus, '5', 'SUCCESS', 'FAILED') as UPLOAD_STATUS,
       decode(JOIN_RESULT.recordstatus,
              '5',
              null,
              (decode(STATUS_RESULT.vendor_unique_doc_id,
                      JOIN_RESULT.unique_doc_id,
                      STATUS_RESULT.comments,
                      JOIN_RESULT.comments))) as FAILURE_REASON
  from (select d.vendor_unique_doc_id, d.comments
          from doc_duplicate_b d
         inner join doc_recon_record_w r on d.vendor_unique_doc_id =
                                                      r.unique_doc_id) STATUS_RESULT
  full join (select r.batch_id,
                    r.unique_doc_id,
                    r.brand,
                    h.message_date_time,
                    r.recordstatus,
                    r.comments
               from doc_recon_record_w r
              inner join doc_recon_header_w h on r.fileid =
                                                           h.fileid) JOIN_RESULT on STATUS_RESULT.vendor_unique_doc_id =
                                                                                JOIN_RESULT.unique_doc_id
 order by JOIN_RESULT.batch_id

  The Question:

How can I optimize the performance of this query?

Using joins good or is it better to do a Cartesian Product?

Here is a modified query that should be faster and produce the same results:

SELECT r.batch_id, r.unique_doc_id AS DOCUMENT_ID, r.brand,
   (SELECT h.message_date_time FROM doc_recon_header_w h WHERE r.fileid = h.fileid)
      AS UPLOAD_DATE,
   decode(r.recordstatus, '5', 'SUCCESS', 'FAILED') AS UPLOAD_STATUS,
   decode(r.recordstatus, '5',  null,
      (decode(d.vendor_unique_doc_id, r.unique_doc_id, d.comments, r.comments)))
      AS FAILURE_REASON
FROM doc_recon_record_w r
LEFT JOIN doc_duplicate_b d ON d.vendor_unique_doc_id = r.unique_doc_id
ORDER BY r.batch_id;

Reasoning:

Doc_recon_header_w is only joined to get the message_date_time, so that is moved to the select list so it will occur only on the results that will be displayed.  Note that this won't work if there is more than one row per fileid in the table or a header record does not exist for a record, but I wouldn't expect either of these to be the case.
The join on doc_recon_record with doc_duplicate_b is done only to check for the existence of a record in the former, which means the join doesn't need to be a FULL JOIN and can be a LEFT JOIN if the table order is re-arranged.


Integrity constraints in a relational database - should we overlook them? I’m in a permanent discussion with the developers of the company where I work because they say it is better to get rid of relationship enforcement (via FOREIGN KEY constraint definitions) in a relational database in order to speed up large queries and to gain better performance.
The platform under consideration is MySQL 5.x, and no FOREIGN KEY has been set up, even some PRIMARY KEY constraints of the relevant tables are missing which, at least for me, is not reasonable. Maybe they’re right and I’m wrong, but I don’t have enough arguments to discuss about this situation.
This has been the preferred approach for three years now. I’m new in this company (only one month) but, as the product “works”, there is hesitation to enhance the database; nevertheles, the first thing I noticed is one page taking 1 minute to load (yes, 60 seconds!).
One of the claims behind the current state of affairs is that a “denormalized” database is faster than a normalized one, but I don’t believe that’s true.
Most of the relevant queries include JOIN operations, which makes them run very, very, very slow with large amounts of data (the database contains millions of rows).
Commonly, the handling of “CRUD” operations is implemented at the application program code level; for example, in order to DELETE some data FROM, let’s say, TableA:

it is necessary to first check on the fly if there is some relationship between the rows of TableA and TableB,
in case that said relationship is “detected”, then the app program code won’t allow to DELETE the pertinent row(s), but
if for some reason the app program code fails, then the DELETE operation will “succeed”, no matter if there is any relationship regarding the involved rows and tables.

Question
Could you help me to elaborate a good, accurate and solid answer to enrich the debate?

Note: Maybe something like this has been asked (and answered) before, but I couldn’t find anything by means of Google.

If, as stated in your post, the intention is to create a relational database (RDB for brevity) and, therefore, it is expected that it functions as such, the short answer is:

No, you should not overlook data integrity constraints.

The primary objective should be to manage the pertinent data as it is: a quite valuable organizational asset, and a reliable manner to achieve said objective is employing technical means that are supported on sound theory.
Thus, as a database professional, you can take advantage of the state-of-the-art and elegant relational model mechanisms supplied by Dr. E. F. Codd to enforce business rules, and avoid the (technical and organizational) problems that would eventually arise if they are not utilized.
In this respect, I will share (a) my overall take on constraints and also (b) several considerations about the state of affairs of the database and the work environment at issue as follows.
FOREIGN KEY constraints, data relationships and referential integrity
A RDB must reflect the characteristics of the business context of interest with high accuracy, which definitely requires an in-depth conceptual-level analysis led by a modeler or designer who follows best practices, counting with the indispensable assistance of the business experts. That analysis must yield the correct identification and formulation the applicable business rules.
Consequently, if such a modeler has identified that there exist interrelationships between the data of relevance, he or she must configure the corresponding logical-level restrictions so that the database management system (DBMS) can guarantee that the data remains consistent with the exact characteristics and rules determined in the analysis referred to above at all times.
Regarding the database under discussion, one can infer that the pertinent interrelationships have been identified, since you mention that there is a procedural (and easy to circumvent) attempt to enforce them from outside of the DBMS facilities, by dint of application program code (which is a pre-relational approach) that in any case has to “touch” the database to try to validate the wholeness of said interrelationships.
However, as you know, that is not the optimal technique to protect referential integrity, because the relational science has prescribed a very powerful instrument for this purpose, i.e., FOREIGN KEY (FK) constraints. These constraints are very easy to create (via the superior declarative approach) as they are single sentences that avoid resorting to unnecessary and error prone ad hoc procedures. It is very useful to note that the execution speed of FK constraints has been highly optimized by specialized programmers (and the major platform vendors have worked on it for even decades now).
Furthermore, since a RDB must be an independent (self-protective, self-describing, etc.) software component that is capable of being accessed by multiple application programs (desktop, automatic, web, mobile, combinations thereof), it should not be “coupled” with the code of any of these apps.
Likewise, the data —being an significant organizational resource— naturally tends to outlive application programs, application programmers, application development platforms and programming paradigms.
PRIMARY KEY constraints and implications of duplicate rows
When —conceptually speaking— a particular kind of thing has been deemed of significance in a business environment, a database modeler has to (1) determine its relevant characteristics —i.e., its properties—, confirm said kind of thing as an entity instances prototype —i.e., an entity type— and (2) represent it by way of a table that is integrated by one or more columns in a logical design.
Then, just like it is paramount to distinguish each individual instance of a given entity type in the real world business, each corresponding row enclosed in a table must be uniquely distinguished as well. If a table does not have any KEY declared, it will eventually retain duplicates, and if there are two or more rows that retain exactly the same values, then they all carry the same meaning, they all represent the same fact.
On that point, duplicate rows should be discarded due to multiple reasons. From a theoretical perspective, the designer has to make sure that each row is always unique for the purpose of having tables that work as relationally as the SQL data sub-language permits (having important repercussions on data manipulation operations). Besides, from an informational perspective, if multiple rows represent the same fact, recording them is not only superfluous but harmful, as exemplified bellow:

Suppose that someone has inserted two identical rows in a certain table.
Later, someone else comes and updates only one occurrence of the duplicates. As a consequence, the other occurrence is not up-to-date anymore.
Successively, another person updates the occurrence that had not been modified so far. In this manner, both duplicates have undergone different changes at distinct points in time.
After that, when someone is interested in selecting the information conveyed by the rows in question, he or she can find two different “versions” of it.

In this way:

Which “version” can be considered the correct, reliable one?
Which one reflects the real world accurately?

As you know, this phenomenon can even have legal implications, a circumstance that surely is of enormous importance.
Besides, the time and effort that has to be employed to handle such contradictions (perhaps through some kind of “update synchronization”) should better be devoted to tasks that actually produce value for your organization. So, retaining contradictory rows ought to be avoided by design to keep the consistency of a database intact.
That is why the identification of a PRIMARY KEY (PK) and the declaration of the respective constraint should always be performed by the database designer. But it must be mentioned too that a table may have more than one column or combination of columns that hold values that uniquely identify every row; as a consequence, besides setting up a PK constraint (ideally established as PRIMARY due to pragmatical reasons), the designer must as well declare one or more ALTERNATE KEYs (usually defined via one or more UNIQUE plus NOT NULL constraints) when applies (which is pretty common).
Another advantageous property of PKs is that, when “migrated” to other tables to take part in single or composite FKs, they can help to enforce the cardinality ratios of the relationships/associations that exist among the data. All this, yes, by means of simple and efficient declarative settings, ensured by the DBMS invariably.
(Current) CHECK constraints and single-row validation
Let us not forget about the relevance of (current) CHECK constraints that, restricting declaratively the valid set of column values of a row (which may appear simple, but is in fact an fundamental feature of a relational DBMS), help as well to make certain that the rules of the business context are reflected with precision always.
As you marked your question with the MySQL tag, starting with version 8.0.16 (see also this MySQL server team blog post) such a platform finally! enforces this type of constraint. In this regard, it must be mentioned that in all its prior versions this DBMS did permit declaring CHECK restrictions, but ignored its enforcement!, situation that, understandably, was reported as a bug since 2004.
So, if using legacy versions, you would have to take care of this factor by other means, e.g., ACID TRANSACTIONS, TRIGGERS, or other methods within the DBMS itself (see this answer by @ypercubeᵀᴹ
 for information on this subject) so that the data continues to be consistent.
ASSERTION constraints: setting up further multi-row and multi-table business rules declaratively
One aspect that for whatever reasons is very poorly supported —if at all— by the different SQL DBMSs, including MySQL, is enabling multi-row and multi-table constraints in a declarative fashion —beyond PKs and FKs, evidently—.
For its part, the SQL standard has included ASSERTIONs from many years now. I do not know what rules of your business environment would benefit from that logical-level validation approach but, as a database designer, I consider that it would be pretty handy to constraint data with one or more ASSERTIONs when required, although I have to mention that from the point of view of the DBMS developers, this paramount kind of tool has been difficult to implement at the physical level of abstraction.
It appears that the Oracle vendor and/or developers are evaluating ASSERTION support since 2016, and that would make that DBMS more relationally-compliant and, hence, more robust and competitive. I guess that, if (i) their consumers keep pushing and (ii) Oracle succeeds in the implementation, then (iii) other DBMS vendors/communities will have to enable them too, and their usage will start to spread. Certainly, that would be a huge progress in the data management field, and being one of the most distinctive tools envisioned by Dr. Codd, I personally hope that we will see that happening soon.
Data consistency and the decision-making process
As discussed above, one of the most important aspects of a RDB is that it guarantees by itself the consistency of the data it retains, and said consistency is only met when the RDB complies with the integrity constraints declared by the modeler.
In this respect, it is compulsory to have base tables (those established in a DDL structure) which integrity is protected in order to be able to create derived tables (e.g., a SELECT statement or view that retrieves columns from multiple tables) that are trustworthy, because derived tables have to be produced necessarily in terms of base tables.
It is pretty obvious that people use information as the main tool in the organizational (and in the ordinary) decision-making process. Then, if the information presented by a database is not coherent and accurate, the decisions based on such information will not be sound (to say the least). That is why a RDB must be carefully designed and implemented: it should be built to become a reliable resource that can assist its users to make well-founded decisions.
“Denormalization”
Alas, “a ‘denormalized’ database is faster than a normalized one” is a widely spread misconception, although it is also an “argument” that can be refuted on logical, physical and pragmatical grounds.
Firstly, denormalization implies necessarily that a base table has been previously normalized (by virtue of a formal, science-based, procedure fulfilled at the logical level of abstraction of a database).
So, assuming that said table was in actual fact normalized correctly, “denormalizing” it (which, in contrast to the formal meaning of the word, involves appending to it columns that belong in, and are also part of, other tables in an ad hoc fashion) might aid, e.g., to speed up (at the physical level) the processing of only one or a few particular SELECT statement, while such course of action might, at the same time, be undermining the execution of many other associated data manipulation operations (e.g., several INSERT, UPDATE, DELETE and SELECT statements, or combinations thereof enclosed within a single or multiple ACID TRANSACTIONS).
In addition, denormalization (be it formal or informal) would introduce update/modification anomalies that deteriorate the coherence of the database, a problem that “may” be handled by complex, costly and error-prone procedures, when all this can be prevented from the very beginning.
Physical-level scaffoldings supporting normalized and “denormalized” tables
A logical (abstract) layout (SQL-DDL design) that is meant to be utilized in the real world clearly holds physical (concrete) repercussions that must be considered.
In this manner, a “denormalized” table would necessarily be “wider” (holding additional columns) which means that its rows would necessarily be heavier (requiring more and larger physical-level components), so that means that the underlying computing processes (e.g., those that have to do with the hard drive or memory) can easily turn slower.
In contrast, a normalized table that is of course “narrower” (having less columns) would be a “lighter” element (served by less and smaller physical components) that “behaves faster”, which would speed up the series of actions related to, e.g., data writing and reading.
That being so, it is very convenient to (a) normalize the relevant tables formally and prudently, keeping them as such, and then (b) to make use of any physical level resource that can optimize data retrieval and modification speed, e.g., implementing a careful and efficient indexing strategy, enabling proper software and hardware server configurations, upgrading network bandwidth capabilities, etc.
The functioning of the database under consideration and approaching your colleagues
The following paragraphs of your question have to do with the speed of the data retrieval operations:

[A]s the product “works”, there is hesitation to enhance the database; nevertheless, the first thing I noticed is one page taking 1 minute to load (yes, 60 seconds!).

If loading a certain page takes that long, it is evident that the users of the system are not receiving an appropriate service; therefore, even when it “works”, its functioning does not seem to be optimal at all, point that demonstrates that your intentions to make the entire computerized information system  (database and apps) more efficient are well sustained, and shows a very constructive attitude.
Then, even when science definitely supports you and hence you should maintain a firm posture, I suggest approaching the situation in a diplomatic way, since at the end of the day, your employers, coworkers and yourself are JOINing efforts in order to make the whole organization more successful. Thus, that is one argument that you should stress, that, while they are doing other things more than well, improving general and specific data management practices can considerably help in producing more organizational and individual growth.

Most of the relevant queries include JOIN operations, which makes them run very, very, very slow with large amounts of data (the database contains millions of rows).

It is worth to note that the JOIN operator is an essential and powerful element that pertains to relational manipulation of data. Then, although more robust platforms serve it with comparatively faster executions, the circumstance you describe is most probably a symptom of a non-eficient design (at the conceptual, logical and physical levels of abstraction). So, my first sight estimations are:

The INDEX settings may require improvement.
The PK and FK column type and size definitions need to be reviewed (and I totally agree with @Rick James regarding his PK considerations, as composite KEYs tend to be much more efficient than appended surrogates in the appropriate cases).
Further (formal, science-based) normalization might help to alleviate these problems, on account of the fact that, in the right circumstances (i.e., carried out in a well-designed RDB), JOINs are executed very fast.

Moreover, yes, as @TommCatt mentions in his answer, sometimes a (logical) rewrite of a query modifies its (physical) execution plan accelerating data reading/writing, which is a factor that should decidedly be taken into account.


ORDERBY timestamp performance issue I have some queries dealing with coordinates stored in POINT.
Everything is ok without order (about 0.6 ms to query a 5 millions posts and 1 million users database, for testing) but go to 13s when add ORDER BY created_at DESC.)

All fields in WHERE clauses have index.
Created_at index is DESC sort.

Deal is to recover Posts submitted by Users in Geographical Sphere.

SET @radius = 30;

SET @center = GeomFromText('POINT(12.005 -1.001)');
SET @r = @radius/69.1;
SET @bbox = CONCAT('POLYGON((',
  X(@center) - @r, ' ', Y(@center) - @r, ',',
  X(@center) + @r, ' ', Y(@center) - @r, ',',
  X(@center) + @r, ' ', Y(@center) + @r, ',',
  X(@center) - @r, ' ', Y(@center) + @r, ',',
  X(@center) - @r, ' ', Y(@center) - @r, '))'
);

SELECT * FROM posts INNER JOIN users ON posts.user_id = users.user_id
WHERE posts.user_id IN (
    SELECT user_id  FROM users_contact WHERE
    Intersects( coordinates, GeomFromText(@bbox) )
    AND SQRT(POW( ABS( X(coordinates) - X(@center)), 2) + POW( ABS(Y(coordinates) - Y(@center)), 2 )) < @r )
    AND users.user_status=1 AND posts.status=1 AND posts.created_at <= '2016-10-24 10:30:53' AND posts.context = '1'
# ORDER BY posts.created_at DESC
LIMIT 30;

Tried INNER JOIN instead of IN Clause, but result is the same.
Putting Coordinates in users table instead of users_contact is the same.

Thanks, MySQL 5.5

This is normal situation

when You run query without ORDER BY - LIMIT 30; work perfect - it stop query after first 30 records, any 30 records
When You add ORDER BY - MySQL first send all records to temporary table (if dataset big), than sort it, and then return to You 30 correct records

So on huge dataset - it work as expected.
Solution really only one - analyse query and reduce number of total records returned by query, how - depend from business logic

For example - posts.created_at <= '2016-10-24 10:30:53' (it mean yesterday) return all records from 5 000 000, but if add posts.created_at >= '2016-09-24 10:30:53' it return only month of data and etc - any legal ways for reduce number of records.

Second recommendation depend from Your table structure, some time replace SELECT * to the SELECT "WHAT REALLY NEED" could dramatically increase speed - for example one of column BLOB or TEXT, so exclude this column from SELECT - reduce size of data before sort for many. many times.
Even if this BLOB column need for You - You can request it by ID (single record) by second query from the page


T-SQL LIKE Predicate failed to match with whitespace in XML converted varchar Recently I attempt to search for a particular pattern by converting XML data into varchar(max) although I'm aware it's not the best practice and found out it's not working as expected:-

Setup

declare @container table(
    [Response] xml not null
);

declare @xml xml =
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://abc.com/xsd" xmlns:ns="http://abc.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Header>
     <ns:MessageHeader>
       <xsd:ID>ABC</xsd:ID>
       <xsd:Date>2018-12-31T23:59:59</xsd:Date>
     </ns:MessageHeader>
   </soapenv:Header>
   <soapenv:Body>
     <ns:MessageResponse>
       <ns:return>
         <xsd:ResponseList xsi:nil="true" />
       </ns:return>
     </ns:MessageResponse>
   </soapenv:Body>
 </soapenv:Envelope>';

insert into @container values (@xml);

This query works

select *
  from @container
 where cast(Response as varchar(max))
  like '%<xsd:ResponseList xsi:nil="true"%';

notice the wildcard character ends 3 characters (i.e.' />') before the XML node

but this is not

select *
  from @container
 where cast(Response as varchar(max))
  like '%<xsd:ResponseList xsi:nil="true" %' -- with space
    or cast(Response as varchar(max))
  like '%<xsd:ResponseList xsi:nil="true" />%' -- whole XML node;

I suspect this is probably due to escape characters and tried a few other alternatives but to no avail, appreciate if someone can shed some light on this.

EDIT (ANSWERED)

Following query would work based on Mr. Browstone's insight:-

select *
  from @container
 where cast(Response as varchar(max))
  like '%<xsd:ResponseList xsi:nil="true"/>%';

Here's my follow question @ CodeReview with XQuery expression:-

T-SQL Verify whether XML node from SOAP request contains any child nodes

This is by design.
When you store a document using the XML data type it is compressed and organised into a structure that Sql Server can perform operations on efficiently. One of the steps that it goes through to do this is to generate the InfoSet. When it does this, it removes anything that it determines to not be necessary, in your example, whitespace:

The InfoSet content may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.

When you select the entire contents of the field (such as when you are converting it to NVARCHAR(MAX) it rebuilds the XML document before returning it. This document may not be an identical copy of the document that you inserted. For example, if you have used self-closing elements, Sql Server may return opening and closing elements instead.
The documentation also continues on to say:

Example: Retaining Exact Copies of XML Data
For illustration, assume that government regulations require you to retain exact textual copies of your XML documents. For example, these could include signed documents, legal documents, or stock transaction orders. You may want to store your documents in a [n]varchar(max) column.

So, if you want to store the exact copy of your document, then NVARCHAR(MAX) or VARCHAR(MAX) is the best option. You can then convert it to XML to query it later on (though this can be costly).
For more information, see the documentation on XML Data Type and Columns (SQL Server) and also Define the Serialization of XML Data which outlines the rules that Sql Server applies when converting XML to a string type.


deploy replicas on multiple VMs How do you set up a multi-node replica set in Mongodb? I don't know where to start. I'm trying to set up a replica set of three members in three virtual machines, i.e. there will be a total of three members, one for each virtual machine.

Please follow the steps from the below links, If you face issues in setting up the replica set or any questions on the blog. Please add a comment with your issues.

How to Setup MongoDB Replication

Deploy a Replica Set


SYSTEM tablespace getting filled up My SYSTEM tablespace is getting filled up enormously within a few days. I've added another dbf file when it was filled up completely earlier. Now, the second .dbf is also about to fill. The only process that's happening on daily basis is an export database for logical backup. Also, tablespace auto extent is ON. What might be the reason for this tablespace fill up? How can I tackle this situation?
I'm using Oracle 11g r2 in archivelog mode.

Attaching result for below query:

SELECT owner, segment_name, segment_type, extents, blocks, bytes
FROM dba_segments
WHERE tablespace_name='SYSTEM'
ORDER BY bytes;

First of all, make sure that SYSTEM tablespace is not assigned as default or temporary tablespace for normal database users.

Second of all, one of the space consuming table in SYSTEM tablespace is AUD$. Next, as you have said you are just performing export/import, datapump creates master tables in the default tablespace of the user performing export job.

The master table is either retained or dropped, depending on the circumstances, as given here. You might have done the export using SYS user. If the job is failed master table will not be dropped.

Now, you can use the following query to see which object is consuming most of the space on this tablespace.

SELECT owner, segment_name, segment_type, extents, blocks, bytes
FROM dba_segments
WHERE tablespace_name='SYSTEM'
ORDER BY bytes;

Update:

If you need the audit trail then copy its records to another table, another tablespace if needed, and/or export the audit table, otherwise you can truncate it.

Login as sqlplus sys as sysdba

SQL> CREATE TABLE backup_aud$ AS SELECT * from sys.aud$;
SQL> truncate table aud$;

OR

You can moveaudit trail table, SYS.AUD$, to another tablespace using DBMS_AUDIT_MGMT
 Create new tablespace
 Move the audit trail tables using procedure DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'NEW TABLESPACE');
END;
/


Speed up slave replication on large database / quickly spawn slave We have a large MySQL database (about 100GB on disk) that we would like to spawn a slave for.

Our typical process for creating a slave is:

Create a slave server
Load in a mysql dump of the master
Start the slave
Wait for slave replication to sync w/ master
Place slave server in production

The problem we are having is with the size of the database.  By the time step two above is completed, the slave is so far behind the master that it is unable to catch up.  In fact, the slave replication grows further and further behind.  I'm not sure why this happens.

Any ideas on how to either seed the slave server faster, or fix the replication issue?  Note that the master server has multiple databases, and a mixture of InnoDB/MyISAM tables.  The server is running MySQL 5.1

There are two problems here, which must be solved independently.

Creating a slave

With that size, 100GB, mysqldump is usually to slow too be done efficiently. Try using a binary backup. You have several options: @paul is telling you one, but it has the inconvenient that the master will be locked for the duration of the copy process. Additionally, rsync can be very efficient if you have multiple small files, but it may not be for larger file sizes changed randomly throughout it (if you have a large ibdata1).

My recommendations would be snapshoting (if you are using virtual machines or a filesystem that allow it: ZFS, any other on top of LVM, etc.) or Percona Xtrabackup/Oracle Enterprise Backup. These options will make the backup process almost as fast as copying files from the filesystem without almost no lock at all. Some of them also allow for parallel copy, if your bandwidth allows it.

If none of these work for you, try using a logic parallel backup/restore utility like mydumper.

Replication with increasing lag

You must discover why that is happening first, (profile your queries on both servers) but these are some of the most common causes:

Use hardware/resources at least as good or better that the master for your slave. If the slave is slower, as replication runs in a single thread (mostly), the slave will lag.
If you have long-running transactions in concurrency, try using the binlog_format = ROW. It can augment your bandwidth usage but reduce the slave's load.
Try upgrading the MySQL versions, if that is a possibility. There has been a lot of performance improvements in terms of query execution and binary log in later versions. For example, one reason why you may be lagging is because the buffers are not hot on the recently-started slave. That is mitigated partially in the latest versions. Also, multiple-thread replication execution has been somewhat integrated.
You can relax some integrity configurations on the slave, as in the case of a crash, you can always re-import from the master in the event of the crash (innodb_flush_log_at_trx_commit, for example).
As a last resort, you can try alternative protocols that allow more synchronized communication between servers, but that is usually involves much more work.


User and login mapping after an SQL Server restore I need to restore a DB from an SQL Server instance to another and I am well aware that the DB users will be transferred but the logins won't. I have created a script to be able to create the logins on the destination SQL Server instance, including the password hashed and the login SID. However, I am uncertain if it makes a difference whether the logins are created on the destination instance prior to the DB restore or after the DB is restored.

If the logins are created first and then the DB is restored, the DB users will map with the existing corresponding logins accordingly due to SID, however, if I do the DB restore and create the logins afterwards, will they still map automatically or will one need to do an ALTER USER WITH LOGIN or similar?

It doesn't matter when the logins are created.  As long as the SIDs match, the database user will be associated with the login.

Every time a user attempts to connect to a database, SQL Server is going to check for a mapped login (unless it's a contained database).  It does this by checking to see if there is a login with a SID that matches the user's SID.  Accordingly, it doesn't matter when the login was created because it will be matched at the the time the user attempts to connect to the database.


Concatenation behaves differently on two servers I have two servers (not master/slaves of each other), each running MySQL 5.1.71-log.

One server 1 (which happens to be in the US), the following query returns good results:

select distinct(devicemode || ' - ' || model || ' - ' || version) as a from <TABLE1>, <TABLE2> where <TABLE1>.<FKEY> = <TABLE2>.key and <TABLE1>.key = 1234 order by devicemode ,model,version;

The results look like:

US - MicroMaxx - 30.80.306.024

When the same query is run on server 2 (which happens to be in the UK), I get different results:

0

If I change the query to be:

select distinct(concat(devicemode, ' - ', model,' - ', version) as a from <TABLE1>, <TABLE2> where <TABLE1>.<FKEY> = <TABLE2>.key and <TABLE1>.key = 1234 order by devicemode ,model,version;

the same results are returned on both servers:

US - MicroMaxx - 30.80.306.024

Any idea why there would be a difference?

Thanks

Alan

Most probably the two servers have different SQL_MODE settings.

One has PIPES_AS_CONCAT defined and the other not. The server that hasn't this setting, treats || ("pipes") as a synonym to OR and not as the string concatenation operator.


Do I need to set "ConnectionReset" parameter in the connection string when using isolation levels occasionally? I have a MySql 5.6.44, and an dotnet application using Mysql.Data 8.0.12.

The application code has not specified any isolation level till now because the default one was enough. Suddenly we need to do a SERIALIZABLE transaction in a new feature, and only the transactions in that part of the code specify such level.

In dotnet, connections to the DB are pooled, so after the connection usage is over, the connection gets back to the pool. I have the concern, that the isolation level set for that operations remains in the connection affecting other transactions in the code which do not need such high level.

According to the component documentation, there is an optional connection string parameter that indicates if the connection state needs to be reset:

  ConnectionReset , Connection Reset Default: false

  If true, the connection state is reset when it is retrieved from the
  pool. The default value of false avoids making an additional server
  round trip when obtaining a connection, but the connection state is
  not reset.

Checking the decompiled component code, it seems that transaction levels are set with SESSION scope (MySqlConnection.cs:1238):

MySqlTransaction mySqlTransaction = new MySqlTransaction(this, iso);
MySqlCommand mySqlCommand = new MySqlCommand("", this);
mySqlCommand.CommandText = "SET SESSION TRANSACTION ISOLATION LEVEL ";
switch (iso)
{
  case System.Data.IsolationLevel.Chaos:
    this.Throw((Exception) new NotSupportedException(Resources.ChaosNotSu
    break;
  case System.Data.IsolationLevel.ReadUncommitted:
    mySqlCommand.CommandText += "READ UNCOMMITTED";
    break;
 ...

According to MySql documentation about transactions:

  With the SESSION keyword:

  The statement applies to all subsequent transactions performed within
  the current session.

So if I understand correctly, the isolation level set for one operation will affect other operations in our code that are not specifying an isolation level themselves, since the connections are being returned to the pool without reseting.

So being this the case, I should use the "ConnectionReset" parameter in our connection strings, am I right?

PS: The open source MySql connector (which we do not use) seems that is setting true by default in the ConnectionReset parameter by default, which makes total sense to me.

Probably the following allows you to use any isolation mode for your application:

BEGIN;
SELECT ... FOR UPDATE;   -- necessary if about to change a row
...
UPDATE ...;
UPDATE ...;
COMMIT;


Privileges to select on a view - Issue I have two schemas: A and B.

A has one table with the name 'TEST'. B has created a view 'BASE' which does select on table 'TEST'.

Now I create another user C to which I grant, select on the view 'BASE'.

When the user C runs the following statement, he gets insufficient privileges error:

select * on B.BASE;

What privileges is required to select the contents of the view?

You need to grant select on the tables in schema A to the user B WITH GRANT OPTION.

From the documentation:

  To grant SELECT on a view to another user, either you must own all of
  the objects underlying the view or you must have been granted the
  SELECT object privilege WITH GRANT OPTION on all of those underlying
  objects. This is true even if the grantee already has SELECT
  privileges on those underlying objects.


Monitor Database Memory usage SQL Server In our PROD environment, there are two servers on which we have system critical database. The management requirement is to monitor these databases for high memory usage or access or long running queries, etc. My requirement is to create a SQL Job for this purpose and if any spikes are observed for this database an event gets generated. So that we can take corrective actions, before the sites hosted on these Dbs slows down. Please don't recommend using any third party tools for this purpose.

Also, I have come up with one idea, using the following query:

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters
   WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
   AND counter_name = 'Total Pages';

;WITH src AS
(
   SELECT
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [db_name] = CASE [database_id] WHEN 32767
       THEN 'Resource DB'
       ELSE DB_NAME([database_id]) END,
   db_buffer_pages,
   db_buffer_MB = db_buffer_pages / 128,
   db_buffer_percent = CONVERT(DECIMAL(6,3),
       db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

The output of the above code is BUFFER USAGE (MB) and % BUFFER for each database, except system Dbs. Planning on creating a job which will keep on monitoring the output and check if the Buffer usage goes beyond a threshold.

Kindly suggest something or give comments about my idea.
Thanks in advance

You can use the below query to determine the Memory usage at database level:

SELECT

(CASE WHEN ([database_id] = 32767)
    THEN N'Resource Database'
    ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
COUNT (*) * 8 / 1024 AS [MBUsed],
SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
 FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id];
   GO

If you want to analyse further at object level that which object in that database from above query is using a lot memory use below query:

 EXEC sp_MSforeachdb
N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name]
FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ''?'')
BEGIN
 USE [?]
SELECT
''?'' AS [Database],
OBJECT_NAME (p.[object_id]) AS [Object],
p.[index_id],
i.[name] AS [Index],
i.[type_desc] AS [Type],
--au.[type_desc] AS [AUType],
--DPCount AS [DirtyPageCount],
--CPCount AS [CleanPageCount],
--DPCount * 8 / 1024 AS [DirtyPageMB],
--CPCount * 8 / 1024 AS [CleanPageMB],
(DPCount + CPCount) * 8 / 1024 AS [TotalMB],
--DPFreeSpace / 1024 / 1024 AS [DirtyPageFreeSpace],
--CPFreeSpace / 1024 / 1024 AS [CleanPageFreeSpace],
([DPFreeSpace] + [CPFreeSpace]) / 1024 / 1024 AS [FreeSpaceMB],
CAST (ROUND (100.0 * (([DPFreeSpace] + [CPFreeSpace]) / 1024) / (([DPCount] + [CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS [FreeSpacePC]
    FROM
   (SELECT
    allocation_unit_id,
    SUM (CASE WHEN ([is_modified] = 1)
        THEN 1 ELSE 0 END) AS [DPCount],
    SUM (CASE WHEN ([is_modified] = 1)
        THEN 0 ELSE 1 END) AS [CPCount],
    SUM (CASE WHEN ([is_modified] = 1)
        THEN CAST ([free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [DPFreeSpace],
    SUM (CASE WHEN ([is_modified] = 1)
        THEN 0 ELSE CAST ([free_space_in_bytes] AS BIGINT) END) AS [CPFreeSpace]
FROM sys.dm_os_buffer_descriptors
WHERE [database_id] = DB_ID (''?'')
GROUP BY [allocation_unit_id]) AS buffers
 INNER JOIN sys.allocation_units AS au
ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
 INNER JOIN sys.partitions AS p
ON au.[container_id] = p.[partition_id]
 INNER JOIN sys.indexes AS i
ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100 AND ([DPCount] + [CPCount]) > 12800 -- Taking up more than   100MB
  ORDER BY [FreeSpacePC] DESC;
  END';

Use the querys above in SP's to run at a time period to collect the data you require and this would help you analyse better!


Created databases and inserted data on MySQL slaves in Master-Slave replication - how to fix? I got MySQL Master-Slave replication running on 1 master with 2 slaves. SHOW MASTER STATUS / SHOW SLAVE STATUS says they are in sync, but databases have been created on the slaves and filled with data which of course cannot be seen on the other slaves/master, because no replication from Slave to Master happened.

Is there an easy way to fix this situation? Two outcomes would be acceptable for me, but I'm not sure what is the best way to achieve this (sorry, DBA noob):

No replication at all, but no data lost on each server in its current state (replicated data stays in its current state, new data on the slave stays there, too)
Converting Master-Slave to Master-Master-Replication so every server has the same data.

You say:

  databases have been created on the slaves and filled with data.

The easiest way of correcting this is to take a mysqldump of the specific databases on the slave, drop the database on the slave, and import the dumpfile onto the master. The slaves will then replicate the data.

This of course gets trickier if it is not whole databases or even tables (but specific rows inside a table).


Join query result of 2 different tables based on common column I want to join the result of two different queries from two different tables based on a common column (which is named differently in the two tables) but I cant seem to figure out the easy way to do it. I'm using psycopg2 on python 3.5.

From the first table, I need the Count of meetings. So I do:

SELECT   user_id,COUNT(1) as num
FROM     tasks
WHERE    created_at BETWEEN %s and %s
GROUP BY user_id """,(DAYS_AGO_7_CONFIG,YESTERDAY))

This gives me

UserID | Number
1      |  123
2      |  12

UserID is my mutual column. Now I made userID into a list by list comprehension, then I do:

SELECT id, given_name,family_name,email FROM users WHERE id in +str(userID))

and I get the id, given_name, family_name, and email fields from the second table.

Now I need to add the Number column into this result, which I'm doing by very inefficient non-DB means (pandas library in python)

I'm pretty sure this entire thing can be done in one query (perhaps with union?), but I cant seem to work it out.

Intended output:

id | given_name | family_name | email | Number

You can do it with:

SELECT   u.id, u.given_name, u.family_name, u.email, COUNT(1) as num
FROM     users u JOIN tasks t
          ON (u.id=t.user_id)
WHERE    t.created_at BETWEEN %s and %s
GROUP BY u.id, u.given_name, u.family_name, u.email, (DAYS_AGO_7_CONFIG,YESTERDAY))

To address the question in the comments, you can add a HAVING clause after GROUP BY to limit the results to those where num is greater than 5:

HAVING   COUNT(1)>5


Database defragmentation and autogrowth settings We do have some maintenance plan for our SQL Server 2008 r2 express.
Every month we do de-fragmentation of the database if any table has page count more than 50 for any table and average fragmentation more than 20.

If the database log size>2 MB, then the recovery mode is made as simple, and it is shrinked, and the recovery mode is set back to FULL.
If the Page_count>50 and avg_fragmentation_in_percent > 30 then the index is REBUILD.

And if Page_count>50 and avg_fragmentation_in_percent > 5 and <30 then the index is REORGANIZE.

This is what we are doing till now.But we found that autogrowth events are resource intensive and it should not happen repeatedly. Now for all database autogrowth is set to MB for mdf file and 10% for ldf file which is default value while creating new database.We are planning to increase the autogrowth values for the database depending on how much database is getting bigger every day.But i want to know how much autogrowth events is ideal for the database.Should i set autogroth so that it happens only once a day,week or month etc.So please help me to set the autogrowth value for my database.
Also there is another problem if I do monthly de-fragmentation of  database then it will be shrinked. So after this for all database for which i did shrink autogrowth occurs once when new data is written to it.So there will be so many autogrowth events. So whether it will be a problem?
Please tell me a solution.

if any table has page count more [than] 50 [pages]

That is still extremely small.  I typically don't take fragmentation into account for any index with less than 1000 pages.

  and avg_fragmentation_in_percent > 30 then the index is REBUILD

That's a good threshold for rebuilds, but I'd also consider reorganizing indexes with fragmentation in the range of 5% to 30%.

  If the database log size>2 MB, then the recovery mode is made as simple, and it is shrinked, and the recovery mode is set back to FULL

Why are you doing this?  Why is it a problem if a log file is greater than 2 MB?  That's extremely small.  Not to mention, when you switch to SIMPLE, shrink, and then back to FULL recovery you are breaking the log chain.  You could get yourself into serious trouble for point in time recovery.  Size your log files appropriately, and allow for autogrowth only in the case of emergency.  Shrinking a database file should only be done in extremely corner cases, and by no means part of a routine maintenance schedule.

  But we found that autogrowth events are resource incentive and it should not happen repeatedly

Correct.  That is where sizing comes into play.  Monitor and get alerted when a certain percentage of space is being used.  That way you can plan your manual file growth operations for a maintenance window to minimize end-user impact.

  and 10% for ldf file which is default value while creating new database

Even though a percentage file growth is default, I recommend against it.  You don't want to have a variable amount of file growth.  If you must fall back on autogrowth, then you want to know exactly how much the file will grow by.  Not to mention, with growth of a log file you will have a variable amount of virtual log files (VLF) with the additional space.  You want to smartly plan this, and this can be done through a fixed amount of file growth.

  Should i set autogroth so that it happens only once a day,week or month etc

Set autogrowth for a fixed amount of space (not percentage), and only you can determine what is a good amount.  Too small, you will have frequent autogrowth with minimal end user impact, but happening often.  Autogrowth set too high, then you'll have less frequent growths but when it does happen the impact will be for a longer duration.

My recommendation?  Monitor the space used in your database files.  When it hits a certain percentage (say, 80% space used) you should be alerted.  And then schedule a manual growth during a maintenance window.


BULK INSERT Data from file but ignore the first occurence of field terminator I have a file that has data arranged in the following style:

    1    1    0.5102    0.4898    0.0000    0.0000    0.0000
    1    2    0.3114    0.6886    0.0000    0.0000    0.0000

I need to load this into a table on MS-SQL and process it. I am using the BULK INSERT statement with the following settings

FIELDTERMINATOR = '    '
ROWTERMINATOR = '\n'

But what happens is that I end up with a table having this content (NOTE: I made all columns VARCHAR to see how the data is getting imported):

COL1    COL2    COL3    COL3    COL5    COL6    COL7
NULL    1       1       0.5102  0.4898  0.0000  0.0000    0.0000
NULL    1       2       0.3114  0.6886  0.0000  0.0000    0.0000

Essentially, there is a '    '(four spaces) before the first column of data. But BULK IMPORT assumes that the first column is NULL and offsets the data by a column.

Now, my question - is it possible to obviate this issue? Can we instruct BULK INSERT to ignore the first occurrence of the FIELD TERMINATOR? I cannot change the input file as it is used by some other parts of the process.

If the amount of data is small enough or you can otherwise afford to spend a bit more resources on the operation, there are ways around this. Most of the scenarios where I've done bulk inserts required additional data manipulation in any case, so I usually just insert the bulk data to a specially created bulk insert table, and then manipulate and transfer the data to the real table from there. Makes tasks such as this one a lot easier. But of course there's an increased overhead from handling the same data twice.


Joining two ltree based tree tables recursively I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join them to each other – and bring in all the relations – in a reasonably efficient way?

In this system, the ancestry determines the capabilities of a role or permission. A role below building_access will inherit the rights and permissions of its parent. I can quite easily query roles or permissions and determine the entire line of ancestry for a given record.

What I am trying to do is query a role and determine the complete tree of permissions for that role. I am also trying to query all roles and determine the same list of permissions, for each and every role, aggregating it into a string.

SELECT r.name AS "role", CONCAT('["', string_agg(p.name, '", "' ORDER BY p.id), '"]') AS "permissions"
FROM roles r
JOIN roles_permissions rp ON rp.role = r.id
JOIN permissions p ON p.id = rp.permission
WHERE r.path @> 'company_employee.warehouse_employee'
GROUP BY r.id;

Actual Results:
---------------------+-------------------------------------------------
        role         |                   permissions
---------------------+-------------------------------------------------
 company employee    | ["building access", "break room access"]
 warehouse employee  | ["warehouse access", "warehouse stock access"]

Intended Results:
---------------------+-------------------------------------------------
        role         |                   permissions
---------------------+-------------------------------------------------
 company employee    | ["building access", "break room access"]
 warehouse employee  | ["building access", "break room access", "warehouse access", "warehouse stock access"]

This query functions to give me the direct permissions for every role, but it doesn't get the ancestors of those permissions to establish the actual total permissions for the role.

My sample database is as displayed below:

CREATE EXTENSION ltree;
CREATE EXTENSION tablefunc;

CREATE TABLE roles (
  id   int PRIMARY KEY,
  name text NOT NULL,
  path ltree
);
CREATE INDEX roles_path_idx ON roles USING gist (path);

INSERT INTO roles (id, name, path) VALUES
  (1, 'company employee', 'company_employee'),
  (2, 'warehouse employee', 'company_employee.warehouse_employee'),
  (3, 'warehouse manager', 'company_employee.warehouse_employee.warehouse_manager');

CREATE TABLE permissions (
  id int PRIMARY KEY,
  name text NOT NULL,
  path ltree
);
CREATE INDEX permissions_path_idx ON permissions USING gist (path);

INSERT INTO permissions (id, name, path) VALUES
  (1, 'building access', 'building_access'),
  (2, 'break room access', 'building_access.break_room_access'),
  (3, 'warehouse access', 'building_access.warehouse_access'),
  (4, 'warehouse stock access', 'building_access.warehouse_access.warehouse_stock_access'),
  (5, 'warehouse security access', 'building_access.warehouse_access.warehouse_security_access');

CREATE TABLE roles_permissions (
  role       int REFERENCES roles,
  permission int REFERENCES permissions,
  PRIMARY KEY (role, permission)
);

INSERT INTO roles_permissions (role, permission)
VALUES (1, 1), (1, 2), (2, 3), (2, 4), (3, 5);

Inner join returns all ancestors of an specific path, I've used array_agg(distinc to avoid duplicates.

        select
            array_agg(distinct p2.name) permissions
        from
            permissions p1
        join
            permissions p2
            on  p2.path @> p1.path

Then I've used it for each role that match the path company_employee.warehouse_employee

select r.name,
       (select array_agg(distinct p2.name) permissions
        from   permissions p1
        join   permissions p2 on  p2.path @> p1.path
        join   roles_permissions rp on rp.permission = p1.id
        where  rp.role in (select distinct r2.id
                           from   roles r1
                           join   roles r2 on r2.path @> r1.path
                           where  r1.id = r.id)) permissions
from   roles r
where  r.path @> 'company_employee.warehouse_employee';

| "company employee"   | "{"building access","break room access"}"                         |
|----------------------|-------------------------------------------------------------------|
| "warehouse employee" | "{"building access","break room access","warehouse access","warehouse stock access"}" |

rextester here

As Evan Carroll has pointed out

If a role X.Y.Z inherits permissions from X.Y and hence from X
And a permission A.B.C inherits from A.B and from A

Your bridge table should avoid to assign those rows twice.

Given next data:

ID | Roles    ID | Perm
---+-----     ---+------
1  | X        1  | A
2  | X.Y      2  | A.B
3  | X.Y.Z    3  | A.C
              4  | A.C.D
              5  | A.C.E

Your bridge table joins (1,1) and (1,2):

X => A            <-- Redundant
X => A.B ~> A

In this case (1,1) is redundant because A permission is given by inheritance on A.B

And same for (2,3), (2,4):

                           Inherits from role
X.Y => A.C                 ~> X ~> A.B ~> A     <-- Redundant
X.Y => A.C.D ~> A.C ~> A   ~> X ~> A.B ~> A


How to trigger newlines in log messages? Is it possible for log messages to span multiple lines? I am expecting:

RAISE WARNING 'Line 1\nLine 2';

to result in the following log:

Line 1
Line 2

But instead I get:

Line 1\nLine 2

Any ideas?

It's the same as any other string. Either use non-standard E'' strings to tell PostgreSQL you want escape processing:

RAISE WARNING E'Line 1\nLine 2';

or use SQL-standard strings with literal newlines:

RAISE WARNING 'Line 1
Line 2';


Snapshot_isolation_state_desc NOT turning off I am trying to resolve the following issue and need your help.

We wanted to turn on is_read_committed_snapshot_on and Snapshot_isolation_state_desc ON on our db!

When I ran following command to check current status of both above values, I got

snapshot_isolation_state_desc  = ON &
is_read_committed_snapshot_on = 0.

After this I ran two ALTER commands,

ALTER DATABASE [<databasename>]
SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE <Your crm database>
SET ALLOW_SNAPSHOT_ISOLATION ON

The values updated to following,

snapshot_isolation_state_desc  = ON &
is_read_committed_snapshot_on = 1.

So we did some test we still got some locks but they we different type not the SHARED LOCK. after that I wanted to turn OFF and see if we go back to normal or not.

and I ran,

 ALTER DATABASE [<databasename>]
    SET READ_COMMITTED_SNAPSHOT off

    ALTER DATABASE <Your crm database>
    SET ALLOW_SNAPSHOT_ISOLATION off

and we got,

snapshot_isolation_state_desc  = OFF &
is_read_committed_snapshot_on = 0.

And we wanted to turn it ON again and leave it in ON status I ran,

ALTER DATABASE [<databasename>]
SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE <Your crm database>
SET ALLOW_SNAPSHOT_ISOLATION ON

BUT the thing is, it is still showing the following values,

**snapshot_isolation_state_desc  = OFF &**
is_read_committed_snapshot_on = 1.

and that's my issue, that why it's still in OFF status. I restarted SQL Services many times restarted App. Server but no luck.

Please help.

Thanks.

I found another way to turn ON snapshot_isolation_state_desc . Go to your DB, right click go to properties and find "Options" under Select a page on left side and then look for "Miscellaneous" option and you will see the first variable names as "Allow Snapshot Isolation" and just select the "True" and hit OK. it should be good to go. When I am running following query now to select the status, I get following.

snapshot_isolation_state_desc  = ON &
is_read_committed_snapshot_on = 0.


How to put the below data in 3NF? I need to put the data from the diagram in 3NF (third normal form)

Assuming that your table is for storing Sales Record, we can do the following:

For 1st Normal Form:

Eliminate columns with duplicate (not really eliminate but rather re-arrange).

I just separated the columns with repeating data and made them new rows.

For 2nd Normal Form

The table should be in 1NF
Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
Create relationships between these new tables and their predecessors through the use of foreign keys.

Created new table for the Seller, Customer, Product, Rating and the Records.
Seller No., Cust ID, Prod ID and Prod Rating are their primary keys (which is foreign keys for Sales Record Table)

For 3rd Normal Form

The table should be in 2NF
Remove columns that are not dependent upon the primary key.

I usually encounter this form when dealing with addresses. As you can see i added the table City and used Zip Code as a foreign key.
I also added a primary key in Sales Record table (Record ID).

You can read about normalization here and here.

Last word:
Normalization is good for reducing data redundancy and ensuring that their relationships is used. Useful with JOINs but can be a headache when there is too much data and tables.


Can Oracle Heterogeneous Services be used to connect to a SQL Azure database? I would like to be able to use a database link from an Oracle 10g database to a SQL Azure database.

Will Oracle Heterogeneous Services support this?

I can see various articles relating to connecting to SQL Azure via ODBC, so does this mean that it will be possible to do it using Oracle HS, or am I oversimplifying things?

If there is an ODBC driver to connect to it, then the Oracle Transparent Gateway for ODBC should work just fine.


How can I identify and fix bottlenecks in an Oracle11gR2 import I've recently been tasked with moving our Oracle 11g database from a linux CentOS desktop to a windows 2008 server. Given I've not done much behind the scenes stuff with oracle before I'm a bit out of my depth.

I did a full expdp from the source server and imported it to the target server (i built tablespaces before the load but nothing else).

Everything went well enough (a few errors that were related to having a different global_name which, from documentation, appeared ignorable.

The problem I am having is that the new server appears to take longer to run queries compared to the old server. This is in spite of more cores and more RAM. I think the problem is due to I/O limitations as the old server ran on an SSD.

I get the same 'explain plans' on queries yet getting through the blocks on v$sql_longops just seems to take longer... Can anyone provide details on what steps to take to check and compare output so I can try to ascertain the underlying issue?

Depending on the edition (enterprise, standard, express, etc.), the licensed options (particularly the Performance and Tuning Pack), and whether you have installed Statspack, I would start by taking an AWR or Statspack report for both system when they are executing a similar workload and compare them.  If you are licensed to use the AWR (note that querying the AWR tables violates your license agreement if you aren't), use an AWR report.  Otherwise, assuming you (or the prior DBA) installed Statspack, use that.  Otherwise, you'll have to install Statspack in both systems.

If your guess is correct, I/O is the bottleneck, and the problem is that the I/O subsystem on the new server is slower than the I/O subsystem on the old server, you would expect that the top wait events in the report on the new server would be primarily I/O, that the amount of I/O would be consistent across the reports, and that you'd see that individual I/O operations on the various tablespaces and data files were consistently slower on the new system.  Otherwise, comparing the reports should tell you what the bottleneck is (though an AWR report has a ton of information and a comprehensive discussion of interpreting an AWR report is a book not a forum post).


Exported data become unknown character May I know how to export MySQL data and prevent the data display a weird character?

This table uses to store the record about crawling information. It stores IP, PatternSignature, status, updated and PTR.

But whenever I export out the table, all the IP will become an unknown character which unable to import into database.

May I know how to rectify this issue?

Do not put that kind of data in VARCHAR or TEXT; use only VARBINARY or BLOB.  Please provide SHOW CREATE TABLE so we can discuss in more detail.

Use --hex-blob on mysqldump.

If you are "exporting" by some other means, explain.


Primary Key At the End of Foreign Key index in MySQL is in order for sure? I am aware that any Index in MySQL Innodb contains primary key value in the end of it. If there was a table as follows.

create table BaseTbl (
    base_id int not null primary key auto_increment,
    base_name varchar(30)
);

create table Tbl (
    record_id int not null primary key auto_increment,
    base_id int not null,
    foreign key (base_id) references BaseTbl(base_id)
);

Table Tbl would have an index on base_id because that is FK. And that would implicitly have record_id as the final key part. Would that be ordered?

Example:

If the data was

BaseTbl
+---------+-----------+
| base_id | base_name |
+---------+-----------+
|       1 | Name1     |
|       2 | Name2     |
|       3 | Name3     |
+---------+-----------+

Tbl
+-----------+---------+
| record_id | base_id |
+-----------+---------+
|         1 |       1 |
|         2 |       2 |
|         3 |       1 |
|         4 |       1 |
|         5 |       1 |
|         6 |       2 |
|         7 |       2 |
|         8 |       1 |
|         9 |       3 |
|        10 |       1 |
|        11 |       2 |
|        12 |       3 |
+-----------+---------+

Would the base_id FK index's record_id part will it be ordered? Like this?

+---------+-----------+
| base_id | record_id |
+---------+-----------+
|       1 |         1 |
|       1 |         2 |
|       1 |         3 |
|       1 |         4 |
|       1 |         5 |
|       2 |         6 |
|       2 |         7 |
|       2 |         8 |
|       3 |         9 |
|       3 |        10 |
|       3 |        11 |
|       3 |        12 |
|       3 |        13 |
|       3 |        14 |
|       3 |        15 |
+---------+-----------+

It seems like it is ordered, as I did not see 'using FileSort' when I explicitly tried mentioning Order By on record_id. But I'd like a confirmation or point in documentation that confirms this. (I failed to find it myself).

FK is a constraint.  To facilitate checking that constraint, an INDEX is created.  Run SHOW CREATE TABLE for confirmation.  An InnoDB INDEX is expanded to include PK column(s).  Also, if there is an index with the leftmost column(s) matching the FK's column(s), the FK is happy.

When doing a SELECT, the Optimizer has many possible ways to do the query.  If you do not have an ORDER BY, then you must not assume the output is in any particular order.  The Optimizer may not have chosen the index you expect, or there could be a GROUP BY that is performed by first sorting, or the GROUP BY is done by hashing, hence randomly ordered.  Etc.

"Filesort" is a misnomer in two ways.  It may not involve a "file"; that is it might be done in RAM.  And it may need to be plural -- For example, GROUP BY a ORDER BY b is likely to involve two sorts.  EXPLAIN FORMAT=JSON ... is clearer on this than a plain EXPLAIN.

So, yes, the index for the FK is effectively as you describe (2 columns, ordered).  But so what?  Don't trust it; tack on an ORDER BY if you need the ordering.  As I mentioned above, the Optimizer is free to ignore that index.

A tip:  If you want to make use of both columns of the 'index' in question, then explicitly specify INDEX(base_id, record_id).  The FK should see that and say "That index is all I need; I won't make another one".  Humans reading your code will say "Hmmmm, the schema designer needs these two columns indexed for some reason".


mongodb disable write access (while db migration) I want to migrate from one cluster to another. So I'm goint go make a backup with mongodump and mongorestore. Db is not so big, so I would like to disable writes on source db before the start to exclude accidental writes from apps.

What would be the best way to do this?

There are two similar options available, based on user permissions.

For the user which your application uses to connect to the DB (let's call it applicationConnUser), change its permissions: for example if it currently has the readWrite role, revoke that and grant the read role instead.
Set up a different user - leave applicationConUser unchanged and create a new account applicationConUserReadOnly to which you grant the read role - and reconfigure your application to connect with this account instead.

Note that your application may not react helpfully when its normal operations cannot write to the database; depending on how it is coded, it may crash out as soon as it gets a db error.


Cannot Delete FileStream Group from Sql Server 2008 We have migrated all our varbinary(max) column data to Azure Blob Storage and so we want to remove the old filestream columns that remain in our Sql 2008 database and the filestream filegroup but when we try to we are getting the error:

Msg 5042, Level 16, State 11, Line 2
The filegroup 'FileStreamGroup' cannot be removed because it is not empty.

However when we run this:

exec sp_helpfilegroup 'FileStreamGroup'

It is returning this:

groupname           groupid    filecount
FileStreamGroup     2          0

So the file count is 0 but it won't let us remove it, has anybody else had this problem and how on earth do you completely remove the filestream from the database.

In addition this query:

select * from sys.tables t
join sys.data_spaces ds on t.filestream_data_space_id = ds.data_space_id

Returns 0 rows so no tables are using any filestream data if I understand this correctly.

I had the same problem.  Eventually I worked out that I needed to change the db recovery model from "Full" to "Simple", and then I could delete my filestream files.


Unable to display parent-child heirarchy I am creating a parent-child hierarchy in my dimension.
The AdventureWorks2012DW sample database is used.
I have set the ParentEmployeeKey, NameColumn property to LastName as per the wrox sql server 2012 book.
I have attached the Dimension Browser Image.

I am  getting the Employee Key instead of LastName

I just had to change the Employee Display NameColumn to LastName.
And it worked!!! Thanks!!


Does READ UNCOMMITTED have the same effect as AUTOCOMMIT=1? AFAIK running transactions as READ UNCOMMITTED means that there is no lock, so every change caused by a transaction is immediately visible to other transactions.

Is it correct to assume that this is the same behaviour we get with any transaction if we enable AUTOCOMMIT? AFAIK in this case, as well, the effects of any query are immediately visible to others.

Of course, the two concepts are somewhat different, semantically, but is there any difference in what's actually happening?

I'm interested in a general answer, though I do tend to care more about MySQL and Microsoft SQL Server.

This is an answer assuming MySQL/InnoDB. Visibility rules are slightly different on Oracle:

No, as you say, SET autocommit = 1 (default behavior) is that every single query is like if it has a START TRANSACTION; ... COMMIT; around it, committing after every query. That is different from READ UNCOMMITTED (which doesn't mean that there is not a lock). InnoDB will always lock the row affected during its operation, the difference is that as the transaction commits just after the statement is executed, the lock will be released just afterwards in all cases.

However, even if you are using autocommit=1, rollback area cannot be purged inmediately when using tx_isolation > READ COMMITED, because other long-running transactions may need those values.

To show you the difference, I can show you an example:

session1> create table test (id int primary key, c varchar(20)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.15 sec)

session1> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

session2> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

session1> select * from test.test;
Empty set (0.04 sec)

session2> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

session2> insert into test values (1, 'a');
Query OK, 1 row affected (0.10 sec)

session1> select * from test.test;
Empty set (0.04 sec)

Even if the other session is is using autocommit = 1; session 1 can't see the changes of session 2 because a transaction is ongoing. Also:

session1> create table test (id int primary key, c varchar(20)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.15 sec)

session1> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

session2> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

session1> select * from test.test;
Empty set (0.04 sec)

session1> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

session1> select * from test.test;
Empty set (0.04 sec)

session2> insert into test values (1, 'a');
Query OK, 1 row affected (0.10 sec)

session1> select * from test.test;
Empty set (0.04 sec)

Session 2 cannot see the new value autocommited in session 2 becase start transaction + select is like if you had executed start transaction with consistent snapshot, so in InnoDB's default level "REPEATABLE READ", it cannot be seen even if it has (auto) commited on the other session.

Of course, if you only use autocommit mode, and no longer transactions in any connection, READ COMMITED and READ UNCOMMITED are equivalent, but not because it changes its visibility, but because you commit every single query. Things also get more complex if you throw other non-transactional engines into the mix.

As a side effect, mysql 5.6 changes InnoDB autocommit SELECT into read-only transactions, boosting its performance a bit.


SQL Server - Login failed. The login is from an untrusted domain and cannot be used with Windows authentication I am getting below error while trying to connect SQL from remote server. Local login is working fine. Can anyone help on this. Thanks in advance.
"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.186.12.10]"
My environment :
Client - already test AD/DNS domain joined
Remote SQL Server - already prod AD/DNS domain joined
I have running inside test AD/DNS server isolated environment. No relationship between test AD/DNS server and prod AD/DNS server. Test domain name : contoso.com Otherwise , PROD domain name : contoso.com
Test AD/DNS Server : 10.190.10.1
Prod AD/DNS Server : 10.150.10.1
Do I have to join prod AD/DNS client machine as well ? I mean , is there any workaround solution ?
Thanks in advance,
Last Update:
Solved my issue.

I believe the issue is the client machine's domain isn't trusted by the remote server when logging in via Windows Authentication in SSMS. You should still be able to login with your AD credentials from your local SSMS client by switching the Authentication mode from Windows Authentication to Azure Active Directory - Password.
If you run into the subsequent error "The certificate chain was issued by an authority that is not trusted" then you can tell SSMS to trust it manually by clicking the "Options" button on the Login window. Then under the "Connection Properties" tab, check off the "Trust server certificate" checkbox. Then click the "Connect" button.
Screenshot Example


RDS raising unwanted PostGIS NOTICEs I have an Amazon RDS instance that is producing unwanted NOTICEs.
I have never seen this behaviour before.

mydb=> select current_setting('client_min_messages');
 current_setting
-----------------
 notice
(1 row)

Now, if I ask for geometry column... I get the NOTICEs:

mydb=> SELECT id, ST_AsText(geom) FROM mytable limit 1;

NOTICE:  [lwgeom_ogc.c:LWGEOM_asText:833] Called.
NOTICE:  [g_serialized.c:gserialized_get_type:50] entered
NOTICE:  [g_serialized.c:lwgeom_from_gserialized:1137] Got type 1 (Point), srid=4326
NOTICE:  [g_serialized.c:lwgeom_from_gserialized_buffer:1091] Got type 1 (Point), hasz=0 hasm=0 geodetic=0 hasbox=0
NOTICE:  [g_serialized.c:gserialized_get_type:50] entered
NOTICE:  [lwgeom.c:lwgeom_set_srid:1455] entered with srid=4326
NOTICE:  [lwout_wkt.c:lwgeom_to_wkt_sb:590] lwgeom_to_wkt_sb: type Point, hasz 1, hasm 0
NOTICE:  [lwgeom_ogc.c:LWGEOM_asText:841] WKT size = 33, WKT length = 32
  id   |            st_astext
-------+----------------------------------
 12445 | POINT(-119.67608831 48.15767852)
(1 row)

Setting client_min_messages up one level will suppress then, but then it obviously spoils NOTICEs for all.

mydb=> SET client_min_messages=WARNING;
SET
mydb=> SELECT id, ST_AsText(geom) FROM mytable limit 1;
  id   |            st_astext
-------+----------------------------------
 12445 | POINT(-119.67608831 48.15767852)
(1 row)

What's going on here? My local and custom installed versions of Postgres never acted this way.

Update: This seems to be isolated to Postgres 9.4.4 - 9.4.1 does not raise these.

We had the same problem with our RDS instances for a stretch of a week, but AWS support patched it on Sept 16. Now all RDS instances we launch with postgres 9.4.4 have functional and normal-logging postgis 2.1.5 on them. Your instances should be working as expected now too.


Create a SQL Server Login and User with limited permissions for CRUD application Access I'm trying to create a SQL statement to create a login and user in SQL Server that will allow a web application to perform crud actions on a single database, and allowing full access to all tables and stored procedures, views etc.

I know how to create a login and a user... but I then get out of my depth as the best way to continue.

CREATE LOGIN [myUser] WITH PASSWORD=N'xxx', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE USER [myUser] FOR LOGIN [myUser]
    WITH DEFAULT_SCHEMA = dbo;
GO

If you want a user to be able to read any table and view in your database then you would run the following from SQL Server 2012 onwards

ALTER ROLE db_datareader ADD MEMBER MyUser;

For previous versions you would run

sp_addrolemember 'db_datareader','MyUser';

db_datareader is a built in role that grants read access to everything.
db_datawriter is its equivalent for writes.

If you want to grant limited CRUD access then you need to create a new role and use the GRANT statement to assign permissions to that role.  After you have done that you can use the statements above to make your user a member of your new role as well.

CREATE ROLE MyLimitedCRUDRole;

For the stored procedure part of your question I would set up an explicit role for stored proc access.  I would keep this separate to MyLimitedCRUDRole as the visibility of what MyLimitedCRUDRole is of increasingly high importance in a GDPR world.

I would also advise having roles that have clear and single purpose for clarity.

CREATE ROLE db_AllProcExecutor;
GRANT EXECUTE ON MyStoredProc to db_AllProcExecutor;

It is possible to generate the GRANT statements dynamically but on any database with a security sensitivity I would be very careful doing so.