MySQL general query log performance effects

Instead of using the general log, how about going with a query profiler? In fact, you could do query profiling without using any of MySQL's log files and while the queries are still running.
You must use mk-query-digest or pt-query-digest and poll the processlist.
I learned how to use mk-query-digest from this youtube video as a replacement for the slow log: http://www.youtube.com/watch?v=GXwg1fiUF68&feature=colike
Here is the script I wrote to run the query digest program
#!/bin/sh

RUNFILE=/tmp/QueriesAreBeingDigested.txt
if [ -f ${RUNFILE} ] ; then exit ; fi

MKDQ=/usr/local/sbin/mk-query-digest
RUNTIME=${1}
COPIES_TO_KEEP=${2}
DBVIP=${3}

WHICH=/usr/bin/which
DATE=`${WHICH} date`
ECHO=`${WHICH} echo`
HEAD=`${WHICH} head`
TAIL=`${WHICH} tail`
AWK=`${WHICH} awk`
SED=`${WHICH} sed`
CAT=`${WHICH} cat`
WC=`${WHICH} wc`
RM=`${WHICH} rm | ${TAIL} -1 | ${AWK} '{print $1}'`
LS=`${WHICH} ls | ${TAIL} -1 | ${AWK} '{print $1}'`

HAS_THE_DBVIP=`/sbin/ip addr show | grep "scope global secondary" | grep -c "${DBVIP}"`
if [ ${HAS_THE_DBVIP} -eq 1 ] ; then exit ; fi

DT=`${DATE} +"%Y%m%d_%H%M%S"`
UNIQUETAG=`${ECHO} ${SSH_CLIENT}_${SSH_CONNECTION}_${DT} | ${SED} 's/\./ /g' | ${SED} 's/ //g'`

cd /root/QueryDigest
OUTFILE=QP_${DT}.txt
HOSTADDR=${DBVIP}
${MKDQ} --processlist h=${HOSTADDR},u=queryprofiler,p=queryprofiler --run-time=${RUNTIME} > ${OUTFILE}

#
# Rotate out Old Copies
#

QPFILES=QPFiles.txt
QPFILES2ZAP=QPFiles2Zap.txt
${LS} QP_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9].txt > ${QPFILES}

LINECOUNT=`${WC} -l < ${QPFILES}`
if [ ${LINECOUNT} -gt ${COPIES_TO_KEEP} ]
then
        (( DIFF = LINECOUNT - COPIES_TO_KEEP ))
        ${HEAD} -${DIFF} < ${QPFILES} > ${QPFILES2ZAP}
        for QPFILETOZAP in `${CAT} ${QPFILES2ZAP}`
        do
                ${RM} ${QPFILETOZAP}
        done
fi

rm -f ${QPFILES2ZAP}
rm -f ${QPFILES}
rm -f ${RUNFILE}

Make sure

you a user called queryprofiler whose password is queryprofiler and who only has the PROCESS privilege
you put */20 * * * * /root/QueryDigest/ExecQueryDigest.sh 1190s 144 10.64.95.141  in the crontab to run every 20 minutes (Each profile is 20 min less 10 seconds, Keeps the last 144 copies, and only runs if specfifc DBVIP is present [Alter script to bypass checking for DBVIPs])

The output produces a file with the 20 worst running queries based on the number of times the query was called X avg sec per query.
Here is the sample output of the query profiling summary of mk-query-digest
# Rank Query ID           Response time    Calls   R/Call     Item
# ==== ================== ================ ======= ========== ====
#    1 0x812D15015AD29D33   336.3867 68.5%     910   0.369656 SELECT mt_entry mt_placement mt_category
#    2 0x99E13015BFF1E75E    25.3594  5.2%     210   0.120759 SELECT mt_entry mt_objecttag
#    3 0x5E994008E9543B29    16.1608  3.3%      46   0.351321 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
#    4 0x84DD09F0FC444677    13.3070  2.7%      23   0.578567 SELECT mt_entry
#    5 0x377E0D0898266FDD    12.0870  2.5%     116   0.104199 SELECT polls_pollquestion mt_category
#    6 0x440EBDBCEDB88725    11.5159  2.3%      21   0.548376 SELECT mt_entry
#    7 0x1DC2DFD6B658021F    10.3653  2.1%      54   0.191949 SELECT mt_entry mt_placement mt_category
#    8 0x6C6318E56E149036     8.8294  1.8%      44   0.200667 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
#    9 0x392F6DA628C7FEBD     8.5243  1.7%       9   0.947143 SELECT mt_entry mt_objecttag
#   10 0x7DD2B294CFF96961     7.3753  1.5%      70   0.105362 SELECT polls_pollresponse
#   11 0x9B9092194D3910E6     5.8124  1.2%      57   0.101973 SELECT content_specialitem content_basecontentitem advertising_product organizations_neworg content_basecontentitem_item_attributes
#   12 0xA909BF76E7051792     5.6005  1.1%      55   0.101828 SELECT mt_entry mt_objecttag mt_tag
#   13 0xEBE07AC48DB8923E     5.5195  1.1%      54   0.102213 SELECT rssfeeds_contentfeeditem
#   14 0x3E52CF0261A7C3FF     4.4676  0.9%      44   0.101536 SELECT schedule_occurrence schedule_occurrence.start
#   15 0x9D0BCD3F6731195B     4.2804  0.9%      41   0.104401 SELECT mt_entry mt_placement mt_category
#   16 0x7961BD4C76277EB7     4.0143  0.8%      18   0.223014 INSERT UNION UPDATE UNION mt_session
#   17 0xD2F486BA41E7A623     3.1448  0.6%      21   0.149754 SELECT mt_entry mt_placement mt_category mt_objecttag mt_tag
#   18 0x3B9686D98BB8E054     2.9577  0.6%      11   0.268885 SELECT mt_entry mt_objecttag mt_tag
#   19 0xBB2443BF48638319     2.7239  0.6%       9   0.302660 SELECT rssfeeds_contentfeeditem
#   20 0x3D533D57D8B466CC     2.4209  0.5%      15   0.161391 SELECT mt_entry mt_placement mt_category

Above this output are histograms of these 20 top worst-performing queries
Example of the first entry's histogram
# Query 1: 0.77 QPS, 0.28x concurrency, ID 0x812D15015AD29D33 at byte 0 __
# This item is included in the report because it matches --limit.
#              pct   total     min     max     avg     95%  stddev  median
# Count         36     910
# Exec time     58    336s   101ms      2s   370ms   992ms   230ms   393ms
# Lock time      0       0       0       0       0       0       0       0
# Users                  1      mt
# Hosts                905 10.64.95.74:54707 (2), 10.64.95.74:56133 (2), 10.64.95.80:33862 (2)... 901 more
# Databases              1     mt1
# Time range 1321642802 to 1321643988
# bytes          1   1.11M   1.22k   1.41k   1.25k   1.26k   25.66   1.20k
# id            36   9.87G  11.10M  11.11M  11.11M  10.76M    0.12  10.76M
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ###
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `mt1` LIKE 'mt_entry'\G
#    SHOW CREATE TABLE `mt1`.`mt_entry`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'mt_placement'\G
#    SHOW CREATE TABLE `mt1`.`mt_placement`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'mt_category'\G
#    SHOW CREATE TABLE `mt1`.`mt_category`\G
# EXPLAIN
SELECT `mt_entry`.`entry_id`, `mt_entry`.`entry_allow_comments`, `mt_entry`.`entry_allow_pings`, `mt_entry`.`entry_atom_id`, `mt_entry`.`entry_author_id`, `mt_entry`.`entry_authored_on`, `mt_entry`.`entry_basename`, `mt_entry`.`entry_blog_id`, `mt_entry`.`entry_category_id`, `mt_entry`.`entry_class`, `mt_entry`.`entry_comment_count`, `mt_entry`.`entry_convert_breaks`, `mt_entry`.`entry_created_by`, `mt_entry`.`entry_created_on`, `mt_entry`.`entry_excerpt`, `mt_entry`.`entry_keywords`, `mt_entry`.`entry_modified_by`, `mt_entry`.`entry_modified_on`, `mt_entry`.`entry_ping_count`, `mt_entry`.`entry_pinged_urls`, `mt_entry`.`entry_status`, `mt_entry`.`entry_tangent_cache`, `mt_entry`.`entry_template_id`, `mt_entry`.`entry_text`, `mt_entry`.`entry_text_more`, `mt_entry`.`entry_title`, `mt_entry`.`entry_to_ping_urls`, `mt_entry`.`entry_week_number` FROM `mt_entry` INNER JOIN `mt_placement` ON (`mt_entry`.`entry_id` = `mt_placement`.`placement_entry_id`) INNER JOIN `mt_category` ON (`mt_placement`.`placement_category_id` = `mt_category`.`category_id`) WHERE (`mt_entry`.`entry_status` = 2  AND `mt_category`.`category_basename` IN ('business_review' /*... omitted 3 items ...*/ ) AND NOT (`mt_entry`.`entry_id` IN (53441))) ORDER BY `mt_entry`.`entry_authored_on` DESC LIMIT 4\G

There is no performance impact doing this because one DB Connection is maintained to poll the processlist for the duration you specify and hundreds of queries against the processlist are totally lightweight within the confines of a single DB Connection. In light of this, you wll not need a NFS share or any hardware considerations for query performance and analysis.
Give it a Try !!!
UPDATE
mk-query-digest can use either the processlist (via live DB Connection) or tcpdump (via pipe). Here are the options:
--processlist
   --processlist
       type: DSN
       Poll this DSNâs processlist for queries, with "--interval" sleep between.
       If the connection fails, mk-query-digest tries to reopen it once
       per second. See also "--mirror".
   --interval
       type: float; default: .1
       How frequently to poll the processlist, in seconds.

--tcpdump
       tcpdump
           mk-query-digest does not actually watch the network (i.e. it
           does NOT "sniff packets").  Instead, itâs just parsing the out-
           put of tcpdump.  You are responsible for generating this out-
           put; mk-query-digest does not do it for you.  Then you send
           this to mk-query-digest as you would any log file: as files on
           the command line or to STDIN.

           The parser expects the input to be formatted with the following
           options: "-x -n -q -tttt".  For example, if you want to capture
           output from your local machine, you can do something like

             tcpdump -i eth0 port 3306 -s 65535 -c 1000 -x -n -q -tttt \
               â mk-query-digest --type tcpdump

           The other tcpdump parameters, such as -s, -c, and -i, are up to
           you.  Just make sure the output looks like this:

             2009-04-12 09:50:16.804849 IP 127.0.0.1.42167 > 127.0.0.1.3306: tcp 37
                 0x0000:  4508 0059 6eb2 4000 4006 cde2 7f00 0001
                 0x0010:  ....

           Remember tcpdump has a handy -c option to stop after it cap-
           tures some number of packets!  Thatâs very useful for testing
           your tcpdump command.

After 8.5 years, I decided to update this post with a version of my wrapper script using pt-query-digest instead of mk-query-digest:
#!/bin/bash

