MySQL master/slave replication... structure not being replicated Edit:  I might have answered my own question while going back thru the links for this post.  I would still like to get insight from more experienced DBA's and perhaps make the information easier to find for anyone that might need it in the future.

I have 2 MySQL servers.  One sitting right beside me, the other in a remote data center.  Both servers are identical.  Both are dedicated. Both are running Ubuntu 12.04 LTS, MySQL 5.5.28.  These servers power our in house web application, 2 web sites, virtual mail for 9 domains and backend for MS Access.  The slave is not replicating structure changes in the master.

I have reviewed:
dev.mysql.com/doc/refman/5.0/en/replication-howto-slavebaseconfig.html
dev.mysql.com/doc/refman/5.0/en/replication-howto-existingdata.html

And tracked down other issues with my configuration thanks to this question: serverfault.com/q/336888/143841.
Where I posted an answer that solved both mine and his issues at that time.  However, I am having trouble finding a real solution to ensure that structure queries are replicated right along with data queries.

Here is my configuration

Master:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |  6547573 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

my.cnf on master:

#master/slave replication
server-id=172868
log-bin=mysql-bin
log-error=mysql-bin.err

Slave:

mysql> show slave status\G; | pastebin

my.cnf on slave:

# master/slave configuration
server-id=1357749477

I'm not highly concerned about the error listed in the slave status report.  I believe that issue was triggered by changes I made to the structure on the master.  I'm trying to configure the slave to apply any structural changes that occur on the Master.

I found a reference to some slave settings in a mysql bug report: bugs.mysql.com/bug.php?id=956

> replicate-do-db=rate
> replicate-ignore-table=rate.votes
> replicate-ignore-table=rate.clicks
> replicate-wild-ignore-table=rate.%cache

But I'm having trouble finding clear documentation on their usage and repercussions.... Well, on this page I found some information that says it won't replicate any statement made after a USE db; statement.  My ALTER statement was preceeded by a USE statement. But, I don't have replicate-do-db set.  Then further down it states that database-level changes are applied if replicate-wild-do-table=% is set.

So, is "replicate-wild-do-table=%" the correct way to get complete replication?  What are the ramifications of using this setting?  What is the correct way to configure the slave for complete replication of all databases.

Using replicate-wild-do-table=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:

The replicate-* options are restrictive by their presence.  The *-do-* options seem to be telling the server what to "do," but in fact they are telling the server what to only do.
The complete absence of any replicate-* configuration variables means "replicate everything."

  In the simplest case, when there are no --replicate-* options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html

That, I think, is the point you are needing.  It holds true for all MySQL 5.x.

Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ..., and then START SLAVE, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.

If you started out with identical data sets, everything should behave exactly as you would expect it to.

What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions.  Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."

When you use the --replicate-* options, the document cited above also offers this tip:

  it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options

You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.


GROUP BY not for empty strings I would like to group by email. Sometimes email is missing in records, which then should not be grouped.
How can I create an exception for empty strings when grouping?
CREATE TABLE example (
  id INT NOT NULL AUTO_INCREMENT,
  fullname VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  something VARCHAR(255) NOT NULL
);
INSERT INTO example (id, fullname, email, something) VALUES
  (1, 'Name 1', 'name1@example.com', 'Something 1'),
  (2, 'Name 2', '', 'Something 2'),
  (3, 'Name 1', 'name1@example.com', 'Something 3'),
  (4, 'Name 3', 'name3@example.com', 'Something 4'),
  (5, 'Name 3', 'name3@example.com', 'Something 5'),
  (6, 'Name 4', '', 'Something 6');

As a result, I would like:
SELECT fullname, email FROM example GROUP BY email [...];
+----------+-------------------+
| fullname | email             |
+----------+-------------------+
| Name 2   |                   |
| Name 4   |                   |
| Name 1   | name1@example.com |
| Name 3   | name3@example.com |
+----------+-------------------+

You can use UNION and use it to get the wanted result.
Instead of WHERE email = '' you can use WHERE email IS NULL, if the column doesn't have '' as text

CREATE TABLE example (
  id INT NOT NULL AUTO_INCREMENT Primary KEY,
  fullname VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  something VARCHAR(255) NOT NULL
);

INSERT INTO example (id, fullname, email, something) VALUES
  (1, 'Name 1', 'name1@example.com', 'Something 1'),
  (2, 'Name 2', '', 'Something 2'),
  (3, 'Name 1', 'name1@example.com', 'Something 3'),
  (4, 'Name 3', 'name3@example.com', 'Something 4'),
  (5, 'Name 3', 'name3@example.com', 'Something 5'),
  (6, 'Name 4', '', 'Something 6');

SELECT GROUP_CONCAT(DISTINCT fullname), email
FROM example
WHERE email <> ''
GROUP BY email
UNION
SELECT fullname, email
FROM example
WHERE email = ''
ORDER BY email;

GROUP_CONCAT(DISTINCT fullname) | email
:------------------------------ | :----------------
Name 2                          |
Name 4                          |
Name 1                          | name1@example.com
Name 3                          | name3@example.com

SELECT GROUP_CONCAT(DISTINCT fullname), email
FROM example
GROUP BY email

GROUP_CONCAT(DISTINCT fullname) | email
:------------------------------ | :----------------
Name 2,Name 4                   |
Name 1                          | name1@example.com
Name 3                          | name3@example.com

db<>fiddle here


Biggest possible double/float for a column in my MySQL database I am developing a PHP application in which I am in need to store values bigger than 1 million, but with the flexibility of having floating points. I know the traditional float and double. I am looking to store only up to two decimal points, and will only handle operations with numbers that have 2 decimals exclusively.

I have chosen my column type of double and unsigned, but whenever I store a row with a value bigger than one million, it gets truncated to 999999.99

Why is this? I am looking for the right datatype/solution. If you could point me in the right direction, I will appreciate it a lot!

Thank you for your time in advance!

Cheers.

Do not use (m,n) on the end of FLOAT or DOUBLE.  That causes a rounding (at the bottom) or a truncation (at the top).  If you want (m,n), you probably should use DECIMAL(m,n).

FLOAT stores 24 significant bits of data (equivalent to about 7 decimal digits; storage=4 bytes), with an exponent ranging over about 10 ** +/-38.

DOUBLE stores 53 bits (about 16 digits, 8 bytes) and has a huge range.

Your question is vague -- are you storing "money"?  If so, then you really should use DECIMAL(m,2).  m=8 lets you store up to a million dollars (or Euros, etc).  But m can be as big as you want.  (Remember to subtract the 2 before seeing how big the numbers can be.)  (14,2) would store up to a trillion dollars, with precision to the penny.  It will take 8 bytes of storage.


MySQL: Is it safe to have a table with multiple millions of rows? I have a very complex search query to write that will touch many tables, have conditions and joins. For efficiency reasons, I am strongly considering creating a "cache table" where I would maybe once a day compute most of the work so that when a user wants to do a search, it would be faster.

However this cache table could easily have 10 to 50 million rows. Is this bad? Is this worth it? Will I gain in efficiency? What are things that I should keep an eye for?

We use InnoDB.

If your data can be pre-calculated and is static intra-day then why not?
After all, isn't this what a data warehouse does?

As for having up to 50 million rows, this is peanuts.


What does a green icon mean and why do the package bodies not expand when they have it? In SQLDeveloper 4 some 10g PL/SQL packages have a regular crate while others have a green dot.

Those with the green dot do not expand their bodies (might be just a coincidence). What is the difference between the packages with and w/o a green dot and why do some packages not expand their bodies?

The green bug you're seeing (look closely) means the package have been compiled with DEBUG option. It is also seen on bare procedures and functions. It is a coincidence they don't expand - maybe a bug (duh) in SQLdeveloper :) On mine, these expand without problem.


Turning "Set Statistics IO" for stored procedure doesn't show all tables used in stored procedure - why? I'm reviewing a stored procedure that's been a performance problem. And I just realized that some of the tables which are used in the stored procedure don't even show up AT ALL in the results from Set Statistics I/O?
Is this normal, or a bug? It's a little disturbing because I use the results of Set Statistics I/O a lot, when figuring out what to index.
The tables that don't show up in the output of Set Statistics I/O are in a subselect on the main query, like the ActivityFeedCache table below:
SELECT *
FROM (
    SELECT AF.ActivityFeedID
        ,A.ActivityType AS ActivityType
        ,A.IconClass
        ,AF.SubscriptionID
        ,AF.ObjectID
        ,AF.ObjectType
        ,(
            SELECT L.Locale
                ,AFC.LocalizedMessage
            FROM ActivityFeedCache AFC
            JOIN Locales L
                ON AFC.LocaleID = L.LocaleID
            WHERE ActivityFeedID = AF.ActivityFeedID
            FOR JSON PATH
            ) AS MessageJSON
    FROM ActivityFeed AF
    ETC
    ETC
    ) AS Results

This is SQL Server 2017 (RTM-CU13).
EDIT:
I've narrowed down to a small query that shows a missing table in the Statistics IO output. The Locales table is missing in Statistics I/O, but shows up in the execution plan. Here it is:
-- Locales table doesn't show in statistics i/o output
SELECT AF.ActivityFeedID
        ,(
            SELECT L.Locale
                ,AFC.LocalizedTeamMessage AS LocalizedMessage
            FROM ActivityFeedTeamCache AFC
            JOIN Locales L
                ON AFC.LocaleID = L.LocaleID
            WHERE ActivityFeedID = AF.ActivityFeedID
            FOR JSON PATH
            ) AS TeamMessageJSON
    FROM ActivityFeed AF
Where AF.ActivityFeedID = (select top 1 ActivityFeedID from ActivityFeed)

Here's the Statistics I/O output:

