SQL Server log file continues to grow? I'm having an issue with a SQL Server log file which will not maintain a steady amount of space held on a HDD which it has been issued. The drive is ~50GB and the log file is growing to occupy this quite often.

The database is set to FULL recovery mode, with hourly differential backups. I understand that this should allow the log file to truncate and use space already allocated to it?

When checking the status of the log file using DBCC LOGINFO it returns that the status of most VLF's within the files are set to '2' which shows them as being active? We've 537 VLF's in the log file. I don't know why all these would be active at one point using 15GB of space?

I've checked the replication through DBCC OPENTRAN and it reports that there are no actively open transactions on the file.

Is there a method of maintaining this file I may of overlooked?

Is there anywhere I can go to determine exactly what the log file is doing?

Would replication interfere with truncating/shrinking the log file?

At present, it grows around 1.67GB per day. I've ~18 days before having to perform some emergency work on shrinking the file. I'd like it to stay below 25GB if possible so there is plenty of room to grow if this has a sudden need for space.

No. A differential never truncates the log. Please consider your recovery needs and choose a recovery model that fits those requirements.

  There’s still a widely held misconception that when properly in the
  FULL or BULK_LOGGED recovery models that full or differential backups
  can truncate the log. No. It NEVER happens.

http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/

I'd recommend easing up on the Differentials and start with scheduling t-log backups ever 15 minutes.

More information:

Once you get your t-logs backed up appropriately then you'll want to leave the shrinking alone. Whatever it grows to (usually within that 15 minutes) is usually the growth profile of what it actually needs. To shrink the log file only to let it grow again will cause unnecessary overhead and may impact performance.

If you want to consolidate your VLFs then you need to find an outage window when you can shrink the log file and then grow it back to it's preferred size using a standard increment (use growth increments of 8000MB)


SQL Server - Any way to validate insert statements for mismatching column names? I like to be verbose in my coding, so my typical insert statement looks like this:

INSERT INTO MyTable
(
     Column1
    ,Column2
    ,Column3
    ,Column4
    ,Column5
    ,Column6
)
SELECT
     Column1 = 'some value'
    ,Column2 = 'some value'
    ,Column4 = 'some value'
    ,Column3 = 'some value'
    ,Column5 = 'some value'
    ,Column6 = 'some value'
....

If you didn't notice the problem with this statement, then you have been fooled just as I have been numerous times.

And the really insidious part of this type of flaw is that it might work for ages before causing any issues, and when it does fail, if there even is an error, it will not indicate the true error.

For example, if columns 3 and 4 are numeric and string types, respectively, then only some values will cause runtime errors for column 4 (and none for column 3) with a type conversion failure (e.g. '0' converts to a number just fine but 'asdf' will not).

(In my case, this was buried inside a Service Broker activation procedure outside the guarded code section (it was a logging statement) so instead of even that error, I was getting the orphaned transaction error masquerading as a poison message disabling my queue.)

Is there any way to validate such a statement that doesn't require manual proofreading?

P.S. I realize that you can give whatever aliases you like, but in a completely optional context such as an INSERT statement, it would be extremely useful to have some sort of automated tool (i.e. IntelliSense) to point out probable accidental code.

Update: I have opened this feedback item to Microsoft, for this clearly-lacking functionality.

I don't think there's any good way to automate this. Especially since most people, in my experience, who use INSERT ... SELECT have not written the SELECT specifically for the INSERT, but rather copied it from somewhere else. If they have written the SELECT for the INSERT, it's unlikely they went to the trouble of adding column aliases (whether the source is another table with different column names, or just a series of constants), and if they did add column aliases, it's not guaranteed that they named them in such a way that there is a 1:1 mapping  of column names. If we really are talking about INSERT ... SELECT (a bunch of constants), it's much more likely to be INSERT ... VALUES().

You could submit a feature request to Microsoft, but I think you'd have to lay out the use case very thoroughly for this to gain traction over the many other improvements the community wants to see.


Mapping the oracle database directory object to a network path I'm an accidental DBA. We have oracle database running on windows server.

I created an oracle directory object named 'Result' and mapped it one of the local drives (C:\appResult) on the database server. The front end application creates a report and writes the file to 'Result' and a file thus gets written to C:\appResult. Now, we want to change the file path 'C:\appResult' to some other server in the network (another server in the network). What is the best way to achieve this?

Thanks

If you're talking about moving the path of the directory object, just use the Create or replace option :

CREATE OR REPLACE DIRECTORY Result AS 'c:\newpath';


Connection issues with Azure DB from SSMS I am currently making a connection to Azure DB from SSMS using public wifi. For the past few days, I was able to connect SSMS to Azure DB without any issues by adding the current ip address into the server firewall. However, today the following error came up:

Error Message

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and the SQL Server is configured to allow remote connections.
  (provider: TCP Provider, error: 0 -No connection could be made because the target machine actively refused it.)(Microsoft SQL Server Error: 10061)
  No connection could be made because the target machine actively refused it.

Things that I have tried to correct it

Check and retyped server name, login and password
Enable all SQL Server Config protocol
Manually add ip address to azure SQL Server firewall
Enable SQL server through firewall

However the issue is still not resolved, so what should I do?

Results from pinging the name of the Azure SQL server

  Pinging eastasia1-a.control.database.windows.net [191.234.2.139] with 32 bytes of data:

  Request timed out.

  Request timed out.

  Ping statistics for 191.234.2.139:
      Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

  C:>telnet 191.234.2.139 1433

  Connecting To 191.234.2.139...Could not open connection to the host, on port 1433: Connect failed

  C:>tracert 191.234.2.139

  Tracing route to 191.234.2.139 over a maximum of 30 hops

  1     2 ms     1 ms     2 ms  10.27.0.2

  2     2 ms     2 ms     2 ms  172.30.17.1

  3     2 ms     1 ms     2 ms  172.30.2.189

  4     *        *        *     Request timed out.

  5     *        *        *     Request timed out.

*Rest is request timeout

Let start by making sure you are using the current IP of your Azure SQL Database server. Let's ping the name of the Azure SQL server. The ping will fail but it should also return the current IP of the SQL Azure Server if DNS resolution is working.

C:\>ping <myserver>.database.windows.net

Pinging data.sn1-1.database.windows.net [65.55.74.144] with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Ping statistics for 65.55.74.144:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss)

Now try to telnet to port 1433 using the IP address returned.

C:\>telnet 65.55.74.144 1433

Connecting To 65.55.74.144...

If Telnet is successful, the window will change to a completely blank screen. Try connecting with that IP using SSMS again. If telenet is not successful, try tracert to identify at what step it fails to reach the Azure SQL Database server.

C:\>tracert 65.55.74.144

Tracing route to 65.55.74.144 over a maximum of 30 hops
1 2 ms 1 ms 1 ms 192.168.0.1
2 6 ms 5 ms 5 ms 10.33.34.50
3 5 ms 4 ms 5 ms 4-1-753.DLLSTX-LCR-07.verizon-gni.net [130.81.107.26]
4 8 ms 5 ms 5 ms so-6-2-0-0.DFW01-BB-RTR1.verizon-gni.net [130.81.28.208]
5 44 ms 43 ms 44 ms so-11-0-0-0.LCC1-RES-BB-RTR1-RE1.verizon-gni.net [130.81.17.40]
6 49 ms 44 ms 44 ms so-6-0-0-0.ASH-PEER-RTR1-re1.verizon-gni.net [130.81.10.90]


