How to keep smaller transaction logs files while rebuilding index of very large table I need all the help that I can get to get this index rebuild successful. Especially an expert advise on transaction log management.

Background

The target database is a DW database hosting 3 big tables in clustered columnstore index (CCIX). The biggest table called Analog. It holds ~37 billion rows and ~600 GB of high compressed data. Based on our initial estimate using smaller table, a 150 GB CCIX table could expand to 5 TB, so we provisioned additional 29 TB for this rebuild.

SQL Server Enterprise 2016
Recovery mode = Simple

Why do we have to do this?

In the first version of our ETL service, the data are not consolidated and sorted first before they are loaded to CCIX. This results to non-optimal use of CCIX as many row segments are not fully filled before they are compressed. An optimal segment must have compressed 1,0485,76 rows and must be sorted in Time so time-based queries can get the most row-group elimination in sql server and less segments to process. We’re learn more CCIX as we go and as more documentatons are available.

Excerpt of my alignment is here
https://drive.google.com/file/d/0BzGLNskaj70UQUtZYW9CZF9iUUk/view?usp=sharing

The new ETL guarantees that data are consolidated and sorted in time before they are loaded to CCIX. So future loads will be sorted, we are concern here with existing data loaded during First Load.

For full case description, please read here.
https://drive.google.com/open?id=0BzGLNskaj70URmZURlVDWVNYd2M

This is our second attempt and I can see the log file is still piling up despite the new script having partition-based index rebuild. We need to keep this in check.

My questions are:

I already executed the 2nd attempt (in progress), is it possible to check which partitions are already processed and sorted? I have OFFLINE = ON in the rebuild script.
How can we manage the size of transaction log while a partion-based index rebuild is in progress? We need to keep this in check and regularly truncated every partition if possible.
The index rebuild failed initially due to insufficient space in log file. We added a new log file and use a separate disk. But how we make make sure this wont happen again in this second run? Will the partition-based rebuild sufficient to guarantee we can rebuild successfully?

Appreciate your help.

Rebuild scripts by partition (2nd attempt) – IN PROGRESS

Elapsed: 17 hrs, 34 mins

--create a new clustered row index (CRI)
CREATE CLUSTERED INDEX [Analog_ColumnStoreIndex]
ON [dbo].[Analog]
(
    [LogTime] ASC,
    [CTDIID] ASC,
    [WindFarmId] ASC,
    [StationId] ASC
)
WITH (
    DROP_EXISTING = ON,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    ONLINE = OFF
)
ON [AnalogMonthlyPScheme]([LogTime])
GO

The most important advice I can give you is to test against a smaller table first. Don't just dive into converting a 37 billion row table. You can refine your method against a smaller table and use that to get an estimate on converting the large table. Also recovering from mistakes is far less costly (no more 24 hour rollback operations). You may also be able to use the smaller table to test query performance to verify that the conversion that you're doing is worth the effort.

The second most important advice is to get someone to check out your storage configuration. If you recently added a lot of storage it may not have been done in a good way to maximize throughput.

To be honest, it's not clear to me why you're seeing so much transaction log activity. Converting the CCI to a rowstore should be a minimally logged operation since you're using a recovery model of simple.

I'll walk through what I know about how SQL Server implements the following statement:

CREATE CLUSTERED INDEX [Analog_ColumnStoreIndex]
ON [dbo].[Analog]
(
    [LogTime] ASC,
    [CTDIID] ASC,
    [WindFarmId] ASC,
    [StationId] ASC
)
WITH (
    DROP_EXISTING = ON,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    ONLINE = OFF
)
ON [AnalogMonthlyPScheme]([LogTime]);

Before writing any rows SQL Server needs to sort the entire table's data by LogTime, CTDIID, WindFarmId, and StationId. The size of the sort will be massive. I suspect that it will be about the size of the uncompressed clustered rowstore table. It will probably spill over from tempdb to disk. Perhaps that part of the operation is not minimally logged? I can't really guess as to why else you are seeing so much log activity. Note that sorting on LogTime would be sufficient for your conversion and might make the sort cheaper but I don't think it would reduce the workspace required for the sort.

Along with the sort the data will be written to a new table. The old CCI table will be dropped after the new table is finished. For your biggest table you estimate that this will be 20 TB. It could take a long time to write 20 TB of data.

If you want to convert your data avoiding huge sorts and huge transactions it should be possible to do, but messy. Before going into this I need to point out that if your CCI is already partitioned the right thing to do is to use partition switching to better organize your rowgroups. Based on your question it seems like tables aren't partitioned yet but I want to be sure. I also have not tested any of the following.

Create a partitioned rowstore heap or a partitioned view. The partition scheme of these needs to match your future CCI partition scheme. Delete the TOP N rows from the CCI into the partitioned heap using the TABLOCK hint. That should get you minimal logging for the insert. You will not get minimal logging for the delete against the CCI. However, a delete against a CCI is just changing values in a bitmap so it should require far less logging than a standard delete.

The above insert should require a sort for the heap and will probably require a sort for the partitioned view as well. However, since you control the value for N you can limit the size of the sort to fit it within memory + tempdb.

Note that as you continue to do loops your query will probably take longer and longer to read data from the CCI. This is because it needs to scan past the deleted rows. Periodically you should issue a REORGANIZE against the CCI to clean up those deleted rows.

After the loops are finished you'll have all of your data in the correct partitions but unsorted. At this point for each partition or table you can partition switch into a heap, build the rowstore clustered index on the table, build the CCI with DROP_EXISTING = ON and MAXDOP 1, and finally partition switch into the new CCI table. After you are done with all of the partition switching you should have a partitioned CCI with excellent rowgroup elimination by LogTime.

Two notes about loading new data into a partitioned CCI. If you want perfectly ordered, full rowgroups you can't use parallel inserts. That's because the parallel reads of the source data will lead to unevenly distributed rows on threads and each thread will load to a different rowgroup. Also, plan your partitions carefully so that you don't need to split a partition with data. You can't directly SPLIT a partition without dropping the CCI which you obviously don't want to do.


How to enable failed login profle on MySQL We are trying to implement failed_login profile for account lock on MySQL. didn't find it on documentation.

Our version is 5.7.12.

I don't think MySQL supports database/logon triggers as other DBMS do. At least I couldn't find anything about it in the docs. So that "usual way" is ruled out here.

But apparently you can do it via the general server log. Set the location of the log in your option file with

general_log_file = /where/you/want/it

and set

log_warnings = 2

. That will log warnings about unsuccessful logins.

Set

general_log = 1

when you also want to log successful logins.

However be aware, that this will mean, that a lot of other stuff is logged too. It's all just about raising or lowering log levels here, not specifically setting login related logging.


Same query hash for totally different queries Query_hash definition from  sys.dm_exec_query_stats is:

  query_hash: Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.

But when I search for one particular query_hash (as shown below), I am getting multiple query texts that differ heavily in logic and size_in_bytes (for plan).

Note: I am using SQL Server 2016

DECLARE @QueryHashTest BINARY(8)
SET @QueryHashTest = CONVERT(BINARY(8), 'Ð…U¹üŒv¿')

SELECT
        QCP.objtype
        ,qStat.query_hash,
        CONVERT(VARCHAR(100),  qStat.query_Hash) AS VARCHAR_query_hash
        ,sText.text AS QueryText
        ,QCP.size_in_bytes
        ,qStat.creation_time
        ,qp.query_plan
FROM (
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
AND QCP.objtype = 'Proc'
AND qStat.query_hash= @QueryHashTest
AND (size_in_bytes >= 2179072 OR size_in_bytes <= 262144)
ORDER BY size_in_bytes DESC

Result

Why are the unrelated queries showing same query_hash? Is it a bug or is there something unexpected going on in the database?

While hash collisions are possible, I suspect what's happening is you're pulling the text for the whole batch, not the particular statement. A procedure with multiple statements can get a cache entry for each statement. And if some statements are identical across procedures, they'll have the same query hash. Let me demonstrate:

USE TestDB
GO

--DROP TABLE dbo.employees
CREATE TABLE dbo.employees (
ID INT IDENTITY PRIMARY KEY,
title VARCHAR(20),
salary TINYINT,
has_been_fired_yet BIT)

INSERT dbo.employees
VALUES ('Senior DBA',30,0),('Grumpy DBA',50,0)
GO

CREATE OR ALTER PROC dbo.Payroll
AS

SELECT 'Your total payroll is '+CONVERT(VARCHAR(10),SUM(salary))+' query bucks'
FROM dbo.employees
WHERE has_been_fired_yet = 0
GO

CREATE OR ALTER PROC dbo.RightSizing
AS

UPDATE dbo.employees
SET has_been_fired_yet = 1
WHERE salary > 40

SELECT 'Your total payroll is '+CONVERT(VARCHAR(10),SUM(salary))+' query bucks'
FROM dbo.employees
WHERE has_been_fired_yet = 0
GO

--EXEC to get them in the cache
EXEC dbo.Payroll
EXEC dbo.RightSizing

Use the below query to see the how the same statement that is in each proc shows up separately.

SELECT
st.text AS batch_text,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1
) AS statement_text,
qs.execution_count,
qs.query_hash
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
WHERE st.objectid IN (OBJECT_ID('dbo.Payroll'),OBJECT_ID('dbo.RightSizing'))

If you use the query in your question, it fails to pick up the identical statement, since it looks at only the batch text.


Split comma seperated values and concatenate with other row values in MYSQL I have a table like below

|   ID     |    Numbers       |
-----------------------------------------------------------
|   6      |  3245,78765,1234,43566  |
|   7      |  8245,88093,8326,92365  |

I want a query that can produce the below

|     Results         |
-------------------------
|   3245_sub_6       |
|   78765_sub_6       |
|   1234_sub_6       |
|   43566_sub_6      |
|   8245_sub_7       |
|   8326_sub_7      |
|   92365_sub_7     |

I am trying to split values in the above table into something like below;
like concat(splited value,'','sub','',id) something like that.

Can't seem to get how to write this query. Been beating myself up for days. Please Help

SELECT DISTINCT CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(numbers,',',num),',',-1),'_sub_',id) results
FROM `table`, (SELECT 1 num UNION ALL
               SELECT 2     UNION ALL
               SELECT 3     UNION ALL
               SELECT 4) nums

The numbers count in nums subquery is to be equal or greater than max values count in CSV field. If values count is fixed and numbers count is equal to it, you may remove DISTINCT.

If MySQL version is 8+, the numbers pseudotable can be generated in CTE.


Does MongoDB support two 2-way synchronization We are looking at moving our data from IBM Domino to another backend.  One of the things Domino does really well is replicate (or synchronize as they call it) between a Server and Clients.  That means that Users can take their mailbox, or any other application DB, with them and work offline and when they have access to the server the "master" on the Server synchs with the "slave".

