texts:
We have an RDS instance with about 200G of data in it.  A few issues cropped up on our end, and we need to both change the collation and charset of all our tables, as well as have all the data encrypted at rest.  RDS supports encryption, but you can't enable it on an already running instance, and can't restore un-encrypted snapshots.

That said, my current strategy is the following:

Create a new RDS instance with encryption enabled, and with the right charset/collation enabled in the associated ParameterGroup.
Take our application down, and start dumping the data from the old instance into the new instance with something like:

mysqldump -C -h xxxxxxx.rds.amazonaws.com --routines --triggers --single-transaction --quick --add-drop-database --default-character-set=utf8mb4 --disable-key -u user -pxxxxxx --ssl_ca xxxx mydatabase | mysql -u user -pxxxxx -h xxxxxx.rds.amazonaws.com --ssl_ca xxxx mydatabase

After data is in the new RDS instance, make all the necessary collation/charset changes to the database itself, tables, columns, change varchar sizes where needed, etc.

For the database:  ALTER DATABASE mydatabase CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci

For each table:  ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

... etc

My question is... is there any faster way to do this!?  I did a straight mysqldump from the primary database onto disk, and it took about 4 hours.  I haven't run all the ALTER TABLE changes yet (only on a small dataset), but I'm assuming those operations are going to take a ton of time.  All my tables are InnoDB tables if that helps.  I was looking at Percona xtrabackup, but realized I can't use that with RDS.

Any thoughts appreciated!


texts: "Force Encryption" vs "Force Protocol Encryption" in SQL Server

texts:
I'm trying to understand how the different settings affect the behaviour of my server-client communication. On the server I have installed a self-signed CA with an issued exchange cert which also has been configured to be used by my SQL service.

At first I'd like to see this connection fail when the root CA has not been installed on the client. But whatever I do it seem to work anyway.

To better understand why, I'm trying to list all options and what effect they should produce. But I'm not sure I have understood it correct ...

Can anyone please help me to correct and fill out the missing pieces here?

When enabling the "Force Encryption" setting on the SQL Server:

In practice this is the same as setting Encrypt=True;TrustServerCertificate=True; in my connection-string. The client have no say in whether encryption should be used or not and whether the server should be trusted or not.
This option can be used to encrypt individual service instances.
Supports self-signed exchange certs without CA.

When enabling the "Force Protocol Encryption" setting on the SQL Server:

All client connections to all services on the server are encrypted.
Requires an exchange cert issued by a trusted CA available on both client and server.

When using the "Force Protocol Encryption" setting on the Client:

This single client will force use of SSL and requires an exchange cert issued by a trusted CA available on this machine. Without it this connection will fail.

When enabling the "Force Protocol Encryption" setting on both Server and Client:

This is not recommended. But why? What happens and what will fail?

When enabling both "Force Encryption" and "Force Protocol Encryption" on the Server:

What will this yield? Does it matter what Force Encryption is set to when Force Protocol Encryption is enabled?


texts:
How much "sleep process" is considered bad for a MySQL DB instance?

I'm wondering if the high CPU load usage I see could be caused by the sleep processes.

The DB is an "db.r3.xlarge" instance in AWS.


texts: Is it possible to see which SPID uses which scheduler (worker thread)?

texts:
We recently had a problem on our SQL Server 2014 HADR environment, where one of the servers ran out of worker threads. Got the message:

  The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread because there are not enough available worker threads

While we were able to "solve" the problem by moving one of the Availability Groups to another server, I was wondering if it is possible to see which queries run on which scheduler (or worker, or task).

With the following query I'm able to see how many workers are available, in use and waiting for resources:

declare @max int
select @max = max_workers_count from sys.dm_os_sys_info

select
    @max as 'TotalThreads',
    sum(active_Workers_count) as 'CurrentThreads',
    @max - sum(active_Workers_count) as 'AvailableThreads',
    sum(runnable_tasks_count) as 'WorkersWaitingForCpu',
    sum(work_queue_count) as 'RequestWaitingForThreads' ,
    sum(current_workers_count) as 'AssociatedWorkers'
from
    sys.dm_os_Schedulers where status='VISIBLE ONLINE'

And with the following query, I'm able to see which workers are running on which CPU (core):

SELECT *
FROM  sys.dm_os_Schedulers s --> Prozessoren Kerne
JOIN sys.dm_os_workers w ON w.scheduler_address = s.scheduler_address
JOIN sys.dm_os_tasks t ON t.task_address = w.task_address
WHERE s.status = 'VISIBLE ONLINE'
AND s.cpu_id = 2

Is there some way to find which SPID (and in the end which query) is running on which thread?

I was already searching for a while and found some interesting information about the connection between schedulers, workers and threads, but nothing that really showed me if it is possible:

Understanding SQL Server Schedulers, Workers and Tasks by Daniel Farina
Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests – what does it all mean? by _Jo.Pi_

I would like to see which database uses so many worker threads. We have a few databases which (in my opinion) do not belong on the production server. When I check sys.dm_exec_requests there seems not much going on.

The environment has run in the same configuration for more than 1 year without problems. The server in question has 24 CPUs and 5 AGs on it, with a total of 325 databases. 3 AGs are primary. To work around the issue we failed an AG with 50 databases from that server over to the secondary.


texts: How to know which query is resulting in errors in the trace?

texts:
So, I got this:

A simple trace with user errors.

But, how can I know the query that has the problem listed here?

I don't think DBCC INPUTBUFFER(X) is appropriated to this.

EDIT 1:

Well, I'm using:

STORED PROCEDURES:

RPC: COMPLETED
RPC: STARTING
SP: COMPLETED
SP:STARTED

TSQL:

SQL:StmtCOMPLETED
SQL:StmtSTARTED

And I can see this now:

is there a better approach for this?

I could find the query, because it was the only statement using TOP.I think it was luck.


texts: How to sort category by path and sort order?

texts:
first of all I am sory for my english.

My category structure is shown below. My purpose is to get category structure with single query.
I sort my table by path and I get category tree.

sort_order : Indicates sort order between categories with same level

When I run query

SELECT * FROM category order by path;

RESULT:

But result must be as shown below  because sort_order of Electronic is 1 and this is less than sort_order value of Computer category 2

 - ELectronic
 - - TV
 - - - LCD
 - - - - LED LCD
 - Computer
 - - Laptop

EDIT :

For @adopilot

Query : SELECT * FROM category order by level, path;

RESULT:


texts: How to access another db from a stored procedure in db2

texts:
I want to query a table in another db from within a stored procedure in db2. For example, I am connected to db_a1, and call a stored procedure from within which I need to connect to a table in db_a2.

Is this possible in db2 ?


texts: Postgres 9.4, replication slots, doesn't work failover master

texts:
I've installed postgres master and slave nodes by this manual:

At the master (10.0.0.1):

sudo -u postgres initdb -D /data/postgres/main

Change postgresql.conf:

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 2
logging_collector = on
max_wal_senders = 5

Add record for replication to pg_hba.conf:

host replication replica 10.0.0.2/32 md5

Then start postgres

service postgresql start

Create replication user:

CREATE USER replica WITH REPLICATION ENCRYPTED PASSWORD '123456';

And add replication slot:

SELECT pg_create_physical_replication_slot('standby_slot');

At the slave (10.0.0.2):

pg_basebackup -h 10.0.0.1 -U replica -D /db/postgres/main -X s -P

Change state to standby in postgresql.conf:

hot_standby = on

Add recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host=10.0.0.1 port=5432 user=replica password=123456'
primary_slot_name = 'standby_slot'
trigger_file = 'trigger'

Start standby:

service postgresql start

So, replication is working fine. If I create database, it will appear both at the master and the slave.

If slave is going down and then starting, all changes come there. It all fine too.

But I have problem if master is down. When it stops, I create trigger file at the slave. Then I create another database at the slave, which now is primary. After that I start master, trigger file remove automatically, recovery.conf rename to recovery.done automatically too. Ok. But how to synchronize changes from slave (new primary) to master (old primary)?

I found that I've got to do these four steps:

At the master:

Stop postgres:

service postgresql stop

At the slave:

Switch backup mode on:

SELECT pg_backup_start('backup', true);

Then rsync new data from slave to master.

And then switch backup mode off:

SELECT pg_backup_stop();

After last step I get message

NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup

And then if I start master it doesn't start with log message:

FATAL: could not locate required checkpoint record

I don't understand, how to get new data at the master and make it primary again.

So at the future I plan to install pgpool2 for balancing and failover, but test installing did not work because I don't know, how this mechanism should work.


texts: Remove while loop from query inserting x values

texts:
I want to make this query set based and remove the while loop for inserting.

I am passing in a TVP to the stored procedure with the type and quantity required. Because TVP needs to be READONLY, I am copying to a temp table to do the delete in the while loop. I have control over the way the data is passed in so could change the TVP if required.

CREATE TYPE WidgetsUDT AS TABLE(
    [WidgetTypeId] [int] NOT NULL,
    [Required] [int] NOT NULL
)
GO
CREATE TABLE WidgetOrder (Id int, WidgetTypeId int, OrderId int)
GO
CREATE PROCEDURE [BuyWidgets]
@Widgets [WidgetsUDT] READONLY
AS
BEGIN
SET NOCOUNT ON
DECLARE @WidgetTypeId int, @Required int
CREATE TABLE #TempWidgets (WidgetTypeId int, Required int)
INSERT INTO #TempWidgets SELECT * FROM @Widgets

