texts: SQL Server Trace Stopped Messages Displaying Random Login Name

texts:
When traces are started and stopped we get messages similar to the following in the SQL Server error log:

  SQL Trace ID 2 was started by login "DOMAIN\loginA".

and

  SQL Trace stopped. Trace ID = '2'. Login Name = 'loginB'.

The problem I've noticed is that the loginB name doesn't always correspond to the loginA name that started the trace.

This has become a problem since we have started using Extended Events sessions more in our environments.  We schedule traces to start and stop as the service account but we cannot properly filter these messages out of our error reporting systems because loginB randomly changes to some other user.  We also can't reliably filter on trace ID because it could change.

My questions are:

Is this a known bug or is it expected behavior?  I have only found one article from someone posting evidence of this issue in their environment (http://www.360sqldba.com/2015/08/sql-error-log-shows-random-user-and.html).  I haven't found anything official from Microsoft.
Is there a way to reliably set the trace ID so that I can filter by trace ID rather than by login name?

I am seeing this happen in our SQL Server 2012 and SQL Server 2016 environments.


texts: Getting error while starting slave on MySQL slave./ Backups in MySQL 8.0.20

texts:
I want to set up below command for daily MySQL database backup.suppose We have more than 50 MySQL database in server.
I am using below command to take backup of all databases on server.
Note: I am using --add-drop-database because if I remove, I will get error if already db is there in case of doing refresh replication etc.
shell> mysqldump -u --single-transaction --master-data=2 --all-databases --add-drop-database --routines --triggers --events  -p > backup.sql
I will use backup.sql file for restoring on another server in below scenarios.
Scenario1:- In case of production server crash/disk corruption and in any disaster situation.
Scenario2:-  Suppose, I want to configure Master-slave replication.
Scenarios3:- In case of Point-in-Time recovery I will use this backup and binary logs which was created post backup. because i used --master-data option which will apply flush tables with read lock at global level to take binary log position and file(coordinates)
Note:- if I have Mixed table in db then I should use below flush tables with read lock because --master-data will use flush tables with read lock for small duration when dump is going to start.
due to --single-transaction,no read/writes are blocked once the dump started.
But the issue is, in MySQL 8.0+, they have removed access from user to drop mysql system database.we can't restore dump on another server if I want to restore. because, if I take fresh installed mysql server still there will be mysql system database. Refer documentation link
So I am using below command to mitigate the issue and fulfilling the above scenarios.
First I will take mysql database backup separately. using below command.
shell> mysqldump -u -p mysql  > users_backup.sql
and for user created database i will use below command.
shell> mysqldump -u --single-transaction --master-data=2 --databases --add-drop-database dbname1 dbanme2  --routines --triggers --events  -p > backup.sql
Question 1:- For above command --> What will be the steps if I have more than 50/100 databases? how we can solve this issue?
Question 2:- Do we have another options in MySQL 8.0+  to take all databases backup like we used to do before 8.0 and it should fulfill the scenario?
Before MySQL 8.0  we can use only below command to solve all issues along with above scenarios.
shell> mysqldump -u --single-transaction --master-data=2 --all-databases --add-drop-database --routines --triggers --events  -p > backup.sql


texts: MySQL queries taking very long/not finishing

texts:
I just started working with databases and have a problem with queries taking very long.

My table has the following form

CREATE TABLE table (column1 INT(10) AUTO_INCREMENT PRIMARY KEY,
column2 VARCHAR(255) NOT NULL,
column3 VARCHAR(255) NOT NULL,
column4 VARCHAR(255) NOT NULL,
column5 VARCHAR(255) NOT NULL,
column6 VARCHAR(255) NOT NULL);

ALTER TABLE table ADD UNIQUE entry (column2, column3, column4, column5);
ALTER TABLE table CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I have inserted about 10 million rows.

Simple queries including a cartesian product like

SELECT a.column1, b.column1
FROM table AS a, table AS b
WHERE a.column2 = ''
AND NOT a.column3 = b.column3

take about five minutes before the results start showing.
A more complex one which includes ordering by multiple columns runs about half an hour before it eventually causes my system to freeze. There are no other concurrent database operations.

I'm running MySQL 5.6.25 on Ubuntu Vivid and didn't change any settings. My machine is a 6x3GHz Phenom II with 8 GB RAM.

Is there a problem with my table/query? Are there any default setting I should change?

edit

The total size of my data is 1.25 GB.

I tested a few more queries.
Before, I set innodb_buffer_pool_size=4G and removed the UNIQUE CONSTRAINT.

SELECT * from table; ~ 8 sec.
SELECT * from table WHERE column2='xyz'; ~ 20 sec.
SELECT * from table WHERE column2='xyz' ORDER BY column1; ~ 50 sec.
SELECT * from table WHERE column2='xyz' ORDER BY column1, column3; > 3 min.

I noticed that a temporary file is created, which is larger than 10 GB for query 4.
When doing queries with cartesian products, my system freezes after a few minutes and this file is never created (as my disk space remains constant).

This is my first real-life database project and I don't know what performance I can expect. Do I simply need different hardware for this amount of data?


texts: Optimise Percona 5.7 my.cnf for a 32GB mixed nginx/php/mysql server

texts:
I have a dedicated server which I run my website from.  It's got 32GB of ram,   Xeon E5-1650 3.2Ghz (6 core, 12 with HT) and two 3TB drives in RAID 1.

I'm running Percona 5.7.18

99% of my tables are InnoDB

My ibdata1 file is 500MB.

It's mainly SELECT queries, but some pretty complex joins/queries.  With a proportion of INSERT/UPDATE too though.

The server spends some of its time resizing JPEGs that users have uploaded, so I need to be mindful of that when allocating memory etc.

Is there anything else I could tell you that would help?

I do plan to implement some decent webapp-level caching at some point, but would like to get the mysql config improved in the meantime.

The site runs pretty well on normal traffic days, but can struggle a bit on busy days (like today...)

I'm a sole dev, so full-stack and spread thin, and will happily admit that my DBA skills are fairly poor.  I can write a decent query, but I know nothing about server set up.  What's in my my.cnf has been cobbled together from various different sources over the years, and I'm guessing it's far from optimal!

[client]
default-character-set           = utf8mb4

[mysql]
default-character-set           = utf8mb4

[mysqld]
user                            = mysql
default-storage-engine          = InnoDB
socket                          = /var/lib/mysql/mysql.sock
pid-file                        = /var/run/mysqld/mysqld.pid

datadir                         = /var/lib/mysql

skip-name-resolve

max-allowed-packet              = 16M
max-connect-errors              = 1000000

# is this a silly thing to do..?
tmpdir                          = /dev/shm

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links                  = 0

character-set-client-handshake  = FALSE
character_set_server            = utf8mb4
collation_server                = utf8mb4_unicode_ci

ft_min_word_len                 = 2
group_concat_max_len            = 4096

max_connections                 = 500

tmp-table-size                 = 32M
max-heap-table-size            = 32M
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

join_buffer_size                = 2M
sort_buffer_size                = 2M
read_rnd_buffer_size            = 2M

slow-query-log                  = 1
long-query-time                 = 1
slow-query-log-file             = /var/lib/mysql/slow_log

log-error                       = /var/lib/mysql/mysite.err

max_allowed_packet              = 256M

query_cache_size                = 268435456
query_cache_limit               = 1048576
query_cache_type                = 1

innodb_buffer_pool_size         = 12G
innodb_buffer_pool_instances    = 12
innodb_read_io_threads          = 12
innodb_write_io_threads         = 12
thread_pool_size                = 24

sql_mode                        = ""

[mysqld_safe]
log-error=/var/lib/mysql/mysite.err
pid-file=/var/run/mysqld/mysqld.pid

Any help/guidance would be hugely appreciated, I love to learn and more importantly I need to learn!

Thank you!


texts:
My application has been logging dead lock exceptions return from sql azure to my .net application: Transaction (Process ID 152) was deadlocked on lock resources with another process....

I would like to investigate this further to see if I can prevent those deadlocks from happening.

How do I get the details about those past and future deadlocks? I remember that the sql server profiler is able to extract some deadlock events with some xml that shows all the queries involved in the dead lock. That is what I'm after.

However, since this runs on Sql Azure, there is no profiler.


texts: Any way to maintain a partial index on only the highest element in Postgres?

texts:
I need to

SELECT MAX(created_at) FROM someTable

whenever my application starts up. The table is enormous (billions of rows). The index on created_at that I created to make this query fast is using up multiple gigabytes. Is there a way to create some kind of partial index only on the highest element? Or any way to make this take up less space? Or an alternate approach entirely? All I ever need to query on this column is the maximum element.


texts: Performance gain by moving a large table from a database and referencing with a synonym

texts:
We have SQL Server 2008 R2 database which is currently about 80Gb.

About 10Gb of this is down to one table, and I wondered if there would be a performance gain if I moved this table into it's own database, and created a referencing synonym so that existing queries and SPs remained functional.

This database is ripe for an overhaul anyway, so that may be the obvious answer but I wondered if the synonym idea would be a quick win.


texts: MySQL Replication - Resync master after slave taking over

texts:
A few days ago, we've had to stop every machine for some maintenance. Two of these were a MySQL master-slave. After the restart, the master didn't get the service back online and, due to the critical nature of the database, the slave took over, basically functioning as a normal, non-replicated service.

There were no changes to any of the my.cnf configuration files. The slave received various UPDATE and INSERT commands, so what will happen if I simply start the master back again? Will it manage to sync back? If not, how should I proceed?


texts:
I am trying to drop an index that has a dot(.) in its name and can't seem to do it (I'm logged in as the SYSTEM user, which owns the index).  Any ideas?

SQL> select index_name,owner,status from all_indexes where owner<&gttable_owner;

INDEX_NAME                     OWNER                STATUS
------------------------------ -------------------- --------------------
IX_isbn_send_h.D_STAT          SYSTEM               VALID

SQL> drop index IX_isbn_send_h.D_STAT;
drop index IX_isbn_send_h.D_STAT
                          *
ERROR at line 1:
ORA-01418: specified index does not exist


texts: Solaris 10 - Why shell limits needs to be increased to run oracle database?

texts:
Shell limits affect shell and scripts running shell like bash.
Oracle's requirements (https://docs.oracle.com/cd/E11882_01/install.112/e48357/pre_install.htm#SSDBI1182 - section 2.10) is to modify these values to a given minimum and to set them to unlimited in production environment.

If the oracle database run a set of binaries, how would changing ulimits affect the database?


