texts: How to get history of mapping for following table data

texts:
I have a history mapping table for UserId changes, where every time when UserId changes, a row for new UserId with old UserId inserted in the history table.
Below is the sample table and data:

UserIdNew | UserIdOld
---------------------
    5     |     1
    10    |     5
    15    |     10

The above data explains that UserId 1 has gone with following transition from UserId 1 -> 5-> 10 -> 15.

I want to query all the Old Ids for a give UserIdNew, how can I do it in a single query?

For this case if UserIdNew = 15, then it should return 1,5,10


texts: Incredibly long execution time for a update query?

texts:
I am using MySQL 5.6.

In my table invoices I added two datetime columns that I will be setting, based on if another column is a certain value.

UPDATE invoices
SET twoWeekAlert = DATE_ADD(now(), INTERVAL 2 WEEK)
WHERE state = 6;

There are only 205 records that have state =6 and 3,500 total records.

After 5 minutes I canceled the query, made an index on the state column, and tried again.  After 10  minutes I canceled that one.

What is going on, is this a known issue with MySQL updating using a datetime calculation function or something?  I worry because I know I will have to run similar type updates in the future and I can't have it take that long.

The EXPLAIN statement I believe is telling me it IS using my index:

selectType   table     type   possible_keys     key        key_len    ref    rows   extra
SIMPLE       invoices  range  stateIndex        stateindex  2          const  205 using where

There were no warnings in my explain statement.

The CREATE statement of my table

CREATE TABLE `invoices` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `number` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `parentSOId` int(11) DEFAULT NULL,
  `parentProjectId` int(11) DEFAULT NULL,
  `status` int(1) DEFAULT '1',
  `active` int(1) DEFAULT '1',
  `dateEntered` varchar(45) DEFAULT NULL,
  `dateDue` varchar(45) DEFAULT NULL,
  `individualId` int(11) DEFAULT '-1',
  `amount` decimal(11,2) DEFAULT '0.00',
  `margin` decimal(11,2) DEFAULT '0.00',
  `comment` varchar(500) DEFAULT '',
  `custContactId` int(11) DEFAULT '-1',
  `custBuyerId` int(11) DEFAULT '-1',
  `taxable` int(11) DEFAULT NULL,
  `taxAmount` decimal(11,2) DEFAULT NULL,
  `totalAmount` decimal(11,2) DEFAULT NULL,
  `paymentTerms` int(11) DEFAULT '-1',
  `type` int(11) DEFAULT '-1',
  `shipVia` int(11) DEFAULT '-1',
  `manTax` int(1) DEFAULT '0',
  `state` tinyint(4) DEFAULT '0',
  `sentToContNotNeeded` int(1) DEFAULT '0',
  `sentToAcctNotNeeded` int(1) DEFAULT '0',
  `twoWeekAlert` datetime DEFAULT NULL,
  `threeWeekAlert` datetime DEFAULT NULL,
  PRIMARY KEY (`idx`),
  KEY `invoiceNum` (`number`),
  KEY `invoiceName` (`name`),
  KEY `fk-listprojects_idx` (`parentProjectId`),
  KEY `soIdIndex` (`parentSOId`),
  KEY `stateInd` (`state`),
  CONSTRAINT `fk-listprojects` FOREIGN KEY (`parentProjectId`) REFERENCES `listprojects` (`idx`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3604 DEFAULT CHARSET=latin1;

Things I've also tried:

Doing it by primary key ie WHERE idx IN (list of ids)

Instead of using datetime, just using date since it is enough for my needs and using DATE_ADD(curDate(), INTERVAL 2 WEEK)

I've tried both ways: NOW() with a datetime type and curDate() with a date type.  Both had the same issue.

Running it on a much stronger machine. Still the same issue.

Luckily I was just told to not retroactively affect old records so I will only be doing one record at a time, which seemed to work.  I am still very curious why this seems to take forever especially when I am the only connection on a dev database.

My server is local, in office, running on a machine in the closet. So, bare metal, not cloud or ESX.

SELECT IFNULL(state,'Total') state_value, COUNT(1) rowcount
FROM invoices
GROUP BY state WITH ROLLUP;

Results:

-1  14
0   3217
2   5
4   54
5   9
6   205
Total   3504

SHOW INDEX ON invoices results:

Table        -  invoices
Non_Unique   -  1
Key_Name     -  stateIndex
Seq_in_Index -  1  (Same for all other indexes)
Column_name  -  state
Collation    -  A (all other indexes have an A for this)
Cardinality  - 12 (All other indexes have a Cardinality of 3476)
Sub_part     - Null (Same for all other indexes)
Packed       - Null (Same for all other indexes)
Null         - YES (Same for all other indexes)
Index_type   - BTREE (Same for all other indexes)


texts: How do I reset the SQL Server password on Linux?

texts:
The instructions on technet say that I either have to reset the SQL Server configuration with mssql-conf setup or run a proc which I can not run because I do not have access.

What the simplest way to way to reset the password on Linux?


texts: Merging continous date range using T-SQL

texts:
I have a table containing a part time working pattern consisting of start and end dates together with hours values.

Where there is an gap between an end date and a subsequent start date, this indicates the member was full time.

Where the most recent entry has a NULL end date, this indicates the part time work is still ongoing. A NULL enddte should be a day before the subsequent start date, if one is present.

I'd like to merge the date ranges where there are no gaps (ie, subsequent row start date is 1 day ahead of previous row start date) and where the hrs1 and hrs2 values match. Other solutions for similar problems don't correctly handle the NULL in the final row.

CREATE TABLE #hours(
STARTDTE DATETIME,
ENDDTE DATETIME,
hrs1 DECIMAL(6,2),
hrs2 DECIMAL(6,2))

INSERT INTO #hours VALUES
('25 MAY 1990','30 NOV 1994',18,36),
('01 DEC 1994','31 DEC 1994',18,36),
('01 JAN 2000', NULL,20,36),
('16 JUN 2002','20 APR 2007',18,36),
('10 OCT 2008', NULL,20,36)

The expected output would be as follows:

25 MAY 1990 | 31 DEC 1994 | 18 | 36
01 JAN 2000 | 15 JUN 2002 | 20 | 36
16 JUN 2002 | 20 APR 2007 | 18 | 36
10 OCT 2008 | NULL        | 20 | 36

This query needs to be compatible with SQL Server 2008 so unfortunately lead/lag aren't an option to fill in any null end dates.


texts: Best practices for committing a transaction in SQL Server where TRY CATCH is used

texts:
In a SQL Server code block, what is the best place to place the commit transaction? Inside the try catch block or outside it?.

For example, is option A or option B the correct approach or are they subjective choices?

Option A

CREATE PROCEDURE DummyProc
BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

Option B

CREATE PROCEDURE DummyProc
BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)

   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH
   IF @@trancount > 0  COMMIT TRANSACTION

In Option B, is there a possibility of some error happening when its doing a commit outside the TRY-CATCH block ?


texts: Preventing low-priority tasks from dominating Postgresql server

texts:
I have a side project which involves scraping several websites and aggregating their content, which can be queried via an API. As such, I have a few background tasks which update/insert millions of rows per day. When these tasks are running, the API is noticeably slower - 5-10 seconds of delay instead of <1 second. It's not practical to provide a hard rate limit on the background task. The background worker would be leaving performance on the table 99% of the time, and there still wouldn't be enough performance left over for larger surges to the API.
I'm looking for a practical way to prevent the background worker from hogging the Postgresql server. I was thinking I could have 2 servers in a cluster - one would be read/write, which the background worker would connect to, and the other one would be read only. Since writes would be propagated to both servers anyway, I'm not sure if that would actually help performance.


texts: Total Server Memory and Target Server memory Value same

texts:
Can anyone help me what does this indicate:

From the MSDN forums thread Total Server Memory VS Target Server Memory made me crazy answer by Jonathan Kehayias:

  If Total Server Memory is less than Target Server Memory it can be a
  sign of memory pressure

But in my case the values are same:


texts: I have two PostgreSQL standalone servers A and B. From server A I need to sync only one database and table to server B. How should I proceed?


texts: Securing High-Availability on Partitioned Database

texts:
I have a 90GB SQL 2012 database I am sharding over 3 database and I plan on growing as more clients are coming. A new customer came to us with a demand to know on we secure high-availability in our platform. This is a very important customer with a high potential.

I am using SQL with PartitionDB wrapper. I have read about the built-in backup feature. I am not sure if this is enough, I want to use AlwaysOn but I don't know how to use it on partitioned database.

We manage a security platform for our customers where we need to secure isolation for each client. Our production environment currently holds 3 small customers with 90GB but now we need to plan for our major customer, comes with special requirements.

Some of the requirements demands we give 24/7 real-time DB access to his data, special addins fitting his solution including code and data structure, and 24/7 availability. It's very important customer and we need to adjust to his needs. We are built-in Azure, on-premise database.

We are using partitiondb, the product, and not the services. Their solution helped us manage our databases, the product works as an Object-Oriented modelling which fits great with our generic platform behavior. Now we are testing our new architecture with stress tests reaching over 100GB per customer with over 1K concurrent users. So far looks good from performance aspects.

Now we are dealing with the high-availability requirement. We understand we can use BackupDB but it's not enough for our customer. We require a cluster-like real-time solution. Using AlwaysOn seems quite unsustainable when dealing with multi-tenant programming same we have, as we may have more than 20 databases. I was wondering if someone else, had the same incident, and how it worked for him? Talking about SaaS systems with multi-tenant cloud programming.


texts: After I kill my clients, MySQL continues to process queries for hours

texts:
Hi I have a MySQL db with 10 million rows with lots of triggers. A php client connects to mysql and INSERT's hundreds of rows per second.

When the client has been running for hours, and I terminate the client, using mytop i see transactions are still ocurring, even new ones. It lasts for hours before it "catches up".

Now as a DBA, how can I view this "back log"? what variable is it listed as? What my.cnf parameters would control it? Why is this even happening? I thought the


texts:
I have an index that is very expensive to calculate, and I'm looking for ways to have it be restored or transfered in a simple copy fashion. It would be done along with the underlying data of course.

Alternatively could I do it with files? Something akin to using pg_class.relfilenode to find where the index lives, and copy these files.


texts: Ensure 'Adjust memory quotas for a process' is set to 'Administrators, LOCAL SERVICE, NETWORK SERVICE' vulnerability for Windows 2008 R2 SQL

texts:
I have a MSSQL Windows 2008 R2 server. Vulnerability for "Ensure 'Adjust memory quotas for a process' is set to 'Administrators, LOCAL SERVICE, NETWORK SERVICE'" has been found. Current settings on my server are:
'mssqlserver'
'mssqlfdlauncher'
'administrators'
'sqlserversqlagentuser$$mssqlserver'
'network service'
'local service'
The CIS Microsoft Windows Server 2008 R2 Benchmark, and all other sources I have found, says remediation is to remove everything except administrators, network service, and local service. What are the possible outcomes if the other values are removed? If there are adverse side effects, how could I restore the original user/group security settings for this policy?


texts:
I am working on a project for my university. They gave me a login username and password for MySQL database they've setup on the server.

With that username and password, I'm able to login to the server. I ran the following query on it:

SHOW GRANTS;

And got following output

GRANT USAGE ON *.* TO 'me'@'localhost' IDENTIFIED BY PASSWORD '*mypass'

Now if I am trying to create a database with following code

CREATE DATABASE test;