WHILE EXISTS(SELECT * FROM #TempWidgets)
BEGIN
SELECT TOP 1 @Required = Required, @WidgetTypeId = WidgetTypeId FROM         #TempWidgets

;WITH CTE AS
(
SELECT TOP (@Required) *
FROM WidgetOrder
 WHERE OrderId IS NULL AND
 WidgetTypeId = @WidgetTypeId
)
UPDATE CTE SET OrderId = 100

DELETE FROM #TempWidgets WHERE WidgetTypeId = @WidgetTypeId
END

DROP TABLE #TempWidgets

    SELECT * FROM WidgetOrder
END
GO

INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (1,1,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (2,1,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (3,1,1)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (4,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (5,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (6,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (7,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (8,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (9,2,2)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (10,3,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (11,3,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (12,3,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (13,3,3)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (14,3,3)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (15,3,3)

DECLARE @MyWidgets WidgetsUDT
INSERT @MyWidgets (WidgetTypeId, Required) VALUES (1,2)
INSERT @MyWidgets (WidgetTypeId, Required) VALUES (2,3)
INSERT @MyWidgets (WidgetTypeId, Required) VALUES (3,1)
EXEC BuyWidgets @MyWidgets

DROP TABLE WidgetOrder
DROP PROCEDURE [BuyWidgets]
GO
DROP TYPE WidgetsUDT
GO


texts: I have 2 databases that reside on the same server. I am trying to read static data from one database in another database using synonym. And this caused no issues since we are not even using link server or anything. But DBA seems to think this will cause huge performance issue since synonyms use tempdb to create object reference to the other database. I am not sure how this works? Can someone explain if there is really a serious impact and if really tempdb is used? I thought synonym just creates an object that refers to the database object directly with no tempdb involved since they are both on same server.


texts: In MySQL how do I grant a user access to a read replica only?

texts: I have some users that I want to grant read only access to my database so I setup a replica server, but I don't want to grant them access to the master. I'd assume that this is a common use case, but I've not been able to find out how to do this easily. The only thing I can think of to accomplish this is to restrict access by IP to the master server.


texts: How to join two table without getting duplicate from right table in Oracle

texts:
Tables Details:
CREATE TABLE Test2 (
  ID INT,
  Value INT,
  other INT);

CREATE TABLE Test1 (
  ID INT,
  TYPE INT,
  other INT);

INSERT INTO Test2 VALUES (123456, 5, 12);

INSERT INTO Test1 VALUES (123456, 00, 2);
INSERT INTO Test1 VALUES (123456, 01, 6);
INSERT INTO Test1 VALUES (123456, 02, 4);

INSERT INTO Test1 VALUES (987654, 00, 7);
INSERT INTO Test1 VALUES (987654, 01, 8);

INSERT INTO Test1 VALUES (456789, 00, 6);
INSERT INTO Test1 VALUES (456789, 01, 16);

This is the Query i m using to avoid duplicate from table Test
SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value
FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID
GROUP BY t1.ID, t1.TYPE, t1.other, t2.value
ORDER BY t1.ID ASC;

Query Result:
ID  TYPE    other   value
123456  0   2   5
123456  1   6   5
123456  2   4   5

Description:
Expected Result is , when i m fetching match records from tables. Should get all the records from left table(Test1) and avoid duplicate record from right table (Test2).
Expected Query Result are,
ID  TYPE    other   value
123456  0   2   5
123456  1   6
123456  2   4

SQL Fiddle Link
http://sqlfiddle.com/#!9/8953fc/29
Criteria
When there is ID match should get all matched records from left table (Test1) and from right table (Test2) should get distinct records.


texts: SQL ODBC silently truncating numeric data

texts: We have a middleware that replicates data from an Oracle (11g) database to a SQL Server (2012) using ODBC connection to the destination. One of the tables has a NUMBER data type on Oracle and given there is no exact equivalent on SQL Server, we have used DECIMAL (18,0) on the SQL Server side in hope of catching errors if the data actually has fractions. However we've noticed that replication works without any error while truncating the fractions from the numbers. We traced the queries coming from the middleware and there are bunch of sp_prepexec statements where parameters are defined as the target type (e.g. DECIMAL(18,0)) and values are already converted before reaching SQL server and hence no error despite having NUMERIC_ROUNDABORT ON on the SQL Server. Is this how ODBC supposed to work? Are there ways to force errors on these type of data type conversions?


texts: Error on simple Select statement: Subquery returned more than 1 value

texts:
When I query a table

select *
from tableName

it returns error msg

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

How is this possible I can get this error on simple selection of a table?

Table Definition:

CREATE TABLE [dbo].[TableName](
    [Column1] [int] NOT NULL,
    [Column2] [int] NOT NULL,
    [Column3] [int] NOT NULL,
    [Column4] [bit] NULL,
    [Column5] [numeric](15, 2) NULL,
    [Column6] [numeric](15, 2) NULL,
    [Column7] [datetime] NULL,
    [Column8] [datetime] NULL,
    [Column9] [varchar](5) NULL,
    [Column10] [varchar](5) NULL,
    [Column11] [varchar](15) NULL,
    [Column12] [datetime] NULL,
    [Column13] [varchar](5) NULL,
    [Column14] [varchar](17) NULL,
    [Column15] [varchar](256) NULL,
    [Column16] [numeric](18, 2) NULL,
    [Column17] [tinyint] NULL,
    [Column18]  AS ([dbo].[udf_ChargePointer]([Column1],[Column17])),
 CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].TableName  WITH CHECK ADD  CONSTRAINT [CK_TableName]
CHECK  ((case when len([Column11])>(2) AND isnumeric(replace([Column11],',','A'))=(1) then (1) else (0) end=(0)))
GO

ALTER TABLE [dbo].TableName ADD  DEFAULT ('01/01/1900') FOR [Column12]
GO

ALTER TABLE [dbo].TableName ADD  DEFAULT ('') FOR [Column13]
GO


texts: MySQL: Updating large data set including time aggregation

texts:
I am having a large MySQL (5.7) table with millions of rows (contains data for each second). Based on these values calculations should be performed, in some cases for large data ranges. Therefore, I want to perform a preprocessing where I perform the calculations beforehand and store the result in a separate table. Due to some internal reasons, aggregations are performed so, for example, I need the avaerage value of the formula for one hour. Here is an example:
CREATE TABLE `datavalues` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `ddate` DATE NOT NULL,
    `ttime` TIME NOT NULL,
    `unixtime` BIGINT(20) NOT NULL,
    `value1` DOUBLE,
    `value2` DOUBLE,
    PRIMARY KEY (`id`),
    INDEX `unixtime_idx` (`unixtime`)
);
CREATE TABLE `calculation_result` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `ddate` DATE NOT NULL,
    `ttime` TIME NOT NULL,
    `unixtime` BIGINT(20) NOT NULL,
    `result1` DOUBLE,
    PRIMARY KEY (`id`),
    INDEX `unixtime_idx` (`unixtime`)
);

As an exemplary calculation I calculate value1-value2. The initial data query for filling the calculation table with one value for each hour could look like this:
INSERT INTO calculation_result (ddate, ttime, unixtime, result1)
select max(ddate), STR_TO_DATE(TIME_FORMAT(max(ttime),'%H:00:00'),'%H:%i:%s'),
UNIX_TIMESTAMP(adddate(max(ddate), INTERVAL HOUR(max(ttime)) HOUR)), AVG(value1 - value2)
FROM datavalues GROUP BY ddate, hour(ttime);

So far so good. However, I am struggling to implement a query for updating the result1 column in the calculation_result table in case the calculation description changes, for example, from value1 - value2 to value1 + value2. How can I efficiently update the result1 column? Deleting and recreating the calculation table is not an option as it contains a lot of further calculations in different columns. Additionally, I expect the updating process to take some time. As data is newly imported on a regular basis, how can I prevent the calculation_result table to be locked? Maybe using bulk updates of data chunks?
Thanks for your help.


texts: How to check foreign keys related to a table

texts:
How to see foreign keys related to a table in MySql?

Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this:

Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails

  How can I drop foreign keys related to the table leaving others.


texts: Revoke database-level privileges without the need for "use db_name;" statement

texts:
I have a MySQL user with database-level privileges.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON mydb.* TO 'theuser';

The user can create tables and insert rows into them.

When the user exceeds the limits, for example, the number of allowed rows in his plan, I want to revoke the INSERT privilege. So the user cannot insert new rows into the tables anymore. It's a plan with soft limits.

I know I can execute the following query:

REVOKE INSERT ON mydb.* FROM 'theuser';

But it doesn't affect until the next use mydb.

According to MySQL docs:

  A grant table reload affects privileges for each existing client connection as follows:

  Table and column privilege changes take effect with the client's next request.
  Database privilege changes take effect the next time the client executes a USE db_name statement.
  Global privileges and passwords are unaffected for a connected client. These changes take effect only for subsequent connections.

So my problem is how can I force MySQL to reload database-level privileges without the need for "use db_name" statement, just like table-level privileges.


texts: Column A or Column B required MS Access DB

texts:
I'm in the situation where I would like to enforce the requirement where a record must contain either one column or another at a data level, I'm not sure whether it's achievable.

I've got an Enquiry table which is used for new enquiries being recorded; this can either be based on an existing customer, which is contained within the Customer table or prospective customer which will be created and stored within a ProspectiveCustomer table.

 Enquiry
-----------------------------------------------
 EnquiryID  | CustomerID | ProspectID | Notes |
       3    |     2      |     null   |   x   |
       4    |     null   |     3      |   y   |

There must be a record number in either CustomerID or ProspectID, but not in both. Is this achievable at a data level alone? or would I have to simply validate my inputs in this way and hope for the best?


texts: Different results for logically same, but textually different queries

texts:
Is there any reason why the following queries would return different results?

select count(id)
from mytable
where
  status='active'
  and ((textfield1 ilike '%needle%') or (textfield2 ilike '%needle%'));

vs

select count(id)
from mytable
where
  status='active'
  and ((textfield1 || textfield2) ilike '%needle%');

When I'm running these queries, the former is returning 26 and the latter 19.

What idiotic mistake am I making?


texts:
I use Ubuntu 16.04 and MySQL 5.7.16 and trying to connect from Windows10 with Workbench; My aim is to be able to login from any computer, anywhere --- I have no intention to allow only one IP.

I get failed connect after filling credentials:

method    | TCP/IP
hostname  | IPv4
port      | 3306
username  | I use the MySQL root user (the one I used to login to PHPmyadmin with)
password  | MySQL root password.

The error is:

  Is not allowed to connect to this MySQL server

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 0.0.0.0
# 127.0.0.1

Why it's not likely a firewall issue:

Given I use CSF-LFD as firewall software, I don't use the native UbuntuFireWall (UFW). Yet, I did allow port 3306 ongoing and outgoing in csf.conf, and restarted CSF via sudo csf -r.

netstat -plnt:

    tcp    0    0 0.0.0.0:3306    0.0.0.0:*    LISTEN -

My question:

Given these circumstances, what else can prevent my remote login?


texts: Return Output from SQL Function using command line SQLCMD

texts:
I have a SQL Function that returns an Integer as the Output. I need to execute it via sqlcmd and I only want to get it's integer output.

However right now I'm getting back this type of output:

------------
      25

<1 rows affected>

How can I get my output to be just 25 with no other text.

Here's the Call I'm using:

sqlcmd -S MyServer\Instance -d SQLAdmin -Q "SELECT dbo.UDF_INT_Get_Status ('INT_02')"


texts: Why can't I take RDBMS Database Design practices, and use for NoSQL MongoDB Database Designs?

texts:
Let's say I have a  BankAccount table, and a BankAccountHistoryTransactions table.

When it comes to RDBMS database schema design, most Database designers would recommend something like the following:

BankAccount Table

int: BankAccountNumber Primary key

double: CashBalance

......

..

Also, in an RDBMS Database Design, the BankAccountHistoryTransactions Table would be like:

BankAccountHistoryTransactions Table

int: BankAccountHistoryTransactionsId Primary key

int: FK_BankAccountNumber Foreign key

DateTime2: DateOfTransaction

.................

.........

In NoSQL MongoDB database schema, it would b more like a BankAccount collection containing an embedded BankAccountHistoryTransactions collection:

db.BankAccount.find().pretty()
{
    "_id" : ObjectId("51f7be1cd6189a56c399d3bf"),
    "BankAccountNumber" : "7575785885859",
    "CashBalance" : "890399",
....................................,
...............................,
.......................,
    "BankAccountHistoryTransactions" : {
        "_id" : ObjectId("51f7be1cd6189a56c399d3bf"),
        "BankAccountHistoryTransactionsId": 1,
        "DateOfTransaction" : ISODate("2019-12-31T23:00:00Z")

}
}

My problem with the NoSQL MongoDB Database Schema design approach is that a Bank Account could have a tonne of BankAccountHistoryTransactions entries (possible going into hundred thousands of BankAccountHistoryTransactions entries for a Bank Account).

Therefore, wouldn't it be better if we used a pseudo-foreign key relationship like in the following:

    db.BankAccount.find().pretty()
    {
        "_id" : ObjectId("51f7be1cd6189a56c399d3bf"),
        "BankAccountNumber" : "7575785885859",
        "CashBalance" : "890399",
    ....................................,
    ...............................,
    .......................,
}

And a different separate collection for BankAccountHistoryTransactions

db.BankAccountHistoryTransactions.find().pretty()
    {
        "_id" : ObjectId("51f7be1cd6189a56c399d3bf"),
        "FK_BankAccountNumber" : "7575785885859",
"BankAccountHistoryTransactionsId": 1,
            "DateOfTransaction" : ISODate("2019-12-31T23:00:00Z")

    }

I've heard that NoSQL MongoDB database designers Discourage the use of pseudo-foreign key relationships like the one above.  However, wouldn't it be much more organized, and modular to have a like the "pseudo-foreign key relationship" design?  ( Correct me if I'm wrong but Performance might be a problem, but it's certainly more organized and modular )


texts: Quick and easy ways to check SQL/DB performance

texts:
We host a web application for customers, sometimes they call in and complain of slowness. I was asked to have a list of things to check from network down to the application.

For SQL, can any suggest any quick queries that can be run or any good commercial tools that could be purchased that would quickly determine if SQL is the root cause or is otherwise healthy?


texts: What is the best sql data type for x.x.x


texts: how to reduce transaction log size automatically?

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

how can i reduce the size of Transaction log programatically and automatically, because no one knows when the when it gets full since how can we manage this can any one suggest for this


texts: Don't see it : Integration Services Catalog, and open the [All Executions] report

texts:
I have SQL Server 2012 Enterprise Edition (64-bit) on Windows NT 6.2.
I am not seeing the option to the "[All Executions] report" in the Integration Services Catalog . How can I solve it?

Update: I have attached another image that shows that I do not have the same menu as in the answer by stacylaray.


texts: How to find when change tracking was enabled?

texts:
How can I determine when change tracking was enabled on a table in a SQL database?

Assume that this table has a regular cleanup routine, which means that looking at the oldest record in the change tracking table won't provide the right answer.


texts: Business Continuity Plan for web application and database

texts:
I have been asked by work to set up a business continuity/disaster recovery plan for two similarly implemented web applications and associated databases.

Not something I'm familiar with doing.

What are the best practice procedures for creating plans of this nature?


texts: Creating a stored Proc that creates new user and password

texts:
I'm new to MySQL. I was trying to create a procedure with two parameters:

user_name
password

...that will be generated and granted full rights:

DELIMITER $$

DROP PROCEDURE IF EXISTS `gen_user`$$

CREATE PROCEDURE `gen_user`(`user_name` VARCHAR(10), `keyword` VARCHAR(255))
BEGIN
   CREATE USER `user_name`@'localhost' IDENTIFIED BY `keyword`
   GRANT ALL PRIVILEGES ON * . * TO `user_name`@'localhost'
   FLUSH PRIVILEGES
END$$

DELIMITER ;

The error I get is:

  Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`keyword`    GRANT ALL PRIVILEGES ON * . * TO `user_name`@'localhost'    FLUSH P' at line 3


texts: Why do my backup policies fail when a database's guest user is disabled?

texts:
I'm attempting to implement Policy Based Management on a SQL Server 2012 instance also acting as a Central Management Server using the Enterprise Policy Management Framework. The job running the Powershell script has a login on all servers allowing it to connect and view all databases. It also has all related msdb roles on the CMS. However, the policy only executes against a database if its guest user is enabled. Instances with no databases with an enabled guest just return "No Target Found".

Am I missing something obvious, or do I have to give the agent account a user on every database on every instance?


texts:
I am running this simple query:

SELECT *
FROM ABC_TABLE
WHERE DAY_DATE >='1-JAN-2018' AND DAY_DATE<='31-DEC-18'

There are 64 columns and we need all of them.

There are a total of 2,066,381 rows for year 2018. Total row count is 2,549,803.

There is one index which has INVOICE_NUMBER, EMPLOYEE_ID, DAY_DATE, LOCATION_ID, ITEM_ID columns.

I am using SQL Server 2014.

I am unable to understand why it is taking more than half an hour.

select count(*) from ABC_TABLE was executed in a fraction of a second.

Even if I use few columns in the select statement it takes really long to respond like the below one (>3 mins):

select COUNTRY_ID,
EMPLOYEE_ID,
DAY_DATE,
LOCATION_ID,
CUST_ID,
ITEM_ID,
PROM_ID,
sum(s_cost)
from abc_table
WHERE DAY_DATE >='1-JAN-2018' AND DAY_DATE<='31-DEC-18'
group by COUNTRY_ID,
EMPLOYEE_ID,
DAY_DATE,
LOCATION_ID,
CUST_ID,
ITEM_ID,
PROM_ID


texts: mysql threads running high issue for Percona Mysql 8.0.16

texts:
we are experiencing a threads running high issue

mysql> show global status like '%buffer_pool%'
    -> ;
+-----------------------------------------+--------------------------------------------------+
| Variable_name                           | Value                                            |
+-----------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status          | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status          | Buffer pool(s) load completed at 191011  8:32:57 |
| Innodb_buffer_pool_resize_status        |                                                  |
| Innodb_buffer_pool_pages_data           | 7342440                                          |
| Innodb_buffer_pool_bytes_data           | 120298536960                                     |
| Innodb_buffer_pool_pages_dirty          | 1098108                                          |
| Innodb_buffer_pool_bytes_dirty          | 17991401472                                      |
| Innodb_buffer_pool_pages_flushed        | 32982303                                         |
| Innodb_buffer_pool_pages_free           | 13213728                                         |
| Innodb_buffer_pool_pages_LRU_flushed    | 0                                                |
| Innodb_buffer_pool_pages_made_not_young | 58099577                                         |
| Innodb_buffer_pool_pages_made_young     | 64937403                                         |
| Innodb_buffer_pool_pages_misc           | 415352                                           |
| Innodb_buffer_pool_pages_old            | 2710229                                          |
| Innodb_buffer_pool_pages_total          | 20971520                                         |
| Innodb_buffer_pool_read_ahead_rnd       | 0                                                |
| Innodb_buffer_pool_read_ahead           | 55106                                            |
| Innodb_buffer_pool_read_ahead_evicted   | 0                                                |
| Innodb_buffer_pool_read_requests        | 344963685851                                     |
| Innodb_buffer_pool_reads                | 6418361                                          |
| Innodb_buffer_pool_wait_free            | 0                                                |
| Innodb_buffer_pool_write_requests       | 17527245765                                      |
+-----------------------------------------+--------------------------------------------------+

Could anyone please advise?

Thanks


texts: MongoDB Cluster - Can't access member of shard with credentials

texts:
I have a Sharded Cluster and I am trying to access a specific member of a replica set within a shard.

When connecting to the mongos it is fine, but when using the same credentials on a member of the replica set I am unable to gain access even though my user has the role of root.

The error is:

  SCRAM-SHA-1 authentication failed for root on admin from client
  127.0.0.1:39356 ; UserNotFound: Could not find user root@admin

The mongos are started with:

mongos --configdb cfg/mongo-cfg0:27019,mongo-cfg1:27019,mongo-cfg2:27019 --bind_ip_all --keyFile "/etc/mongodb-keyfile"

The members of the replica set are started with:

mongod --auth --bind_ip_all --shardsvr --replSet a --smallfiles --oplogSize 50  --keyFile "/etc/mongodb-keyfile"

When connecting to the mongos I use the following locally:

mongo --port 27017 -u user -p password --authenticationDatabase admin

When connecting to the memnber of the replica set locally i try and use:

mongo --port 27018 -u user -p password --authenticationDatabase admin

I feel like there is something obvious that I am missing but I can not figure out what, any help would be greatly appreciated.


texts: What structure should be used to store data with multiple category identifiers?

texts:
I have a table within a MySQL database with various data in it (for the sake of example, let's give it the following:

ID   |   Place   |   Phone   |   Email   |   Category )

Now, I want to redesign the "Category" section to be able to include any given row in multiple (searchable) categories.

For example, let's say that there is a place called 'Lakeside Picnic Area.' Currently it is classified as 'Nature Reserve'. So, when people are searching for a nature reserve, it shows in their search results. However, I also want it to show up in "Food Area", "Wildlife", and "Viewing Point".

My question is this: How do I design my table/database structure to allow all of the data entries/rows to be searchable by multiple categories?


texts:
This is not a "group by" question. I am struggling with permissions in postgres because I apparently don't understand how group roles work. Searching for this on SO has proven difficult because I get buried in questions about group by, which isn't the problem.

I have been using postgres to manage a series of research projects. These databases are not connected to any web front-ends. I import all of the data from CSV. For the past few months, I have been the only user. This has worked really well. I am the db owner and nobody else needed to care. Recently, I built a complex database containing data from several sources. Because of the number of tables involved, each data source has its own schema and I have created a series of convenience views to make it easier to work with these various data sets. These schemas should be read only to all users, except for myself.

This project requires several other people to have access to this database. Each user has a username / password and an individual schema, where they can work without cluttering up the public schema. I want to be able to control access to the schemas such that the schemas holding the original data are "select only" to the other analysts. However, I want the all other schemas in the database to more or less function like the public schema in terms of access. These schemas exist for logical structure, not to control access. I want everyone to be able to read/write/create/drop/etc. in these other schemas that don't hold the original data.

Unfortunately, postgres roles are rather tricky or I've been rather slow to understand. Setting this up has been tricky as new tables are created by different users. I keep having to re-run the grant access commands manually as we add new tables.

The alter default privileges seems to imply that I can do this using group roles, but when I have tried to do so, other users were unable to access new tables. This seems to imply that I can set up group roles that will allow me to manage access, but I haven't succeed thus far:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

I'm just looking for some help to understand how to best utilize group roles to control access to schemas/tables/views in Postgres. I don't want to have to manually grant access every time I add a new table, or worse, manually grant access every time I add a new user. I would prefer to be able to add a new user to a larger group, that controls the access.

Based on comments given, I have tried to use the following syntax. In this example, there are two users, achoens (me) and mrubin (a co-worker). I can log in as both users. As achoens, I run the following:

create schema foo;
alter default privileges in schema foo grant select on all tables to public;
select 1 into foo.test;

I log off as achoens and log in as mrubin. I then run

select * from foo.test;

Which returns a permissions error. In this instance, Postgres 9.1 is running on a windows server but I tried it on my home server running Ubuntu and got the same results. I like postgres but this makes it much harder to use in a collaborative environment without just making everyone into an administrator, which I would prefer to not do.

I also edited the above syntax to reference against a user group called analyst, which both achoens and mrubin are members of. No difference. User mrubin was unable to read the silly little one row table.


texts: Is it possible to move the MySQL Table indexes to another location rather than "datadir" ? for example a ssd drive or memory partition ?


texts:
I have a, I think pretty common, schema:

samples <-> sample_labels <-> labels

Here's an SQL Fiddle. I would like to achieve a selection of all samples that have all of the tags specified.

Given parameters of:

label.key=narf, label.value=zord
label.key=foo, label.value=bar

I only would like to retrieve samples that are having both of these labels. A where of

-- will find nothing cause conditions can't be satisfied
WHERE
  label.key = 'narf' AND label.value = 'zord'
  AND label.key = 'foo' AND label.value = 'bar'

will yield 0 rows (seems clear enough because no label tuple can actually fullfill these conditions. Given this:

-- will find either of "narf=zord", "foo=bar"
WHERE
  label.key = 'narf' AND label.value = 'zord'
  OR label.key = 'foo' AND label.value = 'bar'

Will yield all rows in the SQL Fiddle example because (again, seems clear enough to me) every label in that case will fulfill the conditions.

Even worse is this:

-- will also find "foo=zord" or "bar=narf" -> unwanted
WHERE
  (l.key IN ('foo', 'narf')) AND
  (l.value IN ('bar', 'zord'))

My brain is in meltdown mode and I'm pretty close to filtering thru the results in my code. It seems obvious that I'm simpy not smart enough to get the query correct.  :(

What is the query to get only samples that are correlated to ALL the given labels?


texts: what is most efficient way to retrieve next and previous items based on ordering and current item only

texts:
So I have this problem in many of my apps and I don't even know how to describe it correctly.

I'm using MongoDB (3.6) so my IDs are hashes.

I have a table/grid view, which displays items mapped to database records, then when someone clicks on one you get the single-item view page. But then from there, I want to allow next and previous "pagination".

I quote "pagination" because the URL never contains a page number, and can't. The url only contains a direct link to an item.

For example: mydomain.com/posts/59118d075e04da6104421bb4

Let's say to keep it simple, items have a created_at timestamp and the ordering is always by created_at ascending.

You have 100 items, you land on item 54 out of 100. But you don't know that because the id is a hash. Maybe it was 54th in the ordering when you bookmarked it and now since more items have been added it's now 58th out of 104.

The API returns the data for that item, but then what happens when the user wants to navigate to the previous or next item. What's the most efficient way to handle that?

A very slow way would be to query ALL items ordered by created_at, then loop through the list until you find the one that matches the current ID. And then -1 +1 that would be your previous/next item.

And every time a user clicks the prev/next arrow you repeat this slow process.

I have the same problem in MySQL (5.7). I don't know how to say "given this ID, what is the prev/next records based on this ordering"

Another way I thought of doing this is storing a persistent array of all the IDs, and then getting the position for the item that matches a given ID, and then you could more easily get the IDs of items +1 -1 that position. That wouldn't work too well if you had a huge number of records though.

Is there a more efficient way? Hope someone can enlighten me.. thank you!


texts: OPENQUERY mismatching data in records after Upgrade ---update --Only on CLOB columns

texts:
We are having an issue with openquery() after our source database upgraded to ORACLE 19c.  We had no issues before.   However now when we query against the database using openquery the primary keys seem to be associated with the incorrect records.  IF we include a where clause in the openquery portion defining a particular ID (primary key) then we get the correct result.
I have queried the ORACLE db directly and the IDs/record associations are correct.  It is only when using openquery() and only after the 19c upgrade that this issue occurred.  Admin is stating we are using the correct ODBC driver.
Anyone else ever seen an issue like this?
Thanks!
------------------------------UPDATE 1 !!!!!!!!
Additional Info Added:
I looked a bit further and it looks like only CLOB column is being attached to the wrong record using openquery. The rest of the columns are correct.
Name       Null?    Type

REC_ID  NOT NULL VARCHAR2(8)
SEQ_NBR    NOT NULL NUMBER
BEGIN_DATE          CHAR(8)
END_DATE            CHAR(8)
RC_ID      NOT NULL VARCHAR2(4)
ID_DESC            CLOB
------------------------------UPDATE 2 !!!!!!!!
Our work around
We rolled back our ODBC driver and this fixed our issues.  We were also able to band aid a few fixes before the driver downgrade by casting the CLOB as a varchar in the OPENQUERY statement.


texts: Calling a function that creates records and referencing the created records in a join

texts:
I have a function that creates records for different tables that are referenced with each other. The function returns the record from the "parent" table. So I am using that function in the FROM of my query so that I can then perform JOIN on the associated records so that I can piece the tables together. My problem is that the associated records aren't getting returned and I know for certain that they are getting created.
From what I understand, FROM and JOIN are pretty much executed at the same time so it makes sense that the FROM's creation of the records that are used in the JOIN would not exist.
So how can I ensure that the call to the function is executed first before the JOIN is executed? I tried doing this with CTE but no avail.
SELECT
  i.invoice_id,
  i.invoice_date,
  i.invoice_due_date,
  i.created_by_id,
  i.currency_id,
  i.created_at,
  si.supplier_info,
  bi.billing_info
FROM invoices.create_shift_invoice(
  CAST(NULLIF($invoice_date, NULL) AS TIMESTAMP),
  CAST(NULLIF($invoice_due_date, NULL) AS TIMESTAMP),
  CAST(NULLIF($currency_id, '') AS INT8),
  CAST(NULLIF($created_by_id, '') AS INT8),
  CAST(NULLIF($supplier_info, NULL) AS JSONB),
  CAST(NULLIF($billing_info, NULL) AS JSONB),
  CAST(NULLIF($invoice_items_shifts, NULL) AS JSONB)
) i
JOIN (
  SELECT
    _si.invoice_id,
    json_build_object(
      'supplier_info_id', CAST(_si.supplier_info_id AS VARCHAR),
      'invoice_id', CAST(_si.invoice_id AS VARCHAR),
      'suppler_name', _si.supplier_name
    ) AS supplier_info
  FROM invoices.supplier_infos _si
) si USING (invoice_id)
JOIN (
  SELECT
    _bi.invoice_id,
    json_build_object(
      'billing_info_id', CAST(_bi.billing_info_id AS VARCHAR),
      'invoice_id', CAST(_bi.invoice_id AS VARCHAR),
      'customer_id', CAST(_bi.customer_id AS VARCHAR)
    ) AS billing_info
  FROM invoices.billing_infos _bi
) bi USING (invoice_id)

And here's my attempt at a CTE:
WITH create_shift_invoice AS (
  SELECT *
  FROM invoices.create_shift_invoice(
    CAST(NULLIF($invoice_date, NULL) AS TIMESTAMP),
    CAST(NULLIF($invoice_due_date, NULL) AS TIMESTAMP),
    CAST(NULLIF($currency_id, '') AS INT8),
    CAST(NULLIF($created_by_id, '') AS INT8),
    CAST(NULLIF($supplier_info, NULL) AS JSONB),
    CAST(NULLIF($billing_info, NULL) AS JSONB),
    CAST(NULLIF($invoice_items_shifts, NULL) AS JSONB)
  )
)

SELECT
  i.invoice_id,
  i.invoice_date,
  i.invoice_due_date,
  i.created_by_id,
  i.currency_id,
  i.created_at,
  si.supplier_info,
  bi.billing_info
FROM create_shift_invoice i
LEFT JOIN (
  SELECT
    _si.invoice_id,
    json_build_object(
      'supplier_info_id', CAST(_si.supplier_info_id AS VARCHAR),
      'invoice_id', CAST(_si.invoice_id AS VARCHAR),
      'suppler_name', _si.supplier_name
    ) AS supplier_info
  FROM invoices.supplier_infos _si
) si USING (invoice_id)
LEFT JOIN (
  SELECT
    _bi.invoice_id,
    json_build_object(
      'billing_info_id', CAST(_bi.billing_info_id AS VARCHAR),
      'invoice_id', CAST(_bi.invoice_id AS VARCHAR),
      'customer_id', CAST(_bi.customer_id AS VARCHAR)
    ) AS billing_info
  FROM invoices.billing_infos _bi
) bi USING (invoice_id)


texts: 1 big merge publication or N small ones?

texts:
Scenario

We have two servers, each one with its own Microsoft SQL Server

Serv_Home
Serv_Web

In both servers, a local program works with its own local database and inserts/updates the tables. The data is shared between the 2 servers, and for this purpose we have several replicas. Serv_Home is the master server (the distributor of the publications).

A specific table must be copied from Serv_Home to Serv_Web (not viceversa), so the publication is a Snapshot.

For all the rest, a Merge publication is used. We decided to create 5 Merge publications, one for each group of tables which are more or less related. The reason why we have 5 instead of a big single one is simply convenience. Everything works fine.

Lately though, we are experiencing a high CPU usage in Serv_Home (like 50% sometimes) due to the sqlservr.exe process.

Question

Is it better to have a big single Merge publication instead of N small ones? Could this be a performance bottleneck?

I know that the best way is to try it myself, but we are in a production environment and it would be messy to redo it all. Some documentation or good reasoning would be appreciated.


texts:
I have a table of "transactions" where each transaction has an amount:
http://sqlfiddle.com/#!15/42849/1

Records in the table are never REMOVE'ed or UPDATE'ed. Only new transactions are added.

I wish to calculate the SUM of the amounts. The calculation doesn't have to be 100 percent up to date for every request.

On a dataset of about a million rows this takes about 400 ms on my database. This is way too slow for my application and I am trying to find the best solution for speeding this up.

What I have tried so far

Materialized view: Adds complexity of having to have a cronjob running which updates the view every X seconds.
Caching on the application server: Every X request will be slow when the cache needs an update.
Storing results of queries on an old subset: Store the SUM of previous request and use these to calculate the correct total. Adds complexity.

Question

Does PostgreSQL provide a solution for speeding this type of query up?

Update 1

The SUM query is just a basic sum on a single column so I don't believe that this query in itself can get any faster. The solution is probably to do some kind of caching/precalculation or similar. Does PostgreSQL have any features in this regard?

Update 2

Table in question:

CREATE TABLE transactions
(
  id bigserial NOT NULL,
  amount bigint NOT NULL
);

Query in question:

SELECT SUM(amount) FROM transactions;

Update 3

I found that I actually need a "type" as well.

Updated table:

CREATE TABLE transactions
(
  id bigserial NOT NULL,
  amount bigint NOT NULL,
  type int NOT NULL
);

Updated query:

SELECT SUM(amount) FROM transactions GROUP BY type;

SQL Fiddle:
http://sqlfiddle.com/#!15/77e67/2


texts: Facing error while working with Aggregate function in MDX (MultiDimensional-Expresson)

texts:
Hi everyone hope you're doing well
I'm using Aggregate function to aggregate the value of my measure
for two specific years as you can see below :
select aggregate ({[DimTime].[Year].&[1396] ,[DimTime].[Year].&[1397] },[Measures].[IndentCount]) on 0 from [Procurement]

according to the fact that aggregate function accepts two parameters :
Aggregate (Set_expression , Numeric_Expression)

What is wrong with my MDX query?
I get this error :
The Axis0 function expects a tuple set expression for the  argument. A string or numeric expression was used.

Thanks in advance


texts: During installation can't change directory

texts:
I try to install mysql db server on windows 10.

Version mysql-installer-community-5.7.16.0.msi

During the installation Install MSI does not offer to change the installation directory.

Do I correctly understand that I can't change the installation directory during the installation process?


texts: Minimum requirements to run Sql Server BCP.exe utility

texts:
I am working with a client who needs to do a data import to a remote SQL Server (2008) instance. The machine that the import will be done from cannot have SQL Server installed on it. Can the BCP.exe utility run without a SQL Server install?

Thanks for any help.


texts:
I backup my SSAS cubes called "BI LIVE" and restore it to a specific location on a different server.

I am using sql server 2014.
This is where you can see the routine I use to run the backups:

How can I pass the server name as parameter for a XMLA script?

Another way to backup a SSAS Database can be found here:

How can I dynamically back up all SSAS databases on a given instance?

This is the script that I use to restore the backup of my SSAS database:

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <File>H:\SSASBackup\BI LIVE_2015-09-16_210100.abf</File>
    <DatabaseName>BI_Live_restored</DatabaseName>
    <DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">F:\SSAS\</DbStorageLocation>
</Restore>

The restore completes, but it shows me the following error messages:

    Executing the query ...

  READS, 0  READ_KB, 0  WRITES, 0  WRITE_KB, 0 CPU_TIME_MS, 0
  ROWS_SCANNED, 0

  ROWS_RETURNED, 0

        H:\SSASBackup\Boden BI LIVE_2015-09-16_210100.abf
        BI_Live_restored   F:\SSAS\
     Backup and restore errors: Backup storage file corruption:
    file not found. Possible cause of this error is corruption of backup
    file.

  Execution complete

However, when I compare the LIVE cube with the RESTORED one, I get exactly the same results, the restored cube is fully operational, I have not found any errors within it, so far.

This is one of the MDX scripts that I am using so far to check the restored ssas database:

SELECT {
              [Measures].[Units Sold]
              ,[Measures].[UK FSP]
              } ON 0
              ,
              {
              [Order Date].[Trading Calendar].[Trading Week]
              } ON 1
FROM BodenBI
WHERE [Order Date].[Trading Year].&[2015]

Both restored and live give me the same result:

I have been checking some links online,
found this one:

How to Deal with Corruption in Analysis Services

How can I stop this error message?

How can I be sure my RESTORED cube is 100% good?

This can give some insights too:

How to check the health of a SSAS database? And of its backups?


texts: Can I convert date format for this value '19920000'?

texts:
I have a value 19920000. This is date format yyyyMMdd. I want to save by date field in the table. You may see it is invalid data because day and month value is zero. Can I convert date format to this value in SQL Server? Can or not?

How to save just only year in date field?

I get the data from excel sheet. These data are customer birth date. Some people want to hide day and month of birth date. They don’t gave to right date format. My application is read data from excel sheet and show on datagridview table and then save to database. The data is big amount which round about 30000 records above at least for one time read. I can’t separate column for this data field because performance may be down.

I am using SQL Server 2008.

.


texts: Planning of Select on PostgreSQL 9.6 partitioned table very slow

texts:
I have a rather massive table of financial data in Postgres 9.6. It is partitioned by trading day (DATE type) and security ID (BIGINT). Overall the table comprises about 9300 partitions, takeing up 1765 GB in total. I use the conventional method of partitioning using CHECK constraints. The partitions are unlogged tables which I load in-bulk via COPY.

When I perform a query which can be satisfied from a single partition, the planner applies constraint exclusion and determines correctly which table should be consulted. However this takes about 12 seconds, and sometimes a lot more the first time. Could you suggest ways to help the planner work faster?

Example EXPLAIN ANALYZE output:

trading_offline=# EXPLAIN ANALYZE--
select * from public."ticks"
where trading_day ='2016-06-20'::date and inst_id= 1535681 and is_trade = true
and qty<-200;
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Append  (cost=0.00..16751.50 rows=16 width=177) (actual time=299.068..415.080 rows=5 loops=1)
->  Seq Scan on ticks  (cost=0.00..0.00 rows=1 width=161) (actual time=0.008..0.008 rows=0 loops=1)
        Filter: (is_trade AND (qty < '-200'::integer) AND (trading_day = '2016-06-20'::date) AND (inst_id = 1535681))
->  Bitmap Heap Scan on "ticks$20160620$1535681"  (cost=766.35..16751.50 rows=15 width=177) (actual time=299.059..415.068 rows=5 loops=1)
        Filter: (is_trade AND (qty < '-200'::integer) AND (trading_day = '2016-06-20'::date) AND (inst_id = 1535681))
        Rows Removed by Filter: 41833
        Heap Blocks: exact=10002
        ->  Bitmap Index Scan on "ticks$20160620$1535681_is_trade_idx"  (cost=0.00..766.35 rows=41323 width=0) (actual time=45.212..45.212 rows=41838 loops=1)
            Index Cond: (is_trade = true)
Planning time: 12233.724 ms
Execution time: 415.411 ms


texts: Schema to make a Payment History/Viewing History

texts:
On some websites I have seen two history of events: Payment History and View/Watched History.

I want to have both payment and viewing histories in my app, but I got stuck when I started thinking about the database schema.

As a basic example, the easiest way to do this for the view history would be:

Have one table called view_history
Have these four columns, at least: id, user_id, timestamp, view_id

So when a user with an ID of 80 visits a view with an ID of 22, this should be added to the table.

When this user_id 80 views the View History view, it will loop through the view_history table and displays only the rows that match the user_id = 80.

This is the easiest way of doing this, but I'm afraid it is also the worst. Log tables tend to get big pretty quickly and I assume looping through this whole table wouldn't be ideal performance wise.

With that in mind, can you suggest the best approach to this?

Thank you!

Edit: I apologize if this wasn't the right place to post this question.


texts: How to migrate SQL Server credentials from 2005 to 2008 R2

texts: I want to migrate SQL Server credentials used for proxy accounts from SQL Server 2005 to 2008 R2. Is there a way to decrypt credential passwords or a script to copy credential with the same password on new instance. Unfortunately I don't have the passwords for the accounts used for SQL credentials. So looking for a workaround.


texts:
What is the proper way to do a JSON data array in MySQL?

Following the JSON.org example http://json.org/example.html does not work:

 insert into zz_TEST_ObsJSON (jsonData) values ('{"Test1":{"Val1":"37", "Val2":"25"}},{"Test2":{"Val1":"25", "Val2":"27"}}');

  ERROR 3140 (22032): Invalid JSON text: "The document root must not follow by other values." at position 36 in value for column 'zz_TEST_ObsJSON.jsonData'.

For giggles, tried putting it all in another { }:

insert into zz_TEST_ObsJSON (jsonData) values ('{{"Test1":{"Val1":"37", "Val2":"25"}},{"Test2":{"Val1":"25", "Val2":"27"}}}');

  ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'zz_TEST_ObsJSON.jsonData'.

This other method that kind of looks like geoJSON does not either:

insert into zz_TEST_ObsJSON (jsonData) values ('[["Test1":{"Val1":"37", "Val2":"25"}],["Test2":{"Val1":"25", "Val2":"27"}]]');

  ERROR 3140 (22032): Invalid JSON text: "Missing a comma or ']' after an array element." at position 10 in value for column 'zz_TEST_ObsJSON.jsonData'.

insert into zz_TEST_ObsJSON (jsonData) values ('["Test1":{"Val1":"37", "Val2":"25"}],["Test2":{"Val1":"25", "Val2":"27"}]');

  ERROR 3140 (22032): Invalid JSON text: "Missing a comma or ']' after an array element." at position 9 in value for column 'zz_TEST_ObsJSON.jsonData'.


texts: Add Login NT AUTHORITY\NETWORK SERVICE to SQL Server

texts:
I installed a SQL Server 2014 Developer Edition on a VM, because the Express Edition is limited to just 10GB. Now I've tried to conncet to that server remotely from my VM with SSMS. But I get an error that I cannot connec:

  (provider: SQL Network Interfaces, error: 28 - Server doesn't support
  requested protocol) (.Net SqlClient Data Provider)

On the Express Server there is an NT AUTHORITY\NETWORK SERVICE login. So my conclusion was to add NT AUTHORITY\NETWORK SERVICE to the logins, so I can access the server and databases with SSMS from a different PC, hoepfully.

I do find a NETWORK SERVICE user when searching for it. It then shows up as VM-THIS\NETWORK SERVICE. So no NT AUTHORITY.

When I try to add the VM-THIS\NETWORK SERVICE, on hitting OK, I get the following error:

  Windows NT user or group 'VM-THIS\NETWORK SERVICE' not found. Check
  the name again. (Microsoft SQL Server, Error: 15401)

Having a look at the error code couldn't help me solving this.

So on the one side I can find the user, but adding causes an error? What am I doing wrong?


texts: Transactional Replication not sending all commands

texts:
I have a transactional replication topology as follows:

NY Publication Database same server holds distributor

NY - Subscriber
LON - Subscriber
HK - Subscriber

We are using bog standard, one-way transactional replication. All machines SQL 2008 R2

The snapshot gets applied fine to all subscribers, and then starts distributing commands, as you would expect, but to the one subscriber only (London), a whole bunch 2000-4 million commands just get "stuck". You can see the commands in msrepl_commands, you can see the transactions in msrepl_transaction. Replication monitor shows that there are xxxxx undelivered commands. All commands are being delivered without issue to the other 2 subscribers.

The problem is that they just stay there. Replication monitor says that there is nothing to be replicated, and nothing happens, they just stay where they are. The only way I have found around it, it to tear down the subscription and re-initialize!?!

I am aware that there are network "blips" between NY and Lon, and I can see in msrepl_errors, that there are some communication failures. However, I would expect replication to pick them back up? The distribution agent is running fine.

How do I get around this issue, and force SQL Server to pick these commands up, and deliver them.


texts:
I am reviewing a case, where I have 200+ columns mostly varchar(100). The columns are from several external data sources like CRM / Demographics data etc. I cannot hold them in one MySQL table with ever growing number of columns.

The general query situation may contain columns from one or more vertical partitions.

Is it a good idea to vertically split them? and join them on query?
What should be the size of each partition (number of columns)? To improve performance.
What should be the best JOIN matching condition?

MySQL version: 5.7
Storage Engine: InnoDB


texts: Use PostgreSQL builtin operator <@ after including extension intarray

texts:
I added the intarray extension to my PostgreSQL database in order to make use of the - operator, but I still want to use the builtin <@ operator, not intarray's <@ operator. Is there a way to do this?

The reason to prefer the builtin operator is that the builtin operator allows NULL values in the array in question, whereas the intarray operator raises an error in the event of a NULL value.


texts: Postgresql: Does autovacuum impact query performance? Does it apply locks which impact the performance of other queries?

texts:
Postgres recommends using the autovacuum to remove the dead tuples. But does running the autovacuum end up making other queries to the same table slow? Does it add locks (row or table level) which impact performance? Is there a significant load on CPU or does it consume lot of memory.

This will help us decide if we should turn on autovacuum for one of our tables which has very high amount of writes and deletes or if we should rather run a manual vacuum regularly during off peak hours.


texts:
We have a system built with Python 2.7, Django, PostgreSQL 9.1 and PostGIS 1.5. In this system there is some extensive validation processes that are, well, intensive.

Our machine:

SO: CentOS 6
HW: 8 cores, 32GB RAM, 4Tb hard-drive;

We, for development and testing, are using the default PostgreSQL configuration, what I think, is somewhat wrong. I want to take advantage of the full capacities that our hardware provides, and so far we didnt got a lot of traffic. When this is release to the public, there will be a lot of users concurrently.

Our application has some queues and async strategies to handle the load, but, the main question is: how can I safely tune PostgreSQL, run some tests, and assure that the tuning was somewhat effective?


texts: How to restore a database from the SQL 2008R2 log?

texts:
Today, for a great oversight, did a deal with a big mistake, which I forgot to put the WHERE keyword in the UPDATE statement...
I searched in the google, I found a lot of things of how to restore the database ( FROM BACKUP ), but the backup was done daily database simply disappeared :(

Now, the only information I have remaining is the log file of SQL, I found the ApexSQL log, that program make a SQL undo script, but, just for a 10th process... I have 2024 to restore, and, I don't want to pay U$ 1.5k for this program, now, I want to know, how I can restore the informations, have a FREE program to make this ?


texts: How to convert native date value column in a pivot

texts:
OPEN SYMMETRIC KEY TblUserDefinedFieldKey DECRYPTION BY CERTIFICATE mDNACert
SELECT * FROM
(
  SELECT u.public_id, u.instance_id, u.state, u.tag, udf.name AS [user_defined_key],
  CONVERT(NVARCHAR(MAX),
  DECRYPTBYKEY(value_enc)) AS [user_defined_value]
  FROM tbl_user_defined_field udf
  join tbl_user u on u.user_id = udf.user_id
  join tbl_solution_revision sr on sr.solution_revision_id = u.solution_revision_id
  join tbl_solution s on s.solution_id = sr.solution_id
  WHERE
  s.name = 'myName'
  and u.group_id = '1234'
  --and u.state = 'Active'
) as sourcetbl
PIVOT
(
    max (user_defined_value) for user_defined_key in
                           (column1, column2, column3, column4)
) as pvt
CLOSE SYMMETRIC KEY TblUserDefinedFieldKey

Given the above query, and column4 being a date field I am trying to cast the value of that field to a readable date (the value is returned as 1543507200000).

But doing this:
 max (user_defined_value) for user_defined_key in (column1, column2, column3, cast(column4 as date) as column4) gives a syntax error!


texts: how to make secondary slave as readable in MongoDB?

texts:
How do I make a secondary slave as readable in MongoDB?

We can do this via shell command

rs.slaveOk()

But how we can do it permanently for all session via config file?


texts: Error while trying to export logins from SQL Server 2000 to SQL Server 2005

texts:
I'm trying to migrate logins from an instance in SQL 2000 to a destination instance in SQL 2005.

I've used revlogin before as explained here:

https://support.microsoft.com/en-us/kb/246133

(I'm using the "Method 2" as explained in the article)

The problem is that while trying to create the sp_hexadecimal stored procedure (used by sp_help_revlogin_2000_to_2005) I get the following error:

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

Msg 134, Level 16, State 2, Procedure sp_hexadecimal, Line 26
The variable name '@binvalue' has already been declared. Variable names must be unique within a query batch or stored procedure.

This is super weird since the same SP works in other instances.

I'm leaning to believe that this is related to the SQL version I'm working on:

Microsoft SQL Server  2000 - 8.00.2301
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

The script works well in a:

Microsoft SQL Server  2000 - 8.00.2066 (Intel X86)
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Has anyone encountered this issue before?

Is there a workaround?

I found some references to this issue on the Web but they are all inconclusive.

http://www.sqlservercentral.com/Forums/Topic309704-5-1.aspx
https://social.msdn.microsoft.com/Forums/en-US/55b9a6c6-2e03-42dd-bcfc-31f487171b73/variable-already-declared-error-when-adding-stored-procedure?forum=sqldatabaseengine

Thanks!


texts: Replace content of only one table with new version (should disable foreign keys?)

texts:
I have 2 tables. 2nd has foreign keys to reference 1st.
I need to replace the whole content of the 1st table with the new version from another database. The new version satisfies all constraints and foreign keys.
I need a generic way to do it for any table so no exact name of foreign keys or constraints or columns may be "hardcoded" (but script "generation" like pg_dump does is ok).

I can't just truncate 1st table and insert everything from the new version: pg doesn't allow to delete referenced rows.

I tried to use deferred like this (I planned to automatically append it to pg_dump generated sql):

START TRANSACTION;

SET CONSTRAINTS ALL DEFERRED;

DELETE FROM X;

// ORIGINAL PART OF GENERATED SCRIPT
INSERT INTO X VALUES (10, ....)

COMMIT;

but I see Key (id)=(10) is still referenced from table.

The constraint:

CONSTRAINT second_fkey FOREIGN KEY (X) REFERENCES first (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY


texts: Connect to PostgreSQL from different host

texts:
I am facing error while connecting to the PostgreSQL server from different host, I configured both postgresql.conf and pg_hba.conf  but unfortunately with no result.

So I have installed PostgreSQL on Debian from package, added user with createuser and useradd, then created db via createdb as my custom user as an owner. Afterwards I have set postgresql.conf with these values

listen_addresses = '*'
ssl = false

And added this line in pg_hba.conf

host    all      all     192.168.0.0/24  md5

Then I have restarted service and tried to connect from my machine via psql -U admin -d admin -h 10.10.30.88and I am getting this error

psql: FATAL:  no pg_hba.conf entry for host "192.168.10.195", user "admin", database "admin", SSL off

So have I forgotten about something?

Whole pg_hba.conf

local   all             postgres                                peer

local   all             all                                     peer
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

host    all     all     192.168.0.0/24  md5
host    all     all     10.10.0.0/24    md5

ipconfig PostgreSQL server

eth0      Link encap:Ethernet  HWaddr 52:54:00:00:00:03
      inet addr:10.10.30.88  Bcast:10.10.30.255  Mask:255.255.255.0
      inet6 addr: fe80::5054:ff:fe00:3/64 Scope:Link
      UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
      RX packets:4001 errors:0 dropped:0 overruns:0 frame:0
      TX packets:2297 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:1000
      RX bytes:365126 (356.5 KiB)  TX bytes:306782 (299.5 KiB)

ipconfig from other VM in same network

eth0      Link encap:Ethernet  HWaddr 52:54:00:00:00:15
      inet addr:10.10.30.83  Bcast:10.10.30.255  Mask:255.255.255.0
      inet6 addr: fe80::5054:ff:fe00:15/64 Scope:Link
      UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
      RX packets:144749 errors:0 dropped:0 overruns:0 frame:0
      TX packets:18950 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:1000
      RX bytes:16160192 (15.4 MiB)  TX bytes:1323052 (1.2 MiB)

ipconfig from my machine

eth0      Link encap:Ethernet  HWaddr c4:7d:46:11:5c:ab
      inet addr:192.168.10.195  Bcast:192.168.10.255  Mask:255.255.255.0
      inet6 addr: fe80::c67d:46ff:fe11:5cab/64 Scope:Link
      UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
      RX packets:198498 errors:0 dropped:0 overruns:0 frame:0
      TX packets:78205 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:1000
      RX bytes:168115954 (168.1 MB)  TX bytes:10312744 (10.3 MB)
      Interrupt:20 Memory:f0600000-f0620000

I have my own KVM as well (not running)

virbr0    Link encap:Ethernet  HWaddr 2e:e4:de:95:07:1b
      inet addr:192.168.122.1  Bcast:192.168.122.255  Mask:255.255.255.0
      UP BROADCAST MULTICAST  MTU:1500  Metric:1
      RX packets:0 errors:0 dropped:0 overruns:0 frame:0
      TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:0
      RX bytes:0 (0.0 B)  TX bytes:0 (0.0 B)

Wifi is not enabled

wlan0     Link encap:Ethernet  HWaddr ac:fd:ce:b3:dc:e7
      inet6 addr: fe80::aefd:ceff:feb3:dce7/64 Scope:Link
      UP BROADCAST MULTICAST  MTU:1500  Metric:1
      RX packets:32251 errors:0 dropped:0 overruns:0 frame:0
      TX packets:159 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:1000
      RX bytes:3268413 (3.2 MB)  TX bytes:21321 (21.3 KB)


texts: sp_MSforeachdb Continue on failure when running DBCC CHECKDB

texts:
I have the following maintenance script

DECLARE @sql NVARCHAR(30) = 'DBCC CHECKDB(?)'
EXEC sp_MSforeachdb @sql

This checks every database for corruption. I have run this on my test server where I deliberately corrupted a database and as expected, the DBCC CHECKDB errors for that database, however, this then ends the script, so all the other databases that haven't yet been checked go unchecked.

Is there a way I can make the script continue on error so that the remaining databases get checked?

I have also tried Aaron Bertrand's sp_foreachdb and found the behaviour to be the same

1: https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/ as well as Ola.Hallengren's SQL Server Integrity Check using the command DatabaseIntegrityCheck @Databases =  'ALL_DATABASES' which again gives the same behaviour

I have noticed management studio shows "Disconnected" at the bottom also


texts: Adding logging statements at start and end of all stored procedures

texts:
I would like to capture stored procedure execution details which would require altering existing procedures to perform an INSERT at the start and an UPDATE at the end.

I recall finding a blog article some time ago that demonstrated adding statements to the start/end of stored procedures but cannot find it.

How could I make these changes programmatically either in C# or T-SQL ensuring the original stored procedure definition works as expected?

Edit: As a minimum I want to capture the user executing, the stored procedure name, start and end times. Parameters and stored procedure definition are optional.


texts: Concatenate 2-3 table field values in 1 table field in access

texts:
I have a project that in Access. The problem is I am a newbie in this application and I really couldn't figure out what to do, I have checked out several ways but am still stuck.

I'll be handling a survey file that will maintain answers per customer. The visit to customer is done monthly in a year, which means that a customer can have 12 visits in a year.

My problem is that if I will maintain this project for the next five years or more, I have to maintain another field in my table that will block the duplicate entries.

It would be better to attach the file but I couldn't find a way. Here's the layout I would like to have:

From my table, field 1 will be where it concatenates the other fields. Year as field 2, Month(field 3), Customer Code (field 4) and so on for 10 surveyed products.

I need to concatenate field 2, 3 & 4 in this way I can see if the customer was entered on that particular month and year and I can arrange a warning if already exist within my form.


texts: Calculating balance sheet with a single query

texts:
I have this schema for double entry bookkeeping system:

CREATE TABLE account (
  id        INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  parent_id INTEGER REFERENCES account(id),

  UNIQUE (name, parent_id)
);

CREATE TABLE journal (
  id    SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  date  DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE posting (
  id          SERIAL PRIMARY KEY,
  account_id  INTEGER NOT NULL REFERENCES account(id),
  journal_id  INTEGER NOT NULL REFERENCES journal(id),
  amount      NUMERIC (12, 2) NOT NULL,

  -- debit and credit are described by positive and negative value, therefore
  -- zero value is not allowed
  CHECK (amount <> 0)
);

So, basically each transaction will be recorded in journal table and it's amount that is affecting account table will be recorded in posting table.

Since the structure of account is a hierarchical tree, the amount of each node must be calculated with all of it's descendant.

This is the best I can come up with:

WITH RECURSIVE
  -- recursively build tree table, assigning path for each row
  tree(id, parent_id, name, path) AS (
    SELECT account.id        AS id,
           account.parent_id AS parent_id,
           account.name      AS name,
           ARRAY[account.id] AS path -- path from root
      FROM account
     WHERE account.parent_id IS NULL

     UNION ALL

    SELECT account.id                     AS id,
           account.parent_id              AS parent_id,
           account.name                   AS name,
           tree.path || ARRAY[account.id] AS path
      FROM account, tree
     WHERE account.parent_id = tree.id
  ),
  -- make a flat table. each account will appear as whenever it's child is
  -- appeared
  flatten AS (
    SELECT unnest(tree.path) AS id,
           tree.id           AS account_id
      FROM tree
     ORDER BY unnest(tree.path), tree.id
  )

SELECT tree.id                               AS id,
       tree.name                             AS name,
       coalesce(abs(sum(posting.amount)), 0) AS amount
  FROM tree
  LEFT OUTER JOIN flatten ON tree.id = flatten.id
  LEFT OUTER JOIN posting ON posting.account_id = flatten.account_id
 GROUP BY tree.id, tree.name
 ORDER BY tree.id

My question is, Can this be improved? Will it perform well?

PS: Some data for testing code

INSERT INTO account VALUES (100, 'ASSET', NULL);
INSERT INTO account VALUES (101, 'Cash', 100);
INSERT INTO account VALUES (102, 'Bank', 100);
INSERT INTO account VALUES (200, 'LIABILITY', NULL);
INSERT INTO account VALUES (201, 'Loan', 200);
INSERT INTO account VALUES (300, 'EQUITY', NULL);
INSERT INTO account VALUES (301, 'Opening Balance', 300);

INSERT INTO journal VALUES (1, 'opening balance', '2018-12-05');
INSERT INTO posting VALUES (1, 101, 1, 1000000.00);
INSERT INTO posting VALUES (2, 301, 1, -1000000.00);

INSERT INTO journal VALUES (3, 'save to bank', '2018-12-05');
INSERT INTO posting VALUES (5, 102, 3, 1250000.00);
INSERT INTO posting VALUES (6, 101, 3, -1250000.00);

INSERT INTO journal VALUES (2, 'loan money', '2018-12-05');
INSERT INTO posting VALUES (3, 101, 2, 500000.00);
INSERT INTO posting VALUES (4, 201, 2, -500000.00);

The output (for running the query above) should be something like this:

 id  |      name       |   amount
-----+-----------------+------------
 100 | ASSET           | 1500000.00
 101 | Cash            |  250000.00
 102 | Bank            | 1250000.00
 200 | LIABILITY       |  500000.00
 201 | Loan            |  500000.00
 300 | EQUITY          | 1000000.00
 301 | Opening Balance | 1000000.00
(7 rows)


texts: How do I design this Draft Table and select from it?

texts:
I've asked the same Question before and deleted it on a different subject, This one has the same design in it

This is my Query: https://www.db-fiddle.com/f/weourGGnXWj2b6xu7mNfAp/1 with the Table Design.

I'm trying to display the drafts content using the provided Query:

SELECT t.title, p.content, p.version
FROM drafts d
INNER JOIN titles t ON t.id = d.tp_id AND d.t = 1
INNER JOIN posts p ON p.id = d.tp_id AND d.t = 2
WHERE user_id = 1

I'm trying to use INNER JOIN to prevent the not-existing titles and posts from appearing, But I got no results at all,

The result I expect is:

title     content    version
TheTitle  null       null
TheTitle  Content1   1
TheTitle  Content2   2
TheTitle  Content3   3
TheTitle  Content4   4
TheTitle  Content5   5
TheTitle  Content6   6
TheTitle  Content7   7

The first row is from the table titles which has nothing to do with content, version, And all the rows after it is from the table posts,

Also the table posts is linked with the table titles using the column tid, That How I'm supposed to get the title value in rows > 1

Is there a better approach to make this draft? And How do I select the results from it as intended?

Another Queries I tried:

https://www.db-fiddle.com/f/weourGGnXWj2b6xu7mNfAp/4


texts: SQL Server upgrade advisor reserved keyword warning - Can't find keyword!

texts:
I am running SQL Server 2008 Upgrade Advisor tool on a SQL Server 2000 DB, and the final report shows an error:

  Database Server,"PreUpgrade",
  "After upgrade, new reserved keywords cannot be used as identifiers",
  "Upgrade Advisor detected an idetifier with name Merge",
  "Source Type: Database","Database: Domus","Object Name: crosstab","Object Type: P ",
  "Key Word: pivot"

The message is a bit ambiguos for me.... Using the following script, I have searched the keyword 'pivot' and 'Merge' in all tables, however there are no results.

Should I ignore this error?


texts: Cannot connect to DB2 database after restoring an offline backup

texts:
This is for DB2 9.7 Enterprise Server Edition on AIX.

I had a full, offline backup taken of several databases within our instance in a performance environment.

I wanted to restore these databases to our integration environment.

I ran the command

db2 restore database database_name from
/bkp/instance_name/OfflineBackups/RestorePoint/my_restore_point
taken at 20120321103846 into database_name without prompting;

for each of my databases (modifying the database name and timestamp appropriately of course).

Each database restored successfully according to DB2. However, now when I wish to check if the database did indeed restore what I thought, I try to connect to a database and I get the error

    SQL1117N  A connection to or activation of database "database_name" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

I didn't have any in process transactions in the previous database because all connections were forced off and the database was down for an offline backup. What do I do?


texts: Creating trigger from one database table to another database table postgresql

texts:
I have two database say db1 and db2. And two tables user_auth_table in db1 and user_table in db2. Now I want to trigger a function which sync up few values in both tables for each new row or on each new insertion in user_auth_table.

I have tried to write on my own and creating trigger in db1. But the error says user_table doesn't exist. Which I know why I am getting this error. Any suggestion to target the tables outside the db ?

CREATE TRIGGER sync_user_table AFTER INSERT ON user_auth_table for each row execute procedure syncAndMaintainUsersTable()

create or replace function syncAndMaintainUsersTable()
returns trigger as
$BODY$
begin
if NEW.user_uuid<>OLD.user_uuid then
    insert into user_table values (NEW.user_uuid, CURRENT_TIMESTAMP, NEW.provider);
end if;
return new;
end;
$BODY$
language plpgsql;


texts: Lock or disable connections to Postgres while performing maintenance

texts:
I never have this issue with MySQL, but PostgreSQL is always a pain when trying to do any kind of maintenance.
I am trying to truncate and load new data into databases on a PostgreSQL server, but there are usually active connections, and this messes up things. There is also one particular database I need to drop and create, and current active doesn't allow me to drop it.
All these are setup in a script that should run. My issue is active connections is killing me and not letting the script work.
Do I lock connections to the DB or block connections? How do I do this? Not as familiar with PostgreSQL as I am with MySQL.


texts: Create multiple databases dynamically in Microsoft Azure

texts: I am a newbie in Microsoft Azure platform. I want to create multiple databases dynamically (We are developing multi-tenant model. So, Each organization should have their own database. Whenever an organization is registered with our system, we need to create a new database dynamically which should take the existing default database template as it's structure).Please provide some insights on this.


texts: Connection is Using Properly or not in Net Use via SQL Procedure

texts:
am doing an operation of creating a file and writing in a SAN SAMBA where it will be done using the cmd_shell via SQL Procedure. It's creating and writing it successfully but while am calling that procedure 2 two times at the same time am getting the issue as

  The local device name is already in use.

Is there any options while using this net use command I have 15 different procedures each will be having this type of behavior.

Note: Every Procedure is given a Drive name differently.

  EXEC xp_cmdshell  'net use r: \10.44.11.44\dpsshare Password
  /user:Username /persistent:no'

Am also deleting the Network Connection at the end of the Procedure.


texts: join and repeat value from a column of another table


texts: Is it possible to convert existing users from SQL Server authentication to Windows (AD) authentication? (SQL Server 2008 R2)

texts:
We have just undergone a bare-metal restore of an SBS 2003 server running our SQL database and are in the final stages of configuration before going live again.

Previously, we had 20 users accessing this database via SQL authentication. This wasn't ideal, as users had multiple sets of credentials to remember-but the person who set it up decided to do it this way. (Any ideas why someone would choose this over AD authentication?)

I am wondering if there is any way to move from using SQL user accounts to AD credentials? If so, what would be the best practice method for achieving this with an existing database?

Any advice would be much appreciated!


texts: Are SqlServer Sequences, guaranteed to return different values for concurrent transactions?

texts:
In documentation it is told that:

  Duplicate validation only occurs once a record is fully populated. This can result in some cases where the same number is used for more than one record during creation, but then gets identified as a duplicate

I do NOT want to use that number in an insert query like this:

INSERT Test.Orders (OrderID, Name, Qty)
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ;

I want to obtrain that number in my web application and use it to construct a ciphered text. and store the ciphered code in database. Hence, in my case, there is no duplicate checking while inserting.

The thing I need to know is that whether it is probable for two clients calling NEXT VALUE FOR function nearly at the same time, and get the same values? or not? (I need this not to happen)


texts: This installation of MySQL is already upgraded to 5.7.25?

texts:
I used sudo apt upgrade and got this:

E: dpkg was interrupted, you must manually run 'sudo dpkg --configure -a' to correct the problem.

So I did it and I see this for more than an hour.

Setting up mysql-server-5.7 (5.7.25-0ubuntu0.18.04.2) ...
Checking if update is needed.
This installation of MySQL is already upgraded to 5.7.25, use --force if you still need to run mysql_upgrade

Nothing happens. I have not used MySQL for a long time and don't know what should I do. Understand there must be something with force but I am new with Ubuntu and not so familiar with commands. Can you help me? I don't understand nor what the problem is...:(

UPDATE: I can use MySQL Workbench and get access in Terminal too, but still keep seeing that error


texts: Storing different countries' administrative divisions

texts:
I'm designing a data base which should relate users to their
administrative division for statistical purposes, considering that
every country has its own type of divisions. Chile, for example,
is divided in regions which are divided in provinces which are divided
in communes, while Angola is divided in provinces which are divided in
municipalities which are divided in communes. I thought a solution
could be to have a column called first_administrative_division
in the user table, which will refer to a
first_administrative_division table, which will have a
second_administrative_division column which will refer to a
third_administrative_division table which will have a column
which will refer to the country table. But reading
this
I didn't only found that there are countries which have only two
administrative divisions, and in other cases, four; some cases
are even more complex, like Argentina and Brazil, which have
different kind of internal divisions, divided in different ways. So,
I'm crying right now.

I don't want to cover the entire world, countries will be added as
they are needed. Is it posible to modelate such thing?

Thanks beforehand.


texts: Slow query performance due to temporary file?

texts:
Here is the query:

SELECT "products".*
FROM "products"
WHERE (status > 100)
AND "products"."above_revenue_average" = 't'
AND ("products"."category_id" NOT IN (5))
ORDER BY "products"."start_date" DESC

I have an index on status and start_date.

Each time I run the query from my application, I get the following in the logs:

[WHITE] temporary file:
path "pg_tblspc/16386/PG_9.3_201306121/pgsql_tmp/pgsql_tmp2544.0", size 37093376
Query: SELECT "products".* FROM "products"  WHERE (status > 100)
AND "products"."above_revenue_average" = 't'
AND ("products"."category_id" NOT IN (5))  ORDER BY "products"."start_date" DESC

I believe this temporary file creation is the cause of the slow performance.

Running an EXPLAIN ANALYZE I get the following results:

QUERY PLAN
 Sort  (cost=63395.28..63403.51 rows=16460 width=524)
       (actual time=524.134..562.635 rows=65294 loops=1)
   Sort Key: start_date
   Sort Method: external merge  Disk: 36224kB
   ->  Bitmap Heap Scan on products
        (cost=4803.40..60389.73 rows=16460 width=524)
        (actual time=27.390..397.879 rows=65294 loops=1)
         Recheck Cond: (status > 100)
         Filter: (above_revenue_average AND (category_id <> 5))
         Rows Removed by Filter: 25115
         ->  Bitmap Index Scan on index_products_on_status
            (cost=0.00..4802.58 rows=89662 width=0)
            (actual time=18.006..18.006 rows=90411 loops=1)
               Index Cond: (status > 100)
 Total runtime: 577.870 ms
(10 rows)

I have then used http://explain.depesz.com/ to make it a bit more readable:

Per node type stats

+-------------------+-------+--------------+------------+
|     node type     | count | sum of times | % of query |
+-------------------+-------+--------------+------------+
| Bitmap Heap Scan  |     1 | 379.873 ms   | 67.5 %     |
| Bitmap Index Scan |     1 | 18.006 ms    | 3.2 %      |
| Sort              |     1 | 164.756 ms   | 29.3 %     |
+-------------------+-------+--------------+------------+

Per table stats

+------------------+------------+--------------+------------+
|    Table name    | Scan count |  Total time  | % of query |
+------------------+------------+--------------+------------+
| scan type        | count      | sum of times | % of table |
| products         | 1          | 379.873 ms   | 67.5 %     |
| Bitmap Heap Scan | 1          | 379.873 ms   | 100.0 %    |
+------------------+------------+--------------+------------+

Can I increase the database performance by adding some more indexes? Maybe some composite ones? Any ideas?


texts: Cassendra schema design suggestion for chat archive

texts:
I have gone though this article and here is the schema I have got from it. This is helpful for my application for maintaining statuses of a user, but how can extend this to maintain one to one chat archive and relations between users, relations mean people belong to specific group for me. I am new to this and need an approach for this.

Requirement :

I want to store messages between user-user in a table, whenever a user want to load messages by a user I want to retrieve them back and send it to user. I want to retrieve all the messages from different users to the user when user has requested. And also want to store class of users. I mean for example user1 and user2 belong to "family" user3, user4, user1 belong to friends etc... This group can be custom name given by the user.

This is what I have tried

CREATE TABLE chatarchive (
   chat_id uuid PRIMARY KEY,
   username text,
   body text
)

CREATE TABLE chatseries (
username text,
    time timeuuid,
    chat_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time ASC)

CREATE TABLE chattimeline (
    to text,
username text,
    time timeuuid,
    chat_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time ASC)

Below is the schema that I currently have,

CREATE TABLE users (
   username text PRIMARY KEY,
   password text
)

CREATE TABLE friends (
    username text,
    friend text,
    since timestamp,
    PRIMARY KEY (username, friend)
)

CREATE TABLE followers (
    username text,
    follower text,
    since timestamp,
    PRIMARY KEY (username, follower)
)

CREATE TABLE tweets (
    tweet_id uuid PRIMARY KEY,
    username text,
    body text
)
CREATE TABLE userline (
    username text,
    time timeuuid,
    tweet_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time DESC)

CREATE TABLE timeline (
    username text,
    time timeuuid,
    tweet_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time DESC)


texts: Mirroring SQL error log - Error: 8474, State: 11

texts:
I'm getting these errors in SQL error logs:

  An error occurred in a Service Broker/Database Mirroring transport connection endpoint,
  Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')
  Error: 9642, Severity: 16, State: 3.

Principal and mirror servers are in synchronized state. They have been in synchronized state for some months now, but I keep getting these error messages, I checked the permissions and everything is like its supposed to. Timing on both the servers is the same.

How can I stop these error messages. Thanks for your help.


texts:
I always wondered, why a port must be assigned in order to use a DBMS? And what difference does it make using TCP or UDP (and even static and dynamic)?
Is it for communication purposes?
I know this is very basic question, but still I could not find a straight forward answer.


texts: Tips on improving/refactoring slow query?

texts:
Let's play lotto!

Ok. I have a numbers table with 80 rows (numbers 1-80):

create table dbo.numbers (knum tinyint primary key identity);

I have a table with the drawid, and each number drawn.

create table dbo.draws (drawid int, drawnumber tinyint);

Now, given that the draws table may have data like:

drawid   drawnumber
  1         10
  1         36
  1         54
  1         75
  2          9
  2         45
  2         46
  2         72

I want to find out when was the last time every possible three-number permutation occurred.

I'm using this:

declare @curdraw int
select @curdraw = max( drawid)-100 from draws;

select TOP 10 K1,K2,K3, @curdraw-max(d1.drawid)  from THREES
inner join DrawNumbers d1 WITH (NOLOCK) ON K1 = D1.DRAWNUMBER
INNER JOIN DRAWNUMBERS D2 WITH (NOLOCK) ON K2 = D2.DRAWNUMBER AND D1.DRAWID = D2.DRAWID
INNER JOIN DRAWNUMBERS D3 WITH (NOLOCK) ON K3 = D3.DRAWNUMBER AND D1.DRAWID = D3.DRAWID
WHERE D1.DRAWID < @CURDRAW

GROUP  BY K1, K2, K3
ORDER BY @curdraw-max(d1.drawid) DESC

(Oh, and table Threes is a table with all 492,000+ three-number combinations from 1-80)

Is there a better way to do what I'm trying to do here? This particular query is just very slow and I'm sure someone with better math/grouping skills could do better.

Answers to comments - clarifications:

It's for a keno game. Every draw has 20 numbers out of the 80. I just showed 4 each as an example.
Two years of accumulated draws, at 254 draws a day with 20 numbers per draw... Drawnumber table is currently 3.7 mil rows. Also, what I'm looking for specifically is "out of every 3-number combination possible can you give me a sorted list of how long it has been since any one of them has hit, ordered by that length".
The top 10 is merely because I don't need to see all 490K results.

The primary keys:

USE [OKD]
GO

ALTER TABLE [dbo].[DrawNumbers]
  ADD CONSTRAINT [PK_DrawNumbers]
  PRIMARY KEY CLUSTERED
    ( [DrawID] ASC, [DrawNumber] ASC )
  WITH
   (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
    ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
GO

USE [OKD]
GO

ALTER TABLE [dbo].[threes]
  ADD PRIMARY KEY CLUSTERED
    ( [THREEID] ASC )
  WITH
    (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
     SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
     ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
GO


texts: How to change dbid in oracle to specific value?

texts:
I want to change dbid of oracle 11g database.
Current dbid = 2241883xxx   And
want to set dbid = 1380515xxx


texts: Insert on duplicate key update using MongoDB

texts:
Can somebody tell me how I should build MongoDB "updateOne" query to get analog of the following SQL query:

INSERT INTO `collection`
(`hash`, `total`, `base_fare`)
VALUES ('aaa', 200, 150)
ON DUPLICATE KEY UPDATE
`base_fare`=IF(VALUES(`total`)<`total`,VALUES(`base_fare`),`base_fare`),
`total`=IF(VALUES(`total`)<`total`,VALUES(`total`),`total`);

What I tried:

db.collection.updateOne(
    {"hash": "aaa", "total": {$lte: 200}},
    {$set: {"hash": "aaa", "total": 200, "base_fare": 150}},
    {upsert: true}
)

But this query insert new document into the collection if existed document with hash "aaa" has field "total" greater that 200. I understand that this is default behavior.

Is any solutions to get behavior equal to MySQL's INSERT ON DUPLICATE KEY UPDATE?

Thanks