IMPORT FOREIGN SCHEMA fails with Password error for 127.0.0.1 but not for remote IP (of same server)

The problem is that when you connect locally, PostgreSQL does not demand a password. So while the user mapping might define a password, that password can't be used as it is never asked for.  If it doesn't ask for a password but can still connect, that probably means it is relying on the identity of the process doing the connecting to authenticate it.  Since the process doing the connecting is owned by a service account which is running PostgreSQL (usually 'postgres'), you are leveraging an identity you don't have a right to leverage. For security reasons, this must be disallowed.
Since you are using a hosted service, you are at the mercy of the host to fix it.  There are several ways they could fix this.  There are no ways you can fix it without their cooperation, other than to stop using their service.

"Allow only SSL connections" using the remote IP fails because I don't have certs

SSL certs are generally going to have the host name, not the host IP address.  Did you try with the host name?  It might be that Google automatically installs their own certs (or that you can talk them into doing so)


How to transition from a statement based log to row based log for Galera replication?

If you have a one-node cluster, add those settings to my.cnf

[mysqld]
binlog_format=ROW
innodb_autoinc_lock_mode=2

Login in a run

mysql> reset master;
mysql> set global innodb_fast_shutdown = 0;

Shutdown mysql

service mysql stop

Go to the folder where the binary logs are located and manually delete the binary logs (should only be one at this point) and also the binary log index file.

Start up MySQL

service mysql start

That's all you need to do.


Oracle SYS Auditing with Audit Trail to DB

This is expected behaviour SYS auditing will always go to the filesystem.

The documentation for AUDIT_FILE_DEST states:

  Oracle Database also writes mandatory auditing information to this
  location, and if the AUDIT_SYS_OPERATIONS initialization parameter is
  *set, writes audit records for user SYS*.

... and the documentation for AUDIT_SYS_OPERATIONS states:

  Enables or disables the auditing of top-level operations directly
  issued by user SYS, and users connecting with SYSDBA or SYSOPER
  privilege. Oracle Database writes the audit records to the audit trail
  of the operating system. If you set the AUDIT_TRAIL initialization
  parameter to XML or XML, EXTENDED, it writes the audit records in XML
  format. On UNIX systems, if you have also set the AUDIT_SYSLOG_LEVEL
  parameter, then it overrides the AUDIT_TRAIL parameter, which writes
  the SYS audit records to the system audit log using the SYSLOG
  utility.

Documentation link here.


Non Clustered Index Scan Properties window - Predicate order different than query order

SQL Server thinks that the dates are more selective because it knows what the value in the Start and End date fields look like.  Because of the trimming on the StudentId column it has no idea how many rows there are.  Once you fix that to not use the trimmed computed column it should work as expected.


ORACLE: Can I get results of "SHOW PARAMETER" into a variable or table?

Query v$parameter instead (or v$system_parameter in the event that you've changed session-level parameters in your current session and want to ignore those changes).  show parameter is just SQL*Plus syntactic sugar on top of those data dictionary views.


For this there is a COMMENT SQL command:
COMMENT ON PROCEDURE whatever IS 'some text'

To retrieve it later use the function obj_description().


You don't have to use Access as a mediator.

You can look into SSIS or import utility to import data from .TPS to SQL. You have to select Flat file data source.

At the end of the import utility, you will have the option to save the package and then schedule it using SQL Agent job.

Refer to : How to Migrate Data From Top Speed Database Files

Also, look into SSMA (SQL Server Migration Assistant). I have used it for Sybase to SQL Migration, but not sure if it will be good fit for TopSpeed migration.


How to change SQL Server master key password?

According to the Microsoft Docs page, syntax is:
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password';

From that docs page:

The REGENERATE option re-creates the database master key and all the keys it protects. The keys are first decrypted with the old master key, and then encrypted with the new master key. This resource-intensive operation should be scheduled during a period of low demand, unless the master key has been compromised.

However, ensure you understand the ramifications of changing the key, and make sure you backup the new key.
Check my recent post for more details.
Some notes:

You're actually not modifying the existing master key with ALTER MASTER KEY REGENERATE. You're really decrypting anything that relies on the key, then dropping the old key and creating an entirely new key, then using that new key to re-encrypt everything that was encrypted previously with the old key.

You need to be a member of the sysadmin group before you can change the master key. Also, changing the master key doesn't decrypt and re-encrypt any TDE-encrypted database, it simply encrypts and re-encrypts certificates that have been encrypted with the key.  Those certificates remain valid and are not modified in any way; simply they are protected with a new master key.

Saving passwords in scripts is always a problem.  I've addressed that in the past by treating the certificate-related scripts as protected, the same way I protect TDE keys. In source code control systems, it's necessary to apply access controls to the scripts to ensure only authorized sysadmins can access them.


First, check the Windows event log. Most backup software logs there. (It's likely taking a whole-OS backup, not just SQL Server.)

Generally, when you see something taking a backup, it's got sysadmin-level permissions. (You can also use the db_backupoperator role, but that's fairly unusual.)

So to catch the thing doing the backups:

Make a list of logins with sysadmin level permissions
Start auditing their login events (like with login triggers, Profiler, Extended Events, etc.)
Correlate the login times with the backup events

And if you're like Evil Admin Brent, change the SA account's password while you're at it. You should probably be rotating it periodically - especially if it's been the same for years, and you've had turnover in the team. If the backup app uses that login, presto, it will stop working. (Of course, so will your backups, so hopefully you have another solution for backups like natives.)


SELECT * WHERE VarCharColumn IN (...) Optimization

SELECT * will invalidate any optimal use of an index (it isn't covering) even if hash is indexed. Your index scan is most likely on the clustered index because of this.

Personally, I'd start with

putting the 3000 search values into a table with an index
Edit: as per Marian's comment, this can be passed in a list or table already
use this in any of JOIN, IN, EXISTS (same plan usually)
ensure my index on MasterUrls suits using Hash and covers col1, col2, col3

Something like

CREATE TABLE #foo (Hash ...)
INSERT #foo...
CREATE INDEX IX_FOO ON #foo (hash)

--either
CREATE NONCLUSTERED INDEX IX_Hash ON MasterUrls (hash) INCLUDE (col1, col2, col3)
--or
CREATE CLUSTERED INDEX IXC_Hash ON MasterUrls (hash)

SELECT col1, col2, col3
FROM [ohb].[dbo].[MasterUrls] M
JOIN
#foo F ON M.Hash = F.Hash


Postgres fetch exact ROW using JSON column

If you want those where the value matches exactly the key/value pair, use = with a jsonb type.

select *
from the_table
where info::jsonb = '{"101": 1}'

As your column is not defined as jsonb (which it should be) you have to cast it before you can compare it.


SQL Server cluster node fail over details

Just to add, your sql agent job could look like this, which works for us (this assumes you have database mail configured properly):
Set the schedule to be when sql server starts as described in Ronaldo's answer
DECLARE @importance AS VARCHAR(6)
DECLARE @body AS NVARCHAR(1000)
DECLARE @Subject  AS NVARCHAR(1000)
DECLARE @InstanceName AS VARCHAR(100)
DECLARE @NodeName AS NVARCHAR(100)
DECLARE @recipientsList VARCHAR(100)

SELECT  @recipientsList ='someuser@mycompany.com;someuser@mycompany.com'
SELECT  @InstanceName =@@SERVERNAME
SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))
SELECT  @Subject  = 'Failover occured for SQL Server Instance '+@InstanceName
SELECT  @body = 'Failover occured for SQL Server Instance '+@InstanceName + 'This instance is currently running on the node '+@NodeName
SELECT  @importance ='High'
EXEC msdb.dbo.sp_send_dbmail
     @profile_name ='Dbas',
     @recipients=@recipientsList,
     @subject = @subject ,
     @body = @body,
     @body_format = 'HTML' ,
     @importance = 'HIGH'


In the original, unedited question you use UNION instead of UNION ALL in your CTE so the DISTINCT is unnecessary. Consider the following query:

DECLARE @liststr VARCHAR(MAX) = '';
WITH COMMA (name)
AS
(
        SELECT 'a'
    UNION ALL
        SELECT 'b'
    UNION ALL
        SELECT 'a'
    UNION ALL
        SELECT 'd'
)
SELECT @liststr = @liststr+',' + name FROM (SELECT DISTINCT name FROM comma) AS comma;
SELECT @liststr as Result;

Here is the query plan for the above:

The sort occurs before the string concatenation, which is what you want. The result is ",a,b,d".

Now consider this query:

DECLARE @liststr VARCHAR(MAX) = '';
WITH COMMA (name)
AS
(
        SELECT 'a'
    UNION ALL
        SELECT 'b'
    UNION ALL
        SELECT 'a'
    UNION ALL
        SELECT 'd'
)
SELECT DISTINCT @liststr = @liststr+',' + name FROM comma;
SELECT @liststr as Result;

Here is the query plan for the above:

The sort occurs after the string concatenation, which is not what you want. The result is ",d".

I don't recommend using either query. I don't know what we are seeing here is defined behavior or if you'll consistently get the result that you want from the first query. It's possible that you'll end up with a query that works now but is broken by a future update of SQL Server. If it's important for you to get consistent results for this query you may want to consider a different method of string concatenation.


Efficient query for an advanced EAV model

First of all, what you are about to design is probably a VERY bad idea. A much better solution would be to have a dynamic schema where you add new tables and have the application understand how to query those table (you could place them in a schema). This largely avoids all the locking and query plan issues you are bound to run into with this model. There is nothing wrong with applications running CREATE TABLE now and again.

Second, I am not sure I understand why you have normalised Parameter into its own table? Why not put that directly into the ManufacturerParameter table.

Third, if you insist on proceeding with your current model, there are ways to achieve what you want (at least if I am interpreting your requirement correctly). What you can do is to write your query in such a way that it is a sums up the search argument when there is a match and then use HAVING to filter out the values that match. I am assuming that only one of the fields Text, Boolean, Datum etc are populated per ProductParameter record (you probably want to enforce this with a constraint)

For example, to search for all products that have a bolean = true for one parameter AND text = 'abc' for some other parameter you can do:

SELECT P.Name
FROM Product P
JOIN ProductParameter PP
WHERE P.ID = Foo
  AND PP.Boolean = 1 OR PP.Text = 'abc'  ... /* For each filter */
GROUP BY P.Name /* And any other things you want out of product */
HAVING COUNT(*) >= [Number of where clauses]

If you need to list all the parameters of this product, you can use the above query template as a nested query and join back to ProductParameter.