(1 row affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ActivityFeedTeamCache'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ActivityFeed'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And here's a small chunk of the actual execution plan XML, just so you can see the Locales table is actually being used:
  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
    <DefinedValues>
      <DefinedValue>
        <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Locales]" Alias="[L]" Column="Locale" />
      </DefinedValue>
    </DefinedValues>
    <Object Database="[Database1]" Schema="[dbo]" Table="[Locales]" Index="[PK_Locales]" Alias="[L]" IndexKind="Clustered" Storage="RowStore" />
    <SeekPredicates>
      <SeekPredicateNew>
        <SeekKeys>
          <Prefix ScanType="EQ">
            <RangeColumns>
              <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Locales]" Alias="[L]" Column="LocaleID" />
            </RangeColumns>
            <RangeExpressions>
              <ScalarOperator ScalarString="[Database1].[dbo].[ActivityFeedTeamCache].[LocaleID] as [AFC].[LocaleID]">

There is a scenario where an estimated execution plan can contain an operator that doesn't get used at runtime, for example when a predicate renders the operator a no-op.
It's tricky to spot because there aren't many clues it's happened, other than the operator's actual execution count is 0. I helped drive a change in SentryOne Plan Explorer that makes it more obvious - the operator icon gets dimmed. You can read about this change in this blog post under the heading, "Apply opacity for operators with 0 executions + actual rows."
If a table is only touched by a single operator in the execution plan, and that operator gets eliminated at runtime (meaning the table does not get touched), that table will not show up in Statistics IO.


Execute Stored Procedure against CMS from within a SQL Server Agent job Is it possible to execute a stored procedure (or any T-SQL) against a Central Management Server (CMS) via a SQL Agent job?

The idea here is to exercise the CMS to hit all the registered instances with a query (or action), in an unattended (non-SSMS, non-real time) way.

The answer to this is: not possible, however my research lead me to these tables in MSDB which are used to populate the CMS registered servers. These tables can be referenced in your code:

sysmanagement_shared_registered_servers_internal

sysmanagement_shared_server_groups_internal


Optimizing DELETE Query on MySQL MEMORY Table I run a large forum which maintains a MySQL database for backend data storage. The 'session' table tracks logged in users & guests. It is currently about 100k records, so not really that large. However, this session table is showing up in the slow query log when we trim old records:

# Time: 120719 10:05:11
# User@Host: xxx[xxx] @  [10.x.x.x]
# Thread_id: 369051896  Schema: forumdb  Last_errno: 0  Killed: 0
# Query_time: 8.352811  Lock_time: 0.000028  Rows_sent: 0  Rows_examined: 19635  Rows_affected: 19635  Rows_read: 0
# Bytes_sent: 13  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
SET timestamp=1342710311;
DELETE FROM session
    WHERE lastactivity < 1342709401;

I've confirmed there is a BTREE index on the lastactivity table:

mysql> SHOW INDEX FROM session FROM forumdb;
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| session |          0 | PRIMARY      |            1 | sessionhash  | NULL      |       78941 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid       |            1 | userid       | NULL      |       26313 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | idhash       |            1 | idhash       | NULL      |        8771 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_2     |            1 | userid       | NULL      |        NULL |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_2     |            2 | lastactivity | NULL      |       39470 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_3     |            1 | userid       | NULL      |        NULL |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | userid_3     |            2 | host         | NULL      |       39470 |     NULL | NULL   |      | HASH       |         |               |
| session |          1 | lastactivity |            1 | lastactivity | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)

I'm curious why the delete query is taking so long with so few records.

It should be noted that this table is utilized very heavily with so many users online at the same time. We have some heavy hardware sitting behind it.

Any ideas on what I can do to speed up this process? It appears to be locking the table so that I see high load across my cluster when this delete function is taking place.

Query cache is turned off (we use memcache). Here is relevant portions of my.cnf:

table_open_cache=8242
table_definition_cache=600
open_files_limit=65535
binlog_cache_size=6M
sort_buffer_size=8M
key_buffer_size=5G
myisam_sort_buffer_size=256M
join_buffer_size=3M
thread_cache_size=1000
thread_concurrency=16
ft_min_word_len=3
tmp_table_size=512M
max_allowed_packet=128M
max_heap_table_size=512M
read_rnd_buffer_size=1M
skip-external-locking

query_cache_limit=1M
query_cache_size= 32M
query_cache_type = 0

Finally, here is some more info on the table:

FIXED:

After changing table over to InnoDB (at 17:15 in the chart below) I am seeing much better performance and no more DELETE in the slowlogs for this table. The whole cluster is seeing better performance since making this change. Thank you.

I would probably convert your Memory table to InnoDB

ALTER TABLE session ENGINE=InnoDB

for your situation. Memory tables shine for low writes, many-reads that you don't care if  you lose the data (loading configuration values, for instance). But when you start writing a lot, the table locks kill you if you have many connections accessing it:

  Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions. [src]

Tracking of session data may seem like a good candidate because of not caring of the data-loss, but I think you might gain significant benefit of removing table locks by switching to InnoDB in this case.


T-SQL - OPTION (FAST x) and trace flag 8722 I have searched a long time and still haven't found the answer to my problem.

Our Dynamics AX is generating queries with the query hint OPTION (FAST x), which sometimes forces a bad execution plan to be used.
The devs say it is by default and is hard to change (has to be fixed on all forms potentially).

So I was after a way to override these hints using trace flags. I found the lovely trace flag 8722 which is claimed to make SQL Server ignore some query hints, especially the ones in the OPTION clause.

However, this doesn't work in my case. I tried to enable trace flag 8602 (disable index hints) as well, but my query is still running with the FAST x hint (it is much slower than when I actually remove the OPTION clause).

I tried to clear the plan cache as well, to no avail.

Any ideas? Am I missing anything?

P.S. I enabled the trace flags globally, this is SQL Server 2012 Developer edition

As far as I know, trace flags 8722, 8755, and 8602 were never officially documented. The last time I remember 8722 and 8755 being effective was in SQL Server 2000, so it is not surprising you find they are ignored in SQL Server 2012. Trace flag 8602 is still functional in SQL Server 2019 as a global or start-up flag to ignore index hints specifically.
For specific query patterns, it is often possible to remove the FAST n hint using plan guides. Even so, the best fix is to get the AX developers to fix it on the application side of things.


Plan guide validation with fn_validate_plan_guide gives false positives When validating a plan guide for a piece of SQL in a stored procedure that references a temporary table named "#test" the function fn_validate_plan_guide returns the error: Invalid object name '#test'.

But the the plan guide still pushes the query hint into the SQL and the desired execution is achieved.

Does this highlight a problem with the fn_validate_plan_guide function?

The script below recreates the problem.

--Enable the actual execution plan before running the query so the plans can be compared

