Median absolute deviation for time series outlier detection in Amazon Redshift Context

I am tasked with trying to detect outliers in time series data in an Amazon Redshift (PostgreSQL) system. Also known as a public holiday detector around the office. The method I have been using takes a windowed average of the previous N data points, I have also a windowed Standard Deviation and then take those stats about the N prior points and apply the following to the current datapoint:

(x0 - avg(x1:xN)) / stddev(x1:xN) > threshold

So window length and threshold have been sufficient to play with but this method is not a robust method since we have had significant growth after an advertising campaign then the series is straying hugely away from the running average and standard deviation threshold and everything is an outlier.

Reducing the window length allows it to adapt to these changes faster but it doesn't have as good of a long term model.

Increasing the threshold for this sort of growth would mean previous outliers we detected will no longer get detected.

These related questions provide suggestions in R but the answer quite often mention Median Absolute Deviation as a robust method:

https://stats.stackexchange.com/q/1142/90134
https://stats.stackexchange.com/q/104882/90134

https://en.wikipedia.org/wiki/Median_absolute_deviation

tl;dr

  How do I implement Median Absolute Deviation on a times series data set in Amazon Redshift?

I'm not sure if I'm missing something fundamental about the method but I'd like it to only work on a window and not have it work on the entire data set. Although the Median Window functions do not allow a frame clause.

If not this method, then point me in the right direction of more sophisticated outlier detection queries in Amazon Redshift would be appreciated.

I believe this is very doable with a CTE or subquery!

MAD is can be calculated by composing several Redshift functions:

the median of a list of values
absolute value of the difference between each value and that median
the median of those values

I wrote this in the form of:

WITH
medians AS (
  SELECT
    t.values,
    MEDIAN(t.values) OVER () as median_value,
    ABS(t.values-MEDIAN(t.values) OVER ()) AS absolute_deviation
  FROM table AS t
  GROUP BY t.values
)
SELECT
  MEDIAN(absolute_deviation) OVER () AS median_absolute_deviation
FROM medians

Hope this helps


How to extract a SQL database design into Visio in a manageable format? I have a database diagram that I would like to extract from SQL and manipulate it to create a user-friendly and easier-on-the-eye ERD.  The database contains over 200 tables and I would like to map the tables out to show other how it works to others.

I don't want to reverse engineer as Visio reformats the ERD and no longer looks like the model I have in database designs.

I'm aware that you can right-click and copy-to-clipboard however this captures the model as an image which means I can't manipulate it.

Is there an alternative piece of software out there that I'm not aware of?

I'd check out Embarcadero tools Try Data Architect.


Problem with charset on SQL*PLUS We are creating scripts to sent to our customers, with some inserts. We save them with ANSI encoding on NOTEPAD++, but when the customer runs the script via SQL*PLUS, it looks like this:

How can I be sure that the customer will run the script, with ansi encode, so we have no problems with those symbols on â, ó and et.

We told them to customize their environment to use NSL_LANG = WE8MSWIN1252.
TEST QUERY:

Select *,
Case
        When Teste_CHAR=Teste_CHAR_ASCII
        Then 'OK'
        Else 'Erro'
END as STATUS_TESTE,
Case
       When Teste_CHAR=Teste_CHAR_ASCII
       Then 'Everything is OK'
       Else 'Script will not run. your encode is different from ours'
END as Mensagem_TESTE
 from (
Select CHAR(ASCII('ã')) As Teste_CHAR, CHAR(227) as Teste_CHAR_ASCII
) A

as said here ( **How to be sure that a SQL Script is running with ANSI encoding? ) **this is not correct. But, is there a way to use something like this?

First problem is: What do you mean by ANSI?

ANSI is a common misnomer for your default Windows character set. It depends on your Windows language. See a list of codepages on National Language Support (NLS) API Reference (column "ANSI codepage")

You can also query your Registry to get it:

C:\>reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage /v ACP

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage
    ACP    REG_SZ    1252

Let's assume it is CP1252, the most common on "Western" PC's

When you run SQL*Plus then it inherits the codepage from your cme.exe. However this is not "ANSI codepage", you get it in column "OEM codepage" or by Registry

C:\>reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage /v OEMCP

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage
    OEMCP    REG_SZ    850

This is typically CP850 (for Western Europe) or CP437 (in US)

You can change and interrogate the code page by command chcp

NLS_LANG parameter tells the database which character set you are using (because the Oracle Database cannot interrogate your settings from Notepad++ or cmd.exe)

So, it order to make it working run following:

C:\>chcp 1252
Active code page: 1252

C:\>set NLS_LANG=.WE8MSWIN1252

C:\>sqlplus user/pwd@DB @xyz.sql


sqlite: attached database doesn't work properly 1) I can open database from the command line:

bash-3.2 # sqlite3 RecordingDb.sqlite
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.

sqlite> .databases
main: /db/RecordingDb.sqlite

sqlite> select * from recording limit 1;
|110|4||1423222200|1423049398|1423051200|0|0||0|100|||0|0||||1013760|1|11|4|19300||4|4|1801|||4||0|0||||20486|||

2) I can attach it from the sqlite prompt, but it doesn't work properly:

bash-3.2 # sqlite3
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> attach database 'RecordingDB.sqlite' as RecordingDb;

sqlite> .databases
main:
RecordingDb: /db/RecordingDB.sqlite

sqlite> select * from RecordingDb.recording limit 1;
Error: no such table: RecordingDb.recording

Why?

Similar question:

Working with multiple databases?

Linux has case sensitive file names.

       main: /db/RecordingDb.sqlite
RecordingDb: /db/RecordingDB.sqlite
                           ^


MySQL stops in windows VM and shows innodb os error in logs(error no. 5) 2020-08-23 17:22:36 5688 [Note] Plugin 'FEDERATED' is disabled.
2020-08-23 17:22:36 5688 [Note] InnoDB: Using atomics to ref count buffer pool pages
2020-08-23 17:22:36 5688 [Note] InnoDB: The InnoDB memory heap is disabled
2020-08-23 17:22:36 5688 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2020-08-23 17:22:36 5688 [Note] InnoDB: Memory barrier is not used
2020-08-23 17:22:36 5688 [Note] InnoDB: Compressed tables use zlib 1.2.3
2020-08-23 17:22:36 5688 [Note] InnoDB: Not using CPU crc32 instructions
2020-08-23 17:22:36 5688 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2020-08-23 17:22:36 5688 [Note] InnoDB: Completed initialization of buffer pool
2020-08-23 17:22:37 5688 [Note] InnoDB: Highest supported file format is Barracuda.
2020-08-23 17:22:37 5688 [Note] InnoDB: The log sequence numbers 1771332 and 1771332 in ibdata files do not match the log sequence number 24084333 in the ib_logfiles!
2020-08-23 17:22:37 5688 [Note] InnoDB: Database was not shutdown normally!
2020-08-23 17:22:37 5688 [Note] InnoDB: Starting crash recovery.
2020-08-23 17:22:37 5688 [Note] InnoDB: Reading tablespace information from the .ibd files...
2020-08-23 17:22:38 1640  InnoDB: Operating system error number 5 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory. It may also be you have created a subdirectory
InnoDB: of the same name as a data file.
InnoDB: Error: could not open single-table tablespace file table_a.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

I'm using VM and OS is Windows Server 2016 and Uniform Server.
If I set innodb_force_recovery > 0, nothing gets updated in db.
I have started mysql and apache as admin, also I have  tried deleting ib_logfile* and still nothing works.
In the other case, even if both mysql and apache are running, the web page shows like request can't be handled and these errors will be in mysql error log.
How can this be resolved?

Akina: Log tells that mysqld does not have the access rights to the directory. Adjust the access rights in server's filesystem for the account which is used for mysqld starting.


Oracle19C installation is stuck at executing datapatch in Windows 10 using dbca I tried using dbca. There are no errors. It just hangs. I tried leaving it running for 4 hours. It is always stuck at 36% even after multiple tries.

Logs:

[progressPage.flowWorker] [ 2020-05-25 00:26:04.206 IST ] [CloneDBCreationStep.executeImpl:823]  executing datapatch %ORACLE_HOME%\OPatch\datapatch.bat

Make sure that the user installing the software is in the local ora_dba group, and that os authentication is enabled in sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Also, it is a good idea to make sure that any anti-virus software doing active scanning is disabled during install/db create activity, and that when running it contains exceptions for all Oracle-related files and directories.


Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage? I've seen some people suggest that O_DSYNC should be used with a SAN.

The MySQL docs have this to say about O_DSYNC in general:

  In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and other similar methods is surprisingly slow. If you are dissatisfied with database write performance, you might try setting the innodb_flush_method parameter to O_DSYNC. The O_DSYNC flush method seems to perform slower on most systems, but yours might not be one of them.

And specifically with regard to use with a SAN:

  On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Always test this parameter with the same type of hardware and workload that reflects your production environment.

