texts: How do I handle importing high amounts of transaction data into application DB?

texts:
For the moment, this is a hypothetical question. I've got a database that has a high level of transactional data coming into it. I also have a database that supports my website (my application database) to handle my shopping cart, orders, user accounts etc.

Various pieces of information that are reported on the site, for example stock levels, invoice data, item ordering statuses, new catalog items and a host of other pertinent information needs to be fed to the application and displayed on an ongoing basis, a fairly large amount of data.

Right now, the data is coming into the application database by SSIS and my DBA is telling me that because of the large number of SSIS packages required, it's infeasible to engineer a database that is modeled properly for our client facing applications, which is causing all sorts of problems for our application developers... I'm currently calling B.S. on this, but I'm putting it out there to you experts to give me some advice on best practices for handling input of high amounts of data from the back-end and re-indexing to maintain performance of the website including high requirement features such as catalog search, ordering and user account maintenance.

My initial thoughts are to feed data into the back end system using SSIS into transitional tables which can be loaded and re-indexed without affecting the 'live' tables, and then switch the transitional tables for their live counterparts. Is this feasible? Does it make sense? Is there a better practice? I'm not a DBA so it would be helpful to get some expert advice to take back to the team.


texts: SQL Log Messages - FILESTREAM and xp_cmdshell

texts:
I was perusing the SQL logs today and found the following:

  FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

  Configuration option 'xp_cmdshell' changed from 1 to 0. Run the RECONFIGURE statement to install.

These messages are peppered throughout every day during the past week, sometimes in blocks of 30+.  Also, when they happen, they appear in blocks 10-20 seconds.  I don't really see this as many times in the other servers I comb.  Is this anything to be alarmed about?


texts:
I'm hoping to get some information on how vCPU performance changes (if any) based on guest vCPU configuration in a VMWare environment. Not really worried about the licensing aspect, or SKU specific limitations here (that's a whole other can of worms I don't want to get into).

Most of my SQL Servers are configured as:

CPU: 4
Cores per Socket: 1 (Sockets: 4)

I noticed one of my servers was configured differently with more 'Cores per Socket' to achieve 6 vCPUs:

CPU: 6
Cores per Socket: 3 (Sockets: 2)

I know there are CPU/Memory limitations based on SKU but for simplicity's sake I'm really just wondering which might be the better configuration:

A: more 'CPU'
B: more 'Cores per Socket'
C: neither, it does not matter! :-o

Here's the output of SELECT node_id, online_scheduler_count FROM sys.dm_os_nodes:

On the 4 vCPU system

NODE_ID  ONLINE_SCHEDULER_COUNT
0        4
64       1

And on the 6 vCPU system

NODE_ID  ONLINE_SCHEDULER_COUNT
0        6
64       1


texts: DB2 - question regarding RESTORE OFFLINE backup and Archive Logs

texts:
I configured last night DB2 to be in Archive Mode.
I created required offline backup at 23:00 last night.
First online backup will be created at Friday night.
Archive logs now keep coming to the archive log location which is OK.

My question is:
can I use restore operation (ROLLFORWARD also) for combination with OFFLINE backup and Archive Logs  or ROLLFORWARD operation can be used only for combination ONLINE backup+Archive Logs???

I am confused little with that.

So my question is can I do the restore of offline backup last night and ROLLFORWARD for example archive logs to the point of time today at 10 A.M.?

Is this possible or no? If this is possible what will be the command?Will it be different like when restoring Online Backup+Archive Logs???

Thank you in advance


texts:
I have a homework, where I should create a database for a dental clinic using MySQL Workbench. This database will be used later for a web app for dentists.

Here is a picture of the database design:

Login Table:

Contain login information for dentists (it may be used by multiple dentists online)

The id_logged column is a foreign key of the id of logged in dentists, and it should be in all tables. (To let dentists see only their own patient info and details).

The patient_id column is a foreign key too. And it is taken from patient table and used as foreign key in med_history that represents the general medical information of the patients, patient_info that represents the general information, and the reason of visit and what tasks to do, and in the appointment table.

I have 2 questions:

1- Is there more than that information to add for a dentist database?

2- Is this database correct? Or are there errors?


texts: MySQL replication : Query partially completed on the master (error on master: 1053)

texts:
I got the below error.

Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'optimize table  '

=================

Now, I researched and got something

 Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'FLUSH TABLES'
    Here the server panicked on the "FLUSH TABLES" command.
    Research showed the master db was restarted at the time of the error.
    It seems perfectly safe to run "FLUSH TABLES" on the slave
    Connect to slave database
    Run "SHOW SLAVE STATUS;"
    Note "Last Erno" field is 1053 and "Last Error" is above error
    Run "STOP SLAVE;"
    Run "FLUSH TABLES;"
    Run "SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;"
    Run "SHOW SLAVE STATUS;" and verify "Last Erno" is now 0 (zero) and "Last Error" is blank.
    Exit mysql
    Run "cat /var/lib/mysql/relay-log.info" a few times and verify the index values are updating. If a long command is running it may take several minutes to see an update
    Note: It may take a while for the replication db to catch back up to the primary database.
    Continue to monitor occasionally until the mysql-bin.NNNN file in relay-log.info is the same as the latest binlog on the primary database (in /var/lib/mysql). This means the replication has caught back up.

===================================

you can see, they are asking to run "Flush tables" query again on the slave.

while my query was "optimize table  ".

Do I really need to run this 'optimize table' query again on my slave ? because i think its too large table and it will stop again.

======================================

What should I do, Please Help. Should I run Start Slave in Slave machine ?


texts:
Oracle by default caching the query results and function results(Buffer cache).

I have noticed this with AutoTrace utility, where the physical reads are huge on first execution, but from next execution onward it reduced dramatically.

Then what is the importance of query result cache, function result cache?

I have also noticed that the buffer cache does not gets invalidated even when I try to update physical table. But still I noticed physical reads are 0.

Could some one help on this to understand better?


texts: Can InnoDB data at rest encryption be overriden

texts:
I am exploring options for distributing a MySQL/MariaDB database with my app.  I want to ensure users (even the root user) cannot view or manipulate data in my database/tables. Recent releases of MySQL and MariaDB have data-at-rest capabilities:
MariaDB
https://mariadb.com/kb/en/mariadb/why-encrypt-mariadb-data/
MySQL 5.7.11 comes with InnoDB tablespace encryption
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html
But what is not clear to me is: Can the MySQL root user reset the encryption password on my database/tables to view or manipulate the contents?


texts: How to left join and count two tables where the rows (strings) are different

texts:
PostgreSQL 9.5.6
I have two tables - t1, t2.
t1 has a title column with titles separated by spaces.
t1.title
--------
hello kitty workd
gone wind forever

t2 has a path column with paths separated by dashes and prefixed 'path/'
t2.path
-------
path/hello-kitty-world
path/gone-wind-forever

GOAL: FOR EACH t1.title COUNT THE NUMBER OF TIMES IT SHOWS UP IN t2
Course of action: parse t2.path so that it looks like t1.title in order to do a left join and count**
I'm very new to this, if you have a better approach altogether I would appreciate any suggestions
So far I have:
select t1.title, count(t2.path) as num
from t1 left join t2 on t1.title = substring(log.path from 6)
where t1.title like '%'||split_part(substring(log.path from 6),'-',1)||'%'
group by articles.title;


texts:
I am working with a SQL 2005 database server that contains multiple databases and tables that are replicated from various sources. No one but administrators have access to this server.

There is a SQL 2008 R2 database server that exposes all of the tables from the 2005 databases using views (and only views) within a single database. There is no row-level filtering done on these views, they contain the same data as the original tables.

Regardless of why this architecture was chosen, it is causing three specific problems:

In SQL Server Management Studio, when browsing the views, there is no way to determine the indexes or primary keys on the base table.
When using tool such as SSRS to build a drag-and-drop type report, it does not automatically generate the relationship between the views (probably linked to #1).
Running an execution plan against a query written against the views is virtually useless as many of the nodes end up being "Remote Query"

I have done a little research into Indexed Views. The idea would be to basically replace the existing views with indexed views that have the same unique index as the base table. However, I don't think this is possible due to the following:

  All base tables referenced by the view must be in the same database as the view and have the same owner as the view. http://msdn.microsoft.com/en-us/library/ms191432.aspx

Does anyone have any thoughts on how to alleviate the above three problems?


texts: Relationship that are optionally more specific

texts:
Forgive my title, I couldn't think of anything that accurately describes what I'm talking about.

I currently have the following relationship.

CREATE TABLE Events
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    PRIMARY KEY(ID)
)

CREATE TABLE Locations
(
    ID INT IDENTITY(1,1) NOT NULL,
    EventID INT NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    PRIMARY KEY(ID)
)

ALTER TABLE Locations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)

Basically, an event can have zero or more locations.  What I want now is to associate a donation with either an event, or be more specific and associate it with a location.

CREATE TABLE Donations
(
    ID INT IDENTITY(1,1) NOT NULL,
    EventID INT NOT NULL,
    LocationID INT NULL, -- this is optional
    PRIMARY KEY(ID)
)

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(LocationID) REFERENCES Locations(ID)

However, if the LocationID is specified, then that LocationID should be a Location that belongs to the specified EventID.  In cases where LocationID is specified, it makes EventID redundant, and therefore it is no longer normalized.

Is the schema I proposed considered good form, or should I make both EventID and LocationID nullable, then enforce a check constraint that either exclusively EventID or LocationID should be set?

Down the road, we will then have registration.  Which I am proposing will look similar to this:

CREATE TABLE Users
(
    ID INT IDENTITY(1,1) NOT NULL,
    Username NVARCHAR(64) NOT NULL,
    PRIMARY KEY(ID)
)

CREATE TABLE Registrations
(
    UserID INT NOT NULL,
    EventID INT NOT NULL,
    LocationID INT NULL,
    PRIMARY KEY(UserID, EventID)
)

ALTER TABLE Registrations ADD FOREIGN KEY(UserID) REFERENCES Users(ID)
ALTER TABLE Registrations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Registrations ADD FOREIGN KEY(LocationID) REFERENCES Locations(ID)

Essentially, a user can register to an event once (regardless of whether they picked an location or not). The primary key I have is problematic if I make EventID nullable in order to prevent data redundancy with EventID and LocationID.

The current solution we are using to this problem is we do not have a Locations table.

CREATE TABLE Events
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    ParentEventID INT NULL, -- when null it's an event, otherwise it's a location for the eventid it references
    PRIMARY KEY(ID)
)

ALTER TABLE Events ADD FOREIGN KEY(EventID) REFERENCES Events(ID)

