Invalid Column Using Row_Number() I am attempting to only select the 1st row returned from the query.  This is my syntax ->

insert into @Temp (id, salesID)
select
    RN = ROW_NUMBER() OVER(order by t.psUserID)
    ,t.psUserID
from (
    select distinct
    psUserID = rstln.Bama
    From rusticlines rstln
    AND tnr.Active = '1') t
WHERE RN = 1;

And I get the error:

  Msg 207, Level 16, State 1, Line 51
  Invalid column name 'RN'.

What do I need to change so that it only selects the 1st row number?

With your example in the question, you could skip the whole ROW_NUMBER() thing and just use TOP (1):

INSERT INTO @Temp (id, salesID)
SELECT TOP (1) 1, Bama
FROM rusticlines AS rstln
WHERE Active = '1'
ORDER BY Bama;

For more complex queries (where you need to get first row in a partition), you could probably make something useful with TOP (1) WITH TIES.


Is it safe to run parallel innodb single-transaction dumps of individual tables? I can't seem to find the internals for --single-transaction mysqldump option for InnoDB tables, but is it possible to use --single-transaction for single tables in a parallel dump so that the --single-transaction only affects the current table I'm dumping and it is safe to run in parallel for quicker dumps? I tested this on a development server and works well but without knowing the internal locking methods I am worried to use it in production

While parallel --single-transaction dumps on tables are possible, there are many things you must take under consideration:
InnoDB Buffer Pool
When you mysqldump a single table, the connection launches a START TRANSACTION followed by SELECT /*!40001 SQL_NO_CACHE */ ..... Naturally, this will push all data pages for the dumped table through the InnoDB Buffer Pool. Launching parallel dumps would just make the Buffer Pool become a funnel because of passing multiple tables through it. The combined time of each table dump may not necessarily be be less that dumping all the table sequentially if the combined size of the table is greater than innodb_buffer_pool_size.
Binary Log Position
When doing parallel dumps with --master-data on a DB Server with binary logging enabled, you will quickly realize that the dump of each table is not perfectly consistent. Why ? When launching a mysqldump of everything, lines 23-25 will contain a CHANGE MASTER TO command before the dump data/schema. Each dump will have a different master_log_pos, especially coming from a live system.
Locking
You had made for following statement

I tested this on a development server and works well but without knowing the internal locking methods I am worried to use it in production

Performing a single transaction dump does not trigger any kind of locking. According to the MySQL Documentation on --single-transaction:

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.
The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

SUGGESTION
I have recommended doing parallel dumps in the past (See my old post How can I optimize a mysqldump of a large database? under the heading Option 3 : mysqldump separate tables into separate data files)
If you want to launch parallel dumps of tables in Production, it is best to setup MySQL Replication. Then on the Backup Slave, you can do the following:

STOP SLAVE IO_THREAD;
Wait until the SQL Thread stops processing (SHOW SLAVE STATUS\G shows that Exec_Master_Log_Pos stops changing)
Perform parallel dumps
START SLAVE;

GIVE IT A TRY !!!


Subscriber database to be in sync using Distributor I have database D1 as a publisher, D2 as a subscriber and they are using Distributor 'DIST' which is reside on the subscriber side. Both D1 and D2 database having DR databases called DR1 and DR2. I have setup the Tran. Replication from D1 to D2, D1 to DR1 and D2 to DR2 as shown in below diagram:

When my production incoming traffic is on to database D1 and data is replicating to databases D2, DR1, and DR2. so obviously there might be some delay in data transferred to DR1, and DR2. so lets say a disaster happen and my D1, Dist, and D2 database server got crashed, in that case i have to divert my traffic to DR1 and i have to setup  Tran. replication between DR1 to DR2 (with Nosync option, as i don't want to reinitialize it) but because of delay in data transfer there might be data mismatch between DR1 and DR2.

is there any setting on distributor that can give me the surety that the data transfer to DR1 and DR2 be in same transaction. or is there any alternative ways to handle this problem ?
Note: i am using SQL SERVER 2008, Transactional Replication. I can not afford to re-initialize the replication between DR1 and DR2.

Why don't you setup your environment to replicate from DR1 to DR2 instead of from D2 to DR2. That way you don't have to re-initialize or -synch anything on failover. You just need to make sure that you fail over both D1 to DR1 and D2 to DR2 together.

If that is not an option, you could also look into clustering. if D1 and DR1 build one cluster C1 and D2 & DR2 make up cluster C2 you can setup replication from C1 to C2. In that setup a failover of either site won't affect replication.


Stored Procedure for Vacuum in PostgreSQL I want to create a stored procedure to trigger vacuum, I know there are many ways to automate vacuum, but I would like to use the stored procedure (Postgresql 9.6).

The problem Im facing is, I can't run the vacuum inside the BEGIN/END so there is a workaround, we can use set autocommit=on.

But when I add this autocommit inside the stored procedure, its throwing error.

Sample SP:

create procedure test(table_list varchar(1000))
LANGUAGE plpgsql
AS
$$
DECLARE
*** some values ***
BEGIN
EXECUTE 'set autocommit=on';
*** Rest of the command to get the table names one by one and vacuum in a loop***
END;
$$

Is this possible? Or any alternate way?

vacuum cannot run in a transaction and cannot run in a function or in a procedure.

You can try instead to generate the vacuum statements with the list of tables to be processed with psql CLI and \gexec internal command to run them.

With:

select format('vacuum %s.%s', schemaname, tablename)
from (select schemaname, tablename
     from pg_tables where tablename like 't%'
) lt;
\gexec

Execution says:

select format('vacuum %s.%s', schemaname, tablename)
from (select schemaname, tablename
     from pg_tables where tablename like 't%'
) lt;
           format
----------------------------
 vacuum public.ta
 vacuum private.t
 vacuum public.test
 vacuum s1.t
 vacuum s2.t
 vacuum public.tasks2020_04
 vacuum public.t1
 vacuum public.t2
 vacuum public.t0
 vacuum public.t3
 vacuum public.tts
 vacuum public.t0_0
 vacuum public.tasks
 vacuum public.tasks2020_05
 vacuum public.tasks2020_06
 vacuum public.tasks2020_07
 vacuum pub.t
 vacuum public.t
 vacuum public.tb
(19 rows)

select format('vacuum %s.%s', schemaname, tablename)
from (select schemaname, tablename
     from pg_tables where tablename like 't%'
) lt;
vacuum public.ta
VACUUM
vacuum private.t
VACUUM
vacuum public.test
VACUUM
vacuum s1.t
VACUUM
vacuum s2.t
VACUUM
vacuum public.tasks2020_04
VACUUM
vacuum public.t1
VACUUM
vacuum public.t2
VACUUM
vacuum public.t0
VACUUM
vacuum public.t3
VACUUM
vacuum public.tts
VACUUM
vacuum public.t0_0
VACUUM
vacuum public.tasks
VACUUM
vacuum public.tasks2020_05
VACUUM
vacuum public.tasks2020_06
VACUUM
vacuum public.tasks2020_07
VACUUM
vacuum pub.t
VACUUM
vacuum public.t
VACUUM
vacuum public.tb
VACUUM


Incremental backup MongoDB from oplog I was trying to take backup using oplog based on ts using below code

 import pymongo
    import traceback
    from pymongo.cursor import CursorType
    c = pymongo.MongoClient('mongodb://localhost:28001')
    oplog = c.local.oplog.rs
first = next(oplog.find().sort('$natural', pymongo.DESCENDING).limit(-1))
ts = first['ts']
while True:
    cursor = oplog.find({'ts': {'$gt': ts}}, cursor_type=CursorType.TAILABLE_AWAIT, oplog_replay=True)
    while cursor.alive:
        for doc in cursor:
            ts = doc['ts']
            print doc['ns']
            db,collection=str.split(str(doc['ns']),'.',1)

It is working well when we have less opLog size but as i am using it on production where oplog size is 48 GB around it is not working.

I cannot alter oplog size.Please suggest some way to take incremental backup.

Incremental backup procedure

lock writes on a secondary member
Dump oplog from the recorded oplog position on full (or latest incremental ) backup:

mongodump --host <secondary> -d local -c oplog.rs -o /mnt/mongo-test_backup/1
--query '{ "ts" : { $gt :  Timestamp(1437725201, 50) } }'

Record latest oplog position (same way as for full backups)
Unlock writes

Click Here for detailed information


MS SQL - Find Stored Procedure (Dynamic) Related to Table in Database I have stored procedures like this:

CREATE PROC spSample (@departmentId INT)
AS

DECLARE @q NVARCHAR(500)

SET @q = N'SELECT * FROM Employees WHERE 1=1'
IF @departmentId > 0
BEGIN
  SET @q = @q + ' AND DepartmentId = @departmentId'
END

EXECUTE sp_executesql @q, '@deparmentId INT', @departmentId

It is possible to find all related SPs like this one to the table "Employees"?

For dynamic SQLs, the only way to me is to search the definition of the stored procedure, like the following

use [your db]
select * from sys.sql_modules
where definition like '%Employee%'

-- if you know the pattern, you may use more restrictive way like
select * from sys.sql_modules
where definition like '% from Employee%'

But I have to say this may not be accurate. For example, if 'select * from Employee' is commented out in your SP, it will still be reported using this method.

Hope this helps.


Sql Server Restoring Process Time Takes Too Long Time I have 7 databases on my reporting server. I restore those databases every night with differetial backups. Normally, the differential restore time was taking about 1 or 1.5 hour for each databases. Last day, my restore process took so much long time. 4 of those databases has restored about 11 hours. The other one was completed normally. What can cause those databases to take such a long restoring time?

As long as you state that you did restore before and it took much less time I would quess that some of the following could be the reason:

some other process on the reporting server taking a lot of resources (in- and outside of SQL Server), that put high load on disks;
slow network connection between backups and reporting server if backups are located on a network share;
some process loading the disks with backups preventing fast reading of them by SQL Server.


Is an AWS SQL Server RDS "log capture" the same as a transaction log backup? When an SQL Server RDS instance provided by Amazon is configured for a recovery model using Full transaction logging, will the transaction log be trimmed by the "transaction log capture" that Amazon performs every 5 minutes, or is this capture not a true transaction log backup?

Thanks

Almost certainly it does trim the transaction log. (Otherwise the Amazon cloud will fill up with transaction logs.)

Some details from the Amazon site include this topic and two sub-topics that you can investigate: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

  When you create a DB instance, you are assigned to the db_owner role for all databases on that instance, and you will have all database-level permissions except for those that are used for backups (Amazon RDS manages backups for you).

For a discussion of the backup model and how you influence it, see:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.html

And, of course, if you change the model from FULL to SIMPLE, the retention period, and so forth that will affect your restore points.

Amazon definitely support point-in-time restores, which means that the transaction logs are being used.  But since they do this for all database server types that they support, I do not know the mechanics of what they use to do this.

On the restore process described here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html

  When you restore a DB instance that is using the SQL Server DB engine to a point in time, each database within that instance will be restored to a point in time within 1 second of each other database within the instance. Transactions that span multiple databases within the instance may be restored inconsistently.

So I would say that, whatever the mechanism of the log backup, it will be available within the constraints discussed in their documentation.


Is there a query to see which stored procedures are using a specific symmetric key? Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using:

OPEN SYMMETRIC KEY !@#$%^ WITH DECRYPTION BY CERTIFICATE !@#%^&**^ 'string'

While I don't know of a solution that solves your specific problem, you can search the raw source code of your stored procedures using the sys.sql_modules view. This, however, won't work with encrypted (i.e. WITH ENCRYPTION) procedures.

SELECT s.[name]+'.'+o.[name]
FROM sys.procedures AS o  -- or sys.objects for all objects
INNER JOIN sys.schemas AS s ON o.[schema_id]=s.[schema_id]
WHERE [object_id] IN (
    SELECT [object_id]
    FROM sys.sql_modules
    WHERE [definition] LIKE '%symmetric%' -- search criteria goes here
    );

Important: The query above will return all stored procedures containing the text string "symmetric", not just procedures that use symmetric encryption or keys. Remember that it will also match for instance text that is in comment blocks och quotes.


How do I check if a stored procedure is defined in MySQL When I do :

select routine_definition
from information_schema.routines
where routine_name = 'sp_name';

it returns a row, but the routine_definition column is NULL.  Does this mean that this Stored Procedure is not defined?

Also, when I do show create procedure sp_name, it says that procedure does not exit.

You should run these other queries
SHOW PROCEDURE STATUS WHERE name='sp_name';
SHOW FUNCTION STATUS WHERE name='sp_name';
SELECT type,db,body FROM mysql.proc WHERE name='sp_name'\G

If the procedure does not show up from any of these queries, my condolences.
There is one hope left: If you have a physical backup of the mysql.proc, do this in a Staging DB:

service mysql stop
Copy the backup of /var/lib/mysql into the Staging DB
chown -R mysql:mysql /var/lib/mysql
service mysql start

Once you do that, you can mysqldump all the stored procedures out of it with
MYSQLDUMP_OPTIONS="-uroot -p..."
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --no-data"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --no-create-info"
mysqldump ${MYSQLDUMP_OPTIONS} > MySQLStoredProcs.sql

Now, go vi or nano the file and get the Stored Procedure out of it.
Give it a Try !!!
UPDATE 2013-02-05 11:58 EDT
If you are really having problems with visually seeing the Stored Procedure's code, you may have a corrupt proc.MYD file. Try retrieving from backup as I already mentioned.


Session in a limbo state - Killed/Rollback I have a SQLjob that calls xp_cmdshell that is used to dump a txt file to an ftp site via a script.

At times it fails and the job gets hung up in an executable state. Generally I would kill the session and it gets cleared.

However, I have two sessions that are hung in a limbo state.

 sp_WHO2 53 -- running from 7/8/2018

SP_WHO2 98 -- running from 6/16/2018

The job status says its Runnable but the Command is KILLED/ROLLBACK.

Checking SPID 53

Kill 53 with status only

Output

SPID 53: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

Checking SPID 98

KILL 98 with status only

Output

SPID 98: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

How do I go about clearing these sessions?

I am having performance issues with the SQL Server, which led me to these sessions.
I did some research and points me to believe that it will increase the log files and cause performance issues. I am unaware if the current issue is because of these sessions but they are active expensive queries. So I thought of starting from here. I am developer here and I am aware that activity monitor is obsolete in SQL SERVER 2014 But i did check activity monitor and below are the images.

Can you suggest how I can go about it?

I did read a blog about such a scenario by Brent Ozar
- Rollback: What happens when you KILL a session?.

Learnt the hard way, looking into fixing the root cause.

AK

You could try to kill the windows process that was spawned with xp_cmdshell as that process is probably running and not handled by killing the spid from SQL Server. The way you describe it I would guess the ftp program is still running.

This is because the kill command doesn't kill programs that were started out of process.

You could simulate the scenario like this:

In a SQL Query window start a process:

xp_cmdshell 'notepad.exe'

and then after killing that spid I get the same output in sp_who2 as you

54      RUNNABLE                        Administrator   hostname      . master  KILLED/ROLLBACK     0   0   07/24 14:23:02  Microsoft SQL Server Management Studio - Query  54      0

But I still have a running notepad.exe process:

C:\Users\Administrator>tasklist | find "notepad"
notepad.exe                   4676 Services                   0      3 788 K

Once I kill that notepad.exe process my SQL Server spid is gone too.

If you want to find out what process you need to kill process explorer should show it as a child process of sqlserver.exe like this (you might need to start procexp.exe as administrator):


MySQL: My table size using a query does not match the IBD's file size on disk I have a table with about 600 million rows.  I ran the following commands and noticed

select table_schema "DB name (table_schema)",
    -> sum((data_length+index_length)/1024/1024/1024) AS "DB size in GB" from
    -> information_schema.tables group by table_schema;
+------------------------+-------------------+
| DB name (table_schema) | DB size in GB     |
+------------------------+-------------------+
| my_db                  | 4423.946937561025 |
+------------------------+-------------------+

The whole db is 4.4GB

The table with 600 million rows is:

+------------------------------------+-----------+
| Table                              | Size (MB) |
+------------------------------------+-----------+
| my_table                           |   3729084 |

So this table is 3.7TB in size.

I went to the file system and issued:

sudo du -hs * | sort -rh | head -5
11T my_table.ibd

My table is taking up 11TB in storage.

I ran the following command on the database:

SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

So is it normal for an innodb file to be that large?  If not, is there something that can be done to optimize its storage on disk?

I am running a master slave database:
sudo mysql --version
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

I am running this on an Ubuntu instance;
lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.5 LTS
Release:    16.04
Codename:   xenial

Be gentle with your answers as managing mysql is new to me.  I usually just write programs to read from mysql and haul the data to something like Elasticsearch or HBase for big data analytics.

Thank you...Amro

Of course, it never does.
In your case, you have about 66.67% fragmentation
I have wickedly good script you can run to calculate the fragmentation based on the difference between the .ibd filesize and the (data_length+index_length) of the any table:
MYSQL_AUTH="-h... -u... -p..."

DATADIR=`mysql -ANe"SELECT @@global.datadir"`

TAG_LIST_FILE=/tmp/list_of_tables.txt
TAG_LIST_ERRS=/tmp/list_of_tables.err
DAT_LIST_FILE=/tmp/list_of_databases.txt
FULL_RPT_FILE=Full_FragReport.txt

MB=${1}

if [ "${MB}" == "" ] ; then exit ; fi

NUM_OK=0
if [ "${MB}" ==  "128" ] ; then NUM_OK=1 ; fi
if [ "${MB}" ==  "256" ] ; then NUM_OK=1 ; fi
if [ "${MB}" ==  "512" ] ; then NUM_OK=1 ; fi
if [ "${MB}" == "1024" ] ; then NUM_OK=1 ; fi
if [ "${MB}" == "2048" ] ; then NUM_OK=1 ; fi
if [ ${NUM_OK} -eq 0 ] ; then exit ; fi

MINIMUM_SIZE="(1024 * 1024 * ${MB})"
(( MIN_SIZE = MB * 1048576 ))

SQL="SELECT CONCAT(table_schema,':',table_name,':',data_length+index_length,':',data_free) FROM information_schema.tables"
SQL="${SQL} WHERE table_schema NOT IN ('information_schema','performance_schema','mysql') AND engine IN ('InnoDB','MyISAM')"
SQL="${SQL} AND data_length+index_length > ${MIN_SIZE}"
mysql ${MYSQL_AUTH} -ANe"${SQL}" >${TAG_LIST_FILE} 2>${TAG_LIST_ERRS}
TAG_FILESIZE=`ls -l ${TAG_LIST_FILE} | awk '{print $5}'`
ERR_FILESIZE=`ls -l ${TAG_LIST_ERRS} | awk '{print $5}'`
if [ ${ERR_FILESIZE} -gt 0 ]
then
        echo "Errors"
        echo "======"
        cat ${ERR_FILESIZE}
        echo
        exit
fi
if [ ${TAG_FILESIZE} -eq 0 ]
then
        echo "No Databases with Tables > ${MB}M (${MIN_SIZE})"
        exit
fi

SPC=""
TAGS=""
for TAG in `cat ${TAG_LIST_FILE}`
do
        NEWTAG=${TAG}
        DB=`echo ${TAG} | sed 's/:/ /g' | awk '{print $1}'`
        TB=`echo ${TAG} | sed 's/:/ /g' | awk '{print $2}'`
        IS=`echo ${TAG} | sed 's/:/ /g' | awk '{print $3}'`
        FR=`echo ${TAG} | sed 's/:/ /g' | awk '{print $4}'`
        IBD=${DATADIR}/${DB}/${TB}.ibd
        MYD=${DATADIR}/${DB}/${TB}.MYD
        MYI=${DATADIR}/${DB}/${TB}.MYI
        if [ -f ${IBD} ]
        then
                (( N1 = IS + FR ))
                N2=`ls -l ${IBD} | awk '{print $5}'`
                if [ ${N1} -lt ${N2} ]
                then
                        (( DF = N2 - N1 ))
                        NEWTAG="${DB}:${TB}:${IS}:${DF}"
                fi
        else
                ND=`ls -l ${MYD} | awk '{print $5}'`
                NI=`ls -l ${MYI} | awk '{print $5}'`
                (( N1 = IS + FR ))
                (( N2 = ND + NI ))
                if [ ${N1} -lt ${N2} ]
                then
                        (( DF = N2 - N1 ))
                        NEWTAG="${DB}:${TB}:${IS}:${DF}"
                fi
        fi
        TAGS="${TAGS}${SPC}${NEWTAG}"
        SPC=" "
done

# Collect List of DBs
DBLIST=""
SPC=""
for DB in `cat ${TAG_LIST_FILE} | sed 's/:/ /' | awk '{print $1}' | sort -u`
do
        DBLIST="${DBLIST}${SPC}${DB}"
        SPC=" "
done

for D2 in ${DBLIST}
do
        SUM=0
        SUM_DF=0
        SQL=""
        SELECT="SELECT"
        for TAG in ${TAGS}
        do
                DB=`echo ${TAG} | sed 's/:/ /g' | awk '{print $1}'`
                if [ "${DB}" == "${D2}" ]
                then
                        TB=`echo ${TAG} | sed 's/:/ /g' | awk '{print $2}'`
                        IS=`echo ${TAG} | sed 's/:/ /g' | awk '{print $3}'`
                        FR=`echo ${TAG} | sed 's/:/ /g' | awk '{print $4}'`
                        (( OS = IS + FR ))
                        SQL="${SQL} ${SELECT} '${TB}' TableName,LPAD(FORMAT(${OS},0),18,' ') FileSize,LPAD(FORMAT(${OS}/POWER(1024,3),2),10,' ') FileSizeGB,LPAD(FORMAT(${IS},0),18,' ') TableSize,LPAD(FORMAT(${IS}/POWER(1024,3),2),11,' ') TableSizeGB,LPAD(FORMAT(${FR},0),18,' ') Difference,LPAD(FORMAT(${FR}/POWER(1024,3),2),12,' ') DifferenceGB,LPAD(FORMAT(${FR}*100.0/${OS},2),7,' ') FragPct"
                        SELECT="UNION SELECT"
                        (( SUM    += OS ))
                        (( SUM_DF += FR ))
                fi
        done
        FRAG_RPT=FragReport_${D2}.txt
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "Fragmentation for Database ${D2} (${DT})" > ${FRAG_RPT}
        echo "Minimum Table Size (${MB} MB = ${MIN_SIZE})" >> ${FRAG_RPT}
        echo >> ${FRAG_RPT}
        BIGSQL="SELECT * FROM (${SQL}) A ORDER BY Difference DESC;"
        BIGSQL="${BIGSQL} SELECT FORMAT(${SUM}/POWER(1024,3),2) DiskSpaceUsed,FORMAT(${SUM_DF}/POWER(1024,3),2) SumDiff;"
        mysql ${MYSQL_AUTH} --table -Ae"${BIGSQL}" 2>&1 >> ${FRAG_RPT}
done

rm -f ${FULL_RPT_FILE}
for TXT in `ls FragReport*.txt`
do
        if [ -f ${FULL_RPT_FILE} ]
        then
                echo -e "\n================================================================================\n" >> ${FULL_RPT_FILE}
        fi
        cat ${TXT} >> ${FULL_RPT_FILE}
done
less ${FULL_RPT_FILE}

You will have too run this script as the OS root user or another OS that has rights to read the datadir
GIVE IT A TRY !!!
I wrote How to monitor mysql percentage disk utilization? like 6 years ago based on the same principles


Using dynamic date filter causes data not to return Sorry for the cryptic title, as I'm not sure how to state this question simply.

The problem is this, I have a date column, called DTDATE, the data stored in it is of DATE type. When I filter against this column using a static string such as '03/APR/17' queries return a result. Perfect.

However, I want to create a dynamic query that will return the first day in the week (Monday in this instance) and the same day last year (e.g. the Monday of this week last year). That is, comparing two sets of data year to date (YTD).

The filter would look something like this:

      DTDATE = (SYSDATE - (TO_CHAR(SYSDATE,'d')-1))
  OR
      DTDATE = (add_months(SYSDATE,-12)-(TO_CHAR(add_months(SYSDATE,-12),'d')-1))

The problem is, the query returns no result!

IF, however, I change the query to the following, I get a result set:

      DTDATE = (SELECT SYSDATE - (TO_CHAR(SYSDATE,'d')-1) FROM DUAL)
  OR
      DTDATE = (SELECT add_months(SYSDATE,-12)-(TO_CHAR(add_months(SYSDATE,-12),'d')-1) FROM DUAL)

You will notice I am now specifically selecting the date for each row of data returned in the filter. While this works, the cardinality of the data set goes from 14,475 in a static filter (e.g. date stated in a string), to 2,387,764!

....That's a bit of an issue for run time XD

So, my question is WHY wont the query run without selecting into DUAL?
I can use SYSDATE by itself as a filter (tested).

Notes about the data: The data set is from an MIS, that loads data on a weekly basis each Monday. As such, datasets are only present from a tag date of Monday of each given week. Hence the date requirement of Monday of the given week.

Note I am using Oracle Developer 4.1.5.21 on an 11g server (we update to 12c later this month).

Okay, figured it out, leaving this here in case anyone else comes across a similar issue.

For some reason Oracle will not compare a DATE data type to a SYSDATE when it has been place through a function such as:

SYSDATE - (TO_CHAR(SYSDATE,'d')-1)

To overcome this limitation, I used the NEXT_DAY() and TO_DATE() functions to build the following query:

TO_DATE(NEXT_DAY(SYSDATE-7,'Monday'),'DD/MON/YY')

Using this method, the below filter returns a result set, and has a low cardinality only slightly higher than a static filter.

      PIP.DTDATE = TO_DATE(NEXT_DAY(SYSDATE-7,'Monday'),'DD/MON/YY')
  OR
      PIP.DTDATE = TO_DATE(NEXT_DAY(SYSDATE-371,'Monday'),'DD/MON/YY')

If anyone has any comments about WHY oracle requires you to use TO_DATE(), please extrapolate, because this confused the daylights outta me XD


Can I drop an overlapping index if the larger index is unique I have 2 nonclustered indexes:

IX1 = [col1]
IX2 = [col1],[col2] (unique).

I want to drop the IX1 index as the optimizer can stop use IX2 for the first column and stop using when it has satisfied the query in question, so should not hurt performance. But I had a nagging thought in my head, it might not be able to use the 2nd index if it was marked as unique?

Both columns are integers datatype, col1 is a FK

Yes, it should be safe to remove the IX1 as IX2 is already covering col1. If IX2 was defined in reverse order i.e. col2,col1 then scenario would be a bit different as first column in the index is of utmost importance. Moreover IX2 is a unique constraint which means combination of col1 and col2 will be unique in the table so, you should be careful for dropping or modifying this kind of index.

Indexes help in select however slows down inserts/updated and deletes. If you have multiple indexes with same set of column that means you have that many copy of those columns and will slow down any DML operation as optimizer has to modify all the copies of columns in all these places.

Preferred solution for indexes is to have less number of indexes with higher number of columns than more number of indexes with less columns(Ideally speaking however there are many exceptions).

Same is described by SQL Server SME Mr. Brent Ozar as below:

  I prefer to drop that first index and let the slightly bigger index
  pick up the slack.  Reading a slightly larger index will take slightly
  more time: if a query didn’t need that  field, it still has to pull it
  off the disk in order to perform the query.  However, dropping the
  index pays off during inserts/updates/deletes, because it’s one less
  index SQL Server has to manage.  It also makes the database smaller,
  thereby making database maintenance tasks smaller/faster.

  If:

  I have two indexes with the exact same fields in the same order, but
  One has 1-2 extra fields, and There aren’t include fields, or the
  include fields are the same Then I’ll drop the shorter index with
  extreme prejudice.

You may check below links also for more details:

https://blog.sqlauthority.com/2015/03/26/sql-server-mistake-to-avoid-duplicate-and-overlapping-indexes-notes-from-the-field-073/

https://www.sqlservercentral.com/articles/finding-and-eliminating-duplicate-or-overlapping-indexes-1

https://stackoverflow.com/questions/30604471/sql-index-is-this-overlapping

Hope above helps.


Deconstruct json and insert/update it into a different table in an updatable view I have a view that essentially presents a denormalized view onto my data.

It's roughly like having a blog table and a comments table, and the view then shows a blog post per row, with all the comments belonging to that post in an array in a json field.

The view uses the following code to achieve that:

  (SELECT array_to_json(array_agg(row_to_json(x.*))) AS array_to_json  FROM
     (SELECT foo, bar
      FROM table_b
      WHERE a.id = table_b.abc_id) x) AS baz

I've made the view updatable, which works for everything except this kind of column. I also have some typical many-to-many relationships in that data, and those are automatically updated when you edit them via the view. But I couldn't figure out how to do the same for this pseudo-json column.

Essentially, I need to pass this json to an update/insert statement in my INSTEAD OF trigger. I experimented with json_to_recordset, but I couldn't get that to do what I need.

Any idea how I could achieve this?

From the docs on Updatable Views

  Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements to be used on the view in the same way as on a regular table. A view is automatically updatable if it satisfies all of the following conditions:

  The view must have exactly one entry in its FROM list, which must be a table or another updatable view.
  The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
  The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.
  The view's select list must not contain any aggregates, window functions or set-returning functions.

Your view is twice-over not "automatically" updatable. However,

  You can get the effect of an updatable view by creating INSTEAD OF triggers on the view, which must convert attempted inserts, etc. on the view into appropriate actions on other tables. For more information see CREATE TRIGGER. Another possibility is to create rules (see CREATE RULE), but in practice triggers are easier to understand and use correctly.

I strongly suggest you don't go down that route though and you consider fixing your schema and moving away from jsonb. It's not made to be used in that fashion.

Here is how you can think of the problem, JSON isn't for presentation. You're storing your data in a schema. You're rendering it into JSON and obscuring  that schema entirely. Going back to the schema from JSON is going to require you to create a translation layer as complex as the whole database itself. Instead,

make the queries on the underlying data in your application. Make the JSON in the application when you need to send it to the client. And, make the updates on the underlying schema like you would normally do.
or, use something like PostgREST which defaults to sending out JSON when you write the base query using its API. This method does not collapse your schema. Then it provides you the ability to write against that as well (through other API calls).

Shy of all of that, to proceed start by creating your stored proc, and then set it as a trigger..

CREATE TRIGGER test_trigger
INSTEAD OF INSERT OR UPDATE
    ON test_view
    FOR EACH ROW
    EXECUTE PROCEDURE test_trigger_function();


Is it acceptable to use the presence of a value in a NULLable column as a flag? I have a stored procedure that processes rows in a table.  The stored procedure is called by an external process (Jitterbit, though I don't believe that is relevant).  Occasionally the stored procedure throws an exception wchich is caught in a TRY CATCH block.  My client wants this to work slightly differently; they want the error to be written back to the table (e.g. create a new column for the error information) which can then be periodically aggregated into a notification email.  There will be another bit column (DEFAULT 0) that will be set to 1 when an error in that row is included in a notification email (so that the email is sent only once).

My question is this.  If I make the column for the error message NULLable, and therefore do something like:

SELECT * FROM MyTable WHERE ErrorMessage IS NOT NULL AND EmailSent = 0;

is this an acceptable approach - to use the presence (or absence) of data in a column as a flag?  My inner pedant thinks that another BIT column ought to be used - for example:

SELECT * FROM MyTable WHERE ErrorCreated = 1 AND EmailSent = 0;

Am I being pedantic?

NULL is not a value of the column but an internal metadata flag showing the column has no value.  So duplicating this in user space by adding another flag column is a waste. NULL is a well-supported part of the relational model. Use it for its intended purpose.


Update row where associated row does not exist Given the table named ROLE:

role_id[PK] NUMBER
role_name   VARCHAR2

Using only SQL (no PL/SQL) how would I write a MERGE (or UPDATE?) statement that would update the role_name as below, but only for rows where another row with the new role_name does not already exist? (By "new role_name" I mean 'prefix-' || role_name)

UPDATE role
SET role_name = 'prefix-' || role_name
WHERE role_name LIKE 'com.example.%';

Add the predicate:

 AND NOT EXISTS (
 SELECT 1 FROM role AS r_existing
 WHERE r_existing.role_name = 'prefix-' || role.role_name)


Is there a way to truncate the microseconds of a CSV value when importing data using LOAD DATA INFILE? I'm loading data using LOAD DATA INFILE in 5.5 where the value includes microseconds. I must use DATETIME data type. Currently, I'm using regular expressions to remove them from the CSV prior to import. I'm wondering if there's an option in MySQL to truncate them so it only loads YYYY-MM-DD HH:MM:SS?

Thanks

You can use variables to edit your input data for example.

  LOAD DATA LOCAL INFILE 'file name ' INTO TABLE table (@var1) SET
  Date=str_to_date(@var1,'%Y-%m-%d')

You can use this method for a lot of different stuff like loading only the 3 first char of a string or doing some mathematical funcion etc.

Example

  LOAD DATA LOCAL INFILE 'file name' INTO TABLE table (@var1) SET
  Date=substring(@var1,1,3).

Hope this helps


Do stored procedures have a "signature", and how is that defined? I have a stored procedure which is called by a third-party tool. I believe it to be using ODBC, but it is essentially a black box. The DBMS is SQL Server 2012.

When the stored procedure is a simple select, the third-party tool receives the result set, but when it accesses a temporary table first, it does not.  For example, the following two stored procedures return the same result from SSMS, but only the first can be called successfully from the tool.

What I don't understand is how these two functions are different from an external perspective. They both take the same parameters, and the last query yields the same result.

Where is the stored procedure executed?

--This one works when called both ways
CREATE PROC [dbo].[sp_GetUsers]
AS
    Select Top 1000 userId
    from Users

--This only works from SSMS.
CREATE PROC [dbo].[sp_ComplicatedGetUsers]
AS

    If OBJECT_ID('tempdb..#TMPUsers') IS NOT NULL
    BEGIN
        DROP TABLE #TMPUsers
    END

    Select Top 1000 userId
    INTO #TMPUsers
    from Users

    Select * from #TMPUsers

Your 'tool', whatever it is, is not worth using. It does not follow the TDS protocol. Basically what happens it gets back a result from the server and stops short when parsing it, assuming the first entry is also the last entry. Whatever this tool does, I'm sure there is a decent replacement out there.

As to answer your question: the two procedures have different results, they are not identical from outside. The second incarnation creates additional 'results', in the form of the 'xxx rows affected' messages from the SELECT ... INTO... statement. This breaks your tool. NHibernate is notorious for similar problems, it breaks if the 'xx rows affected' is missing after an update (ie. if you do the update using a stored procedure that has SET NOCOUNT ON).

Pass along to the tool developers that they need to read the ODBC specifications about Processing Results:

  Each INSERT, UPDATE, and DELETE statement returns a result set containing only the number of rows affected by the modification. This count is made available when application calls SQLRowCount. ODBC 3.x applications must either call SQLRowCount to retrieve the result set or SQLMoreResults to cancel it. When an application executes a batch or stored procedure containing multiple INSERT, UPDATE, or DELETE statements, the result set from each modification statement must be processed using SQLRowCount or cancelled using SQLMoreResults. These counts can be cancelled by including a SET NOCOUNT ON statement in the batch or stored procedure.


Choosing sort and distkeys for redshift with multiple joins I have a relatively large Redshift cluster which I am attempting to optimize via the selection of sort and dist keys. I have a recurring situation where multiple tables are joined together via an intermediary join. The join is relatively stable among all queries. I know that you're supposed to distribute on the field that you join to and sort on the conditions in the where clause. However, in this situation there is no where clause and it is joined twice. For example I have three tables:

users -> accounts -> subscriptions. These are in a 1:1:1 relationship. The joins are as follows:

users.id = accounts.user_id
accounts.id = subscriptions.accounts_id.

What is the optimal way to set the sort and dist keys on the accounts table in this situation?

The ideal situation is if you can have a user_id column added onto subscriptions, then you can distribute all three by user_id. Then your joins would be written as:

FROM subscriptions
LEFT JOIN accounts
  ON  accounts.id = subscriptions.account_id
  AND accounts.user_id = subscriptions.user_id --Note this add'l condition
LEFT JOIN users
  ON  users.id=accounts.user_id

The one caveat here is that your number of users should be large enough to ensure that this distribution key does not result in significant skew.

As a bonus, if you do not frequently filter these, you can choose the same column (user_id) as your sort key to enable merge joins rather than hash joins

If you can't add this column, then you are going to have network distribution happening in one of your two joins :-( Pick the one that you want based on frequency of that join and average width of rows participating in that join.


creating a table from a selection and then using group by - oracle sql I'm working in oracle and I can't figure out what's wrong with this statement.  The error it gives me is must name this expression with a column alias

CREATE TABLE shipping_costs_grouped AS
    SELECT SUM(invoice_amt), SUM(entered_weight), SUM(billed_weight), SUM(net_amt), MAX(ZONE), pps_num
    FROM shipping_costs GROUP BY pps_num;

I know it has something to do with the group by statement but I don't know how to resolve it

You need to add aliases for the computed columns so that Oracle knows what names the columns of the table should have

CREATE TABLE shipping_costs_grouped
AS
    SELECT SUM(invoice_amt) total_invoice_amt,
           SUM(entered_weight) total_entered_weight,
           SUM(billed_weight) total_billed_weight,
           SUM(net_amt) total_net_amt,
           MAX(ZONE) max_zone,
           pps_num
      FROM shipping_costs
     GROUP BY pps_num;

should be valid syntax.

Whether it is actually appropriate to create a table for this sort of thing is a question you'd have to answer.  My bias would be that it would be more appropriate to create a view or potentially a materialized view but I don't know the exact nature of the problem you're trying to solve.


Postgres: Insert result of join as an array? Here's what I have:

=# SELECT * FROM users;
 id |     name
----+---------------
  1 | Paul Gilbert
  2 | Bob Dylan
  3 | Cliff Downing

=# SELECT * FROM regs;
 reg_id | user_id
--------+---------
      1 |       1
     11 |       1
    111 |       1
      2 |       2
     22 |       2
      3 |       3

Here's what I want to obtain:

=# SELECT * FROM joined;
 id |     name      |       regs
----+---------------+-------------------
  1 | Paul Gilbert  | {1, 11, 111}
  2 | Bob Dylan     | {2, 22}
  3 | Cliff Downing | {3}

MWE:

-- create users table:
DROP TABLE users;
CREATE TABLE users(
   id    INT      PRIMARY KEY  NOT NULL,
   name  VARCHAR               NOT NULL
);
-- populate users table:
INSERT INTO users (id, name)
VALUES (1, 'Paul Gilbert'),
       (2, 'Bob Dylan'),
       (3, 'Cliff Downing');

-- reg table:
DROP TABLE regs;
CREATE TABLE regs(
   reg_id   INT  PRIMARY KEY  NOT NULL,
   user_id  INT               NOT NULL
);
-- populate reg table:
INSERT INTO regs (reg_id, user_id)
VALUES (1,   1),
       (11,  1),
       (111, 1),
       (2,   2),
       (22,  2),
       (3,   3);

-- joined table:
DROP TABLE joined;
CREATE TABLE joined(
    id    INT,
    name  VARCHAR,
    regs  INT[]
);

Here's my effort so far:

BEGIN;
  INSERT INTO joined (id, name)
       SELECT id, name
         FROM users;

  INSERT INTO joined (regs)
       SELECT ARRAY(
                   SELECT reg_id
                     FROM regs
               INNER JOIN joined
                       ON joined.id = regs.user_id
                   );
COMMIT;

It doesn't work:

 id |     name      |               regs
----+---------------+-----------------------------------
  1 | Paul Gilbert  |
  2 | Bob Dylan     |
  3 | Cliff Downing |
    |               | {1,11,111,2,22,3}

Edit

Solution by a_horse_with_no_name works great.

Do you think there's a way to add yet another join in a single expression?

CREATE TABLE ldap.univer_users(
  first_name  VARCHAR,
  middle_name VARCHAR,
  last_name   VARCHAR,
  code        VARCHAR,
  doc_number  VARCHAR,
  reg_numbers VARCHAR[]
);

BEGIN;
    INSERT INTO ldap.univer_users (first_name, middle_name, last_name, code, reg_numbers)
    SELECT
        d."firstName", d."middleName", d."lastName", d.uid, array_agg(r."regNumber")
    FROM
        student_data d
        JOIN student_reg_numbers r ON d.uid = r.student_datum_uid
    GROUP BY d.uid;

    UPDATE ldap.univer_users
    SET doc_number = p.doc_number
    FROM (
            SELECT student_datum_uid, substring(number FROM '....$') AS doc_number
            FROM student_passports
            WHERE "passportType" IN ('Паспорт РФ', 'Паспорт иностранного гражданина')
         ) p
    WHERE code = p.student_datum_uid;
COMMIT;

This works with my data, but I'm curious is there a way to put it into a single insert statement.

Edit 2

Trying to unite all in a single expression:

INSERT INTO ldap.univer_users (first_name, middle_name, last_name, code, doc_number, reg_numbers)
SELECT
    d."firstName", d."middleName", d."lastName", d.uid, substring(p.number FROM '....$'), array_agg(r."regNumber")
FROM
    student_data d
    JOIN student_reg_numbers r ON d.uid = r.student_datum_uid
    JOIN student_passports   p ON d.uid = p.student_datum_uid
      AND p."passportType" IN ('Паспорт РФ', 'Паспорт иностранного гражданина')
GROUP BY d.uid;

Postgres says

column "p.number" must appear in the GROUP BY clause or be used in an aggregate function

You need to use a single insert statement:

INSERT INTO joined (id, name, regs)
SELECT u.id, u.name, array_agg(reg_id)
FROM users u
  JOIN regs r ON r.user_id = u.id
GROUP BY u.id, u.name;

Your two step approach requires an update as the second step, not an insert:

INSERT INTO joined (id, name)
SELECT id, name
FROM users;

update joined
   set regs = t.regs
from (
  select user_id, array_agg(reg_id) as regs
  from regs
  group by user_id
) t
where t.user_id = joined.id;


Windows cluster failed over, but SQL instances didn't move I recently inherited a SQL Cluster (2008R2) which for the most part behaves itself impeccably.

The windows cluster is made up of two nodes running Active/Passive, Node1 and Node2 are dedicated blades in two different data centers. There are 3 SQL instances all running on Node1. Quorum is established by a File Share Witness and we have a heartbeat between the two nodes.

The other day someone switched off the file share witness by mistake, and the windows server failed over from Node1 to Node2. Or should I say, in Failover Cluster Manager, Node2 was now specified as the active node by Windows.

However, the SQL Cluster didn't do anything. All the instances stayed up and hosted on Node1. I would have expected them to move Nodes, but no.

There was no adverse affect on the databases at all.

Once power was resumed to the File Share Witness I brought it online again and the Windows Cluster failed back to Node1.

Our Windows Technicians are looking into why the cluster failed over, and I'm left scratching my head with the SQL bit.

All I can think of is that the heartbeat kept the SQL instances on Node1 and losing the witness wasn't important.

I'm still learning the small details of Windows Clustering, being much more used to Log Shippping and Mirroring when it comes to HA solutions, so any insight into why the SQL Instances didn't failover would be appreciated.

Clustering is complex, and there are lots of moving parts (no pun intended). Let me try to break this down into more manageable chunks:

From a terminology perspective, there's your Windows Server Failover Cluster (WSFC), and your SQL Server Failover Cluster Instances (FCI). I try to avoid saying "Cluster" and use these acronyms to avoid ambiguity.

Quorum:
The quorum is the number of votes necessary to transact business on your WSFC. Depending on your WSFC configuration, voters can be nodes (servers), a drive, or a file share. You need more than 50% of your votes in order for the WSFC to be online. If you lose 50% or more of your voters, then the WSFC and all clustered services (including your FCI) will go offline and not come back until you have (or force) quorum.

In your configuration, you have two nodes, and one file share for a total of three votes. Any one of those voters can go offline. When you lost the file share, you still had two nodes online, so your WSFC and all clustered services stayed online.

Cluster Owner/Host Server:
When you say that "Node2 was now specified as the active node by Windows", I suspect you are referring to the "Current Host Server" for the cluster. So what is that?

Your WSFC has a network name and an IP address. That name & IP has to be tied to a machine that is part of your cluster. More specifically, it can be tied to any one machine in your cluster. This is part of your WSFC, but not your FCI.

In your scenario, you have three FCIs on a two-node WSFC. It would be a perfectly valid to have one FCI on Node1, and two FCIs on Node2. And the "Current Host Server" for the WSFC could be either node. SQL Server won't care.

So what happened: As you said, there were no adverse effects on the databases. I'd expect that, because SQL Server isn't tied to that WSFC host server. I don't think I wouldn't have expected the host server to move when the file share failed--but I'd let your Windows guys dig into that more. From a SQL perspective, everything worked as expected.


Query a database for empty tables In my system there are two tables called ‘faculty’ and ‘faculty_assets’.

I need to delete faculty by faculty from the ‘faculty’ table. But system should allow deleting the faculty when only faculty_assets table empty. Otherwise It should not allows to delete the faculties from ‘faculty’ table.

Can anyone modify below sql query?  (‘fac_id’ is the primary key of ‘faculty’ table )

function removeFaculty($fac_id){
    $conn=new connection();
    $sql="delete from faculty where fac_id='$fac_id'";
    $result=$conn->query($sql);
    return $result;

}

Assuming faculty_assets and faculty are joined by the fac_id field, you could use something like

$sql="DELETE FROM faculty WHERE fac_id='" . (int)$fac_id . "' AND NOT EXISTS
        (SELECT * FROM faculty_assets WHERE fac_id = '".(int)$fac_id . "')";


DB2 Naming Standards - Similar to OFA I'm an Oracle DBA getting started on DB2.

On Oracle we have a document called Optimal Flexible Architecture that sets a group of naming standards, like what should be the disk name for the database. This is a great because if everyone follows the document you can easily administer new databases without suffering.

I've installed a DB2 (next next finish way) and I noticed that the database is created on /home/db2inst1 is this ok?

I was wondering if IBM provides some guide for that.

Since this is easier to elaborate on in an answer than in a comment.....

We tended to install our instances to /inst/<instance-name>. I leave the instance naming scheme up to you. If you make it too similar than technically you have introduced a security risk as the instance names are eaiser to guess.

Then you could so something like the following:

/inst/<instance-name>/bkp
/inst/<instance-name>/offline
/inst/<instance-name>/online
/inst/<instance-name>/data
/inst/<instance-name>/dbdir
/inst/<instance-name>/logs
/inst/<instance-name>/archlogs

You can work with your server admins or your SAN admins or do the work yourself to decide which of these mounts is physically separate disk, LUNs, etc.

This is just something we did. We did it to provide separation of concerns of where the instance was installed, vs where the system tables vs the data tables, vs the logs, vs the archive logs, vs the backups. Whether or not it works for your organization, you will have to discuss.

Whatever you do, be consistent as much as is possible.


Getting parsable deadlock XML from SQL Server error log I need to be able to grab deadlocks from the SQL Server log file (for various reasons, cannot use Extended events (and that includes the default system health extended event) or WMI)

I have enabled the 1222 trace flag and get the deadlocks in the file.

However, SQL Server is stripping out all of the XML chevrons, which makes parsing the trace flag much more difficult and error-prone!

Does anyone know if it's possible to get SQL Server to log the actual XML into the log file so I can load it into an XML parser?

Trace flag 1222 will

  Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

For the limitations (cannot use - Extended events (and that includes the default system health extended event) or WMI event notifications (script that I wrote to log deadlock info to a table)) that is the max you can get.

Remember that system_health session is running by default.


Is there a way to enable MariaDB MEMORY storage engine for every table? I am new to MariaDB and interested in using it because I want to use MEMORY storage engine for every table. I read MEMORY storage engine on mariadb.com and it seems like I had to specify storage engine at the creation time.

What I want is to run a single command that enable MEMORY storage engine to every table on an instance. Is there anyway I can achieve that?

There are two situations you must address

SITUATION #1 : TABLES TO CREATE

You must add this to my.cnf

[mysqld]
default-storage-engine=MEMORY

Then, restart mysql (MariaDB)

CAVEAT: This will not convert already existing tables to MEMORY tables.

What can done for those existing tables ?

SITUATION #2 : TABLES ALREADY EXISTING

Here is the script to convert all tables to MEMORY tables:

SQLSCRIPT=/root/Convert_To_MEMORY.sql
echo "SET SQL_LOG_BIN = 0;" >
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MEMORY;')"
SQL="${SQL} ConversionSQL FROM information_schema.tables WHERE engine='InnoDB'"
SQL="${SQL} AND table_schema NOT IN ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -AN -e"${SQL}" > ${SQLSCRIPT}
less ${SQLSCRIPT}

If you are satistied with the contents of the SQL Script, login to MariaDB and run

mysql> source /root/Convert_To_MEMORY.sql

EPILOGUE

While I gave you the two major things to do to have everything as MEMORY tables, you should read my old posts on the good, the bad, and the ugly of using MEMORY tables:

May 22, 2011 : I am using the MEMORY storage engine but MySQL still writes to my disk...Why?
Sep 26, 2011 : Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?
Jan 17, 2012 : Mysql Memory table getting many locks
Jan 21, 2012 : Is InnoDB Engine up to speed against Memory Engine?
Jan 30, 2012 : How much memory will a MEMORY table take up?


MYSQL Calculate ranking of row that is not yet inserted I've got a table similar to this data:

|     id    | username | value |
|-----------|----------|-------|
| 1         | bob      | 46    |
| 483       | alice    | 90    |
| 176       | sue      | 3001  |
| 82        | harry    | 0     |
| 10493     | bill     | 327   |

I have this query that returns me the ranking of a user based on their id

SELECT
    username,
    value,
    rank from
    (
        SELECT
        tp.username,
        tp.value,
        tp.id,
        @curRank := @curRank + 1 AS rank
        FROM table tp,
        (SELECT @curRank := 0) r
        ORDER BY tp.value DESC
    )
as temp
WHERE id = 483;

So for the above query, I would get a ranking returned of 4 for the id 483.

Let's say I want to insert the following row into the table:

|   id   | username | value |
|---------------------------|
|    2   |  sally   | 2000  |

Is there any way to know what rank this row will have after it is inserted, without actually inserting it?

I.e. sally would have a rank of 2 from the above query if inserted.

The reason I am curious if this is possible is that I'm attempting to insert the row into the database and only have this one transaction, rather than having to insert the row, then re-run the rank query.

Thanks!

BEGIN;
INSERT the row
calculate the rank; put it into an app variable
ROLLBACK;

The ROLLBACK lets you undo the INSERT.  But meanwhile, you have grabbed the rank.


After ID is updated, get a value from another table I have a roads table:

+---------+---------+
| ROAD_ID | MEASURE |
+---------+---------+
|       1 |    1000 |
|       2 |     100 |
|       3 |     500 |
|       4 |    9000 |
+---------+---------+

And I have a related linear referencing table called projects:

+------------+---------+--------------+------------+
| PROJECT_ID | ROAD_ID | FROM_MEASURE | TO_MEASURE |
+------------+---------+--------------+------------+
|        101 |       1 |            0 |         50 |
|        102 |       1 |          100 |        500 |
+------------+---------+--------------+------------+
|        103 |       2 |            0 |        100 |
+------------+---------+--------------+------------+
|        104 |       3 |          100 |        200 |
|        105 |       3 |          400 |        500 |
+------------+---------+--------------+------------+

Users manually enter projects into application's attribute editing environment (more here).

The workflow for entering new projects is:

The user creates a blank new record.
The FROM_MEASURE has a default value of 0.
The user enters the ROAD_ID manually.
The user enters the TO_MEASURE manually.

You can't tell from the sample table, but 9 times out of 10, projects pertain to an entire road (not just a portion of the road). An example is PROJECT #103.

Rather than make the user manually enter the TO_MEASURE when imputing new projects, would it be possible to get these values automatically once the ROAD_ID is entered?

Example:

The user creates a blank new record.
The FROM_MEASURE has a default value of 0.

+------------+---------+--------------+------------+
| PROJECT_ID | ROAD_ID | FROM_MEASURE | TO_MEASURE |
+------------+---------+--------------+------------+
|        106 |         |            0 |            |
+------------+---------+--------------+------------+

The user enters the ROAD_ID manually.
The TO_MEASURE is automatically retrieved:

+------------+---------+--------------+------------+
| PROJECT_ID | ROAD_ID | FROM_MEASURE | TO_MEASURE |
+------------+---------+--------------+------------+
|        106 |       4 |            0 |      9000  | <-- Magic!
+------------+---------+--------------+------------+

After ROAD_ID is updated, automatically get the related road's MEASURE value as the TO_MEASURE.

Doing this in the database is easy with a trigger. However, it's all well and good doing this in the database, but we have no idea how the application itself works or will deal with this (if at all). For example, the application may display what the user has entered rather than the value that the trigger has automatically inserted. I think you're going to have to deal with this in the front-end, which is something we probably can't help you with.

Anyway, here's how to write a trigger to do this.

Test tables and data:

create table roads
(
road_id number primary key,
measure number
);

create table projects
(
project_id number,
road_id number,
from_measure number,
to_measure number
);

insert into roads values(1,1000);
insert into roads values(2,100);
insert into roads values(3,500);

Trigger:

create or replace trigger projects_btrig
before insert on projects
for each row
declare
    v_measure number;
begin
    select measure
    into v_measure
    from roads
    where road_id = :new.road_id;

    :new.to_measure := v_measure;
end;
/

Test:

SQL> insert into projects (project_id, road_id, from_measure) values (1,1,0);

1 row created.

SQL> select * from projects;

PROJECT_ID    ROAD_ID FROM_MEASURE TO_MEASURE
---------- ---------- ------------ ----------
     1         1            0          1000

SQL>


Optimizing queries on a range of timestamps (two columns) I use PostgreSQL 9.1 on Ubuntu 12.04.

I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my actual table that are not involved with this query.

create table (
   start_date_time timestamp,
   end_date_time timestamp,
   id_phi integer,
   primary key(start_date_time, end_date_time,id_phi);

This table contains roughly 2M records.

Queries like the following took enormous amounts of time:

select * from time_limits as t
where t.id_phi=0
and t.start_date_time <= timestamp'2010-08-08 00:00:00'
and t.end_date_time   >= timestamp'2010-08-08 00:05:00';

So I tried adding another index - the inverse of the PK:

create index idx_inversed on time_limits(id_phi, start_date_time, end_date_time);

I got the impression that performance improved: The time for accessing records in the middle of the table seems to be more reasonable: somewhere between 40 and 90 seconds.

But it's still several tens of seconds for values in the middle of the time range. And twice more when targeting the end of the table (chronologically speaking).

I tried explain analyze for the first time to get this query plan:

 Bitmap Heap Scan on time_limits  (cost=4730.38..22465.32 rows=62682 width=36) (actual time=44.446..44.446 rows=0 loops=1)
   Recheck Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
   ->  Bitmap Index Scan on idx_time_limits_phi_start_end  (cost=0.00..4714.71 rows=62682 width=0) (actual time=44.437..44.437 rows=0 loops=1)
         Index Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
 Total runtime: 44.507 ms

See the results on depesz.com.

What could I do to optimize the search? You can see all the time is spent scanning the two timestamps columns once id_phi is set to 0. And I don't understand the big scan (60K rows!) on the timestamps. Aren't they indexed by the primary key and idx_inversed I added?

Should I change from timestamp types to something else?

I have read a little about GIST and GIN indexes. I gather they can be more efficient on certain conditions for custom types. Is it a viable option for my use case?

For Postgres 9.1 or later:
CREATE INDEX idx_time_limits_ts_inverse
ON time_limits (id_phi, start_date_time, end_date_time DESC);
In most cases the sort order of an index is hardly relevant. Postgres can scan backwards practically as fast. But for range queries on multiple columns it can make a huge difference. Closely related:

PostgreSQL index not used for query on range

Consider your query:
SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    start_date_time <= '2010-08-08 00:00'
AND    end_date_time   >= '2010-08-08 00:05';

Sort order of the first column id_phi in the index is irrelevant. Since it's checked for equality (=), it should come first. You got that right. More in this related answer:

Multicolumn index and performance

Postgres can jump to id_phi = 0 in next to no time and consider the following two columns of the matching index. These are queried with range conditions of inverted sort order (<=, >=). In my index, qualifying rows come first. Should be the fastest possible way with a B-Tree index1:

You want start_date_time <= something: index has the earliest timestamp first.
If it qualifies, also check column 3.
Recurse until the first row fails to qualify (super fast).
You want end_date_time >= something: index has the latest timestamp first.
If it qualifies, keep fetching rows until the first one doesn't (super fast).
Continue with next value for column 2 ..

Postgres can either scan forward or backward. The way you had the index, it has to read all rows matching on the first two columns and then filter on the third. Be sure to read the chapter Indexes and ORDER BY in the manual. It fits your question pretty well.
How many rows match on the first two columns?
Only few with a start_date_time close to the start of the time range of the table. But almost all rows with id_phi = 0 at the chronological end of the table! So performance deteriorates with later start times.
Planner estimates
The planner estimates rows=62682 for your example query. Of those, none qualify (rows=0). You might get better estimates if you increase the statistics target for the table. For 2.000.000 rows ...
ALTER TABLE time_limits ALTER start_date_time SET STATISTICS 1000;
ALTER TABLE time_limits ALTER end_date_time   SET STATISTICS 1000;

... might pay. Or even higher. More in this related answer:

Check statistics targets in PostgreSQL

I guess you don't need that for id_phi (only few distinct values, evenly distributed), but for the timestamps (lots of distinct values, unevenly distributed).
I also don't think it matters much with the improved index.
CLUSTER / pg_repack / pg_squeeze
If you want it faster, yet, you could streamline the physical order of rows in your table. If you can afford to lock your table exclusively (at off hours for instance), rewrite your table and order rows according to the index with CLUSTER:
CLUSTER time_limits USING idx_time_limits_inversed;

Or consider pg_repack or the later pg_squeeze, which can do the same without exclusive lock on the table.
Either way, the effect is that fewer blocks need to be read from the table and everything is pre-sorted. It's a one-time effect deteriorating over time with writes on the table fragmenting the physical sort order.
GiST index in Postgres 9.2+
1 With pg 9.2+ there is another, possibly faster option: a GiST index for a range column.

There are built-in range types for timestamp and timestamp with time zone: tsrange, tstzrange. A btree index is typically faster for an additional integer column like id_phi. Smaller and cheaper to maintain, too. But the query will probably still be faster overall with the combined index.

Change your table definition or use an expression index.

For the multicolumn GiST index at hand you also need the additional module btree_gist installed (once per database) which provides the operator classes to include an integer.

The trifecta! A multicolumn functional GiST index:
CREATE EXTENSION IF NOT EXISTS btree_gist;  -- if not installed, yet

CREATE INDEX idx_time_limits_funky ON time_limits USING gist
(id_phi, tsrange(start_date_time, end_date_time, '[]'));

Use the "contains range" operator @> in your query now:
SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    tsrange(start_date_time, end_date_time, '[]')
    @> tsrange('2010-08-08 00:00', '2010-08-08 00:05', '[]')

SP-GiST index in Postgres 9.3+
An SP-GiST index might be even faster for this kind of query - except that, quoting the manual:

Currently, only the B-tree, GiST, GIN, and BRIN index types support multicolumn indexes.

Still true in Postgres 12.
You would have to combine an spgist index on just (tsrange(...)) with a second btree index on (id_phi). With the added overhead, I'm not sure this can compete.
Related answer with a benchmark for just a tsrange column:

Perform this hours of operation query in PostgreSQL


Breaking and Restoring a mirror I have to do some maintenance on a secondary node of a mirror. Since this is the result of the site becoming slow while the mirror re-established after a brief network outage I want to be safe and disable the mirror, then re-enable it after I'm done poking at the offending server.

The plan I have right now is to:

Disable the mirror
Do my potentially dangerous troubleshooting
Restore all missed transaction logs
Re-establish the mirror

Is this the safest way to go about breaking/re-establishing a mirror? Are there any gotcha's I should look out for?

It sounds as if you simply want to pause and resume your mirror. Have you seen this link yet? http://msdn.microsoft.com/en-us/library/ms190664.aspx

The gotcha with this would be the length of time you expect to have the mirror paused. If you take too long then the principal server could experience a large amount of transaction log growth. if you do not expect to be paused for a long period of time, then you should be fine.

If you feel that you must break the mirror and resume later, then you should do the following:

Break the mirror using this against the principal:
ALTER DATABASE  SET PARTNER OFF

If you are concerned that your maintenance will take a long time, then you should perform regular transaction log backups, so as to make certain the log does not grow out of control and fill up your disks. however, the more trx log backups you have, the more you need to recover to the mirror later.

After you have restored the trx logs, you should be able to get mirroring running again.

Another option would be to reinitialize everything, but I don't know how big of a database this is, it may be too much for you to restore, so trx logs may be the better option.

This link explains it best: http://msdn.microsoft.com/en-us/library/ms189112.aspx

And this link covers more topics: http://msdn.microsoft.com/en-us/library/ms175059.aspx

HTH


How to find minimal cover when attributes have equal chance to be not necessary? I have the schema which consists of 6 attributes: t,s,r,w,e,g.

For the schema I have the following functional dependencies:

s,w --> r;t,w,g --> r,e;s,r --> t,g; e -->w; g --> t,s;r,w,e --> s

I'm not quite sure how to go about the dependencies where one side has 3 attributes. For example, for t,w,g --> r,e should I check for all possible combinations of t,w,g (2^3-2=6 cases to check in total)?

For example I choose the case where I want to check if t is not necessary in t,w,g --> r,e.
Indeed the closure of {w,g} is {w,g,t,s,r,e} which contains {r,e}.

If I continue to look for unnecessary attributes in w,g --> r,e then I have:

w,g --> r OR w,g --> e. So can I choose a random result here or there's some rule on whether r is not necessary or e?

In a functional dependency, both the left part and the right part are sets, so the order of the attributes does not matter.

To find the canonical (or minimal) cover of a set of functional dependencies, the classical algorithm (presented in almost all books on databases) consists of three steps:

First, rewrite the dependencies so that they have a single attribute on the right part (for instance, t,w,g -> r,e is replaced by t,w,g -> r and t,w,g -> e).
Then, for each attribute on the left part, see if the closure of the remaining attributes includes the right attribute. In this case the attribute is not necessary and should be eliminated.
Finally, eliminate the superfluous dependencies, if present. A dependency is superfluous if its right part is contained in the closure of the left part, calculated with respect to all the other dependencies.

For instance, in your example, a possible minimal cover can be computed in this way.

At the end of the first step, we have:

{ e → w
  e r w → s
  g t w → e
  g t w → r
  g → s
  g → t
  r s → g
  r s → t
  s w → r }

At the end of the second step:

{ e r → s
  e → w
  g → t
  g → s
  g w → r
  g w → e
  r s → t
  r s → g
  s w → r }

Finally, a minimal cover is:

{ e → w
  e r → s
  g w → e
  g → s
  g → t
  r s → g
  s w → r }


Return the uniqueidentifier generated by a default on insert Goal
Retrieve the latest guid value in real time after you have inserted the value in the table
Problem
Don't know how to do it
Info

The code should only specify new values for address and zipcode
There can be lots of data in the table

Table
CREATE TABLE [AddressBook]
(
    [testID] [uniqueidentifier] NOT NULL default newid(),
    [address] [nvarchar](50) NULL,
    [zipcode] [nvarchar](50) NULL
)

I think you are looking for output

DECLARE @MyTableVar table([testID] [uniqueidentifier]);
 INSERT [AddressBook] ([address], [zipcode])
        OUTPUT INSERTED.[testID] INTO @MyTableVar
 VALUES (N'address', N'zipcode');

--Display the result set of the table variable.
 SELECT [testID] FROM @MyTableVar;

GO

uniqueidentifier may not be the most efficient id here but this is an answer to the stated question


Cannot Create or Drop User in MySQL Workbench OK. I have set up a new MySQL Server in AWS Ubuntu EC2 and migrate databases from the RDS. After importing the tables, stored procedures and functions into the new server, I encountered the attached error.

This happens whenever I want to create or drop users, or if I modify the stored procedure and apply changes. For stored procedures or functions, if I continue to apply changes second time, it would work.

I tried to delete those users in my Workbench but the pop-up error prevents me from doing that.

I think these users were created when I import those stored procedures with those definers.

Anyone could help?

Do not import the tables in the database mysql from a server running a different version of MySQL.


How to change the replication State Transfer SST to IST in Galera Config I would like to migrate 700GB data to galera cluster(2node+1arbitrator). I found that IST is the best method for huge DBs.So how to forecfully change the replication from SST to IST in Galera Config.
Configured sst method:
my.cnf
wsrep_sst_method=rsync

This is not the whole truth. It is not about SST vs. IST it is about SST followed by an IST.

SST is a full sync, IST is an incremental sync between 2 nodes.

Sometimes you have to use an SST. For example, when you sync your second Galera node with the first one the first time. Later on, when both nodes have the full data set and you take out one node and bring it in again, Galera will choose if IST is possible and if not choose SST.

You can force an SST in favour of IST but not the other way around.


In Full recovery mode, when does the buffer/transaction log changes get applied to the database data pages? When a change is to be made, the data page is fetched from the database data file into the buffer and changes are made to the buffer page and are also written to the transaction log. Write ahead logging. In Simple recovery mode the changes from the buffer are applied to the database data pages at checkpoint. The checkpoint flushes the records from the transaction log. Hence transaction log backups cannot be taken in this mode. Similarly for full/bulk recovery models when a full backup hasn't yet been taken.

Question: How and when does the changes to the data page get written to the database data file in the full/bulk recovery model once the 1st full backup has been taken?

According to the SQL Server Transaction Log Architecture and Management Guide, data pages are modified in cache first (logical write). This occurs the same way regardless of the database recovery model, with only the level of logging detail being different. A transaction log record containing the modification is created and written to the log buffer cache. These log buffers are always physically written to disk before the modified pages (write-ahead logging).

Data pages may be written to disk by lazy writing, eager writing, and the checkpoint process. The log buffer is flushed to disk before the corresponding data pages are written to the data files by these processes.

Transaction log buffers are physically written to disk when 1) they become full, 2) during COMMIT when the log buffer contains data for a durable transaction, 3) a checkpoint, or 4) a modified page is written to disk by one of the aforementioned processes.