The above query CAN be optimised by maintaining a computed column in ProductParameter that has a string representation of the different data types in that table. That way, the above OR statements can be rewritten as an IN list (which you will want to pass as a table valued parameter).

I would like to repeat that what you are doing is probably very wrong. If you do it, you will most likely need to hand tune most of your query plans - the optimiser will not help you anymore. And that is assuming you don't have too many query variants, which will run your plan cache full.


Foreign key constraints check during replication?

Foreign Key Constraints, Check Constraints, IDENTITY columns and Triggers all support the NOT FOR REPLICAION option to control whether they are active when the replication agent updates subscribers.

See Disable Foreign Key Constraints for Replication.


HOW to "SUM" and "JOIN" for single query of a poll results?

Assuming redundant poll_result.poll_id added intentionally to avoid extra join, you can get desired output for a given pool(parameter ':value') with

SELECT a.answer_id, a.answer, COUNT(1)  as num_votes,
IF(b.total = 0,0,100*COUNT(1)/b.total ) as pcnt
FROM poll_answer a
LEFT JOIN poll_results b ON (b.answer_id = a.answer_id)
INNER JOIN (select count(1) as total from poll_results WHERE poll_id = :value)b on (1=1)
WHERE a.poll_id = :value
GROUP BY a.answer_id, a.answer
-- I know mysql is ok with just GROUP BY a.answer_id, but just to make it more standard,
-- I use GROUP BY a.answer_id, a.answer


HA SQL AlwaysOn backup using Copy Only + Logs from secondary replica

Im actually using a similar setup with 1TB of the database.

  Has anyone had a similar issue and resolved it with either option, confirming viability of the option for us?

Im using backup on secondary only.Even in few outages, I tried to use copy only full backups and log backups to recover them.

  No issues that using copy only backups.

The above statement will give a clarity for your 2 and 3'rd questions.


DB2 Express on Windows 7 - Disable security?

The short answer to this problem is that you need to set the DB2 registry variable DB2_GRP_LOOKUP to local.

As a local administrator on your PC (i.e. not using your domain account), start a DB2 Command Window and issue the following:

db2set DB2_GRP_LOOKUP=local
db2stop
db2start

The reason this occurs has to do with how DB2 enumerates which groups a user belongs to upon successful authentication.  In a Windows Domain environment, the default group enumeration behavior is to ask the system that authenticated the user:  If a user is authenticated by the local machine, DB2 asks the local machine what groups belongs to; if the user is authenticated by the domain, DB2 will ask the domain what groups the user belongs to - and it will not ask the local machine.

So, when you connect to DB2 using your domain ID, DB2 asks the domain controller what groups you belong to, so it does not see that your ID belongs to the Administrators group on your local PC.

When you set DB2_GRP_LOOKUP=local, this modifies this behavior:  It tells DB2 to always enumerate groups from the local machine, regardless of whether the user was authenticated by the domain or the local machine.

After doing this, DB2 will see that your Domain ID belongs to the local Administrators group, and you'll end up with full SYSADM privileges (and the ability to create a database).


Most efficient way to perform table operations (spec. drop) on tables having table names that match a certain criteria

Why does the code need to be more efficient?  It seems unlikely that you would be dropping large numbers of tables frequently or that it would take enough time to drop tables that it would be worth optimizing.

What trade-offs are you willing to make to cause the code to be more efficient?  And what do you mean by "efficient" in this context-- wall clock time?  The resources consumed on the server?  Something else? The code that you have written is very straightforward and easy to follow.  You could complicate things by, for example, submitting multiple jobs that each drop a subset of the tables so that you can have multiple threads working simultaneously.  But that would involve making the code much more complicated-- you'd need to assign work to different threads and then coordinate the responses from each thread to verify that all the tables were dropped successfully.  It seems most unlikely that this would be a trade-off that you would really want to make though it would reduce the wall clock time needed to drop all the tables.


A NOMOUNT instance stays in BLOCKED status with dynamic registration. You need to use static registration to connect a NOMOUNT instance remotely.

Add the below to listener.ora and restart the listener:

SID_LIST_LISTENER=
(SID_LIST=
 (SID_DESC=
  (GLOBAL_DBNAME=ORCL)
  (SID_NAME=ORCL)
  (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
 )
)

Of course, change the values of variables accordingly to your environment.


MongoDB 3.4 Server not starting with bind error following a CentOS 7.4 security update

@smile2day, It's pretty clear from your error log

  E NETWORK  [initandlisten] listen(): bind() failed Cannot assign
  requested address for socket: xxx.xxx.xxx.xxx:27017

There is MongoDB 3.4 Server startup problem resist through bind() of IP, not from your  CentOS update.

May be you have mistake of configuration of code. As i am able to see here in one straight line you are writing the bindIp with port number.

As MongoDB BOL documented
Here should be separated in systemLog of mongod settings like such as

net:
   bindIp: 127.0.0.1
   port: 27017


Best database system to store and compare date ranges

PostgreSQL has the datatypes daterange, tsrange (timestamp range) and tstzrange (time stamp with time zone range), along with a number of operators and functions to process, cast and extract information from them.

It also has EXCLUSION constraints, which can be used to ensure no two rows have overlapping ranges. E.g.

CREATE TABLE reservation (
  room_id int not null,
  customer_id int not null,
  during tsrange not null,
  EXCLUDE USING gist (room_id, during WITH &&)
);

The above table would not allow the same room having overlapping reservations.


Multiple "GROUP_CONCAT"s with WHERE clause

SELECT type,
       GROUP_CONCAT( CASE WHEN info = 'yes' THEN name ELSE NULL END
                     ORDER BY id ASC SEPARATOR ' ') AS list_with_info,
       GROUP_CONCAT( CASE WHEN info = 'no' THEN name ELSE NULL END
                     ORDER BY id ASC SEPARATOR ' ') AS list_without_info
FROM table1
GROUP BY type ;

Tested at SQL-Fiddle: test-1

If you wanted the results in two rows instead of one, it would be easier, just GROUP BY both type, info:

SELECT
    type, info,
    GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
      AS list
FROM table1
GROUP BY type
       , info ;

This can also be used to provide the one-row-per-type format you want:

SELECT
    type,
    MIN( CASE WHEN info = 'yes' THEN list END )
      AS list_with_info,
    MIN( CASE WHEN info = 'no' THEN list END )
      AS list_without_info
FROM
    ( SELECT
          type, info,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list
      FROM table1
      GROUP BY type
             , info
    ) AS grp
GROUP BY type ;

Tested at SQL-Fiddle: test-2

The above two queries would benefit from an index on (type, info, name)

The following would benefit from an index on (info, type, name):

SELECT
    dt.type,
    grpy.list  AS list_with_info,
    grpn.list  AS list_without_info
FROM
    ( SELECT DISTINCT type
      FROM table1
    ) AS dt
  LEFT JOIN
    ( SELECT
          type,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list
      FROM table1
      WHERE info = 'yes'
      GROUP BY type
    ) AS grpy
      ON grpy.type = dt.type
  LEFT JOIN
    ( SELECT
          type,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list
      FROM table1
      WHERE info = 'no'
      GROUP BY type
    ) AS grpn
      ON grpn.type = dt.type ;

Tested at SQL-Fiddle: test-3


postgresql materialized views vs versioning

You can create a function with security definer owned by the MVIEW owner that does the refresh and then grant execute privilege on the function (or procedure) to your app user.


It is possible to use an In-Memory Table with the SQLBulkCopy class. I'm not aware of any additional gotchas but did come across this Git Issue in terms of ensuring you properly configure the options of it (depending on the size of your table).
You guys should more importantly understand the differences between an In-Memory Table vs a regular Table, specifically with regards to locks and retry logic for In-Memory Tables being different, which may be important to consider and cause you to make additional changes to your application.
Additional resources for In-Memory Tables:

Microsoft Docs - Memory Optimized Tables

Microsoft Docs - Transactions with Memory Optimized Tables


Adding a nullable column concurrently with an update or insert

Referencing the documentation for ALTER TABLE (highlighting mine),

  The changes specified in ALTER TABLE are implemented immediately. If
  the changes require modifications of the rows in the table, ALTER
  TABLE updates the rows. ALTER TABLE acquires a schema modify (SCH-M)
  lock on the table to make sure that no other connections reference
  even the metadata for the table during the change, except online index
  operations that require a very short SCH-M lock at the end. In an
  ALTER TABLE…SWITCH operation, the lock is acquired on both the source
  and target tables. The modifications made to the table are logged and
  fully recoverable. Changes that affect all the rows in very large
  tables, such as dropping a column or, on some editions of SQL Server,
  adding a NOT NULL column with a default value, can take a long time to
  complete and generate many log records. These ALTER TABLE statements
  should be executed with the same care as any INSERT, UPDATE, or DELETE
  statement that affects many rows.

Also, Paul Randal has some good information about what happens when you add columns (and how long the operation might take) in Misconceptions around adding columns to a table.  To summarize some main points:

New column is nullable, with a NULL default. The table’s metadata
records the fact that the new column exists but may not be in the
record. This is why the null bitmap also has a count of the number of
columns in that particular record. SQL Server can work out whether a
column is present in the record or not. So – this is NOT a
size-of-data operation – the existing table records are not updated
when the new column is added. The records will be updated only when
they are updated for some other operation.
New column is nullable, with a non-NULL default. It depends which
version of SQL Server you’re using:

Before SQL Server 2012: This IS a size-of-data operation. The
non-NULL default forces all existing records to be updated when the
column is added, and so the null bitmap will be updated too.
SQL Server 2012 onward: same behavior as for a NULL default nullable
column (i.e. metadata only operation)

New column is not-nullable (obviously with a non-NULL default). This
IS a size-of-data operation.


Nope.  You'll have to hold off on Monitoring with the 3rd party tools until they release new builds which support the new versions.  The good news is that they are all smallish companies so they can release new builds pretty quickly.  I'd recommend contacting their support departments and see if there's a new build available, maybe even a BETA that you can try out.

I feel you're pain, I've got a client with SQL 2014 in production and Spotlight can't monitor it for them.


Database VIEW with a dynamically changing filter

You can't do what you want with a view.  You have to use a function.  Note that I didn't retain all your double quoting of identifiers, so you will have to put them back if you insist.

CREATE OR REPLACE FUNCTION public.calls_view(timestamp with time zone, timestamp with time zone)
 RETURNS SETOF calls
 LANGUAGE sql
AS $function$
SELECT *
FROM Calls
WHERE CustomerName IN (SELECT CustomerName
                         FROM Calls
                         WHERE Quality='Bad' and timestamp between $1 and $2
                         GROUP BY CustomerName
                         ORDER BY COUNT(*) DESC LIMIT 5)
  AND Quality='Bad' and timestamp between $1 and $2
$function$

You can use it kind of like a view, but you must supply your parameters in parentheses, not a WHERE clause:

select * from calls_view(now() - interval '1 day', now());


Multiple SQL Server data files on same SAN disk

Even multiple tempdb files on a single LUN should be a last resort compared to one tempdb per LUN.
If there are multiple database files on a LUN, and they are allowed to auto-grow, their contents will be interleaved in the filesystem and on the storage.  Hit them both heavily with read or write requests, and a situation known as IO weaving, or disk head thrashing will result.  Even though writes are generally cached, writes are not immune to io weaving, because eventually the writes need to destage from cache.
If tempdb is used heavily by query spill, there will be a lot of sequential write activity.  Put multiple tempdb on the same LUN, and there can be multiple streams of sequential write activity at different locations on the storage, all accessed through the same LUN queue.  Sequential reads such as a table scan introduced on top can encounter worse-than-random read latencies.


Access to databases in SQL Server is managed mainly through two principals:

Logins - Security principal that allows access to the instance.
Users - Security principal linked to a login that allows access to a specific database.

By default, SQL Server follows the practice of least privilege, meaing that when you create these principals (further documentation on that here), they have minimal access to a database and the objects within that database.  Initially, these principals will only be able to connect to an instance or database but nothing else, such as selecting or modifying data.

This means that you, as an administrator, will need to GRANT permissions to principals that need access.  For example, if you had a table and you wanted to give a user the ability to read data on that table:

GRANT SELECT ON table_foo TO user_bar;

Once this is in place, your user can select from table_foo, but do nothing else.  You can further grant additional permissions to other users as necessary, or REVOKE/DENY permissions as well.  See here for further explanation of this.

Finally, you can leverage roles to additionally manage your security.  Roles are essentially containers that have permissions granted to them and you can assign users to these roles, much like Active Directory groups.  SQL Server has several server and database roles that are available by default and are commonly used, but you can also create custom database level roles and assign specific permissions to those roles.

Overall, database security is a large topic that is outside the scope of a StackExchange answer, but the basic idea is you can manage a "whitelist" by assigning specific permissions on your database objects and tightly controlling the security around those objects.


In Oracle 12c Mixed Mode is enabled by default which makes the use familiar with Unified Auditing prior to switching to full Unified Auditing. Mixed Mode allows to pre-12c audit functionality to co-exist with new Unified Auditing functionality.

Using Mixed Mode audit data can be found both in the traditional locations as well as in SYS.UNIFIED_AUDIT_TRAIL.  This is because the Unified Auditing Policy ORA_SECURECONFIG is enabled by default.

You can use the following commands to disable Unified Audit.

NOAUDIT POLICY ORA_SECURECONFIG;
noaudit policy ORA_LOGON_FAILURES;

Unified Auditing is not included in the Oracle 12c kernel so you have enable to manually. But in Mixed Mode it is on though it is not included in the kernel.
You can check whether the Unified Auditing is enabled or not using the following query.

select VALUE from V$OPTION where PARAMETER='Unified Auditing';
If you wish to linked the Unified Auditing to kernel then use the following commands.

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

If you dont set AUDIT_TRAIL=NONE  then both auditing mechanisms will run concurrently.

Reference

Link to Oracle Official Documentation.

Mixed Mode Auditing


MySQL - The DBMS must maintain a separate execution domain for each executing process

I guess the answer depends on how you define the term "process".

MySQL is a single-process DBMS; each connection runs as a separate thread, but they share the same process address space. Subsequently, there's nothing that prevents one thread from accessing another thread stack, for example.

In the spirit of the rule text you quote MySQL is "not capable of maintaining a separate execution domain for each executing process" -- if by "process" you understand a client connection.

However, if you stick to the letter of the rule, since there's only one DBMS process at the OS level, no isolation is necessary apart from the process isolation provided by the underlying operating system.


Getting table definitions on the command line in MySQL

you need to add semicolon at the end of your command.

     show databases;

     show tables;


How restore from .gz backup with new database name using mongorestore r3.2.9?

Nothing works for me but this.

mongorestore --gzip --archive=/path/to/file.gz --db db_name


How to edit `postgresql.conf` with pgAdmin 4?

Workaround: ALTER SYSTEM

As a partial workaround, Postgres 9.4 added a feature to dynamically set the properties seen in postgresql.conf: ALTER SYSTEM commands.

ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }

ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL  -- Clears all the settings set via `ALTER SYSTEM SET`.

