texts:
I have a table from where want to get such classes where 3rd position candidate got more marks than difference of 1st and 2nd.

class  position  name  total_marks
10     1         a     100
10     2         b     200
10     3         c     150
11     1         d     300
11     2         e     400
11     3         f     200

I want to write a query to find similar situation in all classes.


texts: Migrating from MySQL RDS to Aurora RDS with CNAME record

texts:
I understand migrating to Aurora is simply a matter of creating read replica from MySQL source and then promoting when caught up and stopping writes on MySQL RDS previous master.

I use Route 53 DNS that has a CNAME record. My plan is to just change the DNS to Aurora cluster but not sure if that is instant? Is there a way to stop the old MySQL RDS instance so it can’t be written to? (It as a Multi-AZ and there is no stop)


texts: I'm trying to restore single database from mysqldump all-databases with different name

texts:
I'm running mysql-server-5.1.73-3.el6_5.x86_64:

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)

mysql>

I created mysqldump with option --all-databases, to create database backup of all databases located on that server.

I need to restore SINGLE database out of this whole mysqldump and restore it with different (from original) database name.

I usually use something like this: mysql --one-database --database=db1 but that's assuming I'm restoring under SAME name.

Could someone give me a hand with this please?

Thanks in advance!


texts: Restarting Galera MySQL after proper Shutdown

texts:
I already have read and followed dozens of documents describing a split brain situation and how to restart the cluster after then.

However, I am less in this situation as in the situation of shutting down the Cluster properly, that is, stopping all nodes with

systemctl stop mysql

So, the cluster is reachable until the last node is stopped. Then, I know which one was the last one and I should be able to restart the nodes without creating a new cluster, so that joining nodes perform an IST.

How do I achieve this?


texts: SQL 2012 THROW in SP That needs to be run on SQL 2005 and 2012

texts:
Let me start of by saying that I know SQL 2005 doesn't support the THROW feature.

I need a SP to be run on SQL Server 2012 and 2005. I would like to use the following section of code to allow SQL server generate a different error depending on the version of SQL server. But I am getting an error when trying to create the SP on the SQL 2005 server. Is there any way to get around this error when installing the SP?

Code:

IF LEFT(@@VERSION, 25) LIKE ('%2012')
BEGIN
    ;THROW 50013, @FullErrorMessage, 1
END
ELSE IF LEFT(@@VERSION, 25) LIKE ('%2005')
BEGIN
    RAISERROR(@FullErrorMessage, 11, 1)
END

Error:

Msg 102, Level 15, State 1, Procedure SPNAME, Line xxxx
Incorrect syntax near 'THROW'.


texts:
I'm trying to work out what field(s) to use for the clustered index of my fact tables (which all have  a similar structure). I'll use FactSales as an example:

CREATE TABLE dbo.FactSales
(
DateOfSaleKey int NOT NULL, -- PK,
--ProfitCentreKey int NOT NULL, -- (maybe add this?)
RevenueCentreKey int NOT NULL, -- PK
TransactionHeaderNumber int NOT NULL, -- PK
TransactionDetailNumber int NOT NULL, -- PK
TableNumber int NOT NULL,
TransactionOrCheckNumber int NOT NULL,
TerminalKey int NOT NULL,
CashierKey int NOT NULL,
TimeSlotKey int NOT NULL,
TransactionTypeKey int NOT NULL,
TransactionDetailTypeKey int NOT NULL,
MediaKey int NOT NULL,
SaleItemKey int NOT NULL,
SaleTypeKey int NOT NULL,
SalesQuantity int NOT NULL,
SalesGross smallmoney NOT NULL,
SalesNet decimal (16, 8) NOT NULL,
VAT decimal (16, 8) NOT NULL,
...
)

Loading

The fact tables are loaded by ProfitCentre (ProfitCentreKey), which is a single restaurant,  and by day (DateOfSaleKey). We don't have ProfitCentreKey in the table, because we have the more granular RevenueCentreKey, which is a section within a restaurant.

Each load looks like this:

DELETE s
FROM FactSales s
INNER JOIN DimOrganisation o ON s.RevenueCentreKey = o.RevenueCentreKey
WHERE s.DateOfSaleKey = 20160105 AND o.ProfitCentreKey = 385

INSERT INTO FactSales (DateOfSaleKey, RevenueCentreKey, ...)
SELECT DateOfSaleKey, RevenueCentreKey, ... FROM #SalesForProfitCentreAndDateOfSale

We do the delete so we don't have duplicate data.

What I'm considering

Clustered Index: DateOfSaleKey.

This might make the insert easier, but I'm not sure about the delete.
Clustered Index: DateOfSaleKey, ProfitCentreKey.

I would have to add ProfitCentreKey to the table. It  might make the delete easier, but would it fragment the table as ProfitCentreKey would not load sequentially?

And the delete would be simplier:

DELETE FROM FactSales WHERE DateOfSaleKey = 20160105 AND ProfitCentreKey = 385

Any advice would really help. We are using Standard Edition (so no partitioning).


texts: How can I exclude range overlaps if one range may be empty?

texts:
Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table:

CREATE TABLE heights_in_feet(
  size TEXT NOT NULL, min INTEGER NOT NULL, max INTEGER NOT NULL
);
INSERT INTO heights_in_feet (size, min, max)
  VALUES ('wee', 0, 2), ('not so wee', 3, 6), ('friggin huge', 7, 10);

To prevent ambiguity, I add an exclusion constraint:

ALTER TABLE heights_in_feet ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (int4range("min", "max") WITH &&);

At first, this seems to be correct:

INSERT INTO heights_in_feet (size, min, max)
  VALUES ('medium', 4, 5); -- INSERT fails, as it should

However, it doesn't work with empty ranges:

INSERT INTO heights_in_feet (size, min, max)
  VALUES ('medium', 5, 5); -- INSERT succeeds!

This is bad: it's now ambiguous whether 5 feet is "medium" or "not so wee".

The issue is that Postgres considers int4range(5,5) to be empty, and thus non-overlapping:

SELECT int4range(3, 6) && int4range(4, 5); -- t
SELECT int4range(3, 6) && int4range(5, 5); -- f

It does consider (3,6) to contain (5,5):

SELECT int4range(3, 6) @> int4range(5, 5);

... but 1) I can't use that in an exclusion constraint because it's not commutative and 2) it also returns true for int4range(3, 6) @> int4range(50, 50); apparently any empty range is "contained by" any non-empty range.

How can I exclude new rows that would create ambiguous classifications?


texts: Most efficient (or meaningful) way to index grid-based data

texts:
I have a MySQL table which I expect to be in surplus of a few million rows and 99% select statements. The problem I am having is coming up with a meaningful way to determine the Primary Key. (I have provided a table dump at the bottom for reference)

For some background, I am working with a 2-D grid, whose ranges vary from approximately -800000 to +800000 in each direction. Each row is identified  by its X/Z coordinate, for which each coordinate may have 1-30 associated bitstrings (type).

The concerns I have are:

1) I lack a meaningful way to Primary key this table. While I know I can create an id field with auto_increment, I know that in practice this key will never be used, as 100% of SELECTS will be in the form of:

SELECT `type`, `offset`, `bitstring` WHERE `x` = 0 AND `z` = 0;

2) I intend to index on multiple columns (the logical x/z), via:

CREATE INDEX coordinate ON bitstrings(x, z)

While I feel this appropriately addresses my real-world selects, I am constantly concerned it is insufficient indexing, based on the dozens of posts saying 'YES, YOU NEED A PK'.  Is this a case where a PK can be ignored or is the arbitrary id still ultimately going to provide some behind-the-scenes optimization well worth the additional table size and column?

As a side note, I have absolutely no limitations on completely restructuring this table, if there is are any more practical, proven ways to store this sort of data.

