texts: I need to write a shell script that finds and kills long running mongodb queries, but I don't have access to the database that runs slow queries, how to I simulate running slow queries on my local machine running mongodb v3.6.8, so that I get some relevant output from db.currentOp() for now doesn't return much, also any tips are welcomed for the script as well.


texts: How to find out the total no of cell nodes configured as part of a rack in exadata?

texts:
I want to know the exact total no cell nodes configured as part of the rack by login into the first db node of Exadata machine and run some commands or check some files.

We are not able to believe the output of /opt/oracle.Support.Tools/onecommand/cell_group file or the cellip.ora files or ibnetdiscover cmd ,since in some customer environment those files outputs are not giving the exact no of cell node details.

So is there any other command or file to fetch the exact no of cell nodes as part of the rack on the first db node ,even in the case like customer added additional storage nodes as part of that rack?


texts: Where are subscriptions registered in SSMS after creating subscription on Distributor Server?

texts:
With 2 servers, each with a SQL Server instance, I can:

Create a local publication on server A
Create a local Subscription on server B (managed by the Distributor database)

Then on server A I can see the publication. But there is no publication on server B. If I try re-create the same local subscription on server B, then I get an error that the subscription already exists.

Why can I not see the subscription within the subscription folder on Server B?

Server B is also the Distributor server

I think it was a push subscription - i.e. that it was configured on the distributor to be pushed.

I have expanded the publication tree on the publisher and can see the publication. I was expecting the subscription to be seen in the same way. I tried refreshing.

It is on neither publisher nor subscriber. but if I try to recreate the replication then I get an error that it already exists. The question I'm asking is effectively "why can't I see a subscription in the replication folder?"

Alternatively. Is there a SQL Server instance that I need to connect to?

I should note that there are errors in the replication monitor. So there are still a couple problems. Anyway. I can see the jobs in the SQL Server Agent. There was a warning: "Starting the synchronization agent(s)". Would this stop the subscription being created?


texts: Optimizing MariaDB for 5-10K small databases

texts:
Running a SaaS based on MariaDB LTS (1:10.1.44-0ubuntu0.18.04.1) with nearly 5000 databases (1 per tenant). Average per tenant: 8MB of data (on disk) with ~50 tables. CPU/Mem load is negligible. It's been running fine for a couple years, with some 5-20 tenants added per day.

However. When the nr of dbs grows to about 5150, the MariaDB server process crashes. So far I haven't been able to distill a useful log message, unfortunately. Furthermore, it takes about 10 minutes for MariaDB to start up, which is quite long when I want to experiment with new settings.

What settings would you recommend to reduce the risk of crashing, and speed up starting time?

Some more characteristics:

SHOW GLOBAL STATUS
SHOW VARIABLES
Server has 2CPU and 8GB ram, of which 25% is available during peak times.
Load per CPU core never exceeds 0.20 during peak times
Connections are short lived, I expect that there are never more than 20 concurrent db connections.
All tables are InnoDB
Schema may differ per database (but are probably identical for 98% of tenants)
I use stock configuration, except for:

innodb_file_per_table
open_files_limit        = 65536
log_warnings            = 3
innodb_buffer_pool_size = 1G

FWIW, I'm willing to upgrade to a newer version of MariaDB, but given the risk of new uncertainties, I'd rather make an informed decision than take a leap of faith.

Thanks for any advice!


texts: Does it ever make sense to create an index with additional columns after the primary key?

texts: I am looking through our MySQL 5.7.16 database and I noticed that there are some indexes that use the primary key as the first column in the index, followed by additional columns.  Intuitively, this doesn't seem to make sense to me since the index would always narrow it down to one row by using the first column.  However, I am not sure if there are any special circumstances in which it makes sense to have this kind of index.  Is there any situation where this would improve performance?


texts: What is the Maximum Merge Articles in Merge Replication

texts:
Possible Duplicate:
  Hard limitation for merge replication article count?

I am using merge replication in SQL 2012. I am looking here:

Maximum Capacity Specifications for SQL Server (2012)

Scroll to the bottom and under the 'Replication Objects' it says that the maximum number of articles in a merge publication is 256.

I wasn't aware of this apparent limitation, but in the mean time I have been running merge replication with 456 articles and everything seems to run fine.

I found these 3 links about it:

Too many table names in the query sql 2005 merge replication snapshot error
FIX: Merge Snapshot Agent Fails with Error 50007: "Too many table names in the query. The maximum allowable is 256."
Hard limitation for merge replication article count?

So to summarise, apparently in SQL 2000 there was a bug which caused this limit. It was fixed in a service pack, but was reintroduced in SQL 2005.

Does that limitation still exist or not? There doesn't seem to be any official information about SQL 2012.

I would rather management studio tell me 'you cannot add more then 256 articles' rather than having to guess whether this will be a problem for us or not.


texts: How to configure replication from PostgreSQL (RDS) to vanilla PostgreSQL?

texts:
I have an Amazon RDS Postgres DB and I created a "replicator" user and granted it the "replication" privilege.

db=> create role replicator login password 'something';
CREATE ROLE
db=> alter role replicator replication;
ALTER ROLE
db=>

However, when I try to run pg_basebackup from the target host to create the replica, I get a pg_hba.conf error:

# pg_basebackup -D /data/from-master -U replicator -h db-master.example.com -v
pg_basebackup: could not connect to server: FATAL:  no pg_hba.conf entry for replication connection from host "10.1.20.19", user "replicator", SSL on
FATAL:  no pg_hba.conf entry for replication connection from host "10.1.20.19", user "replicator", SSL off

The problem is that, this being RDS, I can't get to the pg_hba.conf at all.  Any ideas?


texts: Export Oracle tables based on owner + all users from database

texts:
I would like to export all user and roles objects from Oracle 10g database to another.
I googled it but I cannot find out how to do it.

First I use this command:

  exp system/root@[DB_Name] file=[Back_file_name] owner=([Owner_Name])

But the problem was that some table settings depend on user privileges. Hence I want to also export user objects.

For example it is possible to export all objects with this command:

  exp system/root@[DB_Name] Full=Y file=[Back_file_name]

But this command does not export the needed objects for me.

I would like to export only:

tables by owner name
all users
maybe roles

Is this possible?


texts: Percona server Master-Slave version query

texts:
I have a master with percona server (5.6.25) running in RHEL host and a slave of the same version. I'm facing some corruption issues in replication slave and I'm thinking of rebuilding the slave from scratch.

Just a query, can I have the slave of version 5.6.31 instead of the original master version? What could be the issues that could occur?


texts: MySQL Cluster SQL Node not synchronizing

texts:
I am new to MySQL Cluster and am trying to setup a new cluster for our new application.  Here is what I have set up on 5 CentOS 64 bit VM and got the cluster  to work using MySQL Cluster 7.2.  I am trying to test it and have some issues.

