texts: Optimization paramters read-only mysql 5.7 database?

texts:
What's the best way to configure a MySQL database that is only read-only, and will never have any modifications made to it?

1) I have increased my innodb_buffer_pool_size to 70% of available RAM

2) Increase query cache size to 40mb

Running against a load generator, after some time the database starts bottlenecking to only 1000 statements per second.

The configuration is 2-cores, 8GB ram 50GB SSD. The database contains a single InnoDB 13 GB table with the same exact query running on two indexed columns.

We are using a vertx server to hit the database. The symptom we see is that the web service performance degrades after 15-20 minutes of heavy load.

Where can the bottleneck be? What parameters should we optimize?


texts: how to Grant Permission on a table to a user on the other database in oracle

texts:
Hi I have a table in my schema to be accessed from a user from Another Oracle databse..

I am trying with

GRANT all on a.table_name to test@o11db;

here a is my Schema and Test is another schema in o11db. But I am not able to get  it right.


texts:
In my application, my INSERTs seem to be taking a major chunk of the time. I have a large number of objects in memory (~40-50,000) which i want to insert into a table.

Lets take a sample table

CREATE TABLE bill (
id BIGINT(20) PRIMARY KEY,
amount INT(11) DEFAULT 0,
bill_date DATETIME DEFAULT NOW(),
INDEX (bill_date)
) ENGINE=InnoDB

Taking 3 rows as my batch size, the following are the approaches i could think of for inserting

Approach 1 - construct and fire 3 raw inserts

INSERT INTO bill (amount, bill_date) VALUES (10, '2012-01-01 00:00:00');
INSERT INTO bill (amount, bill_date) VALUES (20, '2012-01-02 00:00:00');
INSERT INTO bill (amount, bill_date) VALUES (40, '2013-02-05 00:00:00');

Approach 2 - clubbing the values into 1 query

INSERT INTO bill (amount, bill_date) VALUES
(10, '2012-01-01 00:00:00'),
(20, '2012-01-02 00:00:00'),
(40, '2013-02-05 00:00:00');

Approach 3 - fire this query 1 time passing 6 parameters

INSERT INTO bill (amount, bill_date) VALUES
(?, ?), (?, ?), (?, ?);

Approach 4 - Fire this prepared query 3 times changing the 2 parameters each time

INSERT INTO bill (amount, bill_date) VALUES (?, ?);

Any other approaches are welcome.

My question is

What is the quickest way to make multiple inserts in a table?

I have read this link on mysql insert speed and this guide to JDBC programming, but i'm not able to come to a conclusion.

My case -

Currently my table has ~ 20 columns, most of which are numbers, with a couple of varchar(60) and 1 text column. Mysql version 5.5.
Running on INNODB and has 1 index on Integer primary keys.
All the queries run in transaction.

I construct my queries from Java and use Spring JDBC to run the queries.

I am currently following Approach 3, It is taking around 10 seconds for 20,000 inserts to an empty table, not including the time it takes to construct the query.

To keep things in perspective, its taking 100-200 millis to fetch the data from the table.

Is there something i am missing?
How do i make the inserts faster?


texts: MySQL - Seconds shown for query run time is incorrect

texts:
I came into work this morning, and first thing I noticed is that one of my Slave databases was running behind.

I took a look at the processlist and could see that I had the following running.

SELECT Query (1091724 seconds)
SELECT Query (1031500 seconds)
SELECT Query (1031443 seconds)
INSERT Query ( 201692 seconnds)
ALTER Query (  195500 seconds apx)

They all use the same table.

The thing is, I am pretty certain that the select queries weren't running that long.

I checked SHOW ENGINE INNODB STATUS to see what was going on that confirmed the selects were active for that amount of time.

---TRANSACTION 2499F3C3, ACTIVE 1031443 sec fetching rows
---TRANSACTION 2499EB0F, ACTIVE 1031500 sec fetching rows
---TRANSACTION 2486B035, ACTIVE 1091724 sec fetching rows

Is it possible that MySQL was counting the seconds incorrectly?


texts: How to increase size of temp tablespace in Oracle 12C RAC with ASM?

texts:
I have an Oracle 12c RAC consisting of two nodes (OL6) and ASM. I use it for learning. For some reason I want to increase its temporary tablespace size. It's about 50M now. I've considered it using SELECT * FROM dba_temp_free_space; command. I want to increase it to 2048M.
I know, how to increase it in usual configuration:
ALTER DATABASE TEMPFILE '/u02/oradata/my_sid/temp01.dbf' RESIZE 2048M;

knowing temporary tablespace file's path.
I've tried to get information about database files' to see if there are some files with the word "temp" in their names:
select * from dba_data_files;

but I've only got the next files' names:
+DATA/ORCL/DATAFILE/system.287.970304491
+DATA/ORCL/DATAFILE/sysaux.288.970304523
+DATA/ORCL/DATAFILE/users.290.970304549
+DATA/ORCL/DATAFILE/undotbs1.289.970304547
+DATA/ORCL/DATAFILE/undotbs2.295.970304621

So, how to get proper path to a tempXX.dbf file and resize it?


texts: How to make tables unavailable for users during certain times in Oracle?

texts:
Iam a student and one of the questions i am stuck is to implement an IT management rule on all tables in oracle in my own schema using pl/sql.I dont have DBA previlege and the requirement is that on fridays from 08:00-09:00 none of the tables should be available and should display message saying its maintainance time. so basically I have prevent myself from accessing the tables in my schema during those times. I have used the following DML trigger for insert,update and delete

create or replace TRIGGER test_trig

 BEFORE INSERT OR UPDATE OR DELETE ON employees
 FOR EACH ROW
 WHEN (TO_CHAR (SYSDATE, 'DY')='THU' AND
(TO_CHAR(SYSDATE, 'HH24:MI'))BETWEEN '20:25' AND '20:30')

DECLARE
 e_invalid_time EXCEPTION;

BEGIN
 //DBMS_OUTPUT.put_line (' Tables are unavailable at this time.Please try again
later');
RAISE_APPLICATION_ERROR(-20500, 'You may insert'
||' into EMPLOYEES table only during '
||' normal business hours.');

EXCEPTION
WHEN e_invalid_time THEN
DBMS_OUTPUT.put_line (' Tables are unavailable at this time.Please try again
later');
 END test_trig;

But this will only be effective on one table. And SELECT queries will be still performed.I have about 6 tables
I read Fine grain auditing will help with select and all DML commands. what is the bet approach?This is for educational purposes.


texts: Selecting a value from a row where another column is max

texts:
I have the following SQL query:
SELECT bug.`id`,
       Max(report.`date`),
       Count(report.`id`),
       Max(version.`code`),
       bug.`id`
FROM   `bug`
       LEFT OUTER JOIN `stacktrace`
                    ON ( stacktrace.`bug_id` = bug.`id` )
       CROSS JOIN `version`
                  LEFT OUTER JOIN `report`
                               ON ( report.`stacktrace_id` = stacktrace.`id` )
WHERE  stacktrace.`version_id` = version.`id`
GROUP  BY bug.`id`
ORDER  BY Max(report.`date`) DESC;

I now want to select version.name instead of version.code from the row where version.code is maximal. Is this possible? If so, how do I do this with minimal amount of queries/overhead?

Relevant Tables (stripped):
CREATE TABLE `bug` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

INSERT INTO `bug` VALUES();
INSERT INTO `bug` VALUES();

CREATE TABLE `version` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `stacktrace` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bug_id` int(11) NOT NULL,
  `version_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_s_v` (`version_id`),
  KEY `FK_s_b` (`bug_id`),
  CONSTRAINT `FK_s_b` FOREIGN KEY (`bug_id`) REFERENCES `bug` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_s_v` FOREIGN KEY (`version_id`) REFERENCES `version` (`id`) ON DELETE CASCADE
);