Does MongoDB support two 2-way synchronization? Assuming that it does, does it also support selective replication, where the data that is pushed out to the Client can be controlled at a field level.

Thx in advance for any insight.

No, MongoDB does not support two-way synchronization. It has master-slave replication, where all writes must occur on the master node before being replicated to the slave nodes. You can read about all the replication capabilities of MongoDB in their documentation.


MySQL trigger update error 1111 Trying to update the "size" column with the value of "file" column.

BEGIN
  set NEW.size=SUM(LENGTH(file));
END

Executing:

UPDATE `table`.`library` SET `_by`='Danis555, Vovan244' WHERE `id`='20';

  Operation failed: There was an error while applying the SQL script to the database.

  ERROR 1111: 1111: Invalid use of group function
  SQL Statement:
  UPDATE table.library SET _by='Danis555, Vovan244' WHERE id='20'

SET NEW.size = LENGTH(OLD.file);

No SUM, since there is only one row.
Specify which file with OLD..


How do I list all databases and tables using psql in a pipable format? This question deals with getting a list. So I get this:

$ psql --list
                                    List of databases
      Name      |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
----------------+----------+----------+-------------+-------------+-----------------------
 testdb1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 odoo           | openerp  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 odoo-demo      | openerp  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 odoo8          | openerp  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

I could use psql --list|grep UTF8|awk '{ print $1 }' to get a list of just the databases. Can psql also give me a clean useful list directly?

psql -q -A -t -c "SELECT datname FROM pg_database" does the trick.


MySQL statement-based replication and stored procedure When you have a MySQL statement-based replication and you create a stored procedure that modify some rows (INSERT, UPDATE, DELETE commands...), What is really replicated on the slave when you call this procedure on the master ? The CALL himself or the commands (INSERT, UPDATE, DELETE...) inside the procedure ?

For example, create such a procedure:

delimiter |

CREATE PROCEDURE myproc ()
      BEGIN

        DELETE FROM mytable LIMIT 1;

      END |

delimiter ;

Then call on the master:

master> CALL myproc();

=> does it replicates "CALL myproc()" or "DELETE FROM mytable LIMIT 1" ?

In short, no, your CALL is not replicated

You can take a look into the FAQ starting from B.4.22 and the Binary Logging of Stored Programs

B.4.23: Are stored procedures and functions created on a master server replicated to a slave?

  Yes, creation of stored procedures and functions carried out through
  normal DDL statements on a master server are replicated to a slave, so
  the objects will exist on both servers. ALTER and DROP statements for
  stored procedures and functions are also replicated.

B.4.24: How are actions that take place inside stored procedures and functions replicated?

  MySQL records each DML event that occurs in a stored procedure and
  replicates those individual actions to a slave server. The actual
  calls made to execute stored procedures are not replicated.

  Stored functions that change data are logged as function invocations,
  not as the DML events that occur inside each function.


Reject Specific Database PostgreSQL User Authentication Lets say I have two databases: Sales and Inventory

And two users: A and B

I want user A to be able to do: psql sales B

But user A must not be able to do: psql inventory B

Right now user A can access both of the above psql databases by logging in as user B, as done above.

Here's 3 options I've considered:

Edit the /opt/pgsql/data/pg_hba.conf file

But, configuring that file creates tons of security issues

Create a ~/.pgpass file for user A and B

For some reason I can't even get postgres to acknowledge the file's existence, even when I enter ~/.pgpass's contents as \*:\*:\*:B:wrong_password

Create a PGPASSWORD environment variable

Not database specific

