texts: table_name.ibd file size is increasing ( MySQL 5.7.7 Enterprise) ( Innodb Engines)

texts:
I am using MySQL 5.7.17 Enterprise edition in RHEL with configuration innodb_file_per_table=ON .

So now here i have 2 question :

Instead of ibd file per table ,i can see ibdata1,ib_logfile1,ib_logfile0 in data directory which is getting updated regularly .However these files are not big in size but collectively , these 3 files are consuming 15-20 GB space .
So , instead of having ibd file per table "ON" , do these files still exist or if they exist what they contain which is making it updated regularly ?
I have devoted 393 GB to the data directory . and one database folder is occupying 293 GB alone. I have table_name.ibd files of size 84 GB-100 GB. Is there any way that i can check if any memory is getting waste ? and if it is getting waste how can i reclaim it ?

I ran optimize tables for some small tables only ( it helped) because if i run this command for big tables , i  need more space before using optimize command as space required is table_size*2 .

I have checked table sizes as well . Size of table and size of respective table's ibd file , vary with 2 -3 GB difference .

Can anyone help me , what can i do here ?

Should i point MySQL data directory to new path ? if it is possible , how can i do this ?

Size of file (in GB) in Descending order :
93
33
19
17
16
14
13
11
11
9.2


texts: How does pgAdmin III build CREATE statements?

texts:
In PostgreSQL, there is no SHOW CREATE statement, known for example from MySQL. But when you use the pgAdmin III client program and go to object details, you see the corresponding CREATE statement.

So here is my question:
How does pgAdmin III build the CREATE statements it shows? Does it use psql's \d command or some completely different PostreSQL API? If so, how can I use it?


texts: Does mongodb support non readable files?

texts:
I have uploaded PDF/PST file in mongodb by mongofiles, but when retrieving it in R(BSON) and converting it to "Character" type, I couldn't find the PDF/PST file. Is it possible to upload
PDF file or PST file? Which file formats does mongodb support (other than CSV,TSV,TXT,audio) ?


texts: don't want to be able creating stored procedures with incorrect

texts: Right now I can execute a CREATE PROCEDURE or an ALTER PROCEDURE script with incorrect table and column references in the MS SQL Server Management Studio and using ADO.NET commands. The scripts run fine, but if I open the created stored procedures on MS SQL Server using Management Studio, then I'll see incorrect tables and columns being highlighted in red. I want to get those errors when creating or altering stored procedures. How do I do that?


texts: SQL Server - RangeX-X and RangeI-N locks

texts:
I came to a dead point in a deadlock analyze. According to msdn:

RangeX-X are Exclusive range, exclusive resource lock; used when updating a key in a range.
RangeI-N are Insert range, null resource lock; used to test ranges before inserting a new key into an index.

So I understand that if I have an Index on 2 key columns - and I insert a new key I would have RangeI-N lock but if I update an existing key from the index I would have RangeX-X.

But my question is more or less complicated. Say I have the index IX_keys_included on column A, B and included column C.

In Serializable isolation mode I insert a new value for the included column C. Will there be RangeI-N or RangeX-X locks for the index IX_keys_included? Actually , will there be any locks given the fact that I insert a new column for an included column in the index?


texts: Can I use mysql/mariadb databases on different OS?

texts:
If I will use mysql/mariadb on Windows and soon I'll decide to switch to Linux or vice versa, can I use databases created in one operating system in another one without any data-loss?

Thanks in advance!


texts: Working memory on the connection string for postgres instance

texts:
I'm trying to understand how can I set the work_mem setting on the Postgres(v10) for a single connection/session.

Currently I'm connection to my Postgres Instance in the cloud through a NodeJs app using a query string as showing bellow:

postgres://<usr>:<pwd>@<host>:5432/<db_instance>;

I know that you can set parameters in the connection string such as connection_timeout and so on, but I can't find anyware how to set the work_mem for the current connection/session.


texts:
I have the following schema for my database (Postgresql 10):

CREATE TABLE "PulledTexts" (
"Id" serial PRIMARY KEY,
"BaseText" TEXT,
"CleanText" TEXT
);

CREATE TABLE "UniqueWords" (
"Id" serial PRIMARY KEY,
"WordText" TEXT
);

CREATE TABLE "WordTexts" (
"Id" serial PRIMARY KEY,
"TextIdId" INTEGER REFERENCES "PulledTexts",
"WordIdId" INTEGER REFERENCES "UniqueWords"
);
CREATE INDEX "IX_WordTexts_TextIdId" ON "WordTexts" ("TextIdId");
CREATE INDEX "IX_WordTexts_WordIdId" ON "WordTexts" ("WordIdId");

Some sample data:

INSERT INTO public."PulledTexts" ("Id", "BaseText", "CleanText") VALUES
(1, 'automate business audit', null),
(2, 'audit trial', null),
(3, 'trial', null),
(4, 'audit', null),
(5, 'fresh report', null),
(6, 'fresh audit', null),
(7, 'automate this script', null),
(8, 'im trying here', null),
(9, 'automate this business', null),
(10, 'lateral', null);

INSERT INTO public."UniqueWords" ("Id", "WordText") VALUES
(1, 'trial'),
(2, 'audit'),
(3, 'creation'),
(4, 'business'),
(5, 'automate');

INSERT INTO public."WordTexts" ("Id", "TextIdId", "WordIdId") VALUES
(1, 1, 2),
(2, 1, 4),
(3, 1, 5),
(4, 2, 1),
(5, 3, 1),
(6, 4, 2),
(7, 6, 2),
(8, 7, 5),
(9, 9, 4),
(10, 9, 5),
(11, 2, 2);

The database itself is created through Entity framework migration for now.

I would like to know if there's a better, specifically more performance efficient way of writing this query because the WordTexts table will contain hundreds of thousands of records and eventually millions. I am also open to going a NoSql route if that would be more efficient for these kinds of queries.

SELECT *
FROM "PulledTexts"
WHERE "Id" IN (
 SELECT "TextIdId"
 FROM "WordTexts" AS "wordTexts"
 LEFT JOIN "UniqueWords" AS "wordTexts.WordId" ON "wordTexts"."WordIdId" = "wordTexts.WordId"."Id"
 WHERE "wordTexts.WordId"."WordText" = 'automate'

 OR "TextIdId" IN (
  SELECT "TextIdId" and1
  from "WordTexts" AS "wordTexts"
  LEFT JOIN "UniqueWords" AS "wordTexts.WordId" ON "wordTexts"."WordIdId" = "wordTexts.WordId"."Id"
  where "wordTexts.WordId"."WordText" = 'audit' INTERSECT

  SELECT "TextIdId" and2
  from "WordTexts" AS "wordTexts"
  LEFT JOIN "UniqueWords" AS "wordTexts.WordId" ON "wordTexts"."WordIdId" = "wordTexts.WordId"."Id"
  WHERE "wordTexts.WordId"."WordText" = 'trial'
 )
);

At a high level, this query is supposed to return PulledTexts whose IDs match those returned by the subqueries. The subqueries are simply meant to return the list of IDs of PulledTexts that contain lets say ('audit' AND 'trial') OR 'automate' from the UniqueWords table. This is exactly what the example query I gave does. The WordTexts table is a simple mapping of UniqueWord to PulledText.


texts: Start replication again from crashed MySQL master

texts:
I am messing around with MySQL replication trying to learn about it and have ran into a snag that I can't seem to find the solution to. I have a local instance of MySQL running as Master and a remote instance in AWS running as Slave. I had to restart my computer so the Master instance died and when I booted it back up again and ran SHOW MASTER STATUS\G it showed a new binlog filename and position which is probably fine and expected. However the Slave still shows the old binlog file name and position where it left off.

I figured just running STOP SLAVE; START SLAVE; would fix the issue but it resulted in an error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'bin.000004' at 63938331, the last event read from 'bin.000004' at 4, the last byte read from 'bin.000004' at 4.'

What would be the proper steps for recovering from this?
I don't have the replication setup to start writing to the slave on failure of Master because I don't need it. I am basically using the Slave as a read only copy to run reports off of.

I am hoping there is an easier way than taking a snapshot of the master and restoring it to the slave and basically starting from scratch.

EDIT:
This is just for development and learning at the moment. There really isn't any critical data in there but I would like to know how to handle situations like these for when I do deploy it to production.

EDIT 2

Relevant lines from Master config

server-id=1
log-bin=bin.log
log-bin-index=bin-log.index
max_binlog_size=100M
binlog_format=ROW

Relevant lines from Slave config

server-id = 2
replicate-do-db=asterisk
replicate-do-db=asteriskcdrdb


texts: Can the SQL Server optimizer use indexes to update auto created statistics?

texts:
Consider the scenario where auto created statistics are created on a column to satisfy a query.

Afterwards; a nonclustered index is created on the column, with or without other key columns.

Could the SQL Server optimizer leverage indexes to update these auto created statistics when running UPDATE STATISTICS statements?

Is there a difference between (default) sample rates & fullscan statistic updates when adding these indexes?

SELECT @@VERSION;

Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64)   Oct 18 2018 23:11:05   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)


texts: ERD directly producing 5NF relational schema - is this desirable?

texts:
I am given a case study of Event Management to develop an entity-relationship diagram (ERD), and based on it check if the resulting relational schema meets the different normal forms. Now, I have developed my ERD based on the SRS that I have developed.

The question that I am having is: when I converted my ERD to the relational schema I got my tables already normalized. Because of the ERD I had nothing left to accomplish with respect to the normalization process, so I got confused.

Another question that arose in my mind is whether or not this is a desirable situation, I mean when I am getting my all the relations normalized from the ERD itself then why do I need to do normalization at all?

To put it differently, if we get the database already in 5NF from the ERD then why do we need to think of normalization?


texts: User can alter procedures without permission

texts:
I have a sql server user that our application is connecting as.  The user has been granted db_datareader and db_datawriter as well as Grant Execute on each stored procedure that it needs to use.

These are the permissions I see when I veiw the usermapping on the Login and when I view the user under the database.

Given these permissions I don't think the user should be able to alter a procedure; however, when my developer logs in as that user they are able to update the stored procedures.

Am I wrong about what these permissions allow?  Is there someplace I can look to find out about permissions that may not being showing up in the UI?  Or do I need to explicitly deny alter on the stored procedures for that user?


texts: SQL Server 2008 R2 - How to check Foreign Key constraints in a transaction only when committed?

texts:
We are synchronizing tables between two databases (yes, it needs to be done by our software and not by replication etc.)

Both databases are identical with many foreign-key constraints on tables.

Within a transaction we insert rows to various tables. The whole transaction will leave the tables in a state where all constraints are fulfilled.

But it may very well be, that in an intermediate state of the transaction a row gets inserted with a FK of another table that has not been inserted yet.

Is there a way to let SQL Server accept this and only check for violations once the whole transaction is committed?

Thanks for your help!


texts: Databases entering "In recovery" mode on restore

texts:
I am facing an issue while restoring the database.

