texts: MySQL InnoDB Deadlock For 2 simple insert queries

texts:
I have a deadlock for this two insert queries:

insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561)

insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.611', 180, 4, 181, 563)

Here is the InnoDB Status:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-12-23 15:47:11 1f4c
*** (1) TRANSACTION:
TRANSACTION 19896526, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 17988, OS thread handle 0x17bc, query id 5701353 localhost 127.0.0.1 root update
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition,  nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896526 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 19896542, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 17979, OS thread handle 0x1f4c, query id 5701360 localhost 127.0.0.1    root update
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition,   nextClubId, account_id) values (0, '2014-12-23 15:47:11.611', 180, 4, 181, 563)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896542 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of    table `db`.`playerclub` trx id 19896542 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

The only foriegn key on this table is the "account_id".

Any ideas?

EDIT:
Here is my PlayerClub info:

CREATE TABLE `PlayerClub` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `modifiedBy` bigint(20) DEFAULT NULL,
  `timeCreated` datetime NOT NULL,
  `account_id` bigint(20) DEFAULT NULL,
  `currentClubId` bigint(20) DEFAULT NULL,
  `endingLevelPosition` int(11) NOT NULL,
  `nextClubId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_cagoa3q409gsukj51ltiokjoh` (`account_id`),
  KEY `FK_cagoa3q409gsukj51ltiokjoh` (`account_id`),
  CONSTRAINT `FK_cagoa3q409gsukj51ltiokjoh` FOREIGN KEY (`account_id`) REFERENCES   `PlayerAccount` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1


texts: Shipping big WAL archives to hot standby

texts:
I'm looking to extend my PostgreSQL 9.4 master with a few slaves in hot standby read-only for load balancing.

The idea would be to update the slaves only at defined times (once every 24/48 hours) to avoid migration issues with the application server code and also because the "freshness" of the slaves is not so important.

What would be the effect of suddenly introducing a 1-2 GB of WAL archives to the WAL restore folder on the slave? Would there be a big performance effect on the incoming queries to the slave? Would the slave be available for queries while the WAL logs are restored into the DB?

Alternatively, I've been thinking about copying the whole data folder over from the master with rsync. The negatives I see here are that the required disk space on the slave is 2 x the data folder (extra non-running copy needed to do rsync on), and that the DB server on the slaves has to be restarted to switch to the new data folder.


texts:
I have defined function with table value parameter which returns median as per values passed to it. Function defined as:

    CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY)
    RETURNS INT
    AS
    BEGIN
    <function body>
    END

And Table Type TypeMedian definition as:

CREATE TYPE [dbo].[TypeMedian] AS TABLE(
    [VALUE] [int] NULL
)

Now I have a table Listing with filled values in it as and a table RESULT to be filled according to table Listing:

Tables structure as

LISTING(ListingCol1,ListingCol2,ListingCol3,ListingCol4,ListingCol5)
RESULT(Col1,Col2,Col3,Col4,Col5)

Listing table has more that 1000 rows of data.

All column from both tables are of type int.
Now I want to fill columns of RESULT table and that column could be calculate as:

Col1 = SUM(ListingCol1)
Col2 = SUM(ListingCol2)
Col3 = dbo.fn_GetMedian(ListingCol3)
Col4 = dbo.fn_GetMedian(ListingCol4)
Col5 = dbo.fn_GetMedain(ListingCol5)

And I'm doing so as:

INSERT INTO RESULT(Col1)
SELECT SUM(ListingCol1)

Update RESULT
SET Col2 = SUM(ListingCol2)

DECLARE @tbl_Median TypeMedian

INSERT INTO @tbl_Median
SELECT ListingCol3
FROM Listing

UPDATE RESULT
SET Col3 = dbo.fn_GetMedian(@tbl_Median)

-- For next column
DELETE FROM @tbl_Median

INSERT INTO @tbl_Median
SELECT ListingCol4
FROM Listing

UPDATE RESULT
SET Col4 = dbo.fn_GetMedian(@tbl_Median);

--And this update query I repeating for remaining columns.
How could I do that in single query?


texts: DB backups for production to DR without creating jobs in prod

texts:
I had two servers(SQL 2005) A and B. B is Production server and A is DR. I want to take backups for B server databases from A server. We can not create any maintenance plans or jobs in B production server. We can create any jobs in DR server to take backups for Prod server. Is it possible? both are in different domain. If the servers are in same domain is it possible? and please send the procedure.

Please help me asap.


texts: percona cluster 5.6 - handshake with remote endpoint failed, but endpoint is not part of config