“I have successfully installed the Cluster with 5 nodes (2 Data, 1 Mgmt and 2 SQL Nodes). While testing the cluster I have hit on one scenario where I am stuck and cannot make it to work. Here is the screen shot of the Management Node displaying all cluster nodes:

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2  @10.0.3.138  (mysql-5.5.30 ndb-7.2.12, Nodegroup: 0)
id=3  @10.0.3.83  (mysql-5.5.30 ndb-7.2.12, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1  @10.0.3.135  (mysql-5.5.30 ndb-7.2.12)

[mysqld(API)]   2 node(s)
id=4  @10.0.3.87  (mysql-5.5.30 ndb-7.2.12)
id=5  @10.0.3.22  (mysql-5.5.30 ndb-7.2.12)

ndb_mgm>

Here is the scenario:

While all nodes in the cluster are working as a part of my test I shut down one of the SQL Node 4 and while this node is offline, I drop a database which is part of the cluster databases on SQL Node 5. When I bring up the offline SQL Node 4 and rejoin to the cluster the dropped database still shows up. It should sync with the old cluster databases meaning the dropped database when the SQL Node 4 was offline should be removed from the cluster and should not show up on SQL Node 4. This is a real scenario that can happen.

Also, I am searching for MySQL Cluster Test document which describes these scenarios and cannot seem to find it. “

Any Help will be greatly appreciated.

Thanks


texts: Can't add a node to PXC due to SST error

texts:
I've successfully bootstrap a Cluster (mysqld  Ver 5.6.30-76.3-56 for debian-linux-gnu on x86_64 (Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16)).

When starting a second node to join this cluster there is the following error:

2016-07-29 09:31:58 12359 [Note] WSREP: New cluster view: global state: 3f3843a3-54aa-11e6-af38-5b145f899741:195614, view# 4: Primary, number of nodes: 2, my index: 1, protocol version 3
2016-07-29 09:31:58 12359 [Warning] WSREP: Gap in state sequence. Need state transfer.
2016-07-29 09:31:58 12359 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address 'XYZ.40.39.13' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --defaults-group-suffix '' --parent '12359'  '' '
2016-07-29 09:31:58 12359 [ERROR] execlp() failed: Permission denied
2016-07-29 09:31:58 12359 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address 'XYZ.40.39.13' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --defaults-group-suffix '' --parent '12359'  ''
    Read: '(null)'
2016-07-29 09:31:58 12359 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address 'XYZ.40.39.13' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --defaults-group-suffix '' --parent '12359'  '' : 1 (Operation not permitted)
2016-07-29 09:31:58 12359 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
2016-07-29 09:31:58 12359 [ERROR] Aborting

There is no special config, just:

wsrep_provider                  = /usr/lib/libgalera_smm.so
wsrep_cluster_name              = "legacy_cluster"
wsrep_cluster_address           = "gcomm://ZYX.4.123.242,XYZ.40.39.13"
wsrep_node_name                 = legacy6
wsrep_node_address              = XYZ.40.39.13

wsrep_sst_method                = xtrabackup-v2
wsrep_sst_auth                  = sst:secret

I've also tried rsync, but got the same error. I'm not sure how to further debug this error!?


texts: SQL Server maintenance plan not working after renaming administrator

texts:
I have renamed the Windows administrator account to TEST using Local Group Policy editor. After that, the SQL Server maintenance plans are not working anymore:

  Unable to determine if the owner of job has server access

I have tried methods below but no luck yet:

I have tried to add a login to SQL Server for TEST account, but it says that this login already exists. I can not see the name of TEST in the Login tab, but the administrator exists already
I have deleted the Maintenance plans and created new ones but no change happened

Please let me know if there is another way to connect the Maintenance plans to the new administrator name.


texts: MySQL c++ connector: what is the correct way to use SetBlob() to set multiple blob data in a query?

texts:
I am using the MySQL c++ connector version 5.x
The table creation statement is like:

CREATE TABLE files_table (fid INT NOT NULL AUTO_INCREMENT, idx TINYBLOB, head TINYBLOB, size BLOB, PRIMARY KEY(fid));

I could insert record manually

mysql> select * from files_table order by fid asc;
+-----+------+------+------------+
| fid | idx  | head | size       |
+-----+------+------+------------+
|   1 | 1    | 1    | 1          |
+-----+------+------+------------+

But when I use the connector to add the next value

class StreamBufferData : public std::streambuf
{
public:
    StreamBufferData(const char *in_data, size_t in_size)
    {
        setg(in_data, in_data, in_data + in_size);
    }
};

enum{QUEry_SIZE=256;}
char ins[QUERY_SIZE];
memset(ins, 0, QUERY_SIZE);

strcpy(ins, "INSERT INTO files_table (idx, head, size) VALUES (?,?,?)");

try
{
    std::unique_ptr<sql::PreparedStatement> statement(ptr_connection->prepareStatement(ins));

    char test_data[2] = "0";
    StreamBufferData buffer0(test_data, 2);
    std::istream test_s0(&buffer0);
    statement->setBlob(1, &test_s0);

    strcpy(test_data, "1");
    StreamBufferData buffer1(test_data, 2);
    std::istream test_s1(&buffer1);
    statement->setBlob(2, &test_s1);

    strcpy(test_data, "2");
    StreamBufferData buffer2(test_data, 2);
    std::istream test_s2(&buffer2);
    statement->setBlob(3, &test_s2);

    statement->executeUpdate();
}
catch(sql::SQLException &e)
{
    std::cout << e.what() << ‘\n’;
    return;
}

The result is :

+-----+------+------+------------+
| fid | idx  | head | size       |
+-----+------+------+------------+
|   1 | 1    | 1    | 1          |
|   2 | 2    | 2    | 2          |
+-----+------+------+------------+

Only the last value is correctly inserted into the table. My question is: what is the proper way to user msql::PreparedStatement::setBlob() to set multiple blobs in a query?

[environment]
Ubuntu 16.04.2
MySQL 5.7
MySQL connector version: 7.1.1.7
boost 1.58.0
g++ 5.4.0

Thanks for your help

Rong


texts: how much free space do I need for db2 backup file?

texts:
I have a db2 database on Linux (DB2 10.5 fp08).
Now I would like to backup it, before backup I can get the database size with db2 "call GET_DBSIZE_INFO (?,?,?,0)"
For example it is 6 GB.

I know if the backup location has >6 GB free space is Ok for this backup.

But do you know if there is any offical documents/post about this.
What is the best higher free space for this kind of backup?


texts: 100% CPU utilization for postgres processes

texts:
We are having 2.6M rows data within a single table and have joined with the other 2 tables having similar sizes. While running a select query it is taking 100% CPU and the query was hanging forever. We tried reindexing for all these three tables. After reindexing query is still taking 100% CPU but for 1 min and it's coming out.
We have tried the following parameters to tune the Postgres as well but no performance improvement.

shared_buffer = 23GB
work_mem = 100mb
effective_cache_size  = 45GB

OS =  RHEL 7
RAM = 90GB
Please do suggest what else I can try further to improve performance. Any tuning parameters apart from mentioned above. If there are many parameters then any good reference for that as well.
Thanks.


texts: How to use except but excluding a column from the except operator comparison -psql?

texts:
I want to compare two sets of columns but exclude one column from the comparison as it will never be in the second table and I need it on my result.
So, explaining:

columns for both tables:

ID (unique value, so I do not want to compare)
amount
email
date

What I want to achieve:
Get all transactions from table 1 that has the exact same values on amount, email and date. I also want the ID to be in the final result set, but if I compare using the except clause, I will have 0 results at my query.

code:

Select /*ignoreAtComparison*/ t1.ID, t1.amount, t1.email, t1.date from t1

except

Select /*ignoreAtComparison*/ t2.ID, t2.amount, t2.email, t2.date from t2 ;

How can I do this?


texts:
I am currently working on a project handling a lot of data, and I was wondering which is the best way to store it in a MySQL database.

I am parsing about 150 CSV files, which contain approximately 40000 rows each, for a total of around 6M rows.

Each of these rows has to be stored using a unique ID and the CSV ID, and contains 20 other cells, as such :

    unique_id | csv_id | col_1 | ... | col_20

For the moment, I am storing all these rows in just one table in my database, but I'm afraid this is not the best way to store them, and that a simple SELECT query might take forever to be executed.

Moreover, each week, about 10% of this data will be updated, and 10% more will be added, which means that two months from now, I'll have twice as many rows, and so on.

Knowing that, without this table, my database is already about 500MB big, here are some of the solutions I'm considering :

Use a unique table for all rows (this is the solution established for now)

Pros : Easy to manage
Cons : I'm afraid it might be too heavy to handle queries correctly, especially after a few months

Create one table per CSV file

Pros : Data won't be too big to handle, even after several months
Cons : It might be difficult to manage, if and when I'll have to parse new CSV files

Create and use another database just for these elements, and then using one of the solutions above to store the data

Pros : This solution will allow my primary database to stay light
Cons : I don't really know if simultaneously connecting to two different DBs will slow my website more than connecting to a larger DB.

This is the first time I have to manage this much data, which is why I may seem a little confused, but I'm eager for your advice.

Cheers,
Roberto


texts: Replication in same server within different databases. Mysql 5.6

texts:
What I have :

a windows machine with one instance of MySQL 5.6 Server.
two database named test and test2.
Test database has a table called activity with columns id and class

What I need :

replication of test.activity table to test2 database with a condition that Test.activity.class = 'B'.

I know how to replicate with in different server. But not for same server within different database.I have checked this link , but it does not give enough information.


texts: Is there a valid reason to alias a column to the exact same name/case?

texts:
I inherited some code like this:

SELECT
  Col_1,
  Col_2 Col_2,
  Col_3
...

Does the Col_2 alias actually accomplish anything, or is it completely unneeded?


texts: Ola Hallengren script not deleting old backup files but simply taking time

texts:
We recently migrated to a new SQL Server and the SQL Services are running under a Service Account. I did observe that the Service Account do not have FULL rights on G drive and the old files are not being cleaned up.

Question: Why doesn't the xp_delete_file procedure Error out when it cannot delete a file OR simply move on instead of waiting for around 9.5 minutes?

Also, doesn't the job error if it cannot delete the old backups? We will not know until we receive LOW DISK SPACE alert !!

Here is the log details.

Date and time: 2017-05-23 02:02:17
Command: BACKUP LOG [DBName] TO DISK = N'G:\SQLBackups\ServerName\DBName\LOG\ServerName_DBName_LOG_20170523_020217.trn' WITH CHECKSUM, COMPRESSION
Processed 17 pages for database 'DBName', file 'DBFileName_Log' on file 1.
BACKUP LOG successfully processed 17 pages in 1.206 seconds (0.110 MB/sec).
Outcome: Succeeded
Duration: 00:00:01
Date and time: 2017-05-23 02:02:18

Date and time: 2017-05-23 02:02:18
Command: RESTORE VERIFYONLY FROM DISK = N'G:\SQLBackups\ServerName\DBName\LOG\ServerName_DBName_LOG_20170523_020217.trn' WITH CHECKSUM
The backup set on file 1 is valid.
Outcome: Succeeded
Duration: 00:00:02
Date and time: 2017-05-23 02:02:20

Date and time: 2017-05-23 02:02:20
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'G:\SQLBackups\ServerName\DBName\LOG', 'trn', '2017-05-16T02:02:20' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)
Outcome: Succeeded
Duration: 00:09:28
Date and time: 2017-05-23 02:11:48

P.S. The Service Account was granted FULL rights and the old files are being cleaned up.

Version Info: SQL Server 2012 SP3 CU8 / Windows Server 2012


texts: Databases created with different collations - why?

texts:
We have a SQL Server 2014 instance, which houses many related databases. New ones are added via our script regularly. When our app is used to create the database - the databases are created with collation SQL_Latin1_General_CP1_CI_AS  as expected.

But there are now databases with Latin1_General_CI_AS collation. They were not created by our app (we looked at the database creator). For example, "Pete" is the db owner and some DBs he created would have Latin1_General_CI_AS collation and others SQL_Latin1_General_CP1_CI_AS.

Can anyone point out what I could investigate please - should the default collation not always be the same as the SQL instance?
What if I created a database remotely - from PC1 with Collation1 and PC2 with Collation2 - would the database be created with different collations?

Any ideas how this could happen - multiple collations for one user?
Thanks


texts: Self join increase paging query performance


texts: Normalising shared entities and applying constraints

texts:
I'm looking for a specific "best practice" or "pattern" concerning entities that are shared between different entities, having a relation to one of many.

For example, one may have the generic entity "Address", which could be used to store the common address fields for customers, suppliers, employees, etc...

Would a seasoned DBA take that route or would he rather add the fields to the corresponding entities? I'm also thinking about maintainability, constraints that may (in the future) differ depending on the entity, things like that.

I would love to get references to any authoritative or established works on the subject.


texts: Updating table and column names in an existing postgresql db

texts:
What is the best way to go about updating table names and column names in a postgresql database that already has loads of data but, as part of an overall web app upgrade, the database is also being modified, and those modifications include renaming poorly named tables (the largest table is named, “data”!) and column names?

Especially I am concerned about breaking things such as many PK’s are bigserial or serial, and then PK and FK naming will not longer be consistent without manually re-naming all of them.

Is there a recommended way to go about these changes?

If I start renaming tables, and then columns, and then PK, FK, and sequences, am I running the risk of breaking constraints or sequences? Or would it be better to leave the names of PK, FK, and sequences "as is"?


texts: Imported database very slow on a simple query

texts:
A production database has been exported to a separate location for various reasons, however the dumped/imported database is performing much slower on some very specific queries. Here is 2 queries on production:

mysql> SELECT count(id) FROM payments;
+-----------+
| count(id) |
+-----------+
|    964390 |
+-----------+
1 row in set (0.14 sec)

mysql> SELECT count(id) FROM payments WHERE is_held = True;
+-----------+
| count(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.32 sec)

meanwhile the imported database:

mysql> SELECT count(id) FROM payments;
+-----------+
| count(id) |
+-----------+
|    963770 |
+-----------+
1 row in set (0.20 sec)

mysql> SELECT count(id) FROM payments WHERE is_held = True;
+-----------+
| count(id) |
+-----------+
|         0 |
+-----------+
1 row in set (37.20 sec)

So when including a non-indexed boolean in the WHERE-clause, the performance of the imported database drops by over 100 times.

An explain for the queries shows the same result in both databases:

