Ratio per Group in PostgreSQL If I have rows like this:

lec1 major1 class1 10
lec1 major2 class1 40
lec1 major1 class2 30
lec1 major3 class3 35
lec2 major1 class3 15
lec2 major3 class3 10

And I need it to combine the ratio into:

lec1 major1 0.2 -- 10/(10+40)
lec1 major2 0.8 -- 40/(10+40)
lec1 major1 1 -- 30/30
lec1 major3 1 -- 35/35
lec2 major1 0.6 -- 15/(10+15)
lec2 major3 0.4 -- 10/(10+15)

Or something like this:

lec1 major1 1.2 -- 10/(10+40) + 30/30
lec1 major2 0.8 -- 40/(10+40)
lec1 major3 1 -- 35/35
lec2 major1 0.6 -- 15/(10+15)
lec2 major3 0.4 -- 10/(10+15)

What query sould be done?


Is it possible to use Change Tracking on a Replication target? We have a setup where the production SQL Server databases are replicated to an offsite location using SQL Server Replication (transactional replication).

I am looking at using SQL Server Change Tracking to get the updated data in order to perform some processing and add values to a cache. Before I start down this road, does anyone know if it's possible to use Change Tracking on a replication target? It seems like it should work, but it also seems complex enough that there would be gotchas.

does anyone know if it's possible to use Change Tracking on a replication target?

When you say replication target, do you mean subscriber ? Irrespective, of it, CT will work with replication.

Things to consider when implementing Change Tracking (CT):

Make sure you have retention & clean up of the data in the hidden CT tables.
Enable CT on the tables that you need. Dont go haywire and enable it on all the tables in the database.
Read carefully the article written by Kendra - Performance Tuning SQL Server Change Tracking


How can I select all online friends? I'm currently working on a social network software written in PHP & MySQL, and it's all pretty fine, but one thing I'm stuck about is online friends. My tables looks following

Users

+-----+------------+---------------------------------------------------------------+
| uid | timestamp  | profile_img                                                   |
+-----+------------+---------------------------------------------------------------+
|   1 | 1322858590 | /photos/356a192b7b/profile.jpg                                |
|   2 |   13700000 | /photos/356a192b7/profile.jpg                                 |
+-----+------------+---------------------------------------------------------------+

Friends

+------+------+
| uid1 | uid2 |
+------+------+
|    1 |    2 |
+------+------+

now I want to select all online friends (online = timestamp > current time - 30 seconds)

My try to create a query to select online friends is

SELECT  f.uid1 as friend1,
    f.uid2 as friend2,
    u1.profile_img profile_img1,
    u2.profile_img profile_img2
FROM Friends f
INNER JOIN Users u1 ON u1.uid = f.uid1
INNER JOIN Users u2 ON u2.uid = f.uid2
WHERE
    f.uid1 = 1 OR
    f.uid2 = 1;

I have no idea how to compose the timestamp check, considering that

friend1 can be me, but it can also be my friend.
friend2 can be me, but it can also be my friend.

Is there a solution for this or should I think a bit more about database schema?

Personally, I would have friends be a one-way association; this would double the length of your friends table but eliminate many places where you would need to duplicate logic depending on whether the current user was listed on the left or the right of each entry.

+------+------+
| uid1 | uid2 |
+------+------+
|    1 |    2 |
|    2 |    1 |
+------+------+

Given a choice between saving RAM/hard drive space, and simpler business logic, the choice should almost always fall to simpler rather than smaller. I suspect that most use-cases will have this performing better as well.


When does MySQL begin to start sending data to a client? I am working on MySQL 5.7 with the default configuration of InnoDB storage engine. I have two questions each for a different scenario and need to know the internal working on how MySQL sends data to the client.

Scenario 1:

If there is a select query on a very large data (say 1 GB), does MySQL pull the entire data from the disk (from .idb files) to the InnoDB Buffer Pool or does it send data to the client in batches without exhausting the server's memory?

Scenario 2:

A simple inner join of two tables without ORDER BY or GROUP BY clause (i.e the ordering of data doesn't matter). In such case, does MySQL send the data as and when the join data is accumulated (i.e in batches) or does it construct the entire JOIN result in the buffer pool and sends the data after? Is the entire data loaded in-memory?

The my.cnf configuration of my local machine is as follows:

[mysqld]
performance_schema=OFF
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
innodb_buffer_pool_size=4294967296
secure-file-priv = ""

Note: I have disabled the buffer pool load on shutdown and startup so as to simulate the condition where none of data is cached when the server is started.

The buffer pool is not used to materialize query results. It stores table and index pages to be shared between all connected sessions.

Each session allocates its own result buffer, where the selected data is copied from the buffer pool. This applies whether a join is involved or not.

If no additional operation is required, partial results will be sent to the client as soon as the result buffer is full. If the result set must be sorted, that will be done in the sort buffer (and temporary tables if necessary), then sorted data will be sent to the client, via the result buffer, as before.

You can read more about how MySQL uses memory in the documentation.


Can I create alert on ora-20118 from Enterprise Manager? Can I create an alert on a user-defined ORA- error?

I want EM to notify me when the following error happens:

begin
raise_application_error(-20118, 'myerror');
end;
/

You could use DBMS_SYSTEM.KSDWRT to write the error to the alert log when you catch the relevant exception.

Then you can configure Enterprise Manager to alert on that error code. You've two options here:

Modify the regex for the existing error log analysis to cover this error code. This would be much easier, but might be a little inflexible.
Write a Metric Extension to check the alert log for this error. This would be a lot more difficult, but would allow you to create customised error messages and responses to this specific error.


MySQL: TEXT and VARCHAR Speed and Size Does varchar and Text affect the database size?
Also, do they differ in speed?

I have a Database that will have at least 4000 to 8000 new rows per day.
I'm quite worried about how it performs. This is my first time setting up an Enterprise-grade system and database though.

Use what you need and don't worry about performance.  But use only what you need.  There are minor inefficiencies in saying TEXT when VARCHAR(255) will suffice.  Ditto for VARCHAR(255) when VARCHAR(20) will suffice.

Here's why:  If a SELECT needs build a tmp table (eg, for ORDER BY), it first tries to use a MEMORY tmp table (better), but may have to fall back on a MyISAM table (slower).  Any kind of TEXT or BLOB forces MyISAM.  VARCHAR turns into CHAR (for the tmp table); if the tmp table gets too big for MEMORY, it converts to MyISAM.

TINYTEXT is probably never a good idea.

A related issue:  CHAR(...) is useful only if the field is (1) constant length, and (2) declared with the appropriate CHARACTER SET.  A common mistake is putting a fixed-length hex string in a utf8 CHAR field.

VARCHAR and TEXT (all sizes) have a small length field prefixing them.  This is relatively insignificant.


Terribly slow Disk Read (I/O) When fixing corrupt MyISAM table (9GB) So I've launched the myisamchk utility with repair option on all tables within the database.

Smaller tables (1GB max) got fixed in no time, virtually seconds. However, my largest table (9GB) takes forever (20mb/min) to fix. My server is down because of that.

I've checked 'iotop -ao' and this is what i got:

Whats going on here? The read of 300 KB/S is VERY HIGH in this screenshot, i was aiming at capturing the super-fast write. Sometimes the read spikes to 5-10 MB/s, but for the rest of the time its within 0-50KB/s range.

The table has only several columns, 2 indexes (1) Long, (2) Varchar(72). Mysql 5.5.56, CentOS 6. Its doing repair by sort.

To anyone whom it may concern;

The problem of such slow disk read lies in the fragmentation of the data. A subsequent repair of the same table gives very high disk read i/o, indicating sequential access as opposed to random access.

The table has plenty of deleted rows, and the newly inserted records must then be read in a random fashion, resulting in slow read speeds. Again, after the table was repaired, the records where stored sequentially, and the same repair operation gave very fast results. I guess this is another reason to use InnoDB over MyISAM.

Thanks, Michael.


Query Error when trying to run query Please bear with me as I am relatively new to SQL. I am using MySQL and trying to run a query against a DB with two tables ('Network', and 'Server'). When I trying a portion of the query it executes without fail, however, when I try and add to it, I receive nothing but errors ranging from 1046 to 1140 to 1241. I am sure it is a matter of syntax and formatting, but I cannot figure it out. Please help. Below is my query:

SELECT (SELECT CONCAT(ROUND(SUM(server.Tech=7)/COUNT(server.Tech) * 100,2), '%') AS Server,
       CONCAT(ROUND(SUM(network.Tech=7)/COUNT(network.Tech) * 100,2), '%') AS Network
           FROM network join server) AS V,
    (SELECT CONCAT(ROUND(SUM(server.Tech=2)/COUNT(server.Tech) * 100,2), '%') AS Server,
       CONCAT(ROUND(SUM(network.Tech=2)/COUNT(network.Tech) * 100,2), '%') AS Network
           FROM network join server) AS J
    (SELECT CONCAT(ROUND(SUM(server.Tech=4)/COUNT(server.Tech) * 100,2), '%') AS Server,
       CONCAT(ROUND(SUM(network.Tech=4)/COUNT(network.Tech) * 100,2), '%') AS Network
           FROM network join server) AS K

You have to join the tables on a value.  I'm taking a wild guess here, but I think you might be looking for something like this:

SELECT (SELECT CONCAT(ROUND(SUM(server.Tech=7)/COUNT(server.Tech) * 100,2), '%') AS Server,
CONCAT(ROUND(SUM(network.Tech=7)/COUNT(network.Tech) * 100,2), '%') AS Network
           FROM network join server

On network.Tech = server.Tech

) AS V,

I don't know what your equals sign is doing in your arithmetic operation.  Are you trying to pull all Tech where it equals 7?  If so, it would be

SELECT (SELECT CONCAT(ROUND(SUM(server.Tech)/COUNT(server.Tech) * 100,2), '%') AS Server,
CONCAT(ROUND(SUM(network.Tech)/COUNT(network.Tech) * 100,2), '%') AS Network
           FROM network join server

On network.Tech = server.Tech
WHERE server.Tech = 7 AND network.Tech=7

) AS V,

Of course, if you are trying to sum all Techs that equal 7 over the full rowcount, it will need more work.


Getting to know SQL via the "short questions/simple answers style of learning" Looking for a incremental SQL course that would likely be similar to the this Ruby Koan course; which is in fact inspired by the book, "The Little Lisper", which uses a short questions/simple answers style of learning.

Basically the format is short questions are within a well defined context, with a simple answer, that incrementally building and leveraging the student's knowledge within the subject matter, while keeping the to the short questions/simple answers style of learning.

Also, I just wanted to note that I know there are a number of ways to learn SQL, but I'm looking for an existing implementation of this way. This style provides a fixed-path with stepping stones, and if you do not finish a set of questions, you're not allowed to go on, and even if you were able to find a way to cheat this, it would just make things harder, not easier; meaning it's not just free-form learning with information present small packets.

Have a look at:

http://sqlzoo.net/

This seems to be what you are looking for.


Perform Aggregate in Where clause I am wanting to perform the below query that essentially shows where a calculation is >= .70 or <= -.70 However, I get an error of

  Msg 147, Level 15, State 1, Line 8
  An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Here is sample DDL - what is the appropriate way to write this query?

    Declare @Hamburger Table (empName varchar(200), e float, a float, saleid varchar(10))
Insert Into @Hamburger (empName, e,a, saleid) Values
('Jake', 362, 233, 'E111'), ('Jake', 1431, 2702, 'E112'), ('Blue', 849, 280, 'R222'), ('Blue', 1418, 299, 'R390')

Select empName, e, a, saleid, AmtGained = Sum(Coalesce(e,0))-Sum(Coalesce(a,0)),
DiscountPercent = Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0)
From @Hamburger
where ((Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0) >= .70)
OR (Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0) <= -.70))
Group By empName, saleid, e, a
Order By empName ASC