texts:
I set up a cluster of 3 database nodes using a percona 5.6 cluster. Everything works, all nodes are replicated. I configured SSL for state transfer. The cluster recently changed its topography, because we had to migrate 2 nodes to new machines. The cluster was completely restarted recently with configuration files that only contain the 3 current cluster nodes node3, node8, nodeA (don't mind the naming). Earlier we also had node1 but this machine left the cluster and does not run any mysql any more.

Now the problem: In the log files of node3 and node8 I find every couple of seconds an error log that looks like this:

2015-04-20 11:06:24 22546 [ERROR] WSREP: handshake with remote endpoint ssl://<PUBLIC.node1.IP>:57953 failed: 1: 'End of file.' ( )

where <PUBLIC.node1.IP> is the address of the old node1, which is by no means any more part of the cluster. I looked through the my.cnf config files of all cluster nodes, but all traces of node1 are gone from it. How does it come the node3 and node8 try to contact the outdated node? Note, that this is after a complete bootstrapping of the cluster starting from nodeA, which does not show the strange error messages in the log.

I found an error description of a very similar error here: https://mariadb.com/kb/en/mariadb/strange-ssl-messages-in-error-log/

There it was suggested, that this may be due to a broken network configuration where nodes could only ping from one node to the other, but not the reverse direction. But the given reason/solution does not fit in my case, since all nodes can ping each other fine. Also, it does not explain at all the fact, that my cluster nodes try to contact a node that is not part of the cluster any more.

Here is the relevant part of the congiguration file (from node8)

### PERCONA CLUSTER STUFF
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://<PUBLIC.nodeA.IP>,<PUBLIC.node3.IP>,<PUBLIC.node8.IP>
#wsrep_cluster_address=gcomm://
wsrep_node_address=<PUBLIC.node8.IP>
wsrep_slave_threads=8
wsrep_sst_method=xtrabackup-v2
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_name=betdata_cluster
wsrep_provider_options=gcache.size=3G;socket.ssl_cert=/etc/mysql/cert.pem;socket.ssl_key=/etc/mysql/key.pem
wsrep_sst_donor=node3,nodeA
wsrep_node_name=node8
wsrep_sst_auth=sstuser:blahblubb
wsrep_sst_receive_address=<PUBLIC.node8.IP>:14444
query_cache_size=0
query_cache_type=0

Any ideas?

The cluster is working correctly and was started freshly just before I get this error log. I want to understand what is going on. Galera does accept connections fine, also now. All nodes are up and running. Clustercheck says they are OK. The error also is about a node IP address that is not any more part of the cluster. I guess my problems comes from some config that I did not change and don't know about.


texts: Improve reading speed of MySQL table storing stock prices (100 Mil. entries)

texts:
I have a table which stores stock prices in a MySQL database with the following structure:

CREATE TABLE IF NOT EXISTS `tbl_prices` (
  `price_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `stock_id` int(11) NOT NULL,
  `datasource_id` int(11) NOT NULL,
  `price_time` bigint(20) NOT NULL,
  `price_time_frame` bigint(20) NOT NULL,
  `price_open` double NOT NULL,
  `price_close` double NOT NULL,
  `price_low` double NOT NULL,
  `price_high` double NOT NULL,
  `price_volume` double NOT NULL,
  PRIMARY KEY (`price_id`),
  UNIQUE KEY `unique_entry` (`stock_id`,`datasource_id`,`price_time`,`price_time_frame`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

The table mainly acts as a storage, meaning that all data for each stock and time frame is loaded into memory on application start. New stock prices are continuously written to the database.

A common query for loading all prices for a given stock looks like this:

SELECT price_open, price_high, price_low, price_close, price_volume, price_time
FROM tbl_prices
WHERE stock_id=1 AND datasource_id=2 AND price_time_frame=864000
ORDER BY price_time;

EXPLAIN:

EXPLAIN SELECT price_open, price_high, price_low, price_close, price_volume, price_time
FROM tbl_prices
WHERE stock_id=1 AND datasource_id=2 AND price_time_frame=864000
ORDER BY price_time;

results in:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  tbl_prices  range   unique_entry    unique_entry    24  NULL    113036  Using index condition

The table contains more than 100 million entries.

Everything works fine but loading of the prices from the table uses a considerable amount of time. How could I speed up the table architecture?

Regards,


texts:
I would like to know if I can download Oracle Database on my Mac OS X and use it for learning purposes?

I know that Oracle Database Express Edition 11g is free, but is it free for OS X?


texts: Restore from Azure blob storage failing after backup overwrite

texts:
I'm attempting to backup a database (full backup, in simple recovery) to Azure storage, replacing a bak file that already exists, then restoring the new backup. Doing a regular backup and restore works fine, but when I replace a backup in Azure using WITH FORMAT, any subsequent restore commands fail with The media family on device 'https://account.blob.core.windows.net/container/dbname.bak' is incorrectly formed. SQL Server cannot process this media family.

Here's the backup SQL:

BACKUP DATABASE [dbname]
TO URL = 'https://account.blob.core.windows.net/container/dbname.bak'
WITH FORMAT;

This is with a backup already existing at that URI. This part succeeds, and I can see that the modify date of the blob has been updated. Then running:

RESTORE HEADERONLY
FROM URL = 'https://account.blob.core.windows.net/container/dbname.bak'

fails with the above mentioned error. I get the error whether I'm on a different instance or on the same instance as the original database. Is using WITH FORMAT actually corrupting the backup? What am I doing wrong here?

This seems to be the recommended way to overwrite Azure blob backups.


texts: Why doesn't the $1 syntax work for all queries?

texts:
I tried to do this:
COPY postgres_log FROM $1 WITH csv;

And with a separately sent file path, like I make all normal queries (SELECT/UPDATE/INSERT/DELETE). However, it gives:
ERROR:  syntax error at or near "$1"

Um... So does that mean that I'm forced to send it a scary string like this?
COPY postgres_log FROM '/full/path/to/logfileblablabla.csv' WITH csv;

If so, why? Why don't all query types support the parameterized queries interface? Isn't this like asking to enable SQL query injection vulnerabilities?


texts: Should you OPTIMIZE FOR default values in a dynamic search query?

texts:
I have a dynamic search query that looks like this:

@CustID             INTEGER         = -2,
@Name               NVARCHAR        = ''

--more parameters with default values...

Select x, y, z FROM MyTable
WHERE
(@custid = -2  OR CustomerID = @CustID) AND
(Name = ''     OR Name = @Name) AND
--more dynamic search conditions....

We've recently been having a lot of trouble with parameter sniffing and it causes major slowdowns, and even timeouts.

1) I first tried setting all my parameters to local variables, at that fixed the parameters sniffing, but the default query (the query with all default parameter values) was taking 5 seconds when before it would take 1 second. No more timeout level executions though.

2) Next I tried using OPTION (OPTIMIZE FOR (@CustID UNKNOWN, @Name UNKNOWN, ...)), but that essentially does the same thing as using local variables.

3) Last I tried still using OPTION (OPTIMIZE..., but instead of UNKNOWN, I used the default value of each parameter: OPTION (OPTIMIZE FOR (@CustID = -2, @Name = '', ...)). This kept the default search fast (still about 1 second, and it seems all other searches with other values still execute fast.

Is it a good or known practice to to this (#3)?


texts: SQL Server 2005 upgrade to Service Pack 4 (SP4) via command line

texts:
I have been wracking my brain over upgrading SQL Server 2005 to SQL Server 2005 SP4. I have a script for installing new instances of SQL Server for all versions and they all work wonderfully. Problem is, SQL Server 2012 can't upgrade SQL Server 2005 SP3 and below. So, I have to install SP4 to SQL Server 2005, AND THEN upgrade to SQL Server 2012, which is highly annoying.

I need to do this silently via the command line, but I cannot get it to work at all. Here are some samples I tried:

SQLSP4.exe /qb /ACTION=Upgrade /INSTANCENAME="FOOBAR"

SQLSP4.exe /quiet /instancename="FOOBAR"

Among a couple other variations. I could not find anything online at all. In fact, the one helpful thread ended in saying that Microsoft didn't have any support for this, but I'm not sure about that.

What can be done here? Does anyone have any experience in updating service packs via command line?


texts: What is included in ALL permissions for functions in PostgreSQL?

texts:
I am trying to find references to what the difference between EXECUTE and ALL is in:

GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION | ALL FUNCTIONS IN SCHEMA }...

but all I can find is what the docs say:

  EXECUTE
  Allows the use of the specified function and the use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions. (This syntax works for aggregate functions, as well.)

For example, I know that for schemas ALL = CREATE + USAGE but I wanted to double check that in reference to functions:

  This is the only type of privilege that is applicable to functions.

in the above snippet from the docs implies that for functions ALL = EXECUTE and that:

GRANT ALL ON ALL FUNCTIONS...

is equivalent to:

GRANT EXECUTE ON ALL FUNCTIONS...


texts: PostgreSQL vs MySQL benchmarks when a 2nd level cache is included

texts:
Has anyone been able to find any benchmarks comparing PostgreSQL and MySQL performance when they are used by a system through Hibernate (or another comparable ORM) and the 2nd level cache (backed by Memcache, EhCache, etc) is enabled?

I've seen a lot of benchmarks comparing and MySQL performance (most of which are outdated but that's besides the point) but none of them ever seem to factor in a 2nd level cache.  That makes the benchmark entirely irrelevant to me because any highly scalable system that relies on a relational database is going to need a 2nd level cache.

PS Let me know if this is not the site to ask this question and it belongs on one of the many other StackExchange sites instead.  Please migrate the question as usual if that's the case, thanks.


texts:
When attempting to alter a table to enable compression by modifying the KEY_BLOCK_SIZE I received the error:

  ERROR 1296 (HY000): Got error 64 'Temp file write failure' from InnoDB

The command run was:

MariaDB [events_prod]> alter table positions KEY_BLOCK_SIZE=8;

The alter table ran for about an hour before generating this error.  The table in question is 280 gigabytes and the filesystem on which it resides has 3.4 terabytes free.  I am already using per-table files and barracuda for storage.

I can't seem to find any documentation that explains what the cause of this error might be or where to even start trying to resolve it.  Any suggestions are appreciated!


texts:
CREATE   TABLE products(
    products_id serial,
    location_id integer, --(not unique)
    idle_products jsonb --has 1000 elements ('[13,45,8976.....]'::jsonb)
);

CREATE EXTENSION btree_gin;
CREATE INDEX idx ON  products USING GIN(location_id, idle_products);

Take just for example;

products table have  millions of rows and 1000 elements in every row in idle_products column.

SELECT * FROM products
WHERE   location_id IN (SELECT * FROM unnest(array[1245,456,6342,2,23453]::int[])) AND
        idle_products @> to_jsonb(222) ;

On that query what i want to learn is:

Is idle_productsbeing searched through only these 5 rows by composite gin index and if so,  does that mean much more performance gain(because there are only 5x1000 elements in 5 rows in idle_products column,
i mean it would only search through 5 rows using gin composite index) or is idle_products being searched through all rows in products table (despite filtering against location_id) and then reduce(limit) rows to 5
(array[1245,456,6342,2,23453]::int[] has 5 five location_id) by filtering location_id  values?


texts:
We have a SQL Agent job that runs daily that sends emais out to our drivers with their schedules for the next day.

For 85% of drivers, the emails are not getting out of the SQL server. The other 15% send without error.

I checked the logs and see this error:

  The mail could not be sent to the recipients because of the mail
  server failure. (Sending Mail using Account 3 (2020-03-22T16:00:06).
  Exception Message: Cannot send mails to mail server. (Failure sending
  mail.).  )

Does anyone have any idea what's wrong, or how to get a more detailed error?

This problem started after we migrated the server to a new datacenter.

We are running SQL Server 2014 on a Windows Server 2008 R2 host.


texts: Failure Of Backup Due To Limit Exceeded For Recovery Files Since Cannot Reclaim Disk Space [RMAN-03009,ORA-19809,ORA-19804]

texts:
While I try to take incremental backup level 0 by using the command

  RMAN> backup incremental level 0 database plus archivelog;

, encountered this error.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/27/2013 15:58:
05
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 4327473152 limit

When I went through some forums, I found a lot of solutions, but when I checked with their situations, it doesn't match.

One solution was to increase the db_recovery space. So I checked the space and got this result.

db_recovery size

 SQL> show parameter db_recovery;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 4127M

 Space Usage

SQL> select SPACE_USED,SPACE_LIMIT from  v$recovery_file_dest;

SPACE_USED SPACE_LIMIT
---------- -----------
 800155136  4327473152

 Available Space

 SQL>SELECT
NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,
'999,999,999,999') AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT      SPACE_AVAILABLE  PERCENT_FULL
---------------- ---------------- ------------
/u01/app/oracle/fast_recovery_area
   4,327,473,152    3,488,460,800         19.4

 From RMAN

RMAN> show all;

RMAN configuration parameters for database with db_unique_name AGGE are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_agge.f'; # default

What should I do to resolve this problem? Do I have to increase the recovery space even after I am having this much space?

Using ORACLE 11G R2 in RHEL5


texts: Keep a history of transactions for easy rollback

texts: Is there a database (either SQL or NoSQL) which allows you to keep a history or log of all past transactions so that you can easily rollback to any given point in time (similar to how git manages a source repository)? Taking a checkpoint after every transaction probably will do the same job, but keeping a history looks more efficient.


texts: Difference between Client Access Name in SQL server Fail over Cluster and Always on availability group

texts:
We are migrating from SQL Server Fail over Instance to Always On Availability group. The current network name is ABC\ABCTEST where ABC is the SQL server Virtual Name  and ABCTEST is the named instance name. In always on availability group all the connection will make connection through the Listener name for example Listerner123 and it will not use any instance name and the replicas are standalone.

Now is there any way to have the name as VirtualName\InstanceName  or we will simply use the Listener name in the connection strings


texts: Why do I get a BIGINT overflow on MySQL only for some queries?

texts:
I've encountered a weird situation In MySQL5.7.

Of course, the below query is failed as I expected:

mysql> SELECT 18446744073709551615 + 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)'

but the following query worked:

mysql> SELECT 18446744073709551616;
+----------------------+
| 18446744073709551616 |
+----------------------+
| 18446744073709551616 |
+----------------------+

How can it be possible? Is this expected behavior in MySQL or bug?


texts:
I'm currently in-house support for a vendor-supplied application which uses static cursors to crawl through a list of data. I've leaned on the vendor as much as I can to fix their cursor use with no affect, and this software is critical to our operations.

My only recourse in the meantime is to find a way to configure SQL Server that may relieve some of the pressure.

Static cursors that I cannot control are opened with queries that look like:

SELECT {manyColumns} from {table} with (nolock)
WHERE {codeFieldName} > @param
ORDER BY {codeFieldName}
option (fast 1)

The indices are set up well and the result set that ends up being built in tempdb is generated just about as quickly as it can it seems. However, this is where it gets ugly.

Most of the resulting table isn't used. In fact, usually only the first item is used, but these tables are typically thousands of entries (if not tends of thousands) long. What's worse, typically there's no actual need for a static cursor as the underlying data most of the time won't change while the cursor is being accessed. And to make matters even worse, this is the technique that's used to scan an entire subset of the table since these queries are being used only to fetch information about the next item -- create and use cursor, read the top entry, discard the cursor, then create and execute the cursor again... (Yes, this leads to O(n^2) scans, amazingly.)

Is there some way I can force SQL to handle these cursors as dynamic cursors? Or is there a way I can have static cursors not generate a full table in tempdb until the data is about to change? Any other solutions?

Edit: SQL Server 2005 presently, but moving to 2008/2008R2 isn't entirely out of the question. (I have a test environment with 2008.)

Tried so far:

Threw tempdb on a RAM disk. No performance increase. Looks like SQL Server isn't doing much in the way of I/O during these queries and post-2000 versions of SQL have better memory vs. tempdb management. (Note the RAM disk option was taken out after 2000, likely for this reason.)


texts: Can't change _log database physical location in SQL Server

texts:
I just installed Microsoft ServiceBus and it created 5 databases. Now I want to move them to some specific location so I can join them to SQL Server AlwaysOn Availability group.

I stopped service bus farm and tried to execute the following queries one by one:

ALTER DATABASE [SbManagementDB] SET OFFLINE;
ALTER DATABASE [SbManagementDB_log] SET OFFLINE;

ALTER DATABASE [SbManagementDB] MODIFY FILE ( NAME = [SbManagementDB], FILENAME = 'C:\Program Files\Microsoft SQL Server\MyLocation\SbManagementDB.mdf' );
ALTER DATABASE [SbManagementDB_log] MODIFY FILE ( NAME = [SbManagementDB_log], FILENAME = 'C:\Program Files\Microsoft SQL Server\MyLocation\SbManagementDB_log.ldf' );

ALTER DATABASE [SbManagementDB] SET ONLINE;

I brought SbManagementDB offline successfully, but when I tried to do that to SbManagementDB_log I've got the following error:

Msg 5011, Level 14, State 5, Line 2
User does not have permission to alter database 'SbManagementDB_log', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.

I executed the following query SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files and made sure that this DB exists and I tried sa login, but got the same error.

Any ideas what am I doing wrong?


texts: Convert column value stripping html tags into sql view with rows and columns

texts:
I have a table named data with a column pid & display_data.
Pid is the serial number of all rows.
The value of display_data column is as below :
For each row display_Data has values in the format =>
For PID= 1:
The labels are constant for all rows which would become column headers. Values are changing with respect to labels.
There are total 400 such rows hence, the query needs to be dynamic.
I want to parse this column value,strip html tags and into a new view split it using sql query(Regexp_Replace maybe) such that:
All label values become columns, i.e.
The actual data is much more with many labels and values however this is just sample to get help.


texts:
I'm new to distributed databases and there are a couple of things I couldn't figure out.
We're developing an application and need to select the right database,
this application needs strong consistency on financial data related to users, like deposit and withdraws.
I am wondering about tunable consistency in Cassandra, specifically quorum level of consistency.
Imagine the following scenario:
Replication factor is 3.
Initial balance of a user John is 10 on all replicas.
At time t1, an update is happening on John's balance reducing it to 7 (CL=quorum),
Simultaneously, at time t1, a read operation is being run with consistency level set on quorum which is 2 in this case.
Is it possible that the replica on the node where update query is executing on, is not selected for the quorum?
If so, Is it right to assume that the result of read operation would be 10 because there's no write lock?
Overall I'd like to know if it is possible for John to somehow exploit this and spend more than his account's balance.
Thanks.


texts: Append new columns to an existing (empty) table from another table

texts:
Context

I have a PostgreSQL database structure with a table1 defined with many project specific fields (which are empty now, but which will get written through an application):

I have another table, let's say table3, already populated with (many) records of base data:

I'd like to append, in a column-wise manner, all the columns from table3 into table1.
These columns do not exists yet in table1.

The desired results would by as follows:

Question

How to achieve this in a nice way?

What I've tried so far

I have tried to follow this, but without success because the columns defined in table3 do not exist in table1:

UPDATE table1 a
SET (fid, attrib1, attrib2, attrib3, ...)
    =
    (b.fid, b.attrib1, b.attrib2, b.attrib3, ...)
FROM   table3 b
;

I also tried using an `INSERT` statement:
INSERT INTO table1
SELECT fid, attrib1, attrib2, attrib3, ...
FROM table3;

But here I face this (obvious error):

ERROR: INSERT has more expressions than target columns

The third way I was thinking about was to ALTER table1 and add all new columns, one by one, which would be tedious because of the datatype definition (which I don't want to care about because it's already defined in table3). There is probably a smarter way of doing that.


texts: Reducing the size of a PostgreSQL Database, utilizing a process other than full VACUUM

texts:
We have a Postgres 8.4 installation which hosts both our Defect tracking and Code management (Surround) systems. We are looking at converting and migrating Surround to MSSQL on another server. Our Surround structure contains multiple DBs one of which is 121GB, which in order to carry out the migration needs to be purged of its cache.

I have carried out a restore onto my local machine, utilising pg_dump, which restores the DB to a size of 82GB. The premise here was to see if carrying out this process would be efficient, before the migration / conversion, which it very much is.

The problem I'm having is I can't carry out the full VACUUM against this DB, due to the HD space considerations with regards to duplicating its size for this process.

I have come across a very helpful post below, where I can re-initialize the data dictionary after a pg_dumpall and then restore, removing the need for extra HD space required for the VACUUM. The problem here however is that the other DBs will also be included in this process, which is too much downtime. Is there something similar I can carry out against just the effected DB to purge its cache?

I need to run VACUUM FULL with no available disk space

Thanks for your time in advanced.


texts:
Coming from a RDBMS background, this is what I have done in the past.

Create a table with partitions based on a date field. There are typically three tables (partitions) - current (M0), previous month (M1) and previous to previous month (M2).
Detach M2, download data and drop table. Create M2 (empty) again.

How do we achieve the same with MongoDB? I have read about Capped Collections and TTL setting. doesn't seem to fit the bill. There is ranged sharding too; but, we aren't such a huge installation requiring a clustered deployment.

The aim is to have a 'small' data set so that queries run efficiently and API invocations (depending on these queries) do not incur latency.


texts:
I am trying to restore Percona backup but getting the below error.

[/]# xtrabackup --copy-back --target-dir=/backup-billingsystem
xtrabackup: recognized server arguments: --innodb_file_per_table=1 --open_files_limit=16384 --innodb_buffer_pool_size=128M --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=64M --innodb_io_capacity=100 --server-id=1 --log_bin=mysql-bin
xtrabackup: recognized client arguments: --password=* --user=root --copy-back=1 --target-dir=/backup-billingsystem
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
Error: datadir must be specified.


texts: How to enable a SQL Server 2012 login to create, alter and drop the created databases?

texts:
I'm trying to assign a proper GRANT permission to CI login, that will be able to:

Create test DB
Alter it
Drop this test DB (and only it)

But it appears that SQL Server 2012 GRANT CREATE ANY DATABASE only allows the creation, not the altering and dropping of the database (as the newly database does not hold the creator as dbo).

As just giving a sysadmin role is too large of a security risk, any idea what other GRANTS are required?


texts: Getting TEXT data from postgres throught linked server to SQL Server

texts:
I'm using SQL Server 2008 R2 with linked server to postgres 9.4.

My problem is: when I select column with data type text it shows me only XXX first chars and adds (...) on the end.

select description from codes

  1 x   x   2007-08-09
  2 x   x   2007-08-10
  3 x   x   2007-08-10
  4 x   x   2007-08-23
  5 x   x   2007-08-23
  6 x   x   2007-09-11
  7 x   x   2007-09-1 (...)

My second problem is when I try to get it into my SQL Server, I get this error:

  Msg 7347, Level 16, State 1, Line 1
  OLE DB provider 'MSDASQL' for linked server 'lnk_test' returned data that does not match expected data length for column '[MSDASQL].description'. The (maximum) expected data length is 8000, while the returned data length is 8832.

My script:

DECLARE @OPENQUERY NVARCHAR(4000) ,
    @TSQL NVARCHAR(4000) ,
    @LinkedServer NVARCHAR(4000);

SET @LinkedServer = 'lnk_test';
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(' + @LinkedServer + ',''';
IF OBJECT_ID('codes', 'U') IS NOT NULL
    BEGIN
        DROP TABLE codes;
    END;
CREATE TABLE codes
    (
      code_id INT ,
      comment TEXT
    );

PRINT 'Getting stuff';

SET @TSQL = '
        select code_id, description from codes
                                            '')';

PRINT @OPENQUERY + @TSQL;

INSERT  INTO codes
        EXEC ( @OPENQUERY + @TSQL
            );

It drives me crazy...

How can I select FULL value of text data type column in pgAdmin.
Is there any way to get it through linked server?

ps. If I get answer to 1. probably my 2. will be solved by using XML (code_id, description as attributes) it take up to 2GB of data.


texts: How does wired tiger write changes in mongodb?

texts:
Been trying to get the very basics of how wiredTiger (WT) works in MongoDB. I'm only interested in 3.6+
But the docs aren't enough on this matter. This is what I got by bits:
Standard behaviour

Write operations are buffered in-memory (in a snapshot)

Memory is flushed every minute (snapshot written to disk),
a. option is --syncdelay

And Creates a checkpoint of data (write on-disk, data now durable).

And if journaling is enabled (adding up to standard behaviour):

journal helps the system to recover from checkpoint till crash-time
WT uses writes to an on-disk journal file
Journal entries are first buffered in memory
then they are sync to disk every .1 seconds

I'm just checking if this interpretation is correct at a high level, and if you know any extra details or literature to get a better idea.


texts: How to transform results of a select statement formatted as XML?

texts:
When I run the command

mysql EvePlanataryExpenses --xml -uuser -p -e "SELECT sTransactionDateTime, SUM(sQuantity * sPrice) as 'profit' FROM sell GROUP BY sMonth;" |sed '/^\s*$/d'

I get the following output

<?xml version="1.0"?>
<resultset statement="SELECT sTransactionDateTime, SUM(sQuantity * sPrice) as 'profit' FROM sell GROUP BY sMonth" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="sTransactionDateTime">2016-10-13</field>
    <field name="profit">1177089967.25</field>
  </row>
</resultset>

Is it possible to remove the statement="text" from the root element, and set the field name to something else than <field name=""></field> from the SQL query itself?

I saw something here that I thought I could use, but can't make it work.

Or would I need to run the output through a parser (Java, PHP, something else) and get that to remove the statement from the root element and rename the field elements?


texts: Mysql replication problem after export/import on already working replication

texts:
I have 2 databases in master - slave replication.

I had some errors in the replication due to inserts in the slave, so I corrected it and executed:

stop slave;
set global sql_slave_skip_counter=2;
start slave;

After it, I saw that my change wasn't correct so I decided to export the database and import it to the slave.

Now the show slave status seems ok:

 Slave_IO_State: Waiting for master to send event
                  Master_Host: ip
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 7218177
               Relay_Log_File: slave-relay-bin.009595
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 7218177
              Relay_Log_Space: 553
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
 Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

But if I change anything on the master I don't see the change in the slave.
Any suggestions of what can be wrong?


texts: SQL Server Log shipping standby db stuck restoring

texts:
I have 2 servers on SQL Server 2005 SP4. When the secondary server catches up with the log files it is switches into standby mode, but after a while it seems to be 'stuck' in restoring.

There were some open connections & queries on the database, but these have now been closed. The copy and restore jobs are running fine, but its not being switched into standby. Can't see any transactions in the dmvs.

Can I attempt to manually restore the database with STANDBY option to switch it into standby? or will this break the log shipping (need to be careful as snapshot 300GB from a live website)

or is there anything else I need to check for task, etc blocking the switch to Standby..

Cheers


texts:
I am a bit confused about page splits in MS SQL and I'm looking for a definitive answer. There seem to be two versions of the story:

1 - Fillfactor only affects how full pages are at the time the index is created/rebuilt. Page splits are always 50/50

2 - Fillfactor also affects how pages are split. So if there is 70% fillfactor and a page overflows, it will split 70/30

Thanks a lot


texts: Percona/XtraDB Database with many of writes to a small table uses less CPU than the same database with less writes?

texts:
I'm trying to understand why Percona and xtradb InnoDB/MySQL behaves the way it does and if there's anything I can change in the configuration to fix it.

I've a InnoDB database with only one table with about 10 rows.

`ID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`randomnumber` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`ID`)

When I insert a single row, about 1-2 seconds after commit the mysql process causes 30-40% CPU usage for about one second.

INSERT INTO status.statustest (randomnumber) VALUES ('1111111');

But when inserting one row every 500ms for 10 seconds (so in total 20 rows), the CPU spikes only after the last commit.

I found a similar question, but there was no answer to it:

I see this behaviour only with Percona and XtraDB, not with MariaDB.

My system:
2GB RAM
2CPU Cores
20GB SSD
Debian 9.9 x64
mysqld  Ver 5.7.26-29 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release '29', Revision '11ad961')