mysql> explain SELECT count(id) FROM payments WHERE is_held = True;
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+--------+---------------+
|  1 | SIMPLE      | payments | ALL  | NULL          | NULL | NULL    | NULL | 739335 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+--------+---------------+
1 row in set (0.00 sec)

I've tried an OPTIMIZE payments; run on the imported database, it didn't help, though the output was:

mysql> OPTIMIZE TABLE payments;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| payments | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| payments | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2 min 13.01 sec)

The database is hosted in Amazon RDS, so my access to slow query logs and looking at hardware etc is a bit limited.

Being on RDS, I've tried upsizing the instance type a couple times, which I believe will move the database to new hardware, and in each case there's been no significant improvement.

I've run this query many times over the last few days, but now when I run it twice in a row it finishes instantly, and I can start a new MySQL connection and it is instant from the first run. I can even scroll up my terminal window and observe multiple earlier runs where it was slow, though in each case delineated by an RDS instance resize.

The reason I investigated this in the first place is a scheduled task hits this every 30 seconds and the scheduled tasks were starting to overlap and consume all my workers from other tasks so it was definitely recurring regularly before, not just first run.

I've re-enabled the scheduled task again, and it seems that the first run was slow, 33 seconds, but the second run was 2 seconds.


texts: What would the relational diagram look like in this situation?

texts:
Situation

Last week I had to do an assignment, and I did not agree with my teacher's ERD. She used a n-ary relationship while I used two binary relationships. I would like to know a true dba's (you) opinion on this.

The original assignment was to design an ERD, but since the ERD notation we use at school is very uncommon I'm going to ask what the relational diagram is supposed to look like. I think I can figure it out by myself once I know what the relational diagram is supposed to look like.

Assignment

There's a housing assocation company that wants a database.

When a customer registers, the customer has to fill in his details (name, dob, phonenumber), and at that moment he also has to fill in a second form. There, he has to specify what city's he wants to live in, and what types of houses he wants to live in (house, mansion, villa etc.). It's required the customer fills in at least one city and one house.

The second form also has some other fields that the customer must fill in, such as a maximum price and a remark (e.g. the house has to have a swimming pool).

My answer

CUSTOMER: customer# (PK), firstname, lastname, dob, phonenumber
FORM: customer# (PK), dateofregister, remark, maxprice
contains1: customer# (PK), city (PK)
contains2: customer# (PK), housetype (PK)

CUSTOMER 1..1 FORM
FORM 1..n contains1
FORM 1..n contains2

My teachers answer

She merges both CUSTOMER and FORM into the same table since they have the same key customer#, and creates the table CONTAINS: customer# (PK), city (PK), housetype (PK).

I don't agree with her, since I could specify housetype twice with the same customer#. Same thing goes for city. I also don't agree on the fact that she puts all the FORM attributes into the CUSTOMER table. It looks sloppy IMO, and I believe you would split it up in the 3rd normal form and you would end up with my result again...


texts: SQL Server - separate database for reports?

texts:
On our SQL Server, we have a database for each of our web apps. For reports, we use Reporting Services and all report data (including report parameters) come from stored procedures.

The stored procedures are in the same database as the data in the report. So, for example, the procs that serve the Stock reports are in the Stock database. Some reports show information from more than one database and then the proc will be in one of those source databases. The report parameters get their data from procs in an Enterprise database that has data like stores, employees etc.

This means that all reports have at least a connection to the Enterprise database and another connection to another database -- and sometimes more than that.

My question is: is there a benefit of moving the reporting procs into a separate "Reports" database. I know the benefits of moving reports onto another server and I'm not talking about that -- this would be on the same server.

Things that might affect this are:

Does having more than one database connection for a report, affect the speed of the report?
Would having the reporting proc in a separate database from the data, prevent us from using indexed views?
Have you found it easier / harder is administer you reports in a separate database?

Please let me know what you think.


texts: Can an entity be an attribute of another entity?

texts:
For ER/EER Diagram:

I have one entity Stadium and another entity Match. In Match entity I have the stadium as an attribute.

The SOCCER database shall keep information about the stadiums used as venues for the
matches.

Each STADIUM has:

a stadium id (SId – a surrogate key), stadium name
(SName), City, and Capacity (max. no. of spectators).

For each MATCH (game) played, we will keep track of the following information:

match/game id (GId – a surrogate key), game type (GType – see description below in
item 7), date the match was played (GDate), the venue where the game was played
(Stadium), first team (Team1), second team (Team2), first team score (Score1), and
second team score (Score2).


texts: How do I either set a password for postgres, properly use the postgres user, or stop postgres from asking me for a password?

texts:
I am fairly new to programming, and I am using postgres 12.2.

I am having a lot of trouble even setting up postgres. When I try to connect to a database, such as using

psql -U postgres -d demo

which is the first command in the list of commands here, I get prompted for a password, which I have never set.

As far as I can tell, postgres should be a "super-user" that does not have a password. Through using resources online, I have used the command initdb to make an initial database cluster (though I do not know how to use this cluster) and I have set the bin file to be in the path system variable, something which I do not understand the purpose of.

Here is an example:

C:\>psql -U postgres -d C:\usr\local\pgsql\data\base\1

Password for user postgres:
psql: error: could not connect to server: FATAL:  password authentication failed for user "postgres"


texts:
Can someone give me a brief intro to indexes? My questions:

Why would I want to use indexes?
How I should use them? It looks like I can create multiple indexes on a single table - what is that for?
How can I find out if I need to use them?
Are indexes created automatically for me with InnoDB?


texts: How to set selinux permission for non-standard mysql bin log path?

texts:
By default, mysql bin log works in folder /var/log/mysql. but moving to any other share such /mylogs/mysql gives permission error.

Below is the exact message:

usr/sbin/mysqld: File '/mylogs/mysql/mysql-bin.index' not found (Errcode: 13 - Permission denied)

how to fix this without disabling selinux?

There is similar problem in the link below but solution is not acceptable as I cannot disable SELinux.
mysql bin-log.index not found


texts: SSAS - how to script the General settings? where are them stored? how can they be queried?

texts:
I believe I am an administrator on server BIREPLON1 SSAS as you can see on the picture below.

These security permissions can easily be scripted.

this is an example:
apologies foe the horizontal scroll, but that's how it is.

<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'>
    <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Object />
        <ObjectDefinition>
            <Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
                <ID>SASBIDEV01</ID>
                <Name>SASBIDEV01</Name>
            </Server>
        </ObjectDefinition>
    </Alter>
    <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Object>
            <RoleID>Administrators</RoleID>
        </Object>
        <ObjectDefinition>
            <Role xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
                <ID>Administrators</ID>
                <Name>Administrators</Name>
                <Members>
                    <Member>
                        <Name>mycompany.CO.UK\BIUsersDEV</Name>
                    </Member>
                </Members>
            </Role>
        </ObjectDefinition>
    </Alter>
</Batch>

Is there a way to script the settings found on the "General" tab, as seen on the picture below?

otherwise,

where are these permissions stored?

how can they be queried?


texts: Is there any Cross-platform SQL editors that allow joins from different databases?

texts:
Initially tried dbeaver but after configuring it, I realized that you can't perform joins from different data bases.

Also tried TeamSQL which apparently does what I need but looks to be designed without privacy in mind (requires constant connection with internet).

Select a.Product, b.Product, a.ID, b.ID, c.Product, c.ID
FROM SQL_Server 1 a
LEFT JOIN Oracle b
ON a.ID = b.ID
LEFT JOIN SQL Server 2 (another location) c
ON a.ID = c.ID

Thank you for your time.


texts: Find the number of where statements that matched in postgresQL

texts:
Right now I have a query that looks something like this

SELECT id
FROM products
WHERE products.details->'make' = ?
    OR products.details->'model' = ?
    OR products.details->'year' = ?

I'd like to sort the results by how many of the where statements matched, so for example if the three terms that were used in the query were ford, f150, 1985, and I got two results. One that had all three terms in it (a ford f150 from 1985), and a second result that had only two matches (a ford f150 from 1991).

Is there a way for me to do this with postgreSQL 9.3, or will I need to use my application layer to figure out how many matches there were?


texts: What to use instead of WITH ORDINALITY in PostgreSQL 9.3


texts: Change length of varchar on live prod table

texts:
I have a MS SQL Server 2008 R2 DB server currently in use with a production app.

A new enhancement to the app now requires a varchar(100) column in a table to be increased in length.

Can the length of this existing column in the prod DB be increased without affecting the current data?

Does this change have to be completed during off-hours to avoid disruption of service?


texts: Non-priviledge way to restrict UPD/DEL to user knowing certificate pass

texts:
I am assigned to researching column-level encryption, with the additional requirement of the sa not being able to read the encryted column(s).

The idea as I've read in this DBSE post is to create a password-protected certificate, a bunch of symmetric keys under it, and the client should open these key(s) with the password only they are aware.

This seems to ensure that indeed noone other than the client can read the encrypted data. However, they can still modify them (delete them or update=corrupt them). As I understand it, using ENC/DECRYPTBYKEY is just a scalar function, independent of tables. That means that whether someone uses these functions on a specific column does not "inform" the database that these columns hold encrypted data. Thus, the only way to "protect" the data is the old school priviledge one.

Have I missed something? Is there a way to restrict a column to only accept updates or deletes if a specific key is open? Maybe even, in the case of update, require that the inserted. value is always the output of the EncryptByKey function?


texts: Is there any better way in oracle to enable parallel other than this?

texts:
I want to do do following set of operation.

execute immediate "alter session enable parallel query";

execute immediate "alter session enable parallel ddl";

execute immediate "alter session set parallel_force_local=false";

Is there any better way of doing in oracle 11g/12c?

Thanks in advance.


texts: Will logRotate cause any unavailability in MongoDB service?

texts:
In another question, I've mentioned that how would I rotate logs in MongoDB Replica Set installed in Windows Server. However, it seems there's no built-in way to get it automatically on Windows - only with 3rd party tools.

That being said, I was wondering what would happen if I connect into MongoDB and execute

use admin
db.runCommand( { logRotate : 1 } )

Will it cause any unavailability in MongoDB service? Or will be just a matter to stop log capture for couple seconds and start writing in the new file?


texts: select in (select) allow to get duplicate values

texts:
I need help in one SQL query.

I have select in select:

select column1 from table where column2 in (
--there I have select command what returns me 609 rows with duplicate values
)

and I need to get in first select 609 rows with duplicates. But I only get 12 rows because the select in command ignores duplicate values in second select.

I need to get certain values (609rows) not only to find what values I get two or more times.
I will join this result to another result.

My query looks like this :

This is the first select I was talking about