USE [msdb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[test]
AS
BEGIN
    CREATE TABLE #test
    (
        ID INT
    )

    INSERT INTO #test
    SELECT ROW_NUMBER() OVER(ORDER BY job_id)
    FROM dbo.sysjobs

    SELECT *
    FROM #test t
    JOIN #test t2 ON t.ID = t2.ID

    DROP TABLE #Test
END
GO

--Execution before the plan guide is created will have a hash join in the second batch
EXEC msdb.dbo.test
GO

--Create the plan guide
EXEC sp_create_plan_guide 'test',
'   SELECT *
    FROM #test t
    JOIN #test t2 ON t.ID = t2.ID',
'OBJECT', 'dbo.test', NULL, 'OPTION (MERGE JOIN)'
GO

--Validate the plan guide. This returns the error "Invalid object name '#test'."
SELECT
    plan_guide_id, msgnum, severity, state, message,
    name, create_date, is_disabled, query_text, scope_type_desc, scope_batch, parameters, hints
FROM sys.plan_guides
CROSS APPLY fn_validate_plan_guide(plan_guide_id);
GO

--Execution after the plan guide is created will have a merge join in the second batch
EXEC msdb.dbo.test
GO

EXEC sp_control_plan_guide 'DISABLE', 'test'
GO

--Execution after the plan guide is disabled will go back to having a hash join in the second batch
EXEC msdb.dbo.test
GO

EXEC sp_control_plan_guide 'ENABLE', 'test'
GO

--Execution after the plan guide is re-enabled will go back to having a merge join in the second batch
EXEC msdb.dbo.test
GO

--Clean up
EXEC sp_control_plan_guide 'DROP', 'test'
GO

DROP PROCEDURE test
GO

Is this function giving errors that are false positives or are these just warnings that the plan guide may fail or is it something else I haven't thought of?

I have created a Connect item here with the text above but am yet to have a response.

Yes, this is a limitation of sys.fn_validate_plan_guide that can cause a false negative (not a false positive).

The server attempts to compile just the statement in the plan guide, not the whole batch (stored procedure in this case). The request fails because the temporary table definition is not part of the compilation.

I could not reproduce the same issue with a table variable on:

Microsoft SQL Server 2012 (SP3-CU2) (KB3137746) - 11.0.6523.0 (X64)
    Mar  2 2016 21:29:16
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3  (Build 10586: )

The plan validation and guide application worked correctly.

Workarounds

Requesting an estimated plan for a call to the procedure succeeds, with the resulting batch plan showing the guided plan, with the properties of the statement root node showing the correct PlanGuideDB and PlanGuideName properties in SSMS:

An alternative is to create the temporary table (copied from the procedure definition) on the session that calls sys.fn_validate_plan_guide.

CREATE TABLE #test
(
    ID INT
);

-- Succeeds
SELECT
    PG.plan_guide_id,
    PG.name,
    PG.scope_type_desc,
    PG.hints,
    FVPG.msgnum,
    FVPG.severity,
    FVPG.[state],
    FVPG.[message]
FROM sys.plan_guides AS PG
CROSS APPLY sys.fn_validate_plan_guide(PG.plan_guide_id) AS FVPG;

DROP TABLE #test;

Either of these can be used to validate the negative result from sys.fn_validate_plan_guide when the error refers to a missing #object.


How to change the FROM clause of multiple views at once I have multiple views that are the sources for a collection of scripts I use for loading data from NAV into our DWH, meaning the view collects fields from multiple tables in our NAV database and the loading script just has the view as its source.
The problem with this is that the client identifier on the NAV database is changing every couple of months.

As I obviously don't want to go into every single view and update the source table with the new client identifier, my question is, whether or not it is possible to change the source table of multiple views at once. I am using MS SQL Server 13 btw.

I am not sure if I fully understood your question however if it is about changing source table of selected views or all views, you can do the same using SSMS(SQL Server Management Studio).

You can generate scripts from database as below:

In the screen, either you can select all view or select the one that you need as below:

You can save them to a new query window:

Alternatively you can save this as a SQL file. Now, using simple text editor, you can do replace all from existing table name to new table name and either you can run them directly in the new query window or can execute them from SQL file. This file can be handy whenever there is change in table name, you can just find and replace table and would work for you.

One great point made by Aaron Bertrand, if you create another view(only one) wherein you just include the definition as :

create view NotChangingName
as
Select * from changingName

You don't need to change each view, just one change will be sufficient.

Hope this helps.


Dealing with identity ranges for transactional replication I've noticed that when you set up a transactional replication, SQL Server will set identity range management to manual.  What this means is that in my subscription database, when I try to insert a new record into a table whose PK is an identity column, it will give me an error and say that it tried to insert a PK of "1", "2", "3", etc.  This is because the current identity value for all identity columns on the subscriber gets reset to the seed value (usually 1) instead of staying at what it was on the publisher.

I understand why SQL Server does this - you're supposed to leave the subscriber table as read-only.  However, my scenario is a little unorthodox - I update my subscriber from time to time through replication, make an immediate backup of that DB, then I want to do some updates to the subscriber that WON'T be pushed back to the publisher, then when I go to update the subscriber again, I restore its database from the earlier backup and pull the latest updates.  Because I want to do updates to the subscriber in between these updates ('temporary deltas' if you will), I need the identity column to work and not to reset to 1 when replicated.

I tried turning on automatic identity range management when setting up my publication, but that just gives me the following error when I try to add a table to the publication:

  Msg 21231, Level 16, State 1, Procedure sp_MSrepl_addarticle, Line 2243
  Automatic identity range support is useful only for publications that allow updating subscribers.

Is there any way I can get round this problem?  I do kind of want to present this replication to SQL Server as if it were read-only at the subscriber end because I don't plan on making updates that will be pushed back to the publisher, but I do want to make temporary updates that will be erased before the next replication.

I have also considered that snapshot replication might be a more appropriate method than transactional replication for my usage pattern, but the trouble is that snapshot replication requires sending the whole darn DB every single update; because I'm planning on taking an immediate backup of the DB after the latest replication, I shouldn't need to do that whole transfer every time; just the changes since last time.

Assuming your Publisher is using an int identity that begins at 1, you could issue  DBCC CHECKIDENT('dbo.mytable', RESEED, -2147483648)
at the subscriber.  You can then use the range from -2147483648 to 0 to hold your "temporary deltas".


Postgres could not receive data from client: Connection timed out Am a sql DBA, learning postgres..In postgres logs i often receive "could not receive data from client: Connection timed out" I do not have any other logs other than this

Am not sure how to troubleshoot? I check the application logs and DBs logs to compare the time but I could not notice any abnormalities.

can someone guide me how to trace this issue.

Thanks

A few possibilities:

Your application closes connections without telling the PostgreSQL server it's doing so, by simply closing the network socket. It should send a PQclose(conn) if using libpq, or whatever equivalent your language/driver has.
Network address translation timeouts on idle connections or other related network issues cause the TCP connections to break, so client and server lose communication with each other.
A firewall on the client or server is blocking or disrupting connections after they're established (maybe malfunctioning IDS?)


Mysqld can`t start after I changed the socket path here is the log

131229 14:55:27 [ERROR] Can't start server : Bind on unix socket: Permission denied
131229 14:55:27 [ERROR] Do you already have another mysqld server running on socket: /tmp/mysql.sock ?

and what i had down is this in my.cnf:

[mysqld]
socket =/tmp/mysql.sock

what to put the sock file in here and tmp

drwxrwxrwt.  15 root root  4096 Dec 29 14:55 tmp

But I can start it anymore, if I remove that /tmp/mysql.sock. every think is ok.
why?
why tmp got a 777 permission but it still got this error?
thinks in advance.

When everything you can think of to check seems right, you have to think of the things you can't think of. :)

Like possibly "apparmor."

https://askubuntu.com/questions/234668/cant-start-mysql-server-if-the-sock-file-is-changed-in-etc-mysql-my-cnf

https://wiki.ubuntu.com/AppArmor

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


Why MongoDB needs to disable majority read concern for PSA architecture? We have MongoDB 3.6.9 replica set, contains 3 data nodes and 2 arbiters.

I noticed warning in mongodb console:

2020-01-20T11:29:04.057+0000 I REPL     [replexec-0] ** WARNING: This replica set uses arbiters, but readConcern:majority is enabled
2020-01-20T11:29:04.057+0000 I REPL     [replexec-0] **          for this node. This is not a recommended configuration. Please see
2020-01-20T11:29:04.057+0000 I REPL     [replexec-0] **          https://dochub.mongodb.org/core/psa-disable-rc-majority-3.6

I studied the material from the link https://dochub.mongodb.org/core/psa-disable-rc-majority-3.6, but I did not understand why it needs to disable majority RC, and what will be better: remove arbiters or disable majority RC.

Arbiter nodes don't "replicate" data...

The problem with your setup is that, that you can "lose" two of your three data-bearing nodes and your setup has still "majority" in votes. Meaning that replica set still keeps "serving" clients, but all data is only at the one node without replication.

Then comes that "majority RC", with that setup, even you lose one of those data-bearing nodes, the whole replica set stops accepting data (basically) because of missing ack messages. If three nodes of five are data-bearing nodes, that three IS majority of nodes and "majority RC" required that all those three nodes answer "ack, data is safely saved to the storage".


MS Access 2010 adds unwanted wildcard at end of select clause MS Access 2010 has a vey weird behaviour for me. Whenever I create a new Query, it silently adds a wildcard (i.e. ', *') to the end of the SELECT clause.

The wildcard is visible only in the SQL and Data views, but not in the Query Structure view.

For example, I start a new query via 'Create > Query Structure', add a table, add one column, and the SQL result is:

SELECT TABLE1.COLUMN1, *
FROM TABLE1;

What can I do to prevent that unwanted trailing wildcard?

You apparently have the "Output all fields" option enabled for query design. To turn it off,

choose "Options" from the "File" tab of the ribbon,
on the "Object Designers" tab, remove the checkmark beside "Output all fields" in the "Query design" section.


MySQL: selecting arbitrarily ordered rows after a specific id Please excuse the awkward wording of my title, I'm not quite sure how to phrase what I'm asking.

Suppose I have a table like this:

id  value
1   10
2   40
3   20
4   50
5   40

Running this query:

SELECT * FROM table ORDER BY value DESC, id DESC

Results in:

id  value
4   50
5   40
2   40
3   20
1   10

And this query:

SELECT * FROM table ORDER BY value DESC, id DESC LIMIT 2

Results in:

id  value
4   50
5   40

How do I select the next two rows? i.e. the two rows that come after id=5 when you sort by descending value without using an offset (LIMIT 2,2)

id  value
2   40
3   20

If you are fine about using variables and double sorting:

SELECT
  id,
  value
FROM (
  SELECT
    id,
    value,
    @check AS chk,
    @check := IF(id = 5, 1, @check)
  FROM t, (SELECT @check := 0) x
  ORDER BY
    value DESC,
    id DESC
) s
WHERE
  chk = 1
ORDER BY
  value DESC,
  id DESC
LIMIT 2
;

Here's a SQL Fiddle for it.


Is moving large columns to separate table likely to have significant performance improvement? We have a large table that registers Request and Response, and captures other data that is related to that request. We are looking to improving performance. One of the suggestions is to move these larger columns (request and response) to separate tables so they would only be accessed when they actually need to be read and not every time when a record is being looked up.

Is moving large columns to a separate table likely to yield a significant performance improvement?

It is possible that these large string types are not held beside the other columns currently. See here. A forwarding pointer is held instead and dereferenced as required.

It may still be worth splitting out the columns, however, if

the key is smaller than the forwarding pointer; overall space saving, removes the pointer bytes from the row data.
you ever do select * 1; the large fields will not be pulled off disk
the columns are sparsely populated, written to on a different cadence than the other columns, or would benefit from a different indexing strategy.

Conversely  if the values are not too long (say a few hundred bytes) and accessed in most queries you may see a performance improvement by storing them in-row and avoiding the pointer lookup and secondary IO. This can be controlled through sp_tableoption.

1 Not that you ever would, of course.


What impact do indexes have on the performance of table modifications? I have always wondered what the effect indexes have on table modification in SQL Server. Will modification of a table will be slower with an increased number of indexes applied to that table?

My gut feeling says that this will induce overhead of re-indexing but I could be wrong.

As @mrdenny said, every index will need to be changed with INSERT/UPDATE/DELETE operations.

If you are weary about an index is actually be utilized to its fully capacity, and that it's not just a performance degrade, you should query the sys.dm_db_index_usage_stats.

Here are some important fields that are returned:

user_seeks
bigint
Number of seeks by user queries.
user_scans
bigint
Number of scans by user queries.
user_lookups
bigint
Number of bookmark lookups by user queries.
user_updates
bigint
Number of updates by user queries.
last_user_seek
datetime
Time of last user seek
last_user_scan
datetime
Time of last user scan.
last_user_lookup
datetime
Time of last user lookup.
last_user_update
datetime
Time of last user update.


Just JOIN on a subquery:

SELECT *
FROM MyTable T
INNER JOIN (SELECT OrderID, MAX(ProductID) as ProductID
            FROM MyTable
            GROUP BY OrderID) x
  ON x.orderid = t.orderid
  AND x.productid = t.productid


Oracle 11.2.0.4: Basic info using RMAN for backups I'm fairly new to databases. If I plan to use RMAN in archivelog mode to perform full backups each week plus incremental backups (retaining only the last 2 full backups for redundancy), how must I address the archive log files? I plan to use Oracle EM to create/manage RMAN processes, since it seems simplest.

I believe RMAN uses the archive log files to re-create changes to the database during and after the full backup occurs.

QUESTION 1

So, how far back do I need to keep the archive log files?

QUESTION 2

Does the database automatically remove the unneeded log files, or is this something I must address using cron jobs, etc.?

For example, if I do a full RMAN backup today at 1pm, can I delete all archive log files saved before 1pm? Or, will Oracle do that for me since it knows I'm doing a full backup? Or, something else?

QUESTION 3

To complete my understanding of archive logs, do I even need to put the database in archivelog mode to fully restore the database to the point in time of the RMAN backup, if I'm willing to accept loss of data from that time forward?

QUESTION 4

When RMAN finishes, and I want to copy all required backup/restore files to another server, which files need to get copied? The RMAN backup files have .bkp extension, and they need to get copied. The archive log files have .dbf extension, and they should all get copied. Anything else? (control files?)

QUESTION 5

Not sure if anyone can help answer this, but I do a lot of scientific work that uses all available digits in a binary_double number. If I choose compression for backups, will I lose accuracy when uncompressing the data? Or, is the compression algorithm going to restore the exact binary number (assuming the same operating system, etc.)?

QUESTION 1
So, how far back do I need to keep the archive log files?

You need to keep archive logs until you take another full backup.
If you take full backup weekly then you need to keep archive logs for week.

QUESTION 2
Does the database automatically remove the unneeded log files, or is this something I must address using cron jobs, etc.?

If you are using Fast Recovery Area for your backup and recovery purpose then Oracle will take care of it. Otherwise you need to delete it manually using RMAN commands.
For more:Configuring the Fast Recovery Area

QUESTION 3
To complete my understanding of archive logs, do I even need to put the database in archivelog mode to fully restore the database to the point in time of the RMAN backup, if I'm willing to accept loss of data from that time forward?

If you need to perform point time recovery then you need to keep archive logs, thus you are putting your database in archivelog mode. If not simply take cold backup and restore it you can not recover it since you don't have required archived logs.

QUESTION 4
When RMAN finishes, and I want to copy all required backup/restore files to another server, which files need to get copied?

You need to have control file and spfile backup which will be created automatically you have configured control file auto backup in RMAN and backup files to restore it into new host.

QUESTION 5
Not sure if anyone can help answer this, but I do a lot of scientific work that uses all available digits in a  binary_double number. If I choose compression for backups, will I lose accuracy when uncompressing the data?

I have never heard or read about inaccuracy in RMAN decompression. The only disadvantage that I came across is time taken to restore the compressed RMAN backup.


Access Linked Tables and Their Network Connections I need to know some simplistic details about how Microsoft Access' linked tables work. I think I know the answer, and this question is more about asking for a confirmation than anything else. A pointer to documentation would be appreciated, as well.

I am testing an Access ancillary application that pulls its data from a crime statistics Informix IDS database. The Access database contains several linked tables to the Informix database, and, using a multi-table query, updates a native Access table with information.

I am assuming if the network link to the Informix database breaks even for an instant, the linked tables' links also break, and the application must be reloaded. Is that assumption correct? I saw this happen when I let the application run overnight. The links were broken, and I believe the network link burped overnight.

The reason I'm asking this, is I have to build in a re-load mechanism into the Access form, so that the user does not have to close the application and re-open it.

Your Access form would need to delete the linked tables, and recreate them in order to continue to have access to the remote table.

An interruption of the network connection would certainly cause the symptoms you report.  Having said that, you likely shouldn't be having network issues.  I'd try to find the cause of the issue, as well as building in the ability to deal with the problem in Access.


Postgres query to return JSON object keys as array Is it possible to return a JSON object keys as an array of values in PostgreSQL?

In JavaScript, this would simply be Object.keys(obj), which returns an array of strings.

For example, if I have a table like this:

tbl_items
---------
id bigserial NOT NULL
obj json NOT NULL

And if there's a row like this:

id      obj
-----   -------------------------
123     '{"foo":1,"bar":2}'

How can I have a query to return:

id      keys
-----   ------------------
123     '{"foo","bar"}'

Sure, with json_object_keys(). This returns a set - unlike the JavaScript function Object.keys(obj) you are referring to, which returns an array. Feed the set to an ARRAY constructor to transform it:

SELECT id, ARRAY(SELECT json_object_keys(obj)) AS keys
FROM   tbl_items;

Or use jsonb_object_keys() for jsonb.

This returns an array of keys per row (not for the whole table).

A more verbose form would be to spell out a LATERAL join instead of the correlated subquery:

SELECT t.id, k.keys
FROM   tbl_items t
LEFT   JOIN LATERAL (SELECT ARRAY(SELECT * FROM json_object_keys(t.obj)) AS keys) k ON true;


Availability Groups Failover Readinness "No data loss" and rollback of nonqualified transactions I have Availability Groups with Synchronous Replication and Automatic Failover setup with SQL Server 2016 SP2 in my production environment.

I want to do a manual failover for patching and I have done this in my dev environment several times. Right now my AG Dashboard shows, Failover Readiness "No Data Loss".

However whenever I do a failover, I still get notifications (a manually setup alert) from SQL Server telling me there were nonqualified transactions rolled back on the server, just like I did when I had Mirroring.

So, is this truly no data loss? It doesn't sound like it. And what exactly are nonqualified transactions vs. just transactions?

This doesn't count as data loss in this context.

From the documentation (emphasis mine):

  A manual failover causes a synchronized secondary replica to
  transition to the primary role after a database administrator issues a
  manual-failover command on the server instance that hosts the target
  secondary replica. To support manual failover, the secondary replica
  and the current primary replica must both be configured for
  synchronous-commit mode, if any. Every secondary database on the
  availability replica must be joined to the availability group and
  synchronized with its corresponding primary database (that is, the
  secondary replica must be synchronized). This guarantees that every
  transaction that was committed on a former primary database has also
  been committed on the new primary database. Therefore, the new primary
  databases are identical to the old primary databases.

Any transaction in progress while the failover occurs is going to be rolled back and needs to be handled with re-try logic on the application side. You can't have a transaction start on one node, trigger a failover, and then have it finish running on a secondary node - though that would be pretty cool, admittedly.

The synchronous replication just guarantees both nodes are in the same state before/after the failover, otherwise the former primary node could have writes that the now-primary node does not, causing inconsistent data betwixt the two.

Check out the very good documentation for more in-depth information about what really goes on, and what you're actually getting out of the AG feature set.


How to handle big data update based on excel spreadsheet Scenario

Select-based data is exported from DB to Excel spreadsheets.
Some manual work is done on the excel files (filling missing data).
Changed data from excel files is updated back to DB.

Environment

Oracle DB
Original table is about 7 million rows
Can't use import tools in SQL Developer etc, must be a plain SQL script.
There are several excel files with couple hundred of thousand rows each (400-700 thousand)

Current solution

There is an UPDATE statement generated for each row of the excel file. It checks if data has been changed (the file has separate columns for old and new values).
So from each file we get hundreds of thousands of UPDATE statements.

Question is...

This solution is fairly simple, but I wonder if there is a better way to do that?

A Fast Method

A probably faster way to do this is the following:

Provide the data in a database table
Execute a MERGE statement that merges the data from this table into the source table

To provide the data in a database table you can use sql loader or [external tables].

http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606

create table big_tab(
  id number primary key,
  value1 number,
  value2 number,
  value3 number)
;

create table mod_tab(
  id number,
  value1 number,
  value2 number,
  value3 number)
;

Now assume you have the following data in big_tab.

SQL> select * from big_tab;

    ID     VALUE1     VALUE2     VALUE3

     1         11         21         31
     2         12         22         32
     3         13         23         33
     4         14         24         34
     5         15         25         35

you have a csv file mod_data.csv with the following values

1,111,121,131
3,113,123,133

you can load this into the table with sqlldr load.ctl DIRECT=true with the sql loader control file sql.ldr containing

LOAD DATA
INFILE "mod_data.csv"
TRUNCATE
INTO TABLE mod_tab
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  id char,
  value1 char,
  value2 char,
  value3 char
)

Using the option "DIRECT=true" will be faster thean loading without this option.
Now you have

SQL> select * from mod_tab;

        ID     VALUE1     VALUE2     VALUE3
---------- ---------- ---------- ----------
         1        111        121        131
         3        113        123        133

you can merge this into big_tab with the following statement

MERGE INTO big_tab
using mod_tab on (big_tab.id=mod_tab.id)
when matched then update
  set
    value1=mod_tab.value1,
    value2=mod_tab.value2,
    value3=mod_tab.value3
;

and get

SQL> select * from big_tab;

        ID     VALUE1     VALUE2     VALUE3
---------- ---------- ---------- ----------
         1        111        121        131
         2         12         22         32
         3        113        123        133
         4         14         24         34
         5         15         25         35

If the processing was ok you can cleanup

    truncate table mod_tab;

An Even Faster Method

If you have a lot of data to modify, there is a way that is even better (at least if your io system is fast):

create a auxiliary table with the same structure as big_tab table
insert the modify rows in this auxiliary table
insert the rows from big_table that will not be modified in the auxiliary table
drop the big_tab table
rename the auxiliary table to big_tab.
add constraints and indexes to the new big_tab table

This can be don by the following script

create table aux_tab as select * from big_tab where 1=0;
alter table mod_tab add  primary key (id);
insert /*+ APPEND */ into aux_tab select  big_tab.id id,
    decode(mod_tab.id,null,big_tab.value1,mod_tab.value1) value1,
    decode(mod_tab.id,null,big_tab.value2,mod_tab.value2) value1,
    decode(mod_tab.id,null,big_tab.value3,mod_tab.value3) value3
  from big_tab, mod_tab
  where big_tab.id=mod_tab.id(+)
;
drop table big_tab;
rename aux_tab to big_tab;
alter table big_tab add  primary key (id);
alter table mod_tab drop  primary key;
truncate table mod_tab;

And Even Faster

You should always do your load and insert in direct path mod. Therefore I added and APPEND hint to the insert statement and the DIRECT=true option to the SQL*Loader command. But you can try to parallelize some actions to get even faster. Parallel statement work optimal on partitioned tables so it may make sense to partition big_tab and also mod_tab and aux_tab.


Is it possible to do a datapump import with a different user than did the export? I have one db dump below is the log file for that dump

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** PARALLEL=1 SCHEMAS=tmsmv DUMPFILE=tmsmv-20120706.dmp LOGFILE=tmsmv-20120706.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 146.1 MB
...........

I want to restore this dump using another user tmsmv using below command:

sql>conn / as sysdba
CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/home/..../';
GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO tmsmv;
GRANT READ, WRITE ON DIRECTORY SYS.DUMP_DIR TO EXP_FULL_DATABASE;
GRANT READ, WRITE ON DIRECTORY SYS.DUMP_DIR TO IMP_FULL_DATABASE;

And then importing with:

impdp tmsmv/***** schemas=TMSMV directory=DUMP_DIR dumpfile=tmsmv-20120706.dmp

While restoring I am facing below error message:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

Please help me to solve this issue.

It looks like Oracle can't read the .dmp file, because it's under another user's home directory. Oracle has to be able to read it, not the Linux user you're running the impdp command as. (Contrary to my earlier misleading comments!)

Assuming the two users are in different groups, you may need to make the .dmp file world-readable:

chmod o+r /home/..../tmsmv-20120706.dmp

And usually all directories in the path need to be world-readable and world-executable:

chmod o+rx /home/..../

If you wanted the log file to be created, it would be need to be writable too (chmod o+rwx). If it's in a subdirectory of your home directory, you might need to do this to all intermediate levels.

But relaxing permissions on your home directory can be a problem, so you might be better off using a directory somewhere else. Or leave the file where expdp put it, and grant permissions on that DIRECTORY object to tmsmv instead.


How to calculate column (integer) changes in hourly buckets? I have following table:

id serial pk
created_at timestamp with time zone not null
user_id integer not null
points integer not null

For a given time period (min_date and max_date, both timestamp with time zone) it should return list of total (without partitioning by user_id) points changes within this period, partitioned by hour. Point change is a difference of points for a user since previous record for the same user.

For example:

created_at  | user_id | points
10:00       | 1       | 100
10:30       | 2       | 50
11:00       | 2       | 75
12:00       | 1       | 130
12:30       | 2       | 80
13:00       | 3       | 20

Without min/max_date it should return:

hour | points |
10   | 100 + 50 = 150
11   | 75 - 50 (from previous points for user 2) = 25
12   | 130 - 100 (from previous points for user 1) + (80 - 75 (previous for user 2)) = 30 + 5 = 35
13   | 20

With min/max_date it should return:

min_date=11:00
max_date=12:00

hour | points |
11   | 75 - 50 (from previous points for user 2) = 25
12   | 130 - 100 (from previous points for user 1) + (80 - 75 (previous for user 2)) = 30 + 5 = 35

So basically it should return sum of user point changes for each hour bucket since previous (outside of current bucket) row entry.

Note if there are more than one rows for same user in same hour bucket, then the latest should be used in calculation. Also if there will be more than one rows for same user in same bucket hour with same created_at then maximum value of "points" for given user in given bucket should be used in calculation.

Actually "points" for given user will ALWAYS grow (or will be the same as previous) so it is possible to just use max(points) for user in given bucket for calculation.

In examples above I simplified "created_at" field to just hour, but in reality it's a timestamp with time zone and it should be grouped by extract(year from created_at), extract(day from created_at), ....

So far I have:

with stats_buckets as (
  select
    extract(year from stats.created_at) as year,
    extract(month from stats.created_at) as month,
    extract(day from stats.created_at) as day,
    extract(hour from stats.created_at) as hour,
    max(stats.points), -- use max points value inside time-user bucket since points always grow (dont have to use latest entry for given user)
    stats.user_id
  from points as stats
  group by year, month, day, stats.user_id
)
select * from stats_buckets

It just groups records by year, month, day, hour, user_id and selects max(points) from each bucket. Now for each bucket I have to calculate difference between selected max(points) and previous points for given user outside of the bucket. And then group results by year, month, day, hour.

I tried to do this with windowing function but without any luck so far.

Also one more important thing: there might be a LOT of rows in points table so calculating the differences for ALL of the buckets might be too slow. However min_date, max_date will ALWAYS be used to prevent calculating ALL buckets. The problem is that it's not possible to filter out point records initially by min/max_date, since for each row, the previous one (from which we calculate the difference) might be outside of min/max_date range.

This query is for all the buckets.

with points_enhanced as( -- Add date_id for grouping and ordering, hour for displaying
  select to_char(created_at, 'YYYYMMDDHH24') date_id, extract (hour from created_at) as hour, * from public.points
)
, points_with_lag as ( -- Calcuate lag point by user_id
  select date_id, user_id, hour, points,
    lag(points, 1, 0 ) over w points_before
  from points_enhanced pe
  window w as (partition by user_id order by date_id)
)
, cte_buckets as ( -- What if we have more than one record by bucket and user_id ? Use only max(points) and min(points_before)
  select date_id, hour, user_id, max(points)  - min(points_before) points_bucket
  from points_with_lag
  group by date_id, hour, user_id
)

select date_id, hour, sum (points_bucket) points
from cte_buckets
group by date_id, hour
order by date_id

This query is for min_date / max_date filter.
Adjust the dates to your needs.

with points_enhanced as( -- Add date_id for grouping and ordering, hour for displaying
  select to_char(created_at, 'YYYYMMDDHH24') date_id, extract (hour from created_at) as hour, * from public.points
  where created_at >= '2017-05-30 12:00:00' and created_at < '2017-05-30 14:00:00' -- filter by min_date/max_date
)
, points_with_lag as ( -- Calcuate lag point by user_id
  select date_id, user_id, hour, points,
    lag(points, 1,
                 ( select coalesce(max(points), 0) from public.points p -- This correlated subquery gets the lag point value, or 0, for that user_id before the min_date limit
                   where created_at < '2017-05-30 12:00:00'
                   and p.user_id = pe.user_id
                 )
    ) over w points_before
  from points_enhanced pe
  window w as (partition by user_id order by date_id)
)
, cte_buckets as ( -- What if we have more than one record by bucket and user_id ? Use only max(points) and min(points_before)
  select date_id, hour, user_id, max(points)  - min(points_before) points_bucket
  from points_with_lag
  group by date_id, hour, user_id
)

select date_id, hour, sum (points_bucket) points
from cte_buckets
group by date_id, hour
order by date_id

Regards.


Should Amazon RDS SQL Server 2017 Web Edition Max Memory settings be different from that of a locally installed instance? We have an Amazon RDS SQL Server Web Edition on Windows Server 2012 with 2 processors and 8GB RAM.
Should I configure the Max Memory similar to that of an instance on a local server i.e keeping back 3-4GB for OS, memory pool etc. or does RDS require a different calculation?

No, I wouldn't treat this any differently than you would an on-prem instance with regards to max server memory.  The Windows Server 2012 box is almost certainly a VM, but hopefully Amazon is a managing that well to avoid VM problems.

I would definitely reserve 3 GB at minimum for the OS and other overhead.


RMAN-06054: media recovery requesting unknown archived log, Where is SCN kept? I have the following situation:

Backups of DB1(located on srv1 server) database are taken everyday at 1:00 AM.
I took backed up files(datafile,archivelog,controlfile,spfile) of 24/10/2011 and restored them on the server srv2.
During recovering Oracle required log file:

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 228 and starting SCN of 26651733
I searched that logfile on the server srv1 and copied it to the server srv2. Registered it with the database and run recover database command again. Still the same error, but with other sequence number and SCN.

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 229 and starting SCN of 26654944

These archivelogs are generated after 24/10/2011 so backup of this date should not know about newer archivelogs. I can set recovery until that sequence but I just wonder to know where is that SCN stored? Why does my Oracle requires newer archivelogs?

Server srv1 and srv2 are not contacting with each other.

Thank you a lot.

Each redo log file (and archived redo log file) contains starting SCN and ending SCN. In case it is a last redo, the ending SCN is 0xffffffffffff.

nap01:~/oradata/jt10g$ strings redo01.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000004, SCN 0x0000000b05b5-0x0000000bd34f

nap01:~/oradata/jt10g$ strings redo02.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000005, SCN 0x0000000bd34f-0x0000000bf612

nap01:~/oradata/jt10g$ strings redo03.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000006, SCN 0x0000000bf612-0xffffffffffff

Until database sees 0xffffffffffff it keeps asking for further logs. But it is not a problem, you can specify UNTIL SCN or UNTIL CANCEL for your recovery (this means you don't want a complete recovery, in other words you want to lose some recent data).


Percona / MySQL audit log plugin - limiting the content? I've been looking to experiment with Perconas Audit Plugin, as it sounds like it could be potentially very useful.
However it appears that it is a very blunt (ALL or NOTHING) tool.

From my perspective, I am simply interested in DDL statements (CREATE, ALTER, DROP etc).

Is there an way to limit what it logs, or is it really just ALL or NOTHING.

I fear I already know the answer, as I can't see any mention in the documentation.

OK, from what I can tell, Percona Audit Log Plugin, has very little in the way of configuration.

I looked at McAfee Audit Plugin, but that doesn't seem to work on Debian MySQL.

I also looked at MariaDB Audit Plugin, and that seems to have some level of filtering (DDL or DML).
Unfortunately it requires 5.5.42 , and most of my DBs are 5.5.40. Still, on my test system it seems to work.


Downgrading from SQL Server 2008 to 2005 Database files that are built using SQL 2008 are not compatable with 2005. Is there a work around?

You could BCP the data from one SQL Server instance to another instance. That would be the fastest way to copy the data from one version to another. Depending on the volume of data, it might take a long time.


SSIS - Data Import & export wizard - existing tables For a project I'm attempting to move data from a view in source to a table in destination. And this will need to be done periodically.
The quickest way to do this seems to be a SSIS package (as this also grants me additional control for possible data transformations in a following phase.

Now, the first time this works excellent, I go through the wizard and the destination table(s) are created and the data imjected.
However, when I run the same package a 2nd time SSIS complains that the tables already exist (which is right).

The only way I see how to rectify this is to change the source queries and add an ifexists clause. This though will become a lot of work as I will need to do this for 144 destination tables.

Hence my question, is the if exists clause the only way to capture this or is there an option within the wizard/SSIS to have the package check this for me and only create the table if it doesn't exist yet?

The Import/Export wizard generally makes pretty basic 2-step packages.  For instance, I quickly made one that migrates two tables from databaseA over to databaseB, and the Control Flow of the generated package looks as follows:

The CREATE TABLE statements are all performed together in the Preparation SQL Task 1 which means it should be the only place you need to make a change. In support of the advice provided by @RobertCarnegie in the comments, I also recommend putting your table creation/truncation logic within a stored procedure which will be called from the Preparation SQL Task 1 step in the package.

Basically, after this change the Execute Script Task will look as follows:

Taking this approach allows the Data Flow tasks to remain unchanged, and now you should be able to reuse the SSIS package without much more work.

If you're finding it annoying to create a stored procedure that controls the creation/truncation of 144 separate tables, there are various tricks you can employ.  If you're not opposed to using the UI, you can multi-click items in the Object Explorer Details window and script out actions, so for instance if I want to generate out a bunch of drop/create scripts for a lot of tables, it's as easy as clicking on the table folder for a given database, multi-selecting the tables I want to drop/recreate in the Object Explorer Details window, and then right-click on them and select the option I want:

You will need to remove the GO statements if you want to nest this into a SP, but you can hopefully see that it's not nearly as difficult to bulk-generate scripts with this approach.

If you want to generate TRUNCATE statements (which will be easier to do), script out DROP table statements and then do a search/replace on DROP to replace it with TRUNCATE.


Is there a limit to how many credentials SSMS can remember? Using SQL Server Management Studio that came with SQL Server 2008 R2, I make connections to the same server using a number of different sets of SQL Server Authentication credentials. On my development machine, I use the Remember password checkbox extensively to avoid having to look up the password each time.

When I choose a Login which I have previously connected with (using Remember password), the Password entry field gets populated with asterisks, and clicking Connect allows me in - most of the time. But sometimes, with no pattern that I can discern, on choosing a previously-used Login, the Password field will prepopulate with asterisks, but clicking Connect tells me the login fails. I enter the password, click Connect, and get in, and then typically the next time I use the same login it will work.

My suspicion is that there is a maximum number of credentials that SSMS is prepared to remember, and only the most-recently-used n credentials are actually remembered, but I can't find any mention of such behaviour in the documentation.

Is there a known limit on how many credentials SSMS will remember?

It is still a bug (sql server 2008 - 2014 (version 12.0.2000.8 still affected)) as highlighted below :

SQL Server 2012 Management Studio spuriously forgets password, despite being set to remember it.

You can vote up the connect item to get it HOPEFULLY fixed if you are experiencing the bug as well.

%UserProfile%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

might have a limit but I don't know of any kind of hard limit.

BTW, how big is your sqlstudio.bin file now ?


'user' is not valid at this position, expecting an identifier CREATE TABLE 'users' (
'id' int(11) unsigned NOT NULL AUTO_INCREMENT,
'name' varchar(75) NOT NULL,
'password' varchar(100) NOT NULL,
'email' varchar(100) NOT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY 'email' ('email')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

You shouldn't use quotes for the names of objects / entities, that stringifies it as a value instead. This would be the correct syntax you're looking for:
CREATE TABLE users
(
    id int(11) unsigned NOT NULL AUTO_INCREMENT,
    name varchar(75) NOT NULL,
    password varchar(100) NOT NULL,
    email varchar(100) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;


how to find vertica community edition and how to export / copy data I have the hardest time downloading anything other than enterprise edition from vertica.com. It is supposed to have a community edition on https://my.vertica.com/community/ but all the links just show the enterprise editions for download.

I have an older download (forgot how I got to that earlier) vertica-ce-6.0.1-0.x86_64.RHEL5.rpm which may be the latest, not sure, enterprise edition latest seems to be 6.1.0 at the time of writing.

I installed that and pushed 12 million rows to it from an older enterprise edition version using CONNECT TO VERTICA and EXPORT TO VERTICA... however when I tried to now push that data out from the community edition to another test installation that also is community edition, psql says:

ERROR 2678:  Command EXPORT is not supported

Googling that brings up nothing, and the my.vertica.com forums also have no match. Am I the first to see it really?

Whats the next efficient way to dump the data from one community edition vertica installation to another if EXPORT TO VERTICA and COPY FROM VERTICA should not be supported in the community edition?

The reason why I am looking at vertica is that it appears to respond much faster than mysql when there are more than a billion rows in the table, i.e. the result set of a query comes in within 30 seconds instead of 5 minutes on identical hardware.

HP Vertica has their community edition for download at https://my.vertica.com/download-community-edition/. If you don't have one already, you will be prompted to create a MyVertica login. If you have problems creating a login, please email me at dsandahl@vertica.com. If you have further issues loading your data, you can tweet at @VerticaHelp and support will follow up with you.


Can i alter a database to single user which is configured in log shipping We are using a production database (SQL Server 2012) and database compatibility mode is 90 (SQL Server 2005).

Can I alter the database to single user mode and change to 110?
What will be the impact if run Alter database <DBNAME> SET SINGLE_USER with rollback immediate
After changing the compatibility level and switching to multi user will Log shipping work?

There's several parts to this question:

Do I need to set the database to single user mode to change the compatibility level? No.

Will changing the compat mode from 90 to 110 break log shipping? No.

Can I change a log shipped database to single user mode? Yes, but don't do that unless you have to. Sometimes, you might not end up being the single user (like if someone else grabs the connection from you, like if your SSMS crashes after you set it to single user mode.)


SSRS Report dependencies Is there an automatic way to track reports and their dependencies for SSRS? What I want is a map/tree where I can see whether a report depends on a other report or subreport.

So far we did this by hand using Visual Studio 2008 checking all the 'actions' in text field properties, image field properties etc. for generating such a report tree

Someone advised me to query the catalog table in the reportdatabase. However I can't see a possibility there to see which report depends on an other report. Maybe I'm doing it wrong?

What I want is a map like:

Report A refers to/is used by Report B and C

Report B refers to/is used by D and F

Report F refers to/is used by Report G

...

Any advice?

I don't have 2008 to look at but do you have access to the RDL files that were published to the server?  I can't recall if those are plain old XML files, but I think if you have those, it's probably a convenient way - you could parse them with Linq to XML, or XSLT.

The schema is documented at:

http://msdn.microsoft.com/library/dd297486%28SQL.100%29.aspx


How to move geographical data from MS Access to SQL Server For 20 years I've been working with MS Access, with much VBA involved.
I have a lot of geographical data in Access and I'm preparing to move to SQL Server because it supports geographical data types, and because it has a good connection with QGIS, in which I want to show and query my geographical data efficiently in a map.

Basically, my geographical data in Access comprise my GPS tracks that I've collected in 20 years, altogether about 5000 tracks with an average 800 points per track. In Access I have two tables for them:

[tbl Track] with metadata describing the track (e.g. distance, start date and time, duration, maximum elevation etc.).
[tbl TrackGeo] with the tracks themselves in geographical terms. Attributes:

ID (autonum, PK)

TrackID (long, FK to [tbl Track])

LatWGS84 (double)

LonWGS84 (double)

Moving [tbl Track] to SQL Server is easy, but it gets difficult for me (newbie in SQL Server and its geographical data types!) to move the geograhical data to SQL Server in a spatial data field.

My idea is:
Put the data of [tbl TrackGeo] per track in a geography spatial data field (type linestring) in [tbl Track] in SQL Server.

My question is:
What is the best approach to accomplish this, preferably (if possible) using VBA from within MS Access?

I would use VBA to construct & export your spatial data into the Well Known Text (WKT) format. SQL Server supports importing data from this format & as it is text based it is fairly easy syntactically.


Optimal engine for small lookup tables in MySQL I have following question: I am designing web application with several dozens of small lookup tables: These tables usualy contain three columns (ID, Name, Description) and a couple of rows (mostly fewer than 50, max is about 450).

These lookup tables are expected to change only rarely (they come from standard, which changes once in several years) and will be used only to:

fill options in html select
be joined to other records in reports

There will be only SELECT statements on these tables 99 % of times, but there will be quite a lot of them.

I am wondering, which database engine would be most efficient to use?

Here are my considerations:

  MEMORY

  pro: very fast
  con: if the server crashes, all data are lost and need to be recreated
  con: does not support foreign keys

  compressed MyISAM

  pro: fast
  con: does not support foreign keys

  InnoDB

  pro: foreign key support

What I would like to ask, is whether there will be significant advantage in using something different than InnoDB - performance-wise

Thanks, Zbynek

I answered a similar question in Aug 2011 : Which DBMS is good for super-fast reads and a simple data structure?
Since you are asking about MySQL and which storage engine. To be honest, it is hard to say because there are rare occasions when MyISAM can outperform InnoDB when it comes to SELECTs.
Here are some of my past posts on this controversy

Sep 20, 2011 : Best of MyISAM and InnoDB
May 03, 2012 : Which is faster, InnoDB or MyISAM?
Jul 05, 2012 : InnoDB vs MyISAM with many indexes
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options

You might asking right now : Why would I ever favor MyISAM over InnoDB ?
Take a look at this diagram (created by Vadim Tkachenko, Percona CTO)

MyISAM will cache indexes and your table would have 2 indexes (PRIMARY KEY on id and an index of Name). On the other hand, InnoDB has too many moving parts to accommodate for, especially if the InnoDB Buffer Pool has to load and dismiss 16K pages periodically.
To be fair, you should do an experiment.

Go to my post What are the main differences between InnoDB and MyISAM?. Read it carefully.
Setup a server using MyISAM and all your tables using ROW_FORMAT=Fixed (See my post What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?) and use a large key_buffer_size.
Setup another server using InnoDB and a large innodb_buffer_pool_size.
Add your data to both server and query them like crazy.

This will give you the best assessment for Storage Engine choice for your dataset.
GIVE IT A TRY !!!


Mirror failure after windows updates - possible cause? Over a dozen SQL Server mirrors had windows updates installed this morning - 2 of which developed serious faults afterwards.

The windows updates installed where:

KB2939576
KB2957189
KB2957509
KB2957689
KB2957503

The 2 servers effected are running Windows Server 2008 Standard & SQL Server 2008 R2 (10.50.4000)

The fault consisted of 20% through 60% constant CPU usage on the mirror (even when the principle server was idle at 0%), constant mirror state changes (from disconnected to synchronising & vica versa).

When looking into the PID's in question we noticed the following blocked event against msdb:

WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue)

The "Application" listed against this was "Microsoft Windows Operating System" & the status was "SUSPENDED", this was being blocked by a whole chain of system PID's which all seemed to be notification related (for mirror state changes).

As a hunch we tried disabling the WMI service - this wouldn't stop - its almost as if the windows updates have caused WMI to hang and its taking SQL with it - uninstalling the windows updates didn't solve the problem, nor did dropping of all SQL Server Agent Alerts that where making use of WMI events.

Rebooting the servers made no difference - CPU usage was still excessive for no reason, Mirrors where still alternating between Synchronising & Disconnected - on the rare occasion that they did sync, transactions on the principle suddenly became deadly slow (often causing timeouts in client applications) until the mirror dropped again.

We're currently formatting the mirror, re-installing the OS with R2 in an attempt to solve this asap.

Has anyone had problems with these windows updates? Is there any known issues with WMI notifications causing SQL to hang?

I should probably add that the mirror servers in question had been working perfectly before these windows updates for years, CPU usage never goes above 5% really & the mirrors had been synchronised for months - only drops where when servers where deliberately rebooted.

Edit: When we tried dropping all SQL Server Agent alerts (just in-case they where causing the problem) (They fired an email when a mirrored database state changed via WMI) we got the message below suggesting there was something wrong in the notification system & the queue was not clearing down at the normal rate.

Unable to post notification to SQLServerAgent (reason: The maximum number of pending SQLServerAgent notifications has been exceeded.

Edit 2: We also noticed lots of the following in the SQL ERRORLOG:

Failure to send an event notification instance of type 'DATABASE_MIRRORING_STATE_CHANGE' on conversation handle '{5A821186-76A4-DD11-9F30-00188B481C62}'. Error Code = '8429'.

Edit 3: The chain of PID's that where blocking the WAITFOR command above where nearly all of type "BRKR TASK" - often flagged as Head Blocker & consuming the most CPU - SQL is doing something internally - is there any way to determine what a non user PID is doing?

Edit 4: sp_WhoIsActive shows no rows (which makes sense since the server is idle) - yet with the CPU constantly at 28% with sqlsvr.exe being the only process using CPU we've ran it with @show_system_spids = 1 instead:

We had to bring MS in on this one via a paid support call. After a week of dumps, xperf analysis, driver updates, playig with Virtual infrastructure we ruled out all the basics (or so we thought) - over a week later we found the problem - there was over 500,000 sys.server_event_notifications defined - these all looked as follows:

name    object_id   parent_class    parent_class_desc   parent_id   create_date modify_date service_name    broker_instance creator_sid principal_id
SQLWEP_E0B9EA66_1E73_45B7_B528_75166DC7A15D 337640  100 SERVER  0   2013-06-14 01:56:27.077 2013-06-14 01:56:27.077 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_6492D14E_99D2_49F2_9CD9_43F4B7DC7F63 338711  100 SERVER  0   2013-02-12 17:50:55.897 2013-02-12 17:50:55.897 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_C9871B51_F866_48A6_862E_08EC4F92DAFA 351457  100 SERVER  0   2013-03-06 02:04:18.070 2013-03-06 02:04:18.070 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_0E5D8FAF_1A9C_43FA_8EDF_A5713A8C7825 362061  100 SERVER  0   2014-02-08 04:57:17.217 2014-02-08 04:57:17.217 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_712ED252_E529_4022_9EA3_C785BAEB8147 364203  100 SERVER  0   2013-04-05 15:52:11.190 2013-04-05 15:52:11.190 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_E2D31BC2_E416_4D12_8B4A_88F4183FE021 374807  100 SERVER  0   2014-03-15 13:31:25.390 2014-03-15 13:31:25.390 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_7B389ACB_EA1F_434C_8B2A_7B93D4D0F098 376949  100 SERVER  0   2013-05-12 05:02:12.450 2013-05-12 05:02:12.450 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_4A828CDE_D72D_4277_97C0_030686F86377 378020  100 SERVER  0   2013-01-21 16:49:04.780 2013-01-21 16:49:04.780 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_4EA3C2AD_ACCE_4A66_83E9_0676D2250C34 387553  100 SERVER  0   2014-04-24 20:26:31.053 2014-04-24 20:26:31.053 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_6EA3C0FB_6EDA_4B9D_A3FE_D2B07799212C 389695  100 SERVER  0   2013-06-16 18:07:06.043 2013-06-16 18:07:06.043 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259

These had all been created gradually over 5 years, what they are, where they come from, no one could say & MS couldn't find any related alerts to justify them either. Dropping 1 notification took 4 seconds so we would have been looking at 22 days to drop them all - suffice to say we ended up nuking the SQL instance & re-installing SQL from scratch.


Mongo 3.6 performance issue We have a 3-node Mongodb replica set that resides entirely in AWS. The primary is an i3.2xlarge (8 cores 61G RAM), while the secondary members are i3.xlarge (4 cores 30.5G RAM). These instances are each backed by two SSD EBS volumes, with one of the pair formatted XFS solely for the Mongodb installation. The Mongodb installations use the WiredTiger storage engine. The mongod.conf is here.

We have a roughly weekly ETL process that uses Pentaho to write to our replica set via a Tomcat8-based REST API. This process has completed smoothly for the last two years until our upgrade to Mongodb 3.6 at the end of February. During the first run of this process on Mongodb 3.6, the mongod process ran the instance out of memory. The OS invoked oom-killer to stop the process. After the subsequent election, the process then also killed the new primary.

We’d previously never configured swap space for the instances, but after this incident, swap space was configured on each, on a [RAM x 2] sized AWS NVMe instance store volume. Since implementing the swap space, any intensive process will continue to run the instance down to <1G of memory, then roll over into the swap space. We’re no longer experiencing a mongod service crash, but the system will regularly use up all available memory, and up to half of the swap space. As an example, the system was in such a state yesterday, and while restarting the service freed up the memory and swap space, the nightly dump has left the system using 26/60G of memory. It seems that the service isn’t releasing memory/killing threads once they’re no longer actively in use (see below image)..

Other system details:

Ubuntu 14.04.5
4.4.0-112-generic #135~14.04.1-Ubuntu SMP
Mongodb 3.6.2
transparent_hugepage & defrag are set to “never”
readahead is set to 16K
numactl is installed, allowing NUMA to be disabled via the init script

As per your system details the readahead is set to 16K. But as per MongoDB BOL  for the WiredTiger storage engine. Set the readahead setting to 0 regardless of storage media type (spinning, SSD, etc.).

Setting a higher readahead benefits sequential I/O operations. However, since MongoDB disk access patterns are generally random, setting a higher readahead provides limited benefit or performance degradation. As such, for most workloads, a readahead of 0 provides optimal MongoDB performance.
In general, set the readahead setting to 0 unless testing shows a measurable, repeatable, and reliable benefit in a higher readahead value. MongoDB Professional Support can provide advice and guidance on non-zero readahead configurations.

As MongoDB documentation for the WiredTiger, consider the following recommendations on Linux environment:

Turn off atime for the storage volume containing the database files.
Set the file descriptor limit, -n, and the user process limit
(ulimit), -u, above 20,000, according to the suggestions in the
ulimit reference. A low ulimit will affect MongoDB when under heavy
use and can produce errors and lead to failed connections to MongoDB
processes and loss of service.
Disable Transparent Huge Pages. MongoDB performs better with normal
(4096 bytes) virtual memory pages. see  Transparent Huge Pages Settings.
Disable NUMA in your BIOS. If that is not possible, see MongoDB on NUMA Hardware.


MySQL query to create a Matrix view using pivot I have a table with the following fields:

p1
p2
s1
s2

where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2
The number of players can vary from 3 - 10.
The table have the results between them.
For example let's suppose that we have 4 players:
TIM, ROB, NICK, GEORGE
and the matches between them:

    p1     p2       s1  s2
    TIM    ROB      4   0
    NICK   GEORGE   4   0
    TIM    NICK     0   4
    ROB    GEORGE   4   0
    TIM    GEORGE   4   0
    ROB    NICK     4   0

What I want to produce with the query is something like this:

           TIM   ROB   NICK   GEORGE
    TIM     X    4-0   0-4     4-0
    ROB    0-4    X    4-0     4-0
    NICK   0-4   4-0    X      4-0
    GEORGE 0-4   0-4   0-4      X

Is it possible somehow in MySQL?
Thanks in advance.

Example pivot tracking table

CREATE TABLE IF NOT EXISTS `mail_track` (
`ID` int(10) NOT NULL auto_increment,
 MemberID mediumint(7) default NULL,
`MessageTitle` varchar(20) default NULL,
`Type` varchar(6) default NULL,
`URLID` varchar(15) default NULL,
`DateTime` datetime default NULL,
`IP` varchar(15) default NULL,
PRIMARY KEY  (`ID`),
KEY `DateTime` (`DateTime`,`Type`,`MessageTitle`,`MemberID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=199952834 ;

pivot query

SELECT
SUM( IF( mail_track.Type = 'Open', 1, 0 ) ) AS Opens,
SUM( IF( mail_track.Type = 'Click', 1, 0 ) ) AS Clicks,
SUM( IF( mail_track.Type = 'Sent', 1, 0 ) ) AS Sent,
DATE(DateTime)
FROM mail_track GROUP BY DATE(DateTime)


Can I force a user to use WITH NOLOCK? Can I force a user's queries to always run with the hint NOLOCK? e.g. they type

select * from customer

But what is executed on the server is

select * from customer with (nolock)

This question is not:
About the various pros and cons of NOLOCK, respectfully. I know what they are, this is not the place to discuss them.

The real answer is:

Stop using NOLOCK

OTHER INFORMATION THAT MIGHT BE USEFUL:

Sure, deny direct access to the tables/views, and enforce data access through stored procedures (or views - thanks Martin) - which you can code to use NOLOCK.

If you need it to be per user, and you can enforce data access through procedures, you can use the idea suggested here by SQLMenace:

CREATE PROCEDURE dbo.blah
AS
BEGIN
  SET NOCOUNT ON;

  IF SUSER_SNAME() = N'something'
  BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  END

  SELECT columns FROM dbo.Customer;
END
GO

Or you could also do this:

CREATE PROCEDURE dbo.blah
AS
BEGIN
  SET NOCOUNT ON;

  IF SUSER_SNAME() = N'something'
  BEGIN
    SELECT columns FROM dbo.Customer WITH (NOLOCK);

    RETURN;
  END

  SELECT columns FROM dbo.Customer;
END
GO

If that is not possible, I thought of suggesting a plan guide, but I haven't used them to enforce NOLOCK. Also this document in Books Online suggests that this is reserved for INDEX, FORCESCAN, and FORCESEEK hints. Using a plan guide to try and add NOLOCK if it isn't already present in the query text will lead to an error. Though, the code shown in Example K seems to contradict that, so maybe it is worth a shot after all. Of course, you'll need to set up a plan guide for every query, which may be prohibitive (or impossible), and they're not exactly for the weak of heart.

And I know you don't want to hear the cons of NOLOCK, but it would be much easier (and better all around!) to force everyone to use snapshot.

To address Kenneth's suggestion of using a logon trigger: in my testing this SET option can be specified in a logon trigger, but like setting it in a stored procedure, the setting reverts to the server default when the trigger goes out of scope. So no, there doesn't seem to be a way to set a session's isolation level from a logon trigger.


Do not reject entire statement when deleting rows in batch I have a table who's purpose is to store the application's various images. It is defined this way :

CREATE TABLE images
(
  id lo NOT NULL,
  name character varying(1024) NOT NULL,
  type character varying(32) NOT NULL,
  dimension point,
  last_modified timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT "PK_images" PRIMARY KEY (id)
)

Some other tables have foreign keys referencing images.id with constraints that an image should not be deleted if it is referenced somewhere (i.e. the app is responsible to properly delete the reference first);

...
CONSTRAINT "FK_foo_image" FOREIGN KEY (image_id)
   REFERENCES images (id) MATCH SIMPLE
   ON UPDATE CASCADE ON DELETE RESTRICT
...

Since many table rows may reference the same image, I need to automatically cleanup images that no longer have any reference.

My first idea was to implement the application to delete all foreign key references, then remove all images matching the values of these deleted foreign keys. To do so, I'd create a trigger on the table images, before delete, that could "swallow" all constraint exception if a query such as only "orphaned" images would be deleted and the others would be silently ignored. I'm just not sure how to properly do that.

If that idea is not good, what could be other (if not better) alternatives?

Basically, if I'm executing a query like

DELETE FROM images WHERE id IN (101, 102, 103, 104, 105)

and images.id's 102 and 104 have constraints that prevents them to be deleted, I'd like all other images to still be deleted. The DELETE query should be simple as the one above.

Thank you.

I was able to make this work with the below setup
Process:
1. Loop through the pg_constraint table and understand the constraints defined on the table that has to delete a record
2. Before deleting a record verify no reference record exists in any of the dependent table

Detailed implementation can be found below.
Let me know your thoughts on this.

            #1: DDL

            CREATE TABLE core.images
            (
                id serial NOT NULL,
                name character varying(1024) NOT NULL
                CONSTRAINT "PK_images" PRIMARY KEY (id)
            );

            create table core.app_images
            (
            id serial,
            image_id int,
            CONSTRAINT "FK_foo_image" FOREIGN KEY (image_id)
                 REFERENCES core.images (id) MATCH SIMPLE
                 ON UPDATE CASCADE ON DELETE RESTRICT
            );

            create table core.app_images1
            (
            id serial,
            image_id int,
            CONSTRAINT "FK_foo_image1" FOREIGN KEY (image_id)
                 REFERENCES core.images (id) MATCH SIMPLE
                 ON UPDATE CASCADE ON DELETE RESTRICT
            )

            #2: Funciton to check ref data integrity

            CREATE OR REPLACE FUNCTION core.check_ref_exists (
                p_schema TEXT,
                p_table TEXT,
                p_column TEXT,
                p_value TEXT
            )
            RETURNS BOOLEAN
            LANGUAGE plpgsql
            AS $$
            DECLARE
                r record ;
                v_sql TEXT ;
                v_return BOOLEAN DEFAULT FALSE ;
            BEGIN
                FOR r IN (
                    SELECT
                        pn.nspname s_name,
                        r_tab.relname AS t_name,
                        pa.attname AS c_name
                    FROM
                        pg_constraint pc
                    INNER JOIN pg_class tab ON tab.oid = pc.confrelid
                    INNER JOIN pg_class r_tab ON r_tab.oid = pc.conrelid
                    INNER JOIN pg_namespace pn ON pn.oid = r_tab.relnamespace
                    INNER JOIN pg_namespace pn_drv ON pn_drv.oid = tab.relnamespace
                    INNER JOIN pg_attribute pa ON (
                        pa.attrelid = pc.conrelid
                        AND pa.attnum = ANY (pc.conkey)
                    )
                    INNER JOIN pg_attribute pa_d ON (
                        pa_d.attrelid = pc.confrelid
                        AND pa_d.attnum = ANY (pc.confkey)
                    )
                    WHERE
                        contype = 'f'
                    AND tab.relname = p_table
                    AND pn_drv.nspname = p_schema
                    AND pa_d.attname = p_column
                ) loop

                    v_sql := 'SELECT TRUE FROM ' || p_schema || '.' || p_table || ' s
                        WHERE s. ID = ' || p_value || '
                        AND EXISTS (
                            SELECT 1 FROM ' || r.s_name || '.' || r.t_name || ' d WHERE s.' || p_column || ' = d.' || r.c_name || ')' ; EXECUTE v_sql INTO v_return ;
                    v_return := COALESCE (v_return, FALSE) ;
                    IF v_return = TRUE THEN
                        RETURN v_return ;
                    END IF ;

                END loop ;
                RETURN v_return ;
            END ; $$

            #3: Trigger Function
            CREATE OR REPLACE FUNCTION core.images_tr_func()
            RETURNS TRIGGER AS
            $BODY$
            DECLARE
                v_ref_exists boolean;
            BEGIN
                select core.check_ref_exists (
                    p_schema := 'core',
                    p_table :='images',
                    p_column :='id',
                    p_value := old.id::text
                ) into v_ref_exists;

                if v_ref_exists then
                    return null;
                else
                    return old;
                end if;
            EXCEPTION
                    WHEN OTHERS THEN
                            RAISE EXCEPTION 'SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                            RETURN NULL;
            END;
            $BODY$
            LANGUAGE plpgsql;

            #4: Trigger
            DROP TRIGGER if exists images_tr ON core.images;

            CREATE TRIGGER images_tr BEFORE DELETE
                 ON core.images FOR EACH ROW
                 EXECUTE PROCEDURE core.images_tr_func();

            #5: Sample Data
            insert into  core.images(name) values('image1'),('image2'),('image3'),('image4');
            select * from  core.images;

            insert into core.app_images(image_id) values
            (1),(1),(2)

            select * from core.app_images

            insert into core.app_images1(image_id) values
            (2),(3),(3)

            select * from core.app_images1

            #6: Testing
            delete from core.images where id = 1; --Nothing happened as it is still referred
            delete from core.images where id = 4; --Deleted as it is an orphan record


Mongodb: Does the stats command show me the capped size setting? I am looking around my mongo instance. One of the collections was created as capped:

db.createCollection("userObjects", { capped:true, size:400000000000 })

But when I look at the db.userObjects.stats(), I don't see any flag that indicates it is capped, or the cap size.

Am I missing something? (Is there another place to look?)

If you connect to the proper DB and execute a db..stats() in the Mongo shell you will see "capped" : true third from the bottom of the output just after index sizes.

To figure out a collections cap size, you could use the system namespace.

e.g. db.system.namespaces.find({name : "."}, {"options.size":1})