texts: Can view adapt to implicitly reference user's default schema?

texts:
A bit of background. I have a base application and most clients use it as standard. However some clients have small code and database customisations. Each of these clients has their own branch and maintenance can be tricky.

I want to consolidate all these into a single database structure (not a single database - we aren't doing multi-tenancy) to enable upgrades to be applied in a much more uniform fashion. I'm still at the proof of concept stage, but the route I was going down would be to have the standard objects stay in the schema they currently exist in (mostly dbo) and have the custom objects reside in a schema for each client.

For example, I could have dbo.users and client1.users which has some additional columns. If I set the default schema for the client to be "client1" then the following query

SELECT * FROM users

will return data from the client1 schema or the dbo schema depending on which login is connected. This is absolutely perfect for what I'm trying to achieve.

The problem I'm running into is with Views. I have many views which are in the dbo schema and refer to the Users table. No matter which user I connect to the database as, these views always select from dbo.users.

So I'm guessing the question I have is: Can I prefix the tables in the view with some variable like "DEFAULT"? e.g.

SELECT u.username, u.email, a.level
FROM DEFAULT.users u INNER JOIN DEFAULT.accessLevels a ON u.accessID = a.accessID

If this isn't possible and I'm totally barking up the wrong tree, do you have any suggestions as to how I can achieve what I'm setting out to do?

Many thanks.


texts: How to Restore MongoDB Database After Having Deleted DB Files From Directory

texts:
I'm running MongoDB 2.6 on Ubuntu 14.04 and I accidentally deleted db file in /var/lib/mongodb.

I cannot restart the MongoDB instance:

mongo: error SEVERE: unable to open file number 5.

If I purge and re-install will I be able to restore the data? Any help is appreciated.


texts: How to ORDER BY typical software release versions like X.Y.Z?


texts:
I currently use SQL Server 2012 and have BlitzIndex 4.4
not infrequently the execution plan will say I have a missing index on a table.  BlitzIndex will often not say any indexes are missing on that table.
question:  How are both of them determining that an index is missing?  I assumed both were drawing info from the dmv's however I no longer assume that to be the case.


texts: Optimization: insert only not existing rows with additional conditions

texts:
I have two databases:

'target' database looks like

CREATE TABLE parent (
  erow_id  integer PRIMARY KEY,
  -- these two columns is a composite key (uid)
  uid_p1   integer,
  uid_p2   integer,
);

CREATE TABLE child (
  erow_id  integer PRIMARY KEY,
  parent   integer, -- pointed to parent.erow_id
  value    text,
  vtype    integer
);

-- only this one index is presented
CREATE INDEX idx_child_parent ON child (parent);

'patch' (it's attached database) looks like

CREATE TABLE source (
  value   text,
  -- composite uid pointed to target.parent
  uid_p1  int,
  uid_p2  int
);

Both databases are 'fixed' and it's really difficult to modify'em, even add indexes.

I need to insert new rows (values) to child from patch only if parent has no rows of type = 100 (for example).

At this moment I use such an ugly query:

INSERT INTO target.child (value, parent, vtype)
  SELECT
    p1.value, target.parent.erow_id, 100
  FROM
    patch.source p1
    INNER JOIN target.parent
      ON (target.parent.uid_p1 = p1.uid_p1
      AND target.parent.uid_p2 = p1.uid_p2)
  WHERE NOT EXISTS (
    SELECT
      1
    FROM
      patch.source p2,
      target.child,
      target.parent
    WHERE
      (p1.rowid = p2.rowid) AND
      (target.child.vtype = 100) AND
      (target.child.parent = target.parent.erow_id) AND
      (target.parent.uid_p1 = p2.uid_p1) AND
      (target.parent.uid_p2 = p2.uid_p2)
  );

EXPLAIN QUERY PLAN:

SCAN TABLE patch.source AS p1
EXECUTE CORRELATED SCALAR SUBQUERY
SEARCH TABLE patch.source AS p2 USING INTEGER PRIMARY KEY (rowid=?)
SCAN TABLE target.child // looks like a bottleneck
SEARCH TABLE target.parent USING INTEGER PRIMARY KEY (rowid=?)
SEARCH TABLE target.parent USING AUTOMATIC COVERING INDEX (uid_p1=? AND uid_p2=?)

Is it possible to optimize this incredibly slow query w/o new indexes? It's a big problem - I cannot modify at least target db at this moment.

Thank you.


texts:
Is there a way to find out how much memory a table is taking up in SQL Server (2005 and greater)?

For example, suppose I have a table with 30 million records. I'd like to know how many pages belonging to this table are currently on the buffer cache, including index, data and text/image pages.

I found this query by Pinal Dave, but it seems this query only returns pages allocated by indexes (be it clustered or unclustered ones).


texts: Run several commands against data in text file (SQL Server 2008 R2)

texts:
I'm new to this website so forgive me if I'm asking a rudimentary question and/or one that's been asked 100 times before. I wasn't able to find an answer during my searches here.

I'd like to have my script read in values from a txt file (one value per line) and have three different statements executed for each value.

Pseudo code:

$file = getfile('c:\file.txt');

foreach($value in $file)
{
    DELETE x WHERE x = '$value';
    DELETE y WHERE y = '$value';
    DELETE z WHERE z = '$value';
}

I understand that this may not be as easy as my pseudo code desires but all my searches on Google returned long articles with copious amounts of SQL that were way over my head.

I'm looking for examples that I can implement, good articles on the subject, and/or a slap in the face telling me to wake up and realize this is simply not easy in SQL.


texts:
I've been trying to Query an item with its sublist all in one go and one way is to use for xml and subquery. I've been using this strategy with sql server and works fine.

The relationship is Order and OrderItems

for sql server, the query might look like this:

Select O.OrderId, O.ShippingDate,
    (
         SELECT OI.ProductId, OI.Description, OI.Cost
         FROM OrderItems OI
         WHERE O.OrderId = OI.OrderId
         FOR XML PATH('OrderItem'), ROOT('OrderItems')
    ) as OrderItems
    FROM Orders O

I need only the order items list to be xml, the order details is fine as is.

googling ways to apply xml result in ibm db2, I found in their documentaion on their website:
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.xml.doc/doc/c0052638.html

I found some snippet:

    SELECT XMLELEMENT (NAME "saleProducts",
                   XMLAGG (XMLELEMENT (NAME "prod",
                           XMLATTRIBUTES (p.Pid AS "id"),
                           XMLFOREST (p.name as "name",
                                      i.quantity as "numInStock"))))
    FROM PRODUCT p, INVENTORY iWHERE p.Pid = i.Pid

which I applied as

SELECT O.OrderId, O.ShippingDate,
        XMLELEMENT (NAME "OrderItems",
               XMLAGG (XMLELEMENT (NAME "OrderItem",
                       XMLATTRIBUTES (OI.ProductId AS "ProductId"),
                       XMLFOREST (OI.Description as "Description",
                                  OI.Cost as "Cost"))))
    FROM Orders O, OrderItems OI
    WHERE O.OrderId = OI.OrderId

But on execution, IBM db gives an error message:

  OLE DB provider "IBMDA400" for linked server "XXX" returned message "SQL0104: Token "OrderItems" was not valid. Valid tokens: ) ,.
          Cause . . . . . :   A syntax error was detected at token "OrderItems".  Token "OrderItems" is not a valid token.  A partial list of valid tokens is ) ,.
          This list assumes that the statement is correct up to the token.
          The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :
          Do one or more of the following and try the request again:
           Verify the SQL statement in the area of the token "OrderItems". Correct the statement.
          The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses.
           If the error token is , correct the SQL statement because it does not end with a valid clause.".

Help


texts: MySQL query returns unexpected results inside a stored procedure

texts:
This is my stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `isUserValid`(IN `facebookId` VARCHAR(20), IN `userAccessToken` TEXT)
    NO SQL
    COMMENT 'check if user is valid. return true or false'
Begin
    /* declare the select resluts variables*/
    Declare accessToken TEXT;
    Declare expires datetime;

    /* fill the variables with saved user's access token an expires time*/
    Select Access_Token, Expires
    Into accessToken, expires
    From Users Where Facebook_Id = facebookId;

    select expires,accessToken;
    /* If the saved access token and expires are valid return true */
    If (BINARY accessToken = userAccessToken And expires > now()) Then
        select true As isUserValid;
    Else
        select false As isUserValid;
    End If;
End

For some reason the query:

/* fill the variables with saved user's access token an expires time*/
Select Access_Token, Expires
Into accessToken, expires
From Users Where Facebook_Id = facebookId;

returns the Expires as null all the time.

When I'm running the same query outside of the procedure, it returns a valid value for Expires.


texts:
Setup Information

One ConfigServer

mongod.exe --configsvr --dbpath C:\MongoDBCluster\ConfigSrv

--Mongos Server--

mongos.exe --configdb 10.133.1.130)

2 Shard Servers

mongod.exe --shardsvr --dbpath C:\MongoDBcluster\Server2\configdb --port 27020

mongod.exe --shardsvr --dbpath C:\MongoDBcluster\Server3\configdb --port 27021

Configured Sharding using mongos