select  serialnumber from ffPackage where ID in (

and this is the select from what I get 609 rows.

select parentID from ffPackage where SerialNumber in (
select ffPackage.SerialNumber from ffEmployee, ffHistory, ffSerialNumber, ffPackage, ffUnitDetail
where ffHistory.UnitID in (
select UnitID from ffUnitDetail where OutmostPackageID in (
select ID from ffPackage where SerialNumber = 'xxxxxxxx'))
and ffHistory.EmployeeID = ffEmployee.ID
and ffHistory.UnitID = ffSerialNumber.UnitID
and ffHistory.UnitStateID = 'xxxx'
and ffUnitDetail.UnitID = ffHistory.UnitID
and ffUnitDetail.InmostPackageID = ffPackage.ID
))


texts: Is there a timeout for a query over database link?

texts:
Edit/Preface:
This question has been migrated from SO as I am particularly interested on the question on timeouts on DB link queries. The provided workaround from SO is somewhat OK, but I'm really interested in the question itself.

Motivation:
I've had one query running "forever" (more than 2 days, until I killed the session), which was using a database link.
Problem seemed to be that the remote database became unavailable and for some yet unkown reason no ORA-02068 was raised (not to be discussed here) and the query just waited and waited.

(The query is issued by a dbms_scheduler job, which executes a procedure in a PL/SQL package. As a consequence, the job was also stuck. But that's not of special interest for the core of this question)

I've simulated this situation by putting one of my test DBs in quiesce mode and queried it over a database link. As anticipated, the query was waiting until either manually canceled or the remote DB was unquiesced.

Question:
I have no control over the remote database's behavior and up-time, so I am in search for some possibility to set a timeout on a query which is using a database link.

I've already looked into profiles (CPU_PER_CALL etc), sqlnet.ora parameters, adding local naming parameters directly into the connect string (such as adding (connect_timeout=10) to the database link definition), running a command with ... for update wait 1, but they either work for busy or idle sessions, but not for sessions in wait.

So I am in search for some option on the "local" side of the database link, which kind of sets a timeout for queries over database links.
Some solution like alter session set xyz or select ... from a@b "wait 100" --(yes, I know this syntax doesn't exist) would be appreciated, as I have no DBA rights on these particular DBs.

I'm currently on 10gR2 but upgrading to 11gR2 in a few weeks, so ideas for any of these versions will be useful.


texts: Best way to filter result set by secondary key

texts:
I have a table with a primary key and a secondary key, something like

CREATE TABLE Entry (
     PrimaryKey int NOT NULL,
     SecondaryKey int NOT NULL,
     Data nvarchar(100) NOT NULL,
     DateCreated DATETIME NOT NULL
);

    INSERT INTO Entry VALUES(1,1,'test',getutcdate()),
(2,1,'test',getutcdate()),
(3,2,'test',getutcdate()),
(4,3,'test',getutcdate()),
(5,3,'test',getutcdate()),
(6,3,'test',getutcdate()),
(7,4,'test',getutcdate()),
(8,4,'test',getutcdate())

I want to be able to return the entries sorted by datecreated, but only 1 entry per secondary key.  so with the above data, i would only get 4 rows back, the most recent entry for each secondary key.

Whats the best way to do this?

UPDATE:
question answered below, but the question i have next is 'consider the table if it has multiple data fields, what is a better query?'  The original response, where everything is in the CTE:

with CTE as (
 select [PrimaryKey], SecondaryKey, [Data1],[Data2],[Data3],...DateCreated,
        rn = row_number() over (partition by SecondaryKey order by DateCreated desc)
  from dbo.Entry
  )
 select [PrimaryKey], SecondaryKey, [Data1],[Data2],[Data3],...,DateCreated
 From CTE where rn = 1

or in another query, where the data kept in the CTE is minimized:

    with CTE as (
     select [PrimaryKey], SecondaryKey, DateCreated,
            rn = row_number() over (partition by SecondaryKey order by DateCreated desc)
      from dbo.Entry
      )
     SELECT PrimaryKey,SecondaryKey, [Data1],[Data2],[Data3],...,DateCreated
FROM ENTRY
WHERE PrimaryKey IN( select [PrimaryKey] FROM CTE where rn = 1 )


texts: How can I get a copy of the SQL generated by Management Studio?

texts: If I save a table in Management Studio's Table Designer for example, I believe it runs DDL SQL in the background. Is it possible for me to grab this SQL so that I can save it to a file?


texts: How do I get SQL Server agent to show up in SSMS for a non-sysadmin user?

texts:
I have already mapped the user to the msdb database and assigned him the role of SQLAgentOperatorRole, but the user still can't see the SQL Server Agent listed when he connects.

The SQL Server Agent does show up fine when I log in as a sysadmin.


texts: Running sum total over 14 days for each date

texts:
In a database of transactions spanning about 50 customers over a period of 8 months, I need to find which customers have 0 total transactions over a consecutive 14-day period. The customer table (customer_tbl) has the following general structure:
cust_id, date, new_transactions, total_transactions

So for every row, I want a column where it shows the total number of transactions that the customer had, for the past 14 days. I have tried to create a view to display customers who had 0 total transactions using the code below.
create view sumoftransactions as SELECT
cust_id
, date
, REVERSE_RUNNING_SUM
FROM
(
    SELECT
      cust_id
    , date
    , SUM(new_transactions) OVER (PARTITION BY cust_id ORDER BY date DESC, VALID_FLAG ASC) REVERSE_RUNNING_SUM
    , VALID_FLAG
    FROM
    (
        SELECT cust_id
        , date
        , new_transactions
        , 1 VALID_FLAG
        FROM customer_tbl

        UNION ALL

        SELECT cust_id
        , DATE_ADD(date, interval -14 day) date
        , -1 * new_transactions
        , -1 VALID_FLAG
        FROM customer_tbl
    ) t
) tt
WHERE tt.VALID_FLAG = 1
and tt.reverse_running_sum = 0;

However the output is skewed. I believe it has something to do with the cut-off dates. Each customer has a different date for their first record. The running sum formula includes the record of their first transaction date into the output because there are no dates before this, so the running sum is calculated to be 0, even though it is not.
How do I rectify this code? The following is some sample data. To simplify it, the following table only has 3 customers and covers a period of one week. I want to generate the customer_id and dates whereby there have been at least 3 consecutive days of 0 transactions. The desired output is written below.
CREATE TABLE `customer_tbl` (
  `cust_id` text,
  `date` date default null,
  `new_transactions` text,
  `total_transactions` text
)

INSERT INTO `customer_tbl` VALUES
(‘10001’, ‘2020-02-01’, ‘1’, ‘1’),
(‘10001’, 2020-02-02’, ‘1’, ‘2’),
(‘10001’, ‘2020-02-03’, ‘0’, ‘2’),
(‘10001’, ‘2020-02-03’, ‘0’, ‘2’),
(‘10001’, ‘2020-02-04’, ‘0’, ‘2’),
(‘10001’, ‘2020-02-05’, ‘0’, ‘2’),
(‘10001’, ‘2020-02-06’, ‘1’, ‘3’),
(‘10002’, ‘2020-02-01’, ‘1’, ‘1’),
(‘10002’, 2020-02-02’, ‘1’, ‘2’),
(‘10002’, ‘2020-02-03’, ‘4’, ‘6’),
(‘10002’, ‘2020-02-03’, ‘0’, ‘6’),
(‘10002’, ‘2020-02-04’, ‘1’, ‘7’),
(‘10002’, ‘2020-02-05’, ‘0’, ‘7’),
(‘10002’, ‘2020-02-06’, ‘1’, ‘8’),
(‘10003’, ‘2020-02-01’, ‘0’, ‘0’),
(‘10003’, 2020-02-02’, ‘0’, ‘0’),
(‘10003’, ‘2020-02-03’, ‘1’, ‘1’),
(‘10003’, ‘2020-02-03’, ‘0’, ‘1’),
(‘10003’, ‘2020-02-04’, ‘0’, ‘1’),
(‘10003’, ‘2020-02-05’, ‘0’, ‘1’),
(‘10003’, ‘2020-02-06’, ‘1’, ‘2’);


texts: using a join to find data in a second table?

texts:
I'm very new to SQL and don't really understand JOINS as much as I would like, or even if this would be a good case for a join.

I have two tables:

Table1

Headings:
"folder","OwnerNumber"

Data
"folderx","1,2"
"foldery","1,3"

and

table2:

Headings:
"record","emailaddress","samId"

data
"1","user1@domainX.com","user1"
"2","Billy@domainy.com","william"
"3","Admin@domainz.com","Tom"

My application has starts off with the folder name, and the application user's SAM ID. In this case, the app user is 'Tom' and he wants to know about 'folderx'.

I need a query that I can pass in "folderx" and "Tom" into and get the email addresses that belong to the users 1, 2 and Tom.

The same query, if asked for "foldery" and user "Mike" should give me email addresses for users 1 & 3 (with no match for the SAMID of what was passed in).

Now, I know I can do this with multiple calls from my app.  One to get the numbers:

get ownernumber from table1 where folder = 'folderx'

then taking that value, and making the query in my app,

select emailsaddresses from table2 where record='1' or record='2' or samID = 'Tom'

But I figured there had to be a better way, and a way that didn't involve making two calls from my application to SQL to get the data that was all within SQL to begin with.

I suspect this is a JOIN, but what I am not clear on is:

The response from the first query gives me a single string "1,2" and am using my application to split that, and reformat it to a proper select statements for my second query.  How would I break that up within a SQL query?
if I were able to simply do some kind of @Query2 = split('result from query 1'), how would I then use that result set to make the query that goes against table 2?  Items may be listed as "1,2,3" or as "1, 2, 3" in the source, so possible spaces will need to be taken into account.

All I need when I am done is the email addresses that own a specific folder, and the email address of any matching SAMID (if one exists).

Rules:

Folder "ownernumbers" should never be blank
SAMID may or may not have a matching entry.
There may be duplicate email addresses in table2 (but not usually)
Record number is assigned by SQL so it should always be unique in Table2.

My database is the latest version of SQL Express downloaded from Microsoft.  My app is written in Powershell but for the sake of this question, I doubt that it matters.

If desired, I can add the powershell code, but this really isn't a powershell specific question.

Finally, I won't be offended if you treat me like a newbie here.  When it comes to SQL, I am self taught (bad practices as well as good) and sometimes need an answer spoon fed so I understand it.  Please do not assume I know what I am doing here.  If you make assumptions, please detail them so I can understanding them fully.

This is a very small dataset.  Maybe 1000 records in all.  Query does not have to be under efficient and a more understandable solution is preferred to a terse but more efficient one.


texts: Row level security and full text searching

texts:
How do you approach the problem of resolving access permissions when matching tens of thousands of users with tens of millions of documents containing unstructured data? How do you even build and represent a filtered folder structure for navigation? In particular, I'm perplexed as to how you would provide functional ways to search only the content currently accessible by any given user.

Would you filter the result set from a full text query through some sort of ACL table? Before or after other filters or merging with other result sets? Build and maintain in memory bitmap- or hash-tables for active users? Stored procedures? Inverted indexing? De-normalization?

Is there an established way of doing this or is everyone brewing their own solutions?

Seeing how this must be core functionality in any ECM or just about any type of content management system really, I'm sure there must be numerous solutions and best practices available. What might be  good sources of information?

I'm not sure if any large scale services like Evernote even include shared documents in full text searches, but if they do, I sure would like to know how.

If it comes down to a product specific level, I'm biased towards Oracle Database and Lucene XML Index.


texts:
I have a stored procedure and several statements in it. The below is execution time statistics of the procedure:

  SQL Server parse and compile time:     CPU time = 0 ms, elapsed time =
  0 ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 1 ms.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 11484 ms,  elapsed time =
  2277 ms.

  (29 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 1 ms.

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.

  SQL Server Execution Times:    CPU time = 11484 ms,  elapsed time =
  2283 ms.

As you can see the total of 4562 ms. But when I look at the status bar I see that it took 2 seconds. I expect that it should be 4 seconds. Why are these two different?


texts: I'm discovering a number of DBs in my portfolio that have been created with default autogrow settings (either 1 MB or 10% increments) that have expanded for an extended period of time. If I wanted to get a gauge of the amount of external fragmentation for each of the DB files, can I get from metadata how many times a DB file has been modified in size (either by autogrow or manually)? To clarify, can I get from metadata the history of DB file modifications for the life of the DB, not just since instance restart?


texts: High Availability in PostgreSQL with automated failover

texts:
I am currently working on designing an architecture for High Availability in PostgreSQL for a database of approximately 5TB in size. Also, I would like to re-direct my reads to a slave node(preferably SR & Hot Standby) as every hour there are bulk imports. After my research, I have come up with three possible architectures, but would like to know how do I handle STONITH in these cases and also the best suitable of the three methods,

1) Pgpool2 (with HA) --> Master --> Slave (SR & HS)

2) Clustering with Pacemaker + Corosync + DRBD (for a 5 TB database) Performance?

3) PGPOOL2(with HA) + REPMGR