CREATE TABLE `report` (
  `id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  `stacktrace_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_r_s` (`stacktrace_id`),
  CONSTRAINT `FK_r_s` FOREIGN KEY (`stacktrace_id`) REFERENCES `stacktrace` (`id`) ON DELETE CASCADE
);

As SQLFiddle


texts:
I'm running the following command to create a dump of all database on my production server:

prodserver:/# pg_dumpall -U postgres -h 1.1.1.1 > prod.sql

on my development box, I also take a backup by doing:

devserver:/# pg_dumpall -U postgres -h 1.1.1.1 > dev.sql

Then I try to restore the production database on to development like so:

devserver:/# psql -f prod.sql -U postgres

It runs with no errors. And it appears to be creating tables /import data. ( I know because if I run it a second time, I get messages that certain data already exists / relationships already exist. )

But when I log in like so:

psql -U postgres -d databasename

the data looks the same as the old dev data.
I tried the following with the production data supposedly restored:

select count(*) from widgets;

I compare that with the count returned when I'm using the development database and they are the same.

I've also tried restarting postgresql after attempting the restore...it's a no go.

Any suggestions on what I might be doing wrong would be appreciated.

Thanks.

EDIT 1

I also checked the following:

I opened up the prod.sql file and found insert statements like:

INSERT INTO widgets_to_wiget_groups VALUES (363, 15);

And then I did a select statement on the database to see if I had this entry in the table but I don't... maybe it's restoring it but under the context of another user?

EDIT 2

I've also tried

 psql -f prod.sql -U postgres -h 1.1.1.1

but it still didn't work
As a test I tried restoring the dump file on another new server... same results.  So I checked and saw that in the dump file, there is no CREATE DATABASE command.
I'm thinking that the original command to dump the file is incorrect...


texts: How much time should a backup of a 100Gb database take using streaming server setup?

texts:
I've installed barman on a dedicated server to backup all my postgresql servers. One of the server is now using 100Gb space and the instruction barman backup myserver takes 23 hours. I find it too long, but I have no reference points. Is it common? What should I investigate to improve such situation? Here are the settings for streaming the server

; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; Backup settings (via pg_basebackup)
; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
backup_method = postgres
;streaming_backup_name = barman_streaming_backup

; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; WAL streaming settings (via pg_receivexlog)
; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
streaming_archiver = on
slot_name = barman
;streaming_archiver_name = barman_receive_wal
;streaming_archiver_batch_size = 50

minimum_redundancy = 3
last_backup_maximum_age = '3 DAYS'
retention_policy = 'RECOVERY WINDOW OF 16 DAYS'


texts: Override database option in a user-specific .my.cnf config file

texts:
Using MariaDB 10.2.15 (Linux Debian Stretch)

I have created a ~/.my.cnf config file in my home directory in order to set a default database at connection time for all local client tools:

[client]
user=myuser
password=mySecretPassword
database=mydb

But I want to unset (skip) option database for a specific client program (mysqldump). This is to prevent such warning when running mysqldump:

Warning: /usr/bin/mysqldump: ignoring option '--databases' due to
invalid value 'mydb'

I was hoping using skip-OPTIONNAME in a [mysqldump] section would have worked, but unfortunately it doesn't.

[client]
user=myuser
password=mySecretPassword
database=mydb

[mysqldump]
skip-database

In fact, I noticed that database is multi-value. So overriding it is not really possible. For example, the following .my.cnf config file:

[client]
user=myuser
password=mySecretPassword
database=mydb

[mysqldump]
database=mydb2

produces 2 warnings when running mysqldump:

Warning: /usr/bin/mysqldump: ignoring option '--databases' due to
invalid value 'mydb'
Warning: /usr/bin/mysqldump: ignoring option '--databases' due to
invalid value 'mydb2'

Is there a way to "unset" option database in an overridden section?


texts: When was the last time my table was vacuumed?

texts:
I am running Postgres 9.6.
I have recently been reading of the importance of VACUUMing in postgres. I am looking for a SQL statement that could return a list of my tables and when was the last time they were vacuumed. Does a statement like this exist?


texts: Alternative tools to export Oracle database to SQL Server?

texts:
I've got an Oracle database that I need to export (schema and data) to SQL Server.

I am trying the Microsoft SQL Server Migration Assistant for Oracle, but it is horribly slow, grossly inefficient and very un-user-friendly, e.g. I was having problems connecting to the SQL Server DB during data migration - but it still spent ~5 minutes preparing all the data before attempting a connection to SQL Server, then when it failed, the 5 minutes of preparatory work were wasted.

Right now, I'm just trying to connect to another Oracle DB using this tool, I left it overnight and came back this morning, and it's still stuck on 19% of "Loading objects..."  And this is on a machine with a good 18GB RAM, of which maybe 8.5 GB currently in use.  Task Manager shows me that SSMAforOracle.exe is using 0 CPU, 0 PF Delta, and no change whatsoever in memory usage. In other words: frozen stiff. Absolutely intolerable.

Are there any other tools out there that can migrate an Oracle DB to SQL Server a little more efficiently?


texts: Filling in missing dates in record set from generate_series()

texts:
Consider:

with days as (select day::date
from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day
)
select 'Inspections'::text as data_label,
count(i.reporting_id) as daily_count, d.day as date_column
from days d
left join inspection i on i.close_case_date = d.day
group by d.day
order by d.day

This returns a set that looks like this:

data_label | daily_count | date_column
Inspections    1           01/01/13
Inspections    2           01/02/13
Inspections    4           01/04/13
Inspections    8           01/06/13

Note the 1 and 2 day gaps in the record set. I need to generate a set with those values filled in with 0 like so:

data_label | daily_count | date_column
Inspections    1           01/01/13
Inspections    2           01/02/13
Inspections    0           01/03/13
Inspections    4           01/04/13
Inspections    0           01/05/13
Inspections    8           01/06/13

How would I do this? I am not much of a Database Developer and am new to Postgres, so I am not sure where to start and can not find anything on how to accomplish this objective.


texts: Error while starting Cassandra 2.0.2 service

texts:
I am new to cassandra NoSQL database.

When I tried to start cassandra I am getting bellow error.

Command used to start.

./cassandra -f

xss =  -ea -javaagent:./../lib/jamm-0.2.5.jar -XX:+UseThreadPriorities -XX:ThreadPriorityPolicy=42 -Xms1024M -Xmx1024M -Xmn100M -XX:+HeapDumpOnOutOfMemoryError -Xss256k
numactl: execution of `': No such file or directory

Thanks!


texts: When you switch Table A to Table B, does the index data get switched as well?

texts:
I currently have a rather large table (5-7 million rows). This table is rebuild regularly by a procedure which builds the data in a staging table, then switches the data into the production table using the ALTER TABLE .. SWITCH TO .. statement.

Example:

BEGIN TRAN;

-- Rebuild indexes
ALTER INDEX IX_NC_GroupEvent_staging_GroupName on [dbo].[GroupEvent_staging]
   REBUILD;

ALTER INDEX IX_NC_GroupEvent_staging_Created ON [dbo].[GroupEvent_staging]
   REBUILD;

-- Empty production table
TRUNCATE TABLE [dbo].[GroupEvent];

-- Switch data from staging-table into production table
ALTER TABLE [dbo].[GroupEvent_staging] SWITCH TO [dbo].[GroupEvent]

COMMIT;

When this operation is performed, does the current state of the indexes (or index data if you will) get switched as well? I am asking because of 2 reasons:

1) In order to perform a SWITCH TO statement, a requirement is that both the source and target table must contain identical indexes. This leads me to believe the index-data might be switched as well, but I don't know how to verify this.
2) The main benefit of building the table in this manner is to avoid performing excessive work on the production table while in use. Naturally, it would make me very happy if I were able to rebuild indexes on the staging-table and have the rebuilt indexes being switched over to the production indexes along with the table.


texts: WAL Archival not working in PostgreSQL V9.4.9 DB server

texts:
Our DB servers are on VM, during planned server restart for system patch deployment our Master DB server crashed.

We have taken the clone of Primary Slave VM and restore the Master DB using last basebackup taken just before the patch deployment activity.

Backup was successfully restored but after that our pg_xlog location was changed to data directory, which was previously mapped to different mount point on the file system. Due to the said change our WAL archival is failing with below error message:

  [85807-1] db=,user=,app=,client=LOG:  archive command failed with exit code 1
  [85808-1] db=,user=,app=,client=DETAIL:  The failed archive command was: cp -i pg_xlog/00000007.history /Archive/00000007.history && scp pg_xlog/00000007.history postgres@Primary Slave IP:/Archive/00000007.history && scp pg_xlog/00000007.history postgres@Secondary slave  ip:/Archive/00000007.history
  cp: overwrite â/Archive/00000007.historyâ?
  Permission denied, please try again.
  Permission denied, please try again.
  Received disconnect from Primary Slave port 22:2: Too many authentication failures
  Authentication failed.
  lost connection

And since WAL archival was not working our base backup is also failing with following error message.

  transaction log start point: 10D/DA000028 on timeline 8
  69577578/69577578 kB (100%), 1/1 tablespace
  transaction log end point: 10D/DA911D20
  pg_basebackup: base backup completed
  tar: Cowardly refusing to create an empty archive
  Try tar --help or tar --usage for more information.
  du: cannot access âpg_archbackup_2020-05-10.tar.gzâ: No such file or directory
  tar (child): /Backup/pg_online/2020-05-10/pg_archbackup_2020-05-10.tar.gz: Cannot open: No such file or directory
  tar (child): Error is not recoverable: exiting now
  tar: Child returned status 2
  tar: Error is not recoverable: exiting now
  tar: pg_archbackup_2020-05-10.tar.gz: Cannot stat: No such file or directory
  tar: Exiting with failure status due to previous errors

Please help in resolving the above mentioned issue.


texts: False-failures when mirrored in high-safety mode without a witness

texts:
A number of DBs are mirrored in high-safety mode without a witness. I'm trying to understand what might happen in the case that a DB (or DBs) experiences a false-failure while the primary server remains up and functioning and all other mirrored DBs are in their normal SYNCHRONIZED state.

Would the mirroring state for the false-failure DB become DISCONNECTED but, because the server had not failed, the DB on the primary still be able to accept connections and do transactions, although it would be running unprotected?  Or, would the DB on the primary become DISCONNECTED and unavailable?  And, what would be the recovery for this?  Is it most likely to be a simple ALTER DATABASE  SET PARTNER RESUME?

Previously, we've had these DBs in high-safety mode with witness (auto failover) and increased the timeout to 20 seconds to eliminate false-failures, which worked well for a year and a half.  But, this week, we had a single DB failover for unknown reasons.  I've increased the timeout to 30 seconds but we're also considering removing the witness because the app needs manual intervention in the case of failover anyway so we might as well make the DB failover manual as well.  I'm fine with dealing with a planned failover but I'm not so comfortable with the case I described.


texts: How can I display twice each stored row without using UNION ALL or a temporary table?

texts:
The relevant table, named emp, holds the following data:

CREATE TEMPORARY TABLE emp AS
SELECT * FROM ( VALUES (1,'A'), (2,'B'), (3,'C') );

 ID  Name
 --  ----
 1    A
 2    B
 3    C

And the output or result-set of the data manipulation operation should be as shown bellow:

 ID  Name
 --  ----
 1    A
 1    A
 2    B
 2    B
 3    C
 3    C

Requirements

The output must be obtained complying with the following conditions:

No use of the UNION ALL operator in association with the employed SELECT statement(s)
No use of temporary table(s)
No use of an UPDATE operation to the existing table

Note: This scenario was brought up to me by an interviewer.


texts:
Running a phpkb CMS on a ubuntu lamp server.
mysql - 5.5.28-0ubuntu0.12.04.3-log

The tables in this database are not large in fact I have not found one over 10k rows.

Every five min I get a 'copy to temp table' query that hangs out on the server and locks tables. It brings the site to a stand still. Mysql takes 100% of one of the four cores on the server sometimes over I assume taking resources from the other cores. It still hangs.

I have made a ram disk as suggested in other threads and still no performance gains. I'm starting to think it's just going to come down to query optimization for the one that hangs. This seems to be the query that kills it from show full processlist; I also have logged slow queries to the mysql logs and this is the one that shows up over and over.

# Query_time: 152.569759  Lock_time: 0.000075 Rows_sent: 1181 Rows_examined: 383893921
use newkbdb;
SET timestamp=1461243155;

SELECT
  phpkb_articles.article_id AS article_id,
  article_title,
  article_content,
  article_status,
  article_date_time,
  SUM(phpkb_article_visits.article_hits) AS article_hits
FROM
  phpkb_articles
  LEFT OUTER JOIN phpkb_article_visits ON (phpkb_article_visits.article_id
  =phpkb_articles.article_id), phpkb_relations
WHERE
  article_show='yes'
  AND (phpkb_relations.article_id = phpkb_articles.article_id)
  AND article_status IN ('featured','approved')
  AND ( (article_expiry_date = '0000-00-00') OR (article_expiry_date <>
  '0000-00-00' AND article_expiry_date >= now()) )
  AND category_id=221 GROUP BY (phpkb_articles.article_id)
ORDER BY
  FIELD(article_status,'featured','approved'),
  article_title ASC;

Any suggestions or help appreciated I'm not a mysql dba this is just a unicorn site that some dept bought and now its become a huge dependency.

Thanks in advance.

UPDATE:
Create table statements:

CREATE TABLE `phpkb_articles` (
`article_id` int(11) NOT NULL AUTO_INCREMENT,
`author_id` int(11) NOT NULL,
`article_title` varchar(250) NOT NULL,
`article_content` longtext NOT NULL,
`article_date_time` datetime NOT NULL,
`article_status`enum('approved','disapproved','draft','featured','pending','pending-deleted','approved-deleted','featured-deleted','disapproved-deleted') NOT NULL DEFAULT 'pending',
`article_show` enum('yes','no') NOT NULL DEFAULT 'no',
`article_last_updation` datetime NOT NULL,
`article_keywords` varchar(250) NOT NULL,
`article_metadesc` varchar(250) NOT NULL,
`article_expiry_date` date NOT NULL,
`article_email_count` int(11) NOT NULL DEFAULT '0',
`article_print_count` int(11) NOT NULL DEFAULT '0',
`article_comments_allow` enum('yes','no') NOT NULL DEFAULT 'yes',
`article_ratings_allow` enum('yes','no') NOT NULL DEFAULT 'yes',
PRIMARY KEY (`article_id`),
FULLTEXT KEY `article_title` (`article_title`,`article_content`)
) ENGINE=MyISAM AUTO_INCREMENT=2872 DEFAULT CHARSET=utf8

 CREATE TABLE `phpkb_relations` (
 `relation_id` int(11) NOT NULL AUTO_INCREMENT,
 `article_id` int(11) NOT NULL,
 `category_id` int(11) NOT NULL,
 PRIMARY KEY (`relation_id`)
 ) ENGINE=MyISAM AUTO_INCREMENT=21960 DEFAULT CHARSET=latin1