texts:
I want to create a MariaDB 10.4 multi-master cluster on four Ubuntu 20.04 nodes. As you know Galera manager provides a web GUI interface for the cluster and it's handy for monitoring and add or remove nodes.
But because I am not using Galera Manager on the amazon ec2 platform, I encountered some errors, and here is what I've done:
1- I have ubuntu 20.04 with MariaDB 10.4 as node1.
2- On the node1 server, I have enabled root to log in over ssh.
3- On Galera manager which is on another Ubuntu server, I have imported root ssh keys to node1, so Galera Manager can access node1 server over ssh with root users.
4- On the Galera manager console I have created a cluster as you can see here:
5- Then I added node1 server to the cluster as you can see here:
6- You can see the complete deployment log as you can see here:Full Log
7- After node installation finishes node status will become unknown as you can see here:
At this point, you cannot start or stop the node, and as you can see log files are empty! and the whole cluster becomes useless, no matter how many nodes you add they all become unknown.
I know that I can create a multi-master Galera cluster without the Galera manager, but as I mentioned before, I need it for monitoring and scaling.
If we cannot fix this problem, please tell me is there any alternative for managing and monitoring the Galera cluster through a web interface other than ClusterControl which is so expensive??


texts: Grouping and counting rows by existence of matching criteria in any of several columns