postgis installation gone awry [cross-posted from stack ove
On a Ubuntu 14.04 staging server a MySQL connection protocol had to be installed for full-text searching for a separate application.  This led to installing, uninstalling ad installing anew mysql and sphinxsearch.

After a reboot, another application hits an error because:

ERROR:  could not access file "$libdir/postgis-2.1": No such file or directory

in psql (version 9.3.10) the same error message is getting confirmed upon

SELECT PostGIS_full_version();

notwithstanding that a check on the database returns extension "postgis" already exists.. whereis postgis also returns a blank.

Has postgis been somehow uninstalled?

Attempting to install anew:

sudo apt-get install postgresql-9.3-postgis

getting Package postgresql-9.3-postgis is not available, but is referred to by another package. This may mean that the package is missing, has been obsoleted, or is only available from another source E: Package 'postgresql-9.3-postgis' has no installation candidate

Even attempting to create /etc/apt/sources.list.d/pgdg.listwith

deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

then

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.3-postgis

getting the same no installation candidate. Is this server hosed for postgis now?  Would re-installing postgresql impact existing applications on the VPS?

Try

sudo apt-get install postgresql-9.3-postgis-2.1

http://packages.ubuntu.com/trusty/postgresql-9.3-postgis-2.1


Many To Many Relationships I am working on a conceptual model for a database. I have the following entities:

Student (id, name, date of birth, department)

Subject (id, name)

This is a many-to-many relationship, one student may be enrolled in many courses, each course has many students. From what I understand, as I should break this many-to-many relationship by introducing a new into a entity, say 'registered' which allows a one-to-one relationship or one-to-many relationship?

Is this correct design decision?

Generally a many-to-many relationship is done with a junction table, like so:

Student (id, name, date of birth, department)

Course (id, name)

Student_Course (student_id, course_id)

You can either create an artificial key on the Student_Course table or create a primary key or unique index with both fields.  student_id is a foreign key to student.id and course_id is a foreign key to course.id.

Note that the junction table need not be exactly two fields.  You can include fields such as date_added or date_dropped so you could see when students have added and dropped courses.


Star Schema from Relational Database I'm a newbie to data warehousing and I've been reading articles and watching videos on the principles but I'm a bit confused as to how I would take the design below and convert it into a star schema.

In all the examples I've seen the fact table references the dim tables, so I'm assuming the questionId and responseId would be part of the fact table? Any advice would be much appreciated.

Ok, based on your very limited documentation, I would do the following:

Fact Tables - Your fact table is your measurement table. It is the thing that happened. It is the meeting together of dimension tables, typically at a point in time.  In your diagram,

RESPONSES is your fact table.
OPTIONS could be a fact table.  If you denormalize it you could have dimensions associated with it.

Dimension Tables - Dimension tables contain the textual context associated with a measurement event. It describes the “who, what, where, when, how, and why.” Based on your diagram:

SAMPLE would be a dimension table, describing the people who make a response. It would be the closest you have to a "customer" dimension table.
QUESTIONS would be a dimension table, describing the question in detail. You could extend this dimension to include more data on the question type, such as length, IsMultipleChoice, IsFreeText, IsPersonal, isPrivate, etc. I would think of this dimension as a "product" dimension table, based on the limited schema you have.
OPTIONS could be a dimension table. It looks like it is something you base the response field on. The possibility of responses for a given question.

Personally, I think you need to read up on Data Warehousing. The Data Warehouse Toolkit by Kimball is an invaluable resource.


Single year column and multiple weeks columns into single year/week column with PIVOT I have a table that looks roughly like this.

(Pk int, Year int, Week1, Week2, .., Week53)

The values in each "Week" column are sales data.  What I want to do is create a new table from the first table in the form of -

(Pk int, WeekYear, SalesData)

Where "WeekYear" is a concatentation of the year and the week number and SalesData is the same salesdata for that respective field in the former table. (just trying to get the select down for now)

SELECT pk,
   [1],
   [2],
   ...
   [1000]
 FROM (SELECT * FROM SalesTable) st
PIVOT
(
AGGREGATE(pk) -- no idea what to use here
FOR pk IN [1],
   [2],
   ...
   [1000]
 );

My current attempts make no sense as I'm not sure which aggregate function to apply on my dataset. I'm also having a bit of trouble discerning the best way to create the new column YearWeek.

This seemed like an obvious use for PIVOT (I need to transpose the table). However, I'm having a lot of trouble wrapping my head around how to properly accomplish it.  What would be the best way to go about doing this?

This sounds more like an UNPIVOT to me.

SELECT SalesTable.Pk, CrossApplied.WeekYear, CrossApplied.Value
FROM SalesTable
CROSS APPLY (VALUES ('01'+CAST([Year] AS CHAR(40)), Week1),
                    ('02'+CAST([Year] AS CHAR(40)), Week2),
                    ('03'+CAST([Year] AS CHAR(40)), Week3),
                    .......)
        CrossApplied (WeekYear, Value)

There are other ways to do an UNPIVOT but using CROSS APPLY is my favorite.  I give a more general example here.


SQLServer In-Memory database and log file size unexpected increase I have a strange issue with a SQLServer 2014 (12.0.4439.1) database.

I created some months ago a data file and table to play with In-Memory basic features and see if could improve some of our processes.
When I finished I wanted to clean everything but I didn't know that "Once you create a memory-optimized filegroup, you can only remove it by dropping the database".

Well, I didn't want to delete my database (since it is used in the real life by real people) nor re-create / re-fill it (quite a lot of work).

So everything stayed like that : a normal DB with one empty file for in-memory stuff. And everything is going well.

But, once, log file of this database is going crazy : its size increases and nothing can stop it.
And when I try to shrink it, I often (not always) get a "shrink failed for log file due to XTP_CHECKPOINT" which looks like related to in-memory features.
And after trying backup / service restart / checkpoint and other esoteric commands, log file can be manipulated and shrunk correctly.

So briefly : I don't understand what happened.

Does someone has experienced that ? and/or know a clean way to avoid/fix this strange behaviour ?

[EDIT] : the issue looks very similar to https://support.microsoft.com/en-us/kb/3090141 but in my case, hotfix is already installed and the workaround suggested (offline / online) doesn't work

Usually, in SQLServer logs there's a HkHostLogCheckpointRecord() message every hour.
Log file increasing started to happen when this messages stopped to appear.
Is it the cause or the consequence ? that's the question....

OK, I didn't find a clear explanation, but found how to fix that.

It was because file used to store in-memory objects was full and could not allocate more space. So no more checkpoints, and logs and growing more and more. This is happening even though there is no in-memory objects in the database (and therefore no reason for the data file to grow).

Allowing file to grow more is not working, you need to add another file to your filegroup.

So, knowing that, it's easy to fix.

add another file into the filegroup for in-memory
checkpoint (or, if you can afford it, restart SQLServer service)
shrink log file to a more reasonable size

Conclusion of the story : carefully monitoring data size is even more important with in-memory.


Steps or evaluation order of the SQL statement How the SQL statement containing simple statement like

Select x
From y
Where z

Having, order by , group by etc. are evaluated.( In which order they are evaluated.?)

Following is the logical order of execution of the SQL clauses:

  FROM clause
  WHERE clause
  GROUP BY clause
  HAVING clause
  SELECT clause
  ORDER BY clause


PostgreSQL: Is it possible to get a different function result type (i.e. TABLE) in stored function based on input? The question is as follow, I wanted to get some flexibility in my example function, so based on input arguments to function I wanted to return different table as an output result.

So is it possible to get something like this in function definition:

RETURNS TABLE (
    customer_id INTEGER,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    country VARCHAR(50)
)

OR

RETURNS TABLE (
    message VARCHAR
)

My broken example function is:

CREATE OR REPLACE FUNCTION get_customer_by_country(
    country_name VARCHAR,
    id_flag BOOLEAN
)
RETURNS TABLE (
    customer_id INTEGER,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    country VARCHAR(50)
)
AS
$$
<<function_body>>
DECLARE
BEGIN
  IF id_flag THEN
      RETURN QUERY
        SELECT
          cstmr.customer_id,
          cstmr.first_name,
          cstmr.last_name,
          cntr.country
        FROM
          customer AS cstmr
        JOIN address ON cstmr.address_id = address.address_id
        JOIN city ON address.city_id = city.city_id
        JOIN country AS cntr ON city.country_id = cntr.country_id
        WHERE
        cntr.country = country_name;
    ELSE
      RETURN QUERY
        SELECT 'flag was not provided';
    END IF;
END function_body;
$$
LANGUAGE plpgsql;

SELECT get_customer_by_country('Belgium', false);

I searched for a questions like ERROR:  structure of query does not match function result type, but haven't found anything that matched my case.

Thanks in advance!

First off, you should take a look at Erwin Brandstetter's answer to a similar question: https://stackoverflow.com/a/23957098/835781

If the return type you want is mimicked by the structure of an existing table, you could use that existing table's regclass to define the type of the results.  That method seems to be a bit overkill for your use case, if I'm understanding it correctly.

My suggestion would be to just create two functions.  If you need to keep the same name, I would suggest you overload the function definition:

CREATE OR REPLACE FUNCTION get_customer_by_country(
    country_name VARCHAR
)
RETURNS TABLE (
    customer_id INTEGER,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    country VARCHAR(50)
)
AS
$$ ... $$ ;

CREATE OR REPLACE FUNCTION get_customer_by_country(
    country_name VARCHAR,
    id_flag BOOLEAN
)
RETURNS TABLE (
    message VARCHAR
)
AS
$$ ... $$ ;

This results in you still having to create two function definitions.  If you're going to create two separate functions, you should probably just use different names for each.  If your application code is expecting the two different result sets depending on the flag provided, why not interpret that flag in your application code and use that to determine which function to call?

If you're dead-set on returning two different data structures depending on the value of a variable, you can't use the schema of an existing table to define the result type, and you're ok with the processing overhead, then you could create an array or json return type.  This gives your function the flexibility to build out the result in whatever format you choose, but requires additional processing and logic handling to interpret.  At the end of the day, it's usually just easier to create different functions for the different result types.


In SQL Server, in layman's terms, how do you lock resources well enough to perform an INSERT-IF-NOT-EXISTS transaction? If someone asks how to perform an INSERT-IF-NOT-EXISTS operation in SQL Server, they'll typically get an answer like this back:
IF NOT EXISTS(SELECT 1 FROM [TheTable] WHERE [ColumnX] = @valX)
    INSERT [TheTable] ([ColumnX]) VALUES (@valX)

The problem I'm seeing with this is that in between the SELECT statement and the INSERT statement, the situation could change externally.  Another process could insert the ColumnX value after the SELECT statement, but before the INSERT statement, resulting in an error being raised.
I've worked in software for a while, but am not a DB specialist, and when I search for an answer to this problem in SQL Server, the results I'm seeing are either irrelevant or quite difficult to really apply (because they're either answering a different question or are written in terms tailored to DB specialists).
So in layman's terms, how do you resolve this problem?  I did get a little bit rusty with SQL in recent history, but am thinking that there really should be a pragmatic locking mechanism to use for this (whether there is or isn't).  As a fallback, maybe error handling can specifically determine whether an error raised matches this exact issue, ignoring it in that specific case.
Preferably this doesn't involve just locking the whole table every time.

Option 1: take a lock out which locks at least the range in the index that the row would exist in.
SET XACT_ABORT ON;
BEGIN TRAN
IF NOT EXISTS(SELECT 1 FROM [TheTable] WITH (UPDLOCK, HOLDLOCK) WHERE [ColumnX] = @valX)
    INSERT [TheTable] ([ColumnX]) VALUES (@valX)
COMMIT

HOLDLOCK will give serializable semantics and lock the range between existing keys in the index where the value would fit (if there is a suitable index, otherwise it will lock the whole table). UPDLOCK reduces the probability of deadlocks in this pattern as two concurrent queries can't take out the same range lock in the reading phase.
Option 2: You can just add a unique constraint on ColumnX and try the insert anyway and catch the error raised from duplicate key violation.
Given that Option 1 needs an index with leading column ColumnX anyway to meet your preference of not "locking the whole table every time" you might as well add one and define it as unique. The index will speed up the existence check anyway. With that in place I would select between option 1 and 2 on the basis of how frequently I expect attempts to insert duplicates.


Can not connect to MySql server remotely I can not connect to MySQL server from a remote computer. When I try to connect it gives me the error

  Host '' is not allowed to connect to this MySQL server

I've edited the MySQL server my.ini file by changing the bind line from #bind = "127.0.0.1"  to bind = *. I've turned off the firewall of the client machine trying to access the server. I also gave full privilege to the user of MySQL database through which I am  trying to connect to the server by using the MySQL grant command, but nothing seems to be working.

Please help me.

Thanks

A MySQL user has a "from host" field. This is probably set to some IP and those mismatch, giving you that error.
This field allows for extra security and can be set to any IP/hostname.
If this field is set for the user then you can only login to the MySQL server with that user from the IP/hostname specified.
If you want to test your connection you can just use a wildcard for the "from host" field % so that any IP can connect using that user, I wouldn't recommend this in a product environment though.


Query Result Not Perfect on Mysql? I have table Like This

 ID | City     | Prize
_______________________
  1 | Surabaya | Notebook
  2 | Surabaya | Notebook
  3 | Surabaya |
  4 | Bandung  | NoteBook
  5 | Bandung  | PC
  6 | Bandung  | Handphone
  7 | Bandung  |

I want group my table by City, Prize, and Qty Like This :

 ID | City     | Prize    | QTy
______________________________
  1 | Surabaya | Notebook  | 2
  2 | Bandung  | Handphone | 1
  3 | Bandung  | NoteBook  | 1
  4 | Bandung  | PC        | 1

I try This Query but Not Perfect this My query

SELECT City, prize, count( prize ) AS qty
FROM `my_table`
GROUP BY hadiah

Can anyone Help me for fix this Problem?

Im very Appreciated your answer.

Thanks

I Have Try query From JoeTaras

SELECT Kota, hadiah, count( hadiah ) AS qty
FROM unik
WHERE hadiah IS NOT NULL
GROUP BY kota, hadiah

And the result is still not true, The Null Value is Count too

Try this:

SELECT City, prize, count( prize ) AS qty
FROM `my_table`
WHERE prize IS NOT NULL
GROUP BY city, prize
ORDER BY count(prize) DESC


Display Total under name column and sum of amount under sal column in a table empno,ename,sal,hiredate is there.
but i want output is

empno  ename sal hiredate
122    ganga 2000 1989-02-06
123    reddy 3000 1986-06-02
       Total 5000

here empno, hiredate column just empty,no need to display null or zero or anything.

how to write the query in SQL SERVER

You could use Grouping Sets:

select empno
     , coalesce(ename, 'Total') as ename
     , sum(sal) as sall
     , hiredate
from table1
group by grouping sets ((empno, ename, hiredate),());

If ename is nullable this is not sufficient since ename might be null for other reasons than grouping. In that case you can use the GROUPING function:

select empno
     , case when GROUPING(ename) = 1
            then 'Total'
            else ename
       end as ename
     , sum(sal) as sall
     , hiredate
from table1
group by grouping sets ((empno, ename, hiredate),());

But I agree with @Mark Sinkinson that it is probably better to do this in the presentation layer.


Create limited admin role in SQL Server that cannot alter row level security I would like to know if there is a practical way to create an admin role in SQL Server that can essentially do anything except alter row level security policy.

It is simple enough to create row level security policy that will hide data from admins, but much harder of course to prevent the same admins from altering or circumventing the policy.

I have (I think) a working scenario using CONTROL SERVER and some user-level permissions, but there are caveats (see below) and there may also be vulnerabilities that I haven't thought of.  One complicating factor is the fact that "ALTER ANY SECURITY POLICY" is a database level permission, and cannot be denied at the server level (I don't think?).

