texts:
Summary:

I'm using expdp / impdp to export a database from a test / demo database (Oracle 11.2) and import it into an instance of Oracle Express (11g) on my own machine.

The import fails because it tries to create imported database files in locations from the source server.  Literally, my source temo / test database is on a machine with an E:\ drive.  But my computer only has one C:\ drive.  And I get errors because it can't find things on the E:\ drive.

This error feels like a red herring.  Either I've missused my tools, or I'm getting a slightly bogus error message.

Questions:

Did I run expdp wrong, and include full file paths somehow
Is my real problem that the SIDs don't line up ('demo' vs 'XE')
If I need to remap something, what specifically woudl that be?

Many thanks.

Full explanition

I run this command on the demo server:

expdp name/password@localhost:1521/demo full=y directory=DATA_PUMP_DIR dumpfile=demo.dmp logfile=exportDemo.log

I've coppied the file to my notebook, to the appropriate folder within Oracle (C:\oraclexe\app\oracle\admin\XE\dpdump).  I then run this command:

impdp SYSTEM/password@localhost:1521/XE  directory=DATA_PUMP_DIR dumpfile=demo.dmp logfile=impdpDemo.log full=y

The command runs for a while, but with errors:

;;;
Import: Release 11.2.0.2.0 - Production on Sat Jun 11 10:32:21 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

    ;;;
    Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
    Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    Starting "SYSTEM"."SYS_IMPORT_FULL_01":  SYSTEM/********@localhost:1521/XE directory=DATA_PUMP_DIR dumpfile=demo.dmp logfile=impdpDemo.log full=y
    Processing object type DATABASE_EXPORT/TABLESPACE
    ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
    ORA-31684: Object type TABLESPACE:"TEMP" already exists
    ORA-31684: Object type TABLESPACE:"USERS" already exists
    ORA-39083: Object type TABLESPACE failed to create with error:
    ORA-01119: error in creating database file 'E:\ORACLE\PRODUCT\11.2.0\ORADATA\DEMO\MOBILE01.DBF'
    ORA-27040: file create error, unable to create file
    OSD-04002: unable to open file
    O/S-Error: (OS 3) The system cannot find the path specified.

This is the start of the log, but variations of this error happen throughout the import process, and it ultimately fails.


texts:
I am pretty new to working in SQL at and have a gaps and islands problem in which I am trying to get a series of consecutive months of membership. Any member may have multiple memberships and their membership may have gaps or overlap. My thought was to order memberships by member and plan and work backward within this group month by month until I find a gap, then assign the begin and end date of the membership.

Here's a sampling of my data:

Member  YYYYMM      Plan
1       201601      123
1       201602      123
2       201505      123
2       201506      123
2       201510      123
2       201511      123
2       201512      123
1       201510      456
1       201511      456
1       201512      456
1       201601      456

The above data would represent memberships for two separate members to same/different plans for different time spans. Now, I need to get it to look more like this:

Member  YYYYMM_Begin    YYYYMM_End    Plan
1       201601          201602        123
2       201505          201506        123
2       201510          201512        123
1       201510          201601        456

So far, I've got a recursive join that partitions based on Member and Plan and ranks them in descending order by YYYYMM, which seems to be working. But I'm stuck on how to figure out the begin and end dates for consecutive months of membership.

SELECT DISTINCT A.Member, A.Plan, A.YYYYMM, B.Rank
FROM Member_Hist A
 JOIN (SELECT
    MH.Member,
    MH.Plan,
    MH.YYYYMM,
RANK() OVER (PARTITION BY Member, Plan ORDER BY YYYYMM DESC) AS Rank
FROM Member_Hist MH) B ON A.Member = B.Member AND A.Plan = B.Plan
ORDER BY Member ASC, Plan ASC, YYYYMM DESC

Edit: I'm using Oracle, not MySQL; Also, I only have read permission.
Edit: Sorry, NOT using MySQL or SQL Server


texts: Unable to query for thread information in mysql using root

texts:
I'm connected to a server's mySql database hosted on google cloud using the user 'root' with a password.
I was under the impression that the root user had all privileges but when I try to get the thread information using SHOW ENGINE INNODB STATUS, I get an error:

[Error Code: 1227, SQL State: 42000] Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

Does it have something to do with me trying to access the database remotely?
What's the difference between root@localhost and root@%?


texts: How to avoid a select query for holding a Sch-S lock

texts:
I am looking for an option to avoid Sch-S locks when running a Select query.

I have a database which is controlled by an application written by others as well as my own application. One of the tables has millions of rows. I don't have any issue with dirty reads etc. but I don't my select query to lock the other application indexing or modification queries that have to wait due to Sch-S locks from my query.

I tried to set isolation level snapshot before calling my query but that didn't make any difference either WITH NOLOCK option that still acquires an Sch-S lock.

I am looking for an option to run a select on a table without acquiring Sch-S or any kind of lock, maybe if we can quickly have snapshot/view or temp copy (will it work as it has millions of rows of data?) of the table without acquiring any kind of lock at all.


texts: Mirroring Database tables and views on SQL Server Express

texts:
I need some idea on how to mirror all table and views in my development server to my demo server.

Both servers are running SQL Server Express.

I have tried it manually but it's very time consuming.
I can try to replace the demo server virtual machine but that means deleting all data.


texts: Is there any danger using binary data over an UTF-8 MySQL connection?

texts:
My application uses UTF-8 as the default encoding for all fields, and the connection itself; every connection starts with:

SET NAMES utf8;

I need to store a few SHA-1 hashes though, and for efficiency I'd like to store them in their compact, binary form (20 bytes) instead of their hexadecimal form (40 chars).

These fields are declared as BINARY(20).

I've tested it and everything seems to be working fine, but just to be on the safe side, I thought I'd ask if there is anything I need to know, any pitfalls to avoid? Or is it perfectly safe to transmit binary data over an UTF-8 connection?


texts: Differential backups are 2/3 the size of the full backup

texts:
I was investigating why my differential backups were taking almost as long as my full backups and I noticed that the size of the first differential backup after the daily full backup was 2/3 the size of the full backup.  However the subsequent differential backups for the remainder of the day only grew in size by about 1%.

Nothing has significantly changed in the usage pattern on the database and historically the size of the first differential backup after the daily full backup was about .5% of the size of the full backup.  I've looked at the day over day size of the full backups and they only grow by about 2%.

The daily backup schedule is defined as one full backup at 1AM followed by 5 differential backups between the hours of 7AM and 11:59PM.  Typically the full backup takes about an hour and a half to complete and that includes the verify step.  The differentials were taking about 20 mins to complete including the verify step, however now they are taking about an hour and ten mins to complete.

I've confirmed the database usage pattern hasn't changed, there are no new jobs that are writing/changing a significant amount of data between the window of the full backup and the first differential backup and I don't seen any file subsystem errors.

With all of what I've confirmed above my question is what would cause the first differential backup of the day to have become so large when the subsequent differentials on the same day only grow by 1%?

Server Details:

SQL Server 2008r2 Enterprise
LiteSpeed for SQL Server
Database is in Simple Recovery mode
Backup target is a file share on the network


texts: postgresql-setup --initdb with custom data directory

texts:
I am trying to setup Postgres 12 on Centos 8.
Postgres shall be installed in the default directory i.e. /var/lib/pgsql, however I want the data directory to be in /data/pgsql
I want to use postgresql-setup as root, as I believe it will create systemd service files along with it, rather than using pg_ctl or running initdb as postgres user.
However, if I try
$ postgresql-setup --initdb --pgdata=/data/pgsql/

I will receive the following error.
postgresql-setup: unrecognized option '--pgdata=/data/pgsql'
FATAL: can't parse arguments

What is the best way to achieve this?


texts: 'Column reference is ambiguous' when upserting element into table

texts:
I am using PostgreSQL as my database. And I need to create an entry in the database, and if it's already exists, just update its fields, but one of the fields should be updated only if it's not set.

I've used info from this question: https://stackoverflow.com/questions/13305878/dont-update-column-if-update-value-is-null, it's quite related to what I have.

I tried to use this query, but when I run it, it errors with Column reference 'affiliate_code' is ambiguous:

INSERT INTO accounts (id, token, affiliate_code)
VALUES (value1, value2, value3)
ON CONFLICT (id) DO
UPDATE SET token = value2,
  affiliate_code = COALESCE(affiliate_code, value3);

(the real values are substituted, of course).

If I replace affiliate_code = COALESCE(affiliate_code, value3) with affiliate_code = value3, everything works, but not in the way I want it to work.

How can I make this work?

Here is how my table is defined:

CREATE TABLE accounts (
  id VARCHAR NOT NULL UNIQUE,
  token VARCHAR NOT NULL,
  affiliate_code VARCHAR
);


texts: Need to migrate a SQL Server 6.5 Prod to SQL Server 2012 in VM

texts:
We are planning to move our SQL Server shop to VM(VMware). We still have one SQL Server 6.5 in production. What would be my course of action/path to move/migrate 6.5 to virtualized SS 2012?

It's the data that needs to be moved. The rest can be scripted/re-written if a straight migration of a DB(view, SPs.....) is not practical.


texts: JOIN in 4 tables gets phpmyadmin unresponsive

texts:
I can´t manage to create a valid query that shows me 5 columns from these 4 tables:

As you can see the relationships that i'm trying to JOIN in the query are highlighted in differents colors.

The query i came out is:

SELECT c.CompanyName
, s.categoryname
, cprs.strenght
, s.parentid
, ss.Risklevel
, ss.ColumnType

FROM company_details AS c
JOIN category_master AS s ON s.id = c.categoryid
JOIN company_strenght AS cprs ON cprs.companyid = c.id
JOIN subsector_detasils AS ss ON s.parentid = ss.subcategoryid

But server gets unresponsive until i have to kill the process.
The issue is in the last line, if i remove it and only manage 3 tables i get a successful result but is not enough for what i want.

Problem description:
The company_details table is a list of companies. The id field is an unique ID that each company has.
We need to match that id with the companyid field in the company_strenght table so we can get the c.CompanyName, s.categoryname, cprs.strenght columns. All good so far.

Then we need to include in the results the category_master table which contains the category that each company belongs to. So we match s.id = c.categoryidfor that. Still no problem and i get a list of companies with the catergories they belong to and also their parent category id.

Finally here is the problem: Each company has a parent category which id is parentid in the category_master table (marked in green). This parentid field is the same than the subcategoryid field in the subsector_detasils table.
We need to match s.parentid = ss.subcategoryid so we can get ss.Risklevel, ss.ColumnType columns in the results. But as i said, it's not working since phpmyadmin just stops working and i have to kill the process manually.

Is there any other way to get around this problem? I'm running out of ideas...

Thanks in advance.


texts: mySQL efficiency with inner join queries

texts:
I have two tables, both with about 1,000,000 rows.

Here is the question I am trying to get mySQL to solve:

return all record_ids from table_a where that id matches the cat_id in table_b, but only for customer_id 654 (customer_id exists in both tables).

First off I tried this with the following statement:

SELECT record_id from `table_a` where customer_id="654" and record_id in
(SELECT cat_id from `table_b` where cat_id="654");

This is the most logical looking one to me, as it's more at my coding level.  But this query took 2 minutes to run.  So I tried to re-write it using:

SELECT record_id from `table_a` where
customer_id = "654" and exists (SELECT cat_id FROM `table_b` where
table_b.customer_id = "654" and
table_a.record_id= table_b.cat_id
group by record_id having count(*) > 1);

This took 22 seconds to run.

I tried another way as well:

SELECT table_a.record_id from table_a
INNER JOIN table_b ON (table_a.record_id = table_b.cat_id)
WHERE table_a.customer_id = "654" and table_b.customer_id= "654"
GROUP BY table_a.record_id
HAVING COUNT(*) > 1;

This one takes 19.5 seconds to run.

I'm wondering if my third attempt is the most efficient way to ask this question, or as I'm hoping, there is a more efficient way to run these.

Here is mySQL's execution path for the last two queries:

On table_a, I have the following indices:

KEY `rc` (`record_id`,`customer_id`)
KEY `test` (`record_id`,`customer_id`),
KEY `test2` (`record_id`,`customer_id`),
KEY `cust` (`customer_id`)

On table_b, I have the following indices:

KEY `rc` (`cat_id, `customer_id`)

There are duplicates that I made on accident and I haven't gotten around to deleting them, (not sure if that impacts performance):


texts: What happens if I load last 10 rows then next 10 after other rows are inserted?

texts:
What if I had a PHP script that had a pagination that loads last 10 rows then page two loaded next 10 in DESC order, but before loading next 10 on page 2, other 10 rows were added to the table?
What would happen? And if the same 10 rows were to be loaded again, and I had a TIMESTAMP column in my table, how would I take that to my vantage to avoid that scenario?
Thanks.


texts: I have deleted a table, by exporting over the same table, and selecting the 'drop destination table before insertion'

texts:
I understand this might not be the best forum, but I could sure use some help right about now. If not, please suggest a forum where I can.

I was trying to backup a table, by using the export wizard. I accidentally seleected the same table as source and destination. Add insult to injury, I chose the drop destination table before inserting option. Needless to say, I now have an empty table.

I am sure there are (must?) be some backups, but the support team is not available to help right now. Is there anything I can do to recover the said table?

Thank you.


texts: Is it better to have a wider single table or "child" tables based on business processes?

texts:
The example I am working with is an invoice data model, where there may be around 30 columns by the time we're done designing. Each row of data would be unique to a single invoice.

Some of the columns are used by multiple parts of the application (invoice number, obviously, charge totals, etc.) but there are some columns that are only used by a single process.

For example, we have three columns that reflect control numbers (varchar(18)) used by external clients that we should track, but only the tracking process ever looks at these columns. The control numbers are usually one to one to the invoice, so there would only ever be one control number per invoice. There are also some invoices that will just not have a control number because they're older invoices being imported in from a system that didn't keep track of control numbers (they'll represent about 25% of the initial data). Even those older invoices may eventually get a control number (although most likely they're old enough that won't happen).

Does it make more sense to take these columns and make a separate table for them, from a data modeling perspective or a performance perspective, or, should we just leave them in the Invoices table? Answers for this specific example is appreciated, but I'm also curious about a more general answer, since we'll certainly come across this sort of scenario again.

Based on how I read this Wikipedia article, I do intend to mean vertical partitioning, at least in a logical sense (the two different tables would be on the same physical storage in this case).

This is part of an OLTP database.

Probably 75% of the rows will have a control number eventually.


texts: Join on NULL Key Column Optimizing as Table and Index Scans

texts:
I have a question about this query plan.

We have a table in a test environment, Order_Details_Taxes, that has 11,225,799 rows.  This table has a column, OrdTax_PLTax_LoadDtl_Key, which is NULL on every single row.  This test environment is configured in such a way that this column will always be NULL.  There is an index on this column.

I ran some queries against this table using a NULL value for a column.  A NULL INNER JOIN will never yield any results.

declare @Keys table (KeyValue decimal(15,0))
insert into @Keys (KeyValue) values (null)

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue

select *
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue

These are the first queries in the query plan. The first select starts from the hundred-million-row table and joins to @Keys.  The second select starts from @Keys, but it does a clustered index scan on this table.

I know temporary @Tables are questionable in most cases, so I changed my query to use a temporary #Table:

if object_id ('tempdb..#Keys') is not null
    drop table #Keys
create table #Keys (KeyValue decimal(15,0))
insert into #Keys (KeyValue) values (null)

select OrdTax_PLTax_LoadDtl_Key
from #Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue

select *
from #Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = null

These queries were optimized and ran exactly as I expected-- get the #Keys NULL value first and seek to Order_Details_Taxes.  They are the last queries in the query plan linked.

Why do the queries in which I used a @Table variable perform index and table scans on this large table, when I am joining using from a table that has a single NULL value to a table with only NULLs in this key value?

I assume the answer is statistics and/or cardinality limitations of @Table variables, but the resulting query plan was non-intuitive to me.

ANSI_NULLs is on for this table and my SQL session.


texts: MongoDB - Can I create TTL index with rolling method

texts:
Im getting stuck with archiving a huge amount of data in MongoDB 3.6
I want to delete 506 million records in a collection. I tried to remove using bulk.remove(), but that is also slow. 50 records are removing per second.
But somewhere I read, TTL index and do scan interval every 1 hour. So it'll remove in faster way.
But if I create this index in foreground, it'll lock the collection. So i'm thinking to do with the rolling index creation method.
If do like that, lets say on a 3 node replica set, detach the node3 and then create index. Once its created it'll start automatically remove the data. Then once I add the node back to the Replica set, maybe the primary will do the delete after I create the index, that time it'll try to replicate, in the worst case, the data is already removed on that node, then it'll break the replication?


texts:
We have a lot of proprietary database code that is simply encrypted using the "WITH ENCRYPTION" option.  Unfortunately, this is a very weak method of securing database code.

Is there any better way to secure the database code?

We're currently on SQL Server 2005, if that helps, but we're in the process of migrating to SQL Server 2008 R2.


texts: Replicate only a few tables in PostgreSQL 10

texts:
I have a need to keep updated just a few tables between databases with different purposes and schemas, except for these few tables.

My current solution is a cron job that selects from one database and inserts into the other every minute or so, but I was wondering if there are more robust solutions around for PostgreSQL version 10.


texts:
I have a 2 tables

CREATE TABLE testmain
(dummy_Id numeric(10,0) null,
 memberNo varchar(100)  null)

CREATE TABLE testchild
(dummy_Id numeric(10,0) null,
 xummy_Id numeric(10,0) null)

where xummyId is the dependent meber dummyId

The testmain table has data as of these:

SELECT * FROM testmain

1001 82411/1
1002 82411/2
1003 85545/1
1004 82411/3
1005 85545/2

The left part of memberNo is the code and the last part is the dependent member.

So in the above example 82411/1 is the master and 82411/2,82411/3 are its child members

I need to parse the testmain table and for each entry of type
code/1 and insert in the testchild the dummyIds of the child members

SELECT * FROM testchild

1001 1002
1001 1004
1003 1005

Thanx in advance


texts: Should I use separate address dimensions?

texts:
I have a Star Schema model with two Dimensions (User and Store) each of them have addresses, which I store.

Each User and Store can just have one Address. Is it recommended to split the Dimension into a Address Dimension (with a 1:1 relationship)? Are there any technical arguments to handle like this? Or is it a common way to keep the address in the user/store dimension?


texts: How can I connect central and decentral databases?

texts:
I am working in computational science and we are frequently producing large amounts of highly-structured data that depends on various input parameters. Right now, storing the data is managed via files, but analyzing and querying the data by using relational databases and SQL seems way superior to me.
I am suggesting to use a central database server (i.e. PostgreSQL), which is especially helpful when it comes to reproducably reporting and "backing up" the data or making it accessible to others. However, sometimes the data we produce is "worthless" and known that it can be immediatly discarded after quick inspection, thus, it makes only sense to store the data (if at all) on a central server if it has any worth. Each of this data-production runs can generate data usually from 100s of KB to 10s of GB, so depending on the amount, transfer through the network to the server can be expensive. While it is no problem when querying data (as you can decide what you need on server side), when filling the database, large amounts of data have to be moved.
What I would like to do is to locally generate the data (i.e. written to a local hard drive) and then explicitly (or implictly during night times) commit the data to the real SQL server if it is good. It would be nice if the codes that generate this data don't have to be adapted for each case where the data is determined for the local database or to be directly sent to the remote server, somewhat similar to git where I can push a local repository to the remote one (and vice-versa, if required).
I was thinking of combining SQLite and PostgreSQL, such that local SQLite databases are migrated to the server if required. However, if done like this, only features supported by both systems can be used (which is not bad, but a limitation). Also, migrating databases and table structures from SQLite to PostgreSQL is surely not trivial, I guess it requires some additional scripting.
The other option would be to install local PostgreSQL servers on each computer (which seems overkill to me), but they would be more compatible with the server + I could use features like pg_dump to migrate the data + its table structure.
As I am pretty unexperienced on the possibilities, is there already some kind of (Open source) software available that can approximately do this or do I have to come up with a complete home-brew solution?


texts: How to write this query in SQL Server Compact in more elegant way?

texts:
The query is :

 Select
        Case AStatus
            When 1 then 'Success'
            When 0 then 'Faliure'
        END,
        Case AStatus
            When 1 then 'AStatus'
            When 0 then 'AStatus'
        END,
        Case AStatus
            When 1 then 'Expected:1'
            When 0 then 'Recived: 0'
        END
    From Statuses
    Where LocalPath= 'c:\Status'

The original query that didn't work well in SQL Server Compact (and equal to above query) is:

Select
   Case AStatus
      When 1 then 'Success', 'AStatus', 'Expected:1'
      When 0 then 'Faliure', 'AStatus', 'Recived: 0'
   end
From Statuses
Where LocalPath= 'c:\Status


texts: How much does table size affect performance?

texts:
Let's say I have a MyISAM table (amongst other tables) with 2938347 rows. This table is merely being used for logging and doesn't get emptied very often.

By not regularly archiving or deleting these rows, does having this table around affect server performance?

Then, what about tables/databases on a MySQL server that are massive in size, but aren't accessed all that often. These tables co-exist with frequently accessed/queried tables, and I'm wondering if they're causing problems or not.

Darius


texts:
Write the SQL code that will list only once the Physician-Person appointments. In other words it will not list twice an appointment between the same physician and person
Can someone help me solve this problem. Thank you
Here is my solution, but I am getting error.
Select Appointment.Appointment_ID, Physician.Physician_ID, Person.Person_ID From Appointment, Physician, Person;
From appointment
Where exists
(Select 'Appointment_ID' from Appointment where Physician.Person_ID = Appointment.Appointment_ID);

the link below shows you the tables and questions. currently working on question 15
https://www.urgenthomework.com/question/hlst-3320-health-database-applications-sql-assignment


texts: Checking for duplicate entries for Column A - Table A based on user_id from Table B

texts:
I have two tables which have a 1 - 1 relationship.

Table A - Skill_id
Tabel A - Entry_id

Table B - Entry_id
Table B - Author_id

The two entry_id columns are a primary key - foreign key.

I need to check if a user has been allocated the same skill more than once, i.e. if there are duplicates. How can I achieve this?


texts:
I'm working on a book database. And among others, I have the following two tables (a bit simplified for the question):
tblBook: ID, Title
tblPerson: ID, Name

Note that the tblPerson table contains authors, translators and editors (not only authors, that is).
Obviously, there is a many-to-many relationship between these two tables, so I also have the following junction tables:
tblBookAuthorJunction
tblBookTranslatorJunction
tblBookEditorJunction

But when it comes to joining the tables to select all the roles, I have a problem: how do I create a query to have a table that looks like:
BookTitle AuthorName TranslatorName EditorName

Is this at all possible? What should my query look like?
Edit: You can assume SQLite for the RDBMS.

Update
So far, there are two answers that contain queries. I would like to compare these answers for others who may find this information valuable... Note that the names of the junction tables are slightly different than the ones in the question. And, yes I know, the test data is boring.
Answer 1
The query in my own answer is as follows:
SELECT B.BookTitle, P1.PersonName, P2.PersonName, P3.PersonName
FROM tblBook B
LEFT JOIN tblBookAuthor A ON B.BookID = A.BookID
LEFT JOIN tblPerson P1 ON A.AuthorID = P1.PersonID
LEFT JOIN tblBookTranslator T ON B.BookID = T.BookID
LEFT JOIN tblPerson P2 ON T.TranslatorID = P2.PersonID
LEFT JOIN tblBookEditor E ON B.BookID = E.BookID
LEFT JOIN tblPerson P3 ON E.EditorID = P3.PersonID

And the output of this query looks like this:

Answer 2
The query in Erwin Brandstetter's answer is as follows (little bit modified to fit the test database):
SELECT B.BookTitle, BA.Authors, BT.Translators, BE.Editors
FROM tblBook B
LEFT JOIN (
    SELECT  J.BookID, group_concat(P.PersonName, ', ') AS Authors
    FROM    tblBookAuthor J
    JOIN    tblPerson P ON J.AuthorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BA ON BA.BookID = B.BookID

LEFT JOIN (
    SELECT  J.BookID, group_concat(P.PersonName, ', ') AS Translators
    FROM    tblBookTranslator J
    JOIN    tblPerson P ON J.TranslatorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BT ON BT.BookID = B.BookID

LEFT JOIN (
    SELECT  J.BookID, group_concat(P.PersonName, ', ') AS Editors
    FROM    tblBookEditor J
    JOIN      tblPerson P ON J.EditorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BE ON BE.BookID = B.BookID

The output of this query looks like this:


texts: Add fields to view from select (transpose fields)

texts:
I've got a table with following structure:

CREATE TABLE test
(
  id integer,
  field character varying,
  value character varying
);

field     value
------    -----
field1    value1
field2    value2
field3    value3
...       ...

I need to transpose the field value as the fields in a view and the value of the field with be the "value" field's value. Example of what I need:

field1    field2    field3      ...
------    -----     ------      ----
value1    value2    value3      ...

I have no idea on how to start building the create view statement.


texts: Changing SQL account password in specific database

texts:
I am trying to change the password for userId in a specific database called LionsDB.
If I am changing the password manually, I would login into SQL Management Studio using that user account and update the password.

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
$SQLUser = $srv.Logins | ? {$_.Name -eq "$login"};
$SQLUser.ChangePassword($password);

With powershell, I cant find the specific logins in the LionsDB database. The above code gives me the logins at server level. Is there anyway to get to the userIDs in specific catalog and change password for them?


texts: Could not continue scan with NOLOCK due to data movement SQL Server 2008 R2

texts:
I have SQL Server 2008 R2 installed on a Windows 7 laptop. I create a new database via SQL Management Studio and when I right click and select Properties, I get the following message.

Cannot show requested dialog.

Additional information:

|-> Cannot show requested dialog (SqlMgmt)
    |-> An exception occurred while executing a Transact-SQL statement or batch
        |-> Could not continue scan with NOLOCK due to data movement. (Microsoft SQL Server, Error: 601)

Any idea what is happening and what is the possible fix?

UPDATE

I am not entirely sure if this is a fix:

I deleted all of the user databases
Did a Repair on the SQL installation
Created my databases again and it seemed to work without any issues.

However, I still do not what the root cause of the issue is. I had to fix it as soon as possible as the Sales Rep needed the laptop as quickly as possible. I really would like to hear what caused it to happen in the first place.


texts: What affects the speed of a count(*) besides the number of records

texts:
I have a query like

SELECT COUNT(*) FROM Foo Where Bar = 1 AND Baz = 2

the table has 12934600 records of which 1000001 match that predicate

Looking at the query statistics I see

  (1 row(s) affected) Table 'Foo'. Scan count 1, logical reads 1863,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

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

Looking at the query plan 80% of the time is spent in a index seek on the Bar and Baz columns and 20% on aggregating the result.

is there a way to speed this up and if so how? I would also like to understand which hardware components have a big influence here CPU or Disk IO or bus speed.


texts: What is correct way to ensure that row is not deleted in concurrent transaction

texts:
I have 2 tables:

Parent (PK id)
Child (PK id, parent_id)

Note that parent_id is not defined a foreign key (actually they are not parent/child, I use those names just for example).

I need to handle a case when someone inserts new Child with link to Parent's row, and someone else deletes Parent's row. I would like to avoid scenario when DB contains Child row with link to non-existent Parent.

How it works now:

[I1] Insertion Thread: BEGIN
[I2] Insertion Thread: SELECT * FROM Parent WHERE id = 3
[D1] Deletion Thread: BEGIN
[D2] Deletion Thread: DELETE * FROM Child WHERE parent_id = 3
[D3] Deletion Thread: DELETE * FROM Parent WHERE id = 3
[D4] Deletion Thread: COMMIT
[I3] Insertion Thread: INSERT INTO Child VALUES(default, 3)
[I4] Insertion Thread: COMMIT

What I've tried to do:

Use SELECT ... FOR UPDATE in insertion thread

Disadvantage: that will lock Deletion Thread on line D3, but line D2 is already executed at that moment. After Insertion Thread is done, Deletion Thread continue from D3 and will not delete just inserted Child row.

Solution: swap D2 and D3, so deletion from Parent goes first.

Disadvantage: a bit fragile. Those SQL statements are called from business logic code, in different places, so it is easy to swap their order back accidentally.
Use REPEATABLE READ isolation level

Disadvantage: it does not work as I expected. I thought it will abort Insertion Thread's transaction as selected Parent's row was deleted in another transaction, but it does not. I suppose it is because I do not perform update of Parent's row in Insertion Thread, just read.

I'm kinda OK with solution 1 or explicit row locking in the beginning of transaction, but I hope there is more correct way to achieve expected behavior. Maybe I just misused isolation level?

DB is Postgres


texts: Multiple MongoDB databases (1 per client) and sharding - are they compatible?

texts:
Currently I'm working with one MongoDB database per client, on a single replica set. We would like to keep one database per client because of security concerns.

Each client has the same 15 collections and we are growing our customer base so we need to scale this into different machines with groups of customers.

I'm reading the MongoDB docs about sharding but the approach is by collection and the shard key so I'm concerned about how it would be when you have many databases.

Is it possible to use sharding having multiple databases? and if it is possible, what would be a good approach?


texts: Capturing errors in a transaction and rollback - declare not valid at this position

texts:
Trying to craft a transaction from the code to send to MySQL.

START TRANSACTION

INSERT INTO EMPLOYEE (`firstname`,`lastname`)
 VALUES('john','Doe');

SET EMP_ID = last_insert_id();
 //do some inserts here that fail

COMMIT;

Now, when the other inserts into child tables fail I see that just the first insert being committed. TO avoid that I need to capture error and set rollback on error being triggered. When I include the declare statements for error it just fails saying

"Declare is not valid at this position. expecting EOF ;" Any pointers would be greatly appreciated

START TRANSACTION

DECLARE error BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

....other code


texts: Adding Data to Multiple Tables Using Single Query

texts:
I want to add data to multiple tables using a single INSERT INTO query, can I do this?

Please guide me and provide me syntax if this is possible.


texts: Do regular ALTERs weaken the performance?

texts:
In a project, we need to frequently ALTER the tables to delete or add columns. I wonder if this weakens the database performance in long term. If it has a considerable negative impact, what is the approach to keep the database healthy and efficient?

I was thinking of the following approaches:

OPTIMIZE the tables
CREATE a new database with the final architecture, and INSERT the entire data

The database is mysql(5.5) with InnoDB.


texts: How do I configure MySQL to accept a custom column in mysql.users?

texts:
I am running MySQL Ver 14.14 Distrib 5.5.57 on Debian Jessie (x86_64).  In my mysql.users table, I have a custom column added for database owner id--which is for my server management system's use.  Somewhere along the way, MySQL started to complain when I try to modify a user's privileges:

ERROR 1547 (HY000): Column count of mysql.user is wrong. Expected 42, found 43. The table is probably corrupted

The first 42 columns are exactly as MySQL expects. The 43rd about which it is complaining is my added column.  How can I tell MySQL that it's "OK" for there to be 43 columns?

And yes, I have run the appropriate upgrade scripts with each upgrade to MySQL, including one extra time "just to make sure":

mysql_upgrade --force -uroot -p

Thanks in advance for any assistance!


texts: Why is this integer range selection so slow?

texts:
I'm currently doing some inner joins in PostgreSQL, which I know aren't the speediest, but I've run into an efficiency issue.

The following query returns in 167ms

with structure as (
    SELECT s.id, s.terr_id, FROM structures s WHERE s.id=55
  )

SELECT r.id FROM territories t INNER JOIN structure ON (t.id = structure.terr_id)
LEFT JOIN resources r ON (
    t.rownum > r.rownum - 2
    AND t.rownum < r.rownum + 2
    AND t.colnum > r.colnum - 2
    AND t.colnum < r.colnum + 2
)

This query returns 9 rows. The columns are filled with primitives.

The table territories has 5 million rows. The table resources has 400,000 rows. The structure query is negligible.

I have indices set up on all of the rows involved. When I simply query as such:

with structure as (
    SELECT s.id, s.terr_id, FROM structures s WHERE s.id=55
  )

SELECT r.id FROM territories t INNER JOIN structure ON (t.id = structure.terr_id)
LEFT JOIN resources r ON (
    t.rownum > r.rownum - 2
    AND t.rownum < r.rownum + 2
    AND t.colnum = r.colnum
)

eliminating the colnum variation, the query returns 3 rows and only takes 11ms. If I were to run each row individually it would only take 33ms, a fraction of what it takes when combined. I've tried the BETWEEN keyword, using a box contains method, etc. Can anyone point me in the right direction?


texts:
I come from Microsoft SQL Server - but am converting a database to MySQL.  I have this syntax which works excellent on my MSSQL install

CREATE TYPE [dbo].[TableValue_ExceptionsTable] AS TABLE(
[TN] [varchar](31) NOT NULL,
`PF` [varchar](255) NULL,
`ET` [varchar](255) NULL,
`S1` `datetime` NULL,
`S2` `datetime` NULL,
`S3` `datetime` NULL,
`S4` [varchar](35) NULL,
`S5` [varchar](35) NULL,
`S6` [varchar](35) NULL

)

What is the equivelant of this in MySQL?


texts:
We have raw data and aggregated data as well as sometimes multiple levels of aggregation between.  Is there a name for the data in that intermediate "state" of aggregation?

Just like how we have Real time, near real time and historical...


texts: Migrating mysql.user table from MySQL 5.5 to MySQL 5.6

texts:
I have a tricky question.

I am migrating from MySQL 5.5 to MySQL 5.6.
I want to migrate 45 users in MySQL 5.5 to MySQL 5.6

Questions

Should I just take a dump of the MySQL 5.5 user table and restore on MySQL 5.6 ?
MySQL 5.6 user table has more columns then MySQL 5.5 so was wondering if that was doable.
Any other advise ?


texts: MariaDB - Changing explicit_defaults_to_timestamp

texts:
I am trying to check from MariaDB docs online how I can turn ON explicit_defaults_to_timestamp global variable. I encountered this as part of Apache Airflow db setup.
Is there any solution to do this? I am running mysql as system service via systemctl.


texts: User defined table type with a check constraint

texts:
I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on this column with a scalar valued function.

    CREATE TYPE [dbo].[Accounts] AS TABLE(
    [AccountName] [varchar](25) NOT NULL
         CONSTRAINT CHK_AccountName CHECK (dbo.validateAccountName(AccountName) = 1),
    [Sales] [int] NOT NULL,
    [Returns] [int] NOT NULL
);

However I get a syntax error, when I do the same on a regular table it works. Based on MSFT documentation check constraints should be ok on table types. What am I missing here ? Any better approach to validate input on a table type ?


texts: Sort table rows and save the row numbers using UPDATE

texts:
I have Postgres 9.5 with a table movimientos that has the following data:

| id | concepto | movimiento | numero | orden |
|  1 | AJUSTE 1 |       2542 |      0 |     2 |
|  2 | APERTURA |      12541 |      0 |     1 |
|  3 | AJUSTE 2 |       2642 |      0 |     2 |
|  4 | CIERRE   |      22642 |      0 |     3 |

And I need to number the records based on the orden field and keep these numbers in the numero field, because I need this data to sort and search by numero in reports. Example:

| id | concepto | movimiento | numero | orden |
|  2 | APERTURA |      12541 |      1 |     1 |
|  1 | AJUSTE 1 |       2542 |      2 |     2 |
|  3 | AJUSTE 2 |       2642 |      3 |     2 |
|  4 | CIERRE   |      22642 |      4 |     3 |

I tried to do it using a function with a FOR but is very slow with a million rows.

How to do this using a simple UPDATE?


texts: How do I backup/restore database encryption key in order to restore on new server

texts:
I am currently in the process of restoring a TDE encrypted database to another instance. I have backed up master key and certificate, and have restored them to the new server fine, but I am still incurring the following message -

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x85187A0021B33712B314113A5F75DBE2EFD118F2'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

TDE was set up on the source with a database encryption key ....

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDE_CERT
GO

and can be found under sys.dm_database_encryption_keys

and the thumbprint is of this. I have not restored this to the new server. How do I go about this?

Not understanding how I restore/create this on the new server, or am I going the wrong way about it? Relatively new to dba-ing, so please be gentle.


texts:
I have an application that often does insertion into PostgreSQL database, for some tables I have id column with serial type with auto-generated sequences. Running

SELECT nextval(pg_get_serial_sequence('client', 'id'));

on each new session gives 1. What should I do to correct this, or I need to this manually in my app?

For last case, I think get total records and alter sequence, but this is 2 more queries to do, which I better skip.


texts:
For school I have to do this:

Query what salary scale an employee belongs to displaying these columns:

s_schaal.schaal, werknemer.name, werknemer.salaris

But there are no identifiers between the tables. All I have is a bottom line and upper line column (s_schaal.ondergens & s_schaal.bovengrens).

What MySQL query can I use to compare and join this data?


texts:
I did install latest Sybase 16 development version on my Linux box. During the installation I did select to install ODBC driver.

Now looking at the directory I see this:

igor@IgorReinCloud /opt $ find . -name *odbc*
./sap/DataAccess/bin/odbcversion
./sap/DataAccess/ODBC/samples/odbcbatch
./sap/DataAccess/ODBC/samples/odbcbatch/odbcbatch.cpp
./sap/DataAccess/ODBC/samples/odbcloblocator
./sap/DataAccess/ODBC/samples/odbcloblocator/odbcloblocator.cpp
./sap/DataAccess/ODBC/sp/odbc_mda_120.sql
./sap/DataAccess/ODBC/sp/odbc_mda_157.sql
./sap/DataAccess/ODBC/sp/odbc_mda_1251.sql
./sap/DataAccess/ODBC/sp/odbc_mda_1600000.sql
./sap/DataAccess/ODBC/sp/install_odbc_sprocs
./sap/DataAccess/ODBC/sp/odbc_mda_1570020.sql
./sap/DataAccess/ODBC/sp/odbc_mda_125.sql
./sap/DataAccess/ODBC/sp/odbc_mda_12503.sql
./sap/DataAccess/ODBC/sp/odbc_mda_150.sql
./sap/DataAccess/ODBC/sp/odbc_mda_1502.sql
./sap/DBISQL-16_0/lib/jodbc4.jar
./sap/DBISQL-16_0/bin/libdbodbc16_r.so.1
./sap/DBISQL-16_0/bin/libdbodbc16_r.so
./sap/DBISQL-16_0/bin/libdbjodbc16.so.1
./sap/DBISQL-16_0/bin/libdbjodbc16.so
./sap/ASE-16_0/lib/pca/libPCAodbc.so
./sap/ASE-16_0/symlib/pca/libPCAodbc.so
./sap/COCKPIT-4/bin/sa16/linux64/java/jodbc4.jar
./sap/COCKPIT-4/bin/sa16/linux64/lib64/libdbodbc16_r.so
./sap/COCKPIT-4/bin/sa16/linux64/lib64/libdbodbc16.so
./sap/COCKPIT-4/bin/sa16/linux64/lib64/libdbjodbc16.so
./sap/log/odbc.log
./sap/log/odbc.lst
./sap/DataAccess64/bin/odbcversion
./sap/DataAccess64/ODBC/dm/include/unixodbc.h
./sap/DataAccess64/ODBC/dm/include/odbc.h
./sap/DataAccess64/ODBC/dm/include/saodbc.h
./sap/DataAccess64/ODBC/dm/lib64/libodbc.so
./sap/DataAccess64/ODBC/samples/odbcbatch
./sap/DataAccess64/ODBC/samples/odbcbatch/odbcbatch.cpp
./sap/DataAccess64/ODBC/samples/odbcloblocator
./sap/DataAccess64/ODBC/samples/odbcloblocator/odbcloblocator.cpp
./sap/DataAccess64/ODBC/sp/odbc_mda_120.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_157.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_1251.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_1600000.sql
./sap/DataAccess64/ODBC/sp/install_odbc_sprocs
./sap/DataAccess64/ODBC/sp/odbc_mda_1570020.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_125.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_12503.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_150.sql
./sap/DataAccess64/ODBC/sp/odbc_mda_1502.sql

And so for the driver I am looking for:

./sap/DBISQL-16_0/bin/libdbodbc16_r.so.1
./sap/DBISQL-16_0/bin/libdbodbc16_r.so

those 2 files, correct?

So when configuring, I will put in odbcinst.ini:

[Sybase]
Driver=/opt/sap/DBISQL-16_0/bin/libdbodbc16_r.so

right?

Thank you.

P.S.: Just curious, what is "_r" means in the file name of the library? It is not the first time I see it...


texts: Missing Edit Privileges Option In PhpMyAdmin

texts:
The Action column of the PhpMyAdmin is not displaying the Edit Privileges. I did not get the solution for this problem. I even tried to upgrade and degrade the PhpMyAdmin version, but nothing worked. I have attached a screenshot of PhpMyAdmin for verification. Please verify it and give me the solution to this problem.
Operating System: Windows 10,
Php Version : 7.2.34,
Message : You do not the privileges to administrate the users!


texts: How to Dynamically change the database using TSQL

texts:
I'm having trouble with trying to dynamically change the context of SSMS to the database specified in dynamic SQL:

EXEC sys.sp_executesql N'USE db1 ' ;

It executes successfully however the database context of SSMS doesn't change.

I've tried a slight modification to the above like so

DECLARE @sql NVARCHAR(100) DECLARE @db NVARCHAR(50)
SET @db = N'db1' SET @sql = N'Use ' + @db
EXEC sp_executesql @sql

Again, it executes successfully, but the database doesn't change.


texts: Oracle procedure takes forever to compile if I use public synonym

texts:
I have a procedure that queries a table through a DB link. If my procedure uses a public synonym through the DB link, it takes forever to compile.

I'm creating the procedure in Oracle 12c. The DB link is connected to Oracle 18c. The source database was recently upgraded from 12c to 18c. The procedure was fine after the upgrade until I tried to recompile it.

Here are some details about what I've observed.

The following insert statement works and inserts W.

CREATE TABLE TEST_DB_LINK_PROC (VAL varchar(1));
INSERT INTO TEST_DB_LINK_PROC select 'W' from MYTABLE@MYDBLINK WHERE ROWNUM < 2;
commit;

The insert in this procedure inserts X. Here I've added the schema.

CREATE OR REPLACE PROCEDURE TEST_PROCEDURE_DB_LINK_A is
BEGIN
    INSERT INTO TEST_DB_LINK_PROC
    SELECT 'X' FROM MYSCHEMA.MYTABLE@MYDBLINK
    WHERE ROWNUM < 2;
    COMMIT;
END TEST_PROCEDURE_DB_LINK_A;

EXEC TEST_PROCEDURE_DB_LINK_A;

But when I try to create the same procedure using the public synonym (like in the original insert statement), the procedure takes forever to compile:

CREATE OR REPLACE PROCEDURE TEST_PROCEDURE_DB_LINK_B is
BEGIN
INSERT INTO TEST_DB_LINK_PROC SELECT 'Y' FROM MYTABLE@MYDBLINK WHERE ROWNUM < 2;
COMMIT;
END TEST_PROCEDURE_DB_LINK_B;

It appears that I can work around this issue by putting the schema in each query. I have a lot of these procedures, so I'm hoping not to do that.


texts:
Here is the error message in its entirety:

  Failed:(-1073548784) Executing the query "BACKUP DATABASE [MCM] TO  DISK = N'F:\Backup\MCM\M..." failed with the following error: "BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

There is no further information. No backups for any database on the server are working right now, but they did succeed the day before. There is plenty of backup space. One thing to note, though I'm not sure if it's relevant, is that there was a transaction stuck in a Killed/Rollback state the day before; it was blocking on a remote procedure call to a Sybase database. The service was restarted to try to "fix" this stuck transaction (by someone I warned beforehand against doing such a thing).

What do I need to do to fix this situation so that I can get the backups working again?


texts: Get two weeks of data but group by a period of 7 days

texts:
I am looking to run a query that collects the number of results over the last 14 days, this is fairly easy and achieved like so:

SELECT COUNT(id)
FROM table_name

What I'd like to do is then group the results so that I can show this week and the previous week. Using the WEEK() option in MySQL allows me to get the previous two weeks worth of data, but grouping by weeks can return either 2 or 3 results depending on which week of the year the results fall into:

For example:

SELECT WEEK(date_logged) as week, COUNT(id) as rows
FROM table_name
WHERE date_logged > DATE_SUB(NOW(), INTERVAL 14 DAY)
GROUP BY WEEK(date_logged)

produces:

week 38: 10 result
week 39: 20 results
week 40: 3 results

rather than:

this week: 13 results (10 from week 38 and half of week 39)
previous week: 20 results (half of week 39 and all of week 40)

Hopefully that dataset illustrates what I'm after.

If possible, I'd like to avoid doing a select on a select and hopefully I'm not too far what I'm looking for.


texts: MySQL Dumper creating multiple files - spamming email confirmations

texts:
It's been a little while since I ran mysql dumper, but have done so successfully in the past many times, however when trying now something weird is happening.

I had not changed the settings (I have now after the problem reset to default to try and fix) but now when I run it I'm getting multiple files generated often 1 second after the other as can be seen in this screenshot of the files on the ftp;

I had email confirmation turned on too, and I'm pretty much getting an email sent every second as the interface refreshes.

The interface is showing something like this;

Normally, the last bit (2 seconds, 0 page refreshes) might show something like 40 seconds, 25 page refreshes as it processes bigger databases.

The version of PHP running on the server was updated a little while ago from 5.3 to 5.6.18. Could that have anything to do with this odd behaviour? As I said, the settings had not been changed - even so the default settings are pretty much what I had used anyway.

EDIT

I have also just tried re-downloading mysqldumper from scratch and installing it. Unfortunately, nothing changed. There must be some kind of configuration issue, but I don't know where to find that out...

These are my current settings - they are largely unchanged from the default;

If i click auto detect here I get the value 120795955


texts: How to prepare a date and double for a mysql replace statement?

texts:
I have a MySql table with three columns:

CU VARCHAR(3)
DA DATETIME
VA DOUBLE

I need to execute several REPLACE into mytable (CU, DA, VA) values("ABC", mydate, myval) statements from Java. mydate is a Date and myval is a double.

My question is, assuming I have a connection to my database, how do I format and include mydate and myval properly in this string statement?


texts: Restoring from a backup having dynamic file names

texts:
I want to restore a backup from a set of multiple files. The file names are generated dynamically. I have to restore the UAT with that backup. I need to schedule a job for this restore.

This is SQL Server 2012. Our backup script is split into 20 files, and everytime they are generated with timestamp. Now while restoring, we need the restore job to pick up those dynamic files from the backup location. Please advise.


texts: select/union output from two columns into three

texts:
I'm sure there is an easy answer to this, but my PostgreSQL isn't up to it at the moment.

I have a table hostname_facts with three columns:

hostname
name
value

hostname is a list of fully-qualified server hostnames.
name contains things like role (a TLA defining a server's role in the environment), env (part of an FQDN), memorysize, eth1_ip, fqdn, etc.
value - the actual value of role, env, memorysize, eth1_ip, fqdn, etc.

There are about 800 hostnames in this table.

From this table I've been asked to create a flat file that can be used as an inventory list for Ansible, which needs to be in the format:

[role.env]
hostname, value_of_role, value_of_env
hostname, value_of_role, value_of_env
hostname, value_of_role, value_of_env
...

So, I need need to pull out hostname sorted by value of name = 'role' as well as name = 'env'. This is stumping me!

I used:

select hostname,value from hostname_facts
where name = 'role'
union
select hostname,value from hostname_facts
where name = 'env'
order by value ASC

But that returns the 'name' values all in one long column, 'role' followed by 'env', with all hostnames repeated twice (obviously).

Can anyone suggest how to get just one set of hostnames with 'role' and 'env' as two columns alongside, rather than one underneath the other?


texts: Does the SQL Standard mention trigger events on DDL (ALTER, CREATE, DROP)?

texts:
PostgreSQL supports "Event Triggers" which is defined as,

  To supplement the trigger mechanism discussed in Chapter 39, PostgreSQL also provides event triggers. Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of capturing DDL events.

SQL Server "DDL Triggers",

  DDL triggers fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.

Oracle simply calls them "Triggers". Is there any standardized functionality that provides this functionality?


texts: SQL Server 2008 - Log file out of control & cant shrink it

texts:
Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

I am having trouble with my database, the log files is currently 250GB in size, I need to shrink this down somehow.

I have tried backing up the transactional log files, performing a full backup, running various shrink scripts but to no avail.

What are the correct steps to make this happen?

Can I somehow just truncate the whole log file then perform a full backup?

Thank You,
Paul


texts:
I have a drill-through problem apparently related to security. Users in one role are seeing timeout failures when trying to invoke a drill-through action. They have permission on the action through that role and seem to have all necessary dimension and cell security rights.

Profiler is not showing me any obvious reason this is failing. What else can I use to debug this?

If I modify the user's role membership, the action works as expected, so I'm confident this is related to security somehow.


texts: Index fragmentation problem after disabling page-level locking for an Index

texts:
I have an Index which has page level locking disabled and now I have index fragmentation issues for that index.

I was aware that I won't be able to Reorganize the index, but I believed that I would be able to rebuild the index.

Now I think rebuild also is not working. I am using the default fill factor of 100. My database is huge so I don't want the table/db size to get increased by giving a fill factor of 80% or less. By not working, I mean that the Avg Fragmentation remains the same after running Ola's script the previous night.

A little background on why page level locking is disabled.

I was getting lots of transaction deadlocks on this index in that particular table. This table was kind of a fact table where insert, update and delete was happening every moment. And there are some foreign key relationship to a few other tables with CASCADE deletes. So, I was getting lots of locks/deadlocks and after disabling the page level lock, I was able to get rid of all those deadlocks.

I started using Ola Hallengren's script for Index maintenance and I applied Index Rebuild for all those indexes for which Index Reorganize is not working.
But I noticed that Index Rebuild also is not working.

Now, I see a AvgPageFragmentaiton of 95.9413 and Page count of 1196826 for the index, which is not good.

Additional info:

I am using Ola Hall's script as shown below.

 EXECUTE dbo.IndexOptimize
    @Databases = 'DB_NAME',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
    @FragmentationHigh = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE'',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @SortInTempdb = 'Y',
    @MaxDOP = 0,
    @WaitAtLowPriorityMaxDuration = 0,
    @WaitAtLowPriorityAbortAfterWait= 'NONE',
    @Indexes = 'DB_NAME.[dbo].TB1,DB_NAME.[dbo].TB2'

My question is how to reduce the fragmentation in an index if the page level lock is disabled for that index?


texts:
I have this :

CREATE TABLE tickets(id serial PRIMARY KEY, source text NOT NULL, user_id int NOT NULL, shop_id int NOT NULL, created_at time DEFAULT current_time);
INSERT INTO tickets VALUES (0, 'web-mobile', 1, 2);
INSERT INTO tickets VALUES (1, 'web-destkop', 1, 2);
INSERT INTO tickets VALUES (2, 'web-destkop', 2, 2);

SELECT shop_id, source, COUNT(DISTINCT(user_id)) AS user_count
FROM tickets
WHERE shop_id = 2
GROUP BY shop_id, source;

Rexster : http://rextester.com/TDS9913

But what I would like is to get the sum of distinct user_id on web-*.

shop_id source  user_count
2       web     2


texts: Add index to mysql table with ndbcluster engine

texts:
I'm having trouble while adding index on mysql table with ndbcluster storage engine below is the error . [mysqlcluster ver. 7.4 GA]

mysql> alter table `sks_staff_office` add index `pid_index` (pid);
ERROR 1296 (HY000): Got error 156 'Unknown error code' from NDBCLUSTER

mysql> create ONLINE index pid_index on sks_staff_office(pid);
ERROR 1296 (HY000): Got error 156 'Unknown error code' from NDBCLUSTER

mysql> alter online table `sks_staff_office` add index `pid_index` (pid);
ERROR 1296 (HY000): Got error 156 'Unknown error code' from NDBCLUSTER
the link i reffered to is

http://dev.mysql.com/doc/refman/5.6/en/alter-table-online-operations.html

update: Found that the table (with ndbcluster engine) cannot be altered when the table's database is in use by remote connections. here is process list dump

mysql> show processlist;

+---------+--------------+-----------------------------+---------------+---------+------+-----------------------------------+------------------+
| Id      | User         | Host                        | db            | Command | Time | State                             | Info             |
+---------+--------------+-----------------------------+---------------+---------+------+-----------------------------------+------------------+
|       1 | system user  |                             |               | Daemon  |    0 | Waiting for event from ndbcluster | NULL             |
| 3383487 | remote_user01| remote.example.com:51567    | staff_db      | Sleep   |    0 |                                   | NULL             |
| 3388329 | root         | localhost                   | staff_db      | Query   |    0 | init                              | show processlist |
| 3388422 | remote_user02| remote.example.com:41311    | staff_db      | Sleep   |  531 |                                   | NULL             |
| 3388511 | remote_user02| remote.example.com:41554    | staff_db      | Sleep   |  522 |                                   | NULL             |
| 3389659 | remote_user02| remote.example.com:45603    | staff_db      | Sleep   |  405 |                                   | NULL


texts: Backing up large MySQL DB for DR purposes

texts:
Fundamentally I am just trying to come up with the best way to back up my MySQL DB nightly.  Complicated by the fact that:

its a 400 gb DB and growing
its running on windows
I am not permitted any real downtime

I have two replication servers going which I used mysqldump with a read lock to get going and I plan to implement any back-up strategy from a slave.

I have thought about setting up an additional replication server and running percona xtrabackup from it but I am not sure what my recovery strategy might look like taking back-ups from a linux box to restore on windows.

mysqldump is no longer a good strategy as the full process dump/zip/move/restore is 2 days at this point.

it's innoDB with a few myisam log tables mixed in.

looking for any insight or direction from those that have gone down this road already.  Any options outside of oracles enterprise back-up?

thanks


texts: Optimal selection of predefined non-materialised data

texts:
Take the following result set as an example:
Store    Category       Item       Cost       Stock     Sold     Ordered

ABC      Accessories    Lock       344.31     281       141      225
DEF      Components     Chain       24.15     312        53       45
GHI      Other          Gloves      57.50     121       102      100
...

We are looking to demo a feature for our application and this requires us to pull data in a specific format, and generate some additional values on top of the formatted data. In the case above the first three columns we would retrieve from the database and the last four columns we would generate values for.
Because this is only to be used for demo purposes, we would like to skip querying our database as this is costly due to the complex structure, taking around 30 seconds to retrieve the formatted data. This is not suitable for demos as it takes too long.
The solution we came up with is to export the formatted result set, and simply read it in to a query directly.

Please note that we cannot create any permanent tables to store this data.

The exported result set contains around 10,000 rows and a mix of varchar and integer columns.
The export is formatted as:
('ABC', 'Accessories', 'Lock', 344.31, 281, 141, 225),
('DEF', 'Components', 'Chain', 24.15, 312, 53, 45),
('GHI', 'Other', 'Gloves', 57.50, 121, 102, 100),
...

Hence, the question is, what is the most optimal way of selecting this predefined data set directly inside a query.

Would it be best to use a function/procedure/view?
And what would be the best way to select the data?

There are several options, such as:

wrap it inside a single select statement using values
SELECT * FROM (VALUES(...
union several select statements together
SELECT (...) UNION SELECT (...) UNION...
insert it into a temporary table
INSERT INTO #TABLE SELECT * FROM...
insert it into a table variable
INSERT INTO @TABLE SELECT * FROM...
etc.


texts: Analysis of MySQL database in real time Amazon RDS similar to phpMyAdmin

texts:
I'm using a single Amazon RDS instance (medium) to power several WordPress websites running on a single Amazon EC2 instance.

I used to have my databases on another EC2 instance and installed phpMyAdmin there.
phpMyAdmin offers quite neat run time analysis.

Is there anyway I can gain such analysis providing that I'm using Amazon RDS for my databases?


texts:
I'm a programmer, I work on an application where users can design their own custom forms
For Custom design of Forms I have something similar to this:
https://softwareengineering.stackexchange.com/questions/204097/dynamic-form-builder-forms-and-database-design
forms
-----
  id (PK)
  name
  owner_id (FK to users.id)
  (other fields)

form_elements
-------------
  id (PK)
  form_id (FK to forms.id)
  element_type_id (FK to element_types.id)
  caption
  (other fields)

element_types
-------------
  id (PK)
  name

element_list_values
-------------------
  id (PK)
  element_id (FK to form_elements.id)
  name
  value
  (other fields??)

The Problem that I'm that I'm having is that sometimes I would like to have 2 inputs in a row,
sometimes only 1 per row, sometimes to be 3 of them in a row
Like in here: https://www.layoutit.com/build
But How would I store that in my database what fields, how to model my Tables and what fields to add to my Table
How to change my form_elements table to support this ?


texts: I try to use select union with order by and now i can select it but it seem like it not order by date with what i expect why?

texts:
SELECT *
FROM (
    SELECT sale.NAME AS "Name"
        ,sale.responsible AS "Responsible:data:200"
        ,sale.action_type AS "Action Type:data:200"
        ,sale.id_customer AS "Id_Customer"
        ,sale.workflow_state AS "Workflow:data:200"
        ,sale.close_date AS "Close Date:data:200"
        ,sale.modified_by AS "Done By:data:200"
    FROM `tabsales_action` sale
    WHERE sale.workflow_state = 'Done'
    ORDER BY sale.close_date DESC
    ) `tabsales_action`

UNION

SELECT *
FROM (
    SELECT mfi.NAME AS "Name"
        ,mfi.responsible AS "Responsible:data:200"
        ,mfi.action_type AS "Action Type:data:200"
        ,mfi.id_customer AS "Id_Customer"
        ,mfi.workflow_state AS "Workflow:data:200"
        ,mfi.close_date AS "Close Date:data:200"
        ,mfi.modified_by AS "Done By:data:200"
    FROM `tabmfi_loan_action` mfi
    WHERE mfi.workflow_state = 'Done'
    ORDER BY mfi.close_date DESC
    ) `tabmfi_loan_action`

UNION

SELECT *
FROM (
    SELECT credit.NAME AS "Name"
        ,credit.responsible AS "Responsible:data:200"
        ,credit.action_type AS "Action Type:data:200"
        ,credit.id_customer AS "Id_Customer"
        ,credit.workflow_state AS "Workflow:data:200"
        ,credit.close_date AS "Close Date:data:200"
        ,credit.modified_by AS "Done By:data:200"
    FROM `tabin_house_credit_action` credit
    WHERE credit.workflow_state = 'Done'
    ORDER BY credit.close_date DESC
    ) `tabin_house_credit_action`

UNION

SELECT *
FROM (
    SELECT service.NAME AS "Name"
        ,service.responsible AS "Responsible:data:200"
        ,service.action_type AS "Action Type:data:200"
        ,service.id_customer AS "Id_Customer"
        ,service.workflow_state AS "Workflow:data:200"
        ,service.close_date AS "Close Date:data:200"
        ,service.modified_by AS "Done By:data:200"
    FROM `tabservices_planning` service
    WHERE service.workflow_state = 'Done'
    ORDER BY service.close_date DESC
    ) `tabservices_planning`

UNION

SELECT *
FROM (
    SELECT account.NAME AS "Name"
        ,account.responsible AS "Responsible:data:200"
        ,account.action_type AS "Action Type:data:200"
        ,account.id_customer AS "Id_Customer"
        ,account.workflow_state AS "Workflow:data:200"
        ,account.close_date AS "Close Date:data:200"
        ,account.modified_by AS "Done By:data:200"
    FROM `tabaccounting_action` account
    WHERE account.workflow_state = 'Done'
    ORDER BY account.close_date DESC
    ) `tabaccounting_action`


texts: Advantages to column constraints over table constraints

texts:
Is there any advantages whatsoever, other than style to writing this,

CREATE TABLE foo (
  a int PRIMARY KEY,
  b int
);

And then,

CREATE TABLE bar (
  a int REFERENCES foo,
  c int
);

Over,

BEGIN;
  CREATE TABLE bar (
    a int,
    c int
  );
  ALTER TABLE bar
    ADD FOREIGN KEY (a)
    REFERENCES foo;
COMMIT;

I'm trying to build a DDL generator, so I'm wondering if pays to keep constraints on the column (where I was generating them before), or to move them all outside to the table? I know these results produce the same table, I'm just wondering if there is any advantage under the hood -- less wal? etc?


texts: Force some composite type fields to be unique?

texts:
I want to create multiple image paths based on the language and size of the image. I decided to CREATE TYPE and then use it in the table.
CREATE TABLE asset (
  id bigserial not null constraint primary key,
  main_image img[]
);

CREATE TYPE img AS (
  lang varchar(2) not null,
  size varchar(32) not null,
  img_path varchar(255) not null
);

How do I make something like this: unique(lang, size) for my composite type? I want this combo to be unique.
How do I restrict any inserts that try to add an img_path for lang & size combo that already has img_path defined? I have tried to CREATE DOMAIN, but it seems like it is not intended for it.


texts: is restart necessary after a config change in mongodb?

texts:
After a configuration change is it necessary to restart the db so that the new config take effect?

The configuration was changed via this command:

  db.getSiblingDB('admin').runCommand( { setParameter: 1, cursorTimeoutMillis: 300000000 } )


texts:
My group has access to a single database on a SQL Server instance. We have a number of different people in our group, each working on separate projects. In trying to determine how to segregate projects within the database, I came across schemas, which seem to be a good way to define pseudo-databases within the single database instance, and segregate access as necessary at the schema level.

The problem I'm hitting is that a newly created schema seems to grant access to everybody, and I'm not sure how to enforce a whitelist approach. Do I have to manually deny access to all those not in a given group, or is there a mechanism to deny all access to a schema for all users by default and only grant access to those on a whitelist?


texts: Single sql query to select a record from parent table only if all it's records in child table has values

texts:
I've Table1 and Table2, Table1's PK is the FK of Table2. Now i need to perform a select from Table1 if and only if all the records in Table2 corresponding to a Table1 record has values (if any field value is null then skip).

ie, for an id in Table1 there have multiple sub ids in Table2. Out of those multiple ids if a particular field corresponds to any one id is null then select should be skipped.

Any one please help me out to write a single sql query for this requirement.


texts: T-SQL brain teaser: find non-overlapping sets

texts:
I would like to ask for help coming up with a query, which can identify groups on non-overlapping records.  Here is a sample scenario (admittedly contrived).  Let's say I have employees who are assigned to work on various projects.  While an employee can be assigned to multiple projects, he/she can only work on one project at any given time (don't you wish we all had this luxury :).  I need to find out which projects can be scheduled to be worked on in parallel because they do not share any employees.  Here is some code to setup sample tables and data.

--1. Create #Projects table
IF OBJECT_ID('tempdb..#Projects') IS NOT NULL
    DROP TABLE #Projects

CREATE TABLE #Projects (
    ProjectId INT,
    ProjectName VARCHAR(16)
    )

INSERT INTO #Projects ( ProjectId, ProjectName )
VALUES  ( 1, 'Project 1'), ( 2, 'Project 2'), ( 3, 'Project 3'), ( 4, 'Project 4'), ( 5, 'Project 5'),
        ( 6, 'Project 6'), ( 7, 'Project 7')

--2. Create #Employees table
IF OBJECT_ID('tempdb..#Employees') IS NOT NULL
    DROP TABLE #Employees

CREATE TABLE #Employees (
    EmployeeId INT,
    EmployeeName VARCHAR(16)
    )

INSERT INTO #Employees (EmployeeId, EmployeeName)
VALUES (101, 'Employee 101'), (102, 'Employee 102'), (103, 'Employee 103'), (104, 'Employee 104'),
        (105, 'Employee 105'), (106, 'Employee 106'), (107, 'Employee 107')

--3. Create #Employee_Projects table
IF OBJECT_ID('tempdb..#Employee_Projects') IS NOT NULL
    DROP TABLE #Employee_Projects

CREATE TABLE #Employee_Projects (
    ProjectId INT,
    EmployeeId INT
    )

INSERT INTO #Employee_Projects (ProjectId, EmployeeId)
VALUES (1, 101), (1, 105), (1, 107), (2, 102), (2, 103), (2, 107), (3, 104), (3, 105),  (3, 106), (4, 100), (4, 101), (4, 102), (5, 103), (5, 104), (6, 105), (6, 106), (7, 106), (7, 107), (8, 102), (8, 104), (8, 106)

And here is the query, which will show you the employees and projects we have created:

SELECT p.ProjectId, p.ProjectName, e.EmployeeId, e.EmployeeName
FROM #Projects p
JOIN #Employee_Projects ep ON ep.ProjectId = p.ProjectId
JOIN #Employees e ON e.EmployeeId = ep.EmployeeId
ORDER BY ep.ProjectId, e.EmployeeId

Our data looks like this:

ProjectId   ProjectName      EmployeeId  EmployeeName
----------- ---------------- ----------- ----------------
1           Project 1        101         Employee 101
1           Project 1        105         Employee 105
1           Project 1        107         Employee 107
2           Project 2        102         Employee 102
2           Project 2        103         Employee 103
2           Project 2        107         Employee 107
3           Project 3        104         Employee 104
3           Project 3        105         Employee 105
3           Project 3        106         Employee 106
4           Project 4        101         Employee 101
4           Project 4        102         Employee 102
5           Project 5        103         Employee 103
5           Project 5        104         Employee 104
6           Project 6        105         Employee 105
6           Project 6        106         Employee 106
7           Project 7        106         Employee 106
7           Project 7        107         Employee 107
8           Project 8        102         Employee 102
8           Project 8        104         Employee 104
8           Project 8        106         Employee 106

Visually we can tell that, for example, we can schedule projects 1, 2, 3 to run at the same time because they do not share any employees.  Let's call this group of projects "Group 1".  After that we could schedule projects 4, 5, 6.  Let's call it "Group 2".  Finally, in our example we have project 7 left and this would be our "Group 3". My question is, how can I write a T-SQL query to perform such grouping of projects?

Thank you!


texts: Mongoose subquery with additional filters

texts:
I'm new to mongoose for nodejs.  I'm trying to do the equivalent of
SELECT * FROM activityLogs
WHERE activityLogs.id NOT IN (
    SELECT activityLogId FROM graphs
    WHERE graphs.createDate >= '2020-01-01' AND graphs.createDate < '2020-02-15')
AND activityLogs.createDate >= '2020-01-01' AND activityLogs.createDate < '2020-02-15'

So I tried this:
  const startTime = new Date('2020-01-01');
  const endTime = new Date('2020-02-15');
  const ActivityLog = require('./models/activity-log');
  const notGraphedYet = await ActivityLog.aggregate([
    {
      $lookup:
      {
        from: "graphs",
        localField: "_id",
        foreignField: "activityLogId",
        as:"matched_docs",
        pipeline:
        [
          { $match: {createDate: {$gte:startTime, $lt:endTime}}}
        ],
      }
    },
    {
      $match: { "matched_docs": { $eq: [] }, createDate: {$gte:startTime, $lt:endTime} }
    }
  ]);

But my mongoose query gave this error:
 UnhandledPromiseRejectionWarning: MongoError: $lookup with 'pipeline' may not specify 'localField' or 'foreignField'

How can I overcome this problem and achieve the behaviour describe by my SQL statement?

ANOTHER ATTEMPT
I'm not sure if this is even the right approach, but I tried moving localField and foreignField checks into the pipeline based on someone's suggestion like so:
  const notGraphedYet = await ActivityLog.aggregate([
    {
      $lookup:
      {
        from: "graphs",
        let: { ALogId : "$_id" },
        as: "matched_docs",
        pipeline:
        [
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: ["$$ALogId", "$activityLogId"] },
                  { $gte: [ "$createDate", startTime] },
                  { $lt: [ "$createDate", endTime] }
                ]
              }
            }
          }
        ],
      }
    },
    {
      $match: {
        $expr: {
          $and: [
            { $eq: ["matched_docs", []] },
            { $gte: ["createDate", startTime] },
            { $lt: ["createDate", endTime] }
          ]
        }
      }
    }
  ]);

In the process of reviewing the results to see if it seems correct.


texts:
I don't use WSUS for SQL Server patching because we have so many different compatibility issues, requirements and downtime OLA's that it makes it impossible to deploy anything in this way - not sure of many who can to be honest.

However, we have a custom DBA 'app' that I would like to develop a snap-in for that alerts the team to any new updates released by Microsoft for SQL Server (SP's, CU's, Hotfixes etc.)

I thought, hey, let's just hook into the WSUS database, poll for new updates and display them on a web page if they exist...

It works to a fashion, but I noticed that there is a lot of missing updates in WSUS from SQL Server. For example I'm not seeing SQL Server 2014 SP2 CU1 in there which was released recently, 2014 SP1 CU7 is not there, but CU8 is. 2012 SP2 and SP3 are not their either, as another example.

We sync WSUS half-hourly, and all SQL Server products are selected.

Any idea why some SQL Server updates aren't coming through to us via WSUS? IS this normal? Do Microsoft not publish everything through WSUS?


texts: Is there any way to have Unique Constraint and NOT have Unique Index?

texts:
Looking into the Microsoft documentation, it says when a unique constraint is created, a unique index is created automatically.

Is there a trick/workaround to have a unique constraint and NOT have a unique index, so it does not take space?


texts:
Migrating SQL Server to a new machine. Migration involves setting up a number of linked servers, some SQL Server, Oracle and Teradata. No issues with SQL Server or Teradata servers, but every Oracle server I have tried has the ORA-01804 issue. The actual message is "Error while trying to retrieve text for error ORA-01804" but I figure the ORA-01804 is the issue.
TNSPING to the name in @datasrc uses LDAP to resolve and resolves fine. (note: there is no  tnsnames.ora file, ldap.ora and sqlnet.ora files were copied from the source server where everything is working.)
TOAD for Oracle has no trouble connecting to the database using that name as well.
SQL Server (source server is 2016, target is 2019, dev machine but same result on actual server):
SQL Server: 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 18363: ) (Hypervisor)

Oracle:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0

I had scripted the linked server connection from the server being migrated so everything should be equal (names changed to protect the innocent):
EXEC master.dbo.sp_addlinkedserver
    @server = N'ORACLE_SERVER',
    @srvproduct=N'Oracle',
    @provider=N'OraOLEDB.Oracle',
    @datasrc=N'orasrv'

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N'ORACLE_SERVER',
    @useself=N'False',
    @locallogin=NULL,
    @rmtuser=N'orasrv',
    @rmtpassword='redacted'

I'll skip the options, but they are there as well.


texts:
I created a mongo cluster in ubuntu and run the copy in the sharding with a small database, everything works fine.

The problem is that when I try to copy a database 300gb, is not getting done. The host where I have the mongos instance, run the following:

db.runCommand ({copydb: 1, fromdb: "tranches" todb: "tranches" fromhost: "mongoserver: 27025"})

And the following appears:

{ "code": 13398,"ok": 0,"errmsg": "exception: cant copy to sharded DB" }

What could be the problem ?, any idea for the problem?.


texts: Changes to Oracle EM 11G Database Control "Availability >> Backup Settings" don't stick

texts:
Trying to configure Enterprise Manager 11G Database Control for backups for the first time. Whenever I navigate to

Availability >> Backup Settings >> Policy tab

and try to make a change, for example, enable the checkbox for Automatically backup the control file and server parameter file (SPFILE) with every backup and database structural change, then click OK button, the GUI says the settings were saved successfully, but when I revisit the page, it's always showing the default settings. (I make sure to set the correct Host Credentials at the bottom of the tab each time)

If I do it enough times, occasionally, the changes will stick the next time, but upon a 2nd (and subsequent) visits (without changing anything, just viewing the page), the settings go back to default again.

Anyone see this behavior before? The Home page doesn't show any problems.


texts: Duplicate entries - condition on second table with join

texts:
I have 2 tables (1. PLATE, 2. MOVEMENT).
First table contains some duplicate plate numbers.
Every plate number (Fields: PLATE_ID, PLATE_COUNTRY, PLATE_NUMBER) has an unique id, which exists in movement table (Fields: MOV_ID, MOV_ENTRYDATE, MOV_EXITDATE) too.
How can I find out all duplicate entries with the movement date, where MOV_EXITDATE in first entry = MOV_ENTRYDATE in second movement data.

Example:

PLATE_ID   PLATE_NUMBER   PLATE_COUNTRY
1          AS25           UK
2          AS25           UK
3          GG10           D
4          GG10           D
5          BB40           I

MOV_ID    MOV_COUNTRY   MOV_ENTRYDATE     MOV_EXITDATE
1         GB            2019-03-01        2019-03-05
2         GB            2019-03-05        0001-01-01
3         D             2019-02-01        2019-02-20
4         D             2019-02-21        0001-01-01
5         I             2019-01-01        0001-01-01

Result:

PLATE_ID   PLATE_NUMBER   PLATE_COUNTRY    MOV_ENTRYDATE     MOV_EXITDATE
1          AS25           UK               2019-03-01        2019-03-05
2          AS25           UK               2019-03-05        0001-01-01

Note: Plate AS25 is duplicate in table PLATE and MOV_EXITDATE = MOV_ENTRYDATE.

I'm not successful with my query below.

Select PLATE_ID, PLATE_NUMBER, PLATE_COUNTRY, MOV_ENTRYDATE, MOV_EXITDATE
from PLATE t

inner join (Select PLATE_ID, PLATE_COUNTRY, count(PLATE_NUMBER) from PLATE
group by PLATE_ID, PLATE_COUNTRY
having (count(PLATE_NUMBER) > '1')) k
on t.PLATE_ID = k.PLATE_ID and t.PLATE_COUNTRY = k.PLATE_COUNTRY

inner join (Select MOV_ID, MOV_COUNTRY, MOV_ENTRYDATE, min(MOV_EXITDATE) as minexitdate
from MOVEMENT
group by MOV_ID, MOV_COUNTRY, MOV_ENTRYDATE) p
on t.PLATE_ID = p.MOV_ID and t.PLATE_COUNTRY = p.MOV_COUNTRY

inner join (Select MOV_ID, MOV_COUNTRY, MOV_EXITDATE, max(MOV_ENTRYDATE) as maxentrydate
from MOVEMENT
group by MOV_ID, MOV_COUNTRY, MOV_EXITDATE) p
on t.PLATE_ID = p.MOV_ID and t.PLATE_COUNTRY = p.MOV_COUNTRY

where minexitdate = maxentrydate

Thanks, for every helpful tip.


texts: Reusing sql query values for another query in single request

texts:
I have an sql table for some chats with a table messages, containing  id int pk, chat_id int, created_at timestamp.

Given a message id, is it possible to select it, along with 5 messages after it in same chat, in single query?

Or can I somehow combine the two following subqueries (having starting_message_id as an input)?

SELECT * FROM messages WHERE chat_id IN (
    SELECT chat_id from messages where id='starting_message_id'
) AND created_at > (
    SELECT created_at from messages where id='starting_message_id'
) ORDER BY created_at ASC LIMIT 5;

I feel that only two requests are sufficient:

selecting chat_id and created_at columns by chat id
selecting some messages having all data known

What would the correct optimised syntax be?


texts: SQL SERVER - "ApplicationIntent=ReadOnly"Listener redirect to fully qualified domain name

texts:
Hi all my DBA has setup a primary myNodeA and secondary node myNodeB and I'm trying to access the secondary node using "ApplicationIntent=ReadOnly" in JDBC connection string.

The problem we are facing is it redirects to just a server name and not a fully qualified domain name.

is there a setting or configuration in SQL Server to achieve this?

For example using that parameter redirects to  myNodeB server name only

This needs to redirect to myNodeB.myDomain.com


texts: Inserting values into a column when Table1 column A value matches Table 2 Column B Value

texts:
I have two tables ImportData and PurchaseInfo. I am trying to import [Supplier Contact Name] from ImportData to PurchaseInfo, when *[SupplierName] in ImportData = *[SupplierName] in PurchaseInfo

This is the query I tried:

INSERT INTO PurchaseInfo ([Supplier Contact Name])
SELECT [Supplier Contact Name]
FROM ImportData
WHERE PurchaseInfo.[Supplier Name] = SupplierInfo.SupplierName

I get the error

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "PurchaseInfo.Supplier Name" could not be bound.

Any help would be greatly appreciated.


texts:
The event log has hundreds of login failed errors for SQL Server. How do you find out the application generating them?

There are lots of potential candidates on the server and since it's logging in as SA just like half the applications do it's not enough that I know the username.

This is three log in attempts per second all day up to a point where all the sites halt for about 30 seconds. Some asp.net errors appear and then it's back to 3 failed SQL logins per second.

But I can't see a way to trace the actual program name.


texts:
I have two tables named Claim

EmployeeClaimId
      1144
      3481
      3452
      5760
      7296

and ClaimStatus

EmployeeClaimId   Amount   Version
       7            0.00      1
       7          300.00      3
       7          150.00      3
       7          400.00      1
       7          300.00      7
      14         1860.00      1
      14          541.00      7
      14          530.00      1
      14         2490.00      1

I want the sum of amount from second table where version is max for each claimid.

Can someone help?


texts: How to handle / model "cost of provided spare parts per PO line item" where one spare qty is divided between multiple line items? / Postgres 11

texts:
Problem
My objective is to help improve the data modeling / handling of my company’s present cost-of-goods [‘COGS’] calculation.
Everything in the current Postgres 11 table structure is pretty standard, and the calculations easy, except for one thing:  Most purchase orders include line items ‘A’ intended as spares for other line items ‘B’  and ‘C’ on the same order, and whose cost is intended to be factored into the cost of ‘B’ and ‘C’, which means that the cost of ‘A’ must be divided up among ‘B’ and ‘C’ proportional to the quantities of ‘B’ and ‘C.’
Of key consideration for the design of the tables is that suppliers routinely ship incomplete quantities [with respect to total quantity on purchase order], and that suppliers routinely ship items from multiple POs in the same shipment.
A typical purchase order invoice looks something like this :
  sku          qty     each     line_item_cost
----------------------------------------------
  product_B    200       50         10000
  product_C    400       75         30000
  spare_A      600        5          3000
  shipping       1     2000          2000

where ‘spare_A’ is a part for both ‘product_B’ and ‘product_C’ ; in the above example, 2000 of ‘spare_A’ cost should be applied to ‘product_C’ and 1000 to ‘product_A’.
Naturally, I’d like to represent this data as cleanly as possible.
I’m having a real tough time with this problem, and nothing analogous has turned up in my searches here / on SO, nor in my copies of SQL Cookbook  and The Data Warehousing Toolkit.
Current table structure
The current tables involved in this cost-of-goods operation are :
orders
order_items
payments [this records wire transfers, etc]
product_allocations [this records dollar amounts allocated to product line items on POs]
shipment_allocations [this records dollar amounts allocated to shipping and handling costs]
shipments [carrier ID, tracking, total ship cost, etc]
shipment_items [actual items within shipments],
The problematic table is order_items, wherein the aforementioned spare parts lines appear. Here’s the definition of that table with the problematic column noted in comments :
CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  created_date TIMESTAMP WITHOUT TIME ZONE,
  name TEXT , /*this is an identifier which combines SKU and PO# info */,
  order_invoice TEXT REFERENCES orders.order_invoice,
  sku TEXT,
  uses_spare TEXT REFERENCES order_items.name, /* because each spare is also an order item */
  spare_of TEXT[], /*this is the “problem column.”  Currently a 1d array so that it can hold multiple values which are drawn from this table’s “sku” column */
  unit_cost NUMERIC(19,2),
  quantity INT,
  product_allocations TEXT REFERENCES product_allocations.name,  /*records cost allocation*/
);