If those two databases are on the same cluster (beware of the PostgreSQL terminology, it's a name for an instance or installation or whatever you want), then you cannot prevent the users from logging in.  All databases on a single cluster share the same users:

  Database roles are conceptually completely separate from operating system users. In practice it might be convenient to maintain a correspondence, but this is not required. Database roles are global across a database cluster installation (and not per individual database).

As Neil McGuigan pointed out in his comment, this is not completely true - one can set db_user_namespace to have database specific user names.  I don't recommend using this, the reason is in the documentation:

  Note: This feature is intended as a temporary measure until a complete solution is found. At that time, this option will be removed.

Also, if user B knows the password of a@databasea, this cannot prevent him from logging in.

What you can do, however, is preventing user A from doing anything in databaseB by revoking every permission there might be, especially connecting to a database.  The background is that logging in to a cluster is not the same thing as connecting to a database.  Preventing the latter is easy:

REVOKE ALL ON DATABASE databaseb FROM a; -- ALL means CONNECT, too

Please note that pg_hba.conf does its work also on the database level, so you can disallow connections there, too:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
host    databasea       a               10.228.116.0/24         md5
host    databaseb       b               10.228.116.0/24         md5


What are the pros and cons of adding secondary columns to a table's primary key? In MySQL, I understand that with InnoDB the contents of the primary key get attached to the leaf node of every other index on the table.

I have a photos table.  Queries on it come in many different forms, with many different GROUPings, WHERE filters on many columns, etc.

As such I have a plethora of indexes on the table.

One thing that holds true though, is that for almost any query on the table, I want to retrieve filename.

(I've not made the filename the PK as other tables (photo_tags etc) have FK references to the photo table, and an auto-incrementing photoId column seems a lot more efficient?)

Am I right in thinking that it'd be savvy to make the PRIMARY KEY (photoId, filename) so that filename is always present in the leaf nodes of any other index lookups?

Sorry if any of my terminology is wrong, I'm far from a DBA expert!

Short answer:  The filename in PRIMARY KEY (photoId, filename) buys you nothing (if I correctly understand your use case).

Long answer (and a lot more tips):

AUTO_INCREMENT needs the id to be the first column in some index; nothing more.
The PRIMARY KEY is, but definition, UNIQUE.  Adding extra columns changes that uniqueness constraint.  This is likely to hurt in that you can accidentally insert rows that would violate the previous (shorter) PK.
As far as the disk space for the PK and data (which share the same BTree), there is virtually no change.
On the other hand, the fatter the PK is, the fatter secondary indexes will be.  This is because the PK is implicitly added to each secondary key (as you stated).  Only sometimes does this fattening matter.
For performance, you need to have the appropriate index.  That is, design the indexes, then see if you can do some minor cleanups.  Example:  INDEX(a), INDEX(a,b) --> only INDEX(a,b).
Indexes should tackle WHERE, then GROUP BY, then ORDER BY.  More discussion.  (Without specifics, I can't help in more detail.)
What is retrieved (SELECT filename, ...) is rarely a consideration in designing indexes.  Exception: "Covering" index.
If the Optimizer has decided to use the PK, then adding extra columns to the PK will not help at all.  Adding columns to a secondary index may matter.
It's an old wive's tale that you should avoid varchars for ints in the PK.  It is sometimes beneficial to some degree.  I would rather discuss specific cases.  A BTree works the same way whether it is ordered by an INT or VARCHAR(99).
We need to see the main queries against the table in order to be more specific.

In one application (and using your photo analogy):  The giant Photos table had an auto_inc photoID, but most queries included WHERE userID = 123.  So:

PRIMARY KEY(photoID),
INDEX(..., userID, ...),
INDEX(..., userID, ...),
INDEX(..., userID, ...),
...

Since the data is ordered via the PK, looking up by userID led to jumping around on the disk.  This turned out to be almost twice as fast:

PRIMARY KEY(userID, photoID) -- cluster on userid; photoid for uniqueness
INDEX(photoID),   -- kept auto_inc happy
INDEX(..., userID, ...),
INDEX(..., userID, ...),
...   -- fewer were needed

The uniqueness constraint on photoID was lost, but the INSERTs used auto_inc to effectively never violate the constraint.

This table was much too big to be cached in RAM; the speedup mostly came from clustering on userID.

To loop back to your Question...  That suggested change hurts this query:

SELECT filename FROM Photos WHERE photoID = 235;  (with no other columns)

The simple answer is to change INDEX(photoID) into a 'covering' index:  INDEX(photoID, filename).  Note that this does not contradict anything I said above.


Free some space on a full Express Edition instance Using Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Express Edition (64-bit)
I have a database that reaches the maximum space (10Gb) every month.
In order to free up some space I do a bacpac, create a new database with the bacpac and shrink it 2 times.
The database goes from 10Gb to 6Gb, and the application using this database gets faster (I think the bacpac refreshes the indexes).
Is there a SQL command to do this? Can I do this in a production database?

Your current process shouldn't really change application performance (especially with such tiny amount of data) but perhaps your database gets severe index fragmentation and creating the new database results in the indexes being rebuilt. (We wouldn't be able to determine this without more information such as a before and after execution plan for a specific query that improved due to your process.)
In any case, you can run the same shrink commands on your existing database, no need to export and recreate a brand new database every time. Though usually it's not recommended to shrink a database, in your use case where you're bound to only 10 GB per database, I understand the potential need to do so.
In the long term, I'd look to re-architect your application and database to use multiple databases if possible and makes logical sense, as a way to work around the hard cap of 10 GB in SQL Server Express. (E.g. group only certain related tables in one database and other groups of related tables across other databases, or decouple transactionally heavy tables from your less changing ones for another way to slice it.) Cross database querying is always allowed. Or you might find it worth it to purchase a small Standard Edition license eventually.
Furthermore, you might want to consider switching your database to Simple Recovery Model which will reduce the amount of space needed by automatically reclaiming the space used in the Transaction Log. This is likely all the space you're reclaiming with your current process when you run the shrink command, and therefore will likely remove your need to run the command anymore.
Regarding keeping your indexes defragmented, you should read up on Microsoft's BOL: Optimize index maintenance to improve query performance and reduce resource consumption and then you can look into Ola Hallangren's SQL Server Index and Statistics Maintenance which will help you automate that process to ensure your indexes are maintained.
All of this is fine to run in a production environment, but my advise is to always test in a development one first, come up with a game plan that works, then implement it in production.


pt-table-checksum says there is a difference but pt-table-sync with --print says nothing I am using pt-table-checksum to ensure master-slave is synchronized.

When I run pt-table-checksum, I see some differences on employee_profile.trigger_log but when I use pt-table-sync with --print, I see nothing.

I resolved some differences on some tables with pt-table-sync but when I re-test with pt-table-checksum, this tool still says there is a difference.

So what is the problem with pt-table-checksum ? Is is accuracy ?

[UPDATE]

I use

pt-table-checksum -h <masterip> -u <the_user> -p '<passwd of the user>' --recursion-method dsn=h=<slaveip>,P=3306,u=<the_userr>,p=<passwd of the user>,D=check_db,t=dsns --nocheck-binlog-format --set-vars innodb_lock_wait_timeout=50 > ~/stats_check_db_<slaveip>.txt

After that, I run command to filter:

cat ~/stats_check_db_<slaveip>.txt | awk '{print "*"$3"*" " -- " $8}' | grep -Fv  "*0*"

This is output:

*DIFFS* -- TABLE
*1* -- mysql.user
*1* -- employee_profile.entry
*1* -- employee_profile.ip
*1* -- employee_profile.thread

When I run pt-table-sync:

pt-table-sync  --print h=<slaveip>,P=3306,u=<the user>,p=<passwd of the user> --sync-to-master --databases employee_profile --tables entry

I see nothing. After a while, when I re-check with pt-table-checksum, I don't see difference on employee_profile.entry

For a resolved table, I still see some differences on next re-check. These are disappeared from result of pt-table-checksum on the third checksum.

[UPDATE 2]

I am using percona toolkit version 2.2.13 - lastest version on http://www.percona.com/

I reviewed all implementations of db server that I did checksum. These are old implementation of mysql or mariadb that someone built them.

I see the problem just appear when the implementation between master and slave is different such as master is MariaDB 5.5.34 and slave is MariaDB 5.5.37 (I see false positive error: Cannot nibble table because MySQL chose no index instead of the PRIMARY index - just ignore it and then execute pt-table-sync) or master is mysql 5.5.35 and slave is mysql 5.5.37 (I see pt-table-checksum says there is a difference but pt-table-sync --print says nothing - just re-test for sure).

I think it is a reason for false positive errors.

Thank you so much !

There were false positives in old versions of pt-table-checksum. Make sure you don't run ancient version.

To check what the differences are you could also use this script():

#./compare_table.sh <db> <table> <master> <slave>

#!/bin/bash

# Usage:
# $0 database table master slave
set -eux
db=$1
tbl=$2
master=$3
slave=$4
tmpdir=`mktemp -d`

checksum_table="percona.checksums"

chunks=`mysql -h $slave -NBe "select chunk from $checksum_table where (this_crc<>master_crc or this_cnt<>master_cnt) AND db='$db' AND tbl='$tbl'"`

for c in $chunks
do
    echo "# $db.$tbl, chunk $c"
    chunk_index=`mysql -h $slave -NBe "SELECT chunk_index FROM $checksum_table WHERE db='$db' AND tbl='$tbl' AND chunk = '$c'"`
    index_fields=`mysql -h $slave -NBe "SELECT COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA='$db' AND TABLE_NAME='$tbl' AND INDEX_NAME='$chunk_index' ORDER BY SEQ_IN_INDEX"`
    index_field_last=`mysql -h $slave -NBe "SELECT COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA='$db' AND TABLE_NAME='$tbl' AND INDEX_NAME='$chunk_index' ORDER BY SEQ_IN_INDEX DESC LIMIT 1"`
# EXAMPLE:
#
#WHERE
#    (
#    (`user_id` > ?)
#    OR
#    (`user_id` = ? AND `activity_id` > ?)
#    OR
#    (`user_id` = ? AND `activity_id` = ? AND `activity_type_id` >= ?)
#    )
#AND
#  (
#    (`user_id` < ?)
#    OR
#    (`user_id` < ?)
#    OR
#    (`user_id` = ? AND `activity_id` < ?)
#    OR
#    (`user_id` = ? AND `activity_id` = ? AND `activity_type_id` <= ?)
#    )
    if [ "$chunk_index" != "NULL" ]
    then
        where="WHERE"
        v_num=1
        lower_boundary=`mysql -h $slave -NBe "SELECT lower_boundary FROM $checksum_table WHERE db='$db' AND tbl='$tbl' AND chunk = '$c'"`
        upper_boundary=`mysql -h $slave -NBe "SELECT upper_boundary FROM $checksum_table WHERE db='$db' AND tbl='$tbl' AND chunk = '$c'"`
        clause_fields=""
        where="$where (0 "
        for f in $index_fields
        do
            clause_fields="$f $clause_fields"
            op=""
            where="$where OR ( 1"
            for cf in $clause_fields
            do
                if test -z "$op"
                then
                    if [ $cf == $index_field_last ]
                    then
                        op=">="
                    else
                        op=">"
                    fi
                else
                    op="="
                fi
                v=`echo $lower_boundary | awk -F, "{ print \\\$$v_num}"`
                v_num=$(( $v_num + 1))
                where="$where AND \`$cf\` $op '$v'"
            done
            where="$where )"
        done
        where="$where )"

        v_num=1
        clause_fields=""
        where="$where AND ( 0"
        for f in $index_fields
        do
            clause_fields="$f $clause_fields"
            op=""
            where="$where OR ( 1"
            for cf in $clause_fields
            do
                if test -z "$op"
                then
                    if [ $cf == $index_field_last ]
                    then
                        op="<="
                    else
                        op="<"
                    fi
                else
                    op="="
                fi
                v=`echo $upper_boundary | awk -F, "{ print \\\$$v_num}"`
                v_num=$(( $v_num + 1))
                where="$where AND \`$cf\` $op '$v'"
            done
            where="$where )"
        done
        where="$where )"
    else
        where="WHERE  1 "
    fi
    echo $where
    mysql -h $master -NBe "SELECT * FROM $db.$tbl $where"  >> "$tmpdir/master"
    mysql -h $slave -NBe "SELECT * FROM $db.$tbl $where"  >> "$tmpdir/slave"
    set +e
    diff -u "$tmpdir/master" "$tmpdir/slave" | less -S
    set -e
done
rm -r "$tmpdir"


Do I have a memory issue? I ran sp_Blitz that pointed me to a memory issue saying:

  Memory Dangerously Low  The server has
  32755 megabytes of physical memory, but only 235 megabytes are
  available.  As the server runs out of memory, there is danger of
  swapping to disk, which will kill performance.

The Server has 32 GB RAM total, 0-2 MB Free RAM, 200 - 2376 Mb Available.

  @@Version:
      Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30    Copyright (c) Microsoft Corporation     Enterprise
  Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

I watched Performance Counters for a while and see the following values there:

Page Faults/s _Total ~ 1.200 Average, Max 18.000, regular Peeks up to 1500.
Page Faults/s sqlserver ~ 380 Average, Max 1600, regular Peeks up to 1000.
Target Server Memory 30006
Total Server Memory 30006
Buffer cache hit ratio  AVG 99,9
Target Pages 3.750 stable
Total Pages 3.750 stable
Free Pages Avg 8300, Max 13.000
Memory Grants Pending 0
% Processor Time _Total Average 15%

No. of User Connections is constantly around 300.

From my perspective some of these values indicate that the server is doing well, e.g. Memory Grants Pending or Buffer cache hit ratio.
Page Faults/sec look as if there is some caching in place. Resource Monitor shows that Hard Faults /sec (is this the same value) also has regularly high peeks.

So I wondered why sp_blitz indicated this issue. Am I facing a serious I NEED MORE MEMORY issue or is it just pointing to that 30GB assigned to sql of 32Gb physical memory leaves not enough memory left for windows?

If you look closely at SpBlitz memory recommendations it says it has two possible meanings

  Memory Dangerously Low or Max Memory Too High

In your case looking at perfmon counters output you have shared their does not seems to be a memory pressure. So we are left with the fact that max server memory is configured incorrectly. And this seems true, on a system having 32 G memory you have given 30 G to SQL Server this would definitely leave OS fighting for more memory.

In this case OS will start paging out SQL Server processes to disk making it very slow. Or if Locked pages in memory is there for SQL Server service account OS process would be paged to disk. Both these conditions are very bad for SQL Server and OS.

I suggest you take help from This SE thread to calculate appropriate value for max server memory. I have given answer on this thread which asks user to take help of perfmon counters to reach to a sensible value.

You can start by giving 27 G to SQL Server and leaving 5G for OS. Then use perfmon counters mentioned in SE thread to reach to sensible value.

EDIT: I forgot to ask you to apply SQL Server 2008 R2 SP3, currently SQL Server is on SP2. This is must if you need extended support. In any case, to be on safer side you should always make sure SQL Server is patched to latest service pack.


Optimizing query for large number of rows in mysql I have the following database table in MySQL :

and is running the following query

SELECT `scratch_tickets`.`id` AS ticket_id, `scratch_tickets`.`created_at`, `scratch_tickets`.`prize_type`, `scratch_tickets`.`prize`, `scratch_tickets`.`is_win`, `scratch_tickets`.`game_id` FROM (`scratch_tickets`)  WHERE `scratch_tickets`.`created_at` >='2015-02-12 00:00:00' and created_at<='2015-02-18 23:59:59' AND `scratch_tickets`.`status` = 4 LIMIT 50;

Now the EXPLAIN is giving following results

The query is taking almost 35 seconds to execute and when I use this with PHP, it is getting timed out. Here I think the indexes being utilized. How can I optimize this for a better performance ?

The table already have 25708614 records.

Your query:

SELECT scratch_tickets.id AS ticket_id,
       -- more columns
FROM (scratch_tickets)
WHERE scratch_tickets.created_at >='2015-02-12 00:00:00'
  AND created_at <= '2015-02-18 23:59:59'
  AND scratch_tickets.status = 4
LIMIT 50 ;

needs a simple index on (status, created_at):

ALTER TABLE scratch_tickets
  ADD INDEX status_created_at_idx         -- pick a name for the index
  (status, created_at) ;

After testing that the query is using the new index and is more efficient, you can probably drop the old status index. ("probably" because you may have some rare query that profits a lot from a (status) index but is slow when using the (status, created_at) one.)

Some more notes:

Having LIMIT without ORDER BY is a recipe for indeterminate results. Always use ORDER BY so you are in control of which (50) rows will be chosen for return (unless you really don't mind semi-random results or there are serious performance issues).
Also note that adding or changing the ORDER BY expression may affect performance. For the specific query, the index will be used effectively if you have ORDER BY created_at or ORDER BY created_at DESC or ORDER BY created_at, ticket_id. If a different column or expression is used, the efficiency may not be as good.
Instead of inclusive (>= and <= - or BETWEEN) it's easier and less error-prone to use inclusive-exclusive ranges for datetime types. This way, you don't care much if the type is DATE, DATETIME or TIMESTAMP or if it has second, millisecond or microsecond accuracy.
Queries are more clear with aliases. See a rewrite:

SELECT st.id AS ticket_id,
       -- more columns
FROM scratch_tickets AS st
WHERE at.created_at >= '2015-02-12 00:00:00'       -- you could even use '2015-02-12'
  AND st.created_at <  '2015-02-19 00:00:00'       -- notice the   <
  AND st.status = 4
ORDER BY <some_expression>
LIMIT 50 ;


MySQL table doesn't exist What I did before the disaster was:

vi /etc/my.cfg

I added

innodb_buffer_pool_size=4G
innodb_buffer_pool_instance=2
innodb_read_io_threads=12
innodb_write_io_threads=12
innodb_io_capacity=300
innodb_log_file_size=128M

and after that

$ service mysql stop
$ cd /var/lib/mysql
$ rm -f ibdata1 ib_logfile*
$ service mysql start

I receive ERROR 1146 (42S02): Table 'db.table' doesn't exist for every table.

My site is down, database backup does not help.

rm -f ibdata1 ib_logfile*

Well there's your problem. You deleted your INNODB table space.

You'll need to restore from backup.

Just curious: where did you get the idea that you should remove those files?


Conversion error on paramater but not variable I've got a stored procedure that accepts a parameter;

CREATE PROCEDURE [dbo].[Stored_Proc_Name] (@ParamID NVARCHAR(255) = NULL)

I call this stored procedure and pass in a uniqueidentifier, something like this;

EXEC [Stored_Proc_Name] 'a6ed99c1-29c8-43f4-9e3a-0065e6dc7fc1'

The stored procedure does a bit of XML processing and returns a result set, it's fairly simple.

For a few paramaters that are passed I'm getting a type conversion error;

Msg 8114, Level 16, State 5, Procedure Stored_Proc_Name, Line 0
Error converting data type nvarchar to int.

I initially thought that this was an issue with the data but the issue resolves itself if i declare a local variable within the stored procedure, like this and then use that local variable in place of the parameter. The only places that this parameter is used is within WHERE clauses within my stored proc.

CREATE PROCEDURE [dbo].[Stored_Proc_Name] (@ParamID NVARCHAR(255) = NULL)
AS
DECLARE @localID nvarchar(255)
SET @localID = @ParamID

The issue is resolved and the procedure runs fine.

Any ideas on the next step of the investigation or something obvious that I've missed?

Update:

Upon further investigation, this was an issue with an XML attribute missing a [1] so it appears to have been trying to convert multiple attributes which was causing the error. I'm currently investigating how it was fixed with the variable and will update once I have a sensible answer. I'm going to mark the answer as accepted so that somebody gets the bounty.

I'm wondering if the parameter/variable situation is really just masking an unrelated implicit conversion within your query.  This is a stretch, and someone who's better with the internals of SQL Server may be able to clarify if I'm completely wrong here, but I suspect you are getting different execution plans when switching between the parameter and variable approach.  The differences in the plans may be drastic enough where an implicit varchar → int conversion error is only occurring with the parameterized approach based on join order as opposed to the variable approach as the variable isn't being sniffed properly and an entirely different join order is being used as a result.

To test my theory, throw an OPTION (RECOMPILE) statement at the end of your query when you convert your stored procedure to use a variable and see what happens.  If the error occurs again, you are probably running into a different implicit conversion within your query that only occurs with a particular execution plan, meaning the error you're seeing isn't related to the input, but rather data already in the system and how you're joining tables within your statement.


MySQL LIMIT within JOIN I have an 1:m association.

symbols table has many rows in company_key_statistics table. company_key_statistics has column createdAt which is timestamp indicating when row has been created.
I need to join latest symbols with company_key_statistics, but I need only latest company_key_statistics.
For example I need to get ORCL and MSFT symbols and only their latest company_key_statistics.

So far I have tried this.

SELECT `symbols`.`id`,
       `symbols`.`symbol`,
       `statistics`.
       `marketCapitalization`
FROM   `symbols`
       LEFT JOIN (SELECT `s`.`companyId`,
                         `s`.`marketCapitalization`
                  FROM   `company_key_statistics` AS `s`
                  WHERE  `s`.`companyId` = `symbols`.`id`
                  ORDER by `createdAt`
                  DESC LIMIT 1) AS `statistics`
ON     `symbols`.`id` = `statistics`.`companyId`
WHERE  `symbols`.`symbol` IN ('ORCL', 'SNAP');

But unfortunately I found that I can't use columns from parent query in JOIN subqueries.

How can I achieve to this?

For a correlated subquery you should use a CROSS APPLY, unfortunately MySql doesn't allow it, but you can simulate it on this way:

SELECT     `symbols`.`id`,
           `symbols`.`symbol`,
           `statistics`.
           `marketCapitalization`,
           `s`.`companyId`,
           `s`.`marketCapitalization`
FROM       `symbols`
INNER JOIN `company_key_statistics` `s`
ON         `s`.`pk` = (SELECT `pk`          -- the PK of company_key_statistics
                       FROM   `company_key_statistics`
                       WHERE  `companyId` = `symbols`.`id`
                       ORDER by `createdAt`
                       DESC LIMIT 1)
WHERE  `symbols`.`symbol` IN ('ORCL', 'SNAP');

You can find some example on SO.


In Oracle how do I put certain tables or partitions in a different group so the rest of the db can be brought up with OR without them? I've done this with MS SQL Server in the past where data that is not required for disaster recovery for instance is in a separate file group and file and not cloned to save significant space and bandwidth.

Example info on the partial restores we used for SQL Server here: https://msdn.microsoft.com/en-us/library/ms190394.aspx

Is something like this possible with Oracle? We're using RAC in case that matters.

To clarify I'm NOT asking about how to setup disaster recovery. Forget about RAC and DR if that adds confusion. How do I put certain tables or partitions in a different group so the rest of the db can be brought up with OR without them.

Put the tables in a different tablespace. Then you can put the datafiles under a different location/group/naming scheme to the rest of the data, and configure your backups to ignore these files.

If you need to recover the database without this data, you should drop the tablespace with the database in mounted mode:

startup mount;
drop tablespace your_unneeded_data including contents;
alter database open;


"Incorrect syntax near 'UNSIGNED'" when I run the following query, I get the error "Incorrect syntax near 'UNSIGNED'". Can you please help me figure out what's going on? Thanks!

create table  employees (
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT primary key ,
    firstname varchar(25) not null,
    lastname varchar(25), not null,
    email varchar(45) not null,
    phone varchar(10) not null);

The error message suggests to me you are actually running your DDL statement against a SQL Server instance rather than a MySQL one.

If your server is SQL Server and you mistakenly thought it was MySQL and wrote your DDL using the MySQL syntax, you need to fix not just the UNSIGNED part but also AUTO_INCREMENT. In SQL Server's flavour of SQL, called Transact-SQL, that line would look like this:

id tinyint NOT NULL IDENTITY primary key,

You might also want to switch to smallint, because, as SQL Server does not support unsigned types, you will have only 127 positive values using the tinyint type. Additionally, if you want to ensure the values cannot be negative or nil, you can add a check constraint:

id smallint NOT NULL IDENTITY primary key check (id > 0),

just to make sure that incorrect values cannot be inserted explicitly (when temporarily switched to the SET IDENTITY_INSERT ... ON mode).

If you did mean to run the statement in MySQL, then just check your connection settings.


PostgreSQL - Who or what is the "PUBLIC" role? I was looking at information_schema.role_table_grants when I saw public in grantee column, then I've checked at information_schema.enabled_roles but this role_name does not exist.

=> Who or what is the public role?

Right from the manual:

  The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC.


MySQL replication and master users on slave server Do the users initiating inserts on the master server also have to be on the slave with at least as many permissions for the replicated queries to be correctly executed? My understanding is that you only have to have a replication user with enough permissions but I still have a doubt.

Moreover, let's say that the slave server has the same users as the master server, in addition to a replication-dedicated user, but with less permissions, non writes for instance. Would there be any kind of conflicts resulting in the replicated queries not being executed?

The idea is to have a read-only slave with only SELECT perm users to access to data, not necessarily the same as the master's users, plus a replication-dedicated user on the slave with REPLICATION SLAVE perm. Would all replicated queries then supposed to be executed normally? That's my actual wondering.

The "user" that the Slave connects to the Master with -- It needs only REPLICATION SLAVE, no other privileges.  Replication's goal is to keep the Slave identical to the Master.  This goal overrides any privilege issues.
The Slave should have read_only = ON as an extra protection.
"Users" (either human or application), other than the system admin, should not have SUPER privilege.  This keeps you out of trouble, and helps prevent hacking.
Another hacking prevention is to GRANT users access only to dbname.*, not *.*.  The latter would include the system tables where all the Grants are stored.
Users need write permissions when writing to the Master.  Don't worry about whether the users have write perm to the Slave; the Slave user bypasses that kind of check.  (This is probably the answer you were looking for.)
Users need only read permission when reading from the Slave.
TEMPORARY tables follow different rules.  (It is something you might want to use on a read-only Slave.)  I hesitate to spell them out here, because I am not sure of the rules.


How to Export MS SQL DB from Docker-Linux to Access format I have a MS SQL database which is currently up and running on my Mac within a Docker container that is hosting the Ubuntu Linux version of MS SQL Server.  I need to export the database to a format that can be imported into an Access (yes, Access) database.

Alternatively, I would need to export all 300+ tables to CSV, and hopefully would not need to do them one by one.  Is there any way to export all tables en masse to CSV?  But this is an alternative option.  I still prefer the Access-compatible export since our new SIS vendor apparently uses it. Thanks!

Follow step by step guidance using Microsoft SQL Server Management tool.
Invoke context menu on your database Tasks -> Export Data..., where you can choose proper file name, encoding, delimiters.


Get transactions that failed or were never committed Is there anywhere I can look to find failed transactions in SQL server 2012?

No, SQL Server does not maintain any history about transactions that were aborted / rolled back that is trivial to get to and doesn't introduce additional potential problems (as outlined in @ooutwire's answer). Or even transactions that have been committed.

You will have to perform your own logging within your error handling, or capture specific transaction-related events using server-side trace or Extended Events.

Trace:

Extended events:


Is there some function in PostgreSQL to simply check if the input string is valid UTF-8 according to PG? For several years now, I've been hunting this extremely quick-footed little bug which knows every hiding place in my metaphorical coding house. He lurks in the walls, sometimes takes months to show himself, and BAM! He's back, without forewarning. And the hunt continues. And then he runs into a hole in the wall and I can't get him out again no matter what. And so the wait begins...

To make a long story short: PHP and PostgreSQL simply do not have a fully consistent idea of what constitutes a valid UTF-8 string. I can now say this for 100% sure.

To avoid the errors that inevitably are logged when I try to INSERT data which PHP claims is valid UTF-8 but PostgreSQL thinks is not (regardless of who is actually right), I must make some kind of pre-query whose only job is to check if the string is valid UTF-8 according to PostgreSQL. Is this possible somehow?

I'm thinking of something like this:

SELECT is_string_valid_utf8_according_to_postgresql($1);

This would then return a true/false so that I can know for sure if I can go ahead and execute the real, INSERT query, or skip it.

PostgreSQL is very strict about encodings, so you normally won't be able to get an incorrectly encoded string into the database. That is, your proposed function cannot operate on text, because you won't get an incorrectly encoded string into the database as text.

But if you load the string as binary data (type bytea), you can check the string with a function like this:

CREATE FUNCTION is_valid_utf8(bytea) RETURNS boolean
   LANGUAGE plpgsql AS
$$BEGIN
   PERFORM convert_from($1, 'UTF8');
   RETURN TRUE;
EXCEPTION
   WHEN character_not_in_repertoire THEN
      RAISE WARNING '%', SQLERRM;
      RETURN FALSE;
END;$$;

Here it is in action:

SELECT is_valid_utf8('\x736368c3b66e');

 is_valid_utf8
---------------
 t
(1 row)

SELECT is_valid_utf8('\x736368c3f66e');

WARNING:  invalid byte sequence for encoding "UTF8": 0xc3 0xf6

 is_valid_utf8
---------------
 f
(1 row)


Oddities with AT TIME ZONE and UTC offsets I don't understand the difference between these two columns. America/Chicago timezone is UTC-6, so I expect both to return the same result:

select timezone('America/Chicago', '2017-01-01 12:00:00'::TIMESTAMP AT TIME ZONE 'UTC'),
       timezone('UTC-6'          , '2017-01-01 12:00:00'::TIMESTAMP AT TIME ZONE 'UTC');

However, the result is:

2017-01-01 06:00:00 | 2017-01-01 18:00:00

Also, this behavior is highly awkward,

SELECT '1:00 -1'::time with time zone AT TIME ZONE '-1';
  timezone
-------------
 03:00:00+01

Can anyone explain this?

A time zone name carries more information than an abbreviation or a simple time zone offset. 'UTC-6' is a "POSIX-style time zone specification" which is just an abbreviation plus offset.
The manual on Time Zones:

  PostgreSQL allows you to specify time zones in three different forms:

  A full time zone name, for example America/New_York. [...]
  A time zone abbreviation, for example PST. [...]
  In addition to the timezone names and abbreviations, PostgreSQL will
  accept POSIX-style time zone specifications of the form STDoffset or
  STDoffsetDST, where STD is a zone abbreviation, offset is a numeric
  offset in hours west from UTC, and DST is an optional daylight-savings
  zone abbreviation, assumed to stand for one hour ahead of the given
  offset.

The difference you observe stems from another oddity. You have to use + instead of -:

timezone('UTC+6', '2017-01-01 12:00:00'::TIMESTAMP AT TIME ZONE 'UTC')

The manual again:

  Another issue to keep in mind is that in POSIX time zone names,
  positive offsets are used for locations west of Greenwich. Everywhere
  else, PostgreSQL follows the ISO-8601 convention that positive
  timezone offsets are east of Greenwich.

However, even after fixing the offset error, both expressions are still not equivalent. Among other things, a time zone name like 'America/Chicago' also considers rules for daylight saving time (DST).

BTW, your expression can be simplified to:

timestamptz '2017-01-01 12:00:00 +0' AT TIME ZONE 'UTC+6'

But you probably want the time zone name, to be safe:

timestamptz '2017-01-01 12:00:00 +0' AT TIME ZONE 'America/Chicago'

Related:

"AT TIME ZONE" with zone name PostgreSQL bug?
Ignoring timezones altogether in Rails and PostgreSQL

To address your second example:

SELECT '1:00 -1'::time with time zone AT TIME ZONE '-1';

Simplified, equivalent syntax:

SELECT timetz '1:00 -1' AT TIME ZONE '-1';

The two instances of the literal -1 have different meaning. The first offset is part of the *timetz* literal signifying a location east of Greenwich (complying to the SQL standard). The second is a POSIX-style time zone specifications signifying an offset west of Greenwich. In the absence of a zone specification, UTC is assumed as base. See:

Why does postgresql interpret numeric UTC offset as POSIX and not ISO-8601?

Also note that the AT TIME ZONE construct returns timetz for timetz input. It just re-bases the time literal on a different offset. This is different from its use with timestamp / timestamptz input, where the data type is also switched.

But do not use the data type timetz (time with time zone) at all. It's broken by design and only included in Postgres because it's part of standard SQL. It's use is explicitly discouraged. See:

What is a valid use case for using TIME WITH TIME ZONE?


SQL errors Microsoft.ACE.OLEDB.12.0 on dev box, but works on server I'm inserting into a .xls spreadsheet from TSql code.  Runs fine as an Agent job and on command from the server, but not on my dev box.

Both complex and simple commands like:

Insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\test.xls;', 'SELECT * FROM [Sheet1$]')
SELECT 'hello'

Select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\test.xls;', 'SELECT * FROM [Sheet1$]')

SQL Server 2008 R2 64bit installed on Windows Server 2008 R2. SSMS installed, these commands work there.

When trying to run the same commands from another computer (my dev box), I can't do it, even though I've tried the suggestions below and elsewhere. I have Windows 7 64bit, SSMS 2014 installed here, but had the same issues with 2012 and 2008 R2.

When I run this command in SSMS on my dev box, does it send the entire command to the SQL server? Which security context does it run in? I really want to be able to develop these kinds of stored procs on my dev box, right now this is the only reason I ever RDP to my server, which I'd rather not do.

I've tried the steps listed here: [http://www.codeproject.com/Questions/692004/The-OLE-DB-provider-Microsoft-ACE-OLEDB-for-l] to no help.

EDIT: The error I'm getting is:
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

UPDATE: Turns out installing the 2007 Access Engine allows me to work with Microsoft.ACE.OLEDB.12.0 from my dev box, but only when they're stored directly on the SQL Server.  My files are stored on a separate computer (accessed via share), and I still cannot work with those files.  Looks like a double-hop permissions issue, but I still can't find it.

EDIT: I can do these queries from my dev box when logged in as SA, but not as myself.

Have you tried to give permissions to the temporary folder that may be in use for the user? (The root of the drive)
It would be very common that your dev box would have permissions to C:\stuff and not on another box.

A simple way to test would be to use xp_cmdshell (if enabled, included the enable and disable stuff since its pretty dangerous):

exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure
go
exec xp_cmdshell 'nul >> C:\test.xls'
go
exec sp_configure 'xp_cmdshell',0
go

exec sp_configure 'show advanced options',0

go
reconfigure

When I have an issue, I will get the response:
Access is denied.

Also, if the issue is that the server cannot access the files locally and you try this method, cmdshell will fail because cmd doesnt handle UNC paths well.
You could invoke powershell within xp_cmdshell (oh gawd), but I leave that to another SO answer https://stackoverflow.com/questions/16436405/how-to-run-powershell-in-cmd


Proper name for reference tables in OLTP I know this might be a silly question. But I really appreciate accepting and answering this question.
When I have an OLTP database with some big tables like orders and items. I have other tables (similar to dimension tables in OLAP) where we store reference values, such as: currency, city, category.
What is the proper name for those reference tables? Can we still call them dimension tables in OLTP or do they have their own generic name?

A reference table generally loosely fits the definition of a dimension table or sometimes you'll hear the less formal term enum table for reference tables with a unique set of values. I think it's generally fair to use all three terms interchangeably.
Generally when your schema follows the Star Schema or Snowflake Schema, you reference the tables by either the terms fact tables for your measured quantitative values and dimension tables for your qualitative values.


How to get the SUM of custom made query I have created a relation database structure below,

from this relational table i want to get the SUM of all data, below my query,

SELECT ((BASE_InvoiceLine.Price - IIf(SUM(BASE_Expense.Amount) IS NULL, BASE_PurchaseLine.Price, (BASE_PurchaseLine.Price + SUM(BASE_Expense.Amount)))) + BASE_PurchaseLine.Recycle) AS ProfitRecycle
FROM BASE_PurchaseLine INNER JOIN BASE_InvoiceLine ON BASE_PurchaseLine.PurchaseLineId = BASE_InvoiceLine.PurchaseLineId AND (BASE_PurchaseLine.IsDelete = 0) INNER JOIN BASE_Invoice ON BASE_InvoiceLine.InvoiceId = BASE_Invoice.InvoiceId AND (BASE_Invoice.IsDelete = 0) AND (BASE_InvoiceLine.IsDelete = 0) LEFT OUTER JOIN BASE_Expense ON BASE_PurchaseLine.PurchaseLineId = BASE_Expense.PurchaseLineId AND (BASE_Expense.IsDelete = 0)
GROUP BY BASE_PurchaseLine.Price, BASE_InvoiceLine.Price, BASE_PurchaseLine.Recycle

Above query display all the values only.. problem is how to get the Sum of this Query result,

Thanks

The easiest is to wrap your query in a derived table or CTE and then do SUM :

SELECT sum(ProfitRecycle) AS SumProfitRecyxle
FROM
    (
    --
    -- your query here
    --
    ) AS dv ;


Slow SQL Server database on a specific server I have a SQL Server database on a production server (Windows server 2012, Intel Xeon, 24 GB RAM).
The same database is present on my computer (Windows 10, Intel I3, 8 GB RAM).

When I'm querying the databases the response time is ~2 times longer on the production server.

The size of the databases and log files are identical.

Have you tried running Adam Machanic's sp_WhoIsActive while the query runs on prod?  This will show you other queries that may be taking up resources or blocking your query.

Can you confirm that the isolation level is the same for the database on the server versus your laptop?  Different isolation levels can contribute to various issues related to blocking.  You can check that by running the below in the context of the database in question.

SELECT
[name] AS DBName
, snapshot_isolation_state_desc
, is_read_committed_snapshot_on

FROM sys.databases


Screwed up replication by sharing server ids So I had my screwup for the week today.  I was adding another pair of slaves and set the same server ID on the new slave as an old slave.

The layout kind of looks like

Master
|     |
\/    |
oldS  |
      \/
      newS1
      |
      |
      \/
      newS2

So to be clear the old slave (oldS) and the first new slave (newS1) share the same server ID.

It's not circular replication so I'm hoping things will turn out okay.  I wouldn't have expected the fallout though.

Alarms started going off b/c oldS1 started falling farther and farther behind.  Looking at the logdir it was making thousands and thousands of empty relay logs.

I stopped slaving on newS1 and that seemed to clear things up in that oldS1 stopped making empty relay logs and caught back up.

Both slaves seem to be in a consistent state up to the point I stopped slaving on newS1.

Will fixing everything be as simple as bouncing newS1 with a new, unique ID be kosher, especially considering newS1 is itself slaving to newS2?
Is there anything else to be cautious about?
Why did this result in oldS spawning empty relay log after empty relay log?  I would have though oldS and newS1 had no knowledge of the others existence.
I thought relay log rolling was just determined by the slave itself.  Is the master sending some signal that it should spawn a new relay log?

From my standpoint, you may have potentially introduced data drift into replication.

Baron Schwartz presented this as a puzzle in his blog.

You may have to reload oldS and newS1 with fresh data.

At the very least, you should use pt-table-checksum to see if

the data on Master differs from oldS
the data on Master differs from newS1

If the differences are not identical, you can either

reload  oldS and newS1 fresh
run pt-table-sync

Before you touch anything, please fix the server-id situation

The spawning of relay logs is to be expected because sibling slaves take turns getting SQL entries from the Master. They simply cannot share the same server-id. The Master will somehow alert subsequent slaves that I gave server-id an SQL statement already. Thus, the I/O thread on subsequent slaves will disconnect and retry. Consequently, empty relay logs increase. (Trust me, I have shot myself in the foot with this years ago).

This methodology of the Master talking to Slaves for this info allowed MySQL (eh, Oracle) to  come up with semisync replication. This would break semisync replication as well. Even though MySQL 5.6 will soon introduce a Global Transaction ID into the mix, server-id will still be used in its method of checks-and-balances on the Master. After all, if an eagle had
 two eaglets, no eagle would spit into two mouths at the same time in order to feed them.


Is there a more efficient way to apply a new schema to an existing collection when there's existing data? I'm needing to add a new schema to the schema collection on an existing table. Right now, I simply unbind the old schema collection from the table column, drop it, create the new schema, and rebind it to the table column.

The problem is, I need to preserve all of the data already in the table. What looks like is happening, is that when I apply the new schema, it goes through all of the data and revalidates everything which takes a really long time. Is there a better way to do this?

What I've tried so far is to rename the existing the table (e.g. Mytable_bak), then recreate the same table but with the new schema collection attached, select all of the data out of the _bak table into the newly created table, and then drop the _bak table. This is how I've done in the past and it seems to be a bit faster (not sure why). The problem with this is that it's really just a big pain. The table is actually published for replication which causes it to throw errors. I guess maybe if there was a way to temporarily disable replication for the table, make the migration, then re-replication, then that might work. But this is kind of out of my current knowledge.

So I'm wondering if there's a better way to achieve all of this, or maybe just a solution to my current approach?

You can do some limited adjustments to the schema with ALTER XML SCHEMA COLLECTION.  Here's a simple example:

USE tempdb
GO

IF OBJECT_ID('dbo.users') IS NOT NULL DROP TABLE dbo.users
GO
IF EXISTS ( SELECT * FROM sys.xml_schema_collections WHERE name = N'xsd_test')
DROP XML SCHEMA COLLECTION xsd_test
GO

-- Create a simple schema
CREATE XML SCHEMA COLLECTION xsd_test AS '<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="users">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded" name="user">
                    <xs:complexType>
                        <xs:attribute name="name" type="xs:string" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'
GO

-- Create a table with XML bound to that schema
CREATE TABLE dbo.users ( userId INT IDENTITY PRIMARY KEY, userXML XML(xsd_test) )
GO

-- Populate the table
INSERT INTO dbo.users ( userXML )
VALUES
    ('<users><user name="Sinaesthetic"/></users>' ),
    ('<users><user name="wBob"/></users>' )
GO

-- Alter the schema
IF EXISTS ( SELECT * FROM sys.xml_schema_collections WHERE name = N'xsd_test')
ALTER XML SCHEMA COLLECTION xsd_test
ADD '<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="skills">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded" name="skill">
                    <xs:complexType>
                        <xs:attribute name="name" type="xs:string" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'
GO

UPDATE dbo.users
SET userXML.modify('insert <skills><skill name="someSkill"></skill></skills> after (users)[1]')
WHERE userId = 1


MySQL select...into variable yields null, even though data is there My stored procedure performs SELECT ... INTO var yielding NULL, but if I repeat the same SELECT myself, I get a value.

Here's the relevant part of the schema

CREATE TABLE UrlAuthority
(
     id              BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,name            VARCHAR(255) NOT NULL COMMENT 'includes TLD suffix'
    ,UNIQUE(name)
)
ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE UrlFqdn
(
     id              BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,authority       BIGINT NOT NULL COMMENT 'references example.com'
    ,name            VARCHAR(255) NOT NULL COMMENT 'host.example.com'
    ,FOREIGN KEY (authority) REFERENCES UrlAuthority (id)
    ,UNIQUE (authority, name)
)
ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE Url
(
     id              BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,fqdn            BIGINT NOT NULL
    ,path            VARCHAR(255) NOT NULL
    ,FOREIGN KEY (fqdn) REFERENCES UrlFqdn (id)
    ,UNIQUE (fqdn, path)
)
ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

And here's the procedure

CREATE PROCEDURE UrlToId (url TEXT)
BEGIN
    DECLARE host      TEXT;
    DECLARE authority TEXT;
    DECLARE tld       TEXT;
    DECLARE pathtext  TEXT;

    DECLARE authority_tld TEXT;
    DECLARE fqdn          TEXT;
    DECLARE authority_id  BIGINT;
    DECLARE fqdn_id       BIGINT;
    DECLARE url_id        BIGINT;
    DECLARE err           TEXT;

    CALL UnpackURL (url, host, authority, tld, pathtext);

    SELECT JoinHostNames(authority, tld)      INTO authority_tld;
    SELECT JoinHostNames(host, authority_tld) INTO fqdn;

    INSERT IGNORE INTO UrlAuthority (name) VALUES (authority_tld);

    SELECT id
    INTO authority_id
    FROM UrlAuthority
    WHERE name=authority_tld;

    -- LOG ('UrlAuthority', authority_tld, authority_id)

    INSERT IGNORE INTO UrlFqdn (authority, name)
    VALUES (authority_id, fqdn);

    SELECT id
    INTO fqdn_id
    FROM UrlFqdn
    WHERE authority=authority_id AND name=fqdn;

    -- LOG ('UrlFqdn', fqdn, fqdn_id)

    INSERT IGNORE INTO Url (fqdn, path) VALUES (fqdn_id, pathtext);

    -- LOG ('Url', fqdn_id, pathtext)

    SELECT id FROM Url WHERE fqdn=fqdn_id AND path=pathtext;
END
|

The LOG lines are a bit of metaprogramming, they insert strings into a debug table.

If I run

call UrlToId('http://mail4.z.uk/foo/bar');

I get these debug messages

UrlAuthority authority_tld=z.uk authority_id=1
UrlFqdn fqdn=mail4.z.uk fqdn_id=NULL
Url fqdn_id=NULL pathtext=/foo/bar

Clearly the problem is that fqdn_id=NULL however, if I manually repeat the query which does SELECT id INFO fqdn_id and substitute the values of the variables indicted in the trace:

    SELECT id
    -- INTO fqdn_id
    FROM UrlFqdn
    WHERE authority=1 /* authority_id */ AND name='mail4.z.uk' /* fqdn */;

This selects 1 as expected.

The select gets the data, so why is the variable NULL afterwards?

For a while, this one eluded me. I don't like SELECT ... INTO, so I really wanted to blame it for your trouble, but no.

Here'e the problem.

SELECT id
  INTO fqdn_id
  FROM UrlFqdn
 WHERE authority=authority_id AND name=fqdn;

How does MySQL interpret this?

SELECT id
  INTO fqdn_id
  FROM UrlFqdn
 WHERE 'mail4' = 1 AND name = 'mail4.z.uk';
/* Impossible WHERE */
/* see DECLARE authority TEXT; */

  “A local variable should not have the same name as a table column.   If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable.”

  — http://dev.mysql.com/doc/refman/5.7/en/local-variable-scope.html

Using the same identifier for a variable and a column name makes a debugging mess, one way or another.


Exclude Supplemental Logs from Import I am trying to import tables from a production environment, to a test environment.  The production environment has supplemental logs on a number of tables for golden gate replication.  The supplemental logs are being included in the import, which isn't a problem, but they aren't needed so I would like to exclude them.
I am using a par file to do the import currently, and while I feel like I should be able to exclude the supplemental logs, but I don't see an obvious exclusion type
SCHEMAS=PROD_SCHEMA
REMAP_SCHEMA=PROD_SCHEMA:TEST_SCHEMA
REMAP_TABLESPACE=\"TBS_1\":TEST_TBS
REMAP_TABLESPACE=\"TBS_2\":TEST_TBS
REMAP_TABLESPACE=\"TBS_3\":TEST_TBS
parallel=32
cluster=n
DIRECTORY=DPUMP
TABLE_EXISTS_ACTION=REPLACE
DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
EXCLUDE=TABLE:"IN ('TABLE1','TABLE2')"
EXCLUDE=TRIGGER
EXCLUDE=CONTRAINT

Supplemental logging options are treated as contraints by Data Pump.
create table t1(c1 number);
alter table t1 add supplemental log data (all) columns;

$ expdp bp/bp tables=t1
...
$ impdp bp/bp sqlfile=t1.sql
...
$ cat /u01/app/oracle/homes/OraDB19Home1/rdbms/log/t1.sql
-- CONNECT BP
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "BP"."T1"
   (    "C1" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  TABLESPACE "USERS" ;
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT --   <---------------------
ALTER TABLE "BP"."T1" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;    --   <---------------------
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

EXCLUDE=CONSTRAINT will take care of it, which you already have in your parfile. However this will exclude the real constraints too. If you want to keep the other constraints, just drop the supplemental log groups after the import.


SQL Server 2016 Temporal tables metadata Is there any way to get metadata (information scheme) information about temporal table feature for the table, like:

Is DATA_CONSISTENCY_CHECK TURNED ON OR OFF
What name of history table
Is SYSTEM_VERSIONING turned for the table

Is DATA_CONSISTENCY_CHECK TURNED ON OR OFF?

This is a one time operation when the table is created. This will not appear anywhere in the database because it is a one time operation.

  When creating a link to an existing history table, you can choose to perform a data consistency check. This data consistency check ensures that existing records do not overlap. Performing the data consistency check is the default. Use this argument in conjunction with the PERIOD FOR SYSTEM_TIME and GENERATED ALWAYS AS ROW { START | END } arguments to enable system versioning on a table.

You can see this yourself:

select * from sys.tables
order by name

select * from sys.key_constraints

select * from sys.check_constraints

select * from sys.sysconstraints

select definition,name
from sys.check_constraints

select definition,name
from sys.default_constraints

Likewise, you can perform this check after the creation of the table too:

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

MSDN on DBCC CHECKCONSTRAINTS

MSDN Source

  What is the name of history table?

You can use this query to find the history tables:

SELECT NAME
    ,object_id
    ,temporal_type
    ,temporal_type_desc
FROM sys.tables
WHERE temporal_type_desc = 'History_Table'
ORDER BY NAME

If you query around sys.tables, you will find a lot of handy information.

Source: MS SQL Tips by Ameena Lalani

  Is SYSTEM_VERSIONING turned on for the table?

SELECT name, object_id,temporal_type,temporal_type_desc FROM sys.tables
where temporal_type in (1,2)
order by name


When do hash indexes become reasonable? MySQL natively doesn't support hash indexes. So, making a pseudo hash column and creating an index on a hash column needs some thinking.

And it seems to be widely understood that if the text field is long then hashes are worth the overhead they require. But how long should text be to start thinking about hash?

If I intend to use 128bit hash then how many characters threshold would be a sufficient minimum for a text column?

Edit
Details listed in my another question maybe useful https://stackoverflow.com/questions/7403167/index-on-url-or-hashing-considering-ram

I'm not sure what are your reasons for wanting a hash index.

But if you are looking for indexing text/varchar columns then you can go with MyISAM/full-text or you can use something external like Sphinx or Lucene.

You can alternatively, create an additional column with a hashed version of another and as long as its 1000 bytes or less, then it can be indexed by MySQL.

I hope I have answered your question.


Find what data is placed in a datafile I have a database with 5 .ndf (secondary) datafiles on SQL Server 2008 R2. (Each of these datafiles is assigned to a separate filegroup.) I want to know what data is there in those files. Is it possible? I know there are full text indexes in these filegroups (and since there is only one file per filegroup maybe the sole purpose of creating this many files were to place full text indexes in different filegroups, I don't know). I want to make sure there is no data other than full text indexes.
Thank you

The following SQL will show you which file groups your tables and indexes are in which will make it easy to see if there is data in any file group that shouldn't have data.

SELECT      f.[name] AS FileGroupName
            , o.[name] AS ObjectName
            , o.[type] AS [Type]
            , i.[name] AS IndexNAme
            , i.[index_id] AS IndexId

FROM        sys.indexes i
INNER JOIN  sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN  sys.all_objects o ON i.[object_id] = o.[object_id]

WHERE       i.data_space_id = f.data_space_id
            AND o.type = 'U' -- User Created Tables

ORDER BY    f.[name]
            , o.[name]
            , o.[type]
            , i.[name]

full details of the script can be seen here


Can SQL Server WebAdmin degrade the performance of my site? Once upon a time a had to use the Parallels WebAdmin interface.

After contacting the support team that hosts my website and informing them that I must use the Webadmin, they informed me that:

  MS SQL WebAdmin can degrade the performance of your site, database and the overall server.

Is that true? How can a tool like that degrade performance? Should I try searching for a different web hosting provider?

Any application that runs queries against a SQL Server (or any database engine) instance will decrease the performance because it's extra load the server has to deal with. I assume the warning is just to cover themselves, legally.

What it comes down to is the scripts you run, and how much activity the application generates on its own. There may be options to disable the latter, which is usually a good idea when working against a production instance.

I'm not familiar with the particular product you mentioned (Azure?), so for example from Management Studio, I always disable IntelliSense by default on a production management box, because this runs queries behind the scenes to do its work. Activity Monitor is also a big consumer of resources, and sometimes people leave that tab open and forget about it.

So again, their warning is a standard thing; I would probably just ignore it.


Maximum length or special character restrictions for PostgreSQL 13 user password? I'm using a password generator to come up with a password that will be automatically pulled from Azure Key Vault and into the Postgres deployment. The passwords are manually added in Azure Key Vault with the UI and not CLI.
I've tried 256-character length passwords with and without special characters. Each time I then try to login using kubectl port-forward and pgAdmin, and I get a "password authentication failed."
I shorten it to like 16-characters and then it works fine.
So it seems like special characters aren't an issue, but length is. So just curious:

What special characters, if any, will cause an issue in the password?
What is the maximum length of a password?

Note: I've even dropped down to 128 characters and still the same issue.

I was able to answer this one:

What is the maximum length of a password?

The answer seems to be 99. At 100 I was getting the "password validation failed," but not at 99.


How do triggers and check constraints relate to maitaining database rules I was looking at a tutorial on triggers for SQLite. The tutorial said that a use of triggers could be for data validation.

The following code was given as an example:

CREATE TRIGGER validate_email_before_insert_leads BEFORE INSERT ON leads
BEGIN
    SELECT
    CASE
        WHEN NEW.email NOT LIKE '%_@__%.__%' THEN
        RAISE (ABORT, 'Invalid email address')
    END;
END;

My first inclination would be to use a check constraint for validation instead. I did a Google search for situations where one should use a check constraint and when a trigger would be preferable.

Question
My question is, what specific situations should one use a check constraint and what situations should one opt to use a trigger?

You haven't specified a tag in your question so I'll assume that you're asking a generic question despite the SQLite reference (if you're asking a SQLite-specific question, please update your tags).

Generally, I'd suggest using a trigger when you can't use a check constraint.  Different database engines have different levels of support for what you can do in a check constraint.  I don't know of any database engine, for example, that would currently allow you to define a check constraint that references data in a different table or data in a different row of the same table.

If your database engine allows you to define the email format rule in a check constraint, I would use a check constraint.  If your email validation rules were more complex, however, or you were going to have the same validation rules in many places, you may want to encapsulate them in a function.  Once you do that, most database engines are going to require you to use a trigger.  For example, you might want to perform more detailed validations that involve checking to see if the domain is valid, whether the email domain on the row in the leads table was not already in the customer table, etc.  At that point, you're doing something more complicated than what a check constraint can manage.


Does Oracle 11g support CREATE ASSERTION? When I tried to write the following statement:

CREATE ASSERTION ir_collaboration
CHECK(NOT EXISTS(
  SELECT ngo, id_employee, COUNT(*)
  FROM collaboration
  GROUP BY ngo, id_employee
  HAVING COUNT(*) >= 2
  )
);

I got

  Error SQL: ORA-00901: invalid CREATE command

Do I get this error message because it is valid in SQL-92 standard but Oracle 11g does not support it?

Database SQL Language Reference

There is no CREATE ASSERTION in Oracle. RDBMS products do not necessarily implement all features in the SQL standards.

And to add to the above:

Check Constraints

  Restrictions on Check Constraints

  The condition of a check constraint can refer to any column in the
  table, but it cannot refer to columns of other tables.

  Conditions of check constraints cannot contain the following constructs:

  Subqueries and scalar subquery expressions


Cannot create maintenance plan, I get the message "The task with the name '' and the creation name is not registered for use on this computer" I just installed SQL server 2016 (Standard edition) on a new server,
I'm trying to add a Maintenance plan (I've tried back up plan, rebuild indexes and many more)
I'm getting a weird message when I'm trying to open the task:

  The task with the name "back up plan database task" and the creation name is not registered for use on this computer.

At first, I thought it was a permission issue so I went to services.msc and change the login as to a user with administrator permissions - still not working.
Restarting the server - still not working.

I have been having the same issue with SSMS 18 (preview 4) when trying to access any maintenance plan tasks.

I opened this item on the feedback site to capture the bug. Feel free to upvote if this describes the issue you're experiencing.

For now I am just using 17.x versions of SSMS to do any maintenance plan related tasks, since you can have both installed side-by-side.

The bug was acknowledged on 10/15 and it looks like MS is working on a fix, so hopefully the next release resolves the issue.


MySQL CREATE INDEX slowing down My analytics table has 190M rows, about 150GB ins size.
Storage engine is MyISAM with

key-buffer-size=16G
myisam_sort_buffer_size=2G
myisam-max-sort-file-size=200G.

Machine has 32GB memory.
While creating 10 simple indexes (no covering, no prefix, just column names using BTREE) I noticed:

first index - 40 minutes
second index - 50 minutes
...
fifth index - 2 hours
eleventh index - 3 1/2 hours

Each index adds between 1GB and 3GB to the .myi file.
Has anybody experienced a similar increase in run-time? Is there any way to get the performance of the first index creation for the other indexes?

You must be doing something odd with your DDL or you have a very, very old version of MySQL.

MyISAM Index creation can be very lethargic. I wrote about this almost ten years ago.

Oct 10, 2006 : Why does mysql drop index very very slow in a large table? (from the MySQL General Discussion Forum)
May 12, 2011 : Does MySQL still handle indexes in this way? (where I mention this again)

Without any additional details from you, I can only guess. Here it goes:

I suspect your must be just doing the following:

ALTER TABLE mytable ADD INDEX (column01);
ALTER TABLE mytable ADD INDEX (column02);
ALTER TABLE mytable ADD INDEX (column03);
...
ALTER TABLE mytable ADD INDEX (column10);
ALTER TABLE mytable ADD INDEX (column11);

I would like to suggest the following

SUGGESTION #1 : Build all 11 indexes at the same time

You should create all 11 indexes in a single DDL statement

ALTER TABLE myisamtable
    ADD INDEX (column01),
    ADD INDEX (column02),
    ADD INDEX (column03),
    ...
    ADD INDEX (column10),
    ADD INDEX (column11)
;

SUGGESTION #2 : Delay indexes building

You can disable the building of non-unique indexes so that sorting and be done.

Doing so can make a smaller BTREE for each index

ALTER TABLE mytable DISABLE KEYS;
...
ALTER TABLE mytable ENABLE KEYS;

SUGGESTION #3 : Use a temp table

If you wish to test this manually, you can make a temp table, add all the indexes to the empty temp table, disable indexes, insert the data, enable indexes, and switch table names.

Here is a sample of what I just described to you

CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new
    ADD INDEX (column01),
    ADD INDEX (column02),
    ADD INDEX (column03),
    ...
    ADD INDEX (column10),
    ADD INDEX (column11)
;
ALTER TABLE mytable_new DISABLE KEYS;
INSERT INTO mytable_new SELECT * FROM mytable;
ALTER TABLE mytable_new ENABLE KEYS;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

GIVE IT A TRY !!!


MongoDB Tag-Aware Sharding Workflow I wonder the work flow of sharding cluster in Tag-Aware Sharding.

--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "version" : 3,
    "minCompatibleVersion" : 3,
    "currentVersion" : 4,
    "clusterId" : ObjectId("546df06d63a15917a8356f4e")
}
  shards:
    {  "_id" : "shard0000",  "host" : "hadoop4:27017",  "tags" : [  "TR" ] }
    {  "_id" : "shard0001",  "host" : "hadoop5:27018",  "tags" : [  "US" ] }
    {  "_id" : "shard0002",  "host" : "hadoop6:27017",  "tags" : [  "OTHER" ] }

  databases:
    {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
    {  "_id" : "inventory",  "partitioned" : true,  "primary" : "shard0000" }

In tag-aware sharding , with mongorestore command via mongos , first all data writes to PrimaryDatabase and then migrate the appropriate chunks ?

Or it can fill the appropriate chunk according to tagRange at the beggining of the insert?

For example , when i insert {country:"US",keyword:"abc"} document , at first it goes to shard0001 or goes to the primary database shard0000  and then chunk migration to shard0001?

It depends from the distribution of your inserts if moves going to occur and how many.

1) you start with one chunk {minkey,maxkey} which lives on primary (lets say shard000)

2) as you insert data a split will occur. Might be

