texts: Replicating different set of tables to subscribers

texts:
I have transaction pull replication to several subscribers. Publisher has 2008 and distributer and subscribers have 2014.

I need one of the subscribers to replicate more tables.
Is it possible to have different set of tables in subscribers?

Couldn't find such a scenario.

Thanks in advance

Liya


texts:
working with Two scenarios
CREATE TABLE [dbo].[Test](
    [LineID] [int] NULL,
    [BusinessUnit] [nvarchar](50) NULL,
    [Value] [nvarchar](50) NULL,
    [Amount] [money] NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.Test
SELECT 1, 'ABC', 'Apple', 20.00 UNION
SELECT 2, 'DEF', 'Apple', 40.00 UNION
SELECT 3, 'ABC', 'Apple', -20.00 UNION
SELECT 4, 'DEF', 'Apple', -40.00

above scenario is Invalid -- and i need to get line 1, 2, 3, 4 as output because the amount total is not Zero for same business unit as to follow the sequence.
ABC should adjust to zero (Sum of Amount) in the order of InvoiceLine, before new business unit line being added.
So DEF(Line 2) started before ABC business unit's lines turned to zero.

INSERT INTO dbo.Test
SELECT 1, 'ABC', 'Apple', 20.00 UNION
SELECT 2, 'ABC', 'Apple', -20.00 UNION
SELECT 3, 'DEF', 'Apple', 40.00 UNION
SELECT 4, 'DEF', 'Apple', -40.00

this is Valid, and the code should not return any value as this is valid entry.
DEF (line 3) started with ABC's amount total equals to zero.


texts:
My ASP.net application uses ODBC(64 bit) to connect to DB2 Database(9.5.3). I am using 64 bit IBM DB2 Client 10.5. on Windows Server 2008 R2. Connection pool is turned on.

It works fine(immediate connectivity) for most of the time, but occasionally too much time is consumed to establish connectivity. By too much time I mean to say 10 to 11 minutes. No error is reported. No issue with database, as it can be accessed from other servers at same timestamp.

All database request issued during this time via DB2 Client are kept on hold and then once connectivity is established, all are executed immediately.

When the issue is going on, I tried to connect to database through Windows server CMD and that too waits for around 10 mins. No error is reported. Network team says they dont see network traffic from Windows server to Database server when issue is going on. And no errors from network side. Which means DB2 client is not making connectivity request.

What could be causing delay in connectivity? This is not a consistent issue. It automatically resolves after around 10 mins. Is there any issue with DB2 driver ? Was any resource withheld during that time? I am closing all connections properly.


texts: Does our application work with sql-server AlwaysOn?

texts:
This is the question a client of ours is asking. For now I have given a generic response that we will support this however they should provide us with a test environment.

Our application is using .net and we allow clients to configure our server service with the database instance name and username/password (we use sql-server authentication).

From what I understand all they will need to do is configure our application with the cluster instance name instead of the hostname of the database server. Reading the Prerequisites that Microsoft provide it seems like it will be completely transparent for us.

Is this correct? Are there other changes we need to make to our connection string? Any other gotchas?


texts: Status shows InnoDB: 128 rollback segment(s) are active waiting for purge to start

texts:
MySQL process was killed due to memory issues.  When start command was issued, server started and is working but a status command shows the following:

InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.3...514901
Plugin 'FEEDBACK' is disabled.
Server socket created on IP: '::'.
/usr/sbin/mysqld: ready for connections.

Is this something that needs to be addressed?


texts: Use plpgsql to count records selected for output AND output those records

texts:
My query SELECTs 1 row from table1, which has a number of fields plus one very long text field. It JOINs with a second table2, that may have many matching corresponding rows.
I would like to be able to spread the long text field, from table1, out over multiple lines (the number determined by the matching rows in table2).

In the table above there is one row from Table1 where the key is table1_key = 'DU' and year = '2017'.  Then there are four rows from Table2 with matching keys that include the programme_code and course_code. The field long_text_field, which I want to spread over multiple lines, comes from Table1.

Is it possible to extract a total row count for records that have been selected for output and still output the rows?  Meaning that I want to see a count of 4 from the above and be able to work with this number in the displayed output.  So that the result looks something like this:


texts: db2: configurations covered by "create db autoconfigure"

texts:
I am new to configuring memory in db2. I want to create a database using this command:
db2 CREATE DB TEST USING CODESET UTF-8 TERRITORY US AUTOCONFIGURE USING mem_percent 80 APPLY DB ONLY
According to IBM's Knowledge Center:

...the AUTOCONFIGURE command which will generate values for parameters based on your responses to questions about workload characteristics.
Some configuration parameters can be set to AUTOMATIC, allowing the database manager to automatically manage these parameters to reflect the current resource requirements. To turn off the AUTOMATIC setting of a configuration parameter while maintaining the current internal setting, use the MANUAL keyword with the UPDATE DATABASE CONFIGURATION command.

Is there a resource online that will tell me the values that AUTOCONFIGURE based on my current database manager configurations (assuming that I have not modified anything related to memory)? I want to know if not setting certain memory-related configurations will affect the performance of my database. For example, if I don't set configurations like pckcachesz to automatic, what is the default value of it?


texts: SQL Server Service Account - Things to test post change (from Local System to Network Service)

texts:
We needed to change the SQL Service Account for the SQL Server Database Engine & Agent Services from Local System as it has extensive predigests.

What are the things that we should do to test within the SQL Server database itself to ensure that there are no issues / permission problems with this change?

Note the service has been changed, and seems to be running fine Both Agent Services / SQL Server Engine.

The two versions we are implementing this in are SQL Server 2008 R2 and SQL Server 2012.


texts: Proper use of database functions for business logic

texts:
I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry.

These functions are called by an application in java that has almost no business logic in the code.

This database structure is used for different clients and some of these functions have small adaptations per customer with comment in the function body.

This approach where most of the logic is in the database is correct?
If you update a function in a database and we must pass it to each other how we can easily distinguish which function has changes To don’t delete or replace a change in client database by error?


texts: Composite Foreign key to single primary key

texts:
How do I create a composite foreign key to a table with a single primary key? For example suppose I have the following tables:

CREATE TABLE Branches (
    BranchId UniqueIdentifier DEFAULT NewSequentialID(),
    CONSTRAINT pk_BranchId PRIMARY KEY (BranchId)
);

CREATE TABLE Expectations (
    ExpectationId UniqueIdentifier DEFAULT NewSequentialID(),
    BranchId UniqueIdentifier NOT NULL,

    CONSTRAINT fk_Expectations_BranchId FOREIGN KEY (BranchId) REFERENCES Branches(BranchId),
    CONSTRAINT pk_ExpectationId PRIMARY KEY (ExpectationId)
);

CREATE TABLE ClassSections (
    ClassId UniqueIdentifier NOT NULL,
    BranchId UniqueIdentifier NOT NULL,

    CONSTRAINT fk_ClassSections_ClassId FOREIGN KEY (ClassId) REFERENCES Classes(ClassId),
    CONSTRAINT fk_ClassSections_BranchId FOREIGN KEY (BranchId) REFERENCES Branches(BranchId),
    CONSTRAINT pk_ClassSections PRIMARY KEY (ClassID, BranchId)
);

CREATE TABLE ClassExpectations (
    ClassId UniqueIdentifier NOT NULL,
    BranchId UniqueIdentifier NOT NULL,
    ExpectationId UniqueIdentifier NOT NULL,

    CONSTRAINT fk_ClassExpectations_ClassId FOREIGN KEY (ClassId) REFERENCES Classes(ClassId),
    CONSTRAINT fk_ClassExpectations_BranchId FOREIGN KEY (BranchId) REFERENCES Branches(BranchId),
    CONSTRAINT fk_ClassExpectations_ExpectationId FOREIGN KEY (ExpectationId) REFERENCES Expectations(ExpectationId),
    CONSTRAINT fk_ClassExpectations_ExpectationIdBranchId FOREIGN KEY (ExpectationId, BranchId) REFERENCES Expectations(ExpectationId, BranchId),
    CONSTRAINT pk_ClassDueDates PRIMARY KEY (ClassId, ExpectationId)
);

I'm trying to add the following constraint, but it won't allow me:

ALTER TABLE ClassExpectations ADD CONSTRAINT fk_ClassExpectations_ExpectationIdBranchId FOREIGN KEY (ExpectationId, BranchId) REFERENCES Expectations(ExpectationId, BranchId);

I keep getting the error:

  There are no primary or candidate keys in the referenced table
  'Expectations' that match the referencing column list in the foreign
  key

How else do I ensure that an ExpectationId is never added to ClassExpectations without its corresponding BranchId (from the Expectations table)?


texts: COUNT number of column values with INNER JOIN

texts:
I'm new to PostgreSQL and I am making a Report. This is my SQL Fiddle:

http://sqlfiddle.com/#!15/6ce465/5

So basically I have two tables, responses and questions. The structure is

responses
->id
->question_id
->response

questions
->id
->question
->costperlead

For the column response there can only be 3 values: Yes, No, Possibly.

And my report should have the columns

question_id
# of Yes Responses
# of No Responses
# of Possibly Responses
Revenue

where

# of Yes Responses is the count of all Yes values in the response column,
# of No Responses is the count of all No values in the response column,
# of Possibly Responses is the count of all Possibly values in the response column,
Revenue is costperlead * ("# of Yes Responses" + "# of Possibly Responses").

I don't know how to construct the query. I'm new plus I came from MySQL, so some things will be different in PostgreSQL. In my SQL Fiddle sample most responses are Yes and Null – it's OK. Eventually, there will be Possibly and No.

So far I have only

SELECT a.question_id
FROM responses a INNER JOIN questions b ON a.question_id = b.id
WHERE a.created_at = '2015-07-17'
GROUP BY a.question_id ;


texts:
Our environment is currently DB2 9.7 Fix Pack 4 on AIX.

I am in the processing of designing and coding some SQL PL stored procedures responsible for archiving data into an archive schema and then purging out old/un-needed data from the main schema.

I created some user defined types (UDTs), specifically row types and cursor types. I am looking at dividing my logic into three stored procedures. Proc A would be used to purge a particular set of data related to my main EVENT table (ie, the EVENT and everything underneath it). I don't really want others to be able to call this. I just want to modularize the logic. Proc B would archive data from EVENT (and other tables) that the end user wants and then call into Proc A to clean up the original data. Proc C would be used to look for EVENTs that could be discarded and then call into Proc A for actual work.

Now I could create all of these independent of each other, and then deploy them that way. Or....I could use a module. From what I understand a module allows me to associate related functionality (types, stored procs, functions, etc.) together. It also allows me to decide which objects I wish to be accessible external to the module, ie, can be called/used directly, versus which ones can only be called internal to the module (such as the case above, where I could hide stored Proc A, but allow B and C to call it).

This sounds incredibly intriguing. But it has its downsides too. I'm guessing it isn't part of any SQL standard, but something specific to IBM DB2. Even as such, you cannot directly interact with modules via Data Studio or Control Center (other than CALLing the exteral facing procedures/functions). And you cannot create them directly in IBM's InfoSphere Data Architect either. So I would have to maintain the DDL's independently.

So the module concept sounds handy. I can hide some things and choose what can be called to make sure someone doesn't misuse anything.

But, given it doesn't seem to be supported by IBM tooling outside of command line....I was wondering....who all uses modules (specifically in DB2 LUW)? Does anyone? Does it makes sense? If not, is there another way I could accomplish my thought of modularizing code while making the "modules" inaccessible for direct calling?


texts:
I am creating app with ExpressJS and want to use MySQL with password, but I have problem with connection string

mysql://user@localhost:3306/db

in this string where to specify password to connect to db

Please help me


texts:
I'm trying to use letsencrypt to generate certs in order to encrypt communication between a remote client and my postgresql database server. I'm not sure how to get started on setting this up. What kind of certs do I need for the client?  What cert files should I store on the server?
I've gone through this documentation as reference but it's still unclear as to which certificates go where?
Thanks in advance!

Edit: Some clarification since this question was put on hold: I was unsure of what to expect when trying to use letsencrypt. I understand the basics of ssl/tls. But I needed some clarification about whether the certs generated on the server need corresponding certs on the client. The accepted answer has since pointed me in the right direction and the linked postgresql documentation has helped as well.


texts: What is the recommended way to autostart a Postgres cluster using a nonstandard data directory?

texts:
I am running PostgreSQL 9.5 on Linux Mint 18.

At installation, apt creates a cluster in /var/lib/postgresql/9.5/main.
Environment settings (postgresql.conf, pg_hba.conf, pg_ident.conf) are stored in /etc/postgresql/9.5/main.
postgresql.conf sets data directory to /var/lib/postgresql/9.5/main.

I have a root partition that is deliberately small, and want to have my cluster on a larger data partition. I ran initdb in /data/pg_data. I am able to start and stop this cluster manually using pg_ctl. My question is, how do I start Postgres using this cluster at system startup?

I read 17.3 Starting the Database Server in the Postgres docs. That page describes (a) setting the PGDATA environment variable, or (b) having the server start the postgres daemon at boot time.

Using PGDATA

I tried to accomplish this by adding the following line to /etc/environment:

PGDATA="/data/pg_data"

I then restarted. Postgres started, but is still using default data directory. echo $PGDATA indicates environment variable was set, and if I stop running cluster, pg_ctl start without data directory specified does start desired cluster. But it's not handling it at boot time.

Using init scripts

I can't figure this one out.

The docs say:

  Different systems have different conventions for starting up daemons at boot time. Many systems have a file /etc/rc.local or /etc/rc.d/rc.local. Others use init.d or rc.d directories.

  On Linux systems either add

/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data

  to /etc/rc.d/rc.local or /etc/rc.local or look at the file contrib/start-scripts/linux in the PostgreSQL source distribution.

Linux Mint uses init.d folder. I found a postgresql file in there and opened it. It is a 60 line bash script. The first few lines are:

# Setting environment variables for the postmaster here does not work; please
# set them in /etc/postgresql/<version>/<cluster>/environment instead.

[ -r /usr/share/postgresql-common/init.d-functions ] || exit 0

. /usr/share/postgresql-common/init.d-functions

So, first I look in /etc/postgresql/9.5/main/environment. That file is empty except for comments. The first line says # environment variables for postmaster process. Is something supposed to go in here that points to my desired data directory?

The file includes /usr/share/postgresql-common/init.d-functions. That is a 112 line file that clearly points to /etc/postgresql/<version number> and attempts to start all clusters that it finds there. It also clearly references postgresql.conf. So it seems that this is how the default cluster is being started, but it is not clear to me that this can or should be edited.


texts: How to select the set of last non-NULL values per column over a group?

texts:
I am using SQL Server 2016 and the data I am consuming has the following form.

CREATE TABLE #tab (cat CHAR(1), t CHAR(2), val1 INT, val2 CHAR(1));

INSERT INTO #tab VALUES
    ('A','Q1',2,NULL),('A','Q2',NULL,'P'),('A','Q3',1,NULL),('A','Q3',NULL,NULL),
    ('B','Q1',5,NULL),('B','Q2',NULL,'P'),('B','Q3',NULL,'C'),('B','Q3',10,NULL);

SELECT *
FROM    #tab;

I would like a obtain the last non-null values over columns val1 and val2 grouped by cat and ordered by t.  The result I am seeking is

cat  val1 val2
A    1    P
B    10   C

The closest I have come is using LAST_VALUE while ignoring the ORDER BY which is not going to work since I need the ordered last non-null value.

SELECT DISTINCT
        cat,
        LAST_VALUE(val1) OVER(PARTITION BY cat ORDER BY (SELECT NULL) ) AS val1,
        LAST_VALUE(val2) OVER(PARTITION BY cat ORDER BY (SELECT NULL) ) AS val2
FROM    #tab

cat  val1 val2
A    NULL NULL
B    10   NULL

The actual table has more columns for cat (date and string columns) and more val columns (date, string, and number columns) to select the last non-null value.

Any ideas how to make this selection.


texts:
The PostgresSQL wiki suggest a rule for find the optimal amount of DB connection pool size.

amount = ((core_count * 2) + effective_spindle_count)

But it does not explain what the effective spindle count is and how can I find this value.


texts: Restore PostgreSQL database (or db names) to new version, from files?

texts:
Scenario in short:

a development laptop broke
old HDD is still readable
new laptop has PostgreSQL 9.4 instead of 9.1
both laptops use a flavor of Ubuntu Linux

Question 1: Is my understanding correct that in order to restore the old data, I would need a server with the same major+minor version as the one that wrote the old data directory? Since no 9.1 packages are available for my distribution, I would have to compile a v9.1.x server from source, copy over the old data, start the server and perform a normal pg_dump, which could then be restored to the new cluster?

Question 2: There were around 10-15 databases on the old laptop, but since it was a development machine, in theory all of the data should be replacable, apart from some local experiments. I'm thinking of just scrapping the old data, but I can't remember with 100% certainty what those databases were. Is there a way to extract some basic information (such as database names, maybe even sizes or timestamps) from the old data directory without running a 9.1 server?


texts: What problems will I get creating a database per customer?

texts:
I remember from the stackoverflow podcasts that Fog Creek use a database per customer for Fogbugz. I assume that means the Fogbugz On Demand servers have 10s of thousands of databases.
We are just starting to develop a web app and have a similar problem to solve (lots of customers with their own isolated data).
What problems should I expect with using a database per customer? How can I solve them?
My Initial Thoughts
Advantages of a database per customer

Simpler database schema
Simpler backups - you can backup each customer in turn without it really impacting on other customers.
Makes it easy to export a given customers data.
Better cache performance - a write to one of the more active tables only impacts that single customer that performed the write.
Easier to scale across hardware. For example, when we need to go from 1 to 2 servers, we just move half our customers to the new server.

Disadvantages

Can MySQL cope with 5,000 databases? Would performance suck?
Changes to the schema can be hard to replicate out across all the databases. We would really really have to have an automated plan for this, such as versioning the schema and a script that understands how to take a database from one version to another.
Doing anything that is common to all our customers might be awkward or impossible
Similar to above, but any analytics we want to perform across all our customers might be impossible. How should we track usage across all customers for example?


texts: VSS Writer : RESTORE VERIFYONLY FROM VIRTUAL_DEVICE breaks log shipping

texts:
Log shipping has been working for a very long time, but now and then it breaks. I found it sometimes breaks (causes the transaction log to become corrupt and I get the message : "media family on device is incorrectly formed" of "transaction log is corrupted") when

Microsoft SQL Server VSS Writer

executes

RESTORE VERIFYONLY FROM VIRTUAL_DEVICE='{xxx}' WITH SNAPSHOT,COMMIT_DIFFERENTIAL_BASE,BUFFERCOUNT=1,BLOCKSIZE=1024.

Sometimes it would break all the DBs on log shipping. Other times just one. Other times 2 DBs.

Why would this break log shipping?

Why is it executing - what is the use of "Restore VerifyOnly from VirtualDevice"?

Is it an external instruction to execute this?

Should I disable "restore verifyonly fom virtual device" somehow?

I am clueless here and will take any advice please.


texts: Does "WHERE 1=1" usually have an impact on query performance?

texts:
I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language).