Here is what I have so far (assume security policy in question pertains to a db called "SecurityTest"):

USE [SecurityTest]
GO

CREATE LOGIN [LimitedAdmin] WITH PASSWORD = '...';
CREATE USER [LimitedAdmin_usr] FROM LOGIN [LimitedAdmin]

/* Server role */
CREATE SERVER ROLE [srvrole_LimitedAdmin]
GRANT CONTROL SERVER TO [srvrole_LimitedAdmin]

DENY IMPERSONATE ANY LOGIN TO [srvrole_LimitedAdmin]; -- Cannot impersonate a sysadmin or other more priviledged principal
DENY ALTER ANY LOGIN TO [srvrole_LimitedAdmin]; -- Cannot create or modify another login or user (i.e. to create a higher-priviledged login for themselves to use)
DENY ALTER ANY SERVER ROLE TO [srvrole_LimitedAdmin]; -- Cannot tamper with this or other server roles

ALTER SERVER ROLE [srvrole_LimitedAdmin] ADD MEMBER [LimitedAdmin]

/* Database restrictions */
DENY ALTER ANY SECURITY POLICY TO [LimitedAdmin_usr]
DENY ALTER ANY ROLE TO [LimitedAdmin_usr]

It appears that this configuration will prevent "LimitedAdmin" from

Disabling the security policy, i.e. ALTER SECURITY POLICY ... WITH
(STATE=OFF)
Impersonating a login or user with elevated permissions
Creating a login, database role, or user (i.e. to obtain elevated
permissions)
Adding self to a role with higher privileges, i.e
ALTER ROLE [db_owner] ADD MEMBER LimitedAdmin
Creating a stored procedure that will execute as a higher-priviledged user, i.e. CREATE PROCEDURE usp_Malicious WITH EXECUTE AS OWNER...

Caveats

Everything listed in this blog post by Andreas Wolter
Admins defined this way would not be able to manage users, logins and roles, or impersonate other logins.  This seems like the biggest "Catch 22".  Perhaps stored procedures could be used to allow pre-defined user management tasks within specific parameters.
LimitedAdmin could collude with another limited admin by granting him/her permission to alter the security policy, i.e. GRANT ALTER ANY SECURITY POLICY TO [AnotherLimitedAdmin].

Any thoughts on improving (or abandoning) this effort would be greatly appreciated.

A better approach to me would seem to be setting up the LimitedAdmin group at the server level, then set up triggers at the server and/or or DB level that check when the command(s) you want to block is run.

In each trigger, check to see if the user is in the LimitedAdmin group, then abort the DDL trigger if they are. This handles multiple different conditions with multiple similar triggers (i.e. ALTER SERVER ROLE and ALTER ROLE are two different triggers you would need). This would seem to offer finer control than the simple DENY statements.

As an example, suppose LimitedAdmin user [bob] was to trigger an ALTER SERVER ROLE event. The trigger could check what role was being altered, and decide if it was okay for LimitedAdmin users could alter that role in that way for the target user.

For example, adding another LimitedAdmin user should be disallowed for a LimitedAdmin user to perform, but they might be allowed to add an 'AccountingSystem' user. One trigger could be used to check all of the possibilities. This would mean that once the triggers are configured, adding a user to the LimitedAdmin group should be all of the configuration that you need.


Block certain queries from being logged in slow query log I have mysqld_node running on my mysql server to get statistics to be used by grafana + prometheus to graph dashboard for this MySQL server. However, i noticed that 90% of the queries in my slow query logs are created by mysqld_node, and hence hard to filter queries from Application servers. Is there a way to block queries from being logged in the slow query logged based on the port or some other criteria? The port for mysqld_node is 9104.

long_query_time is a session variable and could be set to a large value in the mysqld_node code.


Need to grant select permission on View but not in table which is in a different database I have a table in database A and a view using that table in database B. I want To give select privilege to a user on that view but I do not want to give any access to table.

Can anyone help me on that issue?

I will give a full demo how you can achieve this.
Create 2 database

USE [MASTER];
GO
CREATE DATABASE [TestA]
GO
CREATE DATABASE [TestB]
GO

Create a login and user in both database.

USE [master];
GO
CREATE LOGIN TestUser WITH PASSWORD = 'Password', CHECK_POLICY = OFF;
GO

Create user in both database using the login above.

USE TestA
GO
CREATE USER TestUser FROM LOGIN TestUser;
GO
USE TestB
GO
CREATE USER TestUser FROM LOGIN TestUser;
GO

Create table is TestA database.

CREATE TABLE TestA.dbo.t1(id INT);
GO

Create view in TestB database.

USE TestB;
GO
CREATE VIEW dbo.ViewA
AS
  SELECT id FROM TestA.dbo.t1;
GO

Enabling Cross-database Ownership Chaining

ALTER DATABASE testA SET DB_CHAINING ON;
ALTER DATABASE testB SET DB_CHAINING ON;

Grant permission to read from the view.

USE [TestB]
GO
GRANT SELECT ON [dbo].[ViewA] TO [TestUser];
GO

Login with TestUser into SSMS and run this:

USE [TestB]
GO
SELECT * FROM [dbo].[ViewA]
USE [TestA]
GO
SELECT * FROM [dbo].[t1]
GO

You should see all the rows from testA.dbo.ViewA table via the view but get following error message for the select on table t1.

  Msg 229, Level 14, State 5, Line 6 The SELECT permission was denied on
  the object 't1', database 'TestA', schema 'dbo'.


Timeout expired error. Is it because of a query or server settings? I am getting SQL Server timeout error in my production server, every hour
(~ for 5 minutes or more) during night time. This SQL server database is supporting a .NET application. I have scheduled an email job to send errors to me and I get errors with details as below.

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__174_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.<ExecuteStoreCommandsAsync>d__c.MoveNext()
   --- End of inner exception stack trace ---

I am trying to identify what is happening at that time which causes this timeout error.

SQL Server version: 2008 R2
Physical Memory: 24 GB
SQL server Max memory: 18 GB

Transaction log backup runs every hour for example transaction log back runs at 4:00 am and the timeout occurs at 04:01.But this behavior is not consistent and timeout doesn't occur every hour.

Result of:

SELECT * FROM sys.dm_os_wait_stats AS dows WHERE dows.wait_type = 'THREADPOOL';

 wait_type  waiting_tasks_count  wait_time_ms   max_wait_time_ms    signal_wait_time_ms
 THREADPOOL     15223          68891                1086           4

i noticed in my Job Activity Monitor that Shrink database job which runs every week failed during its last run.I don't know its implications.Any thoughts?

Timeout settings are a configuration of the client connection, so are either set by the client application, or via the connection string. Look at how your applications is instantiating the database connections, or perhaps in your application config files that control how those connections are created.

But increasing the timeout is just treating the symptom. Determining why the timeout occurred is a more difficult problem, and may require some detective work. Here are some avenues of investigation:

Is the error always throwing on one or two particular queries? You might need to do some query tuning.
Is the error always occurring at the same time each hour? Does that correspond to maintenance running overnight, perhaps in scheduled SQL agent jobs?
You might run SQL trace/profiler overnight, filtered by query duration, to see if you can better identify these long-running queries.


How do you enable SQL Server 2019's result set caching? SQL Server 2019 CTP 2.3 added a few new columns to sys.databases, including is_result_set_caching_on:

It's not documented yet in the What's New in SQL Server 2019, nor in the ALTER DATABASE page.

I tried using the same syntax as Accelerated Database Recovery just for laughs:

ALTER DATABASE StackOverflow2013 SET RESULT_SET_CACHING ON;

But no luck:

Msg 5058, Level 16, State 12, Line 8
Option 'RESULT_SET_CACHING' cannot be set in database 'StackOverflow2013'.

Currently, in CTP 2.3, in so far as I can tell - you cannot enable this, even under DAC.


Use SSMS to Create Database Diagrams but not Database Objects I'm looking to be able to create a database diagram without actually creating database objects.  It seems like the best way to do this would be directly through SSMS.  My only question is, is there a way to not have SSMS create the database objects when you create them in the database diagram?

I want this as a planning and design tool, not necessarily for implementation yet.  I just want to lay it all out without actually creating any tables yet.  When I am in the Database Diagram and click New Table, I just want it to create the new table to be used in the diagram as opposed to a table database object being created in the database.

AFAIK - there is no way to do it in SSMS, use other tools for that, such as Microsoft Visio, ERWin etc. - there are a lot of them.

You may create a dummy database and draw you diagrams there, thinking of underlying creating objects as about a part of diagram itself. 8-)


How to model local exceptions to distributed tables I am looking for possible ways to model a distributed table(s) and provide local exceptions to that table.  Consider a situation where head office publishes a list of products/prices that is distributed to branches and they load this price list into their local database.  Now sometimes these branches want to change the head office price to a local price.  Clearly we can simply update the price in the local database, but then it gets lost when the next head office update is applied.  We also only want to block that single column(s), not changes to other fields in the row.
EG.  Head office send
| Name | Price |
|------|-------|
| ABC  | $5    |
| DEF  | $8    |