2.1) {minkey, US} {US,maxkey} -> moves the second chunk to shar001

2.2) {minkey, TR} {TR,maxkey} -> moves first chunk to shard001

2.3) {minkey,OTHER} {OTHER,maxkey} -> moves second shard to shard002

3) I cant analyze all paths but for 2.1)

{US,maxkey} is unmovable and all the chunks come from it as well

{minkey, US} depends if it going to move according to the next split

....


Change-set SQL optimization I'm trying to clean up a change-set query that returns rows from a table that have changed since a given time and also do not exist in a known list of already previously returned results.

select ID, Field1, Field2
from Table1
where Field = 'Foo'
and (
    ID not in ( %s )
    or (
       ID in ( %s ) and
       GREATEST(created, COALESCE(modified, from_unixtime(0))) > from_unixtime(:stamp)
    )
)

Now using some basic 9th grade logic operators....

A or (B and C) = (A or B) and (A or C)

Except in my instance, A = "id not in (%s)" and B = !A. C = the date constraint. Which reduces to...

(A or !A) and (A or C) => A or C

So, following that logic, my where clause should now look like this, but it seems funky. Can anybody more experienced with SQL and/or logic tell me if I've missed something simple?

select ID, Field1, Field2
from Table1
where Field1 = 'Foo'
and (
    id not in ( %s )
    or GREATEST(created, coalesce(modified, from_unixtime(0))) > from_unixtime(:stamp)
)