texts: Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index

texts:
One of the largest tables in our system has its primary key setup as a globally ranged partitioned (in huge, useless, multi-billion value ranges) non-unique index.  Its values are just a sequence value and they are definitely unique.  I am wanting to change the index to just a normal global (the table is partitioned), unique index.

Can I do that without dropping the constraint and index and then rebuilding - during which time the table would be without this vital index?


texts: Check if restaurant sells only the pizzas that customer likes using SQL

texts:
Tables for SQL query

1. Likes(cname, pizza)

2. Customers(cname, area)

3. Restaurants(rname, area)

4. Sells (rname, pizza, price)

Condition:

Customer likes all the pizza in the restaurant
Restaurant has the same location as customer

Result: List of customers that fulfill the conditions

My attempt:

    SELECT DISTINCT Customer (
    FROM Sells S, Likes L, Restaurant R, Customer C
    WHERE C.area = R.area     -- Check whether location of customer = restaurant
    AND L.pizza = S.pizza     -- But i'm not clear how to check if the restaurant sells only the pizzas that the particular customer likes.
    );


texts: Always On Primary Replica On Prem, Secondary Replica On Azure

texts:
We want to create an Always On Availability group where the primary replica will be On-premises and the secondary replica will be on an Azure VM. I have researched and I know it's possible to do this, but in all the examples I find, they always have a secondary replica on-prem as well and then the 3rd VM is the one that goes to azure. We only want to have 2 replicas, one on-prem and one on azure. Is this possible?

I have read the documentation to use the "Add Azure Replica Wizard" and one of the prerequisites is: Your availability group must contain on-premise availability replicas. I'm not sure if that means that I have to have at least one secondary replica on-prem or if it only means that I have to create the availability group first on-prem and then only add the secondary replica.

Thanks for your help and guidance!


texts: Creating Linked Server - Impersonation of Windows Authentication

texts:
I am trying to set up a linked server on Server A to Server B using windows authentication.

Using SSMS, I can connect to both Server A (from ServerB) and Server B (From Server A) using Windows Authentication and the username DOMAIN\User.

Additionally, when I log into Server A from Server B or vice versa and run the following command:

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

the result tells me the auth_scheme is KERBEROS

The SQL Server service on ServerA is running as the same domain account as I am trying to log in as:

Get-ADUser UserAccount -Properties trustedfordelegation|select trustedfordelegation

returns False

When trying to set up the linked server on Server A, I get an error message which is different depending on which option is selected in the For a login not defined in the list above, connections will: section.

To me this suggests that the config in the Local server login to remote server login mappings: is being ignored.

I have set the Local Login to DOMAIN\User and clicked the Impersonate box

Server A is SQL Server 2008R2 and Server B is SQL Server 2016 SP1

Can someone confirm what I need to do to be able to set up the linked server to connect using the DOMAIN\User account?


texts: Creating a GroupBy query to include a result for when no results match

texts:
Let's say I need a query to see sales results by Month and I want the result-set to include results for each month whether there were any sales in that month or not.

And let's say my table "sales" has the columns "sales_date" and "sales_amount" and I want a result-set to look like:

Jan, 100.00
Feb, 240.00
Mar, 0.00
Apr, 430.00

So, if I want to sum the values by month and there aren't any records for Mar, I still want to see a result for Mar that's 0.  How would I do that?


texts: Can I change table structure in a transaction and then roll it back if there is an error?

texts:
I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements if something goes wrong.

Is this possible, or is it only data that can be rolled back?


texts:
I have this query. this is the second query. the first one was using the left/or outside the subquery, and the query plan was horrible.( Edited the question with the full sintax ):

https://www.brentozar.com/pastetheplan/?id=HJEioh56N

I have a nested loop (inner join) with 97% in the query plan.

I'm sure the problem is the OR inside the second join because I changed something here and there and I could get rid of them but I would like to be sure what would be the best way to deal with data like these. also all those tables has millions of rows.

Table Definition:

CREATE TABLE [DBO].[TABLE1](
    [F1] [int] NOT NULL,
    [F1] [varchar](16) NOT NULL,
    [F3] [money] NOT NULL,
    [F4] [money] NOT NULL,

I know created this index:

CREATE NONCLUSTERED INDEX IX_TB1 ON DBO.TABLE1
(
    F1,
    F2)

That index seek is now 14%, but got a HASH MATCH with 82% cost.


texts: Access denied for user 'root'@'localhost' using Password NO

texts:
I have enabled SQL SLOW LOG Queries in my.cnf file but i need to restart mysql and when i try to run this command

sudo /etc/init.d/mysql restart

for restarting it throws this error.

Stopping MySQL database server: mysqld failed!
Starting MySQL database server: mysqld already running.
Warning: World-writable config file '/etc/mysql/debian.cnf' is ignored
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

[Image] :


texts:
I have this in my.cnf:

tmpdir = /dev/shm

and I'm getting this in my error logs:

[23-Apr-2011 10:22:08] WordPress database error Incorrect key file for table '/dev/shm/#sql_30f1_0.MYI'; try to repair it for query  SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY RAND() DESC LIMIT 0, 5 made by require, require_once, include, get_posts, WP_Query->query, WP_Query->get_posts

The file size is very large - over 3 GB in size, but there are other tables in that directory getting the same error are 1024 bytes - not sure what is going on here

How do I try to repair a temp table? I'm getting this error A LOT!

Dan

PS on a separate note - anybody know where in thewordpress core this call is being made? I haven't been able to track it down - I want to disable it because of the ORDER BY RAND() call in it.


texts: How can I execute multiple procedures continuously?

texts:
Here is my code:
$query = "CALL user_top_categories_score(?, 'ALL', 0, 1)";
$sth = $this->dbh->prepare($query);
$sth->execute([$user_id]);
$category = $sth->fetchAll(PDO::FETCH_ASSOC);

$query = "CALL user_top_tags_score(?, 'ALL', 0, 3)";
$sth = $this->dbh->prepare($query);
$sth->execute([$user_id]);
$tags = $sth->fetchAll(PDO::FETCH_ASSOC);

It throws this error:

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. in C:\xampp\htdocs\myweb\others\user.php:71 Stack trace: #0 C:\xampp\htdocs\myweb\others\user.php(71): PDO->prepare('CALL user_top_t...') #1 C:\xampp\htdocs\myweb\application\other.php(24): user->index() #2 C:\xampp\htdocs\myweb\index.php(152): require_once('C:\xampp\htdocs...') #3 {main} thrown in C:\xampp\htdocs\myweb\others\user.php on line 71

Also I've used closeCursor() right after fetchAll(), based on this solution. But sadly it throws a new  error:

Warning: Packets out of order. Expected 1 received 9. Packet size=7 in C:\xampp\htdocs\myweb\others\user.php on line 72
Warning: PDO::prepare(): MySQL server has gone away in C:\xampp\htdocs\myweb\others\user.php on line 72
Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in C:\xampp\htdocs\myweb\others\user.php:72 Stack trace: #0 C:\xampp\htdocs\myweb\others\user.php(72): PDO->prepare('CALL user_top_t...') #1 C:\xampp\htdocs\myweb\application\other.php(24): user->index() #2 C:\xampp\htdocs\myweb\index.php(152): require_once('C:\xampp\htdocs...') #3 {main} thrown in C:\xampp\htdocs\myweb\others\user.php on line 72

Any idea how can I fix the problem?

Noted1: Each of queries above work separately. I mean, when I call a single procedure, it works as well.
Noted2: Each procedure returns a result set. I mean there is a SELECT statement in those procedures, which may return multiple rows.


texts:
I know that this works for a database called DB01_20161110:

ALTER AVAILABILITY GROUP AG1 ADD DATABASE DB01_20161110

I'm trying to add today's date automatically:

DECLARE @DateString   AS VARCHAR(256)
SET @DateString =     cast(year(getdate()) as varchar(4))
+     right('0' + cast(month(getdate()) as varchar(2)), 2)
+     right('0' + cast(day(getdate()) as varchar(2)), 2)
DECLARE @DB AS VARCHAR (40)
SET @DB = 'DB01_' + @DateString

ALTER AVAILABILITY GROUP AG1 ADD DATABASE @DB
GO

I get:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '@DB'

Also tried:

DECLARE @DateString   AS VARCHAR(256)
SET @DateString =     cast(year(getdate()) as varchar(4))
+     right('0' + cast(month(getdate()) as varchar(2)), 2)
+     right('0' + cast(day(getdate()) as varchar(2)), 2)

ALTER AVAILABILITY GROUP AG1 ADD DATABASE 'DB01_' + @DateString
GO

Same error.

I use the same DECLARE part for other scripts so that's working.

Any advice?


texts: Why does my heap not fill its pages completely while an identical clustered index can fill to m_freeCnt = 0?

texts:
-- Example 1: With Clustered Index

CREATE TABLE tbl5
(
    i VARCHAR(900) NOT NULL
) ;
GO
CREATE CLUSTERED INDEX CIX_tbl5
    ON tbl5 (i ASC) ;
GO

INSERT      INTO tbl5
            ( i )
VALUES
            ( REPLICATE('a' , 900) )
    ,       ( REPLICATE('b' , 900) )
    ,       ( REPLICATE('c' , 900) )
    ,       ( REPLICATE('d' , 900) )
    ,       ( REPLICATE('e' , 900) )
    ,       ( REPLICATE('f' , 900) )
    ,       ( REPLICATE('g' , 900) )
    ,       ( REPLICATE('h' , 900) )  -- 900 x 8  + (13 x 8) = 7200 + 104 = 7304
    ,       ( REPLICATE('i' , 761) ) ;-- 7304 + 761 + 13
GO                                    -- = 8078 + 96(Page Header) + 18 (Slot)
                                      -- = 8192
                                      -- m_freeCnt = 0

DBCC IND(tempdb,tbl5,-1) ; -- everything is IN_ROW_DATA
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(tempdb,1,177,3) ;
GO

-- Example 2: With heap. When column 'i' is 392 m_freeCnt = 387. When 'i' = 393, I get a new page. Why can I not add more bytes to 'i'?

CREATE TABLE tbl6
(
    i VARCHAR(900) NOT NULL
) ;
GO
INSERT      INTO tbl6
            ( i )
VALUES
            ( REPLICATE('a' , 900) )
    ,       ( REPLICATE('b' , 900) )
    ,       ( REPLICATE('c' , 900) )
    ,       ( REPLICATE('d' , 900) )
    ,       ( REPLICATE('e' , 900) )
    ,       ( REPLICATE('f' , 900) )
    ,       ( REPLICATE('g' , 900) )
    ,       ( REPLICATE('h' , 900) )
    ,       ( REPLICATE('i' , 393) ) ;
GO

DBCC IND(tempdb,tbl6,-1);
GO
DBCC TRACEON(3604);
GO
DBCC PAGE(tempdb,1,181,3);
GO


texts: Rebuilding a spatial index with minimum downtime

texts:
We just upgraded from SQL Server 2008 to 2014.
It went fairly well except for a problem we have on a spatial index.
On this table we receive the error

  Cannot insert duplicate key row in object 'sys.extended_index_1527780600_384000' with unique index 'lu_unit__geolocation'. - duplicate key value:(0x20330a3504, 95469304).

A spatial index can't have a unique constraint, something is wrong.

I would think the safer way forward is to rebuild the index. I experimented a bit and I find it takes about 50s to rebuild the index on 1.6 million rows.
The production table is about 5.5 million rows, making it at least 3min when the base table won't be accessible since a spatial index can't be built online.

Does anybody have experience with rebuilding spatial indexes with minimum down time? We can do with 30s but not 3min.


texts:
We would like to set up replication between two SQL Server 2012 instances.

Server "Internal" can see and make connections to server "External". External is completely firewalled from the internal network. No inbound connections are allowed on any port.

We want to set up transactional replication from internal to external for a read-only copy on server External. When configuring External as the subscriber, it wants to connect to the publisher which it cannot do.

All my searching leads me to articles on which ports to forward but that is not going to work for us. Basically what we want to do is have Internal initiate the connection and push updates to a copy on External. Up until now we have been using SSIS packages to push data but it's starting to border on unmanageable.


texts: Running psql/postgres client in an ephemeral mode in a container

texts:
I want to link two containers1 where one is a full-blown Postgres (9.3)/PostGIS server, and another one is pure client needing no configuration. Just bear with me - this is a for an evolutionary POC which will later emerge into reactive microservices architecture, and we'll abandon container linking. For now, I am experimenting.

My question is about the Postgres (9.3) client.

I would like to link client container to postgres server, and just run some trivial queries from psql from the container's bash prompt using either scheduled events (cron), or from a docker's exec command line.

For this, I would like to use psql in the dumbest possible way - no initdb, no pga_hba.conf, not postgres.conf, just do something like:

1. Link containers (assume I have pg_server container running):

docker run -it --link pg_server:pg_server_1 --name pg_client_1 pg_client

2. Run query from client (client_1) to running server container (pg_server_1):

docker exec pg_client_1 psql -U my_user -h pg_server_1 -p 5432 -d my_db -c "select uncertainty_estimate(' land_cover', manifold_envelope(-81.85, 6.8, -81.01, 7.8, 4326))"

So this is all docker stuff so far.

This is a Docker file I would like to use for my client. Notice it only installs Postgres.

FROM centos:6.8
MAINTAINER Me

ENV PG_FULL  "9.3"
ENV PG_VER "93"
ENV OS_MAJOR "6"
ENV OS_MINOR "8"

RUN rpm -ivh http://yum.postgresql.org/${PG_FULL}/redhat/rhel-${OS_MAJOR}-x86_64/pgdg-centos${PG_VER}-${PG_FULL}-1.noarch.rpm && \
    yum -y install postgresql${PG_VER}

USER postgres

Question: can I just run the container like this and get to psql prompt from a docker exec, or do I need to configure anything else so I can run a psql client in the client container against the server container (as I have shown above)?  The problem is that the container dies as it executes. I need to keep it alive, probably by running some kind of a process in it. That still does not answer the question of the most ephemeral setup for postgres client.

1 - I will appropriately network them later in Docker Compose.


texts: How to send parameters to BCP inside dynamic sql using sp_executesql

texts:
We are trying to run a BCP-query dynamically to put some data to files. However, when using the following code, it does not seem that sp_executesql understands that @RId inside the BCP query is a parameter. Is there any way to send that parameter value all the way in? When running this, the error message is:
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@RId".

Query:

DECLARE @REPORTID Nvarchar(500) = 'SomeID_Resend2019-02-20 11:38:24.040';
DECLARE @Path varchar(500) = '\\server\folder\_FileName.csv';

DECLARE @SQL nvarchar(500);

SET @SQL
    = N'master.dbo.xp_cmdshell ''BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = @RId" queryout @Path -t, -T -N -S SERVERNAME\INSTANCENAME''';

EXEC sp_executesql @SQL,
                   N'@RId nVARCHAR(500), @Path VARCHAR(500)',
                   @REPORTID,
                   @Path;


texts: Split comma seperated values and concatenate with other row values in MYSQL

texts:
I have a table like below

|   ID     |    Numbers       |
-----------------------------------------------------------
|   6      |  3245,78765,1234,43566  |
|   7      |  8245,88093,8326,92365  |

I want a query that can produce the below

|     Results         |
-------------------------
|   3245_sub_6       |
|   78765_sub_6       |
|   1234_sub_6       |
|   43566_sub_6      |
|   8245_sub_7       |
|   8326_sub_7      |
|   92365_sub_7     |

I am trying to split values in the above table into something like below;
like concat(splited value,'','sub','',id) something like that.

Can't seem to get how to write this query. Been beating myself up for days. Please Help


texts: What's the difference between a cursor and a trigger?

texts:
I'm triying to understand why use a Cursosr or really how they work (I'm a newbie).

I don't really find a clear purpose for using it.

How does it compare to a trigger?


texts: Unable to open database after losing data file

texts:
Last day I've deleted the data files in my database server (Oracle11g in Windows platform) by mistake. Unfortunately, now my database is not opening up. It's showing the following error.

    SQL> startup
ORACLE instance started.

Total System Global Area 2572144640 bytes
Fixed Size                  2283984 bytes
Variable Size             620758576 bytes
Database Buffers         1946157056 bytes
Redo Buffers                2945024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10:
'C:\APP\SERVICEADMIN\ORADATA\TESTPROD\TEST_IDX02.DBF'

I'm having RMAN backups for my database, but I've deleted these backup files as the FRA folder size was getting increased to the maximum. However, I've saved these backup files in my local machine. When I tried to restore the DB using RMAN after coping these files to FRA backup set location, I encountered the below error:

  RMAN-00571: ===========================================================
  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  RMAN-00571: ===========================================================
  RMAN-03002: failure of restore command at 02/23/2017 14:58:35
  RMAN-06026: some targets not found - aborting restore
  RMAN-06023: no backup or copy of datafile 5 found to restore
  RMAN-06023: no backup or copy of datafile 4 found to restore
  RMAN-06023: no backup or copy of datafile 3 found to restore
  RMAN-06023: no backup or copy of datafile 2 found to restore
  RMAN-06023: no backup or copy of datafile 1 found to restore

As suggested, I tried adding catalog for the backup, but still unable to recover the file.

RMAN> CATALOG BACKUPPIECE 'C:\FRA\TRENDZPROD\BACKUPSET\2017_02_23\O1_MF_NNNDF_TA
G20170223T112327_DBWY6SJK_.BKP';

cataloged backup piece
backup piece handle=C:\FRA\TRENDZPROD\BACKUPSET\2017_02_23\O1_MF_NNNDF_TAG201702
23T112327_DBWY6SJK_.BKP RECID=27 STAMP=936726443

RMAN> list copy;

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository

RMAN> recover database;

Starting recover at 23-FEB-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/23/2017 17:48:37
RMAN-06094: datafile 10 must be restored

I am not getting any idea how to restore my database using these files.


texts: MongoDB collection.count() return 0 after restore

texts:
I have restored a 1.74G data into my MongoDB 3.4.4. The mongorestore command returns successfully. But when I run the count on the collection I always got 0. Below is the output:

mongos> db.orders.count()
0
mongos> db.orders.totalSize()
1878683648

From above two commands, I can see the size of the collection is 1.74G but the number of the document is 0.