In one database (out of say 100) we want ABC to have a price of $3.  And this $3 should continue to apply even as future updates from head office are received.  Head office are not aware of this local exeception.
In the past we've done this in two ways.  The first was to maintain a second LocalProducts table with the changes to be applied - then when the head office version was applied, any local changes would then be read from LocalProducts and replace the distributed value.
This works OK, but suffers as the table sizes go up.
The second method we tried was to duplicate the column and use coalesce
eg  rather than select price where name='abc' we would use select coalesce(localprice,price) where name='abc'  This works ok, but had the overhead that we had to change every SQL and remember to do it on future development too.
Are there any other better methods?  I am looking for a database agnostic solution, the target databases are ODBC (90% sql/server), SQL-Lite or MS-Access.   Access can be ignored if it helps, it is being actively phased out.  Engine specific solutions are ok, we can detect the engine and use a different technique, but would prefer not to have a different technique for each engine.
Other notes

The data is distributed by the application, not built in replication.  This means we can have code/logic during the loading phase
Multiple tables are require this feature, not just one as the example above
Some of the tables have a reasonable number of columns that could be involved, but typically not.
Most of the tables aren't large, perhaps 50M rows in the biggest, and only 7 rows in the smallest case.

(please feel free to retag this question - not exactly sure which tags are relevant.  And yes I have spent many hours over the years investigating this)

Put the COALESCE in a view write queries against the view, not the base tables. It would help if you could remove access to these tables and grant access to the view only.
There will be a small runtime penalty with this, as two tables must be read. If you could change the load to perform the COALESCE once at load time that would be better. Subsequent changes to local price must be written to both local table and to the master list. It would not be possible to run reports on, say, the cost of local pricing as the official prices would be unknown.


Is it possible in SQL Server 2008 to have Structures in one file and Rows Data in another file? I know this may sound ridiculous but:
Is it possible to separate the Data Definition (i.e. Structures of my Tables, Views and other things) and Rows-Data on a File-Level in SQL Server.
I think it will be useful to have two or more filegroups on 2 or more physically separated drives and have files in them to solely store the definitions or row-data; so if your row-data HDD crashes, you wont lose the data structure...
Can this be done through Partitioning?

As @Denny pointed out, schemas are always in the PRIMARY filegroup. There are potential availability benefits to having just schema in PRIMARY and data elsewhere, if you can make use of Partial Availability & Piecemeal Restore.

The usefullness of this depends on the nature of your data and if you have tables that are suitable for partitioning. For example, if your biggest tables contained Orders and OrderLines which are partitioned by OrderDate across quarterly filegroups and your remaining tables are relatively small by comparison, in the event of failure you would

Restore the PRIMARY filegroup (instantly, as its tiny).
Retore the filegroup containing the smaller supporting tables.
Restore the filegroup allocated to the current quarter order data (again relatively quickly compared to restoring all history).
Set the remaining partitions as offline.
Bring the database online. You can now accept new orders and process existing orders.
Gradually restore the remaining partitions and bring online in turn.

In a similar vain, if you were to suffer corruption to the database metadata (a rare occurance compared to corruption in data structures but not impossible), you would only need to restore the very small primary filegroup to get up and running.

The availability benefits this can bring to a large database are so significant, I try design partial availability in from the outset on new projects. A 500GB database restoring at 50MB/s will result in at least 3 hours of explaining why it's taking so long to the non-techs. With forethought and planning, it could be a fraction of that to get the business up and running again.


SSRS (2008 R2 to 2014) Migration Issues Running Reports This is my first ever StackExchange question, so sorry if I something is off...I searched and found a rather helpful article that got me this far, but nothing I found addressed what I am currently seeing.

As the title states, I am having issues running reports on SSRS 2014 after having migrated from SSRS 2008 R2. A bit of background information:

SQL Server 2008 R2 was hosted on a Win 2008 R2 server
New Server (SQL Server 2014) is Win Server 2012 (not a big fan of metro interface)
I added the databases with the backup/restore method going from 2008 to 2014
All backups/restores completed smoothly.
I can view all data, and SP from the other databases and the ReportServer DB
I imported the key after backing it up form SQL Report Services Configuration manager which all completed successfully.

So the issue lies in the fact that when I connect to the SSRS home in IE, I can browse and manage everything, but nothing is able to run. I am getting to the point where I can enter parameters, but get the message:

"Query execution failed for dataset 'DataSet1'.(rsErrorExecutingCommand) Could not find stored procedure 'storeprocedurename'"

After having opened them in Report Builder on the server, it appears that when I right-click the data set to check the properties, I cannot actually login to the data source for the data set (data sets are embedded in reports). I tried creating a new data source, but logging into this proved an issue as well when it came time to add a data set - though it was able to successfully test connection to the new data source. So my guess is that the data source is not connecting to the new sql server database? At a loss and grasping for straws.

Another odd thing is that the sys accounts are not enabled for the databases I imported, nor am I given the option to enable it.

If anyone has any insight as to what I may have messed up, or what I need to modify to correct this, I would be incredibly appreciative. Thanks in advance for any help.

I found the solution in case anyone else comes across this.

The issue lied in the connection string property of the data source. For some reason (maybe standard, I am simply unaware of why) the connection string properties were empty after importing them to the new server. After setting them, they were able to hit the stored procedures and all reports ran as intended.


How to delete all records which are not referenced from other tables I have a table to which a bunch of other tables has an FK reference. Is there any way of deleting records from this table only if they are not being referenced?
I know that I can left join the referencing table and check for null, but I have about 10 tables (more will be added) with FKs referencing this table, so it would be cool to have a generic way of doing it.
There are often not more than a handful of records I need to remove. I suppose I could loop and try to remove each record individually and protect each deletion with BEGIN/EXCEPT, but that is an ugly concept.
Does this kind of functionality exist in Postgres? Kind of a soft delete, or delete-if-allowed.

I can think of no better way than doing:
DELETE FROM referenced_table AS r
WHERE NOT EXISTS (SELECT 1 FROM referencing_table_1
                  WHERE ref_id = r.id)
  AND NOT EXISTS (SELECT 1 FROM referencing_table_2
                  WHERE ref_id = r.id)
  AND ...;

This is a very unusual requirement.
If you need to do that often, you could even consider denormalizing your database:

add a column reference_count to the referenced table

define triggers that update reference_count whenever rows in the referencing tables are added, deleted or modified

Then you can simply delete the rows with reference_count = 0. Processing will be fast (if all your foreign keys are indexed!!), but you buy that at the price of slowing down all data modifications on all referencing tables.


Where is stored the messages results from T-SQL? I would like to know if SQL Server store all messages related to T-SQL in a system table or in other object from Database Engine.

Because, I don't know if this is a response of engine or some other stuff.

Now, I did this to capture transactions

And I need to do that for each transaction that I want to "control" (number of register inserted, deleted or updated)

The message you're seeing is not generated by SQL Server itself; it just returns "12".

SQL Server Management Studio generates the complete message; it's a .NET application so it's probably coming from a .resx file which gets compiled into the application package. With a decompiler or DLL analyzer you might be able to find it among the other messages.


DBCC SHRINKDATABASE TRUNCATEONLY - 2008R2 vs 2012 Im a bit confused. Was the TRUNCATEONLY parameter changed in SQL 2012 or is the documentation wrong in SQL 2008 R2?

2008 R2

  Releases all free space at the end of the file to the operating system,
  but does not perform any page movement inside the file. The data file
  is shrunk only to the last allocated extent. target_percent is ignored
  if specified with TRUNCATEONLY.

  TRUNCATEONLY is applicable only to data files. The log files are not
  affected.

The last statement makes me thing this has no effect on log files at all?

2012

  Releases all free space at the end of the file to the operating system,
  but does not perform any page movement inside the file. The data file
  is shrunk only to the last allocated extent. target_percent is ignored
  if specified with TRUNCATEONLY.

  TRUNCATEONLY affects the log file. To truncate only the data file, use
  DBCC SHRINKFILE.

The last statement now tells me it only affects the log file?

So was the functionality changed, or is there an error in the documentation, or is my interpretation wrong?

TRUNCATEONLY affects both the LOG and the DATA files in 2008. On BOL for SQL Server 2012 the message simply indicates that if you only wish to SHRINK the database file, then you should use DBCC SHRINKFILE which will allow you to shrink either the data or log files.

For 2008, it is clearly indicated that TRUNCATEONLY only affects DATA files.

Lets test. To visualize what happens using TRUNCATEONLY with SHRINKDATABASE, here is a run down of what happened to a database called performance that I have installed locally.

SELECT @@VERSION;
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
   Jun 28 2012 08:36:30
   Copyright (c) Microsoft Corporation
   Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

USE performance;

I ran DBCC LOGINFO just to take a peak inside the transaction log and found that all of the virtual log files after about 200 were inactive, status = 0. In my performance database, all those inactive VLFs can be truncated and the space can be given back to the OS.

Here is some sample output from LOGINFO.

FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
------ -------- ----------- ------ ------ ------ -----------------
2      253952   8192        23     2      64     0
2      253952   262144      24     2      64     0
2      270336   516096      25     2      64     24000000013400005

I then ran DBCC SQLPERF(LOGSPACE) and got the following output

DBCC SQLPERF(LOGSPACE)
/*
Database Name   Log Size (MB)   Log Space Used (%)  Status
Performance          9870,867             3,395626  0
*/

I then ran

DBCC SHRINKDATABASE(PERFORMANCE, truncateonly)

and got the following result

/*
DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
8       1       66464       288         44944       44944
8       2       116584      72          116584      72
*/

Next, I reran

DBCC SQLPERF(LOGSPACE)

and got

/*
Database Name   Log Size (MB)   Log Space Used (%)  Status
Performance     910,8047        37,08699            0
*/

SHRINKDATABASE with TRUNCATEONLY gave back over 9 GB of available space from the transaction log file back to the OS.

I tried the same experiment with another database called performance2

sp_spaceused

/*
Performance2 52.19 MB   22.81 MB
*/

DBCC SHRINKDATABASE(Performance2, truncateonly)

sp_spaceused

/*
database_name   database_size   unallocated space
Performance2 31.13 MB   21.13 MB
*/

Which gave back 20 MB to the OS. Using SQL Server 2008, SHRINKDATABASE TRUNCATEONLY shrinks both data and transaction log files.


SQL Server tempdb on RAM Disk? Our vendor application database is very TempDB intensive.

The server is virtual (VMWare) with 40 cores and 768GB RAM, running SQL 2012 Enterprise SP3.

All databases including TempDB are on Tier 1 SSD in SAN.
We have 10 tempdb data files, each pre-grown to 1GB and they never auto-grow. Same with 70GB log file. Trace Flags 1117 & 1118 are already set.

sys.dm_io_virtual_file_stats shows over 50 Terabytes read/written on tempdb data & log files in past month, with cumulative io_stall of 250 hours or 10 days.

We have already tuned the vendor's code and SPs over past 2 years.

Now, we're thinking of placing tempdb files on RAM Drive since we have a ton of memory. Since tempdb gets destroyed/recreated when server is rebooted, it is an ideal candidate to place on volatile memory which also gets flushed out when server is rebooted.

I've tested this on a lower environment and it has resulted in faster query times but increased CPU usage, because the CPU is doing more work instead of waiting on slow tempdb drive.