Does that look right?

Yes, it looks all right. (but only looks so)

Beware! SQL logic is a 3-valued logic, not the same as the "usual" math logic. The three values are TRUE, FALSE and UNKNOWN.

In your case, if the id or the values in the ( %s ) list contain NULL, the two versions are not equivalent. Example: 5 NOT IN (6, 7, NULL) and 5 IN (6, 7, NULL) will both evaluate to UNKNOWN so no row will be returned by the 1st version. While the 2nd version will return the rows that satisfy the other condition greatest() ....

If you have no Nulls, the two versions are equivalent.

You can find the truth tables in Wikipedia: Three-valued logic


How to Set up SSRS Web Services to run under another port than 80? What steps do I have to pace to run Web Service URL and, Report Manager URL, under another port rather than 80? (DBMS is SQL Server 2008R2 and OS is WIn Server 2008R2).

The port can be configured in SSRS Configuration Manager. This will have been installed on the machine when you installed SSRS.

Check this and this BOL articles for all the details.


Calcutation at SQL level I have this query:

SELECT *
FROM (
  SELECT COUNT(lead_status) as 'Pending files'
  FROM vw_file_registrationlist
  WHERE lead_status_id = "1" || lead_status_id="2" || lead_status_id="4" || lead_status_id="5" || lead_status_id="6"
)A,
(
  SELECT COUNT(lead_status) as 'Completed files'
  FROM vw_file_registrationlist
  WHERE lead_status_id = "3"
)B
, (
  select DISTINCT employee_name
  From vw_file_registrationlist
  where employee_name='anna'
)C

