texts: How can I efficiently search for the most recent rows where a column matches any result from a different query?

texts:
I have the following schema:

CREATE TABLE users (
    id   BIGSERIAL PRIMARY KEY,
    name TEXT      NOT NULL UNIQUE
);

CREATE TABLE friends (
    user_id        BIGINT NOT NULL REFERENCES users,
    friend_user_id BIGINT NOT NULL REFERENCES users,
    UNIQUE (user_id, friend_user_id)
);

CREATE TABLE posts (
    id      BIGSERIAL PRIMARY KEY,
    user_id BIGINT    NOT NULL REFERENCES users,
    content TEXT      NOT NULL
);
CREATE INDEX posts_user_id_id_index ON posts(user_id, id);

Each user can unilaterally follow any number of friends. The posts table has a large number of rows and is rapidly growing.

My goal is to retrieve the 10 most recent posts of a user's friends. This query gives the correct result, but is inefficient:

SELECT posts.id, users.name, posts.content
FROM posts JOIN users ON posts.user_id = users.id
WHERE posts.user_id IN (SELECT friend_user_id FROM friends WHERE user_id = 1)
ORDER BY posts.id DESC LIMIT 10;

If the user's friends have recently posted, the query is still reasonably fast (query plan). But if the user's friends haven't recently posted or the user has no friends, it quickly deteriorates (query plan).

If I match only a single post author (e.g. WHERE posts.user_id = 5), Postgres uses the index posts_user_id_id_index. But if I use IN, the index doesn't appear to be used at all.

How can I get these results more efficiently?

I've uploaded the schema and the queries I've tried to dbfiddle. The output of SELECT version() is PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit for me.


texts: MySQL Query Failed: Expression #6 of SELECT list is not in GROUP BY clause and contains non aggregated column fixed bug reappear every restart

texts:
MySQL Query Failed: Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column

This bug is fixed using below query. by logging into MySQL console as root user

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

But the problem is that this bug reappears every time i restarts the system.
How to fix this?


texts: How do I use a dynamic command with copy from program?

texts:
I'm trying to read a JSON API with the Postgres copy from program command.

When I call

copy _temp from program 'curl -uri "https://url"';

it works fine but the API has paging and I need to loop through multiple calls.

When I call it like this:

_command := 'curl -uri "https://url?&page='||(10::text)||"';
copy _temp from program _command;`

I get

ERROR:  syntax error at or near "_command"

You can't even concatenate in place like

copy _temp from program 'curl -uri "https://url?&page='||(10::text)||'"';

Percent parametric replacement in the raise notice style doesn't work either.

What gives? program is a literal string with single quotes so what is the difference between specifying a literal string and using a text or varchar variable? There doesn't seem to be any program data type (::program cast does nothing), what am I missing?

In the docs it says 'it is best to use a fixed command string' not that you can only use a fixed command string...

How do I use a dynamic command string?


texts: SQL query to find all parent records where child records are a specific set

texts:
An Item has many ItemDetails. An ItemDetail has fields of "type", "value" and "item_id".

I need to find all Items if and only if item has exact ItemDetails which are restricted by some changeable conditions.
For example I need to find all Items with ItemDetails of (type=10, value=1000) and (type=20 and value=2000)

My first solution was like this:

select p.*
from item p
where not exists
    (
      select c.id from item_detail c
      where c.item_id=p.id
      and (c.type<>10 or c.value<>1000)
      and (c.type<>20 or c.value<>2000)
    );
-- Execution Time: 17.819 ms

But I realised that it was fetching items with only one ItemDetail(type=10, value=1000).
Then I found this question and changed query like below.

select p.*
from item p
where not exists
    (
      select c.id from item_detail c
      where c.item_id=p.id
      and (c.type<>10 or c.value<>1000)
      and (c.type<>20 or c.value<>2000)
    )
and 2 = (
  select count(c.item_id) from item_detail c
  where c.item_id=p.id);
-- Execution Time: 2426.596 ms

But second subquery is causing performance problem. Execution time for first query is 5 ms, but for second it is 800 ms.
Is there a better way of doing this?

I am using PostgreSQL 9.5.

Here is the fiddle for it.

Edit-1: Here is fiddle example for those who cannot reach it:

CREATE TABLE public.item
(
  id integer NOT NULL,
  name  character varying(10) NOT NULL,
  CONSTRAINT item_pkey PRIMARY KEY (id)
);

CREATE TABLE public.item_detail
(
  id bigint NOT NULL,
  item_id integer NOT NULL,
  type  integer NOT NULL,
  value integer NOT NULL,
  CONSTRAINT item_detail_pkey PRIMARY KEY (id),
  CONSTRAINT fk_item_id FOREIGN KEY (item_id)
      REFERENCES item (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uq_item_type_value UNIQUE (item_id, type, value)
);

INSERT INTO public.item VALUES (1, 'Item1'),(2, 'Item2'),(3, 'Item3'),(4, 'Item4'),(5, 'Item5');

INSERT INTO public.item_detail
VALUES
  (1,1,10,1000),
  (2,1,20,2000),
  (3,2,10,1000),
  (4,3,10,1000),
  (5,3,20,2000),
  (6,3,30,3000),
  (7,4,10,1000),
  (8,4,10,1500);

Edit-2: I came up with a solution like this by choosing the best option for me from this source given by ypercubeᵀᴹ.

SELECT p.* FROM item p
WHERE EXISTS (SELECT item_id FROM item_detail
                WHERE item_id = p.id AND (type, value) = (10, 1000))
AND   EXISTS (SELECT item_id FROM item_detail
                WHERE item_id = p.id AND (type, value) = (20, 2000))
AND NOT EXISTS (SELECT item_id FROM item_detail
                WHERE item_id = p.id AND (type, value) NOT IN ((10, 1000), (20,2000)));
-- Execution Time: 0.984 ms


texts: Why SSRS use Share point server for deployment of reports?

texts:
I switched from Oracle platform to Microsoft SSRS.

Currently I am working on SSRS.
What is the use of Share point server for deployment of SSRS reports ?


texts: How to join multiple results into one row with dynamic columns?

texts:
Foreword - I don't know how to formally describe the situation in a single question so the below is as verbose as I can make it...

Taking wordpress as an example, there's the "wp_users" table which has all the standard things like email and login and then there's the "wp_usermeta" table which looks like the following:

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| umeta_id   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

This table has many rows for each user with the meta_key and meta_value columns being the important bits of actual data.

What I'd like to do is query for all users and all meta, but only returning one row per user with every meta_key/meta_value as additional columns.

As an example scenario: there are three key/value pairs in the meta table, associated with the user_id 42. I'd like to query for the following result:

user_login,user_email,mk1,mk2,mk3
userone,one@example,"one","two","three"
usertwo,two@example,"yes","wow","moars"

Where "mk1", "mk2" and "mk3" are the values of the "meta_key" and the "one", "two", "three" are the corresponding "meta_value"s.

Can this be done in pure SQL (no procedures or code)?


texts: verify barman backed up files after failure

texts:
I have a barman that get backups from a postgres installed on another server. its config is:

    active: True
    archive_mode: on
    archived_count: 47588
    archiver: True
    archiver_batch_size: 0
    backup_method: rsync
    backup_options: BackupOptions(['exclusive_backup'])
    bandwidth_limit: None
    check_timeout: 30
    compression: gzip
    custom_compression_filter: None
    custom_decompression_filter: None
    disabled: False
    failed_count: 0
    immediate_checkpoint: True
    is_archiving: True
    is_superuser: True
    last_archived_time: 2018-11-17 14:49:58.810545+03:30
    last_archived_wal: 0000000100002ACC0000003B
    last_backup_maximum_age: 7 days (latest backup: 6 hours, 3 minutes, 11 seconds )
    last_failed_time: None
    last_failed_wal: None
    max_incoming_wals_queue: None
    minimum_redundancy: 1
    msg_list: []
    network_compression: True
    parallel_jobs: 15
    path_prefix: None
    pgespresso_installed: False
    post_archive_retry_script: None
    post_archive_script: None
    post_backup_retry_script: None
    post_backup_script: None
    pre_archive_retry_script: None
    pre_archive_script: None
    pre_backup_retry_script: None
    pre_backup_script: None
    recovery_options: RecoveryOptions([])
    replication_slot: None
    replication_slot_support: True
    retention_policy: REDUNDANCY 2
    retention_policy_mode: auto
    reuse_backup: None
...

about some days ago we had some problem on postgres and barman failed to back up. now as I list backup logs I see this:

20181117T011505 - Sat Nov 17 08:46:53 2018 - Size: 2.2 TiB - WAL Size: 9.8 GiB (tablespaces: ...)
20181116T011506 - Fri Nov 16 08:48:28 2018 - Size: 2.2 TiB - WAL Size: 38.3 GiB (tablespaces: ...)
20181114T011504 - FAILED

I checked my postgres tablespace and its size is exactly 2.2TB.
Now the questions are:

are backups correct?
did I lost data due to downtime?


texts: sql script execution time differences between host and virtual machine

texts:
In my organization developers are using "docker" technology to develop & test their applications. developer's machines are mostly linux-mint with different specs on each machine. Every time the docker is activated, after the mysql-server container is up & running - an SQL script is then executed to create the schemas (an output of mysqldump) in the following way:
mysql -h localhost -u root --comments < base_schema.sql

I've received complaints that the script execution takes around 25 seconds, which is intolerable.
When I execute the same script on a database on my local machine
It does take between 25 to 35 seconds.
My machine's specs:

Windows 7 64bit SP1
i7-5600 CPU
16GB RAM
mysql  Ver 14.14 Distrib 5.6.28, for Win64 (x86_64)

On the other hand when i execute the script on a virtual machine (on my local machine), the same script takes about 5-6 seconds. the VM specs:

VirtualBox Version 5.0.20 r106931
Ubuntu 14.04.4 LTS
2 CPU's
11,136 MB RAM (~11GB)
mysql  Ver 14.14 Distrib 5.6.31, for Linux (x86_64) using  EditLine wrapper

Furthermore, i created a simple sql script mySchema.sql:
drop database if exists myschema;

create database myschema;

create table myschema.tab_1 (x int);
create table myschema.tab_2 (x int);
create table myschema.tab_3 (x int);
create table myschema.tab_4 (x int);
create table myschema.tab_5 (x int);
...
...
...
create table myschema.tab_100 (x int);

one Bash script mySchema.sh:
SECONDS=0
mysql -h localhost -u dbuser --comments < mySchema.sql
duration=$SECONDS;
echo "ran for $(($duration * 1000)) ms"

And one batch script mySchema.bat:
@echo off

set t0=%time: =0%

mysql -h localhost -uroot --comments < mySchema.sql

set t=%time: =0%

set /a h=1%t0:~0,2%-100
set /a m=1%t0:~3,2%-100
set /a s=1%t0:~6,2%-100
set /a c=1%t0:~9,2%-100
set /a starttime = %h% * 360000 + %m% * 6000 + 100 * %s% + %c%

set /a h=1%t:~0,2%-100
set /a m=1%t:~3,2%-100
set /a s=1%t:~6,2%-100
set /a c=1%t:~9,2%-100
set /a endtime = %h% * 360000 + %m% * 6000 + 100 * %s% + %c%

set /a runtime = %endtime% - %starttime%
set runtime = %s%.%c%

echo Started at %t0%
echo Ran for %runtime%0 ms

and the results:
Windows:
C:\Users\LiorAN\Desktop\vm>mySchema.bat
Started at 16:08:12.65
Ran for 4730 ms

C:\Users\LiorAN\Desktop\vm>mySchema.bat
Started at 16:08:18.65
Ran for 4710 ms

C:\Users\LiorAN\Desktop\vm>mySchema.bat
Started at 16:08:55.16
Ran for 4920 ms

Ubuntu:
lior@lior-VirtualBox:/media/windows-share$ ./mySchema.sh
ran for 1000 ms
lior@lior-VirtualBox:/media/windows-share$ ./mySchema.sh
ran for 1000 ms
lior@lior-VirtualBox:/media/windows-share$ ./mySchema.sh
ran for 1000 ms

when i changed mySchema.sql to create 1000 tables instead of 100-
on windows execution times ranged from 48-60 seconds,
while on the vm execution times ranged from 11-14 seconds.
QUESTIONS:

what could cause the difference in executions times of the same process on the 2 platforms? I already tried to copy the server parameters from windows to the VM and the results were the same, and i doubt that the difference between 5.6.28 and 5.6.31 would be the cause.

can anyone find a better suggestion to look into this problem ?
I didn't mention but my first step was to anaylze the script itself, but i couldn't find any performance issues in it.

thanks alot,
Lior.


texts: SSMS Client Error: Internal connection fatal error

texts:
I am running SSMS 11.0.5646.0 on a Windows 7.0 Pro desktop connected by UTP directly to same network switch as my development server (Windows 2008R2 Standard, SQL 2008R2 SE with SP3). All within the same domain and same sub-net.

I receive the same error as in this question:

  An error occurred while executing batch. Error message is: Internal connection fatal error.

I can run a query successfully 3 times in a row and then have it fail 5 times in a row. No changes to code or parameters. Interesting that I often find results in the result tab even though the error is on the message tab.

Now, when I try to re-run the query (still unmodified) I receive a modal pop-up that says:

  Previous execution is not yet complete. (SQLEditors)

However, sp_WhoIsActive does not show the SPID and sp_Who2 shows it as sleeping.

This seems like an internal SSMS GUI error. I have found MANY posts, questions, etc. about similar issues going back to SQL 2005.  Does anyone have any idea how to troubleshoot these issues?  They are clearly SSMS client side.

Additional links:

SSMS : bad client-side errors (reported by Aaron Bertrand)
https://support.microsoft.com/en-us/kb/945892
https://support.microsoft.com/en-us/kb/949105
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101012


texts: My PLE Counter value is 483.what is the safe side for PLE buffer manager value?

texts:
Today morning i ran this scripts and i got 483.is that counter value is good ?

select [OBJECT_NAME],
[counter_name],
[cntr_value]from sys.dm_os_performance_counters
where[OBJECT_NAME]like '%Manager%'
AND [counter_name]='page life expectancy'


texts: Inhibit Sql Server Log Shipping Alerts during certain times

texts:
There are batch jobs that occur in the middle of the night, which cause log shipping backups to be delayed such that they exceed the alert threshold, and alerts are then emailed.

How can I disable these alerts during this period only?

I don't want to reduce the alerting threshold for the entire day.

Thanks.


texts: All queries on the server have slowed down, cpu is normal, plenty of free memory but SQL is using very little

texts:
The other day I logged onto our server to see SQL was using 100% cpu and the server was slowed to a crawl. I decided to stop SQL and restart it. However, it refused to stop properly and stopped responding. After playing around with it eventually I got it restarted and running again.

However, all queries against it are now going 10-30 times slower, and in some cases affect the performance of the server.

I've been googling for solutions but I can't see anything addressing a global issue with SQL running slow. The CPU usage isn't abnormal and there is plenty of free ram - in fact, SQL is using only about 300mb whereas previously it used to be around 20gb, which may be part of the issue.

I created a brand new database and when I attempt to populate it, it goes about 30 times slower than it used to, and the server becomes very unresponsive.

Could it be something simple that needs resetting or a repair that needs doing after it broke before?


texts:
I can't seem to find any information on this anywhere. I found a question here called Monitor MySQL activity per database? but that's not quite the answer either.

I'd like to be able to log the following to a system log somewhere, preferably via syslog:

User (not master/slave) login
User logout
User connect time
User IP

Something similar to last and lastb for MySQL. Can this be done? Is anything already written and in the Ubuntu and Red Hat Enterprise Linux repositories? Can MaatKit do this?

Thanks!


texts: UTF8 vs ASCII or custom binary format: high-performance optimization on very big tables

texts:
The summary of my question is whether there are advantages, even if minimal, to use ASCII, or even a smaller format designed yet created specifically for the situation, instead of UTF-8 for strings.

Is possible to use webserver who will access the data translate one ASCII string to a more compact format.

On this question, just 1-3 tables on database handle a massive amount of data, so any byte can make difference if could fit on memory instead of access disk. The information will be accessed via a RESTfull service

Read vs Writes:

Project need much more read than writes. But writing has a peculiarity: every 10 seconds an amount between 40-300 new rows are inserted into the main table. These could be written in parallel, since do not depend of each other.

Memory vs Disk usage:

Recent inserted rows, that will be used immediately, will be inserted also on a cache for the webservice use, so no need to read they again. But for search on old records, the database will need, and it should be fast.

This is why I suppose that use less bytes to store some fields will make diference: even for larger amount of data, will be more easy for fit on memory.

If I cannot fit data on memory, and database cannot abstract to me some speed, or I will need to force slow table/partition scans each 10 seconds for just one user, or I will be forced to do single select and cache it on Webserver, but this break concept of "stateless" from REST concept.

Characters that must be supported

0-9,A-Z, "-", "_". Maybe will need "a-z". Just 38 or 64 caracters, and never more than this.

For now, most columns are

CHAR(3), CHAR(6), VARCHAR(8), VARCHAR(10).

Examples:

ABC, XYZ
AB-EFG, XY-XPT
ABC123457
E47F6C, 34210A, E48D37 (hex strings, maybe exist a format specific to this?)

Technologies used

Database will be MariaDB. Maybe part of RAW data will be on some NoSQL database. The language of webservice does not really makes diference here, but will be PHP 5.4 with framework Phalcon PHP.

Different types of cache could be used, from Varnish-cache to APC/Mencached until caches inside database.

My initial hypothesis:

One field with VARCHAR(10) needs 80bits. If I use one custom table to use just 64 or less instead 255 characters, I save around 4 times, so instead of 80bits will use just 20bits.


texts: GCP Cloudsql Mysql Replica unresponsive after mass delete on Master

texts:
Directed here from S/O
We have a Master/Replica configuration for Mysql innodb(5.7.32) databases in Cloud SQL... We have a single Table (let's call it Master table) partitioned on Two keys having both primary and non-clustered indexes... It's a row-based replication with automatic disk increase on both instances... It's not a HA configuration so it's not a failover replica...
What we're trying to do... We're trying to purge the master table back to N number of days... This is done for multiple reasons so let's say this is a client requirement...
What's the issue... Whenever we're purging the master table it just stalls the replica, it deletes a certain number of rows on the replica and then just passes out... The number of records in a single purge is around 5 million rows... The time the purge starts on the master, the lag starts... It's a totally repeatable issue... we know it's caused because it's a row-based, sequential replication so
What we've tried so far...

Increasing the size of the replica, we've given it 104 GB RAM but the lag doesn't go...
Restarting replica
RESET SLAVE
Trying enabling parallel replication https://cloud.google.com/sql/docs/mysql/replication/manage-replicas#configuring-parallel-replication... every single time I tried this it failed with an 'Unknown error occurred'...
Trying setting it to a Statement-based replication by the SET binlog_format="STATEMENT" command but the "root" user doesn't have the privilege and gets an 'access denied' error...

Now the question... what am I missing in my:

explanation
mysql configuration
method

Thanks


texts: Use of HAVING without GROUP BY in SQL queries

texts:
In order to use HAVING in SQL queries , must there be a GROUP BY to aggregate the column names?

Are there any special cases where it is possible to use HAVING without a GROUP BY in SQL queries?

Must they co-exist at the same time?


texts: What should the schema look like for an API-based SaaS product?

texts:
I'm building a developer tool product, which will be accessible solely by APIs. Following are a few features I've identified that will be needed:

Issuing/refreshing API keys
Purchasing API credits
Subscriptions for API credits
Monitoring usage of API for each user

What should the SCHEMA look like? Are there any open examples of schemas for such a product? Is there a term for this?
Note that this would be quite similar to what something like Stripe or any API-based SaaS is doing. Just looking for a good schema example only. Any ideas?


texts: Stuck task in management studio, how do you handle them?

texts:
After clicking on "Take database offline" in management studio this message stays hang and won't close if you click on close.

What's a good way to deal with stuck jobs like these in management studio ? Can you kill them via the activity monitor ? Should I seek what process is stopping this job from going through and terminate it ?


texts: PHP MySQLi Lock Tables Query Sometimes Does Not Complete

texts:
I am attempting to lock some tables while running some code and unlocking the tables when I am done.

Process:

Run Lock Tables MySQL Query.
Run some PHP Code.
Run Unlock Tables MySQL Query.

While running this process, 9 times out of 10 everything runs perfectly. Sometimes, when I run my query no response comes back from MySQL and PHP just waits for a response. Since no response happens, I never get to steps 2 or 3 and the table stays locked indefinitely. I am running the exact same Lock Tables Query in every single attempt.

After some research, I found that the problem happens when a Second Server writes to the database and then the First Server tries to do a "LOCK TABLES" query. It appears that the Second Server's write does a "metadata lock". I believe this is due to MySQL having the "autocommit" feature enabled and the transaction is not yet completed. So when I attempt to lock the tables, MySQL never responds and the "show processlist;" has "Waiting for table metadata lock | LOCK TABLES ..." listed in it until I manually kill the process.

I am locking Numerous tables. Below is a similar example to my actual table query. I am using the same table multiple times with different aliases based on the queries I am attempting to prevent from accessing the tables.

$sql = "LOCK TABLES table1 as t1 WRITE
                    , table2 as t2 WRITE
                    , table3 WRITE
                    , table2 WRITE
                    , table4 WRITE
                    , table1 WRITE
                    , table5 WRITE
                    , table5 as t5 WRITE
                    , table6 as t6 WRITE
                    , table7 as t7 WRITE
                    , table7 WRITE
                    , table8 as t8 WRITE
                    , table9 t9 WRITE
                    , table10 t10 WRITE
                    , table11 t11 WRITE
                    , table12 WRITE;";

$this->mysqli = new mysqli(
    $this->credentials->server
    , $this->credentials->user
    , $this->credentials->password
    , $this->credentials->database
);

try{
    error_log('before first attempt lock tables '.$sql);
    $this->mysqli->query($sql);
    error_log('after first attempt lock tables');
} catch (Exception $e){
    error_log('Error locking tables: '.$e->getMessage());
}

error_log('After try catch.');
if($this->mysqli->error){
    error_log('lock table error: '.$this->mysqli->error);
}

When the process fails, I see "before first attempt lock tables" in my PHP  Error log. I do not see any of the other error_log() calls. After some checking, I determined it is because PHP has not received a response from MySQL.

I never get into the Catch Exception, since MySQL is not returning an error. MySQL is not returning anything unless I manually kill the MySQL Lock Tables Process.

If I don't kill the process, the PHP code never stops waiting for a response from mysql.

I am confused why the "LOCK TABLES" query is not gracefully waiting for the "metadata lock" to complete and then running. It was my understanding that more than one server was allowed to write to a database and the lock table features would work together instead of one hanging indefinitely.


texts: Postgresql simple query takes too much time like five minutes to execute

texts:
a very simple postgres query, like SELECT takes five minutes to execute. It was working fine, taking less than one second and at some point the execution time increased significantly to five minutes. When I do SELECT query to another table in the same database - it works pretty fast. Only one table is affected and it seems to happen at some point, before it was also executing fast. Following query takes five minutes to execute:
SELECT * FROM some_table LIMIT 50 OFFSET 0;

another thing is that if I select by Id - it works instantly, very fast, less than one second:
SELECT * FROM some_table WHERE id = '33ae0b5f0d6a0435e36faf8d';

UPDATE 1
table definition has 34 columns,
Id primary key column is indexed, it is GUID not integer
table contains only 8455 rows
server_prod=# SELECT COUNT(*) FROM some_table;
 count
-------
  8455
(1 row)

UPDATE 2
server_prod=# select current_setting('shared_buffers') AS shared_buffers, pg_size_pretty(pg_table_size('some_table')) AS table_size;
 shared_buffers | table_size
----------------+------------
 128MB          | 62 GB
(1 row)

server_prod=# explain (analyze, buffers, timing) SELECT * FROM some_table LIMIT 10 OFFSET 0;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..9677.70 rows=10 width=1379) (actual time=363665.384..363668.365 rows=10 loops=1)
   Buffers: shared hit=5701604 read=1443335
   ->  Seq Scan on project_views  (cost=0.00..8109910.80 rows=8380 width=1379) (actual time=363665.381..363668.358 rows=10 loops=1)
         Buffers: shared hit=5701604 read=1443335
 Planning time: 0.099 ms
 Execution time: 363668.414 ms
(6 rows)

UPDATE 3
psql version is 10.7
server_prod=# SELECT version();
                                                                   version
---------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.7 (Ubuntu 10.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)

please advise.


texts: Replication Monitor Information using T-SQL

texts:
The image below shows a transaction replication problem that I am currently investigating.

The image comes from the Replication Monitor.

How can I get this information using T-SQL?


texts: Total logical reads per database on SQL Server

texts: Is there a way to get the total running logical reads\writes per database from last restart?  Trying to monitor what dbs are most used and if that changes over time.


texts:
We have a 3-node Mongodb replica set that resides entirely in AWS. The primary is an i3.2xlarge (8 cores 61G RAM), while the secondary members are i3.xlarge (4 cores 30.5G RAM). These instances are each backed by two SSD EBS volumes, with one of the pair formatted XFS solely for the Mongodb installation. The Mongodb installations use the WiredTiger storage engine. The mongod.conf is here.

We have a roughly weekly ETL process that uses Pentaho to write to our replica set via a Tomcat8-based REST API. This process has completed smoothly for the last two years until our upgrade to Mongodb 3.6 at the end of February. During the first run of this process on Mongodb 3.6, the mongod process ran the instance out of memory. The OS invoked oom-killer to stop the process. After the subsequent election, the process then also killed the new primary.

We’d previously never configured swap space for the instances, but after this incident, swap space was configured on each, on a [RAM x 2] sized AWS NVMe instance store volume. Since implementing the swap space, any intensive process will continue to run the instance down to <1G of memory, then roll over into the swap space. We’re no longer experiencing a mongod service crash, but the system will regularly use up all available memory, and up to half of the swap space. As an example, the system was in such a state yesterday, and while restarting the service freed up the memory and swap space, the nightly dump has left the system using 26/60G of memory. It seems that the service isn’t releasing memory/killing threads once they’re no longer actively in use (see below image)..

Other system details:

Ubuntu 14.04.5
4.4.0-112-generic #135~14.04.1-Ubuntu SMP
Mongodb 3.6.2
transparent_hugepage & defrag are set to “never”
readahead is set to 16K
numactl is installed, allowing NUMA to be disabled via the init script


texts: Database structure for a system with multisite

texts:
The system I'm working is structured as below. Given that I'm planning to use Joomla as the base.

a(www.a.com),b(www.b.com),c(www.c.com) are search portals which allows user to to search for reservation.

x(www.x.com),y(www.y.com),z(www.z.com) are hotels where booking are made by users.

www.a.com's user can only search for the booking which are in
www.x.com
www.b.com's user can only search for the booking which are in
www.x.com,www.y.com
www.c.com's user can search for all the booking which are in
www.x.com, www.y.com, www.z.com

All a,b,c,x,y,z runs the same system. But they should have separate domains. So according to my finding and research architecture should be as above where an API integrate all database calls.

Given that only 6 instance are shown here(a,b,c,x,y,z). There can be up to 100 with different search combinations.

My problems,

Should I maintain a single database for the whole system ? If so how can I unplug one instance if required(EG : removing www.a.com from the system or removing www.z.com from the system) ? Since I'm using mysql will it not be cumbersome for the system due to the number of records?

If I maintain separate database for each instance how can I do the search? How can I integrate required records into one and do the search?

Is there a different database approach to be used rather than mentioned above ?


texts: SELECT LEFT JOIN but only the record that every matched value is null

texts:
Suppose that I have two tables name and location

name    |   location_id
'A'     |   NULL
'A'     |   NULL
'B'     |   NULL
'B'     |   2
'C'     |   3
'C'     |   4

location_id |   location_name
1   |   Australia
2   |   Belgium
3   |   Croatia
4   |   Chile
5   |   Denmark

CREATE TABLE location (
    location_id INT PRIMARY KEY,
    location_name VARCHAR(64)
);

INSERT INTO location VALUES
    (1, 'Australia'),
    (2, 'Belgium'),
    (3, 'Croatia'),
    (4, 'Chile'),
    (5, 'Denmark');

CREATE TABLE name (
    id INT PRIMARY KEY,
    fname VARCHAR(64),
    location_id INT,
    FOREIGN KEY (location_id) REFERENCES Location(location_id)
);

INSERT INTO name VALUES
    (1, 'A', NULL),
    (2, 'A', NULL),
    (3 ,'B', NULL),
    (4 ,'B', 2),
    (5 ,'C', 3),
    (6 ,'C', 4);

I want to select only the record that every matched value is null. That is only A will appear because it does not match any location_id but B should not appear because it does match with 2 or Belgium.

SELECT fname, location_name
FROM name n LEFT JOIN location l
USING (location_id)
WHERE location_name IS NULL

Query result:

name    |   location_name
'A'     |   NULL
'A'     |   NULL
'B'     |   NULL

The desired result:

name    |   location_name
'A'     |   NULL
'A'     |   NULL

Can someone guide me how do I write the WHERE statement that will give me the desired result? Thanks!


texts: Is there a name for this pattern, representing one-to-many relationships by adding columns with an index to a single table?

texts:
So a domain expert wants the following relation:

CarOwners(owner_id, name, address, car_1_registration, car_2_registration, car_3_registration, car_4_registration)

The reason given that most people are unlikely to have more cars than that, and that it's easier to reason with than the general case of an arbitrary number of cars. This seems like a common pattern for Excel jockeys, whereas for a DBA it would be much easier to simply have a separate relation:

Car(registration, owner_id)

But this idea is hard to sell to anyone who doesn't know what a JOIN is.

I looked in database literature and couldn't find any references to this (arguably anti-)pattern. Perhaps there's nothing theoretically wrong with it (it's just impractical), or it's simply too goofy to even be considered by academics. But it would be interesting to hear if it's actually covered.


texts:
This is a client environment which needs significant overhaul so all I am trying to do is slow the bleeding.
Situation
CPU is getting crushed, often 80% or higher utilization

VMWARE 5.5, Windows Server 2008r2, SQL Server 2012
1 vCPU with 6 cores (red flag here)
70GB of RAM
16 separate instances, the one driving most of the CPU use has 80 user
databases
All most all instances MAXDOP 0, all Cost threshold for parallelism is
5

Outage is not an option right now, so no reboots, no vCPU changes but I need to slow the bleeding that is the high CPU.
This a sample of top 10 current waits for the instance with 80 DBs, the CPU hog:
╔══════════════════════╦══════════════════╦════════════╦═════════════╗
║      wait_type       ║ Wait Time (Secs) ║ # of Waits ║ Avg ms/Wait ║
╠══════════════════════╬══════════════════╬════════════╬═════════════╣
║ WRITELOG             ║ 9.8              ║ 5563       ║ 1.8         ║
║ PAGELATCH_SH         ║ 7                ║ 21742      ║ 0.3         ║
║ SOS_SCHEDULER_YIELD  ║ 6.5              ║ 8891       ║ 0.7         ║
║ PAGELATCH_EX         ║ 5.3              ║ 20106      ║ 0.3         ║
║ CXPACKET             ║ 4.2              ║ 174        ║ 24.2        ║
║ LATCH_EX             ║ 2.7              ║ 1287       ║ 2.1         ║
║ LCK_M_S              ║ 2.2              ║ 123        ║ 18.1        ║
║ LCK_M_U              ║ 1.7              ║ 385        ║ 4.5         ║
║ PAGEIOLATCH_SH       ║ 0.9              ║ 972        ║ 0.9         ║
║ ASYNC_IO_COMPLETION  ║ 0.5              ║ 3          ║ 150.3       ║
╚══════════════════════╩══════════════════╩════════════╩═════════════╝

My thought, set MAXDOP at 3, cost threshold at 25 for all instances.


texts: Is there a way to trace the DDL and DML activities performed on Microsoft SQL Server manually?

texts:
I had C4 audit enabled to log all the activities previously and it mysteriously turned off. So I was looking for a way to trace changes/updates made to the complete database in terms of it's schema and the data from an year ago to current date.
Most sources pointed towards the data stored in default trace. Unfortunately, it only stores data for last seven days.
I tried using the fn_dblog() and only saw very recent data present. Generating Standard Reports also resulted in same.
So, is there any other way to view this historical data?


texts: Postgres: Preventing objects with same name

texts:
I just spent one hour debugging a query which had previously worked but suddenly created strange results. The cause of this bug was that I have a table in one schema which I reference in the query. Someone had, however, added a view in the current schema which has the same name as the table in the other schema. This caused the new view in the current schema to "take over" the reference in the query causing it to reference the view instead of the table. Obviously this caused me to start looking in the wrong place for this bug and getting completely confused. My question is therefore, how can I prevent this from happening? For example by enforcing that all views an tables in the database needs unique names. By enforcing this, I mean that it will cause and error if one attempts to create two objects with identical names.

Because I am using some external tools, I have set up postgres to search for tables in multiple schemas (I have forgot how I did this), which is probably partly to blame for this mistake, but I cannot change this.


texts: sql agent job hangs without any errors or warnings

texts:
Problem:

we have a SQL Agent job which runs everyday in the morning at 4:00 am. This job hangs sometimes without providing any errors or warnings. It just hangs. BUT when i stop the job and run again it works perfectly. This happens once in a week atleast.

This job is executing a SSIS package which loads data from oracle (source) to SQL SERVER 2008R2 (destination)

Steps Taken to Resolve issue

1) I thought the ssis package is losing connection in the middle of the process and i had increased the Connection Timeout=60 in the config file of this package but this doesn't worked. {I am using a xml configuration file for connection string }

2) I had configured logging for this package by using sysssislog table and i had checked this table when the job hanged, it doesn't showed any error information.

3) I informed this issue to our network administrator to check is there any network issues, but he told that everything is fine with the network.

4) in the OLEDB Destination editor i had used

Data Access Mode=Table or view-fastload

Table lock=checked

keep nulls=checked

check constraints=checked

rows per batch : blank

The above steps didn't worked yet, and it continues hanging oftenly.

Please share your opinions on this issue. I need your advice to troubleshoot this issue


texts: Select the students that are enrolled in both the courses

texts:
This is the table definition. I want to select the students that are enrolled in courses (1 and 3).

From this dataset the expected output is 'B'.


texts:
This query takes around 2 seconds when the order by is included, without the order it takes less than a tenth of a second.
The field that is doing the sorting, qc_products.addedDate, is an index and is a datetime.

What can I do to speed this up other than removing the order by?

SELECT p.productId, vo.Item_Price1,vo.Item_Price2 ,vo.samefldALU, p.visible
FROM
    qc_productInCategories as pc,
    qc_categories as c,
    qc_products as p,
    qc_variantOptions AS vo,
    qc_variants AS v
WHERE c.name='Coquette'
    AND c.categoryId = pc.categoryId
    AND pc.productId = p.productId
    AND p.productId = v.productId
    AND v.variantId = vo.variantId
    AND vo.visible=1
    AND p.visible=1
GROUP BY vo.samefldALU
ORDER BY p.addedDate DESC


texts:
I have a single MongoDB instance is performing quite badly. I noticed that db.currentOP().inprog.length quite often becomes quite big, sometimes even goes up to 100. When I drill down to the actual ops, I find op entry like below.

{
        "opid" : 2.91967e+007,
        "active" : true,
        "secs_running" : 0,
        "microsecs_running" : NumberLong(940789),
        "op" : "update",
        "ns" : "DB1.Collection1",
        "query" : {
            "_id" : "ID1"
        },
        "client" : "192.168.X.X1:PORT1",
        "desc" : "conn1068760",
        "connectionId" : 1.06876e+006,
        "locks" : {
            "^DB1" : "W"
        },
        "waitingForLock" : true,
        "numYields" : 0,
        "lockStats" : {
            "timeLockedMicros" : {},
            "timeAcquiringMicros" : {}
        }
    }

Notice that its waitingForLock property is set to true. How do I know which process is locking the collection and preventing the query from execution?

I'm not sure if MongoDB has deadlock situation or not, but from administration perspective, it's always good to identify the blocking process and diagnose what's causing the issue.


texts: MySQL : count occurences on different columns in a same query


texts: Injecting connection context information using a persistent table

texts:
I am working on an application that has several legacy modules that rely heavily on stored procedure (no ORM, so all fetches and data persistence is done through stored procedures).

Security for legacy modules relies on SUSER_NAME() to get current user and apply security rules.

I am migrating it to use an ORM (Entity Framework) and SQL connector will use a generic user to connect to the database (SQL Server), so I have to provide current username to many procedures.

In order to avoid changes in .NET code, I thought of "injecting" somehow current user in the context when a new connection is made:

CREATE TABLE dbo.ConnectionContextInfo
(
    ConnectionContextInfoId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_ConnectionContextInfo PRIMARY KEY,
    Created DATETIME2 NOT NULL CONSTRAINT DF_ConnectionContextInfo DEFAULT(GETDATE()),
    SPID INT NOT NULL,
    AttributeName VARCHAR(32) NOT NULL,
    AttributeValue VARCHAR(250) NULL,
    CONSTRAINT UQ_ConnectionContextInfo_Info UNIQUE(SPID, AttributeName)
)
GO

When a connection is opened (or reused, as a connection pool is used), the following command is used:

exec sp_executesql N'
    DELETE FROM dbo.ConnectionContextInfo WHERE SPID = @@SPID AND AttributeName = @UsernameAttribute;
    INSERT INTO dbo.ConnectionContextInfo (SPID, AttributeName, AttributeValue) VALUES (@@SPID, @UsernameAttribute, @Username);
',N'@UsernameAttribute nvarchar(8),@Username nvarchar(16)',@UsernameAttribute=N'Username',@Username=N'domain\username'
go

(0 CPU, ~15 reads, <6 ms)

A scalar function allows to easily get current user:

alter FUNCTION dbo.getCurrentUser()
RETURNS VARCHAR(250)
AS
BEGIN
    DECLARE @ret VARCHAR(250) = (SELECT AttributeValue FROM ConnectionContextInfo where SPID = @@SPID AND AttributeName = 'Username')
    -- fallback to session current, if no data is found on current SPID (i.e. call outside of the actual application)
    RETURN ISNULL(@ret, SUSER_NAME())
END
GO

Are there any caveats (robustness, performance etc.) in this approach from a data tier perspective?

Thanks.


texts:
Say I have a table with two columns, new, old.
Whenever a row is inserte for the first time, new will be set and old should default to null.
Whenever a row is updated, the new should become the new value, and old should become the replaced value of new.
How to do this using Oracle?


texts:
I have two queries.

The first one is:

    WITH Numbered AS
(
SELECT
        B.id,
        B.masahat,
        B.tozihat,
        mahsol_name = G.mahsolname,
        ghate_code = G.ghate_code,
        M.cheshme,
        rn = ROW_NUMBER() OVER (
            PARTITION BY B.id
            ORDER BY G.id)
    FROM bahrebardarighate AS B
    JOIN ghatemanabeabi AS G
        ON B.id = G.bahrebardarighate_id
    JOIN manabeabi AS M
        ON M.id = G.manabeabi_id
)
SELECT
    N.id,
    N.masahat,
    N.tozihat,
    N.cheshme,
    [mahsol_name_1] = MAX(CASE WHEN N.rn = 1 THEN N.mahsol_name ELSE NULL END),
    [ghate_code_1] = MAX(CASE WHEN N.rn = 1 THEN N.ghate_code ELSE NULL END),
    [mahsol_name_2] = MAX(CASE WHEN N.rn = 2 THEN N.mahsol_name ELSE NULL END),
    [ghate_code_2] = MAX(CASE WHEN N.rn = 2 THEN N.ghate_code ELSE NULL END),
    [mahsol_name_3] = MAX(CASE WHEN N.rn = 3 THEN N.mahsol_name ELSE NULL END),
    [ghate_code_3] = MAX(CASE WHEN N.rn = 3 THEN N.ghate_code ELSE NULL END)
    -- Repeat the pattern up to 12
FROM Numbered AS N
GROUP BY
    N.id, -- key column
    N.masahat,
    N.tozihat,
    N.cheshme

The second query is:

    WITH Numbered2 AS
(
SELECT
        A.id,
        A.bahrebardarighate_id,
        fullname = S.fullname,
        nationalcode = S.nationalcode,
        rn = ROW_NUMBER() OVER (
            PARTITION BY A.id
            ORDER BY S.id)
    FROM ghate AS A
    JOIN shoraka AS S
        ON A.id = s.ghate_id
)
SELECT
    N.id,
    N.bahrebardarighate_id,
    [fullname_1] = MAX(CASE WHEN N.rn = 1 THEN N.fullname ELSE NULL END),
    [nationalcode_1] = MAX(CASE WHEN N.rn = 1 THEN N.nationalcode ELSE NULL END),
    [fullname_2] = MAX(CASE WHEN N.rn = 2 THEN N.fullname ELSE NULL END),
    [nationalcode_2] = MAX(CASE WHEN N.rn = 2 THEN N.nationalcode ELSE NULL END),
    [fullname_3] = MAX(CASE WHEN N.rn = 3 THEN N.fullname ELSE NULL END),
    [nationalcode_3] = MAX(CASE WHEN N.rn = 3 THEN N.nationalcode ELSE NULL END)
    -- Repeat the pattern up to 12
FROM Numbered2 AS N
GROUP BY
    N.id, -- key column
    N.bahrebardarighate_id

How could i join them in one query?


texts: How can I disable parallel queries in PostgreSQL?

texts:
I want to benchmark some things in PostgreSQL. When I do this I normally turn off some of the features the database is currently using, just for my session, to see what the second-best plan is.

Though setting max_parallel_works = 0 , I still get a parallel plan.

                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..120892.01 rows=1000 width=15) (actual time=0.444..4781.539 rows=666667 loops=1)
   Workers Planned: 2
   Workers Launched: 0
   ->  Parallel Seq Scan on foo  (cost=0.00..119792.01 rows=417 width=15) (actual time=0.102..4711.530 rows=666667 loops=1)
         Filter: (lower(x) ~~ '%999.com'::text)
         Rows Removed by Filter: 9333333
 Planning time: 0.124 ms
 Execution time: 4801.491 ms
(8 rows)

I know it says Workers Launched: 0. But, I would like that to look like a pre-Parallel plan where the plan itself is not even planned to be parallel. How can I accomplish this?


texts: Is there a SELECT statement to subtract rows of the same column?

texts: Column 1 is the mem_id, Column 2 is the Transaction type C and D, and Column 3 is the Amount Owed. So I want to return Column 3 "Amount Owed", but it has to be C-D in Column 2 for each member. thanks, I hope that helps.


texts: Transfer Oracle 10g Database From Linux to Windows

texts: Can anyone tell me how to restore Oracle 10g Database backup created in Linux to the Oracle 10g version for windows...


texts: Difference between LC_COLLATE='C' and LC_COLLATE='C.UTF-8' in PostgreSQL

texts:
I need guidance on the exact distinction, if any, between LC_COLLATE='C' and LC_COLLATE='C.UTF-8' in PostgreSQL. It seems to me that they behave exactly the same and that the codeset (with bearing on character classification for character-case conversion, etc.) is picked up from LC_CTYPE alone.

Here is an experiment with PostgreSQL 9.6 that seems to support this assessment ('Ä' is the upper-case version of the German Umlaut 'ä'):

SHOW LC_COLLATE; -- 'en_US.UTF-8': cluster default
SHOW LC_CTYPE; -- 'en_US.UTF-8': cluster default

CREATE DATABASE test WITH TEMPLATE='template0' ENCODING='UTF8'
  LC_COLLATE='C.UTF-8' LC_CTYPE='C';
\c test
SELECT upper('ä'); -- 'ä': codeset 'UTF-8' apparently not picked up from LC_COLLATE
\c postres
DROP DATABASE test;

CREATE DATABASE test WITH TEMPLATE='template0' ENCODING='UTF8'
  LC_COLLATE='C' LC_CTYPE='C.UTF-8';
\c test
SELECT upper('ä'); -- 'Ä': codeset 'UTF-8' apparently picked up from LC_CTYPE
\c postres
DROP DATABASE test;

CREATE DATABASE test WITH TEMPLATE='template0' ENCODING='UTF8'
  LC_COLLATE='C.UTF-8';
SELECT datcollate, datctype FROM pg_database
  WHERE datname='test'; -- 'C.UTF-8', 'en_US.UTF-8'
\c test
SELECT upper('ä'); -- 'Ä': codeset 'UTF-8' apparently picked up from cluster default `LC_CTYPE`
\c postres
DROP DATABASE test;

So it would seem to me that LC_COLLATE='C' and LC_COLLATE='C.UTF-8' are synonymous. The rationale could be that LC_COLLATE determines string sort order, which does not depend on any codeset in the case of C. Am I correct or is there a ready counter-example?


texts: User has db_owner through AD group but cannot create table

texts:
We've setup a SQL Server 2012 Enterprise, and through certain domain user groups, users are granted access to several databases.

For instance, group 'ADB' has for several databases following rights:

Server role: public

User mapping: db_datareader, db_writer, db_owner, db_public

Now, our application sometimes needs to execute certain queries. One of them was a CREATE TABLE statement. When a user of the group 'ADB' opens the application of us, in the background the script is run to CREATE TABLE. But it doesn't do that, because the user has no right to create. I don't understand because that user is part of a domain group that is DB_OWNER.

If I run a T-SQL command that shows all the members of db_owner, I don't see the AD group 'ADB' (as I would have expected) for those databases, but I do see 'sa'. Could this be the cause of my problem?

Hundreds of databases have rights assigned through server roles and user mapping, manually through SSMS. So it will be a lot of work fixing those things, but maybe it can be done through scripting. All I want to know is, why a user can't do a create table if he is part of a domain group that is db_owner of a database?


texts:
Consider a prices table with these columns:

id         integer primary key
product_id integer -- foreign key
start_date date not null
end_date   date not null
quantity   integer
price      numeric

I'd like the database to enforce the rule that a product can only have one price at a specific quantity in a date range (via where <date> BETWEEN start_date AND end_date).

Is this kind of range-based constraint doable?


texts: Creating Duplicate Table in SQL SERVER 2014

texts:
I am trying to create a duplicate table in SQL SERVER. I changed the table name but all other objects like constraints are same. Do i have to change all the objects as they exists in the base table or i can have same objects name?
I know it might sound a silly question but i am new to this.


texts: How can sqlplus knows where ORACLE_SID point?

texts:
As I understand, SID is unique value to identify an Oracle database instance.

And I can set default database instance sqlplus use by changing ORACLE_SID environment variable.

But how can sqlplus know where the SID point?

I have thought tnsnames.ora let sqlplus know the information but there seems not present that kind information.
(If so when does sqlplus use tnsnames.ora?)


texts: Upgrade MongoDB meta-data after binary upgrade

texts:
I upgraded our MongoDB binaries from 2.2.5 to 2.4.9. I did not follow one of the considerations prior to upgrading:

To upgrade to 2.4, sharded clusters must upgrade following the meta-data upgrade procedure.

The reason, not that it matters, is because I didn't think it was needed since we do not have any sharded collections in our environment. So, instead of following the sharded cluster upgrade, I followed the replica set upgrade procedure.

My question is, can I successfully run the meta-data upgrade without having to rollback to 2.2.5?


texts: Impact of shrinking ldf file after restoring full backup

texts:
What is the use of data in .ldf(log) file after restoring a full backup. I am planning to shrink the log files after restoring the full backup in my dev server.

Is there any side effect for this ?


texts: Having charset "utf8mb4" - does it mean every character takes 4 bytes, or only those that need 4 bytes?

texts:
I have a VARCHAR(80) utf8mb4 column, and I'm testing adding some ASCII & Emoji characters, and use LENGTH() and CHAR_LENGTH() to understand the differences.
From reading in different places, my understanding is that each character on a utf8mb4 column would take 4 bytes. However, it appears that it may not be the case, if I understand LENGTH() as giving me the actual size that the specific contents are taking on that field.
Is it correct that a row with "aaaa" contents would take less storage than a row with ""?


texts:
I just want to know how to import data from source to SQL Server 2008 database using SSIS.

I have a simple txt file (actually it's from this blog : http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/)

1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202

I created a new table in SQL Server :

CREATE TABLE Test
(ID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
BirthDate SMALLDATETIME)

I draged Data Flow Task, then Flat File Source and OLE DB Destination.

I created new Flat file connection, choose the text file, and in Advanced tab, there are 4 column, so I changed their data type as follow:

Column 0 : two-byte signed integer [DT_I2]
Column 1: String[DT-STR] - Outputcolumnwith :20
Column 2: String[DT-STR] - Outputcolumnwith :20
Column 3: database timestamp with precision [DT_DBTIMESTAMP2]

Then, set up new OLB DE Connection, edited it, set [dbo].[Test] under "Name of the table or the view" + mapped each column from input to destination.

But when I run it, these errors occurred  :

  Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (4)
  reached the maximum allowed (1); resulting in failure. This occurs
  when the number of errors reaches the number specified in
  MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

  Error: 0xC02020A1 at Data Flow Task, Flat File Source [52]: Data conversion failed. The data conversion for column "Column 3" returned
  status value 2 and status text "The value could not be converted
  because of a potential loss of data.".

  Error: 0xC0209029 at Data Flow Task, Flat File Source [52]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column
  "Column 3" (90)" failed because error code 0xC0209084 occurred, and
  the error row disposition on "output column "Column 3" (90)" specifies
  failure on error. An error occurred on the specified object of the
  specified component.  There may be error messages posted before this
  with more information about the failure.

  Error: 0xC0202092 at Data Flow Task, Flat File Source [52]: An error occurred while processing file "D:\New folder\text.txt" on data row 1.

  Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code   DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component
  "Flat File Source" (52) returned error code 0xC0202092.  The component
  returned a failure code when the pipeline engine called PrimeOutput().
  The meaning of the failure code is defined by the component, but the
  error is fatal and the pipeline stopped executing.  There may be error
  messages posted before this with more information about the failure.

What could be the cause for not import successfully in my case?

I'm using evaluation version on Windows 7.

Thanks so much!

FINALLY I COULD IMPORT FROM TXT FILE TO SQL 2008 DATABASE,

LET'S NAME THE 4 COLUMNS AND THEIR DATA TYPE AS :

id : two-byte signed integer [DT_I2]
firstname : String[DT-STR] - Outputcolumnwith :20
lastname: String[DT-STR] - Outputcolumnwith :20
yeardate: String[DT-STR] - Outputcolumnwith :20

THEN IN DERIVED COLUMN EDITOR :

Derived column name : yeardate
Derived column : replace 'yeardate'
Expression : (DT_DBTIMESTAMP)(SUBSTRING(yeardate,1,4) + "-" + SUBSTRING(yeardate,5,2) +     "-" + SUBSTRING(yeardate,7,2))


texts: PostgreSQL: Non-continious replication of new data with COPY

texts:
I have a database tables with millions of measurements. New data is coming in every day. For analysis, I want to replicate the data to my laptop (local postgres db). No need for automatic replication. It's OK to start a script for this.

I feel like the standard replication solutions are inadequate and
overengineered for my use because

I need asynchron replication,
some solutions are inefficient because they replicate row-wise
(yet data on the server is bulk-inserted),
and they need too much configuration.

I would like to use COPY TO STDOUT | COPY FROM STDIN but here I
have to select only the new data. How can I do that?

The table has this form:

   Column   |           Type
------------+--------------------------
 devicename | character varying(30)
 id         | integer
 timestamp  | timestamp with time zone
 value      | numeric
 variable   | text

PK would be (devicename, id). Note that id alone is not unique because
the data is coming from multiple devices.

How can I select only new data for COPY?
Any other approaches for this replication requirements?


texts: What are user connections - when are the created and destroyed?

texts:
We've been getting an intermittent error on out site:

  Warning: mysql_connect() [function.mysql-connect]: User '557574_prod' has exceeded the 'max_user_connections' resource (current value: 10)

Which prevents the site from being accesses & effectively it is down.

(obviously increasing the 'max_user_connections' would fix it but that is not an option through our hosting scheme)

I may have 'fixed' it, but I am not 100% sure I understand the issue & I would like to be clear. I thought the problem must be cause by too many cron jobs using up the connections (we have a couple running every minute), and I cut down the number of cron jobs & that seemed to lessen the amount of errors. I have created a separate user account for the cron jobs, so if that was the problem it should be solved now.

However, our logging system does not record the error when it occurs, so I am still concerned.

So I am asking: what constitutes a 'user_connection' in MySQL?

is there a separate connection for each DB call?
if not, when would DB calls require a separate connection?
why would this error only occur on the live production site? perhaps there is a separate connection for each unique ip or simialr?

I feel I am over-thinking it . . .


texts: comparing column values when there are nothing in common between tables

texts:
I have a need to display the value stored in columns of separate tables where the tables and columns have nothing in common. I just want to compare the content of the rows of each column side-by-side. And just the 1st 10 rows would be sufficient for my purposes. There are no primary keys or common link between the tables.

I have many tables with hundreds of columns to compare. Is there a simple way to do this?

i.e.
Table1, column3 | Table2, column6 | Table3, column1

Resulting in something like the following

tbl1col3 | tbl2col6 | tbl3col1
123.9    | abc      | x12
234.0    | bcd      | o23

Using postgresql 9.6, pgadmin4 v3.5


texts: SQL Job fails due to QUOTED_IDENTIFIER = OFF

texts:
I have a SQL agent job that runs nightly at 2am successfully for the last year;  We haven't made any change to that job specifically but it suddenly gives error:

  MERGE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operation

I read around and looks like a quick fix is to insert the command "SET QUOTED_IDENTIFIER ON" at the very top of this job.

However, I really want to know why this happens? the job does not call any store procedure, instead it uses merge, insert, update using some views, tables.

I verified all my views and tables have quoted_identifier set to true which I believe is the default value.

I ran the following script and I see that my SQLAgent - Job Invocation Engine has quoted_identifier set to off.

SELECT *
FROM   sys.dm_exec_sessions
WHERE  is_user_process = 1
AND    quoted_identifier = 0 ;

This perhaps explains why my job fails?  But why it fails all of a sudden with the same job that ran successful the night before?

Could a script that adds new index or alter a store procedure caused this to quoted_identifier value to false? can a database restart do it?


texts: Does order matter for composite primary key in Oracle or any database?

texts: I have a Composite Primary key which is combination of Column1, and Column2.  What would be the difference if I have Composite key with Column2, and Column1.  Does it even really matter which order you set columns?


texts: SQL Server 2019 column store indexes - maintenance

texts:
I have a clustered columned stored index on table used for logging - only inserts (but not bulk inserts).
The current table stats are:

3541 millions rows
6.6 GB reserved space

I see this morning the following operation via sp_whoisactive:
ALTER INDEX [...] ON [...].[...]
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

I use the following query to check how many rows per row_group_id we have:
SELECT
    tables.name AS table_name,
    indexes.name AS index_name,
    partitions.partition_number,
    dm_db_column_store_row_group_physical_stats.row_group_id,
    dm_db_column_store_row_group_physical_stats.total_rows,
    dm_db_column_store_row_group_physical_stats.deleted_rows,
    dm_db_column_store_row_group_physical_stats.state_desc,
    dm_db_column_store_row_group_physical_stats.trim_reason_desc
FROM sys.dm_db_column_store_row_group_physical_stats
INNER JOIN sys.indexes
ON indexes.index_id =
    dm_db_column_store_row_group_physical_stats.index_id
AND indexes.object_id =
    dm_db_column_store_row_group_physical_stats.object_id
INNER JOIN sys.tables
ON tables.object_id = indexes.object_id
INNER JOIN sys.partitions
ON partitions.partition_number =
    dm_db_column_store_row_group_physical_stats.partition_number
AND partitions.index_id = indexes.index_id
AND partitions.object_id = tables.object_id

and we 3383 row groups with 1048576 rows and few at end like this:

The issue is that we are using standard edition (on premise) and the rebuild operation is not performed online and cause a lot of blocking.
I have never seen such issue before. A few weeks ago we have upgraded from SQL Server 2016 SP1 to SQL Server 2019.
My questions are:

if only inserts are applied, should be the operation reorganize and be faster
if not, if we apply partitioning, for example on year basis as the table is used for logging, will the automation process only rebuild the data for the last partition


texts: How to select user that doesn't exist in the second table

texts:
I am trying to understand how to select a user from users table that is not associated with current user in user_follows table: neither in users_id not in shown_users_id.

Basically a new user that not related to my current user.

Tried something like this:

SELECT
   user.id
FROM
   users AS user
LEFT JOIN
   user_follows AS follow ON user.id = follow.users_id
LEFT JOIN
   user_follows AS follow2 ON user.id = follow2.shown_users_id
WHERE
   user.id != 2 # (user that I don't wan't to select)
LIMIT 1;

But it would still return me the row with user 3 in it.

So if in my users table I have user 2,3,4, and with the following data in user_follows table:

I only want to get user 4, since it is not associated with user 2 at this moment.


texts: Optimising latitude and longitude selection query

texts:
I have table as follows the schema below.

CREATE TABLE `gdata` (
`alarmTypeID` tinyint(4) NOT NULL DEFAULT '0',
`fleetID` smallint(11) NOT NULL,
`fleetGroupID` smallint(11) DEFAULT NULL,
`fleetSubGroupID` smallint(11) DEFAULT NULL,
`deviceID` mediumint(11) NOT NULL,
`vehicleID` mediumint(11) NOT NULL,
`gDateTime` datetime NOT NULL,
`insertDateTime` datetime NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
`speed` smallint(11) NOT NULL,
(see full text)
ALTER TABLE `gdata`
ADD PRIMARY KEY (`vehicleID`,`gDateTime`,`alarmTypeID`),
ADD KEY `gDateTime` (`gDateTime`),
ADD KEY `fleetID` (`fleetID`,`vehicleID`,`gDateTime`);
COMMIT;

My issue now is that I can run a query as below EXPLAIN select vehicleID,latitude,longitude from gdata where  gDateTime between '2017-07-03 00:00:00' and '2017-07-04 00:00:00'
and the results of the explains is

1   SIMPLE  gdata       range   gDateTime   gDateTime   5       1251    100.00  Using index condition

I have the following function which works to test if the a latitude and longitude is withing a rectangle.

DELIMITER $$
--
-- Functions
--
CREATE DEFINER=`root`@`localhost`
    FUNCTION `geoProcessor`(pt POINT, mp MULTIPOLYGON)
        RETURNS int(1)
        DETERMINISTIC
BEGIN

DECLARE str, xy TEXT;
DECLARE x, y, p1x, p1y, p2x, p2y, m, xinters DECIMAL(16, 13) DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE p, pb, pe INT DEFAULT 0;

SELECT MBRWithin(pt, mp) INTO p;
IF p != 1 OR ISNULL(p) THEN
RETURN p;
END IF;

SELECT X(pt), Y(pt), ASTEXT(mp) INTO x, y, str;
SET str = REPLACE(str, 'POLYGON((','');
SET str = REPLACE(str, '))', '');
SET str = CONCAT(str, ',');

SET pb = 1;
SET pe = LOCATE(',', str);
SET xy = SUBSTRING(str, pb, pe - pb);
SET p = INSTR(xy, ' ');
SET p1x = SUBSTRING(xy, 1, p - 1);
SET p1y = SUBSTRING(xy, p + 1);
SET str = CONCAT(str, xy, ',');

WHILE pe > 0 DO
SET xy = SUBSTRING(str, pb, pe - pb);
SET p = INSTR(xy, ' ');
SET p2x = SUBSTRING(xy, 1, p - 1);
SET p2y = SUBSTRING(xy, p + 1);
IF p1y < p2y THEN SET m = p1y; ELSE SET m = p2y; END IF;
IF y > m THEN
IF p1y > p2y THEN SET m = p1y; ELSE SET m = p2y; END IF;
IF y <= m THEN
IF p1x > p2x THEN SET m = p1x; ELSE SET m = p2x; END IF;
IF x <= m THEN
IF p1y != p2y THEN
SET xinters = (y - p1y) * (p2x - p1x) / (p2y - p1y) + p1x;
END IF;
IF p1x = p2x OR x <= xinters THEN
SET counter = counter + 1;
END IF;
END IF;
END IF;
END IF;
SET p1x = p2x;
SET p1y = p2y;
SET pb = pe + 1;
SET pe = LOCATE(',', str, pb);
END WHILE;

RETURN counter % 2;

END$$

DELIMITER ;

My issue now I want to find out which vehicle is within a rectangle within a time range value. I have to run two separate queries to first run the first queries get the lat, long value then test if its within the rectangle using the second query. How to opmtimise this?


texts: Can a factless fact table be a bridge table?

texts: As for bridge tables, they can be a helper table both for facts and dimensions. But can the factless fact table be a bridge table?


texts: Logical Drives - I/O aside, does SQL Server care?

texts:
I'm one of those Accidental DBAs. Engineer at a medium sized company who worked for years to work without a DBA. We've since hired some (at my recommendation) and they're great but I'm trying to find the answer to a conversation I'm having with one of them regarding storage.

Here's the high level question: We're on an enterprise level EMC SAN a large shared pool of mixed drives for SQL Server and we tend to have good I/O performance. Data, Logs, and tempdb all exist on "S:".

So, does SQL Server "care" or benefit in some way from having multiple logical drives for it's different data types? I'm currently operating under the assumption that the answer is "No" but the DBA seems to think that SQL Server makes "better decisions" with separate drive letters, though he can't give me an example at the moment.

I can see the benefit from a storage space management perspective and of course if you give a logical volume it's own spindles on the San you might see some performance ... but simply giving SQL Server a new drive letter to work with?


texts:
I'm running SQL Server 2008 R2 on a Windows machine.

I've been tasked with setting up log shipping between our production site (managed by a service provider) and our QA environment which is hosted on our local network.

My research has only mentioned that the accounts on the two different domains must be the same for network passthrough.  I'm not sure how this works since the domains are different and the accounts referenced would be domain accounts. How does network passthrough work with SQL Server?  What do I need to do to enable it?


texts: How group by affects aggregate functions

texts:
I tried to understand the following scenarios:

Table name: family
+------+-------+
| id   | name  |
+------+-------+
|    1 | alice |
|    2 |  bob  |
+------+-------+

That table will be used for illustration purposes.

Server version: 5.6.20 MySQL Community Server (GPL)

Lets try:

SELECT id, name, COUNT(name) FROM family WHERE id = 50;

The query will result in:

+------+------+-------------+
| id   | name | COUNT(name) |
+------+------+-------------+
| NULL | NULL |           0 |
+------+------+-------------+

My first question is about how I got this result?, what I think is that:

The COUNT(name) gets called after the query(internally) resolved into:

+------+------+
| id   | name |
+------+------+
| NULL | NULL |
+------+------+

Now at this point the COUNT(name) is ignoring the NULL value and that is why I get this extra column:

+-------------+
| COUNT(name) |
+-------------+
|           0 |
+-------------+

I only want to understand the steps that build the result.

The following step is taken by me so I can get Empty set and not the table above.

So to get Empty set as the result I add:

SELECT id, name, COUNT(name) FROM family WHERE id=50 GROUP BY id;

At this point I pretty desperate, not just I couldn't understand the problem I'm lost with how the solution is even working.

My second question is about the solution, again my focus is on the process, the actual steps the database has taken to achive that result.

If anyone can walk me step by step I will be very thankful.


texts: MySQL user per database performance implications?

texts: There is a PHP application that uses only one MySQL username. And there are several databases in the MySQL server. Each database is used by a different company. Should I keep "one MySQL" for the entire system or should I switch to "user per database"? Will I have performance increase if I switch to "user per database"?


texts: Convert character varying(255)[] to JSONB

texts:
I currently have array of strings character varying(255)[] and want to convert it to a JSONB object. When I try to cast it directly, it results in an error, despite it being a valid JSON, is there a nice way to convert it?:

ERROR:  cannot cast type character varying[] to jsonb


texts: Finding login or user name of last person to use a DB

texts:
The boss has asked for a list of DB names along with size and name of last person to use that DB. I've found resources for when a DB was last accessed but for by whom. How would I solve this?

SQL2008R2 being used and so far I have this:

exec sp_MSForEachDB '
use ?
select ''?'', (SUM(df.size)*8)/1024 as ''Size (MB)''
from sys.database_files as df
'

It is acceptable for the last person aspect of the answer to be fuzzy or loose. i.e. as long as it's someone who went near it sometime vaguely recently that is "OK".


texts: See whether a groupped item have more than one value in a database column

texts:
I am working on SQL database where there are several thousands of lines in one table. This table (product_lines) looks like this:

sku price   Discount    FinalPrice
1   29      5           24
1   29      5           24
2   30      5           25
2   30      0           30
3   15      0           15
3   15      0           15

Each line contains SKU's information when used in a receipt. SKUs may repeat within the rows (eg sku:2) but discount vary each time. I am trying to collect the items that exist both with and without a value in the column discount -- to collect the SKUs that have sold with and without discount. The result I am looking for should look like:

  sku price   Discount    FinalPrice
  2   30      5           25
  2   30      0           30

How can I do this?


texts: Storing IPv4 and IPv6 hosts and networks

texts:
I have to store the IPv4/v6 address and networks(CIDR notation) in a MySQL database. Unfortunately, I am constrained to use Mysql.

So, I'm looking for the equivalent of the inet type of  PostgreSql. :

  The inet type holds an IPv4 or IPv6 host address, and optionally its
  subnet, all in one field. The subnet is represented by the number of
  network address bits present in the host address (the "netmask"). If
  the netmask is 32 and the address is IPv4, then the value does not
  indicate a subnet, only a single host. In IPv6, the address length is
  128 bits, so 128 bits specify a unique host address.

  The input format for this type is address/y where address is an IPv4
  or IPv6 address and y is the number of bits in the netmask. If the /y
  portion is missing, the netmask is 32 for IPv4 and 128 for IPv6, so
  the value represents just a single host. On display, the /y portion is
  suppressed if the netmask specifies a single host.

I'm aware of the followings functions of Mysql 5.6 INET_ATON(), INET_NTOA(), INET6_ATON(), INET6_NTOA(). But I use Mysql 5.5... So, forget INET6_xxxx.

I'm also aware of the following question "Storing IP address" but it doesn't deals with network CIDR notation.

How you would implement the equivalent of inet type in Mysql 5.5?

3 columns : Network (2xBIGINT), CIDR (TINYINT) ?
string ? (@Phil)


texts: Should I put OLAP and realtime web DBs on the same server?

texts:
I have several different workloads I'd like to consolidate into the same Sql 2012/2014 instance.

Cognos/OLAP is burst oriented and users are accustomed to a response delay
Web databases that require immediate response (user authentication)

Is there any way to leverage SQL to partition these workloads?


texts: MobiLink: How to add a new table to synchronisation using Sybase Central and mlagent

texts:
How to add a new table to a synchronisation using mlagent configured from Sybase Central. This might be related to this or this question.

When doing a change on remote database as described on Sybase Tutorial it's not updating the consolidated database as well as the synchronisation model. When updating the model and add maybe a complete new table (or a new column) what is the correct way on doing this using mlagent and remote tasks configured from inside Sybase Central?


texts: Will this RedGate backup plan interfere with a new differential backup plan I want to create?

texts:
First, I need to get an assumption verified: If I setup a differential backup plan and someone comes along and manually creates a backup without specifying COPY_ONLY then it's going to throw the plan off, right? (I'm assuming a scenario where they backup to some arbitrary location and then maybe even delete the backup they created after they're done with it).

Secondly, we currently have a RedGate backup solution in place that uses the code below. I'm not familiar with how the master..sqlbackup proc it uses works so I am wondering if it's possible for me to leave this in place if I setup a differential plan alongside it or if I would need to disable this.

Why would I do this? Well, we're migrating to the cloud and I'd like to setup differential backups for the purpose of minimizing downtime as we move the database. But I'd just as soon not mess with the existing backup plan if I don't have to. And if I have to then I have to, but would rather not just in case something goes awry with my backup plan in the process.

DECLARE @exitcode INT , @sqlerrorcode INT;

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES
    [DB1, DB2, etc]

    TO DISK = ''F:\MSSQL11.MSSQLSERVER\MSSQL\Backup\<TYPE>\<DATABASE>\<AUTO>''
    WITH    ERASEFILES_ATSTART = 1b,
            CHECKSUM,
            DISKRETRYINTERVAL = 10,
            DISKRETRYCOUNT = 3,
            COMPRESSION = 4,
            THREADCOUNT = 4,
            NAME = ''<AUTO>'',
            DESCRIPTION = ''<AUTO>''"',
    @exitcode OUTPUT, @sqlerrorcode OUTPUT

IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

EDIT: And I can already imagine someone might ask why I don't just use RedGate to do the diff backup plan. First, I have no experience with it and second we won't have RedGate on the new server.


texts: Adding SQL Agent output to text file for all agent jobs in one go

texts:
As a best practice, i am trying to schedule an activity where all SQL agent jobs should log output to text file.

Manually i can do this for single job. But i am looking for a better way either by script or any good method i can make this change for multiple jobs about 80-90 jobs per server.

Moreover i have to do this in QA, UAT and prod, covering over 100 servers.

Please share you're experience or any helpful links how can this be achieved in best way as path of log files where output will be generated should be common across servers/jobs.


texts: Upgrading 5.6 to 5.7 without losing any data

texts: I want to upgrade MySQL from 5.6 to 5.7 but I already have the existing databases and tables. How should I upgrade without losing any data?


texts: PostgreSQL Operator Class for index support of custom operator

texts:
I'm trying to create an index that will support queries that use my custom operator. This is on PostgreSQL 10.4.
The custom operator
I followed the tips in this SO answer to create an operator that performs "LIKE" style matching on elements in an text ARRAY.
CREATE FUNCTION reverse_like (text, text) returns boolean language sql
as $$ select $2 like $1 $$;

CREATE OPERATOR <~~ ( function =reverse_like, leftarg = text, rightarg=text );

The above operator allows me to do things like
SELECT 'ab%' <~~ ANY('{"abc","def"}');

The schema, index and query
I have a table with web traffic visits called sessions which includes an array column.
CREATE TABLE sessions
(
   session_id    varchar(24) NOT NULL,
   first_seen    timestamp,
   domains       varchar[]
);

To query the domains column to see if a given domain (or partial/ wildcarded domain name) was visited I can do the following:
SELECT count(*)
FROM session_4070ba14_f081_41cb_9ef7_9dd385934da7
WHERE 'www.foo%' <~~ ANY(domains);

I want to speed up the above queries with GIN index. So I created the index as follows:
CREATE INDEX idx_domains ON session USING GIN(domains);

The Question
After running  analyze on the table and a set enable_seqscan = false; I have no luck getting Postgres to employ this index. It's always doing a seqscan. It uses the above index of array operators like @> but not for my custom <~~ operator.
I think its because the GIN index doesn't know how to handle my custom operator - so do I need to create an operator class and then create my index using that? Or do I create a functional index?


texts: redshift drop table with + and spaces in name

texts:
I have a table that a user created which appears to be from a python variable that never interpolated.

afsproddb01=# \dt+ *transtable*
                    List of relations
 schema |      name       | type  | owner  | description
--------+-----------------+-------+--------+-------------
 public |  + transtable + | table | usiapp |
(1 row)

I am unable to drop it, I can only describe it with the \dt+ and wildcard method.  Can anyone point me in the right direction?


texts: Oracle(12+) + Kerberos = Protocol Violation

texts:
we have an various oracle database installations and a few Kerberos KDC servers
application uses JDBC driver latest 8 version and it works perfect with 11.2 version
but when I try to connect to a bit higher version of databases like 12c or 19c I may get the Protocol Violation error. When I say "may get" it means I may even not get the error, but in 90% cases this issue can be reproduced.
We eliminate network connection issue because 11 and 12 versions of database are on the same server. Anyway, when server trace is enabled we may find such lines in it:
[27-JUL-2020 13:59:03:914] nauztk5avalidate: entry
[27-JUL-2020 13:59:03:914] nauztk5ahgetcontext: entry
[27-JUL-2020 13:59:03:914] nauztk5ahgetcontext: exit
[27-JUL-2020 13:59:03:914] nauztk5avalidate: Driver State is  "3".
[27-JUL-2020 13:59:03:914] nacomrp: entry
[27-JUL-2020 13:59:03:914] nacomrp: exit
[27-JUL-2020 13:59:03:914] nauztk5avalidate: Error while recieving forwarded credentials : 9.
[27-JUL-2020 13:59:03:914] nauztk5avalidate: failed
[27-JUL-2020 13:59:03:914] nauztk5avalidate: exit
[27-JUL-2020 13:59:03:914] nau_scn: credential validation function failed
[27-JUL-2020 13:59:03:914] nacomsd: entry
[27-JUL-2020 13:59:03:914] nacomfsd: entry
[27-JUL-2020 13:59:03:914] nacomfsd: exit
[27-JUL-2020 13:59:03:914] nacomsd: exit
[27-JUL-2020 13:59:03:914] nau_scn: failed with error 12631
[27-JUL-2020 13:59:03:914] nau_scn: exit
[27-JUL-2020 13:59:03:914] na_csrd: failed with error 12631
[27-JUL-2020 13:59:03:914] na_csrd: exit
[27-JUL-2020 13:59:03:914] nacomsn: entry
[27-JUL-2020 13:59:03:914] nacomap: entry
[27-JUL-2020 13:59:03:914] nacomps: entry
[27-JUL-2020 13:59:03:914] nacomps: exit
[27-JUL-2020 13:59:03:914] nacomap: exit

Something makes this nauztk5avalidate function fail. I can't find any related information on internet. It's interesting that we get this error on various Kerberos servers (like windows and linux) and various database versions 12c and 19c, but not int 11.2
I've tried to change a tons of JDBC parameters but could succeed only in getting new error messages, without getting rid of the subject related one.
This Protocol Violation is so sporadic, but some of connections are successful so configuration seems fine
Seems the root cause is withing database. May be some of you have any ideas?


texts: Strategy on Standby restore option for hourly log shipping file

texts:
I am trying to come up with a strategy of keeping up with daily database update process using "standby" restore mode.
I am getting 24 log shipping files (for the previous day's each hour transaction log files) from a third party's FTP site. I would update these 24 files on a nightly run.
I get these files originally in SQB file formats, and then I have a tool and script to convert these SQB files into BAK file format.
Now, I am trying to come up with a strategy of continuous back up plan.
Database does not have to be updated or modified, but just to be read.
Do I restore each transactional log file as "standby" all the time and just leave them as "standby" mode?
I am planning to create a separate database to retrieve only necessary data from some tables from this "read-only" database.
I have one more question. If I accidentally run a script to restore this database as "NoRecovery or Recovery" mode, is there way to change the mode back to "Standby" by running a script or do I have to restore full bak file again as "Standby" (do the whole process again)?


texts:
I want to get stats from the sys.dm_exec_procedure_stats view.

When I check the records from months ago, I see that after a point of time its stats reset daily, but there have been no server resets.

It seems there is a job running that makes this view reset. Can anyone help me with this problem? What kind of procedures will reset these views?


texts: Which table gets the FK? Is there a clear Parent-Child relationship here?

texts:
I have the following tables:

Each ProductLine will encompass many Parts. For this reason, it initially seemed to me that the Part table is a child of the ProductLine table. However, a part is not simply an extension of a product line, and when I asked myself if it is possible to a part to exist without a product line, the answer seems to be yes.

So I'm wondering if the above design appears to be correct or if there is a better design. In the Part table, PartBase can be thought of as the unique identifier for each part (the part number if you will.)

As a somewhat unrelated question, I have these tables set up as a one to many relationship. Since each Part must belong to a ProductLine but cannot belong to more than one, is it more correct to have this as a one-and-only-one to many relationship? Is the only difference that a Part must contain a not null ProductLineNumber in the actual database? Is that different from requiring that ProductLineNumber be not null?

To follow up to @HandyD's comment, as a business rule, every part must belong to exactly one product line. However, when I was thinking about a part as a physical object, it shouldn't need a product line to exist (a product line is just a label after all.) I'm comparing here to how a sale needs a customer to make sense, so that a Sale table would more clearly be the child of a Customer table. Is this distinction between Part-ProductLine and Sale-Customer a false one?


texts: POSTGRES declaring variable containing date array in functions

texts:
I am trying to declare a date array.

I have tried this:

DECLARE
dateVal DATE[] := ['2018-01-01','2018-02-01'];

I get:

ERROR:  syntax error at or near "["

How do I properly declare it?


texts: Is "Read only routing" the only way to force the connection with ApplicationIntent=ReadOnly to connect secondary replica?

texts:
I have no problem with setup "Read-only routing".

My point is if we use "Read-only routing". We need to add the "ApplicationIntent=ReadOnly" into our application connection string. That's beyond our DBA's role.

Is there any other way to let one application such as SAS to connect to the listener and the SQL AG will point it to the secondary?

Many thanks,


texts: Primary key with "NOT FOR REPLICATION" option

texts:
I have recently taken over a project, and I have discovered that in most tables the primary key has the property "NOT FOR REPLICATION".

I'm no DBA, but surely in most databases, a record without a primary key would be considered corrupted. The primary key of most of these records would almost certainly be used as a foreign key somewhere.

Was this an error on the part of the previous developer (who no longer works for the company), or is there some other logic involved? We don't even use replication in the production environment, so this doesn't actually affect anything serious anyway but I was wondering if there are any other side-effects of removing all these directives that I'm not aware of.

I haven't found many useful hits for a number of search terms related to this topic, so I'm fairly certain that this is just a silly error that I need to reverse, so this question is really to assuage my paranoia.


texts: In Postgres, How can I merge two tables?

texts:
I have a set of web services that all need updating with a new set of static data.

I can't use a distributed transaction, but would like to be able to ask each service to rollback it's changes if I detect something went wrong.

I aim to ask each service to backup the data I care about, and apply the update... then later ask the service to restore the backup if I need to recover.

To complicate things I may only be backing up some of the data in the table (let's say.. where the ID starts with "My."  so "My.123" will be backedup and restored but "YOURS.345" won't be changed by this process at all.

I'd like to make backup and restore functions that take a table name.

I was thinking of backing up tables with a generic version of the following code:

--=== BACKUP ===--
create table utilities.labpreferences_snapshot ( LIKE utilities.labpreferences EXCLUDING DEFAULTS ) WITHOUT OIDS;

-- copy data
insert into utilities.labpreferences_snapshot
select * from utilities.labpreferences where s.id like 'My.%';

When restoring, I am expecting that some rows may have been modified or deleted or new ones created. I want to make sure I don't break andy foreign keys referencing data in this table, but I don't expect any new foreign keys since the backup state was captured.

I could restore the data in the same way I copy it... but INSERT only works for data that was deleted... so.. I could delete all the data I want to replace..

--option1
--=== RESTORE ===--
-- possibly stop foreign key constraint checking?

-- remove master data
DELETE FROM utilities.labpreferences s where s.id like 'My.%';

-- replace snapshot data
insert into utilities.labpreferences
select * from utilities.labpreferences_snapshot;

-- restart foreign key constraint checking somehow?

but I worry about the foreign keys.

I could delete the added ones, add the deleted ones, and update the modified ones.

--option2
--=== RESTORE ===--

DELETE FROM utilities.labpreferences s where s.id like 'My.%' and s.id not in (select id from utilities.labpreferences_snapshot);

insert into utilities.labpreferences
select * from utilities.labpreferences_snapshot where s.id not in (select id from utilities.labpreferences);

Update ???  -- maybe something like http://stackoverflow.com/questions/3891758/how-to-update-one-table-from-another-one-without-specifying-column-names

The update looks hard.

I'm feeling a little out of my depth. Can anyone suggest a solution?


texts:
I need to check for deadlocks in my customer's system. He provided me with unfiltered logs.

Can I filter them after running the trace somehow? (I don't have direct access to his server)

It seems like the template can be applied only before running the trace.


texts: Clustered Index Scan when using greater than in WHERE clause

texts:
I have the following table

create table t1(
col1 varchar(255) NOT NULL,
col2 varchar(255) NULL,
col3 bigint NULL,
CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED
 (
   col1 asc
 )
)

CREATE NONCLUSTERED INDEX I_col3 ON t1(col3 desc)

This table has ~10000 rows with col2 always being populated and col3 has varying percentages of rows that are NOT NULL.

I am running the following query

DECLARE @number bigint
SET @number = 123456
SELECT col1, col2 FROM t1
WHERE col3 > @number

SQL is always generating a execution plan with a clustered index scan.

Now if I run the query as an adhoc query SQl does an index seek on I_col3 with a key lookup

SELECT col1, col2 FROM t1
WHERE col3 > 123456

The value being passed in the WHERE clause results in a small number of rows being returned (e.g. 3)

When running the ad-hoc query the execution plan shows the Estimated Number of Rows as 3.32. However, when I run the parameterized query it shows 2796 estimated rows.

If I add an index hint to the ad-hoc query it still shows 2796 estimated rows (not that I expect this to change) but it does do an index seek. When comparing the number of logical reads between the ad-hoc and parameterized query the
Parameterized Query:
Scan count 1, logical reads 89, physical reads 1, read-ahead reads 64, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ad-Hoc Query
Scan count 1, logical reads 8, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And when forcing the index using the index hint
Scan count 1, logical reads 12, physical reads 1, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I'm currently running SQl 2014 CP1-CU5. I've tried using trace flag 9184 to force SQL to use the old cardinality estimator which reduces the estimated rows to 1627 but still does a clustered index scan. I've also tried on SQL 2008 R2 and it has the same behavior as SQL 2014 with trace flag 9184.

In between each iteration of the query I am clearing the procedure cache and buffer pool.

I should also noted that I have also tried the following

Filtered INDEX on col3 WHERE NOT NULL
Filtered STATS on col3 WHERE NOT NULL

I've also done a full rebuild on the both the clustered and non clustered indexes and verified that stats are up to date.

Any suggestions or ideas?

Thanks again

UPDATE:
From reading through some links from Martin's answer I found the following blog post:

http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/

I confirmed that using sp_executesql or exec(@sql) does result in an index seek execution plan being generated and reused regardless of what the parameter value(s) are.

DECLARE @sql nvarchar(max)
SELECT @sql = 'SELECT col1, col2, FROM t1 WHERE col3 > @number'
EXEC sp_executesql @sql, N'@number bigint', @number = 123456789


texts:
This query produces the correct data :

select
       extract (hour from (tr.eventts-pr.eventts))*3600+
       extract (minute from (tr.eventts-pr.eventts))*60+
       extract (second from(tr.eventts-pr.eventts))*1000 ms
,pr.pid
from (      select max((FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')) eventts, pid
            from WC_PACKAGEREPORT
            where DESTINATION=154
             AND PID in(SELECT unique pid FROM WC_PACKAGEINFO
                        WHERE EXECUTEDTASK='ManualScan'
                        and (FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') >= (FROM_TZ(CAST(trunc(SYSDATE) AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')
          )
group by pid) pr

            inner join (

select max((FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')) eventts, pid
from WC_TRACKINGREPORT
where areaid=1444
and zoneid=68
and equipmentid=03
group by pid) tr

on tr.pid=pr.pid;

I now want to get the average ms taken so i try :

select
       avg(extract (hour from (tr.eventts-pr.eventts))*3600+
       extract (minute from (tr.eventts-pr.eventts))*60+
       extract (second from(tr.eventts-pr.eventts))*1000) ms
,pr.pid
from (      select max((FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')) eventts, pid
            from WC_PACKAGEREPORT
            where DESTINATION=154
             AND PID in(SELECT unique pid FROM WC_PACKAGEINFO
                        WHERE EXECUTEDTASK='ManualScan'
                        and (FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') >= (FROM_TZ(CAST(trunc(SYSDATE) AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')
          )
group by pid) pr

            inner join (

select max((FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')) eventts, pid
from WC_TRACKINGREPORT
where areaid=1444
and zoneid=68
and equipmentid=03
group by pid) tr

on tr.pid=pr.pid;

but i get a "not a single-group group function".

I believe I need to wrap the sql somewhere but I'm not sure where. Can any of you advise?

Tom


texts: Update all values for given key nested in JSON array of objects

texts:
I have a jsonb column data in a Postgres table my_table. It contains the following data:

[
   {"id":"1","status":"test status1","updatedAt":"1571145003"},
   {"id":"2","status":"test status2","updatedAt":"1571145323"}
]

I want to update the updatedAt key of all objects in that array using one query. I tried:

update my_table set data = data || '{"updatedAt": "1571150000"}';

The above query added a new object within the array like the following:

[
   {"id":"1","status":"test status1","updatedAt":"1571145003"},
   {"id":"2","status":"test status2","updatedAt":"1571145323"},
   {"updatedAt":"1571150000"}
]

I want the output like:

[
   {"id":"1","status":"test status1","updatedAt":"1571150000"},
   {"id":"2","status":"test status2","updatedAt":"1571150000"}
]

I also tried jsonb_set(), but that needs the second parameter to be the array index. I can't be sure of the count of JSON objects in the array.

If this can be solved with custom functions, also fine.


texts: How can Amazon Redshift be built based on PostgreSQL if Postgres is row-based?

texts: According to Amazon, Redshift is based on PostgreSQL, and is a column-oriented database management system. This looks to me as a very self-conflicting statement, isn't Postgres a row-oriented database?


texts: Are there tools for SQL-Server helping to add the semicolons?

texts:
Earlier versions of SQL-Server didn't recommend the use of semicolons to terminate sql-statements.

Beginning with sql-server-2008 examples show, that now Microsoft recommends terminating with semicolons.

There are some formating tools which format sql queries and add the semicolons. I think the basic function is somewhere in SMO.

The only problem is that these tools remove all the comments.

Any ideas how to preserve all comments and how to add all recommend semicolons?