Get Primary records where N most recent Secondary records match condition I want a list of Authors (primary records) whose 2 most recent Books (secondary records) are best sellers.

INSERT INTO Authors
    (`author_id`, `author_name`)
VALUES
    (41, 'Heida Palffrey'),
    (42, 'Michael Grist'),
     ...

INSERT INTO Books
    (`book_id`, `author_id`, `book_title`, `published_date`, `best_seller`)
VALUES
(156, 41, 'Red Heat',      '2007-01-21', 'false'),
(161, 41, 'Far From Home', '2003-12-27', 'true'),
(859, 41, 'Storm Warning', '1992-08-19', 'true'),
(914, 41, 'Dead Heat',     '1981-01-04', 'false'),

(780, 42, 'The Last',      '2017-01-31', 'true'),
(198, 42, 'Mr Ruin',       '2016-01-18', 'true'),
(166, 42, 'Mr. Troop Mom', '1982-06-27', 'false'),
(796, 42, 'Ben Bootin',    '1972-12-01', 'false'),

    ...

For the above data, I just want author "Michael Grist" whose two most recent books were best sellers.

I have been able to get authors with consecutive best sellers, but not authors whose most recent two books were best sellers.

SELECT t.author_id, a.author_name, t.consecutive, t.best_seller, COUNT(1) consecutive_count
FROM (
  SELECT b.* ,
    @r:= CASE WHEN @g = b.`best_seller` THEN @r ELSE @r + 1 END consecutive,
    @g:= b.`best_seller` g
  FROM books b
  CROSS JOIN (SELECT @g:='true', @r:=0) t1
  ORDER BY author_id, published_date
) t
JOIN `authors` `a` ON (`a`.author_id = t.author_id)
GROUP BY t.author_id,t.consecutive,t.`best_seller`
HAVING consecutive_count >= 2 AND  t.`best_seller` = 'true';

For the data in the sqlfiddle below, only Chaddie Dreakin and Michael Grist fit the requirements of two most recent best sellers.

http://sqlfiddle.com/#!9/8808649/8

SET @@group_concat_max_len = 9;

SELECT  a.author_name
    FROM
    (
        SELECT  author_id,
                GROUP_CONCAT(best_seller ORDER BY  published_date DESC) AS bs
            FROM  Books
            GROUP BY  author_id
            HAVING  bs = 'true,true'
    ) AS x
    JOIN  Authors AS a USING(author_id) ;

Notes:

9 is based on true,true being 9 characters, and that is enough to catch the first two.
I'm assuming best_seller is a VARCHAR.
ORDER BY  published_date DESC -- the "latest"
HAVING -- can't use WHERE in this situation


PostgreSQL ensure no concurrent reads I'm sorry if it's a very basic question, but would really appreciate educated advice on this:

Say I have an operation, which first has to read from a table and then insert a row if the read value satisfies a certain condition, and throw if it doesn't, e.g. I have a following table:

id     | value
---------------
serial | integer

And before inserting a row I need to first:

select sum(value) from table;

then if sum + value of the row, which I wanna insert is less than 100 - insert, if no - throw.

I need to somehow make those reads be processed only one-by-one, because if I have 2 concurrent queries, both would read, get that the sum is less than 100 and both insert, which would result in an undesired state, which would have been avoided if queries were executed sequentially.

What would be optimal solution to ensure one-by-one processing here?

So far I've tried doing select for update to lock concurrent read operations, but I have a feeling it might be not optimal here...

I'm also aware I could do that with a before insert trigger but the nature of this logic seems not to fit there because the threshold (100 in the example) comes from application layer runtime and is actually different every time.

I've read a bit about transaction modes (e.g. serializable) but not sure if that's what I need here...

I need to somehow make those reads be processed only one-by-one,
  because if I have 2 concurrent queries, both would read, get that the
  sum is less than 100 and both insert, which would result in an
  undesired state, which would have been avoided if queries were
  executed sequentially.

That's a typical use case for the serializable isolation level, with an automated retry of the transaction when it fails with a serialization failure (SQLSTATE=40001)

Short of adapting your schema specifically to deal with the concurrency issue, the other techniques you mention are not going to work directly anyway:

the BEFORE INSERT trigger is limited in visibility to the already committed transactions, so sum(value) <= 100-newvalue may be true even  though another transaction has already inserted a row that will make this untrue as soon as it commits.
In the select for update technique, what row(s) is it supposed to lock? Even if doing select for update on the entire table, that does not prevent reads or new inserts. The select for update technique would work if it targeted a single pre-existing row, that the transaction would need to lock before proceeding further, implementing effectively a critical section.
But that table/row does not exist in your schema as mentioned in the question.


Deselect CPU in affinity setting for OS Only use? A memory recommendation is to reduce MaxMemory by 4GB or 10% of RAM (equally divisible by cores) to dedicate that memory for the OS.

Most questions on CPU affinity deal with splitting them out between instances.

My question is should one (or more) cores be deselected for OS only use? One site did this on a Win2003/SQL 2005.

Q1: Was this a beneficial thing to do? As I understand, older Win versions put everything in NUMA 0 so it sort of made sense. But I just recently learned that SQL was aware of that and favored NUMA 1+ to offset. And now, as I understand it, the newer OS (Win2008+) utilize NUMA and spread their CPU usage across the cores.

Q2: So, even if it was a good idea under older Win/SQL versions is it still a good idea under newer Win/SQL versions?

Changing CPU affinity was never a common practice but did have it's uses on WindowsNT and later on Windows Server 2000/3,

The main issue was that processor load could be misaligned on multiprocessor systems and this allowed for freeing up resources. This could also be helpful on systems that where not dedicated to running SQL Server. So yes this could be beneficial in some edge cases. Old Small Business Servers come to mind.

On later versions of Windows the OS is more likely to distribute load between processor so for performance reasons this will not help but can be beneficial on servers running multiple instances if you want to limit those to specific CPU or NUMA node


Copying my Oracle 10g Express database to another PC I have Oracle 10g Express. How can I make a copy of my database and application?

I don't want to make a back up, I want to move my DB and application to another PC.

First Install XE to another PC.

Then clone your database: Backing Up and Restoring the Database

Then transfer clone to another PC and restore the clone there.

This post may help you also: How to clone Oracle Express 10g Edition?


Determining Database Restore Date Using SQL Server, how can I obtain the restore date for a particular database?

It's a bit difficult to determine exactly what you want from the way your question is worded, but it sounds like this post may give you the needed information

Identify when a SQL Server database was restored, the source and backup date

From the post:

  After restoring a database your users will typically run some queries
  to verify the data is as expected. However, there are times when your
  users may question whether the restore was done using the correct
  backup file. In this tip I will show you how you can identify the
  file(s) that was used for the restore, when the backup actually
  occured and when the database was restored.

Here's the script from that post:

SELECT [rs].[destination_database_name]
    ,[rs].[restore_date]
    ,[bs].[backup_start_date]
    ,[bs].[backup_finish_date]
    ,[bs].[database_name] AS [source_database_name]
    ,[bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC


How to manipulate bytea data when using backup script as a template I have a project with a web app that has many configurable options, with the configurations stored in a PostgreSQL database. Most fields are text or ints, but there are a couple configurations stored as JSON decoded into bytea.

We have one "default configuration" setup script that my team (Software Quality Assurance) utilizes to put everyone's testing server on the same page. The script takes a few hours to manually create data and store configurations via the web app. I'd like to save our team hours of setup time by simply sharing a SQL file with the create / insert statements necessary to get the database to the same configuration in way less time.

So with a script such as:

CREATE DATABASE example;

...

CREATE TABLE example_table (
    id integer NOT NULL,
    ip_address text,
    domain text,
    configuration bytea
);

...

COPY example_table (id, ip_address, domain, configuration) FROM stdin;
1   0.0.0.0 invalid.domain.com  \x7b277265666572656e63655f746f5f69705f61646472657373273a27302e302e302e30272c277265666572656e63655f746f5f646f6d61696e273a27696e76616c69642e646f6d61696e2e636f6d277d
\.

--- The above bytearray is "{'reference_to_ip_address':'0.0.0.0','reference_to_domain':'invalid.domain.com'}"

Originally, my thought was to replace "0.0.0.0" and "invalid.domain.com" with whatever the user wanted, and then the user would execute the script to setup their database with the common configuration. However, with the encoded data, the end result would have invalid references to the server's IP address and domain. Has anyone ever had a problem like this and figured out a solution? Is there a better way of doing this? I'm open to any suggestions other than changing the database organization (I can't swing that one).

Instead of using...

COPY example_table (id, ip_address, domain, configuration) FROM stdin;
1   0.0.0.0 invalid.domain.com  \x7b277265666572656e63655f746f5f69705f61646472657373273a27302e302e302e30272c277265666572656e63655f746f5f646f6d61696e273a27696e76616c69642e646f6d61696e2e636f6d277d
\.

... I think you should be able to change your pattern, and do something like ...

INSERT INTO
    example_table
    (id, ip_address, domain, configuration)
VALUES
    (1, '0.0.0.0', 'invalid.domain.com',
    $quote${"reference_to_ip_address":'0.0.0.0',
            "reference_to_domain":'invalid.domain.com'}$quote$::bytea) ;

... or, if you want to make things a little simpler, use a Common Table Expression, and you will have to change settings only in ONE place:

WITH reference_data(id, ip_address, domain) AS
(
    -- This is the only place that needs changing...
    VALUES (1, '0.0.0.0', 'invalid.domain.com')
)
INSERT INTO
    example_table
    (id, ip_address, domain, configuration)
SELECT
    id, ip_address, domain,
    ('{"reference_to_ip_address":''' || ip_address || ''',' ||
     '"reference_to_domain":''' || domain || '''}')::bytea
FROM
    reference_data ;

You can check the data

 SELECT
     id, ip_address, domain, convert_from(configuration, 'utf8')
 FROM
     example_table ;

 id | ip_address | domain             | convert_from
 -: | :--------- | :----------------- | :-------------------------------------------------------------------------------
  1 | 0.0.0.0    | invalid.domain.com | {"reference_to_ip_address":'0.0.0.0',"reference_to_domain":'invalid.domain.com'}
  1 | 0.0.0.0    | invalid.domain.com | {"reference_to_ip_address":'0.0.0.0',"reference_to_domain":'invalid.domain.com'}


PostgreSQL CPU 100% after configure Wal log shipping hot standby I have configured Hight Avaliabily WAL Log Shipping Hot Standby (SSL) with one node. But after I configure, both hosts (primary and secondary) take 100% CPU usage.
The most strange is because I have only one database called "TESTE" with one table called "EXAMPLE" to check if replication was OK. The replication worked perfectly but I don't understand why my CPU takes 100% usage because I don't have any application connect on my cluster.
I ran HTOP command and get this results:
Image 1 - Primary Server HTOP Info

Image 2 - Secondary Server HTOP info

S.O. version:
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 10 (buster)
Release:        10
Codename:       buster

PostgreSQL version:
postgres (PostgreSQL) 12.3 (Debian 12.3-1.pgdg100+1)

Primary server postgres.conf file:
max_connections = 100
SSL = on
shared_buffers = 2GB
work_mem = 10485kb
maintenance_work_mem = 512MB
dynamic_shared_memory_type = posix
effective_io_concurrency = 200
max_worker_processes = 4
max_parallel_maintenance_workers = 2
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
wal_level = replica
wal_buffers = 16MB
max_wal_size = 200MB
min_wal_size = 80MB
checkpoint_completion_target = 0.7
archive_mode = on
max_wal_senders = 3
wal_keep_segments = 8
hot_standby = on
hot_standby_feedback = on
random_page_cost = 1.1
effective_cache_size = 6GB
default_statistics_target = 100

pg_stat_activity info:

pg_stat_archiver info:

pg_stat_replication info:

If I stop postgres service in both servers the CPU still has 100% usage.
Anyone can help me?

By exposing a database with a weak superuser password to the internet you invited somebody to break in and use your CPU for their own purposes, probably mining crypto-currencies.
Take the machine from the internet, wipe it clean and re-install the operating system. In the case of a hosted database, decommission the database and get a new one.
To protect yourself from this problem, choose good passwords.


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

The PostgreSQL server \COPY command is very simple and just aborts on a single failure. You might think that it could do far better (I know I do), but there's a reason that the PostgreSQL codebase is so compact with respect to MySQL's (by a factor of ~ 10/1).

However, there is the (very) nice pgloader programme which compensates for this at the price of having to run a separate utility.

Of course, if you're good at the PL/pgSQL language (internal to the the server), then maybe you could explore that route - but why reinvent the wheel? Python and Perl also have internal PostgreSQL options. Then of course, there's all the languages under the sun external to the server.

From the manual:

  PgLoader Reference Manual

  pgloader loads data from various sources into PostgreSQL. It can
  transform the data it reads on the fly and submit raw SQL before and
  after the loading. It uses the COPY PostgreSQL protocol to stream the
  data into the server, and manages errors by filling a pair of
  reject.dat and reject.log files.

which appears to be right up your alley?

The way it works is: (sorry for the long quote)

TL;DR  - pgloader loads a batch (configurable) at a time. On failure, it "marks the spot", uses \COPY again up until that point, stops, then puts the bad record into a file and continues from bad-record + 1.

  Batches And Retry Behaviour

  To load data to PostgreSQL, pgloader uses the COPY streaming protocol.
  While this is the faster way to load data, COPY has an important
  drawback: as soon as PostgreSQL emits an error with any bit of data
  sent to it, whatever the problem is, the whole data set is rejected by
  PostgreSQL.

  To work around that, pgloader cuts the data into batches of 25000 rows
  each, so that when a problem occurs it's only impacting that many rows
  of data. Each batch is kept in memory while the COPY streaming
  happens, in order to be able to handle errors should some happen.

  When PostgreSQL rejects the whole batch, pgloader logs the error
  message then isolates the bad row(s) from the accepted ones by
  retrying the batched rows in smaller batches. To do that, pgloader
  parses the CONTEXT error message from the failed COPY, as the message
  contains the line number where the error was found in the batch, as in
  the following example:

  CONTEXT: COPY errors, line 3, column b: "2006-13-11"

  Using that information, pgloader will reload all rows in the batch
  before the erroneous one, log the erroneous one as rejected, then try
  loading the remaining of the batch in a single attempt, which may or
  may not contain other erroneous data.

  At the end of a load containing rejected rows, you will find two files
  in the root-dir location, under a directory named the same as the
  target database of your setup. The filenames are the target table, and
  their extensions are .dat for the rejected data and .log for the file
  containing the full PostgreSQL client side logs about the rejected
  data.


Log shipping and new NDF file I have a log shipped database, and I want to create a new NDF file in another disk for moving historic tables to that NDF file. My goal is to free some space in the MDF file because the disk that the MDF resides is getting full.

I have a doubt and is: if I create the NDF file in a disk in the primary server that the secondary server does not have: does the log shipping process will fail?

If yes, how can I avoid this?

if I create the NDF file in a disk in the primary server that the secondary server does not have: does the log shipping process will fail?

If logshipping paths are same on the primary and secondary servers then adding an ndf will be seamless.

If the paths are different on primary and secondary servers then

Stop the backup, copy and restore jobs related to the database that you are adding a secondary file.
Add the ndf file on primary using ALTER DATABASE db_name ADD FILE..
run the backup job manually on the primary server. This will generate a T-log backup. Make sure that all the previous log backups are applied to the secondary.
Run the copy job manually so that the T-log with above changes is shipped to secondary server.
Manually restore the log file (from above step) using the

RESTORE log db_name FROM      Disk='path_T-log_from_Above_Step.trn'
with norecovery,  ---> Very Important .. !!
MOVE 'logical_file_name_of_ndf'
TO 'physical_path\secondary_file_name.ndf'

Once above steps are successful, enable all the backup, copy and restore jobs for the database. All the subsequent backup, copy and restore jobs should be successful now.


SQL Server 2008 Analysis Management objects work with SQL Server 2005? Can SQL Server 2008 Analysis Management objects API work with SQL Server 2005 install?

I believe that it can, and the API wraps a fairly well documented web service interface called XML/A.  The SQL Server 2008/2008R2 management software will work with SSAS 2005 cubes as well.


Insert file content (with white line and special characters ) in bash variable for insert in oracle table with sqlplus I write this simpe test.ksh scipt:
#!/bin/ksh

TEST=$(cat $1 )
sqlplus -S *****/*****@*****<<- EOF

set define off;
SET SQLBLANKLINES ON;
INSERT INTO DBADOC.TEST
(
        "TEST"
)
 VALUES
(
      q'[ $TEST ]'
)
;
commit;
EOF

exit

Now if i run it with a simple file (3 lines without special char):
./test.ksh /home/oracle/scripts/test.txt
1 row created.

Commit complete.

It runs perfectly.
If I choose a complex file (with blank lines and special character ), it doesn't work:
./test.ksh /home/oracle/scripts/check_free_space_dg.ksh
SP2-0734: unknown command beginning "# ========..." - rest of line ignored.
SP2-0734: unknown command beginning "# @(#) NOM..." - rest of line ignored.
SP2-0734: unknown command beginning "# @(#) GRU..." - rest of line ignored.
SP2-0734: unknown command beginning "# @(#) DES..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
[...]

SQL-Plus doesn't support hash as a comment character.  You can use these to make comments:

REM or REMARK at the beginning of a line
-- (double dashes) at the beginning of a line
or paired /* with */ for block comment, as seen in C.


How to make MySQL query performant WHERE one column uses greater than and one column uses less than? I'd like to make this query perfomant--
SELECT *
FROM my_table
WHERE foo_id = 8
AND enabled = true
AND start_time < '2020-09-12T06:59:59.999Z'
AND end_time > '2020-09-07T07:00:00.000Z'

This is tricky because I need less than on start_time and greater than on end_time.  If there is a composite index on foo_id, enabled, start_time, end_time the index is good at filtering down all the rows with foo_id, enabled, and start_time matching the criteria--but then does a full scan to find the rows that match end_time.
My best efforts at optimizing so far are--
SELECT *
FROM my_table
WHERE foo_id = 8
AND enabled = true
AND start_time < '2020-09-12T06:59:59.999Z'
AND id IN (
  SELECT id
  FROM my_table
  WHERE end_time > '2020-09-07T07:00:00.000Z'
)

and
SELECT *
FROM my_table
WHERE foo_id = 8
JOIN my_table AS t ON t.id = my_table.id
  WHERE t.end_time > '2020-09-07T07:00:00.000Z'
AND enabled = true
AND start_time < '2020-09-12T06:59:59.999Z'

Both of these efforts try to use two indexes separately: the one on foo_id, enabled, start_time and the other on end_time.  They are both faster.  But I'd like to avoid a query with a large IN list.  And doing the JOIN just to sneak in a WHERE doesn't feel right.
Maybe this is good enough?  INTERSECTION would serve well here.  This seems like it would be common problem, so curious if I'm missing an obvious/better solution.

Having two "ranges" is not easy to optimize.  This may help:
INDEX(foo_id, enabled, start_time, end_time)
INDEX(foo_id, enabled, end_time, start_time)

(As you said...)  The optimizer will pick between those two "composite" indexes based on statistics that may tell it which range is shorter.  It will quickly check through the two = tests (foo_id and enabled; btw, they can be in either order).  Then it will scan through the 3rd value.  The final column is there in hopes that "Using index condition" (aka 'ICP') helps.
The reason for having 2 indexes -- the length of one of the scans is likely to be less than the other.
Is that the entire query?  Anything added may throw a monkey wrench into the analysis.
I don't think the time syntax with 'T' and 'Z' will work in MySQL.
A more complex solution...  Can the start_time..end_time ranges overlap?  If not, an optimization can take advantage of that.  http://mysql.rjweb.org/doc.php/ipranges


Directly import a csv gzip'ed file into SQLite 3 I'd like to import a 15GB file comma delimited gzip compressed file into Sqlite 3 without having to use temporary files.

I'd like to run a command like:

zcat input/surgical_code.csv.gz | tail -n +2 | sqlite3 db.sqlite ".import /dev/stdin surgical_code"

This decompresses the file, skips the header and tries to import.

The problem is that I am not able to specify .mode cvs and .separator "," on the same quoted command to SQlite3.

Any ideas?

Using $(echo -e 'line1\nline2') didn't work for me:

gzcat input/surgical_code.csv.gz | tail -n +2 | sqlite3 db.sqlite $(echo -e '.mode csv \n .separator \",\"\n.import /dev/stdin')

  Error: mode should be one of: ascii column csv html insert line list tabs tcl

I found that sqlite3 custom init script can have meta-command as well as SQL statement:

#!/bin/sh

commandfile=$(mktemp)

# create temporary init script
cat <<EOF > $commandfile
.mode csv tablename
.import /dev/stdin tablename
EOF

# import
bzip2 -d -c huge_compressed.csv.bz2 | sqlite3 --init $commandfile dbname.db


MYSQL-BEFORE UPDATE Set value of Column A to value in Column B if Column A is to be updated I have a nightly scheduled event which updates all rows in 'products' table with the newest 'unit_price' from a price list table where 'price_list.active-date' is >= the CURDATE(). What I need to do is create a trigger for the products table to set the field 'last_price' to the old unit_price before the update only on rows where unit_price is changing. Is this possible.

Scheduled Event:

CREATE EVENT `update_active_price` ON SCHEDULE EVERY 1 DAY
    STARTS '2015-02-09 00:00:00'
    ON COMPLETION PRESERVE
    ENABLE COMMENT 'updates nightly at midnight'
    DO
        UPDATE products p
        JOIN price_list pr ON p.product_id = pr.product_id
        SET p.unit_price = pr.amount
        WHERE active_date = CURDATE();

Currently non-working Trigger:

CREATE TRIGGER `update_last_price` BEFORE UPDATE ON `products`
FOR EACH ROW UPDATE products p
SET p.last_price = p.unit_price;

Adjust the trigger's syntax

DROP TRIGGER IF EXISTS `update_last_price`;
CREATE TRIGGER `update_last_price`
BEFORE UPDATE ON `products`
FOR EACH ROW
SET NEW.last_price = OLD.unit_price;

or if the trigger is to have multiple lines, do this

DROP TRIGGER IF EXISTS `update_last_price`;
DELIMITER $$
CREATE TRIGGER `update_last_price`
BEFORE UPDATE ON `products`
FOR EACH ROW
BEGIN
    SET NEW.last_price = OLD.unit_price;
END $$
DELIMITER ;


How can I find who has allocated a user lock via dbms_lock? We have a print queue process that acquires a user lock via dbms_lock.allocate_unique. It performs the work and then releases the lock. The process works very well.

Today, we have a process that allocated a lock, as seen in this output from dbms_lock_allocated:

NAME            LOCKID      EXPIRATION
printer_lock    1073741825  11/30/2013 9:35:46 AM

Whatever session has this hasn't released the lock. Is there any way we can find the session that allocated this lock, so we can end their session and release the lock?

EDIT:

Querying DBA_LOCKS does not show any sessions with the lock type of 'PL/SQL User Lock'.

Thanks!

The type of locks you are referring to are stored in table (not view) SYS.DBMS_LOCK_ALLOCATED.  I believe they only have a "handle", but not a specific owner, i.e.: they all belong to SYS.  That said, if you have active blocking situation going on (someone is waiting for the lock and it's not getting released by the session who allocated the lock) then this query might show what's going on:

SELECT blocker.username blocker, blocker.sid blocker_session
     , blocked.username blocked, blocked.sid blocked_session
  FROM v$session blocked
  JOIN v$session blocker
  ON (blocked.blocking_session = blocker.sid)
 WHERE blocked.wait_class = 'Application'
   AND blocked.event='enq: UL - contention';


MySQL takes a long time to start I'm trying to understand why mysql would take 20mn to start.
I'm running 5.7.17-13-log, and I have a large ibdata1 file (20GB) even with file per table activated. Here is it's content:

# innochecksum -S /data/mysql/ibdata1
File::/data/mysql/ibdata1
================PAGE TYPE SUMMARY==============
#PAGE_COUNT     PAGE_TYPE
===============================================
  781526        Index page
 1031230        Undo log page
   11836        Inode page
   25060        Insert buffer free list page
   22645        Freshly allocated page
     114        Insert buffer bitmap
     107        System page
       1        Transaction system page
       2        File Space Header
     116        Extent descriptor page
       3        BLOB page
       0        Compressed BLOB page
       0        Other type of page
===============================================

Here is what I see in the error log:

2017-06-05T19:08:31.307808Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-06-05T19:08:31.719378Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /data/mysql/xb_doublewrite, size 31457280 bytes
2017-06-05T19:29:09.747406Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-06-05T19:29:09.747507Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-06-05T19:29:09.763021Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-06-05T19:29:09.869445Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-06-05T19:29:09.869470Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-06-05T19:29:09.869574Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 1238576ms. The settings might not be optimal. (flushed=0, during the time.)

I see a mention of the page cleaner afterward taking 20mn, on a startup it sounds weird!

Here is a sysdig output on mysqld which shows that it's reading ibdata1 all that time:

11706 13:01:38.019123953 0 mysqld (94026) > pread fd=4(<f>/data/mysql/ibdata1) size=16384 pos=9304113152
11707 13:01:38.019163204 0 mysqld (94026) > switch next=0 pgft_maj=1 pgft_min=4116243 vm_size=45320516 vm_rss=14609036 vm_swap=0
11709 13:01:38.019292874 0 mysqld (94026) < pread res=16384 data=.......F...E..._...n....E..............6?a..=..h................................
11712 13:01:38.019337538 0 mysqld (94026) > pread fd=4(<f>/data/mysql/ibdata1) size=16384 pos=9232695296
11713 13:01:38.019355435 0 mysqld (94026) > switch next=0 pgft_maj=1 pgft_min=4116247 vm_size=45320516 vm_rss=14609036 vm_swap=0
11715 13:01:38.019399597 0 mysqld (94026) < pread res=16384 data=.......?...9...G...n....E..............5?1.j....................................
11718 13:01:38.040605854 0 mysqld (94026) > pread fd=4(<f>/data/mysql/ibdata1) size=16384 pos=9304522752
11719 13:01:38.040644306 0 mysqld (94026) > switch next=0 pgft_maj=1 pgft_min=4116438 vm_size=45320516 vm_rss=14609560 vm_swap=0
11721 13:01:38.040774631 0 mysqld (94026) < pread res=16384 data=69....._...F...n...n....E..............*?A...u. .....w..........................
11872 13:01:38.062582012 0 mysqld (94026) > pread fd=4(<f>/data/mysql/ibdata1) size=16384 pos=9232826368
11873 13:01:38.062633600 0 mysqld (94026) > switch next=0 pgft_maj=1 pgft_min=4116541 vm_size=45320516 vm_rss=14610084 vm_swap=0
11875 13:01:38.062748273 0 mysqld (94026) < pread res=16384 data=.te=...G...?...O...n.(.gE..............X?8.i.R.......f.c........................

Update 1

innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 32G
innodb_log_buffer_size         = 128M
innodb_buffer_pool_instances    = 8
innodb_max_dirty_pages_pct  = 5
innodb_io_capacity      = 2000
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

Any ideas?

Thanks,

Turned out that mysql scan all the undo looking for uncommitted transactions.
Since I had a very large undo space in the ibdata, I moved to use undo tablespaces. Setting up the undo truncate option keeps my undo tablespace very small. Now my db opens in 2-3mn top!
Thanks,


How to see urls of incoming SQL Server traffic? We are migrating the dns entry for our SQL Server database from an machine name to a C-Name, in preparation for moving it from a bare metal box to a virtual machine.  We have switched all the connection strings we know about and also issued instructions across our department, asking everyone to switch their connections in case there are one-off apps or db connected spreadsheets, etc.

We would like to monitor the traffic coming into the box to verify that there is no incoming traffic that got to the server via the old machine name.  Is there any way to do this?  What I need specifically is the url that all of the clients used to reach the server.  We tried monitoring the port with Wireshark but that didn't seem to provide the info we needed.

Thanks!

By the time the client connects, it typically doesn't present the hostname it used.  The client resolves the hostname in DNS and connects to the IP(s) that were returned from the DNS lookup.

Kerberos connections will fail if the client uses a hostname that doesn't correspond to an SPN registered for the SQL Server service account.  And NTLM can fail if the server is not configured to accept auth at the hostname the client uses.  But there's no general way to see this.  The configuration changes required to connect with these Auth schemes through a DNS alias are outlined here.

One thing you can do is to add an additional IP address to the server and configure the two DNS entries to resolve to different IP addresses.  That you would be able to monitor either at the network level or in sys.dm_exec_connections.


SQL Server: Failed to monitor external signals This program has encountered a fatal error and cannot continue running.
The following diagnostic information is available:

       Reason: 0x00000007
      Message: Failed to monitor external signals
      Process: 26596 - sqlservr
       Thread: 26600 (application thread 0x1000)
  Instance Id: 4065d66f-ef73-4bb1-bfe3-5a4ef091cdf0
     Crash Id: 71fa707f-be4b-4b39-9269-ec697e7b5baf
  Build stamp: 9a4c738f0e0d015cbf851ead55d593dc0c806bb1b05452f58289c247307d420d
*********** PAL PANIC CORE DUMP GENERATION FAILED **********
Unable to locate handle-crash.sh. Error 2.
*********** PANIC CORE DUMP GENERATION FAILED **********
Attempt to launch handle-crash.sh failed with error 0x00000002
Failed to connect to bus: No such file or directory
Attempting to start the Microsoft SQL Server service failed.

If I manually run, /opt/mssql/bin/sqlservr I get

Capturing core dump and information...
cat: /proc/27493/maps: No such file or directory
cat: /proc/sys/vm/max_map_count: No such file or directory
/opt/mssql/bin/crash-support-functions.sh: line 399: (100*0)/: syntax error: operand expected (error token is "/")

This is because you need make the hosts /proc accessable in the chroot, in order to do that you can use a --bind mount,

sudo mount --bind /proc /opt/mschroot/proc

Where /opt/mschroot is the location of your chroot.


When you select from a table on an indexed column and order by that column in the opposite direction of the index, does the index help Let's say I have a large table of transactions with a date field and an index on that field in ascending order. If I select that field from the table and order by in reverse order is it able to somehow use that index in reverse, or is it going to have to do a table scan or some other less efficient method of accessing those records and sorting them?

If the index is covering it's likely it will be used in conjuction with a sort operation in the query plan to reverse the order.

Edit: Following a little more thought!

It will depend on whether this is a trivial query or involves joins. If trivial:

SELECT x,y FROM MyTable ORDER BY y DESC

and the index order is Y ASC, a reverse scan of the index leaf level should avoid a sort.

If non-trivial:

SELECT x,y FROM MyTable mt INNER JOIN MyOtherTable mot ON mot.y = mt.y

it should depend on the sort order of MyOtherTable.y. If it's ASC as per MyTable.y then the two indexes would be read in index order and a sort applied after the join. If it's desc, in theory a reverse order index scan could be used for the join and an additional sort wouldn't be required to satisfy your order by clause.

Edit2: Couldn't recall if this would show up in the execution plan in SQL Server. The icon doesn't indicate this is a reverse scan, nor does the tooltip on hover. Properties however shows 'Scan Direction - Backward' or checking the plan XML reveals

<IndexScan Ordered="true" --->ScanDirection="BACKWARD"<--- ForcedIndex="false" NoExpandHint="false">
            <DefinedValues>
              <DefinedValue>
                <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[MyTable]" Column="OtherId" />
              </DefinedValue>
            </DefinedValues>
            <Object Database="[TestDb]" Schema="[dbo]" Table="[MyTable]" Index="[IX_MyTable_OtherId]" />
          </IndexScan>


Get availability group and listener of the current DB (dynamic SQL) I have hundreds of DB on an instance and I want to remove the DB from their AG (each DB is in a separate AG), remove the listener and then delete that AG. I have written the script below to automate this a bit:

DECLARE @name VARCHAR(50);
DECLARE @sql nvarchar(4000);
DECLARE db_cursor CURSOR FOR select name from sys.sysdatabases where name not in ('testing_db');

Open db_cursor
Fetch Next From db_cursor Into @name
WHILE @@FETCH_STATUS = 0
BEGIN
     select @sql =
     'ALTER AVAILABILITY GROUP [' + @name + '] REMOVE DATABASE [' + @name + '];' + char(10) +
     'ALTER AVAILABILITY GROUP [' + @name + '] REMOVE LISTENER ' + char(10) +
     'DROP AVAILABILITY GROUP [' + @name + '];' + char(10) + 'GO' + CHAR(10) + CHAR(10) + CHAR(10)

     print @sql;

     fetch Next From db_cursor Into @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

This will give me something as the below for each DB in my instance:

ALTER AVAILABILITY GROUP [**DB1**] REMOVE DATABASE [DB1];
ALTER AVAILABILITY GROUP [**DB1**] REMOVE LISTENER N**<>**
DROP AVAILABILITY GROUP [**DB1**];
GO

I cannot find a way to get the availability group and its listener for each DB in each run (The stuff between ** ** needs to be replaced by the AG name/AG listener accordingly). I have searched how to get an AG name/listener for a DB but all of the solutions require a lot of joins which will complicate this query. What's the neatest way to do this?

You need to run queries agains the DMV to get the AG name.

Here's some code you could use as a base:

select DB_NAME(AG_DB.database_id) "DB", AG.name  "AG Name", li.dns_name "Listener"
from sys.dm_hadr_database_replica_states AG_DB
join sys.availability_groups AG on AG.group_id = AG_DB.group_id
join sys.availability_group_listeners li on li.group_id = AG.group_id

This will only returns databases that are in AG (vs a query in sys.databases which will return the system DB as well).

P.s. If you do not need the listener (which seems to be the case) then you do not need the 3rd table and it will keep the query pretty simple.


Oracle 12cR2 RMAN - ORA-01547, ORA-01194, RMAN-06054 Oracle 12cR2.
Error:

channel default: deleting archived log(s)
archived log file name=+RECO/.../thread_1_seq_166200.26582.1054942673
RECID=62 STAMP=1054943183
archived log file name=+RECO/.../thread_1_seq_166201.11529.1054941077
thread=1 sequence=166201
channel default: deleting archived log(s)
archived log file name=+RECO/.../thread_2_seq_223357.10403.1054942671
RECID=58 STAMP=1054943182
unable to find archived log
archived log thread=2 sequence=223358
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get
error below
ORA-01194: file 4 needs more recovery to be consistent
ORA-01110: data file 4: '+DATA/.../data_d...fno-4'
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================
RMAN-03002: failure of recover command at 10/28/2020 00:24:13
RMAN-06054: media recovery requesting unknown archived log for thread
2 with sequence 223358 and starting SCN of 1871183322646

My commands:
restore primary controlfile from '/.../RESTORE/..._controlfile_20201027_....rman';

alter database mount;

restore database preview;

The final output part of 'restore database preview;' command:

recovery will be done up to SCN 1871164140237
Media recovery start SCN is 1871164140237
Recovery must be done beyond SCN 1871183871662 to clear datafile fuzziness
Finished restore at 27-OCT-20

catalog start with '/.../RESTORE/';

run
{
set newname for database to '+DATA/.../%U' ;
set newname for tempfile 1 to '+DATA/.../%U';
set newname for tempfile 2 to '+DATA/.../%U';
set newname for tempfile 3 to '+DATA/.../%U';
set newname for tempfile 4 to '+DATA/.../%U';
set newname for tempfile 5 to '+DATA/.../%U';
set until scn 1871183871663;
restore database;
switch datafile all;
switch tempfile all;
recover database;
}

NOTE: all of the full database backuppieces, archivelog backups, daily spfile backup and daily controlfile backup are under this path '/.../RESTORE/'.
I setted my limit to scn 1871183871663 (Recovery must be done beyond SCN 1871183871662 to clear datafile fuzziness + 1) but it says "media recovery requesting unknown archived log for thread 2 with sequence 223358 and starting SCN of 1871183322646."
I couldn't find any clear solution. How can I resolve it?
Best regards,

1871183871663 is bigger than 1871183322646. You do not have the necessary archivelog in the backups for recovering the database beyond SCN 1871183322646.
You simply specified a point in time recovery target that is not included in the backups.


Monumental difference in execution time between queries when using RECOMPILE query hint I have two almost identical queries running on the same SQL Server 2005 instance:

The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application developer, just the DBA :).
The second one is exactly the same as the first one, added a OPTION (RECOMPILE) at the end.

Nothing else has been changed.

The first one takes 55 seconds everytime its run.
The second one takes 2 seconds.

Both result sets are identical.

Why would this hint generate such a dramatic gain in performance?

The Books Online entry on RECOMPILE doesn't offer a much detailed explanation:

  Instructs the SQL Server Database Engine to discard the plan generated
  for the query after it executes, forcing the query optimizer to
  recompile a query plan the next time the same query is executed.
  Without specifying RECOMPILE, the Database Engine caches query plans
  and reuses them. When compiling query plans, the RECOMPILE query hint
  uses the current values of any local variables in the query and, if
  the query is inside a stored procedure, the current values passed to
  any parameters.

  RECOMPILE is a useful alternative to creating a stored procedure that
  uses the WITH RECOMPILE clause when only a subset of queries inside
  the stored procedure, instead of the whole stored procedure, must be
  recompiled. For more information, see Recompiling Stored Procedures.
  RECOMPILE is also useful when you create plan guides. For more
  information, see Optimizing Queries in Deployed Applications by Using
  Plan Guides.

Since my query has a lot of local variables, my guess is that SQL Server is able to (seriously) optimize it when I use the OPTION (RECOMPILE) query hint.

Everywhere I look people are saying that OPTION (RECOMPILE) should be avoided. The explanation for this is generally that using this hint SQL Server is not able to reuse this exection plan and therefore has to waste time recompiling it everytime. (But) Given the gigantic performance advantage, I'm inclined to think that using this query hint this time would be a good thing.

Should I use it?
If not, is there a way I can force SQL Server to use a better execution plan without this hint and without altering the application?

As documented in the article Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

  If you use a local variable in a query predicate instead of a
  parameter or literal, the optimizer resorts to a reduced-quality
  estimate, or a guess for selectivity of the predicate. Use parameters
  or literals in the query instead of local variables

When the optimiser has no usable statistics at all for a column it will guess that an = predicate will match 10% of rows, BETWEEN 9%, and any of >, >=, < and <= will match 30%. If there are column statistics available an = predicate will be treated differently as below.

  Even when local variables are used in a query, an estimate that is
  better than a guess is used in the case of equality predicates.
  Selectivity for conditions of the form "@local_variable = column_name"
  is estimated using the average value frequency from the histogram for
  column_name. So, for example, if the column column_name contains all
  unique values, then a selectivity estimate of 1/(number of unique
  values in column) will be used, which is accurate.

So this is essentially the same as using for OPTIMIZE FOR (UNKNOWN). It may well be more accurate than a flat 10% guess but it isn't tailored to the specific values that you are querying.

  To force SQL Server to optimize a query every time it is run, and use
  the values of local variables to estimate cardinality and cost during
  optimization of the query, add the RECOMPILE query hint to the query.

With the use of RECOMPILE you presumably are getting more accurate cardinality estimates and therefore a different plan with join orders/ join types more suited to the number of rows returned from different parts of your actual query.


Recursive cte avoiding loops I'm trying to write a recursive CTE to explore a workflow system. Unfortunately, I'm getting maximum recursion errors due to loops:

with cteActs as
 (
  select a.id as [id], aa.TASKNEXTID as [childid]
  from TASK a
  inner join TASKNEXT aa on a.id = aa.TASKPARENTID
  where a.id != aa.TASKNEXTID
  ),
 cteNext as
(
    select a.*
    from cteActs as a
    where a.id=42
    union all
    select a.*
    from cteActs as a
    inner join cteNext as n on a.id = n.childid
    )
select *
from cteNext

The table TASK is a list of tasks whereby 42 is "Start job" for example. TASKNEXT links 42 to possible subtasks back in the TASK table. e.g. it may link 42 to 43 which may be "Find materials" e.g.

ID, name, childID
42, Start job, 43
42, Start job, 44
43, Find materials, 200
44, Report to boss, 201
201, Discuss with boss, 202
202, Receive payment, 44

I think the recursion is dying because 44>201>202>44 creates a loop the query doesn't escape from. How can I allow for this? Most of the examples/tutorials I read assume a strict parent>child relationship, where a child can never be the parent of something in its own higher tree.

What I'm trying to get to is a distinct list of TASKS that originate from flows starting at task 42, or wherever I choose.

This is iteration 2 which may work, but runs so slow:

  select a.id as [id], aa.ACTIONACTIVITYID as [childid]
   into #temp
  from TASK a
  inner joinTASKNEXT aa on a.id = aa.TASKPARENTID
  where a.id != aa.TASKNEXTID
  create clustered index [hello] on #temp (ID ASC)
  create nonclustered index [hello2] on #temp (childid ASC)
  ;
with cteNext as
(
    select a.*,
    cast(',' + cast(a.ID as varchar(10)) + ',' as varchar(max)) as Path,
    0 as [cyc]
    from #temp as a
    where a.id=42
    union all
    select a.*,
    n.Path + cast(a.ID as varchar(10)) + ',',
         case when n.Path like '%,'+cast(a.ID as varchar(10))+',%'
           then 1
           else 0
         end as [cyc]
    from #temp as a
    inner join cteNext as n on a.id = n.childid
    where n.cyc = 0
    )

select   id, childid
from cteNext
where cyc =0

Execution plan

So the best result I've managed to come up with is adding the following improvements
- optimising the queried table (creating a new temporary table with suggested indexes)
- adding a path element to check I'm not revisiting an existing part of the path
- adding a depth counter as a limiter. This does however mean I'm consciously choosing not to have the full result set

  select a.id as [id], aa.ACTIONACTIVITYID as [childid]
   into #temp
  from TASK a
  inner join TASKNEXT aa on a.id = aa.TASKPARENTID
  where a.id != aa.TASKNEXTID

  create clustered index [hello] on #temp (ID ASC)
  create nonclustered index [hello2] on #temp (childid ASC)
  ;
  drop table #temp2;
with cteNext as
(
    select a.*,
    cast(',' + cast(a.ID as varchar(10)) + ',' as varchar(max)) as Path,
    0 as [cyc], 0 as [depth]
    from #temp as a
    where a.id=42
    union all
    select a.*,
    n.Path + cast(a.ID as varchar(10)) + ',',
         case when n.Path like '%,'+cast(a.ID as varchar(10))+',%'
           then 1
           else 0
         end as [cyc], n.depth+1 as [depth]
    from #temp as a
    inner join cteNext as n on a.id = n.childid
    where n.cyc = 0 and n.depth <=11
    )

select *
into #temp2
from cteNext
where cyc =0

The initial purpose of this data is "fluffy", I'm generating a graph-map out of it to show our workflows so a limited depth is fine in the first instance at least. But better answers accepted if anyone has any


Postgres GROUP BY id works on table directly, but not on identical view Lets say I have two tables. One of them references the other in a one-to-many relationship:

CREATE TABLE t (
  id SERIAL PRIMARY KEY
, name text
);

INSERT INTO t (name) VALUES ('one'), ('two'), ('three');

CREATE TABLE y (
  id SERIAL PRIMARY KEY
, tid INTEGER REFERENCES t
, amount INTEGER
);

INSERT INTO y (tid, amount) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(3, 1);

I can query data from these tables in order to get t rows matching a certain condition set on t and y:

SELECT row_to_json(t.*) as "t_nogroup" FROM t
JOIN y ON t.id = y.tid
WHERE t.id = 1
AND y.amount > 1;

This yields

t_nogroup
=========
{"id":1,"name":"one"}
{"id":1,"name":"one"}

Which works, yes, but contains duplicates. I can filter out the duplicates by using a GROUP BY:

SELECT row_to_json(t.*) as "t_group" FROM t
JOIN y ON t.id = y.tid
WHERE t.id = 1
AND y.amount > 1
GROUP BY t.id;

Making:

t_group
=======
{"id":1,"name":"one"}

Great! However, when I try to exchange t and y for non-materialized VIEWs:

CREATE VIEW vt AS SELECT t.* FROM t;
CREATE VIEW vy AS SELECT y.* FROM y;

And run the same queries with the new VIEWs

SELECT row_to_json(vt.*) as "view_t_nogroup" FROM vt
JOIN vy ON vt.id = vy.tid
WHERE vt.id = 1
AND vy.amount > 1;

SELECT row_to_json(vt.*) as "view_t_group" FROM vt
JOIN vy ON vt.id = vy.tid
WHERE vt.id = 1
AND vy.amount > 1
GROUP BY vt.id;

I then get the error for the second query:

ERROR: column "vt.*" must appear in the GROUP BY clause or be used in an aggregate function

Why does this happen? Whatever logic that saw that * contained id when using the table t should still work for the view vt, right?

Fiddle link: http://sqlfiddle.com/#!17/26b0e/5

I'm running PostgreSQL 9.6. I'm also aware that this is a contrived example, but I am running into this error in a very similar one to many relation where multiple GROUP BY statements are needed and so just using DISTINCT isn't an option, like it would be for this example.

The problem appears because Postgres knows that table t has (id) as the primary key - so we can use GROUP BY t.id and have more columns from that table in the SELECT list without aggregating them - but it can't infer the same for view vt.

For the examples provided, you can rewrite them as not to use GROUP BY at all. Since you don't need any column from the second joined table in the select list, you can convert the JOIN to an EXISTS subquery, i.e. convert the join to a semi-join. Now both the tables and views semijoin work without errors:

SELECT row_to_json(t.*) as "t_group" FROM t
WHERE t.id = 1
  AND EXISTS
      ( SELECT 1
        FROM y
        WHERE t.id = y.tid
          AND y.amount > 1
      ) ;

SELECT row_to_json(vt.*) as "t_group" FROM vt
WHERE vt.id = 1
  AND EXISTS
      ( SELECT 1
        FROM vy
        WHERE vt.id = vy.tid
          AND vy.amount > 1
      ) ;


DB2 9.7 - Find rows inserted by a specific trigger We are auditing a table TRANSACTIONS that inserts a row in HISTORY_TRANSACTIONS when specific columns are changed. These inserts happen through a number of triggers defined on TRANSACTIONS table. Is it possible to find out which trigger inserted a specific record in HISTORY_TRANSACTIONS ? (seems impossible, but I wanted to confirm that no system tables could achieve this as well?)

You are correct in your assumption – there is nothing done at the database level that would track the source of a row.  You would need to modify your history table definition and trigger logic to add this kind of tracking.


ERROR: could not read block 84 in file "base/16386/670007": Input/output error I have a database, when I look for some tables in the database it shows error

ERROR:  could not read block 84 in file "base/16386/670007": Input/output error

I am using postgresql.

Whenever this error is encountered I have to reload the table again and again. But this is not a permanent solution. How I can resolve this error? I have tried to take backup using pg_dump but its also not working.

Your storage subsystem is failing. This is not a PostgreSQL error, as such, it's an error from the underlying file system or disks.

You might learn more from the output of the dmesg command and/or the kernel logs.


Can I monitor the progress of importing a large .sql file in sqlite3 using zenity --progress? I'm trying to monitor the progress of a sqlite3 command importing a large .sql file into a database using zenity --progress.

I've tried the following which will import the file, however progress is not shown:

sqlite3 DATABASE < import_file.sql | zenity --progress --percentage=0 --auto-close

I know I need to provide zenity a numeric source for the progress, but don't really know how to obtain the number.

Can anyone help me?

The sqlite3 tool does not output such numbers.

You would have to write some script that feeds the lines of the import file into sqlite3, and at the same time feeds the percentage numbers into zenity. Try something like this:

#!/bin/bash
maxlines=$(wc -l < import_file.sql)
exec 3> >(zenity --progress --auto-close)
counter=0
while IFS= read -r line; do
    echo "$line"
    counter=$(($counter+1))
    echo $(($counter * 100 / $maxlines)) >&3
done < import_file.sql

Call it with thescript | sqlite3 dbfile.


Understanding the simple execution plan in PostgreSQL I'm new to execution plans still trying to learn.
My problem is for below query

select * from item limit 3;

I got following execution plan,

"Limit  (cost=0.00..0.07 rows=3 width=75)"
"  ->  Seq Scan on item  (cost=0.00..2345.00 rows=100000 width=75)"

According to my understanding this first limit the number of records to 3 and then do a sequential scan for all the records. Why does this do a scan for all the records since it has already limited the result to 3 rows ?

According to my understanding this first limit the number of records to 3 and then do a sequential scan for all the records. Why does this do a scan for all the records since it has already limited the result to 3 rows ?

That's not (necessarily) what's happening.

Outer nodes consume rows from inner nodes one by one with a demand-pull model.

So in this case, the limit node will result in a maximum of three nodes being consumed from the inner sequential scan node. As a sequential scan node can deliver nodes progressively it will do very little work.

This won't be true of some other node types. For example, if you add an ORDER BY (which you always should with LIMIT otherwise the results can be pretty much random) it will add a Sort or top-N sort node. A Sort node must fully execute the entire subplan before it returns any results, so it might do a lot of work even if there's a limit node on the outside. A top-N sort node can sometimes avoid some of that work, but it depends on the inner plan.

If you use EXPLAIN (ANALYZE, VERBOSE) you can see the actual row counts processed by each node, which is usually informative. In this case the actual rows processed by the inner seqscan will probably be 3.

You will find http://explain.depesz.com/ extremely useful when reading plans. PgAdmin-III also has a graphical explain that some people like, though I don't use it myself.


Can power failure rollback mysql innodb database of many days? I have an application running on a x86 system with MySQL InnoDB database. This application store many rows per day and yesterday, due to a power failure, I experienced a rollback of a one entire day of data. Simply I have a hole of data of the day before the power failure, and I'm sure there were many data before the power failure because I checked backup dumps.

How can a power failure involving data committed and so many rows? Is because of the log structure of innodb? Can I do something in MySQL configuration (expect to avoid power failure)?

My configuration is a standard-default of the mysql installer in Windows x64 7 environment.

I'm quite sure that the rollback was made by automatic Windows 7 System Restore after the power failure. This restored the mysql data folder. I cannot understand how a OS restore can move files from other applications even becuse I was using a custom path for mysql-data: c:\mysqldata and not the standard c:\programData\Mysql\....
My solution is to disabled the windows restore, but I think can be a better solution.

EDIT

The problem is the IBD data files of Mysql/MariaDB, these are a "monitored extension" and Windows rollbacks them anywhere in the volume.

EDIT2

Seems that switching innodb_file_per_table option to OFF solved the problem because new tables (only new ones) will not use IBD files.


How can I extract a lessequal char from SQL_Latin1_General_CP1_CI_AS? When I create a table like this

create table char_test(
    item varchar(10) collate SQL_Latin1_General_CP1_CI_AS
)
go

I can store varchars containing ≥

insert into char_test values ('≥');

When I do a

select item from  char_test;

I get

item
----------
=

But using

select replace(item, '≥', '>=') from  char_test;

gives

-----------------
>=

So the database knows the character. How can I convert the string to a nvarchar showing this character?

Edit:

The important fact here is, that SQL Server here maps some different characters to the same character without throwing errors or warnings.

I dare call this very bad design.

varchar doesn't support ≥: unicode only

This includes the literal '≥' which is varchar too

Collation here doesn't matter: this is sorting and comparison only never seen this before!

Some more SQL to use your table

SELECT ASCII('≥'), CHAR(61), '≥'
GO
insert into char_test values ('≥');
GO
select item, ASCII(item) from  char_test;
GO
select replace(item, '≥', '>='), replace(item, '=', '>=') from  char_test;
GO

create table nchar_test(
    item varchar(10) collate SQL_Latin1_General_CP1_CI_AS,
    Nitem nvarchar(10) collate SQL_Latin1_General_CP1_CI_AS
)
go
insert into nchar_test values ('≥', N'≥');
GO
select item, ASCII(item), Nitem, UNICODE(Nitem) from nchar_test;
GO

Edit: after more playing

If you have a value then use CP 437

select CAST(Nitem COLLATE SQL_Latin1_General_CP437_CI_AS AS varchar) from nchar_test;
GO
select CAST(N'≥' COLLATE SQL_Latin1_General_CP437_CI_AS AS varchar)
GO

Note: the ASCII is 242 but this won't give either :-)

SELECT
   ASCII(CAST(N'≥' COLLATE SQL_Latin1_General_CP437_CI_AS AS varchar)),
   CHAR(242),
   CHAR(242) COLLATE SQL_Latin1_General_CP437_CI_AS

You may be able to control this with database collation but frankly use unicode...


Should numeric identifiers like serial numbers be stored as integers? Many of our databases store numeric tracking codes (like serial numbers) as integers. There is little chance of these codes every getting alphabetic characters, but it still seems like an incorrect data type since you would never be performing mathematical operations on them.

What is appropriate data type for these types of identifiers?

One of the biggest advantages to using an INT over say a CHAR or VARCHAR(10) is size.  The INT value will be 4 bytes and the CHAR/VARCHAR value will be 10 bytes.  NCHAR/NVARCHAR would be even larger at 20 bytes.  Because of this indexes on INT values are smaller and thus faster, sometimes considerably so, than a corrisponding CHAR/VARCHAR index.  Also your row sizes are smaller requiring less reads per row.  This is all aside from actual space storage of course.  Thomas LaRock has written several blogs on "right sizing" your data types.  Does this datatype make my column look fat and How to: Right sizing the datatypes currently in the buffer pool for example.

All of this of course assumes that you in fact will not be using characters in your
tracking codes.  If you are then of course you have no choice but to use CHAR\VARCHAR.


Wrong result for FileTableRootPath Function of SQL Server I just restored a database backup and now the result of the function FileTableRootPath() is incorrect.

Where is the place that I can change to change the result of this function?

Below is a T-SQL example from the documentation:

ALTER DATABASE database_name
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'directory_name' );

Note the database must be locked exclusively for this operation.


Using function is much slower than running query I am trying to write a plpgsql function that runs a query where the WHERE part is variable using EXECUTE, like this :
CREATE FUNCTION test1(p_filter TEXT) RETURNS SETOF Bin AS $$
    DECLARE
        q TEXT;
    BEGIN
        q = FORMAT('SELECT * FROM Bin WHERE %s;', p_filter);
        RETURN QUERY EXECUTE q;
    END
$$ LANGUAGE plpgsql;

The function was running much slower than using the query directly so I tried to create a function where I would juste return the query like this :
CREATE FUNCTION test2() RETURNS SETOF Bin AS $$
    BEGIN
        RETURN QUERY SELECT * FROM Bin WHERE ((bin.binFrac).linedivisionnb = 0 AND (bin.binFrac).columndivisionnb = 0 AND (bin.binFrac).height = 64 AND bin._weight = 0);
    END
$$ LANGUAGE plpgsql;

In both cases, using auto_explain I got this plan :
2021-01-12 11:18:14.524 CET [19356] postgres@astar LOG:  duration: 26.203 ms  plan:
    Query Text: SELECT * FROM Bin WHERE ((bin.binFrac).linedivisionnb = 0 AND (bin.binFrac).columndivisionnb = 0 AND (bin.binFrac).height = 64 AND bin._weight = 0);
    Seq Scan on bin  (cost=0.00..3779.06 rows=1 width=181) (actual time=0.008..22.763 rows=72687 loops=1)
      Filter: (((binfrac).linedivisionnb = 0) AND ((binfrac).columndivisionnb = 0) AND (_weight = 0) AND ((binfrac).height = 64))
      Rows Removed by Filter: 14316
2021-01-12 11:18:14.524 CET [19356] postgres@astar CONTEXT:  PL/pgSQL function test1(text) line 6 at RETURN QUERY
2021-01-12 11:18:14.532 CET [19356] postgres@astar LOG:  duration: 45.242 ms  plan:
    Query Text: EXPLAIN ANALYZE SELECT * FROM test1('((bin.binFrac).linedivisionnb = 0 AND (bin.binFrac).columndivisionnb = 0 AND (bin.binFrac).height = 64 AND bin._weight = 0)');
    Function Scan on test1  (cost=0.25..10.25 rows=1000 width=511) (actual time=36.904..43.560 rows=72687 loops=1)

When running the query directly or in an SQL STABLE function I only get the first part :
2021-01-12 11:25:30.976 CET [19356] postgres@astar LOG:  duration: 27.241 ms  plan:
    Query Text: EXPLAIN ANALYZE SELECT * FROM Bin WHERE ((bin.binFrac).linedivisionnb = 0 AND (bin.binFrac).columndivisionnb = 0 AND (bin.binFrac).height = 64 AND bin._weight = 0);
    Seq Scan on bin  (cost=0.00..3779.06 rows=1 width=181) (actual time=0.009..25.511 rows=72687 loops=1)
      Filter: (((binfrac).linedivisionnb = 0) AND ((binfrac).columndivisionnb = 0) AND (_weight = 0) AND ((binfrac).height = 64))
      Rows Removed by Filter: 14316

Where does the extra 20ms come from ? Is there a way to remove this overhead or at least mitigate it ?

The extra times goes to the overhead of plpgsql, which is not small.
With RETURN QUERY, it first reads all of the tuples into a tuple store, and then reads them back out again. In this case, I think that that accounts for most of the additional time.  Note that this extra overhead will be proportional to how rows are returned (not how many are inspected but then filtered out).


How can I have an aggregate function in the WHERE clause? Using PostgreSQL 9.3.24

Issue

Just simple operation, but I don't know how to do that best simplest way.

How can I display results only when sum of available_counts for same product_id is 0 and onway is 0 or NULL? I.e. 130479 in this case. I want display all values, for every store_id.

Thank you.

Update

I tried aggregate functions are not allowed in WHERE.

select
*

from
j_product_store_availability psa

where
sum(available_count) = '0'
and onway = '0'

group by
product_id

order by
product_id

Input.

product_id | store_id | available count | onway
1          | 1        | 0               | 0
1          | 2        | 0               | 0
1          | 3        | 0               | 0
2          | 1        | 0               | 0
2          | 2        | 0               | 0
2          | 3        | 0               | 0
3          | 1        | 0               | 0
3          | 2        | 0               | 0
3          | 3        | 1               | 0
4          | 1        | 0               | 0
4          | 2        | 0               | 0
4          | 3        | 0               | 1

Expected output.

product_id | store_id | available count | onway
1          | 1        | 0               | 0
1          | 2        | 0               | 0
1          | 3        | 0               | 0
2          | 1        | 0               | 0
2          | 2        | 0               | 0
2          | 3        | 0               | 0

You have to use a window function, which does something similar to a GROUP BY, but outpots all rows and not just the groups.

SELECT *
FROM (SELECT *,
             sum(available_count) OVER (PARTITION BY product_id) AS sum_count
      FROM j_product_store_availability psa) AS subq
WHERE onway = 0
  AND sum_count = 0
ORDER BY product_id;


How to log into a fresh MariaDB install? I am usually using Postgres, now I have had to install MySQL/MariaDB to help a friend with some issues.

The problem is just - I am not able to log into the freshly installed database on Debian 9.

When searching for default user name, I am told to use "root" with a blank password. But no matter what I have tried, I am just greeted with Access denied.

mysql -u root
   mysql -u root -p

and then repeat with root@localhost and root@127.0.0.1. I have tried to use the description in the accepted answer of https://superuser.com/questions/603026/mysql-how-to-fix-access-denied-for-user-rootlocalhost but to no avail.

Where am I messing up? Are there any command line utilities similar to Postgres' createuser to create a new user?

mysqld 10.1.26-MariaDB-0+deb9u1 on debian stretch (9.3)

Turned out I had to run mysql_install_db before I was able to do anything - although when trying to log in before that everything seemed to work...


Padding MySQL queries I'm trying to retrieve logs of our plant where a certain event has occurred.

At the moment, I can query the actual event by using an alarm_code field. However, I'd also like to include about an hour's worth of data before and after the alarm event.

How would I go about doing this?

The timestamp field is also the primary key, if that helps.

Thanks,

SELECT (My_Field_List)
FROM My_Table
WHERE My_Timestamp BETWEEN TIMESTAMPADD(HOUR, -1, Alarm_Time)
  AND TIMESTAMPADD(HOUR, 1, Alarm_Time);

Details available in the ever useful documentation here.

Example:

mysql> CREATE TABLE Sample(My_Timestamp TIMESTAMP);
Query OK, 0 rows affected (0.71 sec)

mysql> SELECT * FROM Sample;
+---------------------+
| My_Timestamp        |
+---------------------+
| 1999-01-01 00:00:01 |
| 2010-01-01 00:00:01 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT My_Timestamp FROM Sample WHERE My_Timestamp BETWEEN TIMESTAMPADD(HOUR, -1, '1999-01-01 00:00:01') AND TIMESTAMPADD(HOUR, 1, '1999-01-01 00:00:01');
+---------------------+
| My_Timestamp        |
+---------------------+
| 1999-01-01 00:00:01 |
+---------------------+
1 row in set (0.00 sec)


Recreate a table that was deleted during an ALTER statement (errno: -1)? For the third time in the past 2-3 weeks I've gotten the following message during an ALTER statement:

  Error 2013: Lost connection to MySQL server during query SQL Statement:
  ALTER TABLE `my_database`.`my_table` ...

In this case I was changing AUTO_INCREMENT to 55, that's it. This was necessary because we store JSON strings in LONGTEXT fields with references to database IDs, but during the migration script it did not insert all of the rows correctly so the IDs were mismatched from what already exists out there. AUTO_INCREMENT had to be fixed for solely a functional purpose.

The MySQL Workbench version I'm using is 6.3.3.0 build 592.

When trying to refresh my schemata, I get:

  Error Code: 2013 Lost connection to MySQL server during query

When I try to recreate the table, I get this message:

  Error Code: 1005. Can't create table 'my_database.my_table' (errno: -1)

It seems that the ALTER deleted the old table definition before creating the new table definition.

How can I fix this?

The database is running on AWS RDS, so I can't change anything except what Amazon allows me to. I cannot recreate any of the previous three tables by name in my schema anymore, but only for InnoDB. If I were to try creating this table in MyISAM, it would work.

I'm using Propel for the migration script, so I did explicitly specify the id column by suppressing the autoIncrement field of the XML table tags. I didn't want to dump my tables and rerun the migration script, so I fixed the IDs manually and then attempted to change the AUTO_INCREMENT to what it should be, when this all happened.

Move all tables to a temporary database
Drop the original database (it's empty by now)
Move the tables from the temporary database to the original one.

That should fix the InnoDB dictionary and you'll be able to create the table again.


Overwriting MySQL database to only store 1 month of data We are logging data on hardware with only small memory (short on disk storage) only 4GB.

We only require the data to be stored for 1 month and then be over written in a way that it overrides older data first. The memory on hardware is very small so cannot continue to record indefinitely.

We are using a MySQL data base, the hardware it is running on is not always powered on as it is in a vehicle. The data will be viewed in a graph to show historical data over time.

A few options I have thought of but not sure how to execute it:

Let’s assume I will record 1 million rows of data in a month

When the table (table1) gets to 1 million rows, move this table to another and start new table (table2). When table2 reaches 1 million rows. Delete table1, move table2 to new table and create table3 etc...

This way there will be minimum 1 month of entries.

Second option (not sure if possible):

When the table gets to 1 million rows it starts to override from row 1 again.

A short rotation script/event could look like:

drop table if exists table2;
rename table table1 to table2;
create table table1 like table2;


List the most popular item being ordered I have the following tables:

ITEM (Item#, Item_Name, Unit_Price)

ORDER_ITEM (Order#, Item#, Qty)

How do I list the most popular item being ordered ?

I'm using Oracle SQL Developer.  It is popular by most Qty sold.

To understand the logic I suggest you read this.

http://www.w3resource.com/sql/aggregate-functions/count-with-group-by.php

Try this:

SELECT Item#, SUM(Qty) AS SoldQty
FROM ORDER_ITEM
GROUP BY Item#
ORDER BY SoldQty --You can use 'Desc' depending on how you want to see the data
  ;


Partial backup and restore using filegroups I have a single database that I put all tables into with multiple schemas for the different products I have. I know I can't backup and restore just one schema, but if I move all tables for a schema into a separate filegroup can I then back and restore just that schema without affecting other tables in other schemas? I don't have foreign keys between schemas, so there would be no conflicts.

Also, can I restore into a different database just one of the filegroups without restoring the others?

A while back I decided to stick with just one database, thinking there was a way to accomplish this. If there is no way to do this then it is better to create multiple databases.

I don't know of anyway to do a schema backup or restore in SQL Server.  Your solution of putting all of the schema data on a filegroup will allow you to restore the database to another server, but will require that you restore the primary filegroup as well.  (note, this is possible on SQL Server Enterprise Edition versions 2005 and above). One problem for your situation might be that you could only have one of the partially restored databases running on the same instance of SQL Serve  This link covers the piecemeal restore process:  http://msdn.microsoft.com/en-us/library/ms177425.aspx


T-SQL equivalent to Python Pandas Concatenate I have a few tables that I would like to merge in a similar way that Python Pandas Concatenate does.  I do not want to replicate the information for it is rather large. I'd like to have a VIEW like behavior.  Let me illustrate by a fake example that as far as I know would not work but just to properly explain what I would like to achieve.
Let us say I have these two tables:
CREATE TABLE table_1 (
    [a] [bigint] NOT NULL,
    [b] [bigint] NOT NULL,
    [c] [varchar] (32) NULL,
    [d] [bigint] NULL,
)
GO

CREATE TABLE table_2 (
    [a] [bigint] NOT NULL,
    [b] [bigint] NOT NULL,
    [c] [varchar] (32) NULL,
    [e] [varchar](256) NULL,
)
GO

Where a, b, c are equivalent but hold different data.  d exists in table_1 but not in table_2 and e exists in table_2 but not in table_1.
Tables do not hold any common data. That is, a JOIN on any field would bring zero results.
I would like to be able to do the following (Or something equivalent rather. I know what I am showing is not doable):
CREATE VIEW MyUnion FROM(
SELECT * FROM table_1 UNION SELECT * FROM table_2) AS alldata;

In such a way that if I run:
SELECT * FROM MyUnion;

And would get something like (1 and One are just filler representing a generic number or string):
a       b       c       d       e
1       1       One     1       NULL
1       1       One     NULL    One

Is there any way to do anything like this without duplicating the data? (i.e. creating another table holding everything)
Thank you!

Something like this?:
CREATE VIEW MyUnion AS
SELECT
    a, b, c,
    d,
    CAST(NULL AS varchar(32)) AS e
FROM table_1
UNION ALL
SELECT
    a, b, c,
    CAST(NULL AS bigint) AS d,
    e
FROM table_2 ;


How to determine if SQL Server will not execute parts of the script in parallel? Are there any general rules or maybe easy way to tell if SQL script will execute in batches instead of "all at once"?

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 5);
GO
DBCC SHRINKFILE (DataFile2, 5);
GO

Will it start shrinking DataFile2 only after DataFile1 has finished, or simultaneously?

GO

is a batch terminator in SSMS. Every time you have a GO (or a ;) you have told SQL Server that the batch just ended. So you are sending your commands in batches, and the batches are executed after another - not at the same time.

SO in this case they are all executed serially. Really anytime you run commands they are executed serially in one connection.

The bigger question in my mind is - Why are you doing Shrinkfile? I hope this isn't a production database? This is not a best practice operation to regularly be running at all, as this link describes.

Preview: Shrinking your data files shouldn't be needed - unless you just cleared a ton of space you'll never grow back into, are having serious disk capacity issues or are doing something in dev - and even in these situations, there may be alternatives that are better for you.

Shrinking means introducing index fragmentation. It means some expensive IO operations and there is a good chance you'll be growing back into the space eventually anyway if your database is still live and in use with inserts and updates.


Is there a situation where MySQL creates a nonclustered index automatically? MySQL creates a clustered index automatically when I create a primary key.

But is there a situation where MySQL creates a nonclustered index automatically?

The PRIMARY KEY is a UNIQUE key; it is also "clustered". (Note: Clustered)
In the absence of an explicit PK, the 'first' UNIQUE key containing only non-NULL column(s) is effectively promoted to be the PK.
If that fails, a hidden 6-byte number is used for the PK.
A UNIQUE key is an INDEX, plus a uniqueness constraint. (Note: may be clustered)
An INDEX is a BTree that is used for efficient access.
A FOREIGN key creates an INDEX if there is not already a suitable index. (Note: Non-clustered)

The 3 "Notes" are implicitly-created indexes, but not necessarily non-clustered.


How to give SQL Server Job Owner permission to Proxy a Credential I've created a Credential Cred1 (DOMAIN\USER1 with the secret = domain password)
I've created a SQL Agent job with owner to NT AUTHORITY\SYSTEM
The job needs to proxy as credential Cred1

So when I run the job it fails with the message that NT AUTHORITY\SYSTEM wasn't allowed to proxy 1 (I only have one credential) for CmdExec.

  "Unable to start the execution of step1 (reason: JobOwner NT
  AUTHORITY\SYStem doesn't have permissions to use proxy 1"

I've done a bit of google search for the permissions needed, and I read that I need to give operator access to the NT AUTHORITY\SYSTEM login in the MSDB. I've tried this but still no luck.

Expand SQL Agent
Expand Proxies
Find your proxy, right-click on it and choose Properties
Go to the Principals tab
Add the NT AUTHORITY\SYSTEM account

This will give that account permission to use the proxy.


How does Postgres 9.2's streaming replication handle schema changes, and initial table setup? I want to set up a backup database on the physical server which sits right under my primary physical database server. I'm using Postgres 9.2, and I want to use synchronous, streaming replication (for atomicity), but I'm completely unaware of how things like, A) initial table setup is transferred from server 1 to server 2 (e.g., I run syncdb from my Django app, which creates a series of tables on server 1), and B) how continued schema changes are carried from server 1 to server 2 (e.g., I run a Django South migration, which sends ALTER TABLE queries, as well as adding/removing indexes, etc.). Are these things handled transparently with streaming replication, or is there something I have to do to affect this change across both servers?

If you intend to use PostgreSQL's built-in streaming replication, then you seed the replica with a base backup - a file-system level copy of the original database. So it already has all the tables and data from the master. pg_basebackup is usually used for this.

Streaming replication reads changes at the block level from the write-ahead logs, so the databases are kept physically the same. Since DDL is, just like DML, recorded in the WAL, DDL and DML are both transparently replicated.

See the documentation on HA and replication in PostgreSQL for more information.


Restarting SQL Server's services remotely programmatically I have the need to restart Microsoft SQL Server's group of services on a given computer, remotely, through some sort of programmatic method I can insert into a workflow. I'm looking for the best way to do this, I've seen a few different ways and I'm wondering if there's anything I'm missing:

Use Net Stop and Net Start - This would work if I only needed to restart the SQL Service and there were no dependencies, not really an option if SQL Server Agent is running.
Use SC //<computername This would allow me some access into the servicecontroller so I could query whether or not there's a dependent service running.
Use the .NET Servicecontroller method, which would let me build an array of services running on the target computer and then iterate through them to stop what I need to stop.

Am I missing any ways?

Please see http://technet.microsoft.com/en-us/library/hh403394.aspx - you missed for example PowerShell.

Pay extra attention if you're using a clustered instance.


Database file location change on standby site after restore standby database I am configuring Log shipping from SQL Server 2008R2 to 2016 using NORECOVERY option with Log Shopping jobs. I have successfully configured Backup, Copy and Restore jobs.

When I perform a restore on the standby database, then its Database file location changes on standby site.

I was creating a standby database at D:\<FOLDER>, but after restoring the database on 2016 using Restore -> Database, the location changed to
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA.

There are 2 ways you can fix this.

As @ramakant mentioned, SQL Server is using the default paths setup on the server. To change this, right click on the server in Object Explorer>Properties>Database Settings>Database Default Locations. Changing these values will automatically restore all databases to those directories unless different paths are specified.
You need to restore the database via a script, and that way you can specify where the files will be.
That will restore a database from a local location to the D: drive. Alter the script as required with regards to the locations.

RESTORE DATABASE [MyDatabase] FROM
DISK = N'C:\Work\MyDatabase.bak' WITH  FILE = 1,
MOVE N'MyDatabase' TO N'C:\MSSQL\Data\MyDatabase.mdf',
MOVE N'MyDatabase_log' TO N'C:\MSSQL\Log\MyDatabase_log.ldf',
NOUNLOAD,  STATS = 5
GO

More Info : RESTORE Statements (Transact-SQL)

Either one of these methods will work for you.


SQL Server (2012) non-blocking DDL? I'm not very experienced with SQL Server, so maybe I'm missing something

My situation is as follows:

Session 1 runs a CREATE TABLE (or other CREATE statements) with autocommit off and the CREATE is not being comitted.
Session 2 runs a sp_table statement but hangs as long as session 1 is not committing the DDL

The scenario where this happens is developers working on the database. Some of them browsing the tables, some of them doing DDL. If one user forgets to commit the DDL all other sessions that want to list the tables are blocked. Note that the sp_tables is issued e.g. by the SQL client (through the JDBC driver API), so it's not something that can be changed.

The database I'm working with has snapshot_isolation enabled and the isolation level is set to read committed (SET ALLOW_SNAPSHOT_ISOLATION ON and SET READ_COMMITTED_SNAPSHOT ON)

My assumption was that these settings should make SQL Server behave better with regards to locking in concurrent sessions (e.g. like PostgreSQL and Oracle where SELECTs are never blocked by any writer) - but apparently this is not the case.

So, is there any way to make SQL Server more friendly against concurrent read/write situations with regards to DDL? (apart from submitting DDL only in auto-commit mode).

No there's no way of configuring SQL Server to do what you want to do.

Under snapshot isolation the call to sp_tables gets blocked waiting for a shared key lock on one of the system base tables (sysschobjs) when doing a SELECT from sys.all_objects

The Using Row Versioning-based Isolation Levels topic in BOL does say:

  SQL Server does not keep multiple versions of system metadata. Data
  definition language (DDL) statements on tables and other database
  objects (indexes, views, data types, stored procedures, and common
  language runtime functions) change metadata.

Even under TRANSACTION ISOLATION LEVEL READ UNCOMMITTED the call to sp_tables ends up blocking however, whilst the straight forward SELECT ... FROM sys.all_objects is no longer blocked the same query references the HAS_PERMS_BY_NAME function in the WHERE clause. This appears to start a system transaction (CMetadataAccessor::CMetadataAcce) at a higher isolation level and ends up getting blocked waiting for a shared key lock on sysschobjs again.


Help with SQL Server and XML columns I am writing a query to run against the SCOM 2012 OperationsManager database. SQL instance is Microsoft SQL Server 2012 - 11.0.5058.0 (X64).

Within the database is a table called Monitors. The schema of the DB can be found here: Link to Schema

I can run the following query:

select monitorid, configurationxml
from dbo.monitor
where monitorid = '4960E39A-59C8-A2C2-99B1-59B73D73156F'

and it returns this:

monitorid                               configurationxml
4960E39A-59C8-A2C2-99B1-59B73D73156F    <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName><DiskLabel>$Target/Property[Type="Windows!Microsoft.Windows.LogicalDevice"]/DeviceID$</DiskLabel><CounterName>PercentFree</CounterName><IntervalSeconds>900</IntervalSeconds><NumSamples>4</NumSamples><SystemDriveWarningThreshold>10</SystemDriveWarningThreshold><SystemDriveErrorThreshold>5</SystemDriveErrorThreshold><NonSystemDriveWarningThreshold>10</NonSystemDriveWarningThreshold><NonSystemDriveErrorThreshold>5</NonSystemDriveErrorThreshold>

(so the Monitor GUID and XML in the column configurationxml

I can then run this query:

Select
    D.C.value('IntervalSeconds[1]','varchar(4000)') CheckInterval,
    D.C.value('NumSamples[1]','varchar(4000)') SamplesBeforeDown,
    D.C.value('SystemDriveWarningThreshold[1]','varchar(4000)') SystemDriveWarningThreshold,
    D.C.value('CounterName[1]','varchar(4000)') Countertype
    FROM (Select n.c.query('.') as xmlquery
    from
     (Select cast(ConfigurationXML as xml) Recxml
     FROM [dbo].[monitor] mt)
    a Cross Apply Recxml.nodes('/') N(C)) r
    Cross Apply xmlquery.nodes('/') D(C)

which shreds (not sure if that is the right term) the XML and allows me to retrieve specific element values against that Column:

CheckInterval   SamplesBeforeDown   SystemDriveWarningThreshold Countertype

          900                   4                            10 PercentFree

The problem I have is that I would like to be able to pull specific element values linked to the GUID in that specific row so that I can then have the GUID and the xpecific XML element values that I want, something that would look like this:

monitorid                            CheckInterval  SamplesBeforeDown   SystemDriveWarningThreshold Countertype

4960E39A-59C8-A2C2-99B1-59B73D73156F           900                  4                            10 PercentFree

The main problem I am encountering is that when I shred the XML it loses its reference to the row it came from.

How can I link the XML content to the row it is coming from?

Try this:

SELECT
    x.MonitorId,
    x.configXml.value('(IntervalSeconds/text())[1]','INT') CheckInterval,
    x.configXml.value('(NumSamples/text())[1]','INT') SamplesBeforeDown,
    x.configXml.value('(SystemDriveWarningThreshold/text())[1]','INT') SystemDriveWarningThreshold,
    x.configXml.value('(CounterName/text())[1]','VARCHAR(100)') Countertype
FROM
    (
    SELECT
        MonitorId,
        CAST( ConfigurationXML AS XML ) AS configXml
    FROM [dbo].[monitor] mt
    WHERE MonitorId = '4960E39A-59C8-A2C2-99B1-59B73D73156F'
    ) x

Note I have changed a couple of things about your code: 1) always use the text() accessor for better performance against untyped XML and 2) I've specified better datatypes.

Let me know if that works for you.


Primary key guarantees: duplicates and nullity Oracle allows primary keys to have duplicate and null values.
Using this capability isn't a particularly good idea, but it implies that some of what most developers consider a primary key's guarantees (non-null, unique) are not really guarantees.

CREATE TABLE oracle_guarantees (
    ID NUMBER(9,0),
    NAME VARCHAR2(50 BYTE),
    breed NVARCHAR2(100)
);

INSERT INTO oracle_guarantees VALUES (1, 'Fuzz Head', 'Tabby');
INSERT INTO oracle_guarantees VALUES (2, 'Fluffy Thing', 'Mix');
INSERT INTO oracle_guarantees VALUES (2, 'Fluffy Thing', 'Mix');
INSERT INTO oracle_guarantees VALUES (3, 'Tiger', 'Tabby');
INSERT INTO oracle_guarantees VALUES (4, 'Fur Beast', 'Bengal');
INSERT INTO oracle_guarantees VALUES (5, 'Karate', 'Japanese Bobtail');
INSERT INTO oracle_guarantees VALUES (6, 'Chairman Meow', 'Chinese Harlequin');
INSERT INTO oracle_guarantees VALUES (NULL, 'No Cat', 'No breed');

CREATE INDEX oracle_guarantees_pk ON oracle_guarantees (ID);
ALTER TABLE oracle_guarantees ADD CONSTRAINT oracle_guarantees_pk PRIMARY KEY (ID) DISABLE KEEP INDEX;
ALTER TABLE oracle_guarantees MODIFY CONSTRAINT oracle_guarantees_pk ENABLE NOVALIDATE;

SQLite PKs also allow one null but not duplicate values.

Is it possible to have constraints marked as primary keys which have duplicate values or null values in Microsoft SQL or Postgres?

In other words, can I absolutely rely on PK uniqueness and non-nullity within the documented feature set (ignoring cases like manually edited data files, bugs, or modified server source code)?

Further clarification:
I suspect that DBAs and developers work a little more differently than I thought.

Thought exercise:
A developer needs to uniquely identify rows on any table (the table structure is unknown at compile time).
Oracle's documentation on primary key constraints comes up in a search and says:

  A primary key constraint combines a NOT NULL constraint and a unique
  constraint in a single declaration. That is, it prohibits multiple
  rows from having the same value in the same column or combination of
  columns and prohibits values from being null.

The developer then looks for a means to find the primary keys on any table at run time. They probably come across a question like this: https://stackoverflow.com/questions/9016578/how-to-get-primary-key-column-in-oracle

The top voted answer's query yields:

I believe that any reasonable non-DBA person would, at this point, conclude that the ID column cannot have NULL or duplicate values. This conclusion is not true.

Is it Oracle's fault? No.
Is the table properly designed? No.
Are the statements to create such a table complex? Immaterial.
Is the Id column a "real" primary key? Maybe not, but this is more a philosophical matter. ID is listed (and is shown as
"ENABLED") by the query in the top-rated SO answer to the linked question above. Perhaps that question needs to add a check for validation, but I have never once seen anyone check for this in code, nor do any answers in that thread or related threads I have found.

Ergo, in the real world, an arbitrary table can have a primary key (by definition of all_constraints.constraint_type) which has duplicate values. And NULL values.

I now know that software must also ensure that the constraint is validated. Excellent! That provides the guarantee I need.

The question is: Is it possible to have a primary key (as defined by DBMS metadata) that has NULL or duplicate values in PostgreSQL or Microsoft SQL?

Oracle allows primary keys to have duplicate and null values.

Not really. What you have managed to create - after a series of complicated statements - is an enabled but not validated constraint. Which means that Oracle will check inserts and updates (for uniqueness) but there may be left existing duplicates. So it is a PK only in name. It's a not-validated constraint so not really a PK.

In other words, can I absolutely rely on PK uniqueness and non-nullity within the documented feature set (ignoring cases like manually edited data files, bugs, or modified server source code)?
More context: I am working on an application that displays records and allows a user to delete them on any arbitrary table. When dealing with an arbitrary table, I need to use metadata and other means to determine which guarantees I have. Much database development is about guarantees -- guarantees that a large transaction will commit or not (but not partially commit). Guarantees that a successfully modified row stays modified. And guarantees that a primary key uniquely refers to exactly one row.

Yes, you can rely but only if your application reads the metadata and all the details - which may differ from DBMS to DBMS.

SQL Server allows for disabled constraints? The application has to consider this when reading and interpreting the metadata tables.

Oracle allows for disabled or non-validated constraints? The application has to consider those options too, accordingly.

Postgres allows for some different weird scenarios? It has to consider them, too.

Another possible option - which may or may not be an option for you - is if the application is the only application that creates, deletes and modifies database objects or if all applications that do so are under your or a single control. Then it can rely on the soundness of the metadata ("consider only unique constraints") without checking for these details / rare cases - because it can rely that no such rare case is ever created in the first place.
With your conclusions:

Is it Oracle's fault? No.

I agree. I don't know why this was allowed but it probably solves some problem. And it probably was meant to be used only temporarily - e.g. during an import from another source to a database.

Is the table properly designed? No.
Are the statements to create such a table complex? Immaterial.

I agree, too, on both.

Is the Id column a "real" primary key? Maybe not, but this is more a philosophical matter. ID is listed (and is shown as "ENABLED") by the query in the top-rated SO answer to the linked question above. Perhaps that question needs to add a check for validation, but I have never once seen anyone check for this in code, nor do any answers in that thread or related threads I have found.

We come to the same conclusion: needs to add a check for validation.

As for what other possibilities exist in SQL Server and Postgres:

SQL Server:

PRIMARY KEY and UNIQUE constraints cannot be disabled.
Unique indexes can be disabled. They can also be re-enabled without check which would result in a situation very similar to the "enabled non-validated" in Oracle.
FOREIGN KEY and CHECK constraints can be disabled. They can be re-enabled without check, too.
See ALTER TABLE and Disable indexes and constraints for details. My answer in this question: What is a WITH CHECK CHECK CONSTRAINT? explains options and the syntax, which differs from Oracle's.

PostgreSQL:

PRIMARY KEY, UNIQUE and EXCLUDE constraints cannot be disabled.
FOREIGN KEY and CHECK constraints cannot be disabled. They can be created with the NOT VALID option though (which means that they are enabled without checking existing rows, as in SQL Server). See ALTER TABLE for details.


MySQL Read-only Replication I've multiple linux servers which runs MySQL server and my applicaiton each.
I have a cron job which creates daily backups and uploads them to another server. Daily backups are OK but, i want to be able to recover the current state of the database in case of the server goes down or whatever happens.
Can I create a single "read only async replication server" and configure all other servers to create replications to that server?
OR, what is the right way to do that?

It depends on the version.
Look for "multi-source" replication.  This is where multiple Primaries can replicate to a single Replica.  But this does not say how you would restore a single Primary that dies.
Consider using a single Primary for everything, then have one (or more) Replicas.
Or you could have multiple Primary-Replica pairs sitting in VMs or Dockers.  The pair would, of course, have its parts on separate machines, preferably geographically separated (think storms, earthquakes, etc).


SqlServer Database replication in remote datacenter. Which kind of replication? Disclaimer: I am a developer, not a DBA.
Scenario
Currently two servers (Web Edition) in a private cloud in one single datacenter in Europe.
1 DB per client + 1 common DB used by all clients.
We are extending to North America. We need to host NA customers in a NA datacenter.
Requirement: Replicate the common DB to the NA datacenter.
The size of the DB is currently 3-4GB. The bulk of the load is in a time series table, growing at around 15000 rows a day (~2MB) (+ other modifications in other tables, less volume).
What replication scenario would you recommend ?
I don't need immediate replication/mirroring. There can be some delay, although not too long (e.g. 1 minute seems reasonable (rule of thumb). If options include longer delay, this can be discussed). Allowing extra subscriber nodes would be nice to have.
From what I read on the Web, I believe snapshot replication is not a good idea for replication across long distances. Transactional and peer-to-peer replication look interesting. What would work best for me?
TIA

Ok, if you need to replicate data from Europe (SQL Server 2016 Web Edition) to America, lets see what options we have:
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2016
Below features do NOT fit for you:

Database Mirroring (in Web Edition SQL Server can be only in Witness role, it can not be a primary or secondary)
Availability Groups / Failover Cluster Instances (mostly Enterprise / Standard Edition level features)
Transactional / Snapshot Replication  (in Web Edition SQL Server can be only in Subscriber role, can't be a Publisher or Distributor)

You are left only with Log Shipping option, it works in Web Edition
Log Shipping has configurable delay between log backups on primary and log restores on secondary