#
# qdlive : Wrapper Around pt-query-digest
# BASE_FOLDER     : Target Folder for This Wrapper's Output
# PT_QUERY_DIGEST : Absolute path to the pt-query-digest script
# MYCNF           : Absolue path to my.cnf with User Credentials to run pt-query-digest and mysqladmin
# QD_FOLDER       : Folder Where pt-query-digest Output are Stored
#
BASE_FOLDER=${HOME}/qdlive
MYCNF=${BASE_FOLDER}/my.cnf
ERRLOG=${BASE_FOLDER}/err.log
PT_QUERY_DIGEST=/usr/bin/pt-query-digest
QD_FOLDER=${BASE_FOLDER}/reports
mkdir -p ${QD_FOLDER}
cd ${BASE_FOLDER}

#
# 1st Parameter is the Number of Minutes for pt-query-digest to run
# 2nd Parameter is the Number of Copies of the pt-query-digest Output to Keep
#
[   "${1}" == "" ] && exit
[   "${2}" == "" ] && exit
[ ! "${3}" == "" ] && exit

OK1=`echo "${1}" | grep -c "^[1-9]$"`
OK2=`echo "${1}" | grep -c "^[1-9][0-9]$"`
OK3=`echo "${1}" | grep -c "^[1-9][0-9][0-9]$"`
(( OK = OK1 + OK2 + OK3 ))
if [ ${OK} -eq 0 ]
then
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "${DT} : Invalid Minutes (${1})" >> ${ERRLOG}
        exit
fi

OK=1
[ ${1} -lt  2 ] && OK=0
[ ${1} -gt 60 ] && OK=0
if [ ${OK} -eq 0 ]
then
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "${DT} : Invalid Minutes (${1})" >> ${ERRLOG}
        exit
fi
RUNTIME_MINUTES=${1}
(( RUNTIME_SECONDS = RUNTIME_MINUTES * 60 ))

OK1=`echo "${2}" | grep -c "^[1-9]$"`
OK2=`echo "${2}" | grep -c "^[1-9][0-9]$"`
OK3=`echo "${2}" | grep -c "^[1-9][0-9][0-9]$"`
(( OK = OK1 + OK2 + OK3 ))
if [ ${OK} -eq 0 ]
then
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "${DT} : Invalid Copies (${2})" >> ${ERRLOG}
        exit
fi
OK=1
[ ${2} -lt   7 ] && OK=0
[ ${2} -gt 300 ] && OK=0
if [ ${OK} -eq 0 ]
then
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "${DT} : Invalid Copies (${2})" >> ${ERRLOG}
        exit
fi
COPIES_TO_KEEP=${2}

#
# Parse my.cnf
#
# [mysql]
# host = ....
# user = ....
# password = ...
#
MYSQL_HOST=localhost
MYSQL_HOST=`grep ^host     ${MYCNF} | awk '{print $3}'`
MYSQL_USER=`grep ^user     ${MYCNF} | awk '{print $3}'`
MYSQL_PASS=`grep ^password ${MYCNF} | awk '{print $3}'`
MYSQL_PORT=3306

MYSQL_AUTH="--defaults-file=${MYCNF}"
PTOOL_AUTH="-F ${MYCNF}"

#
# Make Sure mysqld is running
#
MYSQL_RUNNING=`mysqladmin ${MYSQL_AUTH} ping 2>&1 | grep -c "mysqld is alive"`
if [ ${MYSQL_RUNNING} -eq 0 ]
then
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "${DT} : mysqld Not Running" >> ${ERRLOG}
        exit
fi

#
# Execute Query Digest
#
DT=`date +"%Y%m%d_%a_%H%M%S"`
QD_OUTPUT=${QD_FOLDER}/qd_${DT}_${RUNTIME_MINUTES}m.rpt
${PT_QUERY_DIGEST} --processlist ${MYSQL_HOST} ${PTOOL_AUTH} --run-time=${RUNTIME_SECONDS}s >${QD_OUTPUT} 2>&1

#
# Rotate Old Reports
#
COPIES_ON_HAND=`ls -l | grep -c rpt$`
if [ ${COPIES_ON_HAND} -gt ${COPIES_TO_KEEP} ]
then
        (( COPIES_TO_ZAP = COPIES_ON_HAND - COPIES_TO_KEEP ))
        FILELIST=""
        SPC=""
        for FIL in `ls *.txt | head -${COPIES_TO_ZAP}`
        do
                FILELIST="${FILELIST}${SPC}${FIL}"
                SPC=" "
        done
        for FIL in ${FILELIST} ; do rm -f ${FIL} ; done
fi


Is it possible grant a PRIVATE Synonym from an the Table Owner schema to an User schema?

It can be done as SYSTEM (or a user with DBA privileges) or if 'create any synonym' is granted to the user.

SQL> create user test1 identified by test1;
User created.

SQL> create user test2 identified by test2;
User created.

SQL> grant create session to test2;
Grant succeeded.

SQL> create table test1.xtable (x number);
Table created.

SQL> create synonym test2.xtable for test1.xtable;
Synonym created.

SQL> drop synonym test2.xtable;
Synonym dropped.

SQL> conn test2/test2
Connected.
SQL> create synonym xtable for test1.xtable;
create synonym xtable for test1.xtable
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>  conn system/manager
Connected.

SQL> grant create any synonym to test2;
Grant succeeded.

SQL>  conn test2/test2
Connected.
SQL> create synonym xtable for test1.xtable;
Synonym created.


What permissions are needed to see view definitions via a query?

Simply granting VIEW DEFINITION and SELECT permissions on INFORMATION_SCHEMA and sys schema wont give you rights to see the definition of the view.

Permissions that you added will provide you information about all objects in sys schema and information_schema.

I suppose you want to see the view definition which are created in some other schema, in which case you would have to provide the user withVIEW DEFINITION to that particular schema.

Such as : GRANT VIEW DEFINITION ON SCHEMA::SchemaWhereViewsAreCreated TO [YourUser]


What permissions should the account for MS SQL Server Agent have?

You also need to verify that the AD account will have access to the parts of the file system that it might need.  We ran into this awhile ago, and I don't remember which jib had an issue.  Could have been backup, or something else.

If you're using SQL to connect to remote SQL Servers, then it depends on if you are using linked servers.  Linked servers use the SQL to connect to the remote server.  If you open a connection directly on the remote server, you'll need a Windows (probably) or SQL authentication login on that server


Is it possible to limit concurrent aggregations in Mongo?

Answer is simple, NO. Not in mongodb. But you can always do it in your own application. Just create "lock" collection where applications mark they will to do aggregation. Ticket system for queuing.


MySQL will chose the index based on few factors. One of them is the cardinality of the indexed field, and other one in the number of rows to be scanned (on average). In your first example, MySQL decided that it will read less rows if it is used the index on "answer_id".
If you provides the stats of your table, it will be much clearer.
Example of data snapshot when this happens:

user_id answer_id order
48      1         15
48      2         17
48      3         19
48      4         13

Here, if MySQL will see that user (User_id, answer_id) will scan 4 rows, while using (exam_id) will scan only 1 row.


MySQL security hasn't been every a particular strong point of the server software. That is why most people recommend not expose the MySQL port outside the internal network/firewall. Like any other software, there has been records of vulnerabilities found.

Having said that, MySQL has a basic protection against port-scan attacks, as it maintains internally a list of blocked hosts and it adds a host to this list when more than 10/100 TCP/IP connections are interrupted (max_connect_errors). Please note that this is not a real brute-force attack protection, as both a successful and an unsuccessful complete connection will reset the counter and localhost is whitelisted.


In order to have it go to one file you have two options, and I think the first will get a little better performance than the second.

First you can simply add a parameter to the Export-Csv to append to the file. As long as the columns are consistent (the same) it should work with out issue.

$dt | Export-Csv music.csv -Encoding ascii -NoTypeInformation -Append

Second, pull the contents of the foreach loop into a variable and then output it all at one time to the Export-Csv. This will will mean it will pull all of the data into memory and then output it all to the file. It depends on how much data you are pulling out but this may add on some time for the execution of the script.

$results =
foreach ($svr in get-content "demo.txt")
{
  $con = "server=MC1-PQ10X.RF.LILLY.COM\SQL01;database=mylilly_WSS_Content_INCTSK0014840;Integrated Security=sspi"

  $cmd = "SELECT
Docs.DirName + '/' + Docs.LeafName AS 'Item Name',
DocVersions.UIVersion,
(DocVersions.UIVersion/512) as Version_Label, DocVersions.Level, DocVersions.TimeCreated
FROM DocVersions FULL OUTER JOIN Docs ON Docs.Id = DocVersions.Id
--   INNER JOIN Webs On Docs.WebId = Webs.Id
--INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE (DirName LIKE '%globalcontentrepository%')
AND (IsCurrentVersion = '0')
AND (DocVersions.Id IN ('$svr'))"

  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

  $dt = new-object System.Data.DataTable

  $da.fill($dt) |out-null

  $dt

 }

$results | Export-Csv music.csv -Encoding ascii -NoTypeInformation


10 TB database backup / restore Question

This is simple and quick to test.

Take that 2TB backup file, copy it to Azure using AzCopy.  Provision a SQL Server using the "Free License: SQL Server 2017 Developer on Windows Server 2016" image configured with 12-15 TB of SSD storage.  Remote desktop to the SQL VM and restore the backup directly from Blob Storage, or copy it to a local disk and then restore it.

Just remember to shut down the VM when you're not testing, and destroy it and the disks when you're done to minimize the charges.


To answer your question, we'd have to know your hardware and how to interpret what iotop is reporting.  Not exactly on topic in this forum.  Taking a giant guess, assuming you're using something like ordinary SATA hardware, the answer would be No.  A single SATA drive is capable of something like 100 MB/s; you're seeing (on the first line) about 5% of that.  Before I'd credit the disk, I'd have to explain the other 95%.

In approaching Database performance problems, it's always a good idea to start with the DBMS itself, especially physical table design.  Work from the top down: DDL, DML, triggers, indexes, partitioning.  Once you're sure you have the DBMS's house in order, you can look at what the DBMS demands from the OS.  You can look at the depth of the I/O queues, I/O operation/second and how memory is being used.  More RAM is hardly ever harmful; a DBMS can use RAM to avoid I/O.

The reason to work this way is technical, not political.  Errors (or suboptimal choices) at the high levels can drive orders of magnitude in performance.  One index can mean the difference between O(n^2) and O(log n) performance, a matter hours versus seconds.  The further down you go, the harder it is to measure performance, and the more expensive to rectify.  Not to mention that any new machine capacity can quickly be absorbed by new bad SQL.


Conversion failed when converting the nvarchar value 'value' to int

Since #Proc_Concat and #Proc50 only have two columns in play here, PatientID and ProcedureCodes, one of them is throwing the error.

Guessing not ProcedureCodes as it's pipe-delimeted so must be a varchar.

That leaves PatientId.  Numeric data types can't handle the space character '1111111 11100', so that's the first place I'd look.

Another possibility is within your subquery, P1.PatientID = P2.LbPatientID, in case one is a varchar and one is an int, and implicit conversion won't allow the varchar value to be converted to an int to test the equality.

Good luck.


group by source_1 and user and count by source_2

Use pivot emulation:

SELECT SOURCE_NAME_1,
       user_nm,
       SUM(SOURCE_NAME_2 = 'NAME_1') NAME_1,
       SUM(SOURCE_NAME_2 = 'NAME_2') NAME_2,
       SUM(SOURCE_NAME_2 = 'NAME_3') NAME_3
