texts: Best Way To Migrate Existing Production Database To New Physical Storage Structure

texts:
I have recently taken a job as a Senior Database Administrator at a small but rapidly growing company. My first order of business was a clean up the logical design of the main database (adding primary and foreign keys to existing tables, removing database objects that were no longer in use, eliminating redundant indexes, adding missing indexes, etc) but now that the preliminary logical cleanup is complete I have turned myself to the physical design.

The current production setup is that we have a physical database server, 4 sockets, 16 cores, hyper-threaded to have 32 logical processors with 256 GB of memory. CPU utilization, even during the busiest times, is typically under 15%.

For storage we have four drives, C, D, E and T, with C for the OS, D for the data files, E for logs and T for TempDB. The size of the primary data file for the main database is currently 650 GB, but is growing since the company is growing. There are 700 GB free on the D: drive, which is an SSD in Raid 10, with 1.45 TB of capacity in total.

We are using SQL Server 2014, but the main database is in 2008 compatibility mode, due to a performance issue with one of the stored procedures which occurs in 2012/2014 compatibility mode (In 2014 compatibility the performance problems are consistent, in 2012 compatibility the performance issue only presents itself under load)

The intermittent change that I would like to make, is to take the one 650 GB data file for the main database and split it into 2 file groups (one for the main data, the other for non-clustered indexes) with 8 files per file group. So my questions are this:

1) Should I expect any gains in performance or reliability breaking up the main data file in the manner I described?

2) How do I make these changes on a production database server where I can schedule (at most) a 30 minute downtime window per day?

Also note that while I have access to a number of servers I could use to test these changes, prior to making them in production, the non-production hardware is under-powered compared to actual production. For example, the box I am planning to use for my tests hosts a production database copy which is refreshed daily. It is a virtual machine with a 8 cores, 35.6 GB of memory and the D: Drive is a virtual disk hosted on a SAN, which has 1.19 TB of total space. So while I can test my changes on this machine, I cannot be confident that any performance increases on this machine will be representative of actual production.

So far all attempts to move towards the state I have described have involved the session_id for my session getting stuck during a DbccFilesCompact command. When this happens, the percent_complete does not increment, and the SPID is suspended, and dm_exec_requests shows it cycling through PAGEIOLATCH_SH waits on various resources. I'm not sure how long it would need to complete (if it could complete), but I have let in run for hours at a time with no progress. I can kill the process and that kill resolves immediately, but when the process is killed no progress is made towards the desired end state.

Has anyone faced this problem in their own companies?
If so, what was the resolution?


texts: Oracle: Can a user log in without a password?

texts:
I'm writing a sqplus-like program.  I assume the connect string contains the password if it contains a slash, e.g. scott/tiger or scott/tiger@orcl.

if connect string does not contain "/":
    prompt for password entry

Is there ever a case where a user could have a connect string scott or scott@orcl and not need to be prompted for a password?


texts: why when i run my sql code, the terminal stop on some wired number?

texts:
I am creating a trigger

The CREATE statement is :

CREATE TRIGGER TRIGGER01
AFTER INSERT ON WAREHOUSE
REFERENCING NEW AS NEWWAREHOUSE
FOR EACH ROW
WHEN (NEWWAREHOUSE.STATE = 'MAINE')
BEGIN
INSERT INTO WAREHOUSE_MAINE VALUES(:NEWWAREHOUSE.SHIPPEREIN,:NEWWAREHOUSE.WAREHOUSENAME);
END TRIGGER01;
INSERT INTO WAREHOUSE VALUES('108-34-9999', 'MoonHouse', 'MAINE', 'kapa');
SELECT * FROM WAREHOUSE_MAINE;

But when I run this with other data on Oracle, my terminal displays:

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

 25

It should be back to style

sql>

or warning me there's something wrong with my code at least, but it doesn't. What is wrong with my statement? It seems like when running this statement, it stops on line 25?


texts:
I have 2 tables users & groups:
user_id | title         group_id | title
----------------        ------------------
U_1     | User 1        G_1      | Group 1
U_2     | User 2        G_2      | Group 2
                        G_3      | Group 3

I have a mapping table between users and groups (a many to many relationship):
members
group_id | user_id | user_type
------------------------------
G_1      | U_1     | admin
G_1      | U_2     | member
G_2      | U_1     | admin
G_3      | U_2     | admin

I'm looking to create a materialized view with nested tables as follows
user_id | title  | groups
----------------------------------------
U_1     | User 1 | group_id | user_type
        |        |----------------------
        |        | G_1      | admin
        |        | G_2      | admin
----------------------------------------
U_2     | User 2 | group_id | user_type
        |        |----------------------
        |        | G_1      | member
        |        | G_3      | admin

In my node.js app, I'm basically expecting the following
[
{
    "user_id": "U_1",
    "title": "User 1",
    "groups": [{"group_id": "G_1","user_type": "admin"},{"group_id": "G_2","user_type": "admin"}]
},
{
    "user_id": "U_2",
    "title": "User 2",
    "groups": [{"group_id": "G_1","user_type": "member"},{"group_id": "G_3","user_type": "admin"}]
},
]

This was easy to achieve using MySQL (cast multiset), but I'm having trouble with postgres... Plz help...


texts:
DESCRIPTION:    SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: 10.XXX.XX.XX} ***The client address is different on different occasion

Can i refer this problem to my network admin? is this a network issue or a sql server issue. Is there any other way other then changing the registry ? Can i safely ignore this error ?


texts: Running the .sql script in Linux to get the postgresql database table and functions

texts:
I am using Red hat Centos 7. When I use this command to run the .sql script to get postgres database table and fucntion:

$sudo psql -U postgres -d tsdb -W -f /.sqlscript/location
It asks for postgres password and when i put password and hit enters it says
psql: FATAL: Peer authentication failed for user "postgres"

What could be my error? How do I fix the error so that I can successfully run the script to restore table and function and all the data in postgresql.

This is my pg_hba.conf. Is there anything that need to be changed in this file?

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                               peer
#host    replication     postgres        127.0.0.1/32           ident
#host    replication     postgres        ::1/128                ident


texts:
This is what my query output looks like and i have modified the data for easier viewing.

TABLE A
                +--+--------------+----------------+----------------+
                |ID|Item Name     | Num of Sales   | Current Stock  |
                +-----------------+----------------+----------------+
                |67| Yellow Table |              23| 0              |
                |68| Red Glass    |              55| 0              |
                |69| Blue Chair   |              54| 0              |
                |70| Black stool  |              66| 0              |
                +--+--------------+----------------+----------------+

However "Current Stock" for "Red Glass" in another table is duplicated because it has actually 2 kind of merchant that provide the stock item in "Table B". This is the output query for the "Table B"

Table B
        +----+--------------+--------------+----------------+
        | Id | Item Name    | Current Stock| Merchant       |
        +----+--------------+--------------+----------------+
        |  68| Red Glass    |             0|Merchant A      |
        |  68| Red Glass    |           700|Merchant B      |
        +----+--------------+--------------+----------------+

What I am trying to result is like this, where the filter is "Current stock" = 0. Because Red Glass is actually not zero due to having stock in Merchant B.

                +--+--------------+----------------+----------------+
                |ID|Item Name     | Num of Sales   | Current Stock  |
                +-----------------+----------------+----------------+
                |67| Yellow Table |              23| 0              |
                |69| Blue Chair   |              54| 0              |
                |70| Black stool  |              66| 0              |
                +--+--------------+----------------+----------------+

Where item similar like "Red Glass" that have more than 1 Merchant in "Table B" should not detected having 0 current stock. I was thinking about doing similar Vlookup in excel to sum the "Current Stock" in Table B. Or using Case When to detect the duplicate row and then sum the "Current Stock" column


texts:
Let's imagine I want to store in a relational database the following :

A list of quizzes
A list of questions associated with quizzes
A list of users
The answers to questions made from users.

I could have a model like this:

However, this doesn't ensure any integrity in the following cases:

A question has been modified so the corresponding answer is now wrong
New questions are added to an existing quiz, so the score students will be different

With MySQL I don't know any way to prevent this kind of data corruption unless to put the database in read-only mode. The only alternative I can think of is to use a revision column for both the quizzes and the questions. Then to use a composite primary key that identifies a question from its revision and its id.
My current idea is to have a JSON column in the activities table that freeze the state of a quiz at a certain moment in time. Then the hash of this activity can be stored somewhere in a blockchain.
What would be the alternatives?


texts: Setup error :Attempted to perform an unauthorized operation SQL Server 2014

texts:
Installation of SQL Server 2014 on Windows 10 Home.

From the log details:- It seems modifying this registry key is causing the problem

HKEY_LOCAL_MACHINE@SYSTEM\CurrentControlSet\Services\FltMgr

    5:02 Slp: Exception type: Microsoft.SqlServer.Configuration.Sco.ScoException
(01) 2017-05-19 12:15:02 Slp:     Message:
(01) 2017-05-19 12:15:02 Slp:         Attempted to perform an unauthorized operation.
(01) 2017-05-19 12:15:02 Slp:     HResult : 0x84bb0001
(01) 2017-05-19 12:15:02 Slp:         FacilityCode : 1211 (4bb)
(01) 2017-05-19 12:15:02 Slp:         ErrorCode : 1 (0001)
(01) 2017-05-19 12:15:02 Slp:     Data:
(01) 2017-05-19 12:15:02 Slp:       WatsonData = HKEY_LOCAL_MACHINE@SYSTEM\CurrentControlSet\Services\FltMgr
(01) 2017-05-19 12:15:02 Slp:       DisableRetry = true
(01) 2017-05-19 12:15:02 Slp:     Inner exception type: System.UnauthorizedAccessException
(01) 2017-05-19 12:15:02 Slp:         Message:
(01) 2017-05-19 12:15:02 Slp:                 Attempted to perform an unauthorized operation.
(01) 2017-05-19 12:15:02 Slp:         HResult : 0x80070005
(01) 2017-05-19 12:15:02 Slp:         Stack:
(01) 2017-05-19 12:15:02 Slp:                 at Microsoft.SqlServer.Configuration.Sco.InternalRegistryKey.OpenSubKey(String subkey, RegistryAccess requestedAccess)
(01) 2017-05-19 12:15:02 Slp: ----------------------------------------

I know about this thread Setup error :Attempted to perform an unauthorized operation

I did the same :- Tried changing the registry setting to full control for everyone via safe mode. Disabled defender, antivirus and firewall. But no luck.

Seems something to do with FILESTREAM in SQL server. Any idea on how to get rid of this issue?


texts: csv import fails without throwing an error