This provides a convenient alternative to manually editing the postgresql.conf file.

Example:

ALTER SYSTEM SET wal_level = replica;

How does it work?

A postgresql.auto.conf file is written by the ALTER SYSTEM SET command.
This file and postgresql.conf are both read, so you can combine these approaches if desired. The auto file trumps the hand-edited file for coinciding properties.
For reliability, a postgresql.auto.conf.temp file is created to rollback to the original state in case of error.

Caveats:

Settings do not take effect immediately. Read the doc.
Must be superuser to execute.
Cannot be called in a transaction block or function.
Cannot set data_directory.
Obviously misuse can be dangerous to your database system, so be careful.

Tip: To read the value of the settings, see this Question, Query for all the Postgres configuration parameters‘ current values?

Workaround: Use command-line editor as postgres user

You can run a simple text-editor like nano from the command-line run with the privileges of the postgres user. Open the pg_hba.conf file, edit, and save.

For example, on a Unix/POSIX-oriented OS such as BSD or macOS:

sudo -u postgres nano /Library/PostgreSQL/11/data/pg_hba.conf

For more info, see the Question, Switch user to 'postgres' user on macOS results in “su: Sorry” error


Personally when i got started out with SSAS the best thing i found to do was run through the examples using Adventureworks. The tutorials are here: http://msdn.microsoft.com/en-us/library/ms170208(v=sql.110).aspx You may need to use the dropdown to select the version of sql server you intend to use.

The pre-reqs take you through getting sample data installed. They're actually very good tutorials to follow along with. I went through them when 2005 was the latest version so can't necessarily vouch for the more recent editions but i'm sure they're of equal quality.

Thanks,

Simon


MySql replication with Raw Data and InnoDB tables

When you get 'Unknown table engine INNODB', it means something when wrong with startup. If you look in your error logs, you should be able to get more information.

My first instinct is that your ib_logfile is a different size on your master than what is configured in the slave. If you stop the slave server, delete the ib_logfile from the snapshot when setting up the slave, then restart mysql, it should correct itself.

NOTE: Do not delete the ibdata file! Only the ib_logfiles


Populate a column using the values of other ones

Just create a single function that handles all columns you want to update. Essentially the same as you already do with X and Y:

CREATE OR REPLACE FUNCTION update_edificacao()
  RETURNS TRIGGER AS
    $$
    BEGIN

      NEW.X := round(ST_X(ST_Centroid(NEW.geom))::numeric,3);
      NEW.Y := round(ST_Y(ST_Centroid(NEW.geom))::numeric,3);
      NEW.area := round(ST_Area(NEW.geom):: numeric,2);

      -- the following was taken from your other question
      NEW.codigo := concat_ws('.', new."PP", new."MM", new."DD", new."BB", new."QQ", new."LL", new."ID", new."UU");

      -- if you have more columns to deal with, add them here
      NEW.some_other_column := ....;
      NEW.yet_another_column := ....;

      RETURN NEW;

    END;
    $$
  LANGUAGE plpgsql;

Then create one trigger that calls the function that handles all columns:

CREATE TRIGGER update_en
  BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4"
  FOR EACH ROW
  EXECUTE PROCEDURE update_edificacao();

And drop all other trigger functions and triggers.


From the Installation Guide ( here )

  Oracle Database XE limits itself automatically to two cores for processing. For example, on a computer with 2 dual-core CPUs (four cores), if a large number of database clients try to simultaneously execute CPU-intensive queries, then Oracle Database XE will process the queries at the rate of just two cores even if more CPU capacity is available.

I would image "large number" means ">2".


Can someone decipher this deadlock for me?

2 Types of deadlocks.
    1. true cycle in the waits-for graph
    2. waits-for graph that is too expensive to check for cycles.

If InnoDB has to check more than a million locks in a graph, or if it recurses
through more than 200 transactions while checking, it gives up and says there's a deadlock. These numbers are hardcoded constants and cannot be configured (although recompile is possible).

The last line suggests Transaction 1 was chosen as a deadlock victim and was rolled back.

Thus when you re-run the transaction that was rolled back, it might have to wait for other transactions to complete, but typically the deadlock does not recur. Please try...


Inserts via Entity Framework on server take about double the time than locally

All of the servers are more powerful than my local machine, so it most
  likely is not a hardware problem.

This is a common misconception.

Single-row inserts, like EF generates, are not expensive in CPU cycles.  Typically bottleneck is writing to the log file.  Your local machine may well give you better log write performance than a powerful server.  Your local machine may have a fast, local SSD.  Or you may have write caching enabled on your local disk.

  What could be the bottleneck here?

My guess is log writes.  If you commit after each row, then this will be much worse.  In EF each call to SaveChanges() uses a separate transaction, so you want to batch 10s to 100s of rows in each call.  You also don't want to have too many rows added to a DbContext instance, as they are all tracked for the lifetime of the DbContext.

But more generally, SQL Server will tell you where the bottleneck is.  You can look at the session wait stats for the session performing the insert in SQL 2016+:

select *
from sys.dm_exec_session_wait_stats
where session_id = 51
order by wait_time_ms desc

Or you can look at the server-wide wait stats while the import is running:

https://blogs.msdn.microsoft.com/dbrowne/2013/01/18/my-favorite-query-for-investigating-sql-server-performance/


Look at

SELECT DISTINCT SiteUserID
FROM SiteUserBroker

EXCEPT

SELECT t1.SiteUserID
FROM SiteUserBroker t1
LEFT JOIN SiteUserBroker t2 ON  t1.BrokerID = t2.BrokerID
                            AND t2.SiteUserID = @SiteUserID
WHERE t2.SiteUserID IS NULL


Why is our SQL Server instance writing a mystery text file to our C: drive?

Thank to the comment from Martin Smith above, I was able to determine that the C:\SQL.LOG file was being created by ODBC Tracing that had been started and forgotten by a developer.


Why are some tables in PostgreSQL available from all databases in my server?

This quote from the manual should help to lift the fog. From "Managing Databases" - "Overview":

  A database is a named collection of SQL objects (“database objects”).
  Generally, every database object (tables, functions, etc.) belongs to
  one and only one database. (However there are a few system catalogs,
  for example pg_database, that belong to a whole cluster and are
  accessible from each database within the cluster.) More accurately, a
  database is a collection of schemas and the schemas contain the
  tables, functions, etc. So the full hierarchy is: server, database,
  schema, table (or some other kind of object, such as a function).