Which solves the EventID and LocationID data redundancy problem.  However I find this structure odd as events and locations are conceptually different things.  Down the road we may have fields that are specific to events and locations.

To determine whether records in this Events table are an event or location, if the PrimaryEventID field is NULL, then it is an event, otherwise it's a location for that EventID.

Please let me know a good schema for these business requirements.


texts: select two record containing dates in it with two input dates

texts:
Simply I have Two Input box

 Start_date
 End_date

This is my database table

| id | category | start_date | end_date   |
| 1  | gold     | 2016-05-24 | 2016-05-31 |
| 2  | silver   | 2016-05-16 | 2016-05-23 |

Now When I insert date like below:

start_date = 2016-05-25
end_date = 2016-05-27

I want to find out category between imputed date in input box.

I use this select query:

SELECT category
FROM tablename
WHERE '$start_date','$end_date' BETWEEN start_date AND end_date;

But I don't get any result. I know that if you use single date after where I get result but I want to search with two dates


texts:
I have two tables: chains and documents.

CREATE TABLE `chains` (
  `chain_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
  PRIMARY KEY (`chain_id`),
...
) ENGINE=InnoDB

CREATE TABLE `docs` (
  `doc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `chain_id` int(10) unsigned NOT NULL,
  `id_status` tinyint(3) unsigned NOT NULL COMMENT 'open,sent,inbox,read,etc',
  `date_created` int(10) unsigned NOT NULL DEFAULT '0',
...
  PRIMARY KEY (`doc_id`),
  KEY `id_status` (`id_status`),
  KEY `family` (`family`),
  KEY `chain_id` (`chain_id`),
  KEY `date_created` (`date_created`),
) ENGINE=InnoDB

One chain contains one or many documents.

I need to find all documents ordered by date_created and grouped by chain_id with limit.

SELECT `chains`.`chain_id`
FROM `chains`
JOIN `docs` USING (`chain_id`)
WHERE `docs`.`family`=1
AND `docs`.`id_status` IN (4,5)
AND `chains`.`is_archive`=0
GROUP BY `chain_id`
ORDER BY `date_created` DESC
LIMIT 0,200

Query time: 36.01 sec

EXPLAIN result:

+----+-------------+--------+------+---------------------------------+------------+---------+--------------------------+---------+----------------------------------------------+
| id | select_type | table  | type | possible_keys                   | key        | key_len | ref                      | rows    | Extra                                        |
+----+-------------+--------+------+---------------------------------+------------+---------+--------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | chains | ref  | PRIMARY,is_archive              | is_archive | 1       | const                    | 2789080 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | docs   | ref  | id_status,family,chain_id,test2 | chain_id   | 4       | for_test.chains.chain_id |       1 | Using where                                  |
+----+-------------+--------+------+---------------------------------+------------+---------+--------------------------+---------+----------------------------------------------+

How to optimize this query, or may be I need to redesign my tables?
May be some index can help me?


texts: Query tuning update with hash flow distinct operator

texts:
Need some help in understanding the slowness in one of the UPDATE statements like below:-
UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc.ENID,abc.Cdate
FROM dbo.table1 xyz WITH (UPDLOCK, READPAST)
INNER JOIN dbo.table2 abc WITH (NOLOCK)
on xyz.id=abc.id
WHERE xyz.flag = 0

Table1 has approx. 0.5 million rows and Table 2 has approx. 5 million rows
Slow Plan
Hash Match distinct flow operator shows a yellow alarm and message is:

Operator used Tempdb to spill data for executed with spill level 4 with 1 spilled thread"
Build residual:
database.dbo.table2.id as abc.id = database.dbo.table2.id as abc.id

I took a screenshot. Unfortunately due to security reasons I can't provide more than that, not even an anonymized plan. From my working station I cannot access the internet so there is no way I can get plan explorer to run there.

Generally for a smaller subset of rows it's under sec like when we have just matching 10K rows or something. But with higher amount of data this seems to be tipping point and app cannot afford 1 min of run time. From SSMS I get 30 secs but from app we have avg. 50 secs approx.  RCSI is in testing phase.
My good plan does not have that Hash Match Flow Distinct operator visible as shown in my screenshot, while rest of plan remains same. Good one completes under 3 secs or so. As seen nearly 16 seconds are spent on that operator. Can we eliminate it via proper indexing or query re-write?
Table schema
CREATE TABLE dbo.table1
(
    Recid  VARCHAR(128) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    Cdate DATETIME NULL,
    flag BIT   NULL DEFAULT (0),
    Rcode INT NULL,
    EDR VARCHAR(255) COLLATE SQL_Latin1_general_CP1_CI_AS NULL,
    id BIGINT NULL
);

CREATE TABLE dbo.table2
(
    ENID BIGINT IDENTITY(1,1) NOT NULL,
    EID VARCHAR(50) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    CID VARCHAR(350) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    CDate DATETIME NOT NULL DEFAULT(getdate()),
    id BIGINT NOT NULL,

    CONSTRAINT PK_ENID PRIMARY KEY (ENID ASC, EID ASC),
);

-- table1
CREATE INDEX ix_Cdate on dbo.table1 (Cdate) WITH (FILLFACTOR=100);
CREATE CLUSTERED INDEX ix_Recid on dbo.table1 (Recid) WITH (FILLFACTOR=80);

-- table2
CREATE INDEX ix_ENID_id on dbo.table2 (ENID,id) WITH (FILLFACTOR=100);

Changes
Changes I made and some numbers:

Added hint OPTION (QUERYTRACEON 4138) - avg. execution 7 secs down
from original 50secs, but app team seems not have access to perform
this in code. Need to check further on this.

OPTION (ORDER GROUP)gave same results of avg. 50secs so no improvement
there.

Added index as suggested:

CREATE INDEX i ON dbo.table2 (id) INCLUDE (CID, CDate);

Not much improvements there.  Avg. 45 secs and plan was similar to one attached in this question (top plan).
Before and after each test I made sure plan was not generated from previous cached plan.
Fast plan
Attaching the plan which is faster and without any change in data or query is still fast for same amount of rows in both tables. App team continuously submit above query throughout the day to finish the batch by completing those TOP 100. There is a plan change based on some tipping number and below is how the good plan looks:

Edit:- With everything unchanged, no code change or any index being added, as suggested when i am trying to add hint (FORCESEEK) its giving me below error

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


texts: Data synchronization across multiple databases

texts:
I have a Django application which uses PostgreSQL as a database.
The application stores hierarchical data. It is like a warehouse management system with multiple branches and hierarchy.

The current version is deployed on one main server and every other warehouse communicates directly with that server.

We want to add additional warehouses, but they are not well connected to internet (bad signal, bad channels, slow) so we are going to deploy application locally and synchronize the data with the main server in the background using database tools.

We don't want to rewrite the whole application and tables with additional "is_synchronized" columns.

Main
- Branch A (connects to Main directly via Web UI)
--- Branch A.1 (fine)
--- Branch A.2 (fine)
...
- Branch B (slow connection to Main) deploy locally
--- Branch B.1 (slow -> B) deploy locally
--- Branch B.2 (slow -> B) deploy locally

B.1 should synchronize the data to B's server and B should see all the data from their remote B.1, B.2 branches eventually. The same should happen with the Main server, it should see all the data from A and B branches eventually and same with their child branches.

In summary every parent should see every child's data (in case of a slow connection, eventually is fine)

How can we achieve this? What should we read? (we are total beginners in DB technologies, only web guys)

UPDATE: The child branches don't get data from parent. The child stores its own data and sends data to parent, parent stores all child data and its own. Main stores everything from everywhere


texts: Restore database into new DB without replacing the file structure

texts:
Our Network Admin has setup a MS SQL 2012 server and wants the DBs setup so the database files are located on 4 different drives.

My problem here is when I try to restore a DB from a different system that only uses a single database file, it wants to bring the old file structure over with it.  In the restore options, I can see the file structure it wants to use (1 database file), but I cannot change it.

Is there a way to restore the database into a new database without losing the file structure?

Thanks in advance.
Richard


texts: Previous 5 Years Data based on a Specific Year

texts:
I have a table like this:

ID|Date    |Name|Score
1 |1/1/2014|Erin|20.2
2 |6/3/2014|Erin|21.2
3 |7/2/2015|Emil|34.2
4 |1/1/2016|Erin|20.2
5 |2/2/2011|Paul|20.2
6 |1/1/2012|John|54.2
7 |1/1/2017|John|54.2
8 |12/31/2016|Erin|23.2

I want the data back 5 years based on the year I provide.
I am using this:

select * from tblStdnt where date > DATEADD(Year,DATEDIFF(year,0, '2016')- 5, 0)

But this gives me data starting from 2017. How do I fix this? any ideas?

It should give me:

  all data from 2011 to 2016 (the 5 year period, regardless of the month or day)

UPDATE
DBFiddle link


texts: Clobbered default database in postgreSQL

texts:
I created a script to drop a particular database and then reload just the structure. I created it using phppgsql.

I'm still new to pgsql, so I simply ran the script using \i myCreateScript.sql with the psql client. What happened was that it clobbered the default postgres database, and now all databases are utterly unusable. I didn't figure out until later that I was supposed to do \c myDatabase first to set the current database.

So.. What do I do now? I'm running on a linux server. Should I purge postgres and re-install? Is there another way to recover from this?

Thanks in advance.


texts: How can I Estimate Table Sizes within Schema (Oracle)

texts:
I'm trying to estimate the table sizes within my schema (in MB). This is what I have so far:

SELECT table_name, owner, last_analyzed
FROM all_tables

I'm fairly new to SQL so I have no idea how I would go around doing this.

Thank you.


texts:
How do you return the connection name via TSQL? This is visible in SSMS via the properties window. For example if you have a DNS alias "ProductionServer" for a machine named "SRVR0001" and further have a local server registration for this named "Live Production" and then

1) Open a new query providing a Server name of "ProductionServer" then the connection name will be "ProductionServer"
2) Right click the local registration and select new query, the connection name will be "Live Production"

And of course in both instances select @@SERVERNAME will return SRVR0001.

This is similar, but different, to these questions:
https://stackoverflow.com/questions/16088151/how-to-find-server-name-of-sql-server-management-studio
https://stackoverflow.com/questions/129861/how-can-i-query-the-name-of-the-current-sql-server-database-instance

I anticipate the answer may well be that this is a local property held by SSMS, and thus can not be returned by TSQL run on SQL Server.


texts: How to aggregate results from SELECT JOIN

texts:
I have two tables:

**user Table **