mongos> sh.status()
--- Sharding Status ---
  sharding version: {
        "_id" : 1,
        "minCompatibleVersion" : 5,
        "currentVersion" : 6,
        "clusterId" : ObjectId("55dec7d254984cdf812cdf55")
}
  shards:
        {  "_id" : "shard0000",  "host" : "Server1:27020" }
        {  "_id" : "shard0001",  "host" : "Server2:27021" }
  balancer:
        Currently enabled:  yes
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  0
        Migration Results for the last 24 hours:
                No recent migrations
  databases:
        {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
        {  "_id" : "demo1",  "partitioned" : true,  "primary" : "shard0001" }
                demo1.demo
                        shard key: { "_id" : 1 }
                        chunks:
                                shard0001       1
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : shard0001 Timestamp(1, 0)

I have 2 problems

When i try to check the sharding status from demo1 db on server1 i get below error

  use demo1
  switched to db demo1
  sh.status()
  printShardingStatus: this db does not have sharding enabled. be sure you are connecting to a mongos from the shell and not to a mongod.

I have inserted couple of records which did not get replicated to server2. what am i doing wrong?


texts: Why do async_network_io wait types occur?

texts:
Last week something strange happened on our database. All of a sudden, the application blocked for our users who were not able to save new entities etc. After looking at the Activity Monitor of the SQL Server (2008 with compatibility mode 2005) I saw the following three entries:

After some time, the users got a connection timeout. When I killed the process 64, they could save normally again.

The problem is that the entities they tried to save during the block were inserted into the DB more than once (up to 3 times) even though there is code which should prevent this from happening (number column which has to be unique but without a constraint... the check happens in the code).

We use Entity Framework 6.0.

Does anyone of you know why and when these ASYNC_NETWORK_IO wait types occur and how to avoid them?
And what exactly do they mean?


texts: "ORA-01017: invalid username/password" : Why is that?

texts:
What is the difference between the two :

SQLPLUS / AS SYSDBA

and

SQLPLUS
Enter user-name: SYSDBA
Enter password:

If I use the first command I get connected and see the prompt SQL> but upon using the second command I always get ORA-01017: invalid username/password; logon denied , when I enter the correct password. Why is this ?

Also how do I get connected as the SYSDBA using the first command , when I didn't enter any password.

I am using Oracle Database 11g Express Edition Release 11.2.0.2.0


texts:
There were other similar questions asked, but none that were able to tell me why I can't connect using a C# .NET web site I've stood up.  While I know this forum is more for DBAs connecting to databases using tools and not so much troubleshooting apps connecting, I thought I'd give it a shot posting the question here.

The web site works/connects from my workstation when ran from Visual Studio, but when I publish it to the server, it gives me the ORA-01017:  invalid username/password;logon denied error, even though I can connect via everything else with the same account/password. Situation:

I can connect using Oracle SQL Developer, SQL*Plus just fine with the account I'm using... let's call it "myaccount".
Using Net Manager, I can expand "Service Naming" and see the entries from my tnsnames.ora.
a. The same string that exists in tnsnames.ora/Service Naming area, exists as a hard-coded string in my application, with the exception of things like the host name, service name, port, user name, and password, which are passed-in as parameters from my web.config.
b. I can click a connection name and everything appears correctly, and clicking "Test Service" > "Change Login" > enter "myaccount" and password > click OK > click "Test" succeeds.
c. I have double-checked my parameters in my web.config - nothing has changed since the web site ran on my local workstation.
tnsping CONNECTION_NAME succeeds.
Environmental variables are the same on both computers, as are the Oracle client locations.
a. ORACLE_HOME = C:\oracle\product\12
b. PATH = C:\oracle\product\12\bin; .....
c. TNS_ADMIN = C:\oracle\product\12\network\admin
d. Registry entry added to HKLM\Software\Oracle\KEY_OraClient12Home for TNS_ADMIN = C:\oracle\product\12\network\admin
I've tried the ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; command and reset the password, just in case that was the issue. I tried connecting with all-caps versions of myaccount and its password, as well as all lower-case versions of both (lower-case on both is how I had it originally when it worked on my workstation).
The account has a PASSWORD_VERSIONS of 10G and 11G - I don't know if it matters that I'm using a 12c client and 12C is not there. Main difference between the server and my workstation is I only have the 12c client on the server - I have 10g, 11g, and 12c on my workstation. I was going to try using the 11g ODP.NET assembly, Oracle.DataAccess.dll, but my code won't compile (I get "Could not load file or assembly 'Oracle.DataAccess' or one of its dependencies. An attempt was made to load a program with an incorrect format.".), even after changing the namespace from using Oracle.ManagedDataAccess.Client to using Oracle.DataAccess.Client.
Attempted passing in the password with quotes around it and without--both methods worked on my Visual Studio instance, just not on the server.
If I deliberately put in a totally bogus password, and run the site on the server enough times, it will lock the account, so I know it's connecting to the database.

Running out of things to try... any help/suggestions would be appreciated.


texts: Which PgBackRest policy for differential backups in PostgreSQL

texts:
I set up pgBackRest and PostgreSQL configuration in two different servers.

I set up with crontab one full backup a week and one differential backup a day.

0  1   *   *   0     pgbackrest --type=full --stanza=stanzaname backup
0  1   *   *   1-6   pgbackrest --type=diff --stanza=stanzaname backup

I set up retention as below to save disk usage:

repo1-retention-full=2
repo1-retention-diff=1
repo1-retention-archive-type=full

Is it possible to ensure PITR to cover up to 2 weeks with this retention configuration?

If yes, is there a benefit to set a differential retention more than 1?
Because keeping more differential backups could consume a lot of disk space.


texts:
I was wondering if the Database Backup .bak Contain the Log file.

i found that the space different between the database and backup is form the locked space for the database.
but i didn't found any related posts that says if the log file is in the backup file.


texts: SQL Server replication - error when creating a subscription - The article '(null)' does not exist

texts:
When trying to add a subscription to an existing publication

use [Product]

exec sp_addsubscription @publication = N'Product',
        @subscriber = N'REPLON1',
        @destination_db = N'Product',
        @subscription_type = N'Pull',
        @sync_type = N'none',
        @article = N'all',
        @update_mode = N'read only',
        @subscriber_type = 0

This is some info that I found:

  sync_type is nvarchar(15),  with a default of  automatic. Can be
  automatic or none.  If automatic, the schema and initial data for
  published tables are transferred to the Subscriber first.  If none, it
  is assumed the Subscriber already has the schema and initial data for
  published tables.  System tables and data are always transferred.

This is the error I am getting:

  Msg 20027, Level 11, State 1, Procedure sp_MSadd_subscription, Line
  228
  The article '(null)' does not exist.

  Msg 14070, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1213
  Could not update the distribution database subscription table. The subscription status could not be changed.

  Msg 14057, Level 16, State 1, Procedure sp_MSrepl_addsubscription_article, Line 388
  The subscription could not be created.

and when I run the script below, I get the exact same error.

exec sp_addsubscription @publication = N'Product',
        @subscriber = N'REPLON1',
        @destination_db = N'Product',
        @subscription_type = N'Push',
        @sync_type = N'automatic',
        @article = N'all',
        @update_mode = N'read only',
        @subscriber_type = 0


texts: SQL Server on SAN, same LUN: one logical drive vs multiple

texts:
First, let me start by saying that I did notice there are multiple similar questions to this, but neither of them is exactly what I want to ask, and not one of them has a definite answer.

Second, let me confirm that I do understand, that it's recommended to use different LUNs/spindles for log/data even on SAN/virtualized environment.

Now the question:

IF there is just one single LUN assigned to a SQL Server virtual machine, is there a performance (NOT management, security, or any other) difference between following configurations:

One virtual disk file (vhd, vmdk, whatever) with one virtual controller with one logical partition in virtual OS, and tempdb/data/log in that partition
One virtual disk file with one virtual controller but multiple logical partitions/drive letters for tempdb/data/log
Multiple virtual disk files one separate virtual controller with separate partitions for tempdb/data/log

So far I have heard the following answers:

There is no or negligible performance difference, as in the end, its still just same spindle with same IO capacity
There is a performance difference in some scenarios because virtual controllers have separate (virtual) IO queues and scheduling in the guest OS

If important, let's assume the workload is very large number of threads with very small requests (so deep small queue).

I would like to get this settled once and for all, so I would like to ask to stay on topic of performance on one LUN, and refrain from suggestions for optimizing the layout.


texts: Active directory automatic login to SSRS

texts:
I have a server running SSRS 2012 at address server1.olddomain.com. This is working fine.

My users will be accessing this server from another address (the server is not physically moving). The new address is server2.newdomain.net. I modified the UriRoot option in the config file, and it works fine, except users are now prompted for their username and password through AD rather than being logged in automatically. (Once they enter their username and password in the form olddomain\username, they are logged in successfully).

How can I configure this to log users in automatically? The users' domain hasn't changed, nor has the server moved physical locations. Is this possible to do?

EDIT: I wasn't sure whether to post on here or ServerFault. Let me know if you think it would be better suited there.


texts:
I started working on a project whose former developer ragequited, and it left a complete mess both in the code and database. The server is using MySQL 5.5

There are a lot of tables created for backup or test purposes. For example, from all this tables, only two are really being used.

Is there any way to check which tables weren't used in the last XX hours, to be able to safely delete them? Can I set a log for that, besides from the general log, which is really big and fills the available space too quickly?

I cant trust names as in some cases, the table being used is the one that ends with _test or _bk.


texts: IF @@trancount > 0 COMMIT TRANSACTION Causing blocking

texts:
We have a Java application / hibernate connecting to SQL Server. It looks like implicit transaction is on by default. I am not sure if this is from hibernate or JDBC Driver . However this implicit transactions are causing alot of blocking. when I checked I always find that the head of the blocking is
set implicit_transactions off IF @@TRANCOUNT > 0 COMMIT TRAN

My question is
what can I perform from  the database side to avoid this problem?


texts: Ensure order when select two rows using two identifiers on the same column

texts:
I'm trying to write a query to retrieve exactly two rows from a table using the same column for identification.

SELECT * FROM teams WHERE name = $1 OR name = $2;

Is there any way to ensure that the result for name = $1 is the first result returned and name = $2 is the second?


texts: Oracle using index on partitioned table when full scan would have been better

texts:
Trying to make some sense out of this observation in Oracle.

Query: SELECT ... FROM MY_TABLE WHERE PART_KEY=x and DT_KEY between 20120101 and 20120731

Uses an index scan on PART_KEY/DT_KEY, even though autotrace shows that the index scan hits more blocks (one at a time, with single-block reads) than a full scan would have hit (in a single multi-block read).  Stats are up to date.

The strange part is if I try with a copy of the same table and index WITHOUT partitioning, there seems to be a much higher threshold of selectivity before using the index - the range I gave above did a full scan, and only a very narrow range of values for DT_KEY between d1 and d2 would use the index.  I verified the index would be used in the non-partitioned case as well but Oracle seems more biased towards doing a full scan.

How is Oracle making the decision to use the index instead of a full scan, and what else should I be looking at?  Stats are up to date since I collected them right away.

Table structure

CREATE TABLE MY_TABLE (
   PART_KEY NUMBER(10) NOT NULL,
   DT_KEY NUMBER(8) NOT NULL,
   ...
)
PARTITION BY LIST(PART_KEY) (
  PARTITION P1 ...
)
AS SELECT .... FROM [source table]

CREATE INDEX MY_INDEX ON MY_TABLE(PART_KEY, DT_KEY) LOCAL;

EXEC DBMS_STATS.GATHER_TABLE_STATS('[USERNAME]', 'MY_TABLE');


texts: Syncing DB's between local and live server?

texts:
I need to sync two tables between MySQL databases. I have read about MySql Replication. But not sure whether it can be used in this scenario. What I want to achieve is explained below.

There are two tables in a local server which needs to be synced to and fro to a live server. There is a random generated customer id field which is unique. This customer  id has a foreign key reference to the second table as well. Local server is used by an android application which will perform new insertions. And on the live server from the website registration data is populated.

What I have done so far is.

On the live server customer id is 6 digit integer. So I have generated 8 digit integer on the local server. Then using 2 stored procedures inserted data to the live server. And updated the customer id's to 6 digit random unique number on the first table. On that updation I have called a trigger to update the values to the second table.

Is this method correct.. or any other good solution's?


texts: How to add a user with access to a single view?

texts:
I'm working with MSSQL Server Management Studio 2008 and I need to expose a view to a third party for their data reconciliation. I have created the appropriate view but I'm having trouble creating a user and giving that user appropriate permissions to select from the view.

I followed the wizards for creating a login and a user and then added my view in the Securables section with the grant box checked for select. Everything seemed fine but when I logged in as that user and tried to do a "Select * from MyViewName" it told me that the select permission was denied.

I just recreated the user (this time just using SQL instead of the wizard) and explicitly granted select permissions and now it is giving me the error: Msg 916, Level 14, State 1, Line 2
The server principal "username" is not able to access the database "unrelated_db" under the current security context. (I don't know why it's trying to access the unrelated database...)

I really don't know where to go from here. Again, basically all I need is to create a user that I can give to the third party to have them connect to our database and select from this view.


texts: How to use IN to compare a single value with a comma separated field in another column

texts:
I have a table that looks like this. let's call it table1:

|attributeMask |
|--------------|
| ,300,302,400 |
| ,301,500     |
| ,900,876     |

These are varchar values.

Then I have another table2:

 columnNumber  |
|------|
| 300  |
| 500  |
| 900  |

These ones are int.

What I need is a query to compare columnNumber with values inside attributeMask.

For example, I need to find inside attributeMask, rows that has 300 (for each row. the second columnNumber is 500, and I will compare this 500 with the respective row from it's own attributeMask that can be ,299,34,500). I'm trying to use FOR XML PATH or other functions but I have no idea what to do.

This is the entire query that i could create until now:

select  top 3
    t1.[Name]
   ,t1.ColumnNumber
   ,t3.AttributeMask
   ,t3.changeData
   ,t3.CreatedOn
   ,t3.ObjectTypeCode
   ,t3.UserId
from metadataschema.attribute     t1
inner join  metadataschema.Entity t2
    on t1.EntityId=t2.EntityId
inner join AuditBase              t3
    on t2.ObjectTypeCode=t3.ObjectTypeCode
where t2.ObjectTypeCode=1
//this is wrong. but is this part that I need to fix
and convert(varchar,t1.ColumnNumber) in (select t3.AttributeMask from
AuditBase)
//until here
and t3.CreatedOn BETWEEN '20190902' AND '20190913'
and t1.[Name] in
(
'address1_postalcode','smart_caixapostal','smart_tipologradouro','smart_complementoprincipal','smart_estado','smart_logradouroprincipal','smart_bairroprincipal',
'new_regiao','smart_numero','address1_city','smart_pais','smart_validarendereco','smart_cepcobranca','smart_caixacobranca','smart_validarendereco_cob',
'smart_tipologradourocobranca','smart_logradourocobranca','smart_numerocobranca','smart_complementocobranca','smart_bairrocobranca','smart_municipiocobranca',
'smart_estadocobranca','territoryid','smart_paiscobranca','smart_cepmkt','smart_logradouromkt','smart_complementomkt','smart_ufmkt','smart_tipodelogradouromkt',
'smart_bairromkt','smart_ddidddmkt','smart_caixapostalmkt','smart_numeromkt','smart_municipiomkt','smart_telefonemkt'
)


texts:
When I add a new database to our dev server (using SSMS), it automatically creates 5 tables.

These are copies of application tables that already exist in another database.

I assume this is some recent SQL Server feature I don't know about where tables can be marked as 'common' or some such, and so get added automatically to new databases.

But how can I stop it happening?

For the avoidance of confusion, this database is not scripted, it is created directly from the SSMS GUI (right-click Databases > New Database)

(moved from StackOverflow)


texts: TokuDB database size unknown in phpmyadmin

texts: In mysql server, I installed the TokuDB storage engine. I converted database engine InnoDB to TokuDB, but my table sizes show unknown in phpmyadmin.


texts: Delete rows on a table with cascading foreign keys

texts:
I want to delete rows on Table A but it has foreign keys on Table B and Table D. And Table B has foreign keys in Table E and Table D has foreign key in table F. Can I delete all the cascading records from Table A to F in a simple script without making multiple delete scripts per table? In reality, Table A has around 20 table referencing to it.
Table A

Table B

Table E

Table C
Table D

Table F

PostgreSQL version: 9.2.24


texts: Oracle 10g - Sudden change in execution time for specific query

texts:
After the DBAs fiddled with some parameters in our Oracle 10g Production Environment, most queries became a little faster. But a single query (a quite complex one) became dramaticaly slower - it went from 4 min per execution to rougly 2 hours per execution when ran from the application server (Jboss). And the question was active in the DB during all that time, so it wasn't just the jboss. I never mananged to explain why this happened, because when I copied it from active session, bound the variables to the data logged in the application, it ran on 4min directly in the DB. The wait events were mostly scattered and sequential reads, no weird stuff. My best guess is typecasting issues in some form resulting in a different execution plan than the one i got directly in the DB.

Then, all of a sudden, it stopped being an issue. After rougly 1,5days, it's back on it's normal execution times. The maintainance crew says they did nothing. The DBAs says they did nothing. It was rougly 12h off from the latest statistics being gathered, and the server wasn't under alot of strain at the time so shouldn't be resource bound.

Is there any factor that can make Oracle suddenly change execution plans? Have anyone seen anything like this before? The SQL contains a view - is there any way I can see when the structure of that view was last changed? Does anyone have any other tips on how to find out what really happened?


texts:
I have a table that looks like this (oversimplified case of the actual case to make my point)

CREATE TABLE sales (
   id int,
   currency VARCHAR(5),
   price int
   shop_id int
);

Now I need to make a query that will give me something like this table, grouping by shops yet giving sum of sales per currency
My problem is when I make currency column non aggregate it fails because my query becomes non standard. When I add currency to group by then each currency creates new row, something I don't want.
I have run out of option and would appreciate any help or pointer to the right direction.

I use MySQL but would appreciate if I would get the solution that works with more databases (PostGreSQL being at the top of the list after MySQL)

Query that works with undesired results

SELECT   shop_id,
         (CASE WHEN currency= "GBP" THEN SUM(price) ELSE 0 END) AS POUND,
         (CASE WHEN currency= "USD" THEN SUM(price) ELSE 0 END) AS USDOLLAR
GROUP BY shop_id, currency

The data that should be produced by the query

shop_id     |   POUND   |   USDOLLAR
-----------------------------------------
    1       |   400     |   300
-----------------------------------------
    2       |   250     |   100
-----------------------------------------
    3       |   400     |   100
-----------------------------------------


texts: Database replication with ability to revert to previous states

texts:
I want to setup a development database such that it'll initially be a mirror of the database used in production.  At fixed points during the day (maybe midnight or something) and/or manually, I want it to be sync'd once again with the production DB.

My goal is to have a database I can play and mess with, with no lasting consequences.  I've looked at replication, and it doesn't seem to be a good fit for this scenario.  See, what I thought initially is that I could set up a slave, dump it, do my work, then use the dump to revert it to its previous (pristine?) state, before syncing with the master.  Repeat when needed.  While I might be able to get away with this with relatively small DBs, 8 gigs worth of dumps tend to take a while to be generated and imported.  Plus I'd like to be able to have something that will scale to several devs.

Any hints as to where I should look next?


texts:
We have a two SQL Server instances that use merge replication to keep in sync.

There is a single table that potentially can add millions of rows in between syncronisations (which is a relative small interval).

This table has a compound primary key (which is also the clustered index).

Replication is currently unable to merge changes between the two database servers (the query times out).

Is there any potential pitfalls with merge replication in this scenario? Is there a performance problem related to the database schema?


texts:
We thinking about switching our database structure to a more modern schema. We hope to reduce server performance peaks and gain more performance.

This for I read some concepts about general so called bigdata, OLTP and Natively Compiled Stored Procedures.

Our existing concept for data tables is the "normal" one like referencing data by foreign key to a device.

Like:
device data -> FK -> device

In many IoT/bigdata concept I see this foreign key is missed. The DeviceID is stored but there are no constraints.

For example: https://github.com/Microsoft/sql-server-samples/tree/master/samples/applications/iot-smart-grid

Is this normal or did I missed something?


texts:
Why the error when I run the below in pgAdmin 4 query editor?  I copied the create statement in one database and trying to move to a new server.

So I right clicked the function in pgAdmin and selected from the menu Scripts --> CREATE Script the generate the below code.  Copied and executed into a new database.

-- FUNCTION: public.osmtrip_format(bigint, numeric, numeric)

-- DROP FUNCTION public.osmtrip_format(bigint, numeric, numeric);

CREATE OR REPLACE FUNCTION public.osmtrip_format(
    num bigint,
    length numeric,
    tolerance numeric)
RETURNS bigint
    LANGUAGE 'sql'
    COST 100
    IMMUTABLE
    ROWS 0
AS $BODY$

    SELECT
        CASE WHEN $1=0 OR $2<=0.7*$3 THEN NULL ELSE $1 END;

$BODY$;

ALTER FUNCTION public.osmtrip_format(bigint, numeric, numeric)
    OWNER TO gis;

ERROR:  ROWS must be positive
SQL state: 22023


texts: How to get query execution time and query result at the same time?

texts: Is there any SQL query syntax that returns query execution time and query result at the same time?


texts: Availability groups using multiple subnets changes

texts:
I have a question about using multiple subnets in a AlwaysOn Availability group. We have a AlwaysOn setup (SQL 2012) already in production that has a listener that is configured for 2 subnets. 1 of these is local and the other is used for DR. Obviously, we want to avoid anything being routed through the DR subnet on everyday use. We had an issue where when we first did this that we experienced timeouts. The ways to overcome this is well-documented and we have implemented fixes and looking at some of the alternate ways.

Primarily, we are using “MultiSubnetFailover=True” in our connection strings, but we are looking at modifying the RegisterAllProviderIP cluster resource setting to 0 due to some limitations on the MultiSubnetFailover parameter to handle this and then just working around the slower cutover time as a result. The only other subnet we are using is DR so I think this is manageable.

My question though is if AlwaysOn changed the way connections are made in any of the newer versions of SQL (or .NET Framework) that prevents the need to do some of this. We are currently planning to upgrade the coming year to SQL 2016 and at the same time move to AlwaysOn AG. Is “MultiSubnetFailover=True” or changing the RegisterAllProviderIP still required in SQL 2016 or is any of this handled automatically\different?

Most of the information regarding this is specific to SQL 2012 and I cannot seem to find information that definitively says if this applies for all versions. I did find some articles that mentioned this “MultiSubnetFailover=True” is handled automatically, but not sure if that was just application specific or product specific. Thanks.


texts: Creating and editing my.cnf by MySQL Workbench?

texts:
I just installed MySQL Server and MySQL Workbench on a clean Snow Leopard MacBook. I hooked up Workbench with the server and was able to start the MySQL Server from within the Workbench. I enter the Administration menue of MySQL Workbench it tells me there is no /etc/my.cnf. And if I click on Option File on the left side in the Workbench it tells me, that it will generate a my.cnf file after hitting Apply.

Now I see many configuration possibilitys. Is there a documentation for that? The best I could find was this MySQL-Dev-Page, but it does not help much.

Even better would be an explanatory default file, which I could adapt, since I am no expert with MySQL and I only want to use python sqlalchemy and django with the MySQL Server as localhost.

Every hint is appreciated.


texts: SQL Server Stored procedures run very slowly after a while

texts:
We have an ASP.Net application with a report page that runs several stored procedures. We have a problem with a production system where the report runs so slowly that it times out when the user selects a longer date range.

When the slow down occurs, we are able to restore functionality by running EXEC sp_recompile 'CrucialTable' on a table that is touched by all of the stored procedures, and then immediately running the report for a long date range. This fixes the problem for a while, but it slowly creeps back.

My guess was a bad execution plan so I added WITH RECOMPILE to the stored procedures, but sadly it didn't fix the problem.

We are using SQL Azure if that makes any difference.

UPDATE

I discovered a couple of SPs that were still missing the recompile options so that has now been added and now we have a new problem. The long date range reports run great (under 4 seconds which is fine given the amount of crunching that is taking place), but oddly now the short range reports are running horribly. For example a report that covers 2 years worth of transactions takes about 3.5 seconds, but 2 weeks takes 20 seconds!

UPDATE 2

I've managed to replicate the problem on a dev machine and have a look at the execution plan. I've tried a raft of option combinations like OPTION(RECOMPILE) and OPTIMIZE but as it turns out the query itself runs quite fast.

The problem is that

  The query had to wait xx seconds for MemoryGrant during execution

xx ranged from 20 to 90 seconds depending on the run.

That struck me as a but unusual because there were no other queries running at the time and the dev machine has plenty of ram. The execution plan also suggested a missing index so I'm going to try and add that and see if it helps.

UPDATE 3

I've experimented with a number of recompile/optimize options but the solution still eludes me.

Basically the stored procedures all have recompile options on them now, and in test everything works great even with big data (queries take less than a second). My initial problem of MemoryGrant has stopped, which I think was just because at the time the machine it was running on had a lot of applications open and was a little low on free ram.

In production we now have all SPs with the same recompile options on them, but unfortunately the short range queries continue to be incredibly slow. I've run the same SPs using SSMS to the production DB (with ARITHABORT set to OFF to simulate a similar connection) and they run virtually instantaneously regardless of a long or short range.

When running the short range report from the application in production there is an entry in the sys.dm_exec_query_memory_grants table for about 12 seconds. The full report then takes another 14+ seconds to complete after that entry has disappeared.


texts:
I am posting here on the forum for the first time... Please feel free to point me to the correct section of the website, if "DBA" is the wrong one.

So I work as a Business Intelligence Consultant, but my current project role focuses more on various database development tasks. One of these (it is more of a housekeeping task) is removing all restore points from the database that are older than 30 days.

I am not very familiar with databases in general.

Could someone point me in the right direction?
Is there some way to achieve this using PLSQL/SQL?
For example, a loop that runs every day at a specific time and gets rid of too old restore points in the DB?


texts: Compare 2 columns in 2 different tables with non uniques values

texts: I want to compare and check whether the values in columnA of table A exist in column A of Table B. But the columnA consist of duplicate values in both the table.


texts: Benefit to keeping a record in the database, rather than deleting it, for performance issues?

texts:
So I have a client that I am building a Rails app for....I am using PostgreSQL.

He made this comment about preferring to hide records, rather than delete them, and given that this is the first time I have heard about this I figured I would ask you guys to hear your thoughts.

  I'd rather hide than delete because deletions in tables eventually lead to table index havoc that causes queries to take longer than expected (much worse than Inserts or Updates).  This won't be a problem in the beginning of the site (it gets exponentially worse over time), but seems like an easy issue to never encounter by just not deleting anything (yet) as part of the "everyday" web application functionality.  We can always handle deletions much later as part of a Data Optimization & Maintenance process and re-index tables in that process on some (yet to be determined) scheduled basis.

In all the Rails apps I have built, I have never had an issue with records being deleted and it affecting the index.

Am I missing something? Is this a problem that used to exist, but modern RDBMS products have fixed it?


texts: Create/copy new superuser from an existing one

texts:
I have a superuser for a PostgreSQL database, let's call him userX.

How can I make a new superuser (let's call him userY) with all the same permissions, access, etc...?

Basically, I want a clone of everything about userX to userY and keep both users.


texts: Is there an efficient way to use 3rd party packages in PL/Python?

texts:
I've found examples of people importing 3rd party packages into PL/Python scripts, but it seems to me that re-importing libraries upon every run of a stored procedure is terribly inefficient.

Does Postgres maintain a process-wide python interpreter that's re-used for subsequent PL/Python script executions?

If so, is there any way to have it execute import statements in that context BEFORE a PL/Python procedure runs?

Alternatively, would it be possible/practical to provide a C-compatible dynamically-linked library that hosts a persistent Python interpreter?


texts:
How would I use the pre-installed SQLDeveloper that comes packaged within Oracle 11g if I am invoking it with a user that has no oracle or admin rights on linux?

Would it be enough just to change permissions to the sqldeveloper folder, just as shown below:

chmod -R a+x sqldeloper/

Or it would be better to install SQLdeveloper with the "user" that has not oracle or admim rights?


texts: How to deny drop permission for a table?

texts: Is there a way to deny drop permissions for a specific table from a user or role?


texts: “Column does not exist” with INNER JOIN and MAX query in ORDER BY

texts:
I'm using PostgreSQL 9.6 with ActiveRecord. I'm trying to get all customers sorted by a specific provider. The relationship between models is: customer has many orders, which belong to a provider.

What I want to achieve is something like:

Example data:

customer1 <- order1 -> provider1
customer2 <- order2 -> provider2
customer3 <- order3 -> provider3

Expecting result:

query_for_provider(2) => customer2, customer1, customer3
query_for_provider(1) => customer1, customer2, customer3
query_for_provider(3) => customer3, customer1, customer2

I wrote the following query:

SELECT "customers".*, MAX(providers.id) AS provider_id FROM "customers"
INNER JOIN "orders" ON "orders"."patient_id" = "customers"."id"
INNER JOIN "providers" ON "providers"."id" = "orders"."provider_id"
GROUP BY customers.id
ORDER BY @(2 - provider_id);

Unfortunately, it returns an error:

column "provider_id" doesn't exist

When I wrap this with SELECT * FROM (as below) it works, but in this case, can't use this with ActiveRecord, which requires SELECT ... FROM customers construction.

SELECT * FROM (
  SELECT "customers".*, MAX(providers.id) AS provider_id FROM "customers"
  INNER JOIN "orders" ON "orders"."patient_id" = "customers"."id"
  INNER JOIN "providers" ON "providers"."id" = "orders"."provider_id"
  GROUP BY customers.id
) AS customers_with_provider
ORDER BY @(2 - provider_id);

Any thoughts how to achieve this?


texts: Can I extrapolate from the progress reporting in MyISAM ALTER?

texts:
I'm migrating a 300 million row MyISAM table to add some indexes.

Just because it might be relevant, here's my ALTER statement:

ALTER TABLE my_table
    ADD UNIQUE INDEX my_table_abcd (a, b, c, d),
    ADD INDEX my_table_abc (a, b, c),
    ADD INDEX my_table_c (c),
    ADD INDEX my_table_ce (c, e),
    ADD INDEX my_table_d (a);

It currently says

Stage: 1 of 2 'copy to tmp table'   74.8% of stage done

and the processlist says that the progress of the ALTER statement is 37.440.

I don't remember the exact timings, but the 'Stage 1' value jumped up to 60% in the first hour or so, and has been I crawling up to 70% in the last 12 hours. In the lat 3 hours it has increased 1.5%.

I have a decision to make about whether I abort this and try changing some configuration settings or let it run. It's not linear based on previous observations. If it's slowing down exponentially then it's a waste to let it continue when I could be trying another approach.

Any ideas for bases on which to make a decision?

NB this question is relevant but not a duplicate. I'm not asking about predicting the time, I'm asking about whether the progress report has a linear relationship with time.


texts: AutoIncrement non-primary key or Make unique composite key?

texts:
I have a db with a table that has a rollover value such as OrderNumber and another column is year.

This is the primary key - a composite key - but there are other tables with which I need to use a foreign key relationship - I really would prefer to have an auto increment surrogate key for this table, the trick though is that OrderNumber and Year must be unique. IS there a good way to handle this?

I was reading here for some inspirations. Composite field in SQL Server, does it exist?


texts: Converting MySQL master-master replication to master-standby

texts:
I set up two mysql servers in a master-master configuration.  This was working fine until recently when I encountered a duplicate-key problem.  After doing some research and evaluating my needs, I decided that master-master replication was unnecessary and was more likely to cause problems than a master-standby configuration with automatic failover.
Current State

one master(db1) is up-to-date and still accepting writes.
the other master(db2) is no longer replicating, and is three days out of date.

Goals

convert db2 to a standby without interrupting db1 for more than a few moments.
Set up automatic failover.  Is mysqlfailover recommended?  I could also monitor the master with zabbix and trigger the failover process from zabbix.

EDIT 2015-05-18 20:28:13 PDT
I decided to stick with the master-master configuration.  Setting auto_increment_increment and auto_increment_offset eliminated the duplicate entry errors on the auto_increment PRIMARY_KEY, but I still do worry about UNIQUE_KEY duplicate entry issues as well as inconsistencies due to UPDATEs


texts:
My table structure is below:

CREATE TABLE [ACC].[Document](
    [DocumentID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NOT NULL,
    [SalesCompanyFinancialPeriodID] [int] NOT NULL,
    [DocumentTypeID] [int] NULL,
    [Number] [int] NULL,
    [Title] [nvarchar](200) NULL,
    [ConfirmStatusEnumID] [int] NULL,
    [SignedPrice] [money] NOT NULL,
 CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED
(
    [DocumentID] ASC
)

I want to set Number column value automaticaly increment number started from 1 for each SalesCompanyFinancialPeriodID column. In other word if new record inserted in this table, value of Number column calculated in instead of trigger, such as below:

 ISNULL( (Select Max(A.Number)
          From Acc.Document A
          Where A.SalesCompanyFinancialPeriodID = Inserted.SalesCompanyFinancialPeriodID),1)

What is the best practices for this problem.


texts:
Am trying to run the following dblink start transaction;

select dblink_connect('host=remote port=5432 dbname=remote_db user=user password=pass');
insert into local_table (column1, column2 )
select * from dblink(`
    select remote_column1 ltrim(remote_column2, ''TEST'') from remote_table
       where remote_column2 ilike ''TEST%''
) as t(column1 varchar, column2 varchar) ;

now the above works fine, but am encountering a challenge:- I want instead of hardcoding TEST, I want it to be queried from a local table so I would wish to substitute TEST to something like SELECT uniq_id from some_local_table The logic behind it is I want to get records which has prefix matching the result of SELECT uniq_id from some_local_table but trim the prefix when inserting to local_table hope someone understands me


texts: Retrieve game details that their level's value is higher than 1/10 the sum of guilds levels

texts:
I've a database which contains tables:

Quest:

PK  Name
PK  qName
    goal

Guild:

PK  Name
PK  gName
    level

Game:

PK  Name
    MaxPlayer
    levels
    uName

GuildQuest:

PK  Name
PK  gName
PK  qName

I need to retrieve  the game details that their level's value is higher than 1/10 the sum of guilds levels

This is what I've build so far:

SELECT
  Name, MaxPlayer, levels, uName
  SUM(level) AS guildsLevels
FROM
  Guild
GROUP BY
  level

I need to add the multiplication by 0.10 of the sum which I don't know how to do, and then check every game that its value is bigger than sum of guild levels multiplied by 10%.

I'm using MySQL (and PhpMyAdmin to run the queries).


texts: How to implement a dynamic string into a (prepared) sql statement?

texts:
I have some spatial datasets which are available in a open source metadata catalog software GeoNetwork and in a PostGIS database. The problem is that the student who created the database used different IDs for the same datasets. So the IDs in the GeoNetwork differ from the IDs in the PostGIS database. They only have the same name.

If someone uploads a new dataset into GeoNetwork, the same dataset is uploaded into a PostGis database, too. I want to add a ID column into the database to give the field the same ID like the ID in GeoNetwork. I think I can only do this with using the filename cause they are the same in both cases.

I want to alter the table in eclipse with the following code:

  Connection con = null;
            PreparedStatement ps = null;
            final String sqlps = "ALTER TABLE ? ADD COLUMN ?";

            String filen = filename.substring(0, filename.indexOf('.'));
            try {
                con = DriverManager.getConnection("jdbc:postgresql_postGIS://localhost:5433/geonetwork?user=postgres&password=dominik1");
                try {
                    ps = con.prepareStatement(sqlps);
                    ps.setString(1, filen);
                    ps.setString(2, "GN_ID");

                    ps.execute();

But I get an error:

Exception:org.postgresql.util.PSQLException: ERROR: syntax error at »$1«
  Position: 13

It seems that Postgres has problems with the ? in the prepared statement.
Does anyone know a solution for this problem?

I also tried using a normal statement, but this is giving me a findbug error that I try to pass a non-constant string to an execute method on an sql statement. So I really don't know how to get rid of this problem.


texts:
I'm trying to design an ER diagram in the relational database module of my course. We were given a scenario to draw a diagram for and then create a database within Access. I have the database covered, but want to be sure I'm on the right track for the diagram.
Scenario:

The model is for business functions within a car dealership. Minimum
and maximum cardinalities must be indicated. Entities and processes to
be considered include Customer; Cash Received; Cash; Salesperson; AR
Clerk; Sell Vehicle; Inventory.
A few assumptions must be made about resources and the payment event. Assume the company only sells inventory that is in stock, thus
customers will not be ordering vehicles and every sale is only for one
vehicle. Inventory is categorized based on model information.
Additionally, assume that the dealership has multiple cash accounts
and that customer payments are always deposited in the same cash
account. Since the dealership offers financing, customers can make
monthly payments on their vehicles so customers can buy now and pay in
installments later. Any other assumptions that need to be made should
be consistent with the business processes in a typical auto
dealership.

My answer (I don't believe I have a firm grasp on this at all -- I also don't have the ability to embed pictures yet):
https://i.stack.imgur.com/iZrv9.png
Thank you in advance for your help!!


texts: Can SQL Server R Services be enabled on a readable secondary?

texts:
To avoid excess load on the primary SQL Server, I'd like to install and enable R Services on a secondary replica of the primary server. Is this possible?

Also, can I push computations from a remote R client onto the secondary server with R Services enabled?

Does this have any impact on the primary at all?


texts: Can't recover old master Postgresql using pg_rewind

texts:
I have 2 Postgresql-12 DB, db01 and db02. we synchronise master db01 with slave db02 using wal shipping logs.
We had issue on filesystem of db01 and we hade te restaure the VM image. The issue happened on say Day: D0.
The image recovered for VM was for D0-2 at 22:00. The issue happend on D0 at 15:00 and we promote slave db02 as master.
Now when we try to recover the master db01 using pg_rewind, it says missing wal file.
Is there a solution without using pg_rewind.
BR
PS: I'm new to DBs


texts:
We have a multi-subnet AlwaysOn availability group. There are 2 subnets and those are listed on the cluster and also the listener.  Whenever I fail over and ping by the listener name it still shows the old IP address before failover. Any expert opinion to get over this will be greatly appreciated.
I have set the RegisterAllProvidersIP -Value 1.

After failover the client does not connect as the listener is picking up the old IP address.  After a couple of minutes it picks up the correct one and the client connects.


texts:
I have problem with my SQL Server 2008 R2 instance. I want to log in using windows authentication and server name "local", but error 2 is returned. I've tried to login with a server name of "IP address of my server" but again it failed. Does anybody know what should I do?

Error message:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)


texts:
I have a little misunderstanding when I make SQL Server full database backup. How does exactly full backup work? What data does it backup?

Here is a case: I scheduled full database backup supposing at 12.00 it takes about 15 minutes, so it finishes at 12.15, But at 11.58 one transaction has begun and finished at 12.05.

So my question is: Will my full backup contain this transaction or now? And why?


texts: Mysql database in a symlink-ed directory gives ERROR 1005 (errorno: 13)

texts:
I've the need to put my database outside the default datadir of mysql.

To achieve that I followed the symlink guidance in the mysql documentation.

However when I use the db if I try to create a table I'm greeted with:

  ERROR 1005 (HY000): Can't create table 'test' (errno: 13)

In my search for solutions I already read Can I use symlink or data directory for ARCHIVE MySQL tables? and this answer which didn't apply for my context.

At the moment my data dir looks like this with ls -Ll

server:/var/lib/mysql# ls -Ll
total 28800
-rw-r--r-- 1 root  root         0 Jan 26 00:37 debian-5.5.flag
-rw-rw---- 1 mysql mysql    25088 Feb  8  2005 ib_arch_log_0000000000
-rw-rw---- 1 mysql mysql 18874368 May 31 15:21 ibdata1
-rw-rw---- 1 mysql mysql  5242880 May 31 15:21 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Feb  8  2005 ib_logfile1
drwx------ 2 mysql mysql     4096 Jan 26 00:37 mysql
drwxr-xr-x 1 root  root         0 May 29 00:01 so
drwx------ 2 mysql mysql     4096 May 28 22:48 so2
drwxr-xr-x 2 mysql mysql      4096 May 31 14:34 symlink
drwxr-xr-x 2 mysql mysql     4096 Apr 20  2013 test

The two symlinks are created with the command

ln -s /mnt/nas/se/data/mysql/ so

I chown the symlinks to mysql:mysql as well as in the directory it self. For the symlink directory that worked (that is on an ext3 fs) but for the so one (that is on a nas over CIFS/SMB) I'm still out of luck.

I managed to enable NFS (ver 3) on the NAS (running BusyBox v1.1.1) and mounted that as well. I synced the UID for the mysql account on the server and the client. Unfortunately the result is the same for ls -Ll

drwxrwxr-x 2 root  rene      4096 May 31 16:51 sonfs

and for ls -l

lrwxrwxrwx 1 root  root         8 Jun  1 00:26 sonfs -> /mnt/nfs

I did su mysql and tried to cp a file to those symlinked directories and that worked which made me assume on the OS level the permissions are OK.

Within mysql I granted root all permissions to the so and the symlink database.

I'm not sure if I the problem I face is something I misconfigured at the Debian OS level or within mysql itself.

Can someone point out to me what diagnosis I need to do and which steps need to be taken to be able to create tables that get stored in those symlinked directories?


texts:
I'm trying the following sql code:

UPDATE ticketsorders
SET amount = $val
WHERE id = $ticketsorder->id
  AND ((SELECT COALESCE(SUM(amount), 0)
        FROM ticketsorders
        WHERE tickettype_id = $tickettype->id) + $val )
      <= (SELECT maxtickets
          from tickettypes
          where id = $tickettype->id)
  AND ((SELECT COALESCE(SUM(amount), 0)
        FROM ticketsorders
        WHERE event_id = $eventid) + $val )
      <= (SELECT maxtickets from events where id = $eventid)

$val = the number of tickets
there is a maximum number defined in maxtickets for an event and for a tickettype thats what the 2 ANDS are for. I have a similair insert statement which works.

But I keep getting:

  #1093 - You can't specify target table 'ticketorders' for update in FROM clause.

I have placed alot of as x as y etc to try and make it work like in examples https://stackoverflow.com/questions/8333376/you-cant-specify-target-table-table-name-for-update-in-from-clause

The wheres are there to make sure the update only succeeds when the new sum is below a set maxtickets on both event and tickettype.

Can anyone enlighten me?


texts: How do I have RMAN treat my restored data files like they are originals?

texts:
I am planning a migration for this ancient-but-critical Oracle database from one Windows Server 2003 cluster to another.

The database has many data files and they are all getting new paths on the destination cluster. The only paths that are staying the same are for the backup files.

The migration plan I have is as follows:

Backup the database on the source cluster using our nightly RMAN backup script:

run {
    allocate channel Channel1 type disk format 'F:\backup\folder\%d_%T_%s_%U.bak';
    allocate channel Channel2 ...

    backup check logical ( database );
    backup check logical ( archivelog all  not backed up 2 times );
    backup check logical ( current controlfile );
}

allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate - 2' backed up 2 times to disk;
delete obsolete device type disk;

Our RMAN retention policy on both clusters is set to redundancy 2, and the control file is being used as the recovery catalog.
Copy the backup files to F:\backup\folder on the destination cluster.
Restore the database on the destination cluster using this RMAN script:

run {
    allocate channel Channel1 type disk;
    allocate channel Channel2 ...

    set until sequence <sequence number> thread 1;

    SET NEWNAME FOR DATAFILE 'Old\Path\FILE01.DBF' TO 'New\Path\FILE01.DBF';
    SET NEWNAME FOR DATAFILE ...

    restore database;
}

Update the data file paths in the control file, recover the database to the appropriate sequence, and open it with resetlogs.

This works great with the following exception:

When I ask RMAN to delete obsolete files on the destination cluster, it names all of the just-restored data files as obsolete!

RMAN> delete obsolete device type disk;
...
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Datafile Copy        1      02-JUL-12          New\Path\FILE01.DBF
...

Well, RMAN is correct to recognize these data files as copies.

However, since this is a database migration, these copies are intended to be the new originals, so I certainly don't want RMAN to delete them as part of its maintenance!

Is it possible to tweak this migration process so that RMAN treats the restored database as the new original?


texts:
How can we best use our existing MySQL queries with SQLserver?

Alternately, what type of changes do we need when we are converting our database server from MySQL to SQLServer?


texts: Why does this logon trigger work in one server but not another?

texts:
I created the below trigger in the same manner in the SYS schema on two different Oracle 11g servers, without any errors.

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON DATABASE
BEGIN
    if (user in ('mytestro', 'mytestrw')) then
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = mytest';
    end if;
exception
  when others
    then null; -- prevent a login failure due to an exception
END logon_trg;
/

On one server I can tell it is working since I can select any table in mytest as soon as I logon with either account.  On the other server it says the table does not exist (ORA-00942).  If I issue ALTER SESSION SET CURRENT_SCHEMA = mytest; manually I can access the table, so it seems the trigger is not firing.


texts: Getting repeated value in a group_concat only if ids are different

texts:
I have two tables, first the table Product:

id|category_id
--+-----------
1 | 12345
2 | 12345
3 | 12465

And then a table activity:

id|prod_id|activity_type   |description
--+-------+----------------+-----------
1 | 1     | Initialization | blah
2 | 1     | Finalization   | foo
3 | 2     | Initialization | blah again
4 | 2     | Duplication    | bar
5 | 2     | Finalization   | foobar
6 | 3     | Initialization | blob
7 | 3     | Migration      | A to B
8 | 3     | Migration      | B to C
9 | 3     | Finalization   | fuh

Now I want to retrieve for each type of activity the number of product having at least one of this kind of activity, and also the list of product category. The categories will be repeated in the list for each product of this category. For now I'm using the following query:

SELECT a.activity_type as Activity, COUNT(DISTINCT p.id) as Products,
CONVERT(GROUP_CONCAT(p.category SEPARATOR ',  ') USING utf8) AS Categories
FROM mydb.product p, mydb.activity a
WHERE p.id = a.prod_id
AND a.activity_type <> '' // To not count activities which haven't been correctly initialized
GROUP BY Categories
ORDER BY Products

Now what I await for result is:

Activity       | Products | Categories
---------------+----------+--------------------
Initialization | 3        | 12345, 12345, 12465
Finalization   | 3        | 12345, 12345, 12465
Duplication    | 1        | 12345
Migration      | 1        | 12465

But with this query I get the value '12465, 12465' for Migration. I could I get that a category appears on the list, only for each different product ids, but not for each activity of one type?


texts: Can't read password from config using --defaults-extra-file in MySQL

texts:
I am trying to backup all databases automatically using a batch script. The password is read from a config file called config.cnf. Below is the batch script:

@echo off
C:\MySQL\bin\mysqldump.exe --defaults-extra-file=config.cnf -u root --all-databases > bak_%DATE%.sql 2> sql_error.log

Contents of config.cnf:

[mysqldump]
password = mypassword

When running the batch the following error messages are returned:

Could not open required defaults file: config.cnf
Fatal error in defaults handling. Program aborted
mysqldump: Got error: 1045: "Access denied for user 'root'@'127.0.0.1' (using password NO)" when trying to connect

For some reason, the config.cnf is not being read and the execution of the script fails. I am not sure why this is happening.

Edit: Version of the database is MySQL version 14.16 Distrib 5.2.4 MariaDB.
Running the batch script as Administrator still results in same error message, as does also modifying the config file, as well as modifying the group to [client]. Found an alternative solution, which is answered below.


texts: Optimize PostgreSQL query to avoid two time DB call for same operation

texts:
I am running following PostgreSQL query to support my service call for same filter criteria, any thoughts on how we can avoid these two DB calls for the same query and return JSON data in a single query

WITH t as (
SELECT row_to_json(z)::jsonb as record FROM
(
    SELECT  t1.A, t2.B, t1.C
 FROM Table1 t1
 JOIN Table2 t2 ON t1.id = t2.id) z)

 SELECT record FROM t
 {=filterParam} ORDER BY {=orderParam} LIMIT(250) OFFSET({=offsetParam});

WITH t as (
SELECT row_to_json(z)::jsonb as record FROM
(SELECT  t1.A, t2.B, t1.C
 FROM Table1 t1
 JOIN Table2 t2 ON t1.id = t2.id) z)

 SELECT COUNT(record) AS total_record_count From t
{=filterParam};

I am very beginner to PostgreSQL so all answers are appreciated


texts: Why do I have THREADPOOL waits even though I have threads available?

texts:
We are seeing application time outs and with help of the First Responder Kit, I have captured details. One of the things that caught my attention was:

Poison wait detected: Thread Pool

So I ran below queries:
---get thread pool waits---thread pool wait query
SELECT count(*) FROM sys.dm_os_waiting_tasks
 where wait_type ='threadpool'

-- get threads available--Threads available query
 declare @max int
select @max = max_workers_count from sys.dm_os_sys_info

select
    @max as 'TotalThreads',
    sum(active_Workers_count) as 'CurrentThreads',
    @max - sum(active_Workers_count) as 'AvailableThreads',
    sum(runnable_tasks_count) as 'WorkersWaitingForCpu',
    sum(work_queue_count) as 'RequestWaitingForThreads' ,
    sum(current_workers_count) as 'AssociatedWorkers',
    Getdate() as 'DateLogged'
from
    sys.dm_os_Schedulers where status='VISIBLE ONLINE'

When I run the "thread pool wait" query, thread pool waits are appearing in above result (sometimes, more than 20 results) for fraction of second and then disappearing when I run it again. But I could see more than 400 threads available  in second result set through the "threads available" query.
I am not able to understand why I am seeing THREADPOOL waits when I run the first query, but second query shows threads are still available. Can any one please explain?

Maxdop: 8
Max degree of parallelism: 50

Thread pool waits:


texts: How to use postgresql variable in where clause

texts:
# It works!
select * from device where device.user_id in (select user_id from user);

# It doesn't work!
select user_id into test from user;
select * from device where device.user_id in test;

Why does the first one work but second?

If I want to use a variable in where clause like the above, how can I do that?


texts: A form including content from two tables

texts:
I am working to create a database of all the places or administrative entities of one country.

Most work will be about adding new districts. I have to detail which region every district belongs to.

Two tables, one for 1/ regions 2/ districts.
Every region has its ID and Name.
Every district has a Name, and a column which refers to ID_Region (the one it belongs to).
The two tables are linked. ID_Region is primary key.

However, I am using a well-known open-source Database management system, (OpenOffice). It seems that it cannot allow me to create forms in which ID_Region could be automatically modified in Name_Region. This is all the more important since there are hundreds of regions.

Do you know if this is possible on any open-source DBMS?
What software should I use instead?

You probably got that I'm a database newbie. I do not know much about SQL.
Thanks for your replies. Feel free to redirect me to the right forum if I am on the wrong one.

Table 1: "Regions"

Column 1: idRegions
Column 2: RegionType (refers to another Table, not necessary here)
Column 3: Name
Column 4: Abbreviation

Table 2: "Districts"

Column 1: idDistricts
Column 2: Name
Column 3: Region (tied to Regions.idRegions)

I can create a form in which I can view and add Districts, but it seems like Districts.Region (an ID) can't "redirect" to Region.Name, which I would like to (no matter how: a drop-down list, or an additional text box).

Should this be possible, it would of course allow other types of join to show names and not id (such as RegionType, or any kind of join whatsoever).

You might think of making Region.Name the Primary key instead of using IDs, however, some Regions are called the same way (all the more when I will add the towns, many Towns' Districts are homonyms).


texts: SQL Server Plan cache size only 4GB on a server with 500GB RAM

texts:
I have a SQL server 2016 SP1 that has been up and running over a year and using the query below, I see that the plan cache size is approx only 4GB where as the physical server has 500GB and there is no max memory setting on the sql instance and there is only one sql instance on the server and no other application.

My understanding is based on how sql server sizes the plan cache using the formula below, the plan cache on my server should be atleast 30GB or more but sql server allocated only 4GB and i have a mix of SP's, sp_executesql and exec(sql) queries on the db. Can experts please help me understand why the plan cache would be so small. Thanks!

SQL Server 2005 SP2 and higher
     75% of visible target memory from 0-4GB
    + 10% of visible target memory from 4GB-64GB
    + 5% of visible target memory > 64GB

SELECT
        [objtype] AS [CacheType],
        COUNT_BIG(*) AS [Total Plans],
        SUM(CAST([size_in_bytes] AS DECIMAL(18,2)))/1024/1024 AS [Total MBs],
        AVG([usecounts]) AS [Avg Use Count],
        SUM(CAST((CASE WHEN [usecounts] = 1 THEN [size_in_bytes] ELSE 0 END)
        AS DECIMAL(18,2)))/1024/1024 AS [Total MBs – USE Count 1],
        SUM(CASE WHEN [usecounts] = 1 THEN 1 ELSE 0 END) AS [Total Plans – USE Count 1]
    FROM [sys].[dm_exec_cached_plans]
    GROUP BY [objtype]
    ORDER BY [Total MBs – USE Count 1] DESC;


texts: Problem with Processing Dimensions with Multiple Facts

texts:
I have a cube built in SSAS that uses 3 facts and 8 dimensions. Some of the dimensions are shared across 3 facts.

When I process one of the dimension, it tries to process the underlying measure groups, however, as the other dimensions are not yet updated with the new data, the measure group processing fails saying the key doesn't exist.

How should we trigger a dimension process in such a case?

Illustration:
3 Fact Tables C1, C2, C3;
8 Dimensions D1, D2, D3, D4, D5, D6, D7, D8

C1 is built on D1, D2, D3, D4
C2 is built on D1, D5, D6
C3 is built on D1, D2, D5, D7, D8

Assume that I have made updates in the Dimensions D1 and D5 and trigger a Dimension process one after the other using AMO.

When I process D1, it processes Dimension D1 and measure groups C1, C2 and C3. When it tries to process the measure group C2, it complains that the updated data in D5 is still not populated.
When I Process D5 first, it processes Dimension D5 and measure groups C2 and C3.
When it tries to process the measure group C3, it complains that the updated data in D1 is still not populated

Note:

It's SSAS 2012
We are processing dimensions using ProcessUpdate and
process using ProcessFull

Please do let me know if you need any additional information.


texts: SQL Server 2000 DTS package, filter rows using stored procedure returned date

texts:
I know very little about DTS packages in the first place (I'm a programmer not a dba) and have been stuck with the task of filtering rows based on the results of a stored procedure call that returns a date, where the desired result would be something like this:
" ( WHERE SpecialDate >= SPROC_Returned_Date )".

The stored procedure lives on a local DB server and the rows are all coming from elsewhere using a DB/C FS 3 driver connection. I'm having a very hard time figuring out how to connect the SQL query on the Transform Data Task that does the row retrieval to the date returning stored procedure.

Where should I even start?

Update:
I've been able to create a view that returns one row with the date I'm interested in from the stored procedure. However the DB/C FS 4 connection doesn't seem capable of interpreting anything involving the use of parameters. I'm at a loss here as it seems I'm just about 90% here but can find any useful information about this DB/C FS4 ODBC driver.


texts: Do MySQL can do automatic EXPLAIN on every query?

texts:
We only have two Junior DBAs, no Senior DBAs, no one to guide us. We need to review each and every query for almost 30 large projects.  Each query took between 5 minutes and two hours depending on the query. As a reviewer, it was a real drag on my time.
My question is, do MySQL can do automatic EXPLAIN on each query, without having to run EXPLAIN by hand and log the result of un-optimized queries? Or maybe by cronjob? If possible, how to do this?


texts: Connection to localhost dramatically slower than to network server

texts:
I have an Excel VBA application that connects to a SQL Server 2008 R2 database through ADODB.

When I'm on the network at work, the connection is almost instantaneous. When I am at home working with a local copy of the database, the connection takes about 5 seconds. At first I thought the performance hit had to do with the queries, and even posted a question on Stackoverflow with that incorrect assumption: https://stackoverflow.com/q/10467876/138938

The connection is just as slow locally whether I specify "localhost" or a ".". It seems like my code is having a hard time resolving where exactly my localhost is. Is there something I should do with my hosts file or some other setting that I can modify to speed up my connection on my local machine?

What other thing should I look at to troubleshoot?

Thanks!


texts:
Trying to remove an extra database file from a database using EMPTYFILE on SQL Server 2008 R2.

use [MYDATABASE]
        DBCC SHRINKFILE (MY_DATABASE_7, EMPTYFILE);
        use master
        ALTER DATABASE [MY_DATABASE]
        REMOVE FILE MY_DATABASE_7;

And getting this error

  Msg 2555, Level 16, State 1, Line 2  Cannot move all contents of file
  "MY_DATABASE_7" to other places to complete the emptyfile operation.

The other database files aren't limited in growth in anyway.
There is more than enough disk space

And there is practically nothing left in the 7th file.

So there is no reason why I can't remove this 7th file.

Any thoughts as to why SQL Server is rejecting the EMPTYFILE ?


texts: IS NOT DISTINCT FROM vs row-wise equality with =

texts:
Looking at the below query,

SELECT null IS NOT DISTINCT FROM null AS indf,
  null = null AS eq;
 indf | eq
------+----
 t    |

From this we can see the result of the IS NOT DISTINCT FROM is true, and the result of the eq is false. The following then logically follows from that,

-- 1 rows
SELECT *
FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON t.a IS NOT DISTINCT FROM g.a;

-- 0 rows
SELECT *
FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON t.a = g.a;

Because if the condition returns null the join fails. But, this throws me off, with row-wise comparison

-- returns 1 row.
SELECT *
FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON (t) IS NOT DISTINCT FROM (g);

-- also returns one row.
SELECT * FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON (t) = (g);

Why does row-wise comparison treat nulls different than scalar comparison? And is there a point of IS NOT DISTINCT FROM in row-wise comparison?


texts: Mapping of multiple server logins to one database user

texts:
This seems to be a stupid question but despite some research I was unable to find any information regarding this only (possibly due to using the wrong terminology).

Is it possible to log multiple server logins (sql server authentication) to a single database user (which has permissions assigned by being member of a database role)?

I have dozens of sql logins (sql server authentication) which need to read one setting from a central database and I'd rather map all these logins to a single DB user in the target database than create an own DB user for each login.

If yes, what would be the correct T-SQL syntax?


texts: How to improve performance of SP with many optional filtering parameters?

texts:
I need to write many SP that have many optional filtering parameters. After reading Turgay Sahtiyan's (Senior SQL Server Premier Field Engineer at Microsoft) blog he mentions exactly the same problem and the way to solve it is through dynamic SQL. Found it in his slides http://blogs.msdn.com/b/turgays/archive/2013/09/07/sqlsaturday-243-cape-town-top-10-performance-tips-for-sql-server-developers.aspx

So my questions is it the only and the best approach or are there other options?

WHERE (@EmployeeFirstName IS NULL OR emp.First_Name LIKE @EmployeeFirstName )
AND (@EmployeeLastName IS NULL OR emp.Last_Name LIKE @EmployeeLastName )
AND (@EmployeeNetworkLogOn IS NULL OR emp.Network_ID LIKE @EmployeeNetworkLogOn )
AND (@EmployeeStatus IS NULL OR emp.Employee_Status = @EmployeeStatus )
AND (@EmployeeType IS NULL OR emp.Employee_Type = @EmployeeType )
AND (@SupervisorFirstName IS NULL OR sup.First_Name LIKE @SupervisorFirstName )
AND (@SupervisorLastName IS NULL OR sup.Last_Name LIKE @SupervisorLastName )
AND (@SupervisorNetworkLogOn IS NULL OR sup.Network_ID LIKE @SupervisorNetworkLogOn)


texts: Why is selecting all resulting columns of this query faster than selecting the one column I care about?

texts:
I have a query where using select * not only does far fewer reads, but also uses significantly less CPU time than using select c.Foo.

This is the query:

select top 1000 c.ID
from ATable a
    join BTable b on b.OrderKey = a.OrderKey and b.ClientId = a.ClientId
    join CTable c on c.OrderId = b.OrderId and c.ShipKey = a.ShipKey
where (a.NextAnalysisDate is null or a.NextAnalysisDate < @dateCutOff)
    and b.IsVoided = 0
    and c.ComplianceStatus in (3, 5)
    and c.ShipmentStatus in (1, 5, 6)
order by a.LastAnalyzedDate

This finished with 2,473,658 logical reads, mostly in Table B.  It used 26,562 CPU and had a duration of 7,965.

This is the query plan generated:

On PasteThePlan: https://www.brentozar.com/pastetheplan/?id=BJAp2mQIQ

When I change c.ID to *, the query finished with 107,049 logical reads, fairly evenly spread between all three tables.  It used 4,266 CPU and had a duration of 1,147.

This is the query plan generated:

On PasteThePlan: https://www.brentozar.com/pastetheplan/?id=SyZYn7QUQ

I attempted to use the query hints suggested by Joe Obbish, with these results:
select c.ID without hint: https://www.brentozar.com/pastetheplan/?id=SJfBdOELm
select c.ID with hint: https://www.brentozar.com/pastetheplan/?id=B1W___N87
select * without hint: https://www.brentozar.com/pastetheplan/?id=HJ6qddEIm
select * with hint: https://www.brentozar.com/pastetheplan/?id=rJhhudNIQ

Using the OPTION(LOOP JOIN) hint with select c.ID did drastically reduced the number of reads compared to the version without the hint, but it is still doing about 4x the number of reads the select * query without any hints.  Adding OPTION(RECOMPILE, HASH JOIN) to the select * query made it perform much worse than anything else I have tried.

After updating statistics on the tables and their indexes using WITH FULLSCAN, the select c.ID query is running much faster:
select c.ID before update: https://www.brentozar.com/pastetheplan/?id=SkiYoOEUm
select * before update: https://www.brentozar.com/pastetheplan/?id=ryrvodEUX
select c.ID after update: https://www.brentozar.com/pastetheplan/?id=B1MRoO487
select * after update: https://www.brentozar.com/pastetheplan/?id=Hk7si_V8m

select * still outperforms select c.ID in terms of total duration and total reads (select * has about half the reads) but it does use more CPU.  Overall they are much much closer than before the update, however the plans still differ.

The same behavior is seen on 2016 running in 2014 Compatibility mode and on 2014.  What could explain the disparity between the two plans?  Could it be that the "correct" indexes have not been created?  Could statistics being slightly out of date cause this?

I tried moving the predicates up to the ON part of the join, in multiple ways, but the query plan is the same each time.

After Index Rebuilds

I rebuilt all of the indexes on the three tables involved in the query.  c.ID is still doing the most reads (over twice as many as *), but CPU usage is about half of the * version.  The c.ID version also spilled into tempdb on the sorting of ATable:
c.ID: https://www.brentozar.com/pastetheplan/?id=HyHIeDO87
*: https://www.brentozar.com/pastetheplan/?id=rJ4deDOIQ

I also tried forcing it to operate without parallelism, and that gave me the best performing query: https://www.brentozar.com/pastetheplan/?id=SJn9-vuLX

I notice the execution count of operators AFTER the big index seek that is doing the ordering only executed 1,000 times in the single-threaded version, but did significantly more in the Parallelized version, between 2,622 and 4,315 executions of various operators.


texts:
If I had a table called person with the fk country_id.
Assuming that we have a table called country, which has a default_country column. And all rows except one (for USA), have default_country set to 0, only USA row has default_country to 1.

So if I m inserting person record, and I don't provide country_id, how can I default all new records to have country_id save with the id from row USA.

I know I can set a default column value to id (which I hardcode), but I don't know if its possible to get that from the table, here is what I mean:

"COUNTRY_ID" NUMBER(38,0) DEFAULT -1 CONSTRAINT PR_CN_NOT_NULL NOT NULL

-1 is the hardcoded. it would be nice if I could select it from country table using default_country flag rather than knowing the ID in advance, any ideas?


texts:
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?


texts: How do I store key-value pairs in a column and query those values with comparisons

texts:
I am not sure if this is the best way to go about the problem I am facing.
I have a table of game items, where each game item has up to 13 types of upgradable items.
Each upgradeable item is denoted by a single character (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C).
So let's say the item has 3 of upgradeable item "B", and "7" of upgradeable item "C", what's the best way to store this information under a single column and be able to pull out what upgradeable items exist for that item, and select items that have greater than or equal to "x" number of a specific upgradeable item.
It looks easy to solve isn't it? A foreign table for the upgradeable items and we plug the upgradeables there and boom, but no, sadly not, it needs to be in the same column, cannot add any more columns or tables.


texts: Referencing specific columns using a variable range

texts:
I have to shift 20+ columns over one to the left, for a set of rows.  Is there a way to do this without manually typing each column name? I know how to get the data for the specific rows.

For example: move data in column q49 to q48.  move data in column q50 to q49 and so on up to column q79.

I know how to move data, I just wondered how I could write it so I wouldn't have to type the long set list in an update statement:

update tablename
set q48 = q49, q49 = q50, q50 = q51, ...
from ...
where ...

I assume there would be a way to use a variable for the column names but I could be wrong.  How can this be resolved?


texts: Proper Guidelines for new Database setup in SQL Server

texts:
I need proper guidelines to setup Database in SQL Server for sales Application (Its a huge Data Transaction System). Specifically i'm looking below information:

How to define initial size to create Database
Should i use different file and filegroups
What type of Collation should i select
Any tips for tempdb configuration
How to defined how much resource needed and how to allocate them.
How to handle Server Memory Setup (Minimum server memory, Maximum
server memory, Index creation memory, Minimum memory per query)
How to calculate/estimated number of Maximum worker threads
How to calculate/estimated Maximum number of concurrent connections
How to configure SQL Server Parallelism (Cost Threshold for Parallelism, Locks, Max Degree, Query Wait)

Thanks in Advance


texts: Why aggregate operator used after unique index scan

texts:
I have a table with a unique index filtered for non-nullable values. In the query plan there is use of distinct. Is there a reason for this?

USE tempdb

CREATE TABLE T1( Id INT NOT NULL  IDENTITY PRIMARY KEY ,F1 INT , F2 INT )
go
CREATE UNIQUE NONCLUSTERED INDEX UK_T1 ON T1 (F1,F2) WHERE F1 IS NOT NULL AND F2 IS NOT NULL
GO
INSERT INTO  T1(f1,F2) VALUES(1,1),(1,2),(2,1)

SELECT DISTINCT   F1,F2 FROM T1 WHERE F1 IS NOT NULL AND F2 IS NOT NULL
SELECT  F1,F2 FROM T1 WHERE F1 IS NOT NULL AND F2 IS NOT NULL

query plan :


texts: combined SELECT with two mixed different criteria

texts:
I'm having problems selecting the data from the following example.

I have a dataset with contracts belonging to users that, in some cases, are overlapping in time (say, for current_date). Only when this overlap happens, I just want to know the latest contract signed. However, if one of the overlapping contracts is a 'test' (type = test), then I should take the valid one. The problem is that I can't figure out how to select this. Could anyone give me a hint?

So in this example

Table contracts
    START_DATE  END_DATE    USER_ID TYPE            CONTRACT_ID
    2015-09-17  2017-09-16  3193522 valid           318159780
    2016-09-21  2017-09-20  3193522 valid           318159779
    2015-11-26  2016-11-25  3510657 valid           319501137
    2016-09-20  2016-11-26  3510657 test            319501127

The desired output would be

    START_DATE  END_DATE    USER_ID TYPE            CONTRACT_ID
    2016-09-21  2017-09-20  3193522 valid           318159779
    2015-11-26  2016-11-25  3510657 valid           319501137

The dbms is MySQL. If they don't overlap I want to show them regardless their type. There are no overlapping contracts with the same start_date, this is controlled at application level.


texts:
I have managed to come up wit the below script for exporting some files. This needs to be done on a number of datasets, and is done daily.

The problem I am having is the I am getting the following error message:

  Error Code: 1064. You have an error in your SQL syntax; check the
  manual that corresponds to your MySQL server version for the right
  syntax to use near ''20160913.csv'' at line 12

    SET @exportfile =
   CONCAT("SELECT
            'FIELD_1'
        ,   'FIELD_2'
        ,   'FIELD_3'
        ,   'FIELD_4'
        ,   'FIELD_5'
        ,   'FIELD_6'
        UNION ALL
        SELECT * FROM mydatabasetable INTO OUTFILE 'E:/Data/exports/DailyExport/myfilename_'
        FIELDS TERMINATED BY ','
        ENCLOSED BY '''
        LINES TERMINATED BY '\r\n'"
       , DATE_FORMAT( NOW(), '%Y%m%d')
       , ".csv'"
    );

PREPARE processing FROM @exportfile;
EXECUTE processing;
DROP PREPARE processing;

Please can someone identify what I am doing wrong?


texts: Moving from MS Access to SQL Server 2012, need help with column history

texts:
I'm moving an access database from all the data being in MS Access to MS Access being just a frontend, but I'm having trouble figuring out how to do a sort of column history.

I have a tasks.description column, and in another table descriptionLog, I have ID, Description, DateTime, and Task. Task is the ID of the task from the Task table. I was able to do this using data macros, but I need it to be agnostic from MS Access.

I've read a few guides on change tracking but I'm not sure how to get the data out.

Detail on column history: column history is a functionality of MS Access databases 2007 and up where you can set a memo column to be append only, keeping a log of what was entered or removed from the column.


texts: SQL Server transferring results slowly in table result set, but instantly with XML

texts:
I have a query that left joins 100+ tables and returns 2,000+ columns, but only a few rows (10-20).  This part is out of my control at the moment and it used to run ok with a bit fewer columns (maybe under the normal non-wide table width).  The execution is "instant" after the initial plan is compiled and I don't actually return all the results to the client.  If I return 1 row, it is instant and returns the result instantly.

It's when I return all 11 or so results it goes down to taking about 15 seconds.  BUT, if I add any of the "FOR XML [RAW [ELEMENTS, XSINIL]]" iterations, it returns all the results plus the XML overhead instantly as well.  This rules out the actual execution of the query, as far as I can tell, and isolates it to something between the query results being calculated and them being sent to the client.

I've been looking for about 4 days for anything to explain this.  Is there a way to get the XML speeds with the result sets?  Why is it doing this?

Have tried TCP/IP and Named Pipes on 2008R2 and 2016

UPDATE

Based on the feedback, for the tabular results, what happens is the first time through, it takes about 20 some seconds.  The timer in SSMS keeps running until about 11 seconds, then it stops.  Then, when the results are displayed, the timer jumps to the 20 seconds.  The next run the timer just hangs, then when the results are show, the timer pops up to around 9-11 seconds.

The way the 11 rows are selected by the joining the unique id of the main table against a table function.  I have tried replacing the table function with a temp table.  For selecting one record I basically pass data into the table function that results in one result.  I also tried using a straight id = literal number.  For those, the timer doesn't even leave 0 before the result is shown.

I can't "insert into" a temp table because there are too many columns for a regular table.  I also can't create a indexed view on it to check that out.

I ran the query with FOR XML again and checked for the timer.  It sits at 0:00 and says query complete.  I try again and immediately open the result in xml and it shows 11 rows of xml rows, with the last one of them 54,000 characters.  It does indeed only show 2023 if you cut and paste the tabular result.  The timing differences might be the amount of data in each row?  There are multiple columns with text types.

Speaking of text types, it keeps me from doing a "count distinct" on the full query to pull all the columns but not send them at all.  Otherwise I used SSMS and told it to discard the results.  I'm not sure if that affects the query plan.  It runs fast.

I was hoping to get this thing limping along for enough to change the way it was architected.  This simply isn't acceptable in the medium to long term.

THANKS for everyone's insight and help so far.


texts: Monitor For Disabled Queue On RDS SQL Server

texts:
I would like to somehow get notification of a queue being disabled due to poisoned messages. (The issue is occasional resource contention. Until we solve it, monitoring would help us be aware.) In my case, SQL Server's service broker is running under Amazon's RDS environment, which limits what is available.

Suggestions?


texts: How to copy/export part of my SQL database

texts:
So I have this huge live SQL database. I want to do some testing so I thought to export it, however, it is around 200 GB which is impossible to export.

I thought maybe I export the schema of it and then populate it with random data, but that doesn't seem a good idea and it will take so much time to do so.

Another suggestion is to only export part of it. They challenge is that it's relational database which has 7 tables which are connected to each other and when export like 1000 rows from one table I want to export the related rows in the other tables so I don't encounter errors of missing data while testing. This is the schema of the database.

Any suggestion/hint would be highly appreciated.


texts: Oracle standby database recovery stopped

texts:
I have a primary and an standby database.
Because my file-system became full in standby database, the recovery has stopped in standby.

This is the present state of sequences in primary and standby:

Thread Last Sequence Received Last Sequence Applied Difference

     1                    560                   394        166

How can I start the recovery again?


texts:
We currently have an SSIS package that exports about 70 large tables nightly, with truncate and load at the destination. Our tables keep changing, we sometimes add some tables and remove some and editing the SSIS package accordingly is tiring.

Is there a way to have a text file or a table with the list of table names in it and have an SSIS package to loop the table and perform the export? That way, it is easier to handle dynamic list of tables that get exported.

Any links, pointers are appreciated.


texts: Best database design for payments with multiple constraints

texts:
I have to register payments.

There are two things to pay. thingC or thingD (they may not be paid).
You can only pay one thingD per payment transaction.
You can pay many thingC per payment transaction.
You can pay for thingC or thingD per payment, not both.
There are three codes that have to be generated for each thingC or thingD that was paid.

Existing tables

thingC

idthingC serial PRIMARY KEY
--more columns

thingD

idthingD serial PRIMARY KEY
--more columns

Tables I think I need:

payment

idpayment serial PRIMARY KEY
idthingD integer REFERENCES thingD -- the payment may be for a thingD
type character(1) NOT NULL --C or D
c1 integer
c2 integer
c3 integer

payment_C

idpaymentC serial NOT NULL
idpayment integer NOT NULL REFERENCES payment
idthingC integer NOT NULL REFERENCES thingC
c1 integer NOT NULL
c2 integer NOT NULL
c3 integer NOT NULL

In table payment I'll have values in idthingD, c1, c2 and c3 if type is 'D'. The columns mentioned above will be null if type is 'C', and there will be values in table payment_C.

I have to be able to say:

idpayment 1 was for thingD 2 and have the codes 1, 4, 8.
idpayment 2 was for thingC 5 with codes 2, 3, 6 AND thingC 8 with codes 4, 5, 8

What do you think? In which table should the codes be? Can the design be improved.


texts: When the Full Backup (hot backup)  happens and let us say it takes about 5-6 hours to complete.  Then what about the Transactions that happen during the backup.  Will it be recorded in the Backup file when the Full Backup is complete ?


texts: How do I an add a DEFAULT on a column if the table already exists?

texts:
I want to add a DEFAULT for a timestamp column in PostgreSQL. With SQL Server you use ALTER TABLE ADD CONSTRAINT

You can see the example in their docs,

ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b;

In my case, I'm trying the same pattern,

ALTER TABLE foo
ADD CONSTRAINT column_name
  DEFAULT now() FOR bar;

This doesn't work. What is the proper way to add a DEFAULT for a column in PostgreSQL?