I am getting following error

Error: 1044
Access denied for user 'me'@'localhost' to database 'test'

As far as I know, . means access to all tables of all databases. Where is the problem. Please help me to rectify me.


texts:
When I built the cluster I forgot to uncheck the storage. It's now giving me all sorts of errors. How can completely remove the cluster so I can rebuild it?
This is a 3 node cluster and I evicted two nodes, but when I try to evict the last node I get:

I removed all the Disk and Pools from the remaining node.
Can you please guide me


texts: Any way to pass additional parameters to Azure SQL Audit?

texts:
We are exploring Azure SQL Auditing capabilities. It gives us a list of all the queries executed and some additional data which is good.

However, field server_principal_name is always reflecting a SQL username that is provided in a connection string. And it is always the same value - whatever was in the connection string.

We are running ASP.Net MVC application with Entity Framework for data access and there is a single connection string for the database, but there are multiple application users. It would be beneficial if we could see username of application user in audit rather than username of SQL user from connection string.

So my question - is it possible to pass parameters into Audit log in Azure SQL?

There is similarly looking question, but talking about DML, hostnames and IP. Answers seem to be saying "NO", but question is slightly different. Anybody can confirm that what I'm trying to do is (im)possible?


texts: What determines the default schema name in SQL Server?

texts: when I tried to create a table, it is using my ID as the schema name. But when the table is created on my manager's computer, the schema name is "dbo". Why? He might have administrator rights. When I tried to rename by using sp_rename, it is not changing.


texts: Incorrect syntax near '+' in stored procedure sql server

texts:
I am writing a stored procedure which will accept a parameter and send mail to some recipients. I am using the sql builtin stored procedure.

here is the code

create proc send_mail_admin(@usage_data as varchar(200))
as
begin

--get the list of mail admins from Users

declare @admin_list varchar(max);

set @admin_list = '';

--query to get all the admins

begin
    select
        @admin_list = @admin_list + email + ';'
    from
        Users;
end

--call the mail stored procedure

exec msdb.dbo.sp_send_dbmail
@profile_name='some_mail_profile',
@recipients=@admin_list,
@subject='Mail from sql server',
@body=@usage_data + ' end of data',
@importance='HIGH' ;

end;

I am getting an error in the + symbol. If I remove the + symbol and execute, the procedure is getting executed. Otherwise it is giving error.
A little help please.

I am trying this on a knowledge based. So feel free to share the disadvantages of sending a mail like this if there are any.

Thank you.


texts: How to remove data from multiple tables with different foreign keys without using ON DELETE CASCADE?

texts:
I have the following SELECT statement:

SELECT * FROM "Users" u
INNER JOIN "Calendars" c ON (u."UserId" = c."UserId")
INNER JOIN "Actions" a ON (c."CalendarId" = a."Calendar_CalendarId")
WHERE u."AuthenticationStatus" = 0 AND u."CreationDate" < now()::date - 7;

This properly selects all rows I want from the three tables "Users", "Calendars" and "Actions". It does this by matching the "UserId" of the "Users" table with the foreign key "UserId" of the "Calendars" table, and the "CalendarId" of the "Calendars" table with the foreign key "Calendar_CalendarId" of the "Actions" table.

I want to convert this to a DELETE statement. What I tried is:

DElETE FROM "Users" u
    USING "Actions" a, "Calendars" c
WHERE u."UserId" = c."UserId" AND
      c."CalendarId" = a."Calendar_CalendarId" AND
      u."AuthenticationStatus" = 0 AND
      u."CreationDate" < now()::date - 7;

But this says:

  update or delete on table "Calendars" violates foreign key constraint "FK_public.Actions_public.Calendars_Calendar_CalendarId" on table "Actions"
  DETAIL:  Key (CalendarId)=(2) is still referenced from table "Actions".

Does anyone know how I convert this query into a DELETE statement?


texts:
I have oracle database 11gR2 .
I want to save all changes of all tables in database such as insert,update and delete values . I want to save them in a table .
When i search for its solution ,find out that ORACLE AUDIT can not save data of changes .
Does ORACLE have any solution for this problem ?
Thanks


texts: A question on MySQL SELECT Algorithm/Function

texts:
Let's say I have 100,000 rows in a table, and each of the rows has a boolean value. Only ten of these rows have a value of 1, the rest are 0.

How would MySQL perform a SELECT on only the rows with a boolean value of 1? Would it have to do a binary search through all the values? Or would it be smart enough to pull out only the ones with a boolean value of 1 without having to search through the entire list?


texts:
We have a number of SSRS reports and want to show the report in Excel format to the users. SSRS has provided an option to save the report in Excel format (.xls), however when the report is saved as Excel, all the cell borders are gone and the spread sheet background color is white. For example:

Is there any way to prevent it from happening? So the report is shown in Excel with all grids shown as per normal?


texts: MySQL : LEFT JOIN not fetching required data

texts:
I'm using left join in MySQL as mentioned below:

SELECT  Q.QUESTION_ID,
        school_id
FROM    Q_MASTER AS Q
LEFT JOIN (
            SELECT  keyword,
                    school_id
            FROM    E_HEADER
            WHERE   low_q = 1
                    AND school_id = 6
          ) AS X
        ON X.SCHOOL_ID = Q.SCHOOL_ID
WHERE   Q.QUESTION_ID IN ( X.KEYWORD )

Here Q_MASTER is having question_id as primary key (INT).
E_HEADER is having keyword as comma seperated values of question id. (VARCHAR100)
Ex: 1,2,3,4

I want to exclude all question id's in select query from Q_MASTER which are present in "KEYWORD" column of E_HEADER

But above mentioned query is only execluding first value in comma seperated string.
I tried , NOT EXISTS clause also, but every time only first comma seperated value is getting execluded.

Can you please let me know , how to solve this issue.


texts: Understanding Plan Choice When Query Store Doesn't Respect Forced Plan

texts:
I'm trying to figure out a situation that I encountered last week where, in an attempt to temporarily fix a plan regression during an incident, I forced an execution plan using Query Store, but found it didn't work as expected. I have read Andrew Kelly's article on how forced doesn't always mean forced and think this may be what I'm seeing, but I'm hoping for some more insight into it, as in my case the plans are more different than I'd expect. I've also had a look over the Plan Forcing Limitations section of the Query store documentation on plan forcing, but I don't think any of the restrictions here apply.

The view in query store is shown below - around 8:30 a new plan with a higher cost showed up (2.14178 vs 0.894238), and I forced the plan which was in use previously. As can be seen in the graph, the new plan is shown against the metrics for the query from this point on, despite me having forced the old plan:

Looking at sys.query_store_plan, I can see that the old plan is showing as forced and has nothing against it suggesting any failures in forcing it:

The strange thing here is that when I later looked in the plan cache, neither of these plans were in use, though the plan in use did have the same query plan hash value as plan 13449 shown above, despite being quite different. The plan that was actually in use had a much higher estimated cost at 72.6743.

I took the compiled values from each of these plans and ran the query for the three of them to get an idea of what the actual metrics looked like for the plans and the values are quite different from the estimates. Notably, the plan I took from the cache produced a ~400 MB memory grant due to it using a different index and having to sort the data, compared to an 8 MB memory grant on the other 2 plans, with no sort. The estimated costs of the three plans were a bit closer, but the 2 from query store had much higher estimated costs than were in the plans shown in query store.

This is the query this happened with:

(
    @id_group int,
    @create_date datetime,
    @rows_per_page int,
    @page_number int
)
SELECT *
FROM ts_customer
WHERE
    id_group = @id_group
    AND enabled = 'Y'
    AND (create_date >= @create_date)
ORDER BY
    full_name,
    id_customer desc
offset
    ((@page_number - 1) * @rows_per_page) rows
    fetch next @rows_per_page rows only

Could anyone help me understand why the plan wasn't forced, and why the one in use was so much more expensive?

I know that select * will be causing the large memory grant as it is a wide table, but I'm mainly trying to understand the behaviour seen here with query store, and to a lesser extent, why on the plan that was used instead of the forced plan it used a different index and had to do the sort.

The database this happened with is running on Azure SQL DB, compatibility level 140, and using the legacy cardinality estimator.

I've attached all 3 estimated plans, as well as the plan that was generated when I ran the query with each set of compiled values. These can be accessed here.


texts: Who is using a SQL Server database user credential?

texts:
I have a database user -- let's say "x" -- and I want to know how many Windows (desktop) users are using this database user (who2 or who is only give the current result, I want to know the entire history)?

Actually this question arise when you join any new organization and you don't know how many Windows or desktop users are using a specific database user. Is there any way to know this? How do you track all Windows users who are using this (x) user credential?


texts: A query to find patients with any 4 visits within a 365 day period

texts:
I need to find our high utilizers. I have this query:

with Visitlist as(
select v.PatientID, a.VisitID,  a.AdmitDateTime,
rank() over (partition by PatientID order by AdmitDateTime desc) as VisitNumber
from AdmittingData a
inner join MriPatientVisitEvents v on a.VisitID = v.VisitID
where
a.AdmitDateTime >= '2016'
and v.PatientID is not null)

select distinct v1.PatientID,
v1.AdmitDateTime, v1.VisitNumber, v2.AdmitDateTime, v2.VisitNumber,
v3.AdmitDateTime, v3.VisitNumber, v4.AdmitDateTime, v4.VisitNumber
from Visitlist v1
inner join Visitlist v2 with (nolock) on v1.VisitNumber = v2.VisitNumber - 1 and v1.PatientID = v2.PatientID
inner join Visitlist v3 with (nolock) on v1.VisitNumber = v3.VisitNumber - 2 and v1.PatientID = v3.PatientID
inner join Visitlist v4 with (nolock) on v1.VisitNumber = v4.VisitNumber - 3 and v1.PatientID = v4.PatientID
where
v1.VisitNumber = 1
and datediff(day, v4.AdmitDateTime, v1.AdmitDateTime) <= 365
and v1.AdmitDateTime >= '2017'

The Problem: This query will only return patients whose last 4 visits happened within 1 year.

Question: How would I find all the patients with any 4 visits that happened within 1 year of each other?

Credit to Jonathan Fite, I used his suggestion to rewrite the query like so:

with VisitEvents as(select distinct PatientID, VisitID from MriPatientVisitEvents),

VisitList2 as (
select v.PatientID, a.VisitID, a.AdmitDateTime, rank() over (partition by v.PatientID order by a.AdmitDateTime desc) as VisitNumber
from AdmittingData a
inner join VisitEvents v on a.VisitID = v.VisitID
where a.AdmitDateTime is not null),

VisitList as(
select v.*,
lag(AdmitDateTime,1) over (partition by PatientID order by AdmitDateTime) as Delta1AdmitDateTime,
lag(VisitNumber,1) over (partition by PatientID order by AdmitDateTime) as D1VisitNum,
lag(AdmitDateTime,2) over (partition by PatientID order by AdmitDateTime) as Delta2AdmitDateTime,
lag(VisitNumber,2) over (partition by PatientID order by AdmitDateTime) as D2VisitNum,
lag(AdmitDateTime,3) over (partition by PatientID order by AdmitDateTime) as Delta3AdmitDateTime,
lag(VisitNumber,3) over (partition by PatientID order by AdmitDateTime) as D3VisitNum,
lag(AdmitDateTime,4) over (partition by PatientID order by AdmitDateTime) as Delta4AdmitDateTime,
lag(VisitNumber,4) over (partition by PatientID order by AdmitDateTime) as D4VisitNum

from VisitList2 v
)

