texts: Troubleshooting spid suspended ASYNC_NETWORK_IO

texts:
Recently we moved our database to a new server

The old server was running

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) Jul 12 2019 17:43:08
Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)

The new server is running

Microsoft SQL Server 2019 (RTM-CU2) (KB4536075) - 15.0.4013.40 (X64) Feb 3 2020 16:40:57
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Linux (CentOS Linux 8 (Core))

We have a C#  Windows service that uses the database. It is intermittantly stopping or running extremely slow.

The windows service is using .Net Framework 4.7.2 and it calls a library written in .net standard 2.0 which also makes sql calls.

I found this question on why the status of a SPID is suspended and see that our Suspended Spids have wait_type ASYNC_NETWORK_IO

According to the docs this

  Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.

Restarting the windows service does temporarily solve the problem. However this does not help me troubleshoot the cause of the problem.

Mostly I am using EntityFramework 6.4

[Update]

I have taken to running

dbcc inputbuffer(sessionId)

to see the last running SQL


texts:
I have a big Oracle table (around 40 million rows) that looks like this:

ID  Name    Question Answer Reason
3   Name1   1       Yes     blah blah
3   Name1   2       No      NA
3   Name1   3       No      NA
3   Name1   4       Yes     blah2
3   Name1   5       Yes     null
3   Name1   6       Yes     blah3
3   Name1   7       No      null
6   Name2   1       Yes     blah4444
6   Name2   2       No      null
6   Name2   3       Yes     blah3
6   Name2   4       NA      blah5
6   Name2   5       Yes     null
6   Name2   6       Yes     blah6
6   Name2   7       NA      null

I need one row per ID i.e. I will need to add columns for each question's answer (there are 7 questions per ID) and each question's reason. I need to make it look like this:

ID  Name    Q1     Q1-Reason    Q2    Q2-Reason   Q3    Q3-Reason    etc.
3   Name1   Yes    blah blah    No    null
6   Name2   Yes    blah4444     No    null

My query currently looks like this:

select
   A.ID,A.NAME,B1.Q1,B1.Q1-REASON,B2.Q2,B2.Q2-REASON
from
   TABLENAME A
inner join
    (
    select distinct C1.ID,C1.ANSWER as Q1,C1.REASON as Q1-REASON
    from TABLENAME C1
    where C1.QUESTION=1
    ) B1 on B1.ID=A.ID
inner join
    (
    select distinct C2.ID,C2.ANSWER as Q2,C2.REASON as Q2-REASON
    from TABLENAME C2
    where C2.QUESTION=2
    ) B2 on B2.ID=A.ID
...
...

However, as the table is huge, this is taking a VERY long time to retrieve the data. Could someone suggest ways to optimize this query? Any help would be appreciated!

I'm on Oracle 10g and SQLDeveloper 4.0.2.15


texts:
I'm trying to connect to my RAC database using odbc drivers. While trying I get

  ORA-28040: No matching authentication protocol

I have added this in both client and the server:

SQLNET.ALLOWED_LOGON_VERSION=8

I have also tried by replacing the above mentioned deprecated parameter with the server and client. I still get the same error.

More details:

Database version 12c
Client version 12c


texts: How can I use RMAN to show if there are archive logs that need to backed up?

texts:
My main concern is knowing if the Oracle Archive Logs haven't been backed up in a while because something is broken that I may not know about.

When I query v$log it appears to be good with First_Time being very recent.  I would think v$log wouuld have lots of rows if the logs had not been backing up.

SELECT * FROM v$log;

Shows only a few, two say current (four returned).

select * from v$log_history order by FIRST_TIME desc;

Shows lots.


texts: Postgres CREATE DATABASE already has data and tables

texts: When I do a: DROP DATABASE mydb; and subsequently: CREATE DATABASE mydb WITH OWNER user; the new mydb database is created with all the tables and data that I had in the database I just dropped. What am I be missing?


texts: MySQL conversion from float (10,2) to fixed(10,2)

texts:
I have a database that contains financial data stored as floats. Naturally, this can present a real issue at random times (eg, when there is an exact value that cannot be approximated closely enough with a floating-point algorithm).

My question is:

Will using  ALTER TABLE table MODIFY field fixed(10,2) preserve the fixed value that is currently shown by MySQL when you select the field?
Is there any possibility that the value would be something other than that?


texts:
We have a SQL Server Agent job for loading dumps in case we need to restore from our daily backups. The first step in this job is supposed to kill all connections to the database. However, we frequently encounter a problem where somebody on the network has left an application running which will try to reconnect to the database if disconnected, and so after this first step has completed, this application will reconnect. Therefore, when the restore step comes it fails because it was unable to gain exclusive access to the database.

Usually I would use ALTER DATABASE [db] SET SINGLE_USER WITH ROLL BACK IMMEDIATE to ensure exclusive access, but unfortunately in this case the application reconnects sooner than the restore begins, and so the restore script is unable to access the database at all.

In order to get around this, I thought that it might be possible to disable all TCP connections during the restore so that only the locally running restore script could access the database. So, my question is is there any way in T-SQL to disable/block TCP connections? Or perhaps is there a way to configure the SQL Server Agent to do this when it starts the job?


texts: Triggers lost when changing table property in SQL Server Management Studio designer

texts:
We found a bug in the SQL Server Management Studio. When you change any table property that requires the table to be dropped and re-created, all your triggers are gone. It must be done within the designer. The designer needs to have "Prevent saving changes that require re-creation" option disabled. In general, every time such a change occur the table is copied, dropped and recreated with the new set of properties. Unfortunately, the process deletes your triggers.

We checked every version of SQL SMS we have, and this are: 2008, 2012, 2014 and 2016. The bug is fixed only in the 2016 version. It might be important, that we used to have Microsoft SQL Server 2000 (32 bit) database and upgraded it to Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64).

We found the bug thanks to our database documentation. We saw there are no triggers in the table and our first thought was that there were none from the very beginning. A quick look at the documentation was enough to see that we are wrong. We recreate the process in our database. You can watch in on the video below:

https://www.youtube.com/watch?v=1VWrPBOC5rA

Did you encounter it in any other SSMS version and if yes, then on which database version?

Do you know any workaround for it?


texts: What is an InnoDB Page checksum exactly? Does a Page checksum only detect issues with underlying storage when writing or reading to a page/block?


texts:
I am working on a query for a project and sometimes the results have multiple rows for the same entity. What I need to do is:

If the results look like this:

   Type        |   Entity
-----------------------------
   TypeA       |      B. Didly

   TypeA       |      J. Hendrix

   TypeC       |      G. VanFleet

   TypeA       |      M. Manson

   TypeB       |      M. Manson

   TypeC       |      M. Manson

Then I need to choose TypeB over TypeA or TypeC for M. Manson

My results need to look like this:

   Type        |   Entity
-----------------------------
   TypeA       |      B. Didly

   TypeA       |      J. Hendrix

   TypeC       |      G. VanFleet

   TypeB       |      M. Manson

I am struggling with using a cursor or finding another option.

Basically if an entity has all three types, I need to only get TypeB for that entity.

There are 10 possible "types" from what I have been told. And the client has given me their ranking of those types. I am thinking a case statement to set the rank.


texts:
I'm calling a function from pl/sql, but I do not require the result in this case.  Is there a cleaner way to call this function without using a dummy variable?

Works:

junk := performWork();

Does not work:

performWork();         --PLS-00221: performWork is not a procedure or is undefined
EXEC performWork();    --this might work from SQL*Plus, but not from pl/sql
SELECT pkg.performWork() FROM DUAL;  --PLS-00428: INTO clause is expected in this SELECT statement


texts:
Firstly, I know my title is somewhat misleading, but I couldn't think of a better title.

Here is my current relationship

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

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

ALTER TABLE Events ADD FOREIGN KEY(DefaultLocationID)
    REFERENCES Locations(ID)
    ON UPDATE CASCADE ON DELETE SET NULL

ALTER TABLE Locations ADD FOREIGN KEY(EventID)
    REFERENCES Events(ID)
    ON UPDATE CASCADE ON DELETE CASCADE

My question is knowing that an Event can have a list of 0 or more Locations, and each Location can only be associated with exactly 1 Event.

I want the ability for an Event to have a default Location, but the integrity is that if an Event has a Location then that Locations foreign key must be the same Event.

My initial thought was to put a nullable foreign key in the Event table DefaultLocationID, but it dawned on me that there is no integrity to prevent that Location from being associated with a different Event.

My other idea was to have a IsDefault field on the Location table, but that doesn't stop multiple Locations from having their IsDefault field to true.

How would I enforce this relationship, preferabily without triggers and complex sub-queries?


texts: How to assign privileges on a postgresql schema to a user?

texts:
pg_hba.conf contains:
...
host 10.10.10.10 service_db admin md5
...

Logged in as root, executed the following commands:

###CREATE DB ADMIN
sudo -u postgres psql -p 5432 -d postgres -c "CREATE USER 'admin' ENCRYPTED PASSWORD 'admin_pwd';"
###CREATE DB
sudo -u postgres psql -p 5432 -d postgres -c "CREATE DATABASE service_db OWNER 'admin' ENCODING 'UTF-8' TABLESPACE service_ts LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' LIMIT -1;"
###CREATE SCHEMA
sudo -u postgres psql -p 5432 -d service_db -c "CREATE SCHEMA 'service_schema';"
###REVOKE PREVILEGES ON service_schema from public
sudo -u postgres psql -p 5432 -d service_db -c "REVOKE ALL ON SCHEMA 'service_schema' FROM 'public';"
###GRANT PREVILEGES ON TABLES OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON TABLES IN SCHEMA 'service_schema' TO 'admin';"
###GRANT PREVILEGES ON SEQUENCES OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON SEQUENCES IN SCHEMA 'service_schema' TO 'admin';"
###GRANT PREVILEGES ON FUNCTIONS OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON FUNCTIONS IN SCHEMA 'service_schema' TO 'admin';"

and all commands were executed successfully.

Observed that:
a) Got permission denied error while selecting from a table in that schema in cli, as follows:

sudo -u postgres psql -H 10.10.10.10 -p 5432 -d service_db -U admin
service_db=#
service_db=#select * from service_schema.customers;
permission denied

While connecting and executing the command from Netbeans:

select * from service_schema.customers;

throws error:

Exception, Error Code 0, SQL State 42501
ERROR permission denied for schema admin

Please guide me in resolving the above issue.

Thanks in Advance!

Update:

GRANT USAGE ON SCHEMA... just before GRANT PRIVILEGES... resolved the problem. ie.

