Using "Generate Scripts" with EMERGENCY/SINGLE USER mode

You can drop your connection and the database will stay in EMERGENCY/SINGLE user mode.  And the object manager in SSMS does use it's own connection.  I'm not sure if the script generator tool uses yet another connection or not.  However there is no way to guarantee that your generate scripts tool connection will be the one that takes up the one connection once you have dropped it.  The best bet is to generate your script on an alternate database (where ever you are moving your data to should work).  Then copy that script to the query window that is connected to the database and run it.

EDIT:

Ok I did a little testing.  If you drop your query connection and open up a connection in Object Explorer you can script an INSERT statement (assuming that is what you want).

In Object Explorer find your table, then right click on it.  Go to Script Table As -> INSERT To -> Clipboard

Make sure you use clipboard.  If you do new query window it will fail.  Then disconnect Object Explorer and open a query window to your database and paste.

Otherwise I'll need more information about what type of INSERT statement you are trying to create.

EDIT2:  Based on comments

Assuming that you have your destination database (where you are trying to move the data to) on the same instance (and if it isn't make one at least temporarily) then use the following piece of code.

INSERT INTO DestinationDB.dbo.DestinationTable
SELECT * FROM SourceTable

If you have an identity column then you will have to do the following

SET IDENTITY_INSERT DestinationDB.dbo.DestinationTable ON

INSERT INTO DestinationDB.dbo.DestinationTable
(Field List)
SELECT [Field List] FROM SourceTable

SET IDENTITY_INSERT DestinationDB.dbo.DestinationTable OFF


How to prevent different connections treating bytea in same query differently?

I suspect that the difference is the appearance of a bytea depending on the current setting of bytea_output:
CREATE TABLE test (id integer PRIMARY KEY, t text, b bytea);

SHOW bytea_output;

 bytea_output
--------------
 hex
(1 row)

INSERT INTO test VALUES (1, decode('74657374', 'hex'), decode('74657374', 'hex'));

TABLE test;

 id |     t      |     b
----+------------+------------
  1 | \x74657374 | \x74657374
(1 row)

SET bytea_output = 'escape';

TABLE test;

 id |     t      |  b
----+------------+------
  1 | \x74657374 | test
(1 row)

INSERT INTO test VALUES (2, decode('74657374', 'hex'), decode('74657374', 'hex'));

TABLE test;
 id |     t      |  b
----+------------+------
  1 | \x74657374 | test
  2 | test       | test
(2 rows)

RESET bytea_output;

TABLE test;

 id |     t      |     b
----+------------+------------
  1 | \x74657374 | \x74657374
  2 | test       | \x74657374
(2 rows)

The text column actually contains the result of the type cast from bytea to text, which depends on the current setting of bytea_output.
The bytea column contains the correct four bytes, but is displayed differently, depending on the current setting of bytea_output.


Postgresql 8.3: Limit resource consumption per query

To limit memory consumption, the main configuration parameter is work_mem.  Since this applies per operation, not per query, you cannot simply set it to N if you want to spend N amount of memory, but you have to tweak and tune it a bit to get the desired result.

This doesn't help, however, in case of bugs and other memory leaks in the server code or extensions.  You could control that with the process-specific resource limits controlled by ulimit.  But if you reach the limit and memory allocation fails, what do you want to have happen?  It probably won't behave too nicely.  Better fix those bugs or use a different version.

The stop queries whose execution time passed a threshold, use the parameter `statement_timeout', e.g.,

SET statement_timeout TO '10min';


row n was truncated; it contained more data than there were input columns: only some rows

Try opening your file with a text editor, and going to the 66th line (header + 65).

You'll see the movie_title is:

  "The Chronicles of Narnia: The Lion, the Witch and the Wardrobe "

This title includes a comma, which (based on your criteria) means that the above is interpreted as movie_title ("The Chronicles of Narnia: The Lion) and num_voted_users (the Witch and the Wardrobe "). this will push each of the following columns over by one, leaving you with an extra column in this row.

And, of course, that's not a particularly useful num_voted_users value....

However, the solution is already there in the file (for this row at least - presumably for the others). This .csv file appears to be formatted so that values that include the column separator are enclosed by double quotes (").

Try running your script with the following modification:

FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'

If MySQL Workbench handles this like most systems I've worked with, it won't assume that every value is enclosed by double quotes; however, if a value starts with double quotes, it will ignore column separators until it gets to the closing double quote.

Tip: I downloaded your file, opened it in a text editor, and did the following to find lines with problems:

counted the number of commas in the header line (there were 11);
using a regular expression search, marked all lines with exactly 11 commas;
Inverted the marking, so only lines with more or less than 11 commas were marked;
Looked at the first marked line.

(FYI - I use TextPad as my editor; any good text editor should be able to do something like this, though the details may vary).


Effective SQL Server permissions when user is in several AD groups

Rights applicable to the login are summed then applied from least restrictive to most restrictive on a per object basis.  DENY'd rights take precedence over GRANT'd rights.

Taken from the MSDN SQL Server page on Database Engine Permissions:

  Checking permissions can be complex. The permission check algorithm includes overlapping group memberships and ownership chaining, both explicit and implicit permission, and can be affected by the permissions on securable classes that contain the securable entity. The general process of the algorithm is to collect all the relevant permissions. If no blocking DENY is found, the algorithm searches for a GRANT that provides sufficient access. The algorithm contains three essential elements, the security context, the permission space, and the required permission.

  General Steps of the Algorithm

  When the algorithm is determining whether to allow access to a securable, the precise steps that it uses can vary, depending on the principals and the securables that are involved. However, the algorithm performs the following general steps:

  Bypass the permission check if the login is a member of the sysadmin fixed server role or if the user is the dbo user in the current database.
  Allow access if ownership chaining is applicable and the access check on the object earlier in the chain passed the security check.
  Aggregate the server-level, database-level, and signed-module identities that are associated with the caller to create the security context.
  For that security context, collect all the permissions that are granted or denied for the permission space. The permission can be explicitly stated as a GRANT, GRANT WITH GRANT, or DENY; or the permissions can be an implied or covering permission GRANT or DENY. For example, CONTROL permission on a schema implies CONTROL on a table. And CONTROL on a table implies SELECT. Therefore, if CONTROL on the schema was granted, SELECT on the table is granted. If CONTROL was denied on the table, SELECT on the table is denied.
  A GRANT of a column-level permission overrides a DENY at the object level.
  Identify the required permission.
  Fail the permission check if the required permission is directly or implicitly denied to any of the identities in the security context for the objects in the permission space.
  Pass the permission check if the required permission was not denied and the required permission contains a GRANT or a GRANT WITH GRANT permission either directly or implicitly to any of the identities in the security context for any object in the permission space.


Get IO stats request sql like activity monitor

You can use a query like this to calculate read/write rates and latency (though due to my laziness these figures are not in units that match Activity Monitor).

SELECT d.name, f.name, f.type_desc, f.physical_name,
 [read b/ms] = num_of_bytes_read * 1.0/sample_ms,
 [avg read latency] =
   (1.0*s.io_stall_read_ms / (COALESCE(NULLIF(s.num_of_reads,0),1))),
 [write b/ms] = num_of_bytes_written * 1.0/sample_ms,
 [avg write latency] =
   (1.0*s.io_stall_write_ms / (COALESCE(NULLIF(s.num_of_writes,0),1)))
FROM sys.master_files AS f
INNER JOIN sys.databases AS d
ON f.database_id = d.database_id
INNER JOIN sys.dm_io_virtual_file_stats(default, default) AS s
ON d.database_id = s.database_id
AND f.[file_id] = s.[file_id];

See these resources for more info:

What Virtual Filestats Do, and Do Not, Tell You About I/O Latency
How to examine IO subsystem latencies from within SQL Server
Capturing IO latencies for a period of time
The Accidental DBA (Day 26 of 30) : Monitoring Disk I/O
sys.dm_io_virtual_file_stats (MSDN)

If you want to simulate the graph and capture spikes in real time, well, you'll need to build scaffolding to collect snapshots of this (or some other) query, and compare the deltas to generate metrics. This is not something that is trivial to write for you on a Q & A site - people pay big money for high-quality monitoring tools that make this kind of thing easy. :-)

If you want to get an idea of how Activity Monitor does this kind of thing, you can get at the definition for the procedure #am_generate_waitstats with a little effort. I did the heavy lifting for you and here it is in all its unformatted glory - this is how it calculates various wait statistics:

CREATE PROC #am_generate_waitstats AS
BEGIN

-- Setup query starts a tran -- make sure it wasn't orphaned
WHILE (@@TRANCOUNT > 0) COMMIT TRAN;

-- Get the most recent snapshot ID in the history table ('previous snapshot')
DECLARE @previous_snapshot_id bigint;
DECLARE @previous_collection_time datetime;
SELECT @previous_snapshot_id = ISNULL (MAX (snapshot_id), 0) FROM #am_wait_stats_snapshots;
SELECT TOP 1 @previous_collection_time = ISNULL (collection_time, GETUTCDATE()) FROM #am_wait_stats_snapshots
WHERE snapshot_id = @previous_snapshot_id;

-- The snapshot we're about to capture ('current snapshot')
DECLARE @current_snapshot_id bigint;
DECLARE @current_collection_time datetime;
SET @current_collection_time = GETUTCDATE();
SET @current_snapshot_id = @previous_snapshot_id + 1;

-- The snapshots table holds the two most recent snapshots. Delete the older of the two to
-- make room for a new snapshot.
DELETE FROM #am_wait_stats_snapshots WHERE snapshot_id < @previous_snapshot_id;
DELETE FROM #am_resource_mon_snap WHERE current_snapshot_id < @previous_snapshot_id;

DECLARE @interval_sec numeric (20, 4);
SET @interval_sec =
    CASE
        -- Avoid int overflow that DATEDIFF (ms, ...) can cause in the event of a huge gap between intervals
        WHEN DATEDIFF (second, @previous_collection_time, @current_collection_time) >= 10
            THEN DATEDIFF (second, @previous_collection_time, @current_collection_time)
        -- Avoid divide-by-zero
        WHEN DATEDIFF (millisecond, @previous_collection_time, @current_collection_time) = 0.0
            THEN 0.0001
        ELSE DATEDIFF (millisecond, @previous_collection_time, @current_collection_time) / 1000.0
    END;

-- This query captures in-progress and completed (cumulative) wait time for each wait type
INSERT INTO #am_wait_stats_snapshots
SELECT
    @previous_snapshot_id + 1 AS snapshot_id,
    @current_collection_time AS collection_time,
    wait_type,
    SUM (waiting_tasks_count) AS waiting_tasks_count,
    SUM (signal_wait_time_ms) AS signal_wait_time_ms,
    SUM (wait_time_ms) AS wait_time_ms,
    SUM (raw_wait_time_ms) AS raw_wait_time_ms
FROM
(
    -- global server wait stats (completed waits only)
    SELECT
        wait_type,
        waiting_tasks_count,
        (wait_time_ms - signal_wait_time_ms) AS wait_time_ms,
        signal_wait_time_ms,
        wait_time_ms AS raw_wait_time_ms
    FROM sys.dm_os_wait_stats
    WHERE waiting_tasks_count > 0 OR wait_time_ms > 0 OR signal_wait_time_ms > 0
    UNION ALL
    -- threads in an in-progress wait (not yet completed waits)
    SELECT
        wait_type,
        1 AS waiting_tasks_count,
        wait_duration_ms AS wait_time_ms,
        0 AS signal_wait_time_ms,
        wait_duration_ms AS raw_wait_time_ms
    FROM sys.dm_os_waiting_tasks
    -- Very brief waits quickly will roll into dm_os_wait_stats; we only need to
    -- query dm_os_waiting_tasks to handle longer-lived waits.
    WHERE wait_duration_ms > 1000
) AS merged_wait_stats
GROUP BY merged_wait_stats.wait_type;

/*
Now join the current snapshot to the prior snapshot to calculate the wait time for
the just-completed time interval.

The previous_snapshot derived table represents cumulative wait stats at the beginning
of the just-completed time interval, while current_snapshot is wait stats at the end of
the interval. By subtracting the start cumulative wait time from the end cumulative
wait time, we can calculate the wait time that accumulated during this time interval.

The query uses a CTE to expose the interval-specific stats that are the result of the
comparison of the current snapshot to the previous snapshot.

Notes on the calculation of the [weighted_average_wait_time_per_sec] column:
-------------------------
We use a weighted average formula to provide the user with a more stable indicator of
the recent waittime. This is primarily useful if the user has selected a rapid refresh
rate.  With a sample interval of 1 second, for example, it could be hard to assess the
overall bottleneck on the system if short-term fluxuations in waittime distribution
were creating constant changes in the 'worst' wait category for the just-completed 1
second sample interval.

The goal of this is to make the average of (approximately) the past 30-60 seconds
dominate the value.  The longer the sample interval, the greater the weight that is
given to the just-completed sample interval. With a 1-second sample interval, the
just-completed sample is given a weight of about 10%.  With a 5 second sample
interval, it is given a weight of about 50%.  Sample intervals longer than 5 seconds
quickly approach 100% (a 60 second interval is weighted as ~80%, and a 10 minute
interval at ~95%).

The formula for the weight W of the current interval is below (T is the duration of
the just-completed time interval, in seconds):

    W  =  (1 - (T / (T^1.4 + 0.1)))

You can see the curve of the weight function by running this TSQL:
    DECLARE @interval_sec numeric(20, 2)
    DECLARE @current_interval_weight numeric (20, 2)
    SET @interval_sec = 1
    PRINT CONVERT (char(20), 'Interval (sec)') + 'Interval Weight'
    PRINT CONVERT (char(20), '==============') + '==============='
    WHILE (@interval_sec <= 6000)
    BEGIN
        SET @current_interval_weight = 1 - (@interval_sec / (POWER (@interval_sec, 1.4) + 0.1))
        PRINT CONVERT (char(20), @interval_sec) + CONVERT (char(20), @current_interval_weight)
            + '  ' + REPLICATE ('-', @current_interval_weight * 100)
        SET @interval_sec = CASE WHEN @interval_sec = 1 THEN 5 ELSE @interval_sec + 5 END
    END

The wait time for the just-completed interval is combined with the previous weighted
average in the following way, where P is the previous sample's weighted average wait
time, C is the current interval's wait time, and W is @current_interval_weight (the %
weight given to the current waittime, using the prior formula):

    ((1-W) * P) + (W * C)

*/

DECLARE @current_interval_weight numeric (10,6);
-- SET @current_interval_weight = @interval_sec / 60;
SET @current_interval_weight = 1 - (@interval_sec / (POWER (@interval_sec, 1.4) + 0.1))
IF @current_interval_weight > 1 SET @current_interval_weight = 1;

WITH interval_waitstats AS
(
    -- First get resource wait stats for this interval.
    SELECT
        #am_wait_types.category_name,
        current_snapshot.wait_type,
        -- All wait stats will be reset to zero by a service cycle, which will cause
        -- (snapshot2waittime-snapshot1waittime) calculations to produce an incorrect
        -- negative wait time for the interval.  Detect this and avoid calculating
        -- negative wait time/wait count/signal time deltas.
        CASE
            WHEN (current_snapshot.waiting_tasks_count - previous_snapshot.waiting_tasks_count) < 0 THEN current_snapshot.waiting_tasks_count
            ELSE (current_snapshot.waiting_tasks_count - previous_snapshot.waiting_tasks_count)
        END AS interval_waiting_tasks_count,
        -- Use [raw_wait_time_ms] for this check because [wait_time_ms] is a calculated
        -- value and tiny differences in sample time for signal wait time vs. wait time
        -- can cause its calculated value to be slightly negative even when stats weren't
        -- reset.
        CASE
            WHEN (current_snapshot.raw_wait_time_ms - previous_snapshot.raw_wait_time_ms) < 0 THEN current_snapshot.wait_time_ms
            ELSE (current_snapshot.wait_time_ms - previous_snapshot.wait_time_ms)
        END AS interval_resource_wait_time,
        CASE
            WHEN (current_snapshot.signal_wait_time_ms - previous_snapshot.signal_wait_time_ms) < 0 THEN current_snapshot.signal_wait_time_ms
            ELSE (current_snapshot.signal_wait_time_ms - previous_snapshot.signal_wait_time_ms)
        END AS interval_resource_signal_time,
        current_snapshot.wait_time_ms AS resource_wait_time_cumulative
    FROM (
            SELECT * FROM #am_wait_stats_snapshots WHERE snapshot_id = @previous_snapshot_id
        ) AS previous_snapshot
    INNER JOIN (
            SELECT * FROM #am_wait_stats_snapshots WHERE snapshot_id = @current_snapshot_id
        ) AS current_snapshot
        ON previous_snapshot.wait_type = current_snapshot.wait_type
    INNER JOIN #am_wait_types ON #am_wait_types.wait_type = current_snapshot.wait_type
    WHERE #am_wait_types.ignore != 1 AND #am_wait_types.category_name != 'Idle'
)
INSERT INTO #am_resource_mon_snap
SELECT
    @previous_snapshot_id AS previous_snapshot_id,
    @current_snapshot_id AS current_snapshot_id,
    @previous_collection_time AS previous_collection_time,
    @current_collection_time AS current_collection_time,
    @interval_sec AS interval_sec,
    interval_waitstats.category_name,
    interval_waitstats.wait_type,
    interval_waitstats.interval_waiting_tasks_count,
    -- Tiny differences in sample time for signal wait time vs. wait time can cause our
    -- calculated wait time to be slightly negative
    CASE
        WHEN interval_waitstats.interval_resource_wait_time < 0 THEN 0
        ELSE interval_waitstats.interval_resource_wait_time
    END AS interval_resource_wait_time,
    interval_waitstats.interval_resource_signal_time,
    CONVERT (bigint, interval_waitstats.interval_resource_wait_time / @interval_sec) AS interval_wait_time_per_sec,
    CONVERT (numeric (10, 2), interval_waitstats.interval_resource_wait_time / @interval_sec / 1000) AS interval_avg_waiter_count,
    interval_waitstats.resource_wait_time_cumulative,
    CASE
        WHEN previous_interval_waitstats.weighted_average_wait_time_per_sec IS NULL
        THEN CONVERT (bigint, interval_waitstats.interval_resource_wait_time / @interval_sec)
        ELSE
            -- weighted average formula -- see comment above for explanation
            ((1-@current_interval_weight) * previous_interval_waitstats.weighted_average_wait_time_per_sec)
                + (@current_interval_weight * CONVERT (bigint, interval_waitstats.interval_resource_wait_time / @interval_sec))
    END AS weighted_average_wait_time_per_sec
FROM interval_waitstats
LEFT OUTER JOIN (
    SELECT * FROM #am_resource_mon_snap
    WHERE current_snapshot_id = @previous_snapshot_id
) AS previous_interval_waitstats ON interval_waitstats.wait_type = previous_interval_waitstats.wait_type;

-- We can delete the data for the previous snapshot now
DELETE FROM #am_resource_mon_snap WHERE current_snapshot_id < @current_snapshot_id;
END

For file I/O specifically, it is not so cut and tried. With a trace you can uncover code snippets like these, but I have yet to find what ties them together, or even where the #temp tables are defined - I suspect some of the math and other work is done in the application and/or in other queries I'm not capturing in my trace. Needless to say, this is not just a simple SQL query.

Batch #1:

SET NOCOUNT ON;

DECLARE @previous_collection_time datetime;
DECLARE @previous_total_io_mb numeric (28, 1);
DECLARE @current_collection_time datetime;
DECLARE @current_total_io_mb numeric (28, 1);
DECLARE @mb_per_sec numeric (20, 1);

-- Get the previous snapshot's total I/O
SELECT TOP 1 @previous_collection_time = collection_time, @previous_total_io_mb = total_io_bytes
FROM #am_dbfileio
ORDER BY collection_time DESC;

-- Get the current total I/O.
SET @current_collection_time = GETDATE();
SELECT @current_total_io_mb = SUM(num_of_bytes_read + num_of_bytes_written) / 1024.0 / 1024.0
FROM sys.dm_io_virtual_file_stats(default, default);

-- Calc the total I/O rate (MB/sec) for the just-completed time interval.
-- Round values larger than 2MB/sec to the nearest MB.
SET @mb_per_sec = (@current_total_io_mb - @previous_total_io_mb) / DATEDIFF (millisecond, @previous_collection_time, @current_collection_time) * 1000;
IF @mb_per_sec > 2
BEGIN
  SET @mb_per_sec = ROUND (@mb_per_sec, 0);
END;

-- Save off current total I/O
INSERT INTO #am_dbfileio (collection_time, total_io_bytes)
VALUES (@current_collection_time, @current_total_io_mb);

-- Return the I/O rate for the just-completed time interval.
SELECT ISNULL (@mb_per_sec, 0) AS mb_per_sec;

-- Get rid of all but the most recent snapshot's data
DELETE FROM #am_dbfileio WHERE collection_time < @current_collection_time;

DECLARE @current_collection_time datetime;
SET @current_collection_time = GETDATE();

-- Grab a snapshot
INSERT INTO #am_dbfilestats
SELECT
    @current_collection_time AS collection_time,
    d.name AS [Database],
    f.physical_name AS [File],
    (fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB Read],
    (fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written],
    (fs.num_of_reads + fs.num_of_writes) AS [Total I/O Count],
    fs.io_stall AS [Total I/O Wait Time (ms)],
    fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)]
FROM sys.dm_io_virtual_file_stats(default, default) AS fs
INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.file_id = f.file_id
INNER JOIN sys.databases d ON d.database_id = fs.database_id;

-- Get the timestamp of the previous collection time
DECLARE @previous_collection_time datetime;
SELECT TOP 1 @previous_collection_time = collection_time
FROM #am_dbfilestats
WHERE collection_time < @current_collection_time
ORDER BY collection_time DESC;

DECLARE @interval_ms int;
SET @interval_ms = DATEDIFF (millisecond, @previous_collection_time, @current_collection_time);

-- Return the diff of this snapshot and last
SELECT
    cur.[Database],
    cur.[File] AS [File Name],
    CONVERT (numeric(28,1), (cur.[Total MB Read] - prev.[Total MB Read]) * 1000 / @interval_ms) AS [MB/sec Read],
    CONVERT (numeric(28,1), (cur.[Total MB Written] - prev.[Total MB Written]) * 1000 / @interval_ms) AS [MB/sec Written],
    -- protect from div-by-zero
    CASE
        WHEN (cur.[Total I/O Count] - prev.[Total I/O Count]) = 0 THEN 0
        ELSE
            (cur.[Total I/O Wait Time (ms)] - prev.[Total I/O Wait Time (ms)])
                / (cur.[Total I/O Count] - prev.[Total I/O Count])
    END AS [Response Time (ms)]
FROM #am_dbfilestats AS cur
INNER JOIN #am_dbfilestats AS prev ON prev.[Database] = cur.[Database] AND prev.[File] = cur.[File]
WHERE cur.collection_time = @current_collection_time
    AND prev.collection_time = @previous_collection_time;

-- Delete the older snapshot
DELETE FROM #am_dbfilestats
WHERE collection_time != @current_collection_time;

Batch #2:

DECLARE @current_collection_time datetime;
SET @current_collection_time = GETDATE();

-- Grab a snapshot
INSERT INTO #am_dbfilestats
SELECT
    @current_collection_time AS collection_time,
    d.name AS [Database],
    f.physical_name AS [File],
    (fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB Read],
    (fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written],
    (fs.num_of_reads + fs.num_of_writes) AS [Total I/O Count],
    fs.io_stall AS [Total I/O Wait Time (ms)],
    fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)]
FROM sys.dm_io_virtual_file_stats(default, default) AS fs
INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.file_id = f.file_id
INNER JOIN sys.databases d ON d.database_id = fs.database_id;

-- Get the timestamp of the previous collection time
DECLARE @previous_collection_time datetime;
SELECT TOP 1 @previous_collection_time = collection_time
FROM #am_dbfilestats
WHERE collection_time < @current_collection_time
ORDER BY collection_time DESC;

DECLARE @interval_ms int;
SET @interval_ms = DATEDIFF (millisecond, @previous_collection_time, @current_collection_time);

-- Return the diff of this snapshot and last
SELECT
    cur.[Database],
    cur.[File] AS [File Name],
    CONVERT (numeric(28,1), (cur.[Total MB Read] - prev.[Total MB Read]) * 1000 / @interval_ms) AS [MB/sec Read],
    CONVERT (numeric(28,1), (cur.[Total MB Written] - prev.[Total MB Written]) * 1000 / @interval_ms) AS [MB/sec Written],
    -- protect from div-by-zero
    CASE
        WHEN (cur.[Total I/O Count] - prev.[Total I/O Count]) = 0 THEN 0
        ELSE
            (cur.[Total I/O Wait Time (ms)] - prev.[Total I/O Wait Time (ms)])
                / (cur.[Total I/O Count] - prev.[Total I/O Count])
    END AS [Response Time (ms)]
FROM #am_dbfilestats AS cur
INNER JOIN #am_dbfilestats AS prev ON prev.[Database] = cur.[Database] AND prev.[File] = cur.[File]
WHERE cur.collection_time = @current_collection_time
    AND prev.collection_time = @previous_collection_time;

-- Delete the older snapshot
DELETE FROM #am_dbfilestats
WHERE collection_time != @current_collection_time;

So, if you'd like to work from there, that's probably a good starting point. Personally, since I know there are already plenty of tools that do what I'm trying to do, my time is better spent elsewhere, and I prefer to not re-invent the wheel. YMMV.


How to measure or estimate the streaming replication lag in Postgres 9.2

My question is this: what impacts the delay between creating a new record on the master, and when it shows up / is replicated to the slave?

What really matters

Replication lag depends on a few things.

  The archive command is only invoked on completed WAL segments. Hence, if your server generates only little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage. To put a limit on how old unarchived data can be, you can set archive_timeout to force the server to switch to a new WAL segment file at least that often. Note that archived files that are archived early due to a forced switch are still the same length as completely full files. It is therefore unwise to set a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable.

wal_level

First, in order to use streaming replication meaningfully WAL has to be set to hot_standby (now called replica in 9.6+). Logical decoding is useful to make sense of WAL logs if you need to do that. For instance, using logical decoding you can make use of these plugins:

test_decoding – the default plugin
wal2json – shows the changes in JSON format
decoder_raw – reconstructs the query that has applied the change

See this post for more information

Other things mentioned

I don't think these things matter substantially,

wal_keep_segments this stores how many past log files you're keeping.  If you exceed this limit you fall out of sync. If you transfer fast enough and don't have connect interruptions I'm not sure how much it matters. You can now use replication slots to do this in a better way.
max_wal_senders WAL shipping either works or not. You set this on servers exporting wal logs to enable replication and it goes against max_connections.


ERDs: How do you indicate mutually-exclusive encounters?

I don't know about ERD but, if I have understood correctly, this kind of business rule is very common when considering relationships between entities e.g. consider this example in SQL:

CREATE TABLE People
(
 person_ID VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE Marriages
(
 spouse_1 NOT NULL UNIQUE REFERENCES People (person_ID),
 spouse_2 NOT NULL UNIQUE REFERENCES People (person_ID),
 CHECK (spouse_1 < spouse_2)
);

In addition to enforcing the rule that a person cannot marry themselves, it also ensures that the same marriage is not entered a second time 'in the other direction' i.e. 'Albert is married to Victoria' is the same as 'Victoria is married to Albert' but only the row  `('Albert', 'Victoria') may be entered into the database.

If this is too complex for ERD, consider using another more expressive model e.g. object role modelling.


pg_stat_activity - is it possible to decompose a stored procedure further?

No, but given the function is read-only, one can try running it again in the concurrent session and see if the slowdown is reproducible.

If it is, try running the same queries as this function does manually. You may find them by running \sf+ on the function in psql, or, if you are running Bucardo5, by looking at the schema definition: https://github.com/bucardo/bucardo/blob/master/bucardo.schema


Running EXPLAIN in a SQL client improve performances on production server

Use JOIN .. ON to establish how the tables are related.  (Actually a "self-join" in this case.)
Since you are building the WHERE clause, get rid of the spurious stuff about 1 at the end.
If version cannot be negative, use > 0 instead of <> 0; this might be easier to optimize.
Since you are JOINing on executionNumber and lineNumber, there is no need to repeat the filtering on them.

Something like this:

SELECT  t.*
    FROM  myTable AS t,
    JOIN  myTable AS t2
      ON   t2.lineNumber = t.lineNumber
      AND  t2.executionNumber = t.executionNumber
    WHERE  t2.version = 0
      AND  t.version > 0
      AND  t2.executionNumber='87710'
      AND  t2.lineNumber IN (24000,24001,24002,24003,[...],25996, 25997,25998,25999 )
    ORDER BY  t.lineNumber, t.version > 0, t.recordDate DESC;

Now it is easier to see what INDEXes are needed.  (Please provide SHOW CREATE TABLE; meanwhile, I will guess.)

I think myTable needs both of these "composite" indexes:

INDEX(version, executionNumber, lineNumber)
INDEX(executionNumber, lineNumber, version)

Then show us EXPLAIN SELECT ....  I think t2 will be referenced first, using the first index.  Then t will be reached into using the second.  In both cases, the "Rows" should be much less than what you saw.

There will be a "filesort" and probably "using temporary".  They are unavoidable due to the complexity of the ORDER BY.

I assume the table is ENGINE=InnoDB?  Then I don't see how 5.7.xx would end up with any inconsistencies in EXPLAIN.  Gone are the days of re-probing to get statistics at the 'wrong' time.

I'm pretty sure the Optimizer has no 'cache' for explain-plans.  However, the plan may change from one day to the next, due to lots of inserts, etc.


Calculating balance sheet with a single query

If your account table doesn't change often, you could take advantage of Postgres Materialized Views.

CREATE MATERIALIZED VIEW account_tree
AS
WITH RECURSIVE
  -- recursively build tree table, assigning path for each row
  tree
AS (
    SELECT account.id        AS id,
           account.parent_id AS parent_id,
           account.name      AS name,
           ARRAY[account.id] AS path
      FROM account
     WHERE account.parent_id IS NULL

     UNION ALL

    SELECT account.id                     AS id,
           account.parent_id              AS parent_id,
           account.name                   AS name,
           tree.path || ARRAY[account.id] AS path
      FROM account, tree
     WHERE account.parent_id = tree.id
  )
SELECT
    t.id,
    t.account_id,
    CASE WHEN t.parent_id = t.account_id
         THEN a.name ELSE t.name END AS name
FROM
    (SELECT id, parent_id, name, unnest(path) as account_id
     FROM   tree) t
JOIN
    account a
    ON a.id = t.parent_id;

Materialized views allows the use of index:

CREATE INDEX account_tree_ix
  ON account_tree (id, account_id);

This view returns next result:

 id | account_id | name
--: | ---------: | :--------------
101 |        100 | ASSET
101 |        101 | Cash
102 |        100 | ASSET
102 |        102 | Bank
201 |        200 | LIABILITY
201 |        201 | Loan
301 |        300 | EQUITY
301 |        301 | Opening Balance

Now you can use this view to obtain the balance sheet avoiding the recursive CTE:

SELECT    at.account_id,
          at.name,
          coalesce(abs(sum(p.amount)), 0) AS amount
FROM      account_tree at
LEFT JOIN posting p
          ON p.account_id = at.id
GROUP BY  at.account_id,
          at.name
ORDER BY  at.account_id;

account_id | name            |     amount
---------: | :-------------- | ---------:
       100 | ASSET           | 1500000.00
       101 | Cash            |  250000.00
       102 | Bank            | 1250000.00
       200 | LIABILITY       |  500000.00
       201 | Loan            |  500000.00
       300 | EQUITY          | 1000000.00
       301 | Opening Balance | 1000000.00

db<>fiddle here


MySQL table locks solution -> InnoDb / Partitions

Definitely go with InnoDB, and especially upgrade to MySQL 5.5 (if you're not already running it and if possible). There are many improvements in InnoDB performance in 5.5.

Frankly, your use-case of a table that is 10GB and 15million rows can easily be handled by InnoDB. Proper tuning is the stuff of another question though!

As a side note though, even if you partitioned it and left it as MyISAM, you would still get table-locking issues.

  For example, a SELECT from a partitioned MyISAM table causes a lock on the entire table.


It is not possible to restore an EC2 snapshot to RDS.

It is possible, though not necessarily advisable, depending on the circumstances, to restore an EC2 snapshot to another server.


Selecting info from users who have been inactive for at least a week

Assuming the date you are referring to is t_week.created_at, not i.created_at:

select * from (
    select DISTINCT ON (u.id) u.id, t_week.created_at, ...
        FROM users u JOIN task t_week on u.id = t_week.user_id JOIN...
        ORDER by u.id, t_week.created_at desc
) foo
WHERE t_week.created_at < %s
ORDER BY u.id, i.created_at

You have to use the sub-select because DISTINCT ON doesn't support a HAVING clause, so you wrap it in a sub-select and use a WHERE clause on the outside.

This would not find people have never done a task all.  To do that you would need to do a left join to task table, then

WHERE t_week.created_at < %s or t_week.created_at is null


Why is postgresql still consuming space on my other drive?

I do not have all information for pointing to the source of your problem, but I think you should create your postgresql cluster in a different way. Instead of using the postgresql generic command pg_ctl, you should use the Debian/Ubuntu provided command pg_createcluster. This way you will have a working setup that survives reboot, you may specify your data directory and your log directory, you may also specify the start policy in order to activate it automatically on boot. And you may use all cluster commands: pg_lsclusters, pg_upgradecluster, and so on.

Every cluster uses three directories:

/etc/postgresql/8.4/CLUSTERNAME, for its configuration
your data directory, for all your data and WAL (default /var/lib/postgres/8.4/CLUSTERNAME)
your log directory, for your logs (default /var/log/postgresql)


Access denied when connecting to mongosqld with MySQL

As per MongoDB documentation Connect from the MySQL Client You can use the command-line MySQL client to connect to the MongoDB Connector for BI.

Connect from MySQL without Authentication or TLS/SSL

To connect to a mongosqld instance listening on the MySQL default port 3307, run the following command:

mysql --protocol tcp --port 3307

Connect from MySQL with Authentication

Install the C Authentication Plugin as described in Install the C Authentication Plugin.

The following example uses the C authentication plugin to connect to a mongosqld instance listening on port 3307 as user reportsUser. The MySQL shell prompts for the password after the command has been entered.

mysql --user='reportsUser?source=admin' --default-auth=mongosql_auth -p

Note: This example assumes that the authentication plugin file mongosql_auth.so is located in the default MySQL plugin folder. The location of the plugin folder varies by platform, but you can locate it by running the following command:

mysql_config --plugindir

mysql_config.pl can find the plugin directory only on macOS and Linux hosts.

Important : The C Authentication plugin was developed against MySQL 5.7.18 Community Edition (64-bit), and tested with MySQL 5.7.18 Community Edition and the latest version of MongoDB Connector for BI. The plugin is not compatible with MySQL Server or Connector/ODBC driver version 8 and later.

Connect from MySQL with Authentication and TLS/SSL

To connect to a mongosqld instance listening on port 3307, as user grace using authentication mechanism PLAIN, and using specific TLS/SSL CA and x.509 certificates, run the following command:

mysql --enable-cleartext-plugin --protocol tcp --port 3307 \
  --user='grace?mechanism=PLAIN&source=$external' \
  --ssl-ca=/path_to_the_CAcert/ca.crt \
  --ssl-key=/path_to_my_certificate_key/mysql.key \
  --ssl-cert=/path_to_my_client_certificate/mysql.crt \
  -p

Important: The binary distribution of MySQL Community uses the yaSSL SSL library to encrypt connections. MySQL Enterprise uses OpenSSL which is compatible with MongoDB Connector for BI. Use MySQL Enterprise to connect to BI Connector over TLS / SSL.

If using the $external authentication source, wrap your username in single quotes or escape the $ character with a backslash to prevent your shell from performing interpolation.

For further your reference here


What happen when changing column type for a table with clustered index?

1: If the column you're changing is part of the clustered index definition, then yes. If not, no. Any non-clustered indexes involving that column will have to be rebuilt when you change the column type, also.

2: A heap just means no clustered index, so nothing to rebuild. Same as answer #1 for non-clustered indexes.


Does it matter to convert integer primary key into equivalent string?

There are potential performance differences and sorting differences (collation dependent) per this DBA.StackExchange answer and it's linked answers such as this one. I highly recommend reading through all the information referenced in that answer.
These sections of the above linked articles are specifically relevant:

There is no performance difference among these three types [char,
varchar, text]...

Short answer: integer is faster than varchar or text in every aspect.

Therefore integer (and BIGINT) is more performant of a data type, even of equal data length.


How do I compare two tables with the same column definitions in different schemas?

If you just want to do a quick check, you can always use EXCEPT in you query to identify if rows in 2 tables are identical.

SELECT 'TABLE1-ONLY' AS SRC, T1.*
FROM (
      SELECT * FROM TABLE1
      EXCEPT
      SELECT * FROM TABLE2
      ) AS T1
UNION ALL
SELECT 'TABLE2-ONLY' AS SRC, T2.*
FROM (
      SELECT * FROM TABLE2
      EXCEPT
      SELECT * FROM TABLE1
      ) AS T2
WITH UR;

If you are using Toad, it has data compare features as well.


Suggestion for storing a lot of simple data

To reduce space requirements you might consider a few things:

Not bothering to store http:// or the leading www. - this is just wasted space (though in a few cases the www. is required because people don't know how to configure their web sites properly).
Making sure you use data compression. Most systems are still I/O-bound, not CPU-bound.
Only storing any domain name once, and storing the page URLs separately. Both may actually repeat and storing them multiple times is wasteful. So for example instead of:

CREATE TABLE dbo.Pages
(
  ID BIGINT IDENTITY(1,1) PRIMARY KEY, -- need BIGINT for 500 billion rows
  URL VARCHAR(2048) -- far too large to apply UNIQUE
);

CREATE TABLE dbo.PageLinks
(
  PageID BIGINT NOT NULL FOREIGN KEY REFERENCES dbo.Pages(ID),
  LinkID BIGINT NOT NULL FOREIGN KEY REFERENCES dbo.Pages(ID),
  PRIMARY KEY (PageID, LinkID)
);

You could do something like this:

CREATE TABLE dbo.Domains
(
  DomainID INT IDENTITY(1,1) PRIMARY KEY, -- probably no more than 2BN domains
  DomainName VARCHAR(255) NOT NULL
) WITH (DATA_COMPRESSION = PAGE);

CREATE UNIQUE INDEX dn ON dbo.Domains(DomainName)
  WITH (DATA_COMPRESSION = PAGE);

CREATE TABLE dbo.URLs
(
  URLID INT IDENTITY(1,1) PRIMARY KEY, -- maybe you need BIGINT here
  URL VARCHAR(2048) NOT NULL -- still can't apply UNIQUE here
  -- but you can have the same URL (e.g. /page.php) from two different
  -- domains only listed once.
);

CREATE TABLE dbo.DomainURLs
(
  DomainURLID INT IDENTITY(1,1) PRIMARY KEY, -- may also need BIGINT here
  DomainID INT NOT NULL FOREIGN KEY REFERENCES dbo.Domains(DomainID),
  URLID INT NOT NULL FOREIGN KEY REFERENCES dbo.URLs(URLID)
) WITH (DATA_COMPRESSION = PAGE);

CREATE UNIQUE INDEX du ON dbo.DomainURLs(DomainID, URLID)
  WITH (DATA_COMPRESSION = PAGE);

Yes, table design and query semantics will be much more complex, but it will scale much better once you index many pages on the same site (or many pages with the same URL across different sites).

Just to demonstrate the potential for space savings here. Taking just the storage of the URLs and ignoring the links between them, let's look at just the Pages, Domains and URLs tables (and to be fair, I'll even test your pages table with compression).

Note that we take advantage of the fact that you will probably index each site in turn, alphabetically, rather than hash and index random URLs each iteration. This allows compression to work as well as possible in this case.

For some sample data, on my system this generates about 121,000 rows, but that will vary depending on a lot of factors, such as how many databases are on your system, how many objects in your database, the design of any non-system objects (e.g. number of columns), and even @@VERSION:

;WITH x AS
(
  SELECT
    d = t.name + CONVERT(VARCHAR(5), d.database_id) + '.com',
    p = '/' + c.name + '.php'
   FROM sys.all_objects AS t
   CROSS JOIN sys.databases AS d
   INNER JOIN sys.all_columns AS c
   ON t.[object_id] = c.[object_id]
 )
 SELECT d, p
 FROM x
 ORDER BY d, p;

Sample results:

all_columns1.com    /collation_name.php
all_columns1.com    /column_id.php
all_columns1.com    /default_object_id.php
all_columns1.com    /is_ansi_padded.php
all_columns1.com    /is_column_set.php
...

Now let's use that to populate our four tables:

;WITH x AS
(
  SELECT
    d = t.name + CONVERT(VARCHAR(5), d.database_id) + '.com',
    p = '/' + c.name + '.php'
   FROM sys.all_objects AS t
   CROSS JOIN sys.databases AS d
   INNER JOIN sys.all_columns AS c
   ON t.[object_id] = c.[object_id]
 )
 SELECT d, p
 INTO #blat
 FROM x
 ORDER BY d, p;

 CREATE CLUSTERED INDEX x ON #blat(d, p);

 INSERT dbo.Pages(URL) SELECT 'http://www.' + d + p FROM #blat ORDER BY d, p;

 INSERT dbo.Pages_compressed(URL) SELECT 'http://www.' + d + p FROM #blat ORDER BY d, p;

 INSERT dbo.Domains(DomainName) SELECT DISTINCT d FROM #blat ORDER BY d;

 INSERT dbo.URLs(URL) SELECT DISTINCT p FROM #blat;

Now of course you would have to build the junction table here by cross-referencing the Domains and the URLs. But this will still show how much space you can save by not storing the same domain name or page name twice, even if it means more complicated logic to put these things together:

EXEC sp_spaceused 'dbo.Pages';            -- 8,904 KB
EXEC sp_spaceused 'dbo.Pages_compressed'; -- 4,552 KB
EXEC sp_spaceused 'dbo.Domains';          --   656 KB
EXEC sp_spaceused 'dbo.URLs';             --   136 KB

That's with 121,000 URLs. For 500 billion? Let's extrapolate. Per complete URL, without compression, you are storing about 73 bytes. With compression, 38 bytes per URL. My method: 6.5 bytes per URL. (Discard, for a moment, how unrealistic my sample data is. Let's pretend I've come somewhere close to the average URL length, and that both (a) you will be indexing many URLs on every domain and (b) you will get duplication of page paths across many domains.)

So ignoring those realities, simple math shows more than 90% reduction in storage space between your uncompressed method and my method:

Your method, uncompressed = 33.2 TB
Your method, compressed   = 17.1 TB
My method                 =  2.9 TB

And again, it's more complicated, so more work up front, but usually this is worth it. You only design the schema and write the code around it once; you'll be maintaining this for, well, how long do you expect the service to exist?


SQL Server one-to-many and index fragmentation

A related question is whether I should have a primary key on the
  Course table at all? I usually add it by default to all of my (except
  association) tables, but in this case I never query courses
  individually, but always in the context of a student. Does it make
  sense to keep the primary key, even if I know I'll never reference it
  from anywhere? Would the lack of a primary index hurt performance?

Every table should have Primary Key/Clustered Index.Since ID is seldom use in join condition.You can drop Clustered index from ID column.

Make Studentid +courseID in Course table as Clustered index.

In Future or Present, Studentid will be most frequently use in join condition and in all important query.On ther hand Course ID column will be very less use and not so important query.

I think [StudentId] [uniqueidentifier]  should have been INT or Bigint.

  Now the 2 courses are no longer next to each other, and this probably
  causes big performance problems in the long run since my keys are now
  fragmented, is this correct?

You can rebuild index.It is very normal in database.So when index is rebuild then id=1 and next id=5000 will be very much together.

First of all you can reconsider [Description] [nvarchar](max) NOT NULL

If it is possible to make it VARchar (2000),(3000) etc.

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.

Is manually keeping the Id's together bad practice?

Just because there is gap in ids that doesn't mean it is Index Fragmentation or it will hurt performance in any way.Gap in id is ok.Even if ids are continuous there will be index fragmentation.

Index Fragmentation


You can use below script :

SELECT QUOTENAME(SCHEMA_NAME(tb.[schema_id])) AS 'SchemaName'
    ,QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) AS 'TableName'
    ,C.NAME AS 'ColumnName'
    ,T.NAME AS 'DataType'
    ,C.max_length
    ,C.is_nullable