Just like the error says, you can't have aggregates in the WHERE clause.  WHERE is logically before GROUP BY, so it filters rows before they are grouped, not after.

For filtering the groups, use HAVING (or push the GROUP BY into a subquery).  eg

Declare @Hamburger Table (empName varchar(200), e float, a float, saleid varchar(10))

Insert Into @Hamburger (empName, e,a, saleid) Values
    ('Jake', 362, 233, 'E111'), ('Jake', 1431, 2702, 'E112'), ('Blue', 849, 280, 'R222'), ('Blue', 1418, 299, 'R390')

Select empName, e, a, saleid, AmtGained = Sum(Coalesce(e,0))-Sum(Coalesce(a,0)),
DiscountPercent = Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0)
From @Hamburger
Group By empName, saleid, e, a
having ((Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0) >= .70)
OR (Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0) <= -.70))
Order By empName ASC


When to use a "middle table" instead of storing a JSON with IDs? I'm working on a store for a mobile app, the database is mysql.
I have the store items in a table called items and that table has a column called price_point_id (one item has 1 price point) which references another table called price_points.
Now there is one last table called templates which has a one-to-many relationship with the items table (one template has many items).
I could create a middle table with 2 columns item_id and template_id and join them, but wouldn't it be much faster to avoid a 4 tables join (I need data from items, price points and the middle table, but I only have the template ID to start) like in this example:

But what if I kept the item IDs in the templates table, that would avoid a join with big table (not huge but hundreads of thousands), In this case I could query the template first then use an IN statement to get the items + price points, sure it's still a join but in much smaller tables and I'd be querying by primary keys only. Like in this example:

Do you guys see any draw backs with the second approach?

As nbk mentions, the middle table is known as a bridge table or linking table. JSON is best for structureless or variable structure situations, but not so good when needed to be used in predicates within the context of a relational system.
By stuffing a bunch of values in a single JSON field and then using the IN clause, you're telling MySQL to treat the values of that JSON field as a predicate which is very similar to as if you wrote a bunch of OR clauses with each value in it. Having a large number of predicates in this form is generally sub-performant.
Furthermore, you're still dealing with roughly the same amount of data regardless if you store it in a JSON column or in a bridge table, because your templates table will still be the same number of rows times the same number of item_ids in the one-to-many relationship they naturally share. (I know there's some data redundancy in practice, but the optimizations of the database system under the hood with normalized data probably make that a moot point from a performance perspective.)
Joining multiple tables together is not a bad thing for performance, especially only a few tables as you mentioned and especially with small tables of only a few hundred thousand rows at most. It's more so an issue if you over-normalize the data and always need to rejoin all the tables back together in every query. You probably wouldn't see any issues even in that case until your data grows significantly more and/or you're joining around 10+ tables together every query (rough estimates).
So at the end of the day you'd be at best ending up with the same performance on average by leveraging a JSON column, and potentially worse off when querying it as a predicate, by sidestepping one of the key things a relational database is meant to do - join tables. :)
Regarding the purpose of a JSON column, it's better used for data points that are variable in nature and structure, that generally need to be returned as part of the SELECT list (i.e. are commonly returned as part of your queries' data lookups) as opposed to as being part of the JOIN, WHERE, or HAVING clause predicates.


1 time shrink of abused databse or restore from backup I have a database that I have inherited which has been left for a long time to grow and grow. 95% of this growth is caused by 1 log table used by our application. The database is 120GB and in full recovery mode. The compressed backups are around 10GB. The server is 2008. The log file is 3.5GB

After asking I have found that I can remove all data from this log table over 3 months old. This gives me 100GB of free space. I have also written an agent job to keep the log table at this level.