Has anyone found a solution as how to get rid of those CPU spikes and what causes them?


texts: Pivot a table to repeat the first column for each following column

texts:
i'm not sure how to explain so I'll try and write out an example.

I'm currently using an access database with a table in the following format

1 A B C D
2 A B C D

I'm trying to perform a query that will provide me with an output in the following format:

1 A
1 B
1 C
1 D
2 A
2 B
....

Currently this will be performed on a Access Db but later on it will need to be converted to a SQL Server query.

I'm not that fluent in SQL so any help would be welcome :)

Thanks!


texts:
I have a query that looks like this:

SELECT PubKey, Title FROM Publication
UNION
SELECT NoteKey, Title, FROM Note

Which works fine. My trouble start when I try to add an EXIST to it:

SELECT PubKey, Title FROM Publication
UNION
SELECT NoteKey, Title, FROM Note
WHERE EXISTS (SELECT * FROM UserPublication WHERE UserPublication.PubKey = Publication.PubKey)

That throws this error: The multi-part identifier "PubKey" could not be bound.

Could it be written another way?


texts: Oracle Enterprise manager express is another language

texts:
I have recently installed oracle 12c for my lab, everything went fine, but when i log on the OEM express everything is in spanish, how can i change the language back to english?

I have the OS  (windows 2008 R2) in English and everything is installed in English.