texts:
Ι am trying to import a csv file into an existing mysql 5.7 table.
Here is the command (the db is in docker with bind mount for persistence)
mysql -h 127.0.0.1 -uroot -prootpass --local_infile=1 jira_statistics -e "LOAD DATA LOCAL INFILE '/home/centos/bugs.csv' INTO TABLE bugs FIELDS TERMINATED BY ','"

However:
mysql> select * from bugs;
Empty set (0.05 sec)

The file cannot be loaded because there is a foreign key constraint.  I need to be able to catch errors like this (and any others that might occur) as an exit code since I will be scripting a large number of imports like this.
There is no error returned to the command line, and the docker logs of the MySQL container show no errors.
What is more, the exit code of the mysql command is 0!
Any ideas?


texts: What is limiting MySQL to write max. 10 Inserts per second?

texts:
I am running an MySQL server. Build a small RAID with 3 harddisks which can Write 300MB/s and this damn MySQL just don't want to speed up the writing. 10 Inserts per second is the max I have seen till now.

What is the bottleneck?!

Here are some Screenshots:

Answers to comments:

Example table: 10 Double Columns and one Datetime, 500.000 rows (to import)
The inserts I do are just initial data inputs like 500.000 times "insert into ... value ..."
I tried RazorSQL which is sending 500 transactions at once, but no effect.
It is a RAID0 with 3x2TB no-SSD-disks.

Tuning LOG:

SET GLOBAL sync_binlog = 500; results in 20.25 inserts per second.
SET GLOBAL innodb_buffer_pool_size = 2684354560; no effect
SET innodb_buffer_pool_instances = 4 (in mysqld.conf); setup in config file makes no effect. still instances = 1.


texts: PostgreSQL failover: Who is responsible for handling it? How do clients connect to the new leader?

texts:
On a high level, consider two PostgreSQL instances on two separate nodes: the leader and its hot standby. Replication from leader to follower is configured.

Consider that the leader node crashes. Two questions:

Who is responsible for promoting the follower to be the new leader? Do you have to setup some kind of heartbeat system, or is this handled automatically by PostgreSQL?
The clients relying on PostgreSQL now have to be aware that the leader was changed. What is the best way to make this seamless for the client? It there an automatic procedure that PostgreSQL offers?

For question 2. some ideas come to my mind, in which an external server or the hot standby follower would heartbeat the leader. If the leader is considered dead, do one of the following:

The client connects to the PostgreSQL instance via hostname. On failover, the DNS entry would be changed to point to the new leader node by the heartbeat server.
The client connects to the PostgreSQL instance via IP. Something along the veins of a floating IP, in which the heartbeat server broadcasts an ARP request to update the MAC tables of the switches, in order to point the IP to the new leader.
The client connects to the PostgreSQL instance via an IP that is provided by ZooKeeper. The heartbeat server would update the entry on ZooKeeper to the new leader.

Could you help me answer the first two questions and assert the validity of the proposals for question 2.?


texts: What is a good solution for reliably transferring data between SQL Server and PostgreSQL?

texts:
In analyzing some of our business data, we're realizing that we need to join data across disparate data sources. For example, our application data is warehoused in Postgres (ported from MongoDB via MoSQL), while our purchase info (subscriptions, financial info, etc.) is archived in SQL Server.

I've written a basic command line tool using Python to prove that I can move data from Postgres to Postgres and from SQL Server to Postgres, after which I would manipulate the data using plain SQL. This looks like it could work, but I'm quickly realizing that I'll probably run into lots of data translation issues (e.g. None needs to be \N) along the way and there's probably a tool that does this.

I'm having a hard time searching for tools that can be run from the command line. CloverETL showed up in several searches, but that seems pretty complicated for what I need (who knows—maybe I need something complicated). Rather, I'm looking for something where I can give the SQL for one data source and have the tool compensate for the differences and insert that data into a disparate data source. I want to develop on Mac and deploy on *nix.

Are there good, lightweight, scriptable ETL tools that would help with this process?


texts: Security Concerns ODBC DSN - how well does DSN protect SQL Credentials?

texts:
ODBC DSN's have been around for a long time, yet SQL Server still supports them, which says a lot. But I'd like to know, given that this is an older technology, how well are the credentials protected?

I understand that System and User DSN's are stored in the registry, but it looks like these are only references with limited information.
(please keep in mind, it's not a question on whether to use DSN or not)
Thanks.


texts: Cannot remove orphaned InnoDB Temp Table - missing .frm file

texts:
For the second time, I have encountered an issue, where our MySQL server (MariaDB v10.1) suddenly threw an error - Could not remove temporary table: './database_name/#sql-4593_791', error: 120.

Following the guids like https://mariadb.com/resources/blog/get-rid-of-orphaned-innodb-temporary-tables-the-right-way/ does not work, I still get an error that the table is not known (ERROR 1051 (42S02): Unknown table 'database_name.#mysql50#sql-4593_1e9')

What makes this issue different from other questions on the removal of orphaned InnoDB temp tables is that the database server seems to have deleted the .frm file, both, from disk and from memory (running lsof | grep sql-4593_1e9 shows only the .ibd file open)

Can that be a source of the problem? If so, is there any way to recreate the .frm file of a virtually inaccessible table of unknown structure?

The only mention of it I can get in the database itself is by running:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

Which outputs the following:

+----------+-----------------------------+------+--------+---------+-------------+------------+---------------+
| TABLE_ID | NAME                        | FLAG | N_COLS | SPACE   | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+-----------------------------+------+--------+---------+-------------+------------+---------------+
|  1576128 | database_name/#sql-4593_1e9 |    1 |    118 | 1576114 | Antelope    | Compact    |             0 |
|  1576129 | database_name/#sql-4593_78d |    1 |    118 | 1576115 | Antelope    | Compact    |             0 |
|  1576130 | database_name/#sql-4593_791 |    1 |    118 | 1576116 | Antelope    | Compact    |             0 |
+----------+-----------------------------+------+--------+---------+-------------+------------+---------------+

I do not feel comfortable removing the database files from disk manually, as I am not entirely sure it wouldn't lead to issues down the road. Even if the chances of tablespace id collision are near zero.


texts: Determining max database and table size supported and present size

texts:
One of my MySQL databases has been running on a single PC from the last four years.

Here is my setup:

MySQL 5.5.14
Windows XP on NTFS partition
All of my tables are using InnoDB engine

Here are two things I want to determine:

the maximum database and table size supported by this version and engine on the platform I mentioned above
the current database size and table sizes of all the tables in a database.


texts: mysql "link/reference" (not copy) values from a table to another

texts:
just starting learning mysql.

here is what i am trying to do:

table meds:

id small int not null unsigned auto_increment
name varchar not null
category varchar not null
primary_effect varchar
side_effect varchar

medtakes

id smallint
med_ID
morning smallint not null
noon smallint not null
night smallint not

what am i trying to achieve?

insert into medtakes referencing from existing meds from the meds table. so I dont have to retype all the info (thats the whole point of a DB)

I have read many articles about join, innerjoin, leftjoin, rightjoin and foreign keys.

this is the article that made most sense to me(none of the others made any barely)

https://www.hostingadvice.com/how-to/mysql-foreign-key-example/

Although I (barely) manage to make it (see screenshot, following the instructions from article I believed I made both tables "working", I dont think is exactly what I need. they are connected somehow, but not the way I want. I think.

Like I said, my end goal is that from the takes table, I can work/filter data of the meds from the information saved in the meds tabs. That when I input the second column (med_ID), is it referencing to the contents of the specified med on the med table

my problem (i think) is that i dont know what operator i need for this purpose. Excuse my noobiness, second day on sql.


texts: Why does SSMS highlight SUBSTRING in purple but RIGHT in grey?

texts:
I apologise for the trivial question, but I was trying to work out how to do a RIGHT in SQL so I experimentally typed RIGHT and it didn't highlight in purple, so I spent some time scratching my head about how to do this using substring and length, then I later discovered that RIGHT is perfectly valid.

How come it wasn't highlighted purple?


texts:
I'm new to SQL encryption and have a SQL Server 2012 database. The database itself needs to be accessible by DBAs but the data within one of the columns a table should only be viewable in plain text by a few users.

To do this, I tried using a Symmetric Key to encrypt the column which works fine. However, I don't see the security in this as any of the DBAs who have access to the database also have access to the key (seems to be stored within the 'Security' folder in Management Studio).

How can I ensure that only certain users have access to the key itself so that it can be decrypted even if more users have access to the database itself?

If there's a parameter I can pass from the app, that works for me too (C#/WPF)

Thanks


texts: What does "unpackaged" mean in PostgreSQL extensions?

texts:
I've read the docs and a few blog postings, but I still don't get what the "unpackaged" tag in an extension file name is for.

When would I use it and not use it?


texts: Index is seeked and scanned - is there a risk for seek to fail after modification?

texts:
I have an index that has been seeked 230.000 times and scanned 130.000 times.
In order to improve the queries that have scanned, because they use an additional column I guess, I would like to change the index and add the column that is  additionally used by the index-scanning query.

In the relevant index-scanning query both of the columns are used within a join operation as the joining columns.

Do I have to expect that the queries that so far seeked the index will afterwards fail to seek and scan the index instead? Should I instead add a seconde, new index?

CREATE TABLE [dbo].[ArtCatVRC](
[AVid] [uniqueidentifier] NOT NULL,
[ACatalID] [uniqueidentifier] NOT NULL,
[ACategID] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_A] PRIMARY KEY NONCLUSTERED ([AVid] ASC, [ACatalID] ASC, [ACategID] ASC) WITH ... ON [PRIMARY] ) ON [PRIMARY]

Query (simplified):

Select V.Col1, V.Col2, C. ColN From ... AS C inner join ... AS V LEFT OUTER  JOIN dbo.ArtCatVRC AS R ON C.ID = R.ACategID AND R.ACatalID IN (V.X, V.Y, V.Z)

The used (scanned) index:

CREATE NONCLUSTERED INDEX [ACategId] ON [dbo].[ArtCatVRC] ( [ACategID] ASC )

After every execution of the above query, SYS.DM_DB_INDEX_USAGE_STATS increments SCANS +1 to this index.

The idea is to add the column R.ACatalID to the index, so that joining with both columns will change to seek the index.

Exec. Plan:

StmtText
--------
Stream Aggregate(GROUP BY:([cat].[ID]))
|--Filter(WHERE:([DATABASE].[dbo].[ArtCatVRC].[AVid] as [R].[AVid] IS NULL))
           |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([R].[ACategID])=([cat].[ID]), RESIDUAL:([DATABASE].[dbo].[T_ACateg].[ID] as [cat].[ID]=[DATABASE].[dbo].[ArtCatVRC].[ACategID] as [R].[ACategID] AND ([DATABASE].[dbo].[ArtCatVRC].[AVid] as [R].[AVid]=[DATABASE].[dbo].[T_ACatVM].[SS] as [viewMap].[SS] OR [DATABASE].[dbo].[ArtCatVRC].[AVid] as [R].[AVid]=[DATABASE].[dbo].[T_ACatVM].[SAVid] as [viewMap].[SAVid] OR [DATABASE].[dbo].[ArtCatVRC].[AVid] as [R].[AVid]=[DATABASE].[dbo].[T_ACatVM].[AVid] as [viewMap].[AVid])))
                |--Index Scan(OBJECT:([DATABASE].[dbo].[ArtCatVRC].[IX_ACategID_AVid] AS [R]), ORDERED FORWARD)

                |--Sort(ORDER BY:([cat].[ID] ASC))
                     |--Nested Loops(Inner Join, OUTER REFERENCES:([viewMap].[ACatalID]))
                          |--Clustered Index Seek(OBJECT:([DATABASE].[dbo].[T_ACatVM].[IX_AVid] AS [viewMap]), SEEK:([viewMap].[AVid]={guid'00000000-0000-0000-0000-111122223333'}) ORDERED FORWARD)
                          |--Index Seek(OBJECT:([DATABASE].[dbo].[T_A_Categ].[IX_ACateg_ACatalID_Deleted_ID_DisOrd] AS [cat]), SEEK:([cat].[ACatalID]=[DATABASE].[dbo].[T_ACatVM].[ACatalID] as [viewMap].[ACatalID] AND [cat].[Deleted]=(0)) ORDERED FORWARD)