Currently, the cost of the spares is allocated by a function that considers the spares line item qty and cost, and divides it among the line items referenced in that record's spares_of column, proportionally with their respective quantities.
My gut tells me there must be a more optimal way to represent this “spare goes to multiple items” information.
One option considered was to make spares a separate table and have each spare-instance<->sku-instance relationship represented there and referred back to order_items, but this was decided against [however, if this is the best way, I’d like to know that].
Summary
What is the best way to represent the above-described relationship, i.e., the cost of a single spare_of record being associated with multiple sku line items, with the overall goal of producing an accurate and easily-reportable cost-of-goods (COGS)?


texts: Mongo Cluster sharded replica cluster design question

texts:
I have 5 old servers, each with single socket CPU, 4 physical cores(8 logical) and 128GB of RAM. I am getting started with mongoDB and plan to deploy MongoDB 4.2.2 on my 5 servers for a lab environment. I installed Virtual Box on each one, and plan to have a high-available installation of MongoDB. After a lot of reading I came up with a design and I'd like to get an opinion on my setup, particularly the shard and replica portion of the cluster.

I have the following:

5 Physical servers(denoted as P1 through P5) each with Virtual Box installed. All the VMs are running Ubuntu 16 Server edition guest OS.
3 Config servers sitting on 3 different VMs on 3 different physical servers. Each config server is allocated 1CPU and 4GB of RAM. Let's call these Conf1_P1 (Config server 1 is sitting on Physical server P1), Conf2_P2, Conf3_P3
2 mongos routers sitting on 2 different VMS on 2 different physical servers. Each Router is allocated 2CPUs and 8GB RAM. Let's call these Router1_P4 and Router2_P5
5 Arbiters sitting on 5 different VMs on 5 different physical servers. Each arbiter is allocated 1 CPU and 2GB of Ram. Let's call these Ar1_P2(Arbiter server 1 is sitting on Physical server P2), AR2_P3, AR3_P4, AR4_P5, AR5_P1
5 shards sitting on 5 different VMs on 5 different physical servers:

3 shards are allocated 2 CPUs, and 8GB of RAM. Let's call these SH1_P1, SH2_P2, SH3_P3.
2 Shards are allocated 1 CPU and 8GB RAM. Let's call these SH4_P4, SH5_P5. These are allocated 1 CPU because the physical server these VMS are sitting on only has 4 physical core, and 2 are already assigned to run Mongo Router.

5 replica members: 1 replica/shard on 5 different VMs, each is assigned 1 CPU and 8GB RAM. Let's call these REP_SH1_P2(the replica for shard 1 is sitting on physical server P2), REP_SH2_P3,  REP_SH3_P4,  REP_SH4_P5,  REP_SH5_P1
1 replica set for the 3 config servers
5 replica set, each with 2 members and 1 arbiter:

ReplSet1: SH1_P1, REP_SH1_P2, Ar1_P2
ReplSet2: SH2_P2, REP_SH2_P3, Ar2_P3
ReplSet3: SH3_P3, REP_SH3_P4, Ar3_P4
ReplSet4: SH4_P4, REP_SH4_P5, Ar4_P5
ReplSet5: SH5_P5, REP_SH5_P1, Ar5_P1

The summary for each physical server's resources usage is as follow

I decided to go with 5 shards to maximize parallelism (horizontal scaling).
The choice to have only 2 members in a replica set is due to disk space constrain. What are your thoughts on this particular design? Any concern I should be aware of?