FROM SYS.COLUMNS C
INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.[object_id]
INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id
WHERE tb.[is_ms_shipped] = 0
ORDER BY tb.[Name]

For Complete Documenting sql server, I highly recommend :

SQL Server & Windows Documentation Using Windows PowerShell written by Kendal Van Dyke

Brief description from the link :

  SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations. SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. SQL Power Doc is also capable of documenting Windows Azure SQL Databases.


Postgres - Optimizing an view dependent on an aggregate function?

You can hugely simplify the query using DISTINCT ON:

SELECT DISTINCT ON (client, practice, account_id, encounter_id, charge_id)
       id, charge_id, post_date
FROM   charges
ORDER  BY client, practice, account_id, encounter_id, charge_id, post_date DESC, id DESC;

Will be considerably faster in any case. Detailed explanation in this related answer on SO:
Select first row in each GROUP BY group?

The last ORDER BY expression id DESC is optional to break ties if rest should not be unambiguous, yet. May not be needed.

Support this with a matching multicolumn index:

CREATE INDEX charges_latest_idx ON charges
(client, practice, account_id, encounter_id, charge_id, post_date DESC, id DESC);

Whether such an index will be useful depends on undisclosed details.

Note in particular, that sort order has to match the query. In Postgres 9.2 or later this may even function as covering index, depending on undisclosed details.

