All queries waiting for lock in mongodb I have 1 primary, 1 secondary and 1 arbiter instance in my mongo cluster and no sharding. The priority of secondary is lower than priority of primary to keep the same machine as primary.

During a very high load today, I saw 1176 queries in db.currentOp(). All of these queries except 1 are waiting for some lock. And these queries are on various collections (not just on 1 collection). One such query waiting for the lock is

        {
        "desc" : "conn802243",
        "threadId" : "0x2055e1a0",
        "connectionId" : 802243,
        "opid" : 946990251,
        "active" : false,
        "op" : "query",
        "ns" : "db.collection",
        "query" : {
            "foo" : "bar",
        },
        "client" : "10.240.0.14:49355",
        "numYields" : 0,
        "locks" : {
            "Global" : "r",
            "MMAPV1Journal" : "r"
        },
        "waitingForLock" : true,
        "lockStats" : {
            "Global" : {
                "acquireCount" : {
                    "r" : NumberLong(2)
                }
            },
            "MMAPV1Journal" : {
                "acquireCount" : {
                    "r" : NumberLong(1)
                },
                "acquireWaitCount" : {
                    "r" : NumberLong(1)
                }
            }
        }
    }

The only query which is not waiting for the lock is a "dbstat" query which was fired by the arbiter("client" is the arbiter's ip in the below sample) and is running for more than 5 seconds.

{
        "desc" : "conn909",
        "threadId" : "0x7f53dc0",
        "connectionId" : 909,
        "opid" : 946985942,
        "active" : true,
        "secs_running" : 5,
        "microsecs_running" : NumberLong(5239773),
        "op" : "query",
        "ns" : "tb_dev",
        "query" : {
            "dbstats" : 1
        },
        "client" : "10.240.0.3:44403",
        "numYields" : 0,
        "locks" : {
            "Global" : "r",
            "MMAPV1Journal" : "r",
            "Database" : "R"
        },
        "waitingForLock" : false,
        "lockStats" : {
            "Global" : {
                "acquireCount" : {
                    "r" : NumberLong(2)
                }
            },
            "MMAPV1Journal" : {
                "acquireCount" : {
                    "r" : NumberLong(1)
                }
            },
            "Database" : {
                "acquireCount" : {
                    "R" : NumberLong(1)
                }
            }
        }
    }

Also, this looks to be a db level lock even though I am using 3.0(with mmapv1 as the storage engine).What is causing this db level lock to be held for so long?

In MMAPv1 write locks are collection level (version 3.0 and forward; Before 3.0, locks where database level). So, if you have 1174 clients reading "db.collection" and one client who is writing that same collection, readers will wait! Especially if you have write concern w:2 (or w:"majority") and you (only) secondary is lagging.

With wiredTiger locking mechanism is on document level.

You check here how concurrency works.


SQL Server 2017 Express faster than Standard Edition What could explain SQL Server Express Edition being much faster than Web / Standard Edition?

These are multiple VMs in cloud hosting. They all experience the same problem. Some are on SQL Server 2012, others on 2017 - same problem. All machines have latest updates.

Machines are good enough for the job: 8G 4 cores and DBs are small in size less than 1GB of data. Same machines. I even tried have both Express and Web Editions on the same machine with only one instance enabled at a time, same problem.

It does not matter if the web application is run locally in the same machine or remotely accessing SQL sever though network, same problem.

The DBs have exactly the same data. I used a backup of Express and loaded it on a Web Edition server. This is a VM with SSD Drives. Install was as standard as possible. I compared settings in SSMS looking at server properties. Files are small and in the standard locations.

Queries are multiple in forms and complexities all run about half the speed in Web Edition. This is a repeatable problem not just once off.

It is not specific to Web Edition and the type of server as this was replicated on development machines on Express VS Developer Edition.

SQL Server settings appear to be the same.

Multiple people have looked at it and we cannot figure out why.

I was told to give example:

    select top (500) *
    from UserLog u
    join staff s on  s.Username = u.username
    join Forms f on f.PageFile = u.PageFile
    left outer join Client c on c.CID=u.CID
    order by u.id desc

Execution plans are exactly the same.

These are the plans for Express and Web Edition. They look the same to me.

Here are the timings as measured by SSMS (latest):

Express

Web Edition

Over multiple tries Express is 50% faster on this very simple query.

Is there a minimum DB size for non-Express Edition? (this would not make sense...)

Wait statistics

In the execution plan of the query that performs worse we find the following wait stats:

  <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="280" WaitCount="27" />
        </WaitStats>
        <QueryTimeStats CpuTime="14" ElapsedTime="295" />

For the 295ms that the query executed, 280ms was waiting on ASYNC_NETWORK_IO.

As a result, 14ms cpu time & 15ms elapsed time without these wait stats seem reasonable to me.

These details in the actual execution plan mean that the issue is not directly related to the performance of the instance itself, but to the processing of these rows.

ASYNC_NETWORK_IO

Some parts of a previous answer on why ASYNC_NETWORK_IO occurs by Tom V

Which refers to a post by Thomas LaRock

  The ASYNC_NETWORK_IO wait indicates that one of two scenarios are
  happening. The first scenario is that the session (i.e., SPID) is
  waiting for the client application to process the result set and send
  a signal back to SQL Server that it is ready to process more data. The
  second is that there may be a network performance issue.

Most of the time I see ASYNC_NETWORK_IO happening it is due to the application not processing the rows fast enough (application design / app server under heavy load / stress).

After validating the application, the network would be the next suspect.


How to append zeros to substring Datetime values in sql table? I need to change a DateTime to a new format by appending zeros to dates less than 10. DateTime column is nvarchar(MAX)

Currently: - April 8 2018
4/8/2018 00:00:00 AM

New wanted format:

4/**08**/2018 00:00:00 AM

Do not store dates and times as strings like nvarchar while the database has native datatypes like datetime. These are not saved as strings but in an internal binary format designed for storing this type of information.

Such datatype saves the exact information instead of some of its various representations, allowing you to get it in different formats, making the conversion needed in this question unnecessary. More than that, it allows doing calculations and comparisons using database functions.


Theoretical writes/sec limit If I have solarwinds which has an alert set whenever we have more than 300 writes per second, and I know 300 writes per second is fine, and the writes per second often goes over 6500, how can I work out what the theoretical limit of writes per second is, so I can alter the alert thus making it actionable?

If "The number of writes should not exceed 85% of the disk capacity" how can I work out the number of writes that are possible with my current disk configuration?

One way to do this will be to monitor the writes/sec for weeks or a month and then come up with a number based on what the max values that you are seeing.  Obviously this is a rough guide, and as your workload changes you would have to adjust the threshold as well.

You could also use disk benchmarking tools to test the throughput of your disk subsystem.  diskspd is something which we use.  Please also note that writes/sec is dependant on the block size that is used.  Based on what the drive is used for(data or transaction log)  and also based on the type of workload, SQL Server could use different.  You can read about SQL IO sizes here.  Obviously if you run this on a production system, you might have a performance impact.

A better performance counter to alert on would be sec/Write or sec/Read.  You can read about it here and here


How to handle id's referring to multiple tables in an SQL database I'm working on an MySQL Database for my website but I'm not sure on how to proceed now. I get to the point where actions have requirements in multiple other tables, but also have influences in multiple other tables.

In my end design I will have at least 6 tables that an action has potential requirements for and a potential influence on.

Now, there are 2 ways I've thought of it myself:

1 Make action_req_x and action_inf_x tables for every x which would look like this:

action_id ...
x_id ...
minvalue ...
maxvalue ...

Where the minvalue and maxvalue can be changed upon which table it's refferencing. For example, stats would be Decimal(10,3) and items would be Integer(1). This method would require a LOT of tables and I can't believe I've found the best method on my own.

2 Make action_req_x and action_inf_xtables, but only one of each, which would look like this:

action_id ...
x_tablename ...
x_id ...
minvalue ...
maxvalue ...

but this wouldn't be perfect, because different tables have different types of values and I wouldn't know how to setup FK's :( I don't necessarily think this is a really good way to go, but it's better than nothing..

Now, so far I've only talked about the influences and requirements for actions, now I'm not sure yet, but I might eventually want items to have requirements and influences as well, which would require even MOOOORE tables!

My question: Is there a better way to handle this than my first method? Or is there a way I can alter my second method and make that work better? How would database professionals attack this problem?

Don't "over-normalize".

The table stats, items, and skills are just normalizations of short, unique, names.  Consider just having the names in the other tables.

Or...  have just one table for id<->name mapping, perhaps with an ENUM to say which of stat/item/skill/etc it is.

Based on your Schema, what queries will you be performing?  Do you ever need to 'start' with action_influence?  Or is it just some extra info to enter and later display?  If the latter, consider putting that into a JSON string of all the reqs, influences, etc that are associated with an action.  Then have that JSON string in the actions table.  If you use MySQL 5.7 or MariaDB 10, there are even tools for having SQL reach into the JSON, but try to avoid that.


Stored Procedure Create Table from Variable with Variable Constraint I have managed use a stored procedure to create a copy of a table with a variable name. But I am struggling to understand how to incorporate a constraint into the stored procedure.

The problem: the constraint must be a variable as when it makes a copy of a table it cannot name the PK that I have with the same as one used before. I am getting syntax errors in these areas.

I am very new to SQL Server... literally just started learning today!! So please explain in dummy terms.

Code so far below:

    CREATE PROCEDURE procFinancialPeriodTable
        (@TABLENAME as varchar (50))
    AS
        DECLARE @SQL varchar(2000)
        --SET @SQL = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
        --drop table [dbo].[@TABLENAME]
    BEGIN
        SELECT @SQL = 'CREATE TABLE ' + @TABLENAME + '('

        SELECT @SQL = @SQL + '[ID] [int] IDENTITY(1,1) NOT NULL,[FinPeriodNr] [int] NOT NULL,[FinCurrentPeriod] [bit] NULL,[FinStart] [date] NULL,[FinEnd] [date] NULL,[FinStatusOpen] [bit] NOT NULL,[PeriodClosedTS] [smalldatetime] NULL,[PeriodClosedUID] [varchar](3) NULL,)'
    CONSTRAINT [PK_FinancialPeriod' + @TABLENAME + '_1] PRIMARY KEY CLUSTERED(
    [FinPeriodNr] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,                 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    EXEC(@SQL)

    SET NOCOUNT ON;
END

Your logic seems fine, you are getting a syntax error because you aren't delimiting your strings correctly.

[PeriodClosedUID] [varchar](3) NULL,)' <-- problem

Also it's very helpful to be liberal with carriage returns and indenting, so problems like this aren't hidden 4 screens over and only accessible via horizontal scrollbar.

CREATE PROCEDURE dbo.procFinancialPeriodTable -- always use schema prefix
    @TABLENAME SYSNAME
AS
BEGIN -- use body wrappers around whole body
  SET NOCOUNT ON; -- put this at the beginning - no point in setting it at the end

  DECLARE @SQL NVARCHAR(MAX); -- dynamic SQL should always be Unicode

  SELECT @SQL = 'CREATE TABLE dbo.' + QUOTENAME(@TABLENAME) + '('
    + '[ID] [int] IDENTITY(1,1) NOT NULL,[FinPeriodNr] [int] NOT NULL,
       [FinCurrentPeriod] [bit] NULL,[FinStart] [date] NULL,
       [FinEnd] [date] NULL,[FinStatusOpen] [bit] NOT NULL,
       [PeriodClosedTS] [smalldatetime] NULL,[PeriodClosedUID] [varchar](3) NULL,
       CONSTRAINT [PK_FinancialPeriod_' + @TABLENAME + '_1] PRIMARY KEY CLUSTERED(
         [FinPeriodNr]));'; -- all those options you specified were verbose defaults

  EXEC sp_executesql @sql; -- instead of EXEC(@sql)
