Why is SSIS slow to enumerate over many files in a directory and import them?

I think you're running into a limitation of the UI/debugger.

I created two packages: MakeAllTheFiles and ReadAllTheFiles

MakeAllTheFiles accepts as input the number of files to be created. It will make use of pseudo-random function to distribute the data across a number (7) of sub folder.

MakeAllTheFiles

    public void Main()
    {
        int NumberOfFilesToGenerate = (Int32)Dts.Variables["User::FilesToGenerate"].Value;
        string baseFolder = Dts.Variables["User::FolderInput"].Value.ToString();
        System.Random rand = null;
        int fileRows = 0;
        DateTime current = DateTime.Now;
        int currentRandom = -1;
        int seed = 0;
        string folder = string.Empty;
        string currentFile = string.Empty;

        for (int i = 0; i < NumberOfFilesToGenerate; i++)
        {
            seed = i * current.Month * current.Day * current.Hour * current.Minute * current.Second;
            rand = new Random(seed);
            currentRandom = rand.Next();

            // Create files in sub folders
            folder = System.IO.Path.Combine(baseFolder, string.Format("f_{0}", currentRandom % 7));

            // Create the folder if it does not exist
            if (!System.IO.Directory.Exists(folder))
            {
                System.IO.Directory.CreateDirectory(folder);
            }

            currentFile = System.IO.Path.Combine(folder, string.Format("input_{0}.txt", currentRandom));

            System.IO.FileInfo f = new FileInfo(currentFile);
            using (System.IO.StreamWriter writer = f.CreateText())
            {
                int upperBound = rand.Next(50);
                for (int row = 0; row < upperBound; row++)
                {
                    if (row == 0)
                    {
                        writer.WriteLine(string.Format("{0}|{1}", "Col1", "Col2"));                        }

                    writer.WriteLine(string.Format("{0}|{1}", row, seed));
                }
            }
            ;
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }

ReadAllTheFiles

The general appearance of the package is thus

I have two Connection Managers defined: One is to my database and the other is to a Flat File with an Expression on the ConnectionString property such that it uses my Variable @[User::CurrentFileName]

Variables, I like lots of Variables so there are plenty.

My Execute SQL Task simply stands up a table for me to write to, knocking it down if it already exists.

IF EXISTS
(
    SELECT * FROM sys.tables AS T WHERE T.name = 'dbase_54462' AND T.schema_id = SCHEMA_ID('dbo')
)
BEGIN
    DROP TABLE dbo.dbase_54462;
END

CREATE TABLE
    dbo.dbase_54462
(
    CurrentFile varchar(256) NOT NULL
,   Col1 int NOT NULL
,   Col2 varchar(50) NOT NULL
,   InsertDate datetime NOT NULL DEFAULT(CURRENT_TIMESTAMP)
);

My Foreach Enumerator simply looks at everything in my Input folder based on the file mask of *.txt and traverses subfolders. The current file name is assigned to my variable @[User::CurrentFileName]`

The Data Flow is bog standard. The Derived Column Transformation there simply adds in the Current File Name variable into the data flow so I can record it in my table.

Analysis

I'm lazy and didn't want to do anything special to record processing times so I deployed my packages into the SSISDB catalog and ran them from there.

This query looks at the catalog data to find out how long the package ran, how many files it processed and then generates a running average for file count. Run 10047 was bad and was excluded from analysis.

SELECT
    E.execution_id
,   DATEDIFF(s, E.start_time, E.end_time) As duration_s
,   ES.rc AS FilesProcessed
,   AVG(ES.rc / (1.0 * DATEDIFF(s, E.start_time, E.end_time))) OVER (PARTITION BY ES.rc ORDER BY E.execution_id) AS running_average
FROM
    catalog.executions As E
    INNER JOIN
    (
        SELECT
            MIN(ES.start_time) As start_time
        ,   MAX(ES.end_time) AS end_time
        ,   count(1) As rc
        ,   ES.execution_id
        FROm
            catalog.executable_statistics AS ES
        GROUP BY
            ES.execution_id
    ) AS ES
    ON ES.execution_id = E.execution_id
WHERE
    E.package_name = 'ReadAllTheFiles.dtsx'
    AND E.execution_id <> 10047
ORDER BY 1,2

The resulting data (gratuitous SQLFiddle)

execution_id    duration_s  FilesProcessed  running_average
10043   15  104 6.93333333333333
10044   13  104 7.46666666666666
10045   13  104 7.64444444444444
10050   102 1004    9.84313725490196
10051   101 1004    9.89186565715395
10052   102 1004    9.87562285640328
10053   106 1004    9.77464167060435
10055   1103    10004   9.06980961015412
10056   1065    10004   9.23161842010053
10057   1033    10004   9.38255038913446
10058   957 10004   9.65028792246735
10059   945 10004   9.83747901522255

Based on this sampling size, I see no appreciable difference between processing 100, 1000 or 10,000 files with SSIS as described herein.

Root cause assumption

Based on the comment about DTExecUI.exe that says you're running the package from within Visual Studio (BIDS/SSDT/name-of-the-week). To get the pretty color changes and debugging capability, the native execution (dtexec.exe) is wrapped up in the debugging process. That creates an appreciable drag on execution.

Use the design environment to create your packages and to run them for smaller data sets. Larger ones are best handled through the non-graphical & non-debugger execution interfaces (shift-F5 in VS, deploy to SSIS catalog and execution from there, or shell to the command line interface and use dtutil.exe)


The Database Diagram objects are indeed created using T-SQL.  You can see what SSMS does when creating, editing, and saving diagrams by running a T-SQL trace against your development SQL Server while using the Database Diagram tools.

A database that has the Database Diagram objects enabled will contain a dbo.sysdiagrams table.

Many properties use by the Database Diagram system are stored in Extended Properties.  These can be seen looking at sys.extended_properties in the diagram-enabled database.

The following objects are also engaged in the Database Diagram system:

dbo.sp_upgraddiagrams
dbo.sysdiagrams
dbo.sp_helpdiagrams
dbo.sp_helpdiagramdefinition
dbo.sp_creatediagram
dbo.sp_renamediagram
dbo.sp_alterdiagram
dbo.sp_dropdiagram

Hopefully the above details will get you on the road to reverse-engineering the Database Diagram system, however be aware the actual diagrams are stored in a binary format that is, as far as I can tell, not documented.


psql is not prompting me for a password, then denies access

Peer authentication

This means it's using a unix socket connection, and connections for unix sockets are set to use peer authentication in pg_hba.conf. It just checks that the unix user name is the same as the postgres user name requested, and doesn't care about passwords.

If you want password auth, use md5 auth in pg_hba.conf instead.

See the manual.

The way PostgreSQL splits authentication between SQL configuration and a config file is definitely confusing, so you're not alone. Being able to set a password for a user, but having that password be ignored in some contexts and used in others, takes some getting used to. Once you understand the system it makes sense but it's definitely not discoverable and intuitive.


Check if data from database is up to date

Just keep "last updated/last stored" as timestamp value. Easy to do even on a record level (i.e. for each record, and not for whole table).


Create primary keys based upon higher-level entity in access?

I think the difficultly lies in using the parent table as part of the sub-table's primary key. So long as you have a reference to the parent table, you can always generate a reference that combines Location+Room or Room+Rack. Otherwise the primary key will get too hard to update and you have bad primary key.

I can see two ways to tackle this depending on how many locations, rooms, racks you have. It depends on quantity for your interface.

Firstly, a schema...

tblLocation(locationID[PK++], locationReference)
tblRoom(roomID[PK++], keyLocation[tblLocation.locationID], roomNumber)
tblRack(rackID[PK++], keyRoom[tblRoom.roomID], rackReference)
tblDevice(deviceID[pk++], keyRack[tblRack.rackID], positionU, deviceLabel)

...tables for custom devices can extend tblDevice

Foreign keys in square brackets, PK++ means an auto increment primary key.

As you can see, a Location has many Rooms, a Room has many Racks, a Rack has many Devices.

I have made primary keys as auto IDs as I prefer them to be integers and there's less to think about should you change a location/room/rack reference.

Populate this with Locations, Rooms and Racks as appropriate.

Solution 1 :: Not a lot of racks

Have three drop downs on the form.
User chooses a location, you then select all the rooms of that location in the next combobox.
User then selects the room, you then fill the next combobox with the racks in the room.
User then fills in device details and U position (which you probably want to validate to make sure that you don't have anything allocated there.)

Solution 2 :: Lots of racks

Have a query similar to:

SELECT
    rackID,
    locationReference + '-' + roomReference + '-' + rackReference AS [Full Location]
FROM tblLocation
    INNER JOIN tblRoom
        ON tblLocation.locationID = tblRoom.keyLocation
    INNER JOIN tblRack
        ON tblRoom.roomID = tblRack.keyRoom
ORDER BY
    locationReference ASC,
    roomNumber ASC,
    rackReference ASC

To generate a list of Location-Room-Rack references.

ABC-101-A/1
ABC-101-A/1
ABC-101-A/2
ABC-101-A/2
ABC-105-B8
ABC-105-B8
FAY-208-X8
FAY-210-A9T
FAY-210-A9T
FAY-210-F4
NAT-410-78
NAT-410-78

User selects one, then fills in the information. List will be equal to the number of racks (which could get a bit excessive).

Advantages

If you move a rack (or racks) of equipment from one room to another, it's just a simple update query to update the database
If a location/room/rack reference changes, you only have one piece of data to update.

Further schema additions

I've used racks in the past, I simplified the schema but these are some additions that spring to mind.

Add rackSize to tblRack: the size in U's
Add sizeU to tblDevice: the size of the device in U's

You can then do further validation: if you have a 39U rack and somebody tries to put a 5U device in at location 38, you can throw an error (as U's 40-42 don't exist!). Also if somebody puts a 3U device in at location 10, and something is in location 11, you know it won't fit or they have not updated the details of the device that is no longer there!

You could also make some clever queries that would tell you how much space there is in your racks (and where). But that's beyond the scope of this question!

Hope this helps.


Pentaho Kettle - Unable to connect to virtual Oracle Database

So the trick was to go into the Options tab in the Edit Connection dialogue and create a database parameter to define the connection string.


log file very large how to apply changes then truncate sql server 2008 r2

In order to stop your transaction log growth you need to take regular transaction log backups (along with full database backups).

If it is a test database and you don't need to recover it to a point in time in case of a disaster you can switch the database recovery model to SIMPLE (you probably are using FULL recovery because it is default in SQL Server).

Once you run regular log backups you can use DBCC SHRINKFILE to reduce the file size.

Please read the following question: Why Does the Transaction Log Keep Growing or Run Out of Space? - it provides you with a really good understanding of the problem.


Azure Sql data warehouse - is there any way to see the true query plan?

Traditional SQL Server query plan is not available yet.  You can use DBCC PWD_SHOWEXECUTIONPLAN to see estimated execution plan. For actual execution plan you can get a D-SQL plan using Visual Studio. Grant Fritchey explains in details.


Get the list of all Tables, Views, Stored procedures that are not added in a publication for replication

For Snapshot and Transactional:

SELECT
    name
FROM sysobjects WITH (NOLOCK)
WHERE ((xtype = 'U') OR (xtype = 'V') OR (xtype = 'P')) AND
    category <> 2 AND
    name NOT IN (SELECT DISTINCT object_name(OBJID) FROM sysarticles)

For Merge:

SELECT
    name
FROM sysobjects WITH (NOLOCK)
WHERE ((xtype = 'U') OR (xtype = 'V') OR (xtype = 'P')) AND
    category <> 2 AND
    name NOT IN (SELECT DISTINCT object_name(OBJID) FROM sysmergearticles)


No, Logs cannot be disabled.  Even if you set the database option for SIMPLE recovery model, the log will still exist since it is necessary for transactionally consistent activities, which are a mainstay of SQL Server.

Nonetheless, running in the SIMPLE recovery model should reduce the conflicts that you are facing.  Since this is SQL Server 2008, why not try getting rid of the database prior to rebuilding with something like the following:

EDIT: Minor update to script to show how to keep control of the database.

USE master;
GO
ALTER DATABASE yourdatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
GO
USE yourdatabase; -- So that you become the SINGLE_USER for the next step
GO
ALTER DATABASE yourdatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE master;
GO
DROP DATABASE yourdatabase;
GO

The first ALTER may not be necessary, but it helps prevent someone else getting in as the "single" user.


NON-Random UUID keys in custom distributed DB

Yes, you can use a sequentially generated UUID as a primary key.  SQL Server even has it built in:

CREATE TABLE dbo.keytest
(
    kt_guid uniqueidentifier NOT NULL
        CONSTRAINT keytest_pk
        PRIMARY KEY
        CLUSTERED
        CONSTRAINT keytest_kt_guid_df
        DEFAULT (NEWSEQUENTIALID())
);

Insert some rows, and SELECT them:

INSERT INTO dbo.keytest DEFAULT VALUES;
GO 10

SELECT kt.kt_guid
FROM dbo.keytest kt

Results:

╔══════════════════════════════════════╗
║               kt_guid                ║
╠══════════════════════════════════════╣
║ D9A1905A-ADBE-E911-9440-005056805C06 ║
║ DAA1905A-ADBE-E911-9440-005056805C06 ║
║ DBA1905A-ADBE-E911-9440-005056805C06 ║
║ DCA1905A-ADBE-E911-9440-005056805C06 ║
║ DDA1905A-ADBE-E911-9440-005056805C06 ║
║ DEA1905A-ADBE-E911-9440-005056805C06 ║
║ DFA1905A-ADBE-E911-9440-005056805C06 ║
║ E0A1905A-ADBE-E911-9440-005056805C06 ║
║ E1A1905A-ADBE-E911-9440-005056805C06 ║
║ E2A1905A-ADBE-E911-9440-005056805C06 ║
╚══════════════════════════════════════╝

In fact, in situations where good insert performance is desirable, it may be more effective to use sequential GUIDs instead of randomly generated ones.  Sequential GUIDs make great keys since they are monotonically increasing, which reduces unnecessary page splits when inserting rows into the index.


The queries where you have your datet column wrapped within a function (e.g. DATE(), YEARWEEK()) are not efficient due to the fact that MySQL has to evaluate the output of the function for each row in your table. This renders the query as non-sargable (i.e. not able to utilize indexes on the datet column).

It appears you want to select the count of all records within a particular timeframe. To do this, you have to use >=, not =. Also, there is a difference between "this month" and "within one month". I'm assuming you want the former (i.e. if say today was August 3rd, you'd only want records within August, and not from July 3rd, and same thing for week, etc...).

Assuming datet is a DATETIME/TIMESTAMP column and the values of the column will never be "in the future", you can rewrite your queries like so (each of which can utilize an index on the datet column):

-- Today
SELECT COUNT(*) AS count
FROM   log
WHERE  datet >= CURDATE()

-- This Week
SELECT COUNT(*) AS count
FROM log
WHERE datet >= CURDATE() - INTERVAL CASE WEEKDAY(CURDATE()) WHEN 6 THEN -1 ELSE WEEKDAY(CURDATE()) END + 1 DAY

-- Last Week
SELECT COUNT(*) AS count
FROM log
WHERE datet >= (CURDATE() - INTERVAL CASE WEEKDAY(CURDATE()) WHEN 6 THEN -1 ELSE WEEKDAY(CURDATE()) END + 1 DAY) - INTERVAL 1 WEEK
      AND
      datet < CURDATE() - INTERVAL CASE WEEKDAY(CURDATE()) WHEN 6 THEN -1 ELSE WEEKDAY(CURDATE()) END + 1 DAY

-- This Month
SELECT COUNT(*) AS count
FROM   log
WHERE  datet >= CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY

 -- Last Month
SELECT COUNT(*) AS count
FROM log
WHERE datet >= (CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY) - INTERVAL 1 MONTH
      AND
      datet < CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY

-- This Year
SELECT COUNT(*) AS count
FROM   log
WHERE  datet >= MAKEDATE(YEAR(CURDATE()), 1)


DBCC CheckDB run into early hardware failure

When you first kick off a DBCC CHECKDB it will create a database snapshot to allow the database to stay online during the process. There are some things you can look at if you are running into problems with this:

Defragment you NTFS system, there is an issue where if the NTFS storage is heavily fragmented it will not allow SQL Server to create the database snapshot. This is because the snapshot can only have a certain amount of fragments.This is documented on the microsoft support center in OS errors 1450 and 665 are reported for database data files. (I believe your problem lies here)
Check to see if anyone has updated an NTFS filter driver as there can be a problem with alternate filter streams.

Additionally, you can always attempt to create a database snapshot of the database on a less fragmented part of the NTFS and run CHECKDB there to hopefully.


Query that returns a unique ID for each different clause matched?

Schematically:
CREATE TABLE tmp ([parameters group] INT, [client_handle] CHAR(11), [mobile_no] CHAR(15));

INSERT INTO tmp VALUES
(1,'axtwe-wasst','+44 7747 122123'),
(1,'axtwe-wasst','+44 7904 223323'),
(2,'zjfft-albwq','+44 7758 444111'),
(2,'zjfft-albwq','+44 7758 444222'),
(2,'zjfft-albwq','+44 7758 444333');

SELECT DISTINCT
    [client_handle] = tmp.[parameters group],
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive] t
JOIN
    tmp ON t.[mobile_no] = tmp.[mobile_no];

PS. [parameters group] is not used - on shown source data it is excess, but may be useful in real task (if so then GROUP BY instead of DISTINCT must be used).


How to convert default instance to named instance in SQL Server 2012?

Install the named instance, move the databases, agent jobs, etc over to it, then uninstall the now unused default instance.


Can SQL Server Audit be used to track hostnames or IP adresses of the users performing DML on a table?

With SQLAudit you cannot track HostName or IP address.

However, there's an alternative method for auditing, based on streaming extended events. I blogged about it earlier this year.

Basically, you treat locks acquired as indicators of the operations being performed on the tables (shared locks and schema stability locks for reads and exclusive locks for writes) and you capture these events with an extended events session. You will need to hook a script (or an executable) to that session using the streaming API and you can implement your own logic to filter, categorize and persist the events you're interested in.

Another possibility is capturing the "Audit Schema Object Access Event" events with a trace and streaming them to your own custom application. Here's a tutorial. In Extended Events the same event is not available, as it is reserved for the SQLAudit implementation.


Maintaining 24x7 databases is a fairly large topic with lots of options to consider.  This broad topic has many items to consider, but we can try and touch base on some of the high points.

What you first will want to identify is, while many operations are 24x7, there are usually times of low activity.  You can leverage these times for running your maintenance so you reduce the interference you will have on the database.  The second is you will need to reserve some time for complete outages (for things like service packs or database migrations), so you will need to negotiate full maintenance windows with your management.  For specific items you will need to consider and plan for each one, as well as leverage your tools appropriately.  The important piece is you must PLAN each of these, any examples I provide are very much "your miles may vary".

Backups

Backups commonly won't have a huge impact on workloads, but must be accounted for as they can consume a lot of I/O.  You will want to schedule these appropriately and monitor the amount of time it takes to complete.  The biggest hurdle here is that in a 24x7 operation, you will likely not be able to conduct full nightly backups every night of the week.  You will want to plan out when you can take fulls, when you are taking differentials, and retention periods for both of these in combination with your log backups.

As an example, I run full backups of all my databases on Sunday night (lowest activity), differentials on all other nights (Monday-Saturday).  I keep the last two weeks of fulls and diffs on disk, logs for the last two days.  This gives me enough flexibility for recovery, but I might have to recover backups from tape if necessary.

Index/Statistics Maintenance

This is the most common type of active maintenance you will have to deal with.  You can't avoid it, but you can mitigate the impact.  The initial rule of thumb is that you should only do maintenance on objects that need it.  General guidelines are to only rebuild indexes that are greater than 30% fragmented and larger than 1000 pages.  If you have auto-update statistics, this will handle most of your statistics maintenance, but a nightly job to keep things in sync isn't a bad idea.

If you have Enterprise Edition, you also have access to some other options for managing maintenance.  Foremost is Online Index Rebuilds, which will allow you to rebuild indexes while they are still in use (essentially, it builds the index side by side, then swaps it in).  You also can leverage partitioning for "large" tables to reduce the amount of rebuild time necessary.

Your best bet for this type of maintenance, if you don't have custom scripts that handle these best practices, is to use Ola Hallengren's Maintenance scripts.  These are fairly easy to setup and configure and have many of these guidelines built in.

DBCC Consistency Checks

Depending on your overall workload, you might find DBCC checks to be to disruptive for your operation.  There are two common ways to minimize your DBCC impact for your databases:

PHYSICAL_ONLY - Running this option will check your databases at a physical page level and avoid the more invasive full check.  This will cover identifying the most likely types of corruption.
Checking a restored copy - If you have the space, you can restore the database to another instance and run a DBCC check against the restored copy.  This will tell the same tale about your live database, but you obviously won't interfere with the activity.  Some other alternatives here are running DBCC against a log shipped copy or a mirrored db.

This blog post provides more detail on your options.

Batch jobs/ETL

This really comes down to how you design your processes.  Your ETL can always interfere with live OLTP tables (just as any other application), so some keys to keep in mind:

Schedule such work around your other maintenance and in low activity periods.
Right size the work so that it's batched for both performance and so that the batch is not so big that it locks your table for hours.  Examples of the ends of the spectrum:  Row-by-agonizing-row (RBAR) versus a single million row delete.
Use stage tables and offline your data processing where appropriate.  Only touch the live stuff when absolutely necessary.

Conclusion

Again, there's a lot of ground to cover here.  This is not a comprehensive guide, but a high level overview of some approaches.  I haven't even discussed high-availability options (such as Availability Groups and Failover Clustering).  You will need to review each item and build out a plan for how to handle it.  In many ways, you will also need to iterate and refine your work as you move forward.

Additional resources:

SQL Skills VLDB maintenance best practices


Source control makes sure my code is never lost, how do I make sure my users/orders database is never lost?

You can dump databases with a cron job, but your database schema should be under version control just like all the other code.

For MySQL, see mysqldump.

Copy or move the backup files to a safe place. (Or to two different safe places.) The database server is not a safe place.


Getting formatted MDX values using OPENQUERY

I ended up solving this using the Format function, similar to the following:

MEMBER [RO Count] AS Format([Measures].[ROH Count], "General Number")

I'll keep the question open for a bit, in case anyone has a cleaner answer.  I would have liked to pull the format string in from the calculated members, instead of hardcoding it, but I couldn't figure out how.


List the most popular item being ordered

To understand the logic I suggest you read this.

http://www.w3resource.com/sql/aggregate-functions/count-with-group-by.php

Try this:

SELECT Item#, SUM(Qty) AS SoldQty
FROM ORDER_ITEM
GROUP BY Item#
ORDER BY SoldQty --You can use 'Desc' depending on how you want to see the data
  ;


How could I handle heavy load and consistency for product inventory in online retailers?

First off, taking a step back, do you really need to solve the inventory management problem on the front end?  Since you're selling large volumes of a relatively small set of products, it should be relatively easy to manage your inventory so that you are never out of stock or, if you are, it doesn't prevent you from fulfilling orders.  There is a great deal of literature and examples that deal with calculating safety stock which requires just a bit of statistics to follow.  It would make far more sense to me to focus your attention on giving the company the tools (if it doesn't already have them) to manage their inventory to prevent stock-out situations rather than trying to prevent them from happening in the sales portal.

That being said, I'm not quite sure that I follow your problem with the two scenarios you outline.  Even if the database performance was flawless, if you have only 1 of item A in stock and you can't sell an item if it's not in stock, then one of the two customers, by definition, one of the two potential customers is going to lose out.  If in the first scenario C2 is going to go away without buying anything if any of his 35 items are not in stock (which seems unlikely if he spent 20 minutes filling his cart), there is nothing you can do in the database to prevent that.  Your interface could potentially have some AJAX that alerts them while they're shopping that one of the items in their cart is out of stock much like StackExchange notifies you while you're entering an answer that someone else has entered an answer.  It's not at all clear to me, that telling C2 about the problem earlier is going to be beneficial-- if he's going to leave if he can't buy all 35 items in one transaction, he's going to leave no matter when you tell him that C1 bought the item.  Realistically, there is no way to design the system so as not to disappoint one of the two customers in that case.

It might help if you can explain a bit more about why your application and your customers are so sensitive to stock-out situations.  Most customers and most retailers are relatively accustomed to the fact that sometimes after placing an order they get notified that the retailer isn't going to be able to fulfill the order as quickly as they had expected and are given the option to cancel that part of their order, the whole order (assuming the remaining items haven't shipped yet), or to wait for the item to come back in to stock.  Assuming that you do something to notify customers while they're browsing that inventory is relatively low (i.e. Amazon will tell you "N items in stock" if you're looking at an item for which they only have a handful of stock left), most customers are reasonably understanding 20 minutes when they get to the checkout and are told that the item is now out of stock since they knew in advance that they needed to order quickly.  And most retailers are comfortable that even if they run out of stock of most popular items, they can still satisfy more requests than they have inventory in hand because they undoubtedly have new inventory arriving in the next day or two or they can rush an order for new inventory.


Get the nth distinct column value ordered by another column

You need two changes:

a major one: Using an non-aggregated column in a GROUP BY query will yield unpredictable result - it's a pity that default setting s in MySQL allow this type of query, and good that it is corrected in 5.7 version.
The problem with your query is that an email address can appear in many rows - with many different created_at values - and the question is: Which of these different values will MySQL use for the ORDER BY? The answer is any one of the them. It's usually the first it will find but "first" here can mean different things, depending on the execution plans and available indexes. So, the results will not be determinate and may change from one execution to another.
You can use MIN(created_at) instead for the ORDER BY and the issue  is solved.
a minor one: OFFSET x means "skip x rows". Replace 1000 with OFFSET 999:

SELECT email_address
    FROM footable
    GROUP BY email_address
    ORDER BY MIN(created_at) ASC
    LIMIT 1
    OFFSET 999 ;


Every row of the result depends on the previous row. A recursive CTE comes to mind, I tried that. But one would need to refer to the worktable in an OUTER JOIN or a subquery expression which is not allowed. This does not work (building on the table layout in my fiddle):

WITH RECURSIVE
   t0 AS (SELECT *, COALESCE(array_length(opp_log,1), 0) AS len FROM tbl)
,  t1 AS (
   SELECT t1.player_id AS pl, t2.player_id AS p2
         ,t1.len AS len1, t2.len AS len2
   FROM   t0 t1, t0 t2
   WHERE  t2.player_id <> t1.player_id
   AND    t2.player_id <> ALL (t1.opp_log)
   )
, cte AS (
   (
   SELECT pl, p2
   FROM   t1
   ORDER  BY len1 DESC, len2 DESC
   LIMIT  1
   )

   UNION ALL
   (
   SELECT pl, p2
   FROM   t1
   LEFT   JOIN cte c ON t1.p1 IN (c.p1, c.p2)
                        OR t1.p2 IN (c.p1, c.p2)
   WHERE  c.p1 IS NULL
   ORDER  BY len1 DESC, len2 DESC
   LIMIT  1
   )
   )
SELECT *
FROM   cte;

> ERROR:  recursive reference to query "cte" must not appear within an outer join

I don't think there is a halfway decent way to solve this with pure SQL. I suggest:

Procedural solution with PL/pgSQL

CREATE OR REPLACE FUNCTION f_next_round()
  RETURNS TABLE (player_id1 int, player_id2 int) AS
$func$
DECLARE
   rows int := (SELECT count(*)/2 FROM tbl);  -- expected number of resulting rows
   ct   int := 0;                             -- running count
BEGIN

CREATE TEMP TABLE t ON COMMIT DROP AS         -- possible combinations
SELECT t1.player_id AS p1, t2.player_id AS p2
     , COALESCE(array_length(t1.opp_log,1), 0) AS len1
     , COALESCE(array_length(t2.opp_log,1), 0) AS len2
FROM   tbl t1, tbl t2
WHERE  t2.player_id <> t1.player_id
AND    t2.player_id <> ALL (t1.opp_log)
AND    t1.player_id <> ALL (t2.opp_log)
ORDER  BY len1 DESC, len2 DESC;               -- opportune sort order

LOOP
   SELECT INTO player_id1, player_id2  p1, p2 FROM t LIMIT 1;

   EXIT WHEN NOT FOUND;
   RETURN NEXT;
   ct := ct + 1;                              -- running count

   DELETE FROM t                              -- remove obsolete pairs
   WHERE  p1 IN (player_id1, player_id2) OR
          p2 IN (player_id1, player_id2);
END LOOP;

IF ct < rows THEN
   RAISE EXCEPTION 'Could not find a solution';
ELSIF ct > rows THEN
   RAISE EXCEPTION 'Impossible result!';
END IF;

END
$func$  LANGUAGE plpgsql VOLATILE;

How?

Build a temporary table with remaining possible pairs. This kind of cross join produces a lot of rows with big tables, but since we seem to be talking about tournaments, numbers should be reasonably low.

Players with the longest list of opponents are sorted first. This way, players that would be hard to match come first, increasing the chance for a solution.

Pick the first row and delete related pairings now obsolete. Do need to sort again. Logically any row is good, practically we get the player with the longest list of opponents first due to initial sort (which is not reliable without ORDER BY, but good enough for the case).

Repeat until no match is left.
Keep count and raise an exception if the count is not as expected. PL/pgSQL conveniently allows to raise an exception after the fact, which cancels any previous return values. Details in the manual.

Call:

SELECT * FROM f_next_round();

Result:

player_id1 | player_id2
-----------+-----------
1          | 7
2          | 3
4          | 8
5          | 6

SQL Fiddle.

Note

This does not guarantee to calculate the perfect solution. I just returns a possible solution and uses some limited smarts to improve the chances to find one. The problem is a bit like solving a Sudoku, really and is not trivially solved perfectly.


How 'Select * into' works into background?

You didn't include the exact query to reproduce the issue (it doesn't happen every time), so here's a few different ways it'll happen:

Using WITH (NOLOCK) - this hint in your query can cause records to be read twice, to be skipped altogether, and for your query to fail outright. (The same thing can happen with the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED hint.)

An error in the SELECT query - for example, if you're joining multiple tables together to get the data for the INSERT, and you have an error in the join (or a legitimate join that produces multiple rows of results), you can get duplicates.

Concurrency - say that while you're working, someone else is inserting and removing records from the source table. They might have temporarily had duplicates in the source table, and when you go to look later, they're gone. (I've hit issues where people would insert a new version of a row, then delete the old version of a row, so some selects out of that table would see duplicate rows. They couldn't implement unique constraints in the database because of that code pattern.)

To get an exact answer for your exact situation, post your exact code, or code that reproduces the issue you're having, and we'd be glad to dig deeper. And welcome to Stack!


Let's say the table is called mydb.mytable
This query
SELECT VAR1,VAR2 FROM mydb.mytable where var3=X AND var4=Y order by var5 desc limit 1;

can dramatically be improved if you index the table.
There is one of two techniques you can try when indexing the table
Technique #1
ALTER TABLE mydb.mytable ADD INDEX search_index (var3,var4,var5);

Within the index, all var5 values are ordered for every var3,var4 tuple. It would simply be a rightmost traversal down the BTREE to get to the last var5. Then, the row is looked up to retrieve var1,var2 from the table.
Technique #2
ALTER TABLE mydb.mytable ADD INDEX search_index (var3,var4,var5,var1,var2);

This has the same searchability as INDEX #1, but with an added bonus: var1,var2 are already in the index, so there is no need to go back to the table for a separate retrieval. Making an index that has every column requested in a query is called a covering index.
I have discussed covering indexes before

Jun 12, 2014 : How to return last used record?
Aug 06, 2013 : In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index
Oct 28, 2011 : Using FORCE INDEX
Aug 15, 2011 : How do you increase MySQL performance using current server resources?

Give it a Try !!!


Assign object owner to a role other than the creator automatically on creation for a database

You can't use alter default privileges because it can't reassign ownership.

What you could do instead is run for each dev:

alter user john_q_developer set role = feature_role;

This way all of the developers will by default create all their objects with feature_role as the owner.  This is pretty much the same thing as all the developers just logging on to the database as the same user, except this way they all get their own password (or their own authentication method of choice) rather than sharing one.

This has the same "friction" that alter default privileges does, in that you have to run the alter user ... one time for each create user ..., but that level of friction seems pretty small to me.

The objects won't be tagged inside the database with who created them, but you can set up the server to log that information if you want (include "%u" in the log_line_prefix, and set log_statement = 'ddl' in the configuration file postgresql.conf.)

A slightly more restricted approach would be to create a feature_owner role that can create objects, and a feature_dev noinherit in role feaure_owner role which has all rights other than to create objects, and grant all devs the role feature_dev.

That way attempts to create objects will fail by default, but anyone can just execute set role = feature_owner and then repeat the creation in order to create objects under that ownership.  It would just be common knowledge that only the feature lead is supposed to make use of this power under normal circumstances.


Which database system can break me free?

There's a lot to consider. I'm going to narrow this down to a single problem and offer one solution, although there's many approaches.

Problem: You need AD-HOC queries on lots of rows.

First, setup a server to handle writes and a replicated slave (running some flavor of MySQL 5.5.x) to handle reads. My personal preference is Percona Server. Your mileage may vary.

Once you have replication caught up, ALTER your slave table to: engine=InnoDB, ROW_FORMAT=COMPRESSED, partition by date, and index the data to optimize your query WHERE clauses. Each of these have an optimization for your case and there is further tuning required.

Finally, setup a schedule to archive/shard the data when it falls out of scope. Say 2-3 years. Depends on your SLA for providing back reporting. You can always union it if you have to.

MySQL 5.6 will have Memcached built in which will allow you to store pre-processed results. Map/Reduce is great, as long as it's not the first run of the reduce function. NoSQL is great as long as the data is already processed. Neither is really your problem.

Additions 2012-06-13:
Since it appears my recommendations have stirred another response, I'll submit my reasons:

Why InnoDB in this case? With the constant insertions of many rows,
you can use InnoDB to avoid implicit read locks at the table level.
Why COMPRESSED + Barracuda? "The compression means less data is transferred between disk and memory, and takes up less space in memory. The benefits are amplified for tables with secondary indexes, because index data is compressed also."
Why partition by date? Reduce scan breadth.
Why index properly? Decrease search times by reducing cpu bottleneck through elimination of full table scans.


Which databases are backed up by "mysqldump --all-databases"?

I just did mysqldump --all-databases in MySQL 5.6.22 for Windows.
Neither performance_schema nor information_schema are in the dump.
It says so the the MySQL Documentation

Restrictions
mysqldump does not dump the INFORMATION_SCHEMA or performance_schema database by default. To dump either of these, name it explicitly on the command line. You can also name it with the --databases option. Also, use the --skip-lock-tables option.

That's a good thing. Why ?
PERFORMANCE_SCHEMA
The instrumentation tables within performance schema are transient within the DB Server. The metrics from one time period to another can be different or the same (thus, trying to use PERFORMANCE_SCHEMA idempotently would never reveal the same results). Such results would make absolute no sense loaded onto a different server or even back into the source from another time period. Therefore, performance_schema is not dumped by default.
If you simply need to recreate the performance_schema database from backup because you dropped it accidentally, that would be OK. Then, upon using the instrumentation, stuff would get rewritten anyway.
If you wanted to snapshot the instrumentation results to view later on in a text editor or an app, that would OK as well.
INFORMATION_SCHEMA
This is also transient. Furthermore, all information_schema tables are stored in memory and simple hold metadata about the tables in the instance since MySQL started. (See my old post How is INFORMATION_SCHEMA implemented in MySQL?) Dumping it and reloading it into another MySQL Instance is completely useless because mysqld would immediately deny its load (or at the very least, mysqld would start reloading its metadata by re-reading the mysql schema and all table files and tablespaces again if it had to).
If you wanted to capture the state of all tables and their files at a moment in time without touching the tables, then dumping INFORMATION_SCHEMA and viewing the dumps from an editor or some app would be OK.
EPILOGUE
Think of it this way: If you had a slow log from one MySQL instance and copied it to another, how does that benefit you as a Developer or DBA with queries that did not run on the new server ?
In like fashion, having performance metrics and table metadata from one server would not serve any useful purpose being dumped then reloaded back to the original server or another server. mysqld needs to have a clear picture of the data files it currently manages, not what mysqld managed on another server or at some time in the past.
CAVEAT
You should refrain from backing up the mysql schema unless you restore it to the same machine or at least to another machine with the same major release of MySQL.
Please see my old post Backup and restore "mysql" database as to why you should not.


How can I best combine 3 queries into 1?

SUBSET QUERY #1
SELECT
    merchant.locationName,
    wp_postmeta_location.meta_value,
    wp_postmeta_location.post_id,
    merchant.brandIdentifier,
    wp_postmeta_brand.meta_value
FROM
    (
        SELECT `brandIdentifier`,`locationIdentifier`,`locationName`
        FROM merchant WHERE postalCode='90210'
        GROUP BY `locationName`,`locationIdentifier`
    ) merchant
    INNER JOIN
    (
        SELECT post_id,meta_value FROM wp_postmeta
        WHERE meta_key = 'locationIdentifier'
    ) wp_postmeta_location
    ON merchant.locationIdentifier = wp_postmeta_location.meta_value
    INNER JOIN
    (
        SELECT post_id,meta_value FROM wp_postmeta
        WHERE meta_key = 'brandIdentifier'
    ) wp_postmeta_brand
    ON
    (merchant.brandIdentifier = wp_postmeta_brand.meta_value AND
    wp_postmeta_location.post_id = wp_postmeta_brand.post_id)
;


Database gives "ORA-01109: Database not open" error when it IS open

Your database instance is open.

Your database open_status is a different thing, it could be open READ ONLY. The database is expected to be open for READ WRITE when you switch a logfile. So you might conclude that the error message is not complete.

select name, open_mode from v$database;

and since you are in 12c also check:

select name, open_mode from v$pdbs;

Most likely your database is not open for READ WRITE, as the error states. Question that remains is: why?

Documentation about Oracle Database Instance

When your database is open READ ONLY and try to perform a log switch, you get ORA-1109 : database not open.


With one table you would need to limit it to a single row.

I think a trigger on InUse would be a better solution.


SSRS: Error Getting the Fields And Parameters for a shared Dataset based on a Stored Procedure

This error is due to the way Visual Studio Data Tools/BIDS/SSRS Designer  to caches datasets. The workaround I found to get the fields and parameters refreshed correctly is as follows.

With a Shared Dataset Properties Window Open

Hit Refresh Fields
Close out the error msg.
Click on Query Designer
Click on the Exclamation Mark Button
A define Query Parameters window should pop up. Put in values for the parameters. Good defaults to use are ones that return little to no data. You are just looking for SSRS to refresh the data set metadata.
Hit Ok.
Wait a bit until the query is done.
Hit ok.
Check to see if data is now in the fields and parameters list. It should be!

Please note: The error will pop up even after you do have fields and parameters listed. However, you just need to make sure it is up to date. The error message popping up is fine and will continue to pop up. When ever you need to refresh the shared data set, just follow the steps above.


Row level security in PostgreSQL for different groups

So, if user1 has group in common with user2, user2 should be able to see that row, yes?

One way would be to enumerate all the groups that user1 is in, and all the groups user2 is in, and see if there's a union

See How to get all roles that a user is a member of (including inherited roles)? to get all groups a user belongs to

See also pg_has_role https://www.postgresql.org/docs/9.6/static/functions-info.html


Restoring database backup file gives access error

You need to actually specify the backup file in the path.  It's not the file list in an NTFS directory, as it seems may be your understanding. It's the file list of the database files in the backup file.

What you should be doing is:

restore filelistonly
from disk = 'D:\path\backupfile.bak';


Column Name in separate table SQL Server

What you're looking for is Dynamic SQL Pivots. A PIVOT will turn row values into columns based on an aggregate, however you need to define the column names as part of the PIVOT, luckily we can do that on the fly with dynamic SQL. The following should generate the result set you want for a particular company (replace rowValues with your table name):

DECLARE @SQL NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Company NVARCHAR(5) = '01'

SET @Columns = STUFF( (SELECT ',['+H.description+']' AS [data()]
                        FROM dbo.Headers H
                        WHERE H.company = @Company
                        ORDER BY H.fieldNumber
                        FOR XML PATH('')),1,1,'')

SET @SQL = '
SELECT company,jobNumber,'+@Columns+'
FROM
(
    SELECT h.company,RV.jobNumber,RV.information,h.description
    FROM Headers h
            INNER JOIN dbo.RowValues RV
                ON RV.fieldNumber = h.fieldNumber
                   AND RV.company = h.company
    WHERE h.company = '+@Company+'
) as Data
PIVOT
(
    MAX(information) FOR [description] IN ('+@Columns+')
) as p
ORDER BY jobNumber ASC'

EXEC sp_executesql @sql;

Unfortunately due to the possibility that different companies will have different columns there is no reliable way to combine all the the possible sets the above query could generate. Depending on how you want to use this query the easy option is to loop through all the companies and call an SP that contains the above, allowing you to output each company as a separate SELECT. Or you can do something with your SSIS package to output each one into a file.

If you want to insert the data into a table matching the calculated schema you can do a SELECT INSERT and a bit more dynamic SQL to get the data where it needs to go.


Stored procedure returns -4 after catching exception

While I agree that you're raising an error even if you have a PK violation (as the other answer suggests, you need BEGIN/END, because your IF conditional currently assumes that everything indented after it will be part of the IF logic), that doesn't really answer why you're getting -4 specifically.
You don't have an explicit return value in the event that you catch an error. I'll borrow from Erland Sommarskog's answer here, which states:

But if there is no RETURN statement, but an error occurs during execution, the return value is 10 minus the severity level of the error. Division by zero is level 16, thus the return value is -6. Permissions errors are typical level 14, thus the return value is -4.
As you may guess this is not terribly useful, but this: 0 is success, and everything else is an error.

So, raise a PK error, you get:

Msg 2627, Level 14, State 1

According to Erland's formula:
10 - 14 = -4

You don't get the actual level in @ErrorLevel when there's a PK violation because those local variables aren't populated when the error message is 2627 (your IF condition prevents that for that specific scenario, but doesn't prevent the RAISERROR from happening). If you manage to say RAISERROR(NULL,NULL,NULL); then Erland's rule applies; this is because, if an actual error has happened (I fake this in a demo below), SQL Server will replace that middle param with the actual ERROR_LEVEL() from the exception. I can show that this is true for other error levels other than 14; for example, the following will print out Hi/-6 after I fail to assign the variable for a divide by zero error, which is severity level 16:
CREATE PROCEDURE dbo.Test
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @ErrorLevel INT;

  BEGIN TRY
    SELECT 1/0; -- level 16!
  END TRY
  BEGIN CATCH
    IF ERROR_NUMBER() <> 8134 -- divide by 0
      SET @ErrorLevel = ERROR_SEVERITY();
    RAISERROR('Hi', @ErrorLevel, 1);
  END CATCH
END
GO

DECLARE @rc INT;
EXEC @rc = dbo.Test;
PRINT @rc;

(In fact, inside a CATCH block, I couldn't find a way to override the actual exception's true severity level, even by hard-coding @ErrorLevel to sensible and non-sensible values.)
Also from John Saunders' answer on the same question:

My "research" is due, with thanks, to SQL Server MVP Tibor Karaszi. His source is Books Online for SQL Server 6.5. Under ""Control-Of-Flow Language", RETURN", he found
"SQL Server reserves 0 to indicate a successful return and reserves negative values from - 1 through - 99 to indicate different reasons for failure. If no user-defined return value is provided, the SQL Server value is used. User-defined return status values should not conflict with those reserved by SQL Server. The values 0 through -14 are currently in use.

(Current versions of this same topic no longer have this language, but if you read the late Ken Henderson's book, The Guru's Guide to SQL Server Stored Procedures, XML, and HTML, on page 39 he indicates that -1 through -14 indicate different levels of failure (and to go read the then-current version of Books Online to see individual descriptions, a lot of good that does now), and -15 through -99 are reserved for future use.)
In the end, Erland's right; whether you get -4 or -6 or -23 doesn't really matter. If it's not 0 (and you didn't set an explicit non-zero return value to indicate success), you had an error. It sounds like you just want to ignore the PK violation error, but if you want control over what value gets returned in the event of other non-PK errors, you'll have to add your own explicit RETURN(x); command to that same IF block.
And depending on how many PK violations you expect, it might be worth considering checking for the violation manually instead of just blindly throwing everything and letting SQL Server deal with it. There can be substantial overhead with raising exceptions:

Checking for potential constraint violations before entering SQL Server TRY and CATCH logic
Performance impact of different error handling techniques

Further demonstration of where -4 (and other values) come from. Create this procedure:
CREATE PROCEDURE dbo.DemoErrors
  @ErrorLevel INT
AS
BEGIN
  RAISERROR(N'foo', @ErrorLevel, 1);
END
GO

Now if you call it with these values for @ErrorLevel (level < 11 will end up with return value of 0, and > 19 require log etc., so we'll go 11 -> 18):
SET NOCOUNT ON;
DECLARE @rc INT, @i INT = 11;
WHILE @i <= 18
BEGIN
  EXEC @rc = dbo.DemoErrors @ErrorLevel = @i;
  PRINT @rc;
  SET @i += 1;
END

Output shows that the return value is always 10 - @ErrorLevel:

Msg 50000, Level 11, State 1, Procedure DemoErrors, Line 25
foo
-1
Msg 50000, Level 12, State 1, Procedure DemoErrors, Line 25
foo
-2
Msg 50000, Level 13, State 1, Procedure DemoErrors, Line 25
foo
-3
Msg 50000, Level 14, State 1, Procedure DemoErrors, Line 25
foo
-4
Msg 50000, Level 15, State 1, Procedure DemoErrors, Line 25
foo
-5
Msg 50000, Level 16, State 1, Procedure DemoErrors, Line 25
foo
-6
Msg 50000, Level 17, State 1, Procedure DemoErrors, Line 25
foo
-7
Msg 50000, Level 18, State 1, Procedure DemoErrors, Line 25
foo
-8

(If you run as a sysadmin and add WITH LOG to the RAISERROR call, you'll see 19 produces -9, and so on, but up above that you'll start having connections severed etc. because those are serious errors.)


Deadlock scenario, where is this S lock coming from?

I'll repost my response on twitter here:

Based on the deadlock XML you posted, which lists a trancount of 3 in the session that invokes spUpdateUserAccount, a prior statement or batch in that transaction likely put the lock on IX_Person_TaxPreparer_rowCreated.

I'm presuming this lock is held because of the foreign key constraint in place.

The locks are held for the duration of the transaction, so your second statement can't complete til the first commits the open transaction.


On PostgreSQL 9.1 and newer, you can use the COLLATE qualifier on an operation to override the database's default collation. See the manual for information on collation support.

E.g.

SELECT a, b FROM mytable ORDER BY c COLLATE 'th_TH.UTF-8';

Note that PostgreSQL can't mix different collations, using a dynamic collation based on detected language. It doesn't work like that.

On prior versions you must use a single database-wide collation. So you'd have to dump your database, CREATE DATABASE ... ENCODING 'UTF-8' LC_COLLATE 'th_TH.UTF-8', and re-load.


Postgres pg_restore error: invalid byte sequence for encoding "UTF8": 0xe05c62

Your options are pretty simple, and the error message is explicit. This is the problem

  ERROR:  invalid byte sequence for encoding "UTF8": 0xe05c62

And this hint pretty much tells you what we would tell you,

  HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

So you have two options,

Remove the byte sequence (assumes you may not need it), there is no indicator that you know what it's actually supposed to be anyway.
Set the client_encoding to whatever encoding supports that sequence, as UTF-8 doesn't.

You may want to confirm the encoding of your database

SELECT pg_encoding_to_char(encoding)
FROM pg_database
WHERE datname = 'yourdb';

And confirm that the output encoding (--encoding to pg_dump) supports all of those characters (is the same or a superset). And, that also the new database is either the same encoding or a superset.

Try not to use --format=c, it only adds complexity. If you're having problems use plain, the default.


How to find out "InnoDB Write buffer efficiency"?

Why do you need to know it?  There is no actionable information in the statistic.

Make innodb_buffer_pool_size about 70% of available RAM (assuming more than 4GB of RAM); that will be optimal.

Look for slow queries and improve them; that will cut back on performance problems.

Here are some things you can get from SHOW GLOBAL STATUS and SHOW VARIABLES:

Innodb_pages_written / Innodb_buffer_pool_write_requests -- Write requests that had to hit disk -- <15% is 'good'
Innodb_buffer_pool_wait_free / Innodb_buffer_pool_write_requests -- Should not have to wait to get into the buffer pool. -- <1% is 'good'
Innodb_buffer_pool_pages_flushed / Uptime -- Writes actually going to disk
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) -- Buffer pool writes per query


The LIKE operator in SQL does not work like regular expressions. The pattern matches allowed by this operator are very simple, compared to the powerful regexp. Actually, you have two metacharacters: % means (0 or more of anything), which would be the equivalent of a .* in a regexp, and _ meaning (1 of anything), which would be the equivalentof . in a regexp. And that's it.

This means your CHECK condition, as originally written, is not actually checking what you expect. The only thing it will complain about are statements like:

INSERT INTO test
VALUES (37, 'hello [a-zA-Z] impossible');

that contain the literal [a-zA-Z].

You can actually use a REGEXP operator and write:

CHECK (prefix REGEXP '^[a-zA-Z]+$')

However, to be able to use it, you first need to install the sqlite3-pcre pcre regex extension for sqlite3, on a Linux machine (there may be a Windows alternative, but I've not been able to find it). You can find more information and instructions at Stack Overflow's How do I use regex in a SQLite query?.

Other databases such as PostgreSQL would let you use RegExp using the ~ operator. You can check it at SQLFiddle.

SQLite doc references and tutorials:

SQLite Like: Querying Data Based On Pattern Matching
The LIKE, GLOB, REGEXP, and MATCH operators


ORA-01017 connecting to example schemas on Oracle VM appliance from Windows host

Do you have Oracle XE or any database running in your host and listener is started as a service in your Windows host? Probably it is trying to connect there. If you have mapped 1521 from your host to 1521 to your guest, just make sure in your host there is no listener service running. If it is running change you port forwarding to 1522 ( Recommended )or something or uninstall the listener running in 1521 ( Don't seem like a wise thing to do :-) )


Creating a structure to allow for optional teams of people rather than just individual people

Am I on the correct track of thinking here?

IMHO you are not. You are putting the carriage in front of the horse.
To get back on the right track, start at the beginning, with your data universe.
Let's list what entities you have in your game, and separate that from the business rules.
First question is: "what is it in the real world, which we are trying to model?". From what you said, I see the following - please fill in the missing pieces. the essence and relationships between developers - companies - teams - projects is not well defined IMHO.

Developers

A Developer is a Person which is identified by {Fill in}
A Developer contributes work to Projects
A Developer works for a company {True?}
A Developer IS a part of a team
A Developer may be assigned to zero or more teams {True?}

** You said that the developer 'may be part of a team', but it doesn't work that way. you can't have the cake and leave it whole. If your universe has a hierarchy of teams now, it is true for all. The way you keep 'the old way', is by creating a team of one person.
This will resolve the evil cyclic reference that you can see in the solution that Alfonso suggested, who was just following your specs.

Companies

A Company is a legal entity which is identified by {Fill in}.
A Company hires teams of one or more Developers.
A Company owns Projects {True?}.
Developers are employed by Companies {True?}.
Teams

A Team is a logical container for developers which is identified by name.
A Team can be assigned zero or more developers.
A Team can be assigned to zero or more projects {True?}.
A Team belongs to a company {True?}.
Projects

A Project is a collection of tasks which is identified by name.
A Project may be assigned to zero or more teams.
A Project belongs to a company {True?}.

Once you fill in the gaps and clarify what you have, we can continue on the right track.

-- Follow up to OP Updating Requirements --

Developers are not identified by ID. That is a surrogate key which does not exist in the real world. We will sort out the table keys later. The question is "How do you tell developers apart in the real world". I would assume they have a unique user name, or use their Email address to log on, so that your application can identify them, right? perhaps you use SSN?
A company, team, and projects are probably identified by their name, which is what tells them apart in the real world, unique in their respective scope of course - It seems that team per company, and project per company make sense.

As for the entities, based on your reply I now believe that we have missed a few. Let me suggest something, and see if that works for you.
I think the term 'Companies' is misleading, as you don't care if it's a company or an individual. What this represents is your customers, or the project owners, which are the legal entity which you issue invoices for. Since you already mentioned the separation of roles (developer / administrator / Read only etc.), I've taken the liberty of adding a couple of more entities that exist out there, and which you probably want to consider in your model.
Let me know if this makes any sense.

Customer:       A Customer is a legal entity identified by it's tax identification number (SSN/ITIN).
                Customers employ zero or more Employees.
                Customers own zero or more Projects.

Employee:       An Employee is a Person, identified by his (SSN / User Name / Email Address).
                Employees are employed by a Customer.
                Employees can have zero or more roles in association with zero or more Teams.

Role:           A role is a description of responsibility area for an Employee.
                Roles are identified by name.
                Roles can be one of : Developer, Project Viewer, Project Manager, Adsministrator, Team Lead)
                An Employee may have zero or more roles.

Team:           A Team is a collection of zero or more Team Members.
                A Team is identified by name.
                A Team may be associated with zero or more projects.

Team Member:    A Team Member is an Employee that is associated with a Team.
                A Team may consist of Employees from one customer only.
                Team Members have a single role in a Team.

Developer:      A Developer is an Employee.
                A Developer contributes work for projects.
                A Developer can be associated with zero or more teams.

Project Viewer: A Project Viewer is an Employee.
                A Project Viewer may access Projects, but is not allowed to make changes.

Project Manager:    A Project Manager is an Employee.
                    A Project Manager manages zero or more Projects, and the Teams associated with them.

Administrator:  An administrator is an Employee.
                An administrator represents the Customer and has permissions to manage Employees, Roles, Teams, and Projects.

Team Lead:      A Team Lead is a developer.
                A Team Lead manages zero or more teams.
                A Team may have only one Team Lead.

Project:        A Project is a collection of tasks, which are to be executed by Teams.
                A Project belongs to one Customer.
                A Project is identified by name.
                A Project may be assigned to one or more Teams.


Trying to phpMyAdmin import a database with a different name

There are two possibilities here; the easier one is to use phpMyAdmin to change the name on export.

To do that, you'll need to select the "Custom" radio button on the export page, then under the "Output" section look for "Rename exported databases/tables/columns" -- by selecting that checkbox, you get a dialog box to rename the entire database or specific tables or columns.

The other way is to edit the .sql file and find the references to the old database and correct it with your text editor. It's probably only the initial CREATE statement and USE very shortly after that.


When is the SQL Server database ready to accept queries?

The SQL Server database is ready to accept queries as soon as:

SELECT DATABASEPROPERTYEX(N'database name', 'Collation')

does not return NULL.

From the documentation for DATABASEPROPERTYEX (Transact-SQL):

  Note: The ONLINE status may be returned while the database is being opened and is not yet recovered. To identify when a database can accept connections, query the Collation property of DATABASEPROPERTYEX. The database can accept connections when the database collation returns a non-null value. For Always On databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states.


Have ended up Querying information_schema.Table_Privileges and scripting Grants & Revokes based on the result.


DB stored procedures: how do they help increase security in web systems?

The theory behind using stored procedures for increased security is that you don't give DML access directly to users.  The idea is that users never get anything other than execute permissions on the procedures.  There's also a slightly less paranoid version of this approach where all writes are by stored procs, but table/view reads can be done by users.

The reasoning goes that if users only have execute permissions on stored procs and no direct DML on tables or views, then they can't manipulate data in any unpredicable ways.

To take an example, let's say you've got a bank account system and the only way to make a funds transfer is through a stored proc, you can have this proc audit log details about when it happened, where the money went and so forth.  If you allowed direct DML access to the base tables then a compromised account could be used to take money without leaving an audit trail.

You are right that a compromised account is still a big security hole - big enough to drive a truck through.  If all the compromised account's access is restricted to stored proc execution however then at least the hole will only fit a smaller truck.


Is there a way to throttle MySQL requests to mitigate DoS traffic?

I usually consider things like this best to be caught as high in the stack as possible on Nginx or Apache level. Wordpess is doing it's job of serving a 404 page for a url that doesn't exist. The lower you get in the stack the harder it gets to distinguish between usual requests and this behaviour. From MySQL it's just a normal legit request from php.

I have this in many of my sites Nginx config:

location /favicon.ico {
    expires 365d;
    return 200;
}

For apache with htaccess you can put a shared favicon.ico (an empty image or something) and this should work (I didn't test this):

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond favicon.ico
RewriteRule .* path/to/shared/favicon.ico [L]


User has >1 playlist

The Answer by Vérace is correct except that it skipped the fact that each user may have more than one playlist.

So here is a diagram of my table design. I changed one column name to "sort_order" as the name “index” in a database and in programming will cause all kinds of confusion.

Basically I suspect you are failing to notice the 'list' in 'playlist', conflating each person's list of their favoritesfrom the full list of possible choices. Each playlist is a collection of references to the desired items (songs, books, or whatever your matter is  — not clear in the Question).

Performance

As for your performance issue when inserting a new/changed row amongst the existing rows’ sort-order  numbers, you can dramatically reduce the frequency of this chore.

Increment your sort-order value by a hundred or by a thousand or by a million rather than by one. You can insert a new row by assigning a sort-order number in between the numbers of existing rows without any shifting. You only need to do a full shift when 99 or 999 or 999,999 rows happen to have already been inserted between the incremented rows. Until then you may need to do mini-shifts when clumps appear with the in-between numbers, but on small subset of rows, <100 or <1,000 or <1,000,000.

If you need to show sequential 1, 2, 3… numbers to user, let the database create these numbers dynamically as part of your SELECT statement.


Should I use many single field indexes, instead of specific multi column indexes?

What you need are covering indexes, ie. indexes that can satisfy a query on their own. But a 'covering' index has one problem: it is covering a specific query. So in order to develop a good indexing strategy, you need to understand your workload: what queries are hitting the database, which ones are critical and which ones are not, how often each type of query is run, etc etc etc. And then you balance this against the write and update cost of each index, and there you have your indexing strategy. If it sounds complicated that is because it is complicated.

However you can apply some rules of thumb. The MSDN covers the basics quite well:

Index Design Basics
General Index Design Guidelines
Clustered Index Design Guidelines
Nonclustered Index Design Guidelines

There is also a myriad of articles contributed by the community, eg. Webcast Recording – DBA Darwin Awards: Index Edition.

And to answer your question specifically: separate indexes on each column can work, provided that each column has a high selectivity (many distinct values, each values appearing only a few times in the database). The resulted access plan using hash join between two index range scans usually works quite well. Columns with low selectivity (few distinct values, each value appearing many times in the database) make no sense to be indexed on their own, the query optimizer will simply ignore them. However, low selectivity columns many times make good composite keys when they are paired with a high selectivity column.


Difference in efficiency between NVL and IS NULL

To me, the logic is that the NVL value gives a specific answer to the query handler. Whereas, the IS NULL value causes an ambiguous join, thus resulting in a Cartesian product.

That is, every row on the left is trying to join with every single null on the right in the IS NULL. Conversely, in the NVL only those rows which are null on both sides join.


MySQL - Changing from system tablespace to file per table

STEP #1

Add the option to my.cnf

[mysqld]
innodb_file_per_table = 1

STEP #2

If you are running MySQL 5.6, login to mysql and run

mysql> SET GLOBAL innodb_file_per_table = 1;

If you are not running MySQL 5.6, restart mysqld

# service mysql restart

STEP #3

Write a script to create NULL ALTER TABLE commands

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"

SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" > Extract_InnoDB_From_ibdata1.sql

STEP #4

View the script

less Extract_InnoDB_From_ibdata1.sql

If you are OK with it, then  run it

mysql ${MYSQL_CONN} < Extract_InnoDB_From_ibdata1.sql

CAVEAT

This will not shrink ibdata1. It merely extracts all InnoDB tables from it. ibdata1 will no longer grow beyond the 20GB due to the tables themselves.

If you want to shrink ibdata1, please refer to my post How do I shrink the innodb file ibdata1 without dumping all databases?

GIVE IT A TRY !!!


MYSQL - Why does this SELECT get slower the further down the table?

FIRST QUERY

SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 0,1;

Now, look back at the EXPLAIN plan for it:

EXPLAIN SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 0,1;
1   SIMPLE  wp_users    index   NULL    PRIMARY 8   NULL    1

How many rows do the Query Optimizer See? 1

SECOND QUERY

SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 170003,1;

Now, look back at the EXPLAIN plan for it:

EXPLAIN SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 170003,1;
1   SIMPLE  wp_users    ALL NULL    NULL    NULL    NULL    175862  Using filesort

How many rows do the Query Optimizer See? 175862

When the Query Optimizer sees too many rows to have to process, it decide to "throw indexes under the bus". MySQL will then do a full table scan, as indicated by the expression ALL in the EXPLAIN plan. The filesort occurs because you had ORDER BY ID.

SUGGESTION

Usually, it is recommended to retrieve rows by cursors. Personally, I hate using cursors. I have an alternative that is a little unorthodox. I would create a temp table with all the email addresses, treating it like an array but accessing it like a table.

Here is that paradigm

DELIMITER $$
DROP PROCEDURE IF EXISTS ProcessEmail;
CREATE PROCEDURE ProcessEmail()
BEGIN

    DROP TABLE IF EXISTS EmailArray;
    CREATE TABLE EmailArray
    (
        ndx INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        user_email varchar(100) NOT NULL DEFAULT ''
    ) ENGINE=MEMORY;
    INSERT INTO EmailArray (user_email) SELECT user_email FROM wp_users;

    SET @ndxnext = 0;
    SELECT COUNT(1) INTO @ndxlast FROM IDArray;
    WHILE @ndxnext < @ndxlast DO
      SET @ndxnext = @ndxnext + 1;
      SELECT user_email INTO @GivenEmail FROM EmailArray WHERE ndx = @ndxnext;
      ...
      ... Process whatever you need with the @GivenEmail
      ...
    END WHILE;

    DROP TABLE IF EXISTS EmailArray;
END;
$$
DELIMITER ;

GIVE IT A TRY !!!


Script DB Level Permission Before Refresh to Non Prod Environment

You should obviously try using native SSMS functionality.
To scripts database users and their permissions use 'Generate script' context menu option of the database (right click db - tasks - generate scripts).
to restore database from Production to non-Production, again, use Script the action task and then modify it to suit your specific needs (right-click 'Databases' node - restore database - Script button on the upleft of the General tab).


Using IDs from multiple tables in a single column

Your self-reference could introduce a hierarchy of divisions within divisions. You need code (usually a trigger) to ensure that no divisions allow this.

The original schema using encoding is broken too. You have no enforceable FK

Personally, I'd consider this with a check constraint to ensure than only one of DepartmentID and DivisionID per row is populated

Article
-------
ArticleID (PK) int NOT NULL
DepartmentID (FK) int NULL
DivisionID (FK) int NULL
ArticleName varchar(50) NOT NULL

Why?

no code needed
no nested divisions
full DRI via FKs to parent table
enforced "multiple parent, only one at a time"

You could also use computed columns to add a column that says "department" or "division"

Another way: mandate that each department has at least one division...


Should I create a nonclustered index on all searchable columns or just each column separately?

SQL Server will show you an Index it would like to use, since it estimates that index would make life easier.  There is certainly no need to create an index on every searchable field, in fact doing so will make write performance substantially worse.  If you have less than say 5 to 10 indexes on the given table, and you run that query all the time, you probably want to add the suggested index exactly as SSMS shows it.

JNK's Edit Below

It's also important to look at the suggested indexes together. The suggestions are all in silos, so to speak, so one entire suggested index could be replaced by adding an INCULDEd field on another index.

For instance, it's possible to have suggestions for these two indexes:

CREATE INDEX ix_IndexA ON Table (ColA, ColB)

CREATE INDEX ix_IndexB ON Table (ColA) INCLUDE (ColC)

When in fact you could meet the needs of both with:

CREATE INDEX ix_IndexC ON Table (ColA, ColB) INCLUDE (ColC)

I've seen this with much more complicated indexes (10-20 key fields, 10-20 included fields) where adding one field to the INCLUDE list will eliminate the need for the second index.


Is there a way to allow a `unique` key to be the same on a max of 3 records?

Are the URLs coming from a normalized URL table and hence you just have UrlIDs in this table, or is this the source table for URLs? The distinction here is important because you are implying that, in the case of this table being the source of the URLs (which it sounds like it is), you are planning on creating a UNIQUE Constraint or Index on the URL field. Even if they did allow for specifying the number of allowed entries per distinct value, this plan still wouldn't work due to indexes only allowing up to 900 bytes. A lot of URLs are way over that limit, and considering that you should be using NVARCHAR to store the URLs (since they can have Unicode characters in them, even if that is somewhat rare, hence you should also enable DATA_COMPRESSION if you are using Enterprise Edition and SQL Server 2008 or newer) that is only 450 (generally speaking) characters, which is even more limiting.

You should create an AFTER INSERT, UPDATE trigger to enforce this rule. Using a trigger will allow you to enforce this rule regardless of the source of the INSERT or UPDATE, whereas putting this logic into stored procedures is not guaranteed to catch all attempts to insert or update the table, especially ad hoc queries.

In terms of performance, depending on how many rows this table will have over time, it certainly will slow things down a little to have to do this check each time. You should find ways to narrow down a subset of the data for faster searching so that you don't hold up all INSERT and UPDATE queries. Some things to consider:

Normalize the URLs out to another table. URLs can be up to 2048 characters (I believe) so if you know that you will generally have repeats, create a separate URL table that will have the unique URLs and just place a UrlID in this table. Then you can easily index the UrlID Foreign Key field. This approach will save tons of disk space, make the trigger much faster, and actually make most queries (especially those that don't need the URL text) against this table faster as you will be fitting many more rows on each data page.
If you need to keep the URL in this table, add a UrlHash VARBINARY field and populate that with the result of HASHBYTES. The UrlHash field can also be indexed in which case you could consider adding the Url field to that index as an INCLUDE column (which doesn't have the 900 byte limit, but also is not sorted and has no statistics). Then your trigger can search on the HASHBYTES value of the incoming value and any entries that match would then compare the full URL text. Yes, you need to compare both in the case where the hash value matches since there can be collisions on the hash values across different source strings. But it would help you easily eliminate non-matches which should be the vast majority of the time.

This UrlHash field should be a non-Persisted Computed Column (and indexed as noted above). This will ensure that the hash value is always in sync with the source string. The reason for "non"-Persisted is that being PERSISTED is not required for that value to be in the index, and the value is only needed for the index, so there is no reason to take up the extra space. Please see the MSDN documentation for Indexes on Computed Columns for more details, if interested.


Looking for advice on how to integrate data from 100+ client DB's into a centralized reporting database

Do we need to consider replicating each database individually and then perhaps use some sort of data virtualization technique to combine data from the various replicated sources?

Yes.  You can host multiple subscriber databases on a single instance, and then query across them with views, or load them into a consolidated database.


Will it create 2 Primaries here, like 1 PRIMARY from DC1 and 1 PRIMARY from DC2?

Assuming this deployment represents a single replica set, the expected outcome is either 1 primary or no primary.  In the event of network problems, a primary can only be sustained in the partition with a strict majority of voting members that are able to communicate with each other.

While replica sets can have up to 50 members in total, there is a maximum of 7 voting members so you cannot have 9 nodes with priorities.

If you have the maximum of 7 voting members distributed across your data centres, you will need at least 4 of those members healthy in order to elect (or sustain) a primary. If the current primary cannot see a majority of voting members, it will step down and become a secondary.

  DC1 - 3 Nodes with priority 300 DC2 - 3 Nodes with priority 200 DC3 - 3 Nodes with priority 100

If your intent is to prefer DC1 and DC2, that would likely mean having 3 voting members in DC1 and DC2 with a single voting member in DC3. With this configuration the primary would always be in DC1 if available, but preferentially in DC2 in the event DC1 is isolated. Majority votes could be achieved with all voting members in any two DCs available, or any degraded scenario with at least 4 voting members communicating in the replica set.

A primary would be unlikely in DC3, but possible transiently if DC1 is isolated and there is replication lag between DC1 and DC2 where the voting member in DC3 (with lowest priority) has a more current oplog than all eligible secondaries in DC2 (with medium priority). In this case, as soon as a higher priority member in DC2 has caught up with the lower priority primary in DC3, an election will be called so the higher priority member can be elected.

For more information, see:

Replica Sets Distributed Across Two or More Data Centres (MongoDB manual)
MongoDB Multi-Data Centre Deployments (White paper from MongoDB, Inc)


Run show schemas - the ones installed by default
are:

mysql> show schemas;
+----------------------------+
| Database                   |
+----------------------------+
| information_schema         |
| mysql                      |
| performance_schema         |
| test                       |
+----------------------------+

Just don't use any of these and you
should be OK - is there any particular reason
for your query?

Further comment from the OP:

  My reason is that I allow my users to pick a name for their
  environment, and it implicitely creates a database with the same name.
  This is why I have to ensure my exclusion list is reliable.

But surely, even in the unlikely event that your users try and create a schema that already exists, the attempt will be rejected anyway?


which database engine is best for full text search on a 10M rows table?

First, relational systems are not really all that great for full text search.  PostgreSQL's full text search isn't bad as things go but it has important limitations.  For example you cannot search for neighboring words.  So you are going to have to break this part out to another system.  You are far better off just getting a dedicated system for that part.  Most of the RDBMS-based solutions tend to be good enough to use when using other filters as well for general db queries, but that's not the same thing as being good enough on their own.

The second thing to keep in mind is that this is a specific type of search query.  Get a dedicated engine to handle it.

That doesn't mean ditch the RDBMS though.  You very likely are going to need to track things which an RDBMS does a good job at and if you go with a fully non-relational approach you will paint yourself into corners.  NoSQL means saying "No, you can't run an ad hoc report aggregating the data in that way because we aren't storing it in that way."  In general you want to be storing everything except the actual documents and full text indexes in an RDBMS.


Here's the process I use to migrate to a new report server. This is assuming you are not already using source control and you don't have a Visual Studio solution for your reports. If you do, skip to #4. If you have the database permissions, you can use the Microsoft guide Moving the Report Server Databases.
1. Backup deployed reports

Remote to the current report server.

%systemroot%/system32/mstsc.exe

Then save the following procedure as a .rss file and run it with the parameter parentFolder="" as a zero string to save the entire folder structure with all the reports.

Command Line:
rs -s http://localhost/reportserver -i D:\Scripts\Backup_Reports.rss -e Mgmt2010 -v backupFolder="D:\Scripts\BackupReports" -v parentFolder=""
Report Backup Procedure:
Public Sub Main()
    '--------------------------------------------------------------------------------------------------------------------
    ' Purpose:   Script to backup reports from a folder on ReportServer
    '            Save file as .rss extension and run using rs.exe from command line.
    ' Reference: http://bhushan.extreme-advice.com/back-up-of-ssrs-reports-using-rs-utility/
    '            https://docs.microsoft.com/en-us/sql/reporting-services/tools/rs-exe-utility-ssrs?view=sql-server-2017
    ' Example:   rs -s http://localhost/reportserver -i D:\Scripts\Backup_Reports.rss -e Mgmt2010 -v backupFolder="D:\Scripts\BackupReports" -v parentFolder="/IndividualReportFolderNameHere"
    '            rs -s http://localhost/reportserver -i D:\Scripts\Backup_Reports.rss -e Mgmt2010 -v backupFolder="D:\Scripts\BackupReports" -v parentFolder=""
    '--------------------------------------------------------------------------------------------------------------------
    Try
        rs.Credentials = System.Net.CredentialCache.DefaultCredentials
        Dim items As CatalogItem() = Nothing

        If String.IsNullOrEmpty(parentFolder) Then
            items = rs.ListChildren("/", True)
        Else
            items = rs.ListChildren(parentFolder, False)
        End If

        Console.WriteLine()
        Console.WriteLine("...Reports Back Up Started...")

        For Each item As CatalogItem In items
            If item.TypeName = "Report" Then
                Console.WriteLine(item.Path)
                Dim reportPath As String = item.Path
                parentFolder = Path.GetDirectoryName(item.Path) ' comment out this line to save the reports in one folder
                Dim reportDefinition As Byte() = rs.GetItemDefinition(item.Path)
                Dim rdlReport As New System.Xml.XmlDocument
                Dim Stream As New MemoryStream(reportDefinition)
                Dim backupPath As String = Path.Combine(backupFolder, Date.Now().ToString("yyyy.MM.dd") + "\" + parentFolder)

                If (Not System.IO.Directory.Exists(backupPath)) Then
                    System.IO.Directory.CreateDirectory(backupPath)
                End If

                rdlReport.Load(Stream)
                rdlReport.Save(Path.Combine(backupPath, item.Name + ".rdl"))

                Console.WriteLine(item.Name + ".rdl")
            End If
        Next

        Console.WriteLine("...Reports Back Up Completed...")
        Console.WriteLine()

    Catch e As Exception
        Console.WriteLine(e.Message)

    End Try

End Sub

2. Create a Visual Studio solution

Make a report project per report folder under one solution.
Add the reports from the backup .rdl files
Add Shared Data Sources
Add Shared Datasets

3. Use source control with Microsoft Azure DevOps Services

It's free for teams of 5 or less
You can pick a site name with your organization in the url. e.g. https://yourcompany.visualstudio.com
url: https://azure.microsoft.com/en-au/services/devops/

4. Setup deployment configuration
Setup the target deployment settings for each report project using the Configuration Manager.
Report Project Configuration

Configuration Manager

5. Build the solution

I only use this option for the initial build. After that, you can deploy individual reports by right clicking them and selecting Deploy.


QUESTION #1

  Why are there different levels of MySQL collation/charsets?

ANSWER TO QUESTION #1

There are two good reasons for different character sets and collations

Reason #1 : Disk Space

When you run this query

SELECT
    maxlen,
    GROUP_CONCAT(CHARACTER_SET_NAME) CharSets,
    COUNT(1) CharSetCount
FROM information_schema.character_sets
GROUP BY maxlen\G

You get this:

mysql> SELECT
    ->     maxlen,
    ->     GROUP_CONCAT(CHARACTER_SET_NAME) CharSets,
    ->     COUNT(1) CharSetCount
    -> FROM information_schema.character_sets
    -> GROUP BY maxlen\G
*************************** 1. row ***************************
      maxlen: 1
    CharSets: cp1257,cp850,binary,koi8r,latin2,ascii,tis620,koi8u,greek,armscii8,keybcs2,macroman,latin7,cp1251,cp1256,dec8,hp8,geostd8,latin1,swe7,hebrew,cp1250,latin5,cp866,macce,cp852
CharSetCount: 26
*************************** 2. row ***************************
      maxlen: 2
    CharSets: big5,cp932,sjis,gbk,ucs2,euckr,gb2312
CharSetCount: 7
*************************** 3. row ***************************
      maxlen: 3
    CharSets: eucjpms,ujis,utf8
CharSetCount: 3
*************************** 4. row ***************************
      maxlen: 4
    CharSets: utf16,utf32,utf8mb4
CharSetCount: 3
4 rows in set (0.00 sec)

mysql>

Some character sets have a Maximum Length of 1 byte to represent a character. Other need more. Give this information, you may want to refrain from using the eucjpms, ujis, utf8, utf16, utf32, utf8mb4 character sets so that VARCHAR and TEXT data takes less space on disk.

Reason #2 : Internationalization

Characters Sets Each Come With One or More Collations to cover a variety of Languages

When you run this query

SELECT
    A.CHARACTER_SET_NAME,
    GROUP_CONCAT(COLLATION_NAME) Collations,
    COUNT(1) CollationCount
FROM
    information_schema.character_sets A
    INNER JOIN information_schema.collations B
    USING (CHARACTER_SET_NAME)
GROUP BY A.CHARACTER_SET_NAME\G

You will see that some Characters Sets have with multiple collations for Different Parts of Europe. Chinese, Japanese, Greek, and parts of Asia Minor and Scandinavia are also available.

QUESTION #2

  Should you always ensure your PHP connection matches the charset of the database you're working on?

ANSWER TO QUESTION #2

SCENARIO

You are driving at 3:00 AM. You are the only driver on the road. You come to an intersection. You have the red light.

Question : Do you stop or go through the red light?

Answer : Depends on the neighborhood

Safe neighborhood ?

Some abide by the law, stop at the red, and wait for green.
Some chance it and go through

Bad neighborhood or new to the area ?

Some abide by the law, stop at the red, and wait for green AT THE RISK OF A CARJACKING
Some chance it and go through to AVOID OR REDUCE RISK OF A CARJACKING
Assume the worst and find another route

How does this apply?

You should err on the side of caution. You should always check the charset beforehand because you do not know the neighborhood (client program, internet browser) the PHP connection will be entering and if there is a risk of a carjacking (putting invalid data into the database, requesting too much data for retrieval).

QUESTION #3

  If you can have different tables that use different character sets do you just use SET NAMES or mysql(i)_set_charset to switch?

ANSWER TO QUESTION #3

By all means

QUESTION #4

  If you have a table that has multiple charsets how do you manage that since the connection can only use one charset at a time?

ANSWER TO QUESTION #4

You may have to shift character sets with the DB Session. Here are the settings that can be changed at the session level:

character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results

Please set these carefully before reading from and writing to the database. It would also be wise to store the character set name and collation in the same table you will be accessing.


You could do that in Oracle 11gR2 with expdp and the REMAP_DATA option.

Create a function in a package that takes a blob as argument, and returns null (or an empty blob perhaps).
Call expdp as usual, adding:

REMAP_DATA=SCHEMA.TAB.BLOB_COLUM:SCHEMA.PACKAGE.YOUR_FUNCTION

Short example (schema: mat):

create or replace
package remap as
  function null_lob(col blob) return blob;
end remap;
/

create or replace
package body remap as
  function null_lob(col blob)
    return blob is
    eblob blob;
  begin
    return null;
  end null_lob;
end remap;
/

create table foo(a number, b blob);
-- insert a line with a 10M blob, commit

select a, length(b) from foo;

     A  LENGTH(B)
---------- ----------
     1   10485760

Plain export:

$ expdp mat/*** DIRECTORY=EXP_DIR TABLES=MAT.FOO
...
Total estimation using BLOCKS method: 11.06 MB
...
. . exported "MAT"."FOO"                                 10.00 MB       1 rows
...

Dump file is ~11M.

With the data remap:

$ expdp mat/*** DIRECTORY=EXP_DIR TABLES=MAT.FOO \
                REMAP_DATA=MAT.FOO.B:MAT.remap.null_lob
...
Total estimation using BLOCKS method: 11.06 MB
...
. . exported "MAT"."FOO"                                 5.398 KB       1 rows
...

Dump file is ~100k, importing that dump does indeed import a null in the column.

Another option would be to use an external table to do the load/unload, with the ORACLE_DATAPUMP driver (I think this was introduced in 10g, see external table concepts).

For the unload, use something like:

create table foo_unload
  organization external (
    type oracle_datapump
    default directory exp_dir
    location ('foo.dmp')
  )
as select a, empty_blob() b from foo;

(You can drop the external table right after creating it, that doesn't - by default anyway - delete the dump file.)

Then move the foo.dmp file to your target server & directory, and create the external table there:

create table foo_load (a number, b blob)
  organization external
    type oracle_datapump
    default directory exp_dir
    location ('foo.dmp')
  );

And finally load your target table (CTAS or truncate ...; insert /*+ append */ ... for instance):

create table foo as select * from foo_load;

Drop the external loader table once that's done.


How to design a relationship database table to store friendship relationship?

I'd create a table a lot like the one that you have.  I'm using SQL Server data types and syntax, you may need to tweak depending on your platform.

CREATE TABLE FriendStatus
(FriendStatusId BIGINT PRIMARY KEY IDENTITY(1,1),
FromUserId BIGINT,
ToUserId BIGINT,
StatusId TINYINT,
SentTime DATETIME2,
ResponseTime DATETIME2);

Indexing of the table will be critical as the table grows to the tens and hundreds of millions.


Is there a way to find out who changed the password for a login?

My article will help if you set it up in advance, but not when the event happened in the past and you didn't have any kind of auditing mechanism set up.

There is still hope, though. Let's say I did this:

CREATE LOGIN flooberella WITH PASSWORD = N'x', CHECK_POLICY = OFF;

This information is in the default trace under EventClass 104 (Audit Addlogin Event). However, if I change the password using either of these methods:

ALTER LOGIN flooberella WITH PASSWORD = N'y';

EXEC sp_password N'y', N'z', N'flooberella';

These events are not captured by the default trace, for obvious security reasons - it should not be possible for anyone with access to the default trace to figure out what someone else's password is, nor do they want to make it easy to even find out that a password has been changed (polling the frequency of these events, for example, can reveal certain properties of your security strategy).

So what else can you do? While this relies on the information still being in the log, and it also relies on using an undocumented DBCC command against a system database (you may wish to back up master and restore it elsewhere), you can get some information from the transaction log, e.g.:

DBCC LOG(master, 1);

This will yield, for the above two commands, rows with the following (partial) information:

Current LSN             Description
======================  ======================================================================
000000f2:000001b8:0002  ALTER LOGIN;0x01050000000000051500000093a3bcd7a9f8fb1417ab13bce8030000
000000f2:000001b8:0004  Alter login change password;0x01050000000000 ... same sid as above ...

Doesn't seem like much, but now take that 0x portion of the description, and then do:

SELECT name FROM sys.server_principals
  WHERE sid = 0x01050000000000051500000093a3bcd7a9f8fb1417ab13bce8030000;

Smoking gun! This is the person responsible for that event.

Of course, if they use ALTER LOGIN syntax for all operations (which they should be using instead of sp_password), you can't distinguish between someone changing the default database and someone changing the password. You also can't tell (at least that I can see) which login this affected, only that this person changed a login. Jon seems to think that this information is in the log as well, but I failed to find it (unlike the time information, which somehow I scrolled right past).

There may be different answers for contained users in SQL Server 2012 - though I suspect password changes are still obfuscated in similar ways. Will leave that for a separate question.


Is it possible to block the access of a super user?

That should be easy. Put the databases in different PostgreSQL clusters on the same server.
But even if both databases are in the same cluster, a superuser can only access the database if there is a line in pg_hba.conf that allows it. So you can achieve the goal with a properly configured pg_hba.conf file. That requires that the superuser is known by name – you cannot add a line that says “block all superusers from that database”.


I want to find percentage change of Open Interest for every Scrip in the table (previous day's Open Interest value being the base value) in MySQL

SELECT
  y.Trade_date,x.Scrip,
  (((y.Open_Interest - x.Open_Interest)/x.Open_Interest)*100)
     AS Change_in_OI from stock_future x
JOIN stock_future y
  ON y.Scrip = x.Scrip
  AND y.Trade_date = '2020-03-17'
WHERE x.Trade_date = '2020-03-16';


Database failed to process. Divide by zero error encountered running sp_BlitzIndex

That's my bug! It's in the statistics gathering portion. If you search for the line:

CASE WHEN si.rowmodctr > 0

Replace the whole case expression with this:

CASE WHEN si.rowmodctr > 0 THEN CAST(si.rowmodctr / ( 1. * NULLIF(si.rowcnt, 0) ) * 100 AS DECIMAL(18, 1))
                                     ELSE si.rowmodctr
                                END AS percent_modifications,

The best place to file bug reports for Blitz* scripts is over here.

Thanks!