CREATE TABLE `phpkb_article_visits` (
`visit_id` int(11) NOT NULL AUTO_INCREMENT,
`article_id` int(11) NOT NULL,
`article_hits` int(11) NOT NULL,
`visit_date` date NOT NULL,
PRIMARY KEY (`visit_id`)


texts: How to prevent PostgreSQL from rewriting OUTER JOIN queries?

texts:
My query is:

SELECT  Acol1, Acol2, Bcol1, Bcol2, Ccol1, Ccol2
FROM    tableA LEFT JOIN
            (tableB FULL JOIN tableC ON (Bcol1 = Ccol1))
            ON (Acol1 = Bcol1)

EXPLAIN ANALYZE gives me:

                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Hash Right Join  (cost=99.65..180.45 rows=1770 width=24) (actual time=0.043..0.103 rows=3 loops=1)
   Hash Cond: (tableb.bcol1 = tablea.acol1)
   ->  Hash Left Join  (cost=49.83..104.08 rows=1770 width=16) (actual time=0.011..0.062 rows=3 loops=1)
         Hash Cond: (tableb.bcol1 = tablec.ccol1)
         ->  Seq Scan on tableb  (cost=0.00..27.70 rows=1770 width=8) (actual time=0.001..0.002 rows=3 loops=1)
         ->  Hash  (cost=27.70..27.70 rows=1770 width=8) (actual time=0.004..0.004 rows=3 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               ->  Seq Scan on tablec  (cost=0.00..27.70 rows=1770 width=8) (actual time=0.001..0.002 rows=3 loops=1)
   ->  Hash  (cost=27.70..27.70 rows=1770 width=8) (actual time=0.014..0.014 rows=3 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 1kB
         ->  Seq Scan on tablea  (cost=0.00..27.70 rows=1770 width=8) (actual time=0.009..0.011 rows=3 loops=1)
 Total runtime: 0.151 ms

Postgres changed the full outerjoin between tableB and tableC to a right outerjoin, since later the left outerjoin with tableA will eliminate null values anyway. It is equivalent to the original query.

However, I am hacking Postgres to implement my join enumeration related algorithm and to do experiments. I do not want Postgres to change the full outerjoin to a left outerjoin. Is there a way to do so?


texts: How do you drop an unnamed check constraint in a PostgresQL table?

texts:
I created a PostgresQL table but I added an unnamed check constraint on one of the columns:

CREATE TABLE FOO
(
id serial primary key,
price_range smallint CHECK (price_range > 0),
url varchar(255)
);

Now I want to remove this constraint but I can't figure out how. The typical ALTER TABLE...DROP CONSTRAINT... needs a constraint_name but I don't have one.

I know there is an answer here but when I tried to determine the name of my check constraint by following the answer there:

SELECT *
FROM information_schema.constraint_table_usage
WHERE table_name = 'your_table'

All I got back was a single constraint whose constraint_name entry was foo pkey which refers to the primary key constraint and not my check on the price column. So that answer did not help me, unless I am missing something.

How can I drop this constraint without losing any data?

Thanks!


texts: log_reuse_wait_desc - AVAILABILITY_REPLICA Unable to truncate transaction log

texts:
I have just 1 secondary replica and just one database in my availability group. Both Primary and Secondary replicas are in synchronous-commit availability mode. I inserted 1000 records into the table and transaction was committed in both primary and secondary databases. Checkpoints were also issued. Now when I suspend the secondary database and try to take log backup of my primary database in order to truncate transaction log, it won't be truncated with the following log_reuse_wait_desc value: AVAILABILITY_REPLICA. Why isn't it truncated?
As far as I know as long as logs are inactive and VLF is filled up then transaction log can be truncated. Some of you can answer it in a way: SQL Server is waiting to send logs to the secondary Always On Availability Group replica, but my question is if 1000 records were already committed on both sides and checkpoints were issued, what logs are then waiting to be sent to the secondary replica?


texts: Is it possible to pick next row's cell value on same column if condition not matched?

texts:
I have a table flaggedcomment and a table price.

In flaggedcomment, there are two empty columns Price_DateTime and Price_Open which I want to copy from price, depending on the ON conditions. Query as below.

UPDATE flaggedcomment as t1
        LEFT JOIN price as t2
             ON t1.Ticker_Ticker_ID = t2.Ticker_Ticker_ID AND t1.Comment_DateTime = t2.Price_DateTime
SET t1.Price_DateTime = t2.Price_DateTime, t1.Price_Open = t2.Price_Open

This query will leave some cells with null value because there's no match between t1.Comment_DateTime and t2.Price_DateTime.

Is there a way to "pick" the next available Price_DateTime if there's no match found?

For example, if the value of t1.Comment_DateTime is 2014-09-29 20:44:00 but it's not exist in t2.Price_DateTime, can I pick the next row's Price_DateTime value, e.g. 2014-09-30 08:00:00?

I am new to deal with MySQL queries and I've tried my best in explaining what I want to do. If I didn't explain my question well, please let me know and I will try to explain it further.

Any help would be much appreciated. Thank you!


texts: In SQL Server and using only client side encryption requests, can I use a different certificate for each client?

texts:
Based upon the UI dialogue box in SQL Server Configuration Manager, if I right click 'Protocols for server_name', it appears that I can only select one certificate to use.

My experience so far tells me that this certificate option is not for server-side 'force encryption', the flag option one tab over. This is because server-side force encryption does not require that the client have a copy of the public key/ certificate. Any client that wishes to connect can connect provided that they are authorized to connect and a temporary certificate will be used for the encryption. It is only when a client makes the request that a connection be encrypted (client-side encryption) does it matter whether the client has a copy of the certificate selected in the SQL Server Configuration Manager.

The UI appears to limit the number of certificates to one but perhaps this is simply poor UI. Is it possible, say by powershell commands, to establish a pool of certificates, with different durations, by which various kinds of clients could connect?


texts: Oracle 11g: Part Copy of Production data to Development Environment

texts:
I have a Production database with the current size involving two principale schemas:

SQL> SELECT SUM(bytes)/1024/1024/1025 "Size in GB" FROM dba_segments where owner in ('SCHEMA1','SCHEMA2');

Size in GB
----------
201.927804

Out of these the mentioned tables and their indexes are taking up following space (175.326 GB):

SQL> SELECT SUM(bytes)/1024/1024/1024 "Size in GB" FROM dba_segments where owner IN ('SCHEMA1','SCHEMA2')
  2  and segment_name in  ('TAB1','TAB2','TAB3','TAB4','TAB5','TAB6','TAB7','TAB8','TAB9','TAB10','TAB11','TAB12');

Size in GB
----------
72.0917968

SQL> SELECT SUM(bytes)/1024/1024/1024 "Size in GB" FROM dba_segments where owner IN ('SCHEMA1','SCHEMA2')
  2  and segment_name in  (
  3  select index_name
  4  from dba_indexes
  5  where owner IN ('SCHEMA1','SCHEMA2')
  6  and table_name in ('TAB1','TAB2','TAB3','TAB4','TAB5','TAB6','TAB7','TAB8','TAB9','TAB10','TAB11','TAB12'));

Size in GB
----------
103.234375

SQL> select (72.0917968+103.234375) from dual;

(72.0917968+103.234375)
-----------------------
            175.3261718

Thus these 12 tables along with the indexes takes about 88% of the total space in the 2 schemas.

All 12 tables mentioned are subpartitioned based on a partition and subpartition key.

We have 7 development databases which needs to be refreshed on demand with the production database.

Currently, the refresh is done using RMAN, but going forward as the production database size increases we are anticipating space problems on development databases.

The Solution which I tried:

The 12 tables are partitioned in such a way that the data in one partition is functionally independent of other partition.

I created new tablespaces and moved partitions to these new tablespaces based on their business functionality.
Thus a single table had some partitions on one tablespace and rest of the partitions on the other tablespace.

The idea was to transport the necessary tablespces (and not the entire database) across production to development database.

Later on my DBA told me that for a tablespace to be transportable across databases they should be "self-contained".

And one of the violations for self contained tablespace according to oracle documentation is:

"A partitioned table is partially contained in the set of tablespaces.
The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you want to transport a subset of a partition table, you must exchange the partitions into tables."

Thus my design failed.

Kindly note that the 12 big tables which I mentioned above contains data which is actually not needed for my unit testing on development database.
Is it possible to perform RMAN copy without including the data from these 12 tables?

Or could you think of any other solution so that I can do "part" refresh of production database to developement.

Let me know if you need any more information?

Database: Oracle 11g - 11.2.0.2.0

Thanks a lot in advance!!


texts:
SQL Server Maintenance Plan Help

I'm using SQL Server Maintenance and found a way to selectively turn on and off various features of the plan via it's associated job shown in Figure A. This can be handy for me because I can create multiple versions of the job and selectively turn parts of the plan on or off as needed--without going into the GUI interface for the maintenance plans.

However, if I should open the associated plan via the maintenance plan GUI and save it--even if absolutely no changes were made--it deletes all of my customization in the Set values tab of the job--as shown in Figure B. I ran a trace, shown in Listing C, the shows my job being replaced with vanilla values.

Question:

Does anyone know how to stop this annoying behavior that erases the values in the Set values tab? (Or alternately to add my values to the plan GUI so they get added to the SQL job when I hit save).

p.s. SQL Maintenance is definitely not my favorite. I'm a T-SQL guy at heart but the company I'm currently at wants to use SQL Maintenance Plans.

Figure A: Maintenance Plan Job with customized values

Figure B: Maintenance Plan Job values erased by resaving the maintenance plan in the GUI

I ran  a trace and found that the GUI was recreating a vanilla version of the job after I resaved it--deleting my original values:

Listing C: Job values get replaced/erased

EXEC msdb.dbo.sp_add_jobstep @job_id=N'a76facdb-d100-46a0-9b7a-4f0fab85178c', @step_name=N'Subplan_1',
        @step_id=1, @subsystem=N'SSIS',
        @command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\Nightly Maintenance" /set "\Package\Subplan_1.Disable;false"'


texts: Netezza LIKE operator not finding subsequent backslashes

texts:
Netezza is able to find records when using the LIKE operator when the first character contains a backslash character.  But when subsequent characters contain a backslash character, it cannot.  How can I correct this?

Result set has records:

SELECT *
FROM TABLENAME
WHERE PATH LIKE '\some_path%'

Result set has 0 records:

SELECT *
FROM TABLENAME
WHERE PATH LIKE '\some_path\%'


texts:
How to setup mysql on tmpfs ?

I am reading a tutorial in french, where the author do it this way :

1.) Create a tmpfs directory :

mkdir /var/tmpfs
mount -t tmpfs -o size=1G tmpfs /var/tmpfs
chown -R mysql:mysql /var/tmpfs/mysql

2.) Add it to fstab :

tmpfs  /var/tmpfs  tmpfs  nodev,nosuid,noexec,noatime,size=1G  0 0

3.) Copy MySQL to tmpfs (is ramfs here is correct?)