I am getting the result as

Name  Pending files  Completed files
----  -------------  ---------------
anna  3              2

I need to calculate the pending files / total files * 100. Is it possible?

This isn't right,

WHERE lead_status_id = "1" || lead_status_id="2" || lead_status_id="4" || lead_status_id="5" || lead_status_id="6"

What you want is,

WHERE lead_status_id IN (1,2,4,5,6);

Seriously, learn to use whitespace and take some pride in your code. Pick a convention (capitalize all sql tokens, and lower case all of your identifiers) Even if you don't get sql who writes like that..

)a
)b

Where did you even get that from? .. SELECT (...) AS t, (...) AS x whitespace, and AS! That all said, I think you want something like this,

SELECT
  count(lead_status) FILTER (WHERE lead_status_id IN (1,2,4,5,6)) as 'Pending files',
  count(lead_status) FILTER (WHERE lead_status_id = 3) AS 'Completed files',
  any(employee_name = 'anna') AS has_anna
FROM vw_file_registrationlist;


Issue with running multiple mysql commands in a bash for loop I try to restore some .sql backups using a shell script with a for loop.

I run by bash command.sh 'password' using the command.sh below. However, when I typed it out, it didn't run and instead, mysql --help message showed up multiple times.. I think somehow the mysql command wasn't invoked. How to do it here?