mongos> db.orders.find()
{ "_id" : 20, "CustId" : 55304, "invoiceDate" : ISODate("2016-11-24T06:08:29.015Z"), "lineItems" : [ { "prodId" : 4668, "prodCount" : 4, "Cost" : 1564 }, { "prodId" : 3921, "prodCount" : 6, "Cost" : 4548 }, { "prodId" : 4766, "prodCount" : 8, "Cost" : 4584 }, { "prodId" : 4413, "prodCount" : 3, "Cost" : 2193 }, { "prodId" : 3332, "prodCount" : 10, "Cost" : 8920 } ], "orderStatus:" : "C", "statusDate" : ISODate("2016-11-25T01:28:45.309Z"), "orderStatus" : "C" }
{ "_id" : 62, "CustId" : 58324, "invoiceDate" : ISODate("2016-11-24T06:08:29.308Z"), "lineItems" : [ { "prodId" : 4377, "prodCount" : 7, "Cost" : 6419 }, { "prodId" : 6982, "prodCount" : 5, "Cost" : 850 }, { "prodId" : 3731, "prodCount" : 7, "Cost" : 196 }, { "prodId" : 3896, "prodCount" : 3, "Cost" : 66 } ], "orderStatus:" : "C", "statusDate" : ISODate("2016-11-25T01:28:45.380Z"), "orderStatus" : "C" }
...

The above command is the find output and it gives me the correct document format. And I can continue to iterate the document by typing it command.

Below is the two count commands run against find:

mongos> db.orders.find({CustId: {$gt: 0}}).count()
2017-11-01T21:16:50.484+0000 E QUERY    [thread1] Error: count failed: { "code" : 15988, "ok" : 0, "errmsg" : "error querying server" } :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBQuery.prototype.count@src/mongo/shell/query.js:383:11
@(shell):1:1
mongos> db.orders.find().count()
0

I don't know why the first command returns an error while the second one returns 0. Does anyone know this issue? I don't know how to debug the problem.

Below is itcount output:

mongos> db.orders.find().itcount()
2017-11-02T18:44:45.789+1100 E QUERY    [thread1] Error: getMore command failed: {
    "ok" : 0,
    "errmsg" : "End of file",
    "code" : 6,
    "codeName" : "HostUnreachable"
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBCommandCursor.prototype._runGetMoreCommand@src/mongo/shell/query.js:763:1
DBCommandCursor.prototype._hasNextUsingCommands@src/mongo/shell/query.js:791:9
DBCommandCursor.prototype.hasNext@src/mongo/shell/query.js:799:1
DBQuery.prototype.hasNext@src/mongo/shell/query.js:294:13
DBQuery.prototype.itcount@src/mongo/shell/query.js:415:12
@(shell):1:1


texts: Max date record for items for given set of dates


texts: PostgreSQL 9.6 slow UPDATE on production server compared to local machine

texts:
I have a production vm with centos 7.6 under hyper-v and a windows 10 laptop as a dev machine and both are running postgresql 9.6, on my laptop 10k UPDATE queries only take 9 seconds while on the server they take 320 seconds

the laptop has an ssd while the server has a 7200 rpm disk, still I think the difference is way too high to be just about the ssd

this is the table

CREATE TABLE "public"."test_table" (
  "id" int4 NOT NULL DEFAULT nextval('test_table_id_seq'::regclass),
  "field1" varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
  "field2" int4 NOT NULL,
  "fk" int4,
);

CREATE INDEX "test_table_field1_3e4d8ff6" ON "public"."test_table" USING btree (
  "field1" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "test_table_field1_3e4d8ff6_like" ON "public"."test_table" USING btree (
  "field1" COLLATE "pg_catalog"."default" "pg_catalog"."varchar_pattern_ops" ASC NULLS LAST
);
CREATE INDEX "test_table_field2_aff278f4" ON "public"."test_table" USING btree (
  "field2" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "test_table_fk_8a3cfc7c" ON "public"."test_table" USING btree (
  "fk" "pg_catalog"."int4_ops" ASC NULLS LAST
);

ALTER TABLE "public"."test_table" ADD CONSTRAINT "test_table_pkey" PRIMARY KEY ("id");

ALTER TABLE "public"."test_table" ADD CONSTRAINT "test_table_fk_8a3cfc7c_fk_table1" FOREIGN KEY ("fk") REFERENCES "public"."table1" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED;

and the query that is repeated 10k times but with different data each time

UPDATE "test_table"
SET "field1" = 'ABCD',
"field2" = '53',
"fk" = 5
WHERE "test_table"."id" = 1

and explain (analyze, buffers)

Update on test_table  (cost=0.29..8.30 rows=1 width=236) (actual time=0.393..0.393 rows=0 loops=1)
  Buffers: shared hit=31 dirtied=11
  ->  Index Scan using test_table_pkey on test_table (cost=0.29..8.30 rows=1 width=236) (actual time=0.022..0.023 rows=1 loops=1)
        Index Cond: (id = 1)
        Buffers: shared hit=3
Planning time: 0.097 ms
Execution time: 0.419 ms


texts: Why does MySQL flag a duplicate key where none exists?

texts:
I'm working on adding an auto-increment field to a large table.

The field is already populated (roughly 1-500M) and new inserts are manually incrementing correctly. To verify, I ran a query to show any duplicates:

mysql> SELECT new_id, COUNT(*) AS count FROM my_table GROUP BY new_id HAVING count > 1;
#      No Results

And when I manually look at the latest records I see what looks like an auto-incrementing BIGINT(20) field. And no NULL's exist in that column.

Yet when I try to change the index to a UNIQUE INDEX (from non-unique index, but with unique values), it triggers a DUPLICATE KEY error. And the duplicate key is always MAX(new_id)+1, so a SELECT for the offending duplicate always returns no results.

Here's what I'm running:

mysql> SELECT MAX(new_id) FROM my_table;
+-------------+
| MAX(new_id) |
|   512345678 |
+-------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE my_table
        DROP INDEX `idx_new_id`,
        ADD UNIQUE INDEX `idx_new_id ` USING BTREE (`new_id `);

ERROR 1062 (23000): Duplicate entry '512345679' for key 'idx_new_id'

But why? This record doesn't even exist! The MAX is 1 less than this and hasn't changed since before the query. So I prove it...

mysql> SELECT MAX(new_id) FROM my_table;

+-------------+
| MAX(new_id) |
|   512345678 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM my_table WHERE new_id = 512345679;
# No results

Why did MySQL flag 512345679 as a duplicate if record 512345679 doesn't even exist?

I am so far unsuccessful in duplicating the problem from scratch. (db-fiddle)

Can you point me where to look next?

Ultimately I'm headed toward changing the primary key from another field to this one. But am trying to prevent downtime by pre-populating the unique ids (already done). If I am successful with this step I'll switch this new_id field to the PRIMARY KEY and AUTOINCREMENT it.

Here's the SHOW CREATE for my_table:

CREATE TABLE `my_table` (
  `new_id` bigint(20) NOT NULL,
  `id` bigint(20) NOT NULL,
  `username` varchar(80) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_username` (`username `),
  KEY `idx_new_id` (`new_id`) USING BTREE,
  KEY `idx_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


texts: Relaying changes to a slave from another master with Tungsten on MySQL

texts:
I am trying to setup a multi-master type of replication with each master having an additional slave using Tungsten. As an example, lets say I have 4 hosts (A,B,C,D) and hosts A and C are the masters, with B being a slave to A and D being a slave to C.

With my current configuration, I will add data to A. This gets replicated to its slave B and to the other master C with no issue. The same happens with I add data to C, its slave D gets updated and the other master A gets updated. The problem is when A gets updated, D will not see the changes. I do not want A to update D, but rather have D get all of the updates from C, which would also have B get all of its updates from A.

Here is the configuration script I have been working on.

MASTER1='master1-ca' #<-- 'A' from example
MASTER2='master2-va' #<-- 'C' from example
SLAVE1='slave1-ca'   #<-- 'B' from example
SLAVE2='slave2-va'   #<-- 'D' from example

./tools/tpm configure defaults  \
    --reset \
    --replication-user=tungsten \
    --replication-password=password \
    --home-directory=/home/tungsten/PCS_DB \
    --datasource-mysql-conf=/etc/mysql/my.cnf \
    $( cat ./cookbook/security.options ) \
    --start

./tools/tpm configure PCS_Multi \
    --masters=$MASTER1,$MASTER2 \
    --master-services=west,east \
    $( cat ./cookbook/security.options ) \
    --topology=all-masters

./tools/tpm configure west \
    --hosts=$MASTER1,$SLAVE1 \
    --slaves=$SLAVE1 \
    $( cat ./cookbook/security.options ) \
    --master=$MASTER1

./tools/tpm configure east \
    --hosts=$MASTER2,$SLAVE2 \
    --slaves=$SLAVE2 \
    $( cat ./cookbook/security.options ) \
    --master=$MASTER2

./tools/tpm install

I have been looking through documentation and tutorials for a few days now and can't seem to figure out this last piece. I have tried to setup a relay from $MASTER1 to $SLAVE1 but since there is not a lot of documentation on setting up a relay (at least from what I could find) I do not know if I am doing it correctly (or if I should even be doing that in the first place).


texts: Deadlock on inserts to a single table with a single key. (Mysql)

texts:
I have a simple game server.
One of the requirement i received was to handle a single request for a single player at a time. so in order to achieve that i decided to create a lock table:

CREATE TABLE `GameRoundLock` (`theKey` varchar(255) NOT NULL,
PRIMARY KEY (`theKey`))
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Before I handle any request from any player I am inserting a row into that table like so:

// save and flush == acquiring the lock
GameRoundLock lock = this.gameRoundLockRepository.saveAndFlush(somePlayerKey);

   /** Play busniess logic here...  **/

// deleting == releasing the lock
this.gameRoundLockRepository.delete(lock);

This actually works.

In the following scenario the whole system works as expected:

1) PlayerA plays, therefore a new lock is inserted.

2) handling playerA request... (takes 30 seconds)

3) While 2) is still executing, PlayerA plays again - This ofcourse resulting in trying to create a lock for him, and the thread now awaits till 2) completes.

4) thread of 2) completes

5) thread of 3) now continuing

6) DONE, playerA total played rounds == 2.

****Pay attention that the id (playerKey) is identical.****

The problem arises when the playerA tries another play request while the handling of the first thread still occurs. here is the scenario that resulting in a weird deadlock:

1) PlayerA plays, therefore a new lock is inserted.

2) handling playerA request... (takes 30 seconds)

3) While 2) is still executing, PlayerA plays again - This ofcourse resulting in trying to create a lock for him, and the thread now awaits till 2) completes.

4) While 2) is still executing and 3) is still waiting, PlayerA plays again - This ofcourse resulting in trying to create a lock for him, and the thread now awaits till 2)/3) completes.

5) thread of 2) completes

6) ERROR - a deadlock is thrown!!!!!