texts: How to install Distributed Replay (dreplay.exe) for SQL Server 2017

texts:
In SQL Server 2014, dreplay.exe was available under "Management Tools – Basic" feature. However, in SQL Server 2017 that feature no longer exists (Enterprise edition) and it doesn't come with SSMS installer either.
Here is what available from SQL Server 2017 installer:

Did they move dreplay.exe somewhere else?

Any help is much appreciated!

Update 1.

It seems there is a bit of confusion around Distributed Replay and what exactly I am looking for. So I thought to include a diagram from this article:

Administration tool (dreplay.exe) is what I am looking for.

Controller (DReplayController.exe) is Distributed Replay Controller from feature selection screen. It is useless without Administration tool.

Client (DReplayClient.exe) is Distributed Replay Client from feature selection screen. It is useless without Controller.

Update 2.

OS: Windows Server 2016 Standard

SQL Server Management Studio version: 18.4 **

** (As this post suggests dreplay.exe came with SSMS 17.x, but got removed from 18.x)

SQL Server < Shared Components > version: 14.0.1000.169 ***

*** (displayed at Installation Type step or can be found in MediaInfo.xml Property Id="BaselineVersion" that is located in the root of disk with SQL Server installer)

Binn folder contents:

 Directory of C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn

18/11/2019  15:19    <DIR>          .
18/11/2019  15:19    <DIR>          ..
18/11/2019  15:19    <DIR>          DQ
22/08/2017  18:39           697,528 DReplayCommon.dll
22/08/2017  18:39            20,592 DReplayServer.tlb
22/08/2017  18:40            32,952 DReplayServerPS.dll
04/11/2019  15:25    <DIR>          Resources
04/11/2019  15:28    <DIR>          schemas
15/06/2019  11:09           602,848 SqlManager.dll
22/08/2017  18:47            60,088 SQLPS.exe
22/08/2017  18:48               379 SQLPS.exe.config
15/06/2019  11:09            29,472 sqlresld.dll
15/06/2019  11:09            29,264 SqlResourceLoader.dll
15/06/2019  11:08            59,672 SQLSCM.DLL
15/06/2019  11:08           133,200 SQLSVC.DLL
              10 File(s)      1,665,995 bytes
               5 Dir(s)  97,550,450,688 bytes free