Bold emphasis mine.

So there are some tables that are visible in every database of the same cluster. But only a few system catalogs like pg_database. Most other system catalogs, also present in each database, are separate instances, sharing the same name.

Nothing keeps you from creating tables of the same name in distinct databases or distinct schemas within the same database. If you have created tables (or other objects) in the template1 database of your cluster, one copy is added to every new database automatically. All of these are distinct tables regardless.

Finally, "server" is a fuzzy term. Might refer to a single DB cluster or to a system with several PostgreSQL DB clusters (listening to distinct ports) ... Use accurate terminology to avoid confusion.


How can I migrate a sybase database to mysql?

Having no experience with Sybase, I suppose I would start by exporting the database schema into a text file, then go through and modify the CREATE TABLE statements manually until I am able to load the schema into MySQL.

Next, dump the data from each sybase table into CSV format file and then in the MySQL database, use LOAD DATA INFILE.

Depending on your database size, this could be a painfully slow option, but it is 'easy'.


How to optimize mysql table of 2 billions rows?

Use InnoDB.
Normalize entityType or turn it into an ENUM.
Change INT UNSIGNED (always 4 bytes) into a suitable sized int)
If the 3-column key is unique, then make it the PRIMARY KEY and toss id
id, if kept, will need to be BIGINT UNSIGNED -- you are getting too close to the 4 billion limit of INT UNSIGNED.
The optimal order for the index:  (entityId, entityType, timeStamp), or possibly (entityId, timeStamp)
Minimize the number of indexes on a DataWarehouse table.
For purging data after a year, DELETE will be painful.  Instead PARTITION BY RANGE and use months:  http://mysql.rjweb.org/doc.php/partitionmaint
If you get in trouble with the ingestion rate: http://mysql.rjweb.org/doc.php/staging_table
If you keep id, then do

this trick:

PRIMARY KEY (entityId, timeStamp, id)
INDEX(id)

This, together with InnoDB, makes the data for that SELECT mostly 'clustered', hence faster to fetch.

But the big fix to that query, and similar "report" queries in a Data Warehouse (which what you have), comes from building and maintaining Summary table(s):  http://mysql.rjweb.org/doc.php/summarytables

The summary table to support that would look something like:

CREATE TABLE summary (
    entityType VARCHAR... -- denormalized
    dy  -- timestamp truncated to the day
    entityId
    sumCount  -- SUM(entityCount); use SUM(sumCount) to get weekly total (etc)
    PRIMARY KEY(entityid, entityType, dy)

See the link for various ways to keep the summary table updated.

addenda

Munch on what I said.  Then think about how it splitting into multiple similar tables based on entityType.

One simplicity -- count is always "1" for some Types, so you don't need a count column (except in the Summary table for that Type).


Use MySQL to regularly do multi-way joins on 100+ GB tables?

Have you tried piling more data and benchmarking it? 100K rows is inconsequential. Try 250M or 500M like you're expecting you'll need to handle and see where the bottlenecks are.

An RDBMS can do a lot of things if you pay careful attention to the limitations and try and work with the strengths of the system. They're exceptionally good at some things, and terrible at others, so you will need to experiment to be sure it's the right fit.

For some batch processing jobs, you really cannot beat flat files, loading the data into RAM, smashing it around using a series of loops and temporary variables, and dumping out the results. MySQL will never, ever be able to match that sort of speed, but if tuned properly and used correctly it can come within an order of magnitude.

What you'll want to do is investigate how your data can be partitioned. Do you have one big set of data with too much in the way of cross-links to be able to split it up, or are there natural places to partition it? If you can partition it you won't have one table with a whole pile of rows, but potentially many significantly smaller ones. Smaller tables, with much smaller indexes, tend to perform better.

From a hardware perspective, you'll need to test to see how your platform performs. Sometimes memory is essential. Other times it's disk I/O. It really depends on what you're doing with the data. You'll need to pay close attention to your CPU usage and look for high levels of IO wait to know where the problem lies.

Whenever possible, split your data across multiple systems. You can use MySQL Cluster if you're feeling brave, or simply spin up many independent instances of MySQL where each stores an arbitrary portion of the complete data set using some partitioning scheme that makes sense.


How to send two query into one? Is there any other alternative?

For MySQL (as the question tagged):

SELECT SUM(status='failed') AS failed_orders, COUNT(1) AS total_orders
FROM result
WHERE order = '4373'
-- GROUP BY status -- ??

For another DBMS (for example, for Microsoft SQL Server):

SELECT COUNT(CASE WHEN status='failed' THEN 1 END) AS failed_orders, COUNT(1) AS total_orders
FROM result
WHERE order = '4373'


"Alter Table" modify multiple columns vs multiple Alter table single column in INNODB?

You must do it all in a single ALTER TABLE command. Think of it : You are changing the collation of a column, so ALGORITHM=COPY must be done. This requires a full table lock. It is better to execute the collation change of multiple columns in a single ALTER TABLE command than to do it multiple time
There is no drawback in this instance. Why ? Back in May 12, 2011, I wrote about MySQL 5.0's old behavior in executing multiple column modifications (Does MySQL still handle indexes in this way?). Under the hood, MySQL used to execute multiple column changes as individual ALTER TABLE commands. I pointed this out to MySQL back on Oct 10, 2006. They had fixed it since.
Now that ALTER TABLE handles multiple column changes as a single operation, there is no need to change the columns one by one.
Let's say it takes 5 min to change one column. That's 5 minutes the table is locked and no INSERTs, UPDATEs, or DELETEs can happen. You would basically double the time changing two columns to 10 min, triple the time changing three columns to 15 min, and so forth.
Just run the one ALTER TABLE command. MySQL handles it correctly now.
ALTER TABLE mydb.`cwd_group`
    MODIFY `active` char(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
   ,MODIFY `local`  char(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
;

GIVE IT A TRY !!!
P.S. You need to specify a length for char. That's why I put (25).
If you are not sure of the length, then use VARCHAR(25).


When dedicated server process model should be used instead of shared process model?

1) One Tom Kyte maintains that with shared server, the only performance that will surely increase is processing of CONNECTs. For the rest of statements, shared server is inherently a bit slower, unless it happens that pooling/multiplexing is sufficient to maintain advantage.

2) Dedicated is simpler - it has less components. Since much of administrator's job is investigating strange behaviors, tracing, analyzing... simpler means better.

3) Most importantly, everybody uses dedicated, and a best approach in case of any "enterprise-level" software is to take a path more traveled by - my personal opinion. A side-effect would be when contacting Oracle support with problems, you may receive more often the advice to "try again using dedicated server instead" than "well, why don't you try it using shared server instead". So it's one less thing to discuss with those beautiful people.


Stored Procedure to return dynamically created table data

Based on comments from people in chat, I decided to change my script slightly to INSERT INTO a temp table instead of creating one long SQL statement to execute at the end. So in the end my stored procedure contains the following:

create table #SurveyData
(
    tableName varchar(50),
    columnName varchar(50),
    columnId int,
    rownum int
)

create table #results
(
    SurveyId int,
    InstanceId int,
    QuestionNumber int,
    Response varchar(1000)
)

-- insert the survey table structures for use
insert into #SurveyData (tableName, columnName, columnId, rownum)
select tables1.name, cols1.name, column_id, ROW_NUMBER() over(order by tables1.name, column_id)
from sys.all_columns cols1
inner join
(
    SELECT *
    FROM sys.all_objects
    WHERE type = 'U'
    AND upper(name) like 'LIBRARY%'
) Tables1
    ON cols1.object_id = tables1.object_id
WHERE cols1.name Like 'Q_%'
ORDER BY tables1.name, column_id;

