MySQL User Authentication is a rather lengthy process to explain. I refer you to my 3.5-year-old answer to MySQL error: Access denied for user 'a'@'localhost' (using password: YES) so you can see the steps a user takes to authenticate.
What the PROXY grant does is allow one user to masquerade as another user and bypass MySQL's normal but lengthy user authentication protocol.
Such proxy grants could be a security hole if one knew of this and started taking advantage of it. The quickest way to deactivate this would be to run
TRUNCATE TABLE mysql.proxies_priv;
FLUSH PRIVILEGES;

This will wipe out the current proxy privileges.
Afterwards, if you want to restrict proxy privileges, go to the OS and do this:
cd /var/lib/mysql/mysql
chmod -w proxies_priv.*

This will prevent new proxy privileges from being created.
GIVE IT A TRY !!!


What does the Union sign in EER diagrams mean?

It means that the publication can be of type chapter, article, or book. That makes publication different from overlap and disjoint.

When it comes to disjoint or overlap of entities, the topic becomes about a super class and a subclass.

This, however, is more like a kind of category.

For example, if an organization has an owner, and if the owner can be a person, a company, or a bank, then it is a union of entities, if they are separate "tables".


Counting repeated occurences for a group of fields

SELECT person, max(ct) AS max_ct
FROM  (
   SELECT person, count(*) AS ct
   FROM  (
      SELECT person, result
           , row_number() OVER (PARTITION BY person ORDER BY time)
           - row_number() OVER (PARTITION BY person, result ORDER BY time) AS grp
      FROM   tablex
      ) sub1
   GROUP BY person, result, grp
   ) sub2
GROUP  BY person
ORDER  BY max(ct) DESC, person;

SQL Fiddle.

Result exactly as desired.

After forming groups (grp) of consecutive pairs (person, result), the count has to be per pair  and group (person, result, grp), not just per person and group (person, grp). There can (and will) be one group with the same group number per person and result, those have to be counted separately.

Also, it's not efficient to use CTEs here. In Postgres, performance is generally superior for subqueries, since CTEs are optimization fences that always materialize the result. Use CTEs only where they are actually necessary.


This is a very broad question as the list you gave of what you want to practice would need multiple databases to learn dataguard and RAC.  Why do't you try Oracle's free trial of the cloud based databases?  You can make mistakes there and it will be easy to learn on the newest versions and when you have some more knowledge you can deploy Oracle Virtual Box at home for a test that will last longer.

Running an Oracle database on Virtual Box on a home PC could easily use four to six GB of memory if you are doing any thing intensive.  Storage is cheap these days so allocating at least 100Gb for the database files gets you started.

A typical enterprise disk setup is to mount separate drives

/u01 for the oracle product ~100 Gb
/u02 for datafiles ~100 Gb (but if you import data you could need up to 500 Gb)
/u03 for indexes ~100 Gb
/u04 for archive logs ~100 Gb and so on

This is probably overkill for a learning situation but, at the least, separate the oracle install from the data for easier management. Suggested disk allocations get you started but if you actually use it to store data you should ensure that you can extend the virtual disks.  In other words allocate 1 Tb of storage to the virtual machine and then you can extend the storage as needed.


Failover from Primary to Asyn Secondary Replica

No. It is not a problem.

Just to it in a low intensive moment. And waith sync is complete. You can check it via interface.

Otherwise you can use this query (checked in SQL 2016):

SELECT ag.NAME
    ,drs.database_id
    ,drs.group_id
    ,drs.replica_id
    ,drs.synchronization_state_desc
    ,drs.end_of_log_lsn
FROM sys.dm_hadr_database_replica_states drs
    ,sys.availability_groups ag
WHERE drs.group_id = ag.group_id;

synchronization_state_desc must be: SYNCHRONIZED.

Then you can switch.


ALTER TABLE SET LOGGED locked by autovacuum

You can terminate the autovacuum (32090).  You should not make a habit of doing that, but in this situation it should be OK.

You might want to first run:

select age(relfrozenxid) from pg_class where relname='pg_toast_315873283';

And make sure the reported value is well below 2 billion.

You also might want to check that pg_toast_315873283 is the toast table for sensors.metrics.  I don't see why it would block in the first place were that not the case, but it doesn't hurt to verify those assumptions.

On the other hand, you could just be patient and let the autovacuum finish.  The vacuum needs to happen, and it will generate less WAL traffic if you get the vacuum done while it is still unlogged.

On the third hand, you might want to start a manual VACUUM FREEZE sensors.metrics in one session, and then go cancel both the ALTER TABLE and the autovac, and then do the ALTER TABLE once the vacuum has finished.  This will get done faster than the autovac (the default setting for manual vacuums do not use IO throttling), and again does work that needs to be done eventually and is probably better done while table is still set to UNLOGGED.


Oracle Discoverer like app native for SQL Server?

SQL Server 2005+ comes with an ad-hoc reporting tool called Report Builder, which does much the same sort of thing as Discoverer.  This will probably get you what you need.

SQL Server It also comes with a reporting tool called Reporting Services (SSRS), a SQL based tool that does the same sort of thing as Crystal Reports or Oracle Reports.  SSRS will consume a variety of data sources, including Oracle.  One key point to note about SSRS is that you can use it as a medium for publishing reports built with Report Builder.


Is it possible to log ship from on-premise SQL server to Azure SQL Managed Instance?

No, log shipping to Managed Instance is not possible; it only supports the restore of full backups. See this thread:

Lesson Learned #82: Azure SQL Database Managed Instance supports only COPY_ONLY restoring a database backup (was here before)

And the limited syntax diagram for RESTORE in the documentation for Managed Instance:

RESTORE Statements (Transact-SQL)

The docs imply that RESTORE ... FROM URL will implicitly add WITH RECOVERY and so there will be no opportunity for logs to be applied after the initial restore. So your downtime will be >= the time it takes to take a COPY_ONLY full backup, put it somewhere MI can access, restore it, and re-point your app(s). You might also want to read through this tutorial, for a different approach.


If we restart the database server these execution plans in memory will be lost: is it possible that the next time the queries run, Sql Server regenerate some execution plans worst than the previous ones?

Absolutely, it all depends on compile-time differences.  For instance, if a plan is compiled and cached with different values than before the plan cached was wiped (for whatever reason:  Service restart, machine restart, free proc cache, etc.), and those different values give a sub-optimal plan for the typical workload then yes that could be a worse plan for the typical workload than the previous ones.

One way around this is to utilize the OPTIMIZE FOR query hint.  As per BOL:

  Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.

Another method is to use Plan Guides.  Again, as per BOL:

  Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server 2012. Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them.

Though I will caution you, plan guides can potentially be complicated and I wouldn't personally use them as a first-option to getting the optimal plan(s) you're looking for.  But, nevertheless, they are an option.


How to Configure PostgreSQL Hotstandby Automatic Failover

Failover - automatic or otherwise - isn't provided directly by PostgreSQL.

You'll need external tools like repmgr.

The newly released repmgr 2.0 supports autofailover. However, I recommend thinking very hard about whether you should actually use it. Automated manually-triggered failover is usually a LOT safer.

If you do choose to use autofailover, you should make sure to force autofailovers as part of normal operation to make sure it's really working in your environment. Otherwise it's just a false sense of security, and when something actually breaks it'll probably bring the whole system down in a mess.

You should test any failover setup regularly, of course, but at least with manually triggered failover you're aware it's going to happen and ready to step in if there are problems.

It's not just a matter of promoting the server and rebuilding the old master as a replica, either. You've also got to deal with the need to change client connections over to the new server (using something like PgBouncer, PgPool-II, or HAProxy). You also need to reliably terminate the old server so that you don't get divergence, where both servers think they're masters and some clients are writing to each of them.

There's a fair bit of info on all this in the PostgreSQL documentation and on the wiki, so I suggest you start there.


Checking for ApplicationName in a transaction through a Before Trigger

Yes, you can use the APP_NAME() function in SQL.

This is set in the connection string though so it isn't reliable

Why not limit users? If a user has permissions, they can run msqry32 which is on any PC with Office installed, set a connection string using ODBC and make it look like what they want...


Use ##import as your table. Global temp table will last for the life of the connection. The #import tables goes out of scope after the exec


DataGrip shows a time substantially slower than EXPLAIN ANALYZE

EXPLAIN ANALYZE is showing you the planning and execution time.
DataGrip is showing you the completion time. This may include,

Network transit
Drawing in the GUI, etc.

Just think, if you have a one-row table with one bytea column that contains 2GB, transferring it across the network or even using TCP/IP will be a substantial transit compared to the planning and execution of the query.

  [2017-02-10 21:38:30] 134 rows retrieved starting from 1 in 135ms (execution: 127ms, fetching: 8ms)

That's 134 rows you didn't have to retrieve using EXPLAIN ANALYZE.


Binding errors with dependent stored procedures

Thanks SQL Kiwi, you answered my question. The temp tables are all #temp tables. After reading your comments I thought why not try rename all the temp tables and make each a unique name.

I went thought each procedure and gave each #temp table a unique name per stored procedure, then I ran the command

DBCC FREEPROCCACHE;

Then then tried running my main procedure again and all seems to be working again. SQL must be getting confused with the temp table names being the same in the all the stored procedures. What I cannot explain is why from the SSRS server the report executes and from SSMS I get errors (On our Live Environment Only)

I unfortunately have taken this system over from an outsourced development house and I would not have done things they way they did it. I have been slowly replacing some of the temp tables with CTE's & re factoring out some of the un necessary joins, in most cases this gives us better performance.


How To Create A New bash_profile And Connect New Database To It In Oracle

First, remove all that junk from .bash_profile. Use oraenv. Example:

$ grep u01 /etc/oratab
MIN112:/u01/app/oracle/product/11.2.0/dbhome_1:N
MIN121:/u01/app/oracle/product/12.1.0/dbhome_1:N

$ . oraenv
ORACLE_SID = [oracle] ? MIN112
The Oracle base has been set to /u01/app/oracle
$ env | grep ORA | sort
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=MIN112

$ . oraenv
ORACLE_SID = [MIN112] ? MIN121
The Oracle base remains unchanged with value /u01/app/oracle
$ env | grep ORA | sort
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
ORACLE_SID=MIN121