texts: What does SPJ stand for in MySQL parlance?

texts:
MySQL has a lot of references to SPJ in MySQL including in official Oracle slides but I can't find what it actually stands for. I see it show up in a few blogs too.

I looked in the source and found nothing.

What does SPJ stand for?


texts:
This question (SQL Server transactional replication distributor), mentions a blog post HERE, which talks about how the distribution database works in relation to publishers and subscribers. Seems pretty clear.

Is the Distributor a fully fledged instance of SQL Server? I understand that it could run on a separate server, so it would seem like it. If not, what is the difference between a Distribution server and a SQL Server instance?

I guess from THIS article that the transaction logs of the publisher are stored in the distribution database, which are then read by the subscribers. And that this is called 'transactional replication'? Are there any other types of replication?


texts: Sql Server: A seemingly strange behaviour of BEGIN TRAN - COMMIT

texts:
First create two tables:

CREATE TABLE xyz (Id INT)
CREATE TABLE abc (Id INT)

Please, observe the following SQL code:

DELETE FROM abc
BEGIN TRAN
EXEC('
INSERT INTO xyz VALUES (1),(2)
DECLARE @x INT = (SELECT Id FROM xyz)
')
INSERT INTO abc VALUES (1)
COMMIT

SELECT * FROM abc

Running it outputs the following error:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

And there is no result. All as expected.

Now let us change the error to a syntax error by replacing xyz) with xyz. Here is the error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'xyz'.