Does this set of locks imply table lock and if so is it avoidable? I have a query on SQL Server 2008 that when using sp_whoisactive with get_locks = 1 has the following locks open....

<Object name="my_table" schema_name="dbo">
  <Locks>
    <Lock resource_type="KEY" index_name="pk_mypk" request_mode="X" request_status="GRANT" request_count="1" />
    <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
    <Lock resource_type="PAGE" page_type="*" index_name="pk_mypk" request_mode="IX" request_status="GRANT" request_count="1" />
  </Locks>
</Object>

I understand the Key and Page lock but am unsure what the Object one is. Is that a table lock? If so why did it bother taking key and page locks as well? Are there ways to work out why a particular query that is only touching a small set of rows would use a table lock, I'm guessing it's lock escalation.

Reference:

Lock Modes

See above reference for more details.

Intent = Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

  Intent Locks

  The Database Engine uses intent locks to protect placing a shared (S)
  lock or exclusive (X) lock on a resource lower in the lock hierarchy.
  Intent locks are named intent locks because they are acquired before a
  lock at the lower level, and therefore signal intent to place locks at
  a lower level.

  Intent locks serve two purposes:

  To prevent other transactions from modifying the higher-level    resource in a way that would invalidate the lock at the lower level.
  To improve the efficiency of the Database Engine in detecting lock    conflicts at the higher level of granularity.

  For example, a shared intent lock is requested at the table level
  before shared (S) locks are requested on pages or rows within that
  table. Setting an intent lock at the table level prevents another
  transaction from subsequently acquiring an exclusive (X) lock on the
  table containing that page. Intent locks improve performance because
  the Database Engine examines intent locks only at the table level to
  determine if a transaction can safely acquire a lock on that table.
  This removes the requirement to examine every row or page lock on the
  table to determine if a transaction can lock the entire table.