FROM tbl_data
LEFT JOIN tbl_user ON ...
LEFT JOIN tbl_source_1 ON ...
LEFT JOIN tbl_source_2 ON ...
GROUP BY SOURCE_NAME_1, user_nm

If SOURCE_NAME_2 values list is not static, use stored procedure which pivot data, or (better) count data only, then pivot it on the client side using client report subsystem.


table organization with data that will never change

I'm not the one who downvoted your question, so I can't speak on behalf of that person, but my guess is because your question is a little abstract and wordy, and this site is usually for more direct and concretely worded questions, that was the downvoter's reasoning, because it's a little unclear exactly what your goals are / what you're asking. That being said, I agree it is annoying to be downvoted without explanation, so conversely you have my upvote, because your question is fair enough.
Anyways, what I can say is it sounds like the size of your data is of no concern here for PostgreSQL. A quick calculation shows that at the rate of new data you mentioned, you'd still be under half a billion records in a 10 year timespan. I wouldn't be even concerned about caching so much because your object is rather small (ergo a single row in the table isn't very wide and will be small).
If your Token object and TokenLiquidity object have a one-to-one relationship then it doesn't realistically matter too much if you normalize them into two tables or denormalized them into one, because you wouldn't have data redundancy, and the row / table wouldn't grow much in total size by combining them into one table, so there's no concern there either.
If the relationship from Token is one to many with TokenLiquidity then that's a different story because you'll end up repeating properties of the Token across multiple rows of the TokenLiquidity table, which can make management more difficult should one of those properties' values change.
The only thing I'd personal recommend, because it just makes sense structurally, is having a third separate table dedicated to storing the history of the LiquidityToken table and furthermore would specifically just store the Token0Amount, Token1Amount and Address (and maybe a DateTime based field to log the when) fields to minimize data redundancy / maximize normalization. This will ensure your LiquidityToken table is as lightweight as possible, and ergo making the joins to the Token table as efficient as possible too. The history table would only be joined to as needed, and if you had the DateTime field, for only as far back as needed.


PostgreSQL-Backup from windows hard drive to linux

There's no way to dump your Windows PostgreSQL database directory using Linux directly1.

You need to start up a virtual machine running Windows, then copy the database directory to that virtual machine. You can then pg_dump -Fc it, and pg_restore the dump to your new blank PostgreSQL install on the Linux machine.

You must make sure that if your PostgreSQL data directory was from 64-bit PostgreSQL you install a 64-bit Windows version and install 64-bit PostgreSQL on it.

If your PostgreSQL data dir is 32-bit it doesn't matter whether you install 32-bit or 64-bit Windows, but you must install 32-bit PostgreSQL.

Licensing restrictions mean that you can't just download a virtual machine image for Vagrant or whatever to run Windows. You will need a Windows install DVD or ISO image. It's possible to download these legally from Microsoft's digital distribution system if you don't already have a DVD or ISO. You can install them without a license key, but it'll disable its self after a few days, and it's not legal to use it without possessing a license.

For that reason, unless the database is really huge one of the best options is to sign up to Amazon EC2 (if you haven't already) and launch a Windows Server virtual machine on EC2. You can install PostgreSQL then copy your database to that virtual machine, run pg_dump, and copy the dump back to your computer. Then just shut down the VM. You can use EC2 for free if you use a micro instance.

1 It's possible you might be able to run PostgreSQL using WINE, but I suspect that'd be even more complicated, if it works at all, and probably not particularly trustworthy. So just use Windows.


Alter table - existing column as primary key

A TEXT column cannot be in an INDEX (a PRIMARY KEY is an index).  As Akina points out, uuids may as well be

uuid CHAR(36) CHARACTER SET ascii

Such is indexable.  Before changing away from TEXT, running your ALTER will give you

ERROR 1170 (42000): BLOB/TEXT column 'uuid' used in key specification without a key length

You could change the datatype, then do the ALTER.  Or if you are starting from scratch,

CREATE TABLE `cb_company` ( `uuid` CHARACTER SET ascii,
                        `num_employees` text,
                        ...
                        PRIMARY KEY(uuid)
                      ) ENGINE=InnoDB
                        DEFAULT CHARSET=utf8mb4
                        COLLATE=utf8mb4_0900_ai_ci;

It is generally better to use VARCHAR with realistic limits.

If num_employees is a number, use INT.

If announced_on is a date, consider using DATE if you ever plan to compare or manipulate as a date.

Be aware that you will have to provide the uuid value.  One way is

INSERT INTO cb_company (uuid, num_employees, ... )
     VALUES
     (UUID(), ...)

Then a problem is -- how to retrieve the value given a company name?  Perhaps you also want some other index.

Etc.


What's stored in data_dir/base of postgresql

This folder is where PostgreSQL keeps all the data you insert on your databases (see PostgreSQL Database File Layout). Each sub-directory is used by a database in you cluster, the files inside them are the actual data of the relations (tables, indexes, sequences, ...).

So, your answer is no! You cannot clean it out, and if you do so, you will lose your data and PostgreSQL server will stop with a panic.

Basically, only files inside the pg_log directory (if any) can be removed. A side note, do not, ever remove files inside pg_xlog, it is not useless as the name may suggest (you didn't ask for it, but I saw happening sometimes).

Tips to solve your problem:

There are some things you can do to use less space in your case, like:

Use another (not in RAM) tablespace for some tables or databases;
Run a VACUUM FULL. But notice that you will need some temporary space to do that, so you can't do it while you are really out of space;
Move pg_xlog to another location;
As you said it is a temporary database, try to remove some indexes that are not been used.


Yeah, that's going to be slow. Best would be to pre-chew the data by adding those JOIN fields as computed columns to MainTable. If you don't control that table, you can try rewriting your query with a CTE, though I'm not sure how much it will help:

WITH NewMainTable
AS (
    SELECT t.*
        , my_func(t.col1, 1) as MyJoinField1
        , = my_func(t.col1, 6) as MyJoinField6
    FROM MainTable t
    )
SELECT *
FROM NewMainTable nmt
    JOIN table1 t1
        ON nmt.MyJoinField1 = t1.col1
    JOIN table6 t6
        ON nmt.MyJoinField6 = t6.col1;


how to check who has killed the job which was running ?if logs are not generated

The error log should contain some information about what session killed a particular process.  Run the following statement to get the hostname and process id of the user the KILL command originated from.

EXEC xp_readerrorlog 0, 1, N'kill', NULL, NULL, NULL, N'DESC'

Note that if any time has gone by since the command was run, this may return stale results as session ids are reused.  Using the Host Process ID, you can then find the user:

select login_name, session_id, host_name, host_process_id
from sys.dm_exec_sessions where host_process_id = XXXX AND session_id = YY

Here's an example:

EXEC xp_readerrorlog 0, 1, N'kill', NULL, NULL, NULL, N'DESC'

LogDate                 ProcessInfo  Text
----------------------- ------------ ----------------------------------------------------------------------
2017-06-20 07:17:32.210 spid64       Process ID 62 was killed by hostname JOHNLAPPY, host process ID 7968.

(1 row(s) affected)

select login_name, session_id, host_name, host_process_id from sys.dm_exec_sessions where host_process_id = 7968 AND session_id = 62

login_name              session_id host_name  host_process_id
----------------------- ---------- ---------- ---------------
CONTOSO\john.eisbrener  62         JOHNLAPPY  7968

(1 row(s) affected)


How should I select a primary key in my use case

Since a device can have more than one IMEI number, that is obviously not the primary key of the devices. Is there no existing attribute, such as serial number, that can serve as unique identifier? If not, then you probably need a devices master table with one entry per device and a generated ID for the primary key. This value would then be used in the transaction table along with the IMEI number. Something like
Table: Device
   DeviceID  int serial primary key

Table: DeviceIMEI
   DeviceID  int FK references Device.DeviceID
   IMEI      character(16)  primary key,
   Status    Boolean

The IMEI is defined as globally unique so can be the PK of DeviceIMEI, even though it may not be the only IMEI for its DeviceID.
One million transactions over 5 hours is about 55 per second. A modern relational DBMS can handle that.


To answer your first question:

  Why is there a Stream Aggregate in the query when I dont have a group by. I am guess it has something to do with the join being merge and its doing a sort?

SQL Server is effectively rewriting your query. This query:

select Object1.Column1
FROM Object2 Object3 WITH (ROWLOCK)
INNER JOIN Object4 Object1 WITH (ROWLOCK) ON Object3.Column2 = Object1.Column1
WHERE Object3.Column2 in (SELECT Column2 FROM Object5)
or Object3.Column3 in (SELECT Column2 FROM Object5);

Can also be written like this:

select Object1.Column1
FROM
(
select Object3.Column2
FROM Object2 Object3 WITH (ROWLOCK)
WHERE Object3.Column2 in (SELECT Column2 FROM Object5)

UNION

select Object3.Column2
FROM Object2 Object3 WITH (ROWLOCK)
WHERE Object3.Column3 in (SELECT Column2 FROM Object5)
) Object3
INNER JOIN Object4 Object1 WITH (ROWLOCK) ON Object3.Column2 = Object1.Column1;

That's why you have two different access methods on Object2 in the query plan. The Merge Join (Concatenation) operation isn't actually a merge join. It's just implementing a UNION ALL and combining the results. The stream aggregate that you mentioned groups by Object3.Column2. This both removes duplicates from the Merge Join (Concatenation) and sorts the data so that it can be used in the following MERGE JOIN to Object4.

To answer your second question:

  Secondly and more importantly why does an estimate of 13 going
  into the stream aggregate comes out as an estimate of 24,595,900? This is causing the secondary problem of Object4 getting Clustered index scanned instead of a nested loop. I had to split the query into two queries instead of using a OR and the join turns into a nested loop and seeks into Object4.

It looks like a bug. After some searching I found an article by Paul White explaining the issue. The bug is reported to Connect if you want to vote for it or add yourself as affected.

In short, the cardinality estimate is 24595900 because 27328800 (table cardinality of Object2) * 0.9 = 24595900 after rounding. You're on SQL Server 2008 so the legacy CE calculation is used:

  A similar issue occurs with the pre-2014 cardinality estimator, but the final estimate is instead fixed at 90% of the estimated semi join input (for entertaining reasons related to a inversed fixed 10% predicate estimate that is too much of a diversion to get into).

I recommend reading through the entire article. The issue is a little difficult to summarize, but I will attempt to do so by quoting part of the text near the end:

  The cardinality estimator uses the Fixed Join calculator with 100% selectivity. As a consequence, the estimated output cardinality of the semi join is the same as its input, meaning all 113443 rows from the history table are expected to qualify.

  The exact nature of the bug is that the semi join selectivity computation misses any predicates positioned beyond a union all in the input tree. In the illustration below, the lack of predicates on the semi join itself is taken to mean every row will qualify; it ignores the effect of predicates below the concatenation (union all).

  A similar issue occurs with the pre-2014 cardinality estimator, but the final estimate is instead fixed at 90% of the estimated semi join input (for entertaining reasons related to a inversed fixed 10% predicate estimate that is too much of a diversion to get into).

Splitting up the query as you did should prevent the issue. You also might have some luck by rewriting the query using UNION (see Example 4 in the article).


Mysql - Replication of multiple database

You should remove the filters you placed in the my.cnf file, so you could replicate all the changes you did in all databases


How do I merge these two queries so that the result set is "combined" and consistent?

Being somewhat lazy the easiest way to handle it is with a FULL OUTER JOIN and subqueries.  I'm making the assumption this is SQL Server btw.

SELECT ISNULL(x.cnum, y.cnum) cnum, ISNULL(x.name, y.name) name,
    x.sums, y.assets
(
    SELECT cf.cnum as cnum, rc.name as name, SUM(rs.dh_simple + rs.ha_simple) as sums
    FROM retirement_survey as rs
    INNER JOIN currentfunds as cf
    ON rs.fundid = cf.fundid
    INNER JOIN research..complex rc
    ON cf.cnum = rc.cnum
    WHERE rs.date  = '9/30/2016'
    GROUP BY rc.name, cf.cnum
    HAVING SUM(rs.dh_simple + rs.ha_simple) > 0
) x
FULL OUTER JOIN
(
    SELECT r.cnum as cnum, c.name as name, r.assets as assets
    FROM research..complex c, research..retirement_simple_ira r
    where r.date = '9/30/2016'
    and c.cnum = r.cnum
) y
    ON x.cnum = r.cnum
ORDER BY ISNULL(x.cnum, y.cnum)


Cassandra security - private IPs with some VPN solution or public IPs and open ports?

If you care about your data, do not EVER put a database server on a public network. There is no preventing attacks on public ports. Databases are secured from most direct attacks by placing them behind firewalls, and using web servers and application servers (also protected by firewalls and load balancers) to reverse proxy user requests for information. No one should ever connect directly to a database or its server directly over the Internet. A database server or cluster, directly exposed on a public network, is begging to be hacked and its contents stolen.

https://www.vpnmentor.com/blog/how-to-secure-website-database/
https://www.esecurityplanet.com/network-security/6-database-security-best-practices.html
https://www.slideshare.net/DataStax/securing-cassandra-for-compliance


Backing up file groups with restore to earlier backup

What is the backup and restore method?
It is called Piece Meal Restore and unrestored filegroups can be restored at a later time.

Is it possible to restore only the lamb filegroup?
Yes it is possible to restore only the lamb filegroup.

Below will show you - how you can do it.

Create a database called "FGTest"

create database [FGTest] on primary (
name = N'FGTest'
,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest.mdf'
,SIZE = 3072 KB
,FILEGROWTH = 1024 KB
)
,FILEGROUP [lamb] (
name = N'lamb'
,-- fileGroup1
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_2.ndf'
,SIZE = 3072 KB
,FILEGROWTH = 1024 KB
)
,FILEGROUP [lamb1] (
name = N'lamb1'
,-- fileGroup2
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_3.ndf'
,SIZE = 3072 KB
,FILEGROWTH = 1024 KB
) LOG on (
name = N'FGTest_log'
,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_log.ldf'
,SIZE = 1024 KB
,FILEGROWTH = 10 %
)

go
Now create tables on different file groups - lamb and lamb1

create table TAB1 (
TAB1_ID int IDENTITY(1, 1)
,TAB1_NAME varchar(100)
,constraint PK_TAB1 primary key (TAB1_ID)
) on lamb -- Filegroup we created.
go

create table TAB1_lamb1 (
TAB1_ID int IDENTITY(1, 1)
,TAB1_NAME varchar(100)
,constraint PK_TAB1_lamb1 primary key (TAB1_ID)
) on lamb1 -- 2nd Filegroup we created.
go

Take a Base backup

-- Take a base backup
BACKUP DATABASE [FGTest] to
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_basebackup.bak'
with init, stats = 10
go

Now insert some values .... You can load a CSV at this point

INSERT INTO FGTest..TAB1(TAB1_NAME)
select ('TAB1')
union all
select ('TAB2')

backup filegroup lamb -- with 2 records

-- backup filegroup lamb - with 2 records

BACKUP DATABASE [FGTest] FILEGROUP = N'lamb'
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2Records.bak'
WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup',
STATS = 10
GO

-- log backup filegroup lamb - with 2 records

BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2records.trn' WITH INIT

Now insert few more values in lamb and lamb1 file groups

INSERT INTO FGTest..TAB1(TAB1_NAME)
select ('TAB3')
union all
select ('TAB4')

INSERT INTO FGTest..TAB1_lamb1(TAB1_NAME)
select ('TAB3')
union all
select ('TAB4')

Now backup lamb and lamb1 filegroups along with the T-logs

-- -- backup filegroup lamb - with 4 records

BACKUP DATABASE [FGTest] FILEGROUP = N'lamb'
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_4Records.bak'
WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup',
STATS = 10
GO

-- log backup filegroup lamb - with 4 records

BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_4records.trn' WITH INIT

-- -- backup filegroup lamb1 - with 2 records

BACKUP DATABASE [FGTest] FILEGROUP = N'lamb1'
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_lamb1.bak'
WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup',
STATS = 10
GO

-- log backup filegroup lamb1 - with 2 records

BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_lamb1_log.trn' WITH INIT

Now comes the restore Part :

Restore the database with PARTIAL and NORECOVERY

 use master
 go

 -- restore the base backup with PARTIAL and NORECOVERY

 restore database [FGTest]
 filegroup = 'Primary'
 from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_basebackup.bak'
 with REPLACE, PARTIAL, NORECOVERY
 go

Now Restore the lamb filegroup along with the T-log

 RESTORE DATABASE [FGTest] FILE = N'lamb' FROM
 DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2Records.bak'
 WITH  FILE = 1, REPLACE,  STATS = 10
 GO

 RESTORE LOG [FGTest] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2records.trn'
 WITH FILE = 1, NORECOVERY

-- since you want to restore the database to 1st backup and you already restored the sequence, its time to bring the database back online with 2 records.

restore database [FGTest] with recovery

Since you have not brought the other file group online, when you try to query TAB1_lamb1 table residing on lamb1 filegroup, the query processor will throw an error.


Unfortunately this is not mentioned in the documention.

You can import it via Tools > Import Tables


Create SQL login to multiple instances at once

Download the awesome Powershell scripts from dbatools and use New-DbaLogin to add the new login.

Example:

New-DbaLogin -SqlInstance sql1 -Login domain\user


Sorry, you must create the PARTITIONs yourself.  I would do it in Perl; do you know that language, or some other?


MS SQL Server 2012 Log Shipping Questions

1) If the primary database server dies, is it possible to allow users to connect to the secondary database so as to continue working with Read/Write access?

You have to manually failover to the secondary server as logshipping does not have a built-in mechanism to support automatic failover.

Once you failover, your application/s (provided they are using ADO.NET or SQL Native Client) can leverage the Failover Partner=Secondary_server

Brent has blogged about failover partner:

  you don’t need to use database mirroring to use Failover Partner.  Whether you’re using database mirroring, replication, log shipping, or duct tape, much like the honey badger, your applications don’t care.  They’ll just try to connect to the Failover Partner name whenever the primary server is down.

.

  2) Once the primary server comes back online, is it a difficult process to switch back to the primary database server?

Traditionally, depending on the size and number of database that are invloved in logshipping, it may or may not be difficult.

Database size and your network bandwidth matters a lot. Normally, you just have to resetup log-shipping. Meaning - point all your apps to primary and in background, do a full backup of primary, copy that to secondary server and restore it with no-recovery with subsequent log backups.

To minimize the downtime, you can use "Reverse logshipping" will prove to be a huge help.

  3) Will changing the Recovery model to Full (from simple) have any issues?