Depending on undisclosed details, a materialized view might be a candidate, too. The more write operations the smaller the likelihood this would help. The same goes for the covering index.


User has Areas, which have Buildings, 3 PK's or tables in between?

From the question and comments:

A user can have an area and build on it, another user can also have that area and build on it, but they won't ever see each other's building.

Based on this, both solution 1 and 2 are ok.

The user_area table won't be a many-to-many table then, but become it's own entity.

Yes, it would a many-to-many table and an entity of its own. That's only a matter of definition really. If you have additional attributes (eg inventory per user and area), then the columns can be added in the user_area table. Then the table is more obviously an entity of its own but it is still a many-to-many table.

Let's say that later a user can also have an inventory for a certain area of his, and there can be payments added to a certain area, would solution one still be okay? Each of these new tables will have a reference to both user_id and area_id.

The payments table would need to be separate and have a FK referencing user_area.
If it were payments associated to user and building, it would be a separate table with a FK referencing building.

A user has many areas, and a user can add buildings to his/her areas.

Based on this, the area_building from the 1st solution looks correct but with a FK on (area_id, building_id) to user_area
area_building (area_id, building_id, user_id)

So my suggestion would be to use solution 1 with the following FKs:
user (id, name)
area (id, size)
user_area (user_id, area_id)
building (area_id, building_id, user_id)