id   username
---  -----------
100  greg
101  john
102  patrick
103  bill

**user_subscription_plan Table **

id   user_id     amount
---  ---------   -------
1   100           50
2   100           52
3   101           10
4   103           100
5   103           110

I would like to make a query on the user table to extract username, but also to include id's from USER_SUBSCRIPTION which are related to the user_id

This is what I was trying:

SELECT
   u.id AS user_id,
   u.username AS username,
   usp.id AS subscription_id
   FROM user AS u
INNER JOIN user_subscription_plan usp on usp.id = u.id;

It gets me the results, but not in the form I need. If the user has two subscription entries, then it returns two rows for each subscription.

I would need to get only ids from Subscription table. Something like this:

SELECT
   u.id AS user_id,
   u.username AS username,
   usp.id AS subscription_ids -- Array of ids for each user
   FROM user AS u
INNER JOIN user_subscription_plan usp on usp.id = u.id;


texts:
I have a temp table which I update many times based on joins (each time almost all the rows). I have kept it as a heap since if I don't get an inner loop join then after each update I get a sort in the execution plan. And also the other tables which I join on need to have a unique index if I want to get the correct join (i.e. so I don't get a sort after the join). This answer helps explain the problem: Updating a table efficiently using JOIN
This was just an explanation for why the table is a heap.
Now my question is: how much do these forwarded records hurt the performance of my SP? Say I have 10 updates and with each update I get more forwarded records. Should I rebuild the heap temp table after say five updates? How can I figure out how many times to rebuild the heap?
So basically my question is how to deal with a table that gets updated many times and with each update basically all rows for a particular column get updated. This is a reporting query.
If I keep the table has a heap I get extra IO and if I create a clustered index I need to get all the statements to do an inner loop join and even if I achieve this I still will get page splits...
I have not tried this yet but I am thinking to change the string columns to fixed length columns in order to avoid forwarded records, but that seems to increase the IO also, at least at first...


texts: How can I expose an Amazon RDS database as read only to other partners who access through home broadband?

texts:
In one of our projects we have hosted our web application on an Amazon Linux AMI instance and it relies on a Amazon RDS MySQL instance as DB server.

One of our client's developer wants to have access to the DB to query the tables and its contents.  We do not want to provide an access with which he should be able to change any table contents (inadvertently).  We are looking something like PHPMyadmin or SQLWorkbench access, but only in read-only mode.

Adding complexity he works from home and have a broadband with dynamic IP.  Hence it seems we cannot grant IP based access as well.

As an alternate idea, we thought of replicating the database on to another server and grant full access to that replicated one so that we can be safe and satisfy the need. But this involves additional cost and the changes will not be replicated immediately.

Any ideas to expose the existing RDS as read-only will be a great advantage and is highly appreciated.


texts: How to flatten results of a table with two related "many" tables?

texts:
I've reorganized some tables in my database to be more flexible but I'm not really sure how to write the SQL to extract meaningful data from them.

I have the following tables (somewhat abbreviated for a clearer example):

CREATE TABLE Loans(
    Id int,
    SchemaId int,
    LoanNumber nvarchar(100)
);

CREATE TABLE SchemaFields(
    Id int,
    SchemaId int,
    FieldName nvarchar(255)
);

CREATE TABLE LoanFields(
    Id int,
    LoanId int,
    SchemaFieldId int,
    FieldValue nvarchar(4000)
);

With the following data:

INSERT INTO Loans (Id, SchemaId, LoanNumber) VALUES (1, 1, 'ABC123');

INSERT INTO SchemaFields (Id, SchemaId, FieldName) VALUES (1, 1, 'First Name');
INSERT INTO SchemaFields (Id, SchemaId, FieldName) VALUES (2, 1, 'Last Name');

INSERT INTO LoanFields (Id, LoanId, SchemaFieldId, FieldValue) VALUES (1, 1, 1, 'John');
INSERT INTO LoanFields (Id, LoanId, SchemaFieldId, FieldValue) VALUES (2, 1, 2, 'Doe');

The objective is to get a query that is flat for a loan with all its fields. (In the real world there will likely be between 20-30 fields for the same schema, but we just have 2 in the example):

LoanNumber   First Name    Last Name
----------   -----------   ----------
ABC123       John          Doe

I cannot use a pivot that references the 'First Name' and 'Last Name' because I'll have no idea what will actually be there.

I have a SQL Fiddle here with schema already in place.

How can I get the desired result?


texts: Why can't I insert an email on a table that has one more dot than an existing one?