CREATE TABLE `bitstrings` (
  `x` int(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `z` int(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `type` smallint(6) NOT NULL COMMENT 'range: 1-4096',
  `offset` smallint(6) NOT NULL COMMENT 'range: 1-65535',
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192'
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

--
-- Dumping data for table `bitstrings`
--

INSERT INTO `bitstrings` (`x`, `z`, `type`, `offset`, `bitstring`) VALUES
(0, 0, 1, 0, 0x52),
(0, 0, 2, 1878, 0x52);


texts: How do I install SQL-Server Tools if I can't use curl?

texts:
Attempting to install mssql-tools, I get

$ sudo apt-get install mssql-tools
Reading package lists... Done
Building dependency tree... 50%
Building dependency tree
Reading state information... Done
Package mssql-tools is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source

E: Package 'mssql-tools' has no installation candidate

So, I go to add the repository,

curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Command 'curl' not found, but can be installed with:

apt install curl

But now I get an error about curl, trying to install curl

$ sudo apt-get install curl
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libc++1 libc++abi1 libsss-nss-idmap0
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libcurl4
The following packages will be REMOVED:
  libcurl3 mssql-server
The following NEW packages will be installed:
  curl libcurl4
0 upgraded, 2 newly installed, 2 to remove and 0 not upgraded.
Need to get 373 kB of archives.
After this operation, 929 MB disk space will be freed.
Do you want to continue? [Y/n] n
Abort.

So in essence, you need curl to install mssql-tools, and installing curl uninstalls mssql-server because sql-server is build with libcurl3, and /bin/curl on Linux wants libcurl4. What do you do?


texts: SQL Server Long Running Transaction - WAITFOR(RECEIVE conversation....DatabaseMail)

texts:
I have recently implemented an Agent Job which checks SQL Server every 10mins for any long running queries and if detected it will send out a mail to recipients with the information. However since putting this in, I notice alot of the below query and wonder if this is something I should be concerned about:
WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout

Understand its from Database Mail and the wait info is (1x: 62093ms)BROKER_RECEIVE_WAITFOR but should I need to worry or simply exclude it from the alerting.
Looking at it via sp_whoisactive can see that the open_transaction count is 1 and the status is suspended.
Any help is appreciated.


texts: postgresql has more DB connection in staging servers than production server

texts:
We have postgresql 9.5 where we use it for an application confluence.
in staging and in production both max connection is same.
But we see staging uses more connection than production e.g staging has 170 connection to DB and production has 105.where as staging is very rarely used.
and among 170 connection 169 is idle.

can any body explain this behavior where highly used Db has less used connection and rarely used application has more used connection.


texts:
We have a SSRS report built using Report Builder 3. There is a coloured logo on top of this report which is displayed correctly when see the report on screen, print it or export it to Word. However when the report is exported to pdf, the image gets faded and seems transparent. The image is embeded in the report and the image file type is .jpg. W

Why is it happening? How can I prevent it?


texts: Log File Full -- Database is in AlwaysOn

texts: Database log file is 99% used.and sys.databases says LOG_BACKUP.Will manual log backup affect the AlwaysOn ? and like log shipping will AlwaysOn have log backup jobs ? Shrinking fails because of pending log backup ? Please advice should I create a maintenance plan for regular log backups ?


texts:
I'm running SQL Server 2000. While doing some profiling to trace activity, I noticed some hostnames showing up that were mysterious -- names that shouldn't exist on the network anymore. I got the MAC address for one of them from master.dbo.sysprocesses, looked up the IP address in the ARP cache, and then used nbtstat to resolve that IP to a network name. The name it resolved to was not the name in sysprocesses, nor was the name in sysprocesses resolvable on the network.

So where is this name coming from? Does SQL Server have a hostname cache somewhere that needs to be cleared? How can I fix this so that the correct network hostname shows in my traces?


texts: Why should pupbld.sql be run as SYSTEM (and not as SYS)?

texts:
pupbld.sql
--
-- DESCRIPTION
--   Script to install the SQL*Plus PRODUCT_USER_PROFILE tables.  These
--   tables allow SQL*Plus to disable commands per user.  The tables
--   are used only by SQL*Plus and do not affect other client tools
--   that access the database.  Refer to the SQL*Plus manual for table
--   usage information.
--
--   This script should be run on every database that SQL*Plus connects
--   to, even if the tables are not used to restrict commands.

-- USAGE
--   sqlplus system/<system_password> @pupbld
--
--   **Connect as SYSTEM before running this script**

-- If PRODUCT_USER_PROFILE exists, use its values and drop it

What are the specific reasons to run it as SYSTEM?


texts:
I have the following table structure:

CREATE TABLE "Material" (
    "MaterialId" TEXT NOT NULL CONSTRAINT "PK_Material" PRIMARY KEY,
    "Content" TEXT NULL,
    "Title" TEXT NULL,
    "Paid" INTEGER NOT NULL,
    "Published" INTEGER NOT NULL,
    "ParentMaterialId" TEXT NULL,
    "Dates" TEXT NULL,
    "Private" INTEGER NOT NULL,
    CONSTRAINT "FK_Material_Material_ParentMaterialId" FOREIGN KEY ("ParentMaterialId") REFERENCES "Material" ("MaterialId") ON DELETE RESTRICT
)

Each material can have an infinite sub materials inside them, and i would like:

Get the outer most ancestor material for a particular child.
Get the First ancestor that is Paid (Paid is a boolean)

I am not that good at complex database operations, so please be kind.


texts:
I would like to transform this:

INSERT INTO users (username) VALUES ('JohnDoe');

Into a copy equivalent:

COPY users (username) FROM ('JohnDoe');

What would be a correct syntax for the copy statement?


texts: PostgreSQL: windowing to replace multiple sub-queries ... hopefully

texts:
I have two tables:

CREATE TABLE fund_data
(
  id serial NOT NULL,
  fund_entries_id integer NOT NULL,
  fund_val numeric(25,6) NOT NULL,
  bbg_pulls_id integer NOT NULL,
  CONSTRAINT fund_data_pkey PRIMARY KEY (id),
  CONSTRAINT fund_data_bbg_pulls_id_fkey FOREIGN KEY (bbg_pulls_id)
      REFERENCES bbg_pulls (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_data_fund_entries_id_fkey FOREIGN KEY (fund_entries_id)
      REFERENCES fund_entries (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_data_fund_entries_id_bbg_pulls_id_key UNIQUE (fund_entries_id, bbg_pulls_id)
)

And:

CREATE TABLE fund_entries
(
  id serial NOT NULL,
  fiscal_prd_end_date date NOT NULL,
  company_id integer NOT NULL,
  ern_dt_id integer NOT NULL,
  CONSTRAINT fund_entries_pkey PRIMARY KEY (id),
  CONSTRAINT fund_entries_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_entries_ern_dt_id_fkey11 FOREIGN KEY (ern_dt_id)
      REFERENCES ern_dt (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_entries_company_id_fiscal_prd_end_date_key UNIQUE (company_id, fiscal_prd_end_date),
  CONSTRAINT fund_entries_ern_dt_id_key UNIQUE (ern_dt_id)
)

To get the most recent value from fund_data for a given company_id and bbg_pulls_id, I currently do two correlated sub-queries:

SELECT fd.fund_val
FROM fund_data fd
    ,(
        SELECT fe.id
        FROM fund_entries fe
            ,(
                SELECT max(fe.fiscal_prd_end_date)
                FROM fund_entries fe
                WHERE fe.company_id = 858
                ) md
        WHERE fe.fiscal_prd_end_date = md.max
            AND fe.company_id = 858
        ) mi
WHERE fd.bbg_pulls_id = 20
    AND fd.fund_entries_id = mi.id

Question: The query works find, but can I use windowing to avoid the two sub-queries? I've tried several times and I keep getting multiple rows.

EDIT: A description of the query in words: Find the max date for a given company_id, so I can find the id for that record. Then use that id to get the most recent value associated with that fk id (and max date) for a given bbg_pulls_id and company. Hopefully that helps.


texts: Is using CLR for regular expressions safer than using external scripts?

texts:
Problem
The main problem we need to use regular expression on MS SQL Server 2019, with the capability
of at least the level on the POSIX Regular expression.
Possible solutions
This Q/A from stackoverflow rightly concludes that if you query must rely on regular expressions you shuould use CLR. This Readgate article elaborates this approach more. So one of my colleagues and I proposed this solution, but my other colleague categorically stated that using CLR here would be a huge risk to security and stability, and using external script (Python or R) is more secure.
This seems to be dubious claim, since the user code in the CLR can be managed, so perhaps the opposite is true, but I was not able to persuade my colleague.
In my other question which I wrote in my desperation because I was forced to use external script and still produce a blazing fast query. SQLpro user states in his comment that:

Using Python or R can be worst in terms of security rather than using CLR!

Which I tend to believe.
Questions
So I have two questions:

Which Regexp solution is more secure external script or CLR based (as described here)? And why?

I also proposed to run the python code on the same Windows Server (must be the same server, because of a policy) but with python intrepeter installed on the OS. Because the results are exported into CSV files either way and stored in the SQL Server. So then I would able to use Python's multiprocessing module to achieve the right performance. The answer was the same that running Python inside SQL Server is more secure than in a outside application. Which is also a questionable claim.


texts: What are the bad design points in the following (simple) Entity-Relationship Diagram?

texts:
The objective is to design an Entity-Relationship Diagram for a company that has addresses, telephones, emails and representants. Here's the list of the constraints:

a company has one or more telephones
a company has one or more e-mails
a company has one or more address
an address has one or more representants
a representant has one or more telephones
a representant has one or more e-mails
if a representant is removed, then it must be saved in a list "last
representants".

Other constraints are (hopefully) represented in the diagram, using the following notation:

circle: attribute
double-lined rectangle: weak entities
double-line: total participation
line ending with an arrow: cardinality 1:N (arrow points to the "1")

Entity Relationship Diagram

I designed that diagram. I'm sure there are some bad design points, because I'm a beginner, and I would like to hear your opinions, suggestions and criticisms.
More information
(based on the answer by miracle173)
English is not my native language, I should've told that in my question, so please excuse my poor word picking. By representant I mean a person that will speak on behalf of the company. With telephones I mean one or more phone numbers. With emails I mean one or more email addresses.
Here is why I chose telephone and email as being weak entities: when a company is removed, I want the list of telephones numbers and the list of emails addresses associated with that company to be also removed. Did I achieve that goal with the use of weak entity?
An address of a company can have more than one representant, but the representant can only work for one company at a time (hence the restriction in my diagram). I agree, some singular and some plural is bad practice. I will fix that.
Changing the relation to more precise verb, like "represented by" (instead of "an address has representants"): from top to bottom, one can read "An address is represented by representants"; from bottom to top, it gives incorrect reading ("A representant is represented by a company"), when it should be "A representant represents a company". So I think it makes difference the reading direction.
What is wanted with "it must be saved" is: to have a short list, like a small history, of the last representants that worked for the company. Example: If John - which is a representant of Miracle's Corporation - suddenly is fired from the company, he should be removed from the Representants list (along with his phone numbers and email addresses), but it is desired to keep him in that short history list after his deletion from Representants.


texts:
I'm completely new to Oracle and I was provided with what appears to be an Oracle database backup (170 DBF files and 3 CTL files).

I'd like to extract the information using SSIS or Pentaho's Kettle but I'm not sure if that's possible

Can you please let me know if that's possible, and if so, what data provider should I use?

I don't have Oracle installed, so I guess it'll be hard and expensive to restore the DBFs

Thanks in advance


texts: What is "Field List" command in mysql processlist?

texts:
In my processlist, I see a long running process. There is no query, just the command "Field List" with an empty info column. Its been waiting almost 20 minutes on a table meta data lock on an unspecified table.

Googling for "MySQL command Field List" hasn't returned anything useful. Can someone tell me what this command is trying to do and what may have caused it?

Using MySQL 5.6.35 on a CentOS 6 box


texts: In Amazon RDS, how does one enable PLV8 extension?

texts:
I see it is available. Just can't find the right documentation to help me enable it.

From the docs, I entered the following command and got:

postgres=> select * from pg_available_extensions where name in ('plv8','plls','plcoffee');
   name   | default_version | installed_version |                     comment
----------+-----------------+-------------------+--------------------------------------------------
 plcoffee | 2.3.8           |                   | PL/CoffeeScript (v8) trusted procedural language
 plls     | 2.3.8           |                   | PL/LiveScript (v8) trusted procedural language
 plv8     | 2.3.8           |                   | PL/JavaScript (v8) trusted procedural language
(3 rows)

PLV8 looks to be available.


texts: MySQL repair deleted data - Any chance of recovery?

texts:
Well, this has been a long Saturday morning. MySQL was throwing an error when dumping backups. That error was...

mysqldump: Error 1030: Got error 5 from storage engine when dumping table mytable at row: XXXXXXXX

This is a MyISAM table with 300 million rows of data. It's become corrupt in the past, and MySQL repair would always resolve the issue. Aside from the error during mysqldump, the data appeared normally on the production site.

I assumed it was a similar issue to before, and the table needed to be repaired. The repair command ended unusually fast with the result...

database.mytable    repair  warning Number of rows changed from 303970410 to 22082981

database.mytable    repair  Error   Error reading file './database/mytable.MYD' ...

database.mytable    repair  status  OK

And just like that, 93% of the data was gone, with the MYD and MYI files replaced. Any idea what might have went wrong here? Any chance of recovering data? I'm assuming no, and I'll have to go with the most recent valid backup, but I'm scratching my head. Appreciate any help.

Edit:

MySQL 5.1.73
CentOS 6.5


texts: How can I make an automatic copy of a MySQL database to another MySQL database on the same server?

texts:
I have two MySQL databases called LIVE and REPORTS on the same server.

I want to copy the LIVE database and all its contents into the REPORTS database everyday at a specific time frame between 1 am and 5 am.

How could I do this automatically?


texts: Full to Simple recovery mode during massive data update?

texts:
I`ve got a 50 Gb database, in which a table takes up 43 GB, and it's full of bad data(over 90%), and i plan to clean it. Database is in Full recovery mode.

My plan is:

0 - take a backup for safety reasons

1 - put database in simple recovery mode

2 - remove 90% of bad data from that table (use batches so that log file doesn't grow)

3 - shrink the data file to an acceptable size, so estimated data for next 1-2 years wont cause any file growth (ofc i will need to rebuild all indexes)

4 - put database back Full recovery mode

5 - take a full backup of the database, so the log backup chain is restored from this point on

Now, my question is a bit strange, but, am I doing anything wrong ? especially by switching database from full -> simple, then simple -> full ? Am I missing anything important from this plan ? Is this the best approach to my problem ?

I hope i gave all needed details, and my questions are not too vague.
Thanks.


texts: Huge Analysis Services log file: msmdsrv.log

texts:
I've just discovered our prod analysis services is writing to a log that is 1.6 GB in size.

I was able to get a copy of the log file saved off to an alternate location and open it in WordPad (Notepad, Word, etc.. said the file was too large).  It appears this log has been churning along for almost 4 years without a problem.  But now the problem is that it is too big.  The msmdsrv.ini file doesn't appear to have a setting for rolling over the log or limiting it's size.

What is the best approach to preventing this from happening in the future? How do you properly manage your Analysis Services log file?


texts:
Normally used to MySQL so need help on syntax check.

with
SELECT * FROM Bit WHERE CustomerID='3512242' I have a field called Filer. I want to change it from a P to a D. Do I use UPDATE to change the value?


texts: Sort for specific column by keep other column grouped


texts: Domain Controller on Multi Subnet Always on Availability Group

texts:
This is will be my first big project. We have 4 Replica Always on availability group  with 3 Replica on site and another replica offsite (different subnet) the File share witness will be placed on site on the primary data center. On various online resources i saw that we need 2 Domain controllers one onsite and the other one on the Disaster Recovery site. My question is what will be the  problems that i will face if all the replicas are joined to one Domain controller .

Suppose if the Primary site goes down the server on the DR will still be able to used cached credentials.

The distance between Primary and Disaster Recovery site only 20 Miles !

Any advice on this will be highly appreciated.


texts: I need to copy data from remote Postgres server that has limited disk space and limited connectivity to a server

texts:
I have a remote Postgres server recording some data every second. the server has very less space like 6-7 GB, currently I write data to a remote VM server but due to poor connectivity the applications fails everyday without notice (I believe as I get error 0x80004005 which is a ODBC related error). I need to fix this and I thought if I can run a local server which store data and the VM server periodically connects>copies>confirms data>deletes the original data. So that even if the connectivity issues occurs the VM can try next time and when gets the data executes the procedure. I can fix this interval as hourly or daily to avoid the data loss.

I am a newbie on DB and really stuck with an app that needs some fix. (The original developer of the app is not available so I have need to get this fixed by myself). Someone please help.


texts:
Is there anyway I can obtain a SQL Login account password, I did not create the logins so I would like to see what the password is for a certain SQL login.

Thanks!


texts: Mysql performance related question for very large database

texts:
Let's say I have database "DB" and it has many tables.

Question 1.
If table A has 100 million rows of data and table B has 100000 rows, will database size as a whole impact performance if I do a select query on table B?

Question 2.
Table A` has 100 million rows of data for around 8000 products. Searching data for particular product on this table is very slow. Will performance increase if I instead create 8000 tables, 1 for each product and put respective data on respective table and make a search on that particular table? Does this have any caveats in a long run?

More information:

It's an InnoDB  Table
Ram size is 27GB
Table A size (the largest table)

Rows=100.43 Million
Data Size= 13.62GB
Index Size= 7.21GB
Total Size = 20.83GB

Table B size

Table B could be various other tables but their size does not exceed 500MB

Table A is indexed and it has three indices on id (Pk), ProductCode, and AddedDate columns.

UPDATE

Table A

CREATE TABLE `ProductDetails` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `DepartmentID` INT(10) DEFAULT NULL,
  `SellerID` INT(10) DEFAULT NULL,
  `ProductCode` VARCHAR(20) DEFAULT NULL,
  `WholesalerType` VARCHAR(5) DEFAULT NULL,
  `Remarks` TEXT DEFAULT NULL,
  `AddedDate` DATETIME DEFAULT NULL,
  `InsertedTimestamp` INT(10) UNSIGNED NOT NULL,
  `WholesalerID` TINYINT(3) UNSIGNED DEFAULT NULL,
  `ProductType` TINYINT(4) DEFAULT NULL,
  `ArrivalDate` DATETIME DEFAULT NULL,
  `SerialNumber` VARCHAR(4) DEFAULT NULL,
  `MessageID` INT(11) DEFAULT NULL,
  `Model` VARCHAR(10) NOT NULL DEFAULT 'NA'
  PRIMARY KEY (`id`),
  KEY `ProductCode` (`ProductCode`),
  KEY `AddedDate` (`AddedDate`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='Engine should be InnoDB for performance'

Table B

CREATE TABLE `Products` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `ProductCode` VARCHAR(20) NOT NULL DEFAULT '00A',
  `Created` DATETIME DEFAULT NULL,
  `LastEdited` DATETIME DEFAULT NULL,
  `Model` VARCHAR(15) CHARACTER SET utf8 DEFAULT NULL,
  `ProductType` VARCHAR(15) CHARACTER SET utf8 DEFAULT NULL,
  `StockItem` INT(11) NOT NULL DEFAULT '0',
  `StockThreshold` TINYINT(2) NOT NULL DEFAULT '10',
  `CostPrice` DOUBLE DEFAULT NULL,
  `SellingPrice` DOUBLE DEFAULT NULL,
  `ShortDescription` MEDIUMTEXT CHARACTER SET utf8,
  `Specification` TEXT CHARACTER SET utf8,
  `Description` TEXT CHARACTER SET utf8,
  `BrandID` INT(11) NOT NULL DEFAULT '0',
  `ManufacturerID` INT(11) NOT NULL DEFAULT '0',
  `ProductStatus` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `ProductCode` (`ProductCode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

Example Query

SELECT * FROM ProductDetails WHERE (ProductCode = 'PR1920A'
AND AddedDate > FROM_UNIXTIME(1519841700)
AND AddedDate < FROM_UNIXTIME(1523934207))
AND ((ProductType in('14','17')
AND MessageID = 28
AND Model LIKE 'MBA-1201')
OR (WholesalerType in('PARAM1','PARAM2','PARAM3','PARAM4','PARAM5')
AND UPPER(Model) IN('RAGL21','BVRK10','TPL001','LTV2960','DEN7YZ','6YU90Q','47GER8','GB3049')))
AND DepartmentID != 0 AND SellerID != 0
ORDER BY AddedDate;

UPDATE 2

Here are the result of the EXPLAIN

id   = 1
select_type =  SIMPLE
table = ProductDetails
type = ref
possible_keys = ProductCode, AddedDate
key = ProductCode
key_len = 63
ref = const
rows = 104456
Extra = Using index condition; Using where; Using filesort

And here is the SHOW INDEX results for ProductDetails table


texts: question about MySQL5.7 server(Master) and a MariaDB 10.3(Slave)

texts:
I have question:

I want to install a MySQL5.7 server(Master) and a MariaDB 10.3(Slave).Replay SQL(select and update) on the slave server after SQL running on the master server.get slow sql log on slave server(MariaDB).how to deployed it ?

Thanks!


texts: First log backup after FULL taking lots of space

texts:
In the backups I'm doing in my database, the first LOG backup I do after the FULL backup is taking too much space, even more than the full backup.

For example:

4:50 AM:  FULL BACKUP  - .bak 7.2GB
6:30 AM:  LOG BACKUP   - .trn 9.3GB
7:00 AM:  LOG BACKUP   - .trn 2mb
7:30 AM:  LOG BACKUP   - .trn 150mb
8:00 AM:  LOG BACKUP   - .trn 1mb
8:30 AM:  LOG BACKUP   - .trn 50mb

Note that the first LOG backup is in GB, while the others are in MB.

What I'm doing is the following:

4:50 am:  (using multiple steps in the same job, not running all of them in parallel)

Index optimize
DB integrity check
FULL backup

6:30am and every 30 minutes after that:

LOG backup

Also, another job called PurgeOldImportData is executed every hour and it deletes old information, but it's currently deleting very little information (if any).  In fact, it takes only 2 seconds to execute.

Can anyone tell me why is this happening? Besides the space it takes, it takes longer to do the RESTORE.  Thank you!

UPDATE:

I'm using Ola Hallengren's scripts for backup.  This is what each job/step does:

JOB: DatabaseBackup - FULL

IndexOptimize:   [dbo].[IndexOptimize] @Databases = 'USER_DATABASES'
Database Integrity Check:  [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES'
Full Backup:  [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\SQLServer\Backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y'
BackupToS3:  (executes a .bat which moves the file, does not touch the SQL Server)

JOB: DatabaseBackup - LOG

[dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\SQLServer\Backups', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y'
BackupToS3:  (executes a .bat which moves the file, does not touch the SQL Server)

At night users run some import data files, but they are not running nothing that produces 9GB of changes AFAIK. Even more, the biggest data imports are not run from 4:50am to 6:30am.


texts: Why add secondary indices before AWS DMS CDC?

texts:
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html

For a full load + CDC task, we recommend that you add secondary indexes before the CDC phase. Because AWS DMS uses logical replication, secondary indexes that support DML operations should be in-place to prevent full table scans.

That doesn't make sense, given that logical replication updates happen based on PK.  (Or do they, if something on the source does UPDATE foo SET bar = 15 WHERE blarg = 12; and there's a secondary index on blarg?)


texts: Where to put glogon.sql in 32-bit ODAC (11.2.0.2.1)?

texts:
I installed the 32-bit  ODAC (11.2.0.2.1) from here. The  Oracle Universal Installer version contains Oracle SQL*Plus 11.2.0.2.0. But I'm missing the sqlplus/admin/ folder in the installation, where I usually put the glogin.sql file.

Edit:

This install is on a new Windows 7 - 64 bit Notebook, which is my first 64-bit Windows system.

The initial idea is not to install any Oracle server there, but I guess I'll change my mind about this during the first 2 month.

I find sqlplus.exe in the folder D:\app\berndk\product\11.2.0\client_1.

BTW This install has no sqlplus subfolder and the ORACLE_HOME environment varaiable is not set. As I do not need ODBC that install generally works quite well.


texts: Why does ENCODE on BYTEA not seem to have any performance impact?

texts:
I am working on a scenario to store strings in hex format of fixed length 64 to database. Obviously the choices are BYTEA and CHAR(64).

Initial thought was to enforce a valid hex string storing it as BYTEA will be good idea, but impact I evaluated was the use of ENCODE on select queries.

I did some performance benchmarks, considering both tables have few million rows;

# A file query_with_char.sql
SELECT "key" FROM table_varchar;;

# A file query_with_binary.sql
SELECT ENCODE("key", 'hex') FROM table_binary;

pgbench -c 30 -T 120 -n -f ./query_with_binary.sql -f ./query_with_char.sql -P 5 -S my_db

SQL script 1: ./query_with_binary.sql
 - weight: 1 (targets 33.3% of total)
 - 236 transactions (34.6% of total, tps = 1.876072)
 - latency average = 8896.888 ms
 - latency stddev = 2548.701 ms
SQL script 2: ./query_with_varchar.sql
 - weight: 1 (targets 33.3% of total)
 - 225 transactions (33.0% of total, tps = 1.788628)
 - latency average = 7164.604 ms
 - latency stddev = 2209.866 ms

I am unable to understand why the performance of query ENCODE is faster compared to normal string. How is PostgreSQL able to do encoding on million rows faster than just fetching the string columns?

Can someone explain what might be wrong in the above test?


texts: Best approach for populating date dimension table

texts:
I am looking to populate a date dimension table in a SQL Server 2008 database.
The fields in the table are as follows:

[DateId]                    INT IDENTITY(1,1) PRIMARY KEY
[DateTime]                  DATETIME
[Date]                      DATE
[DayOfWeek_Number]          TINYINT
[DayOfWeek_Name]            VARCHAR(9)
[DayOfWeek_ShortName]       VARCHAR(3)
[Week_Number]               TINYINT
[Fiscal_DayOfMonth]         TINYINT
[Fiscal_Month_Number]       TINYINT
[Fiscal_Month_Name]         VARCHAR(12)
[Fiscal_Month_ShortName]    VARCHAR(3)
[Fiscal_Quarter]            TINYINT
[Fiscal_Year]               INT
[Calendar_DayOfMonth]       TINYINT
[Calendar_Month Number]     TINYINT
[Calendar_Month_Name]       VARCHAR(9)
[Calendar_Month_ShortName]  VARCHAR(3)
[Calendar_Quarter]          TINYINT
[Calendar_Year]             INT
[IsLeapYear]                BIT
[IsWeekDay]                 BIT
[IsWeekend]                 BIT
[IsWorkday]                 BIT
[IsHoliday]                 BIT
[HolidayName]               VARCHAR(255)

I have written a function DateListInRange(D1,D2) that returns all the dates between two parameter dates D1 and D2 inclusive.

ie. parameters '2014-01-01' and '2014-01-03' would return:

2014-01-01
2014-01-02
2014-01-03

I want to populate the DATE_DIM table for all dates within a range, i.e. 2010-01-01 to 2020-01-01. Most of the fields can be populated with the SQL 2008 DATEPART, DATENAME, and YEAR functions.

The fiscal data contains slightly more logic, some of which is dependant on each other. For example:
Fiscal quarter 1 -> Fiscal month must be 1, 2 or 3
Fiscal quarter 2 -> Fiscal month must be 4, 5 or 6

I can easily write a table valued function that accepts a specific date, and then outputs all of the fiscal data, or ALL of the fields even. Then I would just need this function to run on each row of the DateListInRange function.

I am not highly concerned with speed as this will only need to be populated a few times a year when the table of holidays is altered.

What is the best way to write this in SQL?

Currently its like this:

SELECT
    [Date],
    CAST([Date] AS DATE)                AS [Date],
    DATEPART(W,[Date])                  AS [DayOfWeek_Number], -- First day of week is sunday
    DATENAME(W,[Date])                  AS [DayOfWeek_Name],
    SUBSTRING(DATENAME(DW,[Date]),1,3)  AS [DayOfWeek_ShortName],
    DATEPART(WK, [Date])                AS [WeekNumber],
    DATEPART(M, [Date])                 AS [Calendar_Month_Number],
    DATENAME(M, [Date])                 AS [Calendar_Month_Name],
    SUBSTRING(DATENAME(M, [Date]),1,3)  AS [Calendar_Month_ShortName],
    DATEPART(QQ, [Date])                AS [Calendar_Quarter],
    YEAR([Date])                        AS [Calendar_Year],

    CASE WHEN
    (
        (YEAR([Date]) % 4 = 0) AND (YEAR([Date]) % 100 != 0)
        OR
        (YEAR([Date]) % 400 = 0)
    )
    THEN 1 ELSE 0
    END                                     AS [IsLeapYear],

    CASE WHEN
    (
        DATEPART(W,[Date]) = 1 OR DATEPART(W,[Date]) = 7
    )
    THEN 0 ELSE 1
    END                                     AS [IsWeekDay]
FROM [DateListForRange]
('2014-01-01','2014-01-31')

If I do the same for the fiscal data there will be quite a bit of repetition in each case statement would could be avoided using a function and maybe cross applying the TVF over the list of dates.

Please note I am using SQL Server 2008 so a lot of newer date functionality is minimal.


texts: MySQL, DROP TABLE generates a thread locking itself?

texts:
I'm trying to drop a table on a local mysql instance, and the DROP statement (DROP TABLE wqs_info) hangs out forever...

Looking at mysql logs, I see the lock dumps:

--Thread 18692 has waited at btr0cur.cc line 1870 for 241.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 00000194D3745E88 created in file buf0buf.cc line 1460
a writer (thread id 18692) has reserved it in mode  wait exclusive
number of readers 1, waiters flag 1, lock_word: efffffff
Last time read locked in file row0row.cc line 989
Last time write locked in file g:\ade\build\sb_0-26514852-1514435511.83\mysql-5.7.21\storage\innobase\include\mtr0mtr.ic line 117

What's strange here is that thread 18692 (a mysqld thread) is waiting for a lock that it's already owning?! How can this be possible?

(after 4-5 server restarts, I finally managed to drop the table, but I want to know how it's possible for a thread to wait for a lock itself owns?!)


texts: Dropping/creating procedure causes transaction log to empty

texts:
Steps to reproduce:

Make sure database is in Full recovery mode
Run procedures (containing insert, updates) causing transaction log to grow
use DBCC SQLPERF(logspace) to check transaction log size
drop/create used procedures
Observe that the Log space used dropped (in my case from about 20% to 0.5% with Log size unchanged)

I am unable to determine what is going on.

NOTE: This is a dev machine, nothing escept management studio queries interact with the database


texts: Same Parameter in MySQL stored procedure

texts:
How do I cache dynamic query from a stored procedure?

Right now I have created my stored procedure like this :

CREATE PROCEDURE usp_MyProcedure (
  IN UserID INT,
  ....
)
BEGIN

   SET @sqlQuery = CONCAT("SELECT Name From Users WHERE UserID > ", UserID, " AND UserID  IN ( SELECT UserID FROM OtherTable WHERE UserID = ", UserID, " ) Order by Name")

   PREPARE stmt FROM @sqlQuery;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;

END;

But this kind of query does not cached. So, every time it gets more time to execute/process query.

Now I have tried some other method like this:

CREATE PROCEDURE usp_MyProcedure (
  IN UserID INT,
  ....
)
BEGIN
   SET @UserID = UserID;

   SET @sqlQuery = "SELECT Name From Users WHERE UserID > ? AND UserID  IN ( SELECT UserID FROM OtherTable WHERE UserID = ? ) Order by Name";

   PREPARE stmt FROM @sqlQuery;
   EXECUTE stmt @UserID, @UserID; -- here i passed same variable twice.
   DEALLOCATE PREPARE stmt;
END;

In the above case I have to pass same variable (@UserID) twice, because it is used 2 times in my query. but this job is very hectic in long or complex query. so, how do I avoid this?

One another method I tried as follows:

CREATE PROCEDURE usp_MyProcedure (
  IN UserID INT,
  ....
)
BEGIN
   SET @UserID = UserID;

   SET @sqlQuery = "SELECT Name From Users WHERE UserID > @UserID AND UserID  IN ( SELECT UserID FROM OtherTable WHERE UserID = @UserID ) Order by Name";

   PREPARE stmt FROM @sqlQuery;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
END;

But above query again does not cached. so, execution time is very long. And this type of variable declared as session global variable has may be conflict with another stored procedure's variable. Because I have call stored procedure within this stored procedure and variable name should be same in another stored procedure.

So, let me know what is the best solution for the same.

Thanks.


texts: SQL Database query working really slowly

texts:
I have a database with two columns: 1)Word    2) Times Used

When the user enters a word it find the combination of words already typed (ex: combo = hello world) and updates the database using a dataset

The query is as follows:

 SELECT TimesUsed, Word FROM Predictions WHERE (Word LIKE @partialWord) ORDER BY TimesUsed DESC

In my application I am calling it as follows:

PredTable = PredTA.GetDataByPartialWord(combo)
            If PredTable.Count > 1 Then MsgBox("problem")
            If PredTable.Count = 1 Then PredTA.Update(PredTable(0).TimesUsed + 1, combo)
            If PredTable.Count = 0 Then PredTA.Insert(combo, 1)

When I debug it is taking 0.15 seconds to just do this query
Is there anyway that I can quicken this up?


texts:
I'm getting sluggish performance on a table with ~300,000 rows and B-Trees on each column.

This is for a dynamic pagination page where the query is constructed on demand, and the application caches the primary key in the query's specified order.

For this query

explain analyze
 SELECT supplier_management.buyer_purchase_order_id
     FROM supplier_management
     ORDER BY item_description DESC,
      item_number DESC,
      order_type ASC,
      possession_date DESC,
      shipment_type DESC,
      store_type DESC

I get these results:

  Sort  (cost=51026.98..51750.35 rows=289348 width=72) (actual time=8229.280..12349.596 rows=289348 loops=1)

  Sort Key: item_description, item_number, order_type, possession_date, shipment_type, store_type

  Sort Method: external merge  Disk: 24744kB

  ->  Seq Scan on supplier_management  (cost=0.00..10876.48 rows=289348 width=72) (actual time=0.015..187.426 rows=289348 loops=1)

  Total runtime: 12407.064 ms

How can the performance of a multi-column sort be improved?  Or should I just do it in C++?

Table structure

buyer_purchase_order_id bigint
supplier_number bigint
supplier_name   character varying
purchase_order_number   bigint
store_number    integer
item_number bigint
item_description    character varying
project_type    character varying
order_date  integer
requested_arrival_date  integer
department  character varying
store_type  character varying
shipment_type   character varying
order_type  character varying
quantity_ordered    integer
quantity_allocation integer
quantity_staged integer
quantity_shipped    integer
quantity_received   integer
in_stock_date   integer
in_stock_date_visible_on    integer
show_red    boolean
requested_arrival_date_plus_four_business_days  integer
supplier_status character varying
notes_comments  character varying
requested_arrival_date_color    character varying
grand_opening_date  integer
possession_date integer
consolidator_name   character varying
real_requested_arrival_date_plus_four_business_days integer

No character varying exceeds the length limit for indexing; however, there is some duplication.  Would it be more efficient to put the actual values in another table, normalize, and join on the related integer columns?


texts: Endpoint acting as a mirror and witness?

texts:
Let's say I have 4 SQLServer 2012 R2 divided into two mirrors:

SQL1-1 with its mirror SQL1-2. The Witness is SQL2-2.
SQL2-1 with its mirror SQL2-2. The Witness is SQL1-2.

The mirorring has been created with GUI, so I have only one endpoint for mirorring on each server : TCP://SQLx-x.fqdn.lan:5022 (except built-in endpoints like NamedPipes, etc).

So, for servers SQL1-2 and SQL2-2, this endpoint is used for mirorring AND witnessing of the other mirror. Can this be a problem?

This question came because of an unexpected failover last night and this message in event logs :

  An error occurred in a Service Broker/Database Mirroring transport
  connection endpoint, Error: 8474, State: 11. (Near endpoint role:
  Target, far endpoint address: '')

Here is a solution (restarting endpoints) which didn't worked for me.

Additional informations :

I can telnet TCP://SQLx-x.fqdn.lan 5022 from every servers
Endpoints permissions seems to be fine

Thanks, and sorry for my poor english.


texts: Quickest way to partially restore a mongodb instance from AWS snapshots?

texts:
I have multiple large mongodb clusters running mongodb 3.4 configured with wiredtiger. I would like to set up standalone instances with just a few DBs from these clusters, all on the same EC2 instance.

What I've done so far is to create snapshots of the disks (DB and journal) on a replica from each of the clusters. I then create EBS volumes from these snapshots and mount them on the same EC2 instance.

My problem is the time the restoration takes; though I want just 5-10% of the original data, I have a long process of chowning the data, followed by a lengthy DB startup of each of the standalone mongodb instances and then dropping the majority of the DBs. This process takes a few hours which I'd like to expedite.

I went with starting from snapshots rather than performing dump+restore because I was hoping it would be quicker.

Given the fact that I want to get rid of the majority of the DBs (so I don't care about corruption related to them), is there a method someone can recommend that will leave me with just the DBs I need?


texts:
I ran the runInstaller script in the root dir of the extracted zip files. After some pages of the graphical installer it sent me to run

/tmp/CVU_11.2.0.1.0_macbook/runfixup.sh

as root. It gave me the following errors:

# ./runfixup.sh
Response file being used is :./fixup.response
Enable file being used is :./fixup.enable
Log file location: ./orarun.log
./orarun.sh: 186: [: true: unexpected operator
./orarun.sh: 848: [: unexpected operator
./orarun.sh: 864: [: unexpected operator
./orarun.sh: 882: [: unexpected operator
./orarun.sh: 903: [: true: unexpected operator
./orarun.sh: 1052: [: unexpected operator
./orarun.sh: 1057: [: unexpected operator
./orarun.sh: 1075: [: unexpected operator
./orarun.sh: 1085: [: unexpected operator
./orarun.sh: 1115: [: unexpected operator
./orarun.sh: 1143: [: unexpected operator
./orarun.sh: 1189: [: unexpected operator
./orarun.sh: 139: [: unexpected operator
./orarun.sh: 139: [: unexpected operator
./orarun.sh: 1228: [: unexpected operator
./orarun.sh: 1284: [: unexpected operator
./orarun.sh: 1342: [: unexpected operator
./orarun.sh: 1426: [: unexpected operator
./orarun.sh: 1451: [: unexpected operator

What shall I do?


texts: Does Windows Server File Compression slow Oracle DB performance?

texts:
I'm starting a new a new contract and I get to troubleshoot their Oracle instance...  I'm more apps and sql, but I'll be trying to figure out their crashes and slow performance.  Go figure.

I can guess at reasons for some slowness... they want me to review their queries and tune where I can... DB and queries... Time for me to brush off those old Oracle DBA books.

I don't get access to Oracle for a bit, but I've got access to the Win 2008 R2 server and MS SQL Server.  I notice that their MS SQL backups are in pretty poor shape, with 987+ backups across 6 or so databases... they were down to 42M of space left on their 173G drive.

Looking at the system over all, I see that they are keeping their DB and databases on compressed drives.  I would think that this would give them a performance hit, but I really don't know.

Would this affect the performance of their Oracle and MS SQL negatively?

Update:
As I look around their system, I see other patterns of Worst Practices:
Multiple applications on one server:  Oracle DB, OracleBI, MS SQL 2008, Oracle WebLogic, Sharepoint...
All are on a single spindle -- 1 TB set in 5 virtual drives.
Almost all applications are in directories with folder/file compression set.
The sizing for the drives is currently a bit tight.  The Oracle "drive" has 4% free space (4.3 G free).  The MSSQL drive had what... 0.02% free before I deleted some old backups...

I will recommend additional drives (which probably won't happen) or to at least re-size the "Drives" and share out some of the 400 GB remaining on C:

The 4.3 GB free on the Oracle drive may not be a critical piece, but I can't tell until I can get Oracle up and running long enough to see what their DBs and conditions are.  So, "performance"-wise, it's a ZERO... at least MSSQL is running.

Thanks for your input... now off to find and digest the Oracle logs.


texts: PostgreSQL/ Linux, way to have DNS forward to a port/cluster?

texts:
Aws linux(centos) ec2
Host: mydb. 10.10.10.10.
3 clusters: ports 5432, 5433, 5434

What I'd like to see is:
db2.mydb or mydb/db2 connect/fwd to cluster2 (5433).

Anything Linux or psql come to mind? In mssql I'd have named instances as an option

To clarify the needs a little better:
These new instances (clusters) will be used by various people. QA as a team or individually, Dev as a team or individually. There could be a need for 3, 10 or 30 clusters at any given time. Some clusters will remain active for weeks and others for hours.

A friendly name is purely for the human element.
I (as dba) would rather see a list of 20 names than 20 port numbers. But I could create a data driven page to translate for me, just need to track the mapping in a table.
The clusters will be used by people and in connection strings for applications.
The friendly name is self documenting, so there isn't any worry about confusion as to what port 6221 is in a file. Last week port 6221 was a dev sandbox, today it's a special hotfix branch.

In AWS the option exists to just have 1 linux server instance per cluster. Likely even easier to maintain in the long term, but AWS charges by the hour for each server, so it seems like a nightmare to keep turning off/on these linux server instances... or you look at a way higher cost having 30 vs 1 server running.

On a local virtual env I would just have 1 Linux, 1 Cluster, 1 DB each. All default port and I can map dns to that servers IP no problem.

Did any of this information help clarify? Anything else I can add to help?


texts: Using PostgreSQL to hold an array of data

texts:
I am trying to use a postgreSQL data base column to store values from an array (or point to an enum table which does this).  I will get an array of data like ['one','two','five']. The array can contain any amount of items from 0 to 10.
I have an enum table with these values:
id    value
1     one
2     two
3     three
4     four
5     five

I want my database cell to point to all of the values that are contained in the array.  Is this even possible or is there a common way to structure database tables to fix this issue.
So my desired solution when passed the array ['one','two','five'] would be to have a single cell in my database hold the enum pointers 1, 2, 5.


texts: PostgreSQL \COPY fails "ERROR: missing data for column "column name2""

texts:
We are importing bulk data i.e millions of records.
We observed that due to a null value for a column , the import is getting failed.
Is there a way we could skip the import error when a record is missing and keep the job running?


texts: Significantly Slower DB Dumps on AWS RDS After Turning on Encryption?

texts:
Turning on encryption on an AWS RDS MySQL instance results in significantly slower database dumps (using mysqldump).

mysqldump -u test -p -h xxx.aws.com > dump.sql

Is there a reason for significantly slower DBdumps on AWS RDS after turning on encryption?


texts: mongodump fails after exactly 10 minutes

texts:
Using single instance MongoDB v4.2.0 on Debian 9.11 with collection of 78GB and 60M documents on a 2 vCPUs, 13 GB memory server. This command is invoked on the same server where database runs:

mongodump --username user --password pwd --authenticationDatabase admin --host localhost --gzip --archive=out.gz --db database --collection collection

And after 10 minutes this is the output:

2019-10-17T16:13:09.523+0200    Failed: error creating intents to
dump: error counting database.collection:
connection(localhost:27017[-2]) unable to decode message length: read
tcp 127.0.0.1:57798->127.0.0.1:27017: i/o timeout

By looking at mongod.log this is what mongodump outputs:

2019-10-17T16:13:09.523+0200 I  NETWORK  [conn20] end connection 127.0.0.1:57794 (4 connections now open)
2019-10-17T16:13:09.748+0200 I  -        [conn21] operation was interrupted because a client disconnected
2019-10-17T16:13:10.371+0200 W  COMMAND  [conn21] Unable to gather storage statistics for a slow operation due to lock aquire timeout
2019-10-17T16:13:10.371+0200 I  COMMAND  [conn21] command database.collection appName: "mongodump" command: aggregate { aggregate: "collection",
pipeline: [ { $match: {} }, { $group: { _id: 1, n: { $sum: 1 } } } ], cursor: {}, lsid: { id: UUID("4759f9ad-7d37-44c7-bd41-8610af565c47") },
$db: "database" } planSummary: COLLSCAN numYields:437454 ok:0 errMsg:"Error in $cursor stage :: caused by :: operation was interrupted because
a client disconnected" errName:ClientDisconnect errCode:279 reslen:186 locks:{ ReplicationStateTransition: { acquireCount: { w: 437456 } },
Global: { acquireCount: { r: 437456 } }, Database: { acquireCount: { r: 437455 } }, Collection: { acquireCount: { r: 437455 } }, Mutex: {
acquireCount: { r: 2 } } } protocol:op_msg 600852ms

It looks like mongodump internally runs this aggregate query:

[ { $match: {} }, { $group: { _id: 1, n: { $sum: 1 } } } ]

And when the same query is run from MongoDB shell it works:

> db.collection.aggregate([ { $match: {} }, { $group: { _id: 1, n: { $sum: 1 } } } ])
{ "_id" : 1, "n" : 60488853 }

And this is the mongod.log output:

2019-10-17T15:01:37.130+0200 I  COMMAND  [conn2] command database.collection  appName: "MongoDB Shell" command: aggregate
{ aggregate: "collection", pipeline: [ { $match: {} }, { $group: { _id: 1.0, n: { $sum: 1.0 } } } ], cursor: {}, lsid: {
id: UUID("3b732623-e8b4-4365-bac7-efa710db035c") }, $db: "database" } planSummary: COLLSCAN keysExamined:0
docsExamined:60488853 cursorExhausted:1 numYields:472591 nreturned:1 reslen:136 locks:{ ReplicationStateTransition: {
acquireCount: { w: 472593 } }, Global: { acquireCount: { r: 472593 } }, Database: { acquireCount: { r: 472593 } },
Collection: { acquireCount: { r: 472593 } }, Mutex: { acquireCount: { r: 2 } } } storage:{ data: { bytesRead: 79320933249,
timeReadingMicros: 610249624 } } protocol:op_msg 653559ms

What can be seen is that this count query from shell takes 653 seconds while the mongodump internal query timeouts after 600 seconds (10 minutes exactly).

Other smaller collections and databases on the same server do not have this issue, just this large one.

How this timeout or issue for large queries can be solved so that mongodump runs without issues?


texts: Synchronize MySQL database from remote MySQL database

texts:
The documentation mentions a sync function, but that function is not available from the Database menu as shown in the screenshots there. Is this functionality available elsewhere? Using reverse engineer makes the option show, but the wizard is unclear..

It would be nice being able to choose specific tables, but have the necessary tables linked through foreign keys be automatically included to avoid synchronization failure, and to do it all from the mysql shell not from an error prone UI.


texts: Will DB backup time and size be decreased after truncate large tables

texts:
Two tables in our SQL Server DB equate to 500M rows and 350GB of data. Disk space is an issue where we keep backups and the time to execute backups is as well. We plan to truncate these 2 tables.

I understand that to reclaim the disk space, we'll need to shrink the DB after truncation, then do an index rebuild to fix fragmented indexes afterwards. But as we are not sure how long the rebuilds will take (we are spinning up a test server VM in Azure now to test), in the meantime we are looking for incremental gains. After just truncating (before shrink and rebuild), will the backup times and the resulting backup file size (after 7z compression) be reduced dramatically as a result?


texts: SQL server not listing records with NULL values

texts:
I faced a strange phenomenon today. I ran the below query to Find all requests with waits other than BROKER_RECEIVE_WAITFOR

select * from sys.dm_exec_requests where session_id > 55
and wait_type not like ('BROKER_RECEIVE_WAITFOR')

The query ran fine, But it failed to list any request with No waits, ie with wait_type as NULL(there were quite a lot of running queries without any waits at this time).

Why is this happening, Isn't it supposed to list all records with wait_type not like 'BROKER_RECEIVE_WAITFOR' including Nulls?

I'm running SQL server 2008R2 SP3.


texts:
I have a server with PostgreSQL9.1-903 (Windows 7 Professional). After a power cut my database is inaccessible (I presume that the database is corrupted).

The postgreSQL service starts but the following message is displayed when I try to connect via pg_Admin:

  FATAL: catalog is missing 1 attribute(s) for relid 2662

I have tried to disable the option: 'ignore_system_index' but the database is still inaccessible. I guess that is not a problem with the indexes, but with the fact that some rows of the table pg_attribute have disappeared.

I am interested in recovering the data because my last backup is a bit old. Is there any way to just connect to the database? After that I would use the tool pg_dump to retrieve the data and mount it in a new database.

EDIT

The parameter fsync is commented in the postgresql.conf (I assume that the default value is on). Following, I attach the part of the postgresql.conf which has been tuned:

checkpoint_segments = 32    # in logfile segments, min 1, 16MB each

shared_buffers = 1GB        # min 128kB

work_mem = 8MB

maintenance_work_mem = 256MB

effective_cache_size=1024MB

default_statistics_target = 200

autovacuum_vacuum_cost_delay = 50ms


texts: extended events rpc_completed duration does not matching with rpc_starting

texts:
Does rpc_completed duration (micro_sec) = rpc_starting.timestamp - rpc_completed.timestamp?
In my case, I saw a big difference between them. Duration is only few millsec, where rpc_starting.timestamp - rpc_completed.timestamp is 6 - 10 sec.

I am on enterprise 2016 sp2-cu14, the duration field is in microsecond. I did similar thing for catching sp_statement_starting and sp_statement_completed, sp_batch_starting/sp_batch_completed inside rpc_starting and rpc_completed. it shows the same situation. My question is "which one tell me how much time the query actually took? Is it Duration in sp_statement_completed or the difference between starting and completed?"


texts: How would I improve my "algorithm" for figuring out my average spending on food per month?

texts:
I have a database table keeping track of every grocery delivery. It's stored as timestamps and sums (EUR).
Currently, I do this:
SELECT (SUM("amount in EUR") / 3)
FROM personal."food purchases"
WHERE "purchase time" >= now() - INTERVAL '3 months';

Now, let's say that the current day is 1st of September 2020. This "algo" will count together all the deliveries from exactly three months back from the current moment, which I guess means from the first day of June, and then simply slash this price into 3 to symbolize "three months".
Sometimes, depending on the day, this will give a reasonable value. Other times, and I cannot fully describe/understand why, this gives either a too high or too low value. I suspect that the reason for this is that my food purchases/deliveries don't always happen on the exact same day, and vary quite a bit in price as well. Thus, the resulting sum, even though I do an interval of three months and then slash with three, and thus "should" give the average food cost "recently", is oftentimes misleading.
I can't tell for sure, but I think that it sometimes includes one purchase "too many", and sometimes "one too few". It might even be worse than that.
Can you help me improve my formula to be more "fair"? I want to know the average amount I pay for food each month, and I'm not convinced that I'm getting that value with my current query.
I think I need to drop the part which goes three months back from the current time, and instead somehow calculates the... last three... months... um... Frankly, I don't know what I need to do. I just don't think that the current method is correct.
I'd appreciate if your suggestion/solution stays as close as possible to my original query. I want to understand what is happening. I actually suspect that this is a very common issue and probably built into PG and all other database softwares.


texts: Extract some columns data from one table and insert into multiple tables

texts:
I have a table with 16,000 records and need to get out some columns and then insert values in multiples tables. For example, suppose I have the following tables

tbl_1: id, name, lastname
tbl_2: id, name
tbl_3: id, lastname

I need to get the values from tbl_1 and then insert in tbl_2 and tbl_3 meaning tbl1.name goes to tbl_2.name and tbl_1.lastname goes to tbl3.lastname. Is this possible using SQL queries?

I'm using MySQL latest


texts: Saving a variable globally in SQL server it self and use it when required

texts:
We are using ENCRYPTBYPASSPHRASE to encrypt some columns of some database. To decrypt the values every time we need to pass passphrase with DECRYPTBYPASSPHRASE.

So I planned to create a stored procedure as shown below

CREATE function [dbo].[SQLEncrypt](@str varchar(max)) returns varchar(max)
as
 begin
 declare @test nvarchar(max)
 set @test=''

    set @test =   convert(varchar(MAX),DECRYPTBYPASSPHRASE ('password',@str))

 return @test
END

GO

I can use this stored procedure as shown below to decrypt a column

----usgae---

select master.dbo.SQLEncrypt(columnname) from tablename--
-----------------

Now I am planning to store this password somewhere globally in SQL server(Don't know if it is possible or not)  and then use it in stored procedure. IS it possible to do by anyway? Also can I hide the password from users?


texts: Logical page error while attempting to insert data

texts:
I am getting the following error while trying to insert a row in a table:

  Attempt to fetch logical page (1:12032) in database 19 failed. It
  belongs to allocation unit 72057594069450752 not to 72057594051166208.

What could be the reason?


texts: Use GROUP BY and return more than 2 columns

texts:
I am trying to find the latest unit_status, and the timestamp of that status (cdts), for every unid I have in a table.

This is what I have come up with so far:

with cte as
(
    select *
    from un_hi
    where ag_id = 'bfd' and cdts > '201708' and
    unit_status != 'uc'
)
select distinct(unid), max(cdts2) Last_GPS_Report_Date
from cte
where unid in ('C06','C07','D01','D03','D04','D06')
group by unid
order by unid

It correctly returns every unid and the time of their latest status (max(cdts)), but I want to see what the unit_status was.

I have tried taking this query and joining it to the UN_HI table (which is where this data is coming from), but I couldn't get that to work. Any other suggestions?


texts: Move SSAS database to another server without detach-attach

texts:
We take drive level snapshots of our source server every day, so we have copies of the folder OLAP\DATA.

We are trying to restore a database from 10th March and 11th March to a destination server with names like xyz_10march & xyz_11march.

Is that even possible?

Unfortunately, our company takes drive level snapshots every day for all servers irrespective of being SQL Server or SSAS. If I have to attach a SQL Server database, it's easy - but how do I attach it for an SSAS database?


texts: Add timestamp to field automatically in PostgreSQL

texts:
I created a small database in PostgreSQL to manage the data for my thesis. I just started with Postgres, so this is probably a newbie question. I added a column in several tables, that is supposed to hold the last update of a certain field. One of these tables looks like this (The column relevant here would be lastupdate:

-- Drop table

-- DROP TABLE public.finds;

CREATE TABLE public.finds (
    id bigserial NOT NULL,
    id_investigations int8 NOT NULL,
    quote_short varchar NULL,
    "group" varchar NULL,
    "type" varchar NULL,
    subtype varchar NULL,
    description varchar NULL,
    "number" int4 NULL,
    certainty bool NULL,
    note varchar NULL,
    checked bool NULL,
    lastupdate timestamptz NULL,
    CONSTRAINT finds_check_group CHECK ((("group")::text = ANY ((ARRAY['Keramik'::character varying, 'Waffe'::character varying, 'Werkzeug'::character varying, 'Schmuck'::character varying])::text[]))),
    CONSTRAINT finds_pkey PRIMARY KEY (id),
    CONSTRAINT id_investigations FOREIGN KEY (id_investigations) REFERENCES investigations(id)
);

-- Permissions

ALTER TABLE public.finds OWNER TO postgres;
GRANT ALL ON TABLE public.finds TO postgres;

I would like the lastupdate to take the current timestamp upon edit. Is there a simple way to do this?


texts: Ways to Upgrade Production MySQL db server from 5.5.42 to 5.7.13 with zero downtime

texts:
Our product is an enterprise application with MySQL Enterprise version 5.5.42 deployed in many customer datacenters. We're planning to migrate the MySQL db (bundled with our product) to version 5.7.13. So, when our customers upgrade from X version of product to Y version of our product, we need to upgrade their MySQL 5.5.42 to 5.7.13. Customers would hate to have downtime on this important software.

It appears that we need to migrate first to 5.6 and then to 5.7.

Some questions regarding the upgrade:

Is there a way to have zero downtime without requiring duplicate hardware?
Are there ways to reduce the downtime to a minimum ? If yes, what additional storage requirements will this add?

What else should I know?


texts: Which of these two methods is standard when creating a 1 to many database relationship?

texts:
If I have a customer that can have many addresses, I can create an Address table with columns Street, Town etc. and CustomerId. Then I can insert multiple records to have multiple addresses per customer.

Alternatively I can create multiple addresses and give them all the same AddressId, then in my customer table I can have an AddressId (so you'd do SELECT * FROM Address WHERE Address.AddressId = Customer.AddressId).

Which of these is better, is there some reason why you'd use one over the other, or is one of them just silly?


texts: Duration of reclaiming disk space on very large tables (2.5+ billion rows)

texts:
I'm using PostgreSQL 9.4.20 and have 1 table which at 1 point contained 2.5 billion rows. We've started migrating data and reduced the table size down to about 280 million rows.

If possible we'd like to reclaim the used disk space.I read up on VACUUM FULL and know that it will result in a full table lock during the operation. My question here is: given that a VACUUM of the table will take about 12000 - 13000 seconds, is there any way to estimate how long a VACUUM FULL will take give the current and previous size of the table?

Included is one of the VACUUM VERBOSE outputs during the deletion process:

INFO:  vacuuming "public.redacted"
INFO:  scanned index "redacted_prop_id_3df0d554fdfe92ee_idx" to remove 85063130 row versions
DETAIL:  CPU 105.78s/340.93u sec elapsed 3633.81 sec.
INFO:  scanned index "redacted_pkey" to remove 85063130 row versions
DETAIL:  CPU 35.37s/269.95u sec elapsed 1147.31 sec.
INFO:  "redacted": removed 85063130 row versions in 811189 pages
DETAIL:  CPU 12.00s/17.68u sec elapsed 342.43 sec.
INFO:  scanned index "redacted_prop_id_3df0d554fdfe92ee_idx" to remove 39116682 row versions
DETAIL:  CPU 101.30s/346.25u sec elapsed 3576.71 sec.
INFO:  scanned index "redacted_pkey" to remove 39116682 row versions
DETAIL:  CPU 40.21s/237.76u sec elapsed 1219.00 sec.
INFO:  "redacted": removed 39116682 row versions in 879306 pages
DETAIL:  CPU 22.85s/40.58u sec elapsed 817.57 sec.
INFO:  index "redacted_prop_id_3df0d554fdfe92ee_idx" now contains 1597286875 row versions in 1
DETAIL:  121144639 index row versions were removed.
4169907 index pages have been deleted, 3055311 are currently reusable.
CPU 0.03s/0.00u sec elapsed 0.30 sec.
INFO:  index "redacted_pkey" now contains 1597377401 row versions in 5999520 pages
DETAIL:  124175137 index row versions were removed.
1209655 index pages have been deleted, 894966 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.17 sec.
INFO:  "redacted": found 107389052 removable, 820955017 nonremovable row versions in 7045430 oes
DETAIL:  0 dead row versions cannot be removed yet.
There were 13483069 unused item pointers.
0 pages are entirely empty.
CPU 382.60s/1408.62u sec elapsed 12543.69 sec.
INFO:  analyzing "public.redacted"
INFO:  "redacted": scanned 30000 of 15055610 pages, containing 3170455 live rows and 0 dead rosample, 1591104467 estimated total rows


texts: Oracle 11g hash join vs nested loops question

texts:
I'm using Oracle 11g and working on a legacy system. I have two tables, A and B. A has ~40M rows and B ~4M rows. A has a foreign key on B (B is the parent of A).

A query does essentially this:

select count(A.id), sum(A.something1), avg(B.something2), ....
from A inner join B on A.id_b = B.id
where A.filter_column in (...)
group by A.filter_column

condition on filter_column is such that 5-10% of rows from table A are selected. Join picks up 20-25% of rows from B. The end result is about 100 rows that contain aggregated data from selected rows from tables A and B.

The query initially had a hint in it, forcing Oracle to do a nested loops join of A to B. By removing the hint, execution plan changes from nested loops to a hash join. I would expect that to run faster, however, to my great surprise, it actually runs slower! Trying to find out why, I collected statistics on execution using one of Tom Kyte's scripts ("runstats") and found out that the nested loops version of the query did 0 (!) physical reads while the hash join version did a lot of them. I ran the test many times, changing the order of the execution of the two queries so that the nested loops version runs first and hash join version runs second and vice versa, but the results were always about the same. Furthermore, the hash join plan produces less logical I/O but is still slower.

Does anybody have an explanation for that?

Thank you!


texts: Ignoring temp table in Postgres event trigger

texts:
I am trying to have a trigger that gets invoked when new tables, except temporary tables, are created.
This is what I have tried:
CREATE OR REPLACE FUNCTION insert()
RETURNS event_trigger
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        RAISE NOTICE 'caught % event on %', r.command_tag, r.object_identity;
    END LOOP;
 END;
 $$
 LANGUAGE plpgsql;

CREATE EVENT TRIGGER insert_event ON ddl_command_end
  WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'CREATE FUNCTION', 'ALTER TABLE', 'DROP TABLE')
 EXECUTE PROCEDURE insert();

create TEMP table my_table(id serial primary key);

This is the output I see:
CREATE TABLE
CREATE FUNCTION
CREATE EVENT TRIGGER
CREATE FUNCTION
CREATE EVENT TRIGGER
NOTICE:  caught CREATE SEQUENCE event on pg_temp.my_table_id_seq
NOTICE:  caught CREATE TABLE event on pg_temp.my_table
NOTICE:  caught CREATE INDEX event on pg_temp.my_table_pkey
NOTICE:  caught ALTER SEQUENCE event on pg_temp.my_table_id_seq

How do I exclude temporary tables from invoking the trigger?


texts: Compare The length of two string variables inside an Expression task in SSIS

texts:
Hope you're doing well.
I need to compare the Length of two string variables inside an expression task in SSIS so that if they have the same length the package will execute the next step and if not it will stop
I've implemented something like this so far but it won't work

The images show My expression task and the regular expression within the
    task and the precedence constraint from the expression task to the next  step


texts: How to generate a random character string of length 8 in Netezza?

texts: I am trying to generate a random character string of length 8 in Netezza, any suggestions?


texts: ORA-00904 invalid identifer error when using alias

texts:
I'm trying to return a product of two subqueries, and thought I'd make life easier by aliasing each of the subqueries, then divide the two aliases to get the number I'm after, like this:

select distinct
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest
  where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) as Ha_IN_RKB,
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest) as Ha_total,
ROUND(  (Ha_IN_RKB / Ha_total) ,3)
from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest

but this results in a 00904. 00000 -  "%s: invalid identifier" error (Oracle doesn't like Ha_IN_RKB or Ha_total ).

Re-writing the query as follows works, but is rather bulky:
select distinct
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest
  where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) Ha_IN_RKB,
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest)  Ha_total,
ROUND(  ((select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest
  where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) /
  (select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest)) ,3) as RKB_pct
from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest

Anybody know why I can't just make this ROUND(  (Ha_IN_RKB / Ha_total) ,3) work ?


texts: Database Schema with a shared identity Field

texts:
I'm a developer treading into the database world so bear with me please.

I'm creating a Contract database with separate tables for various types of contracts.

I have 5 tables but would like to share a common 'Identity' field between them, so that there will be a unique contract # for any given contract regardless of type.

What is the recommended way to do this? Something that won't over complicate simple crud transactions?

My application will be using a Sql Server 2008 R2 server..the app itself is Silverlight 4 using WCF Ria services for CRUD transactions.

A schema idea would be great, a tutorial of something similar ideas and technology would be even better.

Thanks for looking
Robert


texts: How to configure postgresql-9.2 to know blk_write_time in pg_stat_database

texts:
Interested in the procedure for collecting block write statistics of PostgreSQL 9.2.

Steps followed for configuration, could get blk_read time but not blk_write_time

CREATE EXTENSION adminpack; and CREATE EXTENSION dblink;
Changed postgresql.conf file

# - Query/Index Statistics Collector -

track_activities = on
track_counts = on
track_io_timing = on
track_functions = all           # none, pl, all
track_activity_query_size = 1024    # (change requires restart)
update_process_title = on
stats_temp_directory = 'pg_stat_tmp'

# - Background Writer -

bgwriter_delay = 200ms          # 10-10000ms between rounds
bgwriter_lru_maxpages = 100     # 0-1000 max buffers written/round
bgwriter_lru_multiplier = 2.0       # 0-10.0 multipler on buffers scanned/round

Restarted Postgres Server
Performed around 2000 inserts to table
But could not view the blk_write_time.


texts: MySQL MyISAM corrupted table repair failure

texts:
My MyISAM tables got corrupted(I guess due to fs corruption)

filesystem_cstor.MDS_DATA
warning  : Table is marked as crashed
warning  : Table is marked as crashed
error    : Key in wrong position at page 228804608
error    : Corrupt

filesystem_cstor.OSS_DATA
warning  : Table is marked as crashed and last repair failed
warning  : Size of datafile is: 94746       Should be: 0
error    : got error: 0 when reading datafile at record: 5237
error    : Corrupt

filesystem_cstor.OST_DATA
warning  : Table is marked as crashed
warning  : Found row where the auto_increment column has the value 0
error    : Found 396340 keys of 394727
error    : Corrupt

filesystem_cstor.TIMESTAMP_INFO
warning  : Table is marked as crashed
error    : Can't read key from filepos: 285765632
Error    : Incorrect key file for table './filesystem_cstor/TIMESTAMP_INFO.MYI'; try to repair it
error    : Corrupt

filesystem_cstor.migrate_version
Error    : Table 'filesystem_cstor.migrate_version' doesn't exist
status   : Operation failed

Later we fixed the fs issues and I tried to recover from the corrupted tables above using following command,

mysqlcheck -A --auto-repair

But my repair operation failed with the following error,

Repairing tables
filesystem_cstor.MDS_DATA                          OK
filesystem_cstor.OSS_DATA
warning  : Duplicate key for record at 18683 against record at 17170
warning  : Duplicate key for record at 20196 against record at 17170
warning  : Duplicate key for record at 21709 against record at 17170
warning  : Duplicate key for record at 23222 against record at 17170
warning  : Duplicate key for record at 24735 against record at 17170
warning  : Duplicate key for record at 26248 against record at 17170
warning  : Duplicate key for record at 27761 against record at 17170
warning  : Duplicate key for record at 29274 against record at 17170
warning  : Duplicate key for record at 30787 against record at 17170
warning  : Duplicate key for record at 32300 against record at 17170
warning  : Duplicate key for record at 33813 against record at 17170
warning  : Duplicate key for record at 35326 against record at 17170
.....
warning  : Duplicate key for record at 87584 against record at 17986
warning  : Number of rows changed from 0 to 993
status   : OK

filesystem_cstor.OST_DATA
warning  : Duplicate key for record at 11684489 against record at 9189589
warning  : Duplicate key for record at 10433074 against record at 8973222
warning  : Duplicate key for record at 12141745 against record at 11931356
warning  : Duplicate key for record at 9403943 against record at 8749596
warning  : Duplicate key for record at 11491119 against record at 10110506
warning  : Duplicate key for record at 9982772 against record at 8945406
warning  : Duplicate key for record at 11921962 against record at 8427943
warning  : Duplicate key for record at 10573313 against record at 10246353
warning  : Duplicate key for record at 8797969 against record at 8795285
warning  : Duplicate key for record at 8815781 against record at 8807668
warning  : Duplicate key for record at 10536347 against record at 10523110
...
warning  : Number of rows changed from 394727 to 293275
filesystem_cstor.TIMESTAMP_INFO                    OK

filesystem_cstor.migrate_version
Error    : Table 'filesystem_cstor.migrate_version' doesn't exist
status   : Operation failed

Here I can see that except one all the tables have been successfully recovered. The one that failed is failing due to table doesn't exist.

I do not understand why I am getting this issue because that table was initially present. Could it be a chance that fs corruption has eated up this file and corrupted it's .frm or .myi file?
What does the error mean,

warning  : Duplicate key for record at 10536347 against record at 10523110

Does this mean that someof the data would be lost, this is what I got from the following line,

warning  : Number of rows changed from 394727 to 293275

I would really appreciate pointers links or direct answers.
Thanks!


texts: can access to a view be controlled by access to a referenced table?

texts:
I have a table Table_A and a view View_A that is a select of the contents of Table_A.

If I deny SELECT on Table_A to a user, the user can still use the view View_A.

Is there a way that I can force the view to return an error when the user tries to use it?

The real world case here is a database with about 20 tables and hundreds of views.  User access is logically tied to the tables so we are hoping to stop folks from using the views based on those tables.


texts: What methods are available to create a snapshot of an oracle database for testing purposes?

texts:
I am new to databases and have just been assigned to work with Oracle. We want to create a snapshot of the database for testing purposes that will allow us to revert back once we're done.

I'm a bit confused as to what my options are.

I've looked into creating a standby. It seems like that may be overkill for what we need and the pages describing how to create one are confusing to one with as little experience as I.

Questions

Is a standby the best fit or is import/export a viable option?
Are there other methods?.

Thanks!


texts:
I am a new to Pl/sql and I need to learn to write simple pl/sql scripts.
Is there any difference between cursors and records in PL/sql?
So far I find them both similar.


texts:
Cassandra uses Cassandra File System (CFS) and Hadoop uses Hadoop Distributed File System (HDFS)

I'm already using Pig over HDFS and i would like to install Cassandra.

Having additional servers for Cassandra is might inefficient to the fact that i already have Hadoop setup.

Can Cassandra use HDFS instead of CFS?


texts: Replicate multiple times a set of rows with unique column in a table

texts:
I wish to replicate a set of rows in a table but with handling unique column in SQL Server 17.
The table is:
CREATE TABLE test1 (
    id int CONSTRAINT PK_test1 PRIMARY KEY  CLUSTERED IDENTITY,
    name nvarchar(10) CONSTRAINT UK_test1 UNIQUE NONCLUSTERED,
    value int,
    type varchar(20)
);

The table contents are:
INSERT INTO test1 VALUES ('e_1',1,'A');
INSERT INTO test1 VALUES ('342A',34,'ABC');
INSERT INTO test1 VALUES ('Tame_33',277,'Test10');

Now I wish to replicate those rows multiple times but the problem is with NAME column which is UNIQUE.
The solution I did so far is just use of a cursor, but this is inefficient and I'm curious if this can be done in more efficient way?
DECLARE @id int;
DECLARE @name nvarchar(20);
DECLARE @value int;
DECLARE @type varchar(10);
DECLARE @cnt int = 0;stackoverflow
DECLARE @intcnt int = 0;

DECLARE temp_cursor CURSOR FOR
SELECT * FROM test1
OPEN temp_cursor;
FETCH NEXT FROM temp_cursor INTO @id,@name,@value,@type;

WHILE @@FETCH_STATUS = 0
BEGIN
    WHILE @intcnt < 5
    BEGIN
        INSERT INTO test1 VALUES
        ('ins_' + CONVERT(varchar,@intcnt) + '_'  + CONVERT(varchar,@cnt), @value, @type);
        SET @intcnt = @intcnt + 1;
    END

    SET @intcnt = 0;

    FETCH NEXT FROM temp_cursor INTO @id,@name,@value,@type;
    SET @cnt = @cnt + 1;
END

CLOSE temp_cursor;
DEALLOCATE temp_cursor;


texts: Postgresql 9.5 BRIN index dramatically slower than expected

texts:
I have a table with over 12 million rows of log data and have migrated to Postgres 9.5 to take advantage of the new BRIN index because I have disk space limitations.  I assumed that my situation was made to order for BRIN indexing given the natural ordering by date of my log lines.

However, I'm started by the results. BRIN is more than an order of magnitude slower than btree.

Original Btree index:

EXPLAIN ANALYZE SELECT COUNT(*) from logline where date BETWEEN '2016-01-15' and '2016-01-31';

 Aggregate  (cost=153488.38..153488.39 rows=1 width=0) (actual time=7672.508..7672.509 rows=1 loops=1)
   ->  Index Only Scan using logline_date on logline  (cost=0.43..145945.76 rows=3017046 width=0) (actual time=18.548..4084.455
rows=2977593 loops=1)
         Index Cond: ((date >= '2016-01-15 00:00:00-05'::timestamp with time zone) AND (date <= '2016-01-31 00:00:00-05'::timestamp with time zone))
         Heap Fetches: 5809
 Planning time: 0.293 ms
 Execution time: 7672.562 ms
(6 rows)

DROP index logline_date
CREATE index logline_date_brin on logline using BRIN(date)

 EXPLAIN ANALYZE SELECT COUNT(*) from logline where date BETWEEN '2016-01-15' and '2016-01-31';

 Aggregate  (cost=1230518.30..1230518.31 rows=1 width=0) (actual time=105789.131..105789.133 rows=1 loops=1)
   ->  Bitmap Heap Scan on logline  (cost=31543.27..1222862.87 rows=3062173 width=0) (actual time=103.876..100675.372 rows=2977593 loops=1)
         Recheck Cond: ((date >= '2016-01-15 00:00:00-05'::timestamp with time zone) AND (date <= '2016-01-31 00:00:00-05'::timestamp with time zone))
         Rows Removed by Index Recheck: 2899899
         Heap Blocks: lossy=696832
         ->  Bitmap Index Scan on logline_date_brin  (cost=0.00..30777.73 rows=3062173 width=0) (actual time=103.079..103.079 rows=6968320 loops=1)
               Index Cond: ((date >= '2016-01-15 00:00:00-05'::timestamp with time zone) AND (date <= '2016-01-31 00:00:00-05'::timestamp with time zone))
 Planning time: 0.377 ms
 Execution time: 105805.567 ms
(9 rows)

The BRIN index was >600 times smaller than the Btree, but I didn't expect the execution time to be so much slower.

Does that mean BRIN is not for me, or am I doing something wrong?


texts: Setting Blank lines to ON before executing a SQLFILE in shell script

texts:
echo exit | sqlplus username/password@host_ip:port/Service_name @SQLFILENAME

I want  to execute set sqlbl on before executing The SQLFILENAME and also want that after executing SQLFILENAME it should exit sqlplus


texts: SQL Server 2012 Table Partitioning - Have I done it correctly?

texts:
I have a table that stores accounting transactions for what we call "jobs".

The table looks like this:

(
REGION char(3)
JobNumber char(17)
SeqNum decimal(5,0)
.....
.....
.....
AcctYear decimal(4,0)
)

Unpartitioned, the PK and clustered index is Region, JobNumber, SeqNum.

What we want to do is partition the table by year (AcctYear column), and have each partition located on a separate filegroup. The current and previous year will reside on an SSD and previous years beyond that will reside on cheaper disk storage. Each year has approx. 40 million rows.

I have already partitioned the table, but I am questioning whether I have done it correctly. Let me explain why...

What I have done is added the AcctYear to the end of the PK/Clustered Index (i.e made the PK/CI Region, JobNumber, SeqNum, AcctYear), created the function and schema etc. I have setup 30 partitions, one for every year from 2001 till 2030.

The reason I chose this method of adding the AcctYear to the end of the PK/CI was that I don't always expect the AcctYear to be specified in queries/joins, and if I put the AcctYear at the beginning of the Clustered Index, I would have to either always specify the AcctYear in every query and join, or create another secondary index only on the existing primary key columns.

What makes me doubt what I have done is that when I query for a specific job number AND AcctYear, the Actual Execution Plan shows that it is still looking through the 31 partitions, instead of knowing which partition that data is in. The SHOW STATISTICS IO, also says 31 seeks are being done.

So, have I done this partitioning correctly based on what I want to achieve? or Is there a better way?

Thanks for any help in advance!


texts: Dropping Foreign Key In Production Environment

texts: I have been asked to drop foreign keys on a number of databases in our production environment. I am relatively new to MySQL... what do I need to be aware of when doing this? E.g. full table locks, rebuilding indexes, etc.


texts: How do I find out how far along my PostgreSQL query is?

texts:
I have a pretty decent idea of how many rows my SELECT...INTO query will actually process (e.g. I know how many will materialize).

I understand Postgres won't tell me percentage completeness, is there a way (buried deep in logs, system tables, or otherwise) that I can find out how many rows have been pumped into the destination table or have been read by the SELECT query?


texts: How to see if index is being read "ordered" from DMVs

texts: I have a table that is being used in many reporting queries. I would like to know how many times the table is read in an "ordered" way. In the execution plan for clustered index scan one can see if the read is "ordered". Is this information available other places or is the only solution to search the plan cache?


texts: select items from multiple rows and add to one

texts:
I need some help on SQL as this kind of selecting is beyond my knowledge. The result of the select should be in one row as it is shown in the picture. Can someone provide any ideas on how to achieve this?

if col_name1 =AA -> add col_name2 as value col_name5

if col_name1 =BB -> add col_name3 value as col_name6

if col_name1 =CC -> add col_name4  value of first CC (c31) as col_name7 and col_name4 of second CC (c31) value as col_name8

original table might not have all 4 ids


texts:
For a new SQL Server installation, I plan a disk layout.

My new server has a 10 disk bay, I also plan to use 256GB SSD as 5x Raid1.

My idea is:

OS install W2k19
SQL Server 2019 standard + USER DB data
USER DB logs
tempdb
Backups

I checked some discussion, but just don't know which one will be better solution, also my questions are:

What's better, OS separately and SQL Server + user DB data together or OS+SQL Server on one disk and USER DB data separately
For tempdb need mirrored disk??
Tempdb logs with tempdb data on same disk or tempdb logs together with USER DB logs?


texts: Unrecognized configuration parameter when using set_config(text, text, boolean) in CTE

texts:
I'm trying to implement a simple ABAC system using row level security, with main policy defined as following:
CREATE policy resource_access ON resource
USING (
  (
    org_id::varchar = current_setting('scope.org.id', true)
  )
  AND
  (
    acl_read && regexp_split_to_array(current_setting('scope.acl'), ',')::varchar[]
  )
)

Issuing queries akin to:
WITH
  acl AS (SELECT set_config('scope.acl', 'ACL', true) "__acl"),
  result AS ( ... )
SELECT * FROM acl, result

With the main reason to use WITH is to avoid multiple statements when queries are later PREPAREd and EXECUTEd by the Postgres driver I'm using.
The result in example above can contain any arbitrary queries required by the application. To ensure that set_config is executed in the query, it's also added to the final SELECT.
However, I still do consistently encounter the following error:
QueryFailedError: unrecognized configuration parameter "scope.acl"

Which appears to be caused by executing the subquery from WITH in isolation from the result query.
So the main questions are:

Is there any elegant way to ensure running set_config before the main query (the one in result) is executed?
Is there any better way to construct queries for the application side, to avoid using WITH, but keeping them as a single SQL statement?

Thank you!


texts: What is the relation between Tablespaces, .frm & .ibd files and database pages in MySQL?

texts:
I looked in the documentation but there was not a clear answer regarding their relation, can someone give an real life example of their relation in MySQL?

Database pages are smallest read write unit in databases and is usually 8KB, but:

What is the relation between MySQL pages and .frm & .ibd files?
Is .frm files consists of many pages coming together?
If so then does the .frm files have any type of info on the pages that are inside them?
Like the location of each page and stuff like that?
Where can i read more about this?
And what about tablespaces?
Is every .frm file a tablespace?

I read their documentation for hours but it got me even more confused

(I'm talking about the InnoDB storage engine BTW)


texts:
I have Oracle 10gR2 installed on Redhat Linux 5.4 on two servers (primary and physical standby).

When I import data into the primary server while the standby is started it takes too long to complete (didn't wait for it to finish). When I shutdown Oracle on the standby server it completes in 15 minutes.

Is this really due to standby?  How can I speed up this slow imp?  Here is the command line used:

imp userid=/ file=base.dmp ignore=yes log=log1.log


texts:
We've built a new Windows 2016 VM with SQL Server 2016 that we'll migrate a mission critical database currently on SQL Server 2008 R2 to. The original plan was to have users test, test, test and test each data feed coming in. There is pushback from our integration team on repointing the feeds because no one knows where they all are and of course never documented anything. The suggestion for the production cutover is to take the old Windows machine offline, rename the new Win server to the old Win servername , rename the SQL Server and hope everything works.

This idea of course frightens me and my position is I've never even heard of this being done and it's a great documentation opportunity.

Other than we'd be going into production with all the feeds untested, are there other caveats to doing this if I'm overruled?