Although they suggest one should test, the wording is so wishy-washy (i.e. "some systems, "might be") that one could be forgiven if they didn't even bother testing and stuck with the default (fdatasync). But in my (unscientific) tests I have found O_DSYNC to provide enormous performance benefit - at least with some troublesome SELECT queries.

I was wondering if anyone could explain in greater detail why this option can provide such a benefit when using a SAN. The good book of course discusses this option but not in the context of a SAN.

I'm using:

MySQL 5.1 with the InnoDB plugin on RHEL 6 (x86_64)
Virtualized environment (vSphere); SAN with FC interconnect is provided by our IaaS provider
Ext4 file-system with barrier=0 mount option
I/O Scheduler is deadline

O_DSYNC is faster than O_DIRECT because it performs writes without verification.
O_DIRECT is paranoid but more data consistent

I wrote about this before : Clarification on MySQL innodb_flush_method variable


Party Data Model / Universal Data Model Example I am trying to implement Party Data Model / Universal Data Model in our application which needs to store different relationships between entities (People, Companies).

Example of relationships: Employer/Employee, Husband/Wife, Seller/Customer.

We also need to store the different types of roles a person can have inside a company.

For example, a person can be Employee at Google, but at the same time occupies a Head of Development position.

I am using this schema as an example, but i need to admit that i don't fully understand it.

From the schema, i think that party role is a tree-structure table? A role can have a parent role? Example: CEO > Head of Development > Developer

Or, party roles are organized in categories, like in the picture? Person Role, Customer, Organization Role.

I have tried to create the DB schema for this picture, and this is what i have:

And the data would look like this

Party
John Smith (Person)
Google (Organization)
Jane Smith (Person)

PartyRelationshipType
Employment
Married

PartyRole
Employer
Employee
Husband
Wife

PartyRelationship
Id          RelationshipType            FromParty           ToParty         Role            FromDate            ThruDate
1           Employment                  Google              John Smith      Employer        1/01/2008           16/08/2011
2           Married                     Jane Smith          John Smith      Wife            15/11/2000

Is the structure ok?

How can i define the relationships between Google employess for example? All of them they are employees, but at the same time they occupy different titles. Should i create a new Role for each title? or this data should be saved somewhere else?

Can somebody show me a real-data example?

I think you need to step back first and familiarise yourself with the notation, which seems to be Barker:

http://www.agiledata.org/essays/dataModeling101.html

The Party Role structure does illustrate a hierarchy of a type, but each of the subtypes uses the same primary key, the Party Role ID.
Presumably, when implemented each of these entities would have varying attributes, so wouldn't be implemented as a single PartyRole table.  Also, there will be business rules of what other tables the party roles could relate to.
But in terms of Party Relationships, what you have seems correct


Is SQL*Net message from dblink > 30% a performance issue? I have a Java web-application running under Glassfish. App accesses Oracle using JPA (eclipselink).

Last 2 days I'm observing some perfomance issues: some messages about long running queries (10 to 100 sec) appears in Glassfish logs.

I asked Oracle DBA to provide Oracle AWR-log for 2 hours. There's SQL*Net message from dblink in top 5 Timed foreground events. It's seems suspicious for me, am I right, that in normal case there shouldn't be SQL*Net message from dblink in top 5 Timed foreground events? Is it a perfomance issue with database on the dblink other side?

Most likely means that the the shadow process(es) that support the dblink connection(s) are idle. Well they are definitely idle. The only question is are these idle periods between cursors or within an executing cursor. If they are within an executing cursor then whatever is driving the cursor execution, the fetching, is slow. But odds are it's simply an idle dblink connection. See http://kylehailey.com/oracle-sqlnet-wait-events/


Select rows that have multiple required filters How can I select artists who have punk and rock in their musical genres? I need artists with both genres.

A example:

SELECT * FROM artist_genre WHERE genre_id IN (1,2)

In this way I recover all that belong to punk, or rock. But I only need artists who own both. Is there a easy way for this?

If a change in the structure of the databases is required, I'm up to do.

This is referred to as the intersection of sets.  One way to perform the query is the use the INTERSECT operator:

SELECT ag.artist_id
FROM dbo.artists_genres ag
WHERE ag.genre_id = 1
INTERSECT
SELECT ag.artist_id
FROM dbo.artists_genres ag
WHERE ag.genre_id = 2

INTERSECT returns distinct rows that are output by both the left and right inputs.  The first part of the query retrieves the artist_id where the genre_id is 1, the second part of the query retrieves the artist_id where the genre_id is 2, and the INTERSECT operator then compares both lists, returning only rows that match precisely, eliminating duplicates.

This is very similar to using an INNER JOIN against two common-table-expressions, commonly referred to as CTEs, as in:

;WITH g1 AS
(
    SELECT *
    FROM dbo.artists_genres ag1
    WHERE ag1.genre_id = 1
), g2 AS
(
    SELECT *
    FROM dbo.artists_genres ag1
    WHERE ag1.genre_id = 2
)
SELECT g1.artist_id
FROM g1
    INNER JOIN g2 ON g1.artist_id = g2.artist_id;

If you don't like common-table-expressions (CTEs), this looks like:

SELECT g1.artist_id
FROM
    (
        SELECT *
        FROM dbo.artists_genres ag1
        WHERE ag1.genre_id = 1
    ) g1
    INNER JOIN
        (
            SELECT *
            FROM dbo.artists_genres ag1
            WHERE ag1.genre_id = 2
        ) g2 ON g1.artist_id = g2.artist_id


Postgres: Dictionary to remove leading zeroes from alpha-numerical words I am running a full text search in Postgres 10 over texts which include a variety of alphanumerical IDs (as well as 'real' text).
My clients want to be able to find those IDs without the need to enter the leading zeroes. As the number of digits vary for different IDs I do not want to go down the path of adding extra zero-padded search queries.

I was wondering if there is a way to remove leading zeroes using a dictionary. But this would require some wildcards to catch an alphanumeric expression with leading zeroes. My understanding so far is that this is not supported by the ispell/hunspell dictionaries supported by Postgres 10.

I am currently thinking about using regexes like /\W[0]+(:alnum:)+/ and adding the matches to the text before feeding the text to the search index. This way I would catch both the zero-padded original and the variant without leading zeroes. But it does feel a little clumsy...

Does anybody have a better idea on how to do this using a full text search dictionary (or something similar)?

The texts I am search look like this:

'Max Mustermann 00023131021 Wunderstr. 17 Trallerhausen 002ed2323'

Example - they are typically much longer in reality and contain up to 50 different IDs.
Here, I would like to hit on '00023131021', '23131021', '2ed2323 & Wunder:*', etc.

Postgres text search only supports prefix matching, not postfix matching like you could use:

Get partial match from GIN indexed TSVECTOR column

Depending on exact requirements a number of workarounds come to mind.

If all leading zeros are insignificant, you could remove those from the indexed column in an expression index like:

CREATE INDEX foo_idx ON tbl
USING GIN (to_tsvector('simple', regexp_replace(col, '\m0+', '', 'g')));

Or use a regular expression fitting your actual requirements:

dbfiddle here

Then match the index in queries like:

SELECT *
FROM   tbl
WHERE  to_tsvector('simple', regexp_replace(col, '\m0+', '', 'g'))
    @@ to_tsquery ('simple', '2ed2323 & Wunder:*');

Remove leading zeroes from the tsquery value as well - if those can be contained:

   ...
    @@ to_tsquery( 'simple', regexp_replace(my_querystring, '\m0+', '', 'g'));

Related:

PostgreSQL GIN index not used when ts_query language is fetched from a column


Does Postgres offer a feature like “NEWSEQUENTIALID” in MS SQL Server to make UUID as primary key more efficient Microsoft SQL Server offers the NEWID command to generate a new GUID (the Microsoft version of UUID) value that can be used as a primary key value (in their uniqueidentifier data type). These are not sequential in nature, so updating an index can be inefficient.

Alternatively, MS SQL Server offers the NEWSEQUENTIALID command. To quote their documentation:

  Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

Is there a way to get the more efficiently-indexed UUID in Postgres?

uuid-ossp module
PostgreSQL uses the standardized UUID generation algorithms provided by ITU-T Rec. X.667, ISO/IEC 9834-8:2005, and RFC 4122. From the docs on uuid-ossp,

The uuid-ossp module provides functions to generate universally unique identifiers (UUIDs) using one of several standard algorithms. There are also functions to produce certain special UUID constants.
uuid_generate_v1() This function generates a version 1 UUID. This involves the MAC address of the computer and a time stamp. Note that UUIDs of this kind reveal the identity of the computer that created the identifier and the time at which it did so, which might make it unsuitable for certain security-sensitive applications.

So long as the MAC address does not change, you'll be golden.
That all said, I agree with @a_horse_with_no_name,

From my understanding this is only necessary in SQL Server because tables are stored in a clustered index which makes random insertions slower then with a heap table. Postgres has no such concept, so I don't think that would make a difference in Postgres

In fact, given the chance of fewer collisions and more security, I would take it. And to that I would use uuid_generate_v4()

uuid_generate_v4() This function generates a version 4 UUID, which is derived entirely from random numbers.


Get top 10 records from two identical tables Suppose I have two identical tables as bellow with different values in it as bellow. I want to get top 4 records ordered by date.
These 4 records should be union of both tables

Sample tables:

ID  userID  Date
1   1111    01/01/2018
2   1112    03/01/2018
3   1113    04/01/2018
4   1114    05/01/2018
5   1115    06/01/2018
6   1116    07/01/2018

ID  userID  Date
1   1117    02/01/2018
2   1118    03/01/2018
3   1119    05/01/2018
4   1120    06/01/2018
5   1121    07/01/2018
6   1122    08/01/2018

Expected output:

ID  userID  Date
1   1111    01/01/2018
1   1117    02/01/2018
2   1112    03/01/2018
3   1113    04/01/2018

How can I get this output. Any help will be greatly appreciated.

Assuming you don't care which result you get in ties, you can use ORDER BY / LIMIT twice, once in each subquery and then a third time in the main query.

This will be more efficient than a normal UNION query from two tables and then LIMIt, as it can use indexes on (Date) on both tables:

    ( SELECT ID, UserID, Date
      FROM table1
      ORDER BY Date
      LIMIT 4
    )
UNION ALL
    ( SELECT ID, UserID, Date
      FROM table2
      ORDER BY Date
      LIMIT 4
    )
ORDER BY Date
LIMIT 4 ;


Tracing, debugging and fixing Row Lock Contentions Off late, I've been facing a lot of row lock contentions. The table in contention seems to be a particular table.

This is generally what happens -

Developer 1 starts a transaction from Oracle Forms front end screen
Developer 2 starts another transaction, from a different session using the same screen

~5 minutes in, the front end seems unresponsive. Checking sessions shows row lock contention. The "solution" that everyone throws around is to kill sessions :/

As a database developer

What can be done to eliminate row lock contentions?
Would it be possible to find out which line of a stored procedure is causing these row lock contentions
What would be the general guideline to reduce/avoid/eliminate such problems which coding?

If this question feels too open-ended/insufficient information please feel free to edit/let me know - I'll do my best to add in some additional information.

The table in question is under a lot of inserts and updates, I'd say it's one of the most busiest tables. The SP is fairly complex - to simplify - it fetches data from various tables, populates it into  work tables, a lot of arithmetic operations occur on the work table and the result of the work table is inserted/updated into the table in question.

The database version is Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit. The flow of logic is executed the same order in both the sessions, the transaction isn't kept open for too long ( or at least I think so), and the locks occur during active execution of transactions.

Update: The table row count is larger than I expected, at about 3.1 million rows. Also, after tracing a session I found that couple of update statements to this table are not utilizing the index. Why is it so - I'm not sure. The column referenced in the where clause  is indexed. I'm currently rebuilding the index.

Would it be possible to find out which
  line of a stored procedure is causing
  these row lock contentions?

Not exactly but you can get the SQL statement causing the lock and in turn identify the related lines in the procedure.

SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

  What would be the general guideline to
  reduce/avoid/eliminate such problems
  with coding?

The Oracle Concepts Guide section on locks says, "A row is locked only when modified by a writer."  Another session updating the same row will then wait for the first session to COMMIT or ROLLBACK before it can continue.  To eliminate the problem you could serialize the users, but here are some things that can reduce the problem perhaps to the level of it not being an issue.

COMMIT more frequently.  Every COMMIT releases locks, so if you can do the updates in batches the likelihood of another session needing the same row is reduced.
Make sure you aren't updating any rows without changing their values.  For example, UPDATE t1 SET f1=DECODE(f2,’a’,f1+1,f1); should be rewritten as the more selective (read fewer locks) UPDATE t1 SET f1=f1+1 WHERE f2=’a’;.  Of course if the changing the statement will still lock the majority of rows in the table then the change will only have a readability benefit.
Make sure you are using sequences rather than locking a table to add one to the highest current value.
Make sure you aren’t using a function that is causing an index to not be used.  If the function is necessary consider making it a function based index.
Think in sets.  Consider whether a loop running a block of PL/SQL doing updates could be rewritten as a single update statement.  If not then perhaps bulk processing could be used with BULK COLLECT ... FORALL.
Reduce the work that gets done between the first UPDATE and the COMMIT.  For example, if the code sends an email after each update, consider queuing the emails and sending them after committing the updates.
Design the application to handle waiting by doing a SELECT ... FOR UPDATE NOWAIT or WAIT 2.  You can then catch the inability to lock the row and inform the user that another session is modifying the same data.


MySQL Execution Plan Recompile I've added a new index to account for some performance issues however the queries that I would expect to be using the new index are still are not using it.  In SQL Server you can flush an execution plan a couple of different ways to correct a poor performing query and I'm looking to understand if that is also possible in MySQL.

Can a specific execution plan be removed?
Can a all of the stored execution plans be removed?

Sadly, you cannot do that with MySQL.
Notwithstanding, there are two things try
1: Tweek the Query Optimizer
You can change the Query Optimizer behavior. Please see my posts about doing so, but always check the MySQL Documentation first and foremost.
2: Make MySQL use the index
Rather that reinvent the wheel, see my old post from Oct 28, 2011, Using FORCE INDEX


Running .sql files in postgres (structured programs) In oracle I could create .sql files and run them with a structure (like a stored procedure) with

begin

  declare

end;

I did some tests, because I need to run scripts to fix problems, not is necessary record it as a stored procedure/function on server, so like in Oracle, I want to run it once and check the result.

Not is possible run .sql files on postgres with CURSOR, declare, variables, etc.. , only directly on server with stored procedures/functions ?

The equivalent of Oracle's anonymous PL/SQL blocks are anonymous PL/pgSQL blocks in Postgres. These are started using the do statement:

do
$$
declare
  ....
begin
  ....
end;
$$


Sybase ASE 12 question I support a database that has many tables that hold very similar data.  Over time, much of this data isn't needed, and the table names are dynamic as an application is creating these tables, and the current date is used within the table name.

I am attempting to write a batch file that uses isql to remove rows that are no longer required to store in all of the tables.  They all start off with the same set of characters, followed by _creation date.

Is there any way to write a script to find those names, or use a wildcard such as:

DELETE FROM Table_* WHERE Type = 1

From my testing, the above SQL command does not work. Is there a way around this without having to specify the exact table name?

Sybase ASE will not allow you to directly delete rows from multiple tables using a wild card, but it's pretty simple to create a script to find the table names, and loop through them.

You can find the tables names by querying sysobjects within the database.

To find all the tables with the prefix Table_ you would do:

select name from myDatabase..sysobjects
where type = "U"
and name like "Table\_%" escape "\"

In the above query "U" is the flag for User defined tables.  Also because _ is a single character wild card in T-SQL, we have to escape it to find the literal _.  T-SQL allows almost any character to act as the escape, but it must be defined after the like clause.  Finally % is the multi-character wild card, the T-SQL equivalent to * in *nix and DOS.

It would also be possible to use the above query to build a sql script.  We start by telling the server not to print out the number of rows affected by the query using set nocount.  This allows us to create a script that will run error-free, without needing additional editing.

set nocount on    \\supressess rows affected printout
select "delete from " +name+ " where type = 1" + char(10) + "go"
from myDatabase..sysobjects
where type = "U"
and name like "Table\_%" escape "\"
go

If you execute this from isql and using the -b flag to supress headers, and -o flag to redirect output to a file, it will build a script that can then be executed directly.

isql -Uusername -Ppassword -Sservername -b -omyScriptToDeleteData.sql -iSQLToFindTables.sql


Skipped rows or rows read multiple times with NOLOCK Is it possible to stimulate a situation where rows either get skipped or get read multiple times in a select statement? E.g. the way we can cause a blocking or a deadlock...

Gail Shaw posted a demo of how to do so quite a while ago. It works by inducing row movement by increasing the size of the row over time. No inserts or deletes, just updates to existing records.


Is there way to get transaction commit timestamp in Postgres? I have data-pulling functionality that once in 5 seconds grabs all the data from Postgres table basing on modified_timestamp column. It works the following way:

SELECT * FROM my_table WHERE modified_timestamp > _some_persisted_timestamp
_some_persisted_timestamp = CURRENT_TIMESTAMP
Processing data received from step 1
Sleep for 5s
Go to step 1

Where modified_timestamp updated with trigger (after any row update modified_timestamp becomes CURRENT_TIMESTAMP).
It worked fine until I noticed that CURRENT_TIMESTAMP in Postgres is transaction start timestamp in fact and some of the updates are lost. Why are they lost? That's quite simple - at the moment when I execute query SELECT * FROM my_table WHERE modified_timestamp > _some_persisted_timestamp some of the changes have already occurred, but modified_timestamp is before updated _some_persisted_timestamp because transaction is in progress still.

This problem could be easily solved by assigning in step 2 timestamp when update becomes visible for other transactions (transaction commit timestamp in other words) instead of CURRENT_TIMESTAMP or clock_timestamp().

I read documentation, but have found nothing related to transaction commit timestamp. Could you kindly suggest smth?

Btw, I'm aware of logical decoding and I know that this mechanism suits better for my needs in theory, but there are certain practical problems not allowing me to use it.

This problem could be easily solved by assigning in step 2 timestamp
when update becomes visible for other transactions (transaction commit
timestamp in other words) instead of CURRENT_TIMESTAMP or
clock_timestamp().

This is logically impossible. Postgres writes new row versions before it finally commits to make them visible. It would require prophetic capabilities to write a future timestamp yet unknown at the time of writing.
However, you can get commit timestamps from a different source: since Postgres 9.5, there is a GUC setting track_commit_timestamp to start logging commit timestamps globally.
Then you can get commit timestamps with the utility function pg_xact_commit_timestamp(xid). Your query could look like:
SELECT * FROM my_table t
WHERE   pg_xact_commit_timestamp(t.xmin) > _some_persisted_timestamp;

Be aware that commit timestamps are not kept around forever. After
two billion transactions transactions (2^31), transaction IDs are "frozen". That does not delete it right away, but after 4 billion transactions, the information is gone for certain. That's a big number of transactions, and only very busy databases burn that much over a lifetime. But there can be programming errors burning through transaction numbers more quickly than expected ...
Your step 2 and step 3 trade positions, and you record the commit timestamp instead of CURRENT_TIMESTAMP - or xmin from any freshly updated row to derive the commit timestamp with pg_xact_commit_timestamp() once more.
More:

How do I write a Postgres SQL command based on metadata of the tables themselves?
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5#Commit_timestamp_tracking

About xmin:

How to view tuples changed in a PostgreSQL transaction?

But I am not completely sure I understand your task. Maybe you need a queuing tool or process rows one by one like discussed here:

Postgres UPDATE ... LIMIT 1


Which AWS RDS; current t3.medium over CPU baseline, but not sure what's the next best option I currently have one RDS instance, db.t3.medium running Aurora MySQL.
Due to increase workload (expected), the CPU is, on average running just over its baseline and therefore in about 10 days will run out of CPU credits and then start getting expensive. The 15-minute average CPU Utilization is around 22% (baseline is 20%).
RDS doesn't support any larger db.t3. versions for Aurora MySQL so I either need to change instance class, or move to Aurora Serverless, or look at a second instance alongside. I'm not sure what's going to be the best move so keen to get some advice.
Second Instance
Up until now I've avoided running a second db instance because:

I don't fully understand how to set it up correctly to share the workload (adding different endpoints for read vs write in my applications would be tricky)
I'm not familiar if this would actually share the load like an EC2 load balancer does
most of my MySQL CPU usage is from reading (relatively little write), so I don't predict splitting it to a reader and a writer helping too much (the reader will probably still run high). But my understanding around how this all works is patchy.

I'm aware of the pitfalls of a single instance around availability/fallover etc so it would be nice to mitigate this as a bonus from this upgrade.
Switch Instance Type
If I switch instance types, a db.r5.large is twice the cost of a db.t3.medium but still only has 2 x vCPU. It has a lot more Memory (16 v 4) but my issue is CPU.
I'm not sure how the ECU (the actual relative measure of processing power) compares on db.r5.large vs db.t3.medium as it's documented as 'variable', so not sure if I'm comparing apples with oranges between the r5 and the t3.

db.r5.large ECU = 10
db.t3.medium ECU = Varaible

The db.r5.xlarge has 4 vCPU but would quadruple my RDS costs.
Sidenote:
I have 9 months left on a t3.medium reserved instance, so would lose that if I do anything other than setup a second t3 instance.
Serverless
Based on the fairly vague documentation around serverless resources (1 ACU has approximately 2 GB of memory with corresponding CPU and networking, similar to what is used in Aurora user-provisioned instances.), I would guestimate 2 ACUs is the equivalent of a db.t3.medium, so 4 ACUs would give me double the CPU as my current setup, for roughly the same cost as a db.r5.large.
Serverless would throw in the added benefit of built-in availability and replication.
The RDS load is fairly steady, rather than spiky, so I wouldn't benefit from any time the Serverless scales down to 1 ACU for example.
What's best?
What would you do, and why?!
Thanks!
Additional Info

Any SSD or NVME devices on MySQL Host server? Not sure - it's just a standard AWS RDS instance but not sure what default devices are used.
SHOW GLOBAL STATUS; https://pastebin.com/xkTaPKKF
SHOW GLOBAL VARIABLES; https://pastebin.com/CGjeGJCv
SHOW FULL PROCESSLIST; https://pastebin.com/T8YveG5q
SHOW ENGINE INNODB STATUS; https://pastebin.com/EkQpyqui
SELECT name, count FROM information_schema.innodb_metrics ORDER BY name; https://pastebin.com/kemMrNpS
MySQl Error Log - last 12 hours - all of which were generated during a restart of the server: https://pastebin.com/A6sV0Qxc

Analysis of STATUS and VARIABLES:
Observations:

Version: 5.6.10
4 GB of RAM
Uptime = 68d 06:45:29
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:
innodb_buffer_pool_size could be increased, but not so much as to cause swapping.  (If you don't have much data, then increasing it won't make any difference.)
If the disk is SSD, increase innodb_io_capacity to 500.  (The I/O seems low, so this may not have any impact.)
max_allowed_packet is 12% of RAM; suggest lowering it to 1% of RAM unless you need a huge packet size.
Some things indicate very little I/O, as most of the work is in the CPU.  This further pushes toward studying the slow queries.
The Query cache (query_cache_type) is ON.  And there is some indication of heavy activity with it.  This may be adding to the CPU for "pruning" or it may be helping with the CPU.  (I can't tell which.)  Suggest you change to DEMAND; at the same time, add SQL_CACHE to queries that are likely to benefit from the QC and SQL_NO_CACHE to the others.  This goes right after SELECT.  These changes will make the QC more focused, thereby (hopefully) decreasing the CPU usage on both sides.
If you turn on the slowlog (which I recommend), be sure to lower long_query_time to, say 1.
Details and other observations:
( Key_blocks_used * 1024 / key_buffer_size ) = 22 * 1024 / 16M = 0.13% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size (now 16777216) to avoid unnecessary memory usage.
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((16M / 0.20 + 1513M / 0.70)) / 4096M = 54.7% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain
-- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)
( innodb_change_buffering ) = innodb_change_buffering = none
-- Pre-5.6.11 / 5.5.31, there was a bug that made ="changes" a safer option.
( innodb_doublewrite ) = innodb_doublewrite = OFF -- Extra I/O, but extra safety in crash.
-- OFF is OK for FusionIO, Galera, Replicas, ZFS.
( Innodb_os_log_written ) = 512 / 5899529 = 0.31 /HR -- This is an indicator of how busy InnoDB is.
-- Very idle InnoDB.
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 512 / (5899529 / 3600) / 2 / 48M = 3.1e-9 -- Ratio
-- (see minutes)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 5,899,529 / 60 * 48M / 512 = 9.67e+9 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 50331648). (Cannot change in AWS.)
( Handler_rollback ) = 68,089,346 / 5899529 = 12 /sec
-- Why so many rollbacks?
( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.
( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = OFF -- Usually should be ON.
-- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890).
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.
( max_allowed_packet ) = 512M / 4096M = 12.5%
-- If you do not have large blobs (etc) to load, then decrease the value. Else decrease innodb_buffer_pool_size (now 1586495488) to make room. Swapping is terrible for performance.
( innodb_ft_result_cache_limit ) = 2,000,000,000 / 4096M = 46.6% -- Byte limit on FULLTEXT resultset. (Possibly not preallocated, but grows?)
-- Lower the setting.
( character_set_server ) = character_set_server = latin1
-- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( Qcache_lowmem_prunes ) = 142,717,868 / 5899529 = 24 /sec -- Running out of room in QC
-- increase query_cache_size (now 88158208)
( Qcache_lowmem_prunes/Qcache_inserts ) = 142,717,868/210841277 = 67.7% -- Removal Ratio (frequency of needing to prune due to not enough memory)
( Qcache_not_cached ) = 624,955,002 / 5899529 = 105 /sec -- SQL_CACHE attempted, but ignored
-- Rethink caching; tune qcache
( Qcache_not_cached / (Qcache_hits + Com_select + Qcache_not_cached) ) = 624,955,002 / (608264262 + 835804146 + 624955002) = 30.2% -- Percent of SELECTs that were not cached in the QC.
-- QC is not very useful.
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (88158208 - 1227312) / 34276 / 8192 = 0.31 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size (now 8192)
( Select_scan ) = 171,787,477 / 5899529 = 29 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( Select_scan / Com_select ) = 171,787,477 / 835804146 = 20.6% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries
( relay_log_space_limit ) = 1,000,000,000 = 953.7MB -- The max total size for relay logs on a Replica. (0=unlimited)
-- Let's discuss the rationale for having a limit.
( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)
( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
Abnormally small:
( Innodb_pages_read + Innodb_pages_written ) / Uptime = 0.0118
Innodb_buffer_pool_bytes_data = 262 /sec
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0
Innodb_buffer_pool_pages_misc = 0
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0
Innodb_data_fsyncs = 0
Innodb_data_read = 192 /sec
Innodb_data_reads = 1.3 /HR
Innodb_data_writes = 1.3 /HR
Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 1.3 /HR
Innodb_data_written = 0
Innodb_dblwr_pages_written = 0
Innodb_log_write_requests = 0
Innodb_os_log_fsyncs = 0
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 0.0MB
Innodb_pages_read = 42 /HR
Innodb_pages_read + Innodb_pages_written = 42 /HR
Innodb_pages_written = 0
host_cache_size = 128

Abnormally large:
(query_cache_size - Qcache_free_memory) / query_cache_size = 98.6%
1 - Qcache_free_memory / query_cache_size = 98.6%
Com_create_trigger = 0.00061 /HR
Com_drop_trigger = 0.00061 /HR
Com_empty_query = 0.06 /HR
Com_flush = 21 /HR
Com_purge_before_date = 12 /HR
Com_rename_user = 0.00061 /HR
Com_revoke = 0.0012 /HR
Com_show_tables = 1.6 /sec
Handler_read_last = 1.6 /sec
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ) = 100.0%
Qcache_total_blocks = 118,191
Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache = 14,123
Ssl_accepts = 172
Ssl_default_timeout = 7,200
Ssl_finished_accepts = 166
Ssl_session_cache_misses = 172
Ssl_verify_depth = 1.84e+19
Ssl_verify_mode = 5
back_log / max_connections = 75.6%
innodb_stats_persistent_sample_pages = 128
table_definition_cache = 5,383

Abnormal strings:
core_file = ON
ft_boolean_syntax = + -><()~*:\"\"&
innodb_checksums = OFF
innodb_fast_shutdown = 1
innodb_use_native_aio = OFF
log_output = TABLE
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
relay_log_recovery = ON
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
thread_handling = multiple-connections-per-thread
time_zone = Pacific/Auckland


MongoDB replica set configuration error I am trying to set up a MongoDB replica set on Digital Ocean.

I edited /etc/mongo.conf and specified replica set id:

replSet=rs1

and did not change anything else.

I did rs.initiate() on the first instance. Here is my config:

{
"_id" : "rs1",
"version" : 2,
"members" : [
    {
        "_id" : 1,
        "host" : "localhost:27017",
        "arbiterOnly" : false,
        "buildIndexes" : true,
        "hidden" : false,
        "priority" : 1,
        "tags" : {

        },
        "slaveDelay" : 0,
        "votes" : 1
    }
],
"settings" : {
    "chainingAllowed" : true,
    "heartbeatTimeoutSecs" : 10,
    "getLastErrorModes" : {

    },
    "getLastErrorDefaults" : {
        "w" : 1,
        "wtimeout" : 0
    }
}

}

Now, I want to add two more instances, but I see the error:

{
"ok" : 0,
"errmsg" : "Either all host names in a replica set configuration must be localhost references, or none must be; found 1 out of 2",
"code" : 103

}

How can I change the host to the server's ip address? If I try reconfig I get:

{
"ok" : 0,
"errmsg" : "No host described in new configuration 3 for replica set rs1 maps to this node",
"code" : 103

}

/etc/hostname contents:

mongodb-1

/etc/hosts contents:

# Your system has configured 'manage_etc_hosts' as True.
# As a result, if you wish for changes to this file to persist
# then you will need to either
# a.) make changes to the master file in /etc/cloud/templates/hosts.tmpl
# b.) change or remove the value of 'manage_etc_hosts' in
#     /etc/cloud/cloud.cfg or cloud-config from user-data
127.0.1.1 mongodb-1 mongodb-1
127.0.0.1 localhost

# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts

The problem is clearly stated: the first replica set member is bound to localhost.

Since with this name the other replica set members could not connect to this instance, you can only have other replica set members on the same host (but a different port for obvious reasons).

Chances are that the MongoDB instances at DO are bound to localhost by default. You need to bind them to a different IP address using the net.bindIp configuration option. Please keep in mind that this makes you MongoDB instance accessible to everybody being able to connect to the chosen port. You should enable authentication if a MongoDB instance is bound to a different IP than localhost.


Record inheritance in mySQL I have been struggling with this for a little while.

I want to have a set of calendar records.  The top level will apply to a large group; the next level will apply to a sub-set and the final level could apply to a single person.

The top level might have a record that looks like this:

ID : LinkID : Date     : Venue         :  Training       : Group
1  : NULL   : 26/2/16  : Running Track :  4 x 800m       : AC Seniors

The next level might have a record that looks like this:

ID : LinkID : Date     : Venue         :  Training       : Group
2  : 1      :          :               :  5 x 1000m      : AC Elite Men

The bottom level could look like this:

ID : LinkID : Date     : Venue         :  Training       : Group
3  : 2      :          : Local park    :  5 x 1000m      : Jon Runner

The idea is that the coach can set up a general training programme (top level) that is then edited for a sub group (middle level) and then individualised.

Jon Runner would see this record:

ID : LinkID : Date     : Venue         :  Training       : Group
3  : 2      : 26/2/16  : Local park    :  5 x 1000m      : Jon Runner

If any of the higher level information changes, then there is a need to flag further down the tree.

I have a good idea how to do this in Object Oriented Classes, but struggle to see how to implement it so that it can be stored in a database.

Any ideas or tips would be great.

If you want to store it as a hierarchical structure, the pattern that would best serve you is the "Closure Table" pattern, which allows for subtree operations (select/update/delete all descendants of x).

A generic example (closure table c and data table d) of what flagging an entry and all children at all levels below it would look like this:

UPDATE d
INNER JOIN c ON c.descendant_id = d.id
SET d.flag=1
WHERE c.ancestor_id = @thetopmostdesiredentry;

Here are some well written resources on how to implement that:

https://www.percona.com/blog/2011/02/14/moving-subtrees-in-closure-table/
https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html

The best place to read about it is Bill Karwin's book "SQL Anti-patterns", which details it as an alternative to less savory patterns.


Unexplained InnoDB timeouts I've been seeing some very basic updates timing out lately and have not been able to determine the cause. An example:

//# Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

UPDATE photos SET position = position + 1 WHERE (photo_album_id = 40470);

The same log has no entries with a Lock_time > 0. Running show innodb status does not reveal any related locks either. This problem appears to be affecting at least 5 different tables based on my app server logs (which show a Mysql::Error: Lock wait timeout exceeded error related to each corresponding entry in the mysql-slow log).

Any idea on where to go from here? I'm hitting dead-ends in all directions. Thanks.

EDIT:

CREATE TABLE `photos` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(255) NOT NULL,
  `photo_album_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) default 'Untitled',
  `description` text,
  `credit` varchar(255) default NULL,
  `photo_file_name` varchar(255) default NULL,
  `photo_content_type` varchar(255) default NULL,
  `photo_file_size` int(11) default NULL,
  `photo_updated_at` datetime default NULL,
  `position` int(11) default '0',
  `views` int(11) default '0',
  `folder` varchar(255) default NULL,
  `published` tinyint(1) default '0',
  `published_at` datetime default NULL,
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  `album_published` tinyint(1) default '0',
  `comment_count` int(11) default '0',
  `audio_file_name` varchar(255) default NULL,
  `audio_content_type` varchar(255) default NULL,
  `audio_file_size` int(11) default NULL,
  `audio_updated_at` datetime default NULL,
  `cover` tinyint(1) default '0',
  `slug` varchar(255) default NULL,
  `comments_count` int(11) default '0',
  `delete_from_s3` tinyint(1) default '0',
  `batch` int(11) default NULL,
  `audio` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `index_photos_on_album_published` (`album_published`),
  KEY `index_photos_on_batch` (`batch`),
  KEY `index_photos_on_comment_count` (`comment_count`),
  KEY `index_photos_on_created_at` (`created_at`),
  KEY `index_photos_on_delete_from_s3` (`delete_from_s3`),
  KEY `index_photos_on_photo_album_id` (`photo_album_id`),
  KEY `index_photos_on_published` (`published`),
  KEY `index_photos_on_published_at` (`published_at`),
  KEY `index_photos_on_type` (`type`),
  KEY `index_photos_on_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=42830 DEFAULT CHARSET=utf8

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).