texts:
I'm using the official Heroku add-on for PostgreSQL and I have a database running on version 12.4 of PostgreSQL.
I'm facing an issue where on my users table I have an email like this fakeemail@domain.com and I want to add another one that is fake.email@domain.com. This email column has an associated index that was defined like this:
CREATE UNIQUE INDEX users_lower_email_index
    ON public.users USING btree
    (lower(email::text) COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

It basically assures that I am not able to insert the same email twice if I for some reason try to add the same email with upper case letters. Beside this index, the only other index is the primary key which is a GUID, and there are no triggers or other constraints.
Whenever I try to insert the fake.email@domain.com I get the following error:
ERROR:  duplicate key value violates unique constraint "users_lower_email_index"
DETAIL:  Key (lower(email::text))=(fake.email@domain.com) already exists.

I have queried this table numerous times and I can guarantee that such an email does not exist. So the next thing I assumed could be wrong, is that the index could be somehow corrupted. So I rebuilt the index but I still wasn't able to insert that email - the same error is thrown. I also tried vacuuming the table and then re-indexing the index again but it still didn't work with the same error.
I have no idea what could be wrong or what to try next.


texts: Google Big Query Invalid time zone: +0000

texts:
Getting the error

Query Failed
Error: Invalid time zone: +0000

Is there a way to ignore time zones or default everything to UCT
This is my where condition

WHERE TIMESTAMP(FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%E6S%z", PARSE_TIMESTAMP("%Y%m%d", date )),"UCT") = TIMESTAMP('2018-03-05 00:00',"UCT")

date column is string in this format 20180307


texts: Zero Rows return on querying DMV sys.dm_os_performance_counters

texts:
I have a user on SQL Server 2014 Standard Edition (RTM) with SYSADMIN role with Server View State Permissions but when I execute DMV sys.dm_os_performance_counters it returns no record.

Any idea what is wrong with permissions?

Output of @@Version:

  Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation Standard Edition (64-bit) on
  Windows NT 6.3  (Build 9600: ) (Hypervisor)


texts:
I have two table one tblstationerystock (stationery_name,stationery_code, balance) and other tblstationerytranscation(stationery_code,trsntype,quantity).Trsntype column is transction type consists of two type of value (received or issued)
I want to calculate the value of balance in first table based on any transcation for stationery in second table.
Mathematically suppose there is  a stationery name 'PEN' in first table and in second table  there are three record for stationery 'PEN' -
STATIONERY. TRSNTYPE   QUANTITY
Pen.        Received.     200
Pen.        Issued.         30
Pen.        Issued.         40
In first table in balance column it should show 50(100-30-20)
I dont have much knowledge about sql .Is it possiblePlease guide me to achieve this .


texts: Will this SQL snippet REINDEX my GIN index? Why is it renaming an old index onto another?

texts:
I have a large PostgreSQL 9.3 database. One index on one table is quite large and appears to be bloated. I am using pgcompact from pgtoolkit to reduce database bloat without heavy table locks. However it would not reindex that large index, since it's a GIN index. It suggested this SQL snippet as a way to reindex this index. (mytable is the table, myindex is the index. myindex is just a GIN index on the nodes (bigint) column of mytable).

CREATE INDEX CONCURRENTLY pgcompact_index_17791 ON mytable USING gin (nodes) WITH (fastupdate=off);
BEGIN;
SET LOCAL statement_timeout TO 1000;
ALTER INDEX public.myindex RENAME TO pgcompact_temp_index_17791;
ALTER INDEX public.pgcompact_index_17791 RENAME TO myindex;
END;
DROP INDEX CONCURRENTLY public.pgcompact_temp_index_17791;

Will this snippet actually reindex the myindex index and reduce table index bloat?

What is the point of renaming the old index (myindex) to the name of the temporary index (pgcompact_temp_index_17791), and then renaming it back again? Won't that result in overwriting the currently being created temp index and then just renaming it back, meaning that myindex isn't rebuilt at all? The author of pgtoolkit appears quite knowledgeable, but I don't understand how this works, and I don't want to run SQL on my database without understanding it.

If this approach works, what's the difference between this and doing REINDEX INDEX myindex;?


texts:
I have a requirement where i want XML tags to be case insensitive in my xml queries.

For example:

DECLARE @myTable TABLE ( yourXML XML )

INSERT INTO @myTable SELECT '<z>1<c>2</c></z>'
INSERT INTO @myTable SELECT '<Z>1<c>2</c></Z>'

SELECT * FROM @myTable WHERE ( [yourXML].exist('for $x in /z/a where  ( ($x/b[1]) = 1 )  return $x')>0 )

Returns output as

yourXML
<z>1<c>2</c></z>

But i want the output to display without considering case of the XML tag. So my desired output should be

yourXML
<z>1<c>2</c></z>
<Z>1<c>2</c></Z>

How it can be done?


texts:
Just moved from wrong forum i paste here my question:

I just got a new client recently migrated from SQL Server 2000 to SQL Server 2005 due to vendor application limitation to new releases. Vendor application is compatible only max to SQL Server 2005. I found that the database never got a maintenance plan: it's about 14 years old, always up and, fortunately, always backed up (daily full backup, 4 hour differential and 10 minutes transaction log backup).

At first glance DB is split in 4 .mdf files (10Gb, 30Gb, 2Gb, 2Gb) and one .ldf file of 35Gb. .ldf is quite big because transactions log are max 100 mb. For what office folks say during those years there were 2 - 3 big deletion, but they don't know anything else. Server is a bit old (IBM x346, 8Gb Ram and only 70gb of free hd space), running W2003 R2 32bit with /pae and SQL Server 2005 with AWE.

At first glance index fragmentation is HUGE.

What are the best practices to start 'revive' :-) this situation ?


texts: Searching for a keyword across multiple columns

texts: I have a set of names of schools stored in each row in my schools table. the same table has four different category columns which store the names of the subjects they offer. I want to enable functionality where the user conducts a search on the table to find out all the names of the schools which teach a particular subject (for eg: physics). The name of the particular subject could be stored in any of the four subject columns. Please suggest a mysqli_/mysql_ query to conduct such a search or if you may so please, suggest a PHP script that would achieve the above functionality.


texts: SELECT sql_function(...) vs SELECT * FROM sql_function(...)

texts: I'm reading the PostgreSQL documentation for the first time and when facing SQL functions I think I have understood all the basics, but still can't see the difference between SELECT sql_function(...) and SELECT * FROM sql_function(...) and when to use one or the other.


texts: Do I need to rebuild indexes after importing a .bacpac into a database?

texts:
Do I need to rebuild indexes after importing a .bacpac into a database
or does the database takes care automatically?


texts: Enforce referential integrity for FriendRequest table that has UserA and UserB

texts:
I have a table called User that has UserName as a primary key.

I want to establish a relationship with enforced referential integrity between User and FriendRequest.

FriendRequest has the following: RequestID, UserNameA, UserNameB, Accepted.
UserNameA is the sender of the request and UserNameB is the recpient. Accepting requests sets the relationship between A and B to friends. Rejecting deletes the row. No answer means that Accepted is set to FALSE.

The problem is that I can't enforce integrity and without it I can enter any user name for UserNameA and UserNameB even if that particular UserName does not even exist on UserProfile.

Is there a way to make my FriendRequest table work in Access?

In this screenshot, before I added the second row for relationships, the relation type was displayed as one-to-many. As soon as I add the second relationship it becomes one-to-one.

EDIT

Alright, I did it again (carefully this time). You were right that I did only create one relationship instead of two. However, I get this wierd duplication of the same table. Please take a look at the second screenshot. As you can see UserProfile was duplicated to UserProfile_1. But my list of actual existing tables only show that UserProfile exists. What exactly is UserProfile_1?


texts: Using surrogate key after merging the person's data?

texts:
This question is about business intelligence with sql server 2008 R2.

If you have a person named Jim Brown with his birthdate and social security number (SSN) being located in three different source system.

Do you use surrogate key that should contain Jim's birthday and SSN after you have merged three data row into single one/row in the ETL phase?


texts: Query for cross matching multiple joined tables

texts:
I have four tables, such that:

CREATE TEMP TABLE country (
  countryid serial PRIMARY KEY,
  name text
);
CREATE TEMP TABLE city (
  cityid serial PRIMARY KEY,
  name text,
  countryid int REFERENCES country
);
CREATE TEMP TABLE user_country (
  userid int,
  countryid int REFERENCES country
);
CREATE TEMP TABLE user_city (
  userid int,
  cityid int REFERENCES city
);

I'm trying to write a query which will match users up based on their user_country and user_city entries, so that any one hit will produce a match if,

a user has any of the same user_country or user_city entries as another user.
given the relationship between city and country; if one user has an entry in the user_country table and another user has an entry in the user_city table with a city that is in the first users_country, this will also produce a match.

For example, given the following example data and the user id 1, the query will return the user ids 2, 3, 4: 2 is matched in the user_country table (both users have an entry containing the country_id 1) and user ids 3 and 4 are matched because their entries in the user_city table relate to London which has a country_id which matches user id 1's user_country entry. (In laymans terms, user 1 wants to go anywhere in England, so does user 2, producing a match; users 3 and 4 are more specific but because both London and Leeds are in England they both produce matches)

Given the same data and the user id 3, the query will return the user id 1 and 2, as the country_id in the city table relates to the country that both users 1 and 2 have selected, but not user 4. (in laymans terms, user 3 wants to go to London; users 1 and 2 want to go anywhere in England, so they match.  However user 4 only wants to go to Leeds, not London, so doesn't match)

INSERT INTO country (name) VALUES ('England'),('Canada');
INSERT INTO city (name,countryid) VALUES ('London',1),('Leeds',1),('Vancouver',2);

INSERT INTO user_country (userid, countryid) VALUES (1,1),(2,1);
INSERT INTO user_city (userid, cityid) VALUES (3,1),(4,2);

Can anyone help me with a single performant SQL query, that given a user id, returns a list of user ids which match the above scenarios?

Note: I'm using a recent version of postgres

EDIT
To clarify a few points: a user can have an entry in the user_city table without a corresponding entry in the user_country table for the country matching the city.  This is because a user may have an interest in visiting a city, but not want to see the rest of the country.  Being able to select a country is a shorthand way of selecting every single city in that country.
In layman's terms: just because a user wants to visit London, they don't necessarily want to visit all of the UK. So a user can have a user_city entry for London, but not have a user_country entry for the UK; also worth noting that if a user has a user_country entry for the UK, they won't have any user_city entries for any cities in that country (user wants to visit all of the UK, so doesn't make sense to list London too).

Separately a city will always be in a country, so will always have a country_id

EDIT 2
A bit more example data to replace the data above (not in addition to!)

INSERT INTO country (name) VALUES ('Germany'),('Spain'),('Italy');
INSERT INTO city (name,countryid) VALUES ('Berlin',1),('Madrid',2),('Rome',3),('Venice',3);

INSERT INTO user_country (userid, countryid) VALUES (1,1),(2,1),(5,3)
INSERT INTO user_city (userid, cityid) VALUES (3,2),(4,2),(6,3),(7,4)

Testing a match only on matching user_country table entries:

Entering user id 1 returns user id 2
Entering user id 2 returns user id 1

Testing a match only on matching user_city table entries:

Entering user id 3 returns user id 4
Entering user id 4 returns user id 3

Testing that a user_country matches all entries with related cities:

Entering user id 5 returns user ids 6 & 7

Testing that a user_city entry picks up a user_country entry:

Entering user id 6 returns user id 5
Entering user id 7 returns user id 5


texts: I need help creating an index for timestamp


texts: upgrade from Mariadb 10.2.* to 10.3.* using mariabackup?

texts:
Anyone successfully done upgrade from 10.2 to 10.3 using mariabackup without any downtime to the cluster?

I have 3 node cluster. Node1 serves the traffic rest are for standby and quorum. I would like to upgrade them from 10.2 to 10.3.

10.2 -> Has xtrabackup
10.3 -> Has mariabackup

IST doesn't happen when I upgrade 3rd node from 10.2 to 10.3. But when I copy xtrabackup* files from 2nd node and use wsrep_sst_method=xtrabackup_v2. IST works.
When I change it to mariabackup and restart it doesn't work.


texts: Replicating Results of Queries on Oracle

texts:
I wan to setup a database such that its data come from queries run on a remote database.  Basically the source database is very slow and we have no control over it.  So, I want to create a second database such that it would run the queries periodically on the source database, and saves the results into this second database.  Thus our team would only have to deal with this second database which is simpler and much faster.

Now, my question is what is the best way to run the queries and then save the result into the second database. I can use Java to do this, but I think there are better, and more efficient, database specific way of doing this.  Note that I don't want to just replicate tables, but result of queries.

Thanks,
Alison


texts: Why decrease the wait_timeout configuration parameter in MySQL?

texts:
Question
I'm working on an application that uses a connection pool in Apache Tomcat to communicate with MySQL. I was wondering why would you want to use a smaller wait_timeout than the default of 28,800 seconds? I see plenty of downsides to decreasing the wait timeout but what are the upsides?
Background
The parameter wait_timeout is defined as

The number of seconds the server waits for activity on a
noninteractive connection before closing it.

Problem
If I make the wait_timeout too small then MySQL kills connections that are still valid in the connection pool my web application is using. In my situation I am the only person currently using the application at any time so naturally the db connection goes idle when I'm not testing it. I could solve this problem by simply extending the wait_timeout on MySQL to something very large so that even if I'm away from my desk for 2 weeks the pool never returns a dead connection.
QUESTIONS

Why would anyone ever want a shorter wait_time?
Is the overhead of storing connections that bad?
Although I understand connection pools alleviate the overhead of CREATING connections, is it  such an issue to keep idle connections in memory?


texts: Baffled with MySQL LEFT JOIN query that I have to speed it up

texts:
I am kind of a puzzled with this query trying to optimize it but no way I am able to (due to my limited knowledge as a DBA). So, I asked the experts.

In my setup I have three (primary) tables. contacts, dnc_contacts (dnc = do not call) and callrequests. I have to select the contacts from contacts table, and copy the contact into callrequests table where contact is not equal to contacts from dnc_contacts. It all looks good so far, but since the data is growing, things are getting slower and slower. The original query I am currently using is:

INSERT IGNORE callrequests (contact_id, contact, call_status, campaign_id, phonebook_id, dnc_group_id, created_date, active_call, processed)
SELECT c.id,
       c.contact,
       1,
       '134',
       '70',
       '21',
       '2014-03-14 14:50:58',
       0,
       0
FROM contacts c
LEFT JOIN dnc_contacts d ON c.contact <> d.contact
WHERE phonebook_id = '70' AND d.group_id = '21'
ORDER BY c.id ON DUPLICATE KEY
UPDATE phonebook_id =
VALUES(phonebook_id), dnc_group_id =
VALUES(dnc_group_id), call_status = if(call_status = '2', VALUES(call_status), call_status)

This query is taking around 8 minutes and plus to complete depending on the data in contacts and dnc_contacts table. Schema for all these 3 tables is at http://sqlfiddle.com/#!2/1ec3a

Setup

The setup is a dedicated Xeon machine with 12 cores having 64 GB RAM and 38 GB InnoDB buffer pool size. The DB size is around 6 GB.

Callrequests table has around 5427519 records

Contacts table has around 20498053 records

Dnc_Contacts table has around 4042246 records

Here is the explain output from the select query above,

+----+-------------+-------+------+----------------------------------+------+---------+-------+---------+--------------------------+
| id | select_type | table | type | possible_keys                    | key  | key_len | ref   | rows    | Extra                    |
+----+-------------+-------+------+----------------------------------+------+---------+-------+---------+--------------------------+
|  1 | SIMPLE      | d     | ref  | fk_dnc_contact_dnc_group1_idx,v1 | v1   | 4       | const | 2014474 | Using index              |
|  1 | SIMPLE      | c     | ref  | fk_contact_phonebook1_idx,v1     | v1   | 4       | const | 2218560 | Using where; Using index |
+----+-------------+-------+------+----------------------------------+------+---------+-------+---------+--------------------------+

Is there a way the query can be speed up or maybe if we can change the logic either?


texts: Serializability schedules: can someone help me understand it?

texts:
I am struggling with the Databases courses I have, so I am trying to find answer to my question.
This has been asked:

Add the appropriate locks to enforce serializability on the following
schedule:
T1: W(Y)
T2: R(Y)

And:

Add the appropriate locks to enforce serializability on the following
schedule:
T1: R(Y) R(Z) W(Y)
T2: W(Y) R(Z)

For me, right now, it's hocus pocus. Could someone explain to me what exactly I need to do here... as in: what's the structure I need to follow? I need some structure to understand it.

R(Y) is a read action on database object Y
W(Y) is a write action on Y
S(Y) sets a shared (read) lock on Y
X(Y) sets a exclusive (write) lock on Y
U(Y) releases any lock on Y

PS: it's not about the answer to the questions, although I would appreciate it... yet it's about me understand WHY that's the answer.
Hope someone can help me. I'd appreciate it a lot!


texts: Why won't PostgreSQL 9.3 start on Ubuntu?

texts:
I have successfully installed PostgreSQL 9.3 from the APT repository on 2 VM's running Ubuntu 12.04 and 13.04...however, I cannot get it to install properly on my host machine running Ubuntu 12.04.

The install (this time) seems to have gone ok, but perhaps there is an error I'm not understanding:

* No PostgreSQL clusters exist; see "man pg_createcluster"
Setting up postgresql-9.3 (9.3.0-2.pgdg12.4+1) ...
Creating new cluster 9.3/main ...
  config /etc/postgresql/9.3/main
  data   /var/lib/postgresql/9.3/main
  locale en_US.UTF-8
  port   5432
update-alternatives: using /usr/share/postgresql/9.3/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode.

So I then try to add myself as a PostgreSQL user, but I get this:

createuser: could not connect to database postgres: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I cannot see PostgreSQL running in system monitor, and there is no file in the /var/run/postgresql/ folder...completely empty.

EDIT: On the VM's, there is a file in /var/run/postgresql/ called 9.3-main.pid

There is nothing on the host machine log file located /var/log/postgresql

So... what's going on here that isn't going on in my VM's? Like I said, the other installations on the VM's, including PostGIS and PGAdmin came in perfect...no idea why this host machine isn't going through...


texts: Update table with values from another table runs really slow

texts:
I have a PostgreSQL table (tab_A) with about 32 million records, and I have a second PostgreSQL table (tab_B) with about 4000 records. tab_B contains 3 fields whose values I intend to pass on to tab_A (field1, field2, field3).

tab_A

32 million records
Unique identifier (id)
BTREE index

tab_B

4000 records
Unique identifier (id)
BTREE index

I am trying to do the job with the following query (previous to do this I have already created the 3 new fields in tab_A to host the values):

UPDATE tab_A
SET field1 = t2.field1, field2 = t2.field2, field3 = t2.field3
FROM tab_A t1 JOIN tab_B t2
ON t1.uprn = t2.uprn;

This query runs on and on for more than 5 hours and I eventually need to stop it because it doesn't seem to me it should be taking that long (my understanding is that if both tables contain an index this should be pretty fast).

Any ideas on whether I am missing something here? Perhaps it's normal it takes that long taking into account tab_A contains 32 million records? Any other approach to run this more efficiently?


texts: Why is my composite index not used for this table?

texts:
I am working with a fairly complicated schema, but I've simplified it for the purposes of this question.

                Table "public.scheduler_appointment"
       Column        |           Type           |              Modifiers
---------------------+--------------------------+---------------------------------
 id                  | integer                  | not null default nextval(...)
 provider_id         | integer                  |
 title               | character varying(200)   | not null
 startTime           | timestamp with time zone | not null
 endTime             | timestamp with time zone | not null
 ... more columns ...
Indexes:
    "scheduler_appointment_pkey" PRIMARY KEY, btree (id)
    "scheduler_appointment_provider_id" btree ("provider_id")
    "scheduler_appointment_provider_id_68f646d601089728" btree ("provider_id", "startTime", "endTime")
    ... more indexes ...
Foreign-key constraints:
    "provider_id_refs_id_a536838c" FOREIGN KEY ("provider_id") REFERENCES scheduler_provider(id) DEFERRABLE INITIALLY DEFERRED
    ... more fks ...

I'm doing a complex query that includes a few joins, and slightly more complicated logic, but an EXPLAIN on that query revealed that 80% of the time was spend doing a Seq Scan on this table.

I read this article which suggested indexing on the column that will be equality tested first, then on columns that will be range tested. After adding a composite index on ("provider_id", "startTime", "endTime"), I noticed that the query plan did not change at all, and it was still doing a seq scan.

So I wrote the following smaller query to try and isolate the part of the query that should use an index.

EXPLAIN ANALYZE
SELECT * FROM scheduler_appointment
WHERE (
    "provider_id" = 14
    AND "startTime" >= '2014-07-19 14:30:00+00:00'
    AND "endTime" <= '2014-07-26 14:30:00+00:00'
)
ORDER BY "startTime" ASC;

However, this still does not use the index like I would expect.

 Sort  (cost=36.02..36.52 rows=201 width=143) (actual time=0.477..0.483 rows=57 loops=1)
   Sort Key: "startTime"
   Sort Method: quicksort  Memory: 33kB
   ->  Seq Scan on scheduler_appointment  (cost=0.00..28.33 rows=201 width=143) (actual time=0.050..0.428 rows=57 loops=1)
         Filter: (("startTime" >= '2014-07-20 00:00:00+09:30'::timestamp with time zone) AND ("endTime" <= '2014-07-27 00:00:00+09:30'::timestamp with time zone) AND ("provider_id" = 14))
 Total runtime: 0.538 ms

The first thing I noticed was that it's rewritten my filter to put the provider_id at the end. Is this the reason that it refuses to use the index, or is it simply that I don't yet have enough data in the table to make an index scan worthwhile? How would I go about testing this, or forcing it to use an index if available?


texts: How can the TNS connect identifier passed to SQL*Plus be accessed from the run script for reconnecting?

texts:
I wonder how the TNS identifier can be used within a SQL script. In this example

$ sqlplus MY_USER/pswd@TNSID @script.sql par1 par2

I'd like to get TNSID as a string in order to use it from script.sql but I have no idea where to find it.

The goal is to reconnect as a different user on the same instance.


texts: SQL Server : How to disable trigger for an update only for your current session?

texts:
I am working on SQL Server 2008 R2.

I have a table benefit which has a AFTER INSERT, UPDATE trigger named tiu_benefit.

I want to write an UPDATE statement for this table to update 1 row but I dont want its trigger to fire. I know I can disable trigger before UPDATE and then enable the trigger after UPDATE :

DISABLE TRIGGER tiu_benefit ON benefit;
GO
UPDATE benefit SET editor = 'srh' where benefit_id = 9876
GO
ENABLE TRIGGER tiu_benefit ON benefit;
GO

But this disable and enabling trigger will affect all users logged in currently. So there is a possibility that another user run an UPDATE/INSERT while the trigger is disabled by my script which is not good. Thats why I only want to disable and enabling trigger for my current session. Is it possible? If yes please tell how.

Thanks


texts: How to optimize SQL with WHERE and ORDER BY


texts: Using sys.diana and sys.pidl to parse PL/SQL

texts:
It seems to me that it should be possible to parse existing PL/SQL with the packages  sys.diana and sys.pidl, but I have found nothing on the internet with the exception of a few scripts that seem to unwrap a wrapped package (and that is useless for my purposes).

So, does anyone know about a pointer into the right direction?


texts: Which NoSQL database(s) should be used for advertising platform?

texts:
What NoSQL database or combination of databases best meets these criteria?

Very fast writes and reads
Persistent data that won't get lost in a crash
Plays nice with PHP
Works well in the cloud (EC2)
High availability
Preferably open-source
The data needs to be able to be manipulated in a way to show statistics reports (sums, ranges, groupings, etc)

EDIT
Would a document database such as couch give me the following features?

Very fast writes and reads
Persistent data that won't get lost in a crash
Plays nice with PHP
Works well in the cloud (EC2)
High availability
The ability to manipulate data to show statistics reports (sums, ranges, groupings, etc)

EDIT
If you were me, would you choose couch, mongo or riak as the NoSQL database for me to use on my PHP system knowing that these are my primary goals:

Very fast writes and reads
Persistent data that won't get lost in a crash
Works well in the cloud (EC2)
High availability

I'm examining non-traditional datastores for my environment and am having some trouble deciding which way I should go here.

Either one of the two edits provided above would've been a better match for the question at hand, allowing for better answers and probing questions. It's a shame that this wasn't caught in time.


texts: how to use trigger to set value based on query result

texts:
would appreciate any example on how to set value on the added row based on query results from different table.

something like this

delimiter |

CREATE TRIGGER trigger BEFORE INSERT ON table1
FOR EACH ROW
  BEGIN
    NEW.value = (select value from table2 where id=NEW.id)
    NEW.value2 = (select value2 from table2 where id=NEW.id)
  END;
|

delimiter ;


texts: Triggers that inserts values to a table from another table

texts:
I am trying to create a report table that contains drug_id, total sales and average sales from an inventories table. This code doesn't work as it should. It duplicates records and when I insert additional rows in inventories, it doesn't reflect. I am new to triggers and complex sql queries, please assist.

DELIMITER //
CREATE TRIGGER `report` AFTER INSERT ON `inventories`

FOR EACH ROW
  insert into report (drug_id,total_sales,average_sales,amount_remaining)
 select

i.drug_id,
coalesce(p.total_sales, 0) total_sales,
coalesce(s.average_sales, 0) average_sales,
coalesce(t.amount_remaining, 0) amount_remaining

from inventories i
left join
(
 select drug_id, sum(quantity_sold) total_sales
from inventories
WHERE MONTH(complete_sold) = 01
group by drug_id
) p
on i.drug_id = p.drug_id
left join
(
select drug_id, avg(quantity_sold) average_sales
 from inventories
WHERE complete_sold BETWEEN '2016-11-01' AND '2016-12-31'
group by drug_id
) s
 on i.drug_id = s.drug_id
 left join
(
  select drug_id, sum(amount_remaining) amount_remaining
from inventories
 group by drug_id
 ) t
 on i.drug_id = t.drug_id

 //
DELIMITER ;


texts: How to restore some percentage of backup file?

texts:
I have a backup file size 10 GB in a remote location. When I tried to restore it in my pc it is giving error "insufficient storage."

Is there any alternative way to store some percentage of the data that includes the whole schema?

I am using SQL Server 2014 Enterprise.


texts:
I'm a bit confused about the proper way to update one table, based on the values of corresponding rows in another table.

Here is the query I have:

UPDATE
    features,
    images
SET
    features.deleted = 1
WHERE
    features.objectType = 'image'
    AND images.id = features.objectId
    AND images.uploaded = 0;

My goal:

  I want to set the features.deleted column value for all rows in the
  features table to be 1, only if the corresponding
  images.uploaded value for that associated image is 0.

Business logic:

  I want to go over all the images that been created in my app, and if
  the image wasn't successfully uploaded, I want to delete the
  corresponding features record.

I believe this works, but have been having a hard time finding specific examples using my approach (most suggest using joins).

My concern is that the UPDATE query is too general: I just want to make sure that it will only update feature records that have their associated image record with an uploaded value of 0.

Thanks for any guidance/confirmation!


texts: Not Null Fields and Unmatched Number of Columns

texts:
I am in charge of a task of migrating data from MySQL to PostgreSQL. I have already migrated the MySQL database to PostgreSQL with the help of pgloader. My problem is I need to insert the data from the migrated PostgreSQL to our production PostgreSQL which is using Django as backend. I have mapped the columns for each database and the numbers don't match. We have more columns in our production DB than the one that I migrated.

I want to know what to do with the fields/columns that will not be filled with data and has a NOT NULL constraints? How should I approach this? I am new in this kind of task and my apologies for asking.


texts: Fetch partitions where values are present

texts:
I have table partitioned by range of dates (monthly).
Having a list of dates, I want to select all the partitions, where values from the list of dates are present. That is to avoid scanning the partitions.
What would be the query to do that?
Say, I have:
partitions: calendars_y2021_m01, calendars_y2021_m02, calendars_y2021_m03
list of dates: 2021-01-01, 2021-02-01, 2021-02-02
I want to:
select partitions from calendars
where dates in (2021-01-01, 2021-02-01, 2021-02-02)

That should give me all the rows from partitions: calendars_y2021_m01, calendars_y2021_m02
The only solution I got myself is to identify the target partitions and fetch them one-by-one with python loop, but that is slow.
I am using RANGE partitioning. Selecting from calendars will scan the partitions for the specific dates. I just want to get all the data from the selected partitions into memory to avoid that.
If I select * from calendars where dates in (list of dates) PostgreSQL will first (1) define target partitions, then (2) SeqScan the partitions for the values in list of dates. I want to skip the second operation and get the entire partitions.
Once I have the partitions, I will process them in memory with python pandas. That is way faster, then seq scan. Also, I am just biased towards pythonic tools.


texts: Removing grants for databases that no longer exist

texts:
We have quite a few MariaDB servers with whom the permissions have gotten a bit messy over the years. I'd like to tidy them up.

I've already written some tools for presenting grants/table information nicely, and this has highlighted that that some users have a LOT of specific grants for databases or tables that no longer exist. On one server there are over 150 databases referenced by users in SHOW GRANTS, but only 12 databases still exist.

How might the users of Stackoverflow deal with housekeeping of this order?

Are there any tools that do basic auditing on Grants, accesses and Databases that will help me sort through this mess?

Ideally, something that spits out a list of mysql REVOKE statements that remove grants for databases that no longer exist, that can then be reviewed by a human before pasting as a query.

I can see a way to anagrammatically sorting through them, but it is likely to take a few hours to write that up. It may still come to that but I'd like to know if this wheel has already been invented.


texts: How can I trace the root cause of a MySQL database table that crashed?

texts:
Are there any standard practices or methods for tracing the cause of a MySQL table crash?

If I repair a table that had crashed, how do I determine what caused it to crash in the first place?

Storage Engine: MyISAM
MySQL Version: 5.6.38

I'm specifically interested in learning how to troubleshoot this common issue, not particularly in the methods to avoiding it.


texts: can i shrink log file in full recovery model

texts: i have configured Always on Availability group where iam unable to shrink log file size through simple recovery model. can i shrink the log file size in full recovery model if yes or possible then is there any impact for it..


texts: mysql ibdata1 file corrupted or increased unusually

texts:
We are using MySQL 5.2 version all InnoDB tables.

I have observed that ibdata1 file got increased very unusually like occupied 500 GB in 2 days.

In last 6 months, it had taken only 6 GB though there were no inserts at all. I also deleted the ibdata1, ib_logfile0 and  ib_logfile1 and restarted mysql.

It worked fine. but again after 6 months same issue, and the ibdata1 got increased to 550 GB. There are no errors found in error log file.

I would like to know what may be the reason in general ?


texts: Why is database ownership by groups not allowed? Which user should own a database?

texts:
Per microsoft "ALTER AUTHORIZATION for databases for SQL Server":

  Requirements for the new owner:

  The new owner principal must be one of the following:

  A SQL Server authentication login.
  A Windows authentication login representing a Windows user (not a group).
  A Windows user that authenticates through a Windows authentication login representing a Windows group.

Apparently a group cannot own a database.  Indeed invoking

alter authorization on database::MyDatabase to [domainname\SQLServerAdminGroup]

results in the message

  An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or assymetric keys.

Having a single member of, say, a Windows security group of SQL administrators be a database owner seems vulnerable to that account becoming disused.  That seems to be what happened when the owning employee left the company in this question.  This answer summarizes the problem of database ownership being an NT primary principal as follows:

  Having the database ownership default to the NT primary principal creates a containment issue (the owner is an NT SID managed by AD and does not travel with the database files, the NT account can be thumbstoned etc etc etc).

I'm new to SQL Server administration and the restriction that only primary principals can be owners stands out compared with ownership of, for example, files.  Per microsoft "How Owners are Assigned and Changed":

  By default, a new object's owner is the security principal identified as the default owner in the access token attached to the creating process. ... The only exceptions occur when the user is a member of either the Administrators group or the Domain Admins group. In both cases, the Owner field in the user's access token contains the SID for the group, not the SID for the individual user account. The assumption is that administrative accounts are used only to administer the system and not for any individual purpose. As a result, objects created by one administrator can be managed by other administrators in the same group.

In other words,

for files created by an administrator the default owner of the file is the group, while
for databases the owner cannot be a group.

This leaves me with the following questions:

Is there an underlying reason why databases cannot be owned by a secondary principal?
Which principal should own a database?  Is there a best practice?  If so, what is the reasoning behind that best practice?


texts: Metric to standard unit conversion in SQL Server

texts:
I am giving simplified version of my two table which are as follow.

tblRecords

ID  unit  val
--- ---   ---
1   KV    3
1   BAR   4
2   KV    7
2   KG    5
3   C     37

tblUnit

unit    convUnit    formula
---     ---         ---
KV      CV          @val * .865
KG      lbs         @val / 2.205
BAR     PSI         @val / 14.504
C       F           @val * 9/5 + 32

I will not be able to change table structure.

I know which unit will be converted in which unit (i.e. I know KV will be converted to CV, KG to lbs etc..).

Please note that formula is a varchar column and I have reference from table tblRecords.unit to tblUnit.unit

But my question is how to calculate the formulas on the fly in single query. i.e. I want following output.

ID  unit    val     convVal(column will be counted on the fly using formula stored in tblUnit.formula field)
--- ---     ---     ---
1   KV      3       2.595
1   BAR     4       0.27578599
2   KV      7       6.055
2   KG      5       2.267573696
3   C       37      98.6


texts: Maintenance plan don't stop & don't backup all databases

texts:
I have a server with 100 databases,
I have a maintenance plan (MP) for backups.
I noticed that the maintenance plan don't backup all databases when I'm looking at the backup folder destination I can see that it stops after 50 databases +-.
The job is still in running mode - it doesn't stop.

So in one hand, I can see that there are no new backups files (after the first 50) and on the other, the job is not stopping.


texts: Shopping cart database, should order object have a relationship with the product, or should I duplicate the product information

texts: I am creating a shopping cart and an currently planning / building the checkout process. Everywhere I look I see the recommendation to have an order table, and in that order table have a productId for the product that is being ordered... That seems nice. But I can not seem to get over the issue of what happens of that product is updated or worse deleted? Then the record of what is ordered is updated or deleted. Would it be better to duplicate the product details into an OrderedProduct table so you have a snapshot of what the product looked like when it was ordered? Then even if the product is changed or deleted, you have all the information from when it was ordered? That is what I am doing, but want to make sure I am not making a mistake or there is a better way to accomplish it.


texts:
I am looking for a RAM/memory based database, and these two seem to be interesting:

SQLite
Oracle Berkeley DB

Do you have any other suggestions?

I need more than one executable to be able to access the DB concurrently.

Thank you.

More details:

I want to use a database as a high level communication channel among executables, instead of using the file system or shared memory.

File system pros: persistent storage (I do not care about it in this project).
File system cons: slow; hardware stress (both for hard disk and SSD).

Shared memory pros: speed.
Shared memory cons: data size; non persistent storage; low level access.

RAM Database pros: speed; high level, query-like access.
RAM Database cons: data size; non persistent storage (not a problem in my case).


texts: How to get the name of a variable instead of its value in plpgsql?

texts:
I write a lot of debugging code in plpgsql along the lines:

RAISE NOTICE 'var1 = %', var1;

(there can be more than one variable to print out).

Is it possible to avoid having to repeat the variable var1 every time by writing a PostgreSQL function (say myprint) so that

SELECT myprint(var1);

will generate the same output as RAISE NOTICE 'var1 = %', var1;?

The problem is that I know how to get the value of var1 in the function but I don't know how to get the name var1. Is there some call-stack or context magic in plpgsql that can provide the name of the variable passed to myprint?

(This is with PostgreSQL 12.1 or later)


texts:
When running the command 'sudo service mysql start' this is the output.
[FAIL] Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

Here are the steps taken to troubleshoot already:
1. Checked permissions for /var/run/mysqld, it's owned by mysql but the user is root.
2. The syslog file has been checked and gives the following,

ERROR 2002 (HY000): 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

After checking the my.cnf file the path's for the socket matched.

When running which mysql this is the output /usr/bin/mysql

Monit has recently been installed on the server as well as the port was changed to port 99.

Everything has been running fine up until now. (About a week and a half after said changes were made.)

Linode has been rebooted a couple of times to troubleshoot memory issues but to no avail. Here is some output from various commands

            user@hostname:~$ df -h
            Filesystem      Size  Used Avail Use% Mounted on
            /dev/root        24G  7.3G   16G  32% /
            devtmpfs        494M     0  494M   0% /dev
            tmpfs           100M  208K   99M   1% /run
            tmpfs           5.0M     0  5.0M   0% /run/lock
            tmpfs           250M     0  250M   0% /run/shm

            user@hostname:~$ df -i
            Filesystem      Inodes  IUsed   IFree IUse% Mounted on
            /dev/root      1556480 177140 1379340   12% /
            devtmpfs        126432   1379  125053    2% /dev
            tmpfs           126898    780  126118    1% /run
            tmpfs           126898      2  126896    1% /run/lock
            tmpfs           126898      2  126896    1% /run/shm

            user@hostname:~$ free -m
                         total       used       free     shared    buffers     cached
            Mem:           991        501        489          0         52        237
            -/+ buffers/cache:        211        779
            Swap:          255          0        255

We have ruled out the possibility of a failed mysql process that is still running with the following commands

            user@hostname:/var/run/mysqld$ ps aux | grep mysql
            user  28834  0.0  0.2   7856  2068 pts/0    S+   13:00   0:00 grep mysql
            user@hostname:/var/run/mysqld$ kill
            kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
            user@hostname:/var/run/mysqld$ kill mysql
            -bash: kill: mysql: arguments must be process or job IDs
            user@hostname:/var/run/mysqld$ kill 28834
            -bash: kill: (28834) - No such process
            user@hostname:/var/run/mysqld$ kill 7856
            -bash: kill: (7856) - No such process
            user@hostname:/var/run/mysqld$ kill 2068
            -bash: kill: (2068) - No such process
            user@hostname:/var/run/mysqld$ man kill
            user@hostname:/var/run/mysqld$ kill -l
             1) SIGHUP   2) SIGINT   3) SIGQUIT  4) SIGILL   5) SIGTRAP
             6) SIGABRT  7) SIGBUS   8) SIGFPE   9) SIGKILL 10) SIGUSR1
            11) SIGSEGV 12) SIGUSR2 13) SIGPIPE 14) SIGALRM 15) SIGTERM
            16) SIGSTKFLT   17) SIGCHLD 18) SIGCONT 19) SIGSTOP 20) SIGTSTP
            21) SIGTTIN 22) SIGTTOU 23) SIGURG  24) SIGXCPU 25) SIGXFSZ
            26) SIGVTALRM   27) SIGPROF 28) SIGWINCH    29) SIGIO   30) SIGPWR
            31) SIGSYS  34) SIGRTMIN    35) SIGRTMIN+1  36) SIGRTMIN+2  37) SIGRTMIN+3
            38) SIGRTMIN+4  39) SIGRTMIN+5  40) SIGRTMIN+6  41) SIGRTMIN+7  42) SIGRTMIN+8
            43) SIGRTMIN+9  44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12 47) SIGRTMIN+13
            48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14 51) SIGRTMAX-13 52) SIGRTMAX-12
            53) SIGRTMAX-11 54) SIGRTMAX-10 55) SIGRTMAX-9  56) SIGRTMAX-8  57) SIGRTMAX-7
            58) SIGRTMAX-6  59) SIGRTMAX-5  60) SIGRTMAX-4  61) SIGRTMAX-3  62) SIGRTMAX-2
            63) SIGRTMAX-1  64) SIGRTMAX