I would also just rename it to building and have PK the (building_id). I would also get rid of the id names, in favour of more descriptive id names but that's a personal preference.
In detail:
user
    user_id    PK
    name
area
    area_id    PK
    size
user_area
    user_id    PK  FK1 -> user (user_id)
    area_id    PK  FK2 -> area (area_id)
building
    user_id         FK3 -> user_area (user_id, area_id)
    area_id         FK3
    building_id  PK


What's the limit of TDE certificates allowed in master database [SQL Server]

Microsoft seems not to mention a specific limit for keys or certs, but for databases it is 32767 user databases per instance.

If you consider the TDE key hierarchy all databases have their own DEK, so it's naturally to assume that at least one (and with rollover) at least two keys are possible. (It would not make sense to specify a maximum for databases if they can't use all features like a DEK).

The DEKs might all be encrypted with a single database master certificate (or multiple, but you would not have one for each DB. But even if you would, since those are just stored in master database tables, I would not see a problem (besides heavy CPU usage on startup).


Any way to represent simple AND/OR business logic in a database?

If you had billions of students and the rules were fixed, I'd recommend a stored procedure which performs the logic on rolling subsets, saving the results in another column or table.  Since you likely have fewer students, a view could handle the logic and then you'd never need to store the result.

If the rules are extremely complicated, then you'll want to pull the data into code and perform the logic there, where you have better control.


Anything obvious in optimization missed in my left join query?

SELECT  o.id
    FROM  objects AS o
    JOIN  uchog7x7  ON o.id = u.id  -- Not LEFT
    WHERE  u.needs_update = 1
      -- AND  u.id IS NOT NULL   -- not needed
      AND  o.object_name = 'face'

Don't use CHAR() unless the value is a constant length.

What fraction of the rows have 'face'?  What percentage have needs_update?

The optimizer, when given no good choice, starts with the smaller table.  According to EXPLAIN, it found about 1 row with "needs_update".  Probably that is far off, and you need to remove that index.

What version of MySQL are you using?  5.7 has improved the 'cost models', and might do a better job.

Other notes:

Don't use tinyblob; use VARBINARY(255) instead.

Why are *coord and image_data_size going into blobs; they sound like numeric values?

What will you do with the ids that come back from the query?  Some heavy-duty image processing?  If so, consider adding LIMIT 1 (or LIMIT 10) so that the query comes back faster.  I assume you will pick up others later.


How to make a function in postgresql with two parameters to return count from column table?

This would work:
CREATE OR REPLACE FUNCTION yearly_orders(_year int, _order_cat int)
  RETURNS int
  LANGUAGE sql STABLE AS
$func$
SELECT count(*)::int
FROM   orders o
WHERE  o.status = 'Requested'
AND    o.creation >= to_timestamp(_year::text, 'YYYY')
AND    o.creation <  to_timestamp((_year + 1)::text, 'YYYY')
AND    o.code = _order_cat;
$func$;

The conversion from integer to timestamp really depends on what you pass exactly and what that's supposed to mean. You might want to_timestamp(_year::text, 'YY') instead ...
Consider the list in Akina's comment.
Start reading the manual about about SQL functions.
Have a look at PL/pgSQL functions after that.
Overview:

Difference between language sql and language plpgsql in PostgreSQL functions


After migrating from MySQL 5.7.16 to MariaDB 10 database fails to start

Fixed this by dumping the MySQL database and reimporting it with MariaDB.


PostgreSQL: import several .csv files through sql copy statement

Now, since the server administrator are being a little privy of their server, they would like to give us access only to the SQL server and not to the underlying unix server.

I'm not sure how you're accessing the SQL server from that. However, if you're going to keep getting access to psql, perhaps you can work around their security with \! in psql

  \! [COMMAND]           execute command in shell or start interactive shell


Entries disappeared, auto_increment also reversed, web app security vulnerability or DB Error?

If you are using external DB Hosting, you should expect some intermittency due to the hosting. There are no native MySQL mechanisms in play. I have seen happen while evaluating XEROUND.
When I signed up for the 30-day trial, I was given two public IPs to access the MySQL Database. I loaded a lot of data on it. I was able to access it from one East-coast based public IP and the data was not immediately manifested from the West-coast based public IP till minutes later.
You have find out from Mediatemple if you can look into using their Reboot and Repair Utilities. You may also need to make sure data is being coalesced fast enough for snapsots to be reasonably replicated.
There is one major concern. Under the FAQ section I just saw something very, very disturbing for Mediatemple's Grid Hosting:

Which versions of LAMP software comes with my (gs)? The (gs)
Grid-Service currently features the following versions of software:
PHP 5 (click for specific version)
Perl v5.8.4
Python v2.4.4
Apache 2.0.54
MySQL 5.1.26

I really hope that is a misprint. Why?
The first GA release of MySQL 5.1 is 5.1.30. Mediatemple is offering MySQL 5.1.26.
Why on God's green earth would someone offer a Pre-GA version of MySQL 5.1?
Please, make sure you are using MySQL 5.5.21 (or 5.1.61 if you have to have MySQL 5.1)
UPDATE 2012-03-05 22:51 EST
You should setup MySQL Replication is addition as a secondary form of redundancy to an outside hosting service (such as Amazon EC2) for a while. That way, any missing rows should at the very least show up in the binary logs on the Master and the relay logs on the Slave.
If your data is missing in MediaTemplate but is present and accounted for on the Slave, I would consider switching out of MediaTemplate Grid Services. If it happens more than once, switch out immediately.
If the data on the Slave is exactly the same, you can rule out Database Replication at all levels. From here it is time for a deep dive into WordPress configuration. You can also look through the binary logs to see the data you had was once there at one point disappears at another point.


There are all kinds of ways to view those error logs. All those ways require some kind of configuration by a person with access to shells (that is, ssh access) on the Ubuntu host.

Your best bet is to ask the person who controls that host for help.

Unless things are horribly goofed up (that's a technical term -:) in your MySql server, you can get enough, or almost  enough, troubleshooting information from the errors MySql gives your applications.


Can having a LIMIT on DELETE degrade performance dramatically?

No, at least not in this case.

The evidence pointing towards a DELETE ... LIMIT 1 was real, but not entirely accurate.  The real issue here was a PHP script that called hundreds of unnecessary queries in order to execute the suspected DELETE ... LIMIT 1 query.

Using MySQL's log all queried on a test environment lead to the solution to this problem.


Translating ERD diagrams into SQL DDL statements

The way you can enforce relationships between tables is by creating a foreign key which is referencing a primary key from other table. Both keys are defined either creating or modifying the table by using CREATE statement or ALTER statement, respectively.


AlwaysOn Availability Group listener port selection

Following Sql Server security hardening guidelines.

Because sometimes the Windows Firewall, if enabled, and add the exception for this IP/Port.

  Avoiding port conflicts (as per
  https://msdn.microsoft.com/en-us/library/hh213417.aspx#SelectListenerPort).

You can configure the default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string.

  Being able to identify db calls to specific AG's quicker in network
  traces and such.

Since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.

  Why would you use a non-default port for an Always On AG listener?

In the “Port” field, choose an adequate port, something that is not being used. By Default sometime port sql server port 1433.

suppose that you are configuring the port ( let say 5122) After configuring the 5122 port in AlwaysOn availability group in listener. you must test through telnet like

“telnet <your listener name> <listener port>”

After checking the test connection of listener port number . if you shall get the error like

connecting to SQLServerName ... could not open connection to the host
,on port 5122 : connect failed

Note: Here SQLServerName is the Name of the Primary Replica Server Name.

In that case, check if you didn’t misspell something (the hostname or the port), if all looks good, you might check the Windows Firewall, if enabled, and add the exception for this IP/Port. Another try is use the listener IP, instead f the computer name. If the connection using the IP works, you need to check why the name resolution is not working. Talk with the Domain/DNS/System Administrators in order to troubleshoot that.

If all looks good on Windows point of view, and if the instance/listener are really only and should be replying, you need to check with the Networking team of your company, on order to have this unlocked. Always pass to them the following information:

Source IP and hostname (from where you are connecting).
Destination IP and hostname.
Destination port.

For your further ref Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server) and Here


how to add a data source to SSRS - reporting services 2014?

With Visual Studio with SSDT-BI installed you can create new shared DataSources or copy/paste them.
https://msdn.microsoft.com/en-us/library/ms155845(v=sql.120).aspx


Equivalent of DB2 function "decimal(numeric expression, precision, scale)" in PostgreSQL


How to update data with a trigger function and a join

Just SET the value for field_1 BEFORE INSERT OR UPDATE.  Just, make sure you use the alias for the new row of data.

needs to be edited for postgresql syntax

CREATE TRIGGER trigger1
   BEFORE INSERT OR UPDATE
   ON relation_b
   FOR EACH ROW
begin
  -- "new" represent "current row being inserted/updated"
  -- just modify the values BEFORE it is stored in the DB

  select a.field_1
    into new.field_1
  from relation_a a
  where a.id_a = new.fk_a;

  -- add exception handle where "no data is found"
end;


Start by thinking through a user interface.  Will the user start with the make+model+year of the vehicle?  Or will he start with "airbag"?  More maybe you want to provide both ways to start the drilldown.

Don't plan to do all the work in the database.  Plan to either have some application code to sort through the "so inconsistent" data, or throw text at the user, and let him sift through the final information.

How many different sets of info do you have?  One per make+model+year?  One per component with a service plan?

The design might be one table, with lots of columns.

Or it may need 3 tables (to start with).  One for each distinctly different vehicle; perhaps PRIMARY KEY (make, model, year).  One for service plans; this would contain an id, a TEXT field, and some meta information ('airbag', mfg's id, etc).  The third would be the "relationship" that ties the two other tables in a many-to-many relationship; it would have two ids (for JOINing to the other tables).


There is no .bak file until you name it so.

With the last command in your question, the dump is written to the file test — this, then, is your “.bak file”.

In fact, it is an SQL script, because pg_dumpall only supports the plain text format.

You restore such a dump by feeding it to psql:

psql -h hostname -p 1234 -f test -d postgres -U postgres

Where “1234” is the port number of your new database cluster.


SQL Server 2012 Express Service Pack 2 - installation error 0x84B40001

@Nissanna, As you said, you are getting the error during cmd installation of sql server express 2012.

  The syntax of argument "/HIDECONSOLE" is incorrect;Either the
  delimiter '=' is missing or there is one or more space characters
  before the '='" Error 0x84B40001

when you are running this syntax:

  SQLEXPR_x86_ENU.exe /ACTION=Install /HIDECONSOLE  /FEATURES=SQL
  /INSTANCENAME=SQLEXPRESS /SECURITYMODE=SQL /SAPWD="SOMEPWD"
  /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"
  /SQLSYSADMINACCOUNTS="Builtin\Administrators" /AGTSVCACCOUNT="NT
  AUTHORITY\Network Service"

I would like to say that definitely you are doing some arguments mistake or permission. As MSDN BOL https://msdn.microsoft.com/en-us/library/ms144259(v=sql.110).aspx the simple syntax of installation would be as mention below format:

  Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER
  /SQLSVCACCOUNT=""
  /SQLSVCPASSWORD=""
  /SQLSYSADMINACCOUNTS="" /AGTSVCACCOUNT="NT
  AUTHORITY\Network Service" /IACCEPTSQLSERVERLICENSETERMS

NB: For local installations, you must run Setup as an administrator.

AS local installations, you must run Setup as an administrator. If you install SQL Server from a remote share, you must use a domain account that has read and execute permissions on the remote share. For failover cluster installations, you must be a local administrator with permissions to login as a service, and to act as part of the operating system on all failover cluster nodes.

As your refence https://social.msdn.microsoft.com/forums/sqlserver/en-US/87bb5999-1126-4809-a353-0f5b06c9f81d/sql-express-2008-command-line-options and https://msdn.microsoft.com/en-us/library/ms144259(v=sql.110).aspx


Can I grant privileges to a role without specifying an object?

The system privileges that relate to the object types (CREATE TABLE, VIEW, ...) can be divided in two groups:

Those that lack the keyword ANY are only effective on the user's own schema.
Those with the keyword ANY (DROP ANY TABLE, EXECUTE ANY PROCEDURE, ...) are effective on all schemas. Consequently, you should restrict the ANY privileges to administrative users.

If you grant an object type privilege (without ANY) such as CREATE TABLE to a role, all users of this role will be able to assert this privilege on their own schema. Consequently you can grant these privileges to a role and the users will inherit such rights (on their own schema) when they are granted the role.


Oracle: UTL_RAW.BIT_OR(2,1) gives me 2 instead of 3. Why?

CAST_FROM_NUMBER returns RAW; if you check result of UTL_RAW.CAST_FROM_NUMBER(2) , it's not 2 but "C103" (which I believe means  (03-1)* pow(10,(0xC1-0xC1))) .   You need something like

SELECT   UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.BIT_OR(
            UTL_RAW.CAST_FROM_BINARY_INTEGER(2),
            UTL_RAW.CAST_FROM_BINARY_INTEGER(1)
        ))

FROM DUAL;

Some links about internal number representations: http://www.jlcomp.demon.co.uk/number_format.html , http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci03typ.htm
Your code returns result of C103(internal representation of 2) & C102(internal representation of 1) which is 2


Why have “unidirectional ‘mapping’” tables?

I agree that it is a strange table.

No uniqueness constraint, so dups can occur.  I would change INDEX to PRIMARY KEY.

No index the opposite direction, so unlikely to use it that way.  I would add another column and INDEX(bin_id, board_id, pin_to_board sequence)

On the other hand, perhaps there is a lot of activity in the table -- such as updating all the sequence values frequently.  Still, InnoDB would really prefer to have a PRIMARY KEY, and that triple seems like the 'right' PK.

I would probably work harder to avoid potential timestamp dups.


Single Publication With Multiple Subscribers

Make sure distribution database is set up correctly with data and log file sizes based on the volume of data you are planning to replicate. I am not sure which version of SQL you are using, but i had issues when i was replicating from 1 publisher to 2 subscribers in SQL 2008 during re initialization. My issue was related to virtual memory being maxed out at one subscriber. It may not be applicable in your case.


It shouldn't. It depends on what the system can simplify the query to.

You can demonstrate this very easily by considering the plan for queries such as:

SELECT *
FROM YourTable
WHERE ID < 5;

SELECT *
FROM (SELECT * FROM YourTable) as t
WHERE ID < 5;

The two should be identical.


The condition:

where ( ("ANNO","MESE") IN ('2017','10'),('2017','11'),
                           ('2018','01'),('2018','02'),('2018','03')
   and  ("Invio") IN ('3'),('1'),('2')
      )

has 2 issues:

first, it needs parentheses around the IN lists (and the ones you have around the two sub-conditions are redundant):

where   ("ANNO", "MESE") IN ( ('2017','10'), ('2017','11'),
                              ('2018','01'), ('2018','02'), ('2018','03')
                            )
   and  "Invio" IN ( '3', '1', '2' )

second, even if you fix the above, it won't work in SQL Server as this type of syntax (the first sub-condition, that combines IN with tuples) has not yet been implemented. You can rewrite in various ways, eg. using a JOIN and VALUES syntax:

from
      ( ...
      ) T
    join
      ( values ('2017','10'), ('2017','11'),
               ('2018','01'), ('2018','02'), ('2018','03')
      ) as V ("ANNO", "MESE")
    on
      V."ANNO" = T."ANNO" and V."MESE" = T."MESE"
where  "Invio" IN ( '3', '1', '2' )
---

by the way, the subquery (aliased T) is not really needed. It ony adds complexity for those trying to read it:

select
    T."ATC_1",
    SUM(T."Importo_Scarto") as "Importo_Scarto",
    SUM(T."Q_Scarto") as "Q_Scarto"
from
      dbo.[MyTable] as T
    join
      ( values ('2017','10'), ('2017','11'),
               ('2018','01'), ('2018','02'), ('2018','03')
      ) as V ("ANNO", "MESE")
    on
      V."ANNO" = T."ANNO" and V."MESE" = T."MESE"
where
    T."Invio" in ( '3', '1', '2' )
group by
    T."ATC_1" ;


What causes DBCC CHECKTABLE to return "session is in the kill state"?

After poking around, discovered that the volume was very low on space so probably didn't meet the free space requirements for "dbcc checktable".


SQL Server:Buffer Manager Page reads-write/sec has exceeded the threshold

This does not really indicates a problem. What it means is SQL Server has issues more number of physical read and writes than the threshold you have set. Now next question is how often do you get the alerts, if this is often you might need to find out whether you are facing memory crunch and what queries running are causing this.

  I see PLE more than 300 which is good ?

300 mark should be completely ignored unless you have system with 4 GB memory. The base PLE should be calculated like
(DataCacheSizeInGB/4GB *300). This should be your benchmark PLE anything much lower than that should bother you. Moreover if you have NUMA system you have to track PLE for each NUMA node

  Please clarify other Performance counters if the value is good or needs to be addressed.

The counters should not be taken for a moment collect it for period of 8 hours or so and please run it when load on system is on peak. You can use Performance data collector set


... save information about the children and several pictures and documents ...
... A friend is currently saving the data of over 70 kids in folders and subfolders on her device ...
... We need the database ... frontend ... easily add, change or display informations ... without any IT-knowledge ...

I don't say this very often, but in this case, I don't think you can - or should - avoid it.
Hire somebody that knows what they're doing, especially in the fields of Data Security, Web Application Security, and who knows their way around the General Data Protection Regulations (GDPR) - at the very least.
The rules and regulations around Data Protection and Security and, more importantly still in your application, Safeguarding, etc., are Legion and you DO NOT want to get on the wrong side of them.
Question: What's does your Privacy Notice say?
If your answer is "What's a Privacy Notice", then you should not be touching this problem with a barge-pole!


SQL Server 2012 Transaction Log needed to create database mirror?

The short answer to your first question is yes, your database must be in full recovery mode in order to use the database mirroring feature:

  The database must use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, bulk operations are always fully logged for a mirrored database. For information about recovery models, see Recovery Models (SQL Server).

Prerequisites, Restrictions, and Recommendations for Database Mirroring

  In a database mirroring scenario, every update to a database, the principal database, is immediately reproduced in a separate, full copy of the database, the mirror database. The principal server instance sends each log record immediately to the mirror server instance which applies the incoming log records to the mirror database, continually rolling it forward. For more information, see Database Mirroring.

The Transaction Log (SQL Server)

To answer your second (and third) question - that is an over simplification but you could view it like that. I would highly recommend reading about the Transaction Log in more detail using the link I have provided above. The Transaction Log is used for much more than just synchronising two databases - it is also used to implement point-in-time recovery. Try to understand how the transaction log behaves under each of the different recovery modes - this will improve your understanding of why full logging is required in order to implement high-availability on SQL Server.

  The transaction log supports the following operations:

  Individual transaction recovery.
  Recovery of all incomplete transactions when SQL Server is started.
  Rolling a restored database, file, filegroup, or page forward to the point of failure.
  Supporting transactional replication.
  Supporting high availability and disaster recovery solutions: Always On availability groups, database mirroring, and log shipping.

EDIT

Based on the additional information that you added, the answer is no. You cannot ignore the transaction log when setting up database mirroring - the mirror database must be an exact copy of the source before you can start. Once it has been established, then yes it will fill in the gaps - but that only works after you have begun the mirror. I normally enable mirrors during non-business hours, do a full backup and take the database offline while I copy the backup to the mirror server.


How can we improve this PIVOT operation? ( SQL Server 2008 R2 )

I would stay away from LOOP joins, or specifying any specific joins. The SQL Server query optimizer is very good at picking the best join method to use, and it will pick LOOP on its own if that is the right one. Also, if there is a missing non-clustered index that could improve the performance of the query, SQL Profiler will find it and suggest it to you.

One thing that I noticed missing from your query is the use of common table expressions. I haven't had a pivot yet where I haven't needed to use common table expressions to get the data ready to pivot. I would rewrite your query using common table expressions (particularly the correlated subquery) and see if that improves performance. If it doesn't another thing you could consider doing is changing the order of columns in the clustered index on the tblRespostaINT to better support that query.

Finally I believe that what you are doing in the inner select can be accomplished with a windowed function, and if it can that should be faster.


When you issued the grant you were not connected to the same database as when you connected as abc1.


Get list of DB2 databases and tables through linked server

You cannot get a list of databases via ODBC; ODBC requires a connection to a specific database, and a database does not know anything about the outside world, specifically, if there are any other databases. Depending on a particular DB2 platform, even the concept of "other databases" may be nonexistent.

list database directory is a DB2 CLP command, not an SQL statement, and as such is not understood by the SQL engine.

As for the list of tables in the connected database, you can query the catalog view SYSCAT.TABLES (provided we're talking about DB2 for LUW; refer to the appropriate product manual if not).


Blocked Process Report -two updates causing a long block

The Blocked process report (which you have) can be a little confusing at first. There are a few important sections

Blocking-Process
This is the process(SP, query etc) that is causing the blocking. The process is using a resource that is required by the Blocked-Process

Blocked-Process
This is the process(SP, query etc) that is blocked and is waiting on a a resource that is in use by the Blocking-Process.

waitresource
This details the resource that the process is waiting on. Taking your example of
waitresource="OBJECT: 99:774293818:0 "

The 99 refers to the Database ID of your data and the 774293818 refers to the table. Using the ID 774293818 and systables you should be able to find the table that is causing the blocking.

I would also set up an Extended Events Trace to capture the parameters of the offending blocking process and review the execution plans.

A good starting point is https://support.microsoft.com/en-us/kb/224453


Mysql: slave read binlog from local storage

You don't need all binary logs from the master. Just the ones that your slave hasn't read yet.

On your slave you can do show slave status\G. Look for the lines Master_Log_File and      Relay_Master_Log_File. The first is the binary log file, that your master is using, the second is the log file that your slave is currently reading.

You can then delete all binary logs on the master, that the slave has already read.
For example, Relay_Master_Log_File is mysql-bin.011096, then you could execute the following statement on the master:

purge binary logs to 'mysql-bin.011096';

This is described in more detail here.

You can also configure your master to hold less binary logs by configuring the expire_logs_days variable. Read more about it here.

I assume this already solves your storage problems.

UPDATE based on comment: You can simply copy the binary logs to the slave. Remember the position you already have from the show slave status output. Stop the slave. Then execute the binary logs on the slave with

# mysqlbinlog binlog-file1 binlog-file2 --start-position=x | mysql

Note that the --start-position parameter is only applied to the first binlog file you specify as parameter. Make sure to specify the right one.

Then read the last position from the last binary log with

# mysqlbinlog binlog-file99 | tail

and change the coordinates with

mysql> change master to master_log_file='...', ...

to the appropriate position and start the slave again.

Read more about this here (I guess)


Same Query plan, no new query competition, same hardware, but stored procedure runs slowly

One possible cause for this sudden increase in duration is that there is another process blocking this query from making progress.

You mentioned that you have query store enabled.  I would run a query like this, and look for the point where avg_duration jumps up from 2 hours to 6 hours (after 9/15):

SELECT
    qsrs.first_execution_time,
    qsrs.avg_duration,
    qsrs.avg_cpu_time,
    qsrs.avg_logical_io_reads,
    qsrs.avg_logical_io_writes,
    qsrs.avg_dop,
    qsrs.avg_rowcount
FROM sys.query_store_runtime_stats qsrs
WHERE qsrs.plan_id = @your_plan_id
ORDER BY qsrs.first_execution_time DESC;

If the problem is blocking, you should see all of the other values (CPU, reads, writes, etc) stay mostly the same.  You should then be able to track this problem down by finding other queries running during this time period (either using Query Store, or running sp_WhoIsActive, or logging it to a table).

If other values in the runtime stats did change, then at least this will be a clue as far as where to look next.  For instance, I included avg_dop because maybe the query has suddenly stopped being able to use parallelism as much because of a settings change you didn't know about.

Since you've determined that CPU increased with duration, but the other metrics stayed the same, I'm tempted to think that the VM is fighting to get CPU time on the host, possibly because it's oversubscribed and there are other CPU heavy workloads running on other guests.

Jonathan Kehayias has an interesting post that shows SOS_SCHEDULER_YIELD waits could result from this situation (CPU Ready Impact on SOS_SCHEDULER_YIELD).  If you're logging wait stats, you might look to see if you can correlate the increase in duration with increases in this wait type.  Or better yet, work with your VM admin to see if there's evidence on the host side:

CPU Ready Time in VMware and How to Interpret its Real Meaning
Hyper-V Equivalent to VMware CPU Ready Time


Deleted MySQL "root" user (using phpMyAdmin)...how to restore?

OK, I finally found a solution.  To give proper credit, I had to blend answers provided by two people:  RiggsFolly's answer to this question on resetting the root password in wamp, which is what I'm using, and jm666's answer for this question on restoring a deleted root user in MySQL using MAMP on OS X.
For Wamp Users:

Click on the Wamp system tray icon to bring up the tray menu.  Click
MySQL > Service > Stop Service.

Edit your MySQL's my.ini file...that's your configuration file for your SQL database, and it's generally located in the root folder for your MySQL database (try looking in wamp > bin > mysql > mysql5.5 [or whatever version]).  Use a text editor, and scroll down in the my.ini file to [wampmysqld].  Immediately after this entry, insert a new line containing the following command:
skip-grant-tables
This tells the MySQL database that you want to override any restrictions such as root user or password parameters.  This is a major security hole, so once we are done with our repairs, we'll need to undo this!  Save the my.ini file.

Resume MySQL services in Wamp (click MySQL > Service > Start/Resume Service).

Now open the MySQL console:  click MySQL > MySQL Console

A "DOS-like" terminal window will open up (if it asks you for a password, simply press enter).  You should see a MySQL prompt.  Copy the following block of code and paste it at the prompt.
INSERT INTO mysql.user
SET user = 'root',
host = 'localhost',
password = '',
Select_priv = 'y',
Insert_priv = 'y',
Update_priv = 'y',
Delete_priv = 'y',
Create_priv = 'y',
Drop_priv = 'y',
Reload_priv = 'y',
Shutdown_priv = 'y',
Process_priv = 'y',
File_priv = 'y',
Grant_priv = 'y',
References_priv = 'y',
Index_priv = 'y',
Alter_priv = 'y',
Show_db_priv = 'y',
Super_priv = 'y',
Create_tmp_table_priv = 'y',
Lock_tables_priv = 'y',
Execute_priv = 'y',
Repl_slave_priv = 'y',
Repl_client_priv = 'y',
Create_view_priv = 'y',
Show_view_priv = 'y',
Create_routine_priv = 'y',
Alter_routine_priv = 'y',
Create_user_priv = 'y',
Event_priv = 'y',
Trigger_priv = 'y',
Create_tablespace_priv = 'y';

Press enter.  You should get some sort of message saying that one line (of database code) was affected, with some warnings (probably because you are messing with the root user...I think you can safely ignore these).  Close the MySQL terminal.

Remember that security hole we opened in step two?  Time to fix that.  Reopen your my.ini SQL configuration file, and remove the skip-grant-tables line you added.  Save the my.ini file and close.

RESTART your MySQL Service (same as before, but click on MySQL > Service > Restart Services instead).

THAT should do the trick!
A note on the root user in MySQL for newbies:  for some strange reason, even if you create another user as a superuser, MySQL seems to need this user to operate...however, if you don't set up a password for root, it notes this as a security hole.  Whatever you do, don't delete it:  you're better off just setting some wildly obscure password for it and leaving it alone if you want to use another superuser account.  It would seem that there should be more protections to help prevent unwary users from doing this (a warning, or something)....that's my twelve cents on the issue.


How we can identify if the constraint is failing or if there is some
  other issues causing this?

The most likely cause is that a client is simply inserting a null, or updating the column to null, neither of which is prevented by a default constraint.

If you don't want nulls, just declare the Version column as NOT NULL, or add a check constraint.


How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks?

There are 3 options in Value: Success, Failure and Completion (approximate). Changing the value to Completion for all the preceding precedence constraints should ensure that the backup task is reached regardless of the upstream tasks.

  Execution Results

  The precedence constraint can use the following execution results alone or in combination with an expression.

  Completion requires only that the precedence executable has completed, without regard to outcome, in order for the constrained executable to run.
  Success requires that the precedence executable must complete successfully for the constrained executable to run.
  Failure requires that the precedence executable fail for the constrained executable to run.

Further reading:

https://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/
http://technet.microsoft.com/en-us/library/ms141261.aspx
http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/11/things-you-might-not-know-about-precedence-constraints-ssis.aspx
http://blogs.msdn.com/b/mattm/archive/2006/10/30/using-expression-based-constraints.aspx


MongoDB --- Failed global initialization: Failed to open "/var/log/mongodb/mongod-config.log"

So, I have figured out my own question.

Firstly, the issue was that since I was using a different port 27019 for the config servers, I needed to open that port from the firewall.

Second, I changed the dbPath in my config file from /var/lib/mongodb to /data/db since that is the default path for mongodb to store data.

Make sure to run sudo service mongod restart after.

With that done, I was able to start mongod with my specific config file:

sudo mongod --config <path-to-config-file>

The key is to include sudo for me. That solved the issue of:

F CONTROL  [main] Failed global initialization: FileNotOpen: Failed to open
"/var/log/mongodb/mongod-config.log"

After running the command, I see that mongod has started on my virtual machine (vm) and now I am able to connect to the mongo shell of said vm from another machine with:

mongo <ip_address_of_said_vm>:27019


Relatively green Firebird n00b here w/a fair PostgreSQL background, although specialized GIS work focused mostly to DB design and the sourcing/cleaning/making spatial multi-source data sets (usually of dubious quality) and then querying for map-able results in answer to specific location riddles. Toolbox is light, therefore, on Admin/Optimization/3rd Party Application Setup etc. You may (err, will) see some Firebird specific questions from me in the coming weeks. Thanks in advance!
Took a stab at this slightly older Firebird question to get a feel for the new-to-me flavor of date handling and concatenation. Took more tweaking & testing than expected & my dabbling may not be the most efficient/elegant solution but, assuming the existence of column join_date in table employees, this will work:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
THEN datediff(year, join_date, current_date)-1
ELSE datediff(year, join_date, current_date)
END as Y,
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)-1
WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)>=0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)
WHEN datediff(year, join_date, current_date) = datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