#!/bin/bash

for i in *sql;
do
    fname=$(basename $i .sql)
    echo "working on $fname"
    mysql -uroot -p $1 $fname < $i
    wait
done

Is something like this anything like what you want?

This is how to run through all the databases in a server and backs them up.

#!/bin/bash

USER="your_user"
PASSWORD="your_password"
OUTPUT="/Users/rabino/DBs"

rm "$OUTPUT/*gz" > /dev/null 2>&1

databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/`date +%Y%m%d`.$db.sql
        gzip $OUTPUT/`date +%Y%m%d`.$db.sql
    fi
done

And in order to cycle through files of a particular type, you could look here.

As an example from this site, you could make use of this script

  Filename Expansion You can do filename expansion in a loop such
  as work on all pdf files in current directory:

#!/bin/bash
for f in *.pdf
do
  echo "Removing password for pdf file - $f"
done

So, you want something like

#!/bin/bash
for f in *.sql
do
  echo "\nworking on filename"
  mysql -u root -ppassword < $f
  echo
done

note: gap between -u and root, no gap between -p and password

Now, the script above isn't very sophisticated (and untested), but that combined with the database script above should give you stuff to work with. In any case, you can Google the terms here and similar and you'll get loads of stuff. A few more bash backup scripts (Rolando is a big hitter on this forum).