service mysql stop
cp -Rfv /var/lib/mysql /var/ramfs/
chown -R mysql:mysql /var/tmpfs/mysql

4.) Edit /etc/my.cnf

datadir /var/tmpfs/mysql

Then restart mysqld and you can optionally add a startup/stop script to backup the ram data to disk...

But I am not sure the above is right : did the writer of this tutorial mistake ramfs for tmpfs here ? He then goes on to use ramfs in the start/stop script, but what ramfs has to do there, isn't it really tmpfs only ?


texts: MySQL replication slave using percona and docker

texts:
I am trying to run a MySQL replication slave in a docker container. We are running MySQL 5.7.24-27-log in production and it's from the percona repository (Ubuntu 18.04).

I have used xtrabackup to backup, prepare and ship a starting data set for replication, then I started the percona docker image (docker pull percona) like so:

$ docker run --name mysql-replication -v /replication/data:/var/lib/mysql -v /replication/docker.cnf:/etc/mysql/docker.cnf:ro -e MYSQL_ROOT_PASSWORD=xxxx -P -d percona

My docker.cnf simply notes the server-id (I copied it from the percona image).

[mysqld]
skip-host-cache
skip-name-resolve
bind-address    = 0.0.0.0
server-id       = 4

After then using CHANGE MASTER etc. I have the replication running just fine.

My intention (as per the volume mount -v /replication/data:/var/lib/mysql) is to keep all of the MySQL data on the host machine, and treat the replication docker container as ephemeral, i.e. no state held in the container. It should also be easy to start up another replication container should I need one by stopping the existing container, copying the data elsewhere, changing the server-id and running a new container.

To test this, after it was set up and running properly (I watched Seconds_Behind_Master drop down to 0), I figured I should be able to delete the container and recreate it, and the replication would still work fine. I therefore tried this:

$ docker stop mysql-replication
$ docker rm mysql-replication
$ docker run ... // same command as before

When I do this and connect to MySQL running in the container I find that Slave_IO_Running is No, and after starting it (START SLAVE;) I get the following (as seen in SHOW SLAVE STATUS;):

Last_Error: Could not execute Update_rows event on table databasename.tablename; Can't find record in 'tablename', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000681, end_log_pos 9952

(databasename and tablename are real database and table names)

At first I thought that I had probably mucked something up but I have tried this a number of times now to try and solve the problem. Using docker diff mysql-replication shows no changes to the running container that seem to be of significance:

$ docker diff mysql-replication
C /run
C /run/mysqld
A /run/mysqld/mysqld.pid
C /var
C /var/log
A /var/log/mysql

Googling has suggested that I need to use RESET SLAVE; and START SLAVE; but this doesn't seem to resolve it - it's like the data (outside the container) is no longer in sync with the master and replication therefore cannot continue.

Can anyone pick holes in what I'm doing please?

Thanks so much.


texts:
I am interested in producing the following view where different delivery dates are numbered.

SalesID    Delivery Date DateNo
SO00000014 2014-02-21    1
SO00000015 2014-02-13    1
SO00000016 2014-01-28    1
SO00000017 2014-03-17    1
SO00000018 2014-02-13    1
SO00000020 2014-03-19    1
SO00000020 2014-03-20    2
SO00000020 2014-03-21    3

My question is how to number different dates associated to a single SalesId like in the
 SO00000020 above. I am using SQL Server 2008.


texts: When does the quarantinethreshold counter reset in WSFC cluster?

texts:
An outage that occurred on our SQL availability group this morning caused us to look into the quarantine settings on WSFC - something we never needed to configure.

Now there seems to only be two settings:

QuarantineDuration: 7200 - the number of seconds a node is quarantined for when it's quarantined
QuarantineThreshold: 3 - the number of times a node's taken out of the cluster (or flaps) before a quarantine is applied on it.

Now that's all well and good, but let's say the flapping stops at 2 times. How long before the Threshold counter is reset to 0? Surely it gets reset at some point. If not, then the next time this happens, let's say in a month's time, then it'd get immediately quarantined the first time, which doesn't really make any sense.

I can't seem to find anything in the WSFC documentation. Does anyone know?


texts: Will index on joined table be used here?

texts:
select * from employee, department where employee.id = department.id

department.id is primary key on department

My question is will index created on  department.id(internally created index on primary key by oracle) be used while employee.id = department.id i.e. while finding corresponding row_id in department table


texts: How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)

texts:
I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or recursive SQL statements.

As far as I can tell Snowflake does not support these operations. Are there any other queries that can be made to mimic this or work around this?

"Currently, Snowflake does not fully support common table expressions (CTE) in DDL operations."

Snowflake documentation: https://docs.snowflake.net/manuals/sql-reference/constructs/with.html

see: https://www.postgresql.org/docs/current/static/queries-with.html


texts:
Are there any particular precautions I should take when downgrading PG 10 to 9.4?

I have a database on Heroku (PG 9.4) which I'm going to upgrade to PG 10.6. In case something goes wrong, I want to be able to go back to 9.4.

Things I already tested:

create a backup of the already upgraded PG 10.6 DB (Heroku creates binary compressed dumps)
create a new PG 9.4 DB
upload the backup from step 1. to DB from step 2

The backup seems to be restored properly. What should focus on in particular to be sure this procedure is all right? Are there any other ways to downgrad PostgreSQL?


texts: MEMORYCLERK_SQLCONNECTIONPOOL consuming over 70% of memory

texts:
SQL Server 2014 SP2 Enterprise Core bases License (64 bit)
CPU (logical) = 32
Memory 128GB
SQL Server Max Memory : 100GB

We see that more than 70% is taken by MEMORYCLERK_SQLCONNECTIONPOOL. I see only 300 sessions (50 active). We are having memory contention and we see that plan cache is floored and Page Life Expectancy also going down.

           type                            Page Allocator (MB)
MEMORYCLERK_SQLCONNECTIONPOOL                     71194
MEMORYCLERK_SQLBUFFERPOOL                         26459

DBCC MemoryStatus output:

Memory Manager                           KB
Emergency Memory                         1024
Emergency Memory In Use                  16
Target Committed                         104857608
Current Committed                        104626984

MEMORYCLERK_SQLCONNECTIONPOOL (node 0)   KB
Pages Allocated                          34,733,992
MEMORYCLERK_SQLCONNECTIONPOOL (node 1)   KB
Pages Allocated                          37,559,224
MEMORYCLERK_SQLCONNECTIONPOOL (Total)    KB
Pages Allocated                          72,293,216

Please help me with next steps to troubleshoot this further.


texts: v$ tables not recognized in a sql statement of a shell script

texts:
I have .sh script with this block:

sqlplus -S / as sysdba <<EOFILE

set feedback off
set lines 2000
set pages 20000
col db_size for a15
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

select * from ( select "START_TIME", "END_TIME", INPUT_BYTES_DISPLAY db_size from v$RMAN_BACKUP_JOB_DETAILS order by "END_TIME" DESC) where ROWNUM <= 5;

EOFILE

EOF

But output is this error:

  select * from ( select "START_TIME", "END_TIME", INPUT_BYTES_DISPLAY
  db_size from v order by "END_TIME" DESC) where ROWNUM <= 5
                                                                                * ERROR at line 1: ORA-04044: procedure, function, package, or type is not allowed here

I know there is problem with accessing to v$RMAN_BACKUP_JOB_DETAILS but how should i solve this?

I tried:

v\$RMAN_BACKUP_JOB_DETAILS
'v$RMAN_BACKUP_JOB_DETAILS'
"v$RMAN_BACKUP_JOB_DETAILS"

Can someone help me? Thanks


texts: Dump the buffer pool to disk so it can be loaded after a restart

texts: Is there a way to preserve the buffer pool cache so that is can be loaded in after a restart? My research suggests that other DB servers can do this but SQL Server is not one of them. I'm looking for ways to maximize server performance after a maintenance restart.


texts: Create log file with SQL statements for only one database

texts: I have two Postgres databases. Can I write in a log file SQL statements only for one of them?


texts: trying to connect to MSSQL server using named pipe in HeidiSQL

texts:
I'm trying to connect to a MSSQL server in our office from my local desktop using HeidiSQL. I am able to connect to this server with both SQL Server Management Studio and ODBC with no problems. The connection uses integrated Windows authentication. I installed the MS OLE DB SQL 18.2.2.0 x64 driver. My settings in HeidiSQL are:

network type: Microsoft SQL Server (named pipe)

library: MSOLEDBSQL

hostname: <server>\<instance>

use windows authentication is checked

every time I try to connect I get:

  SQL Error (0): SQL Server Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF] Login timeout expired

It doesn't tell me what protocol is being used, and I don't see any options to change it. Does "protocol" in this case refer to the library?

Is there some way to change the protocol in Heidi?


texts: Index method for very few updates and many inserts

texts:
I am using Postgresql 9.1 with *pg_trgm* extension. I need to create an index on a text-based field. I do not need full-text searches, I use ILIKE queries to make my searches.

I will use pg_trgm but do not have much experience with gin and gist indexes. I will have many INSERT statements (~15000 daily) and very few UPDATE statements (may be 1 or 2 in a week).

What will be the index update overhead for a gin index on a such table? Or is gist more suitable?


texts: Why my code only return 1 row? Leetcode#626

texts:
I have used MySQL. My code is
SELECT
(CASE WHEN mod(s.id,2)=0 THEN s.id-1
WHEN mod(s.id,2)=1 AND s.id != MAX(s.id) THEN s.id+1
END) AS id,
s.student
FROM seat s
ORDER BY id

It only returns one row
{"headers": ["id", "student"], "values": [[2, "Abbot"]]}
But if I delete AND s.id != MAX(s.id)
then I will get all rows.
I don't understand it. Is my understanding of MAX function wrong?


texts: Database design - load multiple excels daily

texts:
My first post ever :) Apologies if I am not so clear with my question - here I go...

I have a design problem from database schema perspective.

My use case is following:

On a daily basis, system will receive around 30 Load files on a daily basis. These files will be basically excel files - with rows and columns. Each file will at most be related to one other file in terms of data contained. For other files, they might at all not be dependant on other file data. We're thinking of using SQL Server to contain this data. This will be loaded into database using a programming language.

A plain relational Database approach would be:

  Create around 20 Tables (As some files are related to other file(s)).
  Every table will have all the fields corresponding to the file(s) it has been created for (fields in the excel file)
  On a daily basis, load the data of these files into the corresponding tables.

Each file will load 10 KB to 15 MB of data per day. Also, there can be multiple iterations of loading data from these files in a day (Max 24 - hourly once). I am dreading pushing so much data into database with above proposed structure, as it will become too loaded in a few days.

If I think of non-relational, I need to also process/clean/filter this data once it is loaded (I might want to create a stored procedure) - so that I can render useful data on UI - I think it will be more complicated on no-sqls due to non-availability of joins.