declare @sql varchar(max) = '';
declare @RowCount int = 1;
declare @TotalRecords int = (SELECT COUNT(*) FROM #SurveyData);

Declare @TableName varchar(50) = '';
Declare @ColumnName varchar(50) = '';

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM #SurveyData
        WHERE @RowCount = rownum

        SET @sql = 'INSERT INTO #results ' +
                    ' SELECT s.SurveyId
                        , s.InstanceId
                        , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
                        , Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
                    FROM #SurveyData t
                    INNER JOIN ' + @TableName + ' s' +
                    ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                    ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

        SET @RowCount = @RowCount + 1
    END

    SELECT SurveyId, InstanceId, QuestionNumber, Response
    FROM #results

drop table #SurveyData
drop table #results

See SQL Fiddle with the final script


I don't think you reset two specific status variables.

You can only reset all of them using FLUSH STATUS;

  This option adds the current thread's session status variable values to the global values and resets the session values to zero. Some global variables may be reset to zero as well. It also resets the counters for key caches (default and named) to zero and sets Max_used_connections to the current number of open connections. This information may be of use when debugging a query. See Section 1.7, “How to Report Bugs or Problems”.

Try running FLUSH STATUS; and see if this works.


How do I Delete A Database And Log Files With SQL 2008 Script?

If you click on the 'Script' in the execute box that pops up from the delete button, it reveals that the script actually uses a DROP command:

USE [DATABASE]
GO
/****** Object:  Table [dbo].[TABLE]    Script Date: 3/6/2014 9:29:39 AM ******/
DROP TABLE [dbo].[TABLE]
GO


How to check that stored procedure is valid

According to the MSDN article - Deferred Name Resolution and Compilation only the first situation is working, second is not:
"Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table."

For checking this kind of situations, I'd use a database project in Visual Studio with automatic (daily) deploy and verification of all warnings (maybe treat warning as errors), because these issues would be warnings, not errors.


Upserting data with existing many-to-many connections

One way could be using returning  clause with cte to make all upserts in one statement. For illustration purposes I use 1 many-to-many relationship.

--- test tables with m2m
create table test1(test1_id bigserial not null primary key, uq_value1 text ,constraint unique_uq_val1 unique (uq_value1));

create table test2(test2_id bigserial not null primary key, uq_value2 text ,constraint unique_uq_val2 unique (uq_value2));

create table test1_test2 (test1_id bigint not null, test2_id bigint not null,
primary key (test1_id, test2_id),
constraint fk_test1  foreign key (test1_id) references test1(test1_id ),
constraint fk_test2  foreign key (test2_id) references test2(test2_id )
);
------------------------------------
-- insert value into the first table , or do a dummy update if it's already there
with
ins1 as
(insert  into test1(uq_value1) values('foo') on conflict on constraint unique_uq_val1 do update
    set  uq_value1=test1.uq_value1
returning test1_id
)
,
-- insert value into the second table , or do a dummy update if it's already there
ins2 as
(insert  into test2(uq_value2) values('bar') on conflict on constraint unique_uq_val2  do update
    set  uq_value2=test2.uq_value2
returning test2_id
)
,
-- select PK of inserted records (since there is exactly one record in each insert,
-- cross join is used
sel_1 as
(
select test1_id,test2_id
from ins1
cross join ins2
)
-- finally insert into link tables if such a record doesn't exist :
insert into test1_test2(test1_id, test2_id)
select * from sel_1  a
where not exists( select null from test1_test2 b where (b.test1_id, b.test2_id) = (a.test1_id, a.test2_id))


These tables are related to Oracle Advanced Replication.

Although it is not advisable to drop them, you might just be fine if you are not using any Advanced Replication features.

Related :  DBMS_REPCAT package


How to handle temporary tables with mutliple connections?

A temporary table is only visible to the connection which created it. There could be 300 temporary tables, all by the same name, and non interrupting or conflicting with each other. In fact, you cannot access temporary tables created by other connections.

So when you query, you get results from the temporary table created by the same connection.

If you close a connection, the temporary table gets dropped. So, moral of the story, create a temporary table then query it, all within the same connection.


Improve performance for order by with columns from many tables

Problem

This is a more complex problem than is obvious on a quick glance. You are sorting by two columns, each from a different table, while you join on two other columns. This makes it impossible for Postgres to use the provided indexes and it has to default to (very) expensive sequential scans. Here is a related case on dba.SE:

Can spatial index help a “range - order by - limit” query

Indexes

You need two indexes for best performance, both of which are already present:

CREATE INDEX idx_samp_barcode_code ON public.samples (barcode, code);
CREATE INDEX requests_sample_code_idx ON public.requests (sample_code, code);

But the plain query you have can not make use of them, not even in pg 9.4.

Query

Postgres 8.4 is quite a hurdle to take. But I think I found a way with a recursive CTE:

WITH RECURSIVE cte AS (
   ( -- all parentheses are required
   SELECT r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
   FROM  (
      SELECT s.barcode
      FROM   samples s
      -- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)
      ORDER  BY s.barcode
      LIMIT  1  -- get smallest barcode to start with
      ) s0
   JOIN   samples  s USING (barcode)  -- join all samples with same barcode
   JOIN   requests r ON r.sample_code = s.code
   ORDER  BY r.code  -- start with ordered list
   )

   UNION ALL
   (
   SELECT r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
   FROM  (
      SELECT s.barcode
      FROM   cte c
      JOIN   samples s ON s.barcode > c.barcode
      -- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)
      ORDER  BY s.barcode
      LIMIT  1  -- get next higher barcode
      ) s0
   JOIN   samples  s USING (barcode)  -- join all samples with same barcode
   JOIN   requests r ON r.sample_code = s.code
   ORDER  BY r.code  -- again, ordered list
   )
   )
TABLE cte
LIMIT 21;

Tested and works for me in pg 9.4. It uses the indexes (partly in index-only scans in pg 9.2+).

pg 8.4 already has recursive CTEs. The rest is basic SQL (even TABLE is available in pg 8.4 (and can be replaced with SELECT * FROM). I hope there are no restrictions to spoil the party, I don't have an installation of pg 8.4 any more.

Explanation

The commented parts:

-- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)

would be needed if there could be samples with no requests at all, which you ruled out in a comment.

The query relies on this documented behavior of recursive CTEs:

  Tip: The recursive query evaluation algorithm produces its output in
  breadth-first search order.

Bold emphasis mine. And on this, too:

  This works because PostgreSQL's implementation evaluates only as many
  rows of a WITH query as are actually fetched by the parent query.
  Using this trick in production is not recommended, because other
  systems might work differently. Also, it usually won't work if you
  make the outer query sort the recursive query's results or join them
  to some other table.

Bold emphasis mine. So, it's only guaranteed to work in PostgreSQL and only if you do not add another ORDER BY in the outer query.

This is fast for a (relatively) small LIMIT in the outer query. For the case at hand, it should be very fast. The query would be no good for a large LIMIT.

PL/pgSQL function

The other option I can think of is a procedural solution with plpgsql. Should be a bit faster yet, especially for repeated calls:

CREATE OR REPLACE FUNCTION f_demo(_limit int)
  RETURNS TABLE (code int8, test_code int, sample_code int8
               , barcode varchar, registry_date timestamptz) AS
$func$
DECLARE
   _barcode text;
   _n       int;
   _rest    int := _limit;  -- init with _limit parameter
BEGIN
   FOR _barcode IN
      SELECT DISTINCT s.barcode
      FROM   samples s
      -- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)
      ORDER  BY s.barcode

   LOOP
      RETURN QUERY
      SELECT r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
      FROM   samples  s
      JOIN   requests r ON r.sample_code = s.code
      WHERE  s.barcode = _barcode
      ORDER  BY r.code
      LIMIT  _limit;

      GET DIAGNOSTICS _n = ROW_COUNT;
      _rest := _rest - _n;
      EXIT WHEN _rest < 1;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM f_demo(21);

MATERIALIZED VIEW

A large OFFSET has a similar effect as a large LIMIT. While all the skipped rows don't add to I/O, they still have to be computed to determine the first rows after the offset. If you need that, use a MATERIALIZED VIEW with an added row number and an index on that.

Postgres 9.3+ has a built-in feature, with your outdated software you have to knit the solution by hand. It's not that complicated, though. Basically a snapshot- table thats populated from a pre-defined SELECT statement or VIEW, like. Basically, create the table as:

CREATE TABLE req_sample AS
SELECT row_number() OVER (ORDER BY s.barcode, r.code) AS rn
     , r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
FROM   requests r
JOIN   samples s on (s.code = r.sample_code)
ORDER  by s.barcode, r.code;

ALTER TABLE req_sample ADD CONSTRAINT req_sample_rk PRIMARY KEY (code);
CREATE INDEX foo ON  req_sample (rn);

Save that SELECT as function, view or the plain query text. Refresh (expensive) in one transaction:

BEGIN;
-- drop PK & index
TRUNCATE req_sample;
INSERT INTO req_sample SELECT ... ;
-- add PK & index
COMMIT;

Redundant barcode column

The MV gets more expensive if underlying tables change a lot and you need "current" results. There's one more halfway cheap option: store the barcode in the requests table redundantly. You could include it as 2nd column in the FK constraint to samples ("Requests_S_fk") and make that ON UPDATE CASCADE to avoid outdated / conflicting data. Then you can work with an index on (barcode, code) in requests.

For the last two options you can then simplify pagination with directly applicable indexes with a WHERE clause on values from the previous page replacing the OFFSET. Consider this presentation about "Pagination done the PostgreSQL way" on use-the-index-luke.com.


Adding secondary replica from different subnet for AlwaysON

IP address for each subnet. Documented here: https://msdn.microsoft.com/en-us/library/hh213080.aspx#Prerequisites


Backing up mongo database of about 120 GB size

Answers originally left in comments:

Compressed size will depend on how compressible your data patterns inside your documents and collections are.  Mongo isn't going to backup the indexes so subtract whatever indexes you have from that 120GB and that's what it will backup, along with any profiling data you have saved in the DB itself.  There's a lot to consider when backing up mongo. V3 and 2.8 backup methods are similar. – Ali Razeghi

If you're not using the database, you could just stop the mongo service, and make a copy of the data directory - if you update on the same server. You stop mongo service, read upgrade info on mongo website. And start your new mongo, it will use your existing data directory.

If you do the same on another server, it's like an upgrade. In your script, save the mongo version, and install the same mongo version:

sudo apt-get install -y
    mongodb-org=3.0.8 mongodb-org-server=3.0.8 mongodb-org-shell=3.0.8
    mongodb-org-mongos=3.0.8 mongodb-org-tools=3.0.8

...and run an upgrade afterwards. Always check upgrade info on the mongo website!  – aldwinaldwin


Merge replication throws PRIMARY KEY violation during the initialization of Subscriber

Have seen this on numerous occasions.  Try these steps to resolve your issue.

Remove table from replication
Reinitialize subscription
Add table to replication
Reinitialize subscription


mysqldump --single-transaction, yet update queries are waiting for the backup

The --single-transaction option of mysqldump does do a FLUSH TABLES WITH READ LOCK prior to starting the backup job but only under certain conditions.  One of those conditions is when you also specify the --master-data option.

In the source code, from mysql-5.6.19/client/mysqldump.c at line 5797:

if ((opt_lock_all_tables || opt_master_data ||
     (opt_single_transaction && flush_logs)) &&
    do_flush_tables_read_lock(mysql))
  goto err;

To get a solid lock on the precise binlog coordinates prior to starting the repeatable-read transaction, the --master-data option triggers this lock to be obtained and then released once the binlog coordinates have been obtained.

In fact, mysqldump does a FLUSH TABLES followed by a FLUSH TABLES WITH READ LOCK because doing both things allows the read lock to be obtained faster in cases where the initial flush takes some time.

...however...

As soon as it has obtained the binlog coordinates, mysqldump issues an UNLOCK TABLES statement, so there shouldn't be anything blocking as a result of the flush you started.  Neither should any threads be Waiting for table flush as a result of the transaction that mysqldump is holding.

When you see a thread in the Waiting for table flush state, that should mean that the FLUSH TABLES [WITH READ LOCK] statement was issued and was still running when the query started -- so the query has to wait for the table flush, before it can execute.  In the case of the processlist you've posted, mysqldump is reading from this same table, and the query has been running for a while, yet the blocking queries haven't been blocking for all that long.

This all suggests that something else has happened.

There's a long-standing issue explained in Bug #44884 with the way FLUSH TABLES works, internally.  I would not be surprised if the issue still persists, I would be surprised if this issue is ever "fixed" because it is a very complex issue to resolve -- virtually impossible to truly fix in a high concurrency environment -- and any attempt at fixing it carries a significant risk of breaking something else, or creating new, different, and still undesirable, behavior.

It seems likely that this will be the explanation for what you're seeing.

Specifically:

if you have a long-running query running against a table, and issue FLUSH TABLES, then the FLUSH TABLES will block until the long-running query completes.
additionally, any queries that begin after the FLUSH TABLES is issued will block until the FLUSH TABLES is complete.
additionally, if you kill the FLUSH TABLES query, the queries that are blocking will still block on the original long-running query, the one that was blocking the FLUSH TABLES query, because even though the killed FLUSH TABLES query didn't finish, that table (the one, or more, involved with the long-running query) is still in the process of being flushed, and that pending flush is going to happen as soon as the long-running query finishes -- but not before.