texts: PLS-00049 bad bind variable when compiling a trigger

texts:
I'm trying to implement a dedupe trigger to a table.

CREATE OR REPLACE TRIGGER USER.TBL_ACTION_DEDUP
BEFORE INSERT
ON USER.ERRORTABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
   IF (:OLD.Identifier = :NEW.Identifier) THEN
      SELECT :OLD.tally + 1 INTO :NEW.tally FROM DUAL;
   END IF;
END TBL_ACTION_DEDUP;
/

Error is:
On line:  2
PLS-00049: bad bind variable 'OLD.IDENTIFIER'


texts:
I'm facing a performances problem in our MariaDB server.

We have a table linking users to pages, a user belongs to 1+ pages.

I have a page that have users and I want to get the 10 best pages based on users they share with the current page (page_id).

Here is the schema :

CREATE TABLE IF NOT EXISTS `pages_users3` (
  `user_id` bigint(20) unsigned NOT NULL,
  `page_id` bigint(20) unsigned NOT NULL,
  `comments` int(11) NOT NULL DEFAULT '0',
  `likes` int(11) NOT NULL DEFAULT '0',
  `shares` int(11) NOT NULL DEFAULT '0',
  `posts` int(11) NOT NULL DEFAULT '0',
  `interactions` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`page_id`,`user_id`) `clustering`=yes,
  KEY `user_page` (`user_id`)
) ENGINE=TokuDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci `compression`='tokudb_zlib' AUTO_INCREMENT=30127543590 ;

I have a query that can retrieve similar pages of a page based on users intersections :

SELECT `pages_users`.`page_id` , COUNT(*) AS count
FROM `pages_users`
LEFT JOIN `pages_users` AS `pu2` ON `pu2`.`user_id` = `pages_users`.`user_id`
WHERE `pages_users`.`page_id` != *PAGE_ID*
AND `pu2`.`page_id` = *PAGE_ID* /* To avoid getting the current page in results */
GROUP BY `pages_users`.`page_id`
ORDER BY `count` DESC
LIMIT 10

Unfortunaly this query runs in about 1m30s, so I would like to optimize it...

Here is the result of an explain :

+------+-------------+-------------+--------+-----------------------------------------------------------------------------------+------------------------------------------------+---------+----------------------------------------+-----------+-----------------------------------------------------------+
| id   | select_type | table       | type   | possible_keys                                                                     | key                                            | key_len | ref                                    | rows      | Extra                                                     |
+------+-------------+-------------+--------+-----------------------------------------------------------------------------------+------------------------------------------------+---------+----------------------------------------+-----------+-----------------------------------------------------------+
|    1 | SIMPLE      | pages_users | index  | pages_users_user_id_page_id_unique,pages_users_user_id_page_id_interactions_index | pages_users_user_id_page_id_interactions_index | 20      | NULL                                   | 165354919 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | pu2         | eq_ref | pages_users_user_id_page_id_unique,pages_users_user_id_page_id_interactions_index | pages_users_user_id_page_id_unique             | 16      | soprism_prod.pages_users.user_id,const |         1 | Using index                                               |
+------+-------------+-------------+--------+-----------------------------------------------------------------------------------+------------------------------------------------+---------+----------------------------------------+-----------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

I'm requesting your help because I spent many hours trying to optimize that query and I feel confused :(
I'm open to anything, from refactoring to changing server parameters...
Thank you !

Some additional insights :

SELECT COUNT(user_id) FROM `pages_users` WHERE `page_id` = *PAGE_ID*;
+----------------+
| COUNT(user_id) |
+----------------+
|         146132 |
+----------------+
1 row in set (0.08 sec)

SELECT COUNT(user_id) FROM `pages_users`;
+----------------+
| COUNT(user_id) |
+----------------+
|      165354918 |
+----------------+
1 row in set (1 min 0.98 sec)

Hardware : Quad Core Xeon, 32Gb RAM, Raid 1 2x2Tb


texts: Should a log table get an id field or primary key?

texts:
I have a log table that captures the datetime stamp of when certain files were exported to another system.

The exportedLog table currently has three fields:

id                (primary key)
messageId         (int)
exportedDateTime  (datetime)

Reviewing this I found that the id field serves no purpose, as there are no joins to this table.
The only thing working on this table is the insert of the batch job that processes the messages and inserts into this log table.

Should I remove the id field?

Should I have a primary key on either messageId or exportedDateTime or both?


texts: Transactional versus Merge Replication (Restore backup after downtime)

texts:
I have the following transactional replication setup:

Windows Server 2008 with SQL Server 2008 Enterprise

Server A: Master, has a database with a table, publisher
Server B: Slave, subscriber (Backup server)

For backup (and read only access to B during down time), transactional replication is a good solution.  However, during downtime of A, I want the application to read and write to the database on B.  It is possible to backup B and restore the content to A, but is a merge replication a better way?


texts: MySQL - best practices/ how to speed up "update, else insert' queries? Insert + delete faster?

texts:
I'm running an ETL process and streaming data into a MySQL table.

Now it is being written over a web connection (fairly fast one) -- so that can be a bottleneck.

Anyway, it's a basic insert/ update function. It's a list of IDs as the primary key/ index .... and then a few attributes.

If a new ID is found, insert, otherwise, update ... you get the idea.

Currently doing an "update, else insert" function based on the ID (indexed) is taking 13 rows/ second (which seems pretty abysmal, right?). This is comparing 1000 rows to a database of 250k records, for context.

When doing a "pure" insert everything approach, for comparison, already speeds up the process to 26 rows/ second.

The thing with the pure "insert" approach is that I can have 20 parallel connections "inserting" at once ... (20 is max allowed by web host) ... whereas any "update" function cannot have any parallels running.

Thus 26 x 20 = 520 r/s. Quite greater than 13 r/s, especially if I can rig something up that allows even more data pushed through in parallel.

My question is ... given the massive benefit of inserting vs. updating, is there a way to duplicate the 'update' functionality (I only want the most recent insert of a given ID to survive) .... by doing a massive insert, then running a delete function after the fact, that deletes duplicate IDs that aren't the 'newest' ?

Is this something easy to implement, or something that comes up often?

What else I can do to ensure this update process is faster? I know getting rid of the 'web connection' between the ETL tool and DB is a start, but what else? This seems like it would be a fairly common problem.

Ultimately there are 20 columns, max of probably varchar(50) ... should I be getting a lot more than 13 rows processed/ second?


texts: Insufficient Privileges on selecting Views

texts:
I am using Oracle 12.

Given 2 schemas, SCHEMA_01 and SCHEMA_09. I have a View_01 owns by SCHEMA_01 but it is made up of SCHEMA_01 and SCHEMA_09 tables.

Then I have a User_02 that could select both tables in SCHEMA_01 and SCHEMA_09. But I am prompted with insufficient privileges when selecting the VIEW_01.

Before that I have tried granting all SCHEMA_09 tables with grant option to SCHEMA_01. This error is also prompted when I tried granting select for view USER_02's role

  grant option does not exist for SCHEMA_09.TABLE_01

Anything that I am missing or maybe somewhere I can check?


texts: pl/pgsql: Dynamicly get a column name from a record

texts:
Greeting,
I want dynamicly get a column name from a record.
As showing in the code below that I created a cursor and I used a loop to process each record in that cursor by fetching each row to a record type r1.
In my table I have these columns [dlq_2000,dlq_2001,...,dlq_2017,dlq_2017].
Also I created on top of it a loop to process each column separate.

The issue I am facing is getting the field name from r1 dynamicly and I am getting this error when I run the code:

  [Err] ERROR: record "r1" has no field "'dlq_'||counter::text" CONTEXT:
  SQL statement "SELECT ( r1."'dlq_'||counter::text" = 1 )"

Please advice how to solve this issue.

Thank you,

CREATE OR REPLACE FUNCTION update()
   RETURNS VOID AS $$
DECLARE
cur SCROLL CURSOR   FOR select * from my_tbl;
r1 RECORD;
begin
OPEN cur ;
FOR counter IN  2000..2017 LOOP
    r1 := NULL;
    LOOP
        FETCH cur INTO r1;
        EXIT WHEN NOT FOUND;

        IF (r1."'dl_'||counter::text" = 1 ) THEN
            -- do some thing
            RAISE NOTICE 'processing year of : %', counter;
        END IF;
    END LOOP;
END LOOP;
CLOSE cur;
END;

 $$

LANGUAGE plpgsql;


texts: Why is a query with a join and limit clause running so slow, even using a covering index?

texts:
I've got two tables that are a 1 to 1 relation.

Table A has an id column, and table B includes foreign key A_id to table A, and a nullable value column. I have a composite index on [value, A_id] on table B, and I'm running the following query:

SELECT B.A_id FROM B JOIN A ON B.A_id = A.id WHERE B.value IS NULL LIMIT 1000;

First of all, I understand the JOIN doesn't seem to serve a purpose here, I'm just illustrating the problem. In reality I need to select one other column from table A. If I get rid of the join, the query is instant.

There are 10 million rows in table B. This query takes more than 50 seconds to run. Yet the explain plan shows that table B is being accessed first, and is using the composite index. "Extra" shows "Using where; Using index". "Rows" shows almost 5 million rows.