I have many dump files in a folder and I try to restore them one by one to a database xyz, perform some update operation and continue my restore operation with the other dumps. During this restore operation all the other databases are entering the state of In Recovery . Also the database xyz is also entering the state of recovery and my restore operation fails for the 2nd dump saying the "Not able to achieve exclusive lock on the xyz" .

I am using SQL Server 2008 R2.

So how can I solve this issue please suggest.


texts: What is the best way to stage data in SSIS?

texts:
Using the SSIS visual designer, my goal is to load data from a CSV into a table of database X. To do this I've opted to 'stage' the data from the CSV and then select from that staged resource into the live tables (A single CSV will get split into may tables).

I can see two methods of achieving this 'staging' environment. Which of these would be considered best practice? (or what alternatives are recommended)?

As far as I know, either I can insert from a CSV into a TVP (Table Valued Parameter) and select from there (which I think is an in-memory resource), or I can insert into a static table, select from there, and then delete from the static table.

To me it seems that the static table would be slower but better in that it would allow for much larger CSV source files and not place a toll on system memory. Particularly if using a cloud box, the memory of a system might be limited. But then again it's unlikely I would ever get a CSV larger than a few hundred MB, so perhaps that doesn't matter.


texts: How to optimize count(*) using only foreign key index on PostgreSQL

texts:
I have a web app that extensively uses data tables that connect to the database to fetch data.
The problem though, it always needs first to count the total results before it could paginate the data by means of limit and offset

Is there any type of special indexes or some configurations to always force the count to be calculated purely by index without relying on the end table? because now, it seems, it ignores the index when querying for the count.


texts: SQL Server Kerberos Delegation on Failover Cluster Instance

texts:
I am attempting to setup kerberos delegation on a SQL Server failover cluster in order to get around the double-hop issue but have hit a roadblock.

Environment Overview:

Four SQL Servers in two datacenters (DA01,DA02,DB01,DB02)
Two SQL Server named instances with dedicated IP addresses statically bound to port 1433 (SQINS01, SQLINS02)

Each sql instance is on a failover cluster within its datacenter (SQLINS01 may live on DA01 or DA02, SQLINS02 may live on DB01 or DB02)

Both SQL server instances run as a domain service account domain\account1

SPN Overview

domain\account1 has the following SPNs in place on the domain
MSSQLSvc/SQLINS01:1433 ,
MSSQLSvc/SQLINS01.domain.com:1433 ,
MSSQLSvc/SQLINS02:1433 ,
MSSQLSvc/SQLINS02.domain.com:1433

Delegation

domain\account1 has been configured for constrained delegation
User is trusted for delegation on the SPNs listed for Kerberos Authentication

I am attempting to test this connection as follows:
Connected from my local computer to SCLINS01 using my domain credentials that are allowed to be delegated.

Running the following command still returns the NT Authority\Anonymous Login failure:

SELECT TOP(1) * FROM OPENDATASOURCE ('SQLNCLI11','Server=SCLINS02.domain.com;Integrated Security=SSPI;').database1.dbo.table1

I have used the microsoft Kerberos Configuration Manager to verify that SPNs are configured properly.


texts: Cannot update certain rows in innodb tables

texts:
I found the following on http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html

  When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are released. However, if just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

My questions are:

What kind of situation is "a single SQL statement is rolled back as a result of an error" referring to? Does autocommit=1 count? Any example?
I could imagine that if this happens, it leaves a lock to the rows affected and make any subsequent update to these rows fail, until a mysql restart or something that clears the lock, is that so?

Thanks for any input!

EDIT

OK I realized that maybe I wasn't asking the right question. Here is my situation:

I'm experiencing a weird locking issue with my innodb tables (MySQL version 5.1.61 shipped with latest Debian Squeeze). The symptom is that after running for a few days, certain rows suddenly cannot be updated. When observing locks with innotop, I can see that every update to those rows is waiting for X lock, but nobody is actually holding the lock, so all those updates wait till timeout. And the symptom could last for a very very long time (actually I haven't seen it to an end). Sometimes a flush table xxx could solve the problem, but other times I had to flush all tables or this wouldn't go away. The only clue I had was that before this happened, there were almost always deadlock about certain stored procedure (wrapped in transaction) and an auto-commit enabled statement in innodb status.

The stored procedure was like:

BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION SET var_is_error = 1;
  DECLARE var_cur CURSOR FOR SELECT items from item_table;
  SELECT some stuff;
  START TRANSACTION;
  REPEAT
    FETCH var_cur INTO item:
      UPDATE stuff set number = number + 1;
      ...MORE SELECTS AND UPDATES;
  Until some condition END REPEAT
  IF var_is_error = 0 THEN
    COMMIT;
  ELSE
    ROLLBACK;
END

and the autocommit = 1 statement

UPDATE `inventory` SET locked=IF(locked-2>0,locked-2,0),ordered=ordered+2 WHERE (`sku` = 10103)

According to innodb status, the auto-committed statement was holding the lock. I don't see a "WE ROLLED BACK TRANSACTION XX" normally seen in innodb status after a deadlock and there are just many lines of hex dumps about record locks, I guess more than innodb status could hold.

That's why I posted the original questions (and now I realize that I misunderstood that paragraph). I was actually trying to ask that is there a possibility that somehow row locks could be left behind after transaction rollbacks, that could affect the system for a very long time? Or am I looking at the wrong direction? Anyone else had a similar problem?


texts: Setting user permissions for different SQL Server schemas

texts:
I need to limit access to a particular user, but they still need to be able to see the data in tables owned by dbo.

I'm trying to do the following:

dbo schema functions as it would normally, has access to everything
schema1 schema has access to only schema1 objects
if a schema1 view or stored procedure accesses data in tables owned by dbo, the permissions chain appropriately
user1 has access to schema1, and nothing else; except in the case of #3

Here's what I've tried:

Create a user1 user mapped to a test login with a random password
Created a couple tables in the dbo schema with some test data in them
Created a schema1 schema
Created a schema1.get_profiles that selects from a view called schema1.profiles which accesses data in dbo.people, dbo.taglinks, and dbo.tags

However, using the following statement while logged in as user1:

EXEC get_profiles 1

results in:

The SELECT permission was denied on the object 'tags', database 'schema_test', schema 'dbo'.

I have tried WITH EXECUTE AS OWNER and can't begin to understand how "ownership chaining" is supposed to work.

I have also tried

GRANT EXECUTE ON SCHEMA::schema1 TO user1
GRANT INSERT ON SCHEMA::schema1 TO user1
GRANT SELECT ON SCHEMA::schema1 TO user1
GRANT UPDATE ON SCHEMA::schema1 TO user1
GRANT VIEW DEFINITION ON SCHEMA::schema1 TO user1

but I get the following error (despite being a user with dbo-level access):

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

What I need is user1 to be able to access the data via the stored procedures I give it, and nothing else.

Additionally, this is intended to eventually live on an existing SQL Azure database, but I'm testing against a local dummy database first.


texts: How do you calculate how much hardware resources you need for a database?

texts:
We are in the process of scaling our database servers. I was wondering how should we calculate how much hardware resources we need for our databases?

Here is a little bit information about our current database server:

MySQL Database
InnoDB for all tables
About 80 tables
Largest tables are: 15 GB, 13 GB, 12 GB, 5 GB, rest smaller than 1 GB
Size of database on disk is 175 GB with ibdata1, and 56 GB without it
Database grows about 10% per month -- 12 month ago it was about 5-6%
Around 60 connections running in normal use
InnoDB buffer size is 16 GB of total 24 GB and is 99% utilised
CPU usage is around 30% on a 2.27GHz Intel Xeon 8-core L5520
We have about 33% write and 66% read
We have about 2.31 TPS and 1126 QPS according to the below snippet -- QPS seems to spike up and down between 750 and 1500

.

use information_schema;
select VARIABLE_VALUE into @num_queries from GLOBAL_STATUS where VARIABLE_NAME = 'QUESTIONS';
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME = 'UPTIME';
select VARIABLE_VALUE into @num_com from GLOBAL_STATUS where VARIABLE_NAME = 'COM_COMMIT';
select VARIABLE_VALUE into @num_roll from GLOBAL_STATUS where VARIABLE_NAME = 'COM_ROLLBACK';
select (@num_com + @num_roll) / @uptime as tps, @num_queries / @uptime as qps;

Our current service provider offers us the following upgrade:

Active/Passive Design (2 servers, each as described below)
Single Processor, Octo Core Intel Xeon E5-2630 v3 2.4Ghz
64 GB of memory
RAID 1; 300 GB 12 Gbps (15K SAS 2.5") x 2

My questions are:

How do I know if this is an overkill or not? despite the fact that this is obviously a huge upgrade, I want to know if that's what we really need and it won't be wasted resources or unnoticeable performance increase. Any formula, article or book recommendation is appreciated.
Are there any automated tools that can help us find out how much resources we might need, let's say in a year from now, given our current database details?

Update #1

SHOW VARIABLES

SHOW GLOBAL VARIABLES

SHOW GLOBAL STATUS

RIAD Controller

Dell PERC 6i; RAID 5
Write policy: Write-through and write-back
Cache memory size: 256 MB


texts:
I am running Postgres 9.1 on Ubuntu.
I have host all all my.ip.address/32 md5
set in pg_hba.conf and listen_addresses= '*' set in postgresql.conf.
Running show listen_addresses shows '*'
Running sudo netstat -ltnp | grep postgres shows

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      28899/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      28899/postgres

However, I sill cannot connect to the DB using pgAdmin III on my machine that is located at "my.ip.address".
What could I have missed in the set up?

Thank you.


texts:
I have a script that inserts test data into a database. This script is shared by different developers who all use their own s3 bucket. I have a table called s3 with s3bucket column and it will only have one row which is the name of their s3 bucket.

Is there a way to do a conditional insert into that table for that one record?
For example, there are 3 developers and there is s3bucket1, s3bucket2, s3bucket3.

@@hostname would not work as the ip can change.

For a single developer, the script for inserting data would include the following statement:

Insert into customer.s3info(s3bucket)
Values ('example_bucket_dev1)

Since we have multiple developers and they all use their own bucket, the statement would ideally look something like this:

If @@server_uuid = '111111111'
Insert into customer.s3info(s3bucket)
Values (example_bucket_dev1)

If @@server_uuid = '222222222'
Insert into customer.s3info(s3bucket)
Values (example_bucket_dev2)

If @@server_uuid = '333333333'
Insert into customer.s3info(s3bucket)
Values (example_bucket_dev3)


texts:
I do not know if piping is the right word.

EDIT below to make the question clearer (ie I show what I am doing in php, and want to do this with a single MYSQL statement).

I know how to do this in php, but am looking on this site and everyone seems to be able to do this kind of stuff with just MYSQL statements alone. Can this be done with just MYSQL statements?

CREATE TABLE `tests` (
 `TestNumber` int(11) NOT NULL AUTO_INCREMENT,
 `InactiveSlotsBitwise` int(11) NOT NULL,
 PRIMARY KEY (`TestNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=latin1

and

CREATE TABLE `testsubject` (
 `UniqueId` int(11) NOT NULL AUTO_INCREMENT,
 `TestNumber` int(11) NOT NULL,
 `UserId` int(11) NOT NULL,
 `Slot` tinyint(4) NOT NULL
 `Time` int(11) NOT NULL,
 PRIMARY KEY (`UniqueId`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1

and

CREATE TABLE `users` (
 `UserId` int(11) NOT NULL AUTO_INCREMENT,
 `Password` varchar(20) NOT NULL,
 `Email` varchar(100) NOT NULL,
 PRIMARY KEY (`UserId`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=latin1

INSERT INTO tests VALUES(1, 0);
INSERT INTO tests VALUES(2, 0);
INSERT INTO tests VALUES(3, 0);
INSERT INTO testsubject VALUES(1, 1, 50, 2, 0);
INSERT INTO testsubject VALUES(2, 2, 51, 5, 0);
INSERT INTO testsubject VALUES(3, 1, 50, 7, 0);
INSERT INTO users VALUES(50, 'abc', 'a@b.com');
INSERT INTO users VALUES(51, 'xyz', 'x@y.com');

What does this all mean? There are two user a@b.com and x@y.com. Our user of focus a@b.com has to withdraw from some of the tests because of illness. There are three tests with TestNumbers 1,2,3. Our subject a@b.com is in only two of them, namely 1 and 3. His test seating position is slot 2 and 7 respectively.

So my aim is to set 'Time' in testsubject to -1 for each of those entries, to show he was in the test but had to withdraw. In test with TestNumber=1 he was in slot 2. So we must show that slot is now free in 'tests'. So in 'tests' with TestNumber=1 the 'InactiveSlotsBitwise' entry must change to InactiveSlotsBitwise = InactiveSlotsBitwise & (1<<2), to show slot 2 is now free. Similarly in test with TestNumber=3 he was in slot 7. So we must show that slot is now free in 'tests'. So in 'tests' with TestNumber=3 the 'InactiveSlotsBitwise' entry must change to InactiveSlotsBitwise = InactiveSlotsBitwise & (1<<7), to show slot 7 is now free.

EDIT:

So I am presently doing the following. I am having to do it in php due to the multi stage:

This gets fills an array of (TestNumber, Slot) combos.

$UserId = 50; // This is sent to the php code

$arrTestNumberSlot = array();
// The Time>=0 requirement is to make sure the person has not already abandoned the test - in which case we do nothing
$stmt = $db->prepare("SELECT TestNumber, Slot FROM testsubject WHERE UserId=:UserId AND Time>=0");
$stmt->execute(array(':UserId' => $UserId));
if($stmt && isset($stmt) && $stmt->rowCount() > 0)
{
    while(($row = $stmt->fetch(PDO::FETCH_ASSOC)))
    {
        array_push($arrTestNumberSlot, array('TestNumber' => intval($row['TestNumber']), 'Slot' => intval($row['Slot'])));
    }
}
$stmt->closeCursor();

Now change the 'Time' in  testsubject to -1

$stmt = $db->prepare("UPDATE testsubject SET Time=-1 WHERE UserId=:UserId AND Time>=0");
$stmt->execute(array(':UserId' => $UserId));

Now go through the TestNumber/Slot array and update the 'tests' table:

$count = count($arrTestNumberSlot);
for($i = 0; $i < $count; $i++)
{
    $stmt = $db->prepare("UPDATE tests SET InactiveSlotsBitwise= (InactiveSlotsBitwise & (1 << :Slot)) WHERE TestNumber=:TestNumber");
    $stmt->execute(array(':Slot' => $arrTestNumberSlot['Slot'], ':TestNumber' => $arrTestNumberSlot['TestNumber']));
}

So the Question is: 'How can this be done all in one with a single MYSQL statement??


texts:
I have one SQL statement as:

SELECT   ARTICLES.NEWS_ARTCL_ID, ARTICLES.NEWS_ARTCL_TTL_DES,
         ARTICLES.NEWS_ARTCL_CNTNT_T, ARTICLES.NEWS_ARTCL_PUB_DT,
         ARTICLES.NEWS_ARTCL_AUTH_NM, ARTICLES.NEWS_ARTCL_URL,
         ARTICLES.MEDIA_URL, ARTICLES.ARTCL_SRC_ID, SOURCES.ARTCL_SRC_NM,
         MEDIA.MEDIA_TYPE_DESCRIP
FROM     RSKLMOBILEB2E.NEWS_ARTICLE ARTICLES,
         RSKLMOBILEB2E.MEDIA_TYPE MEDIA,
         RSKLMOBILEB2E.ARTICLE_SOURCE SOURCES
WHERE    ARTICLES.MEDIA_TYPE_IDENTIF = MEDIA.MEDIA_TYPE_IDENTIF
AND      ARTICLES.ARTCL_SRC_ID = SOURCES.ARTCL_SRC_ID
AND      ARTICLES.ARTCL_SRC_ID = 1
ORDER BY ARTICLES.NEWS_ARTCL_PUB_DT;

Now I need to combine another SQL statement into one which is:

SELECT   COUNT(*)
FROM     RSKLMOBILEB2E.NEWS_LIKES LIKES,
         RSKLMOBILEB2E.NEWS_ARTICLE ARTICLES
WHERE    LIKES.NEWS_ARTCL_ID = ARTICLES.NEWS_ARTCL_ID;

Not sure how I can achieve this as I am new to Oracle. Basically I have one table which contains articles and I need to include the user likes which is in another table.

Any help regarding this is much appreciated.


texts: Postgres 9.1 statistics in pg_stat_database

texts:
I've been searching for some information about Postgres 9.1 stats which are stored in pg_stat_database - on the web and on IRC. I found SOME information for 9.2 but even that doesn't seem to be accurate. What I'm after is to get proper transaction stats ie commits and rollbacks. Also I've no clue what tup_fetched and tup_returned mean.

I made a few tests on a dummy database. I created a database called statistics and one simple table called films in it. Inserted a few values into it and here is what I found out:

statistics=# select xact_commit, xact_rollback, blks_read, blks_hit,tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted from pg_stat_database where datname='statistics';
 xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted
-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------
          94 |             0 |       182 |     3259 |        18345 |        1293 |           27 |           0 |           0
(1 row)

statistics=# INSERT INTO films VALUES ('UA509', 'Bananas', 105, '1971-04-13', 'Comedy', '85 minutes');
INSERT 0 1
statistics=# select xact_commit, xact_rollback, blks_read, blks_hit,tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted from pg_stat_database where datname='statistics';
 xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted
-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------
          97 |             1 |       182 |     3261 |        18345 |        1293 |           28 |           0 |           0
(1 row)

statistics=# select * from films;
 code  |  title   | did | date_prod  |  kind  |   len
-------+----------+-----+------------+--------+----------
 UA502 | Bananas  | 105 | 1971-07-13 | Comedy | 01:22:00
 UA503 | BanaAAas | 106 | 1971-07-13 | Comedy | 01:26:00
 UA504 | BanaAAas | 106 | 1971-07-13 | Comedy | 01:26:00
 UA509 | Bananas  | 105 | 1971-04-13 | Comedy | 01:25:00
(4 rows)

statistics=# select xact_commit, xact_rollback, blks_read, blks_hit,tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted from pg_stat_database where datname='statistics';
 xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted
-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------
          99 |             1 |       182 |     3262 |        18349 |        1293 |           28 |           0 |           0
(1 row)

What this shows is that xact_commit increases by 3 after just one row insert. tup_inserted does seem to report correct value.
After simple SELECT, xact_commit increases by 2 - kind of strange as SELECT shouldn't really be increasing xact_commit ?
What I'm trying to do is to gather some reasonable data which would give me an idea about how many transactions/s particular Database is doing.
Could any of the gurus shed some light on this ? I take it that tup_fetched and tup_returned are some really damn random values which only Postgres developers understand :)
Thanks in advance!


texts: User has more privileges in redshift than shown by relevant query

texts:
Working in Redshift
dev=# SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = 'myser';
 table_catalog | table_schema | table_name | privilege_type
---------------+--------------+------------+----------------
(0 rows)

However the user is able to perform select on several randomly tested pg_catalog schema tables, e.g. SELECT * FROM pg_class_info;, SELECT * FROM pg_class; etc.
The only GRANT command I run after creating myser was:
GRANT SELECT ON stl_connection_log  TO myuser;

after setting
SET search_path TO pg_catalog;

what am I missing?


texts: Scheduling a procedure with DBMS_SCHEDULER. PLS-00103: Encountered the symbol “NULL” when expecting one of the following: ;

texts:
I can't schedule a job to run.
I made a procedure that writes CSV files and it works when I execute it.
Then I tried to make a job to schedule it:
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'AUTOMATE_CSV_EXPORT',
    job_type => 'STORED_PROCEDURE',
    job_action => 'EXPORT_ALL_LYS_FOR_TABLEAU',
    number_of_arguments => 0,
    start_date => '20-AUG-20 2.05.00PM',
    repeat_interval => 'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=23;',
    end_date => NULL,
    enabled => FALSE,
    auto_drop => FALSE,
    comments => 'hello i am a comment');

  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'AUTOMATE_CSV_EXPORT',
    attribute => 'logging_level',
    value => DBMS_SCHEDULER.LOGGING_RUNS);

  DBMS_SCHEDULER.enable(
    name => 'AUTOMATE_CSV_EXPORT');
END;
/

It didn't run at the scheduled time, but it did when I ran it like this:

I tried to edit the start time in the wizard and it gives me this error:

I scheduled it to run every 2 minutes starting a while ago, and it hasn't run yet. It's definitely enabled.

Troubleshooting
TLDR: scheduler works!!! The only thing that doesn't work is me putting in my email to get notifications.

Am i running it in the same schema I created it in?

I changed the job to run every 2 minutes.

^^^ so it is working? But I don't see any CSVs in the destination folder. I changed the start date to NULL like the code below and it worked! I have CSVs!

Testing a scheduled job:
test:

create or replace procedure test_print
is
begin
    dbms_output.put_line('Current date and time is : ' ||
    to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM'));
end;

set serveroutput on;

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'JOB_TEST',
    job_type => 'STORED_PROCEDURE',
    job_action => 'TEST_PRINT',
    number_of_arguments => 0,
    start_date => NULL,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=2;',
    end_date => NULL,
    enabled => FALSE,
    auto_drop => FALSE,
    comments => 'Testing job runs');

DBMS_SCHEDULER.enable(name => 'JOB_TEST');
END;
/

select job_name,output,status,owner
from user_scheduler_job_run_details where  job_name='JOB_TEST';

results:

The thing that still doesn't work is notifications via email. When that's the only thing i edit, i get this error:


texts: multiple select query for the same tables with union

texts:
Using below query i want to find out the top number of students from each catagory(SC,ST,OBC,etc) on plus2percentage basis. eg i want top 3 students from SC catagory, top 2 from ST and Top3 from OBC. But i also want the top 5 from the open catagory, which include all catgory(SC,ST,OBC). but excluding the previous top 8 students from SC,ST,OBC Catagory.

So the final out output i want is like

top 3 students from SC Catagory. top 2 students from ST Catagory. top 3 students from OBC Catagory. top 5 student from(SC/ST/OBC/OPEN)but no one from the previous 8 students.

Total number of top Student= 13

WITH PRIMARY_CHOICE AS ( SELECT RollNo, ApplicantName, Catagory, Plus2Percentage,AppliedCourse, Major1 FROM (
            SELECT RollNo, ApplicantName, Catagory, Plus2Percentage, AppliedCourse,Major1,  row_number() over (partition by Catagory order by Plus2Percentage desc) as rn
            FROM RKMVApplicationFormData) as T
            WHERE rn <= CASE WHEN Catagory='SC'  AND Major1='English' AND AppliedCourse='B.A'  AND Plus2Percentage >= 50  THEN 2
                           WHEN Catagory='ST'  AND Major1='English' AND AppliedCourse='B.A'  AND Plus2Percentage >= 60 THEN 2
                           WHEN Catagory='OBC'  AND Major1='English' AND AppliedCourse='B.A' AND Plus2Percentage >=50 THEn 1
                            ELSE 0
END )
SELECT RollNo, ApplicantName,  Catagory, Plus2Percentage,AppliedCourse,Major1  FROM PRIMARY_CHOICE UNION ALL
SELECT RollNo, ApplicantName,  Catagory, Plus2Percentage,AppliedCourse,Major1
FROM ( SELECT RollNo, ApplicantName, Catagory, Plus2Percentage,AppliedCourse,Major1,  row_number() over (order by Plus2Percentage desc) as rn
FROM RKMVApplicationFormData x
WHERE NOT EXISTS (
select 1 from primary_choice y
where x.RollNo = y.RollNo ) ) AS T2
WHERE rn<=2 AND Major1='English' AND AppliedCourse='B.A' and Plus2Percentage>=65  order by Plus2Percentage desc

SQLFiddle demo


texts: Does SSMS v17 allow user input in Python scripts?

texts:
With the introduction of Python feature shipped with SQL Server 2017, I would like to know if anyone has been able to use SSMS v17.x and run a python script that requires a user input. Here is a sample basic script:

execute sp_execute_external_script  @language = N'Python',
@script =N' print ("This is a PYTHON test")
userinput = int(input ("Enter LBs to convert to KG"))
converted = userinput * 0.453592
print (converted) '

The script runs endlessly in SSMS while if it was just a print statement; that completes successfully and produces the print result.

I know I can use a Python IDE console like Pycharm but I am wondering if SSMS has such capabilities yet. My research on the internet has not been that fruitful. Most of the use of Python is related to modelling and analysis.


texts: pgAdmin 4 including  in all copied strings

texts:
I'm having trouble using copy/paste from pgAdmin 4. The symptom is that I get the character <U+FEFF> aka Zero Width No-break Space aka Byte Order Mark (BOM) prepended to any string I copy.

The workaround is to paste and then delete that first character. But in practice this is cumbersome. In some contexts the character does not get pasted (in vim on my machine). In some contexts it is pasted, but it's not visible. In other cases it is "visible" as a space (in TextWrangler on my machine).

Here's a bit more detail. I successfully type and execute a query:

select 'this is constant text value in my query'

I select and copy these 4 characters: text

I think my clipboard should contain just those 4 characters. But it doesn't. Instead, it contains this: <U+FEFF>text

This is perhaps related to this old thread between Josh Berkus and Dave Page. But that discussion focuses on the fact that pgAdmin puts the character at the beginning of any file. In my case it's really not the file that is the issue. It's the clipboard.

More practically, the solution in that case was to go to File -> Options and turn off Unicode file writing. In pgAdmin 4 (v2.1 on MacOS), that menu doesn't exist. The menu File -> Preference exists, but I can find no corresponding setting in the Preferences.

Question: can anyone help me to prevent the FEFF character from sneaking into the clipboard whenever I copy anything in pgAdmin?


texts: How to profile MySQL during a period of time

texts:
I want to know during the execution of sysbench --time=100 ..., the bottleneck (function) of MySQL. So I want to profile MySQL when executing sysbench --time=100 ....
I know by set profiling = 1;, I can get such profile for a specific query, but not for a period of time (100 seconds). What can I do?
Thanks.


texts: SSRS Report joining data sets in BID 2008

texts:
How can we join to Data sets in SSRS? For example we have Dataset1 and Dataset2 and the common columns between them are c1 and c2. I know we can use lookup, but wondering if there is a shortcut like using variables to do that based on your experience?

Thank you.


texts: Performance impact of sp_configure blocked process threshold change

texts:
I've been tasked with identifying any blocking happening on a production server. My plan to achieve this is to use

EXECUTE sp_configure 'blocked process threshold', 5

In combination with a server side trace to collect a Blocked Process Report.

I understand that this activity will incur a performance hit, I'd like to find some way of quantifying what this will be.


texts: SSIS: How to check if a record DOESN'T exist in flat file but exists on the database

texts:
I am working on preparing an SSIS job where I am importing a .CVSV file to OLE DB destination (sql database). We are going to get these files on daily basis. The .CSV file contains records of doctors. Each row represents a doctor. Below image shows how I am able to do this successfully. No problems upto this point.

Here's what I need help with:

If the doctor is no longer active we are going to get the same .CSV file without the record of him/her. How do I check to see if the record is not in .CSV file but it exists in SQL database? I need to update that doctor row in SQL database and update the IsActive field for that row to false.


texts:
I have an application running in one server eg: My Server's Ip is 192.168.10.10, and I have the MySQL database server in the same machine. The application connects database server as localhost.

However, I have rented new server now and the IP is 192.168.10.11, it also has its own MySQL database server.

My quest is, I want this new server to be the slave of old server. I don't want to copy the Application to New Server, however I want the application in 192.168.10.10 (old server) to access the database of New slave server without exploiting security risks. The reason I want this is to send the read queries to new server, and write queries would be served by same old server.

Please guide me how to achieve this.


texts:
CREATE TABLE entries(
  uuid            BINARY(16) PRIMARY KEY,
  status          VARCHAR(20),
  client_id       VARCHAR(32),
  creation_date   DATETIME,
);

status can be NEW, IN_PROGRESS, DONE

I need one entry with status != DONE for each client_id. I want maximum 500 of such entries and with oldest creation date.

I ended with query:

SELECT TOP (500) *
FROM (SELECT
          *,
          ROW_NUMBER() OVER (PARTITION BY client_id
                             ORDER BY creation_date) rn
      FROM entries
      WHERE status <> 'DONE') v
WHERE rn = 1

We have around 1000 clients and around 1 milion entries.
Currently query executes ~90 seconds on our Azure SQL database.

Azure SQL Server suggested creating following index:

CREATE NONCLUSTERED INDEX [nci_wi_entries_0A6FBEBB67742F72F54C5F86FD2C5023]
ON [dbo].[entries] ([status])
INCLUDE ([client_id], [creation_date])
WITH (ONLINE = ON)

After that, query executes around 20 seconds, but that is still too slow. I must have results in less than second.

Do you have any ideas how to optimize it (in any way, either rebuilding table or optimizing query)?


texts: How do you aggregate the delta of values between two arbitrary datetime columns?

texts:
Please look at the columns and types of a table called History:
- id PRIMARY KEY
- account_id STRING
- total_spent FLOAT
- date_created DATETIME

Sample data for this table:
(id, account_id, total_spent, date_created)
(1, '123-456-7890', 10, 2018-10-01 00:00:00+08)
(2, '123-456-7890', 20, 2018-10-02 00:00:00+08)
(3, '123-456-7890', 30, 2018-10-04 00:00:00+08)

If I have to find out the money spent by account 123-456-7890 between 2018-10-01 00:00:01+08 and 2018-10-02 00:00:01+08:

let A = the latest total_spent before 2018-10-01 00:00:01+08
let B = the latest total_spent before 2018-10-02 00:00:01+08
Subtract B - A = 10.

If I have to find the amount of money spent by account 123-456-7890 between 2018-10-03 00:00:01+08 and 2018-10-04 00:00:01+08:

A will be 20 since that is the latest row before 2018-10-03 00:00:01+08
B will be 30
Thus answer is 10 as well.

Questions:

Given this schema, how do you calculate the total amount of money spent by all accounts between two datetime?
If I am interested to calculate spending for accounts between two arbitrary datetime, how can I restructure my schema to make my life easier?


texts: Configuring Oracle Data Guard or GoldenGate Standby DB replication while restricting DELETE DMLs

texts:
How can I use Data Guard or GoldenGate to create a slave database where the master does not propagate delete queries to the slave database?

I want to have a replication/slave database to have all the records of the master but when a delete query is executed on the master database, it must not propagate to the slave database.

I'm using Oracle 12c

How can I achieve this requirement?


texts: InnoDB log sequence number is in the future

texts:
130711 23:03:40 InnoDB: Error: page 23 log sequence number 3336825872455
InnoDB: is in the future! Current system log sequence number 235089.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files.

I've never seen or had this error before. The client does not have backups so they need a recovery done.

I've done MySAIM recovery before but never InnoDB. Is there any tips on how to do it correctly?

From what I've read up on I need to increase a single counter ("log sequence number") which is stored somewhere in the headers of ib_logfile0 and ib_logfile1 or something like that correct?


texts: SQL Server transactional replication is out of sync

texts:
I have setup transactional replication in SQL Server and I am using PULL to retrieve the data from the subscriber.

Recently, I noticed that the data in my subscriber is not synced up with the database on the publisher server.

Also, I found the following issues:

The replication monitor on the publisher indicates that the performance is critical and the latency is 00:04:25
The replication monitor also indicates there are 56000 undistributed commands. (This has not changed, seems to be stuck)
The "Distributor to Subscriber History" indicates there are 4 running sessions of the distribution agent.

I've setup verbose logging on the distribution agent job, and I am seeing this error message:

  Raised events that occur when an agent's reader thread waits longer
  than the agent's -messageinterval time. (By default, the time is 60
  seconds.) If you notice State 2 events that are recorded for an agent,
  this indicates that the agent is taking a long time to write changes
  to the destination.

I have tried restarting the distribution agent job, but this doesn't seem to fix the issue. I am not really sure where to troubleshoot next.

Also, it is worth mentioning that the subscriber is running on a secondary SQL Server instance, on the same physical server as the publisher, so I would think latency should be low.


texts: Splitting a database into multiple databases

texts:
I have a database which contains large amounts of measurements (20+ instruments, each recording 60 measurements per second, each measurement containing 10+ complex values). All measurements are stored in a single table, normalized, and indexes are used to allow filtering by instrument, time and other columns.

Needless to say, every 6 months, the database gets so large that scheduled index maintenance tasks take more than a day to complete. So far, I've had to turn off the db twice, for almost two days, to run scripts which would backup and then delete old data in order to "refresh" it.

My colleagues suggest a (logical, IMHO) modification to physically separate the database into multiple databases, by instrument and time. Furthermore, different clients will access different instruments, so keeping each instrument separated will simplify multi-tenant scenarios (providing backup to customers, migrating to other machines, etc.).

In other words, I would go from this:

  Big database
     - AllMeasurements table

to this:

  Instrument001 database
     - Measurements-2012-01 table
     - Measurements-2012-02 table
     - Measurements-2012-03 table

  Instrument002 database
     - Measurements-2012-01 table
     - Measurements-2012-02 table
     - Measurements-2012-03 table

Since my DAL is abstracted through the repository pattern, it would be easy to swap the underlying implementation to switch databases and tables on demand, so coding this shouldn't a problem.

But I am now wondering if this is really the best way?

Does it make sense?

[Edit] I'm using SQL Server 2008, if that's relevant.


texts:
I can't make this work. I want to create a trigger that deletes the child foreign keys before I delete the parent primary key. This is the code that I am using on phpmyadmin:

delimiter //
CREATE TRIGGER removeforeign
before delete
on products
FOR EACH ROW
BEGIN
delete from sales_details
where product_code=21;
END//
delimiter ;

Right now, I can't make trigger works at all. Not for insert, delete, update. In this code, it will delete where product_code is 21 because it's only an attempt to get the syntax right, I am still to figure it out how to compare with what is being deleted. Please, help me with the syntax. I can't see why it isn't working.


texts: How to INSERT row in another database/schema in MySQL

texts:
Let's say I have the following database/schema setup with the following tables:

    SCHEMA_1 (default):
        TABLE_1

    SCHEMA_2:
        TABLE_2

How do I insert rows in another schema's table? The following does not seem to work:

    INSERT INTO TABLE_2 (...) VALUES(...)

How is it possible to insert rows in another another schema?


texts: QuasarDB cluster stuck in IOWAIT on AWS EC2 instances

texts:
I am experiencing a system freeze in IOWAIT on my QuasarDB cluster.
I am continuously ingesting data at 10 GBit/s using the QuasarDB batch API. After a couple of hours, I have all the qdb threads in IOWAIT and the whole system is unresponsive.
Before the system becomes unresponsive, I observe a spike of activity.
When the qdb is in the IOWAIT state, the whole system seems frozen: I can log in, but copying files is extremely slow.
I initially thought that I was over my limit of IOPS, but I have 8,000 IOPS provisioned, and the spikes are below 1,000 IOPS.
The system is not paging out, and the volumes are not full.
The cluster is 4 AWS EC2 c5n.18xlarge nodes, data is stored on mounted EBS GP3 volumes.
Any clue about what I should look at to find the root cause of the problem would be welcomed.


texts: How to drop a DB2 instance when the instance owner was removed

texts:
This is a real sticky situation. I was handed over a machine (running an AIX 7.1), and my first task was to re-install DB2 server on it. But someone before me had conveniently removed an instance owner account, and probably recreated it. Now, the problem is this:

1) When I try to uninstall DB2, it says the instance is active and has to be dropped first.

2) When I try to drop this instance, DB2 says there is no such instance.

I am quite new to DB2 administration. Not sure how to proceed here. Any help is appreciated

Thanks


texts: How do you minimize chance of collision between system-named constraints on temp tables?

texts:
We have occasionally hit the error “There is already an object named 'PK__#TempTab__0796211ACE71813B' in the database” when some of our procedures  attempt to create temp tables with a system-named PK constraint like so:

CREATE OR ALTER PROCEDURE dbo.StoredProc AS
BEGIN
    CREATE TABLE #TempTable (id INT NOT NULL, PRIMARY KEY CLUSTERED (id))
    ...
END

Querying tempdb.sys.objects reveals a couple of hundred constraints of the form PK__#TempTab__XYZ. Many were created hours ago and haven’t been modified since. There were very few active sessions when I looked at tempdb.sys.objects, so it is hard to believe there are that many temp tables currently in use. We do have many similarly named temp tables created in a lot of stored procedures.

I think temp table caching is responsible here. It appears that these PK constraints stick around in tempdb until their associated cached plans are removed. Testing has shown that causing temp table caching to be disabled (creating stats, adding named constraints, etc.) causes the PK constraint to get removed from tempdb.sys.objects when the temp table goes out of scope. Triggering recompiles and flushing the proc cache also clear out those PK constraints from tempdb.sys.objects.

I am aware that system-named constraints are not guaranteed to be unique as described in this post Can SQL Server create collisions in system generated constraint names?.

My questions are:

Am I wrong to assume that temp table caching increases the chance of a name conflict for system-named constraints on temp tables?
Could plan cache bloat be responsible for the large number of PK__#TempTab__XYZ constraints that I am seeing in tempdb.sys.objects, thus increasing the chance of two constraints having the same name? I'm trying to understand why we have seen this rare error this often.
If we can't ensure that system-named constraints on temp tables will never encounter naming collisions, what can we do to reduce the chance of these collisions in general?


texts: Storing dart throws in relation database (JSON vs multiple tables)

texts:
I'm trying to model database for storing dart throw practices.
Data sent from frontend will look something like this
[
  {"target_name":"S13","singles":1,"doubles":1, "triples":1, "misses":1}
  {"target_name":"S14","singles":5,"doubles":3, "triples":3, "misses":4}
  {"target_name":"S15","singles":5,"doubles":3, "triples":3, "misses":4}
   ... \\ from 1 to 100 rows
]

And i want to store in table/tables in SQL database.
On tables I want to make operations  (in brackets I placed how this selects would look like)
Requirement listed ordered by importance

Get short game summary,
(Select all_singles,all_doubles,all_triples,all_misses, points, datetime from SDT_GAME)
Get Game data, so i will send back data as was given (Select gamesheet_JSON from SDT_GAME)
Get Merged all games data for current user, for every target return
(SELECT COUNT(singles), COUNT(doubles), COUNT(triples), COUNT(misses) from sdt_row INNER JOIN ... GROUP BY target)

I had 2 ideas like below (SDT_GAME2 vs SDT_GAME)

I like SDT_GAME_2 option more because of db normalization. But I'm worried that storing on average 50 rows for every game will clog db fast and make selecting(requirements 2) much slower.
Are any of these options clearly better considering my requirements?


texts: Mongodb consumes too much RAM during replica resync

texts:
I have a mongodb cluster with 2 data nodes and 1 arbiter.
I use mongodb 4.0.7 for all my centos VMs.

A few days ago one of my servers (lets call it data-2) had a fatal crash and requested a complete resync of the data. After restarting mongodb on data-2 the resync started. However immediately after that the RAM usage on data-1 (primary) started to skyrock.

However on data-2 the memory consumption was near constant:

During normal usage time the memory consumption stays close to that of data-2 during the resync.

After a few hours the worst case scenario happened and the last remaining  data holder (data-1) got OOM killed by the kernel after using the entire ram + swap (~50GB). I could restore data-1 as primary with little effort, but I every time I try to start the resync the same happens again.

This behavior seems to be unrelated to actual database usage during the resync. (Taking a prod db down for a resync is definitely a no go).

Total data size ~500GB, biggest database 480GB with ~300 collections.

Now my questions:

What uses that much memory during the resync?
How do I effectively analyze the memory consumption?
How do I prevent the primary from going down because of the resync?


texts:
Is it correct/acceptable to have two FK pointing to the same table?

In this business logic, a message can only be sent from one user to another.

For example:

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(60) NOT NULL,
    email VARCHAR(60) NOT NULL,
    age TINYINT NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE message (
    id INT NOT NULL AUTO_INCREMENT,
    content VARCHAR(120) NOT NULL,
    from_user_id INT NOT NULL,
    to_user_id INT NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(from_user_id) REFERENCES user(id),
    FOREIGN KEY(to_user_id) REFERENCES user(id)
);


texts: PostgreSQL 8.0 - Windows Server 2008 R2 install - Service won't start

texts:
I am trying to install postgreSQL 8.0 on a windows server 2008 R2 machine. After the installation, the service does not start. When I try to start it manually, I get a message saying that the service started and stopped automatically. I tried running the service on a postgres account and as a local system account (in the service properties).

I don't know if this is relevant, but during the installation, I got a few alert messages about "procedural languages" and "contrib files" being installed but not activated on the database.

Every log file in pg_log is empty.

Does anyone know what's going on here?

Any suggestions would be much appreciated


texts: Is 'desc' a valid name for a column in mysql?

texts:
If I try to create a table called test with one column, it works:
mysql> create table test(surname VARCHAR(300));
Query OK, 0 rows affected (0.02 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

But if I try now to create the same table, but this time the column is called 'desc' instead of 'surname', I get an error:
mysql> create table test(desc VARCHAR(300));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc VARCHAR(300))' at line 1
mysql>

What am I getting wrong here? Is 'desc' a reserved keyword in mysql?


texts: In SQL server during Log Shippping which portion (active\Inactive) of the Log file (.ldf) backup is taken and further restored in SQL Server

texts:
I am confused, like if we take the backup of the whole log file, wont it clear the transaction log though we have copy only backup.

In log file we have 2 parts, like active and inactive logs, in inactive log all the log records are present, who transaction is already committed.

So we take backup of which portion of the log file.

Thanks In Advance.


texts: MySQL - Best long read query solution within a replication based system

texts:
I'm working on a project where work in a queue requires a database read against heterogeneous table schemas (InnoDB/MyISAM). The queries constructed during the "read" process can result in 10-15 minute execution times, and they will be run in parallel, say 10-15 at a time, but they will each be mutually exclusive and unaware of any sibling processes.
QUESTIONS

Is it better to stop replication, issue the read query, then restart replication ? If so, what happens to a currently running read query if the replication is suddenly turned back on (by a sibling process)?
is it safe to allow simultaneous read queries and replication processing ?


texts: Documentation on usage of brackets() in mysql queries

texts:
I have an application where i am needed to write queries with multiple conditions and i am using several other keywords such as between, and, or etc

I have this two conditions

select * from countries where date between (x and y) and filter = filter and column choosen like '%col%';

and

select * from countries where date between (x and y) and filter = filter

I haven't actually run the queries but i want the date condition using between to be executed first and i thought the brackets would help.

What is the exact usage of brackets () in mysql and where is that documented?.


texts: Deadlock when CODES table has 41900 rows

texts:
I'm having a deadlock accessing the same table from two different jobs. I'm testing it on a SQL SERVER 2012 Enterprise edition.

Running SQL Profiler I get this graph:

You can find the SQL Server Profiler log here: http://pastebin.com/bxcwy7RX

It seems there is a problem with two stored procedures. GetCode:

SET @code = (SELECT TOP 1 CODE
             FROM CODES
             WHERE CODE_LEVEL = @aggregationLevel AND COMMISIONING_FLAG = 255
             order by ID_CODE)

With this other statement in stored procedure ReadCodeAuto:

UPDATE CODES SET
      COMMISIONING_FLAG = @commValue
    , SOURCE = 'PLS'
    , USERNAME = ''
    , TIMESPAN = SYSDATETIME()
WHERE (CODE = @code) AND
       CODE_LEVEL = @aggregationLevel

But the strange thing think is that I get this deadlock when on CODES there are around of 41900 rows.

I don't have any transaction inside both stored procedures. And on SQL Server activity monitor I see that process 5& (GetCode) has a WRITE LOG wait type, SUSPENDED task state, and UPDATE command.

Do you know how can I fix this problem?

If you need more info, please tell me.

UPDATE:

Indexes:
CODES_IS_COMM_IX

CREATE NONCLUSTERED INDEX [CODES_IS_COMM_IX] ON [dbo].[CODES]
(
    [IS_TRANSMITTED] ASC,
    [COMMISIONING_FLAG] ASC
)
WITH
(
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

CODES_IS_TRANS_COMM_FLAG_IX

CREATE NONCLUSTERED INDEX [CODES_IS_TRANS_COMM_FLAG_IX] ON [dbo].[CODES]
(
    [IS_TRANSMITTED] ASC,
    [COMMISIONING_FLAG] ASC
)
INCLUDE
(
    [ID_CODE],
    [CODE_LEVEL],
    [CODE],
    [TIMESPAN],
    [USERNAME],
    [SOURCE]
)
WITH
(
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

ID_CODE_ASC_IX

CREATE NONCLUSTERED INDEX [ID_CODE_ASC_IX] ON [dbo].[CODES]
(
    [ID_CODE] ASC
)
INCLUDE
(
    [CODE_LEVEL],
    [CODE]
)
WITH
(
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

PK_CODES_1

ALTER TABLE [dbo].[CODES] ADD  CONSTRAINT [PK_CODES_1] PRIMARY KEY CLUSTERED
(
    [CODE_LEVEL] ASC,
    [CODE] ASC
)
WITH
(
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

I don't change isolation level, so I think is default.

I run this sql to get it:

SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID

And it is ReadCommitted.

About ROWLOCK options, I have removed all hints like WITH(NOLOCK) and HINT(ROWLOCK).

And now I see this on SQL Server monitor activity:

I'm this is another lock that I see in one of my runs:

There is a LCK_M_IX.

I get three indexes with the same object id:

All data here.


texts: Updating mysql trigger via information_schema -- access denied?

texts:
I have some wrong "definer"s for some triggers in MySQL (actually MariaDB). I figured the easiest way of correcting them would be some version of:

use information_schema;
update triggers set definer=current_user() where trigger_schema='foobar';

It produces:

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

In the same session, I can drop and re-create the triggers with "drop trigger" and "create trigger", so I'm not understanding the permissions issue. Is there some privilege that my user needs that it doesn't have?


texts: Which authentication mode to connect to SSRS from .NET 5?

texts:
I wonder which authentication mode to use when accessing to an SSRS report server via .NET 5 app. I would like to use an HttpClient class, with the SendAsync method. I saw things like Basic authentication but the clients are relying on Blazor Web Assembly and I can't see what domain to enter in the credentials. No, the ideal thing would be a simple pair username/password, without a domain.
Is it possible? and by using which authentication mode?
I would like to add that the app will be deployed on azure.
thank you.


texts:
I have a sql server 2012 instance that I'm installing a new license on that had previously had an evaluation license. I've did this on two other servers with out incident, but this server is giving me problems. It is stuck running DB_STARTUP for most databases, all but one DB_STARTUP is blocked by one other DB_STARTUP which is turn blocked by SPID 7 that sp_who is telling me is running UNKNOWN TOKEN.
DBCC_INPUTBUFFER on spid 7 return

No Event  0  NULL

Can someone give me pointers here?


texts: Backup database using logical backup device in T-SQL

texts:
I am new to SQL Server coming from a software development background.
I created a backup device which creates a backup with the date and time appended to the name to make it unique:
DECLARE @Current_Date DATETIME;
DECLARE @pname varchar(300);
SET @Current_Date = SYSDATETIME();
set @pname = (select 'C:\WEEKLY_EXPORTS\LEARNING_DB_Backup_' + replace(CONVERT(varchar,@Current_Date, 20), ':', '_') + '.bak' );
EXEC sp_addumpdevice @devtype = 'disk',
@logicalname = 'WEEKLY_EXPORTS',
@physicalname = @pname;
GO

When backing up the database using SSMS, I can use the backup device without having to specify a backup name everytime.

I was looking for an example on how to create a backup using the logical backup device in T-SQL but found none. Is there a way to create the backup in a simple way in T-SQL like the following:
BACKUP DATABASE test_student_db
TO DISK = 'WEEKLY_EXPORTS';


texts:
Table:

select
    MainStationID,
    CONCAT(SUBSTRING(SubStationID,1,2),' STN - ',MainStationID) as Station,
    count(SubStationID) as [COUNT],
    CAST(EndDateTime-StartDateTime AS TIME(0)) AS Duration
from
    VHASM2.LineStoppageTrans
where
    SubStationID like 'RH%'
group by
    MainStationID,
    SubStationID,
    CAST(EndDateTime-StartDateTime AS TIME(0))
order by
    MainStationID

Output:-

How to get output like this, please help

Mainstation   Station     count   Duration [total duration]
   1          RH STN - 1    2     00:01:09
   2          RH STN - 2    3     00:00:13


texts: Storage efficiency of Postgres index column

texts:
In my PostgreSQL database, I have this table:

    Table "public.data"
  Column   |  Type  | Modifiers
-----------+--------+-----------
  group_id | bigint |
      item | text   |
     price | real   |
Indexes:
    "group_id_index" btree (group_id)

item's are organized into groups, and the group_id column will have individual values appearing many times in the table. Queries to this table have the form:

SELECT item, price FROM data WHERE group_id = 1234;

group_id_index speeds up these queries (as is its purpose).

My question is, does the presence of the index make Postgres realize it does not have to store the group_id column behind-the-scenes? If only one copy (for the index) of each value of group_id is stored, that would save a lot of space.

This is, if it matters, PostgreSQL version 9.4.1


texts: Restore Standard/Enterprise Dump to Express

texts: I have a need to restore a dump (which is less than 1GB).  Can I restore a dump that was made in either enterprise or standard to express?  I'm asking because I know from experience with SQL that this is impossible.  I've been looking and I can't see to locate if Oracle has the same limitations.


texts: Is it Possible to Overload SQL Server Functions?

texts:
Is it possible to overload a sql server function? Either a scalar, like ltrim, or an aggregate function, like count?

Even if this was a really, really, bad idea. Is it possible?

Somewhat of a duplicate of T-SQL User defined function overloading? I would say it isn't 100% a duplicate, since that was for 2005 version. Maybe this has changed?


texts: How can I seamlessly upgrade the major version of an AWS RDS postgres database?

texts:
This morning I was involved in upgrading a PostgreSQL database on AWS RDS. We wanted to move from version 9.3.3 to version 9.4.4. We had "tested" the upgrade on a staging database, but the staging database is both much smaller, and doesn't use Multi-AZ. It turned out this test was pretty inadequate.

Our production database uses Multi-AZ. We've done minor version upgrades in the past, and in those cases RDS will upgrade the standby first and then promote it to master. Thus the only downtime incurred is ~60s during the failover.

We assumed the same would happen for the major version upgrade, but oh how wrong we were.

Some details about our setup:

db.m3.large
Provisioned IOPS (SSD)
300 GB storage, of which 139 GB is used
We had RDS OS upgrades outstanding, we wanted to batch with this upgrade to minimise downtime

Here are the RDS events logged while we performed the upgrade:

Database CPU was maxed out between about 08:44 and 10:27. A lot of this time seemed to be occupied by RDS taking a pre-upgrade and post-upgrade snapshot.

The AWS docs don't warn of such repercussions, although from reading them it is clear that an obvious flaw in our approach is that we didn't create a copy of the production database in the Multi-AZ setup and try to upgrade it as a trial run

In general it was very frustrating because RDS gave us very little information about what it was doing and how long it was likely to take. (Again, doing a trial run would have helped...)

Apart from that, we want to learn from this incident so here are our questions:

Is this kind of thing normal when doing a major version upgrade on RDS?
If we wanted to do a major version upgrade in the future with minimal downtime, how would we go about it? Is there some kind of clever way to use replication to make it more seamless?


texts: Is there a SQL profiler MSSSQL that can be installed and run on Mac? It doesn't matter if it is a paid version as long as it can run on my Mac OS. Thanks.


texts: Which parameters was a stored procedure or command executed with?

texts:
We are using nHibernate which generates queries from .NET code. Occasionally, some queries get stuck in the sp_whoisactive list and we cannot find out why. I have a feeling that it has to do with a missing or broken parameter. When I run sp_whoisactive, however, I only see the parameter name (in example: @p0), not the actual value. Is there a way to retrieve this information while the process is still running?

Example:

SELECT
    this_.Id as Id6_2_, this_.HRNumber as HRNumber6_2_,
    this_.FirstName as FirstName6_2_, this_.LastName as LastName6_2_,
    this_.StatusTypeID as StatusTy5_6_2_,
    this_.PropertyId as PropertyId6_2_, this_.DepartmentGroupId as Departme7_6_2_,
    property2_.PropertyID as PropertyID10_0_,
    property2_.PropertyCode as Property2_10_0_,
    property2_.LMSCode as LMSCode10_0_, property2_.PropertyName as Property4_10_0_,
    property2_.Report as Report10_0_, department3_.Id as Id4_1_,
    department3_.Name as Name4_1_, department3_.DisplayName as DisplayN3_4_1_
FROM
    [dbo].[EmployeeDepartmentGroupView] this_
LEFT OUTER JOIN
    [dbo].[Property] property2_ ON this_.PropertyId=property2_.PropertyID
LEFT OUTER JOIN
    [dbo].[DepartmentGroup] department3_ ON this_.DepartmentGroupId=department3_.Id
WHERE
    this_.HRNumber LIKE @p0;

Please note, this is automatically generated SQL from nHibernate.

Specs: SQL Server 2008r2


texts: Installing Enterprise Edition in a virtual machine

texts:
Out of curiosity, just wondering if this is possible:

I will download a 180-day trial of Enterprise edition of an Microsoft product (ex: SQL Server 2017).
Install it in a virtual machine.
Once the trial has expired, I will reinstall the virtual machine then re-install the SQL Server in it to have another 180-day trial.

Not sure on the background of Developer Edition and its differences against Enterprise Edition. Just hoped that I could explore all the features of SQL Server 2017 even just for home use only.

I just want to have an environment for self studying with full features of the product as much as possible.


texts: How to get last IP address of DB update request ?

texts:
I have a MySql Database hosted in the cloud.

From an embedded device connected to the Internet network via an ISP  I write some values (coming from a sensor) in a table of this Database.

Is it possible to know the last IP address of the embedded device  by querying the DB ?


texts:
I have a database that is hosted on AWS Aurora. Now let's say it has been assigned particular memory, RAM, buffer pool size, etc.

I also have my.ini file on my local machine. What happens if I change the pool size to 16M in my.ini? Would that affect the performance of my query when I run on my machine? Or will it still use the buffer size set up in aurora?

My current variables are like this:

show variables;
innodb_buffer_pool_size 20915945472

Is this number from the aurora? My local my.ini file reads like this:

innodb_buffer_pool_size=2.5G


texts: How far to go when Validating a SQL Service Pack install?

texts:
When applying a Service Pack to a production SQL server, I usually have a planned downtime window of around 30 minutes.

Having been bitten before, and discovering that a required precondition like a patch is not in place, that can add several minutes to your downtime window (and maybe taking you over the window, or forcing a reschedule).

Now when the update is in planning stage, I move the Service Pack to the server and test it through "Check Files in Use" or "Ready to update".  I cancel the update at that point, and feel as confident as I can that during the update, I will not run over the scheduled downtime window.

You can cancel from either screen, but as the "Update" button on "Ready to update" is in the same location as the "Next >" button on "Check Files in Use", an accidental double click could accidentally start an update, while validating.

My question:

Should I stop validation at "Check Files in Use" or "Ready to update"? Have I validated everything I can by the end of "Check Files in Use"?  Does going to "Ready to update" add value to the validation?


texts: SQL Server Configuration Manager: Error 0x800706be

texts:
Running a computer with SQL Server 2012 Express installed.

Just installed SQL Server 2008 Express and updated to SP3. Also have installed SQL Server 2008 R2 Express and updated to its SP3.

Opened SQL Server Configuration Manager, enabled "Named Pipes" and "TCP/IP" in SQLEXPRESS' Protocols, set TCP/IP Dynamic Ports = [null] and TCP/IP Port = 1433.
Opened 'Services' via Start menu and restarted MSSQL$SQLEXPRESS.
Opened SQL Server Configuration Manager and refreshed SQL Server Services, but it continues to read only "The remote procedure call failed. [0x800706be]"

I've read from some folks that its a issue of outdated drivers. Others, it seems, say it's just a matter of deleting SQL Server 2012, but none of that feels right.

I'm not sure what else to try at this point. I've never worked in SQL Server Configuration Manager or Microsoft SQL Server Management Studio before, let alone servers in general. I just want to be able to practice writing SQL queries for work using some unnamed software I brought home along with generic test data, and getting SQL Server working is step one in the installation process.


texts: PostgreSQL isn't using indices in FULL OUTER JOIN

texts:
I have these tables I'd like to do a FULL OUTER JOIN between. They both have an index on (ts_minute, user_id, campaign_id, host_id) (which matches the join condition). In the following query, if I remove FULL OUTER from the query, it runs 10,000 times faster because it uses the indices (confirmed using EXPLAIN SELECT ...). Problem is, I need FULL OUTER.

select user_id, campaign_id, sum(i.cost), sum(b.bids_internal), sum(b.eligible_requests)
    from impr_summary_test i
    join bid_summary_test b USING (ts_minute, user_id, campaign_id, host_id)
    where ts_minute > now() - interval '5 min'
    group by 1, 2 order by 1,2;

There are indices on just (ts_minute) as well. It would be nice if it could use this index. The number of rows returned in the slow case is ~330, the number of rows returned in the fast case is ~220.

Postgres 9.3.

I've tried re-writing this as two separate sub queries, each with their own (identical) WHERE clause, and then JOIN'ing those results, and that does speed it up, but the basic INNER JOIN case is still 5 times faster than the re-write.

How can I speed this up by getting it to use the indices?

Number of rows in bid_summary_test: 8,292,673

Number of rows in bid_summary_test that match predicate ts_minute > now() - interval '5 min' : 28,849

Number of rows in impr_summary_test: 2,523,534

Number of rows in impr_summary_test that match predicate ts_minute > now() - interval '5 min' : 11,081


texts: SQL Server: Apply Custom Server Role afterwards for already existing databases

texts:
I needed a custom SQL Server role which has readonly access to all databases, regardless of whether a database is existing or is newly created.

The role is not a  problem. The problem is that the role will not be applied to existing databases.
If I create a new database, the role is applied and does working as I expected.

I assume there will be some system stored procedure that will be run at the moment, a new database is created.

Is there any possibility to apply the custom server role afterwards to databases which are existed before i created the custom server role?

I am using SQL Server 2012.

Thanks in advance


texts:
I have mysql table with column tags, and would like to search exact match.
For this data:

 proj7 invoice delivery
 proj7 exchange

and this query

 MATCH(tags) AGAINST ('+proj7 +(invoice report)')

should be

 (proj7 AND (invoice OR report))

So i want only first record in result, but there are both, second with low score due to proj7 existence. How can i query exact matches only.

sqlfiddle http://sqlfiddle.com/#!9/26524/3


texts: Make "as" mandatory when create column alias?

texts:
We found a lot of bugs are caused by missing comma,

select
    aaaa,
    bbbb  -- forgot comma
    ccc,
    dddddddd,
.....

Is it a way to make expression name invalid and it must be expression as name in a SQL statement? (not globally because of missing "as" exists legacy code)


texts: Master-Master on a VIP with a multiple Slave

texts:
Is it possible to have a MySQL set-up that is running on a 2 Active master running over a VIP/Keep alive. Then having a multiple slaves that connecting to the VIP of the 2 master for the replication?

(The application is only connected to the VIP of that 2 master and any write can accept on any node, if master1 is busy, then master 2 will take the new write to the database and replicate it to the master1).

So, that if ever that there's an issue with the master1 still the master2 is running and the replication is also running smoothly as it is connected with the VIP of the 2 master.

Any inputs are really and highly appreciated. Thank you very much in advance!


texts: Corruption of files in postgres: The database subdirectory "base/13067" is missing

texts:
Right, my PostgreSQL database 11.1 is on top of a GlusterFS.
My GlusterFS break, my backup was being done on another table and I don't have backups anymore.
I started the database on a docker container, and i recived some erros:
2020-10-07 22:03:43.677 UTC [359] FATAL:  database "template1" does not exist
2020-10-07 22:03:43.677 UTC [359] DETAIL:  The database subdirectory "base/73729" is missing.

Basically, I only have the postgres data directory. The files on directory are:
base                  opa           pg_ident.conf  pg_replslot   pg_stat_tmp  PG_VERSION            postgresql.conf
core.48               pg_commit_ts  pg_logical     pg_serial     pg_subtrans  pg_wal                postmaster.opts
docker-entrypoint.sh  pg_dynshmem   pg_multixact   pg_snapshots  pg_tblspc    pg_xact               postmaster.pid
global                pg_hba.conf   pg_notify      pg_stat       pg_twophase  postgresql.auto.conf

When i try to pg_dumpall:
pg_dumpall -U user1 -g
pg_dumpall: could not connect to database "template1": FATAL:  database "template1" does not exist
DETAIL:  The database subdirectory "base/73729" is missing.

When i try to pg_dump:
pg_dump -Uuser1 desired_database
pg_dump: [archiver (db)] query failed: ERROR:  could not open file "base/16385/2839": No such file or directory
LINE 1: ....tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog...
                                                             ^
pg_dump: [archiver (db)] query was: SELECT n.tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(n.nspacl,pg_catalog.acldefault('n',n.nspowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('n',n.nspowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) as nspacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('n',n.nspowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(n.nspacl,pg_catalog.acldefault('n',n.nspowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) as rnspacl, NULL as initnspacl, NULL as initrnspacl FROM pg_namespace n LEFT JOIN pg_init_privs pip ON (n.oid = pip.objoid AND pip.classoid = 'pg_namespace'::regclass AND pip.objsubid = 0)

Theres any way to recovery, make a partial dump or something to extract at least some part of the tables?


texts:
Just installed a single instance of SQL Server 2012. The instance name is called Example and the server is called WIN2K8R2. It's on a virtual machine and I am RDP'd into it. I'm trying to login with SQL Server Management Studio.

When trying to login, I get this error.

A network-related or instance-specific error occured while establishing a connection to the SQL server.

All SQL Server Services are 'running'. It's a clean build of Win2k8 R2.

Server Type: Database Engine
Server Name: WIN2k8R2\Example
Auth: Windows Auth

Resolved.

1) Deleted VM.
2) Spun up a new VM.
3) Installed SQL Server 2012

Able to login to the database from RDP.


texts: How to handle normalizing the concept of a grid, and also plan for data versioning?

texts:
I have inherited a database that has a normalized structure, except for its data tables which are aggressively denormalized. Our system mostly renders grids into a browser, and the previous developer created a system where every grid designed in the web ui gets its own table put into the database, WYSIWYG.

This presents many problems, not the least of which is the amount of dynamic sql required for it to function because generic processes now have to work with N different data tables instead of one data table, and any external loading of data has been made into dynamic sql as well so that one procedure can load into any of these very different tables with very different schemas (as determined by the admin user in the ui). Running sp_BlitzCache on our machine screams about 100% plans being created in the last 24 hours, and we have extremely limited resources in production (1 cpu and 8 gig ram). I cry myself to sleep at night because the team didn't have time to address these issues.

Until now. There is a new business initiative to bring versioning of data into our system. I have brought up the fact this is pretty much impossible with our denormalized data tables that have frequently changing schemas. I may now have a chance to pitch some suggested changes.

I would argue for a normalized system with tables for Grids, Rows, Columns, and Cells. Any changes that previously would have affected the schema, will now simply be entries in the column table for example, instead of physically altering or dropping and recreating data tables.

Considerations

The two issues I really want to address with my idea are:

The best way to handle cell data that could be any type and small or large. I worked on a previous project where they had cells with varchar(max) values that are converted - and that was ridiculous. I think it is more reasonable to have a pre-determined largest-possible value to feasibly render in a grid and make that the value. Then we also return a bit, HasBigValue, that would allow the user to click on an icon or details button to get the paragraph or whatever huge amount of info might have made max necessary. Stored as a varchar, and we let the mid-tier handle converting to the actual datatype.

Does this seem like a good solution? I am having a hard time conceptualizing anything better. It seems worse to me to have a separate table or column to store each possible data type as its actual type and would require tons of left joins to build out a grid.
How to audit the data for versioning. ATM I have two ideas. We either

(a) have one central table called Audit that any updates/deletes/inserts cause a new row to be created with the same shared uniqueidentifier but a new primary key value, and then a new row gets created in the corresponding table such as grid or row, and marked as the current value; OR

(b) we have a homebrew solution similar to temporal tables (We are on SQL Server 2012) where our data tables and any other important tables all have a history table created where updates and deletes cause that table to have the old value inserted with the same id and with timestamps.

Is there a better approach or a caveat? The big thing I am worried about is what happens if a schema DOES need to change (which shouldn't be as often if we can normalize grids).


texts:
I'm completely new to working with databases (other than retrieving data from them as a front end developer.) and have an idea I was toying with. However, I'm struggling to find exactly what to Google due to my lack of knowledge of DBs and the terminology surrounding them.

I have a database that houses user's ratings of various items. One user can have one rating per item and can rate as many different items as they want. The goal would be to find other users who have similar items rated similar ratings and use their data to recommend new things to the first user. This means any single rating has some level of reach to every user in the database. (A similarity score of some kind). I.e. when I say I like item 1, the ratings of anyone else who liked item 1 are now of higher value to me. And anyone who didn't like it, their ratings are of lesser value to me.

I know for sure people do these things (Netflix, Amazon, Google) but I'm honestly not sure where to begin learning how. Any tips are appreciated.


texts: SQL Server choose at random from giving data

texts:
My problem is that I don't know how to choose the random data from a giving set. For example: I have a Node column which need to produce different inserts but only from these numbers: 16, 17, 18, 22, 24. I have looked at many different posts but couldn't find what I'm looking for. Any tips, hints or very simple sample query would be much appreciate.

I'm using SQL Server Management Studio 2014


texts: Multiple inserts using select row and variable combination with one SQL statement

texts:
I'm working with SQL 2008 R2 and have a need to find a specific row in a table that has three fields and then insert multiple rows into the same table with each insert using two values from the found row but the third value is a variable that is different on each insert.  Can this be done?  Here is a non-functioning SQL statement that, I hope, shows what I am trying to do:

INSERT INTO Routings_Doors_Options
               (HeaderID, Option2, Option1)
VALUES (Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA1'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA2'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA3'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA4'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA6'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA7'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA8')
SELECT HeaderID, Option2
FROM  Routings_Doors_Options AS Routings_Doors_Options_1
WHERE (Option1 = 'OGGA')


texts: Regenerating a cube relational schema in Visual Studio

texts:
I have an Analysis Services cube project that my company received from an outside contractor, and I'm trying to get it so that developers can work on it on their local machines.

I'm pretty sure that the original developers generated the schema for the backend database from the cube project and then worked against that. Therefore, I'd like to generate the schema from Visual Studio, rather than just exporting the database in SQL Server Management Studio. Currently, I'm receiving an error saying that the dimensions are bound to user tables when I try to generate the relational schema.

Is my approach reasonable, and if so, how would I go about doing this? If my approach isn't reasonable, why not?

(NOTE: I'd asked this question a few days ago on StackOverflow.  When I came back to it, it occurred to me that this would be a better place for it, so I moved it here.)


texts: Does an INSTEAD OF trigger gets re-created every time you insert into a view?

texts:
I have SQL Server 2016. I have two tables with the same structure, one contains historical data, and the other one contains current data.
I have a view that programs use, that returns the content of both tables.

CREATE TABLE A (int Id IDENTITY(1,1) NOT NULL, othercolumns, primary key, ect.)
CREATE TABLE B (int Id NOT NULL, othercolumns, primary key, ect.)
CREATE VIEW MyView AS SELECT * FROM A UNION ALL SELECT * FROM B

CREATE TRIGGER TR_INSTEADOFINSERT_ON_MyView on MyView
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO A (columns)
   SELECT data
   FROM inserted
END

I was wondering if the view gets re-created everytime an insert is done in the view. Because if I check in the sys.dm_exec_sessions and sys.db_exec_requests, I always see the "CREATE TRIGGER TR_INSTEADOFINSERT" in the query text beign executed.

Now I know that it's taking a long time because it's a big table and I have a lot of activity on the server. But I was just wondering why I keep seeing the "CREATE TRIGGER" in the sql_text of the query being executed? Is it re-created or not? What's the most efficient way to do this? Should I modify the programs to reference the table directly when inserting?


texts: Free space from image column after update

texts:
I'm doing some cleanup on one of our database for development purposes on a test server. There are a couple of tables with one IMAGE column. Both tables take roughly 50Gb of space, being the image column the culprit as the the other files are just int, datetime and small size varchar. I ran an update on the tables, using recommendations I found here in SO and other sites, putting the db on simple recovery mode, disabling indexes, setting transaction isolation levet to read uncommited and updating in chunks of 1K rows. Updates takes some times to run, about an hour. Here is my code:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--mytable
UPDATE TOP(1000) mytable
SET    myImgCol = NULL
WHERE  DATALENGTH(myImgCol) > 0

WHILE @@rowcount > 0
BEGIN
    UPDATE TOP(1000) mytable
    SET    myImgCol = NULL
    WHERE  DATALENGTH(myImgCol) > 0;
END
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Setting to NULL I guess is the best way to free that space, correct?

Then, issue is that after runnning the updates the size of the database remains the same size. I guess the space is not freed up inmediately. Found several references to a ghost process that is supposed to be running behind the curtains freeing space but it takes quite long time to regain all the space that was used by the IMAGE column. Then found recommendations on running DBCC CLEANTABLE statement to force to free space and afterwards. Also some recommend running DBCC UPDATEUSAGE at the end of the process to update the reports related to used pages, reserved pages, etc. Yes, I read about the transaction log growing. My plan is to run DBCC CLEANTABLE by chunks, as recomended on BOL pages and as I've done with the updates.

Running these commands will help me reclaim the space occupied by the image columns?

EDIT

Just ran the commands. Database size remains the same. Any advice?

DBCC CLEANTABLE (0, 'myBigTableWithNullImages', 1000);
GO
DBCC UPDATEUSAGE (0);
GO

Running EXEC sp_spaceused I get following data.

database_size    unallocated space
159115.02 MB        81323.46 MB


texts: Master MySQL5.6 - Slave MySQL 8.0 slow relay log reads

texts: We have 4 Servers, 1 Master and 3 Slaves. 2 slaves are on 5.7 version and working perfectly, whereas 3rd slave that is mysql 8 (test version). 3rd slave imported dump in 1 hour when other took 3-4 hours, but it cannot catch up with master and from 5000 seconds behind he went to 200K seconds behind in 3 days. Also binlogs are fetched perfectly without any delay from master. Problem seems to be with reading the relay logs, but not sure what exactly cuases it. In process list I see waiting for handler commit changing to Opening tables doing insertion from relay log and then going back to waiting for handler commit. MySQL is idle, not reading any relay logs for some seconds, then again reads. Server has 40CPUs and 250GB Memory. Server it self is idle. Server running on CentOS 7.6


texts: SQL Server unattended / Silent installation override Configuration.ini file in command line parameter

texts:
I am creating a PowerShell script to install SQL Server 2012 silent installation.  It is not hard to find a tutorial or instructions online on how to do so. I want to my script be a bit more flexible by keeping the defaults in Configuration.INI file and override the configurations in script arguments.

Does the parameter provided to C:\SQL2012\setup.exe override the configurations in Configuration.INI? For example:

The configuration file has default INSTANCEDIR="C:\Program Files\Microsoft SQL Server", if I provide alternative to setup.exe, does that override it:

C:\SQL2012\setup /INSTANCEDIR="D:\SQL Server\" /ConfigurationFile="C:\SQL2012\ConfigurationFile.ini"

Also, if I have not specified a parameter in configuration file, but I want specify now, such as Data File locations, is the installation OK with that?

# /INSTALLSQLDATADIR is not specified in Configuration.INI file
C:\SQL2012\setup /INSTALLSQLDATADIR="D:\SQL Server\data" /ConfigurationFile="C:\SQL2012\ConfigurationFile.ini"


texts: SQL statement sequential execution for multiple statments

texts:
If a single SQL file contains 2 SQL statements below each other (as below) for millions of records

Query 1 : select Id into MyTempTable from TableB b join abc a on a.Id = t.Id

Query 2 :  Update TableB set Completed =1

Is there any chance that query 2 will execute first for some of the records or do we have a guarantee of sequential execution of those statements?

In another words, will Query 2 execution be started only after Query1 finishes?


texts: NOT EXISTS with two subquery fields that match 2 fields in main query

texts:
Background: Two different document types in a document management system.
Both Doc Type A and Doc Type B have a Ticket #, and a Ticket Date.
What we're looking for:
Doc Type A docs that don't have a matching Doc Type B doc (NOT EXISTS) with the same Ticket # and Ticket Date.
There like are Doc Type B docs that have the same Ticket # but NOT the same Ticket Date. We want to ignore those.
Seems simple.... but I am stuck.
So far what I have is something like this:
    SELECT
       DISTINCT ki110.keyvaluechar AS "Ticket #",
       ki101.keyvaluedate AS "Ticket Date"
    FROM
       itemdata
    LEFT OUTER JOIN
       hsi.keyitem110 ki110 on ( itemdata.itemnum = ki110.itemnum )
    LEFT OUTER JOIN
       hsi.keyitem101 ki101 on ( itemdata.itemnum = ki101.itemnum )
    WHERE
       ki101.keyvaluedate BETWEEN '01-01-2021' AND '01-31-2021'
    AND
      (
        itemdata.itemtypenum  = 178
      ) -- this is Doc Type A
    AND NOT EXISTS
       (
        SELECT
           ki110.keyvaluechar,
           ki101.keyvaluedate
        FROM
           itemdata,
           keyitem110 ki110 ,
           keyitem101 ki101
        WHERE  --(itemdata.itemnum = ki110.itemnum) --Ticket #

                -- ** the problem is here  for Date: I need to say Date in Doc Type B doc is not the same as Date in Doc Type A doc using ki101.keyvaluedate)

        AND itemdata.itemtypenum = 183) -- this  is DOC Type B


texts: How long does the final phase of an online index creation take?

texts:
In reading the whitepaper: Online Indexing Operations in SQL Server 2005 it says:

  The online index build process can be roughly divided into three
  phases:

  Preparation
  Build
  Final

And Additionally:

  The duration of the preparation and final phases is usually short and is independent of the size of the data.

Assuming I am building a new non-clustered index in SQL Server 2008 (online), is there a way to see how how much time was spent during an online index build for the Final Phase?

I've tried looking at the Profiler EventClass: "Progress Report: Online Index Operation Event Class", but it appears that the Final phase begins after the EventSubClass "7 - Done" has been sent to profiler

(Tested by holding an IX lock on the table during the Build Phase of the index build and seeing the thread waiting for the IS lock to be converted to the S lock on the table).


texts: Indexes all Exist in non Primary Filegroup - Advantages?

texts:
I have come across a database where all the indexes are stored on a filegroup that is not the primary filegroup.
The database has 4 files, one of which is in the primary filegroup and the other 3 are in another filegroup, let's call it FileGroupB.
All the indexes are in FileGroupB according to this query:
SELECT  i.name,
        i.type_desc,
        o.name,
        f.name,
        o.type_desc
FROM    [sys].[indexes] i
        JOIN [sys].[filegroups] f
            ON f.[data_space_id] = i.[data_space_id]
        JOIN sys.objects o
            ON o.object_id = i.object_id
WHERE   o.type_desc = 'USER_TABLE'

If I am correct, that would mean all code definitions (EG stored procs, functions etc) are stored in the primary file but all the data is in the secondary filegroup.
What is the benefit of / thought behind this approach?