The subquery turned conditional on me when it became clear that: a) calculations on year & month don't consider the date as a whole; and b) date subtraction will happily return negative values regardless of the order of events.

Year/Month/Day Example: datediff() returns a value of 1 when retrieving the number of days, months, or years between 31.12.2015 and 1.1.2016.

Negative Value Example: about 69% of my test-date values are less than zero when I calculate the number of days less the number of days contained by whole months using:

SELECT datediff(day, dateadd(month, datediff(month, date_column, current_date), date_column), current_date) as D FROM date_table

We can streamline the subquery if substituting 365 days for one year is acceptable. The trick works with my test data but may trigger pitfalls with a broader range of dates. The simplified query looks like:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
datediff(day, join_date, current_date)/365 as Y,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

Holler if I goofed it or if I employed bad/overwrought logic. Hope it's helpful to someone at some point! Keep a lookout for new Firebird questions (might already have locked in struggle with a JDBC connection string that
just
will
not
lose).

Thanks and hello again!
--Rob

I never had a plan-- Anonyman


This should work:

SELECT a.central_id, sum(b.value)
FROM neighbors AS a
JOIN data AS b
  ON a.neighbor_id = b.central_id
GROUP BY a.central_id;


MSSQL Spatial performance (point in polygon)

Firstly, make sure there is a spatial index on the [Shape] column.