texts:
I need to add index on a JSON column in my table as per documentation I am executing a query similar to the one below.

CREATE INDEX idxgin ON api USING gin (jdoc);

I am getting following error:

  ERROR:  data type json has no default operator class for access method "gin"
  HINT:  You must specify an operator class for the index or define a default operator class for the data type.

Here is the link for the documentation.


texts: Find procedures that haven't been called in  days

texts:
We are deleting old stored procedures and tables.

How can I know what procedures haven't been called recently?

dm_exec_procedure_stats and dm_exec_query_stats aren't reliable, since they only return procedures in the plan cache.


texts:
I was wondering if SQL server 2008 R2 has any default security if you're using it via ODBC or SQL Server Management Studio?

I was reading about SSL here
https://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

The issue is forcing encryption right now will break a lot of things and would take a while. I was wondering if there is anything that's set up to encrypt by default to buy some time while we figure ssl out.

We have hundreds of users and some people designed their own database to pull from this one. It would be a huge task with limited amount of time. I have a trusted cert it's just force encryption plan fell out of place. So I guess right now nothing's encrypted - am just trying to see if anything exist by default.


texts: Setting up the Listeners for Oracle Dataguard

texts:
I setup up a Oracle 12c Dataguard configuration. To build the standby database I used the RMAN DUPLICATE command, which requires a static listener configuration.