Generally speaking, does this addition to a SQL statment negatively affect query performance? I'm not looking for an answer in regard to a specific database system (because I have used it in DB2, SQL Server, MS-Access, and mysql)-- unless it's impossible to answer without getting into specifics.


texts:
For a project, I am using Microsoft SQL Server Management Studio to run my database. I connect to this through localhost.
I have written an application using Qt which use this database, but now I am distribute the application. How do I change my database on localhost so that I can connect to it from other computers and have multiple connection to the database at the same time.


texts: Why is script with " xp_cmdshell " in comment failing with a transport level error?

texts:
When executing the following batch that is part of a large script:

...
GO
-----------------------------------------------------------------------------------------------
-- Removed the xp_cmdshell for deleting the backup file
-----------------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PR_MyProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PR_MyProc]
GO
...

We get the following error on a remote SQL Server:

  Msg 121, Level 20, State 0, Line 0 A transport-level error has
  occurred when receiving results from the server. (provider: TCP
  Provider, error: 0 - The semaphore timeout period has expired.)

When we remove "xp_cmdshell" from the comment, the batch succeeds. This error only occurs on some of our SQL servers. We are running the script remotely. The version of SQL Server that fails is:

  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.2 (X64) (Build 9200: ) (Hypervisor)

On another server the same script succeeds. That server has this 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.1 (X64) (Build 7601: Service Pack 1) (Hypervisor)

Both servers have xp_cmdshell enabled. The failing server is running Windows 2012 R2 and the server that succeeds is running Windows Server 2008 R2 SP1. If we run the script on the server locally it succeeds. If we run the script remotely from another virtual server, it fails. We also found that the script only fails if the comment has "(space) xp_cmdshell (space)".

Can anyone tell me why this script fails? And why does it fail on some servers and not others?

There is no antivirus running on the server. SQL Server Management Studio is connected, changing the comment to remove xp_cmdshell always succeeds and the original script always fails.


texts: Postgresql: How do full_page_writes help prevent data loss?

texts:
From the postgresql docs

  [full page writes are] needed because a page write that is in process during an operating system crash might be only partially completed, leading to an on-disk page that contains a mix of old and new data. The row-level change data normally stored in WAL will not be enough to completely restore such a page during post-crash recovery. Storing the full page image guarantees that the page can be correctly restored...