texts:
Disclaimer: I've inherited a database which is probably not quite as "normal" as I would like, and I'm limited to Jet/MS Access 2010 for my db backend and interface, as well my choice of reporting software (currently Style Intelligence, which has its own data interpretation interface (supports standard SQL, etc.). I'm open to some data restructuring if necessary.

I have several tables of assessment data from which I need to count rows that "pass" or "fail" the assessments (one row = one assessment). Each row has a series of columns which contain the answers YES, NO, NA or null, and these answers comprise the assessment. A NO value in any of the answer columns constitutes a fail. I need a count of rows that contain NO in any of these columns, and then a count of the remainder (or, no "No" values in any of the answer columns. I don't need to count the number of NOs on one row, just group and count by the existence of at least 1 NO answer in any of the given columns. There are other tables which store assessment data in a similar structure, but instead use 1, 2 and 3 as the "yes, no, NA" answers, because they're storing the value from an MS Access form's option group.

Here are some examples of rows that I have, and how they would be grouped for counting:

AsmtID  Q1      Q2      Q3      Q4      Q5      Q6      Q7      Desired grouping

3       NO      YES     YES     YES             NA              FAIL
4       NO      YES     YES     YES     YES     NO      NA      FAIL
5       YES     YES     YES     YES     YES     YES     YES     PASS
6       YES     YES     YES     YES     YES     YES     NA      PASS

It's worth noting that I'm working with a very small amount of data, certainly no more than 200 rows from any one table at a time, so computational efficiency isn't a big concern for me. My best guess would be some routine which steps through each column on a row and then returns Pass or Fail based on the first NO value found. I would really appreciate any insight on how to interpret this data. Thank you in advance!


texts: Help with Postgres 9.1 data import (is 4x expected data size, 4x slower than MySQL, and index still needed)

texts:
I have 25 billion <int, int, float> rows that I'm trying to import into Postgres, and after 77% of the data being imported, the Postgres data folder is taking up 840GB, about 4x the storage requirements for that many rows at 12 bytes each.  Additionally, importing is taking 4x longer than my same import on MySQL (as described in MySQL MyISAM index causes query to match no rows; indexes disabled, rows match).

Here are my commands:

mydb=# CREATE TABLE mytable (id1 int, id2 int, score float)
$ psql mydb -c "COPY mytable (id1, id2, score) FROM 'file_000'"
$ psql ...
$ psql mydb -c "COPY mytable (id1, id2, score) FROM 'file_099'" # 100 files total

I'm running Postgres 9.1.  There are no other tables in the database.  This is not a production environment. The files are TSV text files. The only output from each COPY command is something like "COPY 256448118" -- at least until I ran out of disk space.

Am I doing something wrong here, or is this the expected behavior?

Subquestion 1: Where is this extra storage overhead coming from, and can I avoid it?

Update: It looks like there is a HeapTupleHeader of 23 bytes on each row, so that probably explains this overhead (source: StackOverflow post). Any way to avoid this?

Subquestion 2: If storage requirements are indeed 4x that of expected size, can I speed up importing (i.e. with some configuration change, etc)?

Subquestion 3: I need an index on id1, so what will storage requirements be for that during and after creation (I planned to run CREATE INDEX id1x ON mytable (id1))?


texts: Return total number of rows and selected (aggregated) data

texts:
I have a function which is selecting some data from a table. I would like to return the selected data and the total number of rows in that table.

How can I do that or how can I get the same result in the most efficient way?

I have tried few things and I ended up with the code below, now this is the format that I want, but count(*) over () as total_count will return 1 all the time, what I need it to return is the total number of rows from that records select.

SELECT
row_to_json(selected_records) as data
FROM
(
    SELECT
    count(*) over () as total_count,
    array_to_json(array_agg(row_to_json(records))) as data
    FROM (
        SELECT
            sum(entrances) as entrances
        FROM report_la
        WHERE profile_id = 3777614
        GROUP BY landing_path_id
        limit 10 offset 0
    ) records
) as selected_records

Updated, the code below is producing the result that I want, it will be nice if I can hide that total_count column from the records select

SELECT
row_to_json(selected_records) as data
FROM
(
    SELECT
    min(total_count) as total_count
    ,array_to_json(array_agg(row_to_json(records))) as data
    FROM (
        SELECT
            sum(entrances) as entrances
            ,count(*) over () as total_count
        FROM ga.report_la
        WHERE ga_profile_id = 3777614
        GROUP BY landing_path_id
        limit 10
    ) records
) as selected_records


texts: Timeout for Always On Synchronous Commit Mode

texts:
For SQL Server Enterprise's Always On availability groups, how can you configure the timeout for Synchronous Commit mode?  Is there a maximum?  Can it work, for example, between continents?
Thanks


texts: How to run update query on multiple table via joins simultaneously on postgres?

texts:
In MySQL, I can do a query like this

UPDATE
    prd_sectionshapename  se
inner join
    prd_shape s
ON
    s.id = se.shape_id
SET
   se.company_shape_name = 'ABC',
   s.name_en = 'Another name'
WHERE s.serial_number = '1234ST';

In Postgres, I need to split up into 2 queries

UPDATE prd_shape AS s
SET name_en = 'Another name'
WHERE s.serial_number = '1234ST';

UPDATE prd_sectionshapename AS se
SET company_shape_name = 'ABC'
FROM prd_shape s
WHERE se.shape_id = s.id and s.serial_number = '1234ST';

My question is it possible to do it in 1 query just like MySQL?

Note that other postgres update join answers I found on the internet typically show that the update effect happens only on a single table even though a JOIN is used.

My purpose here is to update multiple tables in one query. Not just one table.


texts: MYSQL ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref used (client option 'secure_auth' enabled)

texts:
when I tried to restore all database dump which is in 5.0 version to 5.6 version, it got restored and after that when I tried to reconnect, am getting the following error

ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref used (client option 'secure_auth' enabled)..

I have tried adding the following lines in My.ini and restarted the service,but the issue persist till.

skip-grant-tables
The following link says its a bug in MYSQL.

https://github.com/santisaez/powerstack/blob/master/packages/mysql/mysql-powerstack-secure_auth.patch

Do anyone have any fixes for this solution?


texts: Is it (always) necessary to backup a certificate with private key?

texts:
I'm starting a project to encrypt database files. I've created a master key and a certificate the following way:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master_K3y';
GO
CREATE CERTIFICATE MY_DB_CERT WITH SUBJECT = 'Some subject here';
GO

I have two options to backup the certificate: with or without a private key. I have no idea where this private key comes from, as I did not provide one. Perhaps it was generated for me when creating the certificate?

In any case, my first backup statements did not provide a private key clause.

USE master;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Master_K3y';
GO
BACKUP MASTER KEY TO FILE = 'MASTER_KEY.bak'
ENCRYPTION BY PASSWORD = 'yeK_r3tsaM';
GO
BACKUP CERTIFICATE MY_DB_CERT TO FILE = 'MY_DB_CERT.bak';
GO

I am wondering if that is enough to restore everything in a working state, say when moving to a new server? If not, what is the use-case for backing up a certificate without a (the?) private key?


texts: how do i use "?" as variables for my values so that i don't get hacked?

texts:
I was wondering how do I use question marks so that I don't get hacked because when I use this:
SQL = "INSERT INTO users (id, username) VALUES ( " + message.author.id + ", " + message.author.username + ")"

it gives me an error: Unknown column '(my username)' in 'field list'
I know you will say that there is already a post like this, but I am kinda a beginner still so I don't understand what they are saying


texts:
I have always-on group running on 4 node cluster. Each DC has 2 node running on it

Data Center1:

Node1: Sync Replica - Automatic failover
Node2: Sync Replica - Automatic failover

Data Center2:

Node3: Sync Replica - Manual failover
Node4: Async Replica - Manual failover

While DR testing, we brought down nodes running on DC1 and I tried to bring Always-ON group on node3 (sync replica), however when I tried to failover, it gives a message that there will be data loss. Would there be any data loss if I failover manually to node3 even though it set as sync replica?


texts:
How the records are updated in an accounting system i.e., what is the standard practice?
do you keep all the records and do the calculations on the fly or you add and subtract in each time from a final amount say "$current_balance".

The first approach of doing the calculations on the fly i.e, summing up all the deposits and subtracting all the expenses is a sure way to get the result but I can see a definite scalability problem as the number of rows grow.

On the other hand accumulating in a variable say "$current_balance" and just updating it has its shortcomings as well.

So what is the recommended  practice to approach this problem?


texts: How to properly change "innodb_buffer_pool_size" value for mysql?

texts:
I have following environment:

# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
# rpm -qa | grep ^mysql-community-
mysql-community-common-5.5.48-2.el7.x86_64
mysql-community-libs-5.5.48-2.el7.x86_64
mysql-community-client-5.5.48-2.el7.x86_64
mysql-community-release-el7-5.noarch
mysql-community-server-5.5.48-2.el7.x86_64
#

I need to adjust innodb_buffer_pool_size value for mysql, however last time when I try to do that, something went south and I end up restoring database from backup( database is ~100G, so ideally I wouldn't want to go that route again.

steps that I took last time:

stop mysqld
adjust value of innodb_buffer_pool_size inside of my.cnf
killed ib_logfile*
start mysqld

What did I do wrong? What should I do different this time?


texts:
I have SQL express server with database that is used by one application. I am trying to optimize the DB but am not sure if the DB is OLTP or OLAP.

There is no analysis server installed, the DB is inside the SQL Server instance hence it  should be OLTP, but when I run the read/write ration on the DB I have the following:

          Reads    Writes    Read%   Write %

DB file   400 000   75 000   85%     15%

DB log        250   30 000    1%     99%

Standard OLTP DB should have more reads than writes, right? This makes me suspect that maybe It is OLAP DB.

I am asking this regarding the use of HyperThreading technologies, MAXDOP setting and whether to configure cache as write-through or write-back among other things.

The query I am using is as follow:

SELECT DB_NAME(DB_ID()) AS [Database Name] ,
[file_id] , num_of_reads , num_of_writes , num_of_bytes_read , num_of_bytes_written ,
CAST(100. * num_of_reads / ( num_of_reads + num_of_writes ) AS DECIMAL(10, 1)) AS [# Reads Pct] ,
CAST(100. * num_of_writes / ( num_of_reads + num_of_writes ) AS DECIMAL(10,1)) AS [# Write Pct] ,
CAST(100. * num_of_bytes_read / ( num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10, 1)) AS [Read Bytes Pct] ,
CAST(100. * num_of_bytes_written / ( num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10,1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) ;

Any thoughts from senior DBAs?


texts:
I have a problem on this query, how do I use the result of the query contained in CostoUnitario field.

I see better explain, after: ... as CostoUnitario..

I should insert ,CostoUnitario* Quantita

How can I do?

CODE:

SELECT Capitolo.Descrizione,SottoCapitolo.Descrizione,
SottoCapitolo.Immagine,
SottoCapitolo.DescrizioneEstesa,
SottoCapitolo.Quantita,
ROUND(((SottoCapitolo.CostoUnitario)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeMargine)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeUtile)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeSpeseGenerali)),0)
 as CostoUnitario
FROM Preventivo inner join Capitolo on Preventivo.IdPreventivo = Capitolo.IdPreventivo inner join SottoCapitolo on SottoCapitolo.IdCapitolo = Capitolo.IdCapitolo order by Capitolo.Descrizione

IMAGE:


texts: Mysql - tablename.ibd file is huge in size

texts:
I'm using MySQL 5.6 and innodb file per table has been enabled.

I noticed that one file named as tablename.ibd is 211GB, then query the information schema there it's showing 21GB.

I have done a huge delete operation on this table. And daily archiving some old data.

Is this because of that?
Optimize table - command is the only way to reclaim the space?


texts: How do I run a batch file from a scheduled Job via SQL Server Agent

texts:
I'm attempting to run a (Windows) batch file from within a SQL Server agent job under SQL Server 2008 R2. In the Job Steps, I've created a new step, set its type to "Operating system (CmdExec)". I initially tried to put just the batch file name in the "Command" field. It gives an error that it can't find the file.

Then I tried: cmd.exe c:\test.bat

This starts, but never completes (and it doesn't look like it's actually working anyway).

I tried wrapping the batch file name in quotes: cmd.exe "C:\test.bat" but that also doesn't make any difference.

What is the magical combination to get this to work?


texts:
I have the following problem:
I can connect to an Orcale database as normal user or as sysdba:

sqlplus myuser/myPw@myDB

sqlplus "sys/sysPw@myDB as sysdba"

But when another (Windows-User) is logged on, he could only use

sqlplus myuser/myPw@myDB

With

sqlplus "sys/sysPw@myDB as sysdba"

he gets the error:

  SQL*Plus: Release 9.2.0.1.0 - Production on Do Sep 29 12:10:07 2011

  Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

  ERROR: ORA-01031: Nicht ausreichende Berechtigungen.

I even tried full qualified paths to sqlplus with the same result.

Edit:

I see the possibility that different windows users use different (windows-) environment variables, especially different PATH variables.
Further I see the possibility that some windows dlls could have wrong security settings and can not be accessed by one of the users.

But I do not see, how this could make an Oracle user, ( SYS in this case ) behave differently.

2nd Edit:

I checked that the Path variable is a system variable, i.e. both windows logins use the same paths.


texts: MySQL: Is there a way to update a field so that all foreign key related fields are updated as well?

texts:
I'm assuming the answer to this question is yes but it is hard to Google this type of question and not get overly ambiguous results.

I am working with a database that was dumped from a Magento instance - EAV architecture. It stores orders with the column entity_id and uses this field throughout the DB, something close to 20 tables by my count. Magento uses foreign keys on this entity_id column to keep track of its relationship to other tables.

My Question: Is there any way to run an update query so that the MySQL DB will automatically update every foreign key related column in the database? I need to "shift" about 1000 of these incremental entity IDs in order to successful merge orders from another Magento instance. Thank you.

Example of update: Change entity_id from 9567 to 10567.


texts: Is SQL Server Browser Service compulsory for remote access?

texts:
We have a Windows machine with MS SQL Server running on it and we want to connect to the database from a script running on a Linux machine. Can we just configure Sql Server to listen to a certain port and connect to it by specifying IP, port, database name, user and password, like we would with MySql? Or do we need to run SQL Server Browser Service to achieve that? Description of what this service actually does is vague, I don't get it. We have no reason to do things like listing databases or doing any anything extra. Just connect and select.

TCP/IP connection is already enabled in the SQL Server, there is no firewall installed, ping is successful, yet the port doesn't seem to be listened to. So I was wondering if that service is what is missing.


texts:
Using TSQL, is there any way to find out SQL server alias configured at configuration manager? Such as follows?

Thanks!


texts: How can a database be normalized when an optional field causes denormalization?

texts:
Suppose you have a warehouse full of widgets.  Each widget in the warehouse is stored in a specific identifiable location within the warehouse.  You might have a schema which looks like this:

A widget may also be (optionally) located on a cart in the warehouse, which could lead to a schema like this:

However, a problem arises because a cart can also only be located in one location within the warehouse, which turns the prior schema into something like this:

Howver, this schema is denormalized and can result in anomalies, such as if the data was:

widgetID  storageLocationID  cartID
========  =================  ======
1         foo                A
2         bar                A

Although both widgets are located on the same cart, the database shows them in different locations which is not possible.

If a cart was required, it would be relatively simple to address this normalization issue by simply removing the storageLocationID from the Widgets table.  However, because a cart is not required, that solution will not work.

How can this data be structured to eliminate the anomalies?


texts: Maximum SQL Server memory set to 12 GB, but it still goes over 12 GB. How do I prevent SQL Server going over max memory limit?

texts:
User 'mrdenny' wrote that after you set SQL Server maximum memory...

  Now this isn't going to limit all aspects of SQL Server to that amount of memory. This only controls the buffer pool and the execution plan cache. Things like CLR, Full Text, the actual memory used by the SQL Server exe files, SQL Agent, extended stored procedures, etc. aren't controlled by this setting.

My problem is that my server is going over the set 12 GB memory and if things like CLR, Full Text, and Server exe files taking additional memory.

How do I configure SQL Server, so it does not go over the set max limit?

I was told there was a way to do it, but I cannot find a solution after searching on Google.


texts: Sybase Database : least permission/roles to extract ddl

texts:
I am currently using Sybase Central 15.x. In which I have a database, a login and a User.
Here I need to give permission(s)/role(s) to a user by which it able to extract ddl of database.
There I found that if I give sa_role then user can extract ddl. But as I know sa_role make a user as a super user(sa). So I need to know that there exist any other permission/role to do the same task.


texts:
I am trying to export schema with below command using expdp in Oracle 18c XE , the schema uses tablespace  named "testtablespace"
expdp myschema/*******@//localhost:1521/XEPDB1 schemas=myschema directory=DATA_PUMP_DIR dumpfile=myschema.dmp logfile=myschema.log

the tablespace "testtablespace" has maximum size of 100MB and currently 70MB of it is utilized , upon running above command I get error
ORA-01654: unable to extend index TESTTABLESPACE.SYS_MTABLE_0000357BA_IND_2 by 8 in tablespace testtablespace

When I increase size of tablespace size the issue is fixed , it seems like during export the tablespace of schema is being used for some temporary data storage
Is there a way that I can specify a temporary tablespace to be used instead of my schema tablespace so that even if my schema tablespace is completely full it can still export schema using expdp without any issue?


texts: How to minimize full ms-access window in vba?

texts:
I have some code to minimize like docmd.minimize but it does not work.

Please let me know how to minimize whole window of access in vba?


texts:
I'm working on setting up a two-node PostgreSQL 9.4 environment. Right now I have test instances running on port 5532 on two identical Centos servers. node1 is correctly sending logs to a shared directory, and node2 is a hot_standby, and it's correctly reading and processing those logs.

I want to test failover. Here are the steps I followed:

issue a checkpoint on node1 (run psql as postgres user, type "checkpoint;")
stop the instance on node1 (systemctl stop postgresql-9.4-test.service)
promote the instance on node2 (pg_ctl -D path_to_directory promote)

All of these steps seems to work. But when I try to create a table on node2, I get "ERROR:  cannot execute CREATE TABLE AS in a read-only transaction" , and if I look at the settings for node2, it tells me that hot_standby is on.

The only change that I can see in node2's directory is that there's now a zero-length file called "promote".

What am I missing? I don't see anything in the documentation about any other steps to follow in order to promote a standby server.

Edited to add: select pg_is_in_recovery() returns true.

I didn't create a trigger file because that's not supposed to be required if you're using pg_ctl promote.


texts:
I have two tables with same column.

First table:

DESC orders;
Field           Type       Null Key      Default    Extra
order_id        int(8)     unsigned      zerofill   NO  PRI (null)auto_increment
customer_id     int(10)    unsigned      YES        MUL (null)

Second table:

DESC items;
Field            Type      Null Key      Default    Extra
item_id          int(11)   unsigned      NO PRI (null)  auto_increment
order_id         int(10)   unsigned      NO PRI (null)

Based on tables above order_id have different data precision.

But upon checking on information_schema database the precision is identical

SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.NUMERIC_PRECISION, t.ENGINE from information_schema.COLUMNS c
INNER JOIN information_schema.TABLES t on t.TABLE_NAME = c.TABLE_NAME
WHERE c.TABLE_SCHEMA = 'database' AND c.COLUMN_NAME = 'order_id' and c.TABLE_NAME in ('orders','items');

The result was.

TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME DATA_TYPE   NUMERIC_PRECISION   ENGINE
database         items      order_id    int         10                  MyISAM
database         orders     order_id    int         10                  MyISAM

Which of them is accurate? The one displayed in DESC table of the result from information schema? Thank you.


texts: Azure SQL - Setting up permissions for users

texts:
I'm busy playing around within Azure SQL and have setup replication from my on-prem DB to the Azure DB for some reporting. I would like to ensure as part of this we setup the security correctly. In this instance I have a "Team" who do the BI/BO reporting (lets call them BIBOTeam). I would like them to have Read only access (db_datareader) on the Synced data, but they will need to create some Views (and potentially tables) on that database for their BI system.

They don't want to create the views in the "source" database as it's a vendore provided system.

I'm assuming schemas/roles is the way to go? I have an AD group called [GRP BIBOTeam] which is currently setup correctly and they can log into the DB and only read data in the Synced database.

Would love some guidance on what path is best to manage this access

Cheers


texts:
How to perform mongo DB dump and restore in a single command, by providing source and sink DB and collection name separately?

I understand we can do mongodump and then mongorestore but if I need to this on an intermediate machine, this will mean I will need to wait for mongodump to complete before I can start mongorestore manually again. Is there a way to do it in a single command?


texts: Postgres query optimization: why could two very similar join tables have vastly different querying times?

texts:
I'm struggling with optimizing the performance of a single query, which is sometimes taking up to 180s for certain tenants. When I re-run this later in pgadmin on the production database server (using EXPLAIN ANALYZE), these are executing in <0.2s. If not on the first try, then at least on the second or third rerun.

My first conclusion is that a big factor is whether the table (or at least the dataset I'm accessing) is currently held in cache. But I'm wondering if there's other options besides increasing the memory on the database server to improve the performance, i.e. whether there's something structurally bad with the query or the indices.

The query is a fairly simple multi-table join, here's a slightly simplified (projection only), anonymized version of it:

SELECT results.id, result_details.title, seven.info_a, oscar.info_b
     FROM results
LEFT JOIN result_details ON results.details_domain_id = result_details.domain_id
                        AND results.tenant_id = result_details.tenant_id
LEFT JOIN seven          ON result_details.a = seven.domain_id
                        AND result_details.tenant_id = seven.tenant_id
LEFT JOIN oscar          ON result_details.b = oscar.domain_id
                        AND result_details.tenant_id = oscar.tenant_id
WHERE results.tenant_id=123

So the basic structure is that there's four tables involved: I want all results (golf_echo in the explain) for a certain tenant. Each result maps to a single entry in result_details (golf_india in the explain). This then gives me the ID of two other tables seven and oscar, to which there's also a single entry (or rarely none) each.

This is the output of the EXPLAIN ANALYZE command: http://explain.depesz.com/s/u1bT
From what I understand, the main portion of the time (90%) is spent on the Bitmap Heap Scan of oscar. My primary question is why that might be, considering the almost identical join for seven being so much faster, using an Index Only Scan.

It's worth noting that seven and oscar have the same basic schema (artificial primary key, a tenant foreign key, a tenant-specific domain ID, followed by various data columns only used in the projection):

id | tenant_id | domain_id | data_1 | data_2 | data_3 | ...
---+-----------+-----------+--------+--------+--------+------

Both of them have almost the same three indices:

1. btree (tenant_id)
2. breee (tenant_id, domain_id)
3. btree (tenant_id, domain_id, other_data_1, other_data_2)

These are obviously not particularly good indices (most are ORM defaults), but I'd like to understand the problem instead of making random guesses how to change these.

They also have almost same table size: seven has ~1GiB, oscar has ~1.2GiB, but seven has 6.5M rows and oscar has 3.5M rows. So seven (the 'slower one') has slightly less data, but twice as many rows.

In the vast majority of cases, a row in the first table matches to exactly one row in the second, third and fourth table. In very rare (but supported) cases, the second, third or fourth table might not have a matching record. That's why these are LEFT and not INNER JOINs.

The database server is running Postgres 9.4.6, on Heroku (so I don't have direct access to most of the tuning parameters).


texts:
While troubleshooting an issue in my company I updated all the files in the table instead of the specific file that needed to be tested using.
create table _file_*date*_BAK as select * from _file;
update _file set category = 'null';
commit;

When trying to fix it I tried to pull from my backup file I got the error
update (select a.category as OLD, b.category as NEW

 from _file a inner join _file_*date*_BAK b on a.filecod = b.filecod) t

 set t.old = t.new;

Error report -
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

not sure why that script didn't work.
Any help would be appreciated


texts: SQL Server 2012 Availability group disk configuration options

texts:
Background
I am quite far into the process of setting up a 2 server SQL Server 2012 Always on availability cluster to provide redundancy with a readable secondary replica of all our databases.
Since the hardware was ordered we've had a professional DBA audit our database and he's suggested separating our data, logs and tempdb. Unfortunately we do not have the capacity for the ideal configuration he's suggested. I therefore need feedback on my ideas for a suitable compromise...
Current setup
single server has 8 hard drive slots. We have a 2 disk RAID 1 for the OS, a 4 disk RAID 10 for data, logs and tempdb and 2 spares. I want to change this to increase performance.
Proposed setup
2 servers, each with 8 slots. Synchronous replication in the AG with auto-failover and the secondary to be readable for backups and reporting.
OS leave as is, a 2 disk RAID 1
tempdb has the most stalls by a long way so first off I'd like to put that on a separate drive, a single SSD for optimal random reads and writes.
data The read/write ratio of our data files is about 12:1 so read performance is key here. Ideally for this drive we would use RAID 10 or 0+1 with 4 disks for redundancy but we don't have enough slots. As we have the redundancy and auto-failover of the AG (the data is replicated on an identical drive on another server) I'm proposing a 2 disk RAID 1.
logs We have 3 slots left and need optimal write performance. Again, redundancy is not an issue as the 2nd host in the AG will have a replica of the logs. I am leaning towards a 2 disk RAID 0 here to avoid the write overhead of RAID 1.
We would then have a slot spare for backups and anything else non-critical.
Question

Should the log disk be RAID 1, RAID 0 or other?
Is the overall solution above the best option for me? If not, why


texts:
I have a simple table with 3 columns:

(cid, from, to) -> (int, datetime, datetime)

The difference between dateFrom and dateTo should be >= 1 month. And unfortunately i have some corrupt data which do not satisfy this condition.

INPUT:

An example data (cid - from - to) :
8 - 01.05.2012 - 01.09.2012
8 - 02.09.2012 - 10.09.2012
8 - 11.09.2012 - 18.09.2012
8 - 18.09.2012 - 02.10.2012
8 - 03.10.2012 - 04.10.2013

DESIRED OUTPUT in a temp table:

I want to merge the dates whose interval is less than a month into 1 row:

8 - 01.05.2012 - 01.09.2012
8 - 02.09.2012 - 02.10.2012
8 - 03.10.2012 - 04.10.2013

So everything between 02.09.2012 - 02.10.2012 is merged into one row.

Is this possible using only T-SQL? (even without a cursor)


texts: Does mysqldump use in memory pages during backup?

texts: I mean when backup command is issued to database, does backup process read all data pages from disk or use in memory pages?


texts: Join query with different column types in "on" part

texts:
In need to make a JOIN on 2 tables based on columns which have different column types.

On table A, I have a DATETIME field and, on table B, I have a DATE and a TIME field, which combined would match the DATETIME field on table A.

What would be the recommended syntax for best performance on such join clause?

BD: MySQL 5.5.43-0+deb8u1-log

PD: Any extra info needed?


texts: Filtered Index hint rejected by SQL Server

texts:
I have a filtered non-clustered index that SQL Server is not using. I'm pretty sure the optimizer is doing the right choice but I would like to force it to run with that index so I can compare the plan and see why it's more expensive.

I've removed everything from the query and I'm just selecting the column that is being filtered.

Index definition:

CREATE INDEX idx_all
    ON tbl_test (CommentDateTime, name)
    INCLUDE (comment, CommentByType)
    WHERE CommentByType='INT';

The query I'm trying to run is:

SELECT CommentByType
FROM tbl_test WITH (INDEX (idx_all))
WHERE CommentByType='INT';

And SQL Server is returning the following error:

Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET FORCEPLAN.

I've read a lot about the column filtered being included either in the key or as include column but nothing helps.


texts:
Have this query that is awfully slow:

SELECT bdp.id
FROM bd_products bdp JOIN bd_productwords bdpw
ON bdp.id = bdpw.productid JOIN bd_words bdw
ON bdpw.wordid = bdw.id
WHERE bdp.price > 0
AND bdw.word = 'nike'
ORDER BY bdpw.productid DESC

It returns 18217 rows and takes around 1,4 seconds to run :(

Removing the ORDER BY doesn't change the execution time. It stays around 1,4 seconds.

But if I remove the bd_products part like below, execution time improves to just 0,005 seconds.

SELECT DISTINCT bdpw.productid
FROM bd_productwords bdpw JOIN bd_words bdw
ON bdpw.wordid = bdw.id
WHERE bdw.word = 'nike'

It's not the bdp.price part that's the problem. Removing it like below doesn't change the execution time. It stays around 1,4 seconds.

SELECT bdp.id
FROM bd_products bdp JOIN bd_productwords bdpw
ON bdp.id = bdpw.productid JOIN bd_words bdw
ON bdpw.wordid = bdw.id
WHERE bdw.word = 'nike'
ORDER BY bdpw.productid DESC
LIMIT 100

The table bd_products contain 2,0 million rows. bd_productwords contain 16,1 million rows and bd_words contain 0,4 million rows.

Any ideas for why execution time is so slow when I include the bd_products part?

Table columns:

bd_words: id (int), word (varchar)
bd_productwords: id (int), wordid (int), productid (int)
bd_products: id (int), name (varchar), description (varchar), price (int)

Indexes for bd_products:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
bd_products 0   PRIMARY 1   id  A   1837288 \N  \N      BTREE
bd_products 1   Price   1   price   A   55675   \N  \N  YES BTREE
bd_products 1   Imageuploaded   1   imageuploaded   A   2   \N  \N  YES BTREE
bd_products 1   BrandId 1   brand   A   9669    \N  \N  YES BTREE
bd_products 1   BrandId 2   id  A   1837288 \N  \N      BTREE
bd_products 1   Id  1   id  A   1837288 \N  \N      BTREE

Indexes for bd_productwords:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
bd_productwords 0   PRIMARY 1   id  A   15757529    \N  \N      BTREE
bd_productwords 1   WordidProductid 1   wordid  A   1313127 \N  \N  YES BTREE
bd_productwords 1   WordidProductid 2   productid   A   15757529    \N  \N  YES BTREE
bd_productwords 1   ProductidWordid 1   productid   A   5252509 \N  \N  YES BTREE
bd_productwords 1   ProductidWordid 2   wordid  A   15757529    \N  \N  YES BTREE
bd_productwords 1   WordId  1   wordid  A   1125537 \N  \N  YES BTREE
bd_productwords 1   ProductId   1   productid   A   5252509 \N  \N  YES BTREE

Indexes for bd_words:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
bd_words    0   PRIMARY 1   id  A   395359  \N  \N      BTREE
bd_words    1   Word    1   word    A   395359  \N  \N  YES BTREE

Explain for the awfully slow query:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  bdw ref PRIMARY,Word    Word    603 const   1   Using where; Using index; Using temporary; Using filesort
1   SIMPLE  bdpw    ref WordidProductid,ProductidWordid,WordId,ProductId    WordidProductid 5   dreammodels local.bdw.id    12  Using where; Using index
1   SIMPLE  bdp eq_ref  PRIMARY,Price,Id    PRIMARY 4   dreammodels local.bdpw.productid    1   Using where


texts:
In MySQL, I wrote functions in C++, compiled them as .so, and added them to MySQL. I am trying to find a website that shows an example for doing the same in MariaDB. I cannot find any instructions, tutorials, or examples of writing custom functions in MariaDB. Does anyone know of any or is anyone willing to write a very simple one here - like a md10 function that returns concat(md5(val),md5(val))?

Just to make it clear - I am looking to write compiled code, not define a function in SQL. It isn't just for speed. We need to clone the boxes and have all MariaDB engines fully functional.


texts: MySQL using CPU after cancelling slow query

texts:
I had to cancel a very slow running query after it ran for a few days without completing on my Ubuntu server.
Since then MySQL has been persistently running a process which is using 25-45% of the CPU at any given time. The process just restarts when attempting to kill it.
Has anyone observed this issue before and know how to fix it?


texts: Check if any table has a user defined type

texts:
I've seen on sys.table_types that I have 8 user-defined table types on my db.

How can I check if there is any table (1000+) on my db that are currently using any of these user-defined table types before dropping them.


texts: Stored procedure inserts 2 columns into a table with 3 columns that are not null, with no defaults on the table

texts:
I struggled with how to ask this question. This is from a piece of software that we have used for well over a decade. For the life of me, I can't figure out why it works. There are 3 objects in question, a table, a view, and a stored procedure. Here is the definition of them:

Table:

CREATE TABLE [dbo].[AgentGroupsHistoryMap]  (
    [AgentGroupFK]  int NOT NULL,
    [UsersFK]       int NOT NULL,
    [Start]         datetime NOT NULL,
    [Stop]          datetime NULL,
    CONSTRAINT [PK_AgentGroupsHistoryMap] PRIMARY KEY CLUSTERED([AgentGroupFK],[UsersFK],[Start])
    WITH FILLFACTOR = 80 ON [PRIMARY])
ON [PRIMARY]
    WITH (
        DATA_COMPRESSION = NONE
    )
GO
CREATE NONCLUSTERED INDEX [IX_AgentGroup]
    ON [dbo].[AgentGroupsHistoryMap]([AgentGroupFK])
    WITH (
        FILLFACTOR = 80,
        DATA_COMPRESSION = NONE
    )
    ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AgentGroupStop]
    ON [dbo].[AgentGroupsHistoryMap]([Stop])
    WITH (
        FILLFACTOR = 80,
        DATA_COMPRESSION = NONE
    )
    ON [PRIMARY]
GO

View:

CREATE VIEW [dbo].[AgentGroupsMap] AS
    SELECT distinct AgentGroupFK, UsersFK from AgentGroupsHistoryMap where Stop is null

GO

Store Procedure:

CREATE PROCEDURE [dbo].[usp_AddAgentToGroup] @GroupKey int, @AgentKey int
AS
    SET NOCOUNT ON
    INSERT INTO dbo.AgentGroupsMap (dbo.AgentGroupsMap.AgentGroupFK, dbo.AgentGroupsMap.UsersFK)
    VALUES(@GroupKey, @AgentKey)
    RETURN
;

GO

This SQL was all written on a SQL 2000 system, but we've been able to keep it all up to date. When I started with it, we were on SQL 2005, and it went through 2008, and then we jumped to SQL 2014.

That stored procedure, which seems like it shouldn't work; does. I just executed and watched the results.

This came up because in out test environment we started seeing an issue. Both production and test are on the same version of SQL. It works fine in production, but in test, we started getting this error:

>[Error] Script lines: 1-1 --------------------------
 Id 4403, Level 16, State 1, Line 4
    Cannot update the view or function 'dbo.AgentGroupsMap' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.
 Msg: 4403, Level: 16, State: 1, Procedure: usp_AddAgentToGroup, Line: 4

So I have 2 questions:

Why am I getting that error? I don't see any of the conditions that the error says are the case.
How in the world does this work? From what I can see, the sproc is able to insert into the "Start" field, which is not null and has no default, without specifying a value.


texts: Large (>22 trillion items) geospatial dataset with rapid (<1s) read query performance

texts:
I'm in the process of designing a new system for a large geospatial data set that will require rapid read query performance. Therefore I want to see if anyone thinks it is possible or has experience/advice about suitable DBMSs, data structure, or alternative methods to achieve the required performance in the following situation:

Data will be continuously produced from processed satellite radar data, which will have global coverage. Based on the satellite resolution and land coverage of the globe, I estimate the full data set to produce values at 75 billion discrete locations on the globe. Over the life span of a single satellite, the output will produce up to 300 values at each of these locations (so a total data set of >22 trillion values). This is for one satellite, and there is already a second in orbit, with another two planned in the new few years. So there will be a lot of data! A single data item is very simple and will only consist of (longitude, latitude, value), but due to the number of items I estimate a single satellite to produce up to 100TB.

The written data should never need updating, as it will only grow as new satellite acquisitions are processed. Write performance is not important, but read performance is crucial. The goal of this project is to be able to visualize the data through a simple interface such as a layer over google maps, where each point has a colored value based on its average, gradient, or some function over time. (demo at end of post).

From these requirements, the database needs to be scalable and we are likely to look towards cloud solutions. The system needs to be able to deal with geospatial queries such as "points near (lat,lon)" and "points within (box)", and have read performance of < 1s for locating a single point, and polygons which contain up to 50,000 points (although up to 200,000 points would be preferable).

So far I have a test data set of ~750 million data items at 111 million locations. I've trialed a postgres/postGIS instance, which worked OK, but without the possibility of sharding I don't this this will be able to cope as the data grows.I have also trialed a mongoDB instance, which again appears to OK so far, and with sharding it might be sufficient to scale with the data volume. I've recently learnt a little about elasticsearch, so any comments on this would be helpful as it is new to me.

Here is a quick animation of what we want to achieve with the full data set:

This gif (from my postgres trial) is serving (6x3) pre-computed raster tiles, each containing ~200,000 points and taking ~17s to generate each. By clicking a point the graph is made by pulling all historic values at the nearest location in < 1s.

Apologies for the long post, all comments/advice are welcome.


texts: debugging an ssis package which is invoked from a .net application?

texts: can we attach a debugger to an SSIS package which is invoked to execute from a .net assembly?


texts:
The following quote is from Database by C J Date.

consistency does not imply correctness. Sadly, therefore, all we can do and all the DBMS can do-is simply assume that transactions are correct, in the sense that they faithfully reflect just those real-world operations they are supposed to.

Then the books says:

Note carefully too that this statement is supposed to apply to all possible correct states D1. Clearly, T might in fact not "faithfully reflect the real-world operations it is supposed to" and yet might still produce a correct state D2 from some specific state D1-but that would not be good enough; we want correctness to be a guarantee, not a matter of mere happenstance.

Can you please if "T might in fact not "faithfully reflect the real-world operations it is supposed to" then how can it produce a correct state?


texts: foreign key on multiple tables based on multiple columns

texts:
I have a question about design part of my db I have 3 tables like these:
CREATE TABLTE projects (
    uuid uuid
    name text
)
CREATE TABLTE invoices (
    uuid uuid
    cost text
)
CREATE TABLTE fields (
    uuid uuid
    parent uuid REFERENCES (???) ON DELETE CASCADE ON UPDATE CASCADE,
    type varchar(255) (project or invoice)
)

is possible somehow to create one foreign key that look on more then one table and based on 2 columns (parent, type)? or do I need to do something like this?
CREATE TABLTE filds (
    uuid uuid
    project_uuid uuid REFERENCES projects(uuid) ON DELETE CASCADE ON UPDATE CASCADE,
    invoice_uuid uuid REFERENCES invoices(uuid) ON DELETE CASCADE ON UPDATE CASCADE,
)


texts: autovacuum challenges after upgrade to postgresql 9.6

texts:
We have a cluster with several dozen databases.  It was running great on 9.3 for a year (growing over time) when we did the upgrade to 9.6 last month.  Since that time, the database will freeze on updates of large tables (tables with 100k+ rows), from between a minute, to 30-60 minutes.

Specifically, one of our processes updates several columns one-at-a-time in such a table, and in the code log, I can see where it pauses and hangs, for up to 30 minutes, and then picks up later as if nothing happened, and updates many consecutive columns in the same with no such hang, within seconds.

Increasing logging in the database showed nothing at first - and analysis of memory usage on the server and in postgres showed nothing unusual for resource usage.  Eventually, I was able to see, with debug-level logging, that postgresql would go through vacuuming all databases, and the query in question would un-stick after the autovacuumer would hit that database, though not on the first loop of all databases, but after several passes.  Since then, I have been messing with the autovacuum settings.  I tried turning autovacuuming off, as it was off on 9.3 and somehow we survived - but that did not help.  So I turned it on, and have been changing various settings as I learn about them.  I got it so that the production server does not hang in this way through frequent autovacuuming, adding 16 workers, etc.

I am pasting the settings I have in general for our db server - the general memory settings were created with pgtune, the autovacuum settings I have been essentially tinkering with.

I am learning but am very much shooting in the dark - if anyone can provide guidance on how best to handle the analysis (through logs, etc) and optimizing of the autovacuuming on this instance of 9.6, that is what I am after.

Various queries at the official site and here designed to show locks don't turn up any waiting processes at all - but I do see at least one each entry for a process with an 'AccessShareLock' and 'RowExclusiveLock' in the pg admin iv dashboard, under Locks, when the query to update the column is running.

Again, what is frustrating - I don't see what is so complicated about a column update of a few hundred thousand records, one-at-a-time - it should be easy to see what is causing the hang (something about autovacuuming), and also how to fix it.  Whereas the settings below seem to be working on production, I am not clear on the why, don't know how to improve them, and don't know how to create a working set of configurations on a dev server.  Your help is appreciated.

autovacuum = on
log_autovacuum_min_duration = 200
autovacuum_max_workers = 16
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 5000

#autovacuum_analyze_threshold = 500
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_multixact_freeze_max_age =

autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 2000
default_statistics_target = 100
maintenance_work_mem = 2920MB
checkpoint_completion_target = 0.8
effective_cache_size = 22GB
work_mem = 260MB
wal_buffers = 16MB
shared_buffers = 7680MB
min_wal_size = 80MB
max_wal_size = 2GB


texts: Select count distinct on key column where multiple others satisfy condition

texts:
I have a table where I am trying to find a count distinct on a column that is not a the primary key

account_id | media_id | sat_cond1 | sat_cond2
---------------------------------------------
123        |  333     |   Y       |     N
123        |  334     |   N       |     Y
123        |  335     |   N       |     N
124        |  221     |   N       |     Y
124        |  222     |   N       |     N
125        |  111     |   Y       |     Y

I'm trying to figure out a query that will return a count of account_ids where there's at least one row that satisfies sat_cond1 and at least one row that satisfies sat_cond2.

For instance, in the above table account_id 123 would, because media_id 333 satisfies sat_cond1 and 334 satisfies sat_cond2.  account_id 124 would not be counted, however, because though media_id 221 satisfies sat_cond2 there is no media_id that satisfies sat_cond1 for it.  account_id 125 meets all conditions on a single media_id, so it would be counted.

What sort of query would give me this?


texts: SQL Server service permissions on network share depending on IntegratedSecurity=true/false

texts:
Our company receives a daily file from one of our customers. It is an EDIFACT file translated to XML and downloaded from an SFTP server.

We have developed one (c# console) application that:

Download the file.
Upload it to one SQL Server (2005) using an stored procedure.
Deletes this file from network share.
Runs another stored procedure to integrate it with our sales order's tables.

SQL-Server & SQL-Server agent service initiates as LocalSystem account. (It will be replaced soon and I'd prefer to let it as is, I know it is not configured properly.)

The procedure to store the file in our SQL-Server is similar to this one:

CREATE PROCEDURE [dbo].[sp_ProviderName_Download]
(
    -- FQN of XML file
    @file_name nvarchar(260)
)
AS
BEGIN

    DECLARE @xml_data XML,
            @cmd nvarchar(max);

    BEGIN TRY

        SET @cmd = 'SELECT @xmlText = BulkColumn FROM OPENROWSET(BULK '
                    + '''' + @file_name + ''''
                    + ', SINGLE_BLOB) x;';

        EXEC sp_executesql @cmd, N'@xmlText XML OUTPUT', @xmlText = @xml_data OUTPUT;

        INSERT INTO [dbo].[ProviderXML_Register] (..., xml_data)
        VALUES (..., @xml_data);

        RETURN 0;

    END TRY
    BEGIN CATCH

        EXEC [dbo].[vsp_error_handler];
        RETURN -1;

    END CATCH

END

The issue:

Stored procedure works depending on how we set the ConnectionString in our C# application.

If it uses IntegratedSecurity=false:

this.scsb = new SqlConnectionStringBuilder();
this.scsb.ApplicationName = "ProviderXML_Download";
this.scsb.WorkstationID = Environment.MachineName;
this.scsb.DataSource = "xxx.xxx.xxx.xxx";
this.scsb.InitialCatalog = "my_db";
this.scsb.IntegratedSecurity = false;
this.scsb.Password = "*******";
this.scsb.UserID = "SqlServerLogin";

It works properly without errors.

But if it connects using IntegratedSecurity=true (AD user)

this.scsb = new SqlConnectionStringBuilder();
this.scsb.ApplicationName = "ProviderXML_Download";
this.scsb.WorkstationID = Environment.MachineName;
this.scsb.DataSource = "SQL_INSTANCE_NAME";
this.scsb.InitialCatalog = "my_db";
this.scsb.IntegratedSecurity = true;

it fails with error:

  Operating system error 5 Access denied.

AD user has MODIFY rights on the network share. And as far as I know, 'LocalSystem' account shouldn't be able to access to the network share.

Why I'm getting this error when IntegratedSecurity=true?


texts: INSERT statements in a transaction and locking a range of rows

texts:
I have a table that is concurrently accessed by many users.

Typically, INSERTs to the table are made one at a time.  Occassionaly, users will add a "batch" of rows at the same time.  The batch is usually small, perhaps 30-50 rows.

The Identity for these rows that are added in batches must be consecutive, in order to match up with an external process that prints receipts for the added range.

I have a stored procedure that does the INSERTs.  It has a transaction wrapped around the INSERT INTO... statements.

This is essentially how the stored procedure is designed:

BEGIN TRANSACTION
WHILE @loopCounter <= @ReceiptCount
 BEGIN
  SELECT  @pinNbr =  CAST(RAND() * 1000 AS INT);
  INSERT INTO MyTable (values)
  SELECT @insertedId = @@Identity;
  UPDATE MyTable SET externalId = CONVERT(VARCHAR,@pinNbr), 3)
         + RIGHT('000000000' + CONVERT(VARCHAR,@insertedId), 9);
  SET @loopCounter = @loopCounter + 1;
 END
COMMIT TRAN

What is happening, is that on occasion, some other users' typical INSERT process will insert a single row into the middle of the batch, messing up the consecutive range of IDs required.

I thought the transaction would lock the table and prevent this from happening.

How do I prevent the table from being updated while the batch process is adding records?


texts: Selecting only when specific values exist in another select query

texts:
A bit of a noob question, but would appreciate the help.

I want to use the result of one select statement in a different one in a specific way.

The first select statemnent:

SELECT DISTINCT id FROM customers WHERE country = 'Neverland';

The result will be a one-column table with distinct IDs.
Now I want to select all these IDs from a different table.

SELECT * FROM shipments WHERE [the id is contained within the first select statement]

Thanks!


texts: I'm performing a post-mortem on an Oracle database that's crashed its' host. Where should I begin?

texts:
I have an Oracle database (10.2.0.1.0 I believe) on a Windows 2003 server that I believe has successfully crashed the OS itself twice.  Debates on platforms and stability aside, the evidence suggests that the server becomes unresponsive to any remote access (ports open but services don't respond) due to what I can only guess is a bad state in the Oracle's process. The IT staff noted the process appeared to be preventing it from successfully rebooting when they went to power-cycle it.

I don't have physical access to the server. However, since it's up and running again, are there any logs/dumps/etc. that I can check which may point me in the right direction? If you were me, where would you begin? Google hasn't been kind on the subject.


texts: Is the query optimizer able to optimize away IS NOT NULL conditions if the column has a NOT NULL constraint?

texts:
I am trying to work out ways to improve the performance on a very slow query. It has more obvious problems but one thing I noticed is that one of the conditions in the WHERE clause is 'AND t.data IS NOT NULL' while the 'data' column of table t has no entries that are NULL and has indeed a NOT NULL contraint.
So I wondered whether the query optimizer would be able to ignore the condition or not. My thinking is that it cannot do that just because of the constraint (since it is not guaranteed if it was created with NOVALIDATE) but might be 'clever' enough to use statistics about the number of NULL fields in the column.
My own testing was inconclusive and I was unable to find any further informations on this topic.


texts: Error SQL: duplicate key value violates unique constraint function

texts:
i've been looking at other posts. It seems many people have had this problem, but I'm somewhat new to Postgres and tried other solutions with no luck.
I would like to delete user duplicates in my token table
I did a job with this function
-- delete
DROP FUNCTION IF EXISTS deleteT();

CREATE OR REPLACE FUNCTION deleteT() RETURNS text AS $$
DECLARE
  cur CURSOR FOR SELECT users FROM Token WHERE id = 'GO' GROUP BY
  users HAVING COUNT(id) > 1;
  rec RECORD;
  names TEXT DEFAULT '';
BEGIN
 OPEN cur ;
 LOOP
     FETCH cur INTO rec;
    EXIT WHEN NOT FOUND;
    DELETE FROM Token WHERE users = rec.users;
    names := rec.user || ', ' || names;
  END LOOP;
  CLOSE cur;
 RETURN names;
END;
$$ LANGUAGE plpgsql;

-- execute
SELECT deleteT();

I have  a error
ERROR:  duplicate key value violates unique constraint "pg_proc_proname_args_nsp_index"
 DETAIL:  Key (proname, proargtypes, pronamespace)=(deleteT, ,
 2200) already exists.
ERROR:  function delete_c3po_tokens() does not exist at character
ERROR:  missing chunk number 0 for toast value 426865 in pg_toast_57360


texts: Process to reclaim free space from mongodb ,replication is there

texts:
We are facing space pressure on production server so we are planning to  reclaim space from prod instance.replication is there ,can anyone help me  to  know complete process in sequence to reclaim free space.

I am thinking to use repair db commend at slave node to reclaim space but don't know how to make secondary node after repairing to primary server and start replication again.


texts: MS Access Unable to Select Number Field Size

texts:
I'm using Microsoft Access 2016 32bit on Windows 10. My issue is the Field Size property list is empty for the Number data type. Why is this empty? How can I change a number's field size? I am trying to specify the Byte field size for my field.

Even when starting from scratch and creating a new empty database, with new tables, I am unable to select a field size for the number data type.

With my existing databases I am unable to change field size of existing number type fields. For example, existing byte fields are still bytes however if I open the design view on the table and click the field size drop down list for that field the list is empty.

I expected a number field's field size drop down list to contain byte, integer, single, double, etc. however it is empty. This list was previously available to me.

Is there a MS Access setting that I need to change? Or is this a bug?

My computer recently had a windows update, it's unclear to me whether or not this included an office update.

UPDATE
As a workaround I can execute ALTER TABLE sql commands to modify my field's data types. examples


texts: MariaDB 10.2: Why does Count(*) work, but not Count(t.*)

texts:
I have a table containing transaction details which I am importing from several vendor-provided daily reports.. I want to be sure that I am not skipping any of the files (one file per day) as I do this process..

So I wanted to query the database to find the number of distinct dates it found in the result set.

At first I tried to execute:

SELECT COUNT(t.*) FROM (SELECT DISTINCT `Date of Survey`  from cc.voc_detail_old) t;

But that generates the error "* SQL Error (1064): You have an error in your SQL syntax;..."

Changing it to SELECT Count(*)... resolves the error and the query functions as expected. Why is it not permissible to use the alias of the derived table in the Count() function, but the derived table's alias is required per syntax?


texts: Mysql Slow when join query running increaed

texts:
Our Mysql dedicated server box always have around 40 - 80 active user connection , so the query speed very good and Cpu usage low . (around 4 - 8 core from 24 core)
but sometimes when some wordpress "Join" query running , server speed slow and other query go in queue so server running query increased over 600 -1000 /s.

this is following query that I think cause issue :

SELECT SQL_CALC_FOUND_ROWS  WP_POSTS.ID FROM WP_POSTS  INNER JOIN
WP_TERM_RELATIONSHIPS ON (WP_POSTS.ID = W
P_TERM_RELATIONSHIPS.OBJECT_ID) WHERE 1=1  AND (
WP_TERM_RELATIONSHIPS.TERM_TAXONOMY_ID IN ()

My question ;

why Join query affected our mysql server performance ?
is it config related problem ? or we need more hardware ? at this time our server 24 core + 64 GIG RAM + SAS 15K RAID 10

Update 1 :

Simple Processlist related one of user :

| 1871506 | xhamster_gdgdgd  | server6:48608 | xhamster_hehehe           | Query   | 456  | Sending data                   | SELECT SQL_CALC_FOUND_RO
WS  wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 2467509 | kordgsmc_gsm     | server6:37144 | kordgsmc_vb             | Query   | 22   | Waiting for table level lock | SELECT userid, username
                                FROM user
                               sWHERE userid IN (6295)                                   |
| 2468708 | samseria_wp      | server6:38899 | samseria_wp             | Query   | 23   | Sending data                 | SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 2469440 | samseria_wp      | server6:39953 | samseria_wp             | Query   | 6    | Sending data                 | SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 2469825 | samseria_wp      | server6:40542 | samseria_wp             | Query   | 23   | Sending data                 | SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |


texts: Remove table from DEFUNCT filegroup so we can recreate the table again in a different online filegroup

texts:
We have a BIG production database. We also have STG and UAT servers, where we restore the PROD database. One of the biggest tables (+250 GBs) in this PROD database is in the SECONDARY filegroup. We need the table in UAT an STG, but we don't need the data we have in PROD. My solution was to restore only the PRIMARY filegroup; that way, the restore process is faster, and also we need a lot less space since the secondary filegroup is big. We don't want to restore the secondary filegroups in the future, so I defunct the Secondary filegroup.

The big table is the only table in the secondary filegroup and it remains inaccessible. If I try to query the table, I get:

  The query processor is unable to produce a plan for the table or view
  'tablename' because the table resides in a filegroup that is not
  online.

I would like to create the table accessible again, an empty table, and even if it's on the PRIMARY filegroup. I tried to DROP the table so I can recreate it in PRIMARY filegroup, but since the SECONDARY filegroup is offline, I get an error. Also, If I try to recreate the table in PRIMARY filegroup, it says the table already exists.

As I said, we need the table for the app to work in UAT/STG, but we don't need the data, and we don't have the space to restore the secondary filegroup.


texts: Recommended ways to upgrade AlwaysOn AG from 2014 to 2016

texts:
What is the recommended process of upgrading Availability Groups with one Sync Replica from SQL 2014 to 2016?

My understanding is that in-place upgrades are not really liked among database professionals. Is there a way to avoid in-place upgrades in Availability Groups? Is there a possibility to join a 2016 server into an existing 2014 availability group and to failover there, then "kill" one of the other instances and upgrade it? (e.g. by removing/re-installing SQL side-by-side on the same host)

Are there any other upgrade scenarios that we should consider?

The ideal solution would be that we always have 2 online replicas, and that we don't need to do any in-place upgrades.


texts: Declaring cursor to loop over some values in SQL Server 2008 R2

texts:
I have some business unit such as India, International, US, UK.

I have to:

Create a table variable (virtual in-memory table) with columns that match stored procedure SP_Report resultset
Declare a cursor that contains all business units and then the cursor should loop over the BU's
For each BU, execute a stored procedure called SP_Report and insert the data into the table variable
Finally I have to select the columns from the table variable + timestamp + BU, insert them into another table and clear the table variable.

Code:

declare @K table (BU nvarchar(max), K nvarchar(max),Y money, A money, D money, YP money)
declare @FY int
declare @BU nvarchar(max)

INSERT INTO @K (BU, K, Y, A, D, YP)
    EXEC dbo.SP_Report '2012', 'India'

SELECT * FROM @K

This code returns the result of the table variable. Now I have to use it cursor, which I don't know how to. How I can solve the same.


texts: A MySQL EXPLAIN number of rows discrepancy

texts:
MySQL 5.5.49-log

More questions on the query in Why does it use temporary? (MySQL) (the query is the same but the question is different):

I have the following table (filled with many rows):

CREATE TABLE `SectorGraphs2` (
  `Kind` tinyint(3) UNSIGNED NOT NULL COMMENT '1 - продюсер, 2 - жанр, 3 - регион',
  `Criterion` tinyint(3) UNSIGNED NOT NULL,
  `Period` tinyint(3) UNSIGNED NOT NULL,
  `PeriodStart` date NOT NULL,
  `SectorID` int(10) UNSIGNED NOT NULL,
  `Value` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `SectorGraphs2`
  ADD UNIQUE KEY `Producer2` (`Kind`,`Criterion`,`Period`,`PeriodStart`,`SectorID`) USING BTREE,
  ADD KEY `SectorID` (`SectorID`);

then I run:

EXPLAIN
    SELECT SectorID, SUM(Value)
    FROM SectorGraphs2
    WHERE Kind = 1 AND Criterion = 7
      AND Period = 1
      AND PeriodStart >= ? AND PeriodStart < ? + INTERVAL 1 WEEK
    GROUP BY SectorID

and it produces:

+----+-------------+---------------+-------+---------------+-----------+---------+------+------+----------------------------------------------+
| id | select_type | table         | type  | possible_keys | key       | key_len | ref  | rows | extra                                        |
+----+-------------+---------------+-------+---------------+-----------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | SectorGraphs2 | range | Producer2     | Producer2 | 6       | NULL | 1    | Using where; Using temporary; Using filesort |
+----+-------------+---------------+-------+---------------+-----------+---------+------+------+----------------------------------------------+

See a nicely formatted explanation here.

My question: Why it is used a temporary table and filesort but it reports only 1 row examined? It seems that because of using a temporary table, it should process more than one row. How can I determine the real number of rows processed? How to solve this discrepancy about number of processed rows?

Note that the task I was assigned to do now is to eliminate heavy (involving too many rows) queries. And now I do not know how to do this.


texts: Identify in details the consumption of memory from a specific session

texts:
We are using Oracle 11g standard, without any options (diag/tunning/perf).

I can identify one session with the SQL bellow which having a high consumption (1.4 GB) of memory on the past hours...  (pga and uga)

There is someway to get details about this consumption?
Whether it is the use of temporary tables or something else?

with x as (select s.osuser osuser , s.username
     , s.status
     , se.sid
     , s.serial# serial
     , n.name
     , round(max(se.value)/1024/1024, 2) maxmem_mb
     , max(se.value) as maxmem
  from v$sesstat se , v$statname n , v$session s
 where n.statistic# = se.statistic#
  and n.name in ('session pga memory','session pga memory max', 'session uga memory','session uga memory max')
   and s.sid        = se.sid
 group by s.osuser, s.username, s.status, se.sid, s.serial#, n.name
 order by maxmem desc
 )
 select * from x where rownum < 5
 ;


texts:
After asking last question about $id=$_GET['id']; and security issues I tried a PDO connection to my server. The following message appeared; ERROR: could not find driver.

I connected to phpMyAdmin - 2.11.1.2 and had this message appear;
Your PHP MySQL library version 5.0.51a differs from your MySQL server version 5.1.61. This may cause unpredictable behavior.

Is there a connection? Are PDO drivers required on the server? Would MySqli work?

Bob


texts: How to prevent a trigger from running on certain statements?

texts:
I'm looking for an answer that satisfies my need on both SQL Server and MySQL.

I have a small, custom audit table that I will be writing to through triggers on various tables. There are situations where I would like to bypass those triggers. I know that I can make a column on the tables and use it as a sort of flag for the trigger, but I'm hoping to find something that generates less clutter.

Is it possible to prevent a trigger from being called using a variable in my statement or some other query side thing?

EDIT: To clarify, I'm not expecting an actual trigger/query that works on both platforms. I'm looking for a solution that fits the desired goal of not having to use an extra column in each table.


texts:
I have table queue with values.

id name
--------
1  apple
2  mango
3  banana -- I assumed this name, don't remember it exactly

But I accidentally changed it to be

id name
---------
1  apple
2  mango
3  John

So I reverted it back to

id name
----------
1  apple
2  mango
3  banana

But now I came to know that name is not banana for id = 3.

How can I know what used to be there before I changed it?


texts:
In my MySQL DB I have one field called html_contents that contains all the html to be shown in a webpage. Obviously the html could be huge, and certaintly bigger than 64KB, therefore I decided to use LONGTEXT rather than VARCHAR.

When the field is not set or left empty would you say it is better (alias more efficient for the DB) to set it to NULL or to empty string?

I read this: When to use NULL and when to use an empty string? but it talks about empty strings in general (probably small strings and not LONGTEXT).

I was wondering if with LONGTEXT is a different story, whether it saves a lot of space or execution time to use NULL instead of leaving empty LONGTEXT around.


texts: Complete deadlock on PostgreSQL 9.3 after removing a numeric id

texts:
The problem - complete DB lock after a schema change

Architecture

I use a PostgreSQL 9.3 with PostGIS to store location updates. The updates are written to two tables - location_updates and location_updates_history.

Each had:

An auto-incrementing numeric id
A textual car_id
geom column

Each update was written to the location_updates table, and then a trigger function would write the same update to location_updates_history. After the update, all updates except for the latest 10 for this car_id would have been deleted. Therefore, the location_update table contained the latest 10 updates for each car in a moving-window fashion, and the location_updates_history table had the entire history of it.

The change

I figured out that I only need the latest update for each car, not the latest 10, so I run the following:

DROP TRIGGER location_update_insertion_trigger ON location_update;
DELETE FROM location_update WHERE id NOT IN
            (SELECT max(id) FROM location_update GROUP BY cat_id);
ALTER TABLE location_update DROP COLUMN id CASCADE;
ALTER TABLE location_update ADD PRIMARY KEY (car_id);

The lock

After a few hours of work, the DB was in a complete lock - so bad that even a pg_dump on its schema was blocked.

With some help from the excellent support team on AWS RDS, and this blog post, I was able to draw this locking schema - in short, everybody was blocking everybody. This has been going on for days, and only stops when I manually kill the listed processes on the calling machines.

The UPDATE statements are of the form:

UPDATE TABLE location_update SET ... WHERE car_id='car_XXX';

The LOCK statements are of the form:

LOCK TABLE public.location_update IN ACCESS SHARE MODE

The SELECT statements query the location_update table.

Why is my DB locked on the location_updates table? Was removing the numeric id the reason for that? What's the right way to fix it?


texts: refresh a materialized view after update of tables

texts:
I am currently stuck in postgres at creating an updateable MATERIALIZED VIEW.

The MATERIALIZED VIEW consists basically from 2 (foreign) tables.

This is an example for the tables:

id | data
---+------
 1 | foo

id | data
---+------
 1 | bar

A UNION is used to generate the VIEW and also to create unique IDs (but thats a different story):

id | data
---+------
 1 | foo
 2 | bar

I want the materialized VIEW to be updateable, meaning, I want to redirect UPDATEs on the data to the corresponding tables.

So I have a 'regular' VIEW on my MATERIALIZED VIEW with a SELECT * and Triggers/Rules (both worked for my case) which handle INSERT, UPDATE and DELETE.

CREATE OR REPLACE FUNCTION updateOrigTable() RETURNS TRIGGER AS
$$
  DECLARE
    myID IN;
  BEGIN
    IF myID > (SELECT MAX(table1.id) FROM table1) THEN
      myID := myID - (SELECT MAX(table1.id) FROM table1);
      UPDATE table2 SET dummy_data = NEW.country WHERE table2.id = myID;
    ELSE
      UPDATE table1 SET dummy_data = NEW.country WHERE users1.id = NEW.id;
    END IF;
    RETURN NEW;
  END;
$$
LANGUAGE plpgsql volatile;

CREATE TRIGGER updateTrigger
  INSTEAD OF UPDATE
  ON myView
  FOR EACH ROW
  EXECUTE PROCEDURE updateOrigTable();

I also want to create a TRIGGER, that is triggered on INSERT, UPDATE and DELETE, which performs a REFRESH.

CREATE OR REPLACE FUNCTION matViewRefresh()
RETURNS TRIGGER AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY matView;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER matViewRefresh
  AFTER INSERT OR UPDATE OR DELETE
  ON table1 FOR EACH STATEMENT
  EXECUTE PROCEDURE matViewRefresh();

CREATE TRIGGER matViewRefresh
  AFTER INSERT OR UPDATE OR DELETE
  ON table2 FOR EACH STATEMENT
  EXECUTE PROCEDURE matViewRefresh();

However, when I try to update myView, it gives me

ERROR: REFRESH MATERIALIZED VIEW with Relation »matView« not possible because it is used by active queries in this session (translated from German)

I do not understand why this blocks. The refresh happens after the update, so the database object from matView should already be unblocked.

Is there a way to create a hardcopy of the database object in my update procedure?

Any other hints?


texts:
I am moving data from Access to SQL Server. One of the tables I'm working with has some columns like this:
ID | Date | Sensor1 | Sensor2 | Sensor3 | Sensor4 | Sensor5 | Sensor6 | Sensor7 | Sensor 8 | Sensor9

Certain Facts:
At any point atleast 3  of these columns would be filled.
More Sensors could be added in the future
This table in entirety has 120 columns (above is just a portion)
ID is TinyInt
Sensor data is Decimal(4,2)

When I looked at the table structure and the Nulls due to the facts above, I thought of changing it to a structure like this:

ID |Date | Sensor_NO | Data

My question is:
Considering I'm breaking down a huge table. This sort of table restructuring would make it much more difficult to join the individual tables because I will have to transpose the table first and then join the various tables based on ID.
Would my proposed structure going to improve efficiency to a point where it is worth the work of creating JOINS later? Would you consider such a transformation?


texts:
Three or four years ago I read in an Oracle blog somewhere that a DBA had used for an emergency incident resolution an Oracle 10g feature of real time SQL substitution. Basically, he configured Oracle in way that every time that it received a certain query A it executed another query B instead. No application code change, no schema change, just a simple "execute query B instead of A" type of configuration.

Not that I am planning to use that feature (I can think of some undesirable consequences), but out of curiosity does it really exist? If yes, what is that feature called?


texts: Dealing with corruption: file-level backup vs. pg_basebackup

texts: The wiki on corruption recommends immediately taking a file-level backup of your database by stopping the postmaster and using tar, but I was under the impression that using pg_basebackup was a better option.  Is there any reason I shouldn't use pg_basebackup?


texts: Connection pools being reset with Error: 18056, Severity: 20, State: 46. & Perfmon Counters not showing

texts:
We are using SQL Authentication (to reduce the number of connection pools) & .NET 4.0 connection strings to connect to SQL Server Enterprise Edition 2012 SP1 on a Windows 2008 R2 Enterprise Server:

  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
  Oct 19 2012 13:38:57
  Copyright (c) Microsoft Corporation
  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

We use about 50 servers split into 8 different groups different parts of a website.

Our website is using this SQL Server to log visit tracking data. Over the last few days it has spat out the following messages about the resetting connection pools:

  The client was unable to reuse a session with SPID 1327, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

Error log reads:

  Error: 18056, Severity: 20, State: 46.
  The client was unable to reuse a session with SPID 959, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier  operation failing. Check the error logs for failed operations immediately before this error message.
  Login failed for user 'xxxx'. Reason: Failed to open the database 'xxxxxxxx' configured in the login object while revalidating the login on the connection. [CLIENT: 10.xx.xx.xxx]

After some digging, I found this document on the CSS blog: How It Works: Error 18056 – The client was unable to reuse a session with SPID ##, which had been reset for connection pooling and this one by Aaron Bertrand: Troubleshooting Error 18456. I know the error number is different but the failure ID is the same with a number of the messages are identical).

Failure ID 46 suggests that the login did not have permissions. Our logins default to the master database and the db name is specificied in the connection string.

I wanted to check the number of connection strings pools, etc. and checked all the counters in Perfmon for .Net Data Provider for SqlServer. It only gave me the option of defaultdomain9675 for the instance, so I selected that assuming that is a system generated ID name for our Datacentre network. Unfortunately all of the counters are reading zero. On one of our other main servers the connection pools are hovering around 10 which is what I expected to see on a healthy server with that kind of load.

My question is 3 fold

Can anyone suggest why the Windows 2008 R2 Server is not showing .Net Data Provider for SqlServer?
Has anyone experienced this as I obviously believe that the login not having permissions is a red herring ?
If different groups of web servers have the same connection string syntax but with slightly different whitespace, would this cause the server to use another connection pool?

Min & max memory settings are 20GB & 58GB respectively. The server is a dedicated database server with 64GB of RAM. I don't think memory is the issue as the box seems to have a decent page lfe expectancy. Auto close is not enabled. The server is always up: this is a 24x7 website with heavy usage.


texts:
I had SQL Server 2016 Standard Edition installed on a server. Due to some drive reconfiguration, I had to do a reinstallation. I uninstalled using the program and features and just as I was about to launch the setup to install it again, I went ahead and deleted the "Microsoft SQL Server" folder under "Program files".
Most of the files were deleted and few remained, mainly in "C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016".
The problem is now when I am trying to launch the setup, it displays the error:

TITLE: SQL Server Setup failure.
SQL Server Setup has encountered the following error:
Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.
Error code 0x84B10001.

Not sure how to proceed next.


texts: BCP cannot read table created in the same transaction when executed from Control-M

texts:
How to export global temp table using BCP when original process locks table for BCP?
We created our own exporting procedure where we provide SELECT or table_name and procedure stores data to global temp table, which then BCP utility exports.
It runs fine, but when we run it from BMC's Control-M system, there is schema modification lock on the table and procedure just waits for itself to unlock.
Static table(s) is not a good solution for us.
We tried using for example self linking linked server or index on all columns of global temp table.
Our current proposal is to add export to a queue which would then be exported asynchronously by another process.


texts: Any reason to have both an index with included columns and one without?

texts:
I am looking through a few slow running queries on a database that has under gone significant development (under a number of different developers) over the last couple years.
One of the most commonly used tables has a couple indexes with the same indexed columns, but one will have included columns and the other will not.

For Example:
One Index:

 CREATE NONCLUSTERED INDEX [IDX_tblTable_IndexedColumnId_Inc] ON [dbo].[tblTable]
 (
[IndexedColumnId] ASC
 )

And then the other:

CREATE NONCLUSTERED INDEX [IDX_tblTable_IndexedColumnId_Inc] ON [dbo].[tblTable]
(
[IndexedColumnId] ASC
)
INCLUDE (   [Field1Id],
[Field2],
[Field3],
[Field4],
[Field5],
[Field6])

I would assume this is just an oversight and is just using up disk space and additional overhead to have the index without the included columns (the included columns are known to be needed).

Is there any benefit of having two indexes on the same column, one with included columns and one without?


texts: Page Writes too high but normal values on PLE and BCHR?

texts:
Today I was just taking a look on my performance counters and found this...

Average Batch Request per second was 0,6
Average Page Reads per second was 12,5
Average Page Writes per second was ~15000
Average Checkpoint Pages per second was ~15000
Average Page Splits per second was 325

Buffer Cache hit ratio at 99%

Page Life Expectancy at 37256

After searching a bit I found that more than 90 Page Reads and Writes per second could indicate memory pressure but how Page Writes could be an indicator of memory pressure if for example I'm loading data in tables constantly?

Thanks!


texts:
I have a very strange problem. When I run a query with SET STATISTICS IO ON, it shows that I had a total of 183 reads. But when I look at the query plan dmv it shows I did 555,132 reads. I cleared the query cache before I ran my query, so I know that run was mine. Any ideas?

The reason I am checking this is I'm seeing some sort of bad parameter sniffing going on and when I look at the trace file and the query plan cache I see a query doing around 550k reads, but when I try it manually I only get 183 reads. So I'm guessing there is some setting in my SSMS that is different than the calling server where mine does not use the bad plan, but compiles a new one.

Stats I/O output:

Table 'Artifact'.     Scan count 1, logical reads 3,   ...
Table 'ArtifactType'. Scan count 0, logical reads 180, ...
Table 'ObjectType'.   Scan count 1, logical reads 3,   ...


texts: Oracle: what permission is necessary to "grant create table" to another user?

texts:
What permission should A have in order to grant B permission to create a table?

logged in as user A, I execute

grant create table to B;

and receive the following error:

SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.