END
GO

Some links:

My Stored Procedure "Best Practices" checklist
Bad habits to kick : Avoiding the schema prefix
Bad habits to kick : Using EXEC() instead of sp_executesql


What could possibly cause a numeric overflow when enqueuing an Oracle AQ message? We create and enqueue messages with various properties, like this:

    message :=  sys.aq$_jms_message.construct(0);
                  message.set_string_property('a', v_a));
                  message.set_int_property('b', v_b);
                  message.set_int_property('c', v_c);
                  message.set_string_property('d', v_d);
    DBMS_AQ.ENQUEUE(queue_name         => SOME_QUEUE,
                    enqueue_options    => queue_options,
                    message_properties => message_properties,
                    payload            => message,
                    msgid              => message_id);

After running this successfully for a long time, we are all of sudden getting

ORA-01426: numeric overflow ORA-06512: at "SYS.DBMS_AQ"

What could possibly cause this?
Running the same enqueuing code against a newly created queue with new queue table produces the same result.

Turns out this problem has nothing to do with message properties, and perhaps not even with the messages themselves. The cause here is actually a bug in Oracle, which you hit when the value of

select max(greatest(object_id, data_object_id)) from dba_objects;

reaches 2^31. There is actually a corresponding patch available from Oracle:

Patch 14837395: AQ CALLS FAIL WITH NUMERIC OVERFLOW WHEN DB OBJECT NUMBERS GREATER THAN 2^31


MongoDB: co-locate the mongos process on application servers I would like to ask a question about a best practice described in this document:

http://info.mongodb.com/rs/mongodb/images/MongoDB-Performance-Best-Practices.pdf

  Use multiple query routers. Use multiple mongos
  processes spread across multiple servers. A common
  deployment is to co-locate the mongos process on
  application servers, which allows for local communication
  between the application and the mongos process.The
  appropriate number of mongos processes will depend on
  the nature of the application and deployment.

Just a little bit of background about our deployment. We have a lot of application server nodes. Each of them runs one JVM-based process with stateless RESTful WS. As this best practice suggests, every single application server node runs its own mongos process, which means that the number of JVM processes always equals the number of mongos processes.

All mongos processes connect to 3 config servers and several mongo shards (with replica sets within each shard). Even though we are using a sharded deployment, we are not really sharding our collections. In fact we have a large number of databases which are spread across all of the shards during their creation time (and this is our main use case for sharding at the moment).

Since best practice also suggest that "The appropriate number of mongos processes will depend on the nature of the application and deployment" I started to wonder whether our usage of mongos is actually appropriate or if it would be better for us to have several dedicated mongos nodes and let our app servers connect to them without having mongos running locally.

What is your opinion on the best approach to decide how many mongos instances are appropriate in relation to the application server instance count or the size of the MongoDB cluster?

Recently we started to look into cluster management for our stateless web services, by which I mean tools like Docker, Apache Mesos, and Kubernetes. If we are using Docker, then it is generally discouraged practice to run more than one process within container. Considering this fact it becomes really hard to make sure that application server container and mongos container are always co-located on the same physical node and have equal amount of processes. This makes me wonder whether this best practice still applies for the cluster architecture I just described. If not, can you please suggest what would be the better way to locate and deploy mongos processes in this architecture?

Since there is already and answer submitted, and a useful and valid one at that, I do not want to distract from its own usefulness but there are indeed points to raise that go way beyond just a short comment. So consider this "augmentation", which is hopefully valid but primarily in addition to what has already been said.

The truth is to really consider "how your application uses the data", and to also be aware of the factors in a "sharded environment" as well as your proposed "container environment" that affect this.

The Background Case

The general take on the practice recommendation for co-locating the mongos process along with the application instance is to obviate any network overhead required in order for the application to communicate with that mongos process. Of course it is also "recommended practice" to specify a number of mongos instances in the application connection string in the case where that "nearest" node should not be available for some reason then another could be selected, albeit with the possible overhead of contacting a remote node.

The "docker" case you mentions seems somewhat arbitrary. While it is true that one of the primary goals of containers ( and before that, something like BSD jails or even chroot ) is generally to achieve some level of "process isolation", there is nothing really wrong with running multiple processes as long as you understand the implications.

In this particular case the mongos is meant to be "lightweight" and run as an "additional function" to the application process in a way that it is pretty much a "paired" part of the application itself. So docker images themselves don't have an "initd" like process but there is not really anything wrong with with running a process controller like supervisord ( for example ) as the main process for the container which then gives you a point of process control over that container as well. This situation of "paired processes" is a reasonable case and also a common enough ask that there is official documentation for it.

If you chose that kind of "paired" operation for deployment, then it does indeed address the primary point of maintaining a mongos instance on the same network connection and indeed "server instance" as the application server itself. It can also be viewed in some way as a case where the "whole container" were to fail then that node in itself would simply be invalid. Not that I would recommend it, and in fact you probably should still configure connections to look for other mongos instances even if these are only accessible over a network connection that increases latency.

Version Specific / Usage Specific

Now that that point is made, the other consideration here comes back to that initial consideration of co-locating the mongos process with the application for network latency purposes. In versions of MongoDB prior to 2.6 and specifically with regard to operations such as with the aggregation framework, then the case there was that there would be a lot more network traffic and subsequent after processing work performed by the mongos process for dealing with data from different shards. That is not so much the case now as a good deal of the processing workload can now be performed on those shards themselves before "distilling" to the "router".

The other case is your application usage patterns itself with regard to the sharding. That means whether the primary workload is in "distributing the writes" across multiple shards, or indeed being a "scatter-gather" approach in consolidating read requests. In those scenarios

Test, Test and then Test Again

So the final point here is really self explanatory, and comes down to the basic consensus of any sane response to your question. This is not a new thing for MongoDB or any other storage solution, but your actual deployment environment needs to be tested on it's "usage patterns" as close to actual reality just as much as any "unit testing" of expected functionality from core components or overall results needs to be tested.

There really is not "definitive" statement to say "configure this way" or "use in this way" that actually makes sense apart from testing what "actually works best" for your application performance and reliability as is expected.

Of course the "best case" will always be to not "crowd" the mongos instances with requests from "many" application server sources. But then to allow them some natural "parity" that can be distributed by the resource workloads available to having at "least" a "pool of resources" that can be selected, and indeed ideally in many cases but obviating the need to induce an additional "network transport overhead".

That is the goal, but ideally you can "lab test" the different perceived configurations in order to come to a "best fit" solution for your eventual deployment solution.

I would also strongly recommend the "free" ( as in beer ) courses available as already mentioned, and no matter what your level of knowledge. I find that various course material sources often offers "hidden gems" to give more insight into things that you may not have considered or otherwise overlooked. The M102 Class as mentioned is constructed and conducted by Adam Commerford for whom I can attest has a high level of knowledge on large scale deployments of MongoDB and other data architectures. Worth the time to at least consider a fresh perspective on what you may think you already know.


Don't rebuild indexes < 1000 pages - then why bother having them? Microsoft recommends not bothering to rebuild indexes that have less than 1,000 pages (from memory I think it's because they are so small that they will be held in RAM).

If I'm not rebuilding them, then they'll become heavily fragmented.  In that case is there any point in having them at all?  I'm imagining a phone book where every single entry is in the wrong place - it would be useless!

For clarification I'm mainly talking about non-clustered indexes, though I'd be interested to know whether or not the answer would be different for clustered indexes.

The answer to this question suggests that indexes can help to avoid blocking issues behind row locks, which would be a reason why the indexes may still be useful if it is correct and applies to SQL Server.

Just because the index doesn't require rebuilding does no mean is useless. A 99999% fragmented index is still 9999 times better than a heap for looking up a key. The negative effects of fragmentation are heavily, heavily, heavily, heavily overestimated in popular DBA culture.

Locking is a valid point (w/o indexes all read-write operations are guaranteed to collide), but not the only one. Even if the data is read-only, so locking is never an issue, it still needs an index for seek and range scans operations.

  I'm imagining a phone book where every single entry is in the wrong place

This is a very misguided interpretation of what fragmentation is and its effects. A fragmented phone book still has all the entries in order. Looking up an entry still takes 2-3 page flips, it will be something like:

the cover says to find 'P's go to page 645.
page 645 says to find 'PA's go to page 56.
page 56 says all 'PAUL's are on page 646.

So yes, you flip back and forth 2-3 times. The fragmented phonebook also may have every page half-blank, so it weight twice as much as the newly minted phonebook.

Compare it with the heap:

the cover will say I don't know where 'PAUL's are, but if they're in the book, I'm sure they're between the front cover and the back cover! And do remember that finding the first 'PAUL' does not mean you found all of them...

The only data/workload that really doesn't need indexes is columnstore/data warehousing. Columnstore access and processing is completely different and can get away w/o indexes, because every query is expected to scan all data anyway. Segment elimination, per-column IO and other forces contribute to keeping these always-end-to-end scans competitive.


Mysql alter table question I have a huge table in my mysql 5.6.19 database -around 5.7 gb of data- and there is a need to alter a description column which is currently set to text, to be able to contain around 500000 chars. Now my question is what is the right way to preform such a proccess. What will be the effect of the disk space when doing it? Can indexes be possibly harmed after this operation? Any help would be highly appriciated here. Thank you very much

You need to ALTER a column from TEXT to MEDIUMTEXT?

Such an ALTER will copy the table over, making the change as it goes.  At the end, rebuild the indexes and will RENAME the table back into place.  The table will be blocked from (at least) writes for the duration, which could be hours.

For a less invasive approach, see pt-online-schema-change.  It will add a Trigger to the table so that writes are tracked while it copies the table over.  It ends with a brief finishing lock to flip the table into place.

Either approach will take about 5.7GB of spare disk space.  Caveat:  If you have innodb_file_per_table = OFF, the spare disk space will not be freed when finished.  I recommend ON when creating 'big' InnoDB tables.