Here is the deadlock info:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-11-23 17:05:26 0x1d98
*** (1) TRANSACTION:
TRANSACTION 5493356, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5191, OS thread handle 5152, query id 15582091 localhost    127.0.0.1 root update
insert into GameRoundLock (theKey) values ('1,1,1,64')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4865 page no 3 n bits 72 index PRIMARY of table      `game_server`.`gameroundlock` trx id 5493356 lock_mode X locks rec but not gap    waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 312c312c312c3634; asc 1,1,1,64;;
 1: len 6; hex 00000053d266; asc    S f;;
 2: len 7; hex 790000030903d1; asc y      ;;

*** (2) TRANSACTION:
TRANSACTION 5493355, ACTIVE 12 sec inserting, thread declared inside InnoDB    1
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5193, OS thread handle 7576, query id 15582083 localhost    127.0.0.1 root update
insert into GameRoundLock (theKey) values ('1,1,1,64')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4865 page no 3 n bits 72 index PRIMARY of table     `game_server`.`gameroundlock` trx id 5493355 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info     bits 32
 0: len 8; hex 312c312c312c3634; asc 1,1,1,64;;
 1: len 6; hex 00000053d266; asc    S f;;
 2: len 7; hex 790000030903d1; asc y      ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4865 page no 3 n bits 72 index PRIMARY of table    `game_server`.`gameroundlock` trx id 5493355 lock_mode X locks rec but not gap    waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info    bits 32
 0: len 8; hex 312c312c312c3634; asc 1,1,1,64;;
 1: len 6; hex 00000053d266; asc    S f;;
 2: len 7; hex 790000030903d1; asc y      ;;

*** WE ROLL BACK TRANSACTION (2)

It seems like two "close\near" inserts for the locks of the 2nd and 3rd play requests causes this. why a deadlock and how to mitigate it?


texts: Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler?

texts:
Using SQL Server Profiler (I'm on SQL Server 2012), I'm trying to generate a useful trace that shows the parameter values, not just the SQL with variable names. The stored procedure walks through a gross amount of Inventory data to generate some extremely valuable results, and I'm trying to document the existing behaviour, so I can unit test it, define it exactly, and then refactor it into something sane.

I have a stored procedure that does a 54-parameter sub-procedure execute, inside a loop where the stored procedure creates a cursor then does a while loop. Here's a simplified view:

CREATE PROCEDURE
   [dbo].[OuterProcedure]
   (  @ProductCode varchar(8),
     -- 41 more parameters omitted
   )
AS
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  SET NOCOUNT ON
 DECLARE @AboutFourHundredLocalvariables -- omit about 400 local variable declarations.
 -- OMIT ABOUT 10 temporary table declarations.
 DECLARE  aCursor CURSOR FAST_FORWARD FOR
   SELECT [ID],bkno,  -- about 40 fields omitted.
              FROM vwVeryComplexViewThatDoesALotOfVeryBrutalJoins
              WHERE  (about_80_boolean_expressions AND omitted_here)
        ORDER BY some,keys,like,this

OPEN aCursor
FETCH NEXT FROM aCursor /* Get First Record */
    INTO @ID, @about_40_fields,....
WHILE (@@FETCH_STATUS = 0) AND
          ( @About80MoreBooleanExpressionsHere)
BEGIN   /* 1 */
     -- about 700 lines of logic, math and if-parameter-this-then-that
     -- stuff omitted
            EXEC  @ConsiderItem =
                      InnerProcedureCallWithinLoop
                                            @from_locn,
                        @About53PARAMSOMITTED,
                                                ...

    FETCH NEXT FROM CurInventory /* Get Next Record */
       INTO @ID,@MoreStuff,...
END
CLOSE CurInventory
DEALLOCATE CurInventory

How would I get a trace to show me all the parameter values passed to
InnerProcedureCallWithinLoop? There are 54 parameters. Do I have to write
essentially "54 lines of debug-printfs" inside my SQL or can I dump all the parameter values of a procedure call while doing an SQL trace some how?

When I get a trace right now, I get this output:

EXEC  @ConsiderItem = InnerProcedureCallWithinLoop  @from_locn,
                        @About53ParmsOmitted

What I'd like to know is that @from_locn = 1 and @About53ParmsOmitted = 'hello world' and so on.

This doesn't tell me the actual value of the parameter @from_locn. In the case of that first parameter, it's passed in to my top level stored procedure, so I know it's 0, or 1, as the case may be.  However about 40 of the 43 params in that inner procedure come from the FETCH NEXT FROM aCursor operation inside a WHILE loop.

Right now tracing tells me How many times InnerProcedureCallWithinLoop gets invoked,  and how long each one took, but not what the values of the parameters to that call were. If I could somehow get "runnable standalone SQL scripts" that replicate some corner case I find within my code, while tracing through these scripts, setting up these gross functions (I know, 54 parameters, that's really gross, but I didn't write them!) could take me an hour of typing just to build an SQL script that lets me invoke this corner-case myself, outside this big gargantuan snarl of SQL Server stored procedures.

This is all part of an effort to drill down to an SQL expression and build scripts that can probe  these complex  stored procedures.

Update I found an RPC "Output Param" recording option, but not an "RPC IN PARAM" recording option.


texts: PostgreSQL: Issue with a corrupted GiST index on ll_to_earth

texts:
I'm facing something that seems like an index corruption issue on a table that stores about 120k entries.
Each entry has a latlon and this latlon is indexed using the ll_to_earth GiST implementation:

CREATE INDEX table_geo ON table USING gist(ll_to_earth(lat, lon));

Now if I query the latlon of a specific entry located at -5.182, -80.622 using earth_box like this:

SELECT id FROM table
WHERE earth_box(ll_to_earth(-5.182, -80.622), 6000) @> ll_to_earth(lat, lon);

I don't have any result, although the same request using the primary key always returns the expected entry:

SELECT id FROM table
WHERE earth_box(ll_to_earth(-5.182, -80.622), 6000) @> ll_to_earth(lat, lon)
  AND id = 999;

> 1 result:
  id     lat     lon
  999    34.01   -117.94

Interestingly, if I increase the radius of the earth_box by a lot, I'm able to see the entry again! I can even find the new location of the entry in the btree by increasing/decreasing the latlon and the radius, but if I reduce the radius too much, I'll lose it because of the Recheck.

I found of that out of 123k rows, 14k (11%) are corrupted.

Query flows

Without the primary key:

EXPLAIN SELECT id FROM table
WHERE earth_box(ll_to_earth(-5.182, -80.622), 6000) @> ll_to_earth(lat, lon);

Bitmap Heap Scan on table  (cost=9.23..495.43 rows=123 width=16)
    Recheck Cond: ('(1029056.37336865277, -6273202.2275760062, -582074.280166476732),(1041056.37292618607, -6261202.22801847383, -570074.280608943431)'::cube @> (ll_to_earth(lat, lon))::cube)
      ->  Bitmap Index Scan on table_geo  (cost=0.00..9.20 rows=123 width=0)
            Index Cond: ('(1029056.37336865277, -6273202.2275760062, -582074.280166476732),(1041056.37292618607, -6261202.22801847383, -570074.280608943431)'::cube @> (ll_to_earth(lat, lon))::cube)

And with the primary key:

EXPLAIN SELECT id FROM table
WHERE earth_box(ll_to_earth(-5.182, -80.622), 6000) @> ll_to_earth(lat, lon)
  AND id = 999;

Index Scan using table_pk on table  (cost=0.42..8.69 rows=1 width=16)
    Index Cond: (id = 999)
    Filter: ('(1029056.37336865277, -6273202.2275760062, -582074.280166476732),(1041056.37292618607, -6261202.22801847383, -570074.280608943431)'::cube @> (ll_to_earth(lat, lon))::cube)

The second query is a simple Index Scan which I think is guaranteed to find the row.

The first query however uses a Bitmap Index Scan on the index + a Bitmap Heap Scan so I can imagine 2 scenarios that would make it fail:

the entry is located in the wrong btree node (= Heap Scan fails)
btree nodes have wrong dimensions (= Index Scan fails)

Things I tried

UPDATE table SET lat = lat + 0.000001 WHERE id = 999;

This is always enough to fix 1 or several specific entries. So I thought I would fix everything by running this query on the whole table but it did not. Some rows were fixed but some others got broken.
The overall corruption rate was still around 10-12%.

Then I read about REINDEX on the postgres manual, so I tried that:

REINDEX INDEX table_geo;

But the same thing happened, some entries got fixed and some others stopped working.

My postgres version on prod:

  PostgreSQL 9.5.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

Extensions used:

cube (1.0)
earthdistance (1.0)


texts: How to store a database table in multiple disks with assigned ratio?

texts:
I am going to deploy database tables to multiple disks.

My database layout would be like following example:

There are 3 tables (Customer, Order, Product) and 4 disks (disk_A, B, C, D).

Customer: 5% on disk_A, 95% on disk_B
Order: 25% on disk_A, 50% in disk_C, 25% on disk_D
Product: 100% on disk_A

Q1: Dose any DBMS support such data layout?

I tried tablespace in following DBMS but they do not work.

1) PostgreSQL: It doesn't support  tablespaces across multiple disks. Is it?

2) MySQL and Oracle: It supports multiple disk tablespaces. But I
cannot control how much data store on each disks. (Eg. 25% in A, 0% in
B, 50% in C, 25% in D). Is it?

Q2: Which DBMS support that?


texts:
I am a student. I have assignments and projects simultaneously.
I want my project database and the other simple databases separate.
I have no option to use MySQL or others as my lecturer suggested to use only Oracle 11g and SQLPlus.
Could you please outline the process?


texts: How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?

texts:
Weird question?...maybe, but I have a need. :)

I have a stored procedure that I want to use universally in any database.
The stored procedure generates some dynamic SQL and then executes that SQL in a database that is passed in as one of the parameters in this procedure.

BUT I want to make the database parameter optional and when no database name is passed in, I want the dynamic SQL to execute within the same database that the procedure itself was called from. (Please keep in mind this procedure could be executed across databases and not within the same database that the procedure itself lives in.)


texts: SQL Server Clustering for HA combined with Log Shipping for DR

texts: Has anyone implemented SQL Server log shipping in a clustered environment? Are there any issues or things to consider when implementing this scenario?


texts: Backup Certificate SQL Server and return bytes with a Select Statement

texts:
I have a certificate in a database in Microsoft SQL Server 2008 R2. I want to get a copy of the certificate so I can burn it onto a USB device and put it in a safe. I don't want to have to go onto the sql server machine itself, I want to do all of this from my client application which I have written.

I know how to backup a certificate using BACKUP CERTIFICATE (http://msdn.microsoft.com/en-us/library/ms178578.aspx) but this only has options to save to a FILE.

Is there another technique (perhaps using sys.certificates) which I can use?

We are planning an upgrade to 2014, so if there is functionality specific to that version I can use that, but ideally the solution should work with 2008R2.