What's going on here, and why doesn't the LIMIT cause this to run as nearly as quickly as without the join?


texts: ASSIGN value for cmd in sysprocesses? What does it mean?

texts:
We're seeing some strange blocking behaviour in a production (SQL Server 2008 R2) system that we're investigating, and it seems to have one consistent and novel element: in the sysprocesses table, the relevant blocking process always has ASSIGN as the "cmd" column value.

The process that is actually running is a stored procedure, but dm_exec_sql_text doesn't give us any more detail that the stored procedure name - it's quite a complex proc.

We know that this stored procedure does a non-negligible amount of Xml (XQuery) processing, so that seems the most likely source of the ASSIGN command, but we're having a hard time understanding exactly what the command is, why it would take a long time, and what it might be "hiding":
For example, could it be that this is generally what happens when using a SET statement, where the value being set is a subquery?

For example, is this a way to reproduce the issue?

DECLARE @Something Int
SET @Something = (
    SELECT Something
    FROM Somewhere
    WHERE DoSomethingReallySlowAndExpensive(Something) = 1
    )

UPDATE: I can confirm that this is not a way to reproduce the issue - a long-running ASSIGN command in sysprocesses must mean something else, something more specific.

The test code above simply shows up as SELECT.

UPDATE 2: To Aaron Bertrand's point/question, the below is ALSO not a way to reproduce the issue. This code will alternate between being listed as cmd CONDITIONAL and cmd SELECT. So... what does ASSIGN mean??

CREATE FUNCTION [dbo].[fn_CpuLoad] (@LoopCount BigInt)
    RETURNS Bit
AS
BEGIN
    WHILE @LoopCount > 0
    BEGIN
        SET @LoopCount -= 1
    END

    RETURN 0
END
GO

DECLARE @Something Int
SET @Something = dbo.fn_CpuLoad(100000000)
GO

DROP FUNCTION [dbo].[fn_CpuLoad]
GO

UPDATE 3:

We eventually realized we were concentrating on the wrong aspect of the data we had available - the "cmd" value was usually ASSIGN, but also sometimes SELECT; what was completely consistent was the "waitresource" value, which was always the same value, "TAB: 9:547043060:0 [COMPILE]". When we noticed this, we rapidly found information on compile locks that helped us track down the causes:

Microsoft article on compile locks, identifying the name-qualification issue that was mainly biting us
Another misc article, about compile locks and encryption, also containing useful info

So we've now addressed the issues we were hunting, but we still have no idea what the ASSIGN value for cmd is, so I'll leave this question open in case anyone with the answer stumbles across it.


texts: Logging all changes made to MySQL database

texts:
I have a web application setup where there are separate Apache-Wordpress containers for development and production. Database is hosted externally on a virtual host.

When setting up development environment, I simply take a copy of the Apache container and create a MySQL container where I import dumps from the external database.

Problem arises when I want to update the production environment by replacing the production container with the development container. Updating the development container has made changes to the testing database, for example new tables or schema changes, which are not present in the external database.

How could I update the external database with the changes made to the testing database, without losing data that have come to the production database after I took the dumps?


texts: PostgreSQL: serial is set to 1, despite of some rows already being inserted in the table

texts:
I was getting duplicate primary key error while inserting a row in a table, despite of the primary key being a serial and me not passing any predefined value for it. So I went down to see what was happening with SELECT last_value FROM tablename_id_seq; and got: 1, despite of table already having several rows. Obviously I would get that duplicate error, but why did serial returned that for me. Any suggestions on what can be happening and how to troubleshoot this?

Tables are migrated up together with some rows, prior to me getting hold on them.


texts:
I have inner-query in left-join that is using sequential scan instead of index scan, and I have no idea on how to improve it.

I have two tables:

pages - columns: id
shares - each page has many shares, columns: page_id, view_counter

shares have an index on page_id and shares is a large table.

The query is:

SELECT id,
       shared.views     views_count,
       is_shared,
       shared.views > 0 AS tracked
FROM   pages
       LEFT JOIN (SELECT shares.page_id,
                         Sum(shares.view_counter)
                  FROM   shares
                  GROUP  BY page_id) AS shared
              ON shared.page_id = pages.id
WHERE page_id IN (1, 2, 3)

The query plan for this query is:

Merge Right Join  (cost=813559.28..895407.83 rows=4 width=21)
  Merge Cond: (shares.page_id = pages.id)
  ->  GroupAggregate  (cost=813558.85..867374.64 rows=2239970 width=8)
        Group Key: shares.page_id
        ->  Sort  (cost=813558.85..824030.88 rows=4188812 width=8)
              Sort Key: shares.page_id
              ->  Seq Scan on shares  (cost=0.00..238288.12 rows=4188812 width=8)
  ->  Index Scan Backward using index_pages_on_id_desc on pages  (cost=0.43..33.53 rows=4 width=13)
        Index Cond: (id = ANY ('{1,2,3}'::integer[]))

How can I re-write the query so I won't have the sequential scan on shares?


texts: PL/SQL + after login seems Not see tnsnames.ora file on my PC

texts:
I get the following error

when I login in by PL/SQL - please advice how to fix this?

Data character set (UTF8) and client character set (IW8MSWIN1255) are different character set conversion may cause unexpected results

you can set the client character set through the NLS_LANG env variable or NLS_LANG registry key in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE|KEY_OraClient10_home1


texts: Why do these foreign key constraints cause problems? (Azure SQL Server 12)

texts:
Here is a simplified version of the database I am attempting to create:

create table Worker (
    ID int,

    constraint PKW
    primary key (ID)
)

create table Business (
    ID int,
    W int,

    constraint PKB
    primary key (ID),

    constraint FKBW
    foreign key (W) references Worker (ID)
    on delete set null on update cascade
)

create table Note (
    ID int,
    B int,
    W int,

    constraint PKN
    primary key (ID),

    constraint FKNB
    foreign key (B) references Business (ID)
    on delete cascade on update cascade,

    constraint FKNW
    foreign key (W) references Worker (ID)
    on delete set null on update cascade
)

Essentially each business has a worker assigned to it, and any worker has the ability to create a note regarding any business. I have 3 foreign key constraints to reflect these relationships, but this creates the error:

  Introducing FOREIGN KEY constraint 'FKNW' on table 'Note' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

There are no relationship cycles in this setup. I can see how having FKBW on delete cascade would create 2 delete paths to Note, but not with my current setup. Even if I remove on update cascade from both FKBW and FBNW, the error persists. I don't see how set null creates any problems.

Any clarity would be appreciated!


texts: Postgres - how to find contents of one field within another field

texts:
How do I find where the contents of one field are within another field?

I have a database table with a auto-generated sequence number in one field, and another character based field which should contain a string which describes a path to a file. The path should be based on the sequence in the other field, sometimes it isn't and I want to identify these rows.

This is query I've been trying (among others) but it doesn't work, position() is obviously designed to find a hard-coded string within a field, not another field

SELECT recordId, path
FROM TABLEA
WHERE position(recordID IN path) = 0;

RecordId is defined as:

recordId integer not nulll default nextval('tablea_recordid_seq'::regclass)

And path is:

character varying(80)

I can't help feeling that the correct solution is probably very simple, but damned if I can find it!

Ok, this works ...

SELECT recordId, path
FROM TABLEA
WHERE position(CAST(recordID AS CHAR) IN path) = 0;


texts: SQLcl history (up-arrow) rewrites my queries

texts:
Version

sql -version
SQLcl: Release 18.4.0.0 Production

I type this:

SQL>  exec dbaspace.long_ops;

SID  % Done  Start Time          Rem [s] Elapsed Message
==== ======= =================== ======= ======= ======================================================================================

There are currently no long running operations.

PL/SQL procedure successfully completed.

I press ARROW UP key to get the last command and SQLcl modifies my history like this:

SQL> BEGIN dbaspace.long_ops; END;;

Error starting at line : 1 in command -
BEGIN dbss.long_ops; END;;
Error report -
ORA-06550: line 1, column 26:
PLS-00103: Encountered the symbol ";"
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The rewritten history command does not work


texts: Omit max entry from each group in postgres?

texts:
I have a query which produces data similar to the following:

a_key     | a_timestamp | a_value
---------------------------------
345223452 | 2016-03-07  | 1
029370982 | 2017-01-12  | 1
984656834 | 2017-02-03  | 1
928459238 | 2014-08-06  | 2
298338793 | 2015-07-10  | 2
203897932 | 2015-09-12  | 3
123477399 | 2016-05-03  | 3

I need to eliminate the most recent timestamp for each given value.

Keys are all unique.  Values in this query are all non-unique (that is, there will be no value that is not contained in at least two rows) due to filters already applied.

It seems there should be some easy way to do this with an aggregate function or a "group by" clause, but I can't seem to work it out.

How can I filter this result to exclude the most recently seen key for each value?

(If it helps, I only really need the key from the results, not all three columns.)

I am using Postgres 9.3.11.


texts: Error on cassandra-3.11.3-1.noarch centos7

texts:
I've a cluster consisting of 5 cassandra nodes. The cassandra version used is

cassandra-3.11.3-1.noarch

The keyspace strategy and is defined as follows:

CREATE KEYSPACE my_keyspace
    WITH replication = {'class': 'NetworkTopologyStrategy', 'datacenter1': '2'}
    AND durable_writes = true;

Now running queries on the cluster, the following issue occurs:

ReadFailure: Error from server: code=1300 [Replica(s) failed to execute read]
message="Operation failed - received 0 responses and 1 failures"
info={'consistency': 'LOCAL_ONE', 'received_responses': 0, 'required_responses': 1, 'failures': 1}
info={'failures': None, 'consistency': 'Not Set', 'required_responses': None, 'received_responses': None}

Does anyone know what is causing this? If more information needed to better debug this problem, please let me know!

UPDATE 1

root# awk '!/#/' /etc/cassandra/conf/cassandra-rackdc.properties

dc=datacenter1
rack=rack1


texts: Postgres Materialized View Dramatically Increasing In Size

texts:
I have a production postgres 10 database. Every hour I run "refresh materialized view concurrently" on my materialized view to recalculate the values; only a few new rows get added each day.

All the materialized views (and their indexes) are increasing in size dramatically. The view that should be 102 MB is now 1700 MB and the indexes have suffered similar inflation. And the refresh time has increased from 17 seconds to 10 minutes. The overall DB size has grown from 4 GB to 21 GB in the last day due to the views and their indexes growing. I can't do a non-concurrent refresh as these views are constantly being read from.

Running a refresh triggers a large amount of disk I/O.