ORA-01653 Unable to Extend Table AGILE.A_DW_TXN_LOG BY 128 in Tablespace AGILE_DATA3 I am trying to import an oracle database from a dump file. While importing I am getting ORA-01653 Unable to Extend Table AGILE.A_DW_TXN_LOG BY 128 in Tablespace AGILE_DATA3.
I have tried to fetch the information about the tablespace with this query and I am not able to understand what these columns hold information about.
SELECT * FROM DBA_DATA_FILES WHERE Tablespace_name = 'AGILE_DATA3';

FILE_NAME                                  |FILE_ID|TABLESPACE_NAME|BYTES      |BLOCKS |STATUS   |RELATIVE_FNO|AUTOEXTENSIBLE|MAXBYTES   |MAXBLOCKS|INCREMENT_BY|USER_BYTES |USER_BLOCKS|ONLINE_STATUS|
-------------------------------------------|-------|---------------|-----------|-------|---------|------------|--------------|-----------|---------|------------|-----------|-----------|-------------|
D:\APP\ORADATA\AG934\AGILE_DATA301AG934.ORA|     11|AGILE_DATA3    |26791116800|3270400|AVAILABLE|          11|YES           |34359721984|  4194302|        1280|26789019648|    3270144|ONLINE       |

I looked up at this link but as I look into my table I find Auto Extension already enabled. What is it I am not doing correctly here ? I am using data pump utility available in sql developer to import the dump file.
Also I hope that when we import the dump file it actually overwrites all of the information already present on the database. Is that correct ?