Has anyone else put their tempdb on RAM in high oltp production systems? Is there any major disadvantage? Are there any vendors to specifically choose or avoid?

First, patch: make sure you're on 2012 Service Pack 1 Cumulative Update 10 or newer. In SQL 2014, Microsoft changed TempDB to be less eager to write to disk, and they awesomely backported it to 2012 SP1 CU10, so that can alleviate a lot of TempDB write pressure.

Second, get exact numbers on your latency. Check sys.dm_io_virtual_file_stats to see the average write stall for your TempDB files. My favorite way to do this is either:

sp_BlitzFirst @ExpertMode = 1, @Seconds = 30 /* Checks for 30 seconds */
sp_BlitzFirst @SinceStartup = 1 /* Shows data since startup, but includes overnights */

Look at the file stats section, and focus on the physical writes. The SinceStartup data can be a little misleading since it also includes times when CHECKDB is running, and that can really hammer your TempDB.

If your average write latency is over 3ms, then yes, you might have solid state storage in your SAN, but it's still not fast.

Consider local SSDs for TempDB first. Good local SSDs (like Intel's PCIe NVMe cards, which are under $2k USD especially at the sizes you're describing) have extremely low latency, lower than you can achieve with shared storage. However, under virtualization, this comes with a drawback: you can't vMotion the guest from one host to another to react to load or to hardware issues.

Consider a RAM drive last. There are two big gotchas with this approach:

First, if you really do have heavy TempDB write activity, the change rate on memory may be so high that you won't be able to vMotion the guest from one host to another without everyone noticing. During vMotion, you have to copy the contents of RAM from one host to another. If it's really changing that fast, faster than you can copy it over your vMotion network, you can run into issues (especially if this box is involved with mirroring, AGs, or a failover cluster.)

Second, RAM drives are software. In the load testing that I've done, I haven't been all that impressed with their speed under really heavy TempDB activity. If it's so heavy that an enterprise-grade SSD can't keep up, then you're going to be taxing the RAM drive software, too. You'll really want to load test this heavily before going live - try things like lots of simultaneous index rebuilds on different indexes, all using sort-in-tempdb.


Oracle SecureFile Compression Where can I read database compression properties after the creation of an oracle database / tables? Is there a special view where the info is stored and can be retieved?

Assuming you have the appropriate DBA-level permissions, you can try this to get some basic information on the SecureFile/LOBs in your system:

SELECT table_name, segment_name, index_name, securefile, in_row
FROM user_lobs;

You can get the size on disk for a specific table name (your table with a SecureFile/LOB) by doing this:

SELECT segment_name, segment_type, segment_subtype, bytes/1024/1024 meg_bytes
FROM user_segments
WHERE table_name = 'yourTableName';

To get the actual compression rating (no/low/high), try this using your table name:

SELECT table_name, column_name, partition_name, tablespace_name, compression, deduplication
FROM user_lob_partitions
WHERE table_name = 'yourTableName';

Hope this helps!


Oracle Active Session Pool Questions what is oracle active session pool? is it activated by default?
what is the default maximum active session that there is per user?

The Active Session Pool allows a limit to be place on the number of sessions that can be active on your instance. It is a part of the Resource Manager. The number possible is defined according to your current resource plan. The default plan allows an unlimited number of sessions. It is activated if resource management is performed in the instance. To find out if this is so then check the resource_manager_plan parameter:

show parameter resource_manager_plan

If there is a value then it is activated. You may see something like DEFAULT_PLAN as the VALUE. You can then query DBA_RSRC_PLAN_DIRECTIVES for the number of active sessions possible:

select GROUP_OR_SUBPLAN, ACTIVE_SESS_POOL_P1
  from DBA_RSRC_PLAN_DIRECTIVES
 where PLAN = 'DEFAULT_PLAN';

You'll get a few rows back relating to the number of groups/subplans that are part of this plan. NULL will indicate unlimited.
I prefer using Enterprise Manager to query and amend this. To find it in EM go to the Server tab then under the Resource Manager heading is Plans.

More information on the Resource Manager can be found here.


A little clarification on how 'LIMIT' works Not being a full blooded member of the DBA clan, but more of a dev clan, I find myself trying to figure out some slow running queries.

The subject of my question however is not 'Why my queries are slow' , but one of wanting to know how the 'LIMIT' command in MySql works.

At the moment, I'm still trying to troubleshoot the server performance itself, so as a way of giving it a little SQL to chew on (Just to make it do some work) I'm using the following:

SELECT * FROM <table> LIMIT 10

The table I'm using is a live table with an excess of 50Mil rows in it, and as I'm only conducting tests at the moment my expectation is this will return the first 10 records off the top of the table, and would easily do so in under 10 minutes.

However...

I'm seeing this SQL still running after 30 mins or more, and appearing not to do anything.

What I want to clarify specifically is this:

Does MySql attempt to fetch the entire table from a DB before then just returning the first 10 rows

OR

Does MySql fetch rows as it receives them and stop after it counts that it's recieved 10 rows

Cheers
Shawty

mysql should optimize a query like your example and not do a full table scan just to retrieve 10 records.  Adding an order by without an index before the limit 10 would perform a full table scan.

Are you sure that your "table" is really a table and not a view?  You might be selecting from a slow view.


LIKE search in bytea datatype (PostgreSQL) Table:

\d blob

 Column  |       Type   | Collation |                 Default
---------+--------------+-----------+------------------------------------------
 id      | integer      |           | nextval('modlink_blob_id_seq'::regclass)
 content | bytea        |           |

This statement:

SELECT COUNT(*) "job" INNER JOIN "blob" ON ("job"."input_id" = "blob"."id")
  WHERE UPPER("blob"."content"::text) LIKE UPPER('%csv%');

Error message:

ERROR:  invalid memory alloc request size 1989028364

What can I do to make this statement not fail?

The best way i found to make 'LIKE' search in PostgreSQL in columns with a lot of content was using tsvector columns.

One advantages is more fast than make 'like' searches and allow create index.

The boring part is create a trigger to replicate the content in a new column.

The article below shows how to use tsvector.

http://rachbelaid.com/postgres-full-text-search-is-good-enough/

Column  |       Type   | Collation |                 Default
---------+--------------+-----------+------------------------------------------
 id      | integer      |           | nextval('modlink_blob_id_seq'::regclass)
 content | bytea        |           |
 search  | tsvector     |           |

This is the SQL

SELECT COUNT(*) FROM "job" INNER JOIN "blob" ON ("job"."input_id" = "blob"."id")
WHERE blob.search @@ plainto_tsquery('csv');

A observation: I use text columns instead bytea.


In trying to setup back up device, net use command throws operating system error 53 when passing in a unc So I'm trying to use XP_CMDSHELL to set up my drive for using a backup.  I already have the drive mapped, but per http://blogs.msdn.com/b/varund/archive/2009/06/04/backup-sql-server-database-to-a-share-drive-on-another-server.aspx, I figured I'd need to mount the drive using net use.

But when I do, I get System error 53, 'The network path was not found.'

EXEC XP_CMDSHELL 'net use S: \\sql-backup'

I know this unc is accessible from the server because it is mounted as a network share on the sql server.

Any ideas why the unc is unreachable?

You have to give it both the server name and a shared folder, i.e., \\Server\SharedFolder.


PostgreSQL/RedShift - students that attended zero classes on a given day I have a couple of tables:

students

id,name,email,age
01,john,john@something.com,14
02,Mike,mike@something.com,15
03,Ted,Ted@something.com,14

Classes

classid,classname,date,studentid
01,Mathematics,2017-01-01,01
02,Physics,2017-01-01,01
03,Physics,2017-01-01,02
04,Chemsitry,2017-01-01,01

students.id = classes.studentid. Note that Ted has not attended any class on 2017-01-01. How do I find students that have zero classes on a given day? I am looking for an output like this:

name,email,date,class
Ted,ted@something.com,2017-01-01,NULL

SELECT <fields> FROM students LEFT OUTER JOIN class ON classes.studentid =  students.id WHERE classes.id IS NULL - the outer join will output one row per student for each matching class and one row per student with no matching classes, checking for classes.id being NULL will bring out just the output where there was no match.

Lookup how OUTER JOINs work and how they differ from INNER JOINs (note that by default if you don't specify otherwise joins are INNER) for more detail.

As a side note your model does appear to be in normal form: I would expect to have a table for students, a table for classes, and a table for the relationships between the two - your classes tbale appears to mix the latter two which may lead to consistency problems later.


Select from stored procedure Hello I have a table called Workers in my database. Also I have a stored procedure that returns a table with Id, xml Data and xml Schema by Worker.Id. I want to select all Worker.Id values and execute this procedure for each Worker.id. Here is some script:

create table #tmpXmlreturn
(
    Id int,
    Data xml,
    [Schema] xml
)

select dbo.Workers.Id as Id,
        Exec dbo.GetBranchByWorkerId Id,
into #temptable from
dbo.Workers

If you need me to provide more information please tell me.

This should do what you need.

DECLARE @Id INT

create table #tmpXmlreturn
(
    Data xml,
)

create table #Ids(
    Id int
)

INSERT INTO #Ids
select dbo.Workers.Id as Id
from dbo.Workers