How come the WAL logs are not enough to do a full restore? My understanding is that they contain all page updates since (atleast) the last checkpoint.

Can someone give an example of how simply replaying the WAL logs from the last checkpoint could still result in data loss?


texts:
I have a table with 20 millions of rows called [transaction].

That table is partitioned by transaction_date.

Can I create a non clustered index in that table even if it is partitioned?

Example:

CREATE NONCLUSTERED INDEX [IX_TRANSACTION_ID_TRANSACTION_DATE]
ON [dbo].[transaction] ([id])
INCLUDE ([transaction_date])
GO

Here is the table schema:

CREATE TABLE [dbo].[TRANSACTION](

    [id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
    [transaction_date] [datetime] NULL,
    [transaction_datetime] [datetime] NULL
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Are there any considerations to take into account? Or what should be the best practices on it.


texts: Postgresql create database from backup programmaticly

texts:
I got a big problems here. Let's say I got the table A (with columns like id, name etc) in the main database. So when I making INSERT into A  I need to create new database lets say B_id. I got .backup file with database sturcture which suppose to use B_id databases.

I guess I need to write trigger which activates script.

But how I suppose to wrtie the script which creates database(with name depends on id number) and applies .backup file to it? Is it even okay to do like that?


texts: ORA-01045 when attempting to connect to PDB. User has CREATE SESSION privilege, but I Oracle says they don't

texts: The user has been granted CREATE SESSION but i can only connect to CDB and not the PDB. Why is this? I'm trying this in SQL developer. I have Oracle 19c.


texts:
I have a simple SQL query: SELECT * FROM table; - a simple sequential scan - which takes me 10s.

When I add CREATE TABLE AS (CTAS syntax) to it, i.e. my query is CREATE TABLE db_test AS SELECT * FROM table;, the query now takes 18s.

I could not see any notable difference between the plan.

What are the steps I can take to minimize this 8s overhead? I have already tried using UNLOGGED keyword.


texts: SSISDB master key in an Availability Group

texts:
I activated Always On support for SSIS and everything works fine until a fail over operation happens. Turns out the secondary nodes have a different service master key than the primary node and after each fail over I need to run this at the new primary node:
USE SSISDB
GO
open master key decryption by password = 'secretpassword'
Alter Master Key Add encryption by Service Master Key

I've thought about including this steps as part of the "SSIS Failover Monitor Job" to check if the node has changed to primary and then alter the DMK but I wouldn't want to leave the password explicit.
Does anybody have an idea how to deal with this?


texts:
I have a script that creates a zip dump file using mongodump for all of my sibling databases in Mongo. I then transfer these to a different mongoDB instance(having the same sibling dbs) and use mongorestore to fill up the collections and their respective documents.
The db version is v3.2.19

Does anybody know of a way or direct me to some resource that helps validating the two mongo processes (preferably programmatically)? I would like to say for certain that the number of documents/collections dumped were the number of documents/collections inserted using restore.

Thanks again!


texts: Firebird begin backup and forced writes off, timestamp update delayed

texts:
We have a Firebird database that's almost 200 Gbyte in sice, and for performance we have forced writes off. Please don't debate the risks of forced writes, we are aware of them. This is Firebird 3.0.4 on Windows Server 2016.

For backup, we use alter database begin backup and alter database end backup, and copy the main database file using FastCopy. We can see that the delta file is created right away when executing alter database begin backup. But the main database file usually gets an updated timestamp quite some time later, often within a few minutes, but sometimes it takes longer.

I assume this is caused by forced writes off and the fact that Windows may delay some writes for an arbitrary amount of time. In other words, I assume that the Firebird engine does in fact not write to the main databse file after alter database begin backup, but writes that were made before this may be delayed by Windows for quite a while, meaning it's not in fact safe to start copying the main database file until Windows has flushed all writes.

Now, my question is how to properly handle this to achieve safe and reliable backups?

Up to now I've scheduled file copy to 3 hours after alter database begin backup, and I also included a dummy transaction right after the alter database begin backup.

But are there better approaches?

I came up with the idea to use gfix to switch forced writes on before executing alter database begin backup (and switch it back off later). I assume this will cause the locked state to be flushed to disk right away, but I also assume that writes that were made before switching forced writes on will still suffer the arbitrary delay from Windows' write cache. Correct? Or is gfix or the Firebird engine actually able to force flush all previous writes that are already in the Windows write cache?

Another idea is to use Sysinternals Sync util to flush Windows' write cache for the entire disk. For overall system performace, this would not be a problem, considering backup is scheduled to a low-traffic time of day.

We could use nbackup instead of FastCopy. Would this help? In other words: would nbackup's reads of the main database file see the new still-cached versions of the database pages being copied, or would it see the outdated on-disk versions?

In fact, I'm not sure if FastCopy actually sees the new still-cached versions or not, but it fails when it notices that the source file's timestamp has been changed since it started copying, so it fails anyway. There's no apparent way to avoid this.


texts: SQL command in stored procedure continue on error

texts:
To perform Checkdb on all database of server i am using a stored procedure which loops over a view which contains list of databases in server and the stored procedure performs checkdb on all databases in the server. But if any database has any error(like corrupt etc) then checkdb does not continue to check other database and stops by giving error. But i want checkdb to continue analysing other databases on error. How it can be done?

Below is stored procedure i used.

USE [master]
GO

CREATE PROCEDURE [dbo].[SP_CHECKDB] AS

SET NOCOUNT ON

DECLARE
    @dbName VARCHAR(255),
    @strSQL NVARCHAR(MAX)

DECLARE sDatabases CURSOR FAST_FORWARD
  FOR SELECT MP.DatabaseName FROM dbo.DatabaseList MP
  OPEN sDatabases
    FETCH sDatabases INTO @dbName
      WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @dbName = RTRIM(@dbName)
      PRINT CHAR(10) + CHAR(10) + '***************' + CONVERT(VARCHAR(25), GETDATE(), 121) +
                ':  PERFORMING DBCC CHECKS FOR DATABASE [' + @dbName + ']' + '***************' + CHAR(10) + CHAR(10)
      SET @strSQL = N'DBCC CHECKDB('''+@dbName+''') WITH ALL_ERRORMSGS, NO_INFOMSGS'
      EXEC sp_executesql @strSQL
    FETCH sDatabases INTO @dbName
    END
CLOSE sDatabases
DEALLOCATE sDatabases

GO

Below is a view(DatabaseList ) which is used by stored procedure

USE [master]
GO

create VIEW [dbo].[DatabaseList]
AS SELECT
      [name] AS DatabaseName,
      RANK() OVER (ORDER BY [name]) AS DatabaseRank

    FROM sys.databases SDB
        WHERE
      --Exclude system databases
      SDB.[name] NOT IN('tempdb')

GO


texts: MySQL Create Index Semantics: Clustered vs. Nonclustered?

texts:
I'm new to databases and specifically MySQL, so pardon me if this is a simple question.

I'm trying to make sense of what happens with MySQL when I call CREATE INDEX. I know that primary keys are unique and correspond to a clustered index but what happens if I have a table where I have no primary key defined and create an index. I know by default the data structure is a B-tree but it's not clear if this corresponds to a clustered or nonclustered index in the typical sense.

Specifically I have a table that that has four columns as integers:

sales(item_id, time, price, location_id)

The only column that has unique values is time. If I create this table and then later run CREATE INDEX dx ON sales(item_id); what happens?

From here I'm lead to believe that when I create the table without the primary key, because time is unique that is the clustered index and so I'm creating a non-clustered index on item_id.

My questions are: is this correct? If so how can I create a clustered index on a non-unique column in MySQL?


texts: SQL not engaging parallelism for extremely large query

texts:
I have a very large query (~630 lines) that involves a lot of nested SELECT statements and pulls from multiple views. Our SQL server has parallelism set to 2 with a threshold of 95 (set that way because our DBA was optimizing it based on some other applications). This query has recently started taking 5-10 minutes to complete, up from usually less than a minute. While investigating the cause, we noticed that it never seems to trigger parallelism, always running serial, and suspect that may have something to do with its performance. What's weird is while experimenting, we even dropped the threshold back down to the default value of 5 seconds, and it still won't run parallel. What could be preventing it?

We've been doing the testing on a non-production environment that nobody else was using at the time, so this was the only query being run. Our DBA also tried things like clearing caches and plans, and even recycling the system, but it had no effect.

Update 1: Per comments, I've verified that stats are updated nightly but the problem persists. We're actually rolling back the code to an earlier version that didn't have such serious performance issues, but will continue to test this code because it was supposed to improve performance over the old code, and actually did in initial testing. Will update here accordingly.


texts: How to avoid a cyclic dependency (circular reference) between 3 tables?

texts:
I have 3 tables:

People
Post
Likes

When I design the ER model it has a cyclic dependency:

         1:N
People --------< Post

         1:N
Post ----------< Likes

         1:N
People --------< Likes

The logic is:

1 people can have many posts.
1 post has many likes.
1 people can like many posts (created person cannot like his own post).

How can I remove this kind of cyclic design? Or is my db design wrong?


texts:
I struggle to get the result for
here is my dataset

A
B
C
cnt

2
1
2

1
5
5

2
1
4

1
1
1

result expected

A
B
C
cnt

2
1
2
2

1
5
5
2

2
1
4
3

1
1
1
1

PG 9.5


texts:
I have a web application that makes simple queries to a MySQL database. The database is quite large (8.4GB) and the time to lookup an entry needs to be as fast as possible.

The database represents a graph theory problem and I'm running a shortest path algorithm that queries it possibly millions of times to find a path. I'm looking at server options to host my app. I expect very light traffic to the site. (Database is well indexed and optimized.)

I know that an SSD server is a must. (Average time with SSD 10-20 seconds on Dell laptop clocked at 1.6Ghz Intel i5, compared to several minute wait on HDD with same hardware)

I consider a 10-20 second wait acceptable and want to ensure that the server can achieve similar results.

My question:

What kinds of CPU speeds/cores should I be looking at? What is the processor clock/cores that will ensure that the SSD is the bottleneck.

I expect that this is varies depending on the nature of the program. If so, how might I go about testing speeds at different clocks, considering I have only one PC?


texts: Mapping a 1:1:N ternary relationship to a relational schema

texts:
I have a question about mapping (conceptual) n-ary relationships to (logical) relations.

Assume we have three strong entity types: Member, Equipment, and Time Slot, and a relationship named Reserves between them.

The attributes do not matter (just assume we have an Id key attribute), but the cardinality ratios are as follows:

Meaning that:

A member can reserve a particular equipment at multiple time slots (the N),
An equipment can be reserved at a particular time slot by only one member (the 1 on the left),
A member can reserve only one equipment per time slot (the 1 on the right).

And there is no total participation constraint.

In look across notation (I believe):

I'm trying to map this relationship to a relation in a relational design.
We would have:

RESERVES
(MEMBER.Id, EQUIPMENT.Id, TIME_SLOT.Id)

Where the three attributes are foreign keys to, respectively, the relations that correspond to MEMBER, EQUIPMENT and TIME_SLOT.

But what should be the primary key?

Elmasri, Ramez, and Shamkant B. Navathe. 2015. Fundamentals of Database Systems (7th Edition). Pearson. reads, p. 296:

  The primary key of S [the relation resulting from the mapping of the n-ary relationship R to the relational model] is usually a combination of all the foreign keys that reference the relations representing the participating entity types. However, if the cardinality constraints on any of the entity types E participating in R is 1, then the primary key of S should not include the foreign key attribute that references the relation E' corresponding to E (…).

Applying this recipe blindly make that only TIME_SLOT.Id is the Primary Key, which does not make sense at all (two different equipments can be reserved at the same time!).

Having the three attributes being the primary key does not reflect the fact that one equipment cannot be reserved multiple times at the same time slot.


texts: What would be possible reasons for a DeadLock to occur in Mysql Database?

texts:
If some one has a Table structure some thing like this,

CREATE TABLE IF NOT EXISTS `domain_source` (`nid` int(11) NOT NULL DEFAULT '0', `domain_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`nid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores the canonical domain for each node.';

On Heavy load i get Deadlock  on Insert to this Table Structure. I usually delete and insert a row in the table rather than update as the engine is Innodb.


texts:
I'm running MySQL 5.5 on Debian 7.8. My entire DB is 2200 MB large (all tables are InnoDB), the mysql process is taking up to 6G RAM but still I think that not evrything is loaded in RAM I have large tables with more than 160 columns and about 500 000 rows. The first 64 columns are indexed with key and when I read them they are loaded instantly but table is allowed to have only 64 keys so the rest of table is extremely slow compared to the first 64 columns.

There are about 350 queries per second in peak hours and the database is consuming up to 160% CPU (Xeon 1246 v3).

I believe that I have something wrong configured but I cannot figure out what. This is my config:

join_buffer_size    = 1M
open_files_limit    = 10000
myisam_use_mmap     = 1
query_cache_type    = 1
table_open_cache    = 2000
concurrent_insert   = 2
max_connections     = 3000
query_cache_size    = 16M
key_buffer_size     = 512M
read_buffer_size    = 8M
query_cache_limit   = 4M
query_cache_min_res_unit = 1K
tmp_table_size      = 128M
thread_cache_size   = 5000
sort_buffer_size    = 2M
max_heap_table_size     = 64M
read_rnd_buffer_size    = 128M
thread_concurrency      = 8
thread_stack        = 1M
innodb_log_buffer_size  = 2M

innodb_buffer_pool_size = 8192M
innodb_file_per_table   = 1

Is there anything wrong in my configuration? I have searched net many hours and this config should be ideal for my DB but I am not sure...


texts:
I want to count the number of rows with duplicate ids

My current code is

SET @x=1;

SELECT

c.id,
so.trans_id,
IF(c.id = c.id, @x + 1, @x) as count_t

FROM customer c
LEFT JOIN sales_order so

Current Result

+-----+----------+----------+
| id  | trans_id | count_t  |
+-----+----------+----------+
| 101 | 100001   |  2       |
| 101 | 100059   |  2       |
| 101 | 100061   |  2       |
| 102 | 100030   |  2       |
| 102 | 100035   |  2       |
| 102 | 100090   |  2       |
| 103 | 100005   |  2       |
| 103 | 100115   |  2       |
+-----+----------+----------+

Desired Result

+-----+----------+----------+
| id  | trans_id | count_t  |
+-----+----------+----------+
| 101 | 100001   |  1       |
| 101 | 100059   |  2       |
| 101 | 100061   |  3       |
| 102 | 100030   |  1       |
| 102 | 100035   |  2       |
| 102 | 100090   |  3       |
| 103 | 100005   |  1       |
| 103 | 100115   |  2       |
+-----+----------+----------+

SQL Version:

MySQL 5.6.32-78.1

UPDATE:

I added a nested query and a GROUP BY function to my code but it doesn't seem to count properly.

My NEW code is

SELECT

id,
time,
next_time,
(SELECT COUNT(1) FROM ecms.customer c1
LEFT JOIN ecms.sales_order so1 ON so1.customer_id = c1.id
WHERE c1.id = t.id AND so1.date_created <= t.time) as count_t

FROM(
SELECT

c.id,
so.date_created as time,
(
    SELECT
        MIN(so1.date_created)

    FROM ecms.customer c1
    LEFT JOIN ecms.sales_order so1 ON so1.customer_id = c1.id
    WHERE c1.id = c.id
    AND so1.date_created > so.date_created
    AND IF((TIME_TO_SEC(TIMEDIFF(so1.date_created,
    so.date_created))/360)>=60,1,0)=1
    ) as next_time

    FROM ecms.customer c
    LEFT JOIN ecms.sales_order so ON so.customer_id = c.id
    LEFT JOIN ecms.sales_order_item soi ON soi.sales_order_id = so.id
    LEFT JOIN ecms.product p ON p.id = soi.product_id
    LEFT JOIN ecms.history h ON h.transaction_id = so.transaction_id
    GROUP BY c.id, next_time
) as t

WHERE id = 7941

Sample Result would be

+-----+----------+-----------+----------+
| id   | time    | next_time | count_t  |
+-----+----------+-----------+----------+
| 7941 | 100001  | 100061    |  1       |
| 7941 | 100061  | 100121    |  4       |
| 7941 | 100121  | 100181    |  7       |
| 7941 | 100181  | 100241    |  8       |
| 7941 | 100241  | 100301    |  16      |
| 7941 | 100301  | 100361    |  21      |
| 7941 | 100361  | 100421    |  45      |
| 7941 | 100421  | NULL      |  69      |
+------+---------+-----------+----------+

Sample Result Desired

+-----+----------+-----------+----------+
| id   | time    | next_time | count_t  |
+-----+----------+-----------+----------+
| 7941 | 100001  | 100061    |  1       |
| 7941 | 100061  | 100121    |  2       |
| 7941 | 100121  | 100181    |  3       |
| 7941 | 100181  | 100241    |  4       |
| 7941 | 100241  | 100301    |  5       |
| 7941 | 100301  | 100361    |  6       |
| 7941 | 100361  | 100421    |  7       |
| 7941 | 100421  | NULL      |  8       |
+------+---------+-----------+----------+


texts: Using the date of a result column as a partial input


texts: Database design for smartphone product and its features


texts:
I have the following update query to populate a table storing user stats

users:

int (pk) user_id

date     last_visit

int (fk) local_site

sites:

int (pk) site_id

site_stats:

int (fk) site_id

int total_users

int monthly_users

int weekly_users

To populate the 'total_users' column I am using the following query...

UPDATE site_stats
SET
    total_users = totalUsers,
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site

The query above works ok, I would like to now extend it to update the monthly_users and weekly_users

Is it possible to do in the same query?

I was initially thinking of using a window function but after reading the docs I am not so sure its possible, eg.

UPDATE site_stats
SET
    total_users = totalUsers,
    monthly_users = monthlyUsers,
    weekly_users = weeklyUsers
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
        count(u.*) OVER ( ** where u.last_visit > now()::DATE - 30 ** ) AS monthlyUsers,
        count(u.*) OVER ( ** where u.last_visit > now()::DATE - 7 ** ) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site

Or would a subquery be more appropriate?


texts: How to determine the size of a SQL Server database for a specific date in the past?

texts:
Microsoft SQL Server 2008 R2 Standard

What I would like to find out is what the size of the DB was at specific dates in the past.  I don't have backups going back that far. The current log may or may not go back far enough.

Is there any way at all to retrieve that type of information?


texts:
I have the following tables:

 create table tags (
        id bigserial primary key,
        file_id bigint,
        key varchar,
        num bigint,
        str varchar)

create table meta_info (
    id bigserial primary key,
    filename varchar,
    created timestamp with time zone,
    version varchar
    )

file_id is a reference for meta_info.id

I need to find a filename and gather all tags for it.
The result must be the following:

[{"key": "val", "key1": "val1", "filename": "A/B/C/1.exe"},
{"key3": 5, "key2": ["val2"], "filename": "A/B/C/2.exe"}]

Tags table can contain the following data:

file_id key   num  str
2343423 "key" null "val"
2343423 "key1" null "val1"
4564576 "key3" 5    null
4564576 "key2" null ["val2"]::text

I cannot keep tags as jsonb since json query doesn't support a complex structure like 'ilike' search smwhere inside json or when I need a deep search inside json and compare value (less than some value), etc.

Also index will be not used for the query (only @>, ? can be used for index)

When we have a value as a numerical data, we will have null in the str column.
When we have a value as a string or dict, list, etc.,  we will have null in the num column.

So I need to gather all tags but I cannot merge list of jsons online.
Here is my SQL query:

 SELECT filename, json_array_elements(json_agg(json_build_object(tags.key, COALESCE(tags.str, CAST(tags.num as varchar(100)))))::text) as val FROM
(SELECT *, rank() OVER (partition by filename order by created desc) AS rank
        FROM meta_info m
        where m.filename like 'DATASETS/14.0/%'
        ) tt
        inner join tags on tags.file_id=tt.id
        WHERE tt.rank = 1
    group by tt.filename

I have the following result:

DATASETS/14.0/Error_1012/input.exe/ [{"key1" : "val1"},{"key2" : "val2"}]
DATASETS/14.0/Error_1011/input.exe/ [{"key3" : ["val3"]},{"key4" : 5}]

So the second column is the list of dictionaries, I need to merge them "online". How can I do it in the SQL query?


texts: Database role to allow .NET program to retrieve MSDB table and view rows

texts:
I'm currently writing a database management application that connects to all of my databases and returns metadata for each database. The user currently has db_datareader role for MSDB but the DataReader .NET object only contains schema and no rows after executing a query on sysjobs or sp_help_jobs stored procedure in the code or in SSMS. I've tried granting execute and select on the database and object levels to no avail. When I give SA to the user it is able to return data, but I do not want to give the user SA.

What is the specific permission needed to retrieve the data from system tables and maintain the highest level of security?


texts: MongoDB 3.0 : Configure storage engine at collection or database level

texts:
I know we can configure the storage engine to be wiredTiger at instance level when we start mongod.

But can we also select the storage engine to be different for different collection or database?


texts: MariaDB "Checking master version" during replication

texts:
Parallel replication with about 8 parallel threads is up continuously between one master instance and one slave instance. Every day at around the same time SHOW SLAVE STATUS on the slave shows these two states:

  Slave_IO_State: Checking master version
Slave_IO_Running: Preparing

The documentation on the first one says

  Checking master version - Checking the master's version, which only occurs very briefly after establishing a connection with the master.

That would be pretty straightforward if this slave had just started, or if the connection between the two had been lost, but there is no other sign of that happening, and this state comes up pretty consistently (+/- a couple seconds) once a day.

The documentation of the second one makes no mention of "Preparing" as a possible value.

  Slave_IO_Running - Whether the slave I/O thread is running and connected (Yes), running but not connected to a master (Connecting) or not running (No).

Both master and slave are running

  Server version: 10.1.13-MariaDB Source distribution

What causes the slave to get into this state? I wonder if the fact that there are multiple parallel replication threads has something to do with it or if that is a red herring.

UPDATE: I just came across this note in the MariaDB documentation, and its bug. I wonder if it is related.

  . . .the TIME column of  SHOW FULL PROCESSLIST and INFORMATION_SCHEMA.PROCESSLIST can not be used to determine if a slave is lagging behind. For this, use instead the Seconds_Behind_Master column in the output of SHOW SLAVE STATUS.


texts: Disable auto-start of postgres server on boot (Mac)

texts: Running a Mac.  I installed Postgresql 9 many years ago and configured it to auto-start.  Now I have upgraded to version 12 using brew, but on system restart version 9 server is still starting automatically.  I don't know how to determine what is starting it, I don't see anything in my bash_profile and I don't see a feature in brew or pg_ctl that allows me to disable an auto-start configuration.  I have no postgres files in /Library/LaunchAgents either.  Thanks


texts:
psql 13.2 executes the query buffer no matter what when exiting the editor, even after quitting it - in which case the buffer may hold the most recently executed SQL query now.
To reproduce:

Start editor (vim in my case) with \e or \e filename from the psql console.
Type something or not, doesn't make a difference.
Cancel edit with :q (or :q! to force it)

Expected behavior: go back to the state before starting the editor. (I cancelled the edit!)
Observed behavior: re-execute the last executed SQL query.
What seems to happen with \e:

it executes (the equivalent of) \p, so the current query buffer contains the previous command when it was empty. (Which is not visible to the user when editing a file!)
The manual  on \p :

\p or \print
Print the current query buffer to the standard output. If the current query buffer is empty, the most recently executed query is printed instead.

Start up the editor. If a filename was provided, load the file, else, load the query buffer.

If the editor ends with a write, copy the result to the query buffer.
If the edit is cancelled (:q in vim), don't overwrite the query buffer. (It holds the previous command now!)

Execute the query buffer. Or, to be precise, do this:

How to prevent query execution after exiting the \e editor in psql?

The behavior seems ok for \p (just copy the query buffer, no harm done), but not for \e, which can execute a command I didn't even get to see.
Is it a bug?
Either way, I would at least like to disable auto-execute when leaving the editor. But that doesn't seem to be possible. Or am I missing something?


texts: Grant User permission to view XE results via sys.fn_xe_file_target_read_file

texts:
I need to grant a user (MyDomain\JohnSmith) permission to view one particular XE session results using sys.fn_xe_file_target_read_file

I thought the best way to do this would be to encapsulate the logic within a stored procedure in a database which John Smith uses called MyDb and then grant him access to the stored procedure.

I have solved this so far but it means using impersonation of the dbo user within the stored procedure and setting IS_TRUSTWORTHY on which some Googling suggests is a less secure method and that signing a stored procedure is the most secure way to achieve this.

Going the the signing method, I have hit some problems as below:

First of all, I create a certificate in the database:

USE MyDb
GO

CREATE CERTIFICATE [CodeSigningCertificate]
ENCRYPTION BY PASSWORD = 'SuperSecretPassword'
WITH EXPIRY_DATE = '2099-01-01',
SUBJECT = 'Code Signing Cert'

then I create my stored procedure which has the logic

CREATE PROCEDURE MySchema.MyProc
AS
BEGIN

    DECLARE @TraceFilePath NVARCHAR(256) = 'Path/To/My/File*.xel'

    SELECT  CONVERT(XML,event_data).value('(/event/@timestamp)[1]', 'NVARCHAR(MAX)' ) AS [TimeStamp],
            CONVERT(XML,event_data).value('(/event/action[@name="database_name"]/value)[1]', 'NVARCHAR(MAX)' ) AS [Database_name],
            CONVERT(XML,event_data).value('(/event/data[@name="statement"]/value)[1]', 'NVARCHAR(MAX)' ) AS [Statement],
            CONVERT(XML,event_data).value('(/event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)' ) AS SQL_Text,
            CONVERT(XML,event_data).value('(/event/action[@name="username"]/value)[1]', 'NVARCHAR(MAX)' ) AS Username,
            CONVERT(XML,event_data).value('(/event/action[@name="client_hostname"]/value)[1]', 'NVARCHAR(MAX)' ) AS Client_Hostname,
            CONVERT(XML,event_data).value('(/event/action[@name="client_app_name"]/value)[1]', 'NVARCHAR(MAX)' ) AS Client_app_name
    INTO    #Results
    FROM    sys.fn_xe_file_target_read_file (@TraceFilePath, NULL, NULL, NULL )

    SELECT  *
    FROM    #Results
    WHERE   Statement LIKE '%some value%'
    ORDER BY SQL_Text
END

Next, I sign the stored procedure

ADD SIGNATURE TO MySchema.MyProc
BY CERTIFICATE [CodeSigningCertificate]
WITH PASSWORD = 'SuperSecretPassword';

Then I create a user which uses the certificate

CREATE USER [CodeSigningUser] FROM CERTIFICATE [CodeSigningCertificate];

At this point, I need to grant the underlying privilege that the is required to run sys.fn_xe_file_target_read_file which is GRANT VIEW SERVER STATE

GRANT VIEW SERVER STATE TO [CodeSigningUser]

but when I try to do this, I get

"Msg 4621, Level 16, State 10, Line 44
Permissions at the server scope can only be granted when the current database is master"

which makes perfect sense as it is a server level permission that I am trying to grant to a database level principal

I have tried creating a login for the certificate (as I can grant VIEW SERVER STATE to a login) :

CREATE LOGIN [CodeSigningLogin] FROM CERTIFICATE [CodeSigningCertificate];

but I get the error

Msg 15151, Level 16, State 1, Line 40
Cannot find the certificate 'CodeSigningCertificate', because it does not exist or you do not have permission.

Which, again makes sense as the certificate is in a user database

I can create the certificate in the master database which allows me to create the login from the certificate but then I can't sign the stored procedure using that certificate as the certificate resides in a different database to the stored procedure.

The only way I can think of to accomplish this is to create the Stored Procedure in the master database and then create the certificate, also in the master database, create the login and assign VIEW SERVER STATE to the login.

Is there a way I can keep my stored procedure in MyDb and have a user MyDomain\JohnSmith execute it to be able to see the XE Session Results?


texts: How do I access a old saved tablespace after reinstalling postgres?

texts:
So a lot of things might have gone wrong here. I first installed postgres 9.3 on mac 9.1 via homebrew to manage some data. I am using postgres.app. Turns out my harddrive was too small so I decided to migrate my tablespace to an external harddrive ( I got this to work ). Then I basically changed my default tablespace to access the external drive. Then... couple weeks later I tried to run psql shell and it was throwing some error that it could not access subdirectories and something like this:

Macbooks-MacBook-Pro:~ macbookpro$ psql
psql: FATAL:  database "macbookpro" does not exist
DETAIL:  The database subdirectory "pg_tblspc/24748/PG_9.3_201306121/16384" is missing.

So, I looked around and found no answer so decided to reinstall postgres. After I reinstalled everything, I tried to reaccess my tablespace but it would not let me! I can see that the folders include over 130 GB of data so there must be something in there.

Please help! How do I access the data?

Thank you!


texts: How can I generate fake data that adheres to the PRIMARY KEY-FOREIGN KEY constraints of my schema?

texts:
I have a schema for which I would like to generate fake data. I can generate fake data using Python for each of the tables separately, but I don't know how to make sure the fake data generation adheres to the constraints I have set between the tables.

I would like to generate 1 million rows for each of the tables. My system is Ubuntu 18.04 LTS.

I came across Red Gate's SQL generator, but it is only for SQL Server AND available only in Windows. SQL Server is currently not available for Ubuntu 18.04 either. I currently have PostgreSQL and MySQL in my system.

http://www.generatedata.com I can install locally (didn't try it yet) but I still can't figure out how to make sure the data generation adheres to PK-FK constraints.


texts: db_datareader, db_datawriter and db_owner permissions

texts:
First of all, sry because I am not a DBA and I'm not familiar with all the permissions.

My question is, if I give a user db_owner permissions on one database. Do I need to give also db_datareader and db_datawriter permissions to have full control? I think db_owner is sufficient to execute all kind of queries on the database? (create, delete, alter, select and all other stuff).

So, in short: If I choose db_owner, then db_datareader, db_datawriter are automatically included. Am I right?


texts: Database schema design implementation feedback

texts:
I'm having difficulty on designing a database schema design with the right relationships. I'm currently trying to design a database system where a question can be answer via a  live_stream. The general idea is that a user can ask a question. Then a user can answer the question and set a live_stream. Many users can join that live_stream. Here is the diagram. https://dbdiagram.io/d/5f2a06ce7543d301bf5de918

I have added a link to the schema design where you can navigate the relationships. But basically the idea is as follows.

A user can create many questions. user 1 -> * question

A question belongs to a user

A user can like a question user 1 -> * likes,

A question can have many likes by users. question 1 -> * likes

A question has one answer question 1 -> 1 answer

and an answer belongs to a user user 1 -> answer

Here is something I have struggle with. Basically I have created a join table for a many to many relationship between users and answers. Using live_streams as the join table. The way I see it is that many users can watch the explanation of the live_stream for the answer and the answer can have many users watch the live_stream. Also I added presenter to the live_streams table. The presenter is also a user.

Many users can join a live stream users 1 -> * live_streams

A live stream belongs to an answer answers 1 -> * live_streams

A user is a presenter of the live stream user 1 -> 1 live_stream

I'm still a but skeptical about the way live_streams table works as a join table between users and answers. Is still clear to me that many users can join a live stream. but not sure if the relationship between answers and live_streams should be a one to many, but I see it as live_stream belongs to answer.
The user presenting the live stream has to be the person who answered the question. So if the answer belongs to a user, that same user should be the presenter in the live_streams table. The answers table works more as setting the time, description and name of when the live stream is going to take place.
Is this correct? I'm open to feedback as to how to improve the design or if there is any errors in the logic.


texts: SQL Server many-to-many relationship, row reuse?

texts:
Below is your typical many-to-many relationship. Pretend there are 2 tasks that have the exact same Tags. because of foreign key/primary key relationship constraints, there is no way for the rows in the table TasksTag to be reused (both Tasks would have the same PK; impossible)

In my scenario, theoretically, a task will usually contain most or all tags, that means every Task will have roughly 300+ rows in the TasksTag table. This scales horribly because there will be a few hundred Tasks per month and a lot of which will have the same Tag list.

Can someone explain another way of doing this without removing the constraints? Am I worrying about space too much?

Thanks!


texts: Query with WHERE NOT and NOT IN with subquery

texts:
MySQL Version: 5.6.27

I am using the following two tables

TABLE: oc_product
product_id      int(11)
model           varchar(64)
tax_class_id    int(11)

and

TABLE: oc_product_to_category
product_id      int(11)
category_id     int(11)

I want to change the tax_class_id for a few products on TABLE oc_product. So I am running a SELECT query before UPDATE

SELECT `product_id`, `model`, `tax_class_id`
FROM `oc_product`
WHERE
NOT `tax_class_id` = 12
AND
`product_id`
NOT IN ( SELECT `product_id`
        FROM  `oc_product_to_category`
        WHERE  `category_id`
        IN ( 73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433)
        )

Unfortunately the results are not as expected.

All I want is to select all product_id from the table oc_product where tax_class_id is not 12 and product_id does not exist in the results of the following sub query.

SELECT `product_id`
FROM  `oc_product_to_category`
WHERE  `category_id`
IN ( 73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433)

Did I miss something? Please advise!


texts: Mysql 5.6.17 drop large table locks the server for minutes

texts:
We run Mysql 5.6.17.

Dropping a large InnoDB table 10GB caused the whole server to lock for about 40 seconds. We have 55GB innodb buffer pool size. I read bunch of old bugs that could be the source of this problem, but as far as I can see they should be fixed: (http://bugs.mysql.com/bug.php?id=64284). Also the same lock happened when we tried alter table with LOCK=NONE. The alter was going well and at the end when it was switching the table the whole server locked for 10 minutes This was very large table. Also it was reproducable on our slave the same alter also locked the whole database for few minutes even though we have no reads on the slave. We use one file per table. Any ideas?


texts: Is SQL Injection possible after filtering " and /?

texts:
I'm aiming at building dynamic SQL queries that modify columns. The DBMS doesn't allow parameters to be there, so I can't escape identifier using more robust methods.

To be clearer, using ? or $1 is not permitted with identifiers, just with values, and my need is to use dynamic identifiers.

One approach I though is to completely filter out the double quotes and backslash characters from any identifier, and then quote them, so if an identifier is like my"field\ it becomes "myfield".

I did some research, but it's not clear if completely filtering out these  characters (or like "[]" for non-standard implementations like MSSQL) can avoid any "execution of bad stuff" when parsing the string. Is there something else I should look for?

Thanks


texts: MongoDB replica set Network Interface exceeded time limit

texts:
I am using MongoDB version 3.6.12 and saw one of running secondary node keeps getting this error from log:

2019-09-23T12:24:48.921-0400 I REPL     [replication-791] Restarting oplog query due to error: NetworkInterfaceExceededTimeLimit: error in fetcher batch callback: Operation timed out. Last fetched optime (with hash): { ts: Timestamp(1569254987, 1), t: 26 }[-1363109028778963646]. Restarts remaining: 1
2019-09-23T12:24:55.922-0400 I REPL     [replication-791] Error returned from oplog query (no more query restarts left): NetworkInterfaceExceededTimeLimit: error in fetcher batch callback: Operation timed out
2019-09-23T12:24:55.923-0400 W REPL     [rsBackgroundSync] Fetcher stopped querying remote oplog with error: NetworkInterfaceExceededTimeLimit: error in fetcher batch callback: Operation timed out

However, in Mongo shell rs.status() showing all nodes are healthy. Restarting the mongod instance running on the problem node solved the issue.

Anyone know what is the most likely cause of this problem?


texts: MySQL 5.1 - long running select locking the table and not allowing inserts

texts:
Platform - MySQL 5.1, MyISAM engine
We reached max on int (id, auto increment) in our Prod system. Changed the type to BigInt. Even since this change was made, long running selects are locking the table and inserts wait in the queue forever. Any suggestions are appreciated.
Thanks!


texts: Why wont SSMS remember my tables when I connect to the server

texts:
I have the same problem everytime I connect to SQL server through the SSMS tool.  If I have been working on a script and close my laptop for a period of time, when I come back and re-connect it doesnt recognise my tables in the database I am working on.  Usually I have to right click on the table name and select top 1000 rows for it to be remembered in the cache.  Its a workaround which I know I dont need to do as I am missing something or not doing something correctly.

Any tips?


texts: Where are filtered-index dependencies listed?

texts:
Given a table of the form...

create table dbo.FilterIDXTest (
    id int not null identity primary key clustered,
    _bigint bigint,
    _varchar10 varchar(10),
    _varchar20 varchar(20),
    _guid uniqueidentifier
);

go

create unique index uq_FilterIDXTest
    on dbo.FilterIDXTest ( _varchar10, _varchar20 )
    include ( _guid )
    where _bigint is not null
        and id > 5;

go

... in what (if any) catalog view (or other well-formed reference object) can the columns names in in the WHERE clause be located and rigorously identified?

sys.indexes can be viewed to remind us that the filter_definition is ([_bigint] IS NOT NULL AND [id]>(5)).

sys.index_columns can be viewed to tell us both the ON and INCLUDE columns but lists only 3 of the 5 columns in the original CREATE INDEX command. The filter_predicate columns are not found here.

I must assume that the filter_predicate dependencies are rigorously checked at some stage because sp_rename 'dbo.FilterIDXTest._bigint', 'foo_bigint', 'column' fails with the the error...

Caution: Changing any part of an object name could break scripts and stored procedures.
Msg 5074, Level 16, State 1, Procedure sp_rename, Line 611
The index 'uq_FilterIDXTest' is dependent on column '_bigint'.
Msg 4922, Level 16, State 9, Procedure sp_rename, Line 611
RENAME COLUMN _bigint failed because one or more objects access this column.

...and I can't bring myself to believe that the only reason this fails is because of some text matching at sys.indexes.filter_definition.

For reference, the task at hand is to extract the source code from a number of undocumented legacy servers. My request to use RedGate/some other paid helper app for this purpose has already been rejected since "You can just script out the objects from the UI, right?". Perhaps I'm been overly rigorous, but I'd like to have a very high level of confidence that the index I'm checking in to source control is the index that actually exists. I'm testing this script on sql server 2012, but the target servers on which it will ultimately be executed range from 2005 SP1 to 2008 R2 SP3.


texts: Performance impact of a group by function call

texts:
I have the following query, which for simplicity sake I use the simple example format below:

SELECT
  col1, col2, dbo.fun3(col3)
GROUP BY col1, col2, dbo.fun3(col3)

This took 30 seconds to run and returned 6000 records.
When I removed dbo.func3(col3) in both the SELECT and GROUP BY it sped up to only 5 seconds to return 6000 records.

So I guess my performance impact comes down to the following:

dbo.func3() is slow and need further work to improve its performance
The function call in the GROUP BY causes the slowness.

I wanted to isolate how slow is dbo.func3() is so I ran the following query without GROUP BY:

SELECT col1, col2, col3

versus

SELECT col1, col2, dbo.fun3(col3)

The first one returns around 900,000 records in 15 seconds.
The second one return 500,000 records in 15 seconds.

This prove I should improve my func3() function.  However, do you know of anything I can do in the group by part of the query to reduce the delays?

Is this function being called twice because I have it in the select and the group by portion of my query?


texts: In PHP MySQL how to sum or not sum multiple columns?

texts:
I have three tables this is my codes:

        Subject
        Mark1
        Mark2
        Mark3
        Sum Marks

<?php
    $query2 = "SELECT  students_info.s_name, subjects.subject, marks.*
            FROM  subjects
            RIGHT JOIN  (students_info
            RIGHT JOIN  marks  ON students_info.s_id = marks.s_id
                )  ON subjects.sub_id = marks.sub_id
            WHERE  students_info.s_id = '".$s_id."'";

    $result2=mysqli_query($link,$query2) OR die(mysqli_error($link));

        while ($row=mysqli_fetch_array($result2))
        {
 ?>

            <?php echo $row['subject'];?>
            <?php echo $row['n1'];?>
            <?php echo $row['n2'];?>
            <?php echo $row['n3'];?>
            <?php echo $row['n1']+$row['n2']+$row['n3'];?>

<?php
        }
?>

 <?php
}
?>

Output my codes:

I want when each mark form(mark1,mark2,mark3) for every subject less than 49 the sum operation not work in the cell sum marks appear empty value or empty cell,
Please review my code and give me a help, to do work this code.


texts: Does inserting in order have the same effect as clustering?

texts:
I have a table that will contain some pre-computed data based off other tables. (Computing on the fly is too computationally expensive given the size of data I have to deal with.) I will be generating incrementally as source data is added. (I won't ever need to UPDATE it in normal usage; portions may be deleted and regenerated.) The table will be fairly large. It's currently about 50 million rows and will grow every year.

Most of the queries against this table will be filtered down by a foreign key ID column. As such, they perform better if all the rows for that ID are grouped into the same pages. I can guarantee this ordering on disk by creating an index and calling CLUSTER periodically, but this is obviously less than ideal as it would require some kind of scheduled task, coordinating against usage and other scheduled tasks, etc.

However, since I'm generating this data in chunks related to the foreign key I want to CLUSTER by, I can easily put an ORDER BY clause on the INSERT command:

INSERT INTO big_table (source_table1_id,a,b,c)
SELECT
   source_table1_id,
   5 /* some formula */,
   /* ... */
FROM source_table1
JOIN source_table2 ON ...
...
WHERE ... /* some condition indicating what needs to be generated */
ORDER BY source_table1_id

Will this affect the on disk storage order, grouping the rows into close to the minimum number of pages? And if it does, are there other processes that could mess up the on disk order later?

I am currently using PostgreSQL 9.3, but I'd like to know about newer versions as well for upgrades.


texts: Index by subkey inside subkey inside array in jsonb

texts:
I have a jsonb field registration that contains an array of objects. I need to create two separates indexes to look for id, by example:

d61802ff-3eec-4a72-97ca-832f51b96bf0 = [ .tour_id.id ]
f492b26d-d260-4bcb-8e96-6532fdf38665 = [ .customer_id.id ]

Example data (I have more keys and arrays, but it's a resume)

[
   {
    "tour_id":
      {
        "id":"d61802ff-3eec-4a72-97ca-832f51b96bf0",
        "name":"Paris  2018"
      },
    "customer_id":
      {
        "id":"f492b26d-d260-4bcb-8e96-6532fdf38665",
        "name":"OBAMA John"
      }
   },
   {
    "tour_id":
      {
        "id":"a52d38d4-9bfb-4ffa-1122-e536f04b0c60",
        "name":"London  2018"
      },
    "customer_id":
      {
        "id":"f492b26d-d260-4bcb-8e96-6532fdf38665",
        "name":"OBAMA John"
      }
   }
]

How to index by [array].customer_id.id?
Or is there a method to index by subkey.subkey? Or deeper nested subkey?

I'm using Postgres 9.6.


texts: MySQL: Error 1114. table is full. can't drop the table

texts:
I am trying to load a single 28GB csv file (257 million rows) into MySQL database for almost a week. However I got Error 1114 while loading after 4730 seconds (or more/less, different in every execution). I have tried to find the solution here and already adjusted the my.ini as following:

max_allowed_packet = 1024M
max_heap_table_size = 1024M
tmp_table_size = 1024M
innodb_buffer_pool_size = 2048M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_log_buffer_size = 1024M
innodb_log_file_size = 1024M
innodb_file_per_table = 1

But the error still occurred. The server logs just showed The table xxx is full. The second problem is that I cannot drop this corrupted, full table. The server connection broken everytime when I want to drop the table (Lost connection). I am wondering how can I modify my my.ini better to deal with this big csv file and drop the broken table?

My RAM is 4G and the harddrive still has enough space (162G, so I don't think this is problem). My session settings are: keep-alive interval / read time out / time out are all 99999 seconds.


texts: When do _WA_Sys_ statistics Get Updated?

texts:
I have some of the auto generated _WA_Sys_ statistics in my database which have not been updated in a while (by comparison to other statistics in the same table)

The rule of thumb appears to be in tables >500 rows that statistics are updated at a 20% + 500 row change of data.

However, I can see using the following query

SELECT  t.name,
        i.name,
        i.rowcnt,
        i.rowmodctr,
        p.last_updated
FROM    sys.sysindexes i
        JOIN sys.tables t
            ON i.id = t.object_id
        JOIN sys.stats s
            ON s.object_id = t.object_id AND i.name = s.name
        CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) p
WHERE   rowmodctr > 0
ORDER BY i.rowmodctr DESC

That there is a table with a number of _WA_Sys_ statistics that are way out of date (and have a rowmodctr that is higher than 20% + 500)

If a run a query against the table and add one of the columns associated with the out of date _WA_Sys_ statistics in the WHERE clause and check the updated date of the statistic, I can see it has updated.

If i run the same query with the WHERE clause again, the statistic doesn't update

It seems like the _WA_Sys statistics update when a query is run that will use them and they are out of date?


texts:
We are planning to merge 2 or 3 region databases who are using almost the same version of SQL Server 2008 R2, have exact tables and most stored procedures for our end users reports.. in the process, we are trying to get rid of all unused objects that may have been created for testing purposes to limit what we see in the production database. So there are 5 or 7 tables that the previous DBA created but is no longer in use (at least the front end application is not using it anymore), how can I make sure that there are nothing else is depending on these tables, triggers or SP?

Is there a checklist for me to use before I attempt deleting them other than backups, like when was the last time was used, by whom, etc...?


texts:
Actually I am bit confused with my database export, while i exporting it shows "Total estimation using BLOCKS method: 20.83 GB" But my DB export dump is only 6 GB. Few days back we deleted some unwanted files and data from our database. But I am not getting idea about this huge difference. Also, my export dump file is not showing any kind of error messages in the log files.

After importing this dump on an server and check DBA segemt using below cmd

"SELECT OWNER, SUM(BYTES)/1024/1024 SIZE_MB FROM DBA_SEGMENTS
group  by owner; "

data seems to be fine but the size shows a large difference.

Please advice if any body have idea on this?


texts: Conceptual Data Model match MySQL script?

texts:
Could someone tell me if my Conceptual Data Model (MCD) matches my MySQL script?

Can cardinality (1,4) exist?

MCD:

tbl1 ---(1,4)---(relation)---(1,1)---tbl2

MySql Script:

CREATE TABLE tbl1(
    idTbl1    Int  Auto_increment  NOT NULL
    [...]
    ,CONSTRAINT tbl1_PK PRIMARY KEY (idTbl1)
)ENGINE=InnoDB;

CREATE TABLE tbl2(
    idTbl2    Int  Auto_increment  NOT NULL
    [...]
    ,CONSTRAINT tbl2_PK PRIMARY KEY (idTbl2)
    ,CONSTRAINT tbl2_tbl1_FK FOREIGN KEY (idTbl1) REFERENCES tbl1(idTbl1)
)ENGINE=InnoDB;


texts: MySQL large number of opened tables -- we're noticing performance hits

texts:
We have 3 database servers, containing 200, 200 and 146 databases respectively. Each database contains about 250 tables. These definitions are the same for 99% of the databases - dependent on the version of software each customer is running on. We've been running this since over a week, and performance was great. It's dropping now, and we're not sure where to look.

Output from the first SQL:

> SHOW GLOBAL STATUS LIKE 'open_%'
>   -> +--------------------------+----------+
    -> | Variable_name            | Value    |
    -> +--------------------------+----------+
    -> | Open_files               | 1        |
    -> | Open_streams             | 0        |
    -> | Open_table_definitions   | 633      |
    -> | Open_tables              | 400      |
    -> | Opened_files             | 5078508  |
    -> | Opened_table_definitions | 1905982  |
    -> | Opened_tables            | 8910560  |
    -> +--------------------------+----------+

show variables output: https://gist.github.com/Ma-ve/453c80de33cb55064bd1dcb142fa83c4

Server has 16 GB of RAM, 8 vCPU. Any other specs can be requested.

edit: Full output from SHOW STATUS; as of a few minutes ago: https://gist.github.com/Ma-ve/ecc8ac4e67965d5da4f5de0a58436263#file-status-log-L3

Full output from SHOW VARIABLES; as of a few minutes ago: https://gist.github.com/Ma-ve/d0ba67695ef6d5b1a2020648250ded89

Server details:

16 GB RAM, a DigitalOcean Droplet (regular droplet)
120 GB disk
Output du -sh /var/lib/mysql: 19G
Output uptime: 11:26:46 up 270 days, 23:37,  3 users,  load average: 1.20, 1.39, 1.49
vm.swappiness = 0

Bit of history behind the query cache: we had it disabled when we had only 15 clients on a server, and performance was godawful. Enabling it (as it was enabled on our old server) instantly solved a lot of performance issues.


texts: What are the real life uses of role based authentication control in a database?

texts:
I am new to database management. I was going through the mongodb docs for Role Based Authentication Control. I have understood how it works, but I have a few questions.

How is it applied in real life scenarios? For example let's say I am running a blogging web app. How would I use Role Based Authentication Control to improve my database management?


texts:
Mysql-5.6.30 running on Debian, uses InnoDB, DB size about 100Gb. Dual Xeon 5645 with 48Gb RAM, 4 HDD 15K in RAID 10 (/var) and system on RAID1

There're many "heavy" ORDER BY/GROUP BY, JOIN/sort queries, they create many TMP tables on HDD. And, OFC, there're many "unoptimal" queries, which doesn't use indexes and also creates TMP tables.

How can I determine good values for sorting/grouping? 'Cause now these kind of queries extremely slow. Reading tons of articles, using optimizing scripts like mysqltuner only made things confusing about value settings...

[mysqld]
port = 3306
connect_timeout=100
expire_logs_days=7

innodb_log_buffer_size = 8M
innodb_log_file_size = 900M
innodb_file_per_table
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 6
innodb_lock_wait_timeout = 200
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 8

join_buffer_size = 2M
key_buffer_size = 384M
max_allowed_packet = 200M
max_heap_table_size = 64M
open_files_limit = 10240
query_cache_limit = 2M
query_cache_type = 1
read_buffer_size = 2M
read_rnd_buffer_size = 14M

socket = /var/run/mysqld/mysqld.sock
sort_buffer_size = 48M
table_open_cache = 640
thread_cache_size = 32

### tmp_table_size = max_heap_table_size
tmp_table_size = 64M
thread_concurrency = 0

##### Replication #####
server-id= 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 5
max_binlog_size = 1024M
##### End replication #####

##### Logs #####
slow_query_log=1
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1
##### End Logs #####

query_cache_size = 96M
max_connections = 300
max-connect-errors = 1000
skip-name-resolve

Especially sort_buffer_size, join_buffer_size, tmp_table_size. And, any recommendations about config will be good and pleased :)

UPD: running next script:

SET @SleepTime = 300;
SELECT variable_value INTO @SMP1
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SELECT SLEEP(@SleepTime) INTO @x;
SELECT variable_value INTO @SMP2
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SET @SMP = @SMP2 - @SMP1;
SET @SMP_RATE = @SMP * 3600 / @SleepTime;
SELECT @SMP,@SMP_RATE;

gave @SMP=24, @SMP_RATE=288


texts:
I want to know if there is some view to track the number of processes limit (aka: ulimit -Su) on an OEM view.
I found on the OMR repository  the view CM$MGMT_EM_OS_ULIMITS which show value for file descriptor but nothing about process limit. I'm sure there has one.
An idea about that?
Thank in advance


texts:
4 years ago, I answered a question on SO, "how to set an UUID for each row in bulk".
I answered, after trying it,
UPDATE table SET uuidcol = UUID();

It worked for me, but about half (it seems) people report that it doesn't work, saying the ID is identical on all rows.
So today, I tried again, dropping each time the column, and creating a new one with a new type, like
text, char(36), varchar(36)

and it worked again, making a unique UUID per row, each time.
What could make that it does not work for other people?
The engine for instance? ( using InnoDB on MySQL 5.7 )
edit
Did another test by

creating a simple table t of a uu varchar(36)
inserting 10,000,000 lines with uu being null
did the UPDATE t SET uu=UUID();

and all of the 10M lines have a different, unique UUID.


texts: Second Instance Installation of PostgreSQL?

texts:
Relating to original question posted on Stack Overflow here.

Interested in distributing a copy of PostgreSQL with a C# app (for Windows). However, trying to understand how the following scenario should be handled:

PostgreSQL already installed on the user's system

From my understanding, should be possible to install a 2nd instance of PostgreSQL to run at the same time on the same computer system (as a different service, bound to a different TCP port #?). However, the closest answer for how this would be done was found here, which does not really provide a lot of detail.

So far, in my test environment (VM), I have one instance of Ver. 12 PostgreSQL installed in the default directory C:\Program Files\PostgreSQL\12.

Am I correct to understand that I should copy all of the contens of the C:\Program Files\PostgreSQL\12 directory and copy/paste to a different directory (e.g. C:\Program Files\PostgreSQL\Test12?

Then, run pg_ctl register, but with what options?

And, then after, I should run pg_ctl start?

Are there any considerations that should be made before all of this is done?


texts: SQL Server avoid repeating the collation type multiple times in a query

texts:
I have a query which selects rows from a source database (DatabaseA), and inserts them into a target database (Database B). The collation type differs between the databases and they cannot be changed. I need to address the collation difference in my query by explicitly specifying the collation for varchar fields.

Currently my query looks like this:

INSERT INTO DatabaseB.dbo.Users(
    Id,
    UserNumber,
    FirstName,
    Surname,
    Address1,
    Address2,
    AddressTown,
    AddressCity
)
SELECT
    Id,
    UserNumber,
    FirstName COLLATE SQL_Latin1_General_CI_AS,
    Surname COLLATE SQL_Latin1_General_CI_AS,
    Address1 COLLATE SQL_Latin1_General_CI_AS,
    Address2 COLLATE SQL_Latin1_General_CI_AS,
    AddressTown COLLATE SQL_Latin1_General_CI_AS,
    AddressCity COLLATE SQL_Latin1_General_CI_AS
FROM DatabaseA.dbo.Users

My question is, can I avoid typing the collation type for every string-based field? Is there a way that I can specify the collation type for the whole query at once? If this is not possible, are there any other shortcuts?


texts: Can somebody help me normalise this database/check my workings

texts:
I am new to database design/normalisation and functional dependencies. I have started a new database class in school this week and we have been given a worksheet that requires the following:

This is a real simple design, and pets with the same type have the same details just to keep things nice and simple.

show the un-normalised structures
identify the primary keys on these un-normalised structures
specify function dependencies between attributes
bring structures up to 3NF showing workings

These are the tables:

My un-normalised structures are as follows:

person(PersonName, Age, Height, Weight, Pets, Sex)
Pet(petType, petName, Age, Colour, Sex, Alive)

Bold shows the primary keys.

Functional dependencies:

personName -> {Age, Height, Weight, Pets, Sex)
petType -> {petName, Age, Colour, Sex, Alive}

I cant think of any more (if any).

Now bring to 3NF.

I can get it to 1NF (i think)

So now I have:

Person(PersonName, Age, Height, Weight,Sex)
Pet(PetType, PetName, Age, Colour, Sex, Alive)

PersonsPet(PersonName*, petType*)

Then I have no idea where to go from here? That is assuming I have been correct until this point, which I most probably have not.

Can somebody help me and point me in the right direction?


texts:
I am currently studying for my MTA Database Fundamentals cert, and I came across this question during my studies.  However, the answer doesn't make any sense to me.  Here is the question/answer:

It is my understanding that 'cardinality' defines the relationship of the database entities, such as if the relationship is 1:1, 1:many, or many:many.  How does removing a row affect the cardinality of this database entity at all?

Question/Answer source is from EnsurePass.


texts:
I can see when a table is created, modified or dropped in the Schema Change History report.  Or by reading the default trace.  But I have not been able to find any record when a table is renamed.  Where might this information be stored?

I did look at modify_date in sys.all_objects.  It returns a row for the renamed table.  My hope was to see some kind of log entry that indicated there was a name change.


texts: Can anybody resolve the issue where I am trying to restore a database to a SQL Server 2008 server from a backup file taken from a SQL Server 2008 R2 server?


texts: Records in table not showing on form Access 2007

texts:
Excuse my ignorance in asking this question - I am a database novice and need help!

I will explain as best I can.

In the table on my DB I have 235 records.  I have created a form which references that table and which is pulling through only 226 of those records - for some reason it will not display the last 9.

What have I done wrong?

I input all the data using an import so I did them all the same way.

Thanks and please let me know if I have not made sense I can explain further!


texts:
How does psql remote connection work (default configuration)? Does it make use of ssh or any other protocol?

I am using linux machine (centos 6.8) and postgresql9.5 version.


texts: The Windows Server Failover Clustering (WSFC) resource control API returned error code 19

texts:
What is root cause of this error. I couldn't find any related information.
There are two nodes on Availability Group and if you reboot second node, database wont up.


texts: Security considerations of allowing logical replication subscribers on PostgreSQL

texts:
I run a non-profit dedicated to sharing data kind of like Wikipedia.

We recently had a client that wanted a replica of our database, and we realized that by using PostgreSQL's new logical replication we can replicate tables of our DB to a server they control.

This would be great for fulfilling our mission of sharing the data. It's 100× better than providing slow APIs.

We created a role for them like this:

CREATE ROLE some_client WITH REPLICATION LOGIN PASSWORD 'long-password';
GRANT SELECT ON TABLE some_table TO some_client;

And we created a PUBLICATION for them like this:

CREATE PUBLICATION testpublication FOR TABLE ONLY some_table;

Is there any risk of doing this? My analysis is that this gives them SELECT access to a table that they're replicating to their own server, but that's it. Any other concerns? If there are concerns, are there ways to make this work? We have tables we don't want to share, but most of our tables only have public data.


texts:
I am trying to run Oracle Express Edition on Windows 7 and getting the following error.

C:\oraclexe\app\oracle\product\1O.2.O\server\BIN>net start OracleXETNSListener
    System error 5 has occurred.
    Access is denied.
    C:\oraclexe\app\oracle\product\1O.2.O\server\BIN>net start OracleServiceXE
    System error 5 has occurred.
    Access is denied.
    C :\oraclexe\app\oracle\product\1O. 2 .O\server\BIN>_

How I can fix this?

Thanks.


texts: Postgresql connection refused from java connection class

texts:
public class PostgreSQLJDBC {
    public static void main(String args[]) {
        System.out.println("looking for db connection");
        Connection c = null;
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager //139.59.175.170 eller 127.0.0.1/32

                    .getConnection("jdbc:postgresql://178.62.33.183:5432/steinar");
        } catch (SQLException e) {
            System.out.print("sql exception thrown ");
            e.printStackTrace();
            System.err.println(e.getClass().getName()+": "+e.getMessage());
            System.exit(0);
        }
        catch (ClassNotFoundException e){
            e.printStackTrace();
        }
        System.out.println("Opened database successfully");
    }
}

So this is my simple connection class in java. My original error message complained that the ip-adress from my computer was not in the pg_hba.conf file:

FATAL: no pg_hba.conf entry for host "ip", user "user", database "db",

All the connection profiles are set to trust. Then i added it along with my computer username, and now i get the following error message:

Connection to 178.62.33.183:5432 refused.
Check that the hostname and port are
correct and that the postmaster is
accepting TCP/IP connections.

Port and listenadresses are set to 5432 and '*' in the postgresql.conf file

# IPv4 local connections:
host    all             myUserName         myIp            trust

Line i added in my pg_hba.conf file. Every time a change this file, psql command stops working:

psql: 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"?


texts: How to configure MySQL Router on two application servers?

texts: I have three MySQL servers running with group replication and inside a InnoDB Cluster. I have two app servers that will be connecting to the cluster and I need to configure mysql router on both app servers. When I run router on server1 I use the --bootstrap option. When I try to do it on server2 with --bootstrap I get an error "a router instance named has been previously configured on this host". My question is do I need to start mysql router with --bootstrap option? Or since it will be configured on two servers --bootstrap option is not required? Do I need to start mysql router with a personalized conf file?


texts: How can I restore a backup without overwriting ALL of the tables?

texts: I have a SQL Server database with 3 tables.  I need to restore a backup that contains 4 tables, while still keeping the old data in the original 3 tables.  Is this possible?


texts: To configure MySQL Enterprise Workbench for Incremental Backup with Online Backup option

texts:
Could not acquire management access for administration
RuntimeError:Could not initialize WMI interface: Workbench.wmiOpenSession():Could not connect to target machine.

When i am trying to configure MySQL Enterprise Workbench for incremental backup with (online backup) option then i am getting the above error.

I am able to take full backup from MySQL Enterprise Workbench tool. I am accessing MySQL Database through remotely. what i shall do in this scenario. In my Network Environment there is SSH tunnel as well as firewall.

Any one suggest to me.


texts: Not able to Connect to a named instance remotely

texts:
I have a server with 2 SQL Server instances, I can connect to the default instance remotely, however I am not able to connect to the named instance.

When I try to connect remotely, I get this error, SQL Server is configured to allow remote connection and the name is correct


texts: How to mount a pg_basebackup on a stand alone server to retrieve accidently deleted data

texts:
Had a bit of a brain fade the other day and deleted all the rows in one of the tables in our database and didn't realise until a couple of hours later.

I have a pg_basebackup file system copy created before that and have setup a virtual server with the same OS and same version postgresql server. I just want to have postgresql start and let me query the database to retrieve information from that one table. I do not want to use it for live data or have it try to update itself from the main server. I have tried but failed so must be missing something.

Can someone please give me a simple point by point on what I need to do or point me to some plain documentation that describes the process.

Jeff


texts: How to make a transition from SQL Server DBA to Oracle?

texts:
As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do?

I would assume the concepts are the same and the difference is merely the programming language but I have not seen the other side of the door.


texts: How does compound index help speed up query?

texts:
Say we have a bunch of column in mysql table.

Latitude Longitude

Say we make an index (Latitude and Longitude)

Well, the index will sort by Latitude first and IF the Latitude is the same it'll sort by Longitude.

Something fishy should show up. Latitude is rarely, if ever, exactly the same.

Say we want to find all points whose Lat,Long is "in a box" How in the earth those indexes can be useful at all.

Note: I am trying to understand how indexing work. I am not trying to snobbishly say that compound index is stupid. Doesn't have to be full answer. Any pointing is fine.

More importantly, I am suspicious that a query will only use 1 index and the only way for a query to use multilpe index is to make compound index.


texts: Oracle 11G Startup failed after altering the number of system processes

texts:
When I alter the number of system processes to 100000 and restart the database, I get the error below:

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semids_per_proc failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: sskgpwcr2
ORA-27303: additional information: semids = 524, maxprocs = 100000

How can I return it to the normal processes?


texts:
I'm trying to forcibly drop a PostgreSQL 9.4.4 database from a Bash script using psql:

#!/bin/bash
db="mydb"
psql -d $db -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$db';"

I'm using the solution shown in this question but it doesn't work. What I see is this:

FATAL: terminating connectoin due to administrator command
server closed the connection unexpectedly
         This probably means the server terminated abnormally
         before or while processing the request.

What am I doing wrong? I own the database and my user role has the "Superuser" attribute as shown when I do \du from the Postgres console.