Thanks in advance for the help.


texts: How to script all the service broker elements for each database it is on, in the current server?

texts:
I have service broker running on different servers.

Just recently I had to rebuild one of the test servers and it had a complete service broker solution running there.

For the next time I need to rebuild a server that has service broker running in any of its databases

I would like to script all the service broker architecture for that specific database

-- Message Types
SELECT *
FROM sys.service_message_types;

Basically using the scripts below, how can I be able to script all the service broker elements for a particular database?

-- Message Types
SELECT *
FROM sys.service_message_types;

-- Contracts
SELECT *
FROM sys.service_contracts;

-- Queues
SELECT *
FROM sys.service_queues;

-- Services
SELECT *
FROM sys.services;

-- routes
SELECT *
FROM sys.routes;

-- Endpoints
SELECT *
FROM sys.endpoints;


texts: Query Optimization - Potential ill effects of adding an full text index to a frequently updated table

texts:
We have a query of the following type which we are trying to optimize.

SELECT table_one.*
FROM table_one
INNER JOIN table_two
ON table_two.fk_id = table_one.id
WHERE (table_one.h_id = 20626 AND table_one.is_active = true)
AND (Match(table_two.search_field) against ('+some_term*' IN boolean mode))
ORDER BY table_one.updated_date DESC
LIMIT 20 OFFSET 0;

As evident, table two has a FULL TEXT SEARCH index on search_field. We are planning to get rid of table_two by moving the search_field column to table_one. That would remove join from the query thereby improving the execution time.

However, table_one is frequently updated. How does FTS index respond to frequent update?

Our gut feeling is that the index should be affected as long as the search_field column itself is not updated. But a bit of experienced hindsight would be great.

To add to it, is there any way to improve the ORDER BY clause?
Any other suggestions/improvements would be appreciated.


texts: Migrating Postgres server from Windows to Linux

texts:
Simple question: I want to migrate all of a Postgres server's data, schema, roles, everything from Windows to Linux.

Can I just copy the contents of the %PGDATA% directory in Windows to which the server points to Linux and start the server there pointing PGDATA argument/variable to that directory? In other words, are the data files platform independent to make porting from one OS to another a matter of copying?

I also have developed a script for cloning PG databases, schema+data but, for a variety of reasons, I would prefer to just put the data files on a USB drive and be done with it.


texts: How should I be backing up Postgres for this scenario?

texts:
So the way I have been backing up Postgres is with:

pg_dumpall -U eox-dev -h localhost --clean --file=/home/backups/postgres.sql

I want to make sure I have a production ready backup that has functions, relationships, triggers, in addition to tables and data.

I'm trying to restore it now in a Kubernetes cluster into a database with a different name prod_db -> staging_db, but get FATAL: database "prod_db" does not exist. I'm trying to restore it to staging_db so that makes sense. One of the first lines in the .sql does declare CREATE DATABASE prod_db. I'm not about the edit the .sql. If I have to do that, I'm doing something wrong.

What is the correct way of making this backup?


texts: SA permissions issues with many nested objects

texts:
I have a broker application that's relatively complicated.

Today, after I made some changes, I started getting the error:

  The server principal 'sa' is not able to access the database 'XYZ' under the current security context.

The whole scenario up to the point of the error is:

(In Database ABC)

A broker message gets submitted into a queue
The queue reader picks it up
The queue reader updates a table to indicate the work is starting
This table has an update trigger on it.  The trigger

Checks a table in database XYZ to make sure the inserted value for this field is valid
The table is accessed via a synonym

The check in the trigger I believe is what is causing the issue.

If I run the update manually, it works fine.  I have also used EXECUTE AS to run the update manually as sa which works fine.

Other relevant facts:

sa is the owner of both database ABC and database XYZ
there's no funny business with the sa account - it's db_owner role in both DBs as well

Is there some sort of strange scoping happening because all this is running in the context of broker?

Updates

Some more info:

DB ownership chaining is on at the server level but not in the DBs.  I turned it on and it made no difference.
Using a three part name instead of a synonym didn't make a difference
Profiler trace shows that the statements are running as SA and not another login
Setting both databases TRUSTWORTHY to ON didn't make a difference
If I run the queue activation proc manually, it processes correctly (under my credentials).


texts: How do long columns impact performance and disk usage?

texts:
In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on.

In reality, how much does it really matter? How good is this optimized? What is the impact of just allowing 100 or 200 or even 500 chars for normal "input" fields? An email can only have 320 chars, so ok - there is a good limit there. But what do I gain if I set it to 200, because I do not expect longer e-mail addresses than that.

Usually our tables will not have more than 100.000 rows, and up to 20 or 30 such columns.

We use SQL Server 2008 now, but it would be interesting to know how different DBs handle this issues.

In case the impact is very low - as I would expect, it would help to get some good arguments (backed up with links?) to convince my DBA, that this long-field-paranoia isn't really necessary.

In case it is, I'm here to learn :-)


texts: How can I find the VLF count for a SQL 2005 database without running dbcc loginfo?

texts: Is it possible to find the number of VLFs in a database's transaction log without running DBCC LOGINFO? DBCC LOGINFO seems a little heavy and verbose when count is the only information my t-sql script needs from it.


texts: Why create a Materialized View Log with Primary Key or RowID, etc?

texts:
I have a table with 600million rows. I want to create a materialized view that filters it down to 50 million records but also uses REFRESH FAST ON COMMIT. To use REFRESH FAST I need a MATERIALIZED VIEW LOG on the base table.

I'm looking at how to create one and I see that I can do:

create materialized view log on t
create materialized view log on t WITH PRIMARY KEY ;
create materialized view log on t WITH ROWID ;
create materialized view log on t WITH ROWID, PRIMARY KEY ;
create materialized view log on t  WITH SEQUENCE ;
create materialized view log on t WITH (column)

I don't see or understand the benefits or cons of this various options

This is what I'm thinking of creating:

CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY

CREATE MATERIALIZED VIEW MV_LLATTRDATA_TEST1
    NOLOGGING
    CACHE
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    AS
        SELECT *
        FROM LLATTRDATA D
        WHERE
            (D.DEFID = 3070056 AND D.ATTRID IN (2, 3, 4)) OR
            (D.DEFID = 3070055 AND D.ATTRID IN (3, 30, 31, 2, 24, 23, 4)) OR
            (D.DEFID = 3071379 AND D.ATTRID IN (3, 5, 8)) OR
            (D.DEFID = 3072256 AND D.ATTRID = 5);

BEGIN
  DBMS_STATS.gather_table_stats(
    ownname => 'me',
    tabname => 'MV_LLATTRDATA_TEST1');
END;


texts: mysql rds mutti az 5.6.40 --> 5.6.44 upgrade downtime

texts:
How much downtime would I expect on the mysql minor version upgrade for multi az rds?

I tested it on a single az read replica and there was about 5 minutes of downtime. How much can I expect on multi az?

There are 500 databases each with 100 tables if that makes any difference.