But this time there is a result:

Why? What is going on?


texts: How can I filter an extended event by database in SQL Server 2008 R2

texts:
If I do an extended event session like this in SQL Server 2008 R2:

CREATE EVENT SESSION [Query tracing] ON SERVER
ADD EVENT sqlserver.rpc_completed
    (
        ACTION(sqlserver.sql_text)
        WHERE sqlsever.database_name = 'master'

I get an error:

  Msg 25706, Level 16, State 8, Line 8
  The event attribute or predicate source, "sqlsever.database_name", could not be found.

The same occurs for WHERE sqlsever.database_id = DB_ID('master')

Is there any way to filter by database?


texts:
Let's say I have table of cars and table of car manufacturers. Each car references one manufacturer.

Furthermore, each car that has manufacturer x requires an extra field. Say, cars from that manufacturer have an extra feature that needs configuring that no other manufacturer provides.

Where does this field go?

I think there should be another table with a one-one mapping between it and the cars table which holds the extra field for cars of that manufacturer.

So given this, how do I make sure that only cars of the correct type have this extra data in the other table? Or is this really bad data design?


texts:
I have an Oracle Database instance installed under an OL7. It is a 12c2.
Oracle GI also installed and my database has some files stored in ASM.

I am  using sqlplus to connect to the database instance and query v$asm_diskgroup. My ORACLE_HOME points to the home of my database instance.

When I connect without specifying  a "@CONNECTION" (e.g using: sqlplus user/name), then the view v$asm_diskgroup returns no rows.
When I connect specifying a @TNSNAME (e.g: sqlplus user/name@TNSNAME ), then the view v$asm_diskgroup returns data correctly.

Also, when I connect using sql developer, from another machine, the v$asm_diskgroup returns the rows.

Why are  no v$asm_diskgroup rows returned when I use sqlplus without @CONNECTION, that is, the default logon?