Secondly, there is no need to call STAsText() in the WHERE clause since that will force each row to convert to string unnecessary (and disqualify the use of any spatial indexes I think).

Lastly, inspect the execution plan to see if the spatial index is used. You may have to use a index hint since SQL Server don't always choose the spatial index in my experience. See the last example here.


Is a remove

 UPDATE table_name
    set jsonB_column_name=(SELECT jsonb_agg(z.a)
                                   FROM  (SELECT jsonb_array_elements(t.jsonB_column_name) as a
                                          FROM   table_namet
                                          where id='3a-a7ed-bf88dcdde0ab') z
                                   WHERE NOT a @> '{"x":"10"}')
    where id='1';

Is a search

select max(c.jsonb->>'y')
from (SELECT jsonb_array_elements(t.jsonB_column_name) as jsonb
      FROM   table_namet) as c;


To restore individual MyISAM tables from XtraBackup:

Copy table.{frm,MYD,MYI} files to MySQL database directory e.g /var/lib/mysql/sakila
Execute FLUSH TABLES (yes, mysqld may be running)
Check the restored version is available on MySQL instance with SHOW TABLES and selecting rows from them.


It's not that a CTE needs a leading ;, but that the statement before must end with a ;.

If the statement with the CTE is the first statement in a batch (or the first statement in your SP), then there's no statement before it to end with a ;

