What's the overhead of updating all columns, even the ones that haven't changed

I know you're mostly concerned about UPDATE and mostly about performance, but as a fellow "ORM" maintainer, let me give you another perspective on the problem of distinguishing between "changed", "null", and "default" values, which are three different things in SQL, but possibly only one thing in Java and in most ORMs:

Translating your rationale to INSERT statements

Your arguments in favour of batchability and statement cacheability hold true in the same way for INSERT statements as they do for UPDATE statements. But in the case of INSERT statements, omitting a column from the statement has a different semantics than in UPDATE. It means to apply DEFAULT. The following two are semantically equivalent:

INSERT INTO t (a, b)    VALUES (1, 2);
INSERT INTO t (a, b, c) VALUES (1, 2, DEFAULT);

This isn't true for UPDATE, where the first two are semantically equivalent, and the third one has an entirely different meaning:

-- These are the same
UPDATE t SET a = 1, b = 2;
UPDATE t SET a = 1, b = 2, c = c;

-- This is different!
UPDATE t SET a = 1, b = 2, c = DEFAULT;

Most database client APIs, including JDBC, and by consequence, JPA, don't allow for binding a DEFAULT expression to a bind variable - mostly because the servers don't allow this either. If you want to re-use the same SQL statement for the aforementioned batchability and statement cacheability reasons, you'd use the following statement in both cases (assuming (a, b, c) are all the columns in t):

INSERT INTO t (a, b, c) VALUES (?, ?, ?);

And since c isn't set, you'd probably bind Java null to the third bind variable, because many ORMs also cannot distinguish between NULL and DEFAULT (jOOQ, for example being an exception here). They only see Java null and don't know whether this means NULL (as in the unknown value) or DEFAULT (as in the uninitialised value).

In many cases, this distinction doesn't matter, but in case your column c is using any of the following features, the statement is simply wrong:

It has a DEFAULT clause
It might be generated by a trigger

Back to UPDATE statements

While the above is true for all databases, I can assure you that the trigger issue is true for the Oracle database as well. Consider the following SQL:

CREATE TABLE x (a INT PRIMARY KEY, b INT, c INT, d INT);

INSERT INTO x VALUES (1, 1, 1, 1);

CREATE OR REPLACE TRIGGER t
  BEFORE UPDATE OF c, d
  ON x
BEGIN
  IF updating('c') THEN
    dbms_output.put_line('Updating c');
  END IF;
  IF updating('d') THEN
    dbms_output.put_line('Updating d');
  END IF;
END;
/

SET SERVEROUTPUT ON
UPDATE x SET b = 1 WHERE a = 1;
UPDATE x SET c = 1 WHERE a = 1;
UPDATE x SET d = 1 WHERE a = 1;
UPDATE x SET b = 1, c = 1, d = 1 WHERE a = 1;

When you run the above, you will see the following output:

table X created.
1 rows inserted.
TRIGGER T compiled
1 rows updated.
1 rows updated.
Updating c

1 rows updated.
Updating d

1 rows updated.
Updating c
Updating d

As you can see, the statement that always updates all the columns will always fire the trigger for all the columns, whereas the statements updating only columns that have changed will fire only those triggers who are listening for such specific changes.

In other words:

The current behaviour of Hibernate that you're describing is incomplete and could even be considered wrong in the presence of triggers (and probably other tools).

I personally think that your query cache optimisation argument is overrated in the case of dynamic SQL. Sure, there will be a few more queries in such a cache, and a bit more parsing work to be done, but this is usually not a problem for dynamic UPDATE statements, much less than for SELECT.

Batching is certainly an issue, but in my opinion, a single update shouldn't be normalised to update all the columns just because there is a slight possibility of the statement being batchable. Chances are, the ORM can collect sub-batches of consecutive identical statements and batch those instead of the "whole batch" (in case the ORM is even capable of tracking the difference between "changed", "null", and "default"


Really what I do typically is just bring in all the data I think I'll ever need into the SQL database. Maximize ease of import and future flexibility.

If you don't want to show "milk" twice --- deal with that downstream. A secondary table or view that has these calculations or additional logic applied.

Typically with JSON-->SQL, the only major problem is that JSON can contain dynamic fields, whereas SQL tables have fixed fields.

It doesn't appear that your JSON contains much dynamic structure anyway (a field like tags, or other stuff like that).

So you really you can just do a 1:1 translation of all the JSON fields you care about into SQL columns, and make one row per entry. I would do this route personally -- you can call it the staging table. Apply calculation or visual logic afterward into a separate table, view, or logic.

If there are dynamic fields, that only pop up in relatively unstructured ways on certain items, create a field or two that simply contains a full brain-dump of these fields and deal with them later in SQL as well. That's been my experience with JSON to SQL.


EXCEPT & INTERSECT: Purpose of Passive Projection in Logical Plan

There are various things we do inside the query optimizer that don't really have a reason that we can explain externally.  The projects you see in the optimizer eventually get rewritten at the end of the optimizer to "flatten" the expressions.  So, the optimizer has things in the search that are part of how the code is implemented that may introduce extra projects that don't really do anything functionally but do allow us to stitch things together where one subtree was created in one part of the optimizer but is used later in a place where it was not originally intended.  There are some physical implementation details that can cause this to happen.  None of them matter for users in terms of plan quality (in almost any case - we have done work to make these not matter in the search).

So, I'd just wave my hand and say "you don't need to see his identification.  He can go about his business.  Move along" ;).


How to list all users that have grants over a package

If the privilege has been granted directly or to a role, the grant will appear in DBA_TAB_PRIVS

SELECT grantee, privilege
  FROM dba_tab_privs
 WHERE owner = 'B'
   AND table_name = 'MYPACKAGE'
   AND privilege = 'EXECUTE'

If the grantee is a role, you would then need to look at dba_role_privs to see what users (or roles) have been granted that role and follow the chain if you have roles granted to other roles.  If you need to account for users that have grants because of the (very dangerous) ANY grants (i.e. EXECUTE ANY PROCEDURE), that would require a separate query.

If you want to get more sophisticated than simply doing a straight query against dba_tab_privs, though, you are probably better off using on of Pete Finnigan's scripts like the who_has_priv.sql (or who_has_priv_procedure.sql).  Pete is probably the leading expert on Oracle security so these are much more likely to account for every possible corner case than anything I would attempt to cobble together.


What happens in this strange SQL Server Mirroring situation?

Firstly I should say great question usr. Sean, Robert and Max have both correctly stated that due to the re-negotiation between the Primary and Secondary, that the correct LSN is always resolved so that there is not possibility of any discrepancy in that scenario.

In response to the discussion with Shanky, in Synchronous mode, the Commit first has to occur on the Mirror (as you have correctly assumed usr) and this does indeed mean that the log has to be written to (those respectively log buffers flushed to disk on commit). Remember that this only means the log file has the transaction hardened to it, but at this stage, the transaction would still be "in the redo queue". This allows for a very fast commit to the Mirror, meaning that the commit on the Primary can also occur very quickly afterwards - this thereby reduces the chances further that there would be a discrepancy in the first place.

Other thing to consider is that if you are using delayed durability, this alters the mechanics further, and could actually result in lost transactions to your Mirrored database as a whole (due to the change in behaviour of the log buffer flushes - which no longer flush on commit), but resolution would between the two would still be able to occur to find the correct lsn point.


2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL

I believe you are asking for the sum of "distinct" values of owgh for each value of mid and pid - a different "granularity" of data.

e.g. for mid = 3 then add 1.5 + 0.6 + 1.2 + 3.0 = 6.3; only count the 1.5 once instead of twice.
However if your record ids for the 1.5 were for 2 different pid values, then you would want to count the 1.5 twice.

One way you could do it is instead of querying the "test" table directly, calculate some of the aggregates beforehand:

Select mid,
COUNT(distinct pid) as cpid,
SUM(nwgh) as totalnwgh,
SUM(owgh) as totowgh    -- I added this one
from
    (select mid, pid, sum(distinct owgh) as owgh, sum(nwgh) as nwgh from test group by mid, pid) as A
GROUP BY mid


MySQL wont start after modifying /etc/my.cnf

You say that you are trying to change the MySQL port from 3306 to 512.  If that is correct, then then problem is that all *nix (Linux, Solaris, AIX, etc) require you to run as root in order to bind to ports below 1024.

Try running it on port 3307 or something.


Note that for some cases, an if statement can be turned into a case expression which is somewhat similar to the ? operator in C and Java, but more generic. Your pseudo-code:

if(trans_type = 'AR','AD','AF','AG') then
trans_type:='A';
elsif trans_type = 'BA','BS','BD','BG' then
trans_type:='B';
end if;

becomes:

case
  when trans_type in ('AR','AD','AF','AG') then 'A'
  when trans_type in ('BA','BS','BD','BG') then 'B'
end case

as a case expression.


From RAC to Standby RAC With Active Dataguard

In your tnsnames.ora on the standby side the needed tns alias for 'rac' is missing. In a data guard configuration it is extremely important that all members can resolve all other members.


How to migrate data of to another sql server

For very large Databases you can always use the compression method in SQL server 2012. That will help a lot in reducing down the backup size of the database:

For how to use and more on this refer to below link:

http://www.sqlservergeeks.com/sql-server-database-backup-compression-faster-disaster-recovery/

To avail the space, make sure you're backup retention period is appropriate so as to make proper amount of disk space available.
If possible do some TPM like cleaning activity on the disk to get some space.
Also, for data level compression you can just script out the schema and then move data using BCP OUT and BCP IN. You can even zip those files and move them across the destination server.
Last but not least: One more thing to add :

Make sure that Instant File Initialization is enabled on the server. That will dramatically reduce the restore time. Refer to below link for more info on this..

http://sqlblog.com/blogs/tibor_karaszi/archive/2009/03/09/do-you-have-instant-file-initialization.aspx


I'm sure I'm missing something pretty obvious, but I can't figure out what it is.

Yes it was something very obvious...

The parameter DB_DOMAIN still contained foo.bar and therefore the instance registered itself using mydb.foo.bar.

After doing a

alter system set db_domain='' scope=spfile;

and bouncing the database everything is working as expected now.


There is no built-in function that I would know of. In Postgres 9.3+ you have the similar function array_remove(anyarray, anyelement) to (per documentation):

  remove all elements equal to the given value from the array (array
  must be one-dimensional)

To delete the 3rd element from a 1-dimenstional array with unique elements:

SELECT array_remove(arr, arr[3]);

If uniqueness is not guaranteed the above might delete multiple elements. You can concat two array slices instead:

Postgres 9.5 or older

SELECT arr[1:2] || arr[4:2147483647] AS arr_without_3rd_elem;

2147483647 is just the maximum possible array subscript to cover all possibilities. Works for any version since at least 8.3, probably more.
About array subscripts:

Normalize array subscripts for 1-dimensional array so they start with 1

Old SQL Fiddle for Postgres 9.3.

If you need this a lot, create a function. I suggest an IMMUTABLE polymorphic SQL function. (An implementation in C would be a bit faster.)

CREATE OR REPLACE FUNCTION f_array_remove_elem(anyarray, int)
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT $1[1:$2-1] || $1[$2+1:2147483647]';

Call:

SELECT f_array_remove_elem('{1,2,3,4,5}'::int[], 4)    AS i
     , f_array_remove_elem('{foo,bar,baz}'::text[], 1) AS t;

Result:

i         | t
----------+-----------
{1,2,3,5} | {bar,baz}

It's more complicated ...

for multi-dimensional arrays.
You cannot just remove a single element, dimensions have to stay in sync. So you have to define which slice to remove exactly. Then I would probably unnest the array using generate_suscripts(), remove selected subscripts and array_agg() / array_agg_mult() the result.
for non-standard array-subscripts.
Normalize subscripts in the source like demonstrated in the linked question or adapt subscripts in the expression with array_lower() and array_upper() ...

Postgres 9.6

.. introduced short notation for upper and lower limit (just omit the number). I added an answer to the question linked above:

Normalize array subscripts for 1-dimensional array so they start with 1

Simpler now:

SELECT arr[:2] || arr[4:] AS arr_without_3rd_elem;

The function:

CREATE OR REPLACE FUNCTION f_array_remove_elem(anyarray, int)
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT $1[:$2-1] || $1[$2+1:]';

Works for all 1-dimensional arrays, including non-standard array-subscripts. Just be sure to provide the correct array index of the element to remove.

dbfiddle here


Is there a name for this pattern, representing one-to-many relationships by adding columns with an index to a single table?

The Domain Expert has given you a requirement disguised as an implementation detail. I would implement this by:

Implementing a relation table as you want
Implementing a view which will give up to 4 associated registrations per owner

When an owner registers a fifth registration the system should keep working with no problem (which is probably what the DE wants) but the view he uses for reporting will still only show 4 registrations. (Maybe the view could include the total number of registrations per owner as well?)

This way you may have to change the view (used only for reporting) every now and then, rather than have to change a major table and all the relations and code that will be affected.


The SentryOne database stores data in UTC. When a user views data through the client, display the data from the S1 database, adjusted for the user's time zone. We don't really expect users to be able to do time zone conversions in their heads as they are reading charts, so if something happened at midnight their time, we show them that it happened at midnight their time instead of whatever UTC happened to be at that point.

So, I would expect users in different time zones to see different timestamps associated with events. For each of them that was n hours ago, but because the clock on their wall says different things now, they also said different things n hours ago when those events happened.

If you want users to see the same time for events regardless of time zone, you'll have to do the same kind of thing you have to do for Outlook meetings across an international team - run in UTC (or a specific time zone) instead of local time zone, or create a VM that does that. I don't know of a way to tell Outlook to show you calendar data in a time zone other than your own.

For future reference, we have an active and responsive support forum of our own; it is probably a quicker and more on-topic path to the answers you want about our software:

SentryOne Support


SysAdmin server role and Active Directory Groups

Individual accounts are going to be much harder to administer.  They give you more granular control over access, but you can not keep out your Active Directory administrators.   Anyone who is granted local administrator rights on your server can gain sysadmin access to the instance.  And, as a very good DBA told me once, if you can't trust your domain admins you've got bigger problems on your hands.

By abstracting your access to AD groups, you gain two benefits:

Easier to manage access groups and rights based on roles, combined with stratified access control for other assets using those AD groups.
Provide more consistent auditing, as you will only need to show access by AD group and then who is contained within that group.


always error "cannot insert value NULL" sqlserver

First observation is that your update statement has no WHERE clause. This means that it will update all rows of the table. Are you sure you want that?

From the comments, it seems that all rows have year_period = 2016 and you do want to update all of them. In my opinion, it wouldn't hurt to explicitly add this condition in the statement. The opposite, it would avoid any unwanted changes, in case you run it in the future, say next year when you have rows with 2016 and 2017.
Second, the error is due to for some rows, either the subquery returns a NULL value or it return no rows at all.

You can easily find the "missing" values (those that the subquery returns no rows) with this:

SELECT DISTINCT tr_kpi_id
FROM tmpiscunitmap AS t
WHERE NOT EXISTS
      ( SELECT *
        FROM    tmptr_kpi AS a
            INNER JOIN tmptr_kpi AS b ON a.tr_kpi_code = b.tr_kpi_code
        WHERE
            a.year_period = 2015 AND
            b.year_period = 2016 AND
            a.tr_kpi_id = t.tr_kpi_id
      )
--  AND year_period = 2016
 ;


You are right about UNION or UNION ALL. Due to the field column, the legs in the UNION will be disjoint:

SELECT FieldA, field1 as value, 'field1' as field
FROM T
UNION ALL
SELECT FieldA, field2 as value, 'field2' as field
FROM T

I don't think MySQL as of latest version yet supports LATERAL, but I may be mistaken so in case it does, it will be slightly more efficient to do something like:

SELECT x.FieldA, x.value, x.field
FROM T
LATERAL JOIN (
    values (T.FieldA, field1, 'field1')
         , (T.FieldA, field2, 'field2')
) x (FieldA, value, field);

You may have to do:

LATERAL JOIN (
    SELECT T.FieldA, field1 as value, 'field1' as field
    UNION ALL
    SELECT T.FieldA, field2 as value, 'field2' as field
) x;


In the MS documentation, there is a chart to estimate space savings for sparse columns.

According to said chart, if at least 86% of the values are NULL, you will have an overall savings of at least 40% for this column. Each TINYINT value is going to take up 5 bytes versus the normal 1 byte in this scenario.

You can compare this with the compression savings by using sp_estimate_data_compression_savings for the proposed index.

This should give you enough information to make an informed decision about which is the right approach.

Resources:

sp_sizeoptimiser Uses statistics to automatically recommend changing a column to sparse if it meets the threshold to reclaim at least 40% space. Disclaimer: I wrote it!


Using cursor to update if exists and insert if not

I think that error is being generated because of this line

If (Select Login from [dbo].[LoginsForDBUserList])  = @log

Multiple rows are being returned from the left side of the equal sign and is attempting to be compared to the single value @log.

I've made some adjustments to your original query and it works for me.  Give this a try:

--demo setup
DROP TABLE IF EXISTS LoginsForDBUserList
GO
CREATE TABLE LoginsForDBUserList (
    LOGIN VARCHAR(200)
    ,LastLoginTime DATETIME
    )

--Adjustments to your original process
DECLARE @log AS VARCHAR(200)
DECLARE @log_time AS DATETIME

DECLARE LoginsSize CURSOR
FOR
SELECT login_name
    ,login_time
FROM sys.dm_exec_sessions

OPEN LoginsSize

FETCH NEXT
FROM LoginsSize
INTO @log
    ,@log_time

WHILE (@@fetch_status = 0)
BEGIN
    IF EXISTS (
            SELECT LOGIN
            FROM LoginsForDBUserList
            WHERE LOGIN = @log
            )
    BEGIN
        UPDATE LoginsForDBUserList
        SET LastLoginTime = @log_time
        WHERE [login] = @log
    END
    ELSE
    BEGIN
        INSERT INTO LoginsForDBUserList
        SELECT login_name
            ,login_time
        FROM sys.dm_exec_sessions
    END

    FETCH NEXT
    FROM LoginsSize
    INTO @log
        ,@log_time
END

CLOSE LoginsSize

DEALLOCATE LoginsSize

SELECT *
FROM LoginsForDBUserList


First, why did you use these tools instead of simply restoring from a backup? My guess is, you didn't have a backup, or you didn't have one that was recent enough to be usable. Okay, we'll deal with that later.