This means I will have roughly 100GB sat in this file forever (as long as no other table starts growing in way it hasn't previously or the agent job fails)

I would like to free some of this space up while leaving enough room for future growth which should be much less then it was before my agent job was put in.

So I don't want to shrink and then defrag indexes as this may require a lot of downtime and is almost always a bad idea.

Is dropping the database and restoring the 10GB backup an option? I'm not even sure if this will give me the desired result which is a smaller database with something like 2GB free in it's file for future growth.

Thanks

Restoring your backup will in fact bring you right back to where you were when the backup was taken.  This is actually one of the few cases where a shrink is appropriate.  Fortunately it's a background process and shouldn't affect your system to much unless you are running a really heavy OLTP system.  Once the shrink is done, and if I was you I should shrink down about 80GB and keep the other 20 for growth, you will almost certainly have to reindex.  The reindex (and the fragmentation that requires it) will most likely cause you performance problems so you should probably consider doing the whole process during a maintenance window.  If you don't have a maintenance window then try a slow period.  Either way if you want to get rid of that extra space shrink is the only option you have that I know of.


Postgresql inheritance based database design I'm developing a simple babysitter application that has 2 types of users: a 'Parent' and the 'Babysitter'. I'm using postgresql as my database but I'm having trouble working out my database design.

The 'Parent' and the 'Babysitter' entities have attributes that can be generalized, for example: username, password, email, ... Those attributes could be
placed into a parent entity called 'User'. They both also have their own attributes, for example: Babysitter -> age.

In terms of OOP things are very clear for me, just extend the user class and you are good to go but in DB design things are differently.
Before posting this question I roamed around the internet for a good week looking for insight into this 'issue'. I did find a lot of information but
it seemed to me that there was a lot a disagreement. Here are some of the posts I've read:

https://stackoverflow.com/questions/190296/how-do-you-effectively-model-inheritance-in-a-database: Table-Per-Type (TPT), Table-Per-Hierarchy (TPH) and Table-Per-Concrete (TPC) VS 'Forcing the RDb into a class-based requirements is simply incorrect.'

Multiple User Types - DB Design Advice:

Table: `users`; contains all similar fields as well as a `user_type_id` column (a foreign key on `id` in `user_types`
Table: `user_types`; contains an `id` and a `type` (Student, Instructor, etc.)
Table: `students`; contains fields only related to students as well as a `user_id` column (a foreign key of `id` on `users`)
Table: `instructors`; contains fields only related to instructors as well as a `user_id` column (a foreign key of `id` on `users`)
etc. for all `user_types`

How to model inheritance of two tables mysql

https://stackoverflow.com/questions/3074535/when-to-use-inherited-tables-in-postgresql: Inheritance in postgresql does not work as expected for me and a bunch of other users as the original poster points out.

I am really confused about which approach I should take. Class-table-inheritance (https://stackoverflow.com/tags/class-table-inheritance/info) seems like the most correct in
my OOP mindset but I would very much appreciate and updated DB minded opinion.

Whether you are better off with single-table-inheritance or class-table-inheritance really depends on the particulars of your case.  The performance advantage can go either way.  The difficulties presented by having lots of NULLS in a table range from the trivial to the overwhelming.
It depends on what your data looks like, and what you intend to do.

Kudos for figuring out that the problem is basically due to a mismatch between object modeling and relational modeling.

PS if you use UserID as the primary key on both Parent and Babysitter, and also declare it as a foreign key, you'll get some benefits, at the cost of a little programming when you go to insert new users.  This technique is called shared-primary-key, and it's also presented over on SO.


SQL Error [1292] "Truncated incorrect INTEGER value" - with Varchar columns and CASE. Why? In a MySQL 5.7 environment, when trying to insert a varchar value into a varchar field, I get the error:

SQL Error [1292] [22001]: Data truncation: Truncated incorrect INTEGER value: '1876301/347'

The source and target columns are both of the datatype varchar.
Here's a small demo to reproduce an example of the error (it occurs on the last insert):
create table SRC
(
    Z varchar(20) null,
    A varchar(63) null,
    B varchar(30) null
);

insert into SRC
select 'XYZ', '1876301/347', null
;

create table TGT
(
    C varchar(63) null
)
;

To generate the error, now run this: (is the 3rd WHEN that matches, meaning it's trying to insert the value from SRC.A into TGT.C)

insert into TGT
(
    C
)
select
    CASE
        WHEN if(Z in ('XYZ'), A, NULL) IS NULL AND B IS NULL THEN NULL
        WHEN if(Z in ('XYZ'), A, NULL) IS NULL AND B IS NOT NULL THEN B
        WHEN if(Z in ('XYZ'), A, NULL) IS NOT NULL AND B IS NULL THEN A
        WHEN if(Z in ('XYZ'), A, NULL) IS NOT NULL AND if(Z in ('XYZ'), A, NULL) = B THEN A
        ELSE if(Z in ('XYZ'), A, NULL)
    END AS C
from
    SRC

It seems to be related to there being a case statement, as, when I try to simply insert the same value directly, it works fine.
insert into TGT
(
    C
)
select
    A
from
    SRC

It also works fine with the CASE STATEMENT if I use INSERT IGNORE INTO..., but I'd rather not use that.

Seems like a bug in mysql5.7 - it works both in mysql5.6 and mysql8.0.
I have no idea why it can be like that, but I was able to reproduce that with simpler insert statement:
insert into TGT(C)
select
    CASE
        WHEN if(Z in ('XYZ'), A, NULL) IS NULL THEN NULL
    END AS C
from SRC;

Seems like some implicit conversion makde by mysql. Forcing explicitly convert helps:
insert into TGT(C)
select
    CASE
        WHEN if(Z in ('XYZ'), convert(A, char(63)), NULL) IS NULL THEN NULL
    END AS C
from SRC;

(but converting whole case..end does not.


How to configure multi-site SQL Server 2014 Always On Availability Group for DR We have a two-node SQL Server AlwaysOn Availability Group cluster configured in one of our datacenters, Datacenter A, and for disaster recovery we are looking to expand this to two additional nodes in Datacenter B.  The two nodes in Datacenter A are configured for synchronous replication; our plan is to configure the replicas in Datacenter B for async replication.  The two datacenters have different networks, but there is cross-site routing (and some sort of dedicated link -- it appears to have good bandwidth and at least decent latency).

It seems broadly that this configuration is supported (also see the description of the StackExchange database configuration), but we're a little unsure of the particulars of the network configuration.

Do the DR nodes need to be joined to the existing WSFC, or will they form their own WSFC?  We currently have a fileshare witness in the cluster for HA automatic failover (hosted on a separate, non-database server in Datacenter A).  If the DR nodes join the existing WSFC, do they also need to see the fileshare witness in Datacenter A?  We don't necessarily want the DR nodes to have any quorum votes as we will only ever manually fail over.  Should we add a fileshare witness hosted in Datacenter B, and does that then need to be reachable by all nodes in the cluster, including those in Datacenter A?  If the DR nodes join the existing WSFC, will the WSFC then have one cluster IP from each network?  Note that we don't want inter-network routing within the cluster; if a client tries to connect to a cluster address from Network B while the primary replica is currently in Network A, we want that connection to fail; we don't want any of the nodes in Datacenter A to have addresses bound from Network B.  Or at least, this is my understanding, we had some concerns from our network team about introducing routing loops if nodes are binding non-local addresses.
As I understand it, the Availability Group Listener will have one IP address from each network; our application then specifies both IPs in the connection string and includes MultiSubnetFailover=True; it is then basically up to the client to determine which network is accepting connections (similar to the older, simpler database mirroring configuration in which the client connection string would specify the failover partner in case the primary IP didn't respond).  Is this correct?  And clients in each network would be able to connect to nodes in both its local network and the remote network?  (In this case it might make sense to alter the connection strings for all clients so that for each client its "local" AG Listener IP is listed first)
At some point in the distant future, we may wish to swap the roles of the datacenters; that is, Datacenter B becomes the primary and Datacenter A becomes the DR site.  In that situation, we would plan to reconfigure the Datacenter B nodes to have synchronous replication, add a fileshare witness in Datacenter B (to enable automatic failover, if we haven't already), and then alter the replicas in Datacenter A to be asynchronous.  Is this reasonable to do with this configuration, and can all of these operations be done fully online (or at least with minimal downtime)?

Yes the DR nodes need to be joined to the existing cluster. SQL Server 2016 has Distributed Availability Groups, which would a cluster at DR separate from the existing one, but you are on SQL Server 2014. The DR nodes should be able to see the fileshare witness. You can adjust quorum votes so that the DR nodes don't have a say  in if quorum is down. I do add a fileshare in DR for when we failover to it, but it's not part of the cluster until the failover occurs.
The connection string would specify the AG listener and would not specify the two IPs. MultiSubnetFailover=True takes care of it by connecting to the IP that returned success first.
Yes it is very reasonable to swap roles. The last 3 companies I worked for had DR sites that we'd failover to 1-2 times per year, running production at DR for a few days and up to 3 weeks. Failovers are an offline event. They take about 15 seconds on AGs.


Hundreds of TCP connections opened by sqlservr.exe We have Tomcat on one machine and SQL Server on another.

Our SQL Server machine is Windows 2008 R2 64bit machine with 32GB usable RAM
4 processors, 16 cpus.
We use database pooling to keep db connection reusable.

I am looking at the Resource Monitor in Task Manager and I noticed hundrerds of TCP connections from application server to SQL Server machine.

There are 1014 total connections.

1009 of them are 'AWAITING COMMAND', status = 'sleeping'.
Other 5 are 'SELECT' and 'UPDATE', status = 'runnable'.

Does anyone know why is this happening?

My application-context.xml:

<bean id="hibernateProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <property name="properties">
        <props>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
            <prop key="hibernate.show_sql">false</prop>
            <prop key="hibernate.c3p0.min_size">4</prop>
            <prop key="hibernate.c3p0.max_size">10</prop>
            <prop key="hibernate.c3p0.timeout">600</prop>
            <prop key="hibernate.c3p0.max_statements">25</prop>
            <prop key="hibernate.c3p0.testConnectionOnCheckout">false</prop>
            <prop key="hibernate.cache.use_second_level_cache">false</prop>
        </props>
        </property>
    </bean>

And my c3p0.properties:

c3p0.maxIdleTime=900
c3p0.maxStatements=150
c3p0.maxIdleTimeExcessConnections=300
numHelperThreads=6
unreturnedConnectionTimeout=3600

Connection pooling is designed to keep connections open.  If your connection pool is set with a maximum pool size of 100 connections, you may well see 100 connections open on the server per intentional connection opened by the client.

I tested this with a simple VB.Net client targeted against my local SQL Server.  The .Net code is:

Module Module1

    Sub Main()
        Dim cb As SqlClient.SqlConnectionStringBuilder = New SqlClient.SqlConnectionStringBuilder
        cb.ApplicationName = "MyTestApp"
        cb.ConnectTimeout = 30
        cb.DataSource = "<servername>"
        cb.InitialCatalog = "tempdb"
        cb.IntegratedSecurity = True
        cb.MultipleActiveResultSets = False
        cb.Pooling = True
        cb.MinPoolSize = 10
        cb.MaxPoolSize = 100
        cb.WorkstationID = My.Computer.Name
        Using cn As New SqlClient.SqlConnection(cb.ConnectionString)
            cn.Open()
            Using c As New SqlClient.SqlCommand("SELECT DB_NAME();", cn)
                Dim r As SqlClient.SqlDataReader = c.ExecuteReader()
                While r.Read
                    Console.WriteLine(r.GetValue(0))
                End While
                r.Close()
            End Using
            Console.ReadKey() ' wait for the user to press a key before we exit.
        End Using

    End Sub

End Module

The code creates what looks like a single connection to my SQL Server, and executes a single very simple query returning the name of the database.  It then "closes" the data reader, returning the connection to the pool.

Running this, then looking at the SQL Server connections with this query:

SELECT des.session_id
    , des.login_time
    , des.program_name
    , des.status
FROM sys.dm_exec_sessions des
WHERE des.program_name = 'MyTestApp';

shows the following results:

╔════════════╦═════════════════════════╦══════════════╦══════════╗
║ session_id ║       login_time        ║ program_name ║  status  ║
╠════════════╬═════════════════════════╬══════════════╬══════════╣
║         65 ║ 2018-04-11 15:18:00.517 ║ MyTestApp    ║ sleeping ║
║         66 ║ 2018-04-11 15:18:00.520 ║ MyTestApp    ║ sleeping ║
║         67 ║ 2018-04-11 15:18:00.523 ║ MyTestApp    ║ sleeping ║
║         68 ║ 2018-04-11 15:18:00.523 ║ MyTestApp    ║ sleeping ║
║         69 ║ 2018-04-11 15:18:00.527 ║ MyTestApp    ║ sleeping ║
║         70 ║ 2018-04-11 15:18:00.527 ║ MyTestApp    ║ sleeping ║
║         71 ║ 2018-04-11 15:18:00.530 ║ MyTestApp    ║ sleeping ║
║         72 ║ 2018-04-11 15:18:00.533 ║ MyTestApp    ║ sleeping ║
║         73 ║ 2018-04-11 15:18:00.533 ║ MyTestApp    ║ sleeping ║
║         74 ║ 2018-04-11 15:18:00.537 ║ MyTestApp    ║ sleeping ║
╚════════════╩═════════════════════════╩══════════════╩══════════╝

Connection pooling is creating 10 connections (the minimum I specified in the code above), and keeps them open as long as the app is running.


Can I rollback/abort a transaction if no rows were inserted? I have a transaction in which I want to do something like this:

START TRANSACTION;

INSERT INTO table1 (...)
SELECT ... FROM table 2;

... if select count(*) from table1 returns 0 abort/rollback ...

COMMIT;

Is this possible?

Sure.  In MySQL, you can only use if in stored procedures, so this is typically done client side.  Pseudo-code:

int rows = db.ExecuteScalar(
    "start transaction; " +
    "insert into table1 (col1) select col1 from table2; " +
    "select count(*) from table1");
if (rows == 0) {
    db.ExecuteNonQuery("abort transaction");
} else {
    db.ExecuteNonQuery("commit transaction");
}

Rolling back a transaction that did nothing (inserted no rows) doesn't make a whole lot of sense, but I'm assuming you simplified reality for this question.


Backup Database (SQL 2000 Compatibility) on SQL Server 2005 with CHECKSUM, but Page Verification TORN_PAGES Detection I am trying to backup a 250GB database on SQL Server 2005 running in SQL 2000 compatibility mode.

The page verification is set to TORN_PAGES detection.
So, far the backup has been corrupted when we try to copy the backup to an external and restore on a different server.

The error I got in the past was as follows:
System.Data.SqlClient.SqlError: RESTORE detected an error on page (0:0) in database "MMS.mdf" as read from the backup set. (Microsoft.SqlServer.Smo)

If I perform a backup with CHECKSUM enabled in the backup options, then does it look like the restore will work on the other server or is the database itself corrupt?

Check the file permissions on the backup on the second server.  It's possible that's your problem, and it's easily fixed.

If that doesn't fix it, you should run a dbcc checkdb on the original database, which will tell you if the database itself is corrupt.

If DBCC returns okay, run a RESTORE VERIFYONLY FROM DISK = 'path to backup' on the backup on the original server.

If all of that returns okay, go back to the second server and double-check the file permissions.


What are the differences between NoSQL and a traditional RDBMS? What are the differences between NoSQL and a traditional RDBMS?

Over the last few months, NoSQL has been frequently mentioned in the technical news. What are its most significant features relative to a traditional RDBMS? At what level (physical, logical) do the differences occur?

Where are the best places to use NoSQL? Why?

NoSQL stands for "Not only SQL" and usually means that the database is not a relational database, which have been very popular the last decades.
The reason why NoSQL has been so popular the last few years is mainly because, when a relational database grows out of one server, it is no longer that easy to use.  In other words, they don't scale out very well in a distributed system. All of the big sites that you mentioned Google, Yahoo, Facebook and Amazon (I don't know much about Digg) have lots of data and store the data in distributed systems for several reasons. It could be that the data doesn't fit on one server, or there are requirements for high availability.
CAP Theorem
The properties of a distributed system can be described by the CAP Theorem. Of the three properties you can only have at most two:

Consistency
Availability
tolerance to network Partitioning

Amazon Dynamo uses Eventual Consistency to come close to get all three properties. The paper Dynamo: Amazon’s Highly Available Key-value Store is worth reading when learning about NoSQL databases and distributed systems. Amazon Dynamo has the A and P properties.
Google take a different approach with BigTable, that has the C and A properties.
Other NoSQL databases
As I wrote in the beginning there are many other kind of NoSQL databases, that are designed for different requirements. E.g. graph databases like Neo4j, document databases like CouchDB and multimodel / object databases like OrientDB.
Finally I would like to say that relational databases will remain popular. They are very flexible and maintainable. But they are not always the best choice.


Why aggregate query with lookup is extremely slow? I have a mongodb query that works but takes too long to execute and causes my CPU to spike to 100% while it is executing.  It is this query here:
  db.logs.aggregate([
    {
      $lookup:
      {
        from: 'graphs',
        let: { logId : '$_id' },
        as: 'matched_docs',
        pipeline:
          [
            {
              $match: {
                $expr: {
                  $and: [
                    { $eq: ['$$logId', '$lId'] },
                    { $gte: [ '$d', new Date('2020-12-21T00:00:00.000Z') ] },
                    { $lt: [ '$d', new Date('2020-12-23T00:00:00.000Z') ] }
                  ]
                }
              }
            }
          ],
      }
    },
    {
      $match: {
        $expr: {
          $and: [
            { $eq: [ '$matched_docs', [] ] },
            { $gte: [ '$createDate', new Date('2020-12-21T00:00:00.000Z') ] },
            { $lt: [ '$createDate', new Date('2020-12-23T00:00:00.000Z') ] }
          ]
        }
      }
    },
    { $limit: 5 }
  ]);

This query looks for all records in the db.logs collection for which they have not been transformed and loaded into db.graphs.  It's analogous to this SQL approach:
WHERE db.logs._id NOT IN (
        SELECT lId FROM db.graphs
        WHERE db.graphs.d >= @startTime
        AND db.graphs.d < @endTime
    )
    AND db.logs.createDate >= @startTime
    AND db.logs.createDate < @endTime
)

The db.logs has over 1 Million records and here are the indexes:
db.logs.getIndexes();
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_"
        },
        {
                "v" : 2,
                "key" : {
                        "createDate" : 1
                },
                "name" : "createDate_1"
        }
]

And db.reportgraphs has fewer than 100 records with indexes on every property/column.
In my attempt to analyze why the mongo query is so slow and CPU intensive, I suffixed my mongo query with a .explain().  But mongo gave me the error saying db.logs.aggregate(...).explain() is not a function.  I also tried adding , {$explain: 1} immediately after { $limit: 5} and got an error saying Unrecognized pipeline stage name $explain.
So I guess I have two questions:

Can someone give feedback on why my  mongo query is so slow or possible solutions?
Is there a way to see the execution plan of my mongo query so that I can review where the performance bottle necks are?

UPDATE
A possible solution I'm considering is to have a property db.logs.isGraphed:boolean .  Then use a simple db.logs.find({isGraphed:false, createDate:{...date filter...}}).limit(5).  Wasn't sure if this is the approach most people would have considered in the first place?

It is slow because it is not using an index.  For each document in the logs collection, it is doing a full collection scan on the graphs collection.
From the $expr documentation page:

$expr only uses indexes on the from collection for equality matches in a $match stage.


So I happened to have stumbled on a more efficient pattern for this sort of operation (based on how much faster this is in testing).

Given the above, here's what it would look like (I've excluded fields beyond what are necessary to point to a compoent_data record):

Updated Query:

WITH cd_start_end AS (
    SELECT
        id,
        owner_rel_id,
        generated_date AS start_date,
        COALESCE(removed_date, now()) AS end_date
    FROM
        component_data
    ORDER BY
        id
), active_dates AS (
    SELECT
        id,
        generate_series(start_date::date, end_date::date, '1 day'::interval)::date AS active_dates
    FROM
        cd_start_end
)

SELECT
    *
FROM
    active_dates
WHERE
    active_dates >= '2013-01-01'
    AND active_dates < now()::date;


How to define the table structure for measured data in sqlite I have an embedded system with an sqlite database.
How should I define the table structure for the measured data from 4 sensors?

Each sensor give us every second an numeric measured value.
The measured value should be stored with an timestamp in the database.

Criteria are:

Table are "fill only". There are no delete and update operations.
INSERT (timestamp, value) should be fast.
SELECT (timestamp, value) for an specific time range should be fast.

First Approach: One table for all sensors

CREATE TABLE Measured_Values (
    id        INTEGER  PRIMARY KEY AUTOINCREMENT,
    sensor_id INTEGER  NOT NULL,
    timestamp DATETIME NOT NULL,
    value     REAL,
    FOREIGN KEY (sensor_id) REFERENCES Sensor_List(id))

with

CREATE TABLE Sensor_List(
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT)

Second Approach: One table per sensor

CREATE TABLE Measured_Values_Sensor_ONE (
    id        INTEGER  PRIMARY KEY AUTOINCREMENT,
    timestamp DATETIME NOT NULL,
    value     REAL)

CREATE TABLE Measured_Values_Sensor_TWO (
    id        INTEGER  PRIMARY KEY AUTOINCREMENT,
    timestamp DATETIME NOT NULL,
    value     REAL)

and so on.

OR is there another better design?

Which design is usually used?
Which design gives me small insert time but also fast query time?

Your second approach is not normalized: adding another sensor would require creating a new table, and querying multiple sensors would be horribly complex.

When all four sensors get a new value every second, and if you have multiple tables, then the database would have to update four tables. A single table is certainly more efficient.

For fast queries on time ranges, you need an index on the timestamp column.
This implies that every INSERT also needs to update the index.
(If you want fast queries on time ranges for a single sensor, you need another index that contains both the sensor_id and timestamp columns.)

Please note that you do not need AUTOINCREMENT when you never delete rows.
(Autoincrement In SQLite)


Very Simple "SELECT * FROM" Times Out (InnoDB) Hello everyone and sorry for what may be a silly question, but I'm completely dazzled.

First thing is that I'm not sure that it's related to InnoDB, but I've tried this on MariaDB and on MySQL and results are the same.

Let's cut to the chase:

I have huge performance issues in the most unpredictable places. To illustrate it best, here is an example:

I have a table called 'image' which stores images as BLOBs as well as the images metadata like image_name or DPI. It's relatively small (~14000 rows) and most images are 250-1000 KB. In the example I will use LOWER() to get rid of index that I have on image_name column.

SELECT id FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

This is super fast, at worst couple milliseconds, we got result id = 1.

SELECT * FROM image WHERE id = '1'

This is fast again, most time spent transferring BLOB to me, no surprise (in the end, it's PRIMARY key).

SELECT * FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

Now this thing is just refuses to execute. And I mean it. In 95% of the cases I will receive a timeout and occasionally it will execute after 10 minutes or so.

EXPLAIN SELECT obviously tells me that it's simple SELECT. And I cannot run SHOW PROFILES because the request is never completed, so I cannot even think of where I can start debugging it.

First of all, here are a few things you can try to get more info about the query in your case:

After starting the query, you can check its status by running

SHOW FULL PROCESSLIST

Also, you can try running the query on a smaller set of rows by adding another where predicate (covered by an index), for example:

SELECT * FROM image WHERE id < 500 and LOWER(image_name) = 'img_0209.jpg'

As for why the query may be slow, I believe that what happens with the third query

SELECT * FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

is that since the index can't be used, the engine does a full table scan and reads all the data for each row (including full BLOB data) and only then tries to filter them. So it probably reads a few GBs of data with that query.

Looking at the amount of data read from disc during the query may give some hints. You can try using the innodb_data_read service status variable:

SET @read_metric = 'innodb_data_read';
SET @read_before = (
  SELECT variable_value
  FROM information_schema.session_status
  WHERE variable_name = @read_metric
);

SELECT * FROM image WHERE id < 500 and LOWER(image_name) = 'img_0209.jpg';

SET @read_after = (
  SELECT variable_value
  FROM information_schema.session_status
  WHERE variable_name = @read_metric
);

SELECT (@read_after - @read_before) / 1024 / 1024;

It should be similar to the size of data in BLOBs:

SELECT sum(length(image_data)) / 1024 / 1024 FROM image WHERE id < 500;

To speed up the query you need to avoid accesing BLOBs as much as possible. A few suggestions:

in your specific case changing the collation to an ignore-case one should help, so the image_name index may be used to find the single row you need
try using a self join to separate image_name and BLOBs access

SELECT image_data.image_data
FROM image
JOIN image as image_data on image_data.id = image.id
WHERE LOWER(image.image_name) = 'img_0209.jpg';

as described in the Optimizing for BLOB Types manual, move the BLOBs to a separate table with just two colums: id and data


MySQL: how to use audit_log plugin to audit user modification I am trying to audit any user modification action (create user/alter user/drop user/rename user) on a MySQL Enterprise server 5.7.24.
I have installed the audit_log plugin following the
 document,
and tried to configure the filter with a few approaches.
First, I tried to use
general
SELECT audit_log_filter_set_filter('user_modification',
'{
  "filter": {
    "class": {
    "name": "general",
      "event": {
        "name": "status",
        "log": {
          "field": { "name": "general_sql_command.str", "value": "create_user" }
        }
      }
    }
  }
}') as RESULT;

and assigned the filter to everyone but no luck, it doesn't generate any audit log when I tried to create user
SELECT audit_log_filter_set_user('%', 'user_modification');

I also tried to swap a few things in the same filter, no luck

swap 'create user' with full instrument name 'statement/sql/create_user'
swap 'general_sql_command.str' with 'general_command.str'

Next, I tried to use 'table_access' instead of 'general', hopeful it will capture any movement on mysql.user table, but still not luck
SELECT audit_log_filter_set_filter('user_modification',
'{
  "filter": {
    "class": [
      {
        "name": "table_access",
        "event": {
          "name": [ "read", "insert", "delete", "update" ],
          "log": {
            "field": { "name": "table_database.str", "value": "mysql"},
            "field": { "name": "table_name.str", "value": "user"}
          }
        }
      }
    ]
  }
}') as RESULT;

Just in case I missed out on any step during installation, I created another filter for test and the result is positive.
SELECT audit_log_filter_set_filter('connection',
'{
  "filter": {
    "class": { "name": "connection" }
  }
}');

SELECT audit_log_filter_set_user('%', 'connection');

Now I am totally puzzled, can anyone help me? thanks

I found the reason for it, you just need to relogin to database... so audit_log will be able to capture the action.
SELECT audit_log_filter_set_filter('user_modification',
'{
  "filter": {
    "class": {
    "name": "general",
      "event": {
        "name": "status",
        "log": {
          "field": { "name": "general_sql_command.str", "value": "create_user" }
        }
      }
    }
  }
}') as RESULT;

SELECT audit_log_filter_set_user('%', 'user_modification');


Remote mysql connection fails with accessed denied exactly every other attempt On my development machine remotely connecting to Mysql i will get an access denied error every other attempt. What's odd is when it does fail like this it doesn't write to the mysql error log on the server like it does when there is a "real" access denied.

I came across this when writing a python program at first i thought it was the mysql python connector, so i switched to mysqldb same problem, and then i just tried straight from the CLI and the same issue. It's exactly every other attempt.

[~]$ mysql -h servername -u user -p db -e "SHOW VARIABLES LIKE '%version%'"
Enter password:
ERROR 1045 (28000): Access denied for user 'user'@'ip' (using password: YES)

[~]$ mysql -h servername -u user -p db -e "SHOW VARIABLES LIKE '%version%'"
Enter password:
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.19                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.19-0ubuntu0.16.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+

[~]$ mysql -h servername -u user -p db -e "SHOW VARIABLES LIKE '%version%'"
Enter password:
ERROR 1045 (28000): Access denied for user 'user'@'ip' (using password: YES)

[~]$ mysql -h servername -u user -p db -e "SHOW VARIABLES LIKE '%version%'"
Enter password:
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.19                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.19-0ubuntu0.16.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+

It's to the point that i had to hack my program to just always try twice and only raise an exception if there were two back to back failures. That's hacky and bad. I have to be messing something obvious its too reproducible.

Had to use ip of host instead of hostname. Problem was dns related but the way that it manifested still confuses me.


SQL Server If statement assigning values Please see the pseudocode below. Basically what I'm trying to do is write a statement that when an account number contains one of the specified codes it will assign that Level to the entire account not to just that one row.

I've tried using a case statement in a CTE and few other options and I'm not having much luck. Any help is appreciated.

if AcctNo contains Code = '1'
then level = '1'
elif AcctNo contains Code = '2'
then level = '2'

AcctNo  Code  charge   level
1       2       50     Null
1       17      60     Null
1       28      61     Null
1       36      62     Null
1       18      63     Null

Desired Results

 AcctNo  Code  charge    level
    1       2       50     2
    1       17      60     2
    1       28      61     2
    1       36      62     2
    1       18      63     2

At a really basic level it would look like this

UPDATE TableName
SET [level] = CASE WHEN AcctNo = '1' THEN '1'
                    WHEN AcctNo = '2' THEN '2'
                    ELSE 'DefaultVal' END

or

UPDATE TableName
SET [level] = CASE AcctNo WHEN '1' THEN '1'
                    WHEN '2' THEN '2'
                    ELSE 'DefaultVal' END

Depending on if you only need the one column in your conditions or if it's more complicated than that.

Edit  On looking at your expected data it looks like what you want is this:

WITH Levels AS (SELECT AcctNo, Level,
        MIN(CASE WHEN code IN (1,2,3,4,5) THEN code ELSE NULL END)
                OVER (PARTITION BY AcctNo) AS MinCode
        FROM Test)
UPDATE Levels
SET level = MinCode

Note: This assumes that you want the minimum code for a given AcctNo.

If you just want a select statement to test then change it to this:

WITH Levels AS (SELECT AcctNo, Code, Charge, Level,
        MIN(CASE WHEN code IN (1,2,3,4,5) THEN code ELSE NULL END)
                OVER (PARTITION BY AcctNo) AS MinCode
        FROM Test)
SELECT * FROM Levels


Read Committed Snapshop Isolation Level - Effect of NOLOCK As I understand it, with Read Committed Snapshot, readers don't block writers and writers don't block readers. The only blocking that can occur is writers blocking writers. By default our database is set to Read Committed Snapshot. However, we have a lot of users issuing queries using WITH (NOLOCK) (out of bad habits). If I understand correctly, a query that uses WITH (NOLOCK) changes the isolation level. When this is used in a query, is it now possible for a reader to block a process attempting to update the data being read?

If I understand correctly, a query that uses WITH (NOLOCK) changes the
  isolation level. When this is used in a query, is it now possible for
  a reader to block a process attempting to update the data being read?

No, you think wrong.
Both in RCSI and Read Uncommitted readers don't block writers.

The mechanism is different: RCSI uses last committed copy of modifying data for the reader, and a SELECT using with (nolock) will access "dirty" data, but there is no blocking in both the cases.

Here is an example. You make an update like this:

begin tran
   update dbo.t
   set col1 = 20 where col1 = 10;
-- transaction is still open, X lock is held on a row

In the second session you make SELECT * from dbo.t;

In the case of RCSI you'll get back col1 = 10, in case of Read Uncommitted you'll get col1 = 20, but session 2 will return data in both cases immediately, without waiting when transaction from 1st session commits/rolls back


Change month partition to day partition with current data in Oracle 12c I have sales table and currently sales table have interval partition by month.

I changed partition type to month to day;

ALTER TABLE sales  set interval (NUMTOYMINTERVAL(1, 'DAY'));

However current data still in Month partitions.

How can i change partition type with existing data on table?

The above affects only partitions created in the future.

If you want to change your monthly partitions into daily partitions, you can use:

ALTER TABLE ... SPLIT PARTITION ...

Example:

ALTER TABLE sales SPLIT PARTITION sales_201701 INTO
( PARTITION sales_20170101 VALUES LESS THAN (TO_DATE('02-JAN-2017','dd-MON-yyyy')),
  PARTITION sales_20170102 VALUES LESS THAN (TO_DATE('03-JAN-2017','dd-MON-yyyy')),
  PARTITION sales_20170102 VALUES LESS THAN (TO_DATE('03-JAN-2017','dd-MON-yyyy')),
  PARTITION sales_20170103 VALUES LESS THAN (TO_DATE('04-JAN-2017','dd-MON-yyyy')),
...
  PARTITION sales_...);

More examples: https://oracle-base.com/articles/12c/online-split-partition-and-subpartition-12cr2


Login failed errors while DB refresh in progress We have a 2.5TB refresh in Production, I have automated the refresh of latest production backup to lower environment every Sunday night. And during this activity, SQL error log is filled with

  Login failed for user 'xxxx'. Reason: Failed to open the explicitly specified database 'xxxx'.

The options to stop these I could think of is:

Ask app team to stop connecting to DB during refresh - they have lot of process, so they are not ready.
Recycle error log post refresh - I need to search the entire log file for any messages during refresh (non login failed errors)
Disable the login - that creates alerts too.

What is the best approach? Any thoughts? Or am I missing something stupid?

Unfortunately, due to other apps that connect to this instance, I can't:

disable logging of failed logins entirely, or
block all connections (via the firewall, etc)

One option might be to change the login for this user to have a different default database:

ALTER LOGIN [xxxx]
WITH DEFAULT_DATABASE = [tempdb];

This way at least the connections will succeed and the logging of errors will stop (the apps will likely have mixed results, mostly failures, with whatever they are trying to do of course).

And then you can change it back to the usual one after the refresh is done.


Top Activity in OEM reports that dbms_scheduler jobs run as the job_creator user? The top activity graph in Oracle Enterprise Manager was reporting that certain activity was being performed by certain users at odd hours of the night.  Upon researching, this activity was because the user in question was the creator of a scheduler job (even though the scheduler job was in a different schema).  This has led to some amount of confusion, so we are attempting to have the Top Activity graph in OEM report that the activity is from the owner of the scheduler job instead of the job_creator.

Test Case

sqlplus kjohnston

begin
    dbms_scheduler.create_job(job_name => 'UTILITIES.SCHEDULE_TEST', job_type => 'PLSQL_BLOCK',
    job_action => 'begin for v_id in (select * from dba_tables) loop null; end loop; end;',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=1', auto_drop => FALSE, enabled => TRUE);
end;

When UTILITIES.SCHEDULE_TEST runs via the repeat_interval, the job is reported as running as the job_creator user.

SELECT sample_time, username, sql.sql_text
  FROM v$active_session_history sess, v$sql sql, dba_users users
 WHERE sess.user_id = users.user_id
   AND sess.sql_id = sql.sql_id
   AND upper(sql.sql_text) = 'SELECT * FROM DBA_TABLES'
  ORDER BY sample_time desc;

SAMPLE_TIME         USERNAME
14-DEC-15 14.47.52  KJOHNSTON
14-DEC-15 14.46.52  KJOHNSTON

However, when the job is run on-demand, the job is reported as having been run as the job owner.

exec dbms_scheduler.run_job('UTILITIES.SCHEDULE_TEST');
/
SELECT sample_time, username, sql.sql_text
  FROM v$active_session_history sess, v$sql sql, dba_users users
 WHERE sess.user_id = users.user_id
   AND sess.sql_id = sql.sql_id
   AND upper(sql.sql_text) = 'SELECT * FROM DBA_TABLES'
  ORDER BY sample_time desc;

SAMPLE_TIME         USERNAME
14-DEC-15 14.59.19  UTILITIES

My first attempt at a solution was to wrap the create_scheduler_job in a procedure owned by the owner of the scheduler job, then run the procedure to create the scheduler job... however, the job_creator is still KJOHNSTON (which would then still look like KJOHNSTON is running the job instead of the job owner: UTILITIES):

create or replace procedure utilities.create_scheduler_job as
begin
    dbms_scheduler.create_job(job_name => 'UTILITIES.SCHEDULE_TEST', job_type => 'PLSQL_BLOCK',
    job_action => 'begin for v_id in (select * from dba_tables) loop null; end loop; end;',
    auto_drop => FALSE, enabled => TRUE);
end;
/
exec utilities.create_scheduler_job;
/
SELECT job_creator FROM dba_scheduler_jobs WHERE owner = 'UTILITIES' and job_name = 'SCHEDULE_TEST';

JOB_CREATOR
KJOHNSTON

I've considered 2 other potential solutions:

1)  Proxy in as the job owner to create the scheduler job.

2)  Create a generic SCHEDULER_JOB user that we login as in order to create the scheduler job.

Neither solution is particularly appealing to me as either solution requires more work per job than I would care to put into scheduler job creation.

Just to be clear, the jobs run without issue whether they are reported to have been run from the job_creator or the owner of the job, but this would help with the future analysis of top activity on our databases.

Has anyone else come across this issue and come up with a better/easier/more elegant solution?

You have to distinct between the user of the current session you used for logon and the name of the database user whose privileges are currently active (see also About Definer's Rights and Invoker's Rights). The Scheduler jobs are running under Definer's Rights.

You can get the name of the database user whose privileges are currently active by SYS_CONTEXT('USERENV', 'CURRENT_USER'), see SYS_CONTEXT

In order to understand, try this test-case:

sqlplus kjohnston

CREATE TABLE UTILITIES.A_SCHEDULER_TEST
   (username VARCHAR2(30), CURRENT_USER VARCHAR2(30), ts TIMESTAMP);

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'UTILITIES.SCHEDULE_TEST',
        job_type => 'PLSQL_BLOCK',
      job_action => 'begin insert into UTILITIES.A_scheduler_test values (USER, SYS_CONTEXT(''USERENV'', ''CURRENT_USER''), LOCALTIMESTAMP ); commit; end;',
      repeat_interval => 'FREQ=MINUTELY;INTERVAL=1;BYSECOND=00',
        auto_drop => FALSE,
        enabled => TRUE);

    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'kjohnston.SCHEDULE_TEST',
        job_type => 'PLSQL_BLOCK',
      job_action => 'begin insert into UTILITIES.A_scheduler_test values (USER, SYS_CONTEXT(''USERENV'', ''CURRENT_USER''), LOCALTIMESTAMP ); commit; end;',
      repeat_interval => 'FREQ=MINUTELY;INTERVAL=1;BYSECOND=10',
        auto_drop => FALSE,
        enabled => TRUE);