WHILE EXISTS(SELECT * FROM #Ids)
BEGIN
  SELECT TOP 1 @Id = Id FROM #Ids

  INSERT INTO #tmpXmlreturn
  Exec dbo.GetBranchByWorkerId @Id

  DELETE FROM #Ids WHERE Id = @Id
END

SELECT * FROM #tmpXmlreturn
DROP TABLE #tmpXmlreturn


How to perform schema migration from array to many-to-many to lookup table? I currently am storing values in a column of type array like follows (this is just an example):

CREATE TABLE  blog_posts(
    id INTEGER PRIMARY KEY,
    blog_title TEXT,
    tags TEXT[]
);

With the column tags being an array of text values. I have since realized that due to the way I will be querying this data, I want to switch from using an array column to store my labels to a many-to-many lookup table instead.

CREATE TABLE  blog_posts(
    id INTEGER PRIMARY KEY,
    blog_title TEXT,
);

CREATE TABLE tags(
    id INTEGER PRIMARY KEY,
    label TEXT

);

CREATE TABLE blog_tags(
    blog_id INTEGER REFERENCES blog_posts(id),
    tag_id INTEGER REFERENCES tags(id)
);

I know how to do the alter schema commands needed to perform the migration for the schema itself but my question is how can I write a migration that will correctly transfer the data from the array to the new tables as well? (Also, I would preferably want the equivalent down migration)

Should I just write the SQL for the SELECTs and INSERTs that would be needed to accomplish this and have those run as part of the migration? Or is having any sort of SELECT or INSERT in a migration script considered a bad practice?

Should I even be trying to do a scripted migration like this at all and should this just be a manual step I have to do?

Is there some other method I have completely missed here?

Doing the needed SELECTs and INSERTs as part of the migration seems like the right approach, but I have never done a migration that required that so I find myself uncertain if that is the right approach.

This is all on PostgreSQL 9.6

Basically, I suggest a many-to-many design like this:

CREATE TABLE blog (              -- I suggest singular terms
   blog_id serial PRIMARY KEY    -- and descriptive names
 , blog    text NOT NULL         -- NOT NULL!
);

CREATE TABLE tag (
   tag_id serial PRIMARY KEY
 , tag    text NOT NULL          -- NOT NULL!
);

CREATE TABLE blog_tag (
   blog_id int REFERENCES blog
 , tag_id  int REFERENCES tag
 , PRIMARY KEY (blog_id, tag_id) -- !
);

See:

How to implement a many-to-many relationship in PostgreSQL?

And the migration can be done efficiently with a a single command using data-modifying CTEs:

WITH org AS (TABLE blog_posts)       -- read original table once
, ins_blog AS (                      -- fill table blob ...
   INSERT INTO blog (blog_id, blog)  -- ... keeping original IDs for simplicity
   SELECT id, blog_title
   FROM   org
   )
, bt AS (
   SELECT unnest(tags) AS tag, id    -- unnest tags & keep link to blog_id
   FROM   org
   )
, ins_tag AS (                       -- fill table tag ...
   INSERT INTO tag (tag)
   SELECT DISTINCT tag               --  ... with distinct tags
   FROM   bt
   ORDER  BY 1                       -- optionally order tags while being at it
   RETURNING tag_id, tag             -- new tag_id + connected tag
   )
INSERT INTO blog_tag (blog_id, tag_id)
SELECT id, tag_id
FROM   bt
JOIN   ins_tag USING (tag)           -- join to unnested tags, voilá
ORDER BY 1,2;  -- optional

Related:

Insert data in 3 tables at a time using Postgres

Don't forget to sync the sequence for table blog:

SELECT setval(pg_get_serial_sequence('blog', 'blog_id'), max(blog_id)) FROM blog;  -- sync blog serial

See:

How to reset Postgres' primary key sequence when it falls out of sync?

Consider an additional index on (tag_id, blog_id). See:

Is a composite index also good for queries on the first field?

db<>fiddle here


Problem when add administrator for Analysis Server ( SQL Server 2008 R2 ) I'm trying to add a user as an administrator in Analysis Server, doing this:

1 - Connect - Analysis Server - Right click on the Instance - Properties - Security - ADD :

This number was already there, So when I click ADD  and insert the user to be administrator, I see this error:

It says in English:

  an error occurred in the system ( microsoft.AnalysisServices)

But what error? I can't add anyone. I can't even add myself ( I'm AD user of all servers ). Can't even add, too, the account I use to start services ( It has domain admin full privileges ).

Obs: This is our test server. We need to create analysis services objects via software. I'm trying to add the software user as administrator here to be able to create a cube.

EDIT1:

Trying to trace the error, I only get this:

The same useless information.

After days with this problem, I deleted that number, and could add users with no problem.


Estimated vs Actual Number of Rows I have 5 tables that contain data split by quarter each table has a similar structure to the following:

ID_Column bigint PK,
EventType int,
DateStamp datetime PK
CustomerId int,
ARG_1 nvarchar(32),
ARG_2 nvarchar(32)

There is also a CHECK constraint on each table to ensure that the DateStamp is within the specified quarter.  I have then created a View across these tables which I am querying against.

I need to execute several queries which aggregate the date by week, month, 3 months and 12 months.  When I look at the Query Plan for my query it is querying all tables regardless of the filter on the DateStamp column.

I have a controller Stored Proc which in turns calls a Processing stored procedure. The processing stored procedure accepts the start and date as parameters, assigns them to local variables within the procedure and runs the query.

The query is similar to the following:

Select v.ARG_1,
       t.SomeColumn,
       Count(Distinct(v.Customer_Id))

From  dbo.My_View v
Join  dbo.AnotherTable t on v.ARG_2 = v.PKColumn
Where v.DateStamp >= @StartDate
And   v.DateStamp <= @EndDate
And   v.EventType = 1
And   t.SomeColumn = 'A_VALUE'
GROUP BY v.ARG_1, t.SomeColummn

I have also noticed that the estimated row count is wildly inaccurate (Estimated rows: 13 million vs Actual rows : 170 million). This confuses me as the statistics are up to date and I have rebuilt the indexes.

The performance of the query is acceptable for my purposes but I am wondering what could be the reason for these oddities?

EDIT: I am running SQL Server 2012 Standard Edition.

The anonymized XML Query plan can be found here

Up to date statistics does not necessarily imply perfect estimation. If you try to represent a range of 170 million values with 200 samples, even a small skew can throw you off by a factor of 10.

Here is a hint: time series are almost always accessed by time. Very likely DateStamp should be clustered key. For more detailes, post the exact DDL and actual execution plan (XML, not picture).


Problem in PostgreSQL replication - ALTER DATABASE Recently I put to work a Master/Slave replication in my PostgreSQL (9.3). Everything is ok... insert, update, delete, in data and changes in tables... but when I changed the data base name the replication not works. The name of the Slave database has not changed. How can I configure the master/slave replication to do this (replication of alter database rename)?

I assume that you are talking about “streaming replication”, not some other kind of replication (streaming replication is the only kind of replication that I know that replicates DDL).

Streaming replication should replicate ALTER DATABASE ... RENAME TO ... just like any other change.

If it doesn't, there is probably some replication conflict that delays the application of the change. What is max_standby_streaming_delayset to? try to disconnect from the standby server and see if that does the trick.


Multi condition DAX row filter I am trying to convert the following SQL row level security function into a DAX filter within a Tabular model

CREATE FUNCTION [Security].[fn_securitypredicate](@BrandID AS INT, @ChannelId AS INT)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN
     (SELECT 1 AS fn_securitypredicate
            WHERE (EXISTS ( SELECT 1 FROM   security.RLSStaffBrand  WHERE StaffUsername = SYSTEM_USER AND BrandId = @BrandID)
              AND  EXISTS ( SELECT 1 FROM   security.RLSStaffChannel WHERE StaffUsername = SYSTEM_USER  AND ChannelId = @ChannelID) )

               OR (        EXISTS (SELECT 1  FROM   security.RLSStaffBrand  WHERE StaffUsername = SYSTEM_USER  AND BrandId = @BrandID)
                   AND NOT EXISTS ( SELECT 1  FROM   security.RLSStaffChannel WHERE StaffUsername = SYSTEM_USER ) )-- this user is not restricted by Channel

                OR (NOT EXISTS ( SELECT 1  FROM   security.RLSStaffBrand WHERE StaffUsername = SYSTEM_USER)
                    AND EXISTS ( SELECT 1  FROM   security.RLSStaffChannel

WHERE StaffUsername = SYSTEM_USER  AND ChannelId = @ChannelID) )

)
    GO

So far I have the following DAX filters, but this only handles the first condition in the SQL code. I don't know if it's even possible to replicate the rest in DAX.

='Brand'[BrandId]=LOOKUPVALUE('RLSStaffBrand'[BrandId], 'RLSStaffBrand'[StaffUsername], USERNAME(), 'RLSStaffBrand'[BrandId], 'Brand'[BrandId])

='Channel'[ChannelId]=LOOKUPVALUE('RLSStaffChannel'[ChannelId], 'RLSStaffChannel'[StaffUsername], USERNAME(), 'RLSStaffChannel'[ChannelId], 'Channel'[ChannelId])

Not an answer to your exact question, but instead of translating the SQL to DAX and maintaining it in two places, you can reuse the SQL logic in your RLS implementation to materialize tables in your tabular model with the allowed (Username,BrandId) pairs, and the allowed (Username,ChannelId) pairs enumerated.

So write a view and load a table in your tabular model with the results of a query like this:

select s.BrandId,c.ChannelId,u.StaffUserName
from dbo.Brands b
cross join dbo.Channels c
cross join dbo.Staff s
cross apply   [Security].[fn_securitypredicate](s.BrandId,c.ChannelId,s.StaffUserName) sp
where sp.fn_securitypredicate = 1

Which will enumerate all the permissible combinations of (BrandId, ChannelId, StaffUserName).  Then use that new table in your DAX row filters by setting up bidirectional cross filtering between this table and Brand and this table and Channel.

David


Id not in set gives no result Basically if I do the following:

SELECT distinct id FROM Table1

I get 20 000 rows

SELECT distinct id FROM Table1
WHERE id IN (SELECT id FROM Table2)

I get 4000 rows

So when I do the following, I expect to get 16000 rows, but I get no result.

SELECT distinct id FROM Table1
WHERE id NOT IN (select id from Table2)

-Empty result-

Why is this? Can it be that I run out of memory or something?

For NOT IN queries, it's safer to use NOT EXISTS if columns are NULLable, rather than try to handle NULLs with some additional logic.

Here's a quick example script.

CREATE TABLE #t1 (Id INT NULL)

CREATE TABLE #t2 (Id INT NULL)

INSERT #t1 ( Id ) VALUES (1), (NULL), (3)

INSERT #t2 ( Id ) VALUES (1), (2), (NULL)

SELECT *
FROM #t1 AS t
WHERE t.Id NOT IN (SELECT t2.Id FROM #t2 AS t2)

SELECT *
FROM #t1 AS t
WHERE NOT EXISTS (SELECT * FROM #t2 AS t2 WHERE t2.Id = t.Id)

DROP TABLE #t1, #t2

Full disclosure: I do not work for either NOT IN Inc. or NOT EXISTS Corp.


How to change default mongo port on a bitnami instance I am running a bitnami instance of mongodb for our pre-production. The default port for mongo is 27017. But for security reasons i want to change it but it is not possible.

I have tried these things:

changes the port in mongodb.conf and restarted the server. It shows the below error:

connecting to: mongodb:///opt/bitnami/mongodb/tmp/mongodb-27017.sock/
2018-04-20T12:55:04.373+0530 W NETWORK  [thread1]
Failed to connect to /opt/bitnami/mongodb/tmp/mongodb-27017.sock:0, in(connect), reason: Connection refused
2018-04-20T12:55:04.373+0530 E QUERY    [thread1] Error: couldn't connect to server /opt/bitnami/mongodb/tmp/mongodb-27017.sock:27017, connection attempt failed :
connect@src/mongo/shell/mongo.js:237:13
@(connect):1:6
exception: connect failed

I restarted mongod with different port. Still i am not able to do it.

when i switch back to 27017. It comes back to normal.

Any suggestions on how to do it the right way?

I am running a bitnami instance of mongodb for our pre-production. The
  default port for mongo is 27017. But for security reasons i want to
  change it but it is not possible.

As you mention error code below

  Error: couldn't connect to server
  /opt/bitnami/mongodb/tmp/mongodb-27017.sock:27017, connection attempt
  failed : connect@src/mongo/shell/mongo.js:237:13 @(connect):1:6
  exception: connect failed

As per your error code location (/opt/bitnami/mongodb/tmp/mongodb-27017.sock) mongod tring to connect with port 27017. May be your mongod.conf data files and log files has not configure properly or may be you don't have a authentication permission of that folder where is mongod.conf .

As Bitnami MongoDB documentation here On Unix, the MongoDB clients can connect to the server using a Unix socket file at

installdir/mongodb/tmp/mongodb.sock

The default port in which MongoDB listens is 27017

The main MongoDB log file is at installdir/mongodb/log/mongodb.log

The MongoDB configuration file is located at installdir/mongodb/mongodb.conf

The official MongoDB documentation has more details about how configure the MongoDB database.

For your further ref here , here and here


Is Order part of a result set? This question got me thinking:
ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C)
In it the questioner states two functions give the same result set, however they don't in that they have identical rows but they are always in different (and consistent) orders.

Is the order of a result set part of the result set? Wikipedia implies no (but doesn't strongly determine an answer, nor does it speak from a position of authority):

https://en.wikipedia.org/wiki/Result_set

Is

Sector  Name
A   Alpha
B   Beta

the same result set as

Sector  Name
B   Beta
A   Alpha

I personally only work with SQL Server, does the answer change by platform? Is the answer a matter of opinion only? (If so please give answers of fact only, or of consensus by professional bodies at any rate- not your personal opinion please)

Possibly the ANSI 92 definition held at

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

gives the answer. Section 3.1 Definitions seems to draw a distinction between the idea of a result set and a result sequence. The distinction was not in my awareness when I wrote the question using the former phrase

  v) sequence: An ordered collection of objects that are not necessarily
  distinct.

  w) set: An unordered collection of distinct objects. The collection
  may be empty.


MySQL on Ubuntu 32bits vs 64bits My web app server is running on Ubuntu 12.04 server 32bits.
The mysql db is going to be installed on a different Ubuntu 12.04 server. Should I use 32 bits for the server on which mysql is or 64 bits?

If the db server is 64bits, can it cause issues knowing that the webapp is running on a 32 bits or it does not matter at all?

Many thanks,
Roman

In 2014, using 32bit architecture for any critical app is insane. So, I would suggest:

Upgrade your web-app server to 64bit (soon you will not find 32bit
editions for most software). It is not that hard and you get better memory management, performance with the exact same hardware.
Still, if you do not want to do that, install MySQL DB (64bit edition) on a 64bit Ubuntu server to get at least there the benefits of a 64bit architecture. There is no danger when you interact with SQL queries if the architectures of the web-app server and the DB server are different. When (when and not if because it will happen eventually) you will be forced to change your web app server architecture, you will have one less thing to worry about.


Disable autocommit for DB2 connection in Oracle SQL Developer I am connecting to the DB2 database using oracle SQL developer, but automatically it is committing, i checked in the advanced menu the auto commit feature is turned off. I am confused now, can someone help me in switching off auto commit.

Check whether auto-commit is really off (or if it's a glitch in the gui), by (for example):

1. create table T (x int);
2. rollback;
3. select * from t;   -- should result in an error


Is it possible to tell if an incoming connection is using an SQL Server Alias? Using SQL Server Configuration Manager to Create a Server Alias for Use by a Client is cool tool used in many cases when it works

In my DBA position I do not have access to the application server, to see if the SQL Server Configuration Manager or the SQL Server Client Network Utility (not sure this is the correct link) was used to create an alias.

Is there any way I can use SSMS, or any logs, events, etc to identify that an incoming connection is connecting with an Alias?

No, that translation is done on the client side and so, with or without an alias, those connections are going to look the same to SQL Server. There is no evidence in DMVs like sys.dm_exec_connections or sys.dm_exec_sessions what name/IP/port combination was entered by the user to resolve to the server where SQL Server is running. SQL Server itself only cares that the connection was established.

Unless you can infer that anyone not using an alias is using shared memory or named pipes, therefore anyone using TCP must be using an alias. I can assure you that this is not a safe assumption.


What table should I host data linking two objects from separate tables? I've tried searching around, but I'm not too sure how to correctly formulate this question, and found nothing. Could be a duplicate.

What is the best way (most efficient way) to host data that will link two objects together from separate tables? Let me explain with an example; there is a table named Users and another named Groups, a user can be part of a group. How should I hold the information on the groups the users are in.

Should I:

Leave the Users table empty, and put an array of user ids under the group in the Groups table.
Leave the Groups table empty, and put an array of group ids under the user in the Users table.
Put an array of both the user ids, and group ids under both the user and the group in both tables.

We'll say for the example that both bits of information (users in group X, and groups in user X) will be polled an equal ammount, both are accessed in an equal manner. Also, there are more users than groups.

The design part of me tells me I should use solution 2, it seems like the prettier way to go. But I'm thinking solution 1 would be faster (between 1 and 2) as there are more users than groups. And of course, solution 3 would be the fastest, but it will take more memory, and it is harder to manage (one table could have different information than the other if the program has bugs).

I know these kind of decisions will only gain tiny ammounts of time, or memory, but I like micro-optimisation, so what would you recommend? Maybe there is another solution I am not aware of?

If Users can only be members of one group, you need only store a GroupId in the Users table. If not, the pattern you are looking for is commonly known as a "Join" table, containing only foreign keys and perhaps a generated primary key.

If you have primary key UserId (uniquely identifies a user record) for the Users table and GroupId for the Groups table, you can create a third table:

CREATE TABLE [dbo].[GroupUsers](
    [UserId] [int] NOT NULL,
    [GroupId] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[GroupUsers]  WITH CHECK ADD  CONSTRAINT [FK_GroupUsers_Groups] FOREIGN KEY([GroupId])
REFERENCES [dbo].[Groups] ([GroupId])
GO

ALTER TABLE [dbo].[GroupUsers] CHECK CONSTRAINT [FK_GroupUsers_Groups]
GO

ALTER TABLE [dbo].[GroupUsers]  WITH CHECK ADD  CONSTRAINT [FK_GroupUsers_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([UserId])
GO

ALTER TABLE [dbo].[GroupUsers] CHECK CONSTRAINT [FK_GroupUsers_Users]
GO

This design allows for one-to-one, one-to-many and many-to-many relationships. In practice, its performance and scalability are excellent, as long as the tables are properly indexed.

To get all users in a group given its GroupId:

SELECT *
FROM Users u
INNER JOIN GroupUsers gu
ON u.UserId = gu.UserId
WHERE gu.GroupId = @GroupId

Many times the relationships need to be traversed across the foreign key table. For instance, to select the groups a user belongs to, given their username (assuming there is a Username field in Users):

SELECT g.*
FROM Groups g
INNER JOIN GroupUsers gu
ON g.GroupId = gu.GroupId
INNER JOIN Users u
ON gu.UserId = u.UserId
WHERE u.Username = @Username

I usually use views to simplify queries that need this sort of traversal.


Question on MySQL Partitioning Using version 5.5 (on which I'm stuck), I have a table I want to partition, here is the DDL:

CREATE TABLE `emp` (
  `id` int(11) DEFAULT NULL,
  `date1` bigint(20) DEFAULT NULL,
  `gen_year` int(11) GENERATED ALWAYS AS (year(from_unixtime(`date1`))) VIRTUAL,
  `gen_month` int(11) GENERATED ALWAYS AS (month(from_unixtime(`date1`))) VIRTUAL,
  UNIQUE KEY `id` (`id`,`gen_year`,`gen_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH (12*gen_year+gen_month) PARTITIONS 77 ;

As you can see I want to partition by unique month (taking the year into account). I set 77 partitions because the test data I have put in there uniformly cover 77 months.

When I run the 1st query below, I get pruning just fine, only one partition gets queried.
When I run the 2nd query, I'd expect just a few partitions to be queried, but instead ALL are.

select count(*) from emp where gen_year=1984 and month=8;
select count(*) from emp where gen_year=1984 and month>8;
select count(*) from emp where gen_year=1984 and month BETWEEN 8 AND 12;

EXPLAIN PARTITIONS gives one partition for the first query, but all 77 partitions for the others.

Please advise on what I'm missing.

Any range (eg month>8 or month BETWEEN 8 AND 12) leads to punting on partition pruning, hence 77 partitions are selected.  That is, PARTITION BY HASH is useless for ranges.  Meanwhile, non-partitioning is excellent for the other type of queries -- "point queries".  So don't bother using BY HASH!

Where does id come from?  Typically it is an AUTO_INCREMENT, but apparently not for your table.

select count(*) from emp where gen_year=1984 and month=8;

can be significantly sped up by

Storing date1 as either a TIMESTAMP or DATETIME.  With fractional seconds if needed.
Adding INDEX(date1)
Writing the query thus:

SELECT COUNT(*) FROM EMP
     WHERE date1 >= '1984-08-01'
       AND date1  < '1984-08-01' + INTERVAL 1 MONTH

If you build and maintain a Summary table, then the query becomes 'instantaneous', and even the other query will be faster than

     WHERE date1 >= '1984-09-01'
       AND date1  < '1985-01-01'

InnoDB really needs a PRIMARY KEY.  Promote the UNIQUE key to primary.  (Though I have serious doubts about that index.)

These are all "range" tests:  BETWEEN, >=, <.  All may work well with non-partitioned tables, but fail miserably on PARTITION BY HASH tables (when the range involves the partition-key).


Can I use a mariadb/mysql dump from older version in newer version? I'm a mariadb/mysql newbie. I want to take a complete backup.

Apparently this is the way (please correct me if I'm wrong):

mysqldump \
  -h... -u... -p... \
  --hex-blob --routines --triggers \
  --all-databases \
  --add-drop-database --add-drop-table \
  | gzip > backup.sql.gz

But according to this answer:

  The drawback is that mysqldumps created this way can only be reloaded into the same majot release version of mysql that the mysqldump was generated. In other words, a mysqldump from a MySQL 5.0 database cannot be loaded in 5.1 or 5.5. The reason ? The mysql schema is total different among major releases.

That was written in 2011 - is it still true?

major   Release Candidate   General Availability
8.0     2017-09-21 8.0.3    2018-04-19 8.0.11
5.7     2015-04-08 5.7.7    2015-10-21 5.7.9
5.6     2012-09-29 5.6.7    2013-02-05 5.6.10
5.5     2010-09-13 5.5.6    2010-12-03 5.5.8
5.1     2007-09-24 5.1.22   2008-11-14 5.1.30
5.0     2005-09-22 5.0.13

(I don't have MariaDB's GA dates.)

You can try to use a mysqldump on a different major version, but there may be incompatibilities.

Note that an "old" mysqldump may not understand a "new" db, but it is more likely that a "new" mysqldump will correctly read "old" data.

Some notable incompatibilities:

TYPE --> ENGINE  (4.1?)
fractional seconds (new in 5.6.4 / 10.0)
VISIBLE indexes  (8.0)

The 8.0 mysqldump apparently insists on including the default, but "new" option of VISIBLE.  This makes a mess when moving a dump (of any version) taken by the mysqldump for 8.0 and loading that dump on any MySQL/MariaDB other than MySQL 8.0.

You may notice comments like this:  This allows the dumps to (mostly) work across any version:

/*!50100 PARTITION BY ... */

That says "5.1 and newer can handle it, but if the target version is older than 5.1, treat this as a comment."  Caveat:  since the MariaDB numbering diverged from MySQL, this style of comment somethings screws up.

  I'm asking if it would create a problem in the future, when I will need to move to a newer version. If that's the case, then I need to take it into account today and use a different approach, as specified in that linked question.

They will always allow dumping from one version to the next major version.  It is a major way to upgrade.  Skipping a version may cause hiccups, but even that rarely causes trouble.


Use COUNT and DISTINCT

CREATE TABLE table1 (
  `user_id` INTEGER,
  `product` VARCHAR(1)
);

INSERT INTO table1
  (`user_id`, `product`)
VALUES
  ('1', 'A'),
  ('1', 'A'),
  ('1', 'B'),
  ('2', 'A'),
  ('2', 'B'),
  ('3', 'A'),
  ('3', 'B'),
  ('3', 'B'),
  ('3', 'C'),
  ('3', 'D');

SELECT `user_id`, COUNT(DISTINCT `product`) FROM table1 GROUP BY `user_id`

user_id | COUNT(DISTINCT `product`)
------: | ------------------------:
      1 |                         2
      2 |                         2
      3 |                         4

db<>fiddle here


Problems in Choosing Foreign Keys :S I am designing my very first relational database. I am now in the conceptual design, building tables and views, selecting the primary and foreign keys and normalizing everything. But now, I am stock with a particular table; I have a hard time selecting the appropriate foreign keys and I need some help. Here is a fictive, simple case to represent the relationships where I have a problem. Let's say I have those 3 tables:

Person Table

Name

DOB

Name-DOB (concatenated PK)

Name Table

Name (PK)

Meaning

Origin

DOB Table

DOB (PK)

Astrological Sign

So far it's okay, but the next table is where I get confused. Let's say I have a many-to-many recursive relationship that points from my "Person Table" to my "Person Table" again, to map the weddings. For this many-too-many relationship, I need a linking table. This table (so far in my design) goes as follow:

Wedding Table

Husband name

Husband DOB

Wife Name

Wife DOB

Now for the "Wedding Table" I have some questions:

Question 1: Do I need a Primary Key for the linking tables on the many-to-many relationships ? If so, what PK may I use ? The only one I see (other than inventing a random "serial number" for each line of data) would be a concatenated of the 4 attributes in the table, because any concatenate of 2 or 3 attributes has chances to be "not-unique" (let's say someone can have a 2nd wedding when the 1st husband/wife dies, for example. I'm sorry, that's the best example of fictive tables I found to illustrate my real life problem). But isn't it too long, a concatenate of 4 attributes ?

Question 2: Do I need to normalize the linking tables on the many-to-many relationships ? If so, how may I do that ? Because it appears to me that a 2nd (elimination of partial dependency) and 3rd (elimination of the transitive dependency) is impossible since the Husband DOB depends only on the Husband Name (same for the wife)...

Question 3: What Foreign Key do I use to link the "Wedding Table" to the "Person Table" ? Should I use the "Husband Name-Husband DOB" and "Wife Name-Wife DOB" concatenates since the "Name-DOB" is the PK of the "Person Table" ? If so, do I need to keep the individual attributes (that is Husband Name, Husband DOB, Wife Name and Wife DOB) in the Wedding Table since they are not needed in the many-to-many relationship ? Will is pose any problem in the updating and feeding of information in the database via user forms if those individual attributes are not there ?

Thanks to anyone who can help me with my questions !

I will be using MS SQL terms below but you can translate them into the DBMS that you will be using. I also made some assumptions that seemed logical, so you can drop those if they do not fit your model. I would setup the tables in the following manner:

Person Table
PersonID int PK
FName Varchar(50)
LName varchar(50)
DoB Date FK

Name Table
Name    varchar(50) PK
Meaning varchar(MAX)
Origin  int FK

DOB Table
DoB                 date    PK
AstrologicalSign    int FK

Wedding Table
WeddingID   int PK
HusbandPersonID int FK
WifePersonID    int FK

Origin Table
OriginID            int         PK
OriginDescription   varchar(50)

Astrological Sign Table
AstrologicalSignID          int         PK
AstrologicalSignDescription varchar(50)

I am a visual person and it helps me to see things laid out graphically, so I am attaching a diagram as well.

The personID, astrologicalsignID, weddingID and originID are all setup as identity fields that auto increment. I can provide a SQL backup of the database if that would help you.


The table 'table_name' is full in MySQL One of our INNODB tables contained about 350 million entries. All of a sudden, we started getting the error 'The table 'table_name' is full'. The table contains logs-like entries(click logs, basically). Since we didn't need data that was a year old, as a temporary fix I deleted about 30 million entries. However, since we are getting about 5-6 million entries everyday now, I fear that this error would crop up again. Any thoughts on how to overcome it ?

The specs of the machine are : 48 GB ram, 757 gb SSD. Currently, the used space on disk is 500 gb. 30 % free space.

Fixed in 5.7.4:  A page allocation for an undo log due failed with a “table is full” error message instead of an “undo log is full” error message.

5.6.3 is pre-GA.  Recommend you upgrade at least to a later 5.6.xx.

See https://dev.mysql.com/doc/refman/5.6/en/innodb-undo-logs.html as for a discussion of what can be done about "undo tablespaces" starting with 5.6.3.


Join external data in SELECT query I have few (~20) names of products corresponding to an integer id that needs to be joined on products while exporting products data (using COPY (<query>) TO). The burden is that these names do not rest in DB, rather in Redis (rails's translations fyi). Therefore, I'll be collecting these names before each export. Now, having these names outside the DB, how can I build the query to join them?

I've tried to join these names on-the-fly:

SELECT
...
LEFT JOIN (
    SELECT unnest('{1,2,3}'::int[]) AS id,
           unnest($${"name1", "name2", "name3"}$$::text[]) AS name
) AS product_names ON product_names.id = products.type_id
...

But I've got busted by the amount of products rows (~500k) and lacking indices for that structure. Is there some (better) way to join these data on-fly? I guess one cannot index these on-fly data though.

Which is a good way to solve that situation? I can think of creating temporary table like:

SELECT ... INTO TEMPORARY ...;

but is it wise to have that stay on for long transaction, while exporting (~30 mins)? Or can I omit transactions while using temporary tables and how? Or am I just troubling myself with needless problems while the best solution is to just store those names as a regular table, only truncating it before each export to have actual names?

-- postgres 9.3.10

Assuming current Postgres 9.4.

Since you are dealing with just ~ 20 names of products, it's largely irrelevant how you provide those to the query. An index would be a waste of time, Postgres is going to use a sequential scan anyway (judging from what you disclosed in the question).

You can use a temporary table, which lives for the duration of the session, not just transaction like you seem to assume. And you can even create indexes on temp tables, it just wouldn't help in this case.
Be sure to run ANALYZE on temp tables manually:

Are regular VACUUM ANALYZE still recommended under 9.1?

Or you can unnest a provided array literal just as well. But don't rely on the outdated trick of unnesting two arrays in the SELECT list in parallel. This is error prone. Move it to the from clause. I'd suggest:

SELECT
...
LEFT JOIN unnest('{name1,name2,name3}'::text[]) WITH ORDINALITY
  product_names (name, type_id) USING (type_id)
...

More:

Order of returned record with IN statement postgres

Assuming the column name type_id is unambiguous in the tables to the left.

If you don't actually have ascending type_id starting with 1 like your example suggests, consider the new variant of unnest() taking multiple array parameters:

SELECT
...
LEFT JOIN unnest('{4,7,9}'::int[], '{name1,name2,name3}'::text[])
  product_names (type_id, name) USING (type_id)
...

More:

Passing arrays to stored procedures in Postgres


MySQL installation and data location on Windows I'm gonna install MySQL 5.5 in a virtual Windows Server. I'd like to have the data in an separate partition (it may be in a different virtual disk) than the system one. I'm considering two options:

Install MySQL in the default installation path ("C:\Program Files\MySQL\MySQL"), create the data folder in the "D:\mysqldata" and modify the "my.cnf" to point to the new folder
Install MySQL in directly in  "D:\mysql"

Are there any difference between then? Is there a recommended approach?

I do not think that the location matters rather it is a matter of personal preference, and the MySQL installer will automatically update the my.cnf file for you.

However I would rather go with D:\mysql as the installation directory because its lowercase, has no special characters (spaces, capital letters) and is not in the Program Files directory which you may need to clean out for a new install or upgrade.

You may also need to add the D:\mysql\bin directory to the PATH environment variable so that you can run the MySQL commands from any command prompt


Return ResultSet with column names from functions I have the following postgresql Function:

CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE)
RETURNS SETOF message
AS $$
DECLARE
    sql varchar;
BEGIN
    sql := 'SELECT * FROM message WHERE (receiver_id IS NULL OR receiver_id = $1)';
    IF qid IS NOT NULL THEN
        sql := sql || ' AND queue_id = $2';
    END IF;
    IF ctxt IS NOT NULL THEN
        sql := sql || ' AND context = $3';
    ELSE
        sql := sql || ' AND context IS NULL';
    END IF;

    IF $4 IS NOT NULL THEN
        sql := sql || ' AND sender_id = $4';
    END IF;

    sql := sql || ' ORDER BY';
    IF byTimeStamp THEN
        sql := sql || ' arrive_time ASC';
    ELSE
        sql := sql || ' priority DESC';
    END IF;

    sql := sql || ' LIMIT 1';
    RETURN QUERY EXECUTE sql
    USING messageFor, qid, ctxt, messageFrom;
END;
$$
LANGUAGE plpgsql;

Now, the above function works fine but the resultSet does not contain column names (for example: a simple SELECT statement returns results with column names). I need to be able to access the result of the above function in Java code as:

Connection con;
PreparedStatement ps = con.prepareStatement("select readMessage(1, 1, 1, 1, true))";
ResultSet rs = ps.executeQuery();
rs.getInt(<column-name>)

instead of

rs.getInt(<column-index>)

for more readable code. How can I modify my postgresql FUNCTION to achieve this?

Consider the example below:

CREATE TABLE test.child (
    id    integer,
    p_id1 integer,
    p_id2 integer
);

CREATE OR REPLACE FUNCTION named_cols()
RETURNS SETOF test.child
AS $$
  SELECT 0, 12, 23;
$$ LANGUAGE sql;

SELECT named_cols();
 named_cols
────────────
 (0,12,23)

SELECT * FROM named_cols();
 id │ p_id1 │ p_id2
────┼───────┼───────
  0 │    12 │    23

If you call the function the first way, you will get back a record, without the column names.  It looks like you are after the second one - you would experience the same behaviour with any programming language, not only Java.