There's a StackOverflow question about it with more detail.


Trigger that updates another table when inserting and deleting from one table

Something along the lines of the below... just join to the inserted/deleted tables.  Please do test this though with real life scenarios:

ALTER TRIGGER trgUpdatesNrOfCopies
ON bookcopy
AFTER INSERT, DELETE
AS
BEGIN
   SET NOCOUNT ON

   IF NOT EXISTS(SELECT*FROM INSERTED)
   BEGIN
       PRINT 'DELETE'
       UPDATE b
       SET nrOfCopies= nrOfCopies-1
       from book b
       inner join DELETED d
         on d.isbn = b.isbn
   END
   ELSE IF NOT EXISTS(SELECT * FROM DELETED)
   BEGIN
       PRINT 'INSERT'
       UPDATE b
       SET nrOfCopies=nrOfCopies+1
       from book b
       inner join inserted i
         on i.isbn = b.isbn
   END
END


A linked list is just a very simple directed acyclic graph.  There is no reason why this is in any way difficult, or should be avoided for sql server.
Think about it, tree structures are more complex than linked lists.  Every implementation of a forum on the internet that stores data in a relational database has implemented the basics of a linked list.  On this very page, answers form a linked list.  They can be added and deleted.  They can be moved in position (aka, ranked by votes).
The particular representation to be used depends only on the trade-offs you wish to make between  maintaining the list (insert, update, delete) and retrieval.
--Works great for INS/UPD/DEL, In order retrieval isn't the best.
CREATE TABLE Item (id int identity, next int, prev int)