Second, forget /u01/app/oracle/product/newdb.
Just run /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbca and create your other database and provide the necesarry path for datafiles. You do not need another ORACLE_HOME for another database.

When you have multiple databases, just put them in /etc/oratab (dbca will do this automatically in 11.2), and use oraenv or just set ORACLE_SID in case of the same ORACLE_HOME for switching between them.


How to reproduce "Could not continue scan with NOLOCK due to data movement"

Since one potential 'band aid' to the NOLOCK issues is to stop using NOLOCK and begin using READ_COMMITTED_SNAPSHOT isolation, I want to point you to the blog post at http://www.brentozar.com by Kendra Little: Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide.

Kendra provides a fair amount of detail on benefits and risks with using the READ_COMMITTED_SNAPSHOT isolation level.

This isolation level becomes the default isolation level for the database code.
You have to have only one user in the database in order to make the change to the READ_COMMITTED_SNAPSHOT isolation level.
Even if you use READ_COMMITTED_SNAPSHOT isolation you will still need to remove the NOLOCK hints since they override the default.
Some of your code may well have problems that need curing.

Some years ago we implemented READ_COMMITTED_SNAPSHOT isolation on a database that was severely suffering from blocking.  But once we changed the isolation level we began getting deadlocks in a couple of critical areas.

Why did this happen?  Because the previous isolation level caused heavy blocking, the code could "never" reach the point of deadlocking.  However, with
READ_COMMITTED_SNAPSHOT isolation, the queries could keep moving forward. However, some percentage of the no-longer waiting transactions began deadlocking.

Fortunately our case was resolved quickly by determining the deadlock points and adjusting the indexes on a couple of tables to have a more rational column order.  This greatly reduced our locking problems.


This can be specified for every write operation:

From the docs about Verify Write Operations to Replica Sets:

  For a replica set, the default write concern requests acknowledgement only from the primary. You can, however, override this default write concern, such as to confirm write operations on a specified number of the replica set members.

  [...]

  To override the default write concern, specify a write concern with each write operation. For example, the following method includes a write concern that specifies that the method return only after the write propagates to the primary and at least one secondary or the method times out after 5 seconds.

db.products.insert(
   { item: "envelopes", qty : 100, type: "Clasp" },
   { writeConcern: { w: 2, wtimeout: 5000 } }
)


Unfortunately, there is no provision in SQL syntax to say "all columns except this one column". You can achieve your goal by spelling out the remaining list of columns in a row-type expression:
SELECT a.id, a.name
     , json_agg((b.col1, b.col2, b.col3)) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;
That's short for the more explicit form: ROW(b.col1, b.col2, b.col3).
However, columns names are not preserved in row-type expressions. You get generic key names in the JSON object this way. I see 3 options to preserve original column names:
1. Cast to registered type
Cast to a well-known (registered) row type. A type is registered for every existing table or view or with an explicit CREATE TYPE statement. You might use a temporary table for an ad-hoc solution (lives for the duration of the session):
CREATE TEMP TABLE x (col1 int, col2 text, col3 date);  -- use adequate data types!

SELECT a.id, a.name
     , json_agg((b.col1, b.col2, b.col3)::x) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;
2. Use a subselect
Use a subselect to construct a derived table and reference the table as a whole. This also carries column names. It is more verbose, but you don't need a registered type:
SELECT a.id, a.name
     , json_agg((SELECT x FROM (SELECT b.col1, b.col2, b.col3) AS x)) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;
3. json_build_object() in Postgres 9.4 or later
SELECT a.id, a.name
     , json_agg(json_build_object('col1', b.col1, 'col2', b.col2, 'col3', b.col3)) AS item
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;
Related:

Return as array of JSON objects in SQL (Postgres)

Similar for jsonb with the respective functions jsonb_agg() and jsonb_build_object().
For Postgres 9.5 or later also see a_horse's answer with a new shorter syntax variant: Postgres added the minus operator - for jsonb to say "all keys except this one key".
Since Postgres 10 "except several keys" is implemented with the same operator taking text[] as 2nd operand - like mlt commented.


Same statement yields NULL when in a stored function, and non-NULL valid result otherwise

Your problem is due to the same variable name is used for the parameter and local variable. i.e, wynik used as INT in the local variable as well as DOUBLE in the parameter of the nowyRanking function.

In this case changing the parameter name will solve the issue. In the below code I renamed the parameter name wynik DOUBLE to wynikDouble DOUBLE and handled the same in the query to solve the problem.

Answer for How should I fix my stored function?

CREATE FUNCTION nowyRanking(mój INT, przeciwnika INT, wynikDouble DOUBLE)
RETURNS INT DETERMINISTIC
BEGIN
  DECLARE wynik INT;
  SET wynik =
    mój + ROUND(3.0E1 * (wynikDouble - spodziewanyWynik(mój, przeciwnika)));
  RETURN wynik;
END

Working fiddle: http://sqlfiddle.com/#!9/76b5b/3

Answer for Why on Earth does this happen?

Update: In your actual code you declared the wynik as INT and used in the SET wynik = mój + ROUND(3.0E1 * (wynik - spodziewanyWynik(mój, przeciwnika))); block, here the wynik value is NULL only.

So the query is execute as SELECT 1500 + ROUND(3.0E1 * (NULL - spodziewanyWynik(1500, 1500))); that's why you are getting NULL as result.


Another Dynamically alias column question

If you can use #temp tables instead of table variables, this becomes dead easy.

CREATE TABLE #Table1
(
 Table1ID Int Primary Key,
 ColumnName varchar(max)
);

INSERT INTO #Table1(Table1ID,ColumnName)
  VALUES(1000,'A'),(2000,'B');

CREATE TABLE #Table2
(
  Table2ID Int Identity Primary Key,
  Table1ID Int,
  FieldData varchar(max)
);

INSERT INTO #Table2(Table1ID,FieldData)
  VALUES(1000,'A DATA'),(2000,'B DATA');

Now, I can do this:

DECLARE @sql NVARCHAR(MAX), @Table1ID INT = 2000;

SELECT @sql = N'SELECT FieldData AS ' + QUOTENAME(ColumnName)
  + N' FROM #Table2 WHERE Table1ID = @Table1ID;'
FROM #Table1 WHERE Table1ID = @Table1ID;

EXEC sys.sp_executesql @sql, N'@Table1ID INT', @Table1ID;

Results:

B
------
B DATA