The error is stating that either all of your tablespace data files have reached the limit of their size supported by the OS (usually 32GB each) and cannot grow, or that the storage media has physically run out of space and they cannot grow. Since the size of the tablespace appears to be about 25GB (the BYTES column) and the upper limit is 32GB (the MAXBYTES column), I'd say it is most likely that your disk is full.
That said, datapump will not automatically empty tables unless you tell it to. By default it will skip existing tables entirely and not load anything. Use the TABLE_EXISTS_ACTION parameter to tell datapump what to do in cases where tables already exist, as described here: https://docs.oracle.com/database/121/SUTIL/GUID-C9664F8C-19C5-4177-AC20-5682AEABA07F.htm#SUTIL936


SELECTing multiple columns through a subquery I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them.

SELECT
  DISTINCT petid,
  userid,
  (SELECT MAX(comDate) FROM comments WHERE petid=pet.id) AS lastComDate,
  (SELECT userid FROM comments WHERE petid=pet.id ORDER BY id DESC LIMIT 1) AS lastPosterID
FROM
  pet LEFT JOIN comments ON pet.id = comments.petid
WHERE
  userid='ABC'      AND
  deviceID!='ABC'   AND
  comDate>=DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MONTH);

Basically, I am trying to get the lastComDate & lastPosterID from the same row - the row which is the latest one in comments for the specific pet. Please suggest how can I get them in an efficient way.