The likely conclusion here is that another process -- perhaps another mysqldump, or an ill-advised query, or a poorly-written monitoring process tried to flush a table.

That query was subsequently killed or timed out by an unknown mechanism, but its after-effects lingered until mysqldump finished reading from the table in question.

You can replicate this condition by trying to FLUSH TABLES while a long-running query is in process.  Then start another query, which will block.  Then kill the FLUSH TABLES query, which won't unblock the latest query.  Then kill the first query, or let it finish, and the final query will successfully run.

As an afterthought, this is unrelated:

Trx read view will not see trx with id >= 1252538405, sees < 1252538391

That's normal, because mysqldump --single-transaction issues a START TRANSACTION WITH CONSISTENT SNAPSHOT, which prevents it from dumping data that was changed while the dump was in progress.  Without that, the binlog coordinates obtained at the start would be meaningless, since the --single-transaction would not be what it claims to be.  This should not in any sense be related to the Waiting for table flush issue, as this transaction obviously holds no locks.


Does AWS Aurora suffer from the same limitations that MySQL suffer from?

There is no limitation of 64KB on the size of a MySQL row.  There are limitations, but almost never does anyone hit them.  Can you point to where you got that limit?

There are a couple of obscure limits in InnoDB, such as having dozens of big VARCHARs, but they are easily worked around.

Here are the main limits for MySQL:  http://mysql.rjweb.org/doc.php/limits

Edit

At first glance the manual page on row size says there is a 64K limit on row size, but reading further it says "BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row."  Since LONGBLOB and LONGTEXT columns can contain up to 4GB each, the maximum total row size (including such columns) is at least 4GB.

Furthermore, "stored separately" is dependent on the ENGINE involved; that quote does not cover all the cases.

The bottom line is that it is possible to create a table with more than 64KB of data.  (I don't know about exceeding 4GB.)  Doing so may require using TEXT instead of VARCHAR(65535) (which is virtually identical in semantics), etc.

I have made many tables (MyISAM and InnoDB) where the "row size" was bigger than 64KB, both in definition and in fact.  I have multiple cases of MEDIUMTEXT or MEDIUMBLOB that work fine, back at least as far as version 4.0.


Sharding for slave node in master slave replication

"Sharding" is not supported directly in MySQL.  See other products, such as Spider.

If you meant "Partitioning", then elaborate on what you hope to gain and provide the partitioned and unpartitioned versions of SHOW CREATE TABLE.


Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query

I think you'll only get the out-of-range error if you try to convert, say a "June 31st" or a 13th month. If the text is just totally un-datelike (is that a word?) it'll give a different error.

With that inb mind, TRY_CONVERT() is a variant of CONVERT() which won't error if the format isn't quite right. You can use it to look for filenames which'll fail the convert.

Try running:

SELECT JobTasks.FileName
FROM   JobTasks
WHERE  TRY_CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20) is null


Calculations over UNION, merge query repetitive code

Setting aside the odd ball row (Calculations/Calculation Formula) for the moment, I opted for doing a union all of the raw data (in a derived table), and then grouped/pivoted the derived table, eg:

select ctype                                                       As employees_salaries,
       COALESCE(SUM(case when month(cdate)=1  then camount end),0) As January,
       COALESCE(SUM(case when month(cdate)=2  then camount end),0) As Febr,
       COALESCE(SUM(case when month(cdate)=3  then camount end),0) As March,
       ...
from   (select 'employees_salaries'                  as ctype,
               mmonth                                as cdate,
               gross                                 as camount
        from   employees_salaries
        where  year(mmonth) = year(curdate())

        union all

        select 'employees_salaries_insurence_costs',
               mmonth,
               insurence_cost1+insurence_cost2
        from   employees_salaries
        where  year(mmonth) = year(curdate())

        union all
        ... ) as c
group by ctype, year(cdate)

order by 1

Here's a fiddle for the complete query.


Before you migrate any databases, run 'Assesment' in Data Migration Assistant. It'll point compatibility issues between versions and also show you some best-practice suggestions. Then proceed with actual migration.
I noticed that one thing that DMA doesn't do, is to raise a warning if a database is running in Sql Server 2005 (or earlier) compatibility mode. Sql 2016 does only support compatibility modes 2008 or higher. So don't trust it blindly as there may be more aspects it ignores.


Why my Oracle 12c database seems not to be started?

Your database seems to be up and running, but it was not registered dynamically into the listener.

Try the below:

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=info

sqlplus / as sysdba
SQL> show parameter local_listener

Check the value of local_listener. You have 3 options here:

parameter value is empty - in this case, the database registers itself into the listener running on port 1521
parameter value is something like LISTENER_INFO - this is a TNS entry that resolves to the address of the listener. If tnsping LISTENER_INFO resolves to a valid address, the database should be able to register itself to the listener specified by that
parameter value is something like (ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)). No name resolution is required, database registers itself in the listener.

You most likely have 2. with an unresolvable name, or 3. with and invalid network address.

You can try fixing it with:

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))';

Which sets the specified address.

Or:

alter system set local_listener='' scope=memory;
alter system reset local_listener;

Which clears the value of local_listener in memory and spfile, so the database will try registering into a listener running on the default 1521 port.

If this still does not help, you can register your database in the listener statically, by adding the below in listener.ora:

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = info.nohoo.biz)
   (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
   (SID_NAME = info)
  )
 )

After that reload the listener:

lsnrctl reload

Or restart the listener:

lsnrctl stop
lsnrctl start

And try logging in again.


Migrating from a physical database to a cloud database

Moving the data is pretty easy.  If you are using full text search that feature isn't available in SQL Azure yet.  Neither is FILESTREAM.  If you require full text search then Azure isn't for you.


Locking, deadlocking workaround while process data at the same table

Simple this one.

Task1 should not insert into the main table, but a staging table, say tableXstaging. This table should either include an identity column or a row_processed (TINYINT or BIT) column.

Task1 keeps inserting all the time into tableXstaging. I would allow updates to update the main table directly as they are rare.

Another task which runs once every minute will either: set the MaxID = max(identity column) from tableXstaging, or if you instead used row_processed, set row_processed = 1. This gives you the batch to work with. This task will insert into tableX all records where identity<=MaxID, or if your using row_processed, WHERE row_processed = 1.
A single insert into your primary tableX is very fast. Then delete from tableXstaging where row_processed = 1, or WHERE identity<=MaxID. I would go with identity as you don't need to update row_processed = 1 where row_processed = 0, and so will be faster.

tableX is now only inserted into once a minute.

Actually re-reading your question, i'd go with row_processed, set row_processed = 1 for your first batch.

Task 2 works on the staging table WHERE row_processed = 1. Then sets row_processed = 2.

Task 3 works on the staging table WHERE row_processed = 2. Then sets row_processed = 3.

Finally, SET XACT_ABORT ON; BEGIN TRAN, you insert the records into tableX in one hit (row_processed=3). This is very efficient as the staging table is tiny, and tableX will be quite large and slow (by relative terms to the staging table). Then delete from staging where row_processed = 3. COMMIT;


What is the most effective way to cluster MySQL for my requirements?

It depends on how you use your database. If it is read-heavy but with few writes (like a blog or a news paper) you could have one mysql for writes and two for reads. You would set up so the write server is a replication master and the two for reads are slaves.

All application servers needs to know about both the write server and one of the read servers, that way when you balance the loads to application servers you automatically balance the reads between the mysql servers. It's also easy to add another mysql+application server once the need is bigger.