If you need to use table variables for some reason (as explained in an excellent answer by Martin Smith, they're actually usually less desirable than #temp tables, or indifferent even in ways where most people make assumptions that table variables are better), then you can use a really fugly hack based on this answer (also by Martin Smith, coincidentally).

First, you need to create a table type in your database (and creating a copy of this in model may be a good idea as well, because in some scenarios it may need to also exist in tempdb).

CREATE TYPE dbo.Table2 AS TABLE
(
  Table2ID Int Identity Primary Key,
  Table1ID Int,
  FieldData varchar(max)
);

Now, your code would be:

DECLARE @Table1 AS TABLE
(
  Table1ID Int Primary Key,
  ColumnName varchar(max)
);

DECLARE @Table2 AS dbo.Table2;

INSERT INTO @Table1(Table1ID,ColumnName)
 VALUES(1000,'A'),(2000,'B');
INSERT INTO @Table2(Table1ID,FieldData)
 VALUES(1000,'A DATA'),(2000,'B DATA');

DECLARE @sql NVARCHAR(MAX), @Table1ID INT = 2000;

SELECT @sql = N'SELECT FieldData AS ' + QUOTENAME(ColumnName)
  + N' FROM @Table2 WHERE Table1ID = @Table1ID;'
FROM @Table1 WHERE Table1ID = @Table1ID;

EXEC sys.sp_executesql @sql,
  N'@Table2 dbo.Table2 READONLY, @Table1ID INT',
  @Table2 = @Table2, @Table1ID = @Table1ID;

Results, again:

B
------
B DATA

From a simplicity (and portability / forward compatibility) perspective, I would argue for the former solution where you just use #temp tables instead of table variables and a TVP.


How do I connect my MS Access database to the LAN?

There is nothing magical about this.  Just put the Access database file in a file share on your LAN and let people access it from whatever computer they are using.

If you will need to be sure to open the database in shared mode.  Go to the options, select client settings, advanced, and set the default open mode to "shared".  (Depending on your version of access, the way to get to this setting may vary.)

You can do something more elaborate, like segregating your data and your GUI and joining the front end databases to the back end with linked tables, but that may well be overkill.


Depending on the hardware you're moving from/to (or not moving), you could just take the data & config files, and copy them across to the new system.  If the new system is set up the same for storage/memory, this might just work, but may need some tweaking of config to make it happen.

If you're changing to hardware where you can't just copy the files, then your only realistic option is a mysqldump export.


Actually both configurations are possible.

Remarks to your ideas

For configuration 1, just set up the first Master->Slave replication. See that you should activate --log-slave-updates on the slave so that the slave creates an appropriate binary log for the propagation of the next slave in the chain. Then set up the new slave with the previous slave as a master. This should be straight forward.

For configuration 2 you can use --replicate-do-table (or --replicate-wild-do-table) or --replicate-ignore-table options on the slave. This alters the behaviour of the slave executing received binary logs. Another way is to use --binlog-do-db or --binlog-ignore-db on the master. This alters the logged binary statements on the master directly. There are some pitfalls thought when using. You might want to read the manual yourself (basically it is unsafe to use the --replicate...--db or --binlog...--db options, because they may have unintended side effects).

These problems are all mentioned in this blog post of Percona too. Since there are no --binlog-do-table options, one does not have the chance to safely restrict the written statements on the master. In this setting the whole binary log would need to be transferred to all n slaves.

Another solution

They do propose a different solution for cases when there should become n slaves propagated, but only with a partial binary log. Setting up a replication chain like you did in configuration 1, but setting the storage engine to all tables on the first slave to BLACKHOLE. BLACKHOLE is just /dev/null, so all written data will be immediately lost. This allows to replicate everything from the first master to the first slave. Then the replication rules from configuration 2 (--replicate-ignore-table) are applied on the slave. All other clients are slaves from this "filter-slave". So the first slave is just responsible for filtering the binary log and propagating this log to (maybe) a lot of slaves. Thus the full binary log must be transferred only once, and not to all of the n slaves.

Maybe the last configuration is something you want to take into account.


One way is to add a memberNo column and restrict to 5 possible values, per account:

CREATE TABLE members
( -- ...
  accountID NUMBER REFERENCES accounts (accountID),
  memberNo NUMBER NOT NULL,
  -- ...,
  UNIQUE (accountID, memberNo),
  CHECK (memberNo IN (1,2,3,4,5))
) ;

This imposes of course the additional burden of providing and keeping values for this column when inserting into the table (with extra complications for deletions. If for example you delete the member with memberNo=2 for an account, should the 3 higher numbers be decreased or should the complications be dealt in future inserts.)


Do I need to set "ConnectionReset" parameter in the connection string when using isolation levels occasionally?

Probably the following allows you to use any isolation mode for your application:

BEGIN;
SELECT ... FOR UPDATE;   -- necessary if about to change a row
...
UPDATE ...;
UPDATE ...;
COMMIT;


Looping through MySQL Query progressively gets slower

SOLVED:

Found one statement involving a table which was missing a crucial INDEX, and it was bogging down there.

Adding the INDEX and each loop is now pretty consistent - no progressive lag.

Re query "can't you do all the work in a single JOIN between the two tables, rather than 1+4000 queries" - there's a heap more going on in each loop (sending messages etc) so it's more than just a MySQL Statement.

Cheers


I have had the same issue and I followed these steps to resolve that.

EXEC msdb.dbo.sysmail_help_queue_sp
and check if length > 0 then DELETE QUEUE items.
Use the following query to delete queue items:

DECLARE @GETDATE datetime
SET @GETDATE = GETDATE()
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp
@sent_before = @GETDATE;

GO

EXEC msdb.dbo.sysmail_start_sp, start the process and check.


I guess you can contact MariaDB to find out how/when/where you can take an exam. They state:

"This certification exam, proctored and administered by MariaDB"

on:

https://mariadb.com/resources/datasheets/certification-mariadb/

There is also preparation material to download there.


Is there any need to persist a (precise) computed search column that I've indexed?

Is there any need to persist these columns?

Generally, no, though there are a number of potential issues that will require validation with your particular workload and client applications.

The major requirements (including SET option requirements for all clients) are listed in the documentation at Indexes on Computed Columns. You may also, for example, be required to add the PERSISTED attribute for a number of reasons e.g. to add a CHECK or NOT NULL constraint.

The reason I have emphasised the need to test above is that SQL Server will sometimes choose not to use an index on a computed column (even if persisted!) in a range of scenarios that are tricky to enumerate in advance - and where using the index is "obviously" the best strategy. For some examples and background, please see my article Properly Persisted Computed Columns.

The upshot is that each query needs to be tested to ensure that SQL Server reliably chooses the index where that strategy is important for performance.

You can encourage selection of the index access method by making it covering for the target queries (so the index contains all columns needed by the query), but even this does not come with a guarantee. Using an index hint does come with a guarantee, but has risks including throwing an error if the index is unavailable.

There is also a limit to which a b-tree index can assist for leading-wildcard searches as given in the question:

WHERE foo.search_license_plate LIKE '%bar%';

At best, this will result in a full scan of the index on the computed column. You may also be interested in Trigram Wildcard String Search in SQL Server as an alternative.


How do I manage column order when using PL/SQL's %ROWTYPE records?

If the definition of toptab will potentially be different in different environments (which is, of course, a separate issue that needs addressing), your options would generally be to either create an object type that does have a defined order and BULK COLLECT into that or to define a different collection for each column that you specify as the targets of your BULK COLLECT.

For the object approach, you'd define an object

CREATE TYPE toptab_like_object
    AS OBJECT (
  col1 number,
  col2 number,
  ...
  colN number
 );

and then use that object rather than the %ROWTYPE in your code

DECLARE
  TYPE t_toptab_list IS TABLE OF toptab_like_object;
  l_data t_toptab_list;
BEGIN
  SELECT toptab_like_object( ... ) -- many columns, with some manipulation, etc.
  BULK COLLECT INTO l_data
  FROM a,b,c,d
  WHERE ... -- join and filter, etc.
END;

You could also define a different collection for each column (you could probably define a much smaller number of collection types)

DECLARE
  TYPE t_col1_list IS TABLE OF toptab.col1%TYPE;
  TYPE t_col2_list IS TABLE OF toptab.col2%TYPE;
  ...
  TYPE t_colN_list IS TABLE OF toptab.colN%TYPE;

  l_col1 t_col1_list;
  l_col2 t_col2_list;
  ...
  l_colN t_colN_list;
BEGIN
  SELECT ... -- many columns, with some manipulation, etc.
  BULK COLLECT INTO l_col1, l_col2, ... l_colN
  FROM a,b,c,d
  WHERE ... -- join and filter, etc.
END;


Dropped Database Connections in SQL Server 2008R2

It can be SQL related, not just OS and hardware:

https://support.microsoft.com/en-us/kb/2491214


Efficiently storing irregular/repeating intervals (think calendar/events)

Always look for standards that support your requirements. What is a standard that supports recurring calendar events? ICalendar RRULEs:

http://en.wikipedia.org/wiki/ICalendar

http://www.kanzaki.com/docs/ical/rrule.html

You can either store the rule as plain text and parse it as needed, or use a database schema for them:

SQL Schema:
https://stackoverflow.com/questions/1054201/ical-field-list-for-database-schema-based-on-ical-standard/1397019#1397019

Perl:
http://search.cpan.org/~rfrankel/iCal-Parser-1.16/lib/iCal/Parser.pm

PostgreSQL-specific:
http://svn.expressolivre.org/contrib/davical/dba/rrule_functions-8.1.sql
Performance-wise, you can use Materialized Views to calculate events, say one month in advance and one month prior (if required):

https://stackoverflow.com/questions/4239871/when-building-a-calendar-app-should-i-store-dates-or-recurrence-rules-in-my-dat/10151804#10151804

Additional information : http://www.vertabelo.com/blog/technical-articles/again-and-again-managing-recurring-events-in-a-data-model
This article explains each and every aspect of repeated events. Please read it before coming to any conclusions.


Should I use MongoDB or a NoSQL solution at all for this?

You certainly could do this but if revision control is what your after for documents use something like subversion or git.  In general storing documents that do not depend on transactions and you are not storing sensitive information that requires immediate atomicy and consitency like financial transactions then a nosql solution may fit your needs and be appropriate.

To the best of my knowledge you'd have to be rolling your own in the revision diffs if that's something you're really looking for whether from a nosql or traditional sql solution.

Regardless, if you're working on something trivial and want to play around with newer technologies that you're unfamiliar with that's great, go for it.

At the same time, if this is for a commercial client and you aren't familiar with mongo, couch, etc it's probabaly better to stick with what you know and leave the sandbox for play time until you're ready.


inaccurate statistics in multitenant database

My suggestion with Azure SQL Database, perform index maintenance during off-hours using Ola Hallengren's scripts and after that update all columns statistics with full scan. Do not rely on automatic features on medium size databases (> 100 GB).

Here you will find steps to create elastic jobs to update indexes and statistics using Ola Hallengren's scripts.

Another way to perform maintenance of indexes and statistics can be found here using Azure Automation and PowerShell.


Is there a way to get count based on multiple columns but still have it be distinct in SQL?

Not sure I understand the problem, but if you first pick distinct rows, and then count number of rows per date

select dt, count(1)
from (
    select distinct dt, job, title, name
    from t
) as x
group by dt

I used dt instead of date since it is a reserved word


Does a Target Service need to do anything for a conversation that is ended?

From Microsoft Docs:

Both participants in a conversation must call END CONVERSATION for the conversation to complete.

If only one side ends the conversation, then the conversation metadata stays valid.
The END CONVERSATION ... WITH CLEANUP can be used to clean up any un-ended conversations.
Remus Rusanu has an excellent article showing how to do that here.


How can I restructure a table of points with repeating lengthy text ids?

First, you shouldn't use those IDs in the database. To start off they're clearly a compound ID, with

bigint (hex encoded)
uuid

The @@@ is a text-token. That means right off the bat you can make your table substantially smaller very easy.

ALTER TABLE foo
  ADD COLUMN id_uuid uuid,
  ADD COLUMN id_num bigint;

Now you need to populate it. (essentially we make sure you have 16 characters, prepend an x and then cast it to a bigint by proxy of ::bit(64))

UPDATE foo
SET id_num =
    (
      'x'
      || lpad(
        (regexp_match(id, '(.*?)@'))[0]
        , 16
        , '0'
      )
    )::bit(64)::bigint,
  id_uuid = (regexp_match(id, '.*@(.*)$'))[1]::uuid;

After this you could drop the original text id column. That's what you should do when you commit to a direction.

Now you have two id columns, but one of them is 16 bytes, the other is 8 bytes. compared to one text column that is 55 bytes (plus varlena overhead which is like 6 byts for you).

What you really want to do though is rewrite the table with these two columns at the start because that'll impact physical layout and make a big difference. Google for PostgreSQL Tetris for the reasons there..

At the point that you have two ids, and you can start to understand the data, the questions emerge.

Are both ids necessary, can I drop one and will it make it make difference
If they're both neccessary, can I store both of them as external vendor ids in a table and assign my own simplier int4 ids?

Depending on the answers to those questions you may want to structure some triggers so on insertion something (like a function you create) takes a text argument, decodes it into a composite type of (uuid,int) and assigns them to the new columns in one pass.

Moreover, you have another massive question here. This is a spatial problem. You have GPS coordinates. If you're talking about billion of rows, you may consider using a table like this until the routes are complete, and then migrating to a GIS solution with LINESTRINGS and PostGIS. Then you can map the routes in the database, and find what roads were traveled, how many miles the routes were, and place them on a map (or do other fancy goodness). The GIS solution makes the points columnar and gives you the ability to index them too.

You may want a consultant.


The difference between using parallelism "/*+parallel(4)*/" in the subquery or the outer query in terms of performance in Oracle

Well, if you have just one table, and your outer select is not accessing other tables, I would guess that "Second Location" would be the only one the optimizer looks at.  In practice, easy to test:  just do an explain plan on the two options, and see.  Then invoke it both ways, and verify what happens.
Oracle is pretty smart in doing the right thing for parallel queries; take a look at the following parameters, if neither option is producing a parallel execution plan:
select name, value from v$parameter where name like 'parallel%' order by 1;

And look at Oracle Database Reference section on "Initialization Parameters" such as parallel_adaptive_multi_user for details.
So nothing terrible is likely to happen if you put it in both places, but definately test in a development environment first.


Hiding or masking sensitive information in the SQL Server audit log

I got the following answer from Emily Fei at msdn Forums

  No, SQL Server Audit can't be able to choose the event field name. The
  Audit log file will record the full event field name.

Seems the only way to achieve something like this is to restrict the access to the raw audit log and then publish a subset of the log for more general use.


Why aren't some spid values showing up in sys.dm_exec_requests?

Sessions can exists without an active request, but still block other sessions.

Consider if you have one window open in SSMS where you run this:

BEGIN TRANSACTION
INSERT INTO dbo.SomeTable DEFAULT VALUES;

Then in another window you run:

BEGIN TRANSACTION
SELECT * FROM dbo.SomeTable;

The first session will be holding locks on dbo.SomeTable, without it showing up in sys.dm_exec_requests.  You will see the session in sys.dm_exec_sessions, and you'll be able to see the open transaction in sys.dm_tran_session_transactions.

I'd recommend Adam Machanic's sp_whoisactive, however if you aren't allowed to download scripts to run against your server, you may consider something like the following code that can identify open transactions without an active request that are blocking other sessions:

/*
    This query shows sessions that are blocking other sessions, including sessions that are
    not currently processing requests (for instance, they have an open, uncommitted transaction).

    By:  Max Vernon, 2017-03-20
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --reduce possible blocking by this query.

USE tempdb;

IF OBJECT_ID('tempdb..#dm_tran_session_transactions') IS NOT NULL
DROP TABLE #dm_tran_session_transactions;
SELECT *
INTO #dm_tran_session_transactions
FROM sys.dm_tran_session_transactions;

IF OBJECT_ID('tempdb..#dm_exec_connections') IS NOT NULL
DROP TABLE #dm_exec_connections;
SELECT *
INTO #dm_exec_connections
FROM sys.dm_exec_connections;

IF OBJECT_ID('tempdb..#dm_os_waiting_tasks') IS NOT NULL
DROP TABLE #dm_os_waiting_tasks;
SELECT *
INTO #dm_os_waiting_tasks
FROM sys.dm_os_waiting_tasks;

IF OBJECT_ID('tempdb..#dm_exec_sessions') IS NOT NULL
DROP TABLE #dm_exec_sessions;
SELECT *
INTO #dm_exec_sessions
FROM sys.dm_exec_sessions;

IF OBJECT_ID('tempdb..#dm_exec_requests') IS NOT NULL
DROP TABLE #dm_exec_requests;
SELECT *
INTO #dm_exec_requests
FROM sys.dm_exec_requests;

;WITH IsolationLevels AS
(
    SELECT v.*
    FROM (VALUES
              (0, 'Unspecified')
            , (1, 'Read Uncomitted')
            , (2, 'Read Committed')
            , (3, 'Repeatable')
            , (4, 'Serializable')
            , (5, 'Snapshot')
        ) v(Level, Description)
)
, trans AS
(
    SELECT dtst.session_id
        , blocking_sesion_id = 0
        , Type = 'Transaction'
        , QueryText = dest.text
    FROM #dm_tran_session_transactions dtst
        LEFT JOIN #dm_exec_connections dec ON dtst.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
)
, tasks AS
(
    SELECT dowt.session_id
        , dowt.blocking_session_id
        , Type = 'Waiting Task'
        , QueryText = dest.text
    FROM #dm_os_waiting_tasks dowt
        LEFT JOIN #dm_exec_connections dec ON dowt.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
    WHERE dowt.blocking_session_id IS NOT NULL
)
, requests AS
(
SELECT des.session_id
    , der.blocking_session_id
    , Type = 'Session Request'
    , QueryText = dest.text
FROM #dm_exec_sessions des
    INNER JOIN #dm_exec_requests der ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest
WHERE der.blocking_session_id IS NOT NULL
    AND der.blocking_session_id > 0
)
, Agg AS (
    SELECT SessionID = tr.session_id
        , ItemType = tr.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = tr.session_id)
        , BlockedBySessionID = tr.blocking_sesion_id
        , QueryText = tr.QueryText
    FROM trans tr
    WHERE EXISTS (
        SELECT 1
        FROM requests r
        WHERE r.blocking_session_id = tr.session_id
        )
    UNION ALL
    SELECT ta.session_id
        , ta.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = ta.session_id)
        , BlockedBySessionID = ta.blocking_session_id
        , ta.QueryText
    FROM tasks ta
    UNION ALL
    SELECT rq.session_id
        , rq.Type
        , CountOfBlockedSessions =  (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = rq.session_id)
        , BlockedBySessionID = rq.blocking_session_id
        , rq.QueryText
    FROM requests rq
)
SELECT agg.SessionID
    , ItemType = STUFF((SELECT ', ' + COALESCE(a.ItemType, '') FROM agg a WHERE a.SessionID = agg.SessionID ORDER BY a.ItemType FOR XML PATH ('')), 1, 2, '')
    , agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.QueryText
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , TransactionIsolationLevel = il.Description
FROM agg
    LEFT JOIN #dm_exec_sessions des ON agg.SessionID = des.session_id
    LEFT JOIN IsolationLevels il ON des.transaction_isolation_level = il.Level
GROUP BY agg.SessionID
    , agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.QueryText
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , il.Description
ORDER BY
    agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.SessionID;


MySQL bug: NOT IN + subquery using GROUP BY HAVING returns nothing

This problem looks weird.

It reminds me of a post I wrote 7 years ago : Problem with MySQL subquery

Sometimes, values in subquery results may disappear intermittently while processing.

This problem was usually associated with non-SELECT queries involving subqueries.

This looks like something even worse. You should file a bug report on this one.


My guess would be that you have TEXT/BLOB data and that the row data in the 14562 row is bigger than your current MySQL Packet, which is sized by max_allowed_packet.

MySQL Perspective

You need to reserve more space for max_allowed_packet

Jul 29, 2013 : Max_packet_allowed setting for Windows 7
Jul 03, 2013 : What max_allowed_packet is big enough, and why do I need to change it?
Jun 12, 2012 : MySQL Error Reading Communication Packets

Your InnoDB Redo Logs (sized by innodb_log_file_size) may need to be increased as well. I first learned of it (in relation to the MySQL Packet) from ServerFault. I refer to that ServerFault post in some of my posts:

Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error
Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?

I would max out the max_allowed_packet to 1G and resize your redo logs to 1.5G

[mysqld]
max_allowed_packet = 1G
innodb_log_file_size = 1536M

Next, run this to purge all transactions out of the redo logs:

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Then, restart mysql (which is required).

Note to everyone running MySQL 5.5 and prior

MySQL 5.6 will handle resizing logs for you when restarting. Previous versions of MySQL require that you do that manually. Your steps after changing my.cnf and setting innodb_fast_shutdown to 0, you would do the following

service mysql stop
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
service mysql start

HeidiSQL Perspective

One very common complaint is Error 2027, particularly with using LOAD DATA INFILE

Some have suggested using older versions of libmysql.dll

HeidiSQL 8.1 released
sql error(2007) Malformed packet

Some suspect a character set issue : Error Code: 2027 Malformed packet

GIVE IT A TRY !!!


This is the classic "Double Hop" problem.  Several pieces need to be in place for this to work. For server1 (SSMS) -> server2 (SQL) -> server3 (sql)

server2 must have a SPN
server3 must have a SPN
server2 must be allowed to delegate permissions to server3

A SPN (Server Principal Name) is set in Active Directory on an account and basically ties a service to that account.  e.g.  MSSQL Server on SERVER2 is running as domain\sql_server.  When you connect with Kerberos, You request a ticket from the DC to connect to "MSSQL Server on SERVER2".  The DC replies with a ticket is can only be un-encrypted by domain\sql_server.
SQL Server will attempt to self-register an SPN when it starts.  This is logged in the ERRORLOG

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/MYLAPTOP:1433 ] for the SQL Server service.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/MYLATOP ] for the SQL Server service.
SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

Verify that you can connect from server1 to server2/3 using Kerberos authentication:

remote into server1
connect using SSMS to server2
Run this:

select auth_scheme from sys.dm_exec_connections where session_id =
@@spid

If it returns NTLM, then you are either missing the SPN on server2 or you have a duplicate SPN.  If it returns "Kerberos" then congratulations, you're partially there.

Repeat this test by trying to connect from server1 to server3.

Once you can successfully connect to both server2 and server3 using Kerberos, you need to ensure that the accounts that SQL is running as have permissions to delegate.  This is an active directory setting that is on the service account SQL is running as.


"Could not find server error" when using INSERT SELECT statement in SQL Server

To execute such statement you need to create LinkedServer first. In this case, you need to have LinkedServer from one RDS instance to another RDS instance.

https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/

  RDS SQL Server to RDS SQL Server

  For this scenario, both instances of RDS SQL Server must be private
  instances (not publicly accessible) within the same VPC. If the RDS
  SQL Server instances are publicly accessible, then you need to refer
  to the private IP of the RDS instances when creating the linked
  servers. Because you don’t have access to the private IP addresses of
  the servers, you need to use an EC2 instance in the same VPC to run
  NSLookup on the private instances of RDS. Do this for each RDS
  instance name.

  NSlookup privatesql.cb2xkfsffmy7.us-west-2.rds.amazonaws.com

  Address: 10.0.4.236

  From there, the code is very much like the RDS to EC2 code, except now
  you use the private IP address of the remote RDS instance.

  EXEC master.dbo.sp_addlinkedserver @server = N’RDSPrivate‘, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N’10.0.4.236′;

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’RDSPrivate’,@useself=N’False’,@locallogin=NULL,@rmtuser=N'<username>’,@rmtpassword=‘<password>’;

  GO

  Just as we did preceding, use a four-part name to reference the remote
  RDS instance:

  SELECT * from RDSPrivate.TestDB.dbo.t1;

  Note one limitation for all RDS instances: If the physical server
  supporting your RDS SQL Server instance changes (such as when
  upgrading to a new version of SQL Server or changing the instance
  type), the private IP address of the RDS instance of SQL Server might
  change. This change can happen with no alerts or notifications to your
  administrative team. Thus, if the linked server connection fails, your
  first troubleshooting steps should include verifying that the private
  IP address of the RDS instance has not changed. If this risk is
  unacceptable in your environment, then AWS doesn’t recommend using the
  linked server feature with RDS SQL Server.


Stored procedure suddenly very slow, updating statistics not helping

I would suggest that you create a temp table or use common table expression to get the list of all longitudes you require from your remote Server and use the new temp table or common table expression to inner join with your FuelCalc table.

Something like below.

WITH CTE as
(
select Longitude, MAX(SampleDateTime) as SampleDateTime, ShipLocationId, SHIPiD
        from [RemoteServer].[dbo].[Ships].LocationAttributes la
        inner join [RemoteServer].[dbo].[Ships].Locations l on l.ShipLocationId = la.ShipLocationId
        where l.SampleDateTime <= timestamp
            and l.SampleDateTime <= @ToDT
            and ShipId = @ShipId
        GROUP BY Longitude
)
select PK_ShipDataSubsystemConfigID
,timestamp
,cte.Longitude
,'Vessel Position Lng'
from FuelCalc
inner join ShipDataSubsystemConfig s on s.PK_ShipDataSubsystemConfigID =       FuelCalc.FK_ShipDataSubsystemConfigId
left outer join cte on cte.SHIPiD = FK_ShipId
where timestamp > @FromDT
and timestamp < @ToDT
and FuelCalc.FK_ShipId = @ShipId
order by timestamp desc


Collecting trace info of SQL Server 2005 using SQL Server 2008 R2 profiler

It sounds like you might possibly be mistaking a server side trace and SQL Server Profiler which perform very similar functions but work slightly differently and store data in different places.

Show us your code on how you started the trace, or tell us what steps you took.  You could also look at the following:

Server Side Trace:
http://www.sqlservercentral.com/stairway/72363/

Profiler (not recommended unless you are collecting a small amount of data):
http://databases.about.com/od/sqlserver/ht/trace.htm

Is the problem that when you start up SQL Server Profiler and start collecting data nothing populates the screen?


How much time will a vacuum/autovacuum operation take?

On my PostgreSQL (8.3) I use this trick:

I get table's disk size using pg_total_relation_size() - this includes indexes and TOAST size, which is what VACUUM processes. This gives me the idea of how many bytes the VACUUM has to read.
I run VACUUM on the table.
I find the pid of the VACUUM process (in pg_catalog.pg_stat_activity).
In Linux shell I run while true; do cat /proc/123/io | grep read_bytes; sleep 60; done (where 123 is the pid) - this shows me bytes read by the process from the disk so far.

This gives me rough idea on how many bytes are processed (read) every minute by the VACUUM. I presume that the VACUUM must read through the whole table (including indexes and TOAST), whose disk size I know from the step 1.

I presume that the table is large enough so that the majority of it's pages must be read from disk (they are not present in Postgres shared memory), so the read_bytes field is good enough to be used as a progress counter.

Everytime I did this, the total bytes read by the process was no more than 5% from the total relation size, so I guess this approach may be good enough for You.


List all MongoDB databases from Linux bash terminal

"show dbs" calculate the databases sizes and that may take some time.

You can pass this command to the mongo shell, then clean the response:

echo "db.getMongo().getDBNames()"|mongo --quiet |tr -d \[\] | tr , "\n"|cut -c3-| tr -d \"


Before a replay, I would:

Run the Upgrade Advisor and Best Practices Analyzer
Run a trace looking for deprecation events
Look for some of these issues; the above methods are unlikely to find all of them.

Note that none of the above, nor replaying the trace, are guaranteed to find all compatibility issues (consider the case where a stored procedure will only have a problem with very specific parameters are used, and those don't happen to be used during your trace).

And when you replay the trace, you'll have to parse the output file(s) or table(s) yourself, and perhaps even perform diffs to see any cases where there are errors on the new version that didn't happen on the old version. The replay doesn't produce a nice "here's what worked differently from the original activity" report. You can do this by simply performing a join against .

Here is a quick example I whipped up on my own system - you need to add the Error column to your trace, and note that I am absolutely not advocating Profiler for this, it was just the easiest way to demonstrate. First, on 2014, create a database using 100 compatibility level:

CREATE DATABASE Compat100;
GO
ALTER DATABASE Compat100 SET COMPATIBILITY_LEVEL = 100;
GO

Now, run these two batches:

USE Compat100;
GO
DECLARE @x TABLE(n DATETIME2, x AS CONVERT(CHAR(10), n)); -- not style 121
INSERT @x SELECT GETDATE();
-- error:
SELECT CONVERT(INT, LEFT(x,4)) FROM @x;
GO

USE tempdb;
GO
DECLARE @x TABLE(n DATETIME2, x AS CONVERT(CHAR(10), n)); -- style 121
INSERT @x SELECT GETDATE();
-- not an error:
SELECT CONVERT(INT, LEFT(x,4)) FROM @x;

A trace will yield these:

(Again, do not use Profiler for the actual work, this was just to quickly illustrate that a trace can capture error conditions, and that if you had these two batches run identically on two different servers, you should easily be able to join on TextData equality and Error inequality. And yes, I do realize that this example works the opposite way - the error happens on 2008 but works on 2014, but hopefully it still demonstrates the approach.)

Also see this tip.


Why do my ON UPDATE/ON DELETE rules disappear?

DISCLAIMER : Not a user of phpMyAdmin

When visually comparing the table design and the screenshot, I noticed something interesting: It seems that phpMyAdmin is not designed to graphically represent a compound constraint (a constraint with more than one column). How can you tell ???

The Users table has a PRIMARY KEY on the single column userNr. A primary key is a constraint. (Just run select count(1),CONSTRAINT_TYPE from information_schema.table_constraints group by CONSTRAINT_TYPE; and will see PRIMARY KEY registered as a constraint).
The Customers table has the sinlge column userNr as PRIMARY KEY also, thus it is a constraint.
The Orders table has a compound PRIMARY KEY, but the screenshoot only shows the first column of the PRIMARY KEY.
The Items table has a compound PRIMARY KEY, but the screenshoot only shows the first column of the PRIMARY KEY.
The OrderContains table has 3 constraints, 1 PRIMARY KEY and 2 Foreign Key. Notice that the two foreign key constraints are defined as having two columns each but the only the first column of each constraint is displayed in the screenshoot.

You can use information_schema.KEY_COLUMN_USAGE

mysql> desc KEY_COLUMN_USAGE;
+-------------------------------+--------------+------+-----+---------+-------+
| Field                         | Type         | Null | Key | Default | Extra |
+-------------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG            | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA             | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME               | varchar(64)  | NO   |     |         |       |
| TABLE_CATALOG                 | varchar(512) | NO   |     |         |       |
| TABLE_SCHEMA                  | varchar(64)  | NO   |     |         |       |
| TABLE_NAME                    | varchar(64)  | NO   |     |         |       |
| COLUMN_NAME                   | varchar(64)  | NO   |     |         |       |
| ORDINAL_POSITION              | bigint(10)   | NO   |     | 0       |       |
| POSITION_IN_UNIQUE_CONSTRAINT | bigint(10)   | YES  |     | NULL    |       |
| REFERENCED_TABLE_SCHEMA       | varchar(64)  | YES  |     | NULL    |       |
| REFERENCED_TABLE_NAME         | varchar(64)  | YES  |     | NULL    |       |
| REFERENCED_COLUMN_NAME        | varchar(64)  | YES  |     | NULL    |       |
+-------------------------------+--------------+------+-----+---------+-------+
12 rows in set (0.01 sec)

mysql>

Notice that the information for constraints and all participating columns can be located here. Since you can see the constraints you defined using the mysql client, rest assured that you are not going crazy. It just seem to me phpMyAdmin is not coded correctly for multicolumn constraints.

Therefore, phpMyAdmin cannot properly see the UPDATE and DELETE CASCADE rules for a compound constraint because its source code is most likely using the first column only as the reference point to the information_schema database.


get column from too many tables in mysql

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!


What happens when all secondary read only nodes are powered off?

Make sure you include the primary in the read_only_routing_list

ALTER AVAILABILITY GROUP  aggroup
MODIFY REPLICA ON 'node1'
WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST= ('node2','node3','node1')
)
);

Do the same for the other 2 nodes. and you can test using this query

SELECT    AVGSrc.replica_server_name AS SourceReplica
        , AVGRepl.replica_server_name AS ReadOnlyReplica
        , AVGRepl.read_only_routing_url AS RoutingURL
        , AVGRL.routing_priority AS RoutingPriority
FROM sys.availability_read_only_routing_lists AVGRL
INNER JOIN sys.availability_replicas AVGSrc ON AVGRL.replica_id = AVGSrc.replica_id
INNER JOIN sys.availability_replicas AVGRepl ON AVGRL.read_only_replica_id = AVGRepl.replica_id
INNER JOIN sys.availability_groups AV ON AV.group_id = AVGSrc.group_id
ORDER BY SourceReplica


Why does SQL Server not do compound column statistics histograms?

Background
The current SQL Server model only uses single-column histograms and multi-column density information. Single column histograms are used to estimate selectivity for suitable predicates e.g. a = 1 or b > 50. A query with multiple predicates simply combines the individual selectivities (with assumptions) to produce an estimated overall selectivity.
For an example, see my article Cardinality Estimation: Combining Density Statistics
Multi-column density further informs the model by providing weak correlation information for multiple equality predicates and grouping cardinalities for aggregations.
Statistics associated with indexes are an opportunistic add-on to that model: The engine might as well collect (normally full scan) statistics while it is building an index. SQL Server automatically constructs a leading-column histogram and density information for the other keys.
Histograms for non-leading columns in an index may be constructed on demand automatically by the query processor, or in advance using sp_createstats with the @indexonly option (among others).
Multi-column histograms
The assumptions made when combining single-column statistics (as above) may or may not model the reality of the data well enough. In many cases, the options available (exponential backoff, independence, minimum selectivity) produce a 'good enough' estimate.
We also have filtered statistics (and indexes) as a natural solution to low-cardinality leading column indexes such as in the question example. Taking these to the logical extreme leads us closer to the multi-dimensional statistics that the question is not about.
When the available modelling options cannot provide a suitable estimate, a multi-column statistic histogram could indeed give a better selectivity estimate for suitable index predicates, in some cases. There are some difficulties around combining different data types in different columns, but nothing insurmountable.
We would also need a histogram for each level of the index keys (for best results); so for an index on (a, b, c) that would mean histograms on (a, b) and (a, b, c) in addition to the current single-column histogram on (a) alone.
The mechanism used to detect stale statistics would also need to be modified to maintain affected multi-column histograms. These histograms would likely end up being rebuilt more often than single-column statistics, simply because modifications to more columns affect them.
All this adds size, complexity, and maintenance overhead.
Multi-column statistics can be simulated (to a limited extent) using a statistic created on a carefully constructed computed column referencing multiple columns. The query would need to include a predicate on the computed column (or an exact textual match for the underlying formula) to take advantage of that statistic. There are probably only very limited situations where this approach is practical. Nevertheless, it has some of the same implementation issues as automatic multi-column histograms would.
Ultimately, the only people that could say for sure why SQL Server does not support multi-column statistics would be the designers themselves. If you feel you can make a strong case for a product improvement in this area with wide applicability, you could suggest it on Connect or via your normal support channel.
Footnote

In this case, it makes the whole histogram a lot less useful, because the first column only ever has two values

The histogram still provides useful information about the distribution of values in the leading column: When the statistics were built, there were 24,398 rows where IsArchived was false, and 216,602 rows where is was true.
In addition, the statistics object tells us there are (1 / 0.5) = 2 distinct values for IsArchived, (1 / 4.149378E-06) ~= 241000 distinct values for (IsArchived, Mystery) with an average row size of 37 bytes, and there same frequency for (IsArchived, Mystery, Id) with 4 extra bytes per row.
That's all good general-purpose information, which can be combined with statistical information about other columns to produce a selectivity estimate in queries with multiple predicates (as mentioned).


"Thread was being aborted" error when inserting 25k rows

Strongly recommend you look into Table-Valued Parameters and treat your insert/update as a set (using MERGE probably) instead of calling a singleton stored procedure 25,000 times. You can skip a lot of steps here (in C# you can just stuff your 25K rows into a DataTable, for example, making it easy to pass to a stored procedure in a single call), and reduce overhead significantly.

If you create a type for your bank rows:

CREATE TYPE dbo.BankRow AS TABLE
(
  SortCode int PRIMARY KEY,
  BankName nvarchar(50),
  ShortBankName nvarchar(50),
  BranchName nvarchar(50),
  AddresseeName nchar(60),
  PostalName nchar(60),
  AddressLine1 nchar(100),
  AddressLine2 nchar(80),
  CityOrTown nchar(50),
  AreaOrCounty nchar(60),
  PostCode nchar(12),
  FasterPayments nchar(10),
  DirectDebits nchar(10),
  Chaps nchar(10),
  ChequeCreditClearing nchar(10)
);

Your procedure then might look like this:

ALTER PROCEDURE dbo.BankImport
  @BankRow dbo.BankRow READONLY
AS
BEGIN
  SET NOCOUNT ON;

  MERGE dbo.Banks AS b
    USING @BankRow AS tvp
    ON tvp.SortCode = b.SortCode
  WHEN MATCHED THEN
    UPDATE SET BankName = tvp.BankName,
               ShortBankName = tvp.ShortBankName,
               ...
  WHEN NOT MATCHED THEN
    INSERT (SortCode, BankName, ShortBankName, ...)
    VALUES (tvp.SortCode, tvp.BankName, tvp.ShortBankName, ...)
END
GO

So now you can pass your DataTable into the stored procedure once instead of calling the singleton stored procedure 25,000 times. You'll have to add the other columns where I put ... and you'll also have to adjust your C# code to work with a TVP.

I wrote up a quick article on TVPs here, including a quick example on C# syntax:

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

And Erland's article is useful as well:

http://sommarskog.se/arrays-in-sql-2008.html


Setting up SQL Server service broker from one database to multiple databases

In your code the  ReceiveOrderForProcess procedure in ReceiverDB2 should be looking at a queue called DestQueue2 rather than DestQueue. I think.


Is there a way to replicate certain part of collection in Mongodb

MongoDB at least to current version 2.6+ for sure does not support that as build-in functionality. I guess because a replica set using selective replication won't be consistent.

There is a feature called filtered replication in Couch but never tried it:
http://wiki.apache.org/couchdb/Replication#Filtered_Replication


SQL Server Agent - Report Failure but continue When intermediate step fails

What I usually do is just add one more step "Error" between step #2 and #3 that sends an  email.
Something like this:


Help preparing for MySQL Master Slave Replication

Common question, common answer.  The first time you set it up it is painful.  But, if you do it one of several 'right' ways, the subsequent backups will be less painful.

LVM -- requires OS involvement.  But backup takes about 1 minute, regardless of data size.

Replication -- requires a full copy.  Xtrabackup can do it relatively painless for InnoDB, but blocks for MyISAM.  Corollary:  You should move away from MyISAM.

Disk sync (via rsync or whatever) -- takes long for 400GB, but it is clean.  You must shutdown MySQL during the operation.  (LVM would make it short.)

If you have an adequate backup, and can build a Slave from it, then that may be your best bet.  In the future, take backups from the Slave.  (Even better, clone one Slave from anothor.)

Yes, once a Slave exists, pt-table-checksums and pt-table-sync are a good idea.

Also, you need to move from 5.5 to 5.6 to 5.7.  It won't be long before 5.5 is no longer supported.


Can I have more than one relationship between two tables?

Yes. You have two different relationships here.

A one-to-one relationship between employees and header_maintenance_report via employee_id on each table.
A many-to-many relationship between employees and header_maintenance_report via the keys on detail_maintenance_report.

So long as you know what these two relationships represent (e.g. report author and report viewers) then there's no problem with that, however... I'd suggest you think carefully about naming. From your wording it's not clear exactly what the relationships represent but you might want to think twice about the use of "header" and "detail" in the table names as it sounds like the "detail" table is actually a joining table between the other two.

When using many-to-many relationships a common approach is to use the names of the two tables you're joining as the name for the join table, e.g.

employees
maintenance_reports
employees_to_maintenance_reports

Edit, see this example: http://sqlfiddle.com/#!9/03743/1


Viewing execution plans for queries with parameters

You can use this

 select st.text, qp.query_plan,  qs.plan_handle,
        total_worker_time/execution_count AS [Avg CPU Time]
 FROM sys.dm_exec_query_stats AS qs
    cross apply sys.dm_exec_sql_text(qs.plan_handle) st
    cross apply sys.dm_exec_query_plan(qs.plan_handle) qp

if you are using sql server 2008 and above you can configure Extended Events as well to see plans etc.


Selecting a record based off conditions CLOB

After surviving the ordeal of installing Oracle 12c on Devuan, I came up with this query using json_exists:
select
  t.id,
  t.data.miscData,
  t.data.miscData.miscType,
  t.data.confirmationNumber
  from json_documents t
  where json_exists(
          t.data,
          '$.miscData?(@.miscType=="CORRECT_TYPE")'
        )
    and json_exists(
          t.data,
          '$?(@.confirmationNumber=="123456789qwerty")'
        )

FYI:
CREATE TABLE json_documents (
  id    RAW(16) NOT NULL,
  data  CLOB,
  CONSTRAINT json_documents_pk PRIMARY KEY (id),
  CONSTRAINT json_documents_json CHECK (data IS JSON (STRICT))
)


MySQL connection count much larger than query count

Here is something interesting to remember : the status variables Questions takes a count of all queries executed by mysqld, regardless of who issued each query.

Why do I say regardless of who issued each query ?

There are queries that are launched internally by mysqld. Here is a simple example:

mysql> show global status like 'Com_select'; show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 15    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 56    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Com_select'; show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 15    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 58    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 59    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 60    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

Please note that issuing a simple query like show global status like 'Questions'; is a query even if your are just requesting internal data from mysqld.

Multiply this by any number of DB Connections coming from MONyog, Munin, Nagios, MySQL Enterprise Monitor, or any other MySQL Monitoring tools and will always have a steady rise in the number of lightweight queries being executed.

I wrote about this before in ServerFault in a question "1 billion mysql queries in 24 hours? Can something be wrong?"

Queries can quitely come and go. The question left to answer is, why so many DB Connections? You need to make sure Apache is closing DB Connections at the same rate as mysqld. You may need to see run netstat and look for DB Connections to mysql that have the TIME_WAIT status.

There are bug reports on this going back to MySQL 4.1 although the root of the problem may actually be with PHP:

http://bugs.mysql.com/bug.php?id=36872
http://bugs.mysql.com/bug.php?id=56979
http://www.webhostingtalk.com/showthread.php?t=251177


Query optimization with extreme statistic

At the risk of stating the obvious, you have a cardinality problem with col1.

Looking at the two queries you posted in the question:

SELECT * FROM table_1 WHERE col1 = 1
AND id NOT IN (79869) ORDER BY date DESC LIMIT 0, 10;

SELECT * FROM table_1 WHERE col1 = 1 AND id <> 18983
AND date > '2012-10-26' AND (col2 LIKE '%word1%' OR col2 LIKE '%word2%')
ORDER BY date DESC LIMIT 5;

you need good indexes that will accommodate these two queries. In your particular case, you will need just one index.

If the table is MyISAM, here is that index

ALTER TABLE table_1 ADD INDEX col1_date_ndx (col1,date,col2,id);

If the table is InnoDB, here is that index

ALTER TABLE table_1 ADD INDEX col1_date_ndx (col1,date,col2);

How will this index help? Although you have to live with the lopsided cardinality of col1, you improve each query's chances of being effectively searched.

You could also take a chance on refactoring the queries. How ???

Perhaps you could collect the ids only then join the ids back to the original table.

QUERY #1

Here is the first query

SELECT * FROM table_1 WHERE col1 = 1
AND id NOT IN (79869) ORDER BY date DESC LIMIT 0, 10;

Collect the ids and join back to table_1

SELECT B.* FROM
(SELECT id FROM table_1 WHERE col1 = 1
AND id NOT IN (79869) ORDER BY date DESC LIMIT 0, 10) A
LEFT JOIN table_1 B USING (id);

QUERY #2

Here is the second query

SELECT * FROM table_1 WHERE col1 = 1 AND id <> 18983
AND date > '2012-10-26' AND (col2 LIKE '%word1%' OR col2 LIKE '%word2%')
ORDER BY date DESC LIMIT 5;

Collect the ids and join back to table_1

SELECT B* FROM
(SELECT * FROM table_1 WHERE col1 = 1 AND id <> 18983
AND date > '2012-10-26' AND (col2 LIKE '%word1%' OR col2 LIKE '%word2%')
ORDER BY date DESC LIMIT 5) A
LEFT JOIN table_1 B USING (id);

I cannot make promises on the queries, but, at the very least, the index suggestion should help.

Give it a Try !!!


Would it be correct to assume that inner query ordering will follow into my outer query?

You cannot depend on any ordering unless you have ORDER BY in the outer query. The ordering will depend on the execution plan chosen.
In the case of the present query I cannot think of a reasonable execution plan that would not preserve the ordering, but who knows what the future will bring for the query optimizer?
I would write a second ORDER BY. Sorting 1000 rows is cheap, and you can be certain then.


How can i make month appear with two digits SQL Server

DECLARE @t TABLE(InsertionDate datetime);
INSERT @t(InsertionDate) VALUES(GETDATE()), ('20190125');

SELECT CONVERT(char(6), InsertionDate, 112) FROM @t;

Result:

202004
201901


Is there a better way than the ones listed within to join data in an excel spreadsheet to a table in a SQL Server database?

Great question! Especially the "monster query" part ;)The answer is this guy here, enjoy: Excel Data Source as Linked Server


Will adding features pick up previously installed service pack updates?

I would always err on the side of just installing the service pack again (it will know which components, if any, to update). Especially when adding client tools to a workstation - where this is not a major disruption. (And if you're adding BIDS to a production server, you should reconsider IMHO.)


Getting around MASTER_HOST length limit in 'CHANGE MASTER TO...'?

Create a DNS CNAME record in one of your own domains and use the cluster endpoint hostname as the target of the CNAME record.  Then set MASTER_HOST to that alternate (shorter) name.

MySQL has no problem with another name in the resolution chain being longer than the somewhat arbitrarily-chosen 60 character hostname limit.


Are MySQL InnoDB transactions serializable?

Per the MySQL documentation, SERIALIZABLE is:

  This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

Locking rows FOR SHARE is not equivalent to a SERIALIZABLE transaction because it doesn't protect against rows being inserted by racing transactions that could have been returned by a SELECT statement.

The PostgreSQL docs on SERIALIZABLE provide an example case where this will occur. In your specific case, I believe MySQL's SERIALIZED isolation level will not protect you because two competing transactions can both lock the row FOR SHARE, then both can issue UPDATEs incrementing the value, one transaction will go through, the next will wait for the first to commit, but then go through. You will get a net of one increment instead of two.


Looks like you are describing a subtype/supertype structure.

Start by creating a reference table to hold the different types of response types you can create. This table can be expanded easily if you come up with more types later on.

CREATE TABLE survey_response_types (
  `response_type_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(255) NOT NULL,
) ENGINE=InnoDB;

Modify your survey_responses table to use the response_type_id value instead of actually holding the response. This is the discriminator that lets you know what type of response you have when looking at or joining into the survey_responses table.

CREATE TABLE survey_responses (
  `response_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `survey_id` INTEGER UNSIGNED NOT NULL,
  `question_id` INTEGER UNSIGNED NOT NULL,
  `response_type_id` INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (`survey_id`) REFERENCES surveys (`survey_id`),
  FOREIGN KEY (`question_id`) REFERENCES questions (`question_id`),
  FOREIGN KEY (`response_type_id` REFERENCES `response_types` (`response_type_id`)
) ENGINE=InnoDB;

Then for each type of response, create another table, whose primary key is exactly the the same as the primary key of the survey_responses table. Also, make a foreign key constraint back to the survey_responses table. Do NOT create a new auto_increment key for this table, there is no need.

CREATE TABLE text_responses (
  `response_id` INTEGER UNSIGNED NOT NULL,
  `text` TEXT NOT NULL,
  FOREIGN KEY (`response_id`) REFERENCES `survey_responses` (`response_id`),
) ENGINE=InnoDB;

You mentioned linking back to the int of an answer, so here is how this table would look.

CREATE TABLE answer_responses (
  `response_id` INTEGER UNSIGNED NOT NULL,
  `answer_id` INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (`response_id`) REFERENCES `survey_responses` (`response_id`),
  FOREIGN KEY (`answer_id`) REFERENCES `answers` (`answer_id`)
) ENGINE=InnoDB;

Add as many more tables as there are possible types of answers.

When inserting the data, you should always insert an entry both into the survey_responses table, and then within the same transaction, insert an entry into the correct subtype table. MySQL does not support check constraints, so you'll have to handle the constraint code in the application layer to ensure that the response_type_id always matches correct subtype table that is used.

Together a subtype and supertype create a single 'record' that you can access. They should always exist in pairs. Never just a super type (survey_responses table record) nor just a subtype (which the foreign key constraint fortunately automatically prevents).

You can query them however you desire though. If you just want to make sure a record exists, simply query or join into the survey_responses table. If you want the actual response, construct your joins and queries appropriately.

Using this setup you can avoid having multiple null columns, and also protect against accidents where somehow a response gets two different types of responses. Although the lack of check constraints in MySQL will actually allow two types of responses in the subtype/supertype setup, the discriminator will let you identify the correct one when querying the data. (Any incorrect ones would be a result of bad coding in the application code.)


The database manager failed to allocate shared memory because an operating system kernel memory limit has been reached

I just had the same problem! In my case the problem was just the memory of the DB2 instance.

In windows type:

db2 get dbm cfg | findstr MEMORY

In linux:

db2 get dbm cfg | grep MEMORY

You will get something like that:

Global instance memory (4KB)          (INSTANCE_MEMORY) = 500000

The solution in my case was simply set the same threshold value but by activating the automatic mode:

db2 update dbm cfg using INSTANCE_MEMORY 100000 automatic immediate

The command "immediate" apply the settings now without the need to restart the instance. So if you get the settings again will have:

Global instance memory (4KB)          (INSTANCE_MEMORY) = AUTOMATIC(5000000)

Now try to connect to your database again. I believe that will again connect normally.

According to the DB2 documentation:

  This parameter specifies the maximum amount of memory that can be
  allocated for a database partition if you are using DB2® database
  products with memory usage restrictions or if you set it to a specific
  value. Otherwise, the AUTOMATIC setting allows instance memory to
  grow as needed.

Font: IBM Knowledge Center


SQL logs filling up with "Setting database option Compatibility_Level" messages

Only a couple of things come to mind.

Hack sp_dbcmptlevel and add a "IF suser_sname() = 'Your SSRS Account' RETURN" to the very top of it.
Add a DDL trigger to the database that simply rolls back any alter database statements.

There's no way to intercept the database call and prevent it from being sent to the database engine.


How can I search the full text of a stored procedure for a value?

Use one of

sys.sql_modules
Red Gate SQL Search (free!)
OBJECT_DEFINITION

Never use

INFORMATION_SCHEMA
sys.syscomments


No missing index requests on SQL Server 2012

Depending on which version of SQL Server you're on, missing index (and other index metadata) may disappear when you rebuild indexes.

See Kendra Little's post for more information.


Cannot connect to Oracle database server through SQL Developer but Test works OK

When you connect from the connection properties dialog, two things will happen in the UI:

the connection icon gets a 'connected' overlay
you get a SQL Worksheet***

If your connection is not successful, you'll get an error message, with the text shown at the bottom of the connection properties window.

***For #2 above, this is conditional on the following preference being enabled, which the default is TRUE.

'Open a Worksheet on connect'


I would rotatelogs command to periodically copy the logs into a new one, and then delete the old log after a point in time. You can put it into a cron job.

However, during troubleshooting it is VERY beneficial to have the entire alert logs - and in one file. Be sure to keep it somewhere close, when trouble comes by.


The size of the backup file is only partly relevant. The restore process first have to create the database files (the "containers" as I like to think of them) and they need to have the same size as when the backup was produced.

So you have have a database with only small amount of information, but sitting in large database files.

Instant File Initialization can help to some extent, but only for database files. I.e., not for transaction log files. See for instant this for more info: https://www.brentozar.com/blitz/instant-file-initialization/

If you want to know the size of the file to be created, you can use RESTORE FILELISTONLY:

RESTORE FILELISTONLY
FROM DISK = 'R:\mybackup.bak'

You might need a FILE option if you have more than one backup in the file (just like in the RESTORE command you are running).


If I do a full MySql restore from a dump file, will the user permissions and privileges restore aswell?

Yes, if your mysqldump is backing up all databases!

If not, for future make sure you use --all-databases with mysqldump.

Alert
but this will take your system to the state what it was earlier...
If you know what your java programs are connecting using (user-name and host) then you can choose to fix the permissions for that!!


Index not used with many WHERE LOWER(column) IN (...) conditions, works fine with just a few

Don't do that. Use citext

CREATE EXTENSION citext;
CREATE TABLE public.devices
(
  id integer NOT NULL DEFAULT nextval('devices_id_seq'::regclass),
  token citext NOT NULL,
  ...some other columns...,
  CONSTRAINT devices_pkey PRIMARY KEY (id, token)
)
CREATE INDEX IF NOT EXISTS idx_devices_lower_token ON devices(token);

Then

AND token IN ('92134f4bb6a2263fd044a5a04440bc1a','cbf7d4a094bd266fd3f1b7ef4196172a', 'aa0f7650cf28010b5c01b77ae206f7fb', ... up to 1k tokens ...)))

And if you're talking about a thousand tokens you should probably use a temp table..

CREATE TEMP TABLE query_params ( token citext );
INSERT INTO query_params(token) VALUES ...

then

SELECT *
FROM devices
WHERE (id > '0')
AND EXISTS (
  SELECT 1
  FROM query_params
  WHERE query_params.token = devices.token
)
ORDER BY id
LIMIT 1000;

You can even index query_params.token


You didn't give us a lot of detail, but this article covers most of the basics: http://msdn.microsoft.com/en-us/library/ms190181(v=sql.105).aspx


How to setup solution for multi-dimensional distance search with SQL Server spatial data types

There might be some spatial methods that are useful to some degree, but your biggest problem is going to be the Z value:

  Z-coordinates are not used in any calculations made by the library and are not carried through any library calculations.

This is by design. Don't think of SQL spatial objects as "true" 3D-geometric objects, think of them as map coordinates with an "elevation tag" that isn't factored into any calculations. So the distance function STDistance() works great for "map distances", but not true 3D distances.

You can define the Z value (and the "M" value) of a point, but it isn't used by any calculations.

To demonstrate:

DECLARE @a geometry = geometry::Parse('POINT(0 0 0 2)');
DECLARE @b geometry = geometry::Parse('POINT(1 0 5 1)');
SELECT @a.Z, @b.Z, @a.STDistance(@b)

will return the values

0   5   1

Clearly the distance between these two points should be more than 1, if it used the Z properly. So, they may not work like you hope.

But if you're ok with ignoring the Z value, then performing these calculations will be pretty straight-forward. You could read these values from your existing table like so:

SELECT geometry::Point(X1,Y1,0).STDistance(geometry::Point(X2,Y2,0))
From myTable;

(That 0 in this use is not a Z value, it's an SRID.)

or, if you take the time to change how they're actually stored:

CREATE TABLE myPoints (ID INT, P1 geometry, P2 geometry)

INSERT myPoints
SELECT 1, geometry::Point(X1, Y1, 0), geometry::Point(X2, Y2, 0)
FROM oldTable

SELECT ID, P1.STDistance(P2)
FROM myPoints

There is some related discussion in one of my older answers here.

EDIT: Some further thoughts:

Regarding performance, I have absolutely no clue how geometry objects will compare, performance wise, to you doing the math manually. I imagine converting to Geometry objects on the fly will have a certain degree of overhead, but perhaps converting how your raw data is stored will do better. Testing will be key here.
The Z parameter might not be used by the STDistance() function, but clearly you can manually query Z, and use it to do that part of the math yourself, just use Pythagoras: True3D_Distance^2 = STDistance^2 + (a.Z-b.Z)^2
But note that if your goal is to simply rank the points by closeness, and you don't need to calculate the actual distance, you can simplify your calculation. I'm thinking something like WHERE PointA.STDistance(PointB) + ABS(PointA.Z-PointB.Z) < tolerance or perhaps WHERE PointA.STDistance(PointB) < tolerance AND ABS(PointA.Z-PointB.Z) < tolerance


How to find the schema name of a data pump dmp file?

Use the sqlfile= parameter of impdp to generate a file containing all of the DDL/DML in the dump.

For example:

[oracle@oel61 ~]$ impdp phil/phil directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt

Import: Release 11.2.0.2.0 - Production on Wed Mar 13 15:15:03 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "PHIL"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "PHIL"."SYS_SQL_FILE_FULL_01":  phil/******** directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "PHIL"."SYS_SQL_FILE_FULL_01" successfully completed at 15:15:05

[oracle@oel61 ~]$

Looking for CREATE USER DDL statements in the file should show you what schemas are needed in order to perform an import.

For example, from my test dump:

-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "PHIL" IDENTIFIED BY VALUES 'S:924B2E756404611021428644B4DF06A4A7BAB886837FCCFA510151E0FC44;181446AE258EE2F6'
      DEFAULT TABLESPACE "PHILDATA"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "PHIL";
GRANT CREATE SESSION TO "PHIL";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "PHIL";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "PHIL" DEFAULT ROLE ALL;

The only drawback is that if the .dmp file is large the resulting SQL dump will be huge.


How to control access security in database

A common practice in my experience has been that if a user needs elevated access to a higher environment, you restore a copy to the level which they have access to. This ensures that you can keep similar security across most environments and mitigates privacy and security concerns as well as the creation of new issues.

For example, if tech_leads need to troubleshoot an issue in STG, restore the staging level database into a dev environment (in parallel or overwriting the previous version). That way they can safely test fixes and then let that code be promoted through change management upwards through the environments.

There will still probably be one-off occasions that arise, but that can be handled manually as they are ideally few and far between. Making all those extra roles sounds like more of a nightmare to keep track of and might create new issues.


You can utilize Get-Service and the -Name property to find the exact server name. It has the same effect as doing the search with Where-Object but is less typing.

Get-Service -Name MySql

or just

Get-Service MySql

If you want to start or stop the service you can pipe that to the appropriate command

Get-Service MySql | Stop-Service


What is the significance of phpMyAdmin's configuration storage and what should I know about it?

There was a broken link in that wiki page (which I've just fixed), but it was supposed to take you to http://docs.phpmyadmin.net/en/latest/setup.html#linked-tables

Basically, they're additional features including bookmarked queries, comments, SQL-history, tracking mechanism, PDF-generation, transformations, relations, and more. By adding the tables (which you can do manually or phpMyAdmin often can do for you, which you have taken advantage of), you get access to these features. If you don't need the features, then you don't need the tables.

So you're correct that you don't need to manually run the SQL statements now.

The tables start with the pma__ prefix for the same reason WordPress tables start with wp_ and other applications use similar prefixes: in shared hosting situations, users often only get access to one database and several applications need to peacefully coexist. By adding a namespace prefix, you can have gallery_users, wp_users, and gramps_users (I'm making up these table names for the sake of example)


This is a way of doing it. Can be tested in http://sqlfiddle.com/#!2/7c4525/4

SELECT R1.*
FROM Results R1
INNER JOIN
(
  SELECT RegNo, SubjectCode, Max(Marks) AS MaxMark
  FROM Results
  GROUP BY RegNo, SubjectCode
) R2
ON R1.RegNo = R2.RegNo
  AND R1.SubjectCode = R2.SubjectCode
  AND R1.Marks = R2.MaxMark

This previous solution could output several rows for the same Reg.No and SubjectCode in case that student tied for her best result in more than one attempt.
In this case, if only the row from the last attempt is wanted to be shown you can use the following variation.
Can be tested in http://sqlfiddle.com/#!2/070c5/3. Note that I have added an extra row to the sample data you provided in order to reproduce the situation.

SELECT R1.RegNo, R1.SubjectCode, Max(R1.Attempt), R1.Grade, R1.Marks
FROM Results R1
INNER JOIN
(
  SELECT RegNo, SubjectCode, Max(Marks) AS MaxMark
  FROM Results
  GROUP BY RegNo, SubjectCode
) R2
ON R1.RegNo = R2.RegNo
  AND R1.SubjectCode = R2.SubjectCode
  AND R1.Marks = R2.MaxMark
GROUP BY RegNo, SubjectCode, Grade, Marks

However, if in case of ties, the prefered result to be shown is the lower attempt number Max(R1.Attempt) should be replaced by Min(R1.Attempt)


below article has more details

https://docs.microsoft.com/en-us/archive/blogs/sqlserverfaq/sql-server-deadlocks-due-to-character-data-type-conversion-when-using-sql-server-jdbc-drivers

If sendStringParametersAsUnicode property is set to "TRUE", String parameters are sent to the server in Unicode format: http://msdn.microsoft.com/en-us/library/ms378988.aspx. That means, if this property is set to False, the data type of the parameters being passed from application will be Varchar, not nVarchar and hence there will be no implicit conversion!

And it worked. No deadlocks.


Why do temporal tables log the begin time of the transaction?

The idea is to track logical time vs physical time. Logical simply refers to what a user/app expects the time of an insert/update/delete to be. The fact that the DML operation may take a while for whatever reason, isn't meaningful or even easily determined and understood by a user. If you've ever had to explain lock vs latch contention to an accountant (I have), it's a comparable situation.

For instance, when Bob "tells" the app that all employees in Bob's department will start making $42/min at 20160707 11:00:00, Bob (and his employees) expects everyone's pay is now calculate at $42/min from that time. Bob doesn't care that for this to be effected, the app has to make 2 reads and 6 writes across the database per employee and their data + log files sit on a bunch of RAID-5 SATA II drives so it takes about 7 minutes to finish the task for all 256 of Bob's employees. Bob, his accountant and the payroll manager care that all his employees are paid $42/min starting 20160707 11:00:00. Else, the employees that were updated at 20160707 11:00:01 will be slightly annoyed while those whose records were updated at 20160707 11:00:07 will be gathering outside the payroll department.

There are valid use cases to track physical time such as debugging and forensics but to the end user, it's generally meaningless. The Tlog keeps both ordering and timing information for each of the write operations (among other things) so it's there if you know how to look.


Assuming you don't have any triggers on that table, the simplest way to do this without locking the table is to use pt-online-schema-change.

Make sure you have enough extra disk space to accommodate this change. The table will be larger when you convert it to InnoDB, and you need to maintain both copies of the table during the conversion. For example, if your table is 100 GB make sure you have at least 200+ GB of free disk space.

You should test this out in a non-production environment first to make sure it works for you and to find out exactly how much disk space you need.

The syntax for your case is quite simple.

First do a dry run to verify that it will work:

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=tracking --dry-run

Then execute it:

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=tracking --execute


AWS RDS : Setting Create and other grants only for single Database is not working

RDS only allows you to grant privileges on the schemas you create. System level schemas are blocked as the “master” user only has certain privileges with the with grant option.

That being said the usual statements of GRANT ALL PRIVILEGES and *.* don’t work. You have to grant privileges explicitly by schema to a user and those grants can only be at most the same ones you have as the “master” user.

This article is for creating another “master” user and you can use it as a reference to model the permissions you are trying to setup by removing the privileges you don’t want the user to have.

Additional Master User in AWS RDS


I wouldn't normally prefix the tracking number with a year, instead I would have a separate field with a date in it. This way you can use BIGINT and the indexing would go a little faster than using something like VARCHAR. If having the year in it is better for human reading, then the application can always display those two numbers together. BIGINT can store more than 9 quintillion values, assuming you only use positive numbers. This could mean that depending on your equipment and performance, you might not need to archive but every few years, instead of every year.


Unable to add a new connection to server in PostgreSQL using pgAdmin

It seem like you are connection via termial to port 5432 as command psql on terminal directly connect to default port 5432

Try connecting to database via terminal using below

psql -p 5433

After connected, change user password

alter role tars with password 'tars'; --if role in not present create one with create role tar with superuser;
alter role tars with login;

now go to pgadmin and change the following :

on maintenance db put "postgres"
on user put "tar"
on password put "tar"

and try connecting if still kindly lemme know the below things :

1) Can u list contents of pg_hba.conf and postgresql.conf file ?

2) Also how u changed port number via postgresql.conf or created another cluster ?