Limit rows in Correlated Subquery I have one big and two small issues with my query I need help with.
First, I want a SQL (postgres if it matters) query that gets SUM of last two ratings of fruits and makes a new column for each fruit column on original table with corresponding result. Conditions are that it has to have happened in the past (before current row date) and I only want the last 2 records and column for 'rush_order' has to have value 'yes'.
Right now my query gets all records but I only want last 2. I tried to use LIMIT in each of the 4 subqueries and also tried OFFSET 0 and FETCH FIRST 2 ROWS ONLY but can't get it to work. If there is a way to use the OFFSET it could eliminate the need to use the comparison of date < current_row.date as the table will always be ordered by ascending date.
Second, if there is advice on how to use CASE here I think it would be much faster. I am running 4 subequeries for every row as it is; there is too much repetition.
Third, I think another way around this is to stack the name1 and rating1 on top of the name2 and rating2 on top of each other. Not necessary but am open to another way of solving this.
Thank you
CREATE TABLE IF NOT EXISTS fruit (
   fruit_id INTEGER  NOT NULL ,
   name1 varchar(50) NOT NULL,
    name2 varchar(50) NOT NULL,
   rating1 integer NOT NULL,
    rating2 integer NOT NULL,
     delivered_on date NOT NULL,
     rush_order varchar(3) NOT NULL,

   PRIMARY KEY  (fruit_id)
 )

INSERT INTO fruit (fruit_id, name1, name2, rating1, rating2, delivered_on, rush_order) VALUES
(1, 'Apple', 'Banana', 5, 4,'2015-10-01', 'yes'),
(2, 'Pear', 'Banana', 3, 4,'2015-10-27', 'yes'),
(3, 'Apple', 'Pear', 5, 4,'2015-10-29', 'no'),
(4, 'Banana', 'Apple', 2, 5,'2015-11-27', 'yes'),
(5, 'Banana', 'Apple', 4, 1,'2015-11-29', 'yes'),
(6, 'Apple', 'Banana', 4, 2,'2015-12-02', 'no'),
(7, 'Pear', 'Apple', 3, 5,'2015-12-20', 'yes');

SELECT *,
    COALESCE(
        (SELECT sum(f2.rating1)
    FROM fruit f2
        WHERE f1.name1 = f2.name1 AND f1.delivered_on > f2.delivered_on),0) +
    COALESCE(
        (SELECT sum(f3.rating2)
    FROM fruit f3
        WHERE f1.name1 = f3.name2 AND f1.delivered_on > f3.delivered_on) ,0)
            AS fruit1_rating,
    COALESCE(
        (SELECT sum(f4.rating2)
    FROM fruit f4
        WHERE f1.name2 = f4.name2 AND f1.delivered_on > f4.delivered_on),0) +
    COALESCE(
        (SELECT sum(f5.rating1)
    FROM fruit f5
        WHERE f1.name2 = f5.name1 AND f1.delivered_on > f5.delivered_on) ,0) AS fruit2_rating
FROM fruit f1;

Expected result:

The Selected tables in the subqueries must be limited first before summing them up, but i can't reproduce your exspected result

CREATE TABLE IF NOT EXISTS fruit (
   fruit_id INTEGER  NOT NULL ,
   name1 varchar(50) NOT NULL,
    name2 varchar(50) NOT NULL,
   rating1 integer NOT NULL,
    rating2 integer NOT NULL,
     delivered_on date NOT NULL,
     rush_order varchar(3) NOT NULL,

   PRIMARY KEY  (fruit_id)
 );

INSERT INTO fruit (fruit_id, name1, name2, rating1, rating2, delivered_on, rush_order) VALUES
(1, 'Apple', 'Banana', 5, 4,'2015-10-01', 'yes'),
(2, 'Pear', 'Banana', 3, 4,'2015-10-27', 'yes'),
(3, 'Apple', 'Pear', 5, 4,'2015-10-29', 'no'),
(4, 'Banana', 'Apple', 2, 5,'2015-11-27', 'yes'),
(5, 'Banana', 'Apple', 4, 1,'2015-11-29', 'yes'),
(6, 'Apple', 'Banana', 4, 2,'2015-12-02', 'no'),
(7, 'Pear', 'Apple', 3, 5,'2015-12-20', 'yes');

 SELECT *,
    COALESCE(
        (SELECT sum(f2.rating1)
    FROM (SELECt * FROM fruit f2
        WHERE f1.name1 = f2.name1 AND f1.delivered_on > f2.delivered_on AND f2.rush_order = 'yes' ORDER BY f2.delivered_on LIMIT 2) f2),0) +
        COALESCE(
        (SELECT sum(f3.rating1)
    FROM (SELECt * FROM fruit f3
        WHERE f1.name1 = f3.name1 AND f1.delivered_on > f3.delivered_on AND f3.rush_order = 'yes' ORDER BY f3.delivered_on LIMIT 2) f3),0) AS fruit1_rating,
            COALESCE(
        (SELECT sum(f4.rating1)
    FROM (SELECt * FROM fruit f4
        WHERE f1.name1 = f4.name2 AND f1.delivered_on > f4.delivered_on AND f4.rush_order = 'yes' ORDER BY f4.delivered_on LIMIT 2) f4),0) +
        COALESCE(
        (SELECT sum(f5.rating1)
    FROM (SELECt * FROM fruit f5
        WHERE f1.name1 = f5.name2 AND f1.delivered_on > f5.delivered_on AND f5.rush_order = 'yes' ORDER BY f5.delivered_on LIMIT 2) f5),0) AS fruit2_rating
FROM fruit f1;

fruit_id | name1  | name2  | rating1 | rating2 | delivered_on | rush_order | fruit1_rating | fruit2_rating
-------: | :----- | :----- | ------: | ------: | :----------- | :--------- | ------------: | ------------:
       1 | Apple  | Banana |       5 |       4 | 2015-10-01   | yes        |             0 |             0
       2 | Pear   | Banana |       3 |       4 | 2015-10-27   | yes        |             0 |             0
       3 | Apple  | Pear   |       5 |       4 | 2015-10-29   | no         |            10 |             0
       4 | Banana | Apple  |       2 |       5 | 2015-11-27   | yes        |             0 |            16
       5 | Banana | Apple  |       4 |       1 | 2015-11-29   | yes        |             4 |            16
       6 | Apple  | Banana |       4 |       2 | 2015-12-02   | no         |            10 |            12
       7 | Pear   | Apple  |       3 |       5 | 2015-12-20   | yes        |             6 |             0

db<>fiddle here


MySQL eats up all the memory from the RHEL OS Currently, I have MySQL server installed in the AWS RHEL EC2 instance.

The team was running heavy data processing in the DB and we found out that every now and then the DB restarted itself. After troubleshooting, we realized that the DB was actually killed by the OS! Because the MySQL is so hungry that it consumes all the memory in the OS.

The OS has 124GB RAM and 68GB is allocated to innodb_buffer_pool_size.
Attached my.cnf file

Question:

How do we prevent the OS from killing the mysqld?

    [mysqld]
# Server configuration
user=mysql
port=3306
socket=/home/mysql/server/run/mysql.sock

# File locations
datadir=/mysql/data
basedir=/home/mysql/server
lc_messages_dir=/usr/share/mysql-8.0
plugin-dir=/lib64/mysql/plugin
innodb_log_group_home_dir=/redolog/redologfiles
innodb_data_file_path=ibdata1:12M:autoextend
log-error=/home/mysql/server/logs/mysqld.log
innodb_log_file_size=2147483648
innodb_log_files_in_group=8
innodb_page_size=16384
innodb_checksum_algorithm=crc32
innodb_buffer_pool_filename=ib_buffer_pool
innodb_undo_tablespaces=2
innodb_buffer_pool_filename=ib_buffer_pool
skip-log-bin

# New server config
character-set-server=latin1
collation-server=latin1_swedish_ci
connect_timeout=31530000
net_read_timeout=31530000
net_write_timeout=31530000
innodb_buffer_pool_size=68719476736
innodb_buffer_pool_instances=64
innodb_temp_data_file_path=ibtmp1:512M:autoextend:max:512000M
innodb_max_undo_log_size=4294967296
innodb_read_io_threads=16
binlog_expire_logs_seconds=259200
#expire_logs_days=10
max_binlog_size=1073741824
sql_mode = ''
max_connections=300
log_timestamps=SYSTEM
local_infile = 1
max_digest_length = 3072
performance_schema_max_digest_length=10240
max_digest_length=10240
performance_schema_max_sql_text_length=10240
innodb_adaptive_hash_index = 0

interactive_timeout  = 86400
mysqlx_interactive_timeout  = 86400
mysqlx_wait_timeout  = 86400
wait_timeout  = 86400

general_log = OFF
general_log_file = /home/mysql/server/logs/mysqld_db.log
[client]
port=3306
socket=/home/mysql/server/run/mysql.sock

#slow_query_log_file=/home/mysql/server/logs/mysql-slow.log
#slow_query_log=ON
#general_log_file=/home/mysql/server/logs/mysql_gen_db.log
#general_log=ON

9 times out of 10 this is due to a hungry query. Set long-query-time to something < 2 and once you have a good sample size analyse the log file using the Percona tool pt-query-digest. This should give you a good place to start.

Try splitting your transactional and analytical workloads. Running them on different hardware is common place.


How am I supposed to update PostgreSQL on Windows, once installed, without manually installing new installers? I have PostgreSQL 11.5 installed on Windows 10. It was installed with the EnterpriseDB installer, which is the only one which exists these days.

If I go to https://www.postgresql.org/download/windows/ , it links to two pages:

One page with installers for Windows. I could manually click the 11.7 installer link and install it manually, but of course, I would go insane if I had to keep that up every time a new minor version is released.
One page with ZIP versions for Windows. The same thing goes here. (Neither has any checksums mentioned anywhere, which is worrying.)

The installers and ZIP files use unpredictable paths. For example, this is the latest version:

https://get.enterprisedb.com/postgresql/postgresql-11.7-2-windows-x64.exe
http://get.enterprisedb.com/postgresql/postgresql-11.7-2-windows-x64-binaries.zip

(The ZIP archives are served over HTTP for some reason.)

As you can see, it says "postgresql-11.7-2" instead of "postgresql-11.7", so I cannot just first determine the latest minor version from the RSS feed and then directly fetch it from a known URL, since it uses an extra "-2" thing.

When trying to go directly to https://get.enterprisedb.com/postgresql/ to browse the files, I get an "Access denied" error in my browser.

Looking instead at the https://www.postgresql.org/ftp/ page, I found a "binary" subdir. However, it only has binaries for "solaris" (???) -- nothing for Windows or any other OS.

People (including myself) are simply not going to be manually downloading and installing new installers all the time. As a result, I have been running outdated PG for a long time now, always postponing it every time I fail to figure out a sane way to update it without me having to manually do it as if it were the initial installation.