Yes. Changing recovery model breaks the log chain. Paul Randall talks in his myths section. Also, see below chart :

  4) Will my existing backups (using Symantec Backup Exec) be affected by enabling log shipping and switching to a full recovery model?

Once you setup logshipping, there is no need to take any additional log backups. In fact, any adhoc log backups will break the log chain. Use COPY_ONLY backups.

As I mentioned above, any change to the recover model should be followed by a FULL backup. That will be your base backup. Any adhoc backups (even full) should be taken with a COPY_ONLY option as if you are relying on differential backups then it will be an issue if someone takes an adhoc full backup.

As a side note, always fully document, test, test and test + automate (as much possible) your disaster recovery strategy. See Paul's DR Poster for more details.


Multiple inserts across tables with serial primary keys

The following is incorrect:

insert into top values (null, 'first_top')

You are explicitly passing null for the first column. Postgres will (try to) store that value in the column and the sequence (that the serial uses) will not be used! By supplying an explicit value for a serial column (or any column with a default) you are overwriting that automatic default.

Now for the actual question:

You can use lastval() to obtain the most recently generated sequence value:

insert into top (top_name) values ('first_top');
insert into mid (top_id, mid_name) values (lastval(), 'first_mid_for_first_top');
insert into bot (mid_id, bot_name) values (lastval(), 'first_bot_for_first_mid');

You can't however insert multiple rows into the mid table in a single statement with that.


How to query mysql tables with regular expressions?

I answer by myself. In short, sql syntax above is correct, so how to query mysql tables with regular expressions? Exactly like I did.

The reason why it didn't work, is regexp syntax being too 'complex' for mysql.
I found out that mysql doesn't support the full syntax possibilities of standard regular expressions. I must use simpler symbols/constructs. So for example, instead of \D{2} I can use [A-Z]{2}, and instead of \d, knowing I'll want 1 or 2, I can use [1,2]?. A syntax like the following will perfectly work in mysql to match a string like 'IT.1.12.00.00.00':

[A-Z]{2}\.[1,2]?\.[1-9][0-9](\.00){3}

With full regexp syntax I could have use lookahead assertions and other constructs, but anyway, for now for my needs I can use plain 'old' grammar.


Why is Oracle so much slower for small atomic inserts compared to Postgres? And what defaults can I change to remedy this?

SQL> CREATE TABLE "TEST_TABLE" (
    "ID" VARCHAR2(32 BYTE) NOT NULL ENABLE,
    "CREATED" TIMESTAMP (6) DEFAULT NULL NOT NULL ENABLE,
    "REFERENCE" NVARCHAR2(500),
    PRIMARY KEY(ID)
);

Table created.

SQL>

SQL> set timing on
SQL> INSERT INTO test_table VALUES ('1', systimestamp, 'hello world');

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL> select executions, elapsed_time from v$sql where sql_text = 'INSERT INTO test_table VALUES (''1'', systimestamp, ''hello world'')';

EXECUTIONS ELAPSED_TIME
---------- ------------
         1         1121

Elapsed: 00:00:00.09
SQL>

The insert took 1121 microseconds = 1.121 milliseconds, on the first try, with default settings.
SQL> INSERT INTO test_table VALUES ('2', systimestamp, 'hello world');

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select executions, elapsed_time from v$sql where sql_text = 'INSERT INTO test_table VALUES (''2'', systimestamp, ''hello world'')';

EXECUTIONS ELAPSED_TIME
---------- ------------
         1          780

Elapsed: 00:00:00.09

Even quicker on additional attempts.
Problem is not with the database, but the software/hardware environment around it.


SQL query fills up RDS free storage space after upgrade to MySQL 5.7

obviously this is an old issue, but we just had a massive problem with this and I thought I'd share:

SELECT queries can write a lot of data to MySQL's temporary file if executed with a 'Using temporary; using filesort;' plan as shown by EXPLAIN EXTENDED.
MySQL can change its execution plan at the drop of a hat based on changing statistics: as a totally not-hypothetical example, it can go from a perfectly reasonable plan using an index to retrieve ~100,000 rows that takes ~15 seconds to a massive on-disk mergesort that takes 45 minutes total :facepalm:.
This temporary table space will be reclaimed automatically on server restart (at least, that's how it worked for us on an AWS RDS MySQL 5.7 instance).

The temp table is usually called ibtmp1, and you can get the size like so:

SELECT *  FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ibtmp%' \G

I haven't yet found a way to configure RDS to limit the size of this file.


How does combining columns save disk space?

int and char(1) are not the same.

int stores a 32 signed whole number, 4 bytes
char(1) stores a single characters, 1 byte (on SQL Server, does not use UTF-8 etc)

So, it makes no sense to convert them as it stands.
Which means your "20% less disk space" is wrong.

Did you SUM reserved pages for a freshly re-indexed table that is at least 1MB?

If you are using int to store boolean (true or false), and you are converting to Y or N in the char(1), then a more efficient solution is to use bit. Which will take even less space. 3 bit columns will use one byte.

Edit, explanation

SQL Server has very specfic rules about how data is stored, based on datatype and NULLability. This takes into account variable or fixed length data, as well as precision (for decimals).

The issue here was choosing the wide 4 byte int instead of single byte tinyint.

Using tinyint means the data stays as a number, no conversions, and no fancy combine-in-a-single-colun madness. And is the smallest data type possible for this data


Is there a possibility to see cascade path in MS SQL

You can read information about FKs from sys.foreign_keys. The object_id and
referenced_object_id will be references to rows in sys.tables (or sys.objects) so you can pull out the names of the tables involved. If you need more detail add in sys.foreign_key_columns and sys.columns.

A simple query on those might be enough to tell you what you need, or you could use a recursive CTE to generate a complete map of the tree of relationships.


Incorrect syntax near 'role' with generated script

You need to make sure your scripting options are set to target the right version. You can do this in Tools / Options / Object Explorer / Scripting / Script for server version. In your case, you should pick 2008 (it is the umbrella for both 2008 and 2008 R2), though 2005 will probably work as well.

The challenge is that if you move to the SQL Server 2016 version of SSMS, some of this backward compatibility is broken (and in fact it may also be broken in the version you're using now, I haven't tested lately, but if so, it's less likely that it will be fixed):

Connect #1870405 : [SSMS] - CTP 2.4 - Can't script certain downlevel options

The main reason you can't script this specific login's role for 2008 is that CREATE SERVER ROLE - in fact the ability to create a user-defined server role in the first place - was introduced in SQL Server 2012. So you will have to script the login without the role if you want to deploy to some version < 2012.

UPDATE: Microsoft just added a comment to the Connect item, indicating that it will not be fixed, and why:

  Hello Aaron,

  We've decided to not change the current behavior of the scripting.

  1. For the "Script As" scenario from OE this is intended as the syntax to create/alter/drop server roles didn't exist prior to 2012, which is why the exception is throw (there's nothing that can be generated that would be valid)

  2. For the Properties -> Script scenario that is also intended. The script button is intended to generate the script that would be ran if the "OK" button is pressed, and so it doesn't take the current SSMS scripting options into account.

  I will be updating the error message displayed for #1 though to more clearly indicate what the problem is.


Extract only single value from multiple rows based on certain condition for same id in Oracle

So what happens then there is no row with Manual, but there is with SendV? Should it be SendV as in your example output?

with data as
(
  select 12345 as id, 'MANX' as grp, 'Apple' as itdesc, 'SendV' as den from dual union all
  select 12345 as id, 'MANX' as grp, 'Apple' as itdesc, 'Manual' as den from dual union all
  select 12345 as id, 'MANX' as grp, 'Apple' as itdesc, 'UnVeri' as den from dual union all
  select 12346 as id, 'MANX' as grp, 'Mango' as itdesc, 'UnVeri' as den from dual union all
  select 12347 as id, 'MANX' as grp, 'PineAp' as itdesc, 'SendV' as den from dual union all
  select 12348 as id, 'MANX' as grp, 'Pine' as itdesc, 'Manual' as den from dual
)
select id, grp, itdesc, den from
(
  select
    id, grp, itdesc, den,
    row_number() over (partition by id, grp, itdesc order by
      case grp when 'SendV' then 1 when 'Manual' then 2 else 3 end) as rn
  from data
)
where rn = 1
order by id;

        ID GRP  ITDESC DEN
---------- ---- ------ ------
     12345 MANX Apple  SendV
     12346 MANX Mango  UnVeri
     12347 MANX PineAp SendV
     12348 MANX Pine   Manual


Where to look for Agent history in Replication?

where will that data be saved by default ?

Agent history clean up: distribution calls

EXEC [distribution_db_name].dbo.sp_MShistory_cleanup @history_retention = 48

This PROC deletes from MSsnapshot_history , MSlogreader_history, MSdistribution_history, MSqreader_history and MSmerge_history and bunch of other tables.

This is the entire code for a ready reference :

CREATE PROCEDURE sp_MShistory_cleanup
(
    @history_retention int = 24
)
AS
BEGIN
    DECLARE @cutoff_time datetime
                ,@replerr_cutoff datetime
                ,@start_time datetime
                ,@num_snapshot_rows int
                ,@num_logreader_rows int
                ,@num_distribution_rows int
                ,@num_replerror_rows int
                ,@num_queuereader_rows int
                ,@num_alert_rows int
                ,@num_tracer_record_rows int
                ,@num_milliseconds int
                ,@num_seconds float
                ,@seconds_str nvarchar(10)
                ,@rate int
                ,@retcode int
                ,@total_rows int
                ,@num_merge_rows int
                ,@num_merge_deleted_articlehistory int
                ,@agent_name nvarchar(255)
                ,@agent_type nvarchar(100)
                ,@message nvarchar(255)
                ,@agent_id int
                ,@error int

    SET NOCOUNT ON

    -- Check for invalid parameter values
    IF @history_retention < 0
    BEGIN
        RAISERROR(14106, 16, -1)
        RETURN 1
    END

    -- Get start time for statistics at the end
    -- Get cutoff time
    -- cleanup MSrepl_error with HistoryRetention+30 days
    SELECT @start_time                          = getdate(),
            @num_snapshot_rows                  = 0,
            @num_logreader_rows                 = 0,
            @num_distribution_rows              = 0,
            @num_merge_rows                     = 0,
            @num_replerror_rows                 = 0,
            @num_queuereader_rows               = 0,
            @num_merge_deleted_articlehistory   = 0,
            @cutoff_time                        = dateadd(hour, -@history_retention, getdate()),
            @replerr_cutoff                     = dateadd(hour, -@history_retention - 30*24, getdate())

    DECLARE #crSnapshotAgents CURSOR LOCAL FAST_FORWARD FOR
        SELECT id
            FROM MSsnapshot_agents

    OPEN #crSnapshotAgents

    FETCH #crSnapshotAgents INTO @agent_id
    WHILE @@FETCH_STATUS <> -1
    BEGIN

        -- Delete sp_MSsnapshot_history (leave at least one row for monitoring)
        DELETE MSsnapshot_history
            WHERE agent_id = @agent_id
                AND time <= @cutoff_time
                AND timestamp not in (SELECT max(timestamp)
                                        FROM MSsnapshot_history
                                        WHERE agent_id = @agent_id)
            OPTION(MAXDOP 1)

        SELECT @error = @@error, @num_snapshot_rows = @num_snapshot_rows + @@rowcount
        IF @error <> 0
            GOTO FAILURE

        FETCH #crSnapshotAgents INTO @agent_id
    END

    CLOSE #crSnapshotAgents
    DEALLOCATE #crSnapshotAgents

    -- Delete sp_MSsnapshot_history that no longer has an MSsnapshot_agent entry
    DELETE FROM MSsnapshot_history
        WHERE NOT EXISTS (SELECT *
                            FROM MSsnapshot_agents
                            WHERE id = agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_snapshot_rows = @num_snapshot_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete sp_MSlogreader_history (leave at least one row for monitoring)
    DECLARE #crLogreaderAgents CURSOR LOCAL FAST_FORWARD FOR
        SELECT id
            FROM MSlogreader_agents

    OPEN #crLogreaderAgents

    FETCH #crLogreaderAgents INTO @agent_id
    WHILE @@FETCH_STATUS <> -1
    BEGIN

        -- Delete sp_MSsnapshot_history (leave at least one row for monitoring)
        DELETE MSlogreader_history
            WHERE agent_id = @agent_id
                AND time <= @cutoff_time
                AND timestamp not in (SELECT max(timestamp)
                                        FROM MSlogreader_history
                                        WHERE agent_id = @agent_id)
            OPTION(MAXDOP 1)
        SELECT @error = @@error, @num_logreader_rows = @num_logreader_rows + @@rowcount
        IF @error <> 0
            GOTO FAILURE

        FETCH #crLogreaderAgents INTO @agent_id
    END

    CLOSE #crLogreaderAgents
    DEALLOCATE #crLogreaderAgents

    -- Delete sp_MSlogreader_history that no longer has an MSlogreader_agent entry
    DELETE FROM MSlogreader_history
        WHERE NOT EXISTS (SELECT *
                            FROM MSlogreader_agents
                            WHERE id = agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_logreader_rows = @num_logreader_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete sp_MSdistribution_history (leave at least one row for monitoring)
    DECLARE #crDistribAgents CURSOR LOCAL FAST_FORWARD FOR
        SELECT id
            FROM MSdistribution_agents

    OPEN #crDistribAgents

    FETCH #crDistribAgents INTO @agent_id
    WHILE @@FETCH_STATUS <> -1
    BEGIN

        -- Delete sp_MSsnapshot_history (leave at least one row for monitoring)
        DELETE MSdistribution_history
            WHERE agent_id = @agent_id
                AND time <= @cutoff_time
                AND timestamp not in (SELECT max(timestamp)
                                        FROM MSdistribution_history
                                        WHERE agent_id = @agent_id)
            OPTION(MAXDOP 1)
        SELECT @error = @@error, @num_distribution_rows = @num_distribution_rows + @@rowcount
        IF @error <> 0
            GOTO FAILURE

        FETCH #crDistribAgents INTO @agent_id
    END

    CLOSE #crDistribAgents
    DEALLOCATE #crDistribAgents

    -- Delete sp_MSlogreader_history that no longer has an MSlogreader_agent entry
    DELETE FROM MSdistribution_history
        WHERE NOT EXISTS (SELECT *
                            FROM MSdistribution_agents
                            WHERE id = agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_distribution_rows = @num_distribution_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete MSqreader_history (leave at least one row for monitoring)
    DECLARE #crQreaderAgents CURSOR LOCAL FAST_FORWARD FOR
        SELECT id
            FROM MSqreader_agents

    OPEN #crQreaderAgents

    FETCH #crQreaderAgents INTO @agent_id
    WHILE @@FETCH_STATUS <> -1
    BEGIN

        -- Delete sp_MSsnapshot_history (leave at least one row for monitoring)
        DELETE MSqreader_history
            WHERE agent_id = @agent_id
                AND time <= @cutoff_time
                AND timestamp not in (SELECT max(timestamp)
                                        FROM MSqreader_history
                                        WHERE agent_id = @agent_id)
            OPTION(MAXDOP 1)
        SELECT @error = @@error, @num_queuereader_rows = @num_queuereader_rows + @@rowcount
        IF @error <> 0
            GOTO FAILURE

        FETCH #crQreaderAgents INTO @agent_id
    END

    CLOSE #crQreaderAgents
    DEALLOCATE #crQreaderAgents

    -- Delete sp_MSlogreader_history that no longer has an MSlogreader_agent entry
    DELETE FROM MSqreader_history
        WHERE NOT EXISTS (SELECT *
                            FROM MSqreader_agents
                            WHERE id = agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_queuereader_rows = @num_queuereader_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete sp_MSmerge_history (leave at least one row for monitoring)
    -- Leave last record ONLY if the agent is not anonymous.  The current logic is to remove all history for anonymous
    -- subscription, the agent definition will also be removed below.
    -- use session id
    DELETE dbo.MSmerge_history
        FROM dbo.MSmerge_history msmh
            JOIN dbo.MSmerge_sessions msms
                ON msmh.session_id = msms.session_id
        WHERE msms.end_time <= @cutoff_time
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_merge_rows = @num_merge_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete sp_MSmerge_history that no longer has an MSmerge_agent entry
    DELETE FROM dbo.MSmerge_history
        WHERE NOT EXISTS (SELECT *
                            FROM dbo.MSmerge_agents
                            WHERE id = agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_merge_rows = @num_merge_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete MSrepl_error entries
    DELETE FROM MSrepl_errors
        WHERE time <= @replerr_cutoff
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_replerror_rows = @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- similiar to above time based cleanup, we need to clean up added tables
    DELETE dbo.MSmerge_articlehistory
        FROM dbo.MSmerge_articlehistory msmah
            JOIN dbo.MSmerge_sessions msms
                ON msmah.session_id = msms.session_id
        WHERE msms.end_time <= @cutoff_time
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_merge_deleted_articlehistory = @num_merge_deleted_articlehistory + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    DELETE FROM dbo.MSmerge_sessions
        WHERE end_time <= @cutoff_time
            AND session_id NOT IN (SELECT max(session_id)
                                    from dbo.MSmerge_sessions
                                    group by agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_merge_rows = @num_merge_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete MSmerge_sessions that no longer has an MSmerge_agent entry
    DELETE FROM dbo.MSmerge_sessions
        WHERE NOT EXISTS (SELECT *
                            FROM dbo.MSmerge_agents
                            WHERE id = agent_id)
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_merge_rows = @num_merge_rows + @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete sysreplicationalerts table
    DELETE FROM msdb.dbo.sysreplicationalerts
        WHERE time <= @cutoff_time
        OPTION(MAXDOP 1)
    SELECT @error = @@error, @num_alert_rows = @@rowcount
    IF @error <> 0
        GOTO FAILURE

    -- Delete Tracer Record history rows
    EXEC @error = sys.sp_MSdelete_tracer_history @cutoff_date = @cutoff_time, @num_records_removed = @num_tracer_record_rows output
    IF @error <> 0
        GOTO FAILURE

    -- Calculate statistics for number of rows deleted
    SELECT @num_milliseconds = datediff(millisecond, @start_time, getdate())
    IF @num_milliseconds <> 0
        SELECT @num_seconds = @num_milliseconds*1.0/1000
    ELSE
        SELECT @num_seconds = 0

    SELECT @total_rows = @num_merge_rows +
                            @num_merge_deleted_articlehistory +
                            @num_snapshot_rows +
                            @num_logreader_rows +
                            @num_distribution_rows +
                            @num_queuereader_rows +
                            @num_replerror_rows +
                            @num_alert_rows +
                            @num_tracer_record_rows

    IF @num_seconds <> 0
        SELECT @rate = @total_rows/@num_seconds
    ELSE
        SELECT @rate = @total_rows

    SELECT @seconds_str = CONVERT(nchar(10), @num_seconds)

    RAISERROR(14108, 10, -1, @num_merge_rows, 'MSmerge_history')
    RAISERROR(14108, 10, -1, @num_merge_deleted_articlehistory, 'MSmerge_articlehistory')
    RAISERROR(14108, 10, -1, @num_snapshot_rows, 'MSsnapshot_history')
    RAISERROR(14108, 10, -1, @num_logreader_rows, 'MSlogreader_history')
    RAISERROR(14108, 10, -1, @num_distribution_rows, 'MSdistribution_history')
    RAISERROR(14108, 10, -1, @num_queuereader_rows, 'MSqreader_history')
    RAISERROR(14108, 10, -1, @num_replerror_rows, 'MSrepl_errors')
    RAISERROR(14108, 10, -1, @num_alert_rows, 'sysreplicationalerts')
    RAISERROR(14108, 10, -1, @num_tracer_record_rows, 'MStracer_tokens')
    RAISERROR(14149, 10, -1, @total_rows, @seconds_str, @rate)

    RETURN 0
FAILURE:
    -- Raise the Agent Failure error
    SELECT @agent_type  = formatmessage(20544),
            @agent_name = db_name() + @agent_type,
            @message    = formatmessage(20553)

    EXEC sys.sp_MSrepl_raiserror @agent_type, @agent_name, 5, @message

    RETURN 1
END


How can you save all user-defined functions to txt files on your local hard drive?

The easiest way I can think of is the following:

Right-click on your database -> Tasks -> Generate Scripts...
Select specific database objects
Select the checkbox User-Defined Functions
Save to file and just name your text file whatever you'd like

This will generate and save the scripts for your UDFs to your desired file.  You could use PowerShell (off the top of my head) to generate scripts for each UDF and save them to a separate text file (one text file per UDF).

Edit:  Below is some PowerShell to do what you want to do.  This script will get all of the User Defined Functions in your database (set the variables to your appropriate environment) and write the definition of each of them to their own file, naming the file what the function is named.

$serverName = "YourServerName"          # example: "YourServer" or "YourServer\YourNamedInstance"
$databaseName = "YourDatabaseName"
$outputDirectory = "C:\Dir1\YourUDFs"   # the dir you want these files saved in

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName)
$database = $server.Databases.Item($databaseName)
$udfCol = $database.UserDefinedFunctions | Where-Object {$_.Schema -ne "sys"}
foreach ($udf in $udfCol) {
    $udfName = $udf.Name
    ($udf.TextHeader + $udf.TextBody) |
        Out-File -FilePath $outputDirectory\$udfName".txt"
}


Have you tried using a LEFT OUTER JOIN? A LEFT OUTER JOIN will keep all the rows from the 'left' side of the join statement (posts table) that do not have a correponding row on the 'right' side of the statement (ratings table). The missing rows from the 'right' side will be filled with NULLS.


My MongoDB Atlas Operations graph has doubled in the last few hours.
  How do I go about debugging this?

As per MongoDB documentation here You can use a third-party application to view and analyze performance metrics that Atlas collects about your cluster.

At this time, you can either build a monitoring integration using the Atlas API or integrate Atlas with Datadog.

Build Monitoring Integrations with Atlas API

You can build a monitoring integration using the Atlas API monitoring and logs endpoints.

Integrate Atlas with Datadog

You can configure Atlas to send metric data about your project to your Datadog dashboards.

  Note: Datadog integration is only available on M10+ clusters.

For further your ref here


Index dive/sampling row count very inaccurate in MySQL

Yes, the samples are inaccurate.  A factor of 2 is actually somewhat common.

It could be that the "random" probe is not very random, and may repeat some pages.

OPTIMIZE TABLE, which is generally useless, would rearrange the data, probably enough to change the statistics.

Consider writing a bug report at bugs.mysql.com .

A side comment:  A table with 3 unique indexes is extremely rare; it usually indicates a flaw in the schema design.  Perhaps nepotism is not allowed in the company?


I haven't yet found a way to customize the dropdown list in the GUI, but you can use the Add Sensitivity Classification statement (only in Azure SQL and SQL 2019 so far -- Ugh! Sorry.) to apply any label you want to classify a column.

See this Microsoft documentation for details on the Add Sensitivity Classification statement. But here are the basics:

ADD SENSITIVITY CLASSIFICATION TO
<object_name> [, ...n ]
WITH ( <sensitivity_label_option> [, ...n ] )

<object_name> ::=
{
    [schema_name.]table_name.column_name
}

<sensitivity_label_option> ::=
{
    LABEL = string |
    LABEL_ID = guidOrString |
    INFORMATION_TYPE = string |
    INFORMATION_TYPE_ID = guidOrString
}

Also see this Aaron Bertrand article at MSSQLTips.com.

That being said, even though you have to deal with HIPAA, CJIS, NFIRS, NFPA, and CISSP, and they might not use exactly the same terms as each other, you should still be able to map the sensitivities to the built-in labels. Customizing the labels might work for now, but you'll have to do extra work to maintain that customization, making sure it is consistent across tables, database, and instances, and documenting it well.

I recommend you work with your auditing and compliance folks to reach a mutual understanding of what each sensitivity label means to your organization, and how to map that to the various regulatory expectations.


CHROOT install after I already installed Mysql 5.6

I didn't tested it but mysql has build in support for chroot by adding "chroot=/path/to/chroot" into my.cnf

Normally mysql should start and load all libraries and the log files it needs and then make a chroot to the path set in the parameter.
That means the database must be in the chroot and the path in the config file have to be relative to the chroot.

So if your "datadir" path is set to /var/lib/mysql/data and your chroot is /var/lib/mysql you have to change the "datadir" path to /data

According to the documentation LOAD DATA INFILE and SELECT ... INTO OUTFILE having some limits.

http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_chroot


PostgreSQL chooses to seq scan huge table instead of index lookup

EXISTS will stop executing as soon as a result is found, so if that happens early on, a sequential scan will be very fast. Obviously PostgreSQL estimates that very many rows in account_balance have that address.
The cost estimate on the sequential scan is misleading, since it is for the complete scan. As you see, the final cost is estimated quite low.
Without EXPLAIN (ANALYZE, BUFFERS) it is impossible to say if PostgreSQL is right, but then you only asked for an explanation why it would choose such a plan.


SQLServer - how to move tables between instances?

First of all if your object names are xpto.tablename you probably created a schema not a new database.

Schemas are the containers for objects such as tables.  Databases contain schemas.  So the naming format is databasename.schemaname.tablename.

Assuming that is correct it's very easy to change the schema that owns your tables using ALTER SCHEMA.

ALTER SCHEMA dbo TRANSFER schemaname.tablename

This will transfer TableName into the dbo schema.  Or a little closer to your specific case

ALTER SCHEMA dbo TRANSFER xpto.tablename

If in fact your tables are in the wrong database you can move them by using something like this:

USE correctDBName
SELECT * INTO xpto.dbo.TableName FROM tablename

You'll then want to generate any indexes, constraints, permissions etc and apply them in the correct database.

Edit

Here is a query you can use to construct the commands to transfer all the tables from the xpto schema to the dbo schema.

SELECT 'ALTER SCHEMA dbo TRANSFER xpto.' + quotename(tables.name)
FROM sys.tables
JOIN sys.schemas
    ON sys.tables.schema_id = sys.schemas.schema_id
WHERE sys.schemas.name = 'xpto'


SQL server Virtual account and folder permissions

As long as you change the service account being used by using the SQL Configuration Manager, then it will configure all the necessary permissions for the new service account.

https://msdn.microsoft.com/en-us/library/ms143504%28SQL.110%29.aspx#Serv_SID

Note that the mechanism that happens under the hood to grant these permissions has changed with the different versions of SQL, according to this discussion:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9e6bb2de-8fd0-45de-ab02-d59bbe05f72e/servicedatabase-accounts-nt-servicemssqlserver-nt-servicesqlserveragent-what-are-they-for?forum=sqlsecurity


What could be the best DBMS to store airbnb data for price prediction

You can certainly accomplish this with any modern RDBMS, as 400,000 records is actually small data nowadays.
Since it's a school project, I assume you'll want to use a free edition of the system you choose. PostgreSQL and MySQL are free out the gate, Microsoft SQL Server also offers a free edition called "Express" edition, and I believe even Oracle offers a free edition of their database system as well.
My somewhat subjective recommendation out of those choices would be to use PostgreSQL or Microsoft SQL Server, just because of their mainstream use and plethora of documentation on how to setup, use, and query in either system. And both offer a sufficient amount of features, that I think they'll meet your data needs without a second thought.
The final criteria to help you decide on which one to use should be based off of which procedural programming language / tech stack you're most familiar with (if you need to use one as part of this project). If you're used to the Microsoft tech stack then SQL Server is a fitting choice, otherwise most other cases will plug into PostgreSQL just fine. If you're planning to use Python for the application side, it probably can plug into either database system just as easily.


RDS Oracle SE - Performance troubleshooting options

You may want to run an EC2 instance as a dev environment. Given that you don't have access to the file system on an RDS database and that you have standard edition, you can't turn on 10046 and 10053 events and use tkprof to read the files, you can't use active session history (ASH) and Automatic Workload Repository (AWR) since you need to have enterprise to use those reports and Oracle would know if you used any of those features in an audit situation.

Your best bet is to look for blocking locks in prod and use and EC2 instance to do load testing possibly using set events 10046 and 10053. There might be another tool that you can use in RDS. I don't know what that would be. You are limited in what you can do give that you are running standard edition on an RDS instance.


How to generate DDL of an object which a synonym points to?

For tables:

select dbms_metadata.get_ddl('TABLE', table_name)
from user_synonyms
where synonym_name in ...;

For other object types as well:

select dbms_metadata.get_ddl(o.object_type, s.table_name)
from user_synonyms s join user_objects o on (s.table_name = o.object_name)
where s.synonym_name in ...;


Is it possible to check all constraints in a MySQL database?

Long ago I wrote a tool called oak-apply-ri. It is part of the openark-kit, which is generally well accepted. I'm beginning with the disclaimer that this tool is less popular, and I don't have much input about it (other than my own).

Nevertheless, it allows you to specify two tables that share a FK relationship, and it will verify whether the constraint holds.

You can ask it to actually resolve the differences via --action=delete or --action=setnull.

If you are suspicious, you may ask it to --print-only in which case it will not apply any changes to your database; instead it will print out the statements it would like to invoke on the database.

I'm happy to get any feedback on this.


Can we create index for key/value of JSONB data type?

I suggest an expression index on the value of the key 'x_id', cast to bigint - plain (default) btree, not GIN. A specific difficulty here: the short notation for type casts would need multiple layers of parentheses to make the syntax work for index creation.

CREATE INDEX y_data_xid_idx ON y (((y.data->>'x_id')::bigint));

Rather use the explicit form (to the same effect):

CREATE INDEX y_data_xid_idx ON y (cast(y.data->>'x_id' AS bigint));


Unable to create UNIQUE Filtered index with IGNORE_DUP_KEY = ON

As already mentioned by the other answer, IGNORE_DUP_KEY is not supported for filtered indexes, which is explicitly stated in the manual:

  Filtered indexes do not allow the IGNORE_DUP_KEY option.

There is a workaround, however, if you can afford a small change to the table's structure. With the help of a uniquifier column you can create a unique index that would effectively allow you to have unique Values only for rows with FLAG = 1.

The following assumes that the ID column is already declared as unique (either being the primary key or having a UNIQUE constraint on it). First, add a computed column that would evaluate to NULL for the FLAG = 1 rows and to ID for all the others:

ALTER TABLE dbo.FOO
ADD Uniquifier AS CASE FLAG WHEN 1 THEN NULL ELSE ID END;

Now you can create a unique index on (Value, Uniquifier) with the IGNORE_DUP_KEY option:

CREATE UNIQUE NONCLUSTERED INDEX UQ_1
ON dbo.FOO (Value, Uniquifier)
WITH (IGNORE_DUP_KEY = ON);

Since Uniquifier will be NULL for rows with FLAG = 1, the uniqueness will be determined based on Value alone. All the other rows will be unique regardless of Value because Uniquifier is unique (since its value is taken from a unique column, ID).

A live demonstration of the method is available at SQL Fiddle.


Sub-Query group-by count(*) not returning 0

You don't need a join. First you need to GROUP BY employee, in order to get the hours per employee. Assuming you have an employee_id that identifies employees:

 SELECT
     employee_id,
     SUM(TRAINING_HOURS) AS Hours_Worked
 FROM
     EMPLOYEE_TRAINING_RECORD
 GROUP BY
     employee_id ;

Then you need to do another GROUP BY, in the whole result set, to count the number of employees that have over and under 75 hours:

SELECT
   COUNT(CASE WHEN Hours_Worked >= 75 THEN 1 END) AS Completed_Training,
   COUNT(CASE WHEN Hours_Worked  < 75 THEN 1 END) AS Incomplete_Training
FROM
   (  SELECT
         -- employee_id,      -- we don't need that in the final results
         SUM(TRAINING_HOURS) AS Hours_Worked
      FROM
         EMPLOYEE_TRAINING_RECORD
      GROUP BY
         employee_id
   ) grp                   -- the first group result set
 ;

You could also use window functions and not use subqueries but the code is rather obfuscated:

SELECT DISTINCT
   COUNT(CASE WHEN SUM(TRAINING_HOURS) >= 75 THEN 1 END)
      OVER () AS Completed_Training,
   COUNT(CASE WHEN SUM(TRAINING_HOURS)  < 75 THEN 1 END)
      OVER () AS Incomplete_Training
FROM
   EMPLOYEE_TRAINING_RECORD
GROUP BY
   employee_id ;


What is the meaning of DOP in the context of SQL Server?

DOP in SQL Server stands for Degrees of Parallelism.

Per the MSDN article here, if SQL Server has multiple processors available, and the query meets certain thresholds, SQL Server will try and split up the query over multiple processors to increase its performance.

In earlier versions of SQL Server, I have seen parallelism actually make queries run slower; that is, the time to spend figuring out the parallelism takes longer than the actual query would take without it.

In queries, you can specify the maximum number of processors to use by using the OPTION (MAXDOP X) query hint, where X is the maximum number of processors available.  You can turn off parallelism by using OPTION (MAXDOP 1).

There is also a known issue with parallelism and SCOPE_IDENTITY() in SQL Server 2005/2008.


Determining what algorithm MYSQL View is using

If the view is being processed with the TEMPTABLE algorithm, the view will be treated by the optimizer as if it were written as a subquery in the from clause (derived table) and should show up in the EXPLAIN output as a derived table.
Here's an example of a view that was explicitly defined with the TEMPTABLE algorithm.  Two tables show up in the query plan, although this view was just defined as SELECT * from a single table.
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   33 | NULL  |
|  2 | DERIVED     | task       | ALL  | NULL          | NULL | NULL    | NULL |   33 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+

When the view is defined with the UNDEFINED algorithm, the optimizer...

prefers MERGE over TEMPTABLE if possible
— http://dev.mysql.com/doc/refman/5.6/en/view-algorithms.html (also previous versions ~sqlbot)

Aggregate functions/group by/having, distinct, limit, unions, scalar subqueries, and views that return literals only (not from a table) require TEMPTABLE be used... a view without those should be processed as MERGE consistently when the algorithm on the view definition is the UNDEFINED algorithm.
Because of the nature of the two view algorithms, the only one that can use indexes on the underlying tables to find rows matching the outer WHERE -- not one inside the view definition, but the one SELECT ... FROM view WHERE << here >>.  If the EXPLAIN output shows those indexes being used the way you would typically hope, then you're getting MERGE.
I generally define views as MERGE explictly.  The server will accept and save the definition, but somewhat quietly change it back to UNDEFINED and throw a warning if it can't be actually be processed with MERGE.
mysql> CREATE ALGORITHM=MERGE VIEW priority_counts AS SELECT priority_id, COUNT(1) AS quanity FROM task GROUP BY priority_id;
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------+
| Level   | Code | Message                                                                       |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1354 | View merge algorithm can't be used here for now (assumed undefined algorithm) |
+---------+------+-------------------------------------------------------------------------------+
1 row in set (0.08 sec)

SHOW CREATE VIEW subsequently shows ALGORITHM=UNDEFINED.
Of course, when MERGE is accepted, it does show in the view definition, as does TEMPTABLE when it's explicitly declared that way.


Periodic connection failure on SQL Server 2012 AlwaysOn cluster

At least in part, this is going to be the answer found so far, and yes, it did take until last night to get anywhere at all with this problem.

Effectively we still don't really trust the network and we have engineers looking into this as we also have other complaints however despite the changes done being 100% revolving around the infrastructure and network the only thing we managed to fix and change so far was on the code end of things.

Effectively we have a piece of software that looks for files being dropped in a folder (hotfolder) and to make sure we never miss anything every ~15 minutes there is a check to see if there are any files there we're not yet processing. We have a lot of these things and they always run fine, nothing was changed in anything either.

Here is the scenario that happened;

Day we started having trouble a file was placed in a folder and processed
The same day the same file was placed there again

Now the code it self was horrible, it ran for 2-20 minutes depending and has today been rewritten quickly to run in ~20 seconds. There were two massive mistakes in the code that caused this problem;

If non of the lines in the file (it was an XML file) were meant to be processed the file would be left there. Triggering a new run every ~15 minutes (so the next problem would happen over and over again indefinitely)
Every line would call a stored procedure and use a DataReader to see if it had any result, if it did the call would move to the next line WITHOUT closing the reader

The combination of these two things (and the fact those files have 100s if not 1000s of rows) had our system crippled and on it's knees begging for the end.

Someone at the office blames 'connection pooling' and he might be right, he might not be. If i'm being honest I'm not sure why this would cause our systems to be so horribly affected (and the code causing it not to be by the way, never got an exception or problem on this piece of software. Which made it a pain to find).

So if anyone can explain why this would break our SQL server I'd be deligted, all I know is that ASYNC_NETWORK_IO as a wait type might be the biggest clue but I'm not enough of a DBA to find the culprit quickly. So again if anyone has any ideas/queries on how we could have pin pointed this I would love to give them the solution vote.


Start mongod instance on replica set as background job

You can make mongod run in the background with --fork and you will also need to direct your logfiles somewhere with --logpath when you do so.  Before you go about doing all of that though, you should not run mongod as root (which you are doing by using sudo in the command you pasted).

Run it as a regular user, if you have installed MongoDB from one of the packages then you should have the mongod or mongodb user on the systems.  Make sure the dbpath (/data) has the appropriate permissions (as well as wherever you put your logs) and you should have no problems.  For reference, you would change the permissions on /data as follows (assuming a user and group of mongod):

sudo chown -R mongod:mongod /data


SQL Server Restore a SINGLE table from a backup

You can't restore a single table directly from your backup to a database.

You could restore your complete backup to new database and then copy your table from there to the desired database.


SELECT t1.Complaint_id,
       t2.currentstatus,
       t2.usertype
FROM firsttable t1
JOIN ( SELECT currentstatus,
              usertype,
              Parent_id
       FROM ( SELECT Currentstatus,
                     Datetime,
                     Parent_id,
                     usertype,
                     MAX(Datetime) OVER (PARTITION BY Parent_id) AS MaxDatetime
              FROM secondtable
            ) AS derived
       WHERE Datetime = MaxDatetime
     ) t2 ON t1.Complaint_id = t2.Parent_id
         AND t1.reopen = 1

?

PS. Does secondtable (Parent_id, Datetime) is unique by index/constraint?

PPS. Use CTE to make the query more readable...


Creating and designing Air Trafic Control System

That is a tough one, if only there was a way to find things like that online.

http://tinyurl.com/q7zxplh

Make a list of the high level objects that need, such as planes, airports, routes, people, roles, etc. Then figure out how they are related, how you identify each object and what attributes they would have.


Is it bad to use NULL in foreign key column to mean something (e.g. this is the default data)?

There is a third design option which is to separate your pay rates data into a separate table with no client or contractor information. This table contains every unique pay rate across all clients, contractors etc.

You then create a linking table to link clients, contractors and pay rates, plus add a column to your client table that references the ID of the default pay rate for that client.

This is a rough diagram of what I mean:

This should avoid the negatives of your previous options- you're not giving NULL value rows meaning (not that this is inherently wrong, but there are risks to data integrity by using NULL to infer a relationship to other data), and you're also not storing multiple copies of the same data or blurring the entities in your data model together.

This design also means you can reuse existing pay rates across multiple clients/contractors without duplicating data.


Service accounts for SQL Server 2012 only allow pre-Windows 2000 name?

Confusion comes from the fact that user account objects have two names in Active Directory: User Principal Name (UPN) and Security Accounts Manager ID (SAMID). Both of these must be unique in the forest.

The UPN is composed of the user logon name concatenated with an at symbol concatenated with one of the UPN suffixes that are set up in the AD forest. It is the "User logon name" entry in the dialog above. For example administrator@adatum.com.

The SAMID is composed of the NetBIOS name of the domain concatenated with a backslash concatenated with the NetBIOS name of the user account. It is the "User logon name (Pre-Windows 2000)" entry in the dialog above. For example, ADATUM\Administrator.

Many places in Windows accept either name; many do not. For example, logging on to a non-AD machine such as Windows NT4 requires the use of the SAMID. For example, logging on across a forest trust may require the use of the UPN.

The user account name part of the SAMID is also used to generate user folders (local profile, home folder, roaming profile, etc).

As for your problem, the service account should, as far as I know, accept either SAMID or UPN. You do, however, have to specify the full UPN. In other words sqlservice@adatum.com, not just sqlservice. I'll do some testing here once my VMs are started.

OK, after some testing (SQL Server 2008 R2 and SQL Server 2012), it seems that SQL Server Configuration Manager ust doesn't like the UPN. I kept getting a "specified network password is not correct" error. Google found me this StackOverflow issue. As that page states, changing the service account in the Services MMC can use both UPN and SAMID.

Historically, changing service accounts using a tool other than SQL Server Configuration Manager was bad as it didn't put the new service accounts in the correct groups. Newer versions of SQL Server don't use groups for registry and folder permissions but use virtual accounts instead. Despite that, I think my advice is still to always use Configuration Manager and to use specify service accounts using their SAMID.

As an aside, you may be better off using Managed Service Accounts (if your AD supports it). They remove the need for you to deal with password expiry and SPN registration.


Fastest way to split/store a long string for charindex function

Storing and processing 1TB of data with only 16GB RAM available may prove to be a challenge. 1GB per core is rather unbalanced, especially for this kind of workload. 8GB per core would be a much better starting point, with more desirable.

That said, I would still be tempted to try a variation of method 2:

Store all possible 12-character substrings as bigint in a clustered columnstore table (with archive compression if that turns out to be useful):

CREATE TABLE dbo.Search
(
    pos bigint NOT NULL,
    fragment bigint NOT NULL,

    INDEX CCS CLUSTERED COLUMNSTORE
        WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE) -- optional
);

You will probably have to implement some way of incrementally loading the source data into this table. Make sure you end up with maximally-sized row groups (1,048,576 rows) in the finished columnstore structure. See the data-loading guidance.

You might stage rows in multiples of 1048576 in an unindexed rowstore table before creating a clustered columnstore index on that, then switching the result directly into a partitioned main table. The exact approach depends on how you intend to load the data, whether it will be added to, and how familiar you are with SQL Server in general.

Very good performance is possible with this method, but as so often with columnstore, you would need to achieve effective partition and segment elimination. Partitioning on the fragment column and building the columnstore index serially while replacing a rowstore clustered index keyed on fragment is the way to achieve this, as noted in the documentation linked above. This will also minimize the storage needs, since fragment values in the same range will be stored in the same segment. This allows effective value rebasing and bit packing.

While loading, try to limit the strings you are working with within SQL Server to non-LOB (max) types. If you do find working with LOBs best for throughput, there is often a sweet spot of data length to be found, above which performance drops off significantly.

Depending on the final size of the structure and the speed of your I/O subsystem, you may find that this approach offers consistently good enough performance. Increasing the memory available would improve things markedly.


Using SQL Server 2017, can log backups be safely taken on primary when using Log Shipping or Availability Groups?

can CommVault continue to take its log backups for reliable point-in-time recovery and at the same time that either of the high availability options is enabled, or is there a risk of interference?

You can only have one log chain, so Log Shipping and your CommVault log backups will conflict.  Log Shipping can co-exist with full/differential backups, and with VSS backups, but not Log Backups.

An Availability Group replica is maintained by replicating the log records over the network, not through Log Backups.  So that can co-exist with your existing backup strategy.


How does PostgreSQL decide what to name its tablespace directories?

I look at the source code of postgres9.6.

/* tablespace.c */

create_tablespace_directories(const char *location, const Oid tablespaceoid)
{
    ....
    location_with_version_dir = psprintf("%s/%s", location,TABLESPACE_VERSION_DIRECTORY);
    ....
    if (mkdir(location_with_version_dir, S_IRWXU) < 0)
    {
        ....
    }
}

/* catalog.h */

#define TABLESPACE_VERSION_DIRECTORY    "PG_" PG_MAJORVERSION"_" \
                                CppAsString2(CATALOG_VERSION_NO)

/* catversion.h */

#define CATALOG_VERSION_NO  201608131

I found PG_9.6_201608131 is made up of three parts.

  'PG_'  +  PG_MAJORVERSION  + '_' + CATALOG_VERSION_NO

In PostgreSQL9.6

PG_MAJORVERSION is 9.6

CATALOG_VERSION_NO is 201608131

PG_MAJORVERSION and CATALOG_VERSION_NO both are macro definition in c, it won't change.


Ola Hallengren solution, backups not cleaning up

This is a list of random things that come to mind, I can't guarantee any are the problem.

DateTime on Server has been knocked out
DateTime on the Backup store has been knocked out
Someone has changed the @CleanupTime on your agent job (potentially accidentally)
There is a backup of your backup location occurring while the cleanup is trying to run locking the files (have had this one myself)

Since you've said that this is a recent problem not a just happened I'm presuming you can still delete the files manually which would indicate they are not in permanent use.

It may be worth running the -o command at the end to write the output of the command to a file ( where it is '-b' chance to '-b -o C:\Logs\backuplog.txt') for example and consult that for if it is bringing back any problems


Restoring multiple differential backups SQL Server 2008

First thing you do is restore the FULL backup, and then you restore the latest DIFF backup.  That's it!  This is assuming no FULL backups were taken in between the DIFF and the FULL you are restoring from (only exception is if you used a COPY_ONLY option in your full backup but most likely that wasn't the case). Remember to use WITH NORECOVERY on the full restore so you could continue to restore the DIFF.

In the rare exception that the DIFF throws an error stating that the LSN's don't match and your full backup is not the right one, you can query MSDB or right click on the DB, go to 'reports', and 'backup/restore' history.  That'll tell if a full backup was taken in between but by the sounds of it that is not the case.  On the DIFF restore make sure to state WITH RECOVERY to replay the log.


Is it possible to define package procedure/function outside of package body block (for Oracle)?

You always need to compile the entire package body -- it is one item of code. You can create functions and procedures inside other procedures, but that does not change the need to recompile the package body.

What problem are you trying to solve here?


What reason is there to create a physical table for a report output?

(+) Reasons to create a physical table to store report data:

The report data is reusable. I point Crystal Reports or SharePoint to the table and then don't worry about how often or when those tools or my end users access the data. (Well, to an extent, since repeatedly reading a large report table will trash my buffer cache.) I can also maintain a sliding window of old reports for the inevitable requests along the lines of: "Can you generate last year's report again? I can't find the CSV I extracted at the time."

This is probably the main reason it's set up that way at your site. Crystal Reports may not be smart enough to cache the report data as users paginate through it or change the report's settings. So in the worst case CR is regenerating your report with each of those actions--a costly and time-consuming operation. With the physical table it just re-queries the table as many times as it needs.
Setting permissions on the report is easy. You want to see this report?  Well, all you need is permission to read the results, NOT generate them. So here, have some read rights on this table in a locked down schema and filegroup/tablespace.

By manually caching the report, you control and isolate the process of generating it. You give your report readers more freedom to act and yourself less to worry about as a DBA.

(-) What you lose with a physical report table:

Flexibility. Wanna change the report?  Argh, need some DDL changes.
Storage space. You're persisting the data on disk, so duh.


Mysql - get sum() result in another column

If you want both values in the result, you can nest them as in:

select user_total_point, (user_total_point * 100) as hundred_times
from (
    select sum(user_point) as user_total_point
    from users
) as tmp;

Renaming ( as user_total_point ) happens last, so you can not reference that at the same level as you are defining it. The name does not exist yet when you reference it.


Question regarding Excel connection to SSAS cube

Easiest fix would be to have your networking folks point DNS for your old server name to your new server's IP address. Unless you rely on Kerberos that will likely work fine.

If that's not an option, then if you left click on the PivotTable, to the Data tab in the ribbon, then click Properties under the Connections section, then go to the Definition tab, please see if "Always use connection file" is checked. If it is, then you can just manually edit the .odc file in Notepad and point to the new server.

If "Always use connection file" isn't checked, then you can left click on the PivotTable, then go to the PivotTable Tools... Analyze tab in the ribbon, then click the Change Data Source button, then click Choose Connection, then choose a different .odc file to connect to. Your PivotTables should be preserved and they should now connect to your new server.


Availability Group log_reuse_wait = Replication

It looks like the CDC Capture job had stopped running. Restarting that (which took a while) then cleared out the log and allowed it to rewrite.


May I Convert Non RAC db to an RAC DB and add on a Node?

See the Oracle Real Application Clusters Installation Guide 11g Release 2 for Linux and UNIX.  It has a section on "Converting to Oracle RAC from Single-Instance Oracle Databases" describing several different ways this can be done.