texts: Retrieving the value of $1, $2 etc. from a Postgres Query

texts:
I am currently trying to update/modify a number of SQL queries on a system that does not have any internal Documentation (Developer left on short notice and on bad terms).

I have enabled Query logging in Postgres and have tracked down the Queries in question to be in a number of approximately 30 queries. From there, I plan on running them and seeing what the output is and then determining which ones need the updates.

The problem is that all them in the log are in this format (Simplified)

Select t.Col1, t2.Col2, t3.Col3 from Table t
join table2 t2 on t2.Col1 = t.Col1
join table3 t3 on t3.Col1 = t2.Col3
where t.Col1 = $1
and t2.Col3 = $2
...
and t3.Col20 = $15

etc.

For some of the parameters, I've been able to guesstimate what the most likely values are (based on the info in the column it looks up), however there are a few that I've got no idea on what they could be.

I'm not super-familiar with Postgres (more of an MSSQL man myself) nor am I familiar with using parameters/variables in queries. It seems to me these values must be either generated somewhere or held somewhere and most be evaluated at some point in order to run a valid query. As such, either logging the values or logging the input or something similar should be possible so I can then finish what is required.

Hoepfully someone can advise how to get the 'real' values of the variables $1,$2, $3 etc.


texts:
I have read through the MongoDB manual about sharding and replicasets.

However, I would like to know if the following can be achieved with sufficient performance (read/write):

Saving 10,000,000 files via GridFS on MongoDB instances
Total file size of about 2TB without indexes and journals
10,000 writes / day
10,000 reads / day
Querying for a document does not need respond instantly, 2 seconds is still acceptable

Setup in mind:

1 Replicaset of 3 nodes
Each node with 32GB Ram, 2TB SSD
1 Mongos instance
1 Config server

In the case the proposed setup would not suffice to obtain the goal of storing 10,000,000 documents and writing/reading 10,000 a day with fairly good performance, what would be the next step to take?

Adding more RAM to each node
Adding more disk capacity to each node
Adding Another replica set with the same configuration
Sharding

I would like to avoid sharding as much as possible since I consider it best practice to avoid sharding whenever possible.
I feel this would only complicate the topology unnecessarily and this would most likely be overkill in this case

Some advice would be much welcome.

Cheers


texts:
I have two tables like this:

CREATE TABLE cmap5 (
   name     varchar(2000),
   lexemes  tsquery
);

and

CREATE TABLE IF NOT EXISTS synonyms_all_gin_tsvcolumn (
   cid       int NOT NULL,  -- REFERENCES pubchem_compounds_index(cid)
   name      varchar(2000) NOT NULL,
   synonym   varchar(2000) NOT NULL,
   tsv_syns  tsvector,
   PRIMARY KEY (cid, name, synonym)
);

My current query is:

SELECT s.cid, s.synonym, c.name, ts_rank(s.tsv_syns,c.lexemes,16)
FROM synonyms_all_gin_tsvcolumn s, cmap5 c
WHERE c.lexemes @@ s.tsv_syns

And the output is:

cid     |  synonym                              | name (query)              | rank
5474706 | 10-Methoxyharmalan                    | 10-methoxyharmalan        | 0.0901673
1416    | (+/-)12,13-EODE                       | 12,13-EODE                | 0.211562
5356421 | LEUKOTOXIN B (12,13-EODE)             | 12,13-EODE                | 0.211562
 180933 | 1,4-Chrysenequinone                   | 1,4-chrysenequinone       | 0.211562
5283035 | 15-Deoxy-delta-12,14-prostaglandin J2 | 15-delta prostaglandin J2 | 0.304975
5311211 | 15-deoxy-delta 12 14-prostaglandin J2 | 15-delta prostaglandin J2 | 0.304975
5311211 | 15-deoxy-Delta(12,14)-prostaglandin J2| 15-delta prostaglandin J2 | 0.304975
5311211 | 15-Deoxy-delta-12,14-prostaglandin J2 | 15-delta prostaglandin J2 | 0.304975
5311211 | 15-Deoxy-delta 12, 14-Prostaglandin J2| 15-delta prostaglandin J2 | 0.304975

I would like to return the name matches of all rows in cmap5 in my main table ranked by the ts_rank() function but for each row in cmap5 I want to:

select only the best X cids to each query (group by cid)
or ORDER BY my results as 1+ts_rank/count(cid)

To get the best match I tried to add select distinct on c.name, but when the rank is the same I want to get the cid with more matches to the query. I have tried adding a simple group by at the end of the query but I get an error, how could I do this?

Added comments:

On one hand for those results whose rank is the same, eg. above 5283035 and 5311211, get 5311211 as top result because that cid has more hits than 5283035, so I sort of want to take into account the number of hits / cid in the rank, like final_rank = 1+ts_rank(cid)/no. of hits(cid).

On the other hand I want to get the first X cids per query name. If I use LIMIT X it returns the first X results of the entire query table, not the first X per name (row) of the query table as I want.


texts: Impact of switching a column's encoding from utf8 to utf8mb4 in MySQL

texts:
Background Info

Let's say I have a table in MySQL that has +1 billion records. I have a column with that contains some description of a post. We don't index, search, or sort this column in any way.

Currently the description column has an encoding of utf8, which in MySQL doesn't support the full unicode standard. I want to add support for emojis, other languages, etc. so I need the column to be utf8mb4

Question

If I run something along the lines of:

ALTER TABLE
user_posts
CHANGE post_description
VARCHAR(300)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

What actually happens in the background, and would it have a massive performance impact during the operation on a very large table with (+1 billion rows)?

Since there is no index, sorting, or searching in this column, would it need to modify each record? oOr since the current utf8 records won't be changing will it just change the table schema. O(n) or O(1)


texts:
Previously I asked about latch contention / locks which can happen during Service Broker work: Service Broker locks.
I got a great answer on general question but I still have narrowly focused question.
MSDN ( SEND ):

Messages that are sent to services in other instances of the SQL Server Database Engine are stored in a transmission queue in the current database until they can be transmitted to the service queues in the remote instances. Messages sent to services in the same instance of the Database Engine are put directly into the queues associated with these services.

Question: Is there a way to force a message to be put in a local transmission_queue even if services are located on the same Database Engine?
It could be preferred solution in my circumstances where I have sufficient hardware resources but sometimes I get latch contention.
Why it can help is described in the link above

Test after David's answer.
Preparation:
-- Source DB
use master
go
drop database [TestSendMessage]
go
create database [TestSendMessage]
go
alter database [TestSendMessage] set enable_broker with rollback immediate;
go
use [TestSendMessage]
go
create message type datachanges_messagetype
  validation = none;
go
create contract datachanges_contract ( datachanges_messagetype sent by initiator );
go
create queue dbo.TestSendMessage_initiatorqueue
  with status = on
     , retention = off
    , poison_message_handling ( status = on )
  on [default];
go
create service TestSendMessage_initiatorservice
  on queue TestSendMessage_initiatorqueue
  ( datachanges_contract );
go
DROP ROUTE [AutoCreatedLocal]
go
CREATE ROUTE Forwarder
    WITH
    ADDRESS = 'TCP://localhost:4022' ;
go
grant send on service::[TestSendMessage_initiatorservice] to public;
go

-- Target DB
use master
go
drop database [TestReceiveMessage]
go
create database [TestReceiveMessage]
go
alter database [TestReceiveMessage] set enable_broker with rollback immediate;
go
use [TestReceiveMessage]
go
create message type datachanges_messagetype
  validation = none;
go
create contract datachanges_contract ( datachanges_messagetype sent by initiator );
go
create queue dbo.TestReceiveMessage_destinationqueue
  with status = on
     , retention = off
    , poison_message_handling ( status = on )
  on [default];
go
create service TestReceiveMessage_destinationservice
  on queue TestReceiveMessage_destinationqueue
  ( datachanges_contract );
go
DROP ROUTE [AutoCreatedLocal]
go
CREATE ROUTE Forwarder
    WITH
    ADDRESS = 'TCP://localhost:4022' ;
go
grant send on service::[TestReceiveMessage_destinationservice] to public;
go

-- endpoint
if not exists ( select * from sys.endpoints where name = 'ServiceBrokerEndpoint' )
create endpoint ServiceBrokerEndpoint
  state = started
  as tcp ( listener_ip = (127.0.0.1), listener_port = 4022)
  for service_broker();
go

-- Tests
use [TestSendMessage]
go

declare @handle  uniqueidentifier = null

begin dialog conversation @handle
                    from service TestSendMessage_initiatorservice
                    to   service 'TestReceiveMessage_destinationservice'
                      on contract datachanges_contract
                    with encryption = off;

select @handle; -- 0EC9CCEE-13D3-EA11-B4E1-983B8F11A147
;send on conversation '0EC9CCEE-13D3-EA11-B4E1-983B8F11A147' message type datachanges_messagetype( '[{aaa:1}]' );
select * from sys.transmission_queue

And yeah I see that local sys.transmission_queue is used using query and XE Trace:

BUT! Some time later ( maybe several seconds, I don't know exactly ) if I send several messages using the conversation, I do not see messages in sys.transmission_queue and that's what's in the XE:

IF I create a new conversation or new_broker I'll get the same situation: sys.transmission_queue usage at the beginning and then - no
select @@version

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
    Mar 18 2018 09:11:49
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18363: ) (Hypervisor)


texts: How to Restrict INSERT operations on table for DB2?

texts:
I have a table OAUTH20_TOKEN_CACHE for which I have restricted INSERT operations by running below command.
db2 REVOKE INSERT ON HVD121.OAUTH20_TOKEN_CACHE FROM USER HVD121

This could be seen
INSERTAUTH = 'N' for particular grantee 'HVD121' in the 'SYSTABAUTH' table,but still I could see INSERT operations succeeding for the particular table wherein the connection is established using user 'HVD121.

Is this correct approach??


texts: Incorrect results when combining full-text search and regular comparison

texts:
create table "Foo"
(
"id" integer identity (1000, 1) not null,
"name" nvarchar(100) not null
)
alter table "Foo" add constraint "Foo_PK" primary key ("id");
create fulltext index on "Foo" ("name" language 0x0) key index "Foo_PK" on "catalog" with stoplist = off;

The following query returns no rows
select * from "Foo" where contains("name", ' "پ*" ') and "id" > 1082;