tnsname.ora on HOSTNAMEA and HOSTNAMEB are the same:

BR =   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEA)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = BR)
    )   )

BR_STBY =   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = BR)
    )   )

listener.ora HOSTNAMEA:

    SID_LIST_LISTENER =
        (SID_DESC =
          (GLOBAL_DBNAME = BR_DGMGRL)
          (ORACLE_HOME = /opt/oracle/product/12200/dbhome_1)
          (SID_NAME = BR)
        )

    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEA)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
  )

listener.ora HOSTNAMEB:

SID_LIST_LISTENER =
    (SID_DESC =
      (GLOBAL_DBNAME = BR_STDBY_DGMGRL)
      (ORACLE_HOME = /opt/oracle/product/12200/dbhome_1)
      (SID_NAME = BR)
    )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEB)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

This configuration is using a static listener configuration. How does the application know the database has for example switched from HOSTNAMEA to HOSTNAMEB? Should I use dynamic listener registration? how should the configuration look like?


texts:
Trying to follow the chase algorithm as I understand it,

If two rows agree in the left side of a functional dependency (FD for brevity), make
their right sides agree too.
Always replace a subscripted symbol by the
corresponding unsubscripted one, if possible.
If we ever get an unsubscripted row, we know
any tuple in the project-join is in the original
(the join is lossless).
Otherwise, the final tableau is a counter
example