###CREATE DB ADMIN
sudo -u postgres psql -p 5432 -d postgres -c "CREATE USER "admin" ENCRYPTED PASSWORD 'admin_pwd';"
###CREATE DB
sudo -u postgres psql -p 5432 -d postgres -c "CREATE DATABASE service_db OWNER "admin" ENCODING 'UTF-8' TABLESPACE service_ts LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' LIMIT -1;"
###CREATE SCHEMA
sudo -u postgres psql -p 5432 -d service_db -c "CREATE SCHEMA "service_schema";"
###REVOKE PREVILEGES ON service_schema from public
sudo -u postgres psql -p 5432 -d service_db -c "REVOKE ALL ON SCHEMA "service_schema" FROM 'public';"
###GRANT USAGE ON service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT USAGE ON SCHEMA "service_schema" TO "admin";"
###GRANT PREVILEGES ON TABLES OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON TABLES IN SCHEMA "service_schema" TO "admin";"
###GRANT PREVILEGES ON SEQUENCES OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON SEQUENCES IN SCHEMA "service_schema" TO "admin";"
###GRANT PREVILEGES ON FUNCTIONS OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON FUNCTIONS IN SCHEMA "service_schema" TO "admin";"

works fine for me!


texts:
Ok so I have a mysql database containing data that is input from various customers. The customers are told that their data should only be encoded as utf8 however I have become aware that this is currently not being checked and some people are using characters not encoded in utf8. We intend to add some checking but in order to clean the data already there is there a sql or mysql query (or admin command) that will show me the data that is NOT encoded as utf8.

Sorry if this is a dumb question I'm a Linux Sysadmin and have limited exposure to databases.

Thanks

B


texts:
I have the following table:

CREATE TABLE `timeline_lists` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `list_type` int(11) NOT NULL,
  `s_object_id` int(11) NOT NULL DEFAULT '0',
  `group_id` int(11) DEFAULT '0',
  `new_items_count` int(11) DEFAULT NULL,
  `last_accessed_on` datetime DEFAULT NULL,
  `last_updated_on` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`),
  KEY `unique_index` (`user_id`,`list_type`,`s_object_id`,`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

every time I run the following query a new record is added:

INSERT INTO `timeline_lists` (`id`,`user_id`,`list_type`,`s_object_id`,`group_id`,`new_items_count`,`last_accessed_on`,`last_updated_on`,`created_at`,`updated_at`)
VALUES
(NULL,18724,11,0,0,5,'2014-09-23 16:37:46',NULL,'2014-09-23 16:37:48','2014-09-23 16:37:48')
ON DUPLICATE KEY UPDATE
`timeline_lists`.`new_items_count`=VALUES(`new_items_count`),
`timeline_lists`.`last_accessed_on`=VALUES(`last_accessed_on`),
`timeline_lists`.`updated_at`=VALUES(`updated_at`)

I would expect that the unique index effect and the fields would just get updated instead, if I tun this query 5 times I would get 5 records in the DB instead of just 1.

what am I missing here?


texts: Mongo Create a user as admin for any database raise an error

texts:
I am trying to create a simple user with the rights permission to access to any database and can do any actions. When I trying to execute the createUser command I got this error:

db.createUser({ user: "mongoadmin" , pwd: "mongoadmin", roles: ["userAdminAnyDatabase", "dbAdminAnyDatabase", "readWriteAnyDatabase"]})
2015-08-20T17:09:42.300+0000 E QUERY    Error: couldn't add user: No role named userAdminAnyDatabase@new_vehicles_catalog

The problem above only happen when I enable the auth configuration and I need it.

So, How do I create an user with admin permission for any database. I want it because I configured my mongo services to uses authentication connection. If I want to execute a dump of my data I have to use this authentication parameters.

Please any help?

Using mongo version 3.0.5.

the service is on Amazon Linux AMI 2015.03 (HVM), SSD Volume Type - ami-1ecae776


texts: Mysql insert fixed column value for each row during multi-insert

texts:
In mysql when I'm doing

# fr-FR.sql
INSERT INTO table (resource_key, resource_value, locale) VALUES
    ("GEN.YES", "Oui", "fr-FR"),
    ("GEN.NO", "Non", "fr-FR"),
    ("GEN.MAYBE", "Peut-être", "fr-FR")
ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`);

# en-US.sql
INSERT INTO table (resource_key, resource_value, locale) VALUES
    ("GEN.YES", "Yes", "en-US"),
    ("GEN.NO", "No", "en-US"),
    ("GEN.MAYBE", "Maybe", "en-US")
ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`);