--makes in order retrieval fast, deletes are a problem, inserts may require re-numbering.
CREATE TABLE Item (id int identity, position int)

--Works great for in-order retrieval, and allow cheap insertion/deletion,
--certain edge cases might be tricky to handle.
CREATE TABLE Item (id int identity, position Decimal(24,12))
--for inserts, use the average of the before and after, for deletes, just delete.

Update:  Question is about trees vs linked lists in SQL server
SQL Server has the HierarchyId data type which is designed for easing the implementation and querying of tree like structures.
CREATE TABLE Item (id int identity, NodeId HierarchyId)


Extract time portion of Datetime2(7) in hhmmssfff format

Please don't use varchar without length.
Please use semi-colons as statement terminators.
I don't think there is a more covert way to do this. Here is how I would do it (14 characters shorter, ignoring the semi-colon):

DECLARE @mdate AS DATETIME2(7);

SET @mdate = '2012-03-15 10:13:27.5437431';

SELECT REPLACE(REPLACE(CONVERT(TIME(3),@mdate),'.',''),':','');


Find highest sold item name for each date

If you want ties:

;WITH od AS
(
  SELECT o.oDate, od.itemId,
    r = RANK() OVER (PARTITION BY o.oDate ORDER BY SUM(od.itemQnt) DESC)
  FROM dbo.orderDetails AS od
  INNER JOIN dbo.orders AS o
  ON od.orderId = o.orderId
  GROUP BY o.oDate, od.itemId
)
SELECT od.oDate, i.name, r, rn
FROM od INNER JOIN dbo.items AS i
ON od.itemId = i.itemId
WHERE r = 1
ORDER BY od.oDate;

If you don't want ties, change RANK() to ROW_NUMBER(), just note that it will pick an arbitrary winner unless you add some kind of tie-breaker to the ORDER BY.

If you need to fill in gaps in some date range, for example if yesterday had no sales but you still want a row, you can generate a set of dates first, and then use an outer join (this example returns rows from January 1 -> January 15 inclusive):

DECLARE @startDate DATE, @endDate DATE;
SELECT @startDate = '20150101',
       @endDate   = '20150115';

;WITH [days](d) AS
(
  SELECT TOP (DATEDIFF(DAY, @startDate, @endDate)+1)
    DATEADD(DAY, ROW_NUMBER() OVER
    (ORDER BY [object_id])-1, @startDate)
  FROM sys.all_columns
  ORDER BY [object_id]
),
od AS
(
  SELECT o.oDate, od.itemId,
    r = RANK() OVER (PARTITION BY o.oDate ORDER BY SUM(od.itemQnt) DESC)
  FROM dbo.orderDetails AS od
  INNER JOIN dbo.orders AS o
  ON od.orderId = o.orderId
  GROUP BY o.oDate, od.itemId
)
SELECT oDate = d.d,
  name = COALESCE(i.name, '<n/a>')
FROM [days] AS d
LEFT OUTER JOIN od
ON d.d = od.oDate
AND od.r = 1
LEFT OUTER JOIN dbo.items AS i
ON od.itemId = i.itemId
ORDER BY d.d;

(Also, do you really think it's productive to make slightly shorter but far more obscure and difficult-to-remember column names like oDate and itemQnt? And why isn't there a unique constraint on orderDetails(orderId, itemId)? Finally, please stay away from regional, ambiguous formats like mm/dd/yyyy.)


Php PDO Get all column names from table in specific database

In MySQL database is a synonym for schema. Subsequently, the information you're looking for is in the column table_schema:

select column_name from information_schema.columns
where lower(table_name)=lower(\''.$table.'\')
  and lower(table_schema)=lower(\''.$mydb.'\')


#1064 - You have an error in your SQL syntax; check the manual for the right syntax to use near ''&lt)' at line 1

I'll bet one of those semicolons is really a Greek question mark (utf8 hex CDBE).  I point out the visual problem in http://mysql.rjweb.org/doc.php/charcoll#other_natural_languages


My knee-jerk reaction would be to add a CourseSchedule table between Course and PersonTranscript. The same course could obviously be offered at different times and places, with different instructors, and maybe some sessions are online vs. on-site, etc. and you need a way to separate those properties from those that are universal for the whole Course.

Then going back to the idea of adding training plans, you could have a PersonTrainingPlan table that joins Person to Course directly (they're just planning which courses to take, not actually scheduling them), whereas PersonTranscript (and possibly a PersonSchedule table if the application warrants) joins a Person to a specific instance of a CourseSchedule.


ALTER DATABASE... SET COMPATIBILITY_LEVEL -- will this impact log shipping? Can it be done live?

You can do it in off hours or when you have less activity on the server. Doing when the server is busy, will incur blocking.

Changing compatibility level does not impact logshipping, Mirroring or AlwaysON along with replication.  I have done it safely during less activity time.