Here's a post about a lot of MySQL backup solutions that don't require bash.


ANSI Joins in Oracle We are doing a large ETL load from Oracle 12c. Every now and then Oracle optimizer decides to create a new execution plan for a SQL statement that has previously been running just fine. Usually this results in a 10x performance degradation. When I ask the client DBAs and Oracle consultants (client is a Gold partner), they usually ask us to convert ANSI joins to traditional Oracle joins. And usually this has helped. My question is, is the poor support for ANSI joins acknowledged by Oracle? Does Oracle recommend using traditional joins? (I've read Oracle books stating the exact opposite.)

This question has been answered by Chris Saxon of Oracle's "Ask TOM" team, who explains why ANSI joins are preferable to the old Oracle syntax:

  This is because there are number of restrictions that apply to the (+)
  operator, but not the "outer join" clause. Follow the link for the
  full list.

The article does acknowledge that there can be cases where the old Oracle syntax is necessary (Materialized Views don't support ANSI - should be "fixed" in his view - implying it is a bug).

The documentation link referred to in the article says:

  Oracle recommends that you use the FROM clause OUTER JOIN syntax
  rather than the Oracle join operator. Outer join queries that use the
  Oracle join operator (+) are subject to the following rules and
  restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

(rules excluded - follow link)

In your case, you might want to take a careful look at Plan Management (previously called Plan Stability - nice Oracle white paper here). The site (oracle-base) is a superb site for all things Oracle - I'd put it in my top 5 no matter what I was doing (dev, test, prod...).

The big plus is you won't get major swings in performance as a plan tilts from one optimization to the other - the price you pay for this is that you won't see performance improvements either! If you do implement plan management, then you should revisit those managed plans (on dev/test) from time to time to ensure that they are still optimal (or at least satisfactory).


MySQL Server Status CPU/Load Formula Are there any variables and formula that can help me calculate CPU/Load value of MySQL ?

You could make use of sysstat (also here) and/or nmon - great free tools! Among the sysstat tools are iostat and mpstat (check the link). There's also top - you could look at that also! The pidstat (no separate wiki entry - see sysstat) might be right up your alley - I would urge you to explore the man pages!
What you want are baselines so that when things start to go wrong, you know:

what has been happening over the last minute, hour, day, week, month, year...?

what's happening now - is your I/O swamped, or is it your CPU or your RAM? Also, the network should be under surveillance!

what's happened when you changed parameter X or cancelled report Y

You should be saving the output. The example is for top, but you should be keeping all of your monitoring stats so that you can graph it and trace problems when they occur. Ideally, I would be putting the output into a database - MySQL in your case. For example, sar (System Activity Report) is the sysstat tool for commands:

used to report on various system loads, including CPU activity,
memory/paging, interrupts, device load, network and swap space
utilization

Even though sysstat isn't part of the Linux kernel proper, I would consider it an "honourary kernel module".
Normally, tool recommendations are off-topic here, but these tools have been around since forever and are unlikely to disappear anytime soon. If these don't suit you, a quick internet search will reveal many more - you really are spoilt for choice when it comes to (impressive) F/LOSS monitoring tools!


Trick to remember which instance I am working with I work as a BI developer and always switch between different instances on a same server. However, sometimes I forget the right instance and for example may cause to truncate a table on the other.
I know this is not a technical question but rather experimental. I would be so thankful if you , dear DBAs, could tell me what do you do to always work on the right instance?

You can colour code the status bar in SQL Server Management Studio
Easiest if you use the Registered Servers or Central Management Servers feature and then allocate colours at a group level i.e. Dev (Green) / Test (Yellow) / Prod (Red)
https://documentation.red-gate.com/sp/ssms-tab-management/working-with-ssms-tab-management-quick-ref
btw You may need to spell it color to get a match


Creating a relationship between one field in a table with two fields in another Is the picture below the only way to create the relationship described in the title with referential integrity? Why does Access not allow me to do this without the "duplicate" table? Thanks!

The only reason I see this occurring is if you wrote a query that INNER JOINs twice

EXAMPLE

SELECT tblState.*,tbl_Estimate.JobSiteCity,tbl_Estimate1.JobSiteZip
FROM tblState
INNER JOIN tbl_Estimate
ON tblState.ID = tbl_Estimate.JobSiteStateID
INNER JOIN tbl_Estimate AS tbl_Estimate1
ON tblState.ID = tbl_Estimate1.JobSiteStateID;

Because you are running INNER JOIN twice, the graphical representation has to match. This would indicate the possibility of checking different columns with each INNER JOIN

Let's say you are checking for Jersey City but didn't specify the state.

SELECT DISTINCT tblState.*,tbl_Estimate.JobSiteCity,tbl_Estimate1.JobSiteZip
FROM tblState
INNER JOIN tbl_Estimate
ON tblState.ID = tbl_Estimate.JobSiteStateID
AND tbl_Estimate.JobSiteCity = "Jersey City"
INNER JOIN tbl_Estimate AS tbl_Estimate1
ON tblState.ID = tbl_Estimate1.JobSiteStateID;

This should return JobSites in all ZipCodes from Jersey City, NJ and Jersey City, WI

If you collapse the query to

SELECT DISTINCT tblState.*,tbl_Estimate.JobSiteCity,tbl_Estimate.JobSiteZip
FROM tblState
INNER JOIN tbl_Estimate
ON tblState.ID = tbl_Estimate.JobSiteStateID
AND tbl_Estimate.JobSiteCity = "Jersey City"

you should get the same result, but the picture should only have tbl_Estimate once