SQL Server - Tempdb data/log file placement Currently we got TEMPDB Data (number of data files:2) and LOG file on same drive.

On monitoring we have found I/O issues on TempDB.

We got an extra drive available - should we move the TempDB Log file to new drive or should we move the second TEMPDb data file on the new drive to help us reduce I/O issue?

If you're already certain you have IO contention, start by understanding which files are the cause.

SELECT
    DB_NAME(fs.database_id) AS [Database Name]
  , mf.physical_name
  , io_stall_read_ms
  , num_of_reads
  , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
  , io_stall_write_ms
  , num_of_writes
  , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
  , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
  , num_of_reads + num_of_writes AS [total_io]
  , CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads
    + num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN
    sys.master_files AS mf
ON  fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY
    avg_io_stall_ms DESC
OPTION
    (RECOMPILE) ;

With the data from that query you can judge whether the log or data files are the point of contention in tempdb. It's probable you'd be better off with both data files on one drive and the log file separate, but you'll never know without the numbers to back the decision.


What is happening under the hood in MySQL 8 when I apply an auto-increment primary key to a raw table of data? I'm responsible for a production database using MySQL 8.0 (InnoDb engine). It has become necessary for me to archive data from a sizeable table (the data file takes up approximately 50% of the hard drive).
I've learnt that using DELETE FROM x WHERE y is a horrible idea and doesn't necessarily reduce disk space usage. My new strategy is to:

Export the data I need to archive to CSV (I was doing this before)
Select the data I want to keep in the DB and place it in a new table (using CREATE TABLE ... SELECT)
Apply the PK AI NN BIGINT primary key that I have in the existing table to the new table
Delete the non-FK indices from the current table (to reduce disk usage)
Apply FK indices to the new table
Apply non-FK indices to the new table
Rename the old table and new table
Verify new table is working
Drop old table

My problem / question really begins at step 3: my raw table consumes 44.6 GB before applying the primary key, and as I am typing this the replacement file is pushing 52.2 GB. That seems like a lot of space for a primary key that's technically based on data that's already there.
Could anyone explain why it's taking so much space? I'm running out of hard drive space now and don't know how to estimate how large the final data file might be.
Here's the CREATE statement I used to generate the temporary table:
CREATE TABLE ts_temp
SELECT * FROM timeseries
WHERE
        (type_id IN (1, 4, 5) AND date > CURDATE() - INTERVAL 24 MONTH)
    OR (type_id IN (8) AND date > CURDATE())
    OR (type_id IN (10, 11) AND date > CURDATE() - INTERVAL 9 MONTH)
    OR (type_id IN (12, 13, 14, 15, 22) AND date > CURDATE() - INTERVAL 6 MONTH)
    OR (type_id IN (16, 17, 19, 20, 21) AND date > CURDATE() - INTERVAL 2 MONTH)
    OR (type_id IN (2, 6) AND date > CURDATE() - INTERVAL 1 MONTH);