While there are 26 rows matched by contains("name", ' "پ*" ') and 87 matched by "id" > 1082 individually. The following two queries have 23 rows in common, oddly, their intersection returns no rows.
select * from "Foo" where contains("name", ' "پ*" ');
select * from "Foo" where "id" > 1082;

And more strangely, the following query returns exactly 23 rows. All "id"s are above 1082.
select * from "Foo" where contains("name", ' "پ*" ') and "id" > 1081;

Someone please explain what is going on in SQL Server.


texts: mysql multiple transaction rollback problem

texts:
I want to have multiple procedure call and one transaction commit, for example:
call CreateRestaurant(?, ?, ?);
call CreateMenu(?, ?, ?, ?, ?);
call CreateMenuItem(?, ?, ?, ?);

but I want to wrap all these call procedure in one place, is there anyway to achieve this goal?


texts: Data Loss when forced failed over to secondary replica on Sql Server 2014

texts:
Setup:

2 Availability Groups
2 Replicas on Synchronous Commits.
1 Replica on Asynchronous Commit.
Automatic Failover configured for the two replicas on Synchronous Commits.
Manual Failover configured for the single replica on Asynchronous Commit.

  Problem:

ESX Server for the primary replica went down.
Automatic failover didn't happen.
Manual forced failover was performed using the command:

ALTER AVAILABILITY GROUP AccountsAG FORCE_FAILOVER_ALLOW_DATA_LOSS;
This resulted in a lot of data loss.

  Question:

What am I missing here ?
Why did the data loss happen? My understanding is that since two replicas was on synchronous commit, there would be no data loss when either one of them goes down.
Is it the data in the Log transactions that was lost. Would periodic log shrink prevented this data loss ?


texts: Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE?

texts:
Since MySQL 5.6 introduced online DDL, the ALTER TABLE command can optionally have either ALGORITHM=INPLACE or ALGORITHM=COPY specified. The overview of online DDL notes that, by default, INPLACE is used wherever possible, and implies (without ever quite stating it) that the INPLACE algorithm is cheaper than the COPY one is.

So what reason would I ever have to specify ALGORITHM=COPY on an ALTER TABLE statement?


texts: Failing on queries with length of multiples of 32

texts:
tl;dr: Every SQL query of length 64 or higher that is a multiple of 32 results in the connection closing with a 2013 error when interacting with our remote MySQL server.

I am having the strangest issue with our MySQL database. For background, it is running MySQL v5.6 on a remote server hosted by Network Solutions which our IT requires us to use. As such, privileges are really lacking. I can create, read, update, and delete databases, tables, columns, rows, etc., but I can't do things like restarting the server or defragging. I don't believe our IT has access to do so either and this is just a Network Solutions thing.

When updating some values, I noticed that some queries were causing a MySQL 2013 error which is

  Lost connection to MySQL server during query.

Those queries would continue to fail on retries, and past successes would still succeed. I realized that the common factor was that the failed queries all had a total length of 64, ex.

SELECT code FROM my_database WHERE assignee = 'abcde@gmail.com'

Also noticed that it will continue to error out if the length is any other multiple of 32, except for 32 itself. The assignee in this instance can theoretically be any length up to 50, so the query can always theoretically be a multiple of 32 no matter how I format it.

I've tried with different tables, databases, and clusters and they all have the same issue. Also tried running the queries on a different computer. Strangely, phpMyAdmin does everything just fine.

The only solution I have right now is to check assignee length and change the query accordingly so that it isn't going to fail but this is a lot of work to put in place for every single SQL statement I have written in our systems.

This might be an issue to bring up with Network Solutions but they have been extremely unhelpful in the past when it comes to technical issues and I don't even know where to start on telling them how to fix it.


texts: How does SQL Server determine the order it takes locks in while selecting a table?

texts:
I have two stored procedures that are deadlocking when the system is under load. Proc A is selecting from a table while the Proc B is inserting into the same table. The Lock Graph is showing that Proc A has an S mode page lock that Proc B wants an IX mode lock for, Proc A however is waiting for an S mode page lock for a different page that Proc B already has a IX mode page lock on.

Clearly this could be sorted out by ensuring that both the queries lock pages in the table in the same order, but I can't figure out how to do that.

My question is: How does SQL Server determine what order to lock pages in while doing INSERTs and SELECTs and how can you modify this behaviour?


texts: Can we create a stored procedure to copy detached .mdf and .ldf files from original location to new location?

texts:
I am trying to create a script to attach database, move .mdf and .ldf files to new location and then attach the database. I created a stored procedure to detach and attach the database. Can I get a suggestion as to how I can move the files from the script itself?

Use master;
Go
Create procedure User_sp_detach_attach
    @db_name varchar(max),
    @new_mdf_path varchar(max),
    @new_ldf_path varchar(max)