Moreover, my files are fixed and I have a structure. To my understanding - NoSqls are more apt when your data doesn't have any fixed format.

Can somebody please suggest what kind of database & the design would be good for this use case? I don't want to create 20 tables for 20 files :(


texts: Does index occupy disk memory for rows for which index expression is NULL?

texts:
Let's assume that I have following pg_trgm index on mydata JSONB column:

CREATE TABLE mytable (mydata JSONB);
CREATE INDEX trgm_index_1 ON mytable
USING gin ((mydata#>>'{myfield}') gin_trgm_ops);

And let's assume that I will insert following data into mytable:

500k rows with mydata = { "myfield": "some_text" }
500k rows with mydata = { "another_field": "some_text" }

So 1M rows in total, but half of them contains value that is used in index expression (mydata#>>'{myfield}'). Does it mean that index will use ~50% less memory on disk than it would use if all of rows contained myfield json field?


texts:
I find it frustrating that the same queries don't work in different DBMS.

Example, some DBMS (such as Microsoft SQL Server and MySQL) seems to support the information_schema table, while others (such as SQLite) don't.

Some databases such as MySQL allows you to do SHOW TABLES, etc.

Some limit the results using SELECT TOP 10 ... while others with ... LIMIT 10.

Why are different DBMS so incompatible and deviant?

Why don't they follow the SQL standard?

Which are good at adhering to the SQL standard?

Which are notoriously bad at adhering to the SQL standard?


texts: Improving performance of insert..on duplicate key update

texts:
You know the feeling when you think you've had the best idea but then realise you might not know what you're talking about? This is one of those moments, and I'm hoping my idea can be confirmed or refuted.

I have a database table that saves activity in the form of "date, sender, recipient, action count", that counts how many interactions one player of the game (which you just lost :p) gives to another on a given day.

Right now, it uses insert..on duplicate key update (with the unique key being (date, sender, recipient) and while it works I have noticed that it causes deadlock errors during periods of intense activity.

I have fixed deadlock errors in other parts of the game by avoiding "insert..select from", "insert ignore" and "insert..on duplicate key update" and this appears to be one of the few, if not the only one left.

My new idea is as follows:

Attempt update
If the number of affected rows is zero...

Perform insert

Essentially it's become "update..on not found insert", and done manually to try and avoid locking rows.

Caveats I've thought of:

Race conditions are not an issue because the client-side code already includes batching operations, and a given user should therefore only be sending one request of this type at a time. If the client bypasses this code (never trust the client!) then the worst case is their displayed numbers will be lower than expected.
This may execute two queries instead of one, but I believe an update matching zero rows is basically a no-op.

That's about it really. Am I missing anything here or should this solve my issues?


texts: What does this margin symbol in the SSMS text editor mean?

texts:
While editing text in SSMS I mistakenly pressed a random keyboard combination, which I think began with Ctrl-K, and found this diagonal arrow symbol appeared in the text margin where the breakpoints normally go:

Can anyone tell me what it means?

SQL Server 2016 13.0.15900


texts: SQL Failover and Failback Script for SRM Recovery Plan(preferably power shell or CMD or Batch script)

texts:
OS: Windows Server 2016 Standard
SQL: SQL Server 2016 Enterprise
SQL Setup: Two Nodes(Primary and Secondary Node) in PDC(Primary Data Center) having Always On Availability Group with Synchronous Mode and Automatic Fail-over.
3rd Node is in DRC(Disaster Recovery Center) with Assynchronous Mode and Manual Fail-over.
Quorom setup:  Node and File Share Majority
For Failover, when PDC is down.
Need to force WSFC without the quorum.
Link: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/force-a-wsfc-cluster-to-start-without-a-quorum?view=sql-server-ver15
I know there is a SQL wizard on doing the failover and failback but is there any powershell or batch script for doing it?
The reason for the scripts is to place it in SRM Recovery plan.


texts: Which datatype to store XML data in: VARCHAR(MAX) or XML

texts:
I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time-to-time; although not frequently; I'll need to query the XML to find element and attribute values. If left to my own devises, I would tend to use the xml data type although I’ve been led to believe this is wrought with issues. So that leads me to my questions.

Given this scenario, what factors should I be considering when trying to decide between storing XML in an xml column vs. a varchar(MAX) column

If it helps… here are some additional details:

No decision has been made regarding the use of schema’s for these fragments (e.g. XSD’s)
Sizes of the fragments will range from small to very large
All XML will be well-formed
Over the course of a day, there will be up to ~10,000 fragments collected with online query support needed for ~3 months
Queries against the XML will happen throughout the day but should remain light with few concurrent queries of this type


texts: Filter rows, but only select most recent row for each Id

texts:
I have some code that is processing a lot of tasks. These tasks are either connected to a user or an account. Each Task go through many statuses -> Queued, Started, Error, Postponed, Queued, Started, Finished ...

Since there will be processed a lot of these Tasks very fast, the goal was to only do writes. As you can see from the comments, the other goal of saving these statuses is to be able to show the Task status in a user interface.

This was solved like so:

-- Used to store processing data for a Task
drop table if exists task;
create table task (
    id uuid,
    data text,
    type text,
    primary key (id)
);

-- Used to find all statuses for a specific task
-- Use case: View one Task i Admin interface
drop table if exists task_status;
create table task_status (
    task_id uuid,
    time_id timeuuid,
    status_enum text,
    status_text text,
    primary key ((task_id), time_id)
) with clustering order by (time_id desc);

-- Used to find Tasks for an Account
-- Use case: Show Account Details > Tasks
drop table if exists account_tasks;
create table account_tasks (
    account_id bigint,
    task_id uuid,
    time_id timeuuid,
    primary key ((account_id), time_id)
) with clustering order by (time_id desc);

-- Used to find Tasks for a User
-- Use case: Show User Details > Tasks
drop table if exists user_tasks;
create table user_tasks (
    user_id bigint,
    task_id uuid,
    time_id timeuuid,
    primary key ((user_id), time_id)
) with clustering order by (time_id desc);

When a Task is created, a row will be written to "task" and depending on which task is it, also to "user_tasks" or "account_tasks".
During processing of the Task multiple rows will be written to "task_status" with different statuses and a user friendly text describing what happened in the step.

I also need to be able to find all the Tasks that currently have a specific status, but also using a filter on type.

I have tried something like this:

-- Used to find tasks with a specific status and type
-- Use case: Admin Task overview showing all tasks
-- select * from task_overview where status_enum = 'Failed' and type = 'ActivateAccount'
drop table if exists task_overview;
create table task_overview (
    status_enum text,
    type text,
    task_id uuid,
    time_id timeuuid,
    primary key ((status_enum, type), time_id)
) with clustering order by (time_id desc);

I would write to this at the same time as "task_status", however when filtering on this table, I am not sure that was actually the last status the Task had. It just shows that the task had this status at some point. I only want the row returned, if for all rows with a specific task_id this is the one with the highest time_id.

I have spend a lot of time thinking of other data structures that could support this only doing writes, but I have not been successful :-(

Said in other words, I need to be able to do a query where I filter on status_enum and type, that only returns the Tasks that currently have that status_enum.


texts: PostgreSQL: After restoring data, unable to use Views (permission denied)

texts:
I'm attempting to get a PostgreSQL environment running locally.  I'm using 9.2.8, and have restored data from a backup made by the DBA of the production environment.

The restoration worked fine with 0 errors; however, in a couple of the Views there is a missing field that is in the production schema from which we restored our data.

As well, when attempting to do a simple select from the Views mentioned using the postgres role, I always get the following error:

ERROR:  permission denied for relation Map
********** Error **********
ERROR: permission denied for relation Map
SQL state: 42501

After doing some research (read: Googling), I tried GRANTing priveleges on the Schema, Views, and associated tables (in this case, the table 'Map') to avail.

Obviously, I'm a neophyte PostgreSQL user.  I'm sure there's something simple I'm missing.  But what could it be?


texts: Can't create table in database with db_owner role (SQL Server 2012)

texts:
I have database in SQL Server 2012 SP2 Enterprise. I created new SQL Login and mapped to X database with db_owner role. Now I can't create table on this database. When I run simple create table script I got below error.

  Msg 262, Level 14, State 1, Line 1
  CREATE TABLE permission denied in database 'INVENTORY_TEST'.

There is no database or server trigger. I did not find any SQL Server bug related to this problem. Any idea?


texts: Can Entity Framework access In Memory OLTP Tables?

texts: Are there any restrictions with having Entity Framework access In Memory OLTP Tables? Or is this not possible?


texts: What is Heap Fragmentation sans Indexes?

texts:
When you talk about fragmentation in a heap that has no indexes, there can't be logical order fragmentation, so does fragmentation in this instance only refer to non-contiguous pages? Edit: no.

Is the same thing happening in a case where a heap has an index that has no fragmentation but the heap itself does have fragmentation?

Why does SQL Server create forwarded records at all, instead of just writing to a new page, and backfilling when it makes sense?


texts: Is there any way easy to use eg. 50 or more values in queries , since manually putting values in query is time consuming

texts: Is there any way easy to use multiple data (eg. 50 values) in queries import from some excel , since manually putting values in query is very tedious and time consuming.


texts: Does OLA Scripts support striping for backup to URL?

texts:
I am testing backups to blob using OLA scripts. However, I am unable to stripe the backups into multiple files. I tried using 'Numberoffiles' parameter.
Please advise.

I am using a shared access signature to backup to URL and below is the command:
EXECUTE dbo.DatabaseBackup
@Databases = 'DBA', @URL = https://xxx.blob.core.windows.net/sqldbabackups',
@BackupType = 'FULL',
@Compress = 'Y',@Numberoffiles=10

Best Regards,
Sharath


texts: What happens when the buffer pool size is insufficient in mysql

texts:
Let us assume I run a query for a table whose size is very huge. My buffer pool size is as low as possible. What happens now? whether I will get an error regarding the insufficient space in buffer pool or will mysql fetch the data from the disk ?
Just curious to know the behaviour of mysql in this scenario.


texts: Identifying Execution Plans with Probe Residual

texts:
I am trying to find out Execution Plans that has Probe Residual.

Need to know the following

Which physical and logical operator has this Probe Residual
What is the Cost% of that operator in the query
Associated Execution plan
Query Text

Following is a try that I gave – but I am stuck for getting other details. How to get these details?

Note: I am using SQL Server 2012

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DEQP.objectid,
    DEQP.query_plan,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE
    1 = DEQP.query_plan.exist(
        '//RelOp[
            @PhysicalOp = "Hash Match"
            ]')

A Probe Residual Example

References from blogs/articles of Grant Fritchey and Rob Farley below

Probe Residual on Hash Match - Home Of The Scary DBA
Probe Residual when you have a Hash Match – a hidden cost in execution plans


texts: Can SQL Server be configured to backup to a network share through a separate NIC?

texts:
We're in the process having our SQL Server instances backup to a network share, but our network admins want to try and divert the bandwidth from the backups through a different network segment. I was curious if it were possible to configure SQL Server to run all backups through a secondary NIC?

Most of our servers are virtualized (so presenting the NIC isn't a problem) and we're running on SQL Server 2008 R2, 2012, & 2014 throughout our infrastructure.


texts: Find out number of active locks on a table

texts: I was wondering if there is a way to find out the number of active locks held on a table in SQL Server?


texts: Very slow update with subquery (different tables)

texts:
I run this query to update "domains" table based on "log" table:

UPDATE domains
SET domains.mx_collected = 0
WHERE domains.domain IN
(SELECT log.mail as domain_name FROM log WHERE
`time` >= DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) AND
`time` <= DATE_SUB( CURDATE( ) , INTERVAL 15 DAY )
group BY domain_name
HAVING count(*) > 200)

While the subquery itself runs very fast (0.6 sec) with a result set of only 6 items the update never ends (not after hours). But if I run the update-query with a item list instead of a subquery it successfully ends after 0.02 seconds:

UPDATE domains
SET domains.mx_collected = 0
WHERE domains.domain IN
("0815.ru","123.com","123mail.org","126.com","139.com","13hotmail.com")

Question is: why?


texts: Is it better to create another table to possible null values in relational databases?

texts:
I'm designing a relational database to use in MySQL. I have the next situation: In one table there are fields that in the most of situations are NULL. The fields would be like:

Table name: tabla

Fields:
  idtabla not null,
  text (varchar(n)) not null,
  image (mediumblob) {this can be null}

The doubt: Is better to create another table and when be necessary to use the image, query the new table?
Why is your solution, better for design? MySQL's time response, ease to make queries, etc?


texts:
I do not really know if this is possible to do this but I will expose my problem.

I have two tables cases and progress

cases

+----------+--------------+---------------------+---------+------+
| id_cases |     name     |    date_surgery     | archive | done |
+----------+--------------+---------------------+---------+------+
|        1 | Cranioplasty | 2016-02-01 00:00:00 |       1 |    0 |
|        2 | Cranioplasty | 2016-02-02 00:00:00 |       0 |    0 |
|        3 | Other        | 2016-02-03 00:00:00 |       0 |    0 |
|        4 | Osteotomy    | 2016-02-04 00:00:00 |       0 |    0 |
|        5 | Bone Tumor   | 2016-02-05 00:00:00 |       1 |    1 |
+----------+--------------+---------------------+---------+------+

progress (which contains thousands of records)

+-------------+---------+---------+---------+
| id_progress | task_id | case_id | current |
+-------------+---------+---------+---------+
|           1 |  103006 |       1 |       0 |
|           2 |  103002 |       1 |       1 |
|           3 |  103003 |       1 |       1 |
|           4 |  201006 |       5 |       0 |
|           5 |  201007 |       5 |       1 |
|        .... |     ... |     ... |      ...|
+-------------+---------+---------+---------+

The link between the tables is cases.id_cases = progress.case_id

I want to select all cases with archive and done = 0. I also want to get some progress that are linked to this case

I tought about a condition to select a specific range of task_id related to the result of cases.name.

So basically I want this

SELECT id_cases, name, date_surgery, task_id, current
FROM cases
LEFT JOIN progress on progress.case_id = cases.id_cases
WHERE archive = 0 AND done = 0

But if name is Cranioplasty I just want progress.task_id that are equal to 103006, 103002. For Bone Tumor I want 201006 and 205003. And this for all the different name.

There is no logic between the id_cases and the task_id. I must hardcode this.

I tried differents things but none suceeded

SELECT id_Cases, name, date_surgery, task_id, current
from cases
left join progress on progress.case_id = cases.id_cases
where archive = 0 and done = 0
and case when name='Cranioplasty' then task_id=103006 and task_id=103002 else
case when name='Bone Tumor' then task_id=201006 else
case when name='Osteotomy' then task_id=301002 else
case when name='MBIO' then task_id=401006 end end end end
order by name, date_surgery

In fine I try to get this result (task_id is not important, I just want to result of current)

+------+--------------+-----------+--------+-------+--------+-------+--------+-------+
| case |     name     | date_surg | task_1 | 1_res | task_2 | 2_res | task_3 | 3_res |
+------+--------------+-----------+--------+-------+--------+-------+--------+-------+
| 1    | Cranioplasty | date      | 103006 |     0 | 103002 |     0 | 105002 |     1 |
| 1    | Cranioplasty | date      | 103006 |     1 | 103002 |     1 | 105002 |     0 |
| 1    | Cranioplasty | date      | 103006 |     1 | 103002 |     0 | 105002 |     1 |
| 2    | Cranioplasty | date      | 103006 |     0 | 103002 |     1 | 105002 |     0 |
| 2    | Cranioplasty | date      | 103006 |     1 | 103002 |     0 | 105002 |     1 |
| 2    | Cranioplasty | date      | 103006 |     0 | 103002 |     1 | 105002 |     1 |
| 3    | Bone Tumor   | date      | 201006 |     1 | 205003 |     0 | 205005 |     0 |
| 3    | Bone Tumor   | date      | 201006 |     0 | 205003 |     1 | 205005 |     1 |
| ...  |              |           |        |       |        |       |        |       |
+------+--------------+-----------+--------+-------+--------+-------+--------+-------+


texts: PostgreSql - Join two tables to extract a combination of three columns

texts:
I have a problem where I need to join two identical tables to extract a column called status.
Table 1
id     | status
----------------------------------------
1      | APPROVED
2      | WAITING
3      | FUTURE_CONSIDERATION

Table 2
id     | corresponding_id (primary key of table 1)  | status
----------------------------------------
4      | 1                                          |REJECTED
5      | 2                                          |IN_REVIEW

I'd like to get the results as follows (order doesn't matter)
id1    | id2     |status
----------------------------------------
1      |         | APPROVED
2      |         | WAITING
3      |         | FUTURE_CONSIDERATION
1      | 4       | REJECTED
2      | 5       | IN_REVIEW

So essentially all rows from table1 (with id2 being empty) and table2 (with id2).
I basically have this for now:
select status, id as id1, null as id2 from table1 UNION ALL select status, corresponding_id as id1, id as id2 from table2;

Is this correct or is there a better way of extracting the results?


texts: Is there a way to find out what queries are run the most against a particular table?

texts: We have been experiencing issues with slowness regarding our Master Data Services application. So we have looked to see which tables have the highest value of range scans, singleton lookups, page lock waits, etc. Can you tell me if there is a way to see the most queries run against a particular table in our MDS database.


texts: Oracle return the first row, not returning the first row

texts:
I have this query :

select (quarter_1 + quarter_2 + quarter_3 + quarter_4) as total
from budget where as_of_dt < to_date('2016-06-28', 'yyyy-MM-dd')
order by as_of_dt desc;

Which returns bunch of numbers such as :

My requirement is to always capture the first number, so when I try to do that :

select * from (
  select (quarter_1 + quarter_2 + quarter_3 + quarter_4) as total
  from budget where as_of_dt < to_date('2016-05-16', 'yyyy-MM-dd')
  order by as_of_dt desc
) WHERE ROWNUM = 1

I get this result :

Which is why I m puzzled about this, why is that I m getting the second row instead of the first one? What am I doing wrong here?

Update including as_of_dt in result for first query:


texts: storing arrays or images in a database table

texts:
I'm a beginner with both database design and use of forums such as this one so forgive me if I forget to provide some relevant information below.

Basically I'm trying to store a set of arrays, in this case raster images, of 2500*2500 in a database in order to extract timeseries for individual pixels. I currently have roughly 1500 images and every day an image is added.

The original raw files of bytetype are only small, but my database table grows very quickly in the following form:

     pixelnr, date, rastervalue
     PK clustered (pixelnr,date)

I understand that this is because I need a pixelnr and date for every pixelvalue. Unfortunately I don't know how else to store the data in a database.

I've tried to optimize the table for select statements of timeseries per pixel with a clustered PK on pixelnr,date. The drawback is that inserts of new images take an increasingly long time.

There are only two things I do with this table:
- bulk insert from a csv file. I convert a new image to a CSV file with the same form as the table. It will have all pixels, but only for a single date.

run simple select queries to derive time series for a number of pixels, usually up to 25000 pixels:

SELECT Pix,Date,Val
FROM FullTable INNER JOIN PixelSubset ....

I now have two questions:

1) Is there a more efficient way to store this kind of data? I've not found much useful on storing arrays in an RDBMS.

2) How to improve the performance of bulk inserts (all pixels for a single date) into the the table while keeping the clustered index optimised as described above.

I use SQL server 2012 developer edition on a PC with 8GB RAM and a RAID system. There's not much room for hardware changes....

Any pointers particularly to more efficient storage of such data in a RMDBS would be greatly appreciated!


texts:
when I do

select *
from all_synonyms
where synonym_name='SYNAAA'

I can see synonym SYNAAA is public, and table behind it is TABAAA in schema A.

Given SYNAA is public, I should be able to query it directly by just select * from SYNAA right? but I got "table or view does not exist" error, I tried to refer it by schemaname.SYNAAA or schemaname.TABAAA, or grant but all failed.

The thing is when I check schema A's table list, there is no table TABAAA under it. Is this the reason I got "table or view does not exist" error? table behind the synonym SYNAA does not exist?

Thank you very much!


texts:
Hello I am trying to run a website sent to me but after doing so this error appeared

connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "4X.XXX.XX.XXX", user "userXXX", database "dbXXX", SSL off in C:\xampp\htdocs\xmastool\index.php on line 37

after Googling it it says that i just need to add an entry in the pg_hba.conf file for that particular user.
this is my pg_hba.conf file.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
local dbXXX userXXX md5
host    dbXXX  userXXX  XX.XXX.XXX.XXX           md5
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

but after doing so, the error still persist. I restarted my XAMPP server several times but no changes appears.
Thanks in advance


texts: Did I Properly Diagnose That It's Not A SQL Server Issue?

texts:
We have a job that populates about 450,000 rows nightly in an update/insert job via a stored proc nightly.  The .Net app pulls in data from a noSQL DB via an API call then pushes it out via a stored proc into SQL Server.  Last night, it took 10x as long as normal.

I was trying to diagnose if it's a SQL Server issue or something in between.  Here is what I checked:

-Plan Cache did the plan change from last night?  There was just 1 in the cache and the cache was not under pressure so it's highly unlikely that it threw away the old plan. I have older plans sitting around but I did not explicitly check the create date on the plan.

-PerfMon metrics were all normal.  CPU was about 20%, lots of free memory, no real diskIO issues (nothing above 50ms)

-started a extended events session on the spid several times for 2 minute internvals.  The results consistently came back with about 5-6 seconds of total wait stats for SQL Server for a 120 second interval.

The figures were:

Pagelatch_EX: 2800ms

SOS_SCHEDULE_YIELD: 1499ms

writelog: 182ms

Network_IO: 2ms

-I saw 0 locking or blocking anywhere on the spid and saw nothing in wait stats showing any real signs of locking outside of the pagelatch for 2.8 seconds.

-Sometimes we have network issues sending the results to the web server but we have had almost no wait stats on network on these queries.

-SOS_SCHEDULER_YIELD was the top wait stat, but CPU usage and thread count was low.  There was no thread contention.  These stored procedures just conduct update/inserts without major complexities and the code hasn't changed lately so I don't think it's a matter of not having enough GHZ.

again, this is over a 120 second interval.

It seems like the API might be returning data slowly to the .Net app.  The .Net app doesn't really hold the data in a short term storage space so we're not sure how fast it's pulling in data then pushing it out.  I'd like to further troubleshoot this by having us pull a subset or the entire set of the data into a flat file and then push it out to another test server to see how long each step takes.

Does the community feel I have sufficiently tested to claim 'this is not a ms sql server issue'?  I hate making such claims without fully checking everything and want to help the team figure out the issue.

Thanks in advance.


texts: How to handle large database updates with node js

texts:
I have a program which is time based. The users have all day to enter data that will affect their score, however if you haven't entered anything by midnight then your score is negatively affected. I had thought about setting up a chron job to handle this at midnight, but am then faced with a batch process that may be updating hundreds of thousands of records all at once.

I've decided one way to reduce this would be to divide the batch into 1000 records and then release the event loop, stack on another 1000. Is this a good way to go about something so massive? Would creating a second server instance and offloading the work to that server be a better idea?


texts: Clear SQL Server Buffer after SSIS is done?

texts:
I'm new...

I've built an ETL process in SSIS that takes 46 flat files and loads them into stg.Suppliers and triage.Suppliers.  Then another solution runs that applies Business Rules.  And then the final solution moves the good stuff from stg.Suppliers into pdw.Suppliers.

My question is when I run all this (once a week); is it good (best practice) to clean up the buffer for the stg.Suppliers table?  I notice that when I run this query that it's the number one cached_pages_count.

If yes, how?


texts: Setup mail notifications on SQL Server (severe) errors

texts:
I want to be notified by email whenever an error occurs on our production database (e.g. failed database backups, engine crashes due to corruption etc). I could not find built-in support for this, so I wrote my own stored procedure that runs every 5 minutes.

My stored procedure takes the following steps:

Store the results from sp_readerrorlog (last x minutes) into a temporary table
Remove all records in that table that are on my ignore list (such as database back succeeded messages).
If there are any entries left, send an email to me (containing the last x minutes of the error log).

While this appears to work fine there is a major flaw: If the SQL Agent Service is not running I'm not getting notified about errors.

Therefore I'm wondering, there must be a better way to do what I want?


texts: Need to make sense of FileTable permissions

texts:
I'm setting up a FileTable to allow users to quickly stuff some PDF report files into a database for later searching via an intranet application (to be built later). Sorting out the necessary permissions is a bit of a challenge, and I'm hitting a few roadblocks.

First, I have confirmed that I (with sysadmin privileges) can add and remove files to the share without any issues, so the FileTable is functional at least.

I've added an end user to an AD group, created a Windows login in SQL Server for this group, mapped it to a database user, and granted that user select, insert, update, and delete permissions on the FileTable. The user has logged out of his computer and logged in again to make sure the group I added him to is in the auth token.

He can access the base filestream share for the instance (\\servername\FileStream), but when he tries to go deeper to the database-specific directory (\\servername\FileStream\DatabaseFileStreamDirectoryName), he gets a permission error ("You do not have permission to access..."). Same thing if he tries to access the FileTable path directly (\\servername\FileStream\DatabaseFileStreamDirectoryName\PDFReports).

So, what is the proper way to grant access so that users can browse to the share subdirectory for a specific database, see the directories in there for FileTables, and ultimately browse/access files within the FileTables? It's apparently more than just a user mapping in the database, and select/insert/update/delete permissions on the table, and I'm not finding a whole lot of clear documentation on this so far.

Here's the (slightly anonymized) code I've used for setting this up:

USE master
GO
ALTER DATABASE AppData ADD FILEGROUP FS CONTAINS FILESTREAM
ALTER DATABASE AppData ADD FILE ( NAME = N'FS', FILENAME = N'S:\Filestream\AppData\FS' ) TO FILEGROUP [FS]
ALTER DATABASE AppData SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE AppData SET FILESTREAM( DIRECTORY_NAME = N'AppData' ) WITH NO_WAIT
ALTER DATABASE AppData SET MULTI_USER
GO
USE AppData
GO
CREATE TABLE PDFReports AS FileTable WITH (FileTable_Directory = 'PDFReports')

GRANT SELECT, INSERT, UPDATE, DELETE ON PDFReports TO [MyDomain\App Operators]


texts: duplicate key error creating unique index on temp, SQL Server 12.0.5589 bug?

texts:
I have a job that runs a stored procedure, that seems to intermittently fail with a duplicate key error when creating a unique clustered index on a temp table.   How is it possible for me to get this error based on the code below.

It seems to happen when the source table is being updated.  Transaction Isolation Level is set to read uncommitted, and there ARE some triggers on "SomeTableWithPrimaryKey". It doesn't happen every time, just three times in the last 100 runs. There are about 3 million rows in the source table "SomeTableWithPrimaryKey", and about 500k of them are being selected after the filters are applied.

Is this a SQL Server Bug?  Some race condition?

This statement is based on a true query, the names have been changed to protect the innocent...and my job. :)

--on SQL Server 12.0.5589
SELECT  p.PrimaryKeyColumn, p.SomeOtherColumn
INTO    #Temp
FROM    dbo.SomeTableWithPrimaryKey p
WHERE   p.SomeCondition = 1
        AND (p.SomeCondition2 = 2
            OR EXISTS(
                SELECT 1
                FROM    dbo.SomeTable st
                        INNER JOIN dbo.SomeOtherTable sot
                            ON st.Column1 = sot.Column1
                WHERE   st.SomeCondition1 = 1
                        AND st.PrimaryKeyColumn = p.PrimaryKeyColumn
                )
            )
;

--productid index
CREATE UNIQUE CLUSTERED INDEX CX_Temp ON #Temp(PrimaryKeyColumn)
;

^this is where we get the error:

  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.#Temp______...000000D5A436' and the index name 'CX_Temp'. The duplicate key value is (123456789).
  [SQLSTATE 23000] (Error 1505)  The statement has been terminated.
  [SQLSTATE 01000] (Error 3621).  The step failed.


texts: aggregation sum and aggregation last value MDX

texts:
I have to design a datawarehouse for the reporting of the stock to date, historized on a daily basis.
I have designed datawarehouse and a fact table
I want to design a mondrian cube with aggregation sum and aggregation exception last value. it is the stock and the measures are semi-additive.
I would like to know the procedure.
here is what i tried.
below are the images of the table fact and the dimensions.


texts: In a MySQL Master/Slave replication, what would happen if I write to the master table and in slave table is drop?

texts: In a MySQL Master/Slave replication, what would happen if I write to the master table and in slave table is drop ?


texts: Why is our query suddenly returning rows it should not (using READPAST and UPDLOCK options)?

texts:
We have a job table that looks like this

CREATE TABLE [dbo].[Clearing](
    [Skey] [decimal](19, 0) IDENTITY(1,1) NOT NULL,
    [BsAcctId] [int] NULL,
    [Status] [varchar](20) NULL,
     CONSTRAINT [csPk_Clearing] PRIMARY KEY CLUSTERED ( [Skey] ASC )
)

with a covering index like this

CREATE NONCLUSTERED INDEX [IX_Status] ON [dbo].[Clearing]
(
    [Status] ASC
)
INCLUDE ( [Skey], [BsAcctId])

and we use this query to pick the next job

select top (1) Skey, BsAcctId, Status from Clearing with ( readpast, updlock )
  where (Clearing.Status = 'NEW')
  order by Clearing.Skey

(The real table has about 10 columns. They are all in the index include() clause and the select column list.)

The execution plan is very simple.  It does an index seek using IX_Status, then a top operator.  Since the index is sorted on (status, skey) the plan does not need a sort.

The table is in a database in an AlwaysOn Availability Group.  The group has 2 DB servers. (It is a test system.)

Normally this table and query work great.
So we go to apply Windows updates, and do the usual.

Fail over the primary to the secondary
Apply Windows updates on the former primary
Fail over back to the original primary
Apply Windows updates on the secondary

After the second fail over and all the worker processes get new connections to the new primary, the query starts failing in the sense that multiple processes start getting the same jobs.

The problem is load related.  With 4 worker processes running it did not happen.  But with 10 workers it happens consistently.

This is using SQL Server 2016 Enterprise.  We do not have the query store enabled to see if the execution plan was weird at some point.

Any suggestions on why the query would start failing after two fail overs?

Since the query is only using the index and not touching the table, is UPDLOCK reliable?

Update 1 - we changed the process to list the locks held by the spid (using sp_lock @@spid) just after doing the select.  For the same skey, we are seeing different KEY locks held on the IX_Status index (indid=9)

KEY (aad9d6e672f9)  U
KEY (154698b9131c)  U

Update 2 - using index hint did not help.

Update 3 - Removing order by clause in query avoided the problem.  But we have a second table with same problem that needs the order by.

Update 4 - Our worker processes maintain a db connection pool.  ODBC does not tell us when a fail over happens, so connections to an old primary stay in the pool until we try to use them and they fail.  We suspect after we failover DB1 -> DB2 -> DB1, then old connections to DB1 might not fail like they should.  We made a change to close all pooled connections after any one connection is lost, and this seems to have avoided the problem.  (SQL Server ODBC added a "Connection Resiliency" feature that is fueling this suspicion.)


texts: Create Temporary Access to production support

texts:
We're thinking of creating a web site where a manager can provide temporary access to a person for a limited set of data in our production SQL azure database for production support.   Auditing is mandatory.  The support user  would need to be their domain account so we can audit the activity.  Write it to a table, add the user to an AAD group that has needed permissions, a logic app would remove that user from the group after x minutes.
Is there an easier way or better solution for this?


texts: SQL minimum permissions to manage any job

texts:
I am looking for a way to grant minimum rights to a login to manage ANY SQL Server Agent job.  The SQLAgentOperatorRole allows to manage own jobs and disable/enable any job, however I need to allow the user to edit any job on the instance.  Is there a built-in msdb role that I am not seeing.  Obviously I dont want to grant the user SA rights on the instance and would like to avoid granting owner rights to MSDB.

Any advice is appreciated.


texts: How to restrict access to SQL Server user in some period of time

texts:
I have two SQL Server users 'A' and 'B'.

I use user 'A'  for ETL and 'B' for reporting.

My requirements is to restrict user 'B' access while user 'A' performing ETL (Extraction, Transforming and loading)


texts: Unable to connect excel to sql server. Getting an error:

texts:
sql server microsoft sql: a connection was successfully established with the server, but then an error occurred during the login process. (provider: ssl provider, error: 0 - the certificate chain was issued by an authority that is not trusted.)
I'm using sql server development edition, and this suddenly stopped working.  I think it's certificate based but can't figure out how to specifically fix this problem, despite reading a number of articles.


texts: Table or dictionary in code on a client for data that's not changed often?

texts:
Suppose I have a table where data won't be changed frequently. Say, a table "currencies" with a list of currencies which I accept on my website. I'd change it once in a while probably.
What are pros and cons of keeping "currencies" directly in code in a web application, as a dictionary, as opposed to keeping them in a database in a table? How is it usually done?


texts:
I have several times converted scalar functions into inline tvf. Usually this is the basic design I used in inline tvf:

CREATE FUNCTION TVF(@ID INT)
RETURNS TABLE
AS
RETURN(
    WITH BASE AS(
        SELECT @ID AS ID -- the collections of parameter
    )
    SELECT TOP 1 T1.ID -- TOP 1 is not needed if always 1 row returned
    FROM BASE
        LEFT OUTER JOIN T1(NOLOCK) -- left join to ensure 1 row returned
        ON T1.ID = BASE.ID
)

This is, used to replace the following function:

CREATE FUNCTION SCALAR(@ID INT)
RETURNS INT
AS
BEGIN
    DECLARE @ID_RET INT;

    SELECT @ID_RET = T1.ID
    FROM T1(NOLOCK)
    WHERE T1.ID = @ID
    OPTION(MAXRECURSION 0)

    RETURN @ID_RET -- 1 row will be ensured to be returned
END

Considering the functionality, this works very well. The scalar can be replaced with TVF without any data changed. However, due to usage of LEFT OUTER JOIN at function level, sometimes I encountered with high read/write and CPU, almost 2 times than scalar function (in SQL Profiler). The duration, however is improving incredibly for large datasets (100k+).

My question is, is this the correct approach to develop one row result TVF? Is high read and CPU is acceptable in exchange of decreased duration? Please note I'm an inexperienced dba and mainly an app programmer, so I know very little about database performance tune up.

I'm not sure about the performance usage of OUTER APPLY compared with this approach.

This is the syntax I used for example. However, I cannot reproduce the high read and high CPU here.

-- TABLE
CREATE TABLE T1(
    ID INT NOT NULL PRIMARY KEY
);
WITH CT1 AS(
    SELECT 1 AS ID
    UNION ALL
    SELECT ID + 1 AS ID
    FROM CT1
    WHERE ID < 1000
)
INSERT INTO T1
SELECT CT1.ID
FROM CT1
OPTION ( MAXRECURSION 0)
GO

-- FUNCTIONS
CREATE FUNCTION TVF(@ID INT)
RETURNS TABLE
AS
RETURN(
    WITH BASE AS(
        SELECT @ID AS ID
    )
    SELECT T1.ID
    FROM BASE
        LEFT OUTER JOIN T1(NOLOCK)
        ON T1.ID = BASE.ID
)
GO
CREATE FUNCTION SCALAR(@ID INT)
RETURNS INT
AS
BEGIN
    DECLARE @ID_RET INT;

    SELECT @ID_RET = T1.ID
    FROM T1(NOLOCK)
    WHERE T1.ID = @ID
    OPTION(MAXRECURSION 0)

    RETURN @ID_RET
END
GO

-- TEST
;WITH T1 AS(
        SELECT 1 AS ID
        UNION ALL
        SELECT ID + 1 AS ID
        FROM T1
        WHERE ID < 1000
    )
SELECT FN.*
FROM T1
CROSS APPLY DBO.TVF(T1.ID) FN
OPTION(MAXRECURSION 0)

;WITH T1 AS(
        SELECT 1 AS ID
        UNION ALL
        SELECT ID + 1 AS ID
        FROM T1
        WHERE ID < 1000
    )
SELECT DBO.SCALAR(T1.ID) AS ID
FROM T1
OPTION(MAXRECURSION 0)

DROP FUNCTION TVF
DROP FUNCTION SCALAR
DROP TABLE T1


texts: How do I write my SQL Code in MS Access SQL?

texts:
Table Name: Records

ID
Date
Time
Status
BoxType
Material
Rack
EmployeeNr
Transaction

1
25-05-2020
13:11:12
70
36757
4
4
188
2

2
25-05-2020
13:12:40
71
31178
2
5
188
2

3
25-05-2020
13:13:31
71
31178
2
5
188
102

4
27-05-2020
13:14:14
71
38444
3
2
181
3

5
27-05-2020
13:14:15
71
38444
3
2
181
3

6
27-05-2020
13:14:41
71
38444
3
2
181
3

7
27-05-2020
13:15:10
71
39123
6
5
188
3

8
27-05-2020
13:15:51
71
38444
3
2
181
103

9
27-05-2020
13:16:51
71
38444
3
2
181
103

The Desired OUTPUT I want is Rows 1, 6 and 7

ID
Date
Time
Status
BoxType
Material
Rack
EmployeeNr
Transaction

1
25-05-2020
13:11:12
70
36757
4
4
188
2

6
27-05-2020
13:14:41
71
38444
3
2
181
3

7
27-05-2020
13:15:10
71
39123
6
5
188
3

Hello dear developer friends,
I have this table "Records". There are two types of transaction viz. 'Forward' and 'Reverse'. The Forward transactions are represented by single to two digits of numbers (that is from 1 to 30) and Reverse transactions are represented by three digits (that is from 100 to 130). Now, every unique Forward transaction has a unique reverse transaction. (For eg. Transaction 1 will have 101, transaction 2 will have 102 and transaction 3 will have 103 and so on).
I do not want to see the Reverse transactions and as well as their forward transactions in my report. (For eg. 102 and 2, 103 and 3) But, I have to find the matching forward transactions whose other columns data (Date, Status,BoxType, Material, Rack,EmployeeNr) are same of Reverse transaction. (As seen in the image, record no. 2 and 3) Also, there can be more than one matches of forward transactions for reverse transactions, and I want to skip only those many forward transactions equal to number of Reverse transactions. (*As seen in the table, record no. 4,5 and 6 matches to record no. 8 and 9, so I want to hide two records no. 4 and 5 with 8 and 9)
So, I tried to write a SQL query, but it is not working in MS access. Appreciate your great help! Thank you! Best Regards.
My code
with table_a as (
  select a.*, row_number() over (partitition by Status, BoxType, Material, Rack, EmployeeNr , Transaction) as dup_n --all columns except ID and time.
  ---for multiple entries of same type
  from Records a
  where Transaction<100
  )
   ,table_b as (
  select b.*, (Transaction-100) as type_new,
         row_number() over (partitition by Status, BoxType, Material, Rack, EmployeeNr , Transaction) as dup_n --all columns except time and ID
         ---for multiple entries of same type
  from Records b
  where Transaction>99
  )
select*from(
  select a.* from table_a
  left join table_b
  on <Status, BoxType, Material, Rack, EmployeeNr , Transaction> --all columns except time and ID
  and a.type = b.type_new
  and a.dup_n = b.dup_n
  )
  where a.Transaction is null


texts: Cannot execute inserts querys after upgrade to mysql 5.7

texts:
I have Debian 8 (Jessie) server and i followed this procedure to upgrade from mysql 5.5 to mysql 5.7 https://dev.mysql.com/doc/refman/5.7/en/linux-installation-debian.html

Now, any script in the server that have an INSERT query is not working... The script is being executed but the INSERT query is returning always false with no error description...

Any ideas how to solve?

This is what i have installed

Also, when i click on Database button in phpmyadmin

i'm getting this error


texts: Find the most recent row where no exact match on date is found

texts:
I am trying to write a query that tries to extract the value of the exchange rate where:

Query date = the exchange date; and
Query currency = the specified currency

If it does not contain a matching value like on 5-Sep-2015, it should extract the value from the last date that contains a value. In this case, the value should be taken from 4-Sep-2015:

 Create Table ExchRate
 (  Exch_ID int,
    Currency varchar(3),
    Exch_value numeric(14,7),
    Exch_date datetime)

Insert INTO ExchRate
Values (1, 'SGD', 1.4173000, '2015-09-04 00:00:00'),
       (2, 'SGD', 1.4240000, '2015-09-07 00:00:00'),
       (3, 'SGD', 1.4291500, '2015-09-08 00:00:00'),
       (4, 'EUR', 0.8984700, '2015-09-04 00:00:00'),
       (5, 'EUR', 0.8955700, '2015-09-07 00:00:00'),
       (6, 'EUR', 0.8957000, '2015-09-08 00:00:00')

The result should be:

Currency   Exch_Date   Exch_Value
SGD        2015-09-05  1.4173000

Secondly, what if I am joining from another table to calculate the total cost in USD? What kind of SQL statement should I be using? In this SQLFiddle example, it should have exchange rate value for every PO with different PO dates.


texts: MariaDB Galera Cluster without HAProxy, MYSQL Proxy, etc

texts:
I'm looking to deploy a MariaDB galera cluster across three servers.  However, I've noticed that the documentation often references HAProxy or the like.  Does this not cause a single point of failure situation?

I guess the question I'm asking is - Do I need any sort of load-balancing software to use a MariaDB cluster?  Or could I have support for the cluster built-in to the app?

Apologies for the English.


texts:
I have following query

SELECT Dept
      ,ID
  FROM (SELECT TOP (@RowTo) ROW_NUMBER() OVER (ORDER BY Dept) AS ROWID,
               Dept
              ,ID
          FROM Department
         WHERE Dept LIKE @Team + '%') as Calls
 WHERE RowID >= @RowFrom AND RowID <= @RowTo

I need to get a DISTINCT list of the Departments, but need the paging that this query returns.  I have tried to rewrite this many a times, but I'm failing miserably.  Anyone got any pointers?

Table data

ID    Department  Location
1     HR          London
2     HR          Berlin
9     HR          Paris
11    HR          Amsterdam
12    IT          Berlin
13    IT          London
15    HR          New York
18    IT          Paris
19    IT          Barcelona
20    HR          Barcelona
21    Finance     Paris
22    Finance     London
23    Finance     New York

Using the following SQL:

SELECT Dept
      ,ID
  FROM (SELECT TOP (@RowTo) ROW_NUMBER() OVER (ORDER BY Dept) AS ROWID,
               Dept
              ,ID
          FROM Department
         WHERE Dept LIKE @Team + '%'
         ORDER
            BY Dept) as Calls
 WHERE RowID >= @RowFrom AND RowID <= @RowTo

Returns:

Dept    ID
HR      1
HR      2
HR      9
HR      11
IT      12
IT      13
HR      15
IT      18
IT      19
HR      20

Now, I know that I need to drop out the ID column in order to get a DISTINCT list of values for the Department column.  By doing so, I only get:

Dept
HR
IT

None of the Finance Departments are dropping into the result set because the sub query is returning the TOP 10 records which it's not part of.  This is where I'm no getting stuck.


texts: Should I used varchar(max) or varchar(4000) SPARSE?

texts:
I have a "comment"-type column that is rarely used -- around 6% non-null in a population of 3 million records. The average length (when used) is 6 characters, and the max so far is around 3KB. A maximum of 4000 characters is reasonable for this field. I have two options:

comments varchar(max) NULL -- this is the current column definition
comments varchar(4000) SPARSE NULL

My current understanding is that in both cases, a NULL value would require no storage -- just the column's NULL bit set and a length of 0 in the row metadata.

But for the non-null cases, does one have a clear advantage over the other?

The extra 4-byte pointer for sparse columns with values suggests they are always stored off-row like text or very large varchar(max) fields. Is that the case?

If so, I'd lean toward using varchar(max), since it only stores values off-row if the total row length exceeds 8KB, and the majority of my values are short and unlikely to put a row over the limit.

I haven't seen this particular situation addressed in the BOL, so I'm hoping someone here knows enough about the innards of MSSQL to give some insight.

(If it matters, I'm currently using 2008R2, but hoping to upgrade soon to 2014.)