Getting a subsequent CREATE statement from MySQL Workbench for this table would look like this:
CREATE TABLE `ts_temp` (
  `id` bigint unsigned NOT NULL DEFAULT '0',
  `item_id` int DEFAULT NULL,
  `date` date DEFAULT NULL,
  `type_id` int DEFAULT NULL,
  `node_id` int DEFAULT NULL,
  `value` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Then, I applied the PK using this:
ALTER TABLE `ts_temp`
CHANGE COLUMN `id` `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
ADD PRIMARY KEY (`id`);

I should perhaps mention that this is a timeseries table and we ran out of AI numbers when it was a (default) signed INT field, which I subsequently converted to BIGINT unsigned.

Do you care whether you keep the old auto_increment values?  Your process seems to loose them.
Did you have innodb_file_per_table turned on when the original table was created?  If yes, there are easier ways; if no, you need to dump all tables and rebuild.
Be sure to have innodb_file_per_table=ON
What percentage of the table is to be kept?  If it is a small percentage, don't bother with the CSV steps, simply rebuild the new table as InnoDB.  Also you won't need to rebuild the AI.
While you are at it, consider shrinking the datatypes.  INT takes 4 bytes and BIGINT takes 8.  Look into smaller datatypes.  And UNSIGNED.  (Eg, SMALLINT UNSIGNED takes 2 bytes had has a range of 0..64K.)
Adding and dropping secondary indexes (but not the PK) is efficient in 8.0.
Do you have any other indexes, perhaps even composite indexes?  Is any of them 'unique'?  If so, could you get rid of id and use that index as the PRIMARY KEY?
Can each of the columns really be NULL?  Consider fixing that if necessary.
Do the rename in these steps.  This minimizes downtime:
RENAME TABLE real TO old,
             temp TO real;
DROP TABLE old;

More tips on big deletes and related things:  http://mysql.rjweb.org/doc.php/deletebig


Change linked server login per-query Given the situation below, can I specify that the pre-configured linked server login should not be used for a particular remote stored procedure call?

I have two SQL Server instances:

[LOCAL] running SQL Server 2012 with database [A] and
[REMOTE] running SQL Server 2008R2 with database [B].

[REMOTE] is already set up as a linked server on [LOCAL].  All users who can access [LOCAL].[A] can access [REMOTE].[B] (though not all its objects) through the SQL Server login GenericUser, which is already set up through a command on [LOCAL] such as:

EXEC sp_addlinkedsrvlogin @rmtsrvname  = N'REMOTE',
                          @useself     = 'FALSE',
                          @rmtuser     = N'GenericUser',
                          @rmtpassword = N'TheGenericUserPassword'

This login works fine.  For authorization reasons, however, I would like to restrict access to a stored procedure [dbo].[RemoteSP] on [REMOTE].[B] to one particular Windows Authentication user, [MYDOMAIN\OneUser].  [REMOTE].[B].[dbo].[RemoteSP] will be called from within a stored procedure [dbo].[LocalSP] on [LOCAL].[A], which [MYDOMAIN\OneUser] may execute (as can other users, who will not call [RemoteSP], as shown below).

The local stored procedure looks like:

CREATE PROCEDURE [dbo].[LocalSP]
    @Id INT
AS
SET NOCOUNT ON
-- Do some stuff, then:
IF SUSER_NAME() = N'MYDOMAIN\OneUser'
BEGIN
    -- This syntax is invalid:
    EXEC [REMOTE].[B].[dbo].[RemoteSP] @MyVar = @Id AS SELF
END

Access is granted (e.g., by GRANT EXEC ON [dbo].[RemoteSP] TO [MYDOMAIN\OneUser]) on [REMOTE], and the user [MYDOMAIN\OneUser] exists on [REMOTE] and in [REMOTE].[B].

Adding WITH EXECUTE AS 'MYDOMAIN\OneUser' to the [RemoteSP] definition, as this answer suggests, is unsatisfactory because I still need only [MYDOMAIN\OneUser] to have access to the stored procedure.  Although this question may be somewhat related, it is unanswered, and the linked chat room has been deleted.

I do not want to change the linked server login that [MYDOMAIN\OneUser] would use for all queries, just this one.  I also would prefer to avoid a syntax like:

EXEC (N'EXEC [B].[dbo].[RemoteSP] @MyVar = ?', @Id) AS LOGIN = N'MYDOMAIN\OneUser' AT [REMOTE]

if for no other reason than there is little reason to use a dynamic string -- I have the stored procedure name, etc.

Based on comments on the question: The desire is to have RemoteSP on [REMOTE] execute within the security context of GenericUser, but only executable by [MYDOMAIN\OneUser] even though everyone accesses [REMOTE] as GenericUser via the same Linked Server with a static security setup.

That seems like an inconsistent security setup. You have a single login on [REMOTE] that you want to have two different sets of permissions for. At the very least you will have to DENY EXECUTE ON [RemoteSP] TO [GenericUser]; in order to prevent everyone outside of [MYDOMAIN\OneUser] from executing the proc over the Linked Server. But you want it to execute in the context of GenericUser so you need to add WITH EXECUTE AS 'GenericUser' to that proc. Both of these steps get you the security context with the restriction on who can run it, but so far there is no way for [MYDOMAIN\OneUser] to execute it either. And yes, it seems counter-intuitive to DENY execute to the User that is then used as the security context, but a) that be the requirements, and b) it does work as I tested it since it seemed like there was a possibility that SQL Server might give the "Did you even look at what you were trying to execute" error ;-).

Allowing only [MYDOMAIN\OneUser] to execute means creating another Login on [REMOTE] for this purpose, call it UserForRemoteSP. Be sure to GRANT EXECUTE ON [RemoteSP] TO [UserForRemoteSP];.

Another desire is that [MYDOMAIN\OneUser] still access [REMOTE] via the current Linked Server (and hence as GenericUser) for everything else besides [RemoteSP]. That can be accomplished by creating another Linked Server, call it [REMOTE_SPECIAL], that has a mapped "local login to remote login" list with only [MYDOMAIN\OneUser] defined in it, mapping to UserForRemoteSP. For the "For a login not defined in the list above, connections will:" option, select "Not be made".

Last step: in [LocalSP], in the IF SUSER_NAME block, just do:

EXEC [REMOTE_SPECIAL].[B].[dbo].[RemoteSP] @MyVar = @Id;

Nobody else will be able to use the [REMOTE_SPECIAL] Linked Server, and the only thing that [MYDOMAIN\OneUser] can use that Linked Server for is to execute RemoteSP, which will run as GenericUser due to the EXECUTE AS clause.


Installing a language with Amazon RDS? I am using an instance of Postgres on RDS and I am trying to run:

=> create language plperlu;

but I get:

ERROR:  must be superuser to create procedural language "plperlu"

If I run:

=> grant rds_superuser to treex;
NOTICE:  role "treex" is already a member of role "rds_superuser"
GRANT ROLE

Is it actually possible to use perl extensions for Postgres on RDS or the only solution is creating a Docker instance with Postgres and Perl modules preinstalled?

You can't do this on RDS. The rds_superuser isn't a true superuser and cannot load any C-language extensions.


Deny access to information schema in SQL Server I am looking for the best way to disable access to the sys.tables / Information Schema for a user / group in SQL Server.

I found this thread from 2008

It shows a way how to deny access on [sys].[something] like so:

 DENY SELECT ON [sys].[columns] TO DenySystemTableSelectRole
 GO
 DENY SELECT ON [sys].[tables] TO DenySystemTableSelectRole
 GO
 DENY SELECT ON [sys].[syscolumns] TO DenySystemTableSelectRole
 GO
 DENY SELECT ON [sys].[sysobjects] TO DenySystemTableSelectRole
 GO

But no way how to disable access on the Information Schema:

DENY SELECT ON INFORMATION_SCHEMA.TABLES To DenySystemTableSelectRole

This seems not to work.

How can I disable access to information_schema?

And is there an easier way disable access to all sys / information_schema?

Update:
Actually I can not run both ot the following statements:

DENY SELECT ON [sys] TO reducedDBO
GO
DENY SELECT ON INFORMATION_SCHEMA To reducedDBO
GO

I tried to run them on the specific DB where the User exists, and I also tried on the "master".

I still can run:

 SELECT * from
 INFORMATION_SCHEMA.TABLES

-->still returns results

 SELECT * from
 sys.TABLES

-->no results anymore

Including SCHEMA:: in the query made it possible to create the securables

DENY SELECT ON SCHEMA::[sys] TO reducedDBO
GO
DENY SELECT ON SCHEMA::INFORMATION_SCHEMA To reducedDBO
GO

But now I still can select all the information from the DB.

I had a look at the "Securables"-Tab in the users Property-window in Management Studio 2008, it looks like this:

Entry that does block the selecion of sys.tables

Schema:sys, Name:tables, Type:View

Permissions for sys.tables: Permission:Select, Grantor:dbo, Deny is checked

Entry that do not block any selection

Schema:, Name:INFORMATION_SCHEMA, Type:Schema

Permissions for INFORMATION_SCHEMA: Permission:Select, Grantor:dbo, Deny is NOT checked (I tried to check it, but no chance..)

Permission:Select, Grantor:INFORMATION_SCHEMA, Deny is checked

I tried to set the permissions over the GUI, but then I get the same error that setting permissions would be possible only on the master DB.
But I not have the user/login added to the master DBs security.

Solution:

The only way I could make the deny work for the information_schema was to add the user to the master-db and run the deny select on the master:

DENY SELECT ON [sys].[tables] TO reducedDBO
GO
DENY SELECT ON INFORMATION_SCHEMA.TABLES To reducedDBO
GO

And as in this code, it can only be executed for single tables.

You should be able to just deny permissions on the entire sys and information_schema schema as a whole:

DENY SELECT On SCHEMA::sys To [user_name]
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To [user_name]

That should basically just prevent that user from doing any selects in those two schemas.


Initial Database Size Too Big I have a clustered SQL 2016 standard database. It is set up in a virtual environment with a total of 250 GB of storage. However, my developer set the initial size of the databases to be 100 GB. There are two databases (200 GB total), using 40 GB and 20 GB, respectively.

I am unable to run a backup due to the large initial database size. I have read multiple articles about how shrinking the database is against best practices including 'Stop Shrinking Your Database Files. Seriously. Now.' but I don't see any other options.

Can someone assist? My plan is to run the command DBCC SHRINKDATABASE down to a size just larger than the databases are now and then rebuild the index. I appreciate any suggestions.

As others have said, shrinking a database in this situation is not at all inappropriate.

What the experts mean when they say things like "Stop Shrinking Your Database Files. Seriously. Now." is that you don't want to shrink them just because they are a little bit bigger than you expect. You want to make sure you know why your database is "too big" before you shrink it. Otherwise, you're only treating the symptom, not the disease.

For example, maybe a big weekly data import causes your database to grow by 200 GB every Sunday, and then a consolidation removes 195 GB of that data every Monday. If you look at your database on Tuesday, it'll seem to be much bigger than it needs to be. But if you shrink it, it'll just grow back on the following Sunday.

In the case presented in this question, the size of the databases is the result of an erroneous initial setup, rather than the result of normal activity. As such, shrinking it makes good sense.


Skip sections that have GO I'm working on a script I can run on new servers to do some initial configuration.  I would like to be able to skip some sections but the problem I'm running into is the "GO" that's required.  Because of the GO I can't wrap my sections of code in IF statements.  For example

:setvar EnableDBMail 0

IF ($(EnableDBMail) = 1 )
BEGIN
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

END
....etc

The problem is this won't work because you have to use GO.  Is there another way of doing this?  I've tried searching but have yet to find anything.

Whether or not you are using SQLCMD mode, you can always avoid the need for a GO (which enforces batch separation) by wrapping the calls in an EXEC().  For example:

IF ($(EnableDBMail) = 1 )
BEGIN
  EXEC('sp_configure ''show advanced options'', 1;');
  EXEC('RECONFIGURE;');
  EXEC('sp_configure ''Database Mail XPs'', 1;');
  EXEC('RECONFIGURE;');
END;

This also works when creating objects that need to be the only CREATE in the batch, such as: stored procedures, functions, triggers, views, etc. The following is a single batch and can be wrapped in an IF or whatever else:

CREATE TABLE dbo.MyTable
(
  Col1 NVARCHAR(256) NOT NULL
);

EXEC('
CREATE PROCEDURE dbo.Proc1
(
   @Param1 INT
)
AS
SET NOCOUNT ON;

SELECT field
FROM   table;
');

EXEC('ALTER TABLE dbo.MyTable ADD Col2 DATETIME NULL;');


Update Table data using variables depending on other tables Suppose the following tables:

faculty:
+----+--------+
| id | headFK |
+----+--------+

department:
+----+----------+--------+
| id | facultyFK| headFK |
+----+----------+--------+

teacher:
+----+--------------+-------+
| id | departmentFK | chief |
+----+--------------+-------+

What is the best way to go through each row of teacher. Save some column values as variables (suppose: id, departmentFK) to use it in further Queries (Update to be specific)

My aim is to set up subordinations of teacher which is like this chain 'Faculty Head' > 'Department Head' > 'Teacher'

Formally it should look like:

FOR EACH ROW IN teacher
{
    Set curDepartment = Current.departmentFK;
    Set curTeacher = Current.id;
    Set curFaculty = (Query to get faculty);
    Set FacultyHead = (Query to get current faculty head by var curFaculty );
    Set departmentHead = (Query to get current department head by var curDepartment );

    (Query to update teacher.chief with conditions using variable acquired before)
}

Could you please tell the way (Procedures, Functions or ordinary queries) how can I do it because I've never had experience with procedures or local variables in MySQL and I cannot figure out how can I implement it

PS: I'm not asking to write code (that would be too much I think) but an approach to implement it

Update

Thanks to the Rick James's note I came up with the following solution:

UPDATE teacher
    JOIN department ON teacher.DepFR = department.DepPK
    JOIN faculty ON department.FacFK = faculty.FacPK
SET teacher.ChiefFK = CASE
    WHEN teacher.TchPK = faculty.DeanFK THEN NULL
    WHEN teacher.TchPK = department.HeadFK THEN faculty.DeanFK
    ELSE department.HeadFK
END;

Is it a good way of doing it?

It is a no-no to have redundant data.

Instead, use JOIN when doing the SELECT; this will let you dynamically get the faculty, faculty_head, etc.

Learn about JOIN.


Data Tier Applications (DAC) on replicated databases We are exploring options to accomplish 3 things: 1) source control the database, 2) centralize and coordinate all database changes and tie them to application changes, and 3) automate database deployments.

I'm considering using DACPACS for this but we have 4 production databases that have replication between themselves set in place. The replication was set up a long time ago and was implemented in an odd fashion. Essentially, not all 4 databases are exactly the same. Some data is replicated and some is not.

My question is about applying DACPACS. If we were to apply the DACPACs to each database during a deployment, how would this affect replication?

Thanks

The short answer is: SSDT (dacpacs) does not support SQL Server replication.

The longer answer is that you might be able to work around this limitation by using pre and post deploy scripts.  And maybe write your own deployment contribution extension to handle replicated objects better.

For the pre and post deploys scripts approach you could write something that captures the current replication state (publications, articles, subscribers and subscriptions) and then pulls all that down.  Then the dacpac updates the current schema and a post deploy script(s) recreates the replication.

If you're strict on your replication topology and configuration this could work.  It won't be easy though!  Another similar approach, which would also buy into your "get the database under souce control" is you only allow replication to be configured from scripts and these become your pre/post deploy changes (much like above but less dynamic work).

Another approach would be to use another system for database control and deployments.  Something like:

DbUp
FlywayDB

These both take the database migrations approach rather than model first approach.  These would probably make the replication aspects easier to handle.


AlwaysOn SQL Server setup questions I have a few questions regarding the setup of Always On HA, and Clusters.

When you setup an AG, and it gets to setting up your IP address for the cluster how can I switch from using DHCP to a static IP address? I have it working with DHCP right now but in the future I would like to change that for my existing AG and also for future groups I setup. It does not seem like I'm being given the option in my setup though.

The second question is when setting up the group to use full sync it works just fine. However what I am trying to figure out is where that sync data is stored. I am using Synchronous-commit mode. From what it looks like the initial sync, and the data that is mirrored is stored and applied in a basic log I just cannot seem to find where that log is stored.

Any information or links would be greatly appreciated.

If you right click on the availability group listener and click properties you can change the IP address from dhcp to a static ip.

As for where the data is stored prior to transfer, it is stored in the transaction log.