So I tried to perform the chase algorithm on an example schema. Given R (A, B, C, D, E) decomposed into relations {A, B, C}, {B, C, D}, {A, C, E} and FDs, A → D, CD → E and E → D

Beginning the chase test

+-----+-----+---+-----+-----+
| a   | b   | c | d   | e   |
+-----+-----+---+-----+-----+
| a   | b   | c | d_1 | e_1 |
+-----+-----+---+-----+-----+
| a_2 | b   | c | d   | e_2 |
+-----+-----+---+-----+-----+
| a   | b_3 | c | d_3 | e   |
+-----+-----+---+-----+-----+

And ending

+-----+-----+---+-----+-----+
| a   | b   | c | d   | e   |
+-----+-----+---+-----+-----+
| a   | b   | c | d_1 | e   |
+-----+-----+---+-----+-----+
| a_2 | b   | c | d   | e_2 |
+-----+-----+---+-----+-----+
| a   | b_3 | c | d_1 | e   |
+-----+-----+---+-----+-----+

So the final tableau is a counter example and this isn't lossless.


texts:
I want to upgrade SQL server from 2005 to 2012 (I'm thinking of in-place upgrade).
Today I have several SSIS packages that works with other servers.
The SQL version in those servers is mainly 2005.

My question is what happen to the SSIS when i'm upgrading?

Do i need to upgrade the SSIS as well?

Could the SSIS work without me upgrading it?

Are there any compatibility issues with the integration server? can 2012 work with 2005 (and the opposite - because replication have compatibility problems)

I'm not a big fan of the SSIS so my goal is to touch it as less as i can so it will keep on working.


texts:
I want to enable full text search in sql server the feature already checked in the installation but in sql when i make this query

select serverproperty('IsFullTextInstalled')

The value 0 should be 1 ? How i can fix this issue?

from SQL server configuration manager it's Running ,
checked

and from installation it's

Check if i have enough permissions to run these procedures.


texts: SSAS cube processing failed: File system error: Insufficient system resources exist to complete the requested service

texts:
Have a mysterious problem with SSAS, may be related to SSAS itself or to PKWare Smartcrypt TDE that we have installed on the server. PKWare should encrypt the data for all users outside the domain, but both myself and SQL/SSAS service account are in the domain.
The server is on Azure (M32ms, 32 vCPU(s), 875 GB RAM). It runs a SQL Server 2016 Enterprise and SSAS multidimensional. There is sufficient space (about 1-10TB) on every DB drive.
I am processing a cube. Source DB is in SQL on the same server. The underlying fact table in SQL has 3.9 million rows. During processing, CPU and RAM usages stay low, space on SSAS drives is still sufficient.
10 minutes into the processing, I receive an error:

File system error: The following error occurred during a file
operation:  Insufficient system resources exist to complete the
requested service.

What file? What service? What system resources?
I started investigation:

Checked \Program Files\Microsoft SQL Server\MSAS13\OLAP\Log\msmdsrv.log. The file looks malformed: there is a signature with word SMARTCRYPT. And further at the end of the file, there is actually UTF16 text from "normal" SSAS log. Seems like the file msmdsrv.log had been encrypted with PKWare Smartcrypt TDE, but at some point the encryption got disabled, the signature remained in the file, and SSAS continued appending to it. Anyway, there were no records from the timeframe when the error occurred.

Checked Windows Event log, but found no entries from the MSOLAP instance.
Played around with SSAS memory settings (who knows, maybe RAM was a bottleneck), but same result regardless of MemoryLimits.

Now I am out of ideas.
Can you please point me in the right direction? I would like to know how else I could capture what happens to the system when the cube is being processed.


texts: How to find minimal cover when attributes have equal chance to be not necessary?

texts:
I have the schema which consists of 6 attributes: t,s,r,w,e,g.

For the schema I have the following functional dependencies:

s,w --> r;t,w,g --> r,e;s,r --> t,g; e -->w; g --> t,s;r,w,e --> s

I'm not quite sure how to go about the dependencies where one side has 3 attributes. For example, for t,w,g --> r,e should I check for all possible combinations of t,w,g (2^3-2=6 cases to check in total)?

For example I choose the case where I want to check if t is not necessary in t,w,g --> r,e.
Indeed the closure of {w,g} is {w,g,t,s,r,e} which contains {r,e}.

If I continue to look for unnecessary attributes in w,g --> r,e then I have:

w,g --> r OR w,g --> e. So can I choose a random result here or there's some rule on whether r is not necessary or e?


texts: Indexing a string by a subset of its characters (i.e.: the Kanji ones)

texts:
I have a database in PostgreSQL modelling a Japanese->English dictionary. The tables I am interested in are listed as follows, where bold represents PK and italic FK:

entries(entry_id int, keyword varchar, reading varchar, meaning varchar)
entries_kanji(kanji char, entry_id int) where entry_id references entries.entry_id

The idea is that entries.keyword is a string containing several kanji. For each of these kanji, there will be an appropriate row in entries_kanji linking back to the entry. This allows me to quickly find all the entries whose keywords contain specific kanji, and by computing the intersection of several queries on this table, identify a word by its kanji alone.

At present, I am using a script external to the database to generate the entries_kanji table by iterating over every row in entries, extracting the kanji from each keyword and inserting a row into entries_kanji. While this works, it is inconvenient to have to run this script every time a new entry is added to the dictionary.

My question is: is there a way to achieve the above using an index alone? I know PostgreSQL allows for indexes to be created based on expressions, but I don't know how to achieve what I described above using this. Or is there some other feature of PostgreSQL that will allow me to do away with the script?


texts:
I am passing a parameter to a dynamic sql string and have the parameter properly encompassed in ' marks to work in my dynamic sql.  However, now I am needing to use the value in a diff location in my code, and want to remove all the ' marks.  I tried the Replace() function, but I must be using incorrect syntax as it still leaves the string with single quotes on each side.  Take the sample DDL Below

DECLARE @Param1 nvarchar(500) = N'''Blue Bell Ice Cream''';
PRINT REPLACE(@Param1, '''''', '')

Prints this

  'Blue Bell Ice Cream'

My desired result to be returned is

  Blue Bell Ice Cream

How should I write the REPLACE() function in order to remove all single quotes?


texts:
I am getting the following syntax error when running the below query.

  Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM shorewarecdrarchive.connect         LEFT JOIN     shorewarecdrarchive.call ' at line 28

Line 28 is the FROM part of the query.

SELECT
    call.Extension,
    -- agentactivity.AgentDN,
    -- agentactivity.AgentFirstName,
    -- agentactivity.AgentLastName,
    -- agentactivity.WorkgroupDN,
    -- agentactivity.WorkgroupName,
    call.DialedNumber,
    connect.PartyID,
    call.CallerID,
    connect.PartyIDName,
    connect.PartyIDLastName,
    -- agentactivity.StartTimeStamp,
    call.StartTime,
    date(call.StartTime),
    time(call.StartTime),
    hour(call.StartTime),
    call.EndTime,
    -- agentactivity.EndTimeStamp,
    call.CallType,
    call.ID,
    connect.PartyType,
    connect.ConnectReason,
    connect.TalkTimeSeconds,
    connect.duration,
    call.duration,
    -- agentactivity.State
FROM shorewarecdrarchive.connect
LEFT JOIN
    shorewarecdrarchive.call ON connect.CallTableID = call.ID
-- LEFT JOIN
-- shorewarecdrarchive.agentactivity ON call.Extension = agentactivity.AgentDN
WHERE
-- StartTimestamp >= '2016-12-05 08:20:00'
-- AND StartTimestamp < '2016-12-05 08:40:00'
-- AND PartyIDName NOT IN ('Overhead Paging','Voice Mail','Voice Mail Login','Door','Door Intercom','Main Line Hunt Group','Main Line','Sales','ACT','Auto-Attendant','Night Bell','Backup Auto-Attendant','Dispatch','North', 'North Lunch')
-- AND WorkgroupName NOT IN ('Dispatch' , 'Main Line')
-- AND CallType between 1 and 3
-- Calltype 1 = Extension to extension call.
-- CallType 2 = A trunk is the originating party. (Inbound calls)
-- Calltype 3 = An extension is originating and a trunk is called. (Outbound calls)
-- CallType 4 = A call originating from a trunk and going to a trunk. (Tie trunks)
-- AND agentactivity.WorkgroupName = 'Sales' or'Pre-sales' or 'QFC Orders'
-- and call.id = '402300'
call.ID = '612916'
order by call.ID ASC;

Can anyone take a look and let me know what I am not seeing; I am pretty sure it's something simple, though I am having one of those 'to close to the painting' situations.


texts: Implementation of a many-to-many relationship with total participation constraints in SQL

texts:
How should I implement in SQL the scenario depicted in the following Entity-Relationship diagram?

As it is shown, every A entity type occurrence must be related to at least one B counterpart (indicated by the double connecting lines), and vice versa. I know I should create the three tables that follow:

    CREATE TABLE A
    (
        a INT NOT NULL,
        CONSTRAINT A_PK PRIMARY KEY (a)
    );

    CREATE TABLE B
    (
        b INT NOT NULL,
        CONSTRAINT B_PK PRIMARY KEY (b)
    );

    CREATE TABLE R
    (
        a INT NOT NULL,
        b INT NOT NULL,
        CONSTRAINT R_PK      PRIMARY KEY (a, b),
        CONSTRAINT R_to_A_FK FOREIGN KEY (a)
            REFERENCES A (a),
        CONSTRAINT R_to_B_FK FOREIGN KEY (b)
            REFERENCES B (b)
    );

But, what about the implementation of the total participation constraints (i.e., enforcing that each instance of either A or B is involved in a minimum of one relationship occurrence with the other)?


texts: Possible to get equivalent MySQL bin log position on another server?

texts:
Let's say we have three MySQL servers, mysql1, mysql2 and mysql3. Replication is set up such that mysql3 is the slave of mysql2, and mysql2 is the slave of mysql1.

At some point I take a dump of mysql2 and record the binlog file and position of it by running show master status;. Is there a way to find the equivalent binlog file and position on mysql1 - for example so that I could load up the dump and continue replicating from mysql1 instead of mysql2?


texts: How do I combine multiple rows when using select that are essentially the same data?

texts:
I have a database where there's a description text stored but the description text itself is broken up into separate rows based on the text itself.

For example:

|ID  |Row  |Text                  |
-----------------------------------
|1   |1    |The cat went to the st|
|1   |2    |ore and bought some ca|
|1   |3    |tnip.                 |
|2   |1    |The dog didn't like to|
|2   |2    |drink water.          |
|3   |1    |It's cold outside     |

How would I write a select query so that all of the rows in the Text column are combined into a single result based on the ID column and ordered according to the Row column?


texts: PSQL cte delete seem have have ambiguity when select same column from delete table

texts:
Both these two sql can be execute , the first one delete all , the second one is logic right.
the first one
with d0 as (
    delete from seller_create_request
        where seller_id in
              (
                  select id
                  from seller
                  where email in
                        ('xx@xx.com')
              )
        returning seller_create_request.seller_id
)
delete from seller
where id in (select id from d0)
returning  seller.id;

the second one
with d0 as (
    delete from seller_create_request
        where seller_id in
              (
                  select id
                  from seller
                  where email in
                        ('xx@xx.com')
              )
        returning seller_create_request.seller_id
)
delete from seller
where id in (select d0.seller_id from d0)
returning  seller.id
;

The first one select id from d0 it seem equal to select id from seller.
My original intention is select seller_id from d0 but have spell wrong. It don't cause
column id does not exist since it from d0.
So I am strange why psql has this behavior, what I am missing from simple understanding?


texts: Transactional Data Replication, SQL Server version compatibility

texts:
I've been following the Microsoft Tutorial: Configure replication between two fully connected servers (transactional). Everything has gone "smooth", however the 'New Subscription Wizard' is stopping me from creating a subscriber due to compatibility issues between the publisher and subscriber.

Error Message

  The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication. (New Subscription Wizard)

Versions

The publisher and distributor are on:

  Microsoft SQL Server 2014 - 12.0.4100.1 (X64)   Apr 20 2015 17:29:27   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

The subscriber is on:

  Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

Transactional Replication

The type of data replication I'm attempting to configure is Transactional and according to the Microsoft documentation for Replication Backward Compatibility, this should be working fine, but for some reason, the wizard is not playing ball.

Any help would be appreciated.


texts: SQL database rejoined to new SQL Server 2008 R2. Users are still unable to connect

texts:
The below error appears when our users attempt to connect to our database following a complete rebuild of our SQL server and restore of the MDL file:

We have created user accounts on the new SQL Server instance which match the ones we had before the server needed to be rebuilt, yet something still seems to be preventing users from connecting to the database... they don't even get the opportunity to enter their credentials before the error appears.

Does anyone have any ideas what could be happening?

As always, any guidance will be appreciated!


texts: SQL Server 2012 Failed to launch local ScenarioEngine.exe

texts:
I have been trying install SQL Server 2012 Developer Edition for quite some time now..
I always get "stuck" at Install Setup Files, the install wizard just hides,

Now, i reviewed the log file for setup, which is: SqlSetup_1

There,i found these errors

Attempting to launch process (null)SQLServer2012\x86\ScenarioEngine.exe
Error: Failed to launch process
Error: Failed to launch local ScenarioEngine.exe: 0x80070003

The full SqlSetup_1 log:

http://pastebin.com/Ehvi1Nvm

any suggestions ?


texts: Are SQL Server statistics stored in database or buffer pool?

texts: Just wondering are statistics kept in the database but not in the memory? If I backup/restore the database from a prod server to a development server, would it keep the same statistics so that execution plans won't be much different if I do it on the development server?


texts:
I believe there are similar question about this. But I need more explanation about my plan.

I have an 11g Oracle database running with one listener on port 1522. And currently using one interface (one IP for the server). Some client using another segment of IP, which always use router to communicate with server. Every morning, my router get heavy load because some different segment user create connection to database's IP.

From this situation, I want to add new interface for the server and assigned same IP segment for those client. From netstat I assume that no need extra configuration from oracle side. Here is the netstat result.

I will only add the IP address from OS side. And change the tnsnames.ora of client to connect to new IP address of server.

Is my scenario will work well? Or will I need to reconfigure the listener and database to work with my situation?


texts: Database structures for similar profiles

texts:
Please bare me if my question is too blunt

I am working on a social network where I need to show similar profiles to users

Every profiles has 40 parameters to match with other profiles.

I want to fetch results in a manner that users with maximum similarity with current logged-in user i.e if user has exact same values to other users then they must be shown at top.

After that users with lesser similarities should be shown i.e 39 similarities

I am planning to use 2 tables

[user][table] ->  id, name, email, password, username etc

[user_values][table] -> It will have 40 columns for all 40 parameters and user_id

Can this be done using single query ?

So we will have 40 values of current login user. Whenever they reach a matches page we should be able to hit a query which will fetch users in order to descending match of columns.

I can change database design/structure if needed.


texts: JDBC Statement vs. Prepared Statement SQL Injection

texts:
as far as I understand a "normal" JDBC Statement which is created via

conn.createStatement()

is prone to SQL-Injection. A prepared statement protects from such attacks. So why/when should I use a normal statement and when a prepared statement.

Or the other way around: If Statement is attackable why not always use prepated statements?

Thanks and regards


texts: How to store data in a database without tables?

texts:
All I learned in school was SQL which saves data to tables.
Right now I am working on a project where data is stored in XML-files. Additionally every XML contains a reference to visual files (JPEG).

The XML itself contains over one thousand coordinate points, plus additional information on the data.

In my opinion it would make no sense to store this information in tables. Besides I couldn't store JPEG-files with SQL either.

What would be appropriate solution, or is there an error in reasoning on my side?

As you can see I am pretty new to databases. So any constructive suggestions, links and advice is welcome.