I have no idea what is causing this issue. I have autovacuum enabled and configured. I have 8GB of memory, and work_mem is set to 256 MB.


texts: SQL Server with small database size and huge log file size

texts:
I have several databases, for some log file size is growing more than normal even though very little data is added to few table around 50-100 rows a month. i.e. Database 12 data file size is 79MB and it log file size is 833MB, and over a week log file size has increased by almost 100MB while only about 100-150 records were added into one of the contact table using web form which only have 5-6 fields.

Similar issue with database 1 , database size is 16MB and log file now is around 554MB, database 1 is a test database and no data was added to this DB over last 1 year.

We have tried shrinking database that didn't make much difference...

Not sure what should i do next & why database log file is so huge. Also when we take backup .bak file those files are also large as log files example Database 10 .bak file is also around 800MB.

Production server is SQL Server 2014 Standard edition

Recovery model for Database 10 is Simple and for all other databases its Full

could this be the reason for large log files


texts: powershell command enable-alwayson not work for SQL Server Named instance

texts:
Hi all,

I installed a SQL server 2016 default instance and a SQL Server 2017 named instance (instance name is SQL17) on my Windows 2012 Server.

I opened a powershell windows with administrator mode, import module SQLPS.
Then I run invoke-sqlcmd to query from the default instance and named instance, it works.
but when I run enable-sqlalwayson, it only worked for my default instance(I checked from SQL Server configuration mamanger), and get error with named instance.
Why?

Please refer the screenshot for detail error message.

Any help will be appreciate!


texts: Oracle SQL: Why are column headers missing?

texts:
I want to understand why my columns are missing their headers.

Consider this table:

SQL> DESC customer;

Name         Null?    Type
------------ -------- ------------
CUST_ID      NOT NULL NUMBER(5)
CUST_NAME             VARCHAR2(15)
ACCOUNT_ID            VARCHAR2(10)
ACCOUNT_TYPE          VARCHAR2(2)
STATE                 VARCHAR2(2)

When I select * from the table, the result is missing the column headers.

SQL> SELECT * FROM customer;

     90001 B and B         A-11101    PR AK
     90002 XYZ             A-11102    CM NJ
     90003 JJ Sons         A-11103    CM NJ
     90004 Exxon           A-11104    PR NY
     90005 ABC             A-11105    CM NY
     90006 Smith Co.       A-11106    CM MD
     90007 Brown Co.       A-11107    CM MD
     90008 Cooper Inc.     A-11108    PR MD

8 rows selected.

Why?

My goal is to display the tables with the headers. If there are other details I need to add, let me know.


texts: Select first and last row based on each user id mysql

texts:
I want to select every first and last event of every user id, if no first event exists,then just the last event instead . tried using partition over , but i am getting first 2 events instead.

Input:

id  timestamp   event
10096   1545136190  songs
10096   1545136202  tv
10096   1545136239  book
10096   1545136244  eat
10096   1545136252  travel
10096   1545136271  tv
10096   1545136275  laptop
10096   1545313396  sleep
10105   1545464444  book
10105   1545464455  eat
10105   1545464457  travel
10105   1545464460  tv
10105   1545464605  book
10105   1546740388  sleep
10213   1545134176  tv
10213   1545134180  computer
10213   1545134221  play
10213   1545134238  book
10213   1545134267  eat
10213   1545146190  travel
10213   1545146212  play
10213   1545623861  sleep
1053    1545131279  sleep

Expected Output:

   id   timestamp   event
10096   1545136190  songs
10096   1545313396  sleep
10105   1545464444  book
10105   1546740388  sleep
10213   1545134176  tv
10213   1545623861  sleep
1053    1545131279  sleep

tried doing this but not getting exactly what i want:

SELECT * FROM (
SELECT
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) as RowNum,
    id,
    timestamp,
    event
FROM
    `table`
) MyData
WHERE RowNum < 3


texts: Design for archiving a sorted list (with ability for a few INSERT)

texts:
I have a somewhat static sorted list of words which I want to save in a database table:

-- simplified!
-- Note: the rank/order of each item is NOT calculable from other values!
CREATE table words (
  id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  word NVARCHAR(50),
  -- foreign key
  book_id INTEGER UNSIGNED
);

The order of words in a 'book' have to be retained.

But what is the best way to store the order of all datasets?

I've already read How to design a database for storing a sorted list? on this site. But I don't have many INSERTs (if I will ever have one) between two records and I don't expect my database to grow up to more than 100k.

So I thought I could use the primary key column id for storing the index in the list. But what if I have to insert one record between two ids?

The other possibilty is to add another column. Is it better to store the numerical position of the dataset or the neighbour IDs in there?

I'm using MySQL.

Example:

I have this list from an external resource:

house
dog
browser
database

Now I have to enter these values in same order into the database!

INSERT INTO words (word) VALUES ('house'), ('dog'), ('browser'), ('database')

The order is now described via the id column (which is the primary key at the same time).

But suddenly, I have to insert another word between 'house' and 'dog'.
I can't simply change the PK id because that would break other table relations.


texts: Trouble when accessing old after deleting a row in postgrsql

texts:
When I delete an athlete from my database I want to take the sum he has raised and divide the sum between all the other athletes in my database.
When I run my code "addBalance" becomes Null and therefore all the sums become Null but when I run the query outside the function it runs exactly as I want it to. It looks like "old" is causing trouble. Any help is appreciated, thank you.

Here is the code and the tables are below the code.

CREATE OR REPLACE FUNCTION divideMoney()
RETURNS TRIGGER
AS $$
DECLARE athleteSum int;
DECLARE peopleCount bigint;
DECLARE addBalance int;
BEGIN
    SELECT sum::INTEGER from People P JOIN Accounts A ON P.ID = A.peopleID
    WHERE A.peopleID = old.ID INTO athleteSum;
    SELECT COUNT(*) FROM Accounts INTO peopleCount;
    RAISE NOTICE 'athleteSum --->%', athleteSum;
    RAISE NOTICE 'peopleCount --->%', peopleCount;
    addBalance := athleteSum / peopleCount::INTEGER;
    RAISE NOTICE 'addBalance --->%', addBalance;
    RAISE EXCEPTION 'STOP';
    FOR person IN 0..(peopleCount - 1) LOOP
        IF person <> old.ID THEN
            UPDATE Accounts
            SET sum = sum::INTEGER + addBalance
            WHERE peopleID = person;
        END IF;
    END LOOP;
RETURN OLD;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER divedeDeletedMoney
BEFORE DELETE ON People
FOR EACH ROW
EXECUTE PROCEDURE divideMoney();

CREATE TABLE Gender (
       gender CHAR(1),
       description VARCHAR(10),
       PRIMARY KEY (gender)
);

CREATE TABLE People (
       ID INT,
       name VARCHAR(50),
       gender CHAR(1),
       height FLOAT,
       PRIMARY KEY (ID),
       FOREIGN KEY (gender) REFERENCES Gender (gender)
);

CREATE TABLE Accounts (
    peopleID int PRIMARY KEY,
    sum varchar(255),
    last_updated date,
    FOREIGN KEY (peopleID) REFERENCES People (id)
);


texts: How can I drop relations in a PostgreSQL database without foreign key check?

texts:
I need to create and delete tables in a test database while in development stage, but foreign keys verification bores me every time I need to erase its tables.

Is there any form to bypass this in Postgresql?


texts:
mongodump failing with invalid json input
mongod --version
db version v3.4.1
git version: 5e103c4f5583e2566a45d740225dc250baacfbd7
OpenSSL version: OpenSSL 1.0.2n  7 Dec 2017
allocator: tcmalloc
modules: none
build environment:
    distmod: ubuntu1404
    distarch: x86_64
    target_arch: x86_64

/usr/bin/mongodump -u backup -p backup12345  --host=XX.XX.XX.XX --port XXXX --out=/tmp -d local -c oplog.rs --gzip --authenticationDatabase admin --query "{'ts': { \$gt: Timestamp(1619548593, 1) } }" --ssl --sslAllowInvalidCertificates

2021-04-28T01:21:37.694-0700    Failed: error parsing query as Extended JSON: invalid JSON input

anyone can figure what's the issue here ?
I understand that I'm using 3.4.1 which is older but still it is working on the other server and with same version. The same query runs perfectly alright on a different cluster node.
Also tried using the latest version of mongodump still the same error.
mongodb-database-tools-ubuntu1804-x86_64-100.3.1.tgz

I passed the same --query parameter as below in my command and I'm getting corresponding error
--query '{ "ts": { "$gt": Timestamp(1111111, 1) }}'

2021-04-28T11:10:01.328-0700    Failed: error parsing query as Extended JSON: error decoding key ts: invalid JSON input. Position: 17. Character: T

--query '{ "ts": { "$gt": ISODate("2021-04-05T08:00:00.000Z") }}'

2021-04-28T11:10:35.627-0700    Failed: error parsing query as Extended JSON: error decoding key ts: invalid JSON input. Position: 17. Character: I

--query "{ 'ts': { \$gt:  ISODate(\"2021-04-05T08:00:00.000Z\") }}"

2021-04-28T11:11:20.797-0700    Failed: error parsing query as Extended JSON: invalid JSON input


texts: Use Dark Theme on SSMS Results Grid, Object Explorer and other windows

texts:
There are several ways to use a Dark Theme on SSMS main coding window, either importing a vsettings file, applying Dark Theme that is disabled on a config file or doing it manually. But all those options do not affect Grid Results, Object Explorer and other windows. Those 2 are the main ones I use other than the coding one.

I tried the usual Tools>Options>Environment>Fonts and Colors then selected on the combo box Show settings for: the option Grid Results and using White for Item Foreground and Black for Item Background. Saved, restarted SSMS but just the text is white, background is still white.

Any idea on what is happening or how to do it? I can't find how to do it for the Object Explorer.


texts: Is the same thing(internally) putting conditions in a query over a view that directly on the view?

texts:
I'm having trouble understanding performance with queries on views. Given this two scenarios, the queries internally will be the same?

SCENARIO 1

View:

CREATE VIEW TEST.SIMPLEVIEW AS
SELECT
    TABLE1.*,
    TABLE2.*
FROM TABLE1
INNER JOIN TABLE2 ON
    TABLE1.id = TABLE2.fk_id;

Query:

SELECT * FROM SIMPLEVIEW WHERE status = 'P'; -- P in select

SCENARIO 2

View:

CREATE VIEW TEST.SIMPLEVIEW AS
SELECT
    TABLE1.*,
    TABLE2.*
FROM TABLE1
INNER JOIN TABLE2 ON
    TABLE1.id = TABLE2.fk_id
WHERE TABLE1.status = 'P'; -- P in view definition

Query:

SELECT * FROM SIMPLEVIEW;

QUESTION

The queries internally will be the same? Disregarding the fact that the condition of status would be or not permanent one.


texts: Get first part of result faster in sorting query on hundreds of millions of rows?

texts:
I have a single table containing around 280 million rows in MariaDB 10.4.10.

I need to process this full table in an external program, sorted by timestamp. The external program is fast; the largest factor is the query speed. This query takes around 37 minutes to run (old laptop, SSD, virtual machine with 8GByte RAM). This is normal, I think.

However, the first result is only returned after 17 minutes, in which the client program is just waiting.

Is there a way to make MySQL return the first results faster, even if the total query time is unchanged, or even slightly longer?

Table structure:

CREATE TABLE `eventlog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `moment` datetime NOT NULL,
  `source_id` int(11) NOT NULL,
  `code` binary(5) NOT NULL,
  `category` binary(2) DEFAULT NULL,
  `type` enum('single','double','triple') CHARACTER SET binary NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_moment` (`moment`)
) ENGINE=InnoDB AUTO_INCREMENT=283252852 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

It is around 12GByte on disk (data length, excluding indexes). The index is 6.5GByte.

Query:

SELECT
  id,
  moment,
  source_id,
  code
FROM
  eventlog
ORDER BY
  moment ASC

EXPLAIN shows it is not using the index on moment to run the query.

MariaDB [test]> explain select id, moment, source_id, code from eventlog order by moment asc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: eventlog
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 282499875
        Extra: Using filesort
1 row in set (0.017 sec)

Profiling the query shows it spends practically all time on "Creating sort index", even though an index on the ORDER BY field already exists. (All other stages in the profile are each less than 0.02s):

*************************** 14. row ***************************
             Status: Creating sort index
           Duration: 999.999999
           CPU_user: 999.999999
         CPU_system: 110.469717
  Context_voluntary: 307668
Context_involuntary: 18439
       Block_ops_in: 74089616
      Block_ops_out: 50343784
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 111
  Page_faults_minor: 6653
              Swaps: 0
    Source_function: <unknown>
        Source_file: sql_select.cc
        Source_line: 21167

I have tried:

Forcing the index: I thought this would make sense, since the query is basically returning the rows in index order. EXPLAIN then shows the index will be used, but the first result is even later (175 minutes?!).
Increasing sort_buffer_size from 2M to 20M, without effect.
Running with LIMIT and OFFSET: the first batch of 500 000 rows is returned in 9 seconds, which is nice, but the total query time (getting all 280 million rows in batches of 500 000) increases drastically as the OFFSET increases, making the total time the external program is idle even longer.

How can I make make MySQL return the first results faster without making the total runtime several times longer?


texts: Is it safe to run antivirus software on my database servers?

texts:
Some implementations of antivirus software are known to Microsoft to cause a variety of problems for SQL Server, and Microsoft has a support article dedicated just to picking the right antivirus software for your SQL Server hosts.

From your experience, is it safe to run antivirus software on your database servers?

Given all the required antivirus exclusions and caveats to consider, is it practical?


texts: Is it possible to Auto Archive Old Data into ReadOnly partitions on a large table with existing PS/PF and auto drop old read only partitions

texts:
Is it possible to Auto Archive Old Data into Read Only partitions on a very large table with existing PS/PF but then also allow continued automatic pruning/dropping of those read only partitions i.e. dropping data after 3 years for GDPR...
So whilst I can see it is possible to
a) auto archive old data into a read only filegroup (to backup once and save from normal backups going forward) and
b) automatically drop partitions that fall out of set retention periods
I'm not sure that I can combine the two?  It also seems you cannot switch data into a partition that isn't on the same FG (the proposed method for archival) - but equally if its in the same FG I can't make it read only(?) - Ideally it would automatically work out what date was 3yrs/2rs ago and alter the PS/PF to redirect that data into an archive file - either natively or post move made read only.
We have up to 3 CDR tables that share the same FG/FS. Partitioned by date, CCI, currently spread across 4 generic files. Controlled through an automated process to forward create partitions 0 and importantly here - drop partitions that go past retention period.  Issue is these are huge tables and take forever in backups which they don't need to be in everyday - one backup of say 2018, and 2019 would be fine... but I'd need to maintain that auto dropping mechanism to comply with GDPR - so potentially need a daily level archive file that is then dropped? (Assuming can drop a read only parititon, and dynamically make today minus 2 years read only on a rolling basis?
Hope that makes sense - running on sql server 2017 btw.
Cheers


texts: How can I calculate the loan balance based on following data set

texts:
There are two loans 12345, 54321.
I want to arrive at 'New Column' which is calculated column based on Balance and Transaction_amount.
Also, need to calculate till maturity date.

CREATE TABLE #Test (
    Account_Number int,
    Maturity_Date Date,
    Interest_Charged_Date Date,
    Transaction_Amount float,
    Balance Float
);

INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-05-12',0,100);
INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-06-12',28.77,0);
INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-07-12',-28.83,0);
INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-08-12',28.77,0);
INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-09-12',28.87,0);
INSERT INTO #Test VALUES (12345, '2016-08-28', '2016-06-28',0,250);
INSERT INTO #Test VALUES (12345, '2016-08-28', '2016-07-28',-17.25,0);
INSERT INTO #Test VALUES (12345, '2016-08-28', '2016-08-28',17.18,0);


texts: Changing the default size and AutoGrowth settings for new SQL Server Databases

texts:
Our technicians regularly go to client sites to fix EPOS tills. Sometimes they create a new database and import the objects and data via a sync.

Ideally, I'd like to force all new SQL Server 2012 databases to default to our particular initial size and autogrowth settings, not the 4Mb out of the box. The sync routines are executables so we can't rewrite them to apply ALTER DATABASE and our technicians aren't always proficient in SQL Server.

Is there any way to reconfigure the new database defaults? I thought there could be something by tweaking the system tables but can't get anywhere.


texts: Connection issues with Azure DB from SSMS

texts:
I am currently making a connection to Azure DB from SSMS using public wifi. For the past few days, I was able to connect SSMS to Azure DB without any issues by adding the current ip address into the server firewall. However, today the following error came up:

Error Message

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and the SQL Server is configured to allow remote connections.
  (provider: TCP Provider, error: 0 -No connection could be made because the target machine actively refused it.)(Microsoft SQL Server Error: 10061)
  No connection could be made because the target machine actively refused it.

Things that I have tried to correct it

Check and retyped server name, login and password
Enable all SQL Server Config protocol
Manually add ip address to azure SQL Server firewall
Enable SQL server through firewall

However the issue is still not resolved, so what should I do?

Results from pinging the name of the Azure SQL server

  Pinging eastasia1-a.control.database.windows.net [191.234.2.139] with 32 bytes of data:

  Request timed out.

  Request timed out.

  Ping statistics for 191.234.2.139:
      Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

  C:>telnet 191.234.2.139 1433

  Connecting To 191.234.2.139...Could not open connection to the host, on port 1433: Connect failed

  C:>tracert 191.234.2.139

  Tracing route to 191.234.2.139 over a maximum of 30 hops

  1     2 ms     1 ms     2 ms  10.27.0.2

  2     2 ms     2 ms     2 ms  172.30.17.1

  3     2 ms     1 ms     2 ms  172.30.2.189

  4     *        *        *     Request timed out.

  5     *        *        *     Request timed out.

*Rest is request timeout


texts: Is there a way to "SELECT and TRUNCATE" without losing new data?

texts:
I have an application that writes raw log files to a table called new in a PostgreSQL database -- lots of rows and lots of duplicate data. Via a cron job running every minute, I normalize the raw data and insert the normalized results into a table called summary, after which I truncate the new table.

The normalization is done all via SQL, but I realize that it may take some time do execute the SQL statement, during which time additional data may get inserted into the new table. This leads me to believe that said additional data inserted while the SQL statement is running may get lost when the table is truncated.

Not knowing exactly how PostgreSQL (or any other SQL-compliant database) handles access to tables when SQL statements are being executed, I'm not exactly sure what to do to ensure no data is lost. Is there a way to "move" all the data in the new table into a temporary table and perform the normalization on the data in the temporary table, leaving the new table empty until new data gets inserted (which will likely happen during the normalization of the data in the temporary table)? Perhaps there's additional/better approaches to this problem, perhaps using transaction blocks?

---- EDIT ----

It never fails... every time I write up a question on Stack Exchange, it gets me to think about the question in different ways and thus leads me to new terms to search for on Google. Happened this time too. As a result, is this answer a potential solution for what I'm wanting to do here? Is it the best solution, given it's now quite dated?


texts: Can DBA create a public synonym for a table that has the same name as an existing table

texts:
Suppose a DBA created a public synonym for a table and named it hr. The user also created a table with the same name hr.

If a user performs a query like the following statement:

select * from hr;

What will be returned by the query?

Whose table values will be shown?

The records of the public synonym
The records of the table


texts: 'The computer must be trusted for delegation and the current user account must...' error when trying to connect to sql server 2008

texts:
Our admin set up a brand new Windows 2012 R2 server with Visual Studio 2015. When I try to connect to an existing sql server 2008 database (running on a windows 2008 r2 server) from Visual Studio, using Tools -> Connect to database..., I can enter all the information, the dialog retrieves the list of databases, Test Connection works, but when I click ok I get:

  The computer must be trusted for delegation and the current user account must be configured to allow delegation

Both servers are under the same domain (development) and I am local admin on both of them. My account though is on a different domain that has access to the development domain.

Any ideas on how to fix this? Are they referring to the windows 2012 server or the database server in the message?

Just as a side note, I can connect successfully from another windows 2008 r2 server using the same windows account.

Update: the connection that fails uses windows authentication.


texts: Identify the type of a SQL Server backup

texts:
Long story short, we had a hosting plan on www.hostforlife.eu (btw, terrible service even on more expensive packages, plus they lost ALL files on their servers and we had to restore our backups as they lost those too) and for db backups we were opening tickets so they would take them manually for us.

When we moved to another host (the plan with hostforlife had expired) and tried to restore the SQL Server 2014 backup we couldn't do it (our host actually).