As
Set NoCount On;
declare @db_id smallint;
declare @mdf_file varchar(max);
declare @ldf_file varchar(max);
declare @ori_mdf nvarchar(max);
declare @ori_ldf nvarchar(max);
declare @new_mdf_file nvarchar(max);
declare @new_ldf_file nvarchar(max);
declare @sql varchar(max);
set @db_id=DB_ID(@db_name);
set @mdf_file=@db_name+'.mdf';
set @ldf_file=@db_name+'_log.ldf';
select @ori_mdf=physical_name from sys.master_files where database_id=@db_id and file_id=1;
select @ori_ldf=physical_name from sys.master_files where database_id=@db_id and file_id=2;
set @new_mdf_file=@new_mdf_path+'\'+@mdf_file;
set @new_ldf_file=@new_ldf_path+'\'+@ldf_file;
print 'New location of .mdf file: '+@new_mdf_file;
print 'New location of .ldf file: '+@new_ldf_file;
EXEC sp_detach_db @db_name;
--Move files
Set @sql='CREATE DATABASE ' + @db_name + ' ON (FILENAME=''' + @new_mdf_file + '''), (FILENAME=''' + @new_ldf_file + ''') FOR ATTACH;'
Print @sql;
Exec (@sql);
Print 'Moved and attached successfully';
Go


texts: Index statistics are requiring constant updates due to timeouts

texts:
We have been experiencing an odd behavior in our application where various modules will begin to timeout in SQL Server 2012. Each time we stumble across this issue, we find that the statistics require update and that running exec sp_updatestats fixes the issue. After updating index statistics, the timeouts go away.

However, the frightening issue is the frequency in which we have been experiencing the need to update the statistics, and the fact that nearly all of the statistics are showing a need to be updated across all of our tables related to order processing. Due to the frequency, we have setup a job to run sp_updatestats prior to business hours at 7:30 AM.

This seemed to have calmed the issue while we continued to investigate until it occurred again today. Not 10 minutes after business opening, they were receiving timeouts. I immediately ran sp_updatestats and the timeouts disappeared and application function returned to normal. The order volume since business opening was low (less than 20 rows added to the primary order table), yet the statistics became so bad between 7:30 AM and 8:10 AM that we began experiencing time outs.

A couple of notes:

This behavior is reproducible on QA environments (but is completely random), and we have been trying to narrow down the cause when it does occur but have not had any success identifying the issue yet.
WAIT STATS are close to perfect - contention with tempdb is minimal with very few instances of PAGELATCH_* waits showing.
Auto statistics updates is set to on.
We have a nightly maintenance plan that rebuilds or reorganizes indexes based on fragmentation level.

We are running two servers, a PRIMARY and REPLICA within an AlwaysOn Availability Group. The VMs are housed on Azure and have 8 logical processors, 28GB of memory, and all SSD drives. The data file layout looks like:

E: - tempdb/templog - we have eight 8GB files with no auto growth set.
F: - SQL data files. Each tenant is split into its own file group (.ndf).
G: - SQL log file.

Has anyone experienced behavior such as this, or know what could be causing index statistics to go awry so quickly across so many tables? Any recommendations for diagnosing next time this occurs? Manually running the stored procedures and diagnosing the execution plans have proved rather unhelpful as the statistics its reading (Expected/Actual Rows) are the same.


texts: SSMS shortcut to comment/uncomment a specific part of the query?

texts:
I'm looking for an option to comment/uncomment a particular part of the query with the Slash Star Comment option.

Scenario:

While debugging the queries with N numbers of JOINs, I comment out each table in the join and its related conditions one by one and find which table causing the issue or wrong data. If the commented table didn't cause the issue, I will uncomment the table and its related condition and try with the next table.

For this purpose currently I manually add -- comment in front of the particular line or press Ctrl + C to comment the entire line, if the select/conditions are in middle of the line, I move the select/condition alone to the next line and add -- comment. If there is no issues with the join, again I undo the changes or Ctrl + U to uncomment.

Moving the condition to the new line and adding the comment is burden to me, some time it caused bad alignment too.

Expectation:

What I want is need some shortcut key to add the /* in front and */ in end of the selected text.

Samples:

The below is the sample case, which my query looks like (in actual it may contain more select columns, tables and conditions).

SELECT T1.Id, T1.Name, T1.Designation, T1.CreatedDate,
       T2.AccountName, T3.LinkId, T3.LinkName, T4.OwnershipName
FROM TableOne T1
INNER JOIN TableTwo T2 ON T2.Id = T1.Id AND T2.Name = T1.Name
INNER JOIN TableThree T3 ON T3.Id = T2.Id AND T3.IsActive = 1
INNER JOIN TableFour T4 ON T4.Id = T1.Id AND T4.LinkId = T3.LinkId AND T4.IsActive = 1
WHERE T1.CreatedDate BETWEEN @FromDate AND @ToDate AND T1.DeletedDate IS NULL
    AND T2.DeletedDate IS NULL AND T3.DeletedDate IS NULL AND T4.DeletedDate IS NULL

Here if I want to comment the TableThree and its related content in the SELECT, JOIN and conditions in WHERE. I will select the content and press the shortcut key to add the /*  */ comment separately.

Expected the behavior is:

SELECT T1.Id, T1.Name, T1.Designation, T1.CreatedDate,
       T2.AccountName/*, T3.LinkId, T3.LinkName*/, T4.OwnershipName
FROM TableOne T1
INNER JOIN TableTwo T2 ON T2.Id = T1.Id AND T2.Name = T1.Name
/*INNER JOIN TableThree T3 ON T3.Id = T2.Id AND T3.IsActive = 1*/
INNER JOIN TableFour T4 ON T4.Id = T1.Id /*AND T4.LinkId = T3.LinkId*/ AND T4.IsActive = 1
WHERE T1.CreatedDate BETWEEN @FromDate AND @ToDate AND T1.DeletedDate IS NULL
    AND T2.DeletedDate IS NULL /*AND T3.DeletedDate IS NULL*/ AND T4.DeletedDate IS NULL


texts:
I am attempting to create a collection of scripts to modify some tables in a Postgres 9.6 database. These scripts are stored in a variety of directories, and are in turn called from one master coordinator script. I'm running the scripts in psql.

In the master script, I use \ir to specify relative pathnames to the other scripts. Accordingly, I can run psql from anywhere, and the master script will find the other scripts. This works as expected.

In some of the scripts, however, I am using the \copy command to load data from CSV files. Unfortunately, it appears that the input filename for \copy is always interpreted as relative to psql's current working directory, rather than relative to its script's directory, even if I call the script containing the \copy command using \ir.

For example, let's say I start psql in C:\, and plan to use the following files:

C:\
    dir1\
        script1.sql
        dir2\
            script2.sql
            input.csv

I can call the first script using \ir dir1/script1.sql. That works fine.

Also, that script can call the second script using \ir dir2/script2.sql. That works fine, as well.

The problem is the \copy command in the second script:

\COPY foo.bar (col1) FROM 'input.csv' WITH (FORMAT CSV);

This fails unless input.csv exists in the current directory of the psql session. Therefore, my question is:

Is there any way for me to call the \COPY ... FROM command from inside a script without requiring the user to run the psql session from a specific directory relative to the input file?

I understand that I could use an absolute path name, but we're going to be running this script from a variety of different machines/platforms when upgrading our different deployments, so there is no common absolute path that will work in this case. I'm looking for some sort of relative or configurable path solution.

Thank you, in advance.


texts: DBCC CHECKDB returning space issue error

texts:
My database in Production has 28ndf files in different Drives.And on T drive test_audit20.ndf to test_audit23.ndf files only. and file test_audit20 and test_audit21.ndf autogrowth is none.
 While executing checkdb command on test_audit db I am getting error :

  Msg 1823, Level 16, State 6, Line 1
  A database snapshot cannot be created because it failed to start.
  Msg 1823, Level 16, State 7, Line 1
  A database snapshot cannot be created because it failed to start.
  Msg 1823, Level 16, State 8, Line 1
  A database snapshot cannot be created because it failed to start.
  Msg 7928, Level 16, State 1, Line 1
  The database snapshot for online checks could not be created.
  Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams.
  Attempting to get exclusive access to run checks offline.
  Msg 8921, Level 16, State 3, Line 1
  Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
  Msg 3313, Level 21, State 1, Line 1
  During redoing of a logged operation in database 'test_audit', an error occurred at log record ID (19372:991854:10).
  Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
  Msg 9001, Level 21, State 7, Line 1
  The log for database 'test_audit' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
  Msg 5128, Level 17, State 2, Line 1
  Write to sparse file 't:\test_audit23.ndf_MSSQL_DBCC6' failed due to lack of disk space.
  Msg 0, Level 20, State 0, Line 0
  A severe error occurred on the current command.  The results, if any, should be discarded.

I am not sure why it would need to write to this disk.

It could be that auto-grow is turned on for one of the files on this disk.  We could turn that off and it might begin working .

Currently I have X and Y drives that are the “active” data drives, where growth should happen.  Those are also almost full (3TB each) and I have to stand up some new disk drives and consolidate the data files to them.  I have hit our limit with AWS for provisioned terabytes in this region.

Additionally, I have been manually extending these data files every week.  I cannot rely on SQL’s auto-grow as I have mentioned before, as it waits until all files are full.  This causes data writes to be centralized into one disk/file and has a dramatic negative impact on performance when this happens – and causes a surge in tickets to Customer Support and impacts  SLAs.

My question is can I try turning auto-grow off on any data files on the T drive, and try again?  will it cause any issue or is there any other way.


texts: Is there a well-known name for this "poor man's ref. integrity" schema design pattern?

texts:
Is there a name for the following database schema design/pattern? My eventual goal is to find more literature about the subject. Today's cursory net search was too full of generic words to be able pin down the term (if any exists) for this kind of thing:

Fruit (id, farm)

Apple (fruit_id, color)
    [fruit_id => Fruit.id]

Banana (fruit_id, length)
    [fruit_id => Fruit.id]

Orange (fruit_id, is_seedless)
    [fruit_id => Fruit.id]

FruitPack (id, destination)

FruitPackFruits (fruitpack_id, fruit_id, fruit_type)
    [fruit_id => Fruit.id, fruit_type => VARCHAR]

Where fruit_type would be a varchar column filled with values like "Apple, Banana, Orange, Cherry".  It's some kind of "poor man's referential integrity".  Obviously, one the failures of this kind of design is being able to insert values that don't resolve out to a useful join (ie: there are no cherries to speak of here).

Here's another example of such a pattern: A single "log (id, table_name, record_id, timestamp)" table that acts as a sort of tracker for modification-times in various other tables.  Strictly speaking, it's got no ref integrity, but, the (table_name, record_id) part is supposed to refer to some record in another table, requiring a join to actually get the full data.

I'm going to take for granted that the schema is a sufficient caricature of some sort of collection of groups of items for the people here.

The question is: What's this kind of "poor man's referential integrity" called?

I'm not trying to learn about referential integrity. I want to identify this poor design's name and look further into the "let's design a database schema" aspects (ex: pros, cons, opinions, teachings, etc) that have to do with this commonly seen disaster of a schema.


texts: Default instance name different than server name

texts:
I just started a new gig, and have discovered that two instances of SQL 2012 (SP2), installed on Core Server 2012 R2, have a different name than the server it's running on. They are both virtual servers. For example, the server name is Server2 and Server3 (can RDP to it and can connect to it multiple ways using this name) and can even connect with SSMS from other machines. However, when I run @@Servername, it returns "CO-SQLTEMPLATE"--on BOTH servers. The reason this is troublesome is because these two instances participate in AlwaysOn replication, and there are several problems...the most glaring one is automated backups will not work. Using the HADR systables and functions, it either shows that none of the replicas are "primary" or all of them are (!!!). Also, it is not clear that the replication is actually working. In a couple of the AGs, it shows that the databases are synchronized with the Primary (Server1), but I don't trust that since there are essentially 2 instances with identical names.

I've been hunting around on Google, and cannot find a source that indicates where SQL Server gets its instance name from during installation. I've never seen this issue before, and it is truly bizarre.

This is my last ditch effort to try to find a solution before I just wipe the 2 servers clean and start from scratch (which, obviously, I'd rather not do).


texts:
I'm writing many reporting queries for my current employer utilizing Oracle's With clause to allow myself to create simple steps, each of which is a data oriented transformation, that build upon each other to preform a complex task.

It was brought to my attention today that overuse of the with clause could have negative side effects on the Oracle server's resources.

Can anyone explain why over use of the oracle with clause may cause a server to crash? Or point me to some articles where I can research appropriate use cases? I started using the With clause heavily to add structure to my code and make it easier to understand, I hope with some informative responses here I can continue to use it efficiently.

Thanks!


texts: Index before or after bulk load using load infile?

texts:
I have a database with over 1B rows and two columns that are indexed (in addition to the PK).
Is it better to have the index pre-defined in the table before the load infile or better to index after the data has been loaded?

A couple of notes regarding data size and system:

System is Linux w/ 8 cores and 32GB memory (currently maxed out
unless I move to new HW)
DB is 1B rows that in raw data size is 150GB data.
Database is MyISAM and is mainly read-only after it's loaded.


texts: Surrogate key / Primary key: Better to use an existing unique data field or create a key field?

texts:
I am not sure if this question has been asked or not. At least I couldn't find it.

I am curious about a primary key in terms of efficiency with data searching and retrieval.

This is a hypothetical example...
I have a list of students with unique StudentID (say 10 digits long, given that the school will never have that many students) and StudentName (which is unique too).

Would it be better to use StudentID as primary key or create a new field for primary key (may be 6 - 8 digits or composite of characters and numbers. i.e. ATC1002)?

If I use StudentName as the primary key, will I see any retrieval performance degradation? If so, approximately how much in general? Are there any other factors which will have an impact on the selection of the primary field?


texts: Is using CRUD stored procedures against a view with NOLOCK bad?

texts:
(I am crossposting this from SO on the suggestion of a commenter)

Our DBAs have created a pattern where our database layer is exposed to EF via views and CRUD stored procedures. The CRUD works against the view. All the views have the NOLOCK hint. From what I understand, NOLOCK is a dirty read, and that makes me nervous. Our databases are not high volume, but it seems like blanket NOLOCK is not very scalable while maintaining data integrity. I get that the decoupling is a good idea, but the problem there is we don't. Our externally exposed objects look just like our views which map 1 to 1 with our tables.

"If we want to change the underlying data model, we can." ... but we don't. I won't touch on what a PITA this all is from a VS/EF tooling viewpoint.

Is NOLOCK used in this situation bad? Since our database looks exactly like our class library, I think it makes sense to just get rid of the whole view/stored procedure layer and hit the DB direct from EF, does it?


texts: Automatic replication on slave node after service restart

texts:
I am new to mysql administration. we have master - slave replication environment.

I added a 2nd slave node to existing env [master-slave] and replication is successful. Whenever I restart the mysqld service on 2nd slave node, replication is not starting automatically on the slave. That's what I am trying to accomplish.

I am executing these queries to re-sync again with master node on 2nd slave

2nd slave mysql> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
2nd slave mysql>mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 0     |
+---------------+-------+
1 row in set (0.00 sec)

2nd slave mysql> SET GLOBAL server_id=3;
Query OK, 0 rows affected (0.00 sec)

** I did specify in /etc/my.cnf file, server-id as 3, but its not taking it after restarting the mysqld service.

2nd slave mysql> show slave status\G
*************************** 1. row ***************************
           Slave_IO_State:
              Master_Host: masterhost
              Master_User: mysql
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000012
      Read_Master_Log_Pos: 17203734
           Relay_Log_File: mysqld-relay-bin.000002
            Relay_Log_Pos: 616388
    Relay_Master_Log_File: mysql-bin.000012
         Slave_IO_Running: No
        Slave_SQL_Running: No
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 17203734
          Relay_Log_Space: 616669
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:
1 row in set (0.00 sec)

** from the above command I take master_log_file and MASTER_log_pos to resync.

mysql> CHANGE MASTER TO MASTER_HOST='masterhost', MASTER_USER='mysql', MASTER_PASSWORD='!adbcd', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=17203734;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

and it starts replicating again from the master. How can I avoid this manual steps in future and replicate automatically ?

Here is my /etc/my.cnf file.

[root@2nd slave ~]# more /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#max_connections
max_connections=400
symbolic-links=0
server-id=3
#skip-slave-start
log-bin=/data/mysql/mysql-bin
#binlog_format=ROW
max_user_connections = 400
key_buffer = 64M
max_allowed_packet = 32M
sort_buffer_size = 64M
net_buffer_length = 64K
read_buffer_size = 16M
read_rnd_buffer_size = 16M
thread_stack = 192K
thread_cache_size = 8
query_cache_limit = 10M
query_cache_size = 64M
innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 100M
innodb_log_file_size = 10M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 16
[root@2nd slave ~]#

Thanks,


texts: Does Detach/Attach or Offline/Online Clear the Buffer Cache for a Particular Database?

texts:
A buddy of mine told me today that instead of bouncing SQL Server, I could simply detach and then re-attach a database and this action would clear the given database's pages and plans from cache. I disagreed and provide my evidence below. If you disagree with me or have a better rebuttal, than by all means supply it.

I am using AdventureWorks2012 on this version of SQL Server:

SELECT @@VERSION;
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

Having loaded the database, I run the following query:

Firstly, run Jonathan K's AW fattening script found here:

AW Get Fat

---------------------------
-- Step 1: Bpool Stuff?
---------------------------
USE [AdventureWorks2012];
GO

SELECT
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [buffer size(MB)]
   , COUNT(*) AS [buffer_count]
FROM
     sys.allocation_units AS a
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
WHERE
     b.database_id = DB_ID()
     AND p.object_id > 100
GROUP BY
     p.object_id
   , p.index_id
ORDER BY
     buffer_count DESC;

The result is shown here:

Detach and re-attach the database and then re-run the query.

---------------------------
-- Step 2: Detach/Attach
---------------------------
-- Detach
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2012'
GO

-- Attach
USE [master];
GO

CREATE DATABASE [AdventureWorks2012] ON
(
    FILENAME = N'C:\sql server\files\AdventureWorks2012_Data.mdf'
)
    ,
(
    FILENAME = N'C:\sql server\files\AdventureWorks2012_Log.ldf'
)
 FOR ATTACH;
GO

What is in the bpool now?

---------------------------
-- Step 3: Bpool Stuff?
---------------------------
USE [AdventureWorks2012];
GO

SELECT
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [buffer size(MB)]
   , COUNT(*) AS [buffer_count]
FROM
     sys.allocation_units AS a
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
WHERE
     b.database_id = DB_ID()
     AND p.object_id > 100
GROUP BY
     p.object_id
   , p.index_id
ORDER BY
     buffer_count DESC;

And the result:

Are all the reads logical at this point?

--------------------------------
-- Step 4: Logical Reads Only?
--------------------------------
USE [AdventureWorks2012];
GO

SET STATISTICS IO ON;
    SELECT * FROM DatabaseLog;
    GO
SET STATISTICS IO OFF;

/*
(1597 row(s) affected)
Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads 0, read-ahead reads 768, lob logical reads 94, lob physical reads 4, lob read-ahead reads 24.
*/

And we can see that the buffer pool was not totally blown away by the detach/attach. Seems like my buddy was wrong. Does anyone disagree or have a better argument?

Another option is to offline and then online the database. Let us try that.

--------------------------------
-- Step 5: Offline/Online?
--------------------------------
ALTER DATABASE [AdventureWorks2012] SET OFFLINE;
GO
ALTER DATABASE [AdventureWorks2012] SET ONLINE;
GO

---------------------------
-- Step 6: Bpool Stuff?
---------------------------
USE [AdventureWorks2012];
GO

SELECT
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [buffer size(MB)]
   , COUNT(*) AS [buffer_count]
FROM
     sys.allocation_units AS a
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
WHERE
     b.database_id = DB_ID()
     AND p.object_id > 100
GROUP BY
     p.object_id
   , p.index_id
ORDER BY
     buffer_count DESC;

It appears that the offline/online operation worked a lot better.


texts: "Deploy Database to SQL Azure" Task missing

texts:
I recently installed SQL Server 2014 Express on a workstation I have and am trying to deploy to SQL Azure. I've done this before on a previous workstation using SQL Server 2012 Express by doing the following:

Right click the database > Tasks > "Deploy Database to SQL Azure"

On SQL Server 2014, the option is completely missing and is instead replaced a new option: "Deploy Database to a Windows Azure VM"

I can't figure out why the option for deploying to SQL Azure is missing. Is there something that needs to be installed separately now for SQL Server 2014? From what I've read online, both options should be present for 2014 editions. Did Microsoft remove this option for SQL Server 2014 Express?


texts:
Let me preface this question by stating that my experience with DBA is limited to an introductory undergraduate university course, and that I'm just getting started with PostgreSQL.

I would like to programmatically import data from an incredibly large (~4GB) flat CSV (I know, not the best format, but it's what I have to work with) into a PostgreSQL database on a regular basis. I believe I can use COPY FROM to accomplish this task. What occurs when a CSV line that matches or conflicts with an existing record is encountered? Does it update the record, ignore it, or throw an error? Is this behavior changeable?

The documentation states, in part:

  COPY FROM will invoke any triggers and check constraints on the
  destination table. However, it will not invoke rules.

My interpretation of this statement is that rather than updating an existing record, this statement would treat the CSV entry as a new record, which would in turn violate constraints that may be placed on the columns. Is this correct? If so, it seems like my only course of action would be to issue DELETE on the tables before importing the data each time. That strikes me as highly inefficient.

UPDATE

A sample of the data-set that I'm working with may be found here. I would like to import and normalize the data from this highly de-normalized source. As you can see, each license record may be duplicated for each related antenna that may exist.


texts: How do I control order/arrangement of certain columns in a resultset?

texts:
Is it possible to control the sort order ourselves in a result set? So if I want to give it a custom order of appearance (instead of the ASC or DESC order), how can I go about doing it? I have read some answers on Stackoverflow which talk about using CASE statements in ORDER BY, but I do not understand how that works and how to make it work to get what I want.

In the above column for example, I want, say, Mitchell to be last, Carson to be first, Reiter to be fifth, etc.


texts:
I need to know some internals about the copy command in PostgreSQL 10.
In  particular I need to know if the copy from into a remote database makes batch insertions. If so, what is  the default insertion batch size?

And the insertion will be performed with a bunch of insert queries with n records?( n is the default batch size)


texts: Meta Destination Owner Differs From Actual Owner

texts:
How can the replication meta data table differ from the properties of the publication through the interface; for instance ...

SELECT destination_owner
FROM distribution..MSarticles

Yet, when selecting the article properties from the interface of the properties for the publication, it has a different schema.  So in case one, the destination_owner is One while in actual properties the owner is Two.

What I see is if the destination_owner from MSarticles is NULL, it means that the owner is the same as the source_owner.  Yet, I'm seeing a situation where the two are reporting different destination owners.


texts:
I've tried writting this update statement every possible way I can think of but I either wind up producing invalid results or run into a syntax barrier.

I have two table variables:

DECLARE
   @Measurements TABLE(Precidence int, -- the relative scale of a measurement
                       Measurement varchar(max), -- Grams, Kilograms, Liters, etc
                       MeasurementType varchar(max)); -- Weight, Volume, etc

Example:

Precidence | Measurement | MeasurementType
     2     |     G       |   Weight
     1     |     KG      |   Weight
     1     |     GAL     |   Volume
     2     |     L       |   Volume
     3     |     ML      |   Volume

DECLARE
   @Items TABLE(ItemType varchar(max),
                Quantity float,
                Measurement varchar(max),
                ToMeasurement varchar(max));

The @Items table can contain multiple measurements for the same ItemType. For each ItemType I need to identify the largest measurement, taking into account incompatible measurement types and update the ToMeasurement. The ultimate goal is to convert the Quantity of each ItemType to the largest measurement present in the @Items table so items of the same ItemType can be summed. I've already written conversion function and the sum operation.

Given the following table input:

ItemType | Quantity | Measurement | ToMeasurement
Widget   |    1     |    G        |    NULL
Widget   |    1     |    KG       |    NULL
Widget   |    1     |    ML       |    NULL
Widget   |    1     |    L        |    NULL

Should be updated to:

ItemType | Quantity | Measurement | ToMeasurement
Widget   |    1     |    G        |    KG
Widget   |    1     |    KG       |    KG
Widget   |    1     |    ML       |    L
Widget   |    1     |    L        |    L

I've rewritten the update multiple times and each time I come up short. At one point I had subqueries going four levels deep. I felt like I was getting close but it was getting so complex I couldn't see the forest for the trees. My most recent attempt is simpler but once again produces incorrect results:

UPDATE A
SET A.ToMeasurement = E.Measurement
FROM @Items A
     JOIN(SELECT C.ItemType,
                 D.Measurement
          FROM @Measurements B
               JOIN @Items C
               ON C.Measurement = B.Measurement
               JOIN @Measurements D
               ON D.MeasurementType = B.MeasurementType)E
     ON E.ItemType = A.ItemType;

I know I'm not even using the Precidence column, which is one of the reasons it is failing to produce the results I'm looking for.

Notes

Here's a query that produces the result I'm looking for (I think) but I'm still not sure how to turn it into an update statement:

SELECT A.ItemType,
       A.Quantity,
       A.Measurement,
       (SELECT TOP 1 M.Measurement FROM @Measurements M
       JOIN @Items C ON C.Measurement = M.Measurement
       WHERE M.MeasurementType = B.MeasurementType
       AND  C.ItemType = A.ItemType
       ORDER BY Precidence)ToMeasurement
FROM @Items A
     JOIN @Measurements B
     ON A.Measurement = B.Measurement;