When "BigSQL" still used to provide an alternative to EnterpriseDB's PostgreSQL installer, I remember that they had some kind of custom utility which was able to update PostgreSQL from the installed copy, with a binary called "pgc" or something like that. I cannot find that in my EnterpriseDB installation dir of PostgreSQL, so I assume it was just a non-standard thing that BigSQL did. (If it's available as a third-party thing, that doesn't seem sensible to me.)

Is there really no way to update PostgreSQL (between minor versions, such as 11.5 => 11.7) from itself? Or at least some way to properly fetch the latest installer for a branch from the PostgreSQL website? And if the latter is possible, is there some flag I can use for the installer so that it doesn't bring up a whole GUI and just silently updates the already installed PostgreSQL?

You don't want your system to magically upgrade the database software, because that is always downtime (typically short, but still).

You have to take the time to plan and schedule it. It's not all that complicated as you present it.

Also, as detailed in the answer to this question, there are sometimes additional tasks to do that require reading the release notes. These tasks can be automated, but you still have to plan for each minor release.


Availability Group cluster memory issue after network issues. How to dump HADR Log Block Msg Pool? We have a four node Availability Group, two nodes in one site, two nodes off site in another data center. I have noticed after every WAN issue where the WAN connection is flapping, and the off site nodes constantly disconnect and reconnect (using AOAG health from the AOAG dashboard), the memory of the primary server gets consumed by the "HADR Log Block Msg Pool"

SELECT  *
FROM    sys.dm_os_memory_clerks
ORDER   BY pages_kb DESC

type: OBJECTSTORE_SERVICE_BROKER
name: HADR Log Block Msg Pool

In the worst case, when the network was flapping for hours, this memory clerk will end up consuming over 90% of the memory of the SQL Server causing the SQL Server to stop functioning (SQL has 10GB of memory "HADR Log Block Msg Pool" was using 9.8GB).

Is there any way to dump this HADR Log Block Msg Pool?  Or stop it from growing so large in the first place?  Our only solution so far has been to failover and restart the box.

There are no errors, just the logs for the node disconnects and reconnects and logs for the DBs re-hardening after the reconnects.

As more and more memory gets eaten by the "HADR Log Block Msg Pool" the memory available for everything else drops, affecting performance. Normally this 10GB of RAM is fine for this AOAG group and usage. It's only when the WAN flaps for a while that we have this issue.

We could throw more memory at the server, but I don't think that will solve the underlying problem, it would just buy us more time before it severely hurts performance.

I agree the network is the root cause, but it seems strange that after the issue is resolved and the AOAG is back in sync that SQL would not recover/reallocate RAM back to other SQL memory clerks like most SQL memory clerks do.

Log shipping won't work; it is a transactional environment, we need near real-time, preferably real-time, offsite DR. The AOAG group works 99% of the time and is almost always real time in sync. We are trying to work with the network team to improve connectivity, and/or maybe make it so it would just disconnect instead of flapping.

System Info
SQL version: SQL 2016 SP1 CU6 13.0.4457.0
OS version: Windows 2012 R2 6.3.9600
Server MEM: 12GB
SQL Max MEM: 10GB

Availability Group config info
Four databases are in the AOAG
The AOAG databases all together are 364GB
The two local nodes are in sync mode with one vote each
The two remote nodes are in async mode with zero votes
There is also a local file witness with one vote.

I have noticed after every WAN issue where the WAN connection is flapping, and the off site nodes constantly disconnect and reconnect, the memory of the primary server gets consumed by the "HADR Log Block Msg Pool"

Yes, this is currently by design. It is expected that the network between the two sites can handle the traffic and is available. Since it seems that's not the case, SQL Server really isn't the problem here but is manifesting as an issue. If you're going to continue to work over an unreliable and possibly extremely high latency low bandwidth connection then I wouldn't use availability groups. In fact, I'm not sure what you'd want to use as nothing would have a solid and reliable connection which seems to be the root cause of the issue.

  Is there any way to dump this HADR Log Block Msg Pool?

Inside of SQL Server? No.

  Or stop it from growing so large in the first place?

Yes, fix the connectivity issue and it won't grow. If it's prolonged connectivity issues, then remove the remote replicas from the AG and it'll stop growing. Since there are two remote replicas, the data will be sent twice which could be exacerbating the issue as it might not have been taken into account for the infrastructure available when architected.

  Server MEM: 12GB

This is an awful small amount of server memory for 364 GB of databases + OS + Cluster + AG + all of the antivirus and agents installed.


How to update table1 field by using other table and function I have two tables and one function:

Table1 contains shop_code,batch_id

shop_code| batch_id  | regstriy_id
123 | 100  | 12
124 | 100  |13
125 | 100  |12

Table2 contains shop_code,shop_name

shop_code| shop_name
123 | need to populate
124 | need to populate
125 | need to populate

Function1 take parameter registry_id from table1 and returns shop_name

Table2 shop_name is empty I want to populate against the shop_code.

It will be great if someone can help, I am using Oracle.

I tried this but not working

 update TABLE2 set T2.SHOP_NAME = T.SHOP_NAME
 from(
 select GET_shop_name(t1.regitry_id) as shop_name ,
           t1.shop_code shop_code
           from TABLE1 T1
                ) t where t.shop_code = t1.shop_code;

There are multiple ways to do it in Oracle.  Right of the bat I can think about two.

Straightforward, not Oracle specific (assuming shop_code is unique in table1)

update TABLE2 t2 set SHOP_NAME =
(
  SELECT GET_shop_name(t1.regitry_id)
  FROM TABLE1 T1
  WHERE t2.shop_code = t1.shop_code
);

Oracle specific syntax(requires inline view to be key-preserved - if shop_code is not a PK column in both tables you will very likely get "SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table" )

UPDATE
 (
    SELECT GET_shop_name(t1.regitry_id) AS new_name,
           t2.shop_code, t2.shop_name
    FROM TABLE2 t2
    INNER JOIN t1 ON (t2.shop_code = t1.shop_code)
  )
SET shop_name = new_name;


User assignment to various roles of changing events We have numerous events throughout the year that are supported by multiple teams.  Team A will support the entire event duration while Team B will support segments (typically per week).  It is common for the event start date and duration (typically 10-30 days) to shift independent of other events creating a problem tracking gaps and overlap in coverage of these events.  We want to create a central schedule so each team can see how event changes creates gaps and/or overlaps in assignments.

Events

Event   StartDate   Duration (Days)
E1      someDate1   25
E2      someDate2   14
E3      someDate3   22

Teams

Team    User
A       1
A       2
A       3
B       4
B       5
B       6

Assignments

Event   User    DayStart    DayEnd
E1      A1      1           NULL
E1      A2      1           NULL
E1      B4      1           7
E1      B6      15          NULL
E2      A2      1           NULL
E2      B7      1           7
E2      B4      1           7
E2      B5      8           14
E3      B5      1           7
E3      B6      8           14

The above example data has a gap for Team B during Event 1 (days 8-14) and Team A has no coverage for Event 3.

The thought is to have a single assignment table where DayEnd being NULL signifies that the individual is covering the remainder of the event.  Is this a reasonable way to structure the tables?  Would it be better to have separate tables for each team (less than five total teams)?  Should NULL be replaced with some sufficiently high number such as 999?  Each team will also have role assignments within events, but believe that will just be an additional table and not relevant to this question.

Disclaimer:  this question was asked on stackoverflow.com without response

First, I would expect there to be a team table:

 TeamId | TeamName
--------+----------
    1   |  Team A
    2   |  Team B

and a separate user table, that identifies the team each user is in:

 UserId |   UserName   | TeamId
--------+--------------+--------
  1001  |   JaneSmith  |   1
  1002  |   JohnJones  |   1
  1003  |   KatieGrin  |   1
  1004  |  AbeSimpson  |   2
  1005  |  BenFranklin |   2
  1006  |  BarbaraEden |   2

Then, assignment would have UserId as a foreign key referencing the user table, just like TeamId in user is a foreign key referencing team.

I would not see value in having separate tables for each team. That would actually make things harder, rather than easier. The assignment table would either need one extra column, to indicate which table the assigned user came from (complicating queries, and preventing most common means to enforce referential integrity), or it will need a unique column to tie back to each distinct team table, in which case each foreign key column would need to allow for NULLs, which can hurt your referential integrity again.

Usually, the main benefit of using an actual value instead of NULL is to avoid the need for functions in your query to adjust the NULL value to as appropriate value. However, using 999 would probably still require some sort of computation, to adjust it to the actual DayEnd.

I do wonder if you wouldn't be better off using the actual DayEnd. I assume you're doing this to avoid having to recalculate the DayEnd values for an event that's duration changes; however, consider carefully if that's really the way you want to go. With NULL (or 999) as your DayEnd, you assume the users can safely be assigned to additional days. I don't know your business rules, but it seems entirely possible that someone may be unable to cover the additional days added to the event; assuming they can cover the additional days simply doesn't seem particularly safe.

Full details on implementation would, of course, depend on your DBMS, and your full business rules.


Syntax Error after adding FOREIGN KEY REFERENCE I'm getting the shown error in the code below, having copied the foreign key line stuff from the tutorial shown in the second image. Even after looking at this and other tutorials throughout the day I'm still not seeing where the problem is. Could someone please point out what's wrong?

Try:

...
poster int references user(userid)
...

E.g.

mysql> create table user ( uname varchar(15) primary key ) ;
Query OK, 0 rows affected (0.01 sec)

mysql> create table note (
    ->   title int primary key
    -> , poster int references user(userid)
    -> );
Query OK, 0 rows affected (0.00 sec)

Or ...

alter table note
add constraint user_fk
foreign key (poster) references user(uname);

E.g.

mysql> create table user ( uname varchar(15) primary key ) ;
Query OK, 0 rows affected (0.00 sec)