The above query works, but seems overkill as same row is fetched twice. Moreover, the ORDER BY clause is significantly slower than the aggregate function - as I found while profiling query. So, a solution avoiding sorting would be appreciated.

SELECT DISTINCT petid, userid, lastComDate, lastPosterId
FROM
    pet
    LEFT JOIN comments ON pet.id = comments.petid
    LEFT JOIN (
        SELECT MAX(comDate), userid, petid FROM comments GROUP BY userid
    ) a ON a.petid = pet.id
WHERE
    userid='ABC'
    AND deviceID!='ABC'
    AND comDate>=DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MONTH)
;

You can also pull your subquery out into a temp table if performance becomes impacted somewhere down the road.


Is it possible to create an alias or synonym for a database? Is it possible to create a database alias or synonym?  What I am hoping to be able to do is have NewDatabaseName aliased as OldDatabaseName to prevent having to rewrite a ton of code.

Backstory:
I was approached by a client with a ridiculous self-induced problem.  They have a third-party application that has a SQL server back-end.  The users have been writing custom reports against this database using Visual Basic, with all database connection information hard-coded in their programs.

Recently the third party software renamed their database.  Now all the report EXE's are failing.

Oh, they dont' have a lot of the source code either.

I held back my laughter and said I would see what I could do.

No this isn't possible (Related Feedback Item)
You could create a new database of the original name and fill that with synonyms pointing to all the objects in the renamed database though.


postgres filtering select based on combination of joins I am having trouble filtering a request that depends on a combination of child elements (one-to-many relationship)

Please consider the following example.

CREATE TABLE sample_container
(
  id serial NOT NULL,
  owner_id integer NOT NULL,
  owner_type character varying(255) NOT NULL,
  CONSTRAINT sample_container_pkey PRIMARY KEY (id)
)