In the meantime, if I were in your shoes, I would update my resume and sprinkle fairy dust all over it create a new, empty database, and start moving all of your tables, objects, users and code over to the new database (much of that can be done with wizards built right into Management Studio, or with 3rd party tools like Red Gate's SQL Compare / SQL Data Compare). Once that's done, you can drop the Frankenstein database you somehow managed to get running (not sure whether "some tools" deserve credit or not - I don't think they did you any big favors at all). Once that's dropped you can rename the new database, and everything should be back to normal.

While it is possible that you may fix whatever problem your current database has with something like DBCC CHECKDB ... WITH REPAIR_ALLOW_DATA_LOSS, that error message you're getting sounds a lot more heinous than a few bad pages. I would get your data out of those files as quickly as possible.

Next, and I mean immediately next, establish a sensible backup and recovery strategy. This doesn't mean just taking backups, but taking backups and regularly testing that you can restore them. This question isn't about corruption or recovering from a crashed database, but the answers there certainly detail the ins and outs of the different recovery models. Typically, taking a nightly or weekly full backup is not enough - you want differential or log backups in between to minimize the potential for data loss in the event of a server crash. And just taking backups, as I've said, isn't enough - you need to validate that the backups you are taking can be restored on different hardware - you know, should your current hardware completely melt down.

After that, contact the vendor of these "tools" and ask for your money back. This is not the way to recover data, and they have obviously done some irrecoverable things to your database that require even more manual work on your part. It's a shame you didn't ask a question about what to do when the crash originally happened, because surely folks here would have been able to guide you on more reliable approaches to recovery. I find that typically people who use these tools that promise complete recovery of a suspect or corrupt database, without having any idea what you might have done to it, only try them because of a misguided and/or panicked Google search. These vendors are great at SEO, but not so great at actually delivering on their far-fetched promises.


In order of what to try:

1. Enable TCP/IP

Go to: SQL Server Configuration Manager on your SQL Server VM, under SQL Server Network Configuration choose your instance (should be Protocols for SQL2008), enable your preferred protocol (I'd guess TCP/IP) and give it another test.

As to why it's usually disabled:

From MSDN article:

  For security reasons, networking protocols are disabled by default in
  SQL Server Express. Individual protocols can be enabled manually or
  during installation of SQL Server Express.

2. Start SQL Server Browser

Go to: SQL Server Configuration Manager on your SQL Server VM, under SQL Server Services make sure SQL Server Browser is running.

from MSDN article:

  SQL Server Browser is used by some networking protocols to associate named instances with  TCP/IP port numbers. However, if SQL Server Express is installed on the same computer as a pre-SP3 version of MSDE or SQL Server 2000, there might be conflicts. Also, the SQL Server Browser service does not start automatically after installation

3. Check your instance name

If you install SQL 2008 Express (or later versions), the Server instance will be named as SQLExpress, so to connect you will need to specify ServerName and InstanceName, ie:

[VMServer\SQLExpress]

4. Check Connection on SQL Server

In SSMS on Server right click Server, go to Connections and make sure that Allow remote connections to this server is checked

5. Basic network checks

Create a folder on your server, share it and make sure you can see it from the other machine.
Try to telnet to it
netstat -ab

Security
You might run into authentication issues, but that should be a straight forward matter of adding your user onto the SQL Server instance.

Note: If none of the above work for you, and you find the solution, please add your solution to the list above (trying to help future users as well)


I created the table big_table according to your schema

create table big_table
(
    updatetime datetime not null,
    name char(14) not null,
    TheData float,
    primary key(Name,updatetime)
)

I then filled the table with 50,000 rows with this code:

DECLARE @ROWNUM as bigint = 1
WHILE(1=1)
BEGIN
    set @rownum  = @ROWNUM + 1
    insert into big_table values(getdate(),'name' + cast(@rownum as CHAR), cast(@rownum as float))
    if @ROWNUM > 50000
        BREAK;
END

Using SSMS, I then tested both queries and realized that in the first query you are looking for the MAX of TheData and in the second, the MAX of updatetime

I thus modified the first query to also get the MAX of updatetime

set statistics time on -- execution time
set statistics io on -- io stats (how many pages read, temp tables)

-- query 1
SELECT MAX([UpdateTime])
FROM big_table

-- query 2
SELECT MAX([UpdateTime]) AS value
from
   (
    SELECT [UpdateTime]
    FROM big_table
    group by [UpdateTime]
   ) as t

set statistics time off
set statistics io off

Using Statistics Time I get back the number of milliseconds required to parse, compile, and execute each statement

Using Statistics IO I get back information about disk activity

STATISTICS TIME and STATISTICS IO provide useful information. Such as were temporary tables used (indicated by worktable). Also how many logical pages read were read which indicates the number of database pages read from cache.

I then activate Execution plan with CTRL+M (activates show actual execution plan) and then execute with F5.

This will provide a comparison of both queries.

Here is the output of the Messages Tab

-- Query 1

Table 'big_table'. Scan count 1, logical reads 543, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 6 ms.

-- Query 2

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'big_table'. Scan count 1, logical reads 543, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 35 ms.

Both queries result in 543 logical reads, but the second query has an elapsed time of 35ms where as the first has only 6ms. You will also notice that the second query results in the use of temporary tables in tempdb, indicated by the word worktable. Even though all the values for worktable are at 0, work was still done in tempdb.

Then there is the output from the actual Execution plan tab next to the Messages tab

According to the execution plan provided by MSSQL, the second query you provided has a total batch cost of 64% whereas the first only costs 36% of the total batch, so the first query requires less work.

Using SSMS, you can test and compare your queries and find out exactly how MSSQL is parsing your queries and what objects: tables, indexes and/or stats if any are being used to satisfy those queries.

One additional side note to keep in mind when testing is cleaning out the cache before testing, if possible.  This helps to ensure that comparisons are accurate and this is important when thinking about disk activity. I start off with DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE to clear out all the cache. Be careful though not to use these commands on a production server actually in use as you will effectively force the server to read everything from disk into memory.

Here is the relevant documentation.

Clear the plan cache with DBCC FREEPROCCACHE
Clear out everything from the buffer pool with DBCC DROPCLEANBUFFERS

Using these commands may not be possible depending on how your environment is used.

Updated 10/28 12:46pm

Made corrections to the execution plan image and statistics output.


How Do I Execute SSIS Catalog Packages As A Different User With TSQL?

If you create the stored procedure in SSISDB, then add the EXECUTE AS statement within the stored procedure and you can execute the package as the necessary login, which is what it looks like you're aiming to do.  As @sepupic noted, adding EXECUTE AS in the header of the SP only applies to database users.

USE SSISDB;
GO

CREATE PROCEDURE usp_Execute_Packages

AS BEGIN

EXECUTE AS LOGIN = 'Domain\YourLogin'

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'YourPackage.dtsx',
@execution_id=@execution_id OUTPUT, @folder_name=N'Your Folder', @project_name=N'YourProject',
@use32bitruntime=False, @reference_id=Null

Select @execution_id

DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50,
@parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id

END


Username length restriction in different MySQL environments

The maximum length for a user name in MySQL does depend on the version, as stated in the manual:

  MySQL user names can be up to 32 characters long (16 characters before MySQL 5.7.8).


Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias?

The column item_cnt may not exist in the base tables (we believe you, we didn't go and search the linked documents.)

But this part of the code:

FROM
     (SELECT product_id, COUNT (*) item_cnt
      FROM order_items
      GROUP BY product_id) i

provides a derived table. The column exists during the time that the query runs, in the derived table i. The (SELECT product_id, ... item_cnt ...) AS i defines a derived table, it names it i and this derived table has two columns, product_id and item_cnt (and item_cnt is an alias for the expression COUNT(*). There is no count(*) column either in any of the tables!)

So, later in the code, you can use them. The i.product_id is used in the ON condition and the i.item_cnt in the SELECT list.

(And it's a bit confusing that it seems to appear earlier and not later. That's because the SELECT part of a query is logically executed after the FROM part.)

FROM (...) i RIGHT OUTER JOIN products p ON ...

For the query execution, the two tables (aliased i and p) have no difference. It doesn't matter that one is a base table and the other a derived table. They can be used the same way. (and as far as we know, the products may not be a base table either. It can very well be a viewed table, usually referred as a view.)


Why when I use variables on SQL the Database Engine avoid the indexes?

Why do you believe using variables instead of parameters will improve performance?
With local variables, the plan is optimized for unknown values using average density values gleaned from statistics to estimate row counts. With parameters, OTOH, SQL Server uses the actual parameter values provided for the initial compilation to estimate row counts. This generally provides a more optimal plan but at the risk of reusing a cached plan that is suboptimal for different values.
I suggest you use a parameterized query and ensure stats are up-to-date. Add an OPTION (RECOMPILE) hint to the query to avoid parameter sniffing for the range query. This will avoid reusing a suboptimal plan and the compilation overhead is insignificant in the case of a large query executed relatively infrequently by SSIS. The recompile hint should be used judiciously and isn't appropriate for an OLTP workload query executed many times per second.
Note that the actual values are sniffed with either parameters or variables when the recompile hint is specified.


Is using a relational DB for storing votes and (accessed resources) overkill?

In general, clustered-NoSQL databases offer you better horizontal scalability. So, as you scale you can get higher capacity (both memory & compute power) by simply adding new nodes. So, it will be a good insurance for the future.

When it comes to ACID properties, NoSQL databases offer flexibility. So, it boils down to how much ACIDness you want and how much performance you are willing to give up for it. Seems that you do not need very high level of ACID. I am sure you can get good performance in lieu of that.

PS: I do not want to comment specifically about MongoDB. You should do your research and choose the NoSQL db that fits your performance & functional needs.


How do I detect execute permission granted to a role (when no ON clause was used)

GRANT EXECUTE TO [principal] is simply a shortcut for GRANT EXECUTE ON DATABASE::<dbname> TO [principal];

You can check this using the following:

SELECT dp.name
    , perms.class_desc
    , perms.permission_name
    , perms.state_desc
FROM sys.database_permissions perms
    INNER JOIN sys.database_principals dp ON perms.grantee_principal_id = dp.principal_id
WHERE dp.name = 'MyRole'


Is running a query on a view any faster or slower than running the raw query itself?

Creating view will not give you any performance gain but may help you with abstraction of underlying object and management of object level security for your users.
Using Index view can definitely help you by preparing data (aggregate, computation etc) before hand but you need to be careful as explained in detail here.


Since the query optimizer decided a full table scan was best, you might need a new query
SELECT Col1, Col2 FROM MyTable WHERE Col3 = 'Val1'
UNION
SELECT Col1, Col2 FROM MyTable WHERE Col3 = 'Val2'
UNION
SELECT Col1, Col2 FROM MyTable WHERE Col3 = 'Val3'
UNION ...

This should force an indexed lookup per value followed by a merge of the query results
UPDATE 2015-12-04 15:35 EDT
Based on jkavalik's comment, I have a suggestion. Please run this
SELECT COUNT(1)/20 INTO @FivePercent FROM RXNCONSO;
SELECT * FROM
(
    SELECT TTY,COUNT(1) ValueCount
    FROM RXNCONSO GROUP BY TTY
) A WHERE ValueCount > @FivePercent
ORDER BY ValueCount DESC;

This will show which values will likely contribute to a table scan.


Mongodb remove reference of corrupt shard

You need to go "under the hood". Shutdown everything else than config server (mongoS, mongoD on shards).

At Config server, go to config database and there shards -collection. Remove wanted line (shard).

Then you need to remove other traces to that removed shard, from chunks -collection (but way that your shard still works):

db.chunks.update({"shard":"<your removed shard -name>},{$set:{shard:"<name of you other shard>"}},{multi:true})

and from databases -collection:

db.databases.update({"primary":"<your removed shard -name>},{$set:{"primary":"<name of you other shard>"}},{multi:true})

After that, start everything (expect that removed shard) back online. First data bearing nodes (shards) and then last mongoS.

After that, you can start that "removed" shard as single replica set and mongodump data out and import it back to the cluster.. And you get back your "missing" data.


Method 1: Easy way

The Best way that i am using as of now is ola hallengren's  SQL server Maintenance solution for  SQL Server Integrity Check.

Within this solution it gives you an option of storing the output or logging it to a table as per you're convenience:

LogToTable

Log commands to the table dbo.CommandLog.

Please read the complete web link as highlighted in above answer to know how you can make use of it:

Method 2:

To insert the DBCC results into a readable and manageable format, you can use the 'WITH TABLERESULTS' option for DBCC CHECKDB to get an output that can be piped to a table for analysis:

1) Collect the Data:- via creating a table to store the output from created SP as explained in this link.

2) Schedule the job

3) Analyse the data from the table.

Also, would suggest you to read CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully?


When an InnoDB table is full, it has nothing to do with the OS Diskspace.
InnoDB views a table as being full in terms of the storage engine itself.
There are two circumstances under which this can happen
innodb_file_per_table off
I once had a client that had 2TB ibdata1/ibdata2. The client was unable to write any more transactions although ibdata file had 106GB of fragmentation. The solution was to give it more space.
innodb_file_per_table on
When InnoDB tables are stored .ibd files, the table can become full when the undo log gets full
See my old posts going back 10 years on this topic

Feb 22, 2018 : The table is full - MariaDB
Jun 16, 2014 : MySQL Index creation failing on table is full
Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
May 31, 2013 : `ERROR 1114 (HY000) the table ... is full` with innodb_file_per_table set to autoextend
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

IMHO this should fully explain a full InnoDB table


Unfortunately i had to go the route i didn't want to. I had to break mirroring for the database and then create a new broker using:

ALTER DATABASE <db> SET NEW_BROKER WITH ROLLBACK IMMEDIATE

Once this completed i was able to instantly delete the services that were stuck.

My attempts to drop the services without setting the new broker actually completely broke my mirroring anyways so i was kinda forced into this method.


Could I realistically use SQL Server Express as a distributor database for transactional replication?

I was wondering this running an instance of SQL Server Express would suffice for the role of a the Distributor database? Is this remotely feasible?

SQL Server Express cannot serve as a Publisher or Distributor.

SQL Express can only be Subscriber. Refer to : Replication Considerations (SQL Server Express) for more details.

EDIT : To make my answer more meaningful, I am adding more details

Test any scenario that you are going to implement to avoid any surprises !!

  In doing my research it seems the recommended approach would be to have the Distributor on a different server than the Publisher DB?

This is true up-to certain extent only. Its a best practice, but depending on how much data you are replicating and how busy (in terms of activity or transactions) is your publication database and the network latency between publisher-distributor-subscriber, this setup will affect you.

In your scenario, if your database is --

having less or moderate activity then

a local distributor would be sufficient and you can just have a publisher-distributor on one server running Enterprise edition and another subscriber running EE or standard edition (choice is yours). This way you save in terms of hardware and possibly licensing costs.

highly active then

a remote distributor will be a good choice. Suggest you to refer Enhance Transactional Replication Performance

Other thoughts:

Since you are already using Enterprise Edition, why not use Database Snapshots - with caution !!.

From Looking at Database Snapshot Performance

  When using database snapshots, even in SQL Server 2012, there is an overhead associated with the additional writes required for copying data pages to the sparse files for the snapshots. If using database snapshots is a part of your general configuration, I would really be careful about planning the I/O subsystem to meet the workload requirements for concurrent I/O activity to the database snapshot sparse files.

So database mirroring is another option as well and you can create snapshots on the mirrored database and have it as reporting database.

Also, if your business does not want real time data, then a custom solution can be designed that can Extract, Transform and Load the data to another server using SSIS (or any tool of your choice) and that can be used as reporting.


Can set unique constraint in table have data not unique SQL Server?

You can use a filtered unique undex to apply a uniqueness constraint to a filtered set of data. For example this: http://sqlmag.com/blog/whats-good-use-unique-filtered-index

For example you could exclude your non unique rows from the index and make it unique

But you really need to fix your data or it's going to cause you problems in future.


How to improve poor performance with two joined CTE expressions on hierarchical data?

Welcome to the forum, a good first question with included DDL that makes it easy to reproduce the scenario. For future posts, also include some sample data that one can use. You may even use DB<>Fiddle or a similar site. I have added a slightly modified version of your question at Your Fiddle. The site would not let me add more than ~90000 nodes in the tree, but the answer is returned within seconds for that amount of data at least.
I have removed quoted identifiers and changed them slightly. There are several reasons to avoid quotes, they make the code harder to read, and also make it case sensitive. Since SQL is case insensitive I also avoid camel-case. I also changed your index. Enough about that, here is a walkthrough of the solution:
CREATE TABLE Files
(  file_id       INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
,  file_name         TEXT    NOT NULL
,  parent_file_id INTEGER NULL
,  UNIQUE
   (  file_name
   ,  parent_file_id
   )
);

CREATE UNIQUE INDEX files_ix1 ON Files (parent_file_id, file_id, file_name);

First step, get the descendants of a certain node. Similar to what you do in your query:
with rec (depth, file_id, parent_file_id, file_name) as (
    select 0, file_id, parent_file_id, file_name
    from files where file_id = 57
    union all
    select depth+1, f.file_id, r.file_id, f.file_name
    from files f
    join rec r
        on f.parent_file_id = r.file_id
)

Second, get the ancestors of the root from previous question. I have added a depth attribute that makes it easy to identify this node:
), root_path (depth, file_id, parent_file_id, file_name) as (
    select depth, file_id, parent_file_id, file_name
    from rec where depth = 0
    union all
    select r.depth-1, f.file_id, f.parent_file_id, f.file_name
    from root_path r
    join files f
        on r.parent_file_id = f.file_id
)

Your counter-part is likely to blame for performance issues since you traverse the whole tree, which you then have to join.
For the third step, we combine the ancestors with the descendants. I have used a union to get rid of the node that exists in both, you can probably increase efficiency by filtering one of the legs and use union all instead:
tree (depth, file_id, parent_file_id, file_name) as (
    select depth, file_id, parent_file_id, file_name from root_path
    union
    select depth, file_id, parent_file_id, file_name from rec
)

Now tree contains all relevant nodes, so we can recursively construct the transitive closure of the tree:
closure (file_id, parent_file_id, file_name, file_path) as (
    select file_id, parent_file_id, file_name, '/'||file_name
    from tree
    where depth = (select min(depth) from tree)
    union all
    select t.file_id, t.parent_file_id, t.file_name
         , c.file_path || '/' || t.file_name
    from tree t
    join closure c
        on t.parent_file_id = c.file_id
)
select * from closure

If traversing and aggregating information over the tree, is something you do a lot, you may consider some kind of Incremental Evaluation System (IES). The idea is that the recursive part above is already pre-calculated when you need it. There are basically 3 different versions of this that you can google for more info

Nested Sets, though not the inventor, Joe Celko has written a lot of articles and books about it. The basic idea is that you have an upper and lower bound on each node, any node that embraces another node is an ancestor of that node. The main criticism of the method is that small changes of the tree may be expensive since it can cause a renumbering of large parts of the tree.

Materialized path, you basically store the ancestor part in each node and use like predicate to find ancestors/descendants. Choose token separator wisely or you may end up with false results.

Use a separate ancestors table, often referred to as Transitive Closure (TC) table. You can maintain the ancestor's table with triggers on the tree table. I have used this for trees with millions of rows without problems. You need to be aware that the ancestor table typically is 5-10 times the cardinality of the tree table.

EDIT: FWIW I tried the query against my local Db2 11.5.5 with 9 million rows. The exact same query returns 29529 rows in ~20 seconds. If I reduce the result by choosing a start further down the tree I get:
file_id = 57      -> 29529 rows, 20 seconds
file_id = 5703    -> 1106 rows,  0.38 seconds
file_id = 57036   -> 132 rows,   0.15 seconds
file_id = 571036  -> 30 rows,    0.08 seconds


Postgresql: Storing a table index on a separate disk to the data

You can change the tablespace of the index explicitly with alter index command.

CREATE TABLESPACE superfastssdtablespace LOCATION '/path/to/super/fast/ssds';
ALTER INDEX name_of_the_index SET TABLESPACE superfastssdtablespace;

Before you do that you may (also) want to look at partitioning your table or using BRIN indexes depending on the use case.


Multi-dimensional range queries with single column indexes

Answer for SQL Server

The short answer is that the one column index could work depending on your workload & data, but there are different options like included columns in sql server, to store an unordered column.

Two indexes could be used, but in most cases (for sql server) a key lookup operator will be applied to get the other column(s) needed. Accessing two indexes / adding a key lookup operator will be slower due to the added JOIN operator between the two.

An example of when it should go fast is when the optimizer thinks that almost no or no values in the specified range of latitute exist (latitude > 51.4946 AND latitude < 51.5079) while having the one column index, the seek should be pretty fast (keeping in mind that the statistics are correct & up to date). See Example 1 for this part.

Another important part of your example query is that with ranges & indexes, the secondary column will be added as a residual predicate in a simple seek even when both columns are key columns.

See Example 3 for the residual predicate explanation.

Start data

CREATE TABLE [dbo].[restaurants]
(
    [RestaurantId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [Latitude] float NOT NULL,
    [Longitude] float NOT NULL
    );

INSERT INTO restaurants([Latitude],[Longitude])
SELECT
       ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) * RAND() /10 as lat ,
       ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) * RAND() /10 as long
FROM MASTER..spt_values

Example 1 One column index + (unused) key lookup

Single column indexes

CREATE INDEX IX_latitude on dbo.restaurants([Latitude]);
CREATE INDEX IX_longitude on dbo.restaurants([Longitude]);

Due to no values returned by the seek on latitude, no key lookup (to get the longitude column and apply a residual predicate on that column) operation happened:

Example 2 Removing the key lookup

When we include the longitude in the index, no key lookup operator is found (but there is a residual predicate on this column.

CREATE INDEX IX_latitude2 on dbo.restaurants([Latitude]) INCLUDE([Longitude]);

From these examples we can make up that, if SQL server cannot find a first selective filter on latitude or longitude one of these will be picked and the residual predicate / key lookup will be the operation that will hurt us when the data set increases.

Example 3 When adding the double key column indexes

Here the decision of selectivity becomes important. Will the first key column selected be latitude or longitude, which one will always be more selective? If that question cannot be answered you could opt to create two indexes:

CREATE INDEX IX_latitude_Longitude on dbo.restaurants([Latitude],[Longitude]);
CREATE INDEX IX_longitude_Latitude on dbo.restaurants([Longitude],[Latitude]);

But due to the fact that you are working with ranges <>, the secondary column will be added as a residual predicate:

The best indexes for the query

All this shows us, that for SQL Server & your particular query the better indexes would be

CREATE INDEX IX_latitude on dbo.restaurants([Latitude]) INCLUDE([Longitude]);
CREATE INDEX IX_longitude on dbo.restaurants([Longitude]) INCLUDE([Latitude]);

Where the included columns are not ordered since we are not getting around the residual predicate.

Unless we are also executing queries with different filters on Longitude or Latitude , such as = on both columns or = on one of the columns, right indexing could remove the residual predicate for these types of queries.

Some more information on indexing range queries can be found here.


How to fetch records with a number of consecutive associated records?

You can use ROW_NUMBER() or RANK() setting a partition by name and ordering by datetime.

Then fetch these rows with a row number >= 2.

create table books (id int, title text);
insert into books values (1, 'title1'), (2, 'title2');

create table events(id int, title_id int, name text, datetime timestamp);
insert into events values
(1, 1, 'event1', current_timestamp),
(2, 1, 'event1', current_timestamp - interval '30 minutes'),
(3, 2, 'event2', current_timestamp),
(4, 2, 'event2', current_timestamp - interval '5 minutes'),
(5, 2, 'event3', current_timestamp);

with x as
(
    select id, title_id, name, datetime,
           row_number() over (partition by name order by datetime) rn
    from   events
    where  datetime >= current_timestamp - interval '5 minutes'
)
select   max(rn) as id, name
from     x
where    rn >= 2
group by name;

id | name
-: | :-----
 2 | event2

dbfiddle here


(re)creating empty tables in partially restored mysql database fails with “table exists”

I solved this by making a complete backup and then truncating the tables that need to be empty on the slave.
Partial backup didn't work unfortunately.


MySQL database configuration for fastening the select and insert queries

Your problem is not the DB Engine, but the lack of the indices. Indices make possible for your database to find things (records) much faster. You had to make some.

I suggest to read an sql indexing tutorial (google is your friend), and then come back with a new question, if you have yet one.

Extension after you gave your query: in this case there is two problem. First, LIMIT isn't really simple to make faster with indices. But it is not a big problem, because in your query is it visible, what is the cause of the sloooow query. It is the multiple inequality conditions in the WHERE. I didn't give a medal for the liferay programmers for this sh... sometimes-maybe-not-really-useful solution.

The greatest problem is, that such indices can't be make faster by indices easily. On big tables they should be avoided, if we want to develop high-quality software.

The solution - indexing a table for multiple inequality search term - isn't known currently also for me, thus I opened a new question for you, what can be done in MySQL in your problem. Maybe we get some interesting answer and in this case I will be able to extend also this question.


Aggregate discarding values in one column that haven't a match in another column

You can use a correlated subquery:

SELECT label, color
    , (SELECT ARRAY (SELECT box_number FROM items WHERE label = i.label)) AS boxes
FROM   items i
WHERE  box_number = 3;

Or (more modern) with LEFT JOIN LATERAL:

SELECT i.label, i.color, b.boxes
FROM   items i
LEFT   JOIN LATERAL (
   SELECT ARRAY (SELECT box_number FROM items WHERE label = i.label) AS boxes
   ) b ON true
WHERE  box_number = 3;

What is the difference between LATERAL and a subquery in PostgreSQL?

Using an array constructor since that is faster than array_agg() for a single column in the SELECT list.

You could also just use a self-join and aggregate:

SELECT i.label, i.color, array_agg(b.box_number) AS boxes
FROM   items i
JOIN   items b USING (label)
WHERE  i.box_number = 3
GROUP  BY i.label, i.color;

I'd expect that to be a bit slower. Test with EXPLAIN ANALYZE.

SQL Fiddle.

We certainly don't need DISTINCT here, since your data definition says:

  Each box can not contain more than one item with a particular label

.. which is surely enforced with a UNIQUE constraint (or equivalent) on (label, box_number)?
This would also automatically provide the second index to make this query fast - besides the obvious index on (box_number). Details:

Is a composite index also good for queries on the first field?


From the Linux command line

for PROC_TO_KILL in `mysql -h... -u... -p... -A --skip-column-names -e"SHOW PROCESSLIST" | grep -v "system user" | awk '{print $1}'` ; do mysql -h... -u... -p... -A --skip-column-names -e"KILL QUERY ${PROC_TO_KILL}" ; done

You can change the grep option in the header of the for loop to locate a specific user or specific string in the query.

If you have MySQL 5.1 where the processlist is in the INFORMATION_SCHEMA, you can do this to generate the KILL QUERY commands in bulk from within the mysql client:

SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery
FROM information_schema.processlist WHERE user<>'system user'\G

You can do WHERE clauses against the INFO field to look for a specific query, the TIME field against long running queries, or the DB field against a specific database.


How do I show zero when I use count by using self left join?

To answer your question, I did the following:
Edit:
Sorry, I missed the bit about 0 modules failed for students who had never got a grade below 40 in any module - please see revised code below (note to self - read the question!). Luckily, the DDL and table insert DML remain the same.
I reworked your schema slightly as follows (fiddle here):
CREATE TABLE assessment
(
  assessment_id INT,
  mark INTGER,
  student_id TEXT,
  module_id TEXT,
  PRIMARY KEY (assessment_id, student_id, module_id),
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (module_id) REFERENCES module(module_id)
);

Populate it:
INSERT INTO assessment VALUES
(1, 10, 1, 'French1'),
(2, 20, 2, 'French1'),
(3, 50, 3, 'French1'),
(4, 40, 1, 'English2'),
(5, 60, 2, 'English2'),
(6, 90, 3, 'English2'),
(7, 30, 1, 'Maths1'),
(8, 10, 2, 'Maths1'),
(9, 80, 3, 'Maths1');

OR (see fiddle here):
INSERT INTO assessment VALUES
(1, 10, 1, 'French1'),
(1, 20, 2, 'French1'),
(1, 50, 3, 'French1'),
(2, 40, 1, 'English2'),
(2, 60, 2, 'English2'),
(2, 90, 3, 'English2'),
(3, 30, 1, 'Maths1'),
(3, 10, 2, 'Maths1'),
(3, 80, 3, 'Maths1');

I'd say assessment_id should be an INTEGER, but I know SQLite has a weird thing going on with data types!
And then run the query above:
SELECT
  COUNT (a2.module_id) AS "Mod. cnt",
  CASE
    WHEN
      MIN(a1.mark) <= 40
    THEN
      COUNT(a2.module_id)
    ELSE
      0
  END AS "Failed modules",
  ROUND(AVG (a1.mark), 2) AS "Avg grade",
  a2.student_id AS "Student id"
FROM assessment a1
  LEFT JOIN assessment a2
  ON a2.assessment_id = a1.assessment_id
  AND a2.student_id = a1.student_id
  AND a2.module_id = a1.module_id
GROUP BY a2.student_id
HAVING avg(a1.mark) < 40 OR (SELECT MIN(a1.mark) >= 40)
ORDER BY a2.student_id, avg(a1.mark) DESC; -- ALL outer queries should have an ORDER BY!

Result (same for either set of data):
Mod. cnt    Failed modules  Avg grade   Student id
       3                 3      26.67            1
       3                 3         30            2
       3                 0      73.33            3

I suggest that you have a separate module table and that you JOIN using INTEGERs as your PRIMARY KEYs - AFAIK, it's faster than TEXT - the SQL may become a wee bit more complex, but it'll be worth it in the long run - normalisation! You may wish to look at the SQLite COALESCE function for students who haven't taken any assessments?
Using GROUP_CONCAT(), you can do the following (just last two lines of query shown - see fiddle here):
a2.student_id AS "Student id",
GROUP_CONCAT(a2.module_id || ', ' ||  a2.mark,  ', ') AS "Grades"

Result:
Mod. cnt    Failed modules  Avg grade   Student id  Grades
       3                 3      26.67            1  French1, 10, English2, 40, Maths1, 30
       3                 3         30            2  French1, 20, English2, 60, Maths1, 10
       3                 0      73.33            3  French1, 50, English2, 90, Maths1, 80


Best way to speed up a linked server query - 49 columns plus two calculated columns and 170 mill rows

it is in some way unreadable. Why not trying to call a stored procedure and getting the dataset?

select * from
OPENQUERY(link_server_name,'exec your_sp par1,par2)

What's more, instead of using CONVERT what about of using CAST?

CAST is ANSI compliant.

Best,


SQL Server on Ubuntu - An existing connection was forcibly closed by the remote host after reboot

MSSQL expects OpenSSL v1.0 while Ubuntu 18+ uses OpenSSL 1.1, resulting in a OpenSSL version mismatch. The solution is to symlink ssl v1.0 as below:

Stop SQL Server

sudo systemctl stop mssql-server

Open the editor for the service configuration

sudo systemctl edit mssql-server

In the editor, add the following lines to the file and save it:

[Service]
Environment="LD_LIBRARY_PATH=/opt/mssql/lib"

Create symbolic links to OpenSSL 1.0 for SQL Server to use

sudo ln -s /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 /opt/mssql/lib/libssl.so

sudo ln -s /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /opt/mssql/lib/libcrypto.so

Start SQL Server

sudo systemctl start mssql-server


Is MySQL replication okay after writing to slave then making identical write to master?

Since the end result on both master and slave was identical, I doubt you'll have any real issues. Though it's definitely best to avoid this whenever possible, since your next query might not be so benign...


MongoDB admin user cannot access system.views collection

What is this system.views collection?

As per percona blog here MongoDB 3.4 views are non-materialized views, and behind the scenes the engine runs an aggregation. Creating a view requires that we specify a collection or a previous existing view. When a view is the source collection from another view, it allows us to execute a chained aggregation.
To create a view, we should use the db.createView(‘view_name’,’source’,[pipeline]) command, specifying the view name, the view source collection and the aggregation pipeline. This aggregation pipeline, as well as the other parameters, is saved in the system.views collection. This is the only space that the view will use in the system. A new document is saved in the system.views collection for each view created.
Views appear as a collection when we are listing them. The show collections command shows us views as one collection, but such collections are read-only. To drop a view, we simply execute db.collection.drop(). The collection is removed from the system.collections, but the data remains untouched because it only removes the code that generates the view result.

How can I get my admin user authorised to run command on this
collection?

use admin
db.runCommand({ createRole: "readViewCollection",
  privileges: [
    { resource: { db: "", collection: "system.views" }, actions: [ "find"] }],
    roles : []
})

db.grantRolesToUser('<your_user>',['readViewCollection']);

for further your ref here, here and here


I think this document sums it up nicely:
10 Behavior Changes, Deprecated and Desupported Features for Oracle Database (Oracle | Docs)

Review for information about Oracle Database 19c changes, deprecations, and desupports, as well as deprecations and desupports in Oracle Database releases 18c, 12.2, and 12.1.
...
In addition to new features, Oracle Database releases can modify, deprecate or desupport features, and introduce upgrade behavior changes for your database
...

It doesn't even contain any reference to 11.x versions.
I guess your databases contains some features that may have been desupported.
Recommended Solutions
Pre-Upgrade Information Tool
You might be able to pin-point issues within your database, with information provided in the article Using the Pre-Upgrade Information Tool for Oracle Database. However, based on the information provided in your question, I guess this could take a while.
The Pre-Upgrade Information Tool is also mentioned in Oracle's whitepaper: Upgrade and Migrate to Oracle Database 19c

How to choose and use the best available method to upgrade or migrate your current Oracle database to Oracle Database 19c.

Consultant
If you are in the process of upgrading a database, then you might want to consider reaching out to a consultant who can possibly provide you with a clear concise list of features/functions/... which are no longer supported in the different database versions.
Answering Your Questions

Is there an alternative to re-writing all the code in the dump file and running that against the impdp job?

See above suggestions.

Is that even a possibility?

Maybe not, depending on the features that were used in your 11g database. It might work and it might not work.

Are there other options for creating a new dump file from the 11g instance that can work with the impdp into 19c?

You could create the DDL of all objects from the old database and try to create them in a new 19c database. But even then, you might be creating objects that contain desupported features.
Good luck.


Postgres asynchronous procedure execution?

Unfortunately, PostgreSQL does not currently support having such an asynchronous call made to it, where the client is able to disconnect before the original call completes.  This has been discussed a number of times and there is ongoing work to implement a capability similar to this using a "pg_background" worker, but it's not in released versions of PG and it isn't clear when that will change.


SQL Server single query takes longer by end of week

I didn't see the speedup this weekend?!
However, I did find the AccountTeamRollup CTE was a huge drain.
I was able to remove the CTE and replace the referring 'Team' column with the below.  It went from 30sec to 4sec.

,STUFF(
    (SELECT ',' + SUB.UserN AS [text()]
    FROM (
        Select distinct atm.AccountId, u.Name UserN
        From AccountTeamMember atm (nolock)
            INNER JOIN [User] u  (nolock)
                ON atm.UserId = u.Id) SUB
    WHERE SUB.AccountId = acct.Id
    FOR XML PATH('') ), 1, 1, '' ) Team


This will be non-specific to oracle, but in general foreign key indexes are most useful because they speed up JOINs.

If you have a foreign key field, it is extremely likely you will use it as a JOIN key as well - that's the whole point of an FK, to make a direct association between one field in tablea to another field in tableb.

Having this field indexed will speed up any queries that use that field in either the WHERE filter or in the JOIN criteria.


Upgrading to new cPanel Server with mySQL on SSD -- What should I change in my.cnf?

open_files_limit=16000

It makes little sense to set this lower than table_open_cache. See the default value described in https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_open_files_limit

  table_open_cache=25000
  table_cache=60000

You probably don't need to set this so high considering you only have < 900 tables open.

Note that table_cache is an obsolete synonym for table_open_cache. The last one in the my.cnf file takes priority. So you have set your table_open_cache to 60000, overwriting the earlier setting. I suggest deciding on the value you want and use one line to set it, to avoid confusion.

  table_definition_cache=2000

This is fine.

  key_buffer_size=128M

The key buffer is only for MyISAM. If you have no MyISAM tables, the default of 8M is fine.

If you have lots of MyISAM tables, you should convert them to InnoDB. ;-)

  innodb_buffer_pool_size=2000M

Since you upgraded to 64G, this seems low. On the other hand, you have only about 2G of InnoDB data and indexes, which is not unusual for a simple Wordpress site. There's no benefit in increasing the buffer pool any higher.

  innodb_buffer_pool_instances=2

The default number of BP instances is 8 as of MySQL 5.6.6. Since you have 24 cores, you might want to split the BP at least up to 8 instances.

  join_buffer_size=4M

16x the default of 256K seems too high. Ideally, you shouldn't need to rely on the join buffer because joins should be handled by indexes.

In MySQL 5.6, the join buffer is also used for Batched Key Access optimization, but this doesn't kick in very often.

  sort_buffer_size=4M

Also 16x the default of 256K. Useful for avoiding sort merge passes when you do filesorts of large tables. Monitor SHOW GLOBAL STATUS LIKE 'Sort_merge_passes', but I bet you can decrease this.

  read_rnd_buffer_size=4M

Also 16x the default of 256K. Used for MyISAM tables, or for Multi-Range Read optimization for any storage engine. Very rare to need this.

  tmp_table_size=3048M
  max_heap_table_size=3048M

These are probably excessive. I start out small, and increase tmp_table_size if I see a steady increase in SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_table'.

  thread_cache_size=256

Probably unnecessary to have a thread cache at all on your system. The default would be 33. On a modern version of Linux, creating a new thread is quick anyway, so the need for the thread cache is not as important is it is on Solaris, FreeBSD, etc.

  max_connections=250
  table_cache=60000

Note that table_cache is an obsolete synonym for table_open_cache. The last one in the my.cnf file takes priority. So you have set your table_open_cache to 60000, overwriting the earlier setting. I suggest deciding on the value you want and use one line to set it, to avoid confusion.


Does the Amazon RDS backup/snapshot service lock tables?

If there are any MyISAM tables present, as your wording implies, the RDS Documentation instructs you on how to handle it and what to expect

According to the RDS Documentation :

  Automated Backups with Unsupported Storage Engines

  Amazon RDS automated backups and DB Snapshots are currently supported
  for only the InnoDB storage engine. Use of these features with other
  MySQL storage engines, including MyISAM, may lead to unreliable
  behavior while restoring from backups. Specifically, since storage
  engines like MyISAM do not support reliable crash recovery, your
  tables can be corrupted in the event of a crash. For this reason, we
  encourage you to use the InnoDB storage engine.

  If you choose to use MyISAM, you can attempt to manually repair tables
  that become damaged after a crash by using the REPAIR command ((see:
  http://dev.mysql.com/doc/refman/5.5/en/repair-table.html). However, as
  noted in the MySQL documentation, there is a good chance that you will
  not be able to recover all your data.

  If you want to take DB snapshots with MyISAM tables, follow these
  steps:

  Launch Process

  1 Stop all activity to your MyISAM tables (that is, close all
  sessions)

  2 Lock and flush each of your MyISAM tables

  3 Issue a
  CreateDBSnapshot API call, or use the RDSCLI rds-create-db-snapshot
  command. When the snapshot has completed, release the locks and resume
  activity on the MyISAM tables. These steps force MyISAM to flush data
  stored in memory to disk thereby ensuring a clean start when you
  restore from a DB snapshot.

  Finally, if you would like to convert existing MyISAM tables to InnoDB
  tables, you can use alter table command (for example, alter table
  TABLE_NAME engine=innodb;).

Believe me, large MyISAM tables have no place in Amazon RDS. InnoDB is far more accepted. Please, either convert them to InnoDB or live with doing your own locking and possible table crashes/repairs.


How can I get this query to use it's index?

Conditions with OR are harder for the optimizer than conditions with AND only.

Two or more range conditions (>, >=, <, <=, BETWEEN, LIKE 'search%') are harder than conditions with equality only or with only one range.

Your query has both the above difficulties. Noticing that it is equivalent to this rewriting:

WHERE ( languageId = 3 AND
        isActive   = 1 AND
        contentVersionId > 1207494
      )
   OR ( languageId = 3 AND
        isActive   = 1 AND
        modifiedDateTime > '2013-05-05 23:00:00.0'
      )

your best option is to have an index that can be used for the first part (equalities and one of the 2 range conditions) and another index for the second part (equalities and the other range):

ALTER TABLE cmContentVersion
  ADD INDEX active_lang_contentversion_IX
    (isActive, languageId, contentVersionId),
  ADD INDEX active_lang_modified_IX
    (isActive, languageId, modifiedDateTime) ;

The optimizer might then choose to use both indexes and combine them with an Index-Merge access algorithm (the Sort-Union variation).

You can "force" the index-merge to be used by rewriting the query with derived tables.

SELECT cv.contentVersionId,
       cv.stateId,
       cv.modifiedDateTime,
       cv.versionComment,
       cv.isCheckedOut,
       cv.isActive,
       cv.contentId,
       cv.languageId,
       cv.versionModifier
FROM   cmContentVersion AS cv
  JOIN
    (   SELECT contentVersionId
        FROM cmContentVersion
        WHERE languageId = 3 AND
              isActive   = 1 AND
              contentVersionId > 1207494
      UNION
        SELECT contentVersionId
        FROM cmContentVersion
        WHERE languageId = 3 AND
              isActive   = 1 AND
              modifiedDateTime > '2013-05-05 23:00:00.0'
    ) AS cvi
      ON cvi.contentVersionId = cv.contentVersionId
ORDER BY cv.contentVersionId;

However, and since you have the ORDER BY, a combined index might be better for efficiency. Try adding this index:

(isActive, languageId, contentVersionId, modifiedDateTime)

and running the query as:

SELECT cv.contentVersionId,
       ...
FROM   cmContentVersion AS cv
WHERE ( languageId = 3 AND
        isActive   = 1 AND
        contentVersionId <= 1207494 AND
        modifiedDateTime > '2013-05-05 23:00:00.0'
      )
   OR ( languageId = 3 AND
        isActive   = 1 AND
        contentVersionId > 1207494
      )
ORDER BY cv.contentVersionId;

and as:

SELECT cv.contentVersionId,
       ...
FROM   cmContentVersion AS cv
WHERE   languageId = 3 AND
        isActive   = 1 AND
        contentVersionId <= 1207494 AND
        modifiedDateTime > '2013-05-05 23:00:00.0'
UNION ALL
SELECT cv.contentVersionId,
       ...
FROM   cmContentVersion AS cv
WHERE   languageId = 3 AND
        isActive   = 1 AND
        contentVersionId > 1207494
ORDER BY contentVersionId;


Procedure with Insert Data From One Table to Another Table

I think you are just missing the @PersonID in the INSERT statement and there is no need to join to PersonLog. In other words, you should do this:

INSERT INTO
dbo.PersonLog(PersonName, Address, Mobile, FK_PersonId)
SELECT
Person.PersonId, Person.PersonName, Person.Address, Person.Mobile
FROM
dbo.Person
WHERE dbo.Person.PersonID = @PersionID  /*  Add this line */

Also, you will most likely want to surround the INSERT and UPDATE with a BEGIN TRANSACTION and an COMMIT TRANSACTION


Schema to generalise potentially parametrised status

Define a StatusType that will migrate through to a StatusLevel and provide a mechanism for a CHECK constraint.

DML (I'm doing this freehand, there may be syntax errors lurking):
CREATE TABLE StatusType
(
  StatusTypeCd  TEXT     NOT NULL
 ,Description   TEXT     NOT NULL
 ,CONSTRAINT PK_StatusType PRIMARY KEY (StatusTypeCd)
)
;
INSERT INTO StatusType VALUES ('N','Non-parameterized'),('P','Parameterized');

CREATE TABLE Status
(
  StatusCd      TEXT  NOT NULL
 ,StatusTypeCd  TEXT  NOT NULL
 ,Description   TEXT  NOT NULL
 ,CONSTRAINT FK_Status_Classified_By_StatusType FOREIGN KEY (StatusTypeCd) REFERENCES StatusType (StatusTypeCd)
 ,CONSTRAINT PK_Status PRIMARY KEY (StatusCd,StatusTypeCd)
 ,CONSTRAINT AK_Status UNIQUE (StatusCd)
)
;
INSERT INTO Status VALUES ('Foo','N','Foo description'),('Bar','N','Bar description'),('Quux','P','Quux description');

CREATE TABLE StatusLevel
(
  StatusCd      TEXT  NOT NULL
 ,Level         INT   NOT NULL
 ,StatusTypeCd  TEXT  NOT NULL
 ,CONSTRAINT FK_StatusLevel_Level_Of_Status FOREIGN KEY (StatusCd, StatusTypeCd) REFERENCES Status (StatusCd, StatusTypeCd)
 ,CONSTRAINT PR_StatusLevel PRIMARY KEY (StatusCd,Level)
 ,CONSTRAINT CK_StatusLevel_Is_Valid CHECK (StatusTypeCd = 'P' OR Level = 0)
)
;
INSERT INTO StatusLevel VALUES ('Foo','0','N'),('Bar','0','N'),('Quux',10,'P'),('Quux',20,'P'),('Quux',30,'P');

From there you would migrate StatusCd,Level directly to entity or to an optional attribute entity_status:

Notes:

There should be unique constraints on names/descriptions.
Three tables? Yes.  But it keeps you from doing something dumb and forces you to think about what you're doing.
There are no Id columns for StatusType/Status/StatusLevel as they are unnecessary.


Why can't I view the SQL Server database created by a web application?

Please stop using the user instance=true setting. Not only has this feature been deprecated, but what is actually happening here is that Management Studio is firing up a different user instance of SQL Server than the one your application is using, so it is not surprising that you can't see the same thing in both cases.

(Usually, the symptom of this is that someone runs an update from their app, then checks the table in Management Studio, thinking there is a bug but not realizing that the update affected a different copy of the database.)

Create the database on your running SQL Server instance (or attach it there). Then connect to that server the same way, without the User Instance setting, from both SSMS and your app. Lots more information here. Don't create the database every time the application starts - that just sounds horrendously wrong.


Streaming replication version between different minor versions

AFAIK it will work and documentation says, it should work.

  In general, log shipping between servers running different major
  PostgreSQL release levels is not possible. It is the policy of the
  PostgreSQL Global Development Group not to make changes to disk
  formats during minor release upgrades, so it is likely that running
  different minor release levels on primary and standby servers will
  work successfully. However, no formal support for that is offered and
  you are advised to keep primary and standby servers at the same
  release level as much as possible. When updating to a new minor
  release, the safest policy is to update the standby servers first — a
  new minor release is more likely to be able to read WAL files from a
  previous minor release than vice versa.

https://www.postgresql.org/docs/9.4/warm-standby.html

For upgrade process

If you installed PostgreSQL via yum, yum update postgresql* should work. If you need rpms here is the PostgreSQL download link;

http://download.postgresql.org/pub/repos/yum/9.4/redhat/


Should SQL full text search match the value test2 when searching for test?

Yes you can use full text search in this case.

Select * from your_table WHERE CONTAINS(your_column, '"test*"' );

will find test2.

See section

C. Using CONTAINS with prefix_term in msdn


How to delete a column named grave accent in MySQL

OK, I found out the answer.

The correct query should be

alter table tablename drop column ````


Divide data in single column to multiple columns

SELECT Name,
       SUM(CASE Is_Trget WHEN 'Yes' THEN 1 ELSE 0 END) Is_Yes,
       SUM(CASE Is_Trget WHEN 'No' THEN 1 ELSE 0 END) Is_No
FROM source
GROUP BY Name;

fiddle


How to determine how much storage space a column takes in Oracle?

You can use the VSIZE to get the number of bytes for the internal representation of a value.

If you want the total space used by a column, you'll need to sum over all the rows (unless the type used has a fixed length).

The DUMP function will also give you the size and raw representation of a value, but not in a format amenable to easy summation.


Need the name value of the enterprise to be the same as it was when it was registered and not the value it currently has

I'd simply store the register_name (meaning the first value that was taken by the name of the enterprise) in the enterprise table :

identerprise
code
name
register_name

Then I think you shouldn't use varchar(n) to store your text value (see that documentation page)


PostgreSQL - Design database to avoid frequent update on large dataset

The only way to avoid updates to your tables is just not to do them. Given the fact that you're just logging data, I would suggest a daily table, where you store the data for one day. Instead of having 190 M updates/day, you may have 190 M (smaller) inserts/day; and as many updates as there are segments.

 CREATE TABLE main_segments_history
 (
       id_segment integer NOT NULL,
       day date NOT NULL,
       day_slices bigint[],
       CONSTRAINT main_segments_history_pk PRIMARY KEY (id_segment, day)
 ) ;

 CREATE TABLE dayly_segments
 (
      id_segment integer NOT NULL,
      day date NOT NULL,
      id_slice integer NOT NULL,
      slice bigint,
      PRIMARY KEY (id_segment, day, id_slice)
 ) ;

This would simulate the data for one day (and 200 segments; instead of the millions you might have):

 INSERT INTO
     dayly_segments
     (id_segment, day, id_slice, slice)
 SELECT
     id_segment, '2017-01-01', id_slice, (random()*1e7)::bigint
 FROM
     generate_series (1, 200) AS s1(id_segment)
     CROSS JOIN generate_series (1, 20*24) AS s2(id_slice) ;

I assume you have some time with low activity, when you run the vacuuming processes. At that time, you can move data from the dayly_segments table to the main_segments_history, and put all the data as an array. This way, you're never updating main_segments_history.

This is, basically, what you would do:

 -- Move segments from dayly_segments to main_segment_history
 INSERT INTO
     main_segments_history
     (id_segment, day, day_slices)
 SELECT
     id_segment, day,
     (SELECT array_agg(slice)
      FROM (SELECT slice
              FROM dayly_segments s1
             WHERE s1.id_segment = s0.id_segment AND s1.day = s0.day
          ORDER BY id_slice) AS s2)
 FROM
     (SELECT DISTINCT
         id_segment, day
     FROM
         dayly_segments s0
     WHERE
         day = '2017-01-01'
     ) AS s0 ;

 -- Delete them from original
 DELETE FROM
     dayly_segments
 WHERE
     day = '2017-01-01' ;

 -- At this point, you should also...
 VACUUM dayly_segments ;

dbfiddle here

Assumptions:

You never "miss" any $current_value. That is, there are no holes in your arrays.
id_slice can be assigned the result of any increasing sequence. You could aso use, instead of an integer value, just a time value.


I see several non trivial dependencies (9 if I count right!)

How to find them: Since, the relation has few attributes, we can examine each possible dependency separately, taking the respective projection. (The possible dependencies with just 3 attributes are not very few. They are 64 but see below for some ways to make that number smaller.)

Example: X -> Y. Does Y depend on X? Yes.

X  Y
x1 y1
x2 y1

Example: Y -> X. Does X depend on Y? No.

Y  X
y1 x1
y1 x2

Example: XY -> Z. Does Z depend on XY? No.

X  Y  Z
x1 y1 z1
x1 y1 z2
x2 y1 z1

Also we don't have to examine them all (64 possible dependencies).

First, we need to examine only the (37) non-trivial ones. The XY -> X for example is trivial and of course holds, We don't have to examine the (27) trivial dependencies at all.

Second, we can deduce several ones, in both directions:

Once we find that X -> Y holds, we can deduce that XZ -> Y holds, too.
Once we find that XY -> Z does not hold, we can deduce that X -> Z and Y -> Z don't either.
We can also use Armstrong's axions to make further deductions.

I won't go on further details but I think the exercise is good - and tricky.

If you are observant enough, you only need to find a single dependency and then you can prove the rest (8) that hold, using these deductions.

And only find that 2 more dependencies do not hold and then you could prove that the above 9 are the only ones and no other holds.


The column specified in the ORDER BY is always retrieved from its underlying table even the specified column is not specified in the select list. An example to show the behavior,

Execute the T-SQL below and include action execution plan,

USE AdventureWorks;

SELECT Title, LastName
FROM Person.Person
WHERE PersonType = 'EM'
ORDER BY FirstName;

In execution plan, the output list of the clustered index scan operator shows 'Title', 'FirstName', and 'LastName'. Then the result set is sorted in the sort operator before returning the ordered result with the specified select column in select operator.


Updating data from subpartition when the subpartition name is unknown

you can not do in dynamically like this. partition name has to be known and the time when statement is compiled.

But you can use PARTITION FOR clause:

UPDATE TABLE T PARTITION FOR(TO_DATE('31-JAN-2007','dd-MON-yyyy')
...


What is the best way to upload custom-parsed data into AWS Aurora PostgreSQL

I could write to a CSV file and use COPY, but the size would be astronomical

You could write the CSV data stream to a pipe rather than a file:

geneate_csv | psql -c '\copy tablename from stdin'

or

\copy tablename from program 'generate_csv'


There is no "general replacement" - you hid all the "dirty work" here so it's hard to tell if there is even a specific replacement in this case. There are certainly some specific cases where you're processing a set of rows one row at a time, whether using a cursor, or while loop, or any other iterative process, where converting to a set-based process that processes all the rows at once is much better. But there are other things that just have to be done one row at a time, like executing a stored procedure or some dynamic SQL per row, the same query across multiple databases, etc.

Cursor or not, the problems you're alluding to and linking are the same whether you use declare cursor or some other looping struct (see this post), and are irrelevant when the thing you have to do has to be done one row at a time anyway. So if you provide some specific details about what this cursor is doing, you might get some advice about how to remove the cursor (or that you can't), but your search for a magical eliminate-all-cursors approach that you can apply to all scenarios is going to be pretty frustrating for you.

The general advice for new people entering the language, IMHO, should be to always think about what you need to do to a set of rows, as opposed to what you need to do to each row in a set. The difference in the language is subtle, but crucial. If people think about the problem as a set of data instead of a bunch of individual rows, they might be less likely to use a cursor by default. But if they come from different types of programming - where iterative is the best/only way - other than simply teaching them that SQL Server isn't optimized to work that way, I don't know that there's any way to make that obvious or automatic.

Your question still asks for a general replacement, and I still believe there is no such thing.


i need to get the members roles for specific users id

SELECT mem.`first_name`, mr.`role`
FROM member_roles mr
JOIN members mem
ON mr.`member_id`=mem.`id`
WHERE mr.`user_id` = 4

If you want the User info as well:

SELECT users.`name` AS "User Name", mem.`first_name` AS "Member First Name", mr.`role` AS "Member Role"
FROM member_roles mr
JOIN members mem
ON mr.`member_id`=mem.`id`
JOIN users
ON mr.`user_id` = users.`id`
WHERE mr.`user_id` = 4


The file's corrupt.  You either need a backup, or you need to engage with a consultant who can try manually editing the file.  (I'm not one of those consultants, and they're not cheap - you're in five-figure territory.)


multiple connections on MySQL database with no users on httpd

You will need to look into how your connection pool is configured in your PHP web app. Even a fully functional connection pool will often keep many connections to a database open even when they are not in use.  Opening connections to the database can be very expensive for a web app and it makes sense to have some unused ones sitting around.

Most connection pools have settings for things like:

Minimum number of connections to keep open
Maximum number of connections to keep open
Number of connections to open when new connections are needed

There is also the possibility that your web app is not releasing connections back to the connection pool properly.  When you are done with your connections, you need to close them to let the pool know that they are available.   Not doing so is a a kind of resource leak and you will eventually run out of connections.


The audit records are written to the operating system's audit trail defined by the audit_file_dest parameter. You can change the format of to XML by changing the audit_trail parameter but the files will be still kept on the file system.
This due to security reasons since DBAs shouldn't be able to access those files if they are being audited.
You can play around with external tables or create a shell script for analyzing the files but there is no built in mechanism for analyzing the data.


mysqldump can write to /tmp and /var/tmp but nowhere else

[Lots of] Other things can get in the way.

SELinux can cause all sorts of problems, making really basic things fail for no apparent [or announced] reason.

ACL's in the file system can cause confusion, sneakily "tweaking" permissions without you realising it.

Is it possible that the MySQL configuration is different?
These days, a clean install of MySQL might well come with --secure-file-priv turned on.  If that doesn't match where you're trying to put the file, you'll hit another wall.

Regards,   Phill  W.


Scheduled Job Succeeds, Does Nothing; Manual Job Succeeds, Performs Requirements

I have faced the same problem, It is a login problem, please check on your login user name and password.


Using those 3 fields for a primary key is not a good idea (or did I misunderstood your question). There are 3 reasons for that:

The length of the key would be big.
The data would be stored in the Person table too.
Streets can sometimes be renamed or renumbered.

The best is to use a special field like you do for the Person data. As soon as you need a value that is not defined by yourself as PK then you should think twice on using it as a PK.

About your ZIP code. Not all countries have a single ZIP code for a single city. As long as you only need cities in your country then this is not a problem.


All users are having NO Privileges in phpMyAdmin

I have discussed situations like this before in these posts:

May 10, 2013 : In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table
Sep 22, 2013 : How to create root user after running mysql_install_db?

In your case do this

STEP #1

Add these lines just under the [mysqld] group header in my.ini

[mysqld]
skip-grant-tables
skip-networking

STEP 2

Open Windows Command Line as Administrator, and reboot MySQL Service

C:\> net stop mysql
C:\> net start mysql

STEP 3

You can login to MySQL

C:\> mysql <Hit Enter>

STEP 4

From the mysql prompt, run this

SET @PasswordHash = PASSWORD('whateverpasswordiwant');
REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = 'localhost',
              Password = @PasswordHash,
             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',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;
REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = '127.0.0.1',
              Password = @PasswordHash,
             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',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;
exit

STEP 5

Remove skip-grant-tables and skip-networking from my.ini

STEP 6

Restart MySQL

C:\> net stop mysql
C:\> net start mysql

STEP 7

Try logging into MySQL

GIVE IT A TRY !!!


How to use msdb.dbo.backupset and dbatools to gather all of the historical backup size information for all instances in an environment

You can capture this all with Get-DbaDbBackupHistory from dbatools, no loops required (it loops internally).
Get-DbaDbBackupHistory -SqlInstance $instancelist -Type Full -Since (Get-Date).AddMonths(-13) -ExcludeDatabase master,model,msdb |`
 Select-Object SQLInstance,Database,Start,Totalsize,CompressedBackupSize |`
 Sort-Object -Property SQLInstance,Database,Start;

You may want to delve into the Totalsize and CompressedBackupSize properties to get the raw number of MB or GB  without formatting.
Get-DbaDbBackupHistory -SqlInstance $instancelist -Type Full -Since (Get-Date).AddMonths(-13) -ExcludeDatabase master,model,msdb |`
 Select-Object SQLInstance,Database,Start,@{n='TotalSizeMB';e={$_.TotalSize.MegaByte}},@{n='CompressedBackupSizeMB';e={$_.CompressedBackupSize.MegaByte}}|`
 Sort-Object -Property SQLInstance,Database,Start;

You should be able to write this output to a table with Write-DbaDbTableData


Filter and Aggregate a column not in the group by clause MySQL

I don't claim to understand what you are doing, but your logic for p_i is wrong. To get your wanted result:

SELECT
    name, date_hr, AVG(nor), AVG(p_i)
FROM
    (SELECT
        name,
            CASE
                WHEN TIME(date_hour) > '12:00:00' THEN DATE(date_hour) + INTERVAL 1 DAY
                ELSE DATE(date_hour)
            END AS date_hr,
            nor,
            validation,
            CASE
                WHEN validation = 1 AND p_i > 0 THEN NULL
                ELSE p_i
            END AS p_i
    FROM
        report) z1
GROUP BY name , date_hr;

name | date_hr    | AVG(nor) | AVG(p_i)
:--- | :--------- | -------: | -------:
SYN  | 2018-02-03 |     18.5 |     null
SYN  | 2018-02-04 |       10 |        3
SYN  | 2020-04-07 |     14.5 |      3.5
YRT  | 2020-04-06 |      136 |     null

db<>fiddle here


MySQL how to make a backup while the server is not running

You can take physical backup of mysql data directory.

Step-1 Take backup of mysql data directory.

cd /datadir/
cp -r /datadir/ /backup_path/

Step-2 Once you re-install it, just verify data are whether data are purged or not ?

Step-3 If data are not purged from datadir, Simple you need to start mysql service & ready go.

Step-4 Otherwise you are suppose to copy backup datadir from backup path.

cd /datadir/
cp -r  /backup_path/ /datadir/


Make to_tsquery return OR of lexemes instead of AND

I don't know how to do that exclusively with dict_xsyn, other than by manipulating the output as text and the converting back, but I don't think you want to do that anyway.  I think it would be a performance nightmare.

You probably want to reverse this so that all nicknames get canonicalized down to a single variant, rather than exploded out to all variants.

alter TEXT SEARCH DICTIONARY names_xsyn (KEEPORIG=true, MATCHORIG=true,
  KEEPSYNONYMS=false, MATCHSYNONYMS=true);

But I have no idea why that is not the default, so perhaps I just fundamentally misunderstand the point of that extension.

Both the tsquery and the tsvector would be canonicalized the same way, so a query with 'bobbie' would match a document with 'robert' and vice versa.

But perhaps I just don't understand your end goal.  You can do what you are directly asking for with ts_rewrite, but it is not done by building on top of dict_xsyn.  Is the example involving supernovae & crab not just what you want?


Equivalent of UTF8_UNICODE_CI collation in PostgreSQL

In Postgres, you would typically solve this differently.

For starters, just use ILIKE for case insensitive matching. The manual:

  The key word ILIKE can be used instead of LIKE to make the match
  case-insensitive according to the active locale. This is not in the
  SQL standard but is a PostgreSQL extension.

WHERE name ILIKE '%lala%'

You do not need a different collation for this. Trigram indexes support both case sensitive and insensitive pattern matching:

LOWER LIKE vs iLIKE
How is LIKE implemented?

Or use the more portable lower(col) LIKE

WHERE lower(name) LIKE '%lala%'  -- pattern must be lower case, too.

Generic Ruby solution for SQLite3 “LIKE” or PostgreSQL “ILIKE”?

Or you could use the special data type citext provided by the additional module citext:

Index on column with data type citext not used

If you insist on a special collation, you can create your own:

Add a new collation to a Postgres database

I would not do that.

If you want to take string "normalization" one step further (remove all diacritic signs), look to unaccent():

Does PostgreSQL support “accent insensitive” collations?

You can combine both, instructions in the linked answer. More here:

PostgreSQL accent + case insensitive search

Overview:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I would just use ILIKE and support it with a trigram index for big tables. Preferably use the latest version Postgres 9.6. It has several improvements for trigram GIN indexes.


Use a loop to delete and make frequent transaction log backups while doing so. That way people can use the table and you will keep the transaction log at a responsible size

DECLARE @ROWCOUNT int = 1
  WHILE @ROWCOUNT > 0
BEGIN
 delete  top (10000)
  from [SomeTable]
  where [Something]
 set @rowcount = @@rowcount;
END;


Database Roles Security: In SQL Server architecture where does permissions are evaluated in RE or SE or Access methods

When your user without DML permissions submit the query, it's compiled. So nither parser nor optimizer controls permissions. The compiled plan (CP) can be seen using smth like this:

select *
from sys.dm_exec_cached_plans
     cross apply sys.dm_exec_sql_text(plan_handle) t
     cross apply sys.dm_exec_query_plan(plan_handle)
where t.text like '%your query text%'

But it's just a compiled plan, not executable plan.
Make note of plan_handle and paste it into

sys.dm_exec_cached_plan_dependent_objects()

It will show nothing, no exacutable plan is built.
I have no idea what name has a piece of code responsible for controlling permissions but the exact moment when the permissions are checked is when we passing from copiled plan (CP) to execution plan (MXC)


How to create INOUT assignment cast on Amazon RDS PostgreSQL? If not possible, then why?

On AWS RDS you must first alter type <type> owner to <user> before create cast since the default owner of all types is the rdsadmin user.

To run alter type you must do so with the master user system account you created for RDS or any other user that you have given the rds_superuser role. From the docs:

  When you create a DB instance, the master user system account that you
  create is assigned to the rds_superuserrole.
  The rds_superuser role is similar to the PostgreSQL superuser role
  (customarily named postgres in local instances) but with some
  restrictions.