select * from VisitList
where datediff(day,Delta3AdmitDateTime, AdmitDateTime) <= 365
and AdmitDateTime >= '2017'
order by PatientID, VisitNumber


texts:
I have the following SQLs executed millions of times a day, a single execution isn't an issue but executing million times is causing some performance issues..Thanks in advance for your help!

NOTE: data can change in any of the tables any time during the day..can't reduce number of executions and this is Oracle 11g env.

SELECT COUNT (*)
    INTO v_cnt
  FROM t1 cpr, t2 brl
 WHERE cpr.cp_id = :1
     AND cpr.brl_id = brl.brl_id
     AND brl.ranking > 0
     AND cpr.security_enabled_ind='Y'
     AND cpr.brl_id NOT IN (
                   SELECT rpr.brl_id
                     FROM t3 rpr, t4 ppr, t5 plcy
                    WHERE rpr.ppr_id = ppr.ppr_id
                      AND ppr.plcy_id = plcy.plcy_id
                      AND plcy.db_object_name = :2);
IF v_cnt = 0
THEN

SELECT where_clause
  FROM
    (SELECT where_clause
       FROM t4 ppr_o
     WHERE ppr_o.ppr_id IN (
                    SELECT ppr.ppr_id
                      FROM t1 cpr,
                           t3 rpr,
                           t4 ppr,
                           t5 plcy
                     WHERE cpr.cp_id = :1
                        AND cpr.brl_id = rpr.brl_id
                        AND rpr.ppr_id = ppr.ppr_id
                        AND ppr.plcy_id = plcy.plcy_id
                        AND cpr.security_enabled_ind='Y'
                        AND plcy.db_object_name = :2
                        AND plcy.statement_type = :3)
    ORDER BY NVL (ppr_o.ranking, 0) DESC)
WHERE ROWNUM = 1;
END IF;

Number of records

t1 -> 205101
t2 -> 42
t3 -> 142
t4 -> 57
t5 -> 13


texts:
I’ve got a question concering the design of a SQLite database. I’m building a database for analyzing purposes that constists of works of literature and metadata concering those entities.
The thing I’m asking is wether it makes sense to split my table into several tables, e.g. buliding a relational database. Right now I’ve got one main table in which my entites are stored. The columns are a unique title, one for the author (who may quite well not be qunique in the database), a publication year, publisher (also not unique) and a lot of specialized fields containing tags connected with the work. Those tags are organized in three categories and each entity can be connected with multiple tags (therefore Tag A 1 and so on). One entity would look like this:

Title | Author | Year | Publisher | Tag A 1 | Tag A 2 | Tag A 3 | Tag B 1 | Tag B 1 | Tag B 3 | Tag C 1 | ...

Is it useful to build a table with authors, one for publishers and than a table for each category of tags and than link those back with foreign keys? What would be the advantages besides no more doubling of data? I’m going to query the database for things like "Titles with more than 2 manifestations of Tag A from 1980 to 1990" or "Authors using Tags A and B".

All my columns are filled with atomic values. If I’m correct I’m looking at brining my database into the first normal form by trying to "eliminate repeating groups in individual tables" – is that correct?

Thanks for your help!


texts:
I have the following ranking query working fine with a limit of 25 records per page, it shows 1st - 25th for the first 25 records, but when i go to the next page, the rank stays the same 1st - 25th when it should say 26th - 50th.

SELECT image, name, wins, losses, level, xp, wins AS W, losses AS L,
TRUNCATE(wins/IF(wins=0,1,wins+losses)*100,0) AS PCT,
@curRank := @curRank + 1 AS rank
FROM teams p
, (SELECT @curRank := 0) r
WHERE `console` = 3 AND `game` = 23 AND `laddertype` = 1
ORDER BY xp DESC LIMIT " . mysql_real_escape_string($limit) . ", 25"


texts:
We have an application that uses TomCat and Java to receive files and move them to a SQL Server 2012 instance.

Previous versions of this software worked just fine.

The new release fails on our system with the error

  Error creating connection pool for dbid:

Many hours have been spent adjusting the configuration to get past this error with no positive result.  Any insight on what the error means and how it might be traced would be appreciated.


texts: How to Change location of postgres cluster and database within the same machine?

texts:
I have my present database cluster of postgres at /mnt/my_hard_drive which I want to change to /home/myfolder. I also want to move all my databases present in the present cluster to /home/myfolder. Is there a way to do so?

I know one way for doing so is to dump my databases in some form e.g. .sql and reconstruct them from there. But considering my database size if 5TB I dont want to use this approach. Is there some other way to achieve this..since my machine is same...all I want to do is to move my database from hard drive to my home folder. Please suggest


texts: Benchmarking set up over HDD+SSD combination?

texts:
I have a Linux system set up (Arch) on an HDD. I have been trying to benchmark some of my queries and found some disk IO bottleneck, and thus opted to move the entire PostgreSQL database from the HDD to my SSD.

I could notice a difference, however, I can't help but wonder if my set up is still imposing some limitations: I have my Linux OS, testing scripts and interface (psql) on an HDD, and my database server is on an SSD. Can I expect much better results if I move literally everything to SSD?

I am not so familiar with how the OS/DBMS interaction is working...

If it helps, the SSD is being exclusively used for the DBMS. And my data size fits comfortably over my RAM and SSD.


texts: Executing SPs in parallel (maybe with a cursor)

texts:
We have an 3rd party accounting system that runs on SQL Server 2008 R2. To insert invoices into this accounting system, we have to use the API that comes with it (which is just a set of encrypted SPs).

Some of our batch processes need to insert a large number (1,000s) of invoices. So, ordinarily in SQL you'd write an INSERT statement and they'd get inserted very quickly.

However, because we have to use the SP to insert the rows we currently have a C# application that runs a PARALLEL FOR loop and makes a call to the SP once per iteration.

The performance isn't great and, while much of this is down to what happens inside the API SP, I'm wondering if there's anything SQL Server can do for me to improve performance. I know I could get rid of the C# app and use a CURSOR in T-SQL, but as far as I know, that wouldn't run in parallel - is that correct?

Similarly, I could dynamically generate thousands of SQL statements to call the SP with different parameters and I'd like to think that the optimizer can see what the encrypted SP is doing (certainly better than a C# app) and come up with a plan that will run them all in with as much parallelism as possible.


texts: Why does MySQL UPDATE with <= lock insert intention?

texts:
I'd like to be able to update a table without locking inserts to avoid possible deadlocks. I thought I could use a query before the update to limit it so that the insert gap is not locked but it doesn't seem to work:

CREATE TABLE `foo` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `value` varchar(45) NOT NULL
) ENGINE='InnoDB';
INSERT INTO foo (value) VALUES ('foo1');
INSERT INTO foo (value) VALUES ('foo2');
INSERT INTO foo (value) VALUES ('foo3');

Transaction 1

start transaction;
select MAX(id) INTO @max FROM foo;
update foo set value = NOW() where id <= @max;

Transaction 2

start transaction;
insert into foo (value) values ('foobar');
-- waiting ...

Now transaction 2 is waiting for transaction 1 which I didn't expect.

My theory works if I subtract 1 from MAX:

Transaction 1

start transaction;
select MAX(id) - 1 INTO @max FROM foo;
update foo set value = NOW() where id <= @max;

Transaction 2

start transaction;
insert into foo (value) values ('foobar');
-- Query OK, 1 row affected

So why doesn't the first example work and is there any way to accomplish the update all the way up to MAX(id) without blocking inserts using a regular auto-increment primary key?

Tested using MySQL 5.7. Below is the SHOW ENGINE INNODB STATUS\G output for the first case (Transaction 2 blocked):

------------
TRANSACTIONS
------------
Trx id counter 6214932
Purge done for trx's n:o < 6214930 undo n:o < 0 state: running but idle
History list length 1836
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422057928858336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6214931, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 369, OS thread handle 140582776452864, query id 7104 localhost root update
insert into foo (value) values ('foobar')
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4135 page no 3 n bits 80 index PRIMARY of table `test`.`foo` trx id 6214931 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------
---TRANSACTION 6214930, ACTIVE 15 sec
2 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 5
MySQL thread id 367, OS thread handle 140582775654144, query id 7102 localhost root cleaning up
Trx read view will not see trx with id >= 6214930, sees < 6214930


texts: postgres: Database replica with some schemas writable

texts:
I have two computers.  Computer A is dedicated to collecting data and storing it in its local database.  Computer B is for analysis.  A is running flat out just with the data collection, so I want to replicate the collected data onto B.  The catch is, analysis will generate its own derived data which should also be stored in the database (but only on B).  My understanding is that the off-the-shelf replication mechanisms require the recipient database to be read-only.

Is it possible to do something like streaming replication, but which allows the recipient database to be read-write?  Ideally, the schema containing the tables replicated from A would be read-only to analysis users, but separate schemas owned by those users would be read-write for them.

It is okay for the replica to be hours out of date.

I want to keep data collection on A writing to a local database, so that it can continue whether or not B is available.