mysql> create table note (
    ->   title int primary key
    -> , poster varchar(15)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> alter table note
    -> add constraint user_fk
    -> foreign key (poster) references user(uname);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


Polling SQL Databases Is there any way to poll for MySQL database changes using JDBC? I need exactly which changes happened (not a simple yes/no).

I tried using a thread which executes every 15 seconds to check whether my database was changed.

The query was directly executed from MySQLworkbench / cli

I need to execute java function, which is not possible in a SQL trigger.

There is no way to pull changes via SQL. But you can read the binary log (be sure it's enabled) just like MySQL slaves do. So, instead of using JDBC, you'll need to use something like mysql-binlog-connector-java.

The basic idea is the same for all RDBMSs: you act as a slave and receive the changes. A real slave would apply those changes to have an exact copy of master's data, but actually once you get the changes, you can do whatever you want with them.

However each database implements replication in a different way, so there is no universal tool to do this. If you need to get changes from Oracle, you'll need to find a specific library for that purpose.


How to schedule jobs in Sql server 2008 for less than 10 sec? I want to run a job every 3 seconds, however in SQL Server 2008 we cannot define an interval of less than 10 seconds.

The job is used to insert/update visitor information, and segmentation information into a database which is tracked by google search.

There are up to about 100 rows inserted in a 2 or 3 seconds. That job inserts and update the table in a database. Is there any way to schedule it using sp job scheduling configuration?

Create a job that is scheduled to start every minute.  Have the job do something like:

WHILE 1=1
BEGIN
    EXEC dbo.SomeProcedure; /*  this would be the
        name of a stored procedure that does the
        actual work */
    WAITFOR DELAY '00:00:03.000';
END


Creating a DOMAIN in MySQL or MariaDB? As far as I know, DOMAINS cannot be created in MySQL. Is there any disadvantage for that? Is there something to be used instead of using CREATE DOMAIN?

No, check constraints are supported by MariaDB. Domains are user defined datatypes. The domain mechanism allows you to put constraints on the datatype instead of having to put constraints on every single column where the specific datatype is being used.


Restoring Oracle prod backup to test server with new database name I need to restore an Oracle production database (aaprod) to a test server that already is home to a test database (aatest) under a new name (bbtest).  I have a cold backup of the production database

Questions:  What is the appropriate approach to restoring this production database to a test server under a new database name using the cold backup?

Easy to do with SQL Server, Oracle is a new gig.

rule #1) use rman for backup, recovery and cloning of databases.

since this was a user managed backup, and you need your datafiles on a different location, there are a few things to do.

1) copy the file to where you want
2) create a pfile.ora that references your control_files
3) startup mount your database

at this moment you have control_files that still refer to the original datafiles locations. They need to be redirected.

4) for all your files in v$datafile, issue "alter database rename
file '/old/name' to '/new/name';
5) for all your members in v$logfile, issue "alter database rename
file '/old/name' to '/new/name';

now you should be able to open your database.

6) alter database open;

If you get problems before or during mount, it is probably because of wrong dbname, db_unique_name. Set them as they were/are in prod.


How to select recently updated row? I have problem in selecting a row in child sp which just got updated in parent SP.

MySP updates MyTestTable's MonkeyColumn, and right after it want to return newly updated row to the user by calling to the GetMonkey procedure, but for some reason monkeyColumn is still empty. I know that i can use Output parameters, but then MySP will only return output parameters and not the result of GetMonkey stored procedure. What have i done wrong?

Create PROCEDURE [dbo].[MySP]
    AS
 Begin
      declare @testid uniqueidentifier
    select @testid = [dbo].GetTestIDFromParams(@someValue1)

        Update MyTestTable
         set MonkeyColumn = 'monkey',
             where id= @testid;

        exec [dbo].GetMonkey @testid

  END

GetMonkey contains :

Create PROCEDURE [dbo].[GetMonkey]
    -- Add the parameters for the stored procedure here
@testid uniqueidentifier
AS
Begin
SET NOCOUNT ON;
Select MonkeyColumn from MyTestTable where id= @testid;

END

The only scenario I can find that no results would be returned is if no row was updated. Architecturally speaking if that is actually your code and I would not bother making Stored Procedures that simplistic, just embed the SELECT query in the original Stored Procedure. Due to how SQL works if you start to get more complex with your queries trying to use certain Object Oriented habits by turning every duplicated or close to duplicated query into a Stored Procedure call can quickly become troublesome.


Screwed up MySQL init_connect I executed an incorrect SQL command:

SET GLOBAL init_connect='SET CHARACTER SET=utf-8';

Now, when I try and run a script that connects to this database, I get the following error: _mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away').

How would I reset the global init_connect values?

You need to place init_connect in /etc/my.cnf
[mysqld]
init_connect='SET CHARACTER SET=utf-8';

This will set the variable for you after the next mysql restart.
If your username has the SUPER privilege, you need not set the variable again dynamically since you already set globally earlier with SET GLOBAL init_connect='SET CHARACTER SET=utf-8';
As for 'MySQL server has gone away', the most common cause is timing out. Please set wait_timeout and interactive_timeout to something sufficiently large (default 28800 sec [8 hours]). Other reasons are specified in the MySQL Documentation.
UPDATE 2012-04-22 01:24 EDT
Since you are changing the character set to process multibyte character strings, you may need to make sure the MySQL Packet is bigger (at least triple whatever the configuration is now). I would bump it up to 256M
[mysqld]
init_connect='SET CHARACTER SET=utf-8';
max_allowed_packet=256M

I recommend this because sometimes Error 2006 : 'MySQL server has gone away' is caused by the MySQL Packet not being big enough. I wrote posts about this earlier:

MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob (Nov 22, 2011)
Fixing MySQL errors recorded in error logs (Sep 25, 2011)
MySQL server has gone away obstructing import of large dumps (Sep 01, 2011)


Reducing table size during initial load with ADR in SQL Server When a table is created, and loaded with data from scratch based on a file, the table size changes significantly when Accelerated Database Recovery is on in SQL Server (realized based on my question here, Table size in Azure SQL Managed Instance vs On Premise SQL Server)

Is there any way (perhaps via a hint) to reduce table size in a database with ADR on (as it cannot be turned off in our case) only during an initial load? There is no possibility of conflict during this initial load, and all of the relevant data can be loaded at once.

Thanks!

I don't see any significant difference in this test.

create database nonadr
ALTER DATABASE nonadr SET ACCELERATED_DATABASE_RECOVERY = OFF

create database adr
ALTER DATABASE adr SET ACCELERATED_DATABASE_RECOVERY = ON;

go

with q as
(select top (1000*1000*10) *
 from sys.messages m, sys.objects o
)
select *
into nonadr.dbo.test
from q;

with q as
(select top (1000*1000*10) *
 from sys.messages m, sys.objects o
)
select *
into adr.dbo.test
from q;

go

exec nonadr.sys.sp_spaceused 'test'
exec adr.sys.sp_spaceused 'test'

SELECT DB_NAME(database_id) AS database_name,
       (persistent_version_store_size_kb / 1024.) AS persistent_version_store_size_mb
FROM sys.dm_tran_persistent_version_store_stats
WHERE persistent_version_store_size_kb > 0;

outputs

name                 rows                 reserved           data               index_size         unused
-------------------- -------------------- ------------------ ------------------ ------------------ ------------------
test                 10000000             3352392 KB         3351944 KB         8 KB               440 KB

name                 rows                 reserved           data               index_size         unused
-------------------- -------------------- ------------------ ------------------ ------------------ ------------------
test                 10000000             3494856 KB         3494384 KB         8 KB               464 KB

database_name        persistent_version_store_size_mb
-------------------- ---------------------------------------
adr                  0.070312


Create a grouping based on chains of pairs Say I have two tables:

CREATE TABLE datum
(
  datum_id SERIAL PRIMARY KEY,
  datum_text TEXT NOT NULL
);

CREATE TABLE datum_pair
(
  datum_id1 INTEGER NOT NULL REFERENCES datum (datum_id),
  datum_id2 INTEGER NOT NULL REFERENCES datum (datum_id)
);

datum_pair records related pairs of rows in datum. What I want to do is form identify groups that are all related, including relationships that pass through another row. For example, if the IDs 1 and 3 are related, the IDs 3 and 5 are related, and the IDs 5 and 7 are related, then 1, 3, 5, and 7 would all be in the same group. The number of rows participating in one of these chains is arbitrary.

I will need to perform various aggregations grouped by each chain. I'm not set on how the output should look, but one idea I have is to associate each ID of a chain with a single ID. For example, if 1, 3, 5, and 7 are all in the same chain, then this output would work:

 datum_id | min_id_in_chain
----------+-----------------
     1    |      1
     3    |      1
     5    |      1
     7    |      1

Solutions that offer a different output format but still allow for aggregation by chain are welcome.

There are two additional complications:

datum_pair always contains relationships in both directions. So if there is a row for IDs 1 and 3, there is also a row for 3 and 1. (In my use case, this is actually the result of a self JOIN, but I don't think this is especially relevant to the question.) Each datum should only appear once in the output.
Not all rows in datum participate in a chain; many are solitary. But the final result still needs to include them. I would expect them to be identified as a chain containing only that row, so that aggregation would not combine these together.

I've created a sample SQL Fiddle containing 100 rows. To simplify testing the output, this sample relates all the odd numbered IDs and all the even numbered IDs except for 1 and 99. 1 and 99 are not related to any of the other rows.

Using PostgreSQL 9.3. (I would appreciate mentioning any simplifications provided by features of 9.4.)

That's a complicated puzzle.  I've found a solution that starts with a list of all identifiers.  Then for each identifier, it creates an array of identifiers that it is paired with.  It iteratively expands that array (called "members") until all pairs are included.

; with  recursive list as
        (
        select  id1 as id
        from    pairs
        union
        select  id2
        from    pairs
        )
,       cte as
        (
        select  id
        ,       ARRAY[id] as members
        from    list
        union all
        select  cte.id
        ,       members || ARRAY[p.id1, p.id2]
        from    cte
        join    pairs p
        on      (
                    cte.members @> ARRAY[p.id1]
                    and not cte.members @> ARRAY[p.id2]
                )
                or
                (
                    not cte.members @> ARRAY[p.id1]
                    and cte.members @> ARRAY[p.id2]
                )
        )
select  id
,       min(v) as min_id
from    cte
cross join lateral
        unnest(cte.members) v
group by
        id
order by
        id
;

Example at SQL Fiddle.  Hopefully someone else can contribute a simpler, more elegant, or better performing solution!


Column order for better index let say I have two tables like tbl_parent and tbl_children (just as an example), in the first table I have parent_id, and in the second one I (will) have something like child_id plus the parent_id, plus some other columns.

My question is does it make any sense to make the database columns in tbl_children like parent_id first, following by child_id? Because I will index them, and my WHERE clause should be some thing like WHERE parent_id = x AND child_id = y.

Again this is just an example. I just want to know if column orders could improve the performance on indexed columns or not?

Platform: MySQL, InnoDB, on Integer Values.

No, the column order in the table is not important at all for the performance of any related query or any additional index.

A table T1(a, b) is equivalent to T2(b, a), if the data inside is the same. The order of the columns matter only for an index key, because there the data is ordered after the first column of the key.

PS: let's say on table Table1 (a, b, c, d) an index is created the following way:

CREATE INDEX idx_Table1 ON Table1 (a, b);

This creates an index, which is a data structure additional to a table in the format of a balanced tree (B-Tree) that helps some queries run faster. The index key is that pair formed of the data from columns (a,b) concatenated.

For example a query like:

select a from Table1 where a = 1 and b = 2

would use the index idx_Table1, while a query like:

select a from Table1 where b = 1 and c = 1

would most probably wouldn't use it but would benefit from an index:

CREATE INDEX idx_Table2 ON Table1 (b, a);

Some references:

The MySQL reference page for CREATE INDEX is here.
How MySQL uses indexes: reference page.
A nice book on indexes: Use The Index, Luke.


Do frequent DML operations on a table with indexes and huge data affect SELECT query performance? If I have frequent DML(insert,update,delete) operations on a table with huge data with clustered and non-clustered indexes. Will it affect the Performance of SELECT queries on that table with a where condition on index columns,
Or will the performance remain same as when there are no DML operations?
This Link says UpdateStatistics does block Select queries. So will DML operations cause UpdateStatistics. It says " The only time you wouldn’t be in control of this is if AUTO_UPDATE_STATISTICS is enabled on your database and AUTO_UPDATE_STATISTICS_ASYNC was disabled.". So is changing these values the best solution?
Note:
 I will be using isolation level (Read Uncommitted) for the SELECT query which should not be blocked by locks.

First a comment on terminology: DML (Data Manipulation Language) includes SELECT. I have a feeling that you when you say DML refer to INSERT, UPDATE and DELETE. I'll be referring to these as "modification statements".
You also say that you run "default isolation level (Read Uncommitted)". That is not correct. The default isolation level is Read Committed, not Read Uncommitted.
Read Committed is indeed blocked by exclusive locks, which are acquired by data modification statements. That is the default behavior of Read Committed, but you can re-configure the behavior by changing the database setting read committed snapshot to ON (RCSI). With RCSI readers are not blocked by modifiers, they'll get the last committed version of the row instead of waiting. (RCSI is by default ON for Azure SQL Database, btw.)
Don't worry about update statistics. It doesn't block readers. That blog post you refer to INDEX REORGANIZE at the same time as UPDATE STATISTICS. UPDATE STATISTICS in itself doesn't block readers. I just verified this by running update statistics on the stats for an index and I simultaneously ran SELECT using that index/stats, several times without any hint of blocking.


SSIS 2012: The environment reference n is not associated with the project I have a package that I am updating. The package is executed by a Job Agent job. After deleting the package and then deploying the new version, I run a script that executes any necessary [create_environment], [create_environment_reference], [create_environment_variable], and [set_object_parameter_value] statements.

However, when I start the job, it fails with the following error message (where "n" is a number which doesn't appear in any of the relevant SSISDB tables or views as an id of any kind):

  The environment reference n is not associated with the project

The reason for this is that the existing job step that executes the package contains a /ENVREFERENCE switch in the command line, and that value must be updated for the new version of the package.

This is not obvious, as using SSMS to view the job step doesn't display this switch anywhere. The only way that I have found to fix this is to script out a drop and create of the job, obtain the correct reference_id using code like this:

SELECT  reference_id
  FROM  SSISDB.[catalog].environment_references er
        JOIN SSISDB.[catalog].projects p ON p.project_id = er.project_id
 WHERE  er.environment_name = @Environment
   AND  p.name              = @ProjectName;

and then updating the @command parameter of the call to sp_add_jobstep with the new number.

Helpful references (that you won't find by searching for the error message):

http://www.biadmin.com/2012/08/ssis-2012-gotcha-dtexecexe-and.html
http://blogs.msdn.com/b/mattm/archive/2011/07/24/using-dtexec-with-packages-on-the-is-server.aspx


How do I install/enable the uuid-ossp extension on Postgres 9.3? I’m using Postgres 9.3 on Ubuntu Linux 14.04.  How do I install/enable the uuid-ossp extension?  I first verified the contrib package was installed …

root@prodbox:/home/rails/myproject# apt-get install postgresql-contrib
Reading package lists... Done
Building dependency tree
Reading state information... Done
postgresql-contrib is already the newest version.

and then I logged in and tried to create the extension …

root@prodbox:/home/rails/myproject# su - postgres
postgres@prodbox:~$ psql
psql (9.6.0, server 9.3.11)
Type "help" for help.

postgres=# CREATE EXTENSION "uuid-ossp";
ERROR:  could not open extension control file "/usr/share/postgresql/9.3/extension/uuid-ossp.control": No such file or directory

I’m all out of ideas.  What am I missing?

You seem to have two versions (9.6 and 9.3) installed (because the psql version is 9.6).  Because the postgresql-contrib package automatically defaults to the currently supported PostgreSQL database contrib package (as noted if you do apt-cache show postgresql-contrib), you'll have to install the contrib package for 9.3:

apt-get install postgresql-contrib-9.3


Filter table before join I have a stored procedure that populates the temp table #employee_benefits with a list of Ids. This table ends up being roughly 10,000 rows long. The query below then selects from a table called EmployeeBenefitData that has around 4 million rows.

SELECT  ebd.EmployeeBenefitDataId, ebd.EmployeeBenefitId, ebd.[DataDefinitionId]
FROM    #employee_benefits eb
INNER JOIN EmployeeBenefitData ebd ON eb.EmployeeBenefitId = ebd.EmployeeBenefitId

The bottle neck was the index scan on the EmployeeBenefitData table. It did the index scan first and then joined it to the temp table. The temp table was acting as a filter meaning that scanning all the data before the join was very inefficient. I added the following code to change the scan to a seek and dramatically cut down the amount of reads needed.

DECLARE @MinEmpBenId INT, @MaxEmpBenId INT

SELECT @MinEmpBenId = MIN(EmployeeBenefitId), @MaxEmpBenId = MAX(EmployeeBenefitId)
FROM #employee_benefits

SELECT  ebd.EmployeeBenefitDataId, ebd.EmployeeBenefitId, ebd.[DataDefinitionId],
        dd.TypeId, dd.DataDefinitionId, dd.Name, ebd.[Value], ebd.[Date], ebd.[Text]
FROM    #employee_benefits eb
INNER JOIN EmployeeBenefitData ebd ON eb.EmployeeBenefitId = ebd.EmployeeBenefitId
INNER JOIN DataDefinition dd ON ebd.DataDefinitionId = dd.DataDefinitionId
WHERE   ebd.EmployeeBenefitId >= @MinEmpBenId AND ebd.EmployeeBenefitId <= @MaxEmpBenId

It makes a massive difference in the client stats

Total execution time 74,      1794
  Wait time on server replies   11,     11

My question is: Is this good practice? And why doesn't the optimiser do this?

UPDATE
I should have mentioned that the temp table has a clustered index on EmployeeBenefitID

Is this good practice?

In this circumstance I would say yes. I'd probably also add an OPTION (RECOMPILE) to let it "sniff" the variable values. The optimal plan will likely vary dependant on the proportion of rows in the larger table that match this range.

It provides a potentially useful extra path to the optimiser and it is not something that the query optimiser ever does by itself as far as I know. The closest thing to it is that with a merge join it will stop processing an input when either one is finished. Thus meaning that it potentially avoids a full scan.

The only downside that springs to mind would be if the calculation of the min/max range values itself might be expensive (but this should be very cheap if the table you are using as a filter is indexed on that column).

I created two test tables

CREATE TABLE EmployeeBenefitData(EmployeeID INT PRIMARY KEY);

CREATE TABLE FilteredEmployee(EmployeeID INT PRIMARY KEY);

And loaded EmployeeBenefitData with integers from 1 to 4,000,000 (6,456 pages)

And FilteredEmployee with integers from 2,000,000 AND 2,010,000 (19 pages)

And then ran 6 queries of the following form

DECLARE @E1 INT,
        @E2 INT

SELECT @E1 = FE.EmployeeID,
       @E2 = EBD.EmployeeID
FROM   FilteredEmployee FE
       INNER LOOP JOIN EmployeeBenefitData EBD
         ON FE.EmployeeID = EBD.EmployeeID
OPTION (MAXDOP 1);

The 6 permutations were made up by inverting the order of the two tables and trying all three join types LOOP, MERGE, HASH.

Results were as follows

+------------+-------------+-------+----------------+-----------+---------------+----------+
| Left Table | Right Table | Join  | EBD Scan Count | EBD reads | FE Scan Count | FE reads |
+------------+-------------+-------+----------------+-----------+---------------+----------+
| FE         | EBD         | Loop  |              0 |     30637 |             1 |       19 |
| EBD        | FE          | Loop  |              1 |      6456 |             0 |  8250009 |
| FE         | EBD         | Merge |              1 |      3257 |             1 |       19 |
| EBD        | FE          | Merge |              1 |      3257 |             1 |       19 |
| FE         | EBD         | Hash  |              1 |      6456 |             1 |       19 |
| EBD        | FE          | Hash  |              1 |      6456 |             1 |       19 |
+------------+-------------+-------+----------------+-----------+---------------+----------+

The above figures illustrate the point about merge join as it "only" scans just over half the larger table. It still read all the rows from 1 to 1,999,999 first though and discarded them.

Repeating the experiment with a WHERE EBD.EmployeeID BETWEEN 2000000 AND 2010000 gave the following.

+------------+-------------+-------+----------------+-----------+---------------+----------+
| Left Table | Right Table | Join  | EBD Scan Count | EBD reads | FE Scan Count | FE reads |
+------------+-------------+-------+----------------+-----------+---------------+----------+
| FE         | EBD         | Loop  |              0 |     30637 |             1 |       19 |
| EBD        | FE          | Loop  |              1 |        21 |             0 |    20636 |
| FE         | EBD         | Merge |              1 |        21 |             1 |       19 |
| EBD        | FE          | Merge |              1 |        21 |             1 |       19 |
| FE         | EBD         | Hash  |              1 |        21 |             1 |       19 |
| EBD        | FE          | Hash  |              1 |        21 |             1 |       19 |
+------------+-------------+-------+----------------+-----------+---------------+----------+

The only query that didn't benefit from the additional range predicate is the one where the larger table was on the inside of a nested loops join.

This is of course not surprising as that plan (plan 1 below) is driven by repeated index seeks using the values from FilteredEmployee.

Plan 1 was also the one chosen "naturally" by the optimiser without the range predicate. With the range predicate in place it chose a different merge join plan seeking into the relevant index range without scanning unnecessary rows and costed that as significantly cheaper (plan 2)


Table with huge data takes long time to query I have a table in Postgres that is having over 50million records in production. It’s a table that I have to query it often for debugging and data extraction because it is heavily used by the application.

However, due to the massive amount of data, the table takes a long time for every query to complete. Even for simple queries that just sort the updated_dt column without any other conditions can take a long time while queries with conditions can take 15-30mins to query! It doesn’t help even when I add a limit 1000 the results and indexed the updated_dt column.

This makes the whole table almost unable to be queried by any kind of statements. My Postgres database is hosted on AWS RDS on an already pretty big instance.

Here's the explain statements:

SELECT "id", "store_id", ... FROM inventory ORDER BY "updated_dt" DESC, "id" DESC  LIMIT 1000;

Limit  (cost=9737754.79..9737757.29 rows=1000 width=1710) (actual time=328054.666..328054.790 rows=1000 loops=1)
  Buffers: shared hit=976936 read=6371799
  ->  Sort  (cost=9737754.79..9829882.64 rows=36851142 width=1710) (actual time=328054.665..328054.711 rows=1000 loops=1)
        Sort Key: updated_dt DESC, id DESC
        Sort Method: top-N heapsort  Memory: 3401kB
        Buffers: shared hit=976936 read=6371799
        ->  Seq Scan on "inventory"  (cost=0.00..7717246.42 rows=36851142 width=1710) (actual time=0.011..291962.272 rows=36813941 loops=1)
              Buffers: shared hit=976936 read=6371799
Planning time: 0.078 ms
Execution time: 328055.138 ms

Here are the indexes applied to the table:

CREATE UNIQUE INDEX "inventory_pkey" ON "inventory"(id text_ops);
CREATE INDEX inventory_store_id ON "inventory"(inventory_store_id text_ops);
CREATE INDEX inventory_item_data ON "inventory" USING GIN (item_data jsonb_path_ops);
CREATE INDEX inventory_created_dt ON "inventory"(created_dt timestamptz_ops);
CREATE INDEX inventory_updated_dt ON "inventory"(updated_dt timestamptz_ops);

What can I do make this table which has a huge number of records run faster when being queried?

Your complaint here is rather inconsistent.  You talk about simple queries that just sort on updated_dt, but that is not the query you show.  Also, the query you did show did not take 15-30 minutes.  Is there another even simpler query that takes even longer?  If so, that would server as a better example.  Particularly if you turn on track_io_timing first.

The query you show orders by both updated_dt and id. While conceptually an index on just updated_dt could speed that up by requiring a sort on id only within ties on updated_dt, this is not implemented in any current version of PostgreSQL.  If you want an index to serve your multi-column ORDER BY, you must include both columns in the index.

If you want to make most things faster without building new indexes for all of them, you might want to get either an even larger instance, or provision faster storage.


Does MySQL or PostgreSQL lock table on deletion? I am about to delete large amount of data from a database. I want to know if deletion of large number of records from table in MySQL or PostgreSQL, lock a table ?

Let's say my table has been adding 2 GB data per day since 2016, and I want to delete all records older than 6 months from now.

Would my table get locked and all my insert or update or delete queries fail during the deletion time ?

I don't know about MySQL but Postgres never locks the whole table when running DML statements - regardless of the number of affected rows.

Additionally writers never block readers, so even if you update all rows in a table, other transactions will still be able to SELECT from that table - obviously seeing the old values until your DML statement is committed.

Deleting rows from the table will also not block concurrent inserts (as long as there are no primary key conflicts). And inserting rows won't block deleting existing rows.

Again: the above is true for Postgres. I don't use MySQL, so I can't comment on that.


SQLSTATE[HY000]: General error: 1025 Error on rename of I'm getting this error:

  SQLSTATE[HY000]: General error: 1025 Error on rename of './salt/ghost' to './salt/#sql2-4ab-630b' (errno: 152). Failing Query:
  "ALTER TABLE ghost DROP FOREIGN KEY ghost.row_id"

I looked for some information but I didn't get a solution..any help?

This is my DDL:

ghost,"CREATE TABLE `ghost` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `packet_number` int(11) DEFAULT NULL,
  `tbl_name` varchar(50) DEFAULT NULL,
  `row_id` int(11) DEFAULT NULL,
  `row_id2` int(11) DEFAULT NULL,
  `column_name` varchar(50) DEFAULT NULL,
  `old_value` mediumblob,
  `new_value` mediumblob,
  `ghost_action` varchar(255) DEFAULT NULL,
  `ghost_user_id` int(11) DEFAULT NULL,
  `ghost_action_comments` varchar(255) DEFAULT NULL,
  `ghost_status_id` int(11) DEFAULT '1',
  `ghost_status_comments` varchar(255) DEFAULT NULL,
  `serialized` mediumblob,
  `parent_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `created_by` bigint(20) DEFAULT NULL,
  `updated_by` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_change_1_idx` (`ghost_user_id`),
  KEY `fk_change_2_idx` (`ghost_status_id`),
  KEY `row_id2_idx` (`row_id2`),
  CONSTRAINT `ghost_ghost_status_id_ghost_status_id` FOREIGN KEY (`ghost_status_id`) REFERENCES `ghost_status` (`id`),
  CONSTRAINT `ghost_ghost_user_id_sf_guard_user_id` FOREIGN KEY (`ghost_user_id`) REFERENCES `sf_guard_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=120450 DEFAULT CHARSET=utf8"

Javi

Without knowing your DDL, I did find this on the docs regarding error code 1025, in one of the user comments:

  To avoid getting this error while trying to drop a foreign key, use the constraint name rather than the column name of the foreign key.

So, instead of dropping the ghost.row_id column, try dropping the foreign key using the FK name.


How can I free space in oracle mount point (/u01/app/oracle/)? My question is what can I delete or how can I free space so that I can log onto oracle via sqlplus as sysdba. When I execute the command df -k to see the free space in the current directory it appears to be full and on the other machine its at 52%.

Filesystem                   1K-blocks     Used        Available  Use%    Mounted on

/dev/mapper/rootvq-rootlv    51606140    48985496      0          100%    /

If your oracle installation is on your root volume you should have a look at the DIAG_DESTINATION, e.g. /u01/app/oracle/diag/. If there's a lot of old trace files in there you can safely remove them with adrci. If your oracle installation is on a separate disk you have to check where the space is consumed (/var?) and free space there.


SQL Server table name starting with # in user database, not in tempdb, not a temp table Somehow, decades ago, a table got created in our database that starts with a #. It shows up in Object Explorer under the app's database, not in tempdb. For some reason, Azure won't import the database like this.

We can't drop it, rename it, or interact with it. I've tried Delete from Object Explorer, Script Drop, Rename from the GUI and none of them have worked.

We're on SQL 2008 R2.

drop table [*app*].[dbo]."#OBSOLETE";

Database name '*app*' ignored, referencing object in tempdb.
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#OBSOLETE', because it does not exist or you do not
have permission.

exec sp_rename "dbo.#OBSOLETE", "dbo.obsolete"

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 338
No item by the name of 'dbo.#OBSOLETE' could be found in the current database '*app*', given that @itemtype was input as '(null)'.

How do we kill this object so we can migrate this to Azure?

Given:

sp_rename uses object names instead of object IDs,
we cannot use the object name since it starts with a # and that is interpreted as having special meaning and is handled differently,
all other options have been exhausted

You should try editing the underlying system catalog table directly via a Dedicated Admin Console (DAC) connection:

Get the object_id of that table.
Restart the instance in Single-User mode. This is to be able to directly update the system tables (i.e. not a requirement for using the DAC connection).
Connect via Dedicated Admin Console connection. You can do this in a SQLCMD interactive session by running the following in a Command Prompt window:

C:\> SQLCMD -A -E

or, connect directly to the DB using:

C:\> SQLCMD -A -E -d {database_name}

In that DB, try something like the following:

UPDATE sys.objects$ {enter}
SET [name] = N'obsolete' {enter}
WHERE [object_id] = {ye_olde_object_id}; {enter}
GO {enter}

It won't execute the statement until you put in the GO {enter}.

Please be cautious when directly editing system catalog tables, and do not get too comfortable with the idea of doing it. This is something that should be done only if there is absolutely no other way of fixing a problem (such as the case here). There are probably several reasons to avoid making direct edits, but the two that initially come to mind are:

Much like the data-models that we create, there are likely rules and workflows for how things work that we are unaware of (e.g. denormalization, "business" rules governing the state of data across various tables, etc)
Quite likely making direct edits voids Microsoft's responsibility to assist you if you run into problems and have a support contract (I have not seen the terms of the Support agreement(s), but I have a hard time believing that such language would not be in there)

@Paul Randal confirmed in a comment on a related answer of mine: "manually editing a system table irrevocably sets a flag in the boot page of the database that marks your database as having been edited in this way, and CSS might decide to not help you if you subsequently have problems with that database."


How to conditionally append rows to a result set? I have a complex SELECT query table that identifies relationship between event_id and attribute. Here is a simplification with a VALUES expression:

SELECT event_id, attribute
FROM (
  VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
) event_attribute (event_id, attribute)

I want to include an extra (event_id, '2D') record for every event_id that is not already associated with a 3D or 2D attribute. How to conditionally append rows to a result set?

In case of the above table, the expected result would be:

(1, '2D'),
(1, 'IMAX'),
(2, 'IMAX'),
(2, '2D'),
(3, '3D')

There is also a table event with one row for each relevant id.

Assuming (like was added later) a separate table event with all relevant unique id - this helps performance a bit:

WITH cte(event_id, attribute) AS (
   -- big SELECT query goes here instead of the VALUES expression
   VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
   )
TABLE cte
UNION ALL
SELECT e.id, '2D'
FROM   event e
LEFT   JOIN cte ON cte.event_id = e.id
               AND cte.attribute IN ('2D','3D')
WHERE  cte.event_id IS NULL;

Related:

Select rows which are not present in other table
Optimize GROUP BY query to retrieve latest record per user
Is there a shortcut for SELECT * FROM?

If your query only returns a subset of all event_id, you can't use the table event to simplify like this. Alternative without table event:

WITH cte AS (
   -- big SELECT query goes here instead of the VALUES expression
   VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
   )
TABLE cte
UNION ALL
SELECT event_id, '2D'
FROM   cte
GROUP  BY 1
HAVING count(*) FILTER (WHERE attribute IN ('2D', '3D')) = 0;

This is somewhat similar to what you answered yourself, just shorter and a bit faster. In particular the aggregate FILTER clause should be instrumental. Related:

For absolute performance, is SUM faster or COUNT?

Since there are no indexes on the derived table from the CTE, the second query may be faster to begin with.


Why different datetimes freezes a query based on indexes? I have a table with a billion records. It has these columns:

Id bigint,
MobileNumber varchar(100),
Date datetime,
Message nvarchar(400)

And it has non-clustered indexes on MobileNumber and Date fields.

I want to find what a MobileNumber has sent us in a specified period. Thus I run this query:

select Message
from ReceivedMessages
where [Date] > 'from-time'
and [Date] < 'to-time'
and MobileNumber = 'number-to-filter'

And this query works lightening-speed fast for past 2 years. But when I change the from-time part to a closer date, it freezes out and takes more than 2 minutes to complete. In other words, based on different inputs, it behaves differently, sometimes even hanging out and not returning for more than 10 minutes.

I expected a consistent behavior. What do I miss about indexing? What can cause this inconsistent performance?

Update: I changed names of columns and table, so I can't attach execution plan as a picture. But here's the issue. Thanks for guiding me.

when I change value of date parameter, SQL changes index seek from IX_MobileNumber to IX_Date. I never thought that SQL creates execution plan based on the value of parameters. How that could be?

We have had the similar issue when dealing with big (billions of records) tables. As Martin Smith suggested in comment - you would probably need to periodically rebuild statistic data on that table.
This worked for us:

UPDATE STATISTICS <TABLE NAME> WITH FULLSCAN

This solution is kind of brute-force and being too simple but still it may help.


pacemaker and semi-synchronous replication In the company I work we use a simple setup of a Primary-Secondary replication. Should the Primary break down for whatever reason, we do the switch manually. This also means, that MySQL hardly ever gets updated. I want to make it possible, to update the servers without downtime. For various reasons we do not want a (over-)complicated solution. So I'm wondering, to achieve my goal, can it be as simple as this:
A Primary-Primary replication with GTID enabled and semi-synchronous replication.
Pacemaker to switch a virtual IP from one server to the other, so I can stop one server to update. Then switch back and update the other server.
For the Primary-Primary replication I do not have auto-increment-increment configured differently. All write processes would use the virtual IP of pacemaker and therefore would write to only one host.

We do this at my company. We run thousands of MySQL master-master pairs that use this method. We developed a service that performs the switch you describe. We call this a "successover" because that has a more positive connotation than failover. We can and do run this anytime of day, without even notifying the app team that we are doing it.
Here are the general steps:
Assume:
"Master1" is the current writeable master MySQL instance, where the VIP is defined.
"Master2" is a replica, in read-only mode.

Make Master1 read-only.
Remove VIP from Master1.
Start killing any outstanding queries on Master1. Continue killing any queries in a loop, in case the apps using it try to run new queries. We shouldn't rely on the prior steps terminating queries or connections. I.e. clients might have bypassed the VIP and connected directly to the physical IP address.
Wait for replication lag on Master2 to reach 0. Ideally, compare slave status on Master2 to master status on Master1. If the failover occurred because Master1 is down, then you can't do that comparison, but you can compare if slave status on Master2 shows SQL thread is caught up to the IO thread.
Make Master2 writeable.
Add VIP to Master2.
Stop killing queries on Master1.

When it's automated, this results in less than a second of downtime, provided there is no significant replication lag. Moving a VIP is a lot faster than updating DNS.
Then the apps must reconnect to the VIP, and thus get access to the new primary instance and re-run any queries they need.
Obviously apps should not use the standby MySQL instance, because the point is to allow it to be taken offline for updates, config changes, etc. This also gives you a quick way to respond if there's a problem with the host server that MySQL is running on. We get a few host crashes or failed disks per week, so we do this switch quickly to make sure the app can continue.
It's unavoidable that this causes a brief "blip" where the apps have their connections dropped and have to reconnect, but it's more brief of an interruption than any other solution. Still, apps have to be designed to detect a dropped connection and reconnect. Our biggest issue is educating the app developers to do this. They keep complaining that they get alerted for a lost connection, and we tell them, "we've already documented what you need to do -- it shouldn't alert for a single blip."
This system has been working for several years, but we now have a mixed environment where we have many cloud MySQL instances. We need a new solution, because we can't create VIPs using BGP in the cloud.
So we have prototyped using Envoy as a proxy to MySQL. We believe this works, but we need to develop a service to notify the Envoy proxy of the change when we do a successover. Envoy supports GRPC protocol, so we can send it a message dynamically and it'll start routing traffic to a different target MySQL instance. This proxy-based solution should work identically in the cloud as it does in the datacenter. But this solution is probably more work than you had in mind.
We could also use ProxySQL to do something similar, but Envoy has a lot of adoption within our company for service-to-service traffic already, so if we can leverage that instead of a new type of proxy we have one fewer pieces of technology to use.

Update re comments:
Step 4 in the above list waits for replication to catch up while both instances are read-only. So there are no new updates allowed on Master1 during that time, and Master2 only has to execute a set of remaining updates that were committed before Master1 was set to read-only. Hopefully, this is a very brief wait, unless Master2 was already lagging behind by a lot.
The service that runs the successover refuses to even begin the operation if it detects that Master2 has high replication lag. The user is encouraged to try again later.
Of course, in the real world sometimes you have to override that sort of restriction because of urgent circumstances, so there's an option to force the successover. But this comes with a risk, because if you make Master2 the primary and start allowing new queries directly on it, while there are still outstanding updates to process because of replication lag, you end up in a split-brain situation: You could make an update which will then be replaced by an event from the binary log that actually occurred in the past.
So in theory you could enable the VIP on Master2, but leave Master2 read-only until it catches up fully with respect to replication. This at least performs part of the successover and allows clients to read data, but not update, temporarily. This might be acceptable for some apps for a short time, but it depends on the app's requirements.
In practice, our implementation of successover doesn't do this temporary read-only mode. We just try to be very reluctant to use the forced-successover option, because a split-brain is extremely difficult to clean up (it may not be possible). We'd rather try the successover when there is no replication lag.


`SELECT FOR UPDATE` in master-master mysql setup I am benchmarking a database schema with a dozen workers processes and was wondering what a SELECT FOR UPDATE does in a master-master configuration.

For the moment it seems to work, but I am either doing INSERT on one and SELECT FOR UPDATE + UPDATE on the other, or doing it on both at the same time.

There is a big lag in the replication (the numbers shows that it keeps growing over time). What would happen if I do some SELECT FOR UPDATE + UPDATE on both while still doing some INSERT on one and not both ?

Can the database be corrupted ?

Generally, it is considered a very bad idea to write to both masters in master/master replication, unless you are writing to different schemas.

MySQL replication is asynchronous (MySQL 5.5 has "semi-synchronous" replication, but it's still asynchronous in practice).  There is no mechanism to synchronize locks or detect conflicts between hosts.

Consider what happens in this situation

master1> BEGIN;
master1> SELECT * FROM table WHERE id = 7 FOR UPDATE;
master1> UPDATE talbe SET col = "Newton" WHERE id = 7;

master2> BEGIN;
master2> SELECT * FROM table WHERE id = 7 FOR UPDATE;
master2> UPDATE table SET col = "Aaron" WHERE id = 7;

master1> COMMIT;
master2> COMMIT;

Depending on timing, replication lag, and whether you have ROW or STATEMENT based replication, you might end up with different data on different hosts.  Certainly, master1's FOR UPDATE will not lock the rows on master2 and vice versa, thus negating the utility of FOR UPDATE (which is to be avoided).

In your situation you could, at best, end up with broken replication and at worst end up with silent data sync issues between the servers.


MySQL Install on Windows 8.1 hangs at server restart I have a 64-bit Windows 8.1 machine and would like to install a local database to host some helper application databases. I have attempted this several times with this version of MySQL (5.6.25 GA) but it hangs at the server restart during the installation. My internet has been stable each time I have attempted this so from my assumptions, this is not caused with a network drop-out.

EDIT

After looking through the install logs and reading the MySQL Notifier message. According to those, the Windows Service cannot be found. During the installation, the service was called MySQL56 but MySQL Notifier says mysql56. Am I doing something wrong?

As I mentioned in the comment, I installed MySQL 5.6.24 for Windows.
I have a 4-year-old post in ServerFault (Installing isolated instance of MySQL on Windows using silent install with .msi) I outline how to install MySQL as a service by hand with

Custom Folder for MySQL Binaries
Custom Folder for MySQL Data
Custom Name for MySQL Service

I also have SuperUser post on why I personally avoid MSI installs of MySQL
I have worked with MySQL Query Browser, MySQL Administrator, and MySQL Workbench the in a much simpler way:

Just copy the software into the desired folder.
Create a Windows Shortcut (.lnk) for the .exe file
Put the Windows Shortcut (.lnk) on the Desktop or Start Menu
Double Click to Launch

Give it a Try !!!


Is dropping foreign key constriants an event in Oracle? I wrote the following trigger in Oracle:

create or replace trigger foreign_drop before ddl on database
begin
  insert into ddl_log
  select ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, null, user,
sysdate
  from dual;
end foreign_drop;

It fires whenever a DDL event happens. But when I drop a foreign key constraint, nothing is recorded. Is dropping a foreign key constraints considered an event? How could I record the action of dropping a foreign key constraint?

The following trigger captures the drop constraint operation.

create or replace trigger foreign_drop after create or alter or drop on database
begin
  dbms_output.put_line('DDL Fired!');
end foreign_drop;
/
SQL> set serveroutput on
SQL> CREATE TABLE t2
( id number,
  CONSTRAINT fk_id
    FOREIGN KEY (id)
    REFERENCES t1(id)
);  2    3    4    5    6
DDL Fired!

Table created.

SQL> alter table t2 drop constraint fk_id;
DDL Fired!

Table altered.


Entity whose possible types differ in the FK I have one entity called "Groups".
"Groups" can be of different types and the difference between each type is the FK.
For instance, if a "Group" is of type "Event Group", then it uses eventID; if it's of type "Institution Group", then it uses institutionID.
What's the proper way to model this?

EventGroup would be one table, InstitutionGroup would be another, each with their own primary key and correlating fields. The foreign key in Group on eventID would reference the primary key in EventGroup, and the foreign key institutionID would reference the primary key in InstitutionGroup.


Better approach for "LIKE OR LIKE, OR LIKE, OR LIKE, OR LIKE" In this question he's having the same problem as I am. I need something like:

select * from blablabla
where product
like '%rock%' or
like '%paper%' or
like '%scisor%' or
like '%car%' or
like '%pasta%'

This is ugly and it's not using indexes.. In this case, this is really the only way to do this ( to select multiple words inside a string ), or should I use FULLTEXT?

As I understand, with fulltext, I can select multiple words inside a string.

This question talks about Full Text as well

Full text indexes generally aren't a magic bullet, and require additional maintenance, disk space, and fairly intrusive changes to query patterns.

Unless you're truly in need of indexing large documents (think email bodies, PDFs, Word docs, etc.), they're overkill (and if we're being honest, I'd take that process out of SQL Server entirely and use Elasticsearch or something similar).

For smaller use-cases, computed columns are generally a better approach.

Here's a quick demo setup:

use tempdb

CREATE TABLE #fulltextindexesarestupid (Id INT PRIMARY KEY CLUSTERED, StopAbusingFeatures VARCHAR(100))

INSERT #fulltextindexesarestupid (Id)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (@@ROWCOUNT))
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2

UPDATE #fulltextindexesarestupid
SET StopAbusingFeatures = CASE WHEN Id % 15 = 0 THEN 'Bad'
                               WHEN Id % 3 = 0 THEN 'Idea'
                               WHEN Id % 5 = 0 THEN 'Jeans'
                               END

ALTER TABLE #fulltextindexesarestupid
ADD LessBad AS CONVERT(BIT, CASE WHEN StopAbusingFeatures LIKE '%Bad%' THEN 1
                    WHEN StopAbusingFeatures LIKE '%Idea%' THEN 1
                    ELSE 0 END)

CREATE UNIQUE NONCLUSTERED INDEX ix_whatever ON #fulltextindexesarestupid (LessBad, Id)

Querying based on even a non-persisted column gives us a plan that 'uses indexes' and everything :)

SELECT COUNT(*)
FROM #fulltextindexesarestupid AS f
WHERE LessBad = 1