Here, 'resource_key' and 'locale' are the primary key. 'locale' is always the same in one file. But differ from file to file (can't set as default value);

This query works. It insert each row and if a label for a given resource_key/locale pair exists, it will update its associated resource_value

Is it possible to insert same value in each row for a given column. There is thousands of lines in different files (added continuously) and always typing the same string in each row is annoying.


texts: How to link users and logins in an Availability Group?

texts:
I've got an Availability Group going and need to map some logins to database users on an active secondary replica. (Node2)  Unfortunately, when I try to exec sp_change_users_login or alter user with login, I get an error message telling me the database is read-only.  That's no surprise, but I'm not sure how to fix it.  I tried failing over last night, fixing the logins on Node2, and then failing back to Node1, but then the login/user mappings just end up wrong on Node1.  When I fix those, Node2's logins get out of whack again.

This is a production system, so I can't do anything I want until later tonight.  I didn't run into this problem in our test lab.  The difference may be that I created the logins before joining the databases before, whereas this time I tried to create them afterwards, and failed to document the process correctly.  Taking the AG down and rebuilding it is something I'd rather avoid if I can.

The problem makes sense to me, I just don't really know how to fix it.


texts: Returning information in a nested select in the outermost select

texts:
I've got the following working,

SELECT info1, info2
FROM   TableA
WHERE  id IN (SELECT xid
              FROM   TableB
              WHERE  userid=3 AND active=1)

which works fine, but in TableB I also have a team column which I'd like to select and return in the outermost select statement, something like this "pseudo" query, which of course doesn't work:

SELECT info1, info2, team
FROM   TableA
WHERE  id IN (SELECT xid, team
              FROM   TableB
              WHERE  userid=3 AND active=1)

I had a read around and thought maybe AS could help but I don't really know for sure. Can anybody suggest a solution? Is this even possible?


texts: MySQL error 1674 even when binlog is set to ROW

texts:
I am trying to alter table by doing,

ALTER TABLE Account
ADD `AccessCode` binary(16) NOT NULL  DEFAULT (UUID())

It gives me error,

Error Code: 1674. Statement is unsafe because it uses
a system function that may return a different value on the slave.

My Binlog format is Row

show variables like 'binlog_format'

--------------------------
|Variable_name  |  Value |
--------------------------
|'binlog_format'| 'ROW'  |
--------------------------

Though I will find some alternative, but why it is not allowing me? Even after setting binlog format as ROW, what is the problem here?


texts: Simple conditional update causing a deadlock hints at MySQL bug

texts:
I have a very simple "inventory management" update that under load causes InnoDB to deadlock and rollback one of the threads. There doesn't appear to be any ordering issue here, so I'm at a loss how to resolve it. MySQL version is 5.6.33-0

update product
set available_qty = available_qty - 1
where sku = arg_sku and available_qty > 0

Here is the full deadlock dump:

InnoDB: transactions deadlock detected, dumping detailed information.

*** (1) TRANSACTION:
TRANSACTION 50428499, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 3

update product
set available_qty = available_qty - 1
where sku = arg_sku and available_qty > 0

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 596 page no 96 n bits 152 index `PRIMARY` of table `mydbname`.`product` trx id 50428499 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 50428498, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 4

update product
set available_qty = available_qty - 1
where sku = arg_sku and available_qty > 0

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 596 page no 96 n bits 152 index `PRIMARY` of table `mydbname`.`product` trx id 50428498 lock mode S locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 596 page no 45 n bits 680 index `sku` of table `mydbname`.`product` trx id 50428498 lock_mode X locks rec but not gap waiting

*** WE ROLL BACK TRANSACTION (2)

From what I'm reading, the interaction between a shared (S) lock and an exclusive (X) lock causes my concurrent updates to deadlock. Is there anything I can do to avoid this?

Update:

I believe this is a bug in MySQL.

Two concurrent threads acquire Shared-locks and try to acquire eXclusive locks to do the update. They wait for the other to release the Shared-lock first. There is no ordering issue in the way locks are acquired; the mixing of types of locks is the issue. MySQL should be able to "upgrade" the existing shared-lock to eXclusive. If another thread wants to do the same, the first thread should make progress by obtaining eXclusive lock, do the update, then release the lock so the other Shared-lock can be upgraded, and so on.

Workaround

My UPDATE statement was modified to use the PRIMARY KEY (from previous UNIQUE KEY). In addition, I'm first calling a SELECT matching the condition of the UPDATE and skip the UPDATE entirely if the SELECT comes back empty, thus avoiding the eXclusive lock whenever possible. All of this is in a stored proc so clients aren't affected.


texts: Using SPID in DB Tables (instead of Table Variable)

texts:
Transactional database used for booking things...

Our vendor was asked to replace #temptables with @tablevariables (because of heavy compile locks) but instead they replaced with an actual table that adds SPID as a column to ensure the stored procedure only acts on the applicable rows.

Do you see any risk in this method of operation?  Before all transactions were isolated within their own transaction...  I worried we may end up locking this table a bunch but their opinion is that SQL uses row-level locking and this won't create more locks.

SQL Server Version: 2016 Enterprise - 13.0.5216.0

CREATE TABLE dbo.qryTransactions (
    ID int IDENTITY (0,1) NOT NULL CONSTRAINT pk_qryTransactions PRIMARY KEY CLUSTERED,
    spid int NOT NULL,
    OrderID int,
    ItemID int,
    TimeTransactionStart datetime,
    TimeTransactionEnd datetime,
...other fields
    )

CREATE INDEX idx_qryTransactions_spidID ON qryTransactions (spid, ID) INCLUDE (ItemID, OrderID, TimeTransactionStart, TimeTransactionEnd)


texts: How do I find the day of week of a date-time field in postgres?

texts:
How do I find the day of week of a date-time field? I'm trying to extract the the day of the week from a column in a sql query in postgres. While

SELECT EXTRACT(DOW FROM '2011-07-07')

works, the following query does not work and I can't figure it out:

SELECT EXTRACT(DOW FROM TIMESTAMP date) as day
FROM purchases GROUP BY day;

It's like postgres won't let me find the day of the week for fields but only constants. How do I find the day of week for a field?


texts:
Im having some problems with my postgresql server. In particular, some databases cannot be dropped.

On further inspection, I see that the postgres user is not a SuperUser.

There are two other users that are super users:

pgsql
s6cefdco

However I do have the password for these.

How can I make postgres a SuperUser again?

The operating system is CentOs.

I have tried the following:

  sudo -u postgres postgres --single /var/lib/pgsql/9.6/data

Which results in

sudo: postgres: command not found


texts: How to add all combinations of two pairs of key-value to a table?

texts:
I have a database of items, as each item has a set of key-value pairs such

place => london
field => engineering

since I frequently need to find items having the same two key-value sets. I created supporting tables by storing designated ids for each key and value.

CREATE TABLE ItemKeyValues
(
ItemID int(11) unsigned,
KeyID mediumint(7) unsigned,
ValueID mediumint(7) unsigned,
PRIMARY KEY (ItemID,KeyID,ValueID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

CREATE TABLE Pairs
(
PairID int(11) unsigned NOT NULL AUTO_INCREMENT,
Key1 mediumint(7) unsigned,
Value1 mediumint(7) unsigned,
Key2 mediumint(7) unsigned,
Value2 mediumint(7) unsigned,
UNIQUE INDEX(Key1,Value1,Key2,Value2),
PRIMARY KEY (ItemID,KeyID,ValueID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

CREATE TABLE ItemPairs
(
PairID int(11) unsigned NOT NULL,
ItemID int(11) unsigned,
PRIMARY KEY (PairID,ItemID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

(I removed indices and FKs for simplicity)

For example, one row of the table Pairs is

PairID    Key1             Value1            Key2              Value2
1         3 (for place)    88 (for London)   10 (for field)    9 (for engineering)

I need to fill the last two tables.

How can I INSERT all combinations of Key1,Value1,Key2,Value2 from the table ItemKeyValues (KeyID,ValueID)?
How can I UPDATE table ItemPairs from the table ItemPairs?

The performance is of great importance since table ItemKeyValues is over 100M rows (though, this is one-time operation).


texts: One SQL statement different results by using SQLOLEDB or MSDASQL

texts:
I'm using VB6 with SQLOLEDB and MSDASQL. By switching the provider I notice that I'm getting different results with one query.

My query checks some server settings and executes one of two BACKUP statements.

When I'm using SQLOLEDB everything works fine and the backup gets created. If I'm using MSDASQL no error occurs but no backup gets created.

It's the same server, the same user and the same statement.

I've used SQL Profiler and it shows that 100% the same command gets executed. Even if I copy the MSDASQL command and execute it in SSMS the backup gets created, but not with VB6.

My statement:

DECLARE @s varchar(1000), @ver int
SET @ver = 10
SELECT @s = case
WHEN
@ver > 9
THEN
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  COMPRESSION, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'
ELSE
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10;'
END
EXEC (@s)

But when I'm executing:

DECLARE @s varchar(1000), @ver int
SELECT @s = case
WHEN
10 > 9
THEN
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  COMPRESSION, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'
ELSE
'BACKUP DATABASE [DB] TO DISK = ''C:\SQL Backup\Backup.dat'' WITH  NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10;'
END
EXEC (@s)

(without SET @ver = 10) the backup works.

Could someone explain?


texts: What server configuration and database should I use?

texts:
I am new to Database's and want to work toward installing a Database in the small company I work at for production and eventually other uses. The company has only 15 employee's and three departments. There will not be more than five computers accessing the database.

Currently the setup is one physical Domain Controller/file server running windows server 2012 r2 standard. This is a small warehouse that has many part numbers with instructions and pictures, all in multiple books. The office uses quick books and spreadsheets.

My goal is to have each department to access each job by sales order which will pull up all the items instructions, procedures and specifications for the job as it pertains to the department and eventually track production and inventory.

I will appreciate any assistance or guidance and I know there is much to learn but I am willing to take my time and build it in a virtual network for testing.

My thoughts on how and what database to use based on a small company and small budget.

Using SQL Server Express because has more storage than Access and would be scalable, secure, and cost effective. Is this correct?
Installing the SQL Server Express on the Domain Controller because it is a small company and would be cost effective and more simple. I am not sure if this is correct and if this is not recommended can I enable Hyper V on the Domain Controller and install server 2012 again using the same product key without having licensing issues in order to run the server for the database.

If there is more information needed then I will give it and I appreciate any advice and guidance.

I am a student while working and just started Database Design class and have a long way to go but I learn better when I am trying to build something in a real world application. I am willing to make mistakes it the virtual network in order to learn but not in my companies network. I believe I need a Database mentor.

Thanks in advance


texts: find all numbers that appear at least three times consecutively

texts:
This is a question from leetcode online judge

I tried this

SELECT Num FROM Logs GROUP BY Num HAVING COUNT (Num) >1

but got runtime error!!

Anyhelp would be appreciated


texts: migrate from sql server 2012 to sql server 2008 R2

texts:
I have been looking for the information since yesterday and could not find anything...
I have sharepoint 2012 fundation and my SQL Server 2010 Evaluation is short of space (we already used 10GB), however I have SQL Server 2008 R2 license.

Question:

Is it possible to migrate my SQL Server 2012 sharepoint database to
SQL Server 2008? Will the new database be compatible with my sharepoint?
could you please send me a link with the
instructions?

thank you very much


texts:
My question may sound childish, but I am really googling a lot regarding this issue. I have a table tblUsers where 20 columns are present (FirstName, LastName, EmailAddress....), which has around 91,000 data.

When I use Select Query

Select * from tblUsers

It takes around 2 min 27 seconds to complete execution.

Is It really any way to speed up the execution time

Any type of suggestions will be welcomed. Thanks in advance


texts:
In PostgreSQL, I often times want to do something like find the factorial of 7. I can do that very simply with

SELECT 7!;

-- PostgreSQL is so full featured
-- it even supports a prefix-factorial
SELECT !!7;

Even Excel has FACT,

=FACT(7)

How do I do that with SQL Server 2017 Enterprise?


texts: Dependency management by decoupling table name from table key

texts:
If I share a Google Spreadsheet with someone, I will share a link like this:
https://docs.google.com/spreadsheets/d/1UL0Aii1aaErbUm6pdmZ_D9dsqx2p3BPZ5GHxz7TH37M/edit

This means that I am free to edit the name of the file (for example to "Book - absolute final version really7"), the Google Drive folder it is located in etc. etc. while the link will still work. Would it be possible to simulate something similar in a database? The problem I am trying to solve is the intense pain and risk I have to go through every time I need to rename a column, table, view or stored procedure in the database, or if I want to move it to a different schema. Because of this pain, I currently have all sorts of naming of all these things so you will see a primary key lookup looking like this in my database:

select thisIsAStoredProcedure(acolumn) from a_Table_Name where a_Table_Name_ID = 70;

Needless to say, it is impossible to get fluent in the naming of our database objects. Also, because we are very agile with releasing new updates (uptime is not much of a concern so fast is better than correct at first attempt), we keep adding more objects with wrong naming.

Postgres will help me out in some cases, but it will not fix references to a table inside a stored procedure and the same goes for any references from our any application, or configuration script.

What I am after is that if I write a stored procedure then the stored procedure will use the following while running:

select 1UL0Aii1aaErbU from aErbUm6pdm;

While what I see when I read the stored procedure in my IDE is this:

select a_column from a_table;

Another solution could be to add aliases, so after renaming a_table to b_table then querying a_table would still work, but all queries to the a_table would get logged and I could search for those queries that get run towards the old table. This would mean that there would be no risk involved in renaming a table. The same goes for columns, stored procedures etc. Does such a solution exist?

I realize that the first solution requires major work in the IDE but having such an IDE would mean that I could safely rename tables and columns at will.

Maybe a quick-fix solution to this would be a Mac/Windows hack where if any text editor presents the following text to me: 1UL0Aii1aaErbU then that will always get auto-replaced with a_column no matter where it is presented. I believe AutoHotKey.com could achieve this on Windows.


texts:
Given the following table:

CREATE TABLE Test (
 id int(10) unsigned NOT NULL PRIMARY KEY,
 value int(10) unsigned NOT NULL
) ENGINE=InnoDB;

If I insert a value with only the id field set:

INSERT INTO Test (id) VALUES (1);

On my development server, I get a warning:

  Warning: #1366 Incorrect integer value: '' for column 'value' at row 1

On my production server, I get an error:

  ERROR 1364 (HY000): Field 'value' doesn't have a default value

What should I configure on my development server to make it behave like my production server?


texts: Synchronize MySQL databases between local and hosted servers

texts:
I'm developing a software in Microsoft Blend and I need it to be synchronized with my online server MySQL database. I have local MySQL database which I use for the app. The app also have an online version in PHP with a MySQL database. So the user can enter either by the online version or offline app. So I need both the databases to be synchronized so when a data is entered online it will be reflected in the software app. I need separate databases because when the app is not connected to internet, it should work perfectly and when reconnected it should start synchronization both ways i.e. from local to online and from online to local database.

So basically I need 2 MySQL databases to be synchronized both ways where one is local and other online.

Also how to find out when a synchronization breaks?


texts:
If we have a table in SQL Server with the following data:

ID      Log_Time
1110    2016-10-31 20:34:50.000
1110    2016-10-31 20:34:58.000
1110    2016-10-31 20:35:03.000
1110    2016-11-01 01:28:29.000
1110    2016-11-01 01:28:33.000
1110    2016-11-01 01:28:37.000
1110    2016-11-01 01:28:42.000
1110    2016-11-01 01:28:46.000
1110    2016-11-01 01:28:50.000
1110    2016-11-01 01:28:54.000
1110    2016-11-01 01:28:59.000
1110    2016-11-01 01:29:03.000

Let's say each user action generates a log_time entry. The business calculates the number of sessions for billing purposes like this - start with session 1 and for each log activity, if the time difference is more than an hour, increment the session count by 1.

This is a fairly large table with different user ids. I have tried a combination of cursors for looping through distinct users and WHILE LOOPS to increment by doing the session counts iterating row by row. It takes long time to complete and when this table grows bigger, this might not even be the right approach. There must be a better way to do this. Any pointers?

The resultset I need is this:

ID     SessionCount
1110   28
1145   42
1116   38


texts: Replace part of a string in MYSQL, when only the substring's starting characters, ending characters & a unique string in the middle is known?

texts:
I'm trying to find a way to replace part of a string in a database without knowing the exact substring content other than how it starts, ends and has a specific ID string somewhere in between that.

To specify, I have Vimeo iframes that are within post content inside a column in a MySQL database table. I need to replace all vimeo iframes strings in this column over to another video platform script. I have a list to use containing the Vimeo IDs and the new code to go in place of the existing Vimeo iframe, so this gives me a unique identifier to use when searching.

The problem is, some of the Vimeo urls have additional GET queries added to the URL so I can't do a 1 for 1 match of the Vimeo iframe.

I do know for certain that each iframe instance I need to replace will begin with <iframe, end with </iframe> and contain a specific Vimeo ID (ie 1234567). Is there a way to do this with Regex and concat or something similar so that I can replace the entire iframe code with a new string?


texts: Consolidate does not work with my subquery

texts:
Possible Duplicate:
  SQL Subquery , One to Multiple rows

Following query I have written returns the result as expected, example 121350 rows.

SELECT dbo.Articles_in_Consignment.Consignment_id,
  dbo.ORDER_D.Orders_boxsize,
  dbo.ORDER_D.Orders_boxweight,
  dbo.ORDER_D.Orders_boxlength,
  dbo.ORDER_D.Orders_boxwidth,
  dbo.ORDER_D.Orders_boxdepth,
  dbo.ORDER_D.Order_dispatchment_id,
  SUM(
    ISNULL(
       dbo.COMPONENT.Component_weight, 0) *
       ISNULL(dbo.ORDER_ALLOCATE.Order_allocate_qty, 0)
  ) AS Component_weight,
  COUNT_BIG(*) AS _ic
FROM dbo.Articles_in_Consignment
   INNER JOIN dbo.ORDER_D
     ON  dbo.Articles_in_Consignment.Order_dispatchment_id = dboRDER_D.Order_dispatchment_id
   INNER JOIN dbo.ORDERS
     ON  dbo.ORDER_D.Order_dispatchment_id = dbo.ORDERS.Order_dispatchment_id
   INNER JOIN dbo.ORDER_ITEMS
     ON  dbo.ORDERS.Orders_id = dbo.ORDER_ITEMS.Orders_id
   INNER JOIN dbo.ORDER_ALLOCATE
     ON  dbo.ORDER_ITEMS.Order_items_id = dbo.ORDER_ALLOCATE.Order_items_id
   INNER JOIN dbo.STOCKIN
     ON  dbo.ORDER_ALLOCATE.Stockin_id = dbo.STOCKIN.Stockin_id
   INNER JOIN dbo.COMPONENT
    ON  dbo.STOCKIN.Component_id = dbo.COMPONENT.Component_id
WHERE  (dbo.ORDERS.Cold = 'No')
GROUP BY
  dbo.Articles_in_Consignment.Consignment_id,
  dbo.ORDER_D.Orders_boxsize,
  dbo.ORDER_D.Orders_boxweight,
  dbo.ORDER_D.Orders_boxlength,
  dbo.ORDER_D.Orders_boxwidth,
  dbo.ORDER_D.Orders_boxdepth,
  dbo.ORDER_D.Order_dispatchment_id

However,WHEN i TRY TO ADD a subquery following SELECT statement the resulted ROWS increases TO 155550 row due TO subquery returning one TO many ROWS.

,(
  SELECT CASE
    WHEN EXISTS (
      SELECT *
      FROM   dbo.ORDER_ITEMS
      WHERE  Component_id IN (1111, 1111)
             AND Orders_id = dbo.ORDERS.Orders_id
    ) THEN CAST(1 AS BIT)
    ELSE CAST(0 AS BIT)
    END
  ) AS DG

Ordering by OrderId.

How can I solve this so that my consolidation of first query does not break due to subquery?

Current result I am getting with subquery...

Consignment_id    Order_boxsize   Order_dispatchment_id   Component_weight   _ic   DG

--------------    -------------   --------------------    ----------------   ---  --

125                L                121212                    0.639           21   0
125                L                121212                    0.639           21   0

expected result with consolidation.

Consignment_id    Order_boxsize   Order_dispatchment_id   Component_weight   _ic   DG

--------------    -------------   --------------------    ----------------   ---  --

125                L                121212                    1. 278         42   0

https://stackoverflow.com/questions/14352512/sql-subquery-one-to-multiple-rows


texts: Getting "pg_ctl: No such file or directory" when trying to start Postgres server on Mac

texts:
I'm using Postgres 9.5 on Mac Sierra.  My postgres db suddenly died, and so I need to restart it, but I'm getting some errors when I try and run

/usr/local/var/postgres start

below tells the saga ...

localhost:myproject davea$ sudo pg_ctl -D /usr/local/var/postgres start
Password:
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
localhost:myproject davea$ sudo su - postgres pg_ctl -D /usr/local/var/postgres start
-bash: pg_ctl: No such file or directory
localhost:myproject davea$ ls /usr/local/var/postgres
PG_VERSION  global   pg_commit_ts  pg_hba.conf    pg_logical    pg_notify    pg_serial     pg_stat  pg_subtrans  pg_twophase  postgresql.auto.conf
base        pg_clog  pg_dynshmem   pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    pg_xlog      postgresql.conf

What is the right way to start up my Postgres server on my Mac?

Edit: In response to comments, this is how I attempted to impersonate the postgres user and restart the server

localhost:myproject davea$ sudo su - postgres
Password:
localhost:~ postgres$ pg_ctl -D /usr/local/var/postgres start
pg_ctl: could not access directory "/usr/local/var/postgres": Permission denied
localhost:~ postgres$ ls -al
total 128
drwxr-xr-x+ 24 postgres  staff     816 Apr 19  2016 .
drwxr-xr-x   3 root      daemon    102 Oct 26  2016 ..
drwx------+  3 postgres  staff     102 Apr 19  2016 Desktop
drwx------+  3 postgres  staff     102 Apr 19  2016 Documents
drwx------+  3 postgres  staff     102 Apr 19  2016 Downloads
drwx------+  5 postgres  staff     170 Oct 26  2016 Library
drwx------+  3 postgres  staff     102 Apr 19  2016 Movies
drwx------+  3 postgres  staff     102 Apr 19  2016 Music
drwx------+  3 postgres  staff     102 Apr 19  2016 Pictures
drwxr-xr-x+  3 postgres  staff     102 Apr 19  2016 Sites
drwxr-xr-x  40 root      daemon   1360 Mar  4  2016 bin
drwx------  27 postgres  daemon    918 May 26 08:35 data
drwxr-xr-x   6 root      daemon    204 Mar  4  2016 doc
drwxr-xr-x  33 root      daemon   1122 Mar  4  2016 include
drwxr-xr-x   3 root      daemon    102 Mar  4  2016 installer
drwxr-xr-x  61 root      daemon   2074 Mar  4  2016 lib
-rw-rw-r--   1 root      daemon   2476 Jan 11  2016 license.txt
drwxr-xr-x   3 root      daemon    102 Mar  4  2016 pgAdmin3.app
-rwxr-xr-x   1 root      daemon    373 Mar  4  2016 pg_env.sh
drwxr-xr-x  12 root      daemon    408 Mar  4  2016 scripts
-rw-r--r--   1 root      daemon  53807 Feb  8  2016 server_3rd_party_licenses.txt
drwxr-xr-x   4 root      daemon    136 Mar  4  2016 share
drwxr-xr-x   3 root      daemon    102 Mar  4  2016 stackbuilder.app
drwxr-xr-x   3 root      daemon    102 Mar  4  2016 uninstall-postgresql.app


texts:
On a 5.5.23 server (that needs to go to 5.7 at least, I know) I have an InnoDB table with 3 columns (there are a few more that are not relevant here):

TimeC (bigint, unix timestamp)
id (integer)
value (integer).

The table currently has an index on  id. The table contains ~15mil rows. The three columns above all allow NULL values (I suspect that's a first clue here).

The query in question is like this:

select id, sum(value) as TotalV
from table_name
where timeC >=154013600000  and timeC < 1540742400000
group by id
order by TotalV desc;

This query takes about 280sec on our server.

I decided to add an index on the date-related column TimeC.

The query now takes about 260sec, i.e. the improvement is very small.

The "explain" utility shows,  without the new index:

select_type: SIMPLE
table: TableName
type: index
possible_keys: NULL
key: TableName_CC_IDX
key_len: 768
ref: NULL
rows: 14830632
Extra: Using where; Using temporary; Using filesort

...and WITH the new index:

select_type: SIMPLE
table: TableName
type: range
possible_keys: TableName_TimeC
key: TableName_TimeC
key_len: 9
ref: NULL
rows: 540694
Extra: Using where; Using temporary; Using filesort

So my question is, why is it not using BOTH indexes in the latter case when both indexes exist and when both are relevant to the specific query? Generally, what could I do to speed up this query?

(from Comment)

CREATE TABLE TableName (
    id varchar(255) DEFAULT NULL,
    timeC bigint(20) DEFAULT NULL,
    value decimal(42,2) DEFAULT NULL,
    KEY TableName (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> show indexes from TableName\G
        Table: TableName
   Non_unique: 1
     Key_name: TableName_CC_IDX
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 29
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: Index_comment:


texts: SQL Server & Storage Question - What's the right choice?

texts:
I am a young DBA from Germany and I am the administrator for our customers SQL Servers.
I have a question and I really hope that I can explain this in English words.
At the moment I am reading Breant Ozwar's eBook SQL Server Setup Checklist and there is one thing that I am confused about.
I mean this one:
2.5.Provision storage for the OS and for SQL Server We don’t want to pile everything on the C drive, so create other volumes for SQL Server to spread across. Ideally, we’ll have separate logical drives for:
• SQL Server application folders – D:
• SQL Server databases data ﬁles – G:
• SQL Server database log ﬁles (including TempDB) – L:
• TempDB data ﬁle(s) – T:
• Backups – H:
I’ve learned /and it’s an advice from Microsoft two) to use separate drives for DB, Logs etc.
BUT I’m asking myself if this makes only sense when we have a physical server with e.g 5 drives, because every disk has different IOPS.
But when the Server got disks from a big storage (that’s managed by a Storage Controller) do we really need different disks for DB, Log etc.?
I mean the Storage Controller controls the IOPS. So if you have one drive and put DB, Log etc. just in different folders then they got the same IOPS when they are on different disks. Or is this wrong?
I hope you understand what I mean.
But the main question is: Does is make sense to put DB, Log, Temp on different drives when the Server get his drives from a Storage? Or does it make only sense when we have a physical server with physical disks?
Hopefully you can help me with this one, because I am not sure how to configure future Cluster systems (we don’t have much hardware servers).
Thanks in advance for reply and I hope your your doing all good.
Melissa


texts: xml queries with exist and contains giving unexpected results

texts:
I have a query as shown below.

DECLARE @tbl TABLE
(
 id INT,
 col XML
)
INSERT INTO @tbl
VALUES
(1,'<Root>
  <Row>
    <User>xyz</User>
    <Rowid>1</Rowid>
  </Row>
  <Maxrowid>1</Maxrowid>
</Root>'),
(2,'<Root>
  <Row>
    <User>xyz</User>
    <Rowid>1</Rowid>
  </Row>
  <Row>
    <User>mnj</User>
    <Rowid>2</Rowid>
  </Row>
  <Maxrowid>2</Maxrowid>
</Root>'),(3,'<Root>
  <Row>
    <User>abs</User>
    <Rowid>1</Rowid>
  </Row>
  <Row>
    <User>xra</User>
    <Rowid>2</Rowid>
  </Row>
  <Maxrowid>2</Maxrowid>
</Root>
')

--table before---
SELECT  *
FROM    @tbl t1
------------------

DECLARE @id varchar
SELECT @id = 'xyz'
SELECT  *
FROM    @tbl t1
WHERE col.exist('//*/text()[contains(.,sql:variable("@id"))]') = 1

I am expecting this query to output rows which has 'xyz' in xml column 'col' anywhere. But it returns all rows.(when i checked it returns all rows having 'x' in xml column. So please tell me why does it happen?where i am going wrong?Also please correct it.

Also i have another problem where below query for the same above xml does not return any output.I expect it to return xml column having 'xyz' anywhere in it.So please have a look at it also and correct my errors.

DECLARE @id varchar
SELECT @id = 'xyz'
SELECT  *
FROM    @tbl t1
WHERE t1.col.exist('//*[text()=sql:variable("@id")]')=1


texts: Can SQL Server 2012 FCI have multiple names?

texts:
Is it possible to have multiple names for the same SQL Server Failover Cluster Instance (virtual server)? IT has a naming convention they like, but it hard codes the Windows cluster name into the SQL Server cluster name, which makes extra client configuration work when we have to migrate servers.

The last time they migrated (from SQL Server 2000 to SQL Server 2005 in 2000 compatibility mode), I think they just added another (DNS) hostname alias, but they only had one FCI. We are now migrating to SQL Server 2012 and we will have two FCIs ("active/active", although I know that's not really the right term), so we can't just use a default instance.

Note that I'm not the one who will be implementing the cluster, but I am migrating the databases and the client software, so I'm making the requests to the guys who are implementing the cluster. I don't want to ask them to do something that can't be done or isn't a good idea.


texts:
I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list.

Unfortunately, the gap from the given timestamp to the first value timestamp for one foreign key may be many times this gap for another FK. I've had a go doing something like this, but it only gives me one row, rather than one row per fk.

SELECT *
FROM   data_item
WHERE  fk_fc_id IN (35246,35247)
AND    di_timestamp > '2013-11-01 00:00:00'
ORDER  BY di_timestamp ASC
LIMIT  1;

It's like I need to take the limit on a per FK basis, but can't work out how to do that.


texts: Restoring database backup makes identity column start at 1 again

texts:
I created a backup of our database in SSMS 2012, using Tasks -> Backup as a Full Backup, including all data.

When I restore this backup on another server (2012 as well), all identity columns are reset to start at 1, which in turn messes up the ORM library (NHiberate) the application uses.

An example is the accounts table. After I restored it and created a new account, that account's ID started with 1, although on the production system that I backed up, the IDs were up to 2000 or something.

CREATE TABLE [accounts](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](255) NULL,
    [password] [nvarchar](255) NULL,
    [passwordSalt] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
) WITH (PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    FILLFACTOR = 90) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
    [name] 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]

Why is this happening and how can I transfer the database, including all data so the identity columns are properly restored?

The backup file only contains two sets, the Data and Log entries.

Running dbcc checkident('accounts', noreseed) on the source server yields Checking identity information: current identity value '2816', current column value '2816'. On the target server, it returns 1 as the current column value.


texts: Multiple SQL queries fired asynchronously progressively slow down

texts:
We are using .NET Core Web API 2. We have a vue client that makes calls to this Web API 2 API method. We have a report dashboard page. On this page we have 13 charts which means 13 calls are made to an API endpoint to get the data for the charts.

If we run the 13 queries in SQL individually all of them run within a second or less. I created a console app in which I ran the 13 queries in a loop and captured some timings. Every query ran in less than 2 seconds.

I then captured some timings in the API method. I captured the start time, end time and duration. All the 13 calls were made within a second of each other. The bottle neck seemed to be around the the code that ran the SQL query. The query timings ranged from less than a second to over 11 seconds:

147
250
250
274
1499
6758
6795
7191
7671
7436
9275
9450
12481

Please note these are times in ms and is only capturing the time the query takes in the API method. I also used SQL Profiler and saw the timings there and they were similar.

It has something to do with making multiple API calls asynchronously. If I made an API call one at a time for each query it will give times of less than a couple of seconds.

I am running the code in C# in such a way that it does not lock the table / database for example I am using isolation level of snapshot. I have also tried to use isolation level or readuncommitted and the problem is still there.

We are using Windows Server 2019 with 2 processors and 10 GB Memory. SQL Server version is 2017.

I have done a few days of research and tried a few things but nothing seems to over come this problem. I needed some advice on what could be the cause of this issue and how to overcome it.

Just to add each query returns a single row of data that is consumed by the chart. I also ran the combined 13 queries in sql management studio and it took 8 seconds for the entire query to process.

I also noticed that when I access the dashboard the server cpu usage goes through the roof hitting 100% for a few seconds.

Update:

Execution plan when I run all 13 directly in SQL Management Studio:

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

Execution plan for one of the queries that was triggered via the api code i.e. when I access the dashboard It took 12+ seconds to finish:

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

Execution plan for the same query if I run it alone via sql it takes 2 seconds:

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


texts: Relational Design - One table, two foreign keys or Two tables, one foreign key each

texts:
Looking for some advice related to optimal design in the following scenario.

There is a Cases table (represents cases of inventory)
There is a LocationInventory table (represents locations with inventory)
Then I have an InventoryNeed table or tables (this is the crux of the question), that needs to account for cases and locations.

Option A:

One table with 2 foreign key columns in which one and only one of the foreign keys would be populated.

Table: Inventory Need

CaseId (FK)
LocationInventoryId (FK)
NeededQuantity

In this case either CaseId or LocationInventoryId would null and the other populated.

Option B:

Two tables for each need type that will frequently be UNION'ed to get summary data.

Table: InventoryNeedsCases

CaseId (FK)
NeededQuantity

Table: InventoryNeedsLocations

LocationInventoryId (FK)
NeededQuantity

Option C:

One table with no referential integrity.

Table: InventoryNeedsCases

NeedType (values of Case or Location)
NeedId (represents the primary key of either Case or LocationInventory based on NeedType).
NeededQuantity

And the winner is? I'd probably narrow it down to say A or B to ensure data integrity...but not sure which is best from there. Or maybe there is an option D (like creating a base table with common columns...)

UPDATED SCENARIO

When I posted this last night, I was only thinking downstream, but there are also upstream dependencies to these same tables. I made some drawings that may hopefully explain it better. With this advent, option B starts to explode the nbr of tables involved, and after reading this SE answer...I am now leaning more towards A.

Pics below. Then red represent Inventory Needs, Green represents Inventory sources to meet those needs. And yellow is the question at hand...how to link the reds and greens efficiently.

Option A Pictorial - with more explanation and context

Option B - removed context for clarity


texts: How to insert into view through GUI in pgAdmin?

texts:
I have some updateable views for inserting the data. The reason is not just "security layer", inserting into some 10 tables directly would be a pain without the view with INSTEAD OF trigger. In complete production, there will be some client app doing the inserts, but it is not ready now. A customer doesn't want to wait for the app, but also doesn't want to learn SQL just to insert few rows. Inserting the data through pgAdmin would be ideal. Unfortunately, pgAdmin shows a "new row" while inspecting data only for tables, not for views.

So is it possible to enable inserting into updateable views in pgAdmin? If not, is it possible in any other PostgreSQL GUI?

It is not just for this customer - we plan to offer the database without the client app as an opensource "demo", and we would like this demo to have some value for SQL-inept potencial customers too.


texts: Correcting the imported latin1 data to be utf8

texts:
I restored my drupal site database from a backup but after i finished i discovered that the Imported data was symbols like this Ù‚ÙˆØ§Ù†ÙŠÙ† ÙƒØ±Ø© because the backup was taken using latin1 charset but my tables and database are utf8

I want a way to convert this data to utf8 so the are stored as letters and words in my database not symbols

How can i do this ?


texts: I've worked with SQL Server for some time and I understand the various issues that one can run into with TempDB. But, with this latest issue, if I have my issue figured out correctly, is it possible that a statement with a BEGIN TRANSACTION that uses tempdb tables/objects that is left hanging (no commit) can hold the tempdb LOG file "open" and not allow tempdb to clear its own log? This leads to the tempdb log file eventually filling up and causing issues in the SQL Server.  I find this hard to believe that there isn't some sort of safeguard in place within SQL Server that would somehow allow the tempdb LOG to not be affected If not, I find this disturbing…


texts: PostgreSQL parses string literal as record before calling cast

texts:
In my PostgreSQL 9.6 database, I have a composite type, let's call that type widget.  I have a PL/pgSQL* function called text_to_widget that parses textual representations of widgets to widget values, and I have an implicit cast that calls this function:

CREATE CAST (text AS widget) WITH FUNCTION text_to_widget(text) AS IMPLICIT;

My cast is working well from variables or table values, but when I try to use the cast (either implicitly or explicitly) from string literals, I get an error:

postgres=# select 'my widget value'::widget;
ERROR:  malformed record literal: ""
LINE 1: select 'my widget value'::widget;
               ^
DETAIL:  Missing left parenthesis.

If I explicitly cast my string literal as text first, then it works fine:

postgres=# select 'my widget value'::text::widget;
      widget
-------------------
 (my,widget,value)
(1 row)

I'm just confused why Postgres is trying to parse my string literal as a record before passing it to my cast, and if there's a way to make it stop doing that.

* Note that this database is hosted in AWS RDS, so I can't use a C function for the cast.


texts:
The db is SQL Server 2012. All data tables are in the dbo schema. All data access is done via stored procedures in a different schema (user rt). I want to add query notifications for some tables, so I follow recommendations from these links: Link1 and Link2:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO rt;
GRANT CREATE SERVICE to rt;
GRANT CREATE QUEUE to rt;
GRANT CREATE PROCEDURE to rt;
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO rt;
GRANT RECEIVE ON [dbo].[QueryNotificationErrorsQueue] TO [rt];

The statement is select Id, Name, UpdateDate from dbo.Reference

The user RT has EXECUTE permission on the stored procedure and SELECT permissions on dbo.Reference table.

After SqlDependency.Start I can see a stored proc and a queue created in the RT schema. I can also see a new service created under ServiceBroker.

When I execute the SqlCommand with the user RT, I get an immediate notification with Info = Invalid, Source = Statement. If I execute the SqlCommand as SA, all works fine.

(Further research (Link3) suggests that I need to grant CONTROL and IMPERSONATE from RT to DBO. This didn't fix the issue either.)

Do you have any suggestions on what might be going on?


texts: SQL Server symmetric key & certificates games by Hacker?

texts:
1)

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';

I'm trying to figure out about the SQL Server encryption.

once I've executed the code above, is there any way to find out later what is the password value for the SecureSymmetricKey ?

2)

If now I'm doing this with Certificates : I`m the admin and created:

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'DB Master key password!'
GO

only I know the password.

later I :

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'My Certificate Subject'

CREATE SYMMETRIC KEY MySymetricKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyCertificate

until now , Its all ok.

Now, when a hacker comes to the computer, all he has to do is :

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

and then :

SELECT
       convert( NVARCHAR(max), decryptbykey(namePAss))
FROM  tbl1

so where is the protection in certificates? No one asked him for a password (like in password encryption (as in my first question)...? He only needed to know the certificate name

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

and its not a problem to find out what is the certificate name ...So where is the protection in decypher data from the hacker ?


texts:
I have tables
company:

    company_id |
- - - - - - - - - - - - - -
      1        |
      2        |

employee:

employee_id |
- - - - - - - - - - - -
      1     |
      2     |

They are connected via table employee_company:

employee_id | company_id
- - - - - - - - - - - -
      1     |     1
      2     |     1
      1     |     2
      2     |     2

Also there is some factor, connected to company via table:

factor_id | company_id
- - - - - - - - - - - -
    1     |     1
    1     |     2
    2     |     3

Also there is score table, and for each score there is unique pair of company and employee:

score_id | employee_id | company_id | score |
- - - - - - - - - - - - - - - - - - - - - - - -
   1     |     1       |      1     | 21
   2     |     1       |      2     | 30
   3     |     2       |      1     | 50
   4     |     2       |      2     | 11

The goal is to get table having set of rows presenting min score for each employee, filtered by factor, something like that:

  | employee_id | score |
 - - - - - - - - - - - - -
        1       |  21
        2       |  11

Now I built query joining all tables and showing all scores:

select * from employee
join employee_company on employee.employee_id=employee_company.employee_id
join company on company.company_id=employee_company.company_id
join score on score.employee_id=employee.employee_id and score.company_id=company.company_id
join factor_company on factor_company.company_id=company.company_id
join factor on factor_company.factor_id=factor.factor_id
where factor.factor_id =:factor_id;

How to reduce it to what I need? Thank you.


texts: Is it possible to extract the multiple variable parts of data into different columns without a procedure?

texts:
I'm currently trying to do an analsis of a table containing a couple thousand rows of data. I have managed to restrict the data to what I really need, but I'm not sure if I can split it up further without a procedure to help.

The table itself has an index on the column myCol and the rows contain different parts of data (example rows for MyCol):

MyText
MyText2\nX1: [S 1: 3] (S 3)\nX2: [S 1: 2] (S 2)\nX3: [S 75: 1] (S 75)Further text2
MyText3\nX2: [S 800: 3] (S 2400)Further text3

Now what I want is to get all rows that have X1 - X4 set. That is no problem:

Select myCol from myTable where myCol LIKE '%X_: [S%] (S %)%'

But then I want to split the data for X1-X4 into 1 output column each. As they can be at different positions within the column (and it is not necessary that all of them appear) the question is if this is doable without having to rely on a procedure (I have very limited permissions on that sql server so I have to avoid procedures where possible).


texts: upgrade mysql 5.1 to 5.5 on Ubuntu 11.10

texts:
I currently have mysql server 5.1  installed via apt-get on my production Ubuntu 11.10 server

root@Ubuntu-1110-oneiric-64-minimal$ dpkg --get-selections | grep sql-server
mysql-server                    install
mysql-server-5.1                install
mysql-server- core-5.1          install

I would like to upgrade this to 5.6, but the mysql docs seem to suggest upgrading to 5.5 first, and from there to 5.6.

I've seen various lengthy guides describing how to upgrade from 5.1 to 5.5, but they all seem to describe how to upgrade by installing the tarball rather than using the package manager. Is there a simpler to upgrade using the package manager if the current version was installed using apt-get.

Obviously I want my existing configuration and databases to be retained after the upgrade and I will be sure to backup my databases using mysqldump before performing the upgrade.


texts: MySQL/MariaDB extract multiple values from a JSON column

texts:
My goal is to extract about 20 fields out of JSON column with 100 fields, some are strings, total length of json text is around 40kb.
The only way I found it's possible is to write it like this:

select json_value(json, '$.field1') field1, json_value(json, '$.field2') field2, ... from json_table where id=123;

This there a better way?

I played with json_extract - even though it's possible to give it multiple paths, it will skip missing keys and so I won't be able to connect the response to the fields.

Is there overhead of using json_value multiple times in a query or MySQL will do it efficiently (only parse the column once)?
At which point do you think it's more efficient to just return the whole json text and parse in the application?

Thanks


texts: Error during MySQL encryption, Code: 3185. "Can't find master key from keyring (...)"

texts:
I was trying to configure MySQL keyring plugin and I did the following:

• Made sure that keyring_file.dll is present either in C:\Program Files (x86)\MySQL\MySQL Server 5.7\lib\plugin directory.

• added to my.ini:

innodb_file_per_table = 1
early-plugin-load = keyring_file.dll

• restarted MySQL service and even the whole dedicated Windows Server
• run the following SQL(s):

ALTER TABLE <table_name> ENCRYPTION='Y';

and I got the following error:

  Error Code: 3185. Can't find master key from keyring, please check in
  the server log if a keyring plugin is loaded and initialized
  successfully.

Very strange, because I did the same on other server with the same version of MySQL and it worked.

I executed this:

SELECT @@plugin_dir

and it shows right directory:

  C:\Program Files (x86)\MySQL\MySQL Server 5.7\lib\plugin\

WHY do I get that error ??


texts: BULK INSERT clent and session requirements

texts:
I'm setting up an ETL process using SQL Server 2014 and SSIS. My Bulk Insert Task is utilizing a source file and format file on ShareA targeting a DB on InstanceA. The package is run via SSA job on InstanceB. Both InstanceA and InstanceB are running the same service account for the Agent. The service account has bulkadmin and read/write privs on the target table. It also has appropriate permissions on both the share and folder via NTFS.

When the SSA job runs on InstanceB, the job fails on the step with code 5 for Access Denied attempting to access the source file. However, if I run the BULK INSERT T-SQL generated by the task in a job on InstanceA it succeeds, confirming share/NTFS permissions are OK.

Am I forced to run the SSIS package on my target server InstanceA to allow BULK INSERT to function or are additional permissions needed in order to run on an alternate server?


texts: SQL Server 2008 R2 - Unable to delete Login

texts:
I've been using this site for a while and this is the first time I've had to ask a question, but just wanted to say "Thank You" to everyone who has helped me in the past (without knowing they did so).

First things first.. I am trying to delete a user. To locate the user, I execute the following:

USE [MASTER]
SELECT SUSER_SID('henryhr')

Which returns:

0x01050000000000051500000084BC6CEC253AF11EEEFBBA6A240F0034

When I try this:

SELECT *
FROM sys.database_principals
WHERE sid = 0x01050000000000051500000084BC6CEC253AF11EEEFBBA6A240F0034

It returns 0 rows. If I try:

USE [MASTER]
DROP LOGIN henryhr

I get:

  Msg 15151, Level 16, State 1, Line 1
  Cannot drop the login 'henryhr', because it does not exist or you do not have permission.

I have tried creating another user and going through the same process and it works just fine. Any idea how to delete this?


texts:
We have database backups that we sometimes have to inject into the databases concerned.
If we copy the backup to the VM hosting mysql, then we perform the import, it is very fast (3 minutes on average).
But if we import from the VM that hosts the backups to the one that hosts the mysql, the import is much longer (15 min on average).
The import uses the private network to which the VMs are connected. What is strange is that these 2 VMs are on the same VMware host. The write speed should only depend on the speed of the host disk (10 Gb / s).
So there is something blocking, but we can't seem to find what.
Please, Help!


texts: Disallow empty strings in text columns (Aurora / PostgreSQL)

texts:
We have a Django application connected to an Oracle database running in production and we are planning to create a new instance on AWS. Both instances would coexist.
On AWS we would like to use Aurora (which is compatible with PostgreSQL), but we found that there is a difference in how both databases handle text columns.
PostgreSQL allows both empty and null strings. Oracle does not allow empty strings and always uses null instead. Django recommends working with empty strings and handles this transformation transparently as it knows the difference between different db backends.
The problem is that using the same source code, the data stored in each database would be different and would affect us, for example, when performing data analytics. To check if a column is empty, we would need to filter by empty string in Aurora and by null in Oracle.
Is it possible to configure Aurora to behave like Oracle in this particular case? We would like to NOT allow empty strings in Aurora (automatically make them null).
Thanks in advance.


texts: mssql-service error exit with status 255 ubuntu

texts:
I cannot run mssql immediately following an apt-get update. Is this due to a bad script (below: The file "C:\Install\SqlTraceCollect.dtsx" does not exist)? Any suggestions on a fix? I'm at a loss.

sudo systemctl start mssql-server
sudo systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
    Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
       Active: active (running)
        ...
sudo systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
    Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
       Active: inactive (dead) (Result: exit-code) since Wed 2018-03-21 22:46:15 PDT; 1min 27s ago
         Docs: https://docs.microsoft.com/en-us/sql/linux
      Process: 2977 ExecStart=/opt/mssql/bin/sqlservr (code=exited, status=255)
     Main PID: 2977 (code=exited, status=255)
    ...
        sudo cat /var/opt/mssql/log/errorlog
        2018-03-21 22:36:24.19 Server      Microsoft SQL Server 2017 (RTM-CU5) (KB4092643) - 14.0.3023.8 (X64)
        ...
        2018-03-21 22:36:35.90 spid6s      Creating SSIS folders...
        ...
        2018-03-21 22:36:35.91 spid6s      Creating procedure [dbo].[sp_syscollector_upload_instmdw]...
        2018-03-21 22:36:35.92 spid6s      Uploading data collector package from disk: C:\Install\SqlTraceCollect.dtsx
        2018-03-21 22:36:35.92 spid6s      Uploading data collector package from disk: C:\Install\SqlTraceCollect.dtsx
        2018-03-21 22:36:35.93 spid6s      Error: 4860, Severity: 16, State: 1.
        2018-03-21 22:36:35.93 spid6s      Cannot bulk load. The file "C:\Install\SqlTraceCollect.dtsx" does not exist or you don't have file access rights.
        2018-03-21 22:36:35.93 spid6s      Error: 912, Severity: 21, State: 2.
        2018-03-21 22:36:35.93 spid6s      Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
        2018-03-21 22:36:35.95 spid6s      Error: 3417, Severity: 21, State: 3.
        2018-03-21 22:36:35.95 spid6s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
        2018-03-21 22:36:35.96 spid6s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.


texts: How do I efficiently query for records that share a year?

texts:
I've got data that looks like
data
uid  id  date        value
1    a   1780-01-01  10
2    a   1780-01-02  11
3    a   1780-01-03  12
4    b   1780-01-01  10
...
999  a   1980-01-01  10

I'd like to know which ids have records between 1880 and 1900.  I currently
generate this big query (which I believe works):
SELECT year1880.id FROM
(SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1880") as year1880

  INNER JOIN
  (SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1881") as year1881
  ON year1880.id = year1881.id

  INNER JOIN
  (SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1882") as year1882
  ON year1880.id = year1882.id

  ...

  INNER JOIN
  (SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1900") as year1900
  ON year1880.id = year1900.id
  ;

Is this the most efficient way to make this query?  Could it be done in a closed
form so I don't need to generate a giant query with python?
The database is sqlite.


texts: Converting dbo file from windows to linux

texts: I have a .dbo file which is created from windows. This file is succesfully reloaded into the mysql database in windows. I need to reload the dbo file into the mysql/mariadb database in linux. How I convert the file that was created from windows to linux?


texts: How to find database usage for a specific application action

texts:
A user is opening a page in our application which takes around 35 seconds.
I want to see what is happening in the database during this time. What's the best way to do this, doing a trace?
Thanks!


texts: Designing a database for a scenario with multiple user types

texts:
I'm working on a project with multiple user types such as Contributor, Student, Sales, CS, Operator, etc. I usually separate them into different tables, and for each entity which is Authenticable I create a table called Credentials with a one-to-one relationship.

My questions are:

What is exactly the best practice when dealing with multiple user that are "authenticable" like that?
What are the deficiencies of my design?
Is it necessary to "move" Username and Password into each type ofUser entity?

Thank you for your help. Please give me a suggestion if my question is hard to understand because I'm not an English expert.


texts: How to generate a reference key between rows

texts:
I would like to group rows under a reference number.

My current approach is this:

CREATE TABLE `txn` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `reference` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

SET @ref := (SELECT FLOOR(RAND() * 9999999999) AS ref FROM txn WHERE "ref" NOT IN (SELECT reference FROM txn) LIMIT 1);

-- They belong together
INSERT INTO txn (reference) VALUES (@ref), (@ref);

This seems to work fine. The only concern is that I have to dump CSV files with around 50 to 100 thousand entries and I do worry about the performance. On every new reference it needs to SELECT to check if the reference already exists.

I would really appreciate other approches or hints how to improve the query.

EDIT: If this can be solved with a sequence, this would be also quite nice.


texts: Add 0 into array_agg if there is no matching data

texts:
I wrote this view, and it is selecting almost everything I want, but I do not get results for all: project_keywords(which has_many project_report(keyword_id, project_id))

So a project_keyword has a data saved into project_report and the data is stored into position column but there are times when there is no data saved for a project_keyword.

When there is no data saved, I would like to add 0 into array_agg(pr.position).

 SELECT
  k.name,
  pr.project_id,
  array_agg(distinct pr.position) as pr_position,
  date(pr.created_at)
FROM
  project_keywords pk
INNER JOIN keywords k on pk.keyword_id = k.id
INNER JOIN project_reports pr USING (project_id, keyword_id)
WHERE
  date(pr.created_at) = '2014-03-27' AND pr.project_id = 2
GROUP BY k.name, pr.project_id, date(pr.created_at);

Also how can return results where array_agg(distinct pr.position) SIZE(no of elements) is > 1

Edit:

SELECT
  k.name,
  pr.project_id,
  COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]),
  date(pr.created_at)
FROM
  project_keywords pk
INNER JOIN keywords k on pk.keyword_id = k.id
INNER JOIN project_reports pr USING (project_id, keyword_id)
WHERE
  date(pr.created_at) = '2014-03-27' AND pr.project_id = 2
GROUP BY k.name, pr.project_id, date(pr.created_at)
HAVING array_length(COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]), 1) > 1;

This query is returning all the project_keywords which have project_reports row's, but they are not returning 0 for project_keywords which do not have associated rows in project_reports.


texts: How to get the information of the optimizer of PostgreSQL?

texts:
I am curious about the information like

total optimization time
the time spent on each stage of optimization
the number of alternative plans and
the cost of each alternative plan.


texts: Can't optimize a nested query using indexes

texts:
There are two tables containing hundreds of thousands of records: client and task. It's necessary to query the following:

SELECT
  id,
  (SELECT status
   FROM task t
   WHERE t.client_id = c.id AND t.status != 2
   ORDER BY task_date DESC
   LIMIT 1) AS task_status
FROM client c;

This query takes about five minutes, which is unacceptable. So, I tried to create indexes in the following ways:

CREATE INDEX CONCURRENTLY some_name_idx
  ON task (status, client_id);

  execution: 1m 1s 475ms, fetching: 21ms

CREATE INDEX CONCURRENTLY some_name_idx
  ON task (status, client_id, task_date DESC);

  execution: 1m 3s 269ms, fetching: 34ms

As you can see, the result is not impressive. Another one approach isn't correct, but it helped to achieve the expected result:

CREATE INDEX CONCURRENTLY some_name_idx
  ON task (client_id, task_date DESC)
  WHERE status != 2;

  execution: 229ms, fetching: 36ms

I can't use this version because similar subqueries used in our system will get an incorrect result because of WHERE status != 2 clause, which specific only for a single query.

I would be grateful for help on query optimization in such cases.

Thanks.


texts: How to get dependencies between tablespaces?

texts:
For example, we have tablespace1 and tablespace2.
The tablespace1 has the table called table1 owned by soe user.
The tablespace2 has a table called table2 owned by soe user, which has a foreign key referencing the table1 in tablespace1.
How can I get for example, all dependencies of tablespace1 in others tablespaces such tablespace2.. tablespaceN?


texts:
I understand that TRIGGERs are useful, as they take care of subsequent queries, but do they have an advantage from performance point of view too?

Replacing two or three queries as

INSERT INTO table1 ...
UPDATE table2 ...
UPDATE table3 ...

with a trigger-based query as

INSERT INTO table 1

with trigger

CREATE TRIGGER test
AFTER INSERT ON table1
FOR EACH ROW BEGIN
UPDATE table2 ... WHERE id=NEW.id;
UPDATE table3 ... WHERE id=NEW.id;
END

Do we still have the same three queries with identical performance? I mean does it matter if we perform the subsequent queries or the TRIGGER does?
What if we use an API (like conducting queries through PHP). The subsequent queries are internal with no need to connector/driver. Does the TRIGGER improve the performance?

Additional Information: The database is mysql 5.5 with innoDB.


texts: Database Dump/Backup but only to specific records?

texts:
This question is about MySQL Backup (or Dump) whereby i want to backup and store information from only information related to a person; SchoolA from the School Table in this example.

So, I should not be able to see any information from other schools using that backup.

I want to give this backup information to that school. However, i do not want them to see information from other schools.

Is there anyway to achieve this?


texts: ib_logfile opened with O_SYNC when innodb_flush_method=O_DSYNC

texts:
mysql will open and flush data/log files as below when innodb_flush_method=O_DSYNC

Open log    Flush log   Open datafile   Flush data

O_SYNC       NONE           NONE         fsync ()

My First question is ,why do we need to Use O_SYNC to open log instead of O_DSYNC ?

With O_DSYNC, each transaction require one write, but with O_SYNC it requires two writes. I think that would make a big difference.

And I found another option in percona server  ALL_O_DIRECT: use O_DIRECT to open both data and log files, and use fsync() to flush the data files but not the log files.

Tested this option use sysbench OLTP , single thread

response time changes from 20.11ms to 8.3 ms

Test option O_DIRECT_NO_FSYNC on mysql 5.7, it still call fsync to flush log files

  strace -p `pgrep -x mysqld`  -ff -e trace=desc >strace.out 2>&1 &
  tail -f strace.out |grep "(8\|(9"

  [pid 12258] pwrite(9, "\200\10K\376\2\0\0N\0\0\0L.\0\4\0\2018\0300\2018\2\0\2018\0304\226\240\4\0"..., 1024, 26740736) = 1024
  [pid 12258] fsync(9)                    = 0
  [pid 12258] pwrite(9, "\200\10K\377\2\0\0\25\0\0\0L\10\215\247\3\4\0\0\0\1\247\f\240\37\0\6\0\1\200\4\200"..., 1024, 26741248) = 1024
  [pid 12258] fsync(9)                    = 0

Second Question ,beside use ALL_O_DIRECT, any other method could stop innodb from writing metadata of log file into storage device ?


texts: I am trying to restore my database using point in time using .bak file. I selected the back from using the from device option. File is shown its type is full as i give the some back data and time. The .bak file shown disappear. what I am doing wrong here


texts: How to use explode mysql function in where condition with cakephp

texts:
I am using cakephp 2.6. I have payment_method fields in mysql table which store the values like 54,52,59,57 .

Now my question is that how can i check if new posted payment method id is available in  the payment_method value.

Here $data['PaymentMaster']['payment_method'] = 52

$paymentDetail = $this->PaymentMaster->find('first',array('conditions'=>array('NOT' => array('payment_method'=>$data['PaymentMaster']['payment_method']))));

I want to check if value not in database then add in to database other wise fire an error that Error: The rule is contradicting with the other rule, please check the rule.

Any help would be appreciated. Thanks in advance


texts:
I have this three tables:

student table

| _id | name      |
| --- | --------- |
| 1   | Student 1 |
| 2   | Student 2 |
| 3   | Student 3 |
| ... | ...       |

subject table

| _id | name      |
| --- | --------- |
| 1   | Subject 1 |
| 2   | Subject 2 |
| ... | ...       |

relationship

| student | subject |
| ------- | ------- |
| 1       | 5       |
| 1       | 4       |
| 2       | 7       |
| 3       | 8       |

What I want is make a query that looks like this:

| student   | subject1  | subject2  |
| --------- | --------- | --------- |
| Student 1 | Subject 5 | Subject 4 |
| Student 2 | Subject 7 | NULL      |
| Student 3 | Subject 8 | NULL      |

Each student has 1 or 2 subjects at most.

Tried with

SELECT st.name AS student,
       su.name AS subject1,
       su2.name AS subject2
  FROM student AS st
       JOIN
       student_subjects AS ss ON ss.student = st._id
       JOIN
       subject AS su ON ss.subject = su._id
       JOIN
       subject AS su2 ON ss.subject = su2._id
 GROUP BY st._id;

but the result is

| student   | subject1  | subject2  |
| --------- | --------- | --------- |
| Student 1 | Subject 5 | Subject 5 |
| Student 2 | Subject 7 | Subject 7 |
| Student 3 | Subject 8 | Subject 8 |


texts: How to optimize concurrent restoration of multiple VLDB user DBs?

texts: For purposes of optimizing native sql backup (full & differential) restore times, can you run concurrent restore commands for two separate VLDB native backups (10+ tb a piece) on a secondary sql server?  If so what are the recommendations for optimizing this process (sql version, san configuration, processors, memory, etc)?


texts: Why does elimination of an if-statement cause notable speed up in my function?

texts:
I have the following table:

CREATE TABLE [dbo].[StateChanges]
(
    [RecordId] uniqueidentifier NOT NULL,
    [LinkedObjectId] uniqueidentifier NOT NULL,
    [ChangeType] TINYINT not null,
    [ChangeTime] DATETIME not null,
    -- some other columns too
)

with the following indices:

-- yes, clustered over GUID. I know it's not very good.
CREATE CLUSTERED INDEX StateChangesIndex ON
    [StateChanges](RecordId)
GO

CREATE INDEX LastChangeIndex_Object_Type_Time] ON
    [StateChanges] (LinkedObjectId, ChangeType, ChangeTime)
    INCLUDE(SomeColumnsHere)
GO

And I have a function that goes like this initially:

CREATE FUNCTION [dbo].[ObjectHasInterestingChanges_v1]
(
    @objectId uniqueidentifier
)
RETURNS BIT
AS
BEGIN
    IF (SELECT TOP(1) 1 FROM StateChanges
        WHERE
    LinkedObjectId=@objectId AND
            (ChangeType = 1 OR ChangeType = 2 ) )
        IS NOT NULL
    BEGIN
        RETURN 1;
    END
    RETURN 0;
END

This function is being called for a large number of rows processed by an outer query - one thousand time for the very same three thousand rows.

If I try to SELECT ObjectHasInterestingChanges_v1(something) all I have is a "constant scan" in the plan which isn't interesting so I extract the contents into a separate query and look into the execution plan. The plan is as follows initially:

"index scan" over LastChangeIndex_Object_Type_Time - as expected
"top" of "index scan result"
"left outer join" result of "top" with some "constant scan" result
"compute scalar" of "left outer join result"
"cond with query" of "compute scalar"
that's it

I look into sys.dm_exec_query_stats table and note the number of execution for this function, number of reads and time taken.

I then change the function like this:

CREATE FUNCTION [dbo].[ufn_ObjectHasInterestingChanges_v2]
(
    @objectId uniqueidentifier
)
RETURNS BIT
AS
BEGIN
    DECLARE @result BIT
    SET @result = 0
    SELECT TOP(1) @result=1 FROM StateChanges
        WHERE LinkedObjectId=@objectId AND
           (ChangeType = 1 OR ChangeType = 2 )
    RETURN @result;
END

It yields exactly the same results. If I look into the plan it's the following now:

"index seek"
"top"
"compute scalar"
"select"
that's it

I run the very same outer query in the same database such that exactly the same data is being processed and exactly the same data is being read and the background workload is exactly the same (zero) and so I'm sure that the comparison is fair and reasonable.

I look into sys.dm_exec_query_stats again. I see that

the number of executions is the same for v1 and v2 - large number close to three millions
the number of logical reads, physical reads and physical writes is the same for v1 and v2
"worker time" is 53.5 seconds for v1 and 48.3 seconds for v2 - 10 percent speed up
"total time" is 86 seconds for v1 and 67 seconds for v2 - 22 percent speed up

Why is this? Even if "worker time" is decreased because if-statements are somehow time consuming why would "total time" decrease even further? "total time" also accounts for I/O and "total time" decreasing more than "worker time" would imply some I/O speedup which is completely unreasonable here.

Where does this notable difference in both "worker time" and "total time" come from after such minor code change?


texts: How to relate the middle table with One or Many relationship?

texts:
I am currently developing a simple CRM system. These are my tables: users, roles, role_users, customers.

Rules:

A user can only be assign with a single role
A role can have a 1 or many users
A Customer is assigned to a user

Question:
Did I mark the correct relationship in the screenshot above?


texts: PostgreSQL json array of objects, default 0 values for properties if not found

texts:
So my query looks like this:
select dk.product_id, dk.product_name,
  json_agg(
    json_build_object('location', dr.location, 'quantity', dr.quantity))::jsonb stock
from dax.dx_k dk
left join dax.dx_r dr on dr.product_id= dk.product_id
where dr.location in ('A', 'B', 'C', 'D')
group by product_name, product_id
order by product_id

And this results in:
00015
TEST PRODUCT NAME
[
  {"location": "A", "quantity": 15000.000000000000},
  {"location": "B", "quantity": 0.000000000000}
]

And my goal is to get the stock for all locations even if it doesn't have a record for C and D I'd like to return C = 0 and D = 0.
Like this:
[
  {"location": "A", "quantity": 1500},
  {"location": "B", "quantity": 0},
  {"location": "C", "quantity": 0},
  {"location": "D", "quantity": 0}
]

Is there a simple way to do this, because I don't want to overcomplicate the query unnecessarily?
UPDATE:
I have another table called sites which contains the locations to any given site and I'll include my site_id in the WHERE clause, e.g.
...
WHERE site_id = 1

will result in:
site_id|location
1      |A
1      |B
1      |C
1      |D

And I'll need all locations for the given site:
[
  {"location": "A", "quantity": 1500},
  {"location": "B", "quantity": 0},
  {"location": "C", "quantity": 0},
  {"location": "D", "quantity": 0}
]

The problem with this is that I have my products associated with the locations in dx_r on product_id.


texts: Questions about the output of "statistics IO"

texts:
Is the TOTAL number of physical reads a query did = [physical reads] + [read-ahead reads]?
Why sometimes we have [physical reads]=0 while the [read-ahead reads]>0? what does that mean?
How many pages SQL Server reads in one physical read? ... if it is
one to one, then
Why when "select * from aTable", the number of [physical reads]
and/or [read-ahead reads] does not equal the table's number of pages?
although the cache was cleared before executing the query.
why sometimes [logical reads] is greater than [physical reads] +
[read-ahead reads], and even greater than the total number of pages
of the table?


texts: Why does this procedure raise a privilege error?

texts:
I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine.
QUESTIONS

Could somebody help me understand which specific part of this code raises this error, i.e. requires the said privilege?
Is there any way I could have corresponding functionality without a privileged call?


texts: How to find unused logins in SQL Server?

texts:
I have a couple of databases on my server, each with its own users. Now I need to find and delete all those logins, which are not mapped to any user in any database. But I've also seen that it is possible that logins are not mapped to any user but they are able to do some operations because they have roles like sysadmin. So in this case those logins are in use. I want to know how can I find really not useful logins which are just left orphaned without any use?

Thanks


texts: SQL Server 2017 Enterprise - how to fast update entire columns

texts:
I need to update all values in one column in a table. The table is large - nearly 420 000 000 rows.  Is there any clever way to conduct this operation quickly and without causing a lot of transaction log growth?
I know I can do that in batches, but maybe some sort of metadata trick is possible? Some clever way to use ALTER TABLE SWITCH maybe?
TIA


texts: Configuring transactional replication with updatable subscription via SSMS 2012

texts: Can I configure transactional replication with updatable subscription via SSMS 2012 (SQL Server 2012)? Is this removed from SSMS 2012 or I'm just missing something here?


texts:
i am running postgres on aws and trying to get my head around the "tx wraparound topic".

i executed the following steps to "play" around

take snaptshot of production database (here "our_main_db")
create new aws instance with same parameters as production one
psql into the newly created instance
take current txid, and age(datfrozenxid) of databases. i can see that they are over 100.000.000 everywhere

    -- 100.517.962
    SELECT txid_current();

    -- template0 100516214
    -- rdsadmin  100516214
    -- template1    100516214
    -- postgres     100516214
    -- our_main_db  100516214
    SELECT datname, age(datfrozenxid) FROM pg_database;

execute a "vacuum freeze analyze" on "our_main_db"

     -- 100.517.962
    SELECT txid_current();

    -- template0 100516214
    -- rdsadmin  100516214
    -- template1    100516214
    -- postgres     100516214
    -- our_main_db  148
    SELECT datname, age(datfrozenxid) FROM pg_database;

execute a "vacuum freeze analyze" on "template1", "postgres"

     -- 100.517.962
    SELECT txid_current();

    -- template0 100516214
    -- rdsadmin  100516214
    -- template1    75
    -- postgres     40
    -- our_main_db  148
    SELECT datname, age(datfrozenxid) FROM pg_database;

Now the following questions trouble me

AWS gives me a predefined metric "MaximumUsedTransactionIDs" which i assume is based on the following query.

SELECT max(age(datfrozenxid)) FROM pg_database; w

which leads to "100516214". i cannot (and should not) connect to "rdsadmin" and "template0" to do a manual "vacuum freeze" on them.

as i understand the docs the "autovacuum" process should eventually deal with with these dbs and when they reach a certain threshold "200.000.000" a forded "vacuum freeze" is done by postgres itself.

is this assumption correct?
should i just change the alert threshold to 1.000.000.000 (which is 50% before the "stop the world thing happens") as recommended in the aws docs.
is there a way to force the "freeze" on the admin tables that i cannot connect to? i want to see this metric value going down.
does the "stop the world" happen for the whole postgres instance if one of the databases reaches the 2billion tx id limit or is this done per db inside the postgres instance?
would a "vacuum full" on the "our_main_db" solve the problem or is there still the problem with the other dbs i cannot directly modify which keep the metric value up?

any help highly appreciated
thanks and greetings from hamburg
marcel


texts: Feature of database-level audit in SQL Server 2012 Express edition

texts:
Like in SQL Server 2012 Enterprise edition, can we have database-level audit in SQL Server 2012 Express edition?

Can we have Audit Folder in Security Tab?


texts:
I tryed to connect to SQL Plus as the admin "System", I'm sure that the password I wrote was correct but the connection failed. I also tried to connect with Scott/tiger but it failed as well.
What can be the problem, and how to resolve it please ?

this is what I get after trying to connect using both System and Scott:


texts: Is left hash join always better than left outer join?

texts:
I have a query that runs quite slow (see below). While searching for a way to improve it we found that the query runs about ten times faster if we replaced the LEFT OUTER JOIN with LEFT HASH JOIN
The result seems to be the same. Is it? In what cases wont it return the same result? What difference are there? Is there something that i should look out for when running a LEFT HASH JOIN instead of a LEFT OUTER JOIN?
The [ABC].[ExternalTable] table in the query is a view on a different server that i have added as a external table
SELECT t.foo, t.bar, t.data
FROM [dbo].[Table] as t
LEFT OUTER JOIN [ABC].[ExternalTable] as s ON s.foo = t.foo and s.bar = t.bar and s.data = t.data
WHERE s.foo is null and s.bar IS NULL and s.data IS NULL


texts: Issues converting MyISAM table to InnoDB (auto column issue)

texts:
I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6.

The following is the table dump:

--
-- Table structure for table `companies`
--

DROP TABLE IF EXISTS `companies`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `companies` (
  `uid` int(20) NOT NULL,
  `cid` int(20) NOT NULL AUTO_INCREMENT,
  `cname` varchar(500) NOT NULL,
  `rfc` varchar(20) NOT NULL,
  `address` varchar(1000) NOT NULL,
  `dbUseExternal` tinyint(1) NOT NULL DEFAULT '0',
  `dbHost` varchar(50) NOT NULL,
  `dbPort` varchar(50) NOT NULL,
  `dbUser` varchar(50) NOT NULL,
  `dbPass` varchar(50) NOT NULL,
  `dbSSL` varchar(50) NOT NULL,
  `dbDriver` varchar(50) NOT NULL,
  `dbName` varchar(50) NOT NULL,
  `status` int(10) NOT NULL,
  PRIMARY KEY (`uid`,`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

It works as MyISAM. But, if I try to convert it to InnoDB (or if I try editing this dump to insert it on the command line as a sql file), I get the following error:

Incorrect table definition; there can be only one auto column and it must be defined as a key

I understand that error - or at least I thought I did. I'm not really using more than one AUTO_INCREMENT column and it is defined as primary key.

Also, the information I've found regarding the error is always because of an obvious missing key or a duplicate AUTO_INCREMENT definition. One more thing I see generally commented is that the same is true for MyISAM and InnoDB.

So, why does it work for MyISAM and not for InnoDB?

Thanks in advance for any comments.

Francisco