The backup had no extension and we are not sure what type of backup it is (a couple of methods were tried but didn't work).

We contacted their support for this but because we had no active plan they refused to even tell us how the backup was taken.

We could pay for a 3 month hosting but I do not want to give any more money to this crappy company.

So, my question is, how can we identify the backup type to restore it?


texts: Why does subquery use parallelism and join doesn't?

texts:
Why does SQL server use parallelism when running this query which uses a subquery but it doesn't when using a join? The join version runs in serial and takes around 30 times longer to complete.

Join version: ~30secs

Subquery version: <1second

EDIT:
Xml versions of query plan:

JOIN version

SUBQUERY version


texts: Should we consider "sqlplus" as an Oracle client?

texts:
I am a newbie of SQL*Plus and Oracle, and I have 2 questions about them.

(1) Executing "sqlplus / as sysdba" command:

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 18 05:09:38 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Does the sqlplus connect an Oracle Instance now?

(2) When use sqlplus connect the local Oracle Instance, like this:

$ sqlplus username/password

Should we also consider sqlplus as a client? If it is true, does the sqlplus also read tnsnames.ora file? And the requests also forward to listener process?


texts: Get first and last record for each date and calculate their difference

texts:
I need to calculate the DATEDIFF in seconds between the First record for each date with EventCode = 1 and the last record for each date with EventCode = 2.

I have posted a sample data set for 2 days here: http://data.stackexchange.com/dba/query/515591/calculate-the-difference-between-first-event-and-last-event-for-each-date

So this is how I am getting the First Record for each Date:

Select
    LogDate,
    StartOfDay = MIN(LogDateTime),
    EventCode
FROM (
        Select
            LogId,
            LogDateTime,
            LogDate = FORMAT(LogDateTime, 'dd-MMM-yyyy'),
            DriverId,
            EventCode
        FROM
            #MyLogs
        WHERE EventCode = 1
     ) A
Group By
        LogDate,  EventCode

And this is how I am getting the Last Record for each date where EventCode = 2

Select
    LogDate,
    EndOfDay = MAX(LogDateTime),
    EventCode
FROM (
        Select
            LogId,
            LogDateTime,
            LogDate = FORMAT(LogDateTime, 'dd-MMM-yyyy'),
            DriverId,
            EventCode
        FROM
            #MyLogs
        WHERE EventCode = 2
     ) A
Group By
        LogDate, EventCode

But how do I get the same in single result set? And I need to get the difference in seconds between the First Record for each date and Last Record for each Date.

Example of Result Set:

| Date        | StartOfDay              | EndOfDay                | Start - End |
|-------------|-------------------------|-------------------------|-------------|
| 20-Jul-2016 | 2016-07-20 04:00:32.000 | 2016-07-20 13:05:19.000 |             |
| 21-Jul-2016 | 2016-07-21 04:55:52.000 | 2016-07-21 14:49:25.000 |             |
|             |                         |                         |             |


texts: I am getting data for 15 minutes interval. I would like to aggregate this data to hourly.Can anybody advise how should i do this query to sum the interval values and display it?


texts: SQLAgentReaderRole and SQL AD ID login case affecting ability to edit jobs

texts:
I have AD ID users on a SQL 2008 server that can't edit their own jobs.  They can create them, but in order to edit them, they have to delete them and then re-add them.  The issue is explained here: http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/08/owner-of-the-job-will-not-be-able-to-modify-edit-the-job-in-sql-server.aspx

I did further research and figured out in my particular case it's because I added AD ID users using the CREATE LOGIN [domain\user] syntax.  Using that method, the case of the AD ID is what you specify in the brackets.

Is there anyway to create users with the correct Windows AD ID cases so that I'm not having to figure it out then modify the logins for each AD ID user?

Furthermore, I was using this script to get the correct casing for a particular user:

execute as login = 'usa\johndoe'
SELECT SUSER_SNAME()
revert;

But it was returning all lowercase for me, yet all uppercase when I asked the user to run SELECT SUSER_SNAME() and give me the result.  The correct is all uppercase (well, in order for that user to be able to edit jobs).

I'm at a loss and very frustrated.

--
I've found the best (only?) way to get the correct case is using the SQL interface to add a user, clicking search, typing the AD ID, then clicking check names.  For the example above, it returns all uppercase, despite that script example returning all lowercase.


texts: What happens to the index of a primary key after a DROP CONSTRAINT?

texts:
I am running PostgreSQL 9.1.4.

I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to :

1 - Remove the pkey constraint on the current primary key because it is currently not a serial type.

2 - Add a serial type column and have that new column be the primary key.

3 - Setting the old column of step #1 to UNIQUE and recreate foreign keys for all tables that had a foreign key pointing to my table. In fact, those tables would all have two foreign keys pointing to my table: one towards the old column that is currently the primary key, and another towards the new serial type column

I have successfully created a script that does this, but I wish to know:

a) Does dropping the pkey constraint at step #1 above also drop the index associated to that primary key?

b) If not, is there any way to reuse that index? After adding back the UNIQUE constraint at step #3, would a new index be created or would it use the one that existed before? No content is being changed in the table except for the new column that is created (serial primary key).

EDIT (Some clarifications):

Let's call the old pk column OLD_PK and the to-be-created-by-my-script new pk column NEW_PK
The OLD_PK column's type is INT
The OLD_PK column will not and can not be dropped (its information still has value) therefore it also can not be converted itself to a type serial, the existing data must stay.
I am aware that serial is a shortcut. Like I said I have come up with a working script, the part a colleague and I weren't sure about is the index and how it would work from that point of view.
All tables that previously had one foreign key pointing to OLD_PK will, after the execution of the script, have one foreign key pointing to OLD_PK (say "fkey_old_pk") and another foreign key pointing to NEW_PK (say "fkey_new_pk")


texts: Having trouble designing my preferred backup scheme

texts:
First some background:

I have two Windows servers running MS-SQL-2014 setup in an availability group (synchronous auto-failover, preferred backup on secondary). They host many databases.

Right now we only need weekly backups (copy only-full backups). We don't need transaction log backups but we have to do them (and then immediately delete them) because the databases have to be in full recovery mode for the availability group, so we burn all our storage up if we don't. I use a maintenance plan (copied on both servers) to run these backups.

In the maintenance plan, I backup the dbs, then the transaction logs, then delete older then 6 months dbs, and then delete ALL transaction logs.

My Questions are these:

How can I keep a smaller interval of backup longer, say keep 1 db backup every 6 months after the initial 6 months, but delete the others.
Is there a better way of handling the transaction log issue, then making them and then deleting them. Feels like I'm burning hard drives cycles for nothing.


texts:
I am trying to work out how database roles work.
Here is my user case...
create role user1 login password 'user1';

create schema authorization user1;

CREATE ROLE DEV_ROLE;

grant connect, temporary on database test to DEV_ROLE;
GRANT ALL ON SCHEMA manager TO DEV_ROLE;

Now how do i view the all the grants assigned to 'DEV_ROLE'? This is possible in Oracle but trying to work out the same here.
Do appreciate your reply.
Ta


texts: Configuring physical storage for a Microsoft SQL Server installation

texts:
I'm configuring a Microsoft SQL Server installation which will use a SAN for storage. For SAN storage I have:

A small number of SSD disks
A larger number of 10K disks

My SAN supports storage tiering.

I'm on the fence about how to use the SSD disks. Here are the options that come to mind:

Option 1: Create tiered storage with the SSDs and 10K disks and use auto tiering to move highly accessed data to the SSDs. Then break that storage pool up into several different drives.
Option 2: Place key (I/O intensive) data on the SSDs and throw everything else on the 10K disks.

Questions

I don't have enough SSDs so that everything can reside on SSDs. So what's most important performance wise for SSD storage? tempdbs? LDF files? MDF files? My database is more write intensive than read intensive (if that makes a difference).

Secondarily, are there any best practices I should follow when setting up the drives?

Should I have separate drives for each tempdb? LDF files? MDF files?

What about the system databases like master, model etc., should those go on their own dedicated drives or maybe on the C: drive?

Server disk activiity

By the way, here is a screenshot of our existing database server disk activity. It seems the tempdbs are the most read/write intensive followed by our MDF file and surprisingly the LDF file seems to have the least I/O.

Any advice would be appreciated.


texts: Seeking help to test a use case on sql server memory

texts:
This is a question which came out of curiosity to know of how SQL Server handles memory.

Assume I set a very low value for max server memory and I increase my workload like running dbcc checkdb, rebuild large indexes, select * from really big tables and so on.
Again, this is only my assumption. Since the memory will not sufficient, some part of data/plans resided in RAM will be flushed out of memory.
Now my question is, whether the flushed out data will be placed/pageout to OS pagefile or it spills to tempdb.
Can anybody provide me same use case/test queries to test this behaviour and confirm(DMV query) the "before" and "after" effect to tell,

sql server data has been page out ?
how much data in bytes/KB it has been paged out?
sql server data has been spilled to tempdb ?
how much amount of data has been spilled out?

Basically I am looking for a proper methodology(step-by-step process) to test this behavior. I am getting ideas but was unable to implement properly.


texts: How can I create different sets of sequential values based on another column in a table?

texts:
I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table.

CREATE TABLE Notes (
   Id int IDENTITY(1,1) NOT NULL,
   Type int  NOT NULL,
   CustomerId int NOT NULL,
   -- etc
)

I am migrating data from a legacy system into this table and there is a requirement that Collection and Delivery notes have their own sequential numbers.

I had previously implemented two sequence tables as

CREATE TABLE CollectionNoteSequence (
    Id int IDENTITY(1,1) NOT NULL,
    NoteId int NOT NULL
)

where the Id column is the unique, sequential Id for collection notes, and then the NoteId foreign key's to Notes.Id.

It's getting towards time to do the final (real) data migration and this setup seems hard to work with.

Is there a way I could bin the two sequence tables and add a NoteNo field to the Notes table such that NoteNo would be sequential depending on the Note.Type ? Is this a composite key or something?

The new table might look like

CREATE TABLE Notes (
   Id int IDENTITY(1,1) NOT NULL,
   NoteNo int NOT NULL,
   Type int  NOT NULL,
   CustomerId int NOT NULL,
   -- etc
)

and the data would look like:

Id    NoteNo    Type    CustomerId
1     4000      1       123
2     4001      1       456
3     15123     2       789
4     4002      1       753
5     15124     2       741

I'm using MS SQL Server 2008.


texts: Stored Procedure AddDate function and validation errors

texts:
I am trying to create a stored procedure and testing some basic concepts.

For the last few hours, I am getting errors with this procedure, in terms of syntax. This is in MySQL (MariaDB).

use fifteens
DELIMITER //
 CREATE PROCEDURE TestProcedure() (INOUT :startDate, :count)
   BEGIN
DECLARE x INT default 0;
REPEAT
 SELECT DATE_ADD(startDate, INTERVAL 1 DAY)
   SET x = x + 1;
        UNTIL x  = count;
        END REPEAT;
END//
DELIMITER ;

Any help in resolving this would help me get to the next level :)


texts: Suppression of statement in Postgresql logging

texts:
Given the following SQL function:

CREATE OR REPLACE FUNCTION log_a_notice() RETURNS void AS
$BODY$
BEGIN
    RAISE NOTICE 'This message is coming from log_a_notice()';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

I get the following lines in my log file:

2014-09-11 18:01:20 EDT NOTICE: This message is coming from log_a_notice()
2014-09-11 18:01:20 EDT STATEMENT: SELECT * FROM log_a_notice();

Is it possible to get the NOTICE line, but suppress the STATEMENT line?  If so, what are the correct values in postgresql.conf?

I'm using:

select version();
                                               version
------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
(1 row)

And here are what I believe are the relevant postgresql.conf values:

log_destination = 'stderr'
logging_collector = on
client_min_messages = notice
log_min_messages = notice
log_min_error_statement = notice
log_statement = 'none'

I thought that setting log_min_error_statement = error would work, but then I don't get the NOTICE message in the file.


texts: How to avoid Timeout Error while turning on DB Replication

texts:
I recently tried to replicate a database with only one table in it using transactional replication, using GUI of SQL server management studio. But, as soon, the publication is set up, I get this error.
Logger: Utility.LogHelper
Callsite: Utility.LogHelper+<>c__DisplayClass2_0.<Log>b__2
Message: RawRequestLogManager > Add > Query EXCEPTION:Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
StackTrace:ExecutionContext.RunInternal => Task.Execute => <>c__DisplayClass2_0.<Log>b__2 Exception:Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Inner Exception:
Inner Exception:System.Data.SqlClient.SqlException Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action]) Win32Exception The wait operation timed out

I tried setting the concurrent flag on in @sync_method, but no use. Any way to avoid this error while using transactional replication or should I switch to snapshot or merge replication?
Although, my requirement is to use transactional replication.
Any workaround will be helpful.


texts: We are currently running a working SQL Server 2005 Failover cluster, with 2 nodes. We are now in the process of replacing the servers in that cluster with 2 new servers running SQL Server 2008, my question, should I add the new servers to the current cluster and then retire the old servers or create a new cluster and then copy the databases to the new servers?


texts: Is EXECUTE AS OWNER in msdb a security risk?

texts:
I've been tasked with creating a system that allows non-privileged users to start and stop SQL Server Agent jobs.  I'm creating stored procedures in the dbo schema in the msdb database using the WITH EXECUTE AS OWNER construct to run EXEC dbo.sp_start_job <JobID>;, etc.

The Books Online topic about EXECUTE AS says this is a "best practice":

  Specify a login or user that has the least privileges required to perform the operations defined in the module. For example, do not specify a database owner account unless those permissions are required.

In my case, EXECUTE AS OWNER indicates [sa], since those modules are in the dbo schema, in the msdb database, which is always owned by [sa].

Am I inadvertently creating a security risk by doing this?

I'm not passing in any parameters that could be used in a SQL injection attack since the only parameter ever being passed in is a UNIQUEIDENTIFIER representing the job affected.

The user running the stored procedures that have EXECUTE AS OWNER have no access to the msdb database aside from membership in a database role I've explicitly created which GRANTs them the EXECUTE permission on the stored procs.

One of the conditions of this project is the users in question should not be able to run the SQL Server Agent GUI tools in any way.  This rules out giving them membership in the built-in SQLAgentUserRole role.  I'd also rather not modify the permissions on dbo.sp_start_job, etc, since I'm trying to not modify anything that SQL Server itself depends on (other than obviously adding items to the msdb database itself).

The code in question is here.


texts: SQL Server authentication failed after server promote

texts:
we recently promoted a Windows 2008R2 server to domain controller.
We then found that it hosted a SQL Server 2008R2 Express database instance.
If we try to login to the DB with the domain admin credentials we get a login error 916.
We are also unable to enable the 'sa' user due to error 15151.

Any help will be greatly appreciated.
Alex.


texts: Error while Uninstalling SQL Server 2016 instance

texts:
We have 3 instances on a windows 2012 server 1 instance of SQL Server 2012 & 2 instances of SQL Server 2016.

One of SQL Server 2016 instance is not in use, so I want to remove it.

But when I try to remove it I face this error.

Thanks!


texts:
I have a few questions about the SQL Server error logs.

Are there any performance gains for recycling the SQL Error log?
How often should you recycle it?


texts: How to combine concatenate operator on JSON values with AS

texts:
I'm trying to combine multiple JSON objects into one big object in Postgres.

I could do this with an extra SELECT row_to_json (as in my first example), but this way it creates a extra object, which I don't want because this object is already created by array_agg which is wrapped around it. However, array_agg doesn't accept multiple columns. So I tried to solve it with the concatenation operator, but I can't use AS with it, which is necessary to give the objects the right keys. (And the data type becomes text, which to_json function escapes, which is not perfect but I can live with it).

SELECT to_json(array_agg(g)) FROM (SELECT
        (SELECT row_to_json(e) FROM
            (SELECT ST_AsGeoJSON(ST_Union(cargogeom))::json AS geometry,
            (SELECT row_to_json(d) FROM
               (SELECT cargoid AS voyageid, voyages, cargovalues) d) AS properties,
            to_json('Feature'::text) AS type) e) AS "ThisKeyShouldBeGone"
    FROM (SELECT
            cargoid,
            cargogeom,
            sum(cargonumvoyages) AS voyages,
            sum(cargovalues) AS cargovalues
        FROM "bgbCargoMinardSplit"
        GROUP BY cargoid, cargogeom)
    AS x
    WHERE cargoid = 1000
    GROUP BY cargoid, voyages, cargovalues
    ORDER BY cargoid ) AS g;
            cargoid,
            cargogeom,
            sum(cargonumvoyages) AS voyages,
            sum(cargovalues) AS cargovalues
        FROM "bgbCargoMinardSplit"
        GROUP BY cargoid, cargogeom)
    AS x
    WHERE cargoid = 1000
    GROUP BY cargoid, voyages, cargovalues
    ORDER BY cargoid ) AS g;

Which results in

[
  {
    "ThisKeyShouldBeGone": {
      "geometry": {
        "type": "MultiLineString",
        "coordinates": [
          [
            [..]
          ]
        ]
      },
      "properties": {
        [..]
      },
      "type": "Feature"
    }
  },
  {
    "ThisKeyShouldBeGone": {
      "geometry": [..]

So to get rid of the "ThisKeyShouldBeGone"-key I tried using concatenate operators, but this way I can't use AS:

SELECT to_json(array_agg(g)) FROM (SELECT
            (SELECT ST_AsGeoJSON(ST_Union(cargogeom))::json  || ',' ||
            (SELECT row_to_json(d) FROM
                (SELECT cargoid AS voyageid, voyages, cargovalues) d)  || ',' ||
            to_json('Feature'::text) AS type)
    FROM (SELECT
            cargoid,
            cargogeom,
            sum(cargonumvoyages) AS voyages,
            sum(cargovalues) AS cargovalues
        FROM "bgbCargoMinardSplit"
        GROUP BY cargoid, cargogeom)
    AS x
    WHERE cargoid = 1000
    GROUP BY cargoid, voyages, cargovalues
    ORDER BY cargoid ) AS g;

So is there a way I can combine the concatenate operator with AS, or is there maybe a better way to do this, since the concatenate operator isn't perfect either.


texts: Best way to store a list of numbers where individual numbers can be deleted

texts:
I want to store a list of 50 or less nine digit numbers for each primary key in a DynamoDB. Usually I would be using a get request to retrieve the list for a specific primary key.
Sometimes I would need to add or delete a number from the list. Would it be better to replace the whole list when changes are made or is there a way to structure the data so that individual numbers can be added or deleted. If that is possible, would it be a less efficient way of doing things even though the individual changes would be easier?


texts:
I'm using Heroku's Postgres option and have a backup I downloaded from it. I'd like to restore it into a fresh database so I can look at it. I've been trying different commands to little avail. My first try:

$ sudo -u postgres psql < db/backups/myapp_2018-05-27.pg.dump
The input is a PostgreSQL custom-format dump.
Use the pg_restore command-line client to restore this dump to a database.

So I try:

$ sudo -u postgres pg_restore -d myapp3 -C db/backups/myapp_2018-05-27.pg.dump
pg_restore: [archiver (db)] connection to database "myapp3" failed: FATAL:  database "myapp3" does not exist

I don't really understand the -C option.

  -C
    --create

  Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

  When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

It would seem logical to me to restore into the new database. I digress… (and perhaps that is properly explained here)

I tried creating a fresh database from the template of another, earlier one with the same schema:

$ sudo -u postgres createdb -T myapp myapp3

$ sudo -u postgres pg_restore -d myapp3 -C db/backups/myapp_2018-05-27.pg.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:

<snip>

WARNING: errors ignored on restore: 5

I get a lot of errors about a user that doesn't exist and no data inserted.

I also tried using template0 but that brings up errors about the schema, as I'd expect.

In summary, I'd like to restore from a Heroku Pg backup into a fresh database. I don't know how I can avoid errors about indexes and missing users etc and have the schema ready.

I also tried -O because of this line in the docs:

  With -O, any user name can be used for the initial connection, and this user will own all the created objects.

I seem to have misunderstood that too because, again, the screen was filled with errors.

Edit: Based on the -C linked answer I managed to restore into a cryptically named database, but when I run the rename database command (alter database rtyghjkuyhgh rename to my_app3) it empties all the data :(

Any help or insight, even just a point in the right direction would be very much appreciated.

Postgres version 9.5.12


texts: AWE memory usage growing with SQL Server 2012

texts:
I've got a problem with my SQL Server 2012 installation on my Windows 2008 R2 x64 box.

Because my memory seemed to be rapidly disappearing (according to the Task Manager graph), but I couldn't see any programs using it, I downloaded RAMMap to get a better picture of what's going on.  Turns out all of my memory was being chewed up by AWE.

My memory gets eaten up by SQL Server fairly quickly, up to the maximum that I allow the instance to have (currently 3GB).

When I try to research this, however, everyone seems to say that AWE isn't enabled for SQL Server 2012 64 bit systems, since it isn't needed; the expanded memory that AWE would enable is directly accessible.  But the system still will happily chew up memory in AWE until it reaches its limit.

Not sure what other information would be useful to help diagnose this...or even if I'm totally misinterpreting something...I'm at the limits of my current knowledge about this now. Please don't assume a lot of knowledge on my part...while I am tech savvy in general, I am absolutely not a DBA by any stretch.

Max memory for SQL server is set to 3GB.
Physical memory on the Server is 10GB.
I cannot give SQL Server any more memory - I have a virtual machine that takes up 4GB, Visual Studio, Outlook, IIS, etc.
Somewhat unusually, it is not really being used as a server, but as a development machine, so the DB workload tends to be quite light (though I say that without any hard metrics).