END;

The content of A_scheduler_test will be like this

USERNAME      CURRENT_USER      TS
------------  ---------------   --------------------
KJOHNSTON     UTILITIES         2015-12-15 08:50:00
KJOHNSTON     KJOHNSTON         2015-12-15 08:50:10

JOB_CREATOR is relevant only for result of USER, resp SYS_CONTEXT('USERENV', 'SESSION_USER'), but the PL/SQL block runs in context of Job owner.

If you run the scheduler job manually you can define how it is executed: RUN_JOB Procedure

use_current_session: This specifies whether or not the job run should occur in the same session that the procedure was invoked from.

use_current_session => TRUE (default): The job runs as the user who called RUN_JOB.
use_current_session => FALSE: The job runs as the user who is the job
owner.

However, when I test this in my database (11.1.0.7.0), this seems to be a (documentation) bug in Oracle!

For use_current_session => FALSE, function USER always returns the job creator.
For use_current_session => TRUE, function USER always returns the job owner.


Can I remove the middle slave in a MySQL "master -> slave -> slave" setup? Let's say I have the following setup:

Master -> Slave1 -> Slave2

I would like to remove the middle slave, Slave1, and have the following:

Master -> Slave2

My gut is that I can do the following:

Stop the Master -> Slave1 replication.
After Slave1 ->
Slave2 replication catches up, stop that replication as well.
CHANGE MASTER on Slave2 to point to Master
Turn on replication on Slave2
Enjoy new Master -> Slave2 setup.