CREATE TABLE sample_link
(
  id serial NOT NULL,
  owner_type character varying(255) NOT NULL,
  label character varying(255),
  owner_id integer NOT NULL,
  sample_container_id integer NOT NULL,
  CONSTRAINT sample_link_pkey PRIMARY KEY (id),
  CONSTRAINT sample_link_sample_container_id FOREIGN KEY (sample_container_id)
      REFERENCES sample_container (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

sample_container
id  owner_type
1   glue_batch
2   glue_batch
3   glue_batch

sample_link
id  owner_type      owner_id    sample_container_id
1   project         1           1
2   raw_material    1           1
3   project         1           2
4   raw_material    2           2
5   project         2           3
6   raw_material    2           3

What I need to do is select every sample_container which "owns" a sample_link where project and material match.

So for example, if I search for project=1 && raw_material=2, I would want it to return container 2, but not container 1 or 3.

So far I can all the containers with:

SELECT DISTINCT sample_container.* FROM sample_container
    INNER JOIN sample_link ON sample_container.id = sample_link.sample_container_id
    WHERE sample_container.owner_type = 'glue_batch' AND (
        ((sample_link.owner_type = 'project' AND sample_link.owner_id = 1) OR
        (sample_link.owner_type = 'raw_material' AND sample_link.owner_id = 2) )
    )

Or no containers with:

SELECT DISTINCT sample_container.* FROM sample_container
    INNER JOIN sample_link ON sample_container.id = sample_link.sample_container_id
    WHERE sample_container.owner_type = 'glue_batch' AND (
        ((sample_link.owner_type = 'project' AND sample_link.owner_id = 1) AND
        (sample_link.owner_type = 'raw_material' AND sample_link.owner_id = 2) )
    )

(notice the change on the OR)

Any ideas on how I should go about this?

Cheers and thank you for your time!

PS: technically I am building the query dynamically from an ORM, but I should be able to do that just fine if I can get a working select statement to go from

The schema is basically Entity-Attribute-Value (EAV), and the same query pattern applies: Use repeated joins with different filters to obtain the desired information.

SELECT *
FROM sample_container sc
INNER JOIN sample_link sl_proj
  ON (sc.id = sl_proj.sample_container_id
      AND sl_proj.owner_type = 'project'
      AND sl_proj.owner_id = 1)
INNER JOIN sample_link sl_rm
  ON (sc.id = sl_rm.sample_container_id
      AND sl_rm.owner_type = 'raw_material'
      AND sl_rm.owner_id = 2);

See: http://sqlfiddle.com/#!15/5b309/3

Like most EAV schemas, this will scale very poorly to large numbers of attributes, and requires dynamic SQL most of the time. Consider whether EAV is the right choice for you.


Convert YYYY-MM-DD date (without time zone) to unix timestamp in Postgres I've got a column of type date with the following value format: YYYY-MM-DD (eg. 2018-08-03). I want to get the unix timestamp equivalent of the dates, but without the timezone.

F.e. the date 2018-08-03 should equal 1533254400 (as of calculated here).

I've tried to_timestamp(my_date_column, 'YYYY-MM-DD') but it returns error function to_timestamp(date, unknown) does not exist. I've tried adding different variations of ::timestamp... at the end of the function call, but to no avail.

Currently I do the timestamp conversion in the application layer, but I'd like to dedicate that job to the database.

Alright... after a change in my approach, I decided to try to "extract" data instead of convert. Turned out there's the oh-so convenient function extract in Postgres, which does exactly what I want.

extract(EPOCH from my_date_column) as date_timestamp

Here is a more detailed explanation.


How to effectively limit a selection size in a generic manner in Oracle? Let's say, we have the a query to an Oracle database:

SELECT *
FROM FOO f
WHERE f.bar > baz
ORDER BY f.qux;

And we are looking for a way to limit the selection size in a generic manner.

The approach of wrapping a query in the following way may be applied:

SELECT *
FROM (
  SELECT *
  FROM FOO f
  WHERE f.bar > baz
  ORDER BY f.qux
)
WHERE ROWNUM <= 1;

Though, it is easy to employ, it may be shortened to the following one with a small price of the input query analysis (to place AND ROWNUM <= 1 at the right position):

SELECT *
FROM FOO f
WHERE f.bar > baz
    AND ROWNUM <= 1
ORDER BY f.qux;

Would the later query practically perform better? Would it affect caching & optimizing in any significant way?

SELECT *
FROM (
  SELECT *
  FROM FOO f
  WHERE f.bar > baz
  ORDER BY f.qux
)
WHERE ROWNUM <= 1;

Means: order the resultset by f.qux then return the 1st row based on that order.

SELECT *
FROM FOO f
WHERE f.bar > baz
    AND ROWNUM <= 1
ORDER BY f.qux;

Means: get the 1st row by unspecified order, then order the result. Order by has no effect at all in this specific example.

They are different.

Use the first one, or starting with 12c, you can use the below:

SELECT *
FROM FOO f
WHERE f.bar > baz
ORDER BY f.qux
FETCH FIRST 1 ROW ONLY; -- FETCH FIRST 1 ROWS ONLY, FETCH FIRST n ROWS ONLY also works


Uniqueness constraint for elements of arrays in the entire table In the following table:
CREATE TABLE user (
    email           varchar[],
    ...
);

I'd like to ensure that no two users have the same e-mail. I'm not sure how the UNIQUE constraint interacts with array data types. What's the appropriate way to ensure uniqueness? Or is this an anti-pattern and should I just declare another emails table instead?

Yes, as you said, I'd create an emails table that refer to the users.
CREATE TABLE users (
    id_user         ... ,
    username        varchar(255),
    ... ,
    PRIMARY KEY(id_user)
);

CREATE TABLE emails (
    id_email        ... ,
    id_user         ... ,
    email           varchar(255),
    ...,
    PRIMARY KEY(id_email),
    CONSTRAINT fk_user
      FOREIGN KEY(id_user)
      REFERENCES users(id_user)
);

Then in the emails table you can add a unique constraint on id_user, id_email.
CREATE UNIQUE INDEX CONCURRENTLY xak1_unique_id_user_and_email_ix
ON emails (id_user, id_email);

ALTER TABLE emails ADD CONSTRAINT xak1_unique_id_user_and_email UNIQUE
                      USING INDEX xak1_unique_id_user_and_email_ix;

P.S. I don't work usually on PostgreSQL, for the syntax I referred to link


PostgreSQL truncating values of real numeric type Using PostgreSQL 9.2 on Windows Server 2007, I have the following table:

CREATE TABLE aaa_dens (read_no INT, position REAL PRIMARY KEY);

Some values in position have 4 or more digits after the decimal point, as shown from a dump:

$ head dump.txt
"read_no"       "position"
    "1"     "150.83"
    "2"     "150.8308"
    "3"     "150.8316"
    "4"     "150.8324"
    "5"     "150.83321"
    "6"     "150.834"
    "7"     "150.83479"
    "8"     "150.8356"
    "9"     "150.8364"

However, SELECT * FROM aaa_dens; shows only a max of 3 digits after the decimal point:

 read_no | position
---------+----------
       1 |   150.83
       2 |  150.831
       3 |  150.832
       4 |  150.832
       5 |  150.833
       6 |  150.834
       7 |  150.835
       8 |  150.836
       9 |  150.836

Furthermore, trying to convert position to numeric(8,4) yields an error, as if it had rounded reads 3 and 4 to 150.832:

ALTER TABLE aaa_dens ALTER COLUMN position TYPE NUMERIC(8,4);
ERROR:  could not create unique index "pk_aaa_dens"
DÉTAIL : Key ("position")=(150.8320) is duplicated.

What exactly is happening here and how to fix it?

From https://www.postgresql.org/docs/9.2/static/datatype-numeric.html we read:

Name    Storage Size    Description                 Range
real    4 bytes     variable-precision, inexact     6 decimal digits precision

and precision is defined as such: "The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point" (same definition for all types, note how in the same table decimal and numeric have their precision defined separately from left and right of the decimal point)

You do have below:

  On most platforms, the real type has a range of at least 1E-37 to 1E+37 with a precision of at least 6 decimal digits.

But that (more than 6 digits of precision) depends on both OS, processor, and compilation.

From your set, you have many numbers over 6 digits in precision, and unfortunately in your case you seem to have exactly only 6 digits available to you as precision, hence all these numbers are not handled as you wrote them:

"2"     "150.8308"
"3"     "150.8316"
"4"     "150.8324"
"5"     "150.83321"
"7"     "150.83479"
"8"     "150.8356"
"9"     "150.8364"

You have this explicit warning in the documentation:

  Rounding might take place if the precision of an input number is too high.

Which explains your observation as if it had rounded reads 3 and 4 to 150.832 since 3 is 150.8316 (rounds naturally to 150.832 because the final 6 is more than 5) and 150.8324 (rounds naturally to 150.832 because the final 4 is less than 5)

See also the bit about the extra_float_digits configuration item.

You solve the problem going to double precision because in that case you have 15 digits of precision...

But the "real"(!) solutions are:

if possible, upgrade your PostgreSQL as 9.2 is not supported anymore,
and more specific to your problem, use appropriate data types, if you need fixed precision for your data set you need at least numeric(8,5) right from the beginning.


What difference between these 2 SQL styles? If I have a simple table named: conn_log in my Redshift/Postgresql

In the performance of view, what difference between these two commands ?

select t1.wifi_name, (t2.sconn*100)::numeric/t1.ttconn
from (select wifi_name, count(*) as ttconn
      from conn_log
      group by wifi_name) t1,
     (select wifi_name, count(*) as sconn
      from conn_log
      where success = 1
      group by wifi_name) t2
where t1.wifi_name = t2.wifi_name;

second query:

select t1.wifi_name, (t2.sconn*100)::numeric/t1.ttconn
from (select wifi_name, count(*) as ttconn
      from conn_log
      group by wifi_name) t1
join
     (select wifi_name, count(*) as sconn
      from conn_log
      where success = 1
      group by wifi_name) t2
on t1.wifi_name = t2.wifi_name

As for the difference between INNER JOIN...ON vs WHERE clause, there is a good answer here. There are several answers there and the accepted answer pretty much summarises it all.

However, I cannot but comment that you query can be rewritten to significantly improve the performance, like this:

select wifi_name
      ,sum(case when success = 1 then 1 else 0 end)*100/count(*) as success_rate
from conn_log
group by wifi_name;

In PostgreSQL 9.4+, it is even simpler:

select
    wifi_name,
    count(*) filter (where success = 1) / count(*) * 100 as success_rate
from conn_log
group by wifi_name;


need help in Query optimization I need help in query optimization,I am having table with structure

Create Table: CREATE TABLE `ip_country_mapping` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cron` bigint(20) NOT NULL,
  `start_ip_number` bigint(20) NOT NULL,
  `end_ip_number` bigint(20) NOT NULL,
  `country` varchar(2) COLLATE utf8_bin NOT NULL,
  `state` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `city` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `zip` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `last_updation_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_IP_COUNTRY_MAPPING_TEMP_START_IP_NUMBER` (`start_ip_number`),
  UNIQUE KEY `UK_IP_COUNTRY_MAPPING_TEMP_END_IP_NUMBER` (`end_ip_number`),
  KEY `FK_IP_COUNTRY_MAPPING_TEMP_CRON` (`cron`),
  KEY `ind_ipscan` (`end_ip_number`,`start_ip_number`),
  CONSTRAINT `FK_IP_COUNTRY_MAPPING_TEMP_CRON` FOREIGN KEY (`cron`) REFERENCES `cron` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2020168 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

The query to optimized is

SELECT ipcountrym0_.country as col_0_0_, ipcountrym0_.state as col_1_0_,ipcountrym0_.city as col_2_0_
FROM ip_country_mapping ipcountrym0_
WHERE ipcountrym0_.start_ip_number<=1376791568
AND ipcountrym0_.end_ip_number>=1376791568;

EXPLAIN plan is giving the output as

EXPLAIN SELECT ipcountrym0_.country as col_0_0_, ipcountrym0_.state as col_1_0_, ipcountrym0_.city as col_2_0_
FROM ip_country_mapping ipcountrym0_
WHERE ipcountrym0_.start_ip_number<=1376791568
AND ipcountrym0_.end_ip_number>=1376791568;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: ipcountrym0_
             type: ALL
    possible_keys: UK_IP_COUNTRY_MAPPING_TEMP_START_IP_NUMBER,UK_IP_COUNTRY_MAPPING_TEMP_END_IP_NUMBER,ind_ipscan
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 2081584
            Extra: Using where

If i add limit 1 to the above query,it scans 199999 rows.The output generated by this query is only one row.if i put order by clause in the above query with limit 1 then it scans only 2 rows.

EXPLAIN SELECT ipcountrym0_.country as col_0_0_, ipcountrym0_.state as col_1_0_, ipcountrym0_.city as col_2_0_
FROM ip_country_mapping ipcountrym0_
WHERE ipcountrym0_.start_ip_number<=1376791568
AND countrym0_.end_ip_number>=1376791568
ORDER BY id LIMIT 1;
        *************************** 1. row ***************************
                   id: 1
          select_type: SIMPLE
                table: ipcountrym0_
                 type: index
        possible_keys: UK_IP_COUNTRY_MAPPING_TEMP_START_IP_NUMBER,UK_IP_COUNTRY_MAPPING_TEMP_END_IP_NUMBER,ind_ipscan
                  key: PRIMARY
              key_len: 8
                  ref: NULL
                 rows: 2
                Extra: Using where
        1 row in set (0.00 sec)

Is this the correct way my query is optimizing or there is some thing wrong the way i had tried to optimize.
My Manager is are not agreeing  with this optimization as he consider that the optimizer is first scanning the index column id and then the start ip number and end ip number which is not relevant.

Can somebody please explain how optimizer is working here and is this correct way the optimizing the query .It is argued that optimizer is showing wrong plan.

Create index on (start_ip_number,end_ip_number) and then check the Explain Plan.The query should user this newly created index.

You can also drop index  ind_ipscan (end_ip_number,start_ip_number) if you are not using it some where else.


Stored procedure to output date differences I have a table of records which, in very simplified form, comprise a pair of dates, well actually let's say three dates:

ID
creation_date
process_date
done_date

and I am required to output records of counts as follows:

ID  P=C  P=C+1   P=C+2+   D=C   D=C+1   D=C+2+

where "P=C+1" for example is the number of records with a specific ID for which process_date equals creation_date + 1, and similarly for the other columns (and C+2+ indicates two or more days after creation_date).

So being a bit shaky with stored procedure syntax, and even the SQL statement required for this, using temp tables (presumably), I would welcome any help.

Typical output might be as follows, grouping by ID and using vaguely plausible values off the top of my head:

ID   P=C  P=C+1   P=C+2+   D=C   D=C+1   D=C+2+
XY   130   29       4      140    13
AB    16    3               27    19       2
   :::::::::::::::::::::::::::::::::::

Using a CASE expression, I think you could do something like this:

Declare @T table (ID int,creation_date date,process_date date,done_date date)

insert into @T(id,creation_date,process_date,done_date) values
(1,'2018-01-01','2018-01-01','2018-01-01')
,(1,'2018-01-01','2018-01-01','2018-01-01')
,(1,'2018-01-01','2018-01-03','2018-01-01')
,(1,'2018-01-01','2018-01-03','2018-01-02')
,(1,'2018-01-01','2018-01-03','2018-01-03')

SELECT ID
    ,sum(CASE
            WHEN process_date = creation_date
                THEN 1
            ELSE 0
            END) AS 'P=C'
    ,sum(CASE
            WHEN process_date = (dateadd(day, 1, creation_date))
                THEN 1
            ELSE 0
            END) AS 'P=C+1'
    ,sum(CASE
            WHEN process_date >= (dateadd(day, 2, creation_date))
                THEN 1
            ELSE 0
            END) AS 'P=C+2+'
    ,sum(CASE
            WHEN done_date = creation_date
                THEN 1
            ELSE 0
            END) AS 'D=C'
    ,sum(CASE
            WHEN done_date = (dateadd(day, 1, creation_date))
                THEN 1
            ELSE 0
            END) AS 'D=C+1'
    ,sum(CASE
            WHEN done_date >= (dateadd(day, 2, creation_date))
                THEN 1
            ELSE 0
            END) AS 'D=C+2+'
FROM @T
GROUP BY ID

| ID | P=C | P=C+1 | P=C+2+ | D=C | D=C+1 | D=C+2+ |
|----|-----|-------|--------|-----|-------|--------|
| 1  | 2   | 0     | 3      | 3   | 1     | 1      |


Mongo replica set with no PRIMARY/SECONDARY, members are STARTUP2 and RECOVERING I have a mongo cluster with 6 replica sets.  5 are fine, one is not.  Each replica set has three members.  Here is the rs.status() for it:

{
    "set" : "rs_5",
    "date" : ISODate("2015-12-16T02:37:39Z"),
    "myState" : 5,
    "members" : [
        {
            "_id" : 0,
            "name" : "mongo_rs_5_member_1:27018",
            "health" : 1,
            "state" : 5,
            "stateStr" : "STARTUP2",
            "uptime" : 33600,
            "optime" : Timestamp(0, 0),
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("2015-12-16T02:37:38Z"),
            "lastHeartbeatRecv" : ISODate("2015-12-16T02:37:37Z"),
            "pingMs" : 0,
            "lastHeartbeatMessage" : "initial sync need a member to be primary or secondary to do our initial sync"
        },
        {
            "_id" : 1,
            "name" : "mongo_rs_5_member_2:27019",
            "health" : 1,
            "state" : 3,
            "stateStr" : "RECOVERING",
            "uptime" : 33842,
            "optime" : Timestamp(1449898728, 18),
            "optimeDate" : ISODate("2015-12-12T05:38:48Z"),
            "lastHeartbeat" : ISODate("2015-12-16T02:37:37Z"),
            "lastHeartbeatRecv" : ISODate("2015-12-16T02:37:37Z"),
            "pingMs" : 3,
            "lastHeartbeatMessage" : "still syncing, not yet to minValid optime 566bb328:3"
        },
        {
            "_id" : 2,
            "name" : "mongo_rs_5_member_3:27020",
            "health" : 1,
            "state" : 5,
            "stateStr" : "STARTUP2",
            "uptime" : 33845,
            "optime" : Timestamp(1449898728, 18),
            "optimeDate" : ISODate("2015-12-12T05:38:48Z"),
            "errmsg" : "still syncing, not yet to minValid optime 566bb327:1",
            "self" : true
        }
    ],
    "ok" : 1
}

In the logs, I see stuff like:

Wed Dec 16 02:40:34.033 [rsMgr] replSet I don't see a primary and I can't elect myself

and

Tue Dec 15 21:41:27.686 [rsSync] replSet initial sync need a member to be primary or secondary to do our initial sync

Here is rs.conf():

{
    "_id" : "rs_5",
    "version" : 125967,
    "members" : [
        {
            "_id" : 0,
            "host" : "mongo_rs_5_member_1:27018",
            "priority" : 3
        },
        {
            "_id" : 1,
            "host" : "mongo_rs_5_member_2:27019",
            "priority" : 2
        },
        {
            "_id" : 2,
            "host" : "mongo_rs_5_member_3:27020"
        }
    ]
}

It has been like this for a number of days.  The cpu and the network are showing no real movement indicating that nothing is happening.  Obviously, I'd like to not lose data, what do I need to do to get this back to a healthy PRIMARY/SECONDARY/SECONDARY replica set.

I was able to resolve this by Breaking the Mirror.  Essentially, I picked one of the members, turned it off, removed the /data/local* files, turned it on, and did a rs.initiate().  At this point, I was a replica set of 1 (myself) and primary (obviously).  Then, for the other two guys, I turned them off, wiped their entire /data/* files and turned them back on.  From the original primary member, I simply added added the two new guys with rs.add("mongo_rs_5_member_1:27018") and rs.add("mongo_rs_5_member_2:27019").  Then the primary synced all the content to the other guys (many hours) and the replica set was health.  No more errors in the relevant application.


Is SQL Server Browser Service compulsory for remote access? We have a Windows machine with MS SQL Server running on it and we want to connect to the database from a script running on a Linux machine. Can we just configure Sql Server to listen to a certain port and connect to it by specifying IP, port, database name, user and password, like we would with MySql? Or do we need to run SQL Server Browser Service to achieve that? Description of what this service actually does is vague, I don't get it. We have no reason to do things like listing databases or doing any anything extra. Just connect and select.

TCP/IP connection is already enabled in the SQL Server, there is no firewall installed, ping is successful, yet the port doesn't seem to be listened to. So I was wondering if that service is what is missing.

The SQL Server Browser Service does many different things... However the main purpose of it is to look up the port number associated with a given instance of SQL Server and giving that information back to the client.

Any instance of SQL Server (assuming tcp/ip) can be identified by using the computer name or ip address and the port number. If you know both of these things (or SQL Server is listening on the default port of 1433) then you'll be able to connect without using the browser service as an intermediary.

To do so, you can connect using the name or IP and the port:

MyComputer,1500
10.1.150.8,1600 (generic randomly picked ip for display purposes only)
MyComputer (this will assume default port of 1433)

If you are not using browser, I would advise you to disable it.


How does MOLAP SSAS default processing detect changes in the relational source? When you set SQL Server SSAS to MOLAP, and default processing, it detect changes in the relational source and only loads those changes into the SSAS MOLAP storage at processing time. But how does it detect those changes in the first place?

Preferably answers will refer to some Microsoft documentation.

I contacted Chris Webb (SQL Server MVP) about this, who got back to me.

The answer is: the premise of the question was wrong. Default processing does not detect changes in the relational source.

In fact, if you have done a full process on your cube, and the data changes in the relational source, Default process will not load the new data into the MOLAP storage.

The only time default processing loads data from the relational source, is when a partition is unprocessed, or one of the indexes is out, or for some other reason certain parts of the cube have been left in an 'unprocessed' state.

So if you're scheduling a SQL Agent job to load data into the cube, and you want to use Default, you need to do something like a Process Update or a Process Add first, followed by a Process Default.

Here is the MS article: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/processing-options-and-settings-analysis-services


Set variable inside cursor for to use in select statement This is my query to create procedure, but its displaying error like :

  'DECLARE curBalRows CURSOR FOR

  select if(

What I'm missing here?

    DELIMITER $$

drop procedure if exists `getAllBalanceRowsUpdate`$$

CREATE PROCEDURE `getAllBalanceRowsUpdate`(
    IN `p_ind` INT(20),
    IN `p_scode` VARCHAR(200)
)

BEGIN

DECLARE finished INT DEFAULT 0;
DECLARE balanced DECIMAL(20,3) DEFAULT 0;
DECLARE orderId INT DEFAULT 0;

select @redeem_unit = (SELECT sum(allotted_unit) from orders where investor_id=p_ind and scheme_code=p_scode and type='R' and status in ('approved', 'awaiting'));
select @balance=0;
select @redeem_unit = round( ifnull( @redeem_unit, @redeem_unit:=0), 3 );

DECLARE curBalRows CURSOR FOR

select if( allotted_unit >= @redeem_unit, @balance:= allotted_unit - @redeem_unit, @balance:= 0 ) as balanced, if( allotted_unit >= @redeem_unit, @redeem_unit:=0, @redeem_unit:= @redeem_unit-allotted_unit ) as leftunit, order_id, allotted_unit, allotted_nav, allotted_nav*@balance as balanceAmount, datetime from orders where investor_id = p_ind and orders.scheme_code =p_scode and type in ('L', 'S') and status = 'APPROVED';

DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET finished = 1;

OPEN curBalRows;

getRows: LOOP
    FETCH curBalRows INTO balanced, orderId;
        IF finished = 1 THEN
            LEAVE getRows;
        END IF;

                UPDATE orders SET balance=balanced where order_id=orderId;

END LOOP getRows;
CLOSE curBalRows;

END $$

DELIMITER ;

DECLARE statement must posess at the beginning of BEGIN-END block, before any other statements. Use nested BEGIN-END:

DELIMITER $$

drop procedure if exists `getAllBalanceRowsUpdate`$$

CREATE PROCEDURE `getAllBalanceRowsUpdate`(
IN `p_ind` INT(20),
IN `p_scode` VARCHAR(200)
)

BEGIN

    DECLARE finished INT DEFAULT 0;
    DECLARE balanced DECIMAL(20,3) DEFAULT 0;
    DECLARE orderId INT DEFAULT 0;

    select @redeem_unit = (SELECT sum(allotted_unit) from orders where investor_id=p_ind and scheme_code=p_scode and type='R' and status in ('approved', 'awaiting'));
    select @balance=0;
    select @redeem_unit = round( ifnull( @redeem_unit, @redeem_unit:=0), 3 );

    BEGIN  -- add nested BEGIN-END block

        DECLARE curBalRows CURSOR FOR
        select if( allotted_unit >= @redeem_unit, @balance:= allotted_unit - @redeem_unit, @balance:= 0 ) as balanced, if( allotted_unit >= @redeem_unit, @redeem_unit:=0, @redeem_unit:= @redeem_unit-allotted_unit ) as leftunit, order_id, allotted_unit, allotted_nav, allotted_nav*@balance as balanceAmount, datetime from orders where investor_id = p_ind and orders.scheme_code =p_scode and type in ('L', 'S') and status = 'APPROVED';

        DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

        OPEN curBalRows;

        getRows: LOOP
            FETCH curBalRows INTO balanced, orderId;
            IF finished = 1 THEN
            LEAVE getRows;
            END IF;

            UPDATE orders SET balance=balanced where order_id=orderId;

        END LOOP getRows;
        CLOSE curBalRows;

    END;  -- finalize nested BEGIN-END block

END $$

DELIMITER ;

PS. Remember - declared construction exists only within the block where it is defined. So whole code between cursor declaration and cursor closing must be included into the block - finalizing END cannot be placed earlier.


Include stored procedure text output in the maintenance plan log I have a maintenance plan subtask in SQL Server 2012 Enterprise that executes a stored procedure (or a SQL Server agent job).

In the log file for this maintenance plan, there are a few lines for this task, such as the start- and end- date, and the SQL statement used to execute the stored procedure.

Can I somehow generate text output in the stored procedure and have it included in the log?

It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.

Does it have to be part of the log? You could just create a table and modify the stored procedure to store its output there.


Heartbeat Options with MySQL and Possibilities I would like to understand if the following scenarios are possible in Heartbeat in Linux.

Setup: Two Database Servers running Mysql in Active/Passive mode in replication mode having Heartbeat setup for HA or failover mechanism. Application connects to DB using VIP that is started at the time of Heartbeat.

Failover VIP to passive site if primary MySQL instance is shut down.
Bring down the heartbeat in primary if the role has been given to passive/secondary site in order to avoid split brain.

Setup lies on two different datacentres in a country. Master (Primary Site) and Slave (Secondary Site).

To Ronaldo

Below is my explanation to Ronaldo for the reason why I have gone for Master/Slave+Heartbeat

The customer had agreed for RPO and RTO of 10 mins.
- With Master/Slave I have tested in my test grounds using highest TPS(Transactions Per Second) of about 250 TPS and more as a stress test using DMLs. Since we have a dedicated WAN to the datacentres for this application function, I have a bandwidth of 20 to 35 MBPS. And with the testings I see that it didn't cross more than 5 to 8 MBPS at max TPS of 260 in a range of 3 to 4 hrs. And the DMLs are applied in relay log immediately with Seconds Behind Master lesser than 5.

I didn't consider DRBD to be an option here. As when the DB size grows like 1.5 TB < at a situation in a regular peak hrs and if the Primary site is down. I observe the recovery process of innodb to build up and be available for applications takes 15 to 20 mins. Which goes beyond the bar with Customer SLA mentioned in RPO.
When you compare the case in Mysql Replication even though I have seconds behind master as 10 mins (which is a very rare possibility) . Still we come under the RPO and RTO of the Customer.

Concerns in Mysql + DRBD

Also DRBD has some constraints to bring up if any logical volume gets corrupted the same situation occurs in secondary site.
Even in DRBD how do you go for an automatic failover to secondary site?
The maintanence of Production will again cause more laboratical work.

Eg: If I need to rotate a larger table in Production but not on Slave which can be treated as backup later. Incase of Master/Slave I can go for set session sql_bin_log=0; Which will not get executed in Slave. Whereas in DRBD, I need to mount slave and take backup of that huge table to tape and then put it back in DRBD mode.

The make-or-break element you should be concerned with is Replication Lag. Why ?
If you run SHOW SLAVE STATUS\G and Seconds_Behind_Master on the Slave is significantly high, failing over to such a Slave could be disastrous. Think of it:
If a Slave is 60 seconds behind, there is a 60 second window where

primary keys you need do not exist
changed column values do not yet exist
rows that should exist are not present
rows that should be deleted are still present

just to name a few possibilities.
SUGGESTION
Right after you've checked that the Master is fully down, you then need to check the Slave by running SHOW SLAVE STATUS\G to look for the following:

if Slave_IO_Running is not Yes
if Slave_SQL_Running is Yes
if Relay_Log_Space stops changing (This indicates that there are no more binlog entries coming over from the Master)
if Exec_Master_Log_Pos stops changing (This indicates that all relay log entries have been processed)

Once these four criteria have been met, the DB VIP can be assumed by the Slave.
CAVEAT
You may need to look into using MySQL with DRBD
Please read some of my earlier MySQL/DRBD posts:

Dec 26, 2012 : MySQL Replication and High Availability
Oct 17, 2012 : Setting up DRBD on an active MySQL server
Mar 29, 2011 : MySQL high availability, failover and replication with Latency

I suggest DRBD because the Slave is Replicating at the Disk Level synchronously.
A failover would just need DRBD mounting and InnoDB Crash Recovery on MySQL Startup.


Trigger After Insert is preventing insertion I have two tables that must have similar information, and one of them can be considered the base table for to other, so in order to have the info in the two tables, I create this trigger:

ALTER TRIGGER [dbo].[TR_INSERTED_READER_LOG]
ON [dbo].[TBL_READER_LOG]
AFTER INSERT
AS
IF EXISTS(SELECT * FROM INSERTED WHERE REL_ENROLLNUMBER < 9999999)
BEGIN
    SET LANGUAGE us_english
    DECLARE @DEV_NID INT
    DECLARE @DATE DATETIME
    DECLARE @USE_NID INT
    DECLARE @REL_MODE INT
    SELECT @DEV_NID = DEV_NID,
           @DATE = LOG_DATE,
           @USE_NID = REL_ENROLLNUMBER,
           @REL_MODE = REL_MODE
    FROM TBL_DEVICE
    INNER JOIN TBL_READER_LOG ON DEV_ORIGINAL_NID = RDR_NID WHERE DEV_TYPE = 1
    INSERT INTO TBL_USER_LOGS (DEV_NID, LOG_DATE, USE_NID, LOG_MODE) VALUES (@DEV_NID, @DATE, @USE_NID, @REL_MODE)
END

It works fine, however when the insert statement in the trigger has an error (like a FK violation) is preventing that the data in the first table can be inserted.

I have searched in the documentation of Microsoft and says that After Insert execute the Trigger when all the instructions has already done so, Why is preventing the insertion?

The trigger is part of the original transaction. Just because it happens after the original insert does not mean that everything doesn't get rolled back in the event of an error. A rollback does not just roll back the last thing that ran. Take this example:

BEGIN TRANSACTION;
INSERT dbo.table1 ...;
INSERT dbo.table2 ...;
ROLLBACK TRANSACTION;

Surely you don't expect the rollback to only affect the 2nd insert?
You might consider using TRY/CATCH or an INSTEAD OF trigger, which will allow you to handle other stuff without suffering from the errors. Or simply checking first
Your use of declaring a bunch of variables, assigning them from a join, and then inserting seems to be both inefficient and problematic. What happens if more than one row matches the join? You know that if you have a multi-row operation, the trigger only fires once, so you'll only end up with one arbitrary row being inserted, right? Why not:

INSERT dbo.TBL_USER_LOGS(DEV_NID, LOG_DATE, USE_NID, LOG_MODE)
  SELECT DEV_NID, LOG_DATE, REL_ENROLLNUMBER, REL_MODE -- table prefixes?
FROM dbo.TBL_DEVICE -- always use dbo prefix!
INNER JOIN dbo.TBL_READER_LOG
ON DEV_ORIGINAL_NID = RDR_NID -- which table do these columns come from?
WHERE DEV_TYPE = 1; -- which table does this column come from?

As an aside, I notice that your query doesn't seem to take the inserted pseudo-table into account. Shouldn't the insert have something to do with the row(s) actually affected by the trigger?


SQL server Profiler can be used any time? I want to run SQL Profiler for Long running queries in SQL server 2016. what is the best practice, without effecting the SQL performance in less time and more productive?

I highly recommend you use Extended Event instead of SQL Profiler trace.  Extended Events can be more targeted (regarding what you collect) and less impact on server resource (Measuring “Observer Overhead” of SQL Trace vs. Extended Events). Few resources below which should give you a good start.

SQL SERVER – Introduction to Extended Events – Finding Long Running Queries
Finding One Problem Query With Extended Events
Three Ways to Identify Long Running Queries - PASS


Save and restore specific tables in an Oracle 11.2 Database, Enterprise Edition, we are running calculations divided into 3 different "modules". These modules can be started on their own, but not parallel, so in between users are able to confirm the calculations and proceed with the next step.

Now I want to find a proper way to "save" and "restore" the tables to allow "a rollback" to the definied start before the module.

I have read the Oracle-Documentation about (garantueed) restore points but i think its way too much. Within its time of usage the process is more or less exclusive, but not garantueed. So i cannot rollback the complete database.

I found some information about the OracleWorkspaceManager but I am not sure if this tool would be something I could use.

Finally, I simply want to save and maybe restore 6 or 7 different tables. Is there an "easy" way to do this? Alternatively I am thinking of materialized views to store the information "before" each step. But that would create dependencies into the workflow I cannot image right now.

Thank you for your help.

IF you just want to be able to restore a few tables and the volume is not to high, why not just make copies of them?

You do need to set up good auditing and security around all your tables to prevent data loss, in the event that users did manual updates on tables that are going to be restored because of a process failure or something similar.

It could be that despite that failure, the user modifications are still needed and valid.
Best would be to prevent any user changes in the period where processes run that could cause the need for a restore.


SQL Server 2008R2 Database refresh in isolated environment I would greatly appreciate your help on my database "refresh" issue.

The scenario:

I have 12 databases on a QA server that were restored there from a Production server approx. 2 years ago.
Now QAs need to sync those 12 databases with databases on a Prod server; all account logins (and corresponding permissions) have to remain unchanged in QA environment - QAs only need to "refresh" databases so that data is as current as it is in Prod environment.

For security reasons there's no (and cannot be) connection between two servers (hence it is not possible to import data with append option), so I had to request DBAs on that Prod server to backup databases and to place backup files in a shared folder (already there).

My question is - what is the best way to "refresh" 12 databases in QA environment - is it to delete/drop old databases and restore them from backup files (then what would happen to the current QA server logins?) or is it to try to restore databases from backups without dropping 12 old databases and is this possible, would be data just appended to existing data and current logins stay unchanged ?

Thank you in advance for any input.

Just restore them over top.

RESTORE DATABASE db1 FROM DISK = '\\share\folder\file.bak'
  WITH REPLACE, RECOVERY;

You may have to use the MOVE option if the folder paths for data/log files don't match, e.g.

RESTORE DATABASE db1 FROM DISK = '\\share\folder\file.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'data_file_name' TO 'D:\SQLData\file.mdf',
  MOVE 'log_file_name'  TO 'E:\SQLLogs\file.ldf';

You can get the file names (and anticipate their sizes, too) from:

RESTORE FILELISTONLY FROM DISK = '\\share\folder\file.bak';


SQL Split Address Line into Separate Columns Whats the best way to split a comma list column into different columns? I am looking for a simple algorithm, heard it is best to avoid scalar value functions, as they might be slow. Searching into many methods, substring, find, splitstring; we need to run this algorithm across millions of addresses, so looking for best, optimal answer with good coding practice.

create table dbo.AddressTest (AddressLine varchar(255))

insert into dbo.AddressTest (AddressLine)
values ('123 Maple Street, Austin, Texas, 78653, 555-234-4589')

-- may not require substring, just looking for good way
select
    substring  as AddressStreet   -- Expected: 123 Maple Street
    substring(...) as City,       -- Expected: Austin
    substring(...) as State,     -- Expected: Texas
    substring(...) as ZipCode     -- Expected: 78653
    substring(...) as PhoneNumber -- Expected: 555-234-4589
from dbo.AddressTest

looking into this method also:
https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string

STRING_SPLIT() is probably best in SQL Server 2016. I've tested against 1 million rows and returns results in 12 seconds (fairly contrived test).

You can use the PIVOT operator to produce the columns from your rows following the split as there are a known number of elements.

Code to setup

CREATE TABLE #Table (ID INT IDENTITY, StreetAddress VARCHAR(MAX))

DECLARE @I INT = 1000000

WHILE (@I) > 0
BEGIN
       INSERT INTO #Table (StreetAddress) VALUES ('123 Fake St, BigCity, My State, 12345')
       SET @I = @I -1
END

Code to retrieve split address elements

SELECT ID,
       [1] AS Street,
       [2] AS City,
       [3] AS State,
       [4] AS Code
FROM
(
       SELECT ID, value, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Rn
       FROM #Table
       CROSS APPLY STRING_SPLIT(REPLACE(StreetAddress, ', ', ','), ',')
) src
PIVOT
(
       MAX(Value) FOR Rn IN ([1], [2], [3], [4])
) pvt

You can probably improve performance by eliminating the REPLACE in STRING_SPLIT, but you'll then need to TRIM your columns to remove whitespace.