(Currently I'm on 9.3 but about to upgrade to 9.4 betas for unrelated reasons.)


texts: Nested Cursors in Mysql problem with FETCH

texts:
I have the problem with my nested cursors in MySQL.

My second cursor only advances 1 single record of the first courses and then shows me message indicating. Below is a sample of the response from my Mysql Workbeanch.

OPEN queryLegalArticleSource;
    queryLegalArticleSource_LOOP : LOOP
        -- PARSED VARS
        FETCH queryLegalArticleSource INTO configTypeArticleTypeTwo;
        -- ************************************
        -- VALIDATE PROCESS noMoreRows
        -- ************************************
        IF noMoreRowsTwo = 1 THEN
            CLOSE queryLegalArticleSource;
            LEAVE queryLegalArticleSource_LOOP;
        END IF;
        SELECT legalBodyId, companyIdSource, configTypeArticleTypeTwo, noMoreRowsTwo;
    END LOOP queryLegalArticleSource_LOOP;
CLOSE queryLegalArticleSource;

If I comment the FETCH line

-- FETCH queryLegalArticleSource INTO configTypeArticleTypeTwo;

Get all the results, but I do not have the variable I need.

Check if any cursor was closing it before, but apparently I have everything right.

Does anyone have any idea?


texts: All recent changes in MySQL have been automatically removed. Recently INSERTed or UPDATEd data is not available

texts:
I have been using MySQL for the last two months. I'm regularly INSERTing new records and UPDATEing old ones. But when I opened phpMyAdmin today, all of the changes which I've made in the 10 days have vanished.
INSERTed data for the last 10 days is unavailable and The UPDATEs I have made to other records have also reverted to previous versions.
The AUTO_INCREMENT field is still incrementing to the next number like nothing is DELETEd.
I need my recently INSERTed data and my UPDATEs restored.
More importantly perhaps, why is this happening?
This problem happen between 5th to 7th January. This is the Error file of these days:
Error Log


texts: Folder permissions changing when running Ola Hallengren maintenance scripts

texts:
We use a centralized backup location on a network share. Each instance uses the Ola Hallengren scripts to perform nightly backups.
I have a team that requires folder permissions to the same location where backups are stored. While the team has permissions to the parent folder it seems they no longer have permissions to any of the sub-folders where the backups are stored. I have narrowed it down to the command which executes the procedure dbo.xp_create_subdir. However, I am unable to figure out why the permissions are overwritten everytime this command is run.

How can I ensure the subfolder permissions are not being changed with the Ola Hallengren scrips?


texts: Naming conflict between function parameter and result of JOIN with USING clause

texts:
Given this setup in current Postgres 9.4 (from this related question):
CREATE TABLE foo (ts, foo) AS
VALUES (1, 'A')  -- int, text
     , (7, 'B');

CREATE TABLE bar (ts, bar) AS
VALUES (3, 'C')
     , (5, 'D')
     , (9, 'E');

db<>fiddle here (also from the previous question).
I wrote a SELECT with a FULL JOIN to achieve the objective of the referenced question. Simplified:
SELECT ts, f.foo, b.bar
FROM   foo f
FULL   JOIN bar b USING (ts);
As per specifications, the correct way to address the column ts is without table qualification. Either of the input values (f.ts or b.ts) can be NULL. The USING clause creates a bit of an odd case: introducing an "input" column that's not actually present in the input. So far so elegant.
I put this in a plpgsql function. For convenience (or requirements) I want the same column names for the result of the table function. So we have to avoid naming conflicts between identical column names and function parameters. Should best be avoided by picking different names, but here we are:
CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT COALESCE(f.ts, b.ts), f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- so something
      RETURN NEXT;
   END LOOP;
END
$func$;
Bold emphasis to highlight the problem. I can't use ts without table qualification like before, because plpgsql would raise an exception (not strictly necessary, but probably useful in most cases):

ERROR:  column reference "ts" is ambiguous
LINE 1: SELECT ts, f.foo, b.bar
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

I know I can use different names or a subquery or use another function. But I wonder if there's a way to reference the column. I can't use table-qualification. One would think there should be a way.
Is there?


texts: How can I draw an entity-relationship diagram for a medical business domain?

texts:
I have to develop an entity-relationship diagram (ERD) for a business domain with the following characteristics:

A doctor consults from one clinic only, but a clinic may have many doctors working from it.
A patient makes an appointment to see a doctor on a specific day and time.
A patient may consult with many doctors and a doctor must consult with at least one patient.

My current work

I've been able to create the following diagram:

Note: I am a beginner and have just the base knowledge.


texts: Why is it faster to run a query via a linked server?

texts:
I have this query that a vendor wants to start running on my data warehouse.   The version I'm posting runs at 18 seconds if I run via a linked server (SQL Server 2017, 12GB RAM, more CPU) as a remote query, and 4 minutes if I run it directly on the server (4GB RAM, SQL Server 2008).

Yes, I know my servers suck. We are getting a better set.

Question: Why would it be faster to run this:

    select * from [eih-dr01].livefdb.[dbo].[RegAcct_Main]

than going directly onto the server eih-dr01 and running this:

    select * from RegAcct_main

Here's the big, bad query, for reference:

    declare @start datetime
    set @start = dateadd(minute, -45, getdate())

    SELECT
     'AST' AS [HDR]
    ,RAM.Facility_MisFacID AS [FID]
    ,MRN.PrefixMedicalRecordNumber AS [MRN]
    ,RAM.AccountNumber AS [VISIT_NBR]
    ,MDSM.[Name] AS [CATEGORY]
    ,PADM.MisDocumentationSectionOid AS [CATEGORY_ID]
    ,MQM.Text
    ,MDSQ.QuestionMnemonic
    --,COALESCE(MQM.Text,MDSQ.QuestionMnemonic) AS [QUESTION]
    ,PADQ.QueryValue
    --,REPLACE(REPLACE(REPLACE(REPLACE(PADQ.QueryValue,'|}',''),'{',''),'}',''),'|',',') AS [FINDING]
    ,PADM.DateTimePerformed
    --,CONVERT(VARCHAR,PADM.DateTimePerformed,101) + ' ' + LEFT(CONVERT(VARCHAR,PADM.DateTimePerformed,108),5) AS [REPORT_DT]
    --,PADM.DateTimePerformed
    --,1 AS Ord

    FROM [eih-dr01].livefdb.[dbo].[PcsAcctAct_IntActAssessments] PAAIAA

    INNER JOIN [eih-dr01].livefdb.[dbo].[RegAcct_Main] RAM
    ON PAAIAA.SourceID = RAM.SourceID
    AND PAAIAA.VisitID = RAM.VisitID

    LEFT JOIN [eih-dr01].livefdb.[dbo].[MisFac_Main] MFM
    ON RAM.SourceID = MFM.SourceID
    AND RAM.Facility_MisFacID = MFM.MisFacID

    LEFT JOIN [eih-dr01].livefdb.[dbo].[MisHimDept_Main] MHDM
    ON MHDM.SourceID = MFM.SourceID
    AND MHDM.MisHimDeptID = MFM.HimDepartment_MisHimDeptID

    LEFT JOIN [eih-dr01].livefdb.[dbo].[HimRec_MedicalRecordNumbers] MRN
    ON RAM.SourceID = MRN.SourceID
    AND RAM.PatientID = MRN.PatientID
    AND MHDM.MedicalRecordNumberPrefix = MRN.MrnPrefixID

    INNER JOIN [eih-dr01].livefdb.[dbo].[PcsAssmntData_Main] PADM
    ON PAAIAA.SourceID = PADM.SourceID
    AND PAAIAA.VisitID + '{A^' + PAAIAA.InterventionActivityAssessment_MisDocSectID + '}' = PADM.PcsAssmntDataID
    AND PAAIAA.InterventionActivityUrnID = PADM.IdentifierID

    INNER JOIN [eih-dr01].livefdb.[dbo].[PcsAssmntData_Queries] PADQ
    ON PADM.SourceID = PADQ.SourceID
    AND PADM.PcsAssmntDataID = PADQ.PcsAssmntDataID
    AND PADM.IdentifierID = PADQ.IdentifierID

    LEFT JOIN [eih-dr01].livefdb.[dbo].[MisDocSect_Main] MDSM
    ON PADM.SourceID = MDSM.SourceID
    AND PADM.MisDocumentationSectionOid = MDSM.MisDocSectID

    LEFT JOIN [eih-dr01].livefdb.[dbo].[MisDocSect_Questions] MDSQ
    ON PADQ.SourceID = MDSQ.SourceID
    AND PADQ.QuerySetID = MDSQ.QuestionSetUrnID
    AND PADQ.QueryNumberID = MDSQ.QuestionUrnID
    AND PADM.MisDocumentationSectionOid = MDSQ.MisDocSectID

    LEFT JOIN [eih-dr01].livefdb.[dbo].[MisQry_Main] MQM
    ON MDSQ.SourceID = MQM.SourceID
    AND MDSQ.QuestionMnemonic = MQM.MisQryID

    where
    --RAM.AdmitDateTime
    PADM.DateTimePerformed >= @start
    --and MDSM.[Name] = 'Vital Signs'
    and PADM.MisDocumentationSectionOid like '%VS%'


texts: Using dynamic query to loop through Db for a truncate statement

texts:
Please, any help with my SQL query below will be appreciated. I am not familiar with how to loop through DBs to truncate a table. What I have tried is below:

DECLARE @DynamicSQL AS VARCHAR(MAX) = '';
DECLARE @dbName     nvarchar(1000) ='SP_%%';

SELECT @DynamicSQL = @DynamicSQL + 'Use' + QUOTENAME(NAME) + ';  '
FROM sys.databases
WHERE NAME LIKE  'SP_%%';

SELECT @DynamicSQL = @DynamicSQL + 'TRUNCATE TABLE ' + QUOTENAME(NAME) + '; '
FROM sys.tables
WHERE name LIKE 'log%'

PRINT @DynamicSQL
--EXEC (@DynamicSQL)

The Result I get is below:

Use[SP__20180314];  Use[SPAUT__06092018];

But I want something like:

Use[SP_20180314] Truncate Table log;  Use[SPAUT_06092018] Truncate Table log;

I am trying to truncate log tables from my databases on SQL Server 2016.
Thanks


texts:
I am looking at both the SQL Server expensive queries report and the query below, but both are showing this mysterious NULL query as the slowest query on my server.
Is there any way I can find out more about this NULL query and why it might be so slow?
Is this some internal query? It doesn't seem like this should be showing up in the report if so.

This is the query which is also showing NULL as the slowest query on my server:
select
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    t.text
from
    sys.dm_exec_requests as r
cross apply
    sys.dm_exec_sql_text(r.sql_handle) as t

How can I find out what this query is and why it's so slow?


texts:
I am new in MySQL.. I want to compute for the percent change of this table using MySQL. Can you help me with the script? I am using 10.3.8-MariaDB MariaDB Server.. Thanks..

CREATE TABLE `MyTable` (
  `Column` VARCHAR(14),
  `Amount` VARCHAR(6);

INSERT INTO `MyTable` (`Column`, `Amount`)
VALUES (`Column 1`, `500`),(`Column 2`, `100`),(`Column 3`, `1100`);

I want to get an output table sorted by Column:

Column   | Amount | Previous Amount         | Percent Change
Column 1 | 500    | 0                       | ((500-0)/0)*100
Column 2 | 100    | 500 (amount in column1) | ((100-500)/500)*100
Column 3 | 1100   | 100 (amount in column2) | ((1100-100)/100)*100


texts: Optimizing bulk update performance in Postgresql with dependencies

texts:
Basically my question is the same as this one, but WITH dependencies, so drop/renaming the table is not a trivial option (I assume).

We are refactoring a large, poorly designed table which has many columns and references to it. It currently has a text field that should be a foreign key. The naive update looks like:

ALTER TABLE ADD COLUMN new_id int REFERENCES list(id);
UPDATE myTable SET new_id=(SELECT id FROM list WHERE name=old_text);
ALTER TABLE myTable DROP COLUMN old_text;

The above takes practically forever because the table is large, and basically gets temporarily doubled due to UPDATE being equivalent to INSERT/DELETE.

We do not need everything done in one transaction. So we are considering some sort of external script to do the updates in batches of 5000 or so, but tests indicate it will still be painful/slow.

Suggestions on how to improve performance?


texts: Can oscillating number of fragment decrease FTS query performance?

texts:
I've got a FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly.

Reading Guidelines for full-text index maintenance I dig around sys.fulltext_index_fragments and noticed the following:

The number of fragment oscillate between 2 and 20 at high frequency
(multiple times per minutes);
The biggest fragment contains ~105 rows and is 11Mb;
The others contains from 1 to 40 rows.

Can this oscillating number of fragment mess up SQL Server's execution plan selection?

What can I do to clean up this?


texts: FLUSH TABLE : can't execute the given command because you have active locked tables mysql

texts:
If I execute these queries one by one, all fail except the first:

Query1 :

flush table tablename1 with read lock;

Works

Query2 :

flush table tablename2 with read lock;

  Error :  can't execute the given command because you have active locked tables mysql

If I execute, in below way, it works:

Query:

flush table tablename1,tablename2 with read lock;

What's happening inside?


texts:
Let's suppose a database table such as this:

Table = {Competition, Sport, Game, Player, Score}

And we have these functional dependencies:

Competition + Game + Player -> Score

Game -> Sport

Player -> Sport

So for the table, the primary key: Competition+Game+Player. Let's apply second normal form with the second dependency above. The result is:

Scores = {Competition, Game, Player, Score}

Games = {Game, Sport} (primary key: Game)

But we have missed the information about the sport that each player practices.

What am I doing wrong? Thank you in advance.


texts: SQL Query with optional WHERE clause parameter for date range?

texts:
I have a procedure that takes, among other things,  start and end date parameters. These two parameters are optional inputs and default to NULL. What I would like to do is modify one of the current SELECT statements so that if the start and end dates are supplied it will return only the results between those two dates. I considered just throwing a CASE WHEN... in the WHERE statement but that that is messy and does not seem right. Is that the appropriate way to deal with this or is there something more efficient?

Existing SELECT clause:

SELECT  do.OrdrNmbr AS 'Order Number',
        mb.BOLNmbr AS 'BOL Number',
        do.DlvryDt  AS 'Deliver Date',
        mh.Text3 AS 'Truck',
        do.DlvrdQntty AS 'Gallons Delivered',
        ba.BANme AS 'Carier Business Associate'
FROM dbo.DeliveryOrder AS do WITH (NOLOCK)
INNER JOIN dbo.ManifestBOL AS mb WITH (NOLOCK)
    ON do.OrdrNmbr = mb.OrdrNmbr
INNER JOIN dbo.BusinessAssociate AS ba WITH (NOLOCK)
    ON do.CrrrBAID = ba.BAID
INNER JOIN SRA.dbo.MovementDocument as md WITH (NOLOCK)
    ON md.MvtDcmntExtrnlDcmntNbr = 'DOD' + CONVERT(VARCHAR, mb.OrdrNmbr)
INNER JOIN SRA.dbo.MovementHeader AS mh WITH (NOLOCK)
   ON mh.MvtHdrMvtDcmntID = md.MvtDcmntID
WHERE mb.OrdrNmbr = @OrderNumber;

and the start/end dates would run against do.DlvryDt.

P.S. I know the table/column names are horrid but this is a 3rd party application the query is running against.


texts: Database design problem: where to put UNIQUE constraint

texts:
I have the following three tables:

Form
-----------
formId INTEGER       PK
name   NVARCHAR(50)  NOT NULL

Block
-----------
blockId     INTEGER        PK
name        NVARCHAR(30)   NOT NULL
orderInForm INTEGER        NOT NULL

FormBlock
-----------
blockId  INTEGER  PK
formId   INTEGER  PK

There is a many-to-many relationship. A block could appear on multiples forms. orderInForm is for a particular form.

On Block table I want to avoid that two blocks have the same orderInForm in a Form.

I think I have to add an UNIQUE constraint, but if orderInForm is on Block table, I don't know how to do it.

May I need to move orderInForm to FormBlock table?


texts: Find the last visit by teacher and show the rest of columns in MySql

texts:
I would like to group the teachers by the last visit and show the other values corresponding to the last visit.

CREATE TABLE IF NOT EXISTS `visits` (
`FIELD1` int(5) NOT NULL,
`teacher` varchar(9) DEFAULT NULL,
`visit_time` int(5) DEFAULT NULL,
`1A` varchar(1) DEFAULT NULL,
`2A` varchar(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I used this query

 SELECT i1.* FROM visits AS i1 LEFT JOIN visits AS i2
ON (i1.teacher = i2.teacher AND i1.visit_time < i2.visit_time) WHERE i2.visit_time IS NULL

The query in general gives correct results, but in the end there are more teachers than expected (for example when using group by teacher).


texts: MySQL: Can't find .ibd files after turning on innodb_file_per_table

texts:
Question:

This a question about MySQL.  Let me give the specifics of my setup:

Mac OSX 10.6.8
MySQL 5.5.28
Workbench 5.2.44 for all administration
InnoDB engine exclusively

In order to manage limited hard drive space on my laptop, I'd like to turn on the innodb_file_per_table option so that each table gets its own .ibd file instead of all data going into the ibdata1 file.  I researched various sources, including Stackoverflow, and they all suggest the same basic steps:

export existing tables
turn off MySQL server
add innodb_file_per_table under the [mysqld] heading of the my.cnf file
delete ibdata1, ib_logfile0, and ib_logfile1 - which for me are located in /usr/local/mysql/data
restart MySQL server
at this point, newly created tables or tables imported from the original dump should each get there own .ibd file.

I followed the steps and there appears to be no problem.  Various checks that I did within Workbench all indicate that the innodb_file_per_table option is turned on.   However, when I create new tables or import the old tables, I'm not seeing any new .ibd files, and it seems like ibdata1 is growing.

When I create/import a myschema, the following directory is created:

/usr/local/mysql/data/myschema

It is my assumption that if I create myschema.table1 that a the file table1.ibd will be added to the directory above, and that all the data associated with that table will be stored in this file.  However, I can't check this assumption because in both Console and Finder, I get "permission denied" messages when I try to look inside the myschema directory.  Moreover, in Finder, the folder icon has a red/white minus sign on it.

Resolution & Observations:

It turns out that my error was a simple one, and that the steps above to get innodb_file_per_table are correct. My issue was simply that I didn't have permissions to /usr/local/mysql/data/myschema - this was indicated by the red minus sign on the folder icon - which is easily resolved in Finder (for details just Google "folder with red minus sign mac").

Here are some things I learned along the way:

Even with innodb_file_per_table turned on, ibdata1 does still grow (and doesn't shrink) as you add data, but at a much slower rate that the actual table.  This makes sense because the Innodb engine still needs this file, even when innodb_file_per_table is turned on, so things should be written to it.
My implementation works even though I don't include the line innodb_data_home_dir = /usr/local/mysql/data in the my.cnf file.  However, I do have datadir = /usr/local/mysql/data - but this must have been a default since I didn't set it.


texts:
We have a new production box with two SQL servers,SQL Server 2016 Standard edition.
The current plan is to have:

One primary SQL server
Backup SQL server:only SQL Report server,which points to primary server.

Can you please suggest any good replication strategy to move data from primary to back up server and which does not use Single User mode.
Realtime replication is expected.

In primary server lots of insert/update happens and will be online 24/7.
New into Replication.

Only one database in Primary SQL server,have to replicate the same db tables/views/triggers etc  into Backup server.

Have done some research on replication(Snapshot,Transaction,Merge),but i am not sure about the Single User mode thing in this context.


texts: Query plan (JSON format) in pgAdmin not showing completely

texts:
I would like to use the tool Postgres Explain Viewer (PEV). For it I need to paste the execution plan in json format. If I try to get the execution plan in pgAdmin with this query:

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
SELECT DISTINCT key
FROM test.table

I don't get the whole json:

query plan

Can I get the whole json? Could I export it from the SQL editor?


texts: Create a key-preserved view across Oracle tables with descending indexes

texts:
I'm trying to create an updatable view between two Oracle tables which have exactly the same key structure. The existing tables are created by an ERP system and:

Do not have primary keys - only a unique index.
All fields are non-null, but one of the composite index fields is descending.

Is there any way I can create an updatable view across these two tables?

The real tables concerned contain a lot of data and are heavily used, so any changes / additions to the indexes would require significant regression testing, so I need to confine any solution to the view itself.

Problem seems to be something to do with Oracle creating separate composite function based indexes for the descending KEY2 fields. I've tried throwing a few hints into the view but am out of my depth here, and really not much idea what I'm doing.

Here's a simplified example. If I remove the "desc" from the two KEY2 index fields the final update works - if I leave it in it fails with: "ORA-01779: cannot modify a column which maps to a non key-preserved table".

create table TEST_KEY_PRES_A (
   KEY1 varchar2(8) not null,
   KEY2 varchar2(8) not null,
   VAL1 smallint not null,
   VAL2 varchar2(8) not null
)
;

create unique index TEST_KEY_PRES_A_IDX
on TEST_KEY_PRES_A (KEY1, KEY2 desc)
;

insert into TEST_KEY_PRES_A values ('K11', 'K21', 1, 'V2-1');
insert into TEST_KEY_PRES_A values ('K15', 'K25', 5, 'V2-5');

create table TEST_KEY_PRES_B (
   KEY1 varchar2(8) not null,
   KEY2 varchar2(8) not null,
   VAL3 varchar2(8) not null
)
;

create unique index TEST_KEY_PRES_B_IDX
on TEST_KEY_PRES_B (KEY1, KEY2 desc)
;

insert into TEST_KEY_PRES_B values ('K11', 'K21', 'V3-1');
insert into TEST_KEY_PRES_B values ('K15', 'K25', 'V3-5');

create view TEST_KEY_PRES_VW (KEY1, KEY2, VAL1, VAL2, VAL3) AS
select pa.KEY1, pa.KEY2, pa.VAL1, pa.VAL2, pb.VAL3
from   TEST_KEY_PRES_A pa
join   TEST_KEY_PRES_B pb on pa.KEY1 = pb.KEY1 and pa.KEY2 = pb.KEY2
where  pa.VAL1 > 3
;

update TEST_KEY_PRES_VW
set    VAL2 = 'V2-5-X'
where  KEY1 = 'K15'
;

SOLVED

Using INSTEAD OF trigger as suggested by Eduard:

create or replace trigger TEST_KEY_PRES_VW_UPD_TR
instead of update on TEST_KEY_PRES_VW
for each row
begin
    update TEST_KEY_PRES_A
    set    VAL1 = :new.VAL1, VAL2 = :new.VAL2
    where  KEY1 = :new.KEY1 and KEY2 = :new.KEY2;

    update TEST_KEY_PRES_B
    set    VAL3 = :new.VAL3
    where  KEY1 = :new.KEY1 and KEY2 = :new.KEY2;
end;


texts: Mongodb crash after WiredTiger Cannot allocate memory error

texts:
I am using MongoDB (3.2.6) as storage for my application and since a couple of weeks MongoDB started to crash usually every 1 or 2 weeks. The crash seems to come from an issue during WiredTiger memory allocation here is the full stacktrace of the last crash:

2016-10-09T18:25:36.389+0200 E STORAGE  [thread1] WiredTiger (12) [1476030336:325695][993:0x7f559c19a700], file:index-1108-8065571460375661294.wt, WT_SESSION.checkpoint: memory allocation of 18624 bytes failed: Cannot allocate memory
2016-10-09T18:25:36.547+0200 E STORAGE  [thread1] WiredTiger (12) [1476030336:422567][993:0x7f559c19a700], file:index-1108-8065571460375661294.wt, WT_SESSION.checkpoint: checkpoints cannot be dropped when in-use: Cannot allocate memory
2016-10-09T18:25:37.347+0200 I COMMAND  [conn10793] command admin.$cmd command: ping { ping: 1 } keyUpdates:0 writeConflicts:0 numYields:0 reslen:37 locks:{} protocol:op_query 208ms
2016-10-09T18:25:37.386+0200 E STORAGE  [thread1] WiredTiger (12) [1476030337:254969][993:0x7f559c19a700], checkpoint-server: checkpoint server error: Cannot allocate memory
2016-10-09T18:25:38.448+0200 E STORAGE  [thread1] WiredTiger (-31804) [1476030337:484838][993:0x7f559c19a700], checkpoint-server: the process must exit and restart: WT_PANIC: WiredTiger library panic
2016-10-09T18:25:38.677+0200 I -        [conn10894] Fatal Assertion 28559
2016-10-09T18:25:38.994+0200 I -        [WTJournalFlusher] Fatal Assertion 28559
2016-10-09T18:25:38.994+0200 I -        [TTLMonitor] Fatal Assertion 28559
2016-10-09T18:25:40.978+0200 I -        [thread1] Fatal Assertion 28558
2016-10-09T18:25:41.207+0200 I -        [thread1]

The server it's running on has 7Gb RAM and is exclusively used for Mongo.

free -m
             total       used       free     shared    buffers     cached
Mem:          6811       6140        670          0         28        634
-/+ buffers/cache:       5478       1332
Swap:         2047       1009       1038

Several dbs are running on it with a total size around 925MB while WiredTiger cache status is :

db.serverStatus().wiredTiger.cache
"bytes currently in the cache" : 969927022,
"bytes read into cache" : 800513540,
"bytes written from cache" : 1349774879,
"maximum bytes configured" : 3221225472,
[...]

According to the documentation, WiredTiger should use by default

  60% of RAM minus 1 GB, or [...] https://docs.mongodb.com/manual/faq/storage/#to-what-size-should-i-set-the-wiredtiger-cache

Based on 7GB RAM we get ~ 3.2Gb so it seems good.

Now how can 925Mb databases can bust a 3.2Gb cache ? I know that we have to take the indexes into account but even by indexing every fields (which is not the case) it should not fill the 2+ Gb remaining ? Last crashed occurred yesterday so the provided stats are just after a fresh restart. Could it be that the way I'm using Mongo a memory leak occurs somewhere slowly increasing the memory consumption ? I may completely miss the point as I am new to Mongo so any leading advice or article would be a relief.

I found this issue : https://jira.mongodb.org/browse/SERVER-24408 but it does not provide any further explanations.

EDIT 12/10:

3 days later the cache status is the following :

"bytes currently in the cache" : 1319874144,
"bytes read into cache" : 1054001990,
"bytes written from cache" : 8936911271,


texts: Identity value jumps when restarting SQL Server

texts:
We just switched from SQL Server 2008 R2 to SQL Server 2012. I am facing a problem with identity columns:

Whenever I restart SQL Server, the seed value for each identity column is increased by 1000 (for int identity columns it is 1000 and for bigint it is 10,000). For example, if the next int identity value for a table was 3, after restarting SQL Server it will be 1003. If I again restart SQL Server, it will be 2003 and so on.

After searching Google, I found that it is a new feature (don't know what is use of it) in SQL Server 2012, having only two solutions if you want the old identity behaviour:

Use a sequence object

This is not possible for me because:

a) I am using the same database in SQL Server 2008 and 2012. I can't use sequence in 2008.

b) If I go with sequence then I need to change the save procedure for each table, which would be a bulky task for us.
Use Trace Flag 272 (-T272)

I can go with this solution because there is no need to make any changes to my application. Someone suggested adding -T272 as a start-up parameter, after this SQL Server identity will work as in previous versions. I did the same but it is not working.

I don't want to make any changes to my database structure. Please suggest solutions or explain why -T272 is not working.


texts: Is there a way to export an Oracle database to a CREATE DATABASE command?

texts:
I have an existing Oracle 11 instance, I want to export the database (which was created via DBCA) to an equivalent "CREATE DATABASE" command, handling the character set, etc. I'd also like to get a list of the datafiles and tablespaces that would be needed to create the same database. I'm not interested in preserving individual schema objects.

I ask because there's the DBMS_DDL package which will give you the DDL to re-create any table, etc. Wondering if there's something similar at a database level.

Thanks!


texts: Using Oracle 12c database (SCAN) with Oracle 8 client

texts:
I have successfully connected to my 12c database using Oracle 8 client. The connection is based on using the database-scan as host in tnsnames.ora.

There are 3 IP addresses reserved in DNS for scan...

However I read in oracle documentation that:

If using a pre-11g Release 2 client (Oracle Database 11g Release or Oracle Database 10g Rel. 2, or older) you will not fully benefit from the advantages of SCAN. Reason: The Oracle Client will not be able to handle a set of three IPs returned by the DNS for SCAN. Hence, it will try to connect to only the first address returned in the list and will more or less ignore the others. If the SCAN Listener listening on this specific IP is not available or the IP itself is not available, the connection will fail. In order to ensure load balancing and connection failover with pre-11g Release 2 clients, you will need to change the TNSNAMES.ora of the client so that it would use 3 address lines, where each address line resolves to one of the SCAN VIPs.

...so even though my application is connecting it might not be running optimally and using only 1 IP address in the SCAN.

My questions are:

What would cause the SCAN listener listening on this IP to be unavailable? How likely is it to happen?

What would cause the SCAN IP to be unavailable? How likely is this to happen?


texts:
Using MySQL, I need to query a table that has a datetime field. I need to count all the records for a particular week. Using date_format(colname, "%Y %V") returns the week #, but I need the last day (day of month 01-28|29|30|31) for that week.

Output E.g.

    COUNT(*) | TheDate
    11       | 2012-01-07
    22       | 2012-01-14
    123      | 2012-01-21

Help?

Note: Last day of the week in my case is Sunday.


texts: What are the circumstances where autovacuum can be disabled?

texts:
I have a table where I am performing only inserts, never deletes/updates. I notice sometimes that autovacuum runs on this table, even though this is the case.

autovacuum: VACUUM ANALYZE public.twitter_shares (to prevent wraparound)

It is taking a long time, and it is having an impact on the performance of my DB. Is it safe for me to just disable autovacuum for this table? Since I am not performing deletes/updates, I don't understand why autovacuum is even needed, and why postgres decides to run it.


texts: Failure during install of SQL Server 2016 sp2

texts:
I'm trying to install the new SP2 for SQL Server 2016, but it fails during the install with an Engine error.
This is what I can read in the errorlog>

Error: 468, Severity: 16, State: 9.
Cannot resolve the collation
conflict between "Latin1_General_CI_AS" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation. The failed
batch of t-sql statements :
CREATE PROCEDURE
sp_sqlagent_get_perf_counters   @all_counters BIT = 0 AS BEGIN ...
Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step
'msdb110_upgrade.sql' encountered error 200, state 7, severity 25.
This is a serious error condition which might interfere with regular
operation and the database will be taken offline. If the error
happened during upgrade of the 'master' database, it will prevent the
entire SQL Server instance from starting. Examine the previous
errorlog entries for errors, take the appropriate corrective actions
and re-start the database so that the script upgrade steps run to
completion.
Error: 3417, Severity: 21, State: 3.
Cannot recover the master database. SQL Server is unable to run.
Restore master from a full backup, repair it, or rebuild it. For more
information about how to rebuild the master database, see SQL Server
Books Online.
SQL Server shutdown has been initiated

The master database was running "Latin1_General_CI_AS" and MSDB (and other dbs) was running "SQL_Latin1_General_CP1_CI_AS".
I have new backups of all databases on the server including master and msdb.
I'm tempted to try to restore the master master as shown in https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-the-master-database-transact-sql?view=sql-server-2017
But I'm trying ensure that it will actually work, and not just make the situation worse.
How do I get my SQL Server back up and running?
EDIT:
I've started the mssqlserver service like this:
NET START MSSQLSERVER /T902

This gets the server started, and I've looked into msdb110_upgrade.sql, and found quite a few missing "COLLATE DATABASE_DEFAULT"s which I've added.
But it is not taking my changes.


texts: Will composite indexes or unique indexes help with query performance?

texts:
I need help with identifying indexes to increase performance of the following query. I'm currently using v5.5 with InnoDB. I cannot modify the query. However, I can add indexes as needed. The following indexes are currently available:

t1.id = Primary key
t1.status
t2.owner
t2.user
t2.total

select t1.`id`
from (tbl1 t1 inner join tbl2 t2 on t1.`id` = t2.`id`)
where (t2.`owner` = `ABC123` or t2.`user` = `ABC123`)
  and t1.status = 1
order by t2.`total`
limit 0, 100;


texts: How to fix issue uuid_generate_v1() does not exist in postgresql , actually i have the function under public schema

texts:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from da.test_get(cast(? as integer),cast(? as text),cast(? as text),cast(? as text),cast(? as text))]; nested exception is org.postgresql.util.PSQLException: ERROR: spiexceptions.UndefinedFunction: function uuid_generate_v1() does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Where: Traceback (most recent call last):


texts:
I want to create a BCP format file from within SSMS. Is there anyway to do this? All the sources are asking to use the BCP command line. I would rather utilize in SSMS window.

https://docs.microsoft.com/en-us/sql/relational-databases/import-export/create-a-format-file-sql-server?view=sql-server-2017


texts:
I have a 400GB Postgres 9.3.4 DB running on Fedora20, and a few indices per table. I have upped shared buffers to 4GB (the system has 16GB available), set shm in the kernel and set the effective_cache_size to 12GB. work_mem is 60MB

Then i'm running hundreds of queries per second against the DB for prolonged period. My entire system (with OS) is only using 3GB of RAM and no swap.

Postgres isn't really using the RAM it could be using and I'd rather max out my RAM and gain query/index performance if possible.

Am I missing something config wise?


texts: How to insert Error_Message using EXECUTE sp_executesql

texts:
Using MS SQL Server I have the below code - it is being executed via a EXECUTE sp_executesql command:

Declare @Cmd nvarchar(max)
;with DistinctTables as
(
select distinct [DestTable], [SourceFile] from [tbl_IN_Ctrl_Dtl]
),
InsertCommands as
(
-- columns from Destination table
select *,
'BEGIN TRY insert into ' + [DestTable] + '(' +
STUFF((
        SELECT ',' + [DestCol]
        FROM [tbl_IN_Ctrl_Dtl] t1
        where t1.DestTable = drt.DestTable
        GROUP BY DestCol,ORDINAL_POSITION
        ORDER BY ORDINAL_POSITION
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' +
' select ' +
-- columns from source table
STUFF((
        SELECT ',' + [SourceCol]
        FROM [tbl_IN_Ctrl_Dtl] t1
        where t1.DestTable = drt.DestTable
        GROUP BY SourceCol,ORDINAL_POSITION
        ORDER BY ORDINAL_POSITION
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
        + ' from ' + [SourceFile]
        + ';
        update tbl_IN_Ctrl_Header set ErrMsg = @@ERROR where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ ''''
        +';
        update tbl_IN_Ctrl_Header set SourceFile_INSERT = 1 where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ ''''
        +'; END TRY BEGIN CATCH'
        as InsertCommand
 from DistinctTables drt
)
select @cmd =
STUFF((
        SELECT ';' + char(10) + [InsertCommand]
        FROM InsertCommands
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+';' from InsertCommands ic

--print @cmd

EXECUTE sp_executesql @cmd

I can retrieve the error number using line:

update tbl_IN_Ctrl_Header set ErrMsg = @@ERROR where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ ''''

However, how do I retrieve the actual text error message?  If I use ERROR_MESSAGE I just get Null.  How do I modify the above to update 'ErrMsg' to the actual text error message?  I've put a BEGIN TRY at the beginning of the Insert and END TRY at the end of the UPDATE but I now receive the error Incorrect syntax near ';'.


texts: How can I generate an .mdf file containing a subset of my tables and rows?

texts:
I need to provide some data to an organisation (lets call them DataCo) that we're working with. Since we're both SQL server shops, my counterpart at DataCo requested an .mdf file.

However, our database contains many tables that DataCo don't need. Moreover, they're only interested in rows from the last X years. For many reasons (inc. file size) I'd like an .mdf file containing only the data that DataCo need.

Is this possible?


texts: How can I disable the notifications shown at bottom right corner in pgAdmin 4?

texts: The notification window at bottom right corner is pesky. Is it possible to disable it?


texts:
I am looking at an example of correlated subquery with aliases from a PostgreSQL book:

bpsimple=# SELECT * FROM orderinfo o,
bpsimple=# (SELECT * FROM customer c WHERE town = 'Bingham') c
bpsimple=# WHERE c.customer_id = o.customer_id;

Why is the c alias used twice here? Isn't the one inside the parenthesis enough?


texts: Data modeling with many attributes where few apply to each record

texts:
I'm pretty new to data modeling, so please excuse me if I've missed something obvious. I'm trying to decide on a schema (or possibly a DBMS) to work with some data where there are quite a few possible attributes (60-100 maybe), but only a handful will apply to each record. Here are my options as I see them.

Option 1: Very wide table

Just include each of the attributes as columns and most will be NULL for any given record.

Pros: keeps schema simple, keeps queries simple

Cons: inelegant, unwieldy, adding/removing properties requires a schema change

Option 2: Entity-attribute-value model

Separate table(s) for listing attributes and their values. This is how I've seen this problem handled before. For example, WordPress uses something like this for their *meta tables.

Pros: easy to understand conceptually, flexible

Cons: query construction becomes a pain, performance suffers, you wind up with a lot of duplicated data (i.e. the same property name is listed over and over).

Option 3: NoSQL Document or Graph DBMS

Go with a DBMS that's built to handle schema-less data. I've looked at MongoDB, RethinkDB, and OrientDB. OrientDB looks really cool, but I don't know Java, and it seems like it's primarily a Java thing (the PHP driver, for example, looks problematic).

Pros: built to handle flexible schemata, fast

Cons: relationships seem difficult, I have no experience with them, seems like a lot of duplicate data (e.g. what happens if I want to change the name of a property?), querying seems way more complicated

As a side note, this application will not get very much traffic, and will have very few concurrent connections. So, scalability is a fairly minor concern. Thanks for any suggestions.


texts: Alternatives to Linked Servers (Allow Inline SQL with Parameters)

texts:
I am trying to find a solution to execute inline SQL with parameters against some linked server databases. From what I have found online the only proper way to query a linked server is to use OPENQUERY. And the only proper way to prevent SQL injection is with parameters.

However, the way OPENQUERY is structured it does not look like parameters are possible. Am I right to assume the only proper way to query with this type of configuration is with stored procedures? Are there any alternatives? Are there any similar database configuration that could be used?

Any thoughts or advice on the subject would be greatly appreciated.


texts:
The title doesn't make too much sense, but I couldn't think a better title for this problem.

I have the following tables

Projects

id
name

Customers

id
id_project
name

Payments

id
id_customer
date
sum

When a users enters the system, he will have access to a certain project. Now, I want to list all the payments for that project, and it should be pretty easy:

SELECT FROM payments where id_customer in (SELECT id from customers where id_project = 5)

My question is : if it isn't better to add a column id_project to payments table this way the queries will be easier and faster.


texts: Cannot find the object because it does not exist

texts:
I have two databases DB_A and DB_B. From DB_A the developers are calling a stored procedure that will truncate table_a in DB_B. When they execute the stored procedure using the login from the application, we will call this "app_user", it returns the error message. Cannot find object "" because it does not exist or you do not have permissions.

Now my first instinct is to check the permissions for app_user on both DB_A and DB_B. The user is a db_owner on the latter. I ran the sp_change_users_login to see if the database user was orphaned, and it was not. I am not exactly sure why I am still getting this error. Any help will be appreciated.


texts: How can I move data from one filegroup to another?

texts:
I have 10 million rows in a table, however the table is created on the primary filegroup.

All the indexes and primary keys were created in the primary filegroup.

Is there any way to move the table, indexes and primary keys to a user-defined filegroup without loosing the data in the table?


texts: Fixing a query that returns failed job information

texts:
I' using a SQL Server 2008 Sp3 environment.

I have a query that returns information about failed jobs from the last 10 minutes.
If a job has two schedulers (for example - a job that runs every 10 minutes in the morning, and every 30 minutes in the rest of the day), the query returns two rows.
Can you help me fix the query so it would return only the relevant row of the relevant scheduler that was used ?

The query I'm using is pasted below:

SELECT h.server
,j.name
,h.step_id
,h.step_name
,h.message
,h.run_status
,Rundatetime        = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
,h.run_duration
,s.last_outcome_message
,sc.name AS ScheduleName
,s.last_run_time
,sjc.next_run_time
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb..sysjobservers  s ON s.job_id=h.job_id
LEFT OUTER JOIN msdb..sysjobschedules  sjc ON sjc.job_id=h.job_id
LEFT OUTER JOIN msdb..sysschedules  sc ON sc.schedule_id=sjc.schedule_id
WHERE run_status=0
AND step_name<>'(Job outcome)'
--Looking on failed job from the last 10 minutes.
AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
ORDER BY h.run_date DESC ,h.run_time DESC

Thanks in advance,
Roni.


texts: How to free used space from disabled index?

texts:
I've come across a need to free used space from a large number of disabled indexes.

Is it possible to make SQL Server release the used space from these indexes without a drop/create for each index?

And I'm limited to SQL-Server 2014 functionality :/


texts: I'm getting a PL/SQL: unhandled user-defined exception error but there's no error

texts:
We have a .net application that suddenly started hanging. The log indicates the error is an unhandled user-defined exception and gives a line number. We can't find any error in the function, yet it hangs indefinitely. We have had to kill the process in the past but we don't think we killed this one. Trace log shows:

errors in file
/u01/app/oracle/diag/rdbms/ccccc/ccccc/trace/prdccccc_ora_22685.trc
(incident=52744):
ORA-00603: ORACLE server session terminated by fatal error
ORA-24557: error 6510 encountered while handling error 28; exiting server process
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "FUNCTIONNAME", line 13
ORA-06512: at line 1
ORA-00028: your session has been killed
Incident details in: /u01/app/oracle/diag/rdbms/ccccc/ccccc/incident/incdir_52744/prdccccc_ora_22685_i52744.trc
Fri Oct 14 17:30:41 2016
Dumping diagnostic data in directory=[cdmp_20161014173041], requested by (instance=1, osid=22685), summary=[incident=52744].
Fri Oct 14 17:30:41 2016
opiodr aborting process unknown ospid (22685) as a result of ORA-603
Fri Oct 14 17:30:41 2016
Sweep [inc][52744]: completed
Sweep [inc2][52744]: completed
Fri Oct 14 17:43:05 2016
Memory Notification: Library Cache Object loaded into SGA
Heap size 51200K exceeds notification threshold (51200K)
Details in trace file /u01/app/oracle/diag/rdbms/ccccc/ccccc/trace/prdcrccf_ora_27648.trc
Fri Oct 14 17:43:05 2016
KGL object name :2387355888921555146 DEFAULT

This is the function containing the "error"

CREATE OR REPLACE
FUNCTION
  fnGetCarriersForGroup(pGroupID IN INTEGER DEFAULT NULL)
  RETURN IDsTable
  PIPELINED
  AS
             lRow IDs;
             CURSOR cCarriers IS SELECT * FROM vwCarrierGroups
                                      START WITH ID=NVL(pGroupID,0)
                                      CONNECT BY NOCYCLE PRIOR ID = ParentID;
  BEGIN
             lRow := IDs(NULL, NULL, NULL, NULL);

             FOR cRow IN cCarriers LOOP
                         IF (cRow.EntityID IS NOT NULL) THEN
                                      lRow.ID := cRow.ID;
                                      lRow.GroupName := cRow.GroupName;
                                      lRow.EntityID := cRow.EntityID;
                                      lRow.ParentID := cRow.ParentID;

                                      PIPE ROW(lRow);
                         END IF;
             END LOOP;

RETURN;
END;


texts: Restore partial data from single file of SQL Server Full Strip Backup

texts:
I don't know if I ask for something possible, but here is my problem:

We had a disaster (virus) in our SQL Server Database and we lost all the information and backups.

The only backup remaining is a single file of a Full Backup in strip mode (the one that splits a single backup in multiple files).

Logically, when I try to restore the database using that file I get the error:

  The media set has 2 media families but only 1 are provided. All
  members must be provided

Is there any way to recover some partial information from that piece of the backup? either using SQL or third party software.

Even if I just manage to get some information of a single table it would be more than none.


texts: How do you fix Autogrowth being set to 12800%

texts:
We're running SQLServer 2005 SP1 and for some weird reason our one database transaction log automatically changed it's Autogrowth to 12800% which isn't even a valid number. I cannot change the growth % because of the invalid number.

I've scoured the Internet and saw a lot of other people also had this problem but with no clear solutions.

What caused this to happen and how can I fix it?


texts:
I've setup a mysql replica slave a couple months ago. After a couple hickups lately, I've decide to restore it from a clean master export.

I'm trying to start my mysql instance with this command :

mysqld_safe --defaults-file=/etc/mysql/my_secondary.cnf --server-id=2 --log-bin --skip-slave-start --skip-name-resolve&

I've declared the server-id in my cnf file as the following :

server-id = 2

But when I connect with the command line client, and I check the variable, I get :

mysql> show variables like "version";
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| server-id     | 0                       |
+---------------+-------------------------+
1 row in set (0.00 sec)

I'm also getting the feeling that my instance is ignoring my "CHANGE MASTER" commands since it's complaining each time i run "start slave", but I would like to settle this server-id issue first.

I'm running two instance of Mysql 5.1.61-0ubuntu0.10.04.1 (one on port 3306, and this one on 4306)


texts: Oracle listener does not restart because port 1521 is not released fast enough after stop

texts:
I'm running Oracle 11g on a Windows 2012R2 server. For some reason we have some code that try to restart the listener and then perform a connection. This code has been working for years on many different servers but on one particular server, the listener cannot restart.
Here is the error found in the listener.log file when the listener tries to restart:
Started with pid=5132
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=thehostname)(PORT=1521)))
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   64-bit Windows Error: 48: Unknown error
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))

I've managed to reproduce the issue using this command:
net stop OracleOraDb11g_home1TNSListener && net start OracleOraDb11g_home1TNSListener

If I modify the command line like this:
net stop OracleOraDb11g_home1TNSListener && sleep 30 && net start OracleOraDb11g_home1TNSListener

The listener restarts correctly.
I suppose that the port 1521 is not released fast enough after stop, but I don't know how to fix it.
For information here is the content of the listener.ora file:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\somepath\database\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = somevalue)
      (SID_NAME = somevalue)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = thehostname)(PORT = 1521))
      )
    )
  )

LOGGING_LISTENER=on
VALID_NODE_CHECKING_REGISTRATION_LISTENER=on


texts: SQL Server 2012 x64 - cannot safely allocate more than 50% RAM

texts:
We're running SQL Server 2012 SP1 CU4 on a 64-bit 12-core, 2 socket server using NUMA architecture server. OS is Windows Server 2008 R2 x64.

Whenever we allocate more than 50% of the physical RAM to SQL server, the machine becomes unstable or unresponsive.

The symptoms are typical of OS memory starvation - i.e. process fail to start, GUI object fail to render, applications misbehave, remote desktop sessions become unresponsive, etc.

We’ve seen this on 2 different machines – one with 192GB RAM, the other with 256GB RAM…  as soon as SQL is given more than 50% of the respective physical total, the symptoms appear.

Has anyone else seen this behavior?

--- EDIT ---

The SQL service runs under an account with LPIM (Lock Pages in Memory) privileges.

McAfee antivirus is, sadly, imposed on the server, though it is at least set up with exclusions for all SQL files.

When the RAM utilization is allowed to exceed 50%, the common behavior we see is:

High RESOURCE_SEMAPHORE wait times.
Inability to connect to SQL server or run queries.
All CPUs maxed out with low disk I/O when running query workloads.

-- EDIT 2 ---

We've tried re-installing SQL (SP1 CU4),  and have checked that nothing else is chomping the RAM. Generally, there's at least 100GB of the total 256GB free at any time.
When we turn off LPIM, we get see the problem "the operating system has swapped at a significant portion of SQL's memory", which is why we have it turned on.


texts: How to BCP export three related tables in a transaction consistent manner?

texts: Have three related tables that I would like to bcp export to file(s).  This three tables have relationships to each other.  Is there a good way to bcp export these tables in a transactionally consistent manner?


texts: Query optimization, SQL Server cache query, SQL Server execution query

texts:
I have 2 queries, the results of their execution is the same, but I don't know which of these is the best in terms of performance and why.

Query #1:

SELECT
CASE
WHEN ( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
    (SELECT
        COUNT(1) AS [A1]
        FROM  [dbo].[CompanyElement] AS [Extent2]
        INNER JOIN [dbo].[Company] AS [Extent3] ON [Extent2].[CompanyID] = [Extent3].[CompanyID]
        WHERE ([Extent1].[ElementID] = [Extent2].[ElementID]) AND (36 = [Extent3].[GroupID])) AS [C1]
    FROM [dbo].[Page] AS [Extent1]
)  AS [Project1]
WHERE 2 = [Project1].[C1]))THEN cast(1 as bit)
WHEN ( NOT EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
    (SELECT
        COUNT(1) AS [A1]
        FROM  [dbo].[CompanyElement] AS [Extent5]
        INNER JOIN [dbo].[Company] AS [Extent6] ON [Extent5].[CompanyID] = [Extent6].[CompanyID]
        WHERE ([Extent4].[ElementID] = [Extent5].[ElementID]) AND (36 = [Extent6].[GroupID])) AS [C1]
    FROM [dbo].[Page] AS [Extent4]
)  AS [Project3]
WHERE 2 = [Project3].[C1])) THEN cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

Query #2:

SELECT [GroupBy2].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM ( SELECT
    (SELECT
        COUNT(1) AS [A1]
        FROM  [dbo].[CompanyElement] AS [Extent2]
        INNER JOIN [dbo].[Company] AS [Extent3] ON [Extent2].[CompanyID] = [Extent3].[CompanyID]
        WHERE ([Extent1].[ElementID] = [Extent2].[ElementID]) AND (36 = [Extent3].[GroupID])) AS [C1]
    FROM [dbo].[Page] AS [Extent1]
)  AS [Project1]
WHERE 2 = [Project1].[C1]) AS [GroupBy2]


texts: Export Wizard never starts Pre-executing

texts:
I'm using the Import/Export Wizard in SSMS 2012 to store some data into a flat file, as I have to move these data from one SQL server to another. I've tried with direct connecting to the server through the export wizard but it fails after som time when the export begins due to some connection-timeout, therefor I stick to the flat-file method. Even if the direct-connection method is really nice.
I've been using the flat-file method with an row-count of ~92+ million rows, but now I'm reaching 1.1 billion (calculated) and the Executing and Pre-Executing phase in the Export wizard doesn't seem to cooperate at all. I've started to export 3 hours ago, and it's still looking as you see on the picture below. Any Idea?

I have seen similar waiting-time for the Pre-execute and executing phase to get done, where it started after 10-20 minutes, and the rows started to count in the pre-execute phase, but 3 hours, I've never tried that before.


texts: Selecting data from a mapping table using a foreign key

texts:
I'm trying to make a simple Login sys where each user should be shown data from a list depending on their user ID Using MS SQL.
After searching for how to do it best i came across Mapping (pretty much making a 3d Table containing both USR_ID and Element_ID, both of course being forign keys)
CREATE TABLE [dbo].[Users] (
[USR_ID]        INT          IDENTITY (1, 1) NOT NULL,
[USR_UserName]  VARCHAR (50) NOT NULL,
[USR_Password]  VARCHAR (50) NOT NULL,
[USR_FirstName] VARCHAR (50) NOT NULL,
[USR_LastName]  VARCHAR (50) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([USR_ID] ASC));

CREATE TABLE [dbo].[GameList] (
[GL_ID]          INT             IDENTITY (1, 1) NOT NULL,
[GL_Title]       NVARCHAR (MAX)  NOT NULL,
[GL_Genre]       NVARCHAR (MAX)  NOT NULL,
[GL_Plattform]   NVARCHAR (MAX)  NOT NULL,
[GL_ReleaseDate] DATETIME2 (7)   NOT NULL,
[GL_Price]       DECIMAL (18, 2) NOT NULL,
CONSTRAINT [PK_GameList] PRIMARY KEY CLUSTERED ([GL_ID] ASC));

CREATE TABLE [dbo].[UserGame] (
[UG_ID]  INT IDENTITY (1, 1) NOT NULL,
[USR_ID] INT NOT NULL,
[GL_ID]  INT NOT NULL,
CONSTRAINT [PK_UserGame] PRIMARY KEY CLUSTERED ([UG_ID] ASC),
CONSTRAINT [FK_USR_ID] FOREIGN KEY ([USR_ID]) REFERENCES [dbo].[Users] ([USR_ID]),
CONSTRAINT [FK_GL_ID] FOREIGN KEY ([GL_ID]) REFERENCES [dbo].[GameList] ([GL_ID]));

Those are how i created the 3 Tables and my Qestion is how can i select all the Games from dbo.GameList that has the same USR_ID
If it was like using USR_ID a normal forign key i wouold've probably used something like
SELECT GameList.Title, GameList.Genre, GameList.Plattform, GameList.ReleaseDate, GameList.Price
FROM GameList
INNER JOIN Users ON GameList.USR_ID=Users.USR_ID;

But since it's my first time using mapping i'm clueless.
Thanks in Advance !!


texts: Copy Specific Columns from DB 1 To DB 2 on MongoDB

texts:
I have Two Instance of Node JS server which is handled By NGINX. First server is connect with MongoDB First Server i.e

  mongoose.connect('mongodb://127.0.0.1/db_primary', { useMongoClient: true });

While Second Node Js Server is Connect with MongoDB Second Serer i.e

  mongoose.connect('mongodb://127.0.0.1/db_secondary', { useMongoClient: true });

Actually, I have to Do some Heavy Calculations on DB and that the main reason I will divide my DB into Two Pieces.

Initially When I have One Server then if the backend is busy with calculations (Millions of Entries) at that time Server is completely freeze. So by the Separation There is now no freezing point of server.

My Question is How I can Copy the selected Columns on The entire Table form DB 1 And paste it to DB 2 with only Selected Columns.

Example:Database 1

{
    _id: ObjectID(54815dfsfsd1f564168ad51a5s),
    a: 123,
    b: 768,
    c: 89.67,
    d: 8976,
    e: 45
  }..........upto 10 million entries

Now I want to Copy Only Column c & e from Database 1 to Database 2.

Expected Result:
{
        _id: ObjectID(54815dfsfsd1f564168ad51a5s),
        c: 89.67,
        e: 45
}..........upto 10 million entries

And all these things are done by CronJob of Nodejs. I mean Not a manual task.

I am Newbie on MongoDB. Any help is appreciated.


texts: What is the difference between  All and Not In

texts:
Could you please tell me the differences between

select CustomerId from Customer Where TerritoryId <> All(select TerritoryId from Salesperson)

select CustomerId from Customer where TerritoryId NOT IN (select TerritoryId from Salesperson)


texts:
I am a programmer changing the way a process works, and will be using a different table to do this. The process is just sending emails from a web application. Currently the database scripts run during a batch each night and create entries in a table that contain the subject, and the entire body of the email.
I am moving to a process where only the variable elements will be held in the table.

I am just wondering what the accepted, or most common strategy is for doing this. I've spoken to a few of our database people and they suggested to create an entirely new table and use this going forward.

I am fine with doing it this way, and was wondering if it's a good idea to alter the current table's name, to something like EmailTableOld and create my new table with the original name EmailTable so I don't have to create additional permission scripts and set up new synonyms while removing the old ones.

Is this a good idea? I have identified everything that refers to the table and there are only a few things mostly security granting scripts, and the scripts that actually create email content. The table does not reference or is referenced by any other tables currently. The entire contents of the old table must be kept.

Any suggestions etc are welcome


texts: What is the recommended RAID configuration for an Oracle database?

texts: RAID (Redundant Arrays of Inexpensive Disks) comes with different configurations (RAID-0, RAID-1...). What is the recommended RAID configuration that I should set up and use when installing an Oracle database. The database will mainly be used as a data warehouse.


texts: Mysql, Getting all rows in which field ends in a specific character, and another field exists that is the same but doesn't end in that character

texts:
I need to get all rows which end in a specific character, P for example, but in which a similar key sans the P exists. I have no idea how to approach this one in MySQL.

This is very small example, my actual data is huge with other columns also.

+------------+
|    key     |
+------------+
| value_100  |
| value_100P |
| value_101  |
| value_101  |
| value_102  |
| value_102P |
| value_102P |
| value_102P |
| value_103P |
| value_103P |
| value_104P |
+------------+

The query would output,

+------------+
|    key     |
+------------+
| value_100P |
| value_102P |
+------------+


texts: Dividing data from one table to multiple storage

texts:
I have the following tables:

[Table_A]
[Table_A_Changes]

The two tables are pretty large (more then 50GBs). The first one contains some data, and the second one records' changes.The database is in FULL recovery model (AlwaysOn).

WRITES:

[Table_A] - constantly/many
[Table_A_Changes] - constantly/many

READS:

[Table_A] - constantly (in 99% of the cases only data for the past 6 months)
[Table_A_Changes] - rearly (in 99% of the cases only data for the past month)

UPDATES:

[Table_A] - constantly/many (in 99% of the cases only data for the past few months)
[Table_A_Changes] - no updates

I am going to use a cloud storage where the fast storage is too expensive to hold all the data.

So, I want to store only the the new data in [Table_A] on fast storage, and the older data on slow/cheap storage.

Is there a way to achieve this?

Also, since the major part of the data is not (read/update) I want to perform only partial backups on the data stored into the fast storage only.Let's say a full backup each week and a partial backup each 12 hours.

I am looking both for storage and recovery strategies (any links are welcome, too).


texts: Constrain array values to an allowed set of values

texts:
I would like to set up a CHECK constraint that makes sure that a text[] column's elements contain only certain values.

Setting up an example:

CREATE TABLE foo(
  countries text[]
);

INSERT INTO foo VALUES ('{"Morocco", "Mali", "Indonesia"}');
INSERT INTO foo VALUES ('{"Sokovia", "Mali"}');

Now only "Morocco", "Mali" and "Indonesia" should be allowed, so the second row should get rejected by the constraint.

I have a "working" solution:

CHECK (array_length(
  array_remove(
    array_remove(
      array_remove(
        countries,
        'Indonesia'
      ), 'Mali'
    ), 'Morocco'
  ), 1) IS NULL)

But this is not very readable.

I tried this as well:

CHECK ((
  SELECT unnest(countries)
  EXCEPT
  SELECT unnest(array['Morocco', 'Mali', 'Indonesia'])
) IS NULL)

but:

  ERROR cannot use subquery in check constraint