If you on the other hand have a write heavy site (I can't even find an example) you need to do some research on sharding. It's normally not recommended unless you really need it.


This is usually an issue where the permissions on xp_cmdshell have been modified for security reasons. You can also have this issue if the SQL Agent account does not have the required network permissions to delete data from your replication folder.

Verify xp_cmdshell is enabled and that the account executing it has proper permissions.

USE master
GO
EXEC sp_configure 'show advanced option', '1';
reconfigure;
GO
exec sp_configure 'xp_cmdshell';

Output should be:

name         minimum    maximum config_value run_value
xp_cmdshell  0          1       1            1


The comment from @ypercube is right.

You shall model an intermediary table to be able to enforce FKs:

A less formal model, in which FK enforcement would not be possible, could be:


MongoDB ixscan seems to be scanning more objects than expected

The large number of nscanned key comparisons is explainable by the skip value: the query is skipping 903,462 documents (ntoskip) in order to return 21 (ntoreturn). The  nscanned value in your output is the sum of ntoskip and ntoreturn.

The number of nscannedObjects (identical to nscanned) is because the skip stage in MongoDB 2.6.x query processing happens after the document fetch and projection stages.

There are a few suggested optimizations for skip performance you can watch and upvote in the MongoDB issue tracker:

SERVER-13946: Consider putting skip stages below fetch stages
SERVER-14663: Put skip stage below projection

In general, large values of skip are discouraged (see: cursor.skip() documentation).

Depending on your use case for skip(), alternative approaches to consider include:

use range-based queries for pagination, if possible
limit pagination for your API/UI (a skip value of 903,462 documents with 21 per page implies this query might be for page number 43,023 of results)
use $sample (MongoDB 3.2+) or an alternative approach for fetching/sampling random documents


With MySQL 5.6, Is there any way to keep a replication slave X seconds behind_the_master?

For any version pt-slave-delay  Is what you're looking for.  Note this is a 3rd party tool that continuously runs with stop/start slave, to keep it around the delay you want.

If you have 5.6 or above you can use the native built in slave delay


Redo log member and control files at Oracle DB Standard Edition

How many redo log members for each group can I define?

Database Limits

MAXLOGMEMBERS

Default: 2
Maximum Value : 5

  How many control files can I use by using the standard edition (one)?
  I couldn't find this information except, that there can be 8 control
  files at all.

That is correct. CONTROL_FILES

Range of values:1 to 8 filenames

  I've read, that I can have only two archive log destinations defined
  by LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST in the standard
  edition (one).

LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST are deprecated long ago. LOG_ARCHIVE_DEST_N is available since 8.1 in EE, and 10.1 in SE. You can define up to 31 destinations, but only the first 10 can be local. Remote destinations are not supported in SE.


Calculate the total on events with two time conditions

Thanks to @mustaccio's pointer above. Here's how I managed to get it to work using INTERSECT DISTINCT.
SELECT COUNT(*) FROM (
  SELECT DISTINCT
    event_id
  FROM
    `foo.bar.blah`
  WHERE
    DATE(timestamp) = current_date()

  INTERSECT DISTINCT

  SELECT DISTINCT
    event_id
  FROM
    `foo.bar.blah`
  WHERE
    (DATE(timestamp) BETWEEN DATE_SUB(current_date(), INTERVAL 30 DAY) AND DATE_SUB(current_date(), INTERVAL 10 DAY))
)

The two dates in the last SELECT needs to be adjusted according to the criterion of course.


Access to database denied :ELEPHANTSQL on Bluemix - “No pg_hba.conf entry for host localhost, user ADMIN, database SYSTEM”

The error message means you are already accessing pg_hba.conf. There is just no line granting access for your connection attempt. At least one of your provided details (host, user, database) does not match for any line in the file.

I am not familiar with ElephantSQL, but it seems to be a database hosting service providing standard PostgreSQL. Advice in the manual should apply. Here is a related answer with more explanation and links:

Something wrong in pg_hba.conf


Is it safe to copy DB files directly from an LVM snapshot?

Yes, it is safe. There is a tool http://www.lenzg.net/mylvmbackup/ that uses LVM snapshots to take backups of MySQL.

From InnoDB's standpoint a backup copy taken with LVM snapshots looks like as InnoDB has suddenly crashed. When you start MySQL from the backup copy it will start crash recovery. To save some time it's better to do the crash recovery immediately after the backup is taken.


The reason for this is that when a database is created by someone who isn't a member of the sysadmin fixed server role their account is setup as the database owner.  Your user will need to be granted the rights to change the database owner to another owner.  However this then creates a new problem, because that user isn't a member of the database owners group any more you'll now need that user to be a member of the security admin fixed server role so that it has the rights to change the permissions within the database.

The better option hear would be to have the customer (or whoever is installing the software) to be a member of the sysadmin fixed server role while the software is being installed, or at least require that they are using a different account than the applications account and that the account has database create and security admin permissions at the instance level.


Oracle USE_DB_RECOVERY_FILE_DEST filesystem path

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +O71_FRA
db_recovery_file_dest_size           big integer 8G


Help with a logic inside WHERE or ORDER BY

I think you should be able to get what you want with ROW_NUMBER().  Please see the below example.

;with cte as (
Select tConc.Faturamento
     , tFat.TotalFatura
     , tCod.Sequencial
     , tCodDet.CdCodigoBarras
     , tCodDet.Linha
     , ValorRecebido
     , RN = ROW_NUMBER() OVER (PARTITION BY  tConc.Faturamento, tCod.Sequencial, tCodDet.CdCodigoBarras, ValorRecebido
                                ORDER BY tCodDet.Linha DESC, tCodDet.CdCodigoBarras DESC, tCod.Sequencial DESC)
  From Sistema.Conciliacao_Faturamento_x_Arrecadacao as tConc
 Inner Join Arquivo.CodigoBarras_Detalhe as tCodDet
    On tConc.CodigoBarras = tCodDet.CdCodigoBarras_Detalhe
 Inner Join Arquivo.CodigoBarras as tCod
    On tCodDet.CdCodigoBarras = tCod.CdCodigoBarras
 Inner Join Arquivo.Faturamento as tFat
    On tConc.Faturamento = tFat.CdFaturamento
 Where Faturamento In(Select Faturamento
                        From Sistema.Conciliacao_Faturamento_x_Arrecadacao
                       Group By Faturamento
                      Having Count(CodigoBarras) > 01))
)
SELECT * FROM cte WHERE RN = 1

You may want to change or play with the PARTITION BY section as well as the ORDER BY to get what you want.  Basically, the PARTITION BY is what you want to be the business keys.  And I wasn't sure if it was just the Faturamento or Faturamento + Sequencial + ... or not.  Order by is if (given the same partition), what order should they appear in.


SELECT a.idx
     , q1.qdesc q1desc, a.q1 q1result
     , q2.qdesc q2desc, a.q2 q2result
     , q3.qdesc q3desc, a.q3 q3result
     , ...
FROM answers a
   , questions q1
   , questions q2
   , questions q3
   , ...
WHERE q1.qid=1
  AND q2.qid=2
  AND q3.qid=3
  AND ...

The number of questions/answers is dynamic, so use dynamic SQL for to build the query text.

PS. Do you really want to output the result table which contains 600 columns?


Delete an oracle instance, the right way

You can delete databases with DBCA which takes care of most of it.

Or you can do as below, but this will do the same as removing the datafiles, redo logs, controlfiles manually.

sqlplus / as sysdba
startup mount exclusive restrict
exit
rman target /
drop database including backups noprompt;
exit

After this, you still have to remove the entry that belongs to the database from /etc/oratab, remove init.ora/spfile, password file from $ORACLE_HOME/dbs, and clean log directories (adump, bdump, cdump, udump).


How to export Oracle database without data

Judging by the answer you gave in response to @Ozzie, I'm not sure you can do what you want.

You can export the metadata of an 11gR2 database using expdp (Data Pump) and the METADATA_ONLY flag.  See http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm

This will export the structure of your data.  This won't include redologs, controlfiles, the supporting files required for the instance.

To recover using this export, you would need to create a new database instance using dbca which has all the logs and supporting structure set up, and then import the metadata using impdp.

If you had a copy of the data files, you could also use transportable tablespaces to move the data to the new instance.

The normal method for backing up the entire database is RMAN.  This will include everything that you need to restore the database.  I don't believe that RMAN provides the option to exclude the data, though.

It may help if you can explain what you are trying to achieve, then better options may be available.


Accessing computed column in a nested query

You just have your nesting a little wrong (and introduced an unnecessary EXISTS clause):

SELECT * FROM
(
   SELECT node.category_id, ...
) AS nested
WHERE depth = 2;

You could also try (I think):

SELECT node.category_id, (COUNT(parent.name) - 1) AS depth
...
    group by
      node.category_id, node.lft
HAVING (COUNT(parent.name)-1) = 2;

But please be mindful of:

Schema prefix
Old-style JOINs


Show the matching record only for the 3 rows


Slow query when using ORDER BY in Postgres

Try putting the original (fast) query into a common table expression:

with my_events as (
  select *
  from (
    ...
  ) as ids
     join events on ids.event_id = events.id
)
select *
from my_events
order by event_ts desc;

CTEs are optimized independently from the rest of the query, so that should run the base query with the fast plan. Ordering the 456 rows should be fairly quick.


Get row with the biggest value from each group

I think something like that should work

SELECT b.* FROM
(
    SELECT a.*,
     ROW_NUMBER() OVER (PARTITION BY a.COD_VOO_REGULAR ORDER BY a.ATRASO DESC ) AS rn
    FROM
    (
      SELECT  VIAGEM.IDVOOREGULAR COD_VOO_REGULAR,
            VIAGEM.DATAPARTIDA DATA,
            VOO.ORIGEM,
            VOO.DESTINO,
            (((EXTRACT (DAY FROM (DATACHEGADA-DATAPARTIDA))*24*60*60 +
               EXTRACT (HOUR FROM (DATACHEGADA-DATAPARTIDA))*60*60 +
               EXTRACT (MINUTE FROM (DATACHEGADA-DATAPARTIDA))*60)/60)-VOO.DURACAO) ATRASO
     FROM   VIAGEM,
            VOO_REGULAR,
            VOO
    WHERE   VIAGEM.IDVOOREGULAR=VOO_REGULAR.ID AND
            VOO_REGULAR.IDVOO=VOO.ID
    )a
) b
WHERE rn = 1 ;


How to convert data type from varchar to number in SQL Server?

In Sql Server 2012 and up: each of these will return null when the conversion fails instead of an error.

try_convert(datatype,val)
try_cast(val as datatype)
try_parse(val as datatype [using culture])

So if you want to change the way a view is returning the value you would use something like:

alter view dbo.myView as
  select
    ....
    , store_nbr = try_cast(dbo.customer.store_nbr as int)
    ....


The following two queries give index usage stats per table which should be a good indicator of which tables are causing the most IO operations.

SELECT d.name as [Database],  OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
Join sys.Databases d on s.database_id = d.database_id
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
Order by USER_SEEKS + USER_SCANS + USER_LOOKUPS + USER_UPDATES desc

SELECT d.name, t.name, OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
 I.[NAME] AS [INDEX NAME],
  A.LEAF_INSERT_COUNT,
  A.LEAF_UPDATE_COUNT,
  A.LEAF_DELETE_COUNT

FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I          ON I.[OBJECT_ID] = A.[OBJECT_ID]
join sys.tables t on i.object_id = t.object_id
join sys.databases d on a.database_id = d.database_id
AND I.INDEX_ID = A.INDEX_ID WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

order by A.LEAF_INSERT_COUNT + A.LEAF_UPDATE_COUNT + A.LEAF_DELETE_COUNT desc


Storing multiple contact addresses for a person

I wouldn't have the ContactInformation linked 1-to-1 to Person anyway; storing several addresses like that in ContactInformation violates normal form. Instead I'd have ContactInformation with its own PK, an FK for PersonID back to the Person table, and store as many Contacts as necessary for each Person.

CREATE TABLE ContactInformation (
    ContactID int IDENTITY(1,1) PRIMARY KEY,
    PersonID int FOREIGN KEY REFERENCES Person(id),
    ContactInfo nvarchar(250),
    ContactType char(2)
)

Populate the ContactInformation table with the PersonID, then an email address/phone number/mailing address/twitter handle/etc and mark it with a ContactType. This could be 'MA' for mailing address, 'EM' for email, "TW' for Twitter, 'TT' for Term Time as in your question.

Triggers could validate any inserts to make sure contacts don't have a TT (term time) address unless they're a student.

Hope that helps you think outside the box. It may be too hard to make these changes at this point, if you're already deep in the application.

Edit:
I didn't feel I properly addressed your direct question. If I were you and making deeper changes isn't possible or feasible at this point, I would add TermTime address to the Students table.

It belongs there because 1) it only applies to Students, so having it in the Person or Contact tables (as they exist now) is less than ideal, and 2) Each student should have one so it's already part of their student record and 3) this can be done with the least disruption to the existing schema.


Thanks Lennart!
A second sub select query within the first worked.

select TTS.SSML, TTS_TAG.SORTID, TTS.VoiceId , TTS_TAG.TAGID,
(select max(TTS_TAG.SORTID) from TTS_TAG
where TTS_TAG.TAGID = 1) as maxsort
FROM TTS
left join TTS_TAG
on TTS_TAG.TTSID = TTS.TTSID
where TTS.MID = 15


For crosstab(), the "row_name" (cagetory in your example) must come before "extra" columns (name in your example).

Assuming name is indeed an "extra" column like the sample data suggests, this would do it:

SELECT name, category, "2020-05-30", "2020-05-31"
FROM   crosstab (
   'select category, name, date(clear_date), count(clear_date)::int from raw_data group
    group by 1,2,3 order by 1 DESC,2,3',
   'select distinct date(clear_date) from raw_data order by 1'
   )
   AS ct(category text, name text, "2020-05-30" integer, "2020-05-31" integer)
ORDER  BY name;

See:

Pivot on Multiple Columns using Tablefunc
PostgreSQL Crosstab Query

I left the 2nd function parameter 'select distinct date(clear_date) from raw_data order by 1' unchanged. But I would provide constants there, as you need to adjust the column definition list to match anyway.


Since at least PostgreSQL 8.3, PostgreSQL indexes NULL using a bitmap on the index. That means "yes" it's indexed. Except it takes a bit, rather than a byte[s]. There is no speed advantage insofar as btree indexes are concerned with using a non-NULL value. It will work just as you'd expect.

Dragons here: for more information see this,

http://patshaughnessy.net/2014/11/11/discovering-the-computer-science-behind-postgres-indexes


Why do large tables slow down queries in databases?

Most of the time, sql performance issues comes from a poor miss job on the indexing. Selecting the wrong field to index or even the lack of clustered index can create huge bottle necks. I have seen many, many times that most dba forgets to create indexes for deletes. Yeah. I know, a delete... How come? Deletes, at least in sql server, performs several tasks that involve checking if the element can be deleted, check related tables, etc and those tasks can be slow as molasses without the proper indexing.

On the other hand, performance issues can very well come from lazy programmers who don't really care about performance or creating heaps on the server. These type of guys just creates select queries or select * without caring if these queries are performing full table scans, and believe me, in those situations, it doesn't matter if the db is 3Mb or 1Gb.

Finally, if you are really planning to have very large tables, you should consider a good partition policy and create your db with that in mind. This way, you can create the proper partitioned indexes.

As in most cases, in databases, the problem is between the keyboard and the chair ;)


Stripping OIDs from tables in preparation for pg_upgrade

That UPDATE might work, but I am not certain, and I wouldn't want to do that with data I value.  You don't have the option anyway, since you cannot get superuser access in a hosted database.
I can think of a safe, but more painful method:

Briefly suspend data modification activity on the table.

Create a trigger that records all data modification activity in another table.

Create a new table that looks like the old one, and start an INSERT INTO ... SELECT ... that copies the data.

Now normal operation can be resumed.
Once the copy is done, replay the recorded changes. Then

Start a transaction.

LOCK the original table.

Replay all the changes that happened since the last replay (should be few).

DROP the original table.

Rename the copy to the original table name.

Foreign keys will require extra attention.


Removing per column and table collation and charset

Converting columns:

ALTER TABLE table_name MODIFY col_name VARCHAR(100) CHARACTER SET utf8;

Converting tables:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;


I'm not sure exactly what you're asking. The table which inherits has all of its rows show when you query the parent.

CREATE TEMP TABLE foo ( id int );
CREATE TEMP TABLE bar () INHERITS (foo);
INSERT INTO foo VALUES (1);
INSERT INTO bar VALUES (1);

test=# TABLE bar;
 id
----
  1
(1 row)

test=# TABLE foo;
 id
----
  1
  1
(2 rows)


'Could not fsync file' while running VACUUM FULL on a table

The file that PostgreSQL tries to sync is probably the new file created during VACUUM (FULL). This system call persists the file to disk so that it cannot get lost during a crash.
Perhaps your disk is having hardware problems, or you are using a file system that has problems with that (which you shouldn't do).


How much Tablespace we need to create for Oracle schema

I don't think there is a "rule of thumb" but it really depends upon your hardware configuration.

With different tablespace for every Schema or Index and Data you can differentiate based on the cost or the speed of the disk space.


Upgrading a big database inside a transaction

Reverting to a valid backup is the most common and safest approach. You can reduce risk and likelihood of needing to restore from backup by versioning the changes.

For instance, say your upgrade process involves 50 steps that have some sequence/order requirements. Any step or small group of steps that can survive a reboot after they are applied should safely rev a version number in your DB so you know exactly what the last successful upgrade step was. You can then resume the process instead of having to restart.

Add this to putting each step or collection of steps in transactions will help ensure your upgrade steps comply with ACID requirements and make your upgrade process more robust. We do this with some of our major upgrades and rarely ever have to restore when something breaks in the middle unless there's a bug in the script of one of the upgrade steps.


how to check whether Oracle database is accessed or not?

Take a look at it this thread on AskTom:

  Auditing of course would be the easiest way

  You could possibly get something from the DBA_HIST views (part of AWR/ASH - workload repository and active session history) but bear in mind

  they are sampled snapshots - not 100% of everything is in there
  many of them are "top" reports only - dba_hist_seg_stat for example - shows IO's against segments - it seems perfect, but it is only "This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT" - it will not have 100% of everything.

  Auditing is the right answer, funny thing with auditing - we only really want it when it isn't on don't we :)

Oracle has a lot of history tables as you can see here:
http://www.dba-oracle.com/oracle10g_tuning/t_dba_hist_data_dictionary_views.htm


How to set identity_insert to on in SQL Server

You are using the right syntax:

SET IDENTITY_INSERT dbo.sometable ON;

However, a schema in SQL Server is not the same as a database.

The default schema for objects is dbo as in my example. You do not need to specify the database name if you are connected to it, but you could like this:

SET IDENTITY_INSERT DbName.dbo.sometable ON;

There seems to be no way to get the current setting as it is session-scoped and not persisted.

If you run this query in your database it returns the fully qualified names for all tables in there:

SELECT
    QUOTENAME(DB_NAME())+'.'+
    QUOTENAME(OBJECT_SCHEMA_NAME(object_id))+'.'+
    QUOTENAME(OBJECT_NAME(object_id)) AS FullTableName
FROM sys.tables;

SET IDENTITY_INSERT ON... must be run in the same session as the insert.


Can I MERGE INTO ... INSERT with a column list in Hive?

Every example I've seen includes all of the columns.  I also found this note about updates (though nothing specific to insert):

  Note that you cannot update only a part of a complex structure field. For example, suppose you have a structure stored as one field in a Hive table:
  {"name":"Johnson","surname":"Fall","age":23,"gender":"MALE"}
  You cannot update only the age field in the structure. You can only replace all values of the structure with new ones.

From: https://mapr.com/docs/61/Hive/MERGE_Statement_with_MapR-DB-JSON-tables.html


Rewriting query to move subquery out of FROM clause

SELECT *
FROM entries
WHERE category = 'B'
ORDER BY id DESC LIMIT 1


MySQL: What is the maximum number of cores used by a single MySQL server?

I recommend you MySQL 5.5 -- add this manual page and licence page and this benchmark together and you will see why. To clarify further: MySQL -- rather, the InnoDB storage engine -- will use all cores in the system but up until 5.4 it actually slowed down with a higher number of cores, Google fixed this and the first MySQL to contain it is 5.5.

This fix also can be found in the 1.0.4 plugin and newer which can be enabled in MySQL 5.1.38.


What is the meaning of "sga_max_size= # internally adjusted"

It means it was adjusted by Oracle, because it was not specified explicitly.
When using Automatic Memory Management, you need to set only the memory_target parameter, the sga/pga parameters are handled internally by the database automatically.


Postgres 10 - Query slows down with order by

PostgreSQL thinks that it will be faster avoiding the sort for the ORDER BY by scanning the rows in the sort order and discarding rows until it finds one with the right school_id.

There can be two reasons why this takes longer than expected:

The table statistics is off, and PostgreSQL overestimates the number of rows with that school_id.

Calculate new statistics, possibly with a higher value for default_statistics_target, to verify if that is the problem:

ANALYZE students;

The (many) rows with the correct school_id all happen to have a rather high id, so PostgreSQL has to scan way more rows than it bargained for until it finds a match.

In that case, you should modify the ORDER BY clause so that PostgreSQL cannot use the wrong index:

... ORDER BY id + 0


Mongodb: Possible to enlarge a capped collection?

No, you can't alter a capped collection size to make it larger (or smaller for that matter) without dropping it and recreating it - it is created with a fixed size.  However you could use mongodump to dump out the existing data, then drop the collection, recreate it with the larger size and then restore the dumped data.

Assuming you are making it bigger, all the data will fit in the new collection. Unfortunately, this will mean that you will need to stop writing to that collection for the duration of this work, there is no way to do it without taking some down time for the collection in question.

There is an existing feature request to add resizing capabilities to MongoDB, but as of writing this it is not yet scheduled. See here for voting and watching purposes:

https://jira.mongodb.org/browse/SERVER-1864


LEFT JOIN / WHERE clause with is null and false Estimated Number Of Rows

Because you need to pull a value from PPC, you can't do a proper Semi / AntiSemi Join. If you put a unique index on PPC (CountryID, Product) INCLUDE (IsBlocked, SpecialPrice), you should get a better cardinality estimate, as it'll either be there or not, and the multi-column statistics from this index will give a better answer.


Is it possible to change ER diagram style in MySQL Workbench? How?

You can change the look of the diagrams a bit, evn though not exactly as shown in your sshot. Under the model menu there are 2 submenus for object and relationship notations:

The result looks then like this:


If I understand your requirements correctly, you want each row in DCString, even when there is no matching row in StringData. The two queries you have now are incorrect:

The first one performs DCString RIGHT JOIN StringData - this is backward to what you seem to want (and is one of the reasons most sane people avoid RIGHT JOIN whenever possible).
The second query performs StringData LEFT JOIN DCString - this is also backward to what you seem to want. I suggested changing the RIGHT JOIN to a LEFT JOIN, without changing the table order, but you swapped the tables too, for no net change. Why that one is currently not returning could be due to a lot of things, no way for us to speculate. Try and generate an estimated plan and see if you are falling prey to any of these reasons.

Again, based on what I think you want (you'll get better answers if you provide some sample data and desired results):

-- don't use regional, ambiguous formats like dd.mm.yyyy:
-- also you can declare multiple variables in one statement

DECLARE @begin datetime    = '20160222',
        @end   datetime    = '20160223',
        @dcStringID bigint = 6658;

-- don't litter your code with square brackets except where necessary:

SELECT d.ID, s.[TimeStamp] -- bad choice for a column name

-- always use schema prefix!
FROM dbo.DCString AS d
LEFT OUTER JOIN dbo.StringData AS s
  ON d.ID = s.DCStringID
  AND s.[TimeStamp] >= @begin
  AND s.[TimeStamp] <  @end
WHERE d.ID = @dcStringID;

Further reading:

Ambigous date formats
Schema prefix