Is this something that would work?

Add additonal step to avoid any confusion you could do flush tables with read lock after replication is caught up as it will do global lock and would avoid conflict in case of any change coming up, use unlock tables at the end to release the lock acquired. Moreover it would be better to have this done in low/off production time in case it is in production, other steps seems to fine

Hope it helps.


Performance differences between executing via Linked Server vs. SSMS We've had an issue reported where a stored procedure was behaving badly when called from some .Net code. On the face of it, that sounds similar to this case, which turned out to be due to EDMX, but in this case, we're just executing a stored procedure.

In the case today, when connected directly to the server holding the database, via SSMS, the query took 2 seconds; when running the query from the client application it was about 5 minutes, and when running the query from a linked server, it also took around 5 minutes. The degradation in performance "suddenly happens" without any changes having been committed.

From a comment on a previous answer, the suggestion wasn't due to query plans, even though recompiling the stored procedure did "fix" the performance for now. i.e. Recompiling the stored procedure makes it equally performant from both SSMS in direct vs linked scenario, and also to the .Net client. I am passing through exactly the same parameters through for comparison.

The stored procedure does a small amount of dynamic SQL (to unwrap a comma-separated list of IDs into a temp table, of the form INSERT INTO #Temp SELECT id FROM dbo.MyTable WHERE Id IN ( 1, 2 ,3 )) before going on to to the main query which joins a whole bunch (circa 20) of tables for the final result.

Ideally I meant to better understand the cause, such that this can be avoided in the future, so what should I be doing to stop this from happening?

The difference between SSMS and .Net client performance could well be down to how plans are cached. Part of the cache key is the session options - for me, at least, SSMS sets ARITHABORT, whereas .Net SqlClient does not, by default.  You sound like you're ending up in the situation where a bad plan is cached vs. one cache key, and a good plan vs. another.


Is there a major RDBMS that DOESN'T allow column name quoting/escaping? The title basically says it: Are there any major DBs that don't handle column name quoting/escaping?

For instance, suppose I want to have a column called FROM (and let's assume I have a really good reason for that). In every SQL-based RDBMS, naturally that's a keyword. In MS SQL Server I can escape it using [ and ]:

CREATE TABLE FOO (
    [FROM] VARCHAR(255)
)

MySQL uses backticks

CREATE TABLE FOO (
    `FROM` VARCHAR(255)
)

Similarly, Oracle and Postegres use double quotes.

Is there any major RDBMS that doesn't support quoting/escaping column names, one way or another?

As Leigh Riffel said, the answer to my question depends partially on what you mean by "major."

I'm going with: No, there are no major RDBMS engines out there that can't handle name escaping/quoting. A quoted/escaped name is apparently properly called a delimited identifier.

Martin Smith pointed out that delimited identifier is in the SQL-92 specification:

<delimited identifier> ::=
    <double quote> <delimited identifier body> <double quote>

Now, how well specifications are followed varies widely, but...

I've verified the following engines:

Microsoft SQL Server supports the standard "name" at least as far back as SQL Server 2000 (and also its own non-standard [name])
Oracle supports the standard
MySQL uses backticks (`name`) by default, but if you set SET GLOBAL SQL_MODE=ANSI_QUOTES; it supports the standard
Postgres supports the standard
SQLite (I said it depended on what you call "major") supports the standard and SQL Server's [name]

I figure the first four up there cover the vast majority of installations, and the fifth is important for embedded database work (as are a couple from the following list).

a_horse_with_no_name tells us that all of these support it too:

Sybase (he says Sybase does it like Microsoft)
DB2
Apache Derby
H2
HSQLDB

(This being a Stack Exchange site, if you know of others that also support this, feel free to edit the answer and add them directly if you have enough rep. If you don't, just comment and I'll fold them in.)


How to represent a list of entities within a table of the same entity in PostgreSQL? I have a table called user with an attribute friendsList which has to be a list of ids pointing to different users. I really have no clue how to do this using Postgres

There's a couple of ways you can go about this but the most relational and normalized way would be to create a second table called UserFriendList with the columns UserId and FriendUserId which would store one row per Friend for each User. This table would be one-to-many from User.Id to UserFriendList.UserId but would also be able to help bridge the join back to the User table on UserFriendList.FriendUserId to User.Id to get all the User attributes of the friends. This kind of table is known as a bridge / junction / linking table.
Example query with this design:
SELECT
    User.Id AS UserId, User.FirstName AS UserFirstName, User.LastName AS UserLastName,
    Friend.Id AS FriendUserId, Friend.FirstName AS FriendFirstName, Friend.LastName AS FriendLastName
FROM User
INNER JOIN UserFriendList
    ON User.Id = UserFriendList.UserId
INNER JOIN User AS Friend
    ON UserFriendList.FriendUserId = Friend.Id

Alternatively you can store the FriendList column directly on the User table as either a comma delimited list or in JSON, but these are both denormalized solutions, which will become harder to maintain changes, potentially lead to data redundancy, and will inflate the size of your User table which could make querying it less efficient.


PostgreSQL determine column type when data_type is set to ARRAY I came across an oddity where PostgreSQL sets the data_type to ARRAY in the information_schema.columns table. I did the following query to gain some insight:

SELECT * FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name='__table_name__';

I noticed the udt_name column and am wondering if it is a reliable way to determine what type of array the column's data is?

SELECT column_name, data_type, udt_name FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name='__table_name__';

One option is to simply cast udt_name to a regtype:

SELECT column_name, data_type, udt_name::regtype
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name='table_name';

Another option is to use the function format_type(), but that needs the oid of the data type:

select c.attname, pg_catalog.format_type(c.atttypid, NULL) as data_type
from pg_attribute c
  join pg_class t on c.attrelid = t.oid
  join pg_namespace n on t.relnamespace = n.oid
where t.relname = 'table_name'  -- your table name
  and n.nspname = 'public' -- table schema
  and c.attnum >= 0 -- exclude internal columns


Clustered Sequential GUID Primary Key vs Non-Clustered GUID and Clustered Sequential ID Primary Keys I'm trying to figure out what the optimal solution is in regards to size and performance when using GUIDs as a primary key. The main reason for wanting GUIDs is that it's more appropriate in our micro-service architecture. There seems to be a lot of debate on what's actually best.

The two main solutions appear to be:

Have the primary key as a sequential GUID (generated by the
application, rather than SQL Servers built in NEWSEQUENTIALID() as it isn't truly sequential)
with a clustered index.
Have two primary keys, a randomly generated GUID with a
 non-clustered index and a basic int IDENTITY column with a
 clustered index -- the approach is described best in this stackoverflow response here

I'm not sure which solution is best. The first method seems to be the most popular and widely used from what I've read. The second seems to have this benefit from that linked stackoverflow response:

  Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible.

Any advice would be appreciated.

If you're going to use a GUID key, simply use a clustered index with NEWSEQENTIALID() and move on with your application.  The alternative designs are simply not predictably and significantly better when you consider the extra data structures and complexity they introduce.  So while careful testing of your actual application might reveal a superior alternative design, just start with a simple clustered index on a sequentially-generated GUID.
For the gory details on why it doesn't really matter that the GUIDs are generated in the "middle" of the sort order, and the insertion point changes see: Good Page Splits and Sequential GUID Key Generation.


Stored Procedure to return dynamically created table data Quick back story, we are working with an outside vendor that has a survey system.  The system is not necessarily designed the best in that when you create a new survey and the system creates a new table, i.e:

Tables
____
Library_1 -- table for Survey 1
SurveyId int
InstanceId int
Q_1 varchar(50)

Library_2 -- table for Survey 2
SurveyId int
InstanceId int
Q_2 int
Q_3 int
Q_4 varchar(255)

The tables are generated with the SurveyId at the end of the name (Library_) and the Question columns are generated with the QuestionId at the end of it (Q_).  To clarify, the questions are stored in a separate table so while the question ids are sequential they do not start at 1 for each survey. The question columns will be based on the id assigned to them in the table.

Seems simple enough to query, except we need to extract the data from all of the survey tables to be sent to another system and this is where the problem comes in.  Since the tables are created automatically when a new survey is added by the front-end application, the other system cannot handle this type of structure.  They need the data to be consistent for them to consume.

So I was tasked with writing a stored procedure that will extract the data from all Survey tables and place it in the following format:

SurveyId    InstanceId    QNumber    Response
________    __________    _______    ________
1           1             1          great
1           2             1          the best
2           9             2          10
3           50            50         test

By having the data for all tables in the same format, then it can be consumed by anyone no matter how many survey tables and questions exist.

I wrote a stored procedure that appears to be working but I am wondering if I am missing something or if there is a better way to handle this type of situation.

My code:

declare @sql varchar(max) = ''
declare @RowCount int = 1
declare @TotalRecords int = (SELECT COUNT(*) FROM SurveyData)

Declare @TableName varchar(50) = ''
Declare @ColumnName varchar(50) = ''

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM SurveyData
        WHERE @RowCount = rownum

        SET @sql = @sql +
            ' SELECT s.SurveyId
                , s.InstanceId
                , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
                , Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
            FROM SurveyData t
            INNER JOIN ' + @TableName + ' s' +
                ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
            ' WHERE t.columnName = ''' + @ColumnName + ''''

        IF @RowCount != @TotalRecords
            BEGIN
                set @sql = @sql + ' UNION ALL'
            END

        SET @RowCount = @RowCount + 1
    END

exec(@sql)

I have created a SQL Fiddle with some sample data and the code.

Is there a different way this type of query should be written?  Are there any noticeable issues with it?

Unfortunately, there are many unknowns with this...how many tables we will have and how many questions per survey.  I would say that we will have between 25-50 surveys, with 2-5 questions each.

Based on comments from people in chat, I decided to change my script slightly to INSERT INTO a temp table instead of creating one long SQL statement to execute at the end. So in the end my stored procedure contains the following:

create table #SurveyData
(
    tableName varchar(50),
    columnName varchar(50),
    columnId int,
    rownum int
)

create table #results
(
    SurveyId int,
    InstanceId int,
    QuestionNumber int,
    Response varchar(1000)
)

-- insert the survey table structures for use
insert into #SurveyData (tableName, columnName, columnId, rownum)
select tables1.name, cols1.name, column_id, ROW_NUMBER() over(order by tables1.name, column_id)
from sys.all_columns cols1
inner join
(
    SELECT *
    FROM sys.all_objects
    WHERE type = 'U'
    AND upper(name) like 'LIBRARY%'
) Tables1
    ON cols1.object_id = tables1.object_id
WHERE cols1.name Like 'Q_%'
ORDER BY tables1.name, column_id;

declare @sql varchar(max) = '';
declare @RowCount int = 1;
declare @TotalRecords int = (SELECT COUNT(*) FROM #SurveyData);

Declare @TableName varchar(50) = '';
Declare @ColumnName varchar(50) = '';

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM #SurveyData
        WHERE @RowCount = rownum

        SET @sql = 'INSERT INTO #results ' +
                    ' SELECT s.SurveyId
                        , s.InstanceId
                        , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
                        , Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
                    FROM #SurveyData t
                    INNER JOIN ' + @TableName + ' s' +
                    ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                    ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

        SET @RowCount = @RowCount + 1
    END

    SELECT SurveyId, InstanceId, QuestionNumber, Response
    FROM #results

drop table #SurveyData
drop table #results

See SQL Fiddle with the final script


Postgresql schema only works when wrapped in quotation marks I'm seeing an odd behaviour - if I don't wrap my schema name in quotes, the server returns

  [42601] ERROR: syntax error at or near "."

The query is simply:

select count(*) from "authorization".logins

Here's what psql \dn shows:

     List of schemas
     Name      |  Owner
---------------+---------
 authorization | user_login
(1 row)

Schema name doesn't contain upper case letters, only English ones. Tried it using DataGrip and Npgsql (.NET driver for PostgreSQL).
DataGrip added quotes around schema name, when with others it doesn't do so.

Table and schema was created and applied via Flyway

authorization is a reserved keyword in Postgres, so it needs to be quoted to be used as a (database/schema/table) name. See the list: Appendix C. SQL Key Words

And a very popular opinion among Postgres developers - to avoid the complications - is:
Do not use reserved words as identifiers.


reject queries based on specific client_app_name and nt_username With a surge of applications that can be used to pull information, my sql server is constantly getting tapped, and there are a couple of users that keeps running refresh. Is there a way to reject query based on specific client_app_name and nt_username? Alternatively, is there a way to add the combination of the user and the app to security to decline access to SQL? i.e. Approve the user access if client_appname is excel but decline if the appname is 'Mashup Engine'.

Yes, you can use a Logon Trigger and call ROLLBACK if you want to deny the logon.

Alternatively if you are on Enterprise Edition you could use these in a resource governor classifier function to route them to a workload group in a resource pool with limited resources.

Note the app_name() is potentially easily settable from the client if they are making adhoc connections, e.g. via SSMS, or can edit the connection string used by your app.


How to find a data warehouse's design diagram? I have a database with lots of tables. I need to quickly find out what data warehouse schema was used. Specifically, looking for the dimensions and fact tables. There does not seem to be a standard naming convention. The database diagram is not showing anything.

You probably need a product that can perform database design reverse engineering.

Having said that, we refrain from giving product advice on dba.stackexchange.com since questions about which tool, library, product or resource you should use quickly become obsolete and often are just about the preferences of the answerer.


Set oracle workspace from java application I'm connecting to an Oracle database 12c which uses Oracle Workspace Manager from a java application.  I'm trying to execute the following command from my java application

EXECUTE DBMS_WM.GotoWorkspace('WORKSPACE');

using this code

String setWorkspace = "EXECUTE DBMS_WM.GotoWorkspace(?);";
CallableStatement cs = conn.prepareCall(setWorkspace);
cs.registerOutParameter(1, OracleTypes.CHAR);
cs.setString(1, "'WORKSPACE'");
cs.execute();

and I'm receiving this exception

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:210)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:53)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:938)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3923)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:5617)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)

I can execute this command from SQL Developer, and I can execute a regular query like this

ResultSet rs = stmt.executeQuery(query);

from my java application.  I get the same exception trying to execute the above command with executeQuery()

I've done some reading and found out that DBMS_WM is a public synonym.  The code I'm using to run the execute statement is used for stored procedures.  However I haven't been able to find any examples of executing a public synonym from a java application.  How do I call a function from a public synonym from java?

Update

I changed my java code to

    String setWorkspace = "CALL DBMS_WM.GotoWorkspace('WORKSPACE');";
    CallableStatement cs = conn.prepareCall(setWorkspace);
    cs.execute();

and now I get

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

I was receiving the

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

because I was using the EXECUTE command rather than the CALL command.  In SQL Developer either EXECUTE or CALL works.
I was getting

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

because of the semicolon at the end of the SQL statement.  The following

String setWorkspace = "CALL DBMS_WM.GotoWorkspace('WORKSPACE')";
CallableStatement cs = conn.prepareCall(setWorkspace);
cs.execute();

works.


Proper way to store a password within a SQL Server agent job I know this is a stupid idea, but I'm having a hard time finding a better solution right now.  I need to execute several sql stored procedures on a daily basis, then execute a powershell script to trigger an external application to pull some data off the sql server

The second step needs to pass username and password as arguments. These two servers are not a part of the same windows domain, so any user-level trust is not possible, only explicitly sending a username and password in the powershell scripts will work.

The server is SQL Server 2008, and we do not have SSIS available as an option.  The sql server itself is restricted to only 3 users today, so having the password in plain text as a powershell command step within an agent job wouldn't get any of those users privilege they don't already have today, but this still just seems like there should be a better way I'm missing.

One risk mitigation is to use a signed stored procedure.  Here's an example of this in action.  In summary, you package all the sensitive action (table access etc.) inside a stored procedure.  You create a certificate in SQL server to sign that SP.  You create a new login who's only ability is to execute the SP and you send the password to this low-privilege login in plaintext.  Depending on how sensitive the data coming from the SP are this can limit damage from a password leak.


A table with a foreign-key, referencing the tables own primary key I was looking at the database sample that is provided by the DB2 server installaion. This database is the one created when running db2samle.exe from the "bin" folder. I noticed that the table "ACT" contains a primary key with a references to it self, see sql below, and was wondering if there are any logic reason for this? or is it just a glitzh in the design?

CREATE TABLE "DB2ADMIN"."ACT" (
        "ACTNO" SMALLINT NOT NULL,
        "ACTKWD" CHAR(6) NOT NULL,
        "ACTDESC" VARCHAR(20) NOT NULL
    )

ALTER TABLE "DB2ADMIN"."ACT" ADD CONSTRAINT "PK_ACT" PRIMARY KEY
    ("ACTNO");

ALTER TABLE "DB2ADMIN"."ACT" ADD CONSTRAINT "RPAA" FOREIGN KEY
    ("ACTNO")
    REFERENCES "DB2ADMIN"."ACT"
    ("ACTNO")
    ON DELETE RESTRICT;

DELETE RESTRICT in DB2 deals with when you wish to delete records from a parent table.

As noted from the DB2 Information Center:

  The delete rule of a referential constraint applies only when a row of
  the parent table is deleted. More precisely, the rule applies only
  when a row of the parent table is the object of a delete or propagated
  delete operation (defined below), and that row has dependents in the
  dependent table of the referential constraint. Consider an example
  where P is the parent table, D is the dependent table, and p is a
  parent row that is the object of a delete or propagated delete
  operation. The delete rule works as follows:

  With RESTRICT or NO ACTION, an error occurs and no rows are deleted.

  With CASCADE, the delete operation is propagated to the dependents of p in table D.

  With SET NULL, each nullable column of the foreign key of each dependent of p in table D is set to null.

  Any table that can be involved in a delete operation on P is said to
  be delete-connected to P. Thus, a table is delete-connected to table P
  if it is a dependent of P, or a dependent of a table to which delete
  operations from P cascade.

So, while it appears like this may prevent records from being deleted from the table, it doesn't. It just prevents records from being deleted from the table if there are records in the child table. Since there is no child table, that clause is essentially worthless and really only adds cost to determining INSERT/UPDATES/DELETES. It is fanciness that serves absolutely no purpose.

You can test this yourself by issuing the following commands:

db2 "insert into <schema>.act values (1, 'blah', 'another blah')";
db2 "select * from <schema>.act";
db2 "delete from <schema>.act where actno = 1";
db2 "select * from <schema>.act";

I tested that myself against the sample database and it ran fine. You will find that it does in fact delete the record just added.

Personally I think it is just fanciness that somebody threw in there that doesn't really belong.


SQL Server : how to drop all constraints on a table in T-SQL I´d like to drop all constraints on all tables in my database, because I need to change the relationships of many of the tables.

Is there any way to do that with T-SQL?  I cannot find any solution and code like:

ALTER TABLE testDB.dbo.testTable1
DROP ALL CONSTRAINT
GO

doesn´t work.

You will have to build your T-SQL statement for each constraint that you want to drop. There are undocumented procedures that could be used for this like sp_MSforeachtable, but I am not to fond of using them. However, SQLUSA.com provides this as an example:

-- SQL disable all constraints - disable all constraints sql server
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

Another option was provided here on SO, by Aaron Bertrand.

Then if you like PowerShell you could do something like this (forum located on SQLServerCentral.com), provided by Rob Farley:

#This is one line just broken up for readability.
PS SQLSERVER:\sql\localhost\default\Databases\YourDatabase> dir Tables\*\Columns\*\DefaultConstraint | % {$_.Script(); $_.Drop()}

And if you want a few more examples check here.


JOIN correctly no matter which end is linked Suppose I have the following two tables: SupportTicket and Link. I also have other tables that Link can connect between. I've created these tables and a hypothetical third table that can be linked to on SQL Fiddle.

Let's suppose I want to count the number of support tickets submitted for technical support related reasons, here is my query:

SELECT
  st.Submitting_User,
  COUNT(l.Link_ID) AS [Count]
FROM
  SupportTicket st
  JOIN Link l
    ON st.SupportTicket_ID = l.Entity_ID1
WHERE
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted BETWEEN '20140201' AND CURRENT_TIMESTAMP
GROUP BY
  st.Submitting_User, st.Submitting_Department
ORDER BY
  st.Submitting_User

Note that this query is already set up and runs on SQL Fiddle (where you can also see the table structures, etc.).

Here's the problem: the SupportTicket can be on either end of the link. Currently this only counts those where the SupportTicket is on the first link end (Entity_ID1). This should also count it when the SupportTicket is on the second link end (Entity_ID2). How do I fix this?

An OR appears to work fine in your fiddle example.

SELECT
  st.Submitting_User,
  COUNT(l.Link_ID) AS [Count]
FROM
  SupportTicket st
  JOIN Link l
    ON st.SupportTicket_ID = l.Entity_ID1
    OR st.SupportTicket_ID = l.Entity_ID2
WHERE
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted BETWEEN '20140201' AND CURRENT_TIMESTAMP
GROUP BY
  st.Submitting_User, st.Submitting_Department
ORDER BY
  st.Submitting_User


How can I modify a trigger at subscribers SQL Server 2005? I have a SQL Server 2005 publisher with SQL Server 2005 subscribers and one SQL Server 2000 subscriber running merge replication.

Is there any way to modify triggers at a subscriber with the replicating schema changes set to false (replicate_ddl = 0) without reinitializing the whole publication?

A solution where the triggers are only modified on the SQL Server 2005 databases is fine.

Yes, you can use the system stored procedure sp_addscriptexec to run an ad-hoc script on all the subscribers.


SQL Server 2019: InitPersistentVersionStore failed after installing CU2 I have installed SQL Server 2019 CU 2 over a vanilla install of SQL Server 2019 RTM.

Now I am puzzled by the following messages in the error log at startup:

  RecoveryUnit::InitPersistentVersionStore failed with db [1] name
  [master]
  RecoveryUnit::InitPersistentVersionStore failed with db [4] name
  [msdb]

AFAIK the PersistentVersionStore links to the new feature Accelerated Database Recovery however I have not configured it.

Here's a screenshot from the error log:

The error occurs again after a service restart.

Is it automatically enabled with CU2 for system databases? Is there anything I can do about this error in order to resolve it?

I already did a web search with the error message but most surprisingly didn't find anything regarding to it.

Quick update (March 16th):

Opened a Ticket with Microsoft Support (no answer yet)
Installed SQL Server 2019 CU 3: Error persists at service restarts just as with SP 2

Eventually, I got an official answer from Microsoft Support which I am glad to share here:

  Hello Martin,   Hope you are fine.   Thanks for call yesterday. As
  validated from Product Group:

  This is only a info message and not a bug. All system databases that
  come from SQL17 to SQL19 will not have persisted version store
  initialized because of the way we did upgrade step in SQL17. But this
  should not affect customer’s databases.”   But we probably will not
  fix the message on next releases as it’s harmful – that’s why was not
  fixed until now.   The question we have discussed, as this is
  something that harmful but should not be there, so you will not be
  charged for this case.   I will close the case and mark it as product
  issue.

It's a bit contradictory on whether the message will be fixed or not....last statement on the phone was that in a problematic case it typically arises as a followup error due to database recovery problems. In my case that is not true and it's totally unproblematic...I even don't use ADR.

A big recommendation for everyone upgrading from SQL 2017:
Do not perform an in-place-upgrade but rather do a fresh install of SQL 2019 to avoid seeing this error message.


SQL Server 2008 R2 - Limit viewing on database list In SQL Server 2008 R2 I have user1, he owns 3 db (db1 , db2, db3) and I want he can see only these 3 db when he login by using SSMS.

I did REVOKE VIEW ANY DATABASE FROM PUBLIC, but then he cannot see any db in list.
How can I fix it?

Make sure his server login exists.(Meaning you dont delete
this!!!)
DENY VIEW ANY DATABASE TO user (I think this is where you are stuck right now as the user is unable to view any databases.)
Then Delete his user logins from those 3 databases that he is a database owner on.
Then go to each database > right click > properties > files > Choose his login as the database owner on those 3.

This way he will remain the database owner on those db's and also not be able to view the other databases on that instance.

Tried and TESTED!!


Conditional Alias in SQL Is there any way to give conditional alias to SQL columns?.

Because I have two tables for Fields and Category. Meaning of each field changes conditionally.

 DECLARE @f1 NVARCHAR(10);
 set @f1= (select Field1 from Category);
 select Field1 as @f1 from AdditionalFields

You should use a dynamic query.

 DECLARE @f1 NVARCHAR(10);
 set @f1= 'MyCategory'

 DECLARE @CMD NVARCHAR(MAX);
 SET @CMD = 'SELECT Field1 AS ' + @f1 + ' FROM MyTable';

 SELECT @CMD;
  --EXEC (@CMD);
GO

| (No column name)                         |
| :--------------------------------------- |
| SELECT Field1 AS MyCategory FROM MyTable |

dbfiddle here


PostgreSQL psql command line tool and SET CONSTRAINTS DEFERRED I am using PostgreSQL 9.1.5 and the psql to run a scipt containing a series of INSERT statements in various tables:

psql -U usernamefoo databasenamefoo -f dml_script.sql

The table constraints have been created with the DEFERRABLE option and the script itself (file dml_script.sql above) issues the following before an offending INSERT:

SET CONSTRAINTS ALL DEFERRED;
INSERT INTO KM_TRANSACTION_GROUPS ..

I've checked that the specific constraint on the table in question has been created with the DEFRRABLE option:

ALTER TABLE ONLY KM_TRANSACTION_GROUPS ADD CONSTRAINT TRGR_TRGR_FK FOREIGN KEY (TRGR_PARENT_ID) REFERENCES KM_TRANSACTION_GROUPS(TRGR_ID) DEFERRABLE;

Yet, running the DML script fails:

psql:./schema-dml-test.sql:8: ERROR:  insert or update on table "km_transaction_groups" violates foreign key constraint "trgr_trgr_fk"
DETAIL:  Key (trgr_parent_id)=(2) is not present in table "km_transaction_groups".

My guess is that psql treats each INSERT line in the dml_script.sql as a separate transaction. If that's the reason, is there a way to group INSERT statements in the dml_script.sql file into one longer transaction (or perhaps treat the whole file as one single transaction) so that at its end (when the scope of the SET CONSTRAINTS ALL DEFERRED; terminates), no constraints are violated?

Otherwise some other method (other then changing the order of the INSERT statements which I have reasons I don't want to) to allow me to load the table with a series of INSERT statements so that constraints can be temporarily deactivated ?

The -1 option to psql causes it to wrap a file specified by -f in a BEGIN..COMMIT block, making it a transaction.

Otherwise, add the BEGIN and COMMIT commands to your script so that it becomes a single transaction.


SSAS: Move tempdb database For SQL server 2008 R2 Analysis services, how can I move tempdb database files from one drive to another? At the time of installation, I didn't have a separate drive for tempdb. Now that I do have one, I need to move to the new drive.

Connect to your Analysis Services server in SQL Management Studio.  Right-click the server, then click Properties in the menu.  In the Properties dialog, check the "Show Advanced (All) Properties" check box at the bottom of the list.  Then, in the grid above that check box, set the location you want for SSAS TempDB in the "TempDir" setting.

You will have to restart SQL Server Analysis Services for the change to take effect.


Tagging PostgreSQL database Is it possible to "tag" a PostgreSQL database with meta-data?

Here is my intended use case: I have a database that goes through several workflow steps, and it would be nice to record somewhere around a database that a step has been taken. Of course there could be a table inside the database for recording this, but I am not supposed to change the schema for the task at hand.

Could/should e.g. custom options (e.g. ALTER DATABASE db SET a.b = 'c') serve such a purpose?

As a_horse commented, a COMMENT ON DATABASE db IS 'foo'; would be a simple option.

set_config() settings are for the duration of the transaction or session only. If you need to persist information beyond the current session, that won't do.

For more structured bookkeeping, maybe including a timestamp etc, consider a bookkeeping table in another database in the same db cluster - without altering the db in question at all. (Maybe in addition to a comment on the main db indicating the current status.) Sample psql session:

CREATE DATABASE db_meta;
\c db_meta
CREATE TABLE db_status_log (
  log_id serial PRIMARY KEY
, log_ts timestamptz NOT NULL DEFAULT now()
, status text
);

Etc.

\c db_meta is the psql meta-command to connect to a db.