Enforce a column as unique based on another column value I want to make a column unique, but only if a different column is a specific value.

Consider the following table:

CREATE TABLE [SampleTable]
(
     [Id] INTEGER NOT NULL IDENTITY(1,1)
    ,CONSTRAINT [PK_SampleTable]
        PRIMARY KEY ([Id])

    ,[Code]      NVARCHAR(255) NOT NULL
    ,[Deleted]   BIT           NOT NULL DEFAULT 0
    ,[CreatedOn] DATETIME      NOT NULL DEFAULT GETDATE()
);

The intention is that an item can be 'deleted' by setting the [Deleted] column to 1.

What I want is for the [Code] column to be enforced as unique, but only among non-deleted rows.

Enforcing data correctness at the database level is generally my strong preference. However, I've used this pattern a lot in the past, but have never been sure if it were possible to enforce this kind of constraint at the database level. Deadline pressures being what they are, I never bothered to find out. So I've always just enforced them at the application level.

If there's a way to do it though, I'd really like to know what it is.

To be clear, I can't just use a combined unique constraint, because I need to be able to support the following data:

    [ID]   [Code]   [Deleted]  [CreatedOn]
=====================================================================
     1     'ABC'    1          Ages ago
     2     'ABC'    1          A while ago
     3     'ABC'    1          Quite recently, actually
     4     'ABC'    0          Just a moment ago!

A combined unique constraint won't work, because for my purposes three different 'deleted' entries with the same code is valid.

I ask this question because the 'enforcing this at the application level' policy has recently bitten me in the ass when it came to integrating data from a third-party application. It would have been nice if the database had rejected the bad integration data outright, because fixing the integration before it happened would have been a lot easier than cleansing the data after it happened incorrectly.

I'm using SQL Server 2008 R2. But I don't mind upgrading if I have to to get this functionality, I've been meaning to upgrade anyway.

Aaron answered this pretty much immediately. I needed to be using a filtered unique index.

The following code demonstrates the solution.

IF EXISTS (SELECT * FROM SYS.TABLES WHERE [name] = 'SampleTable')
BEGIN
    PRINT 'Dropping Table [SampleTable]';
    DROP TABLE [SampleTable];
END;
GO

PRINT 'Creating Table [SampleTable]';
CREATE TABLE [SampleTable]
(
     [Id] INTEGER NOT NULL IDENTITY(1,1)
    ,CONSTRAINT [PK_SampleTable]
        PRIMARY KEY ([Id])

    ,[Code]      NVARCHAR(255) NOT NULL
    ,[Deleted]   BIT           NOT NULL DEFAULT 0
    ,[CreatedOn] DATETIME      NOT NULL DEFAULT GETDATE()
);

CREATE UNIQUE INDEX
    [UNQ_SampleTable_Code]
ON
    [SampleTable]([Code])
WHERE
    ([Deleted] = 0);

INSERT INTO [SampleTable] ([Code],[Deleted]) VALUES ('ABC', 1);
INSERT INTO [SampleTable] ([Code],[Deleted]) VALUES ('ABC', 1);
INSERT INTO [SampleTable] ([Code],[Deleted]) VALUES ('ABC', 1);
INSERT INTO [SampleTable] ([Code],[Deleted]) VALUES ('ABC', 1);
INSERT INTO [SampleTable] ([Code],[Deleted]) VALUES ('ABC', 0);
INSERT INTO [SampleTable] ([Code],[Deleted]) VALUES ('ABC', 0);

UPDATE [SampleTable] SET [Deleted] = 0 WHERE [Id] = 1;

SELECT * FROM [SampleTable];

The sixth (final) insert and the update both fail because of the filtered index.

I'll make good use of this in future. Thanks Aaron!

When you have a unique constraint that you want to apply to only a subset of rows, you can enforce this using a unique, filtered index. The index that seemed to work for you in this case is:

CREATE UNIQUE INDEX [UNQ_SampleTable_Code]
  ON dbo.[SampleTable]([Code])
  WHERE   ([Deleted] = 0);

This ensures that only one distinct value of Code can exist for rows where Deleted is 0, but duplicates can exist where Deleted is 1. Typically this will also help the performance of some queries, since you will often be interested in only the active rows (and not the soft deletes), but you may want to consider adding columns to the INCLUDE clause if this doesn't cover queries (SQL Server may choose to scan the clustered index, or a different index, if lookups are deemed too costly).


MySQL Query - How to get most recent demographics? I have a table that has duplicated cardnbr's. I need to pull out demographics that are the most recently used by the customer. I have a date field (lastvisit) that shows their most recently used card. Here's an example query that doesn't work:

SELECT cardnbr, fname, lname, address, city, state, postal
FROM cards
WHERE lastvisit = MAX( lastvisit )
GROUP BY cardnbr

How can I format my query to do this but actually work?

Also, the primary key for this table was created after duplicated data was added to the table.

Assuming you have a PRIMARY KEY or otherwise UNIQUE KEY called id (replace with whatever it's called in your table), consider the following query:

SELECT
  cardnbr,
  fname,
  lname,
  address,
  city,
  state,
  postal
FROM
  cards,
  (
    SELECT
      GROUP_CONCAT(top_id_per_group) AS top_ids
    FROM
      (
        SELECT
          SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY lastvisit DESC), ',', 1) AS top_id_per_group
        FROM
          cards
        GROUP BY
          cardnbr
      ) s_top_ids_per_group
  ) s_top_codes
WHERE
  FIND_IN_SET(id, top_ids)
ORDER BY
  cardnbr
;

The idea is to get, per cardnbr, the id where the lastvisit is most recent. Then, get all row data for those ids.

The above is a simplification of Selecting Top N Records Per Group, where N = 1.

Another way to solve it is described in Selecting Non Aggregated Column Data in GROUP BY. The query presented in this post actually provides better performance than the above, but uses some parsing and casting to get the final results (all columns parsed as strings, then cast back to their original types). Also see this post. Apologies for the many links, but each post presents with a different solution to the problem.


SERV_MOD_ACT_TRACE_ENABLE problem SQL> EXECUTIVE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=>'ACC_SRV',waits=>TRUE,binds=>FALSE,instance_name=>'inst1');

Why this function need instance_name parameter?
I think the relations between instance and service are as follows(http://docs.oracle.com/cd/B19306_01/network.102/b14212/concepts.htm):

One Service for one instance
multi-services for one instance

If I call SERV_MOD_ACT_TRACE_ENABLE, a single trace file is created for each session that uses the ACC_SRV service. What effect is this instance parameter which is not null?

There is no need for the instance name, as you can see in the documentation

  This procedure will enable SQL tracing for a given combination of
  Service Name, MODULE and ACTION globally unless an instance_name is
  specified.

instance_name has a default value null implicating it is not a required parameter. In a RAC database this would just enable tracing for your service in all open instances.


More CPU cores vs faster disks I'm part of a small company so as usual covering a number of different roles. The latest of which is procuring a dedicated SQL Server box for our .NET web app. We've been quoted on a dual Xeon E5-2620 (six core) 2.00 GHz CPU configuration (12 cores in total), with 32 GB of RAM. This has left us with a limited budget for the disk array, which would essentially consist of two 2.5" SAS 300 GB drives (15k RPM) in a RAID 1 config.

I know that the disk setup is sub-optimal for SQL Server and I'd really like to push for RAID 10 so we can put the database, log files and tempdb on their own drives. In order to make this compatible with our budget should I consider reducing the number of CPU cores? or would I get better bank for buck keeping the cores and using fewer drives, perhaps 4 in a dual RAID 1 setup?

Here are some additional stats

The SQL Server database is tilted towards high numbers of reads to writes, probably 80% vs 20% respectively. The current DB size is around 10 GB 26 GB at present, growing at rate of 250 MB per month.
Currently running on SQL Server 2008 R2 Standard on a single quad core Xeon box shared with the web-server (12 GB Ram, 2 x 10k 300GB SAS drives in RAID 1), looking to move to SQL Server 2012 Standard.
Database serves approx 100-150 concurrent users with some background scheduling tasks thrown in. Reading this, I'm thinking that 12 cores is serious overkill!

I deployed the whole application to an Azure cloud service (2 small instances) linked to an SQL Azure DB. Although performance was reasonable when testing (almost zero load) I lost the courage to use in production due to the unpredictability I'd read so much about. It may work better with a scale-out approach, but with just a 10 GB database I can probably get away with scaling up right now and save some cash.

I initially overlooked the licensing costs and didn't realise that SQL Server 2012 licensing is based on the number of cores. I have a BizSpark MSDN subscription with a SQL Server 2012 Standard licence so I'd need to read up on how many cores this would utilise out of the box.

Speaking from experience which is humble but I think worth sharing, the major bottleneck with SQL databases (Sybase and SQL server here) is storage.

But I think it's only fair that someone first benchmarks their setup before making any wrong assumptions. In my case, CPU usage has never risen high enough to justify upgrading the CPU any time soon. Instead, I have upgraded from single drive to RAID 1 and then to RAID 10 + a bump from 8GB to 16GB of RAM.

All these RAID upgrades helped to reduce previous wait times by a factor of 2 to 6. I suspect upgrading to SSDs would be even better. If you think about it, it can all be reduced to (theoretical) bandwidth. Your [(RAM Speed + RAM Size + Memory controller) link to CPU] combo has a bandwidth limit which would be the most important factor in read operations when your data should always be cached, your particular (RAID) storage has a bandwidth limit (affects reads when cache missed and writes when flushing or with many clients writing lots of data combined).

Normalize all those limits as much as possible (bring them closer such that you don't have wasted resources) and raise them as much as possible (upgrade where needed and only if needed, don't let resources become wasted if the system won't be able to use them because some other bottleneck is in the way). In the end, your worst bottleneck will be the least performing (with least bandwidth) server subsystem in your particular configuration.

I might also add that, in the process of upgrading, I have created separate RAID configurations for the database files and the database log files. The reason was that database log files tend to be write intensive. A log file is used to recover a database from a crash and it's always written to immediately as a transaction is committed before any data is written to the database file.

A log file is also used by some database replication servers but most replication is not done instantly but frequently so the read performance impact is minimal here. At least I think so. I have done minimal benchmarking while making this upgrade so again, I recommend anyone first benchmarks their different configurations and first upgrades their storage, then RAM and network link, before thinking about upgrading their CPUs.

After more extensive upgrades on more than 5 servers, I came back to share my experience. I definitely still advocate for first upgrading storage, then RAM and then CPU. Reason is the disparity of bandwidths in the system between storage, RAM and CPU, in order of lowest to highest. So I upgraded a bunch of servers from RAID10 and dual RAID1s to SSDs.

The way I did it because of cost concerns (I have 20 more servers to upgrade) is to move the database data and objects files to an SSD (yes, just one SSD in RAID0) and move the transaction log plus tempdb to a 4xHDD RAID10 configuration. I tested with the tempdb on the SSD as well with great results (in fact very nice results with more than 15 times faster queries sometimes, yielding some reports taking seconds instead of minutes in the past) but later moved the tempdb to the disk RAID10 because of intensive write-wear concerns for the SSD.

So now basically I have observed 10-15 times faster response times per some of the longest queries. The SSD is great for reading data into RAM fast because SQL Server does not bring data into RAM until requested to and of course data first needs to be loaded into RAM to be processed by the CPU (later, in L1,L2,L3 cache), so SSDs help reduce that initial wait time by a huge factor. And SSDs also help reduce swapping times... clearing out RAM and loading new data, especially if your database is larger than can fit into RAM.

All in all, we're very pleased and been running like so for several months in a sort of slow migration process to allow the servers to run so I can collect wear-level information before I switch all my servers to this configuration. And this is just SQL Server Express! :D - Just make sure your SSDs can provide constant IOPS because that's another thing that makes a huge difference (just google it). That's why I chose Intel DC (DataCenter) series SSDs.


All four data nodes in MySQL Cluster in same node group I am testing MySQL Cluster 7.2. I have two servers, mysql1 and mysql2. I want the management server and two data nodes to run on mysql1 and two other data nodes to run on mysql2. My config.ini file looks like this:

[ndb_mgmd]
hostname=mysql1
datadir=/var/mysql-cluster/ndb_data
NodeId=1

[ndbd default]
noofreplicas=2
datadir=/var/mysql-cluster/ndb_data
DataMemory=8M

[ndbd]
hostname=mysql1
NodeId=3

[ndbd]
hostname=mysql2
NodeId=4

[ndbd]
hostname=mysql1
NodeId=13

[ndbd]
hostname=mysql2
NodeId=14

[mysqld]
NodeId=50

With this configuration I would expect, as per the MySQL Cluster documentation, that the two data nodes 3 and 4 would be in nodegroup 0, while the two data nodes 13 and 14 would be in nodegroup 1.

However, when I start everything up and show the nodes, I see this:

Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     4 node(s)
id=3    @192.168.0.42  (mysql-5.5.30 ndb-7.2.12, starting, Nodegroup: 0, Master)
id=4    @192.168.0.43  (mysql-5.5.30 ndb-7.2.12, starting, Nodegroup: 0)
id=13   @192.168.0.42  (mysql-5.5.30 ndb-7.2.12, starting, Nodegroup: 0)
id=14   @192.168.0.43  (mysql-5.5.30 ndb-7.2.12, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.0.42  (mysql-5.5.30 ndb-7.2.12)

[mysqld(API)]   1 node(s)
id=50 (not connected, accepting connect from any host)

Everything seems to be in nodegroup 0! What do I have to do to get 3 and 4 in one group and 13 and 14 in another?

This seems to be a bug [1] in MySQL. It happens because your data nodes are still starting. Node-group IDs should be shown correctly once they are started completely.

[1] http://bugs.mysql.com/bug.php?id=30831


Better database for "keep always the 5 latest entries per ID and delete older"? I have a PostgreSQL database with a history table where I store an fooID (not a primary key serial but a text), an attribute target and the current timestamp whenever the target for this fooID changes:

CREATE TABLE history (
  fooId       text not null,
  target      text not null,
  updated_at  timestamp not null default default now()
);

There are several million entries in this table and a couple of thousand changes per day. Every day I will scan the table and keep the last 5 entries per fooId and delete any older entries.

The DELETE ... WHERE id in ... rank() over (partiton by nr order by created_at... query is not my question, that works. Just takes awful long.

My question is rather: Is a standard PostgreSQL table the best approach for this problem?

Would PostgreSQL table partitioning help here? I know partitioning is used to easily throw away big chunks of data that are older than X days, but partitioning by fooId would seem to create too many partitions in my case.

Are there NoSQL databases that would be much faster because they store the data differently?

Are there other PostgreSQL tricks that help me to store the data differently and more optimized for the use case of the daily purging (SELECTs are not an issue, it gets rarely queried)?

An exclusive lock for an 1 hour per week would be acceptable. There are about 1 million different fooIDs so no chance of having a separate table per fooID.

One option I can think of is to delete the oldest row as soon as you insert a new one. That will only require a very quick lookup limited to at most 6 rows rather then going through all rows at a time.

To do that efficiently you need a unique key on the table:

create table history
(
  id          serial primary key, -- to make a lookup on a single row efficient
  fooid       text not null,
  target      text not null,
  updated_at  timestamp not null default now()
);
-- to make finding the oldest row for one fooid efficient
create index on history(fooid, updated_at);

Then create a trigger that only keeps the 5 most recent rows for a fooid:

create or replace function remove_last()
  returns trigger
as
$$
begin
  with ranked as (
    select id, row_number() over (partition by fooid order by updated_at) as rn
    from history
    where id <> new.id
      and fooid = new.fooid
  )
  delete from history
  where id in (select id
               from ranked
               where rn >= 5);
  return new;
end;
$$
language plpgsql;

create trigger remove_last_trigger
  after insert on history
  for each row execute procedure remove_last();

On my laptop, a simple test setup with 5 million rows (1 million different fooid values) showed a trigger overhead that was less then a millisecond:

insert into history (fooid, target) values ('1', 'new stuff');

QUERY PLAN
----------------------------------------------------------------------------------------------------
Insert on stuff.history  (cost=0.00..0.01 rows=1 width=76) (actual time=0.062..0.062 rows=0 loops=1)
  Buffers: shared hit=8
  ->  Result  (cost=0.00..0.01 rows=1 width=76) (actual time=0.017..0.017 rows=1 loops=1)
        Output: nextval('history_id_seq'::regclass), '1'::text, 'new stuff'::text, now()
        Buffers: shared hit=1
Planning time: 0.024 ms
Trigger remove_last_trigger: time=0.438 calls=1
Execution time: 0.524 ms

How much overhead that is in your system and whether or not that is acceptable for you I don't know, but "thousand changes per day" doesn't sound like a very busy system.


SQLAnywhere: Mobilink upload_fetch_column_conflict seems not to be executed With Sybase Central I've configured a MobiLink test object, where I'm trying to detect a unique constrain violation before uploading. Unfortunately it appears upload_fetch_column_conflict seems to not being executed. My events are configured like this on Sybase Central:

[...]
Product (DBA): upload_fetch_column_conflict
SELECT "DBA"."Product"."name",
    "DBA"."Product"."quantity",
    "DBA"."Product"."quality"
FROM "DBA"."Product"
WHERE "DBA."Product"."name" = {ml r."name} AND NOT (SELECT count(*) from "DBA"."Product" WHERE quality = {ml r.quality})> 0;
[...]

Any idea what I've maybe missed?

upload_fetch_column_conflict is only getting called, if there is a line updated. Therefore an insert will not trigger that function.

If inserting a new line which might conflict with a recent change on consolidated database, the conflict needs to be caught during upload workflow -- so e.g. inside processing of upload_insert.


Unable to start oracle database after trying to enable archive log mode on I am facing this unusual issue. I wanted to enable archive log mode from noarchivelog mode and used alter command for the same as  ALTER SYSTEM SET log_archive_dest='LOCATION=D:\Oracle\Redo Log Archive\'and then shutdown the database. When i tried to restart as SQL>STARTUP mount it is giving below error
ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed. After searching the web i tried
sql>ALTER SYSTEM SET log_archive_dest_1='LOCATION=D:\Oracle\Redo Log Archive\' scope=both; but getting

ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

and for the other commands like SQL>archive log list it showing above error i.e ORA-01034: ORACLE not available.

P.S: In init.ora in pfile folder there isn't any log_file_dest_1 parameter. Is there any way i could add it manually and start the database or any other way as alter command from sql plus is not working.

If there are a bad value in the spfile, you can create a temporary init file and use that.

Login using sqlplus:

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 14 09:31:21 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

Create a temporary pfile:

SQL> create pfile = 'c:\temp\init.ora' from spfile;

File created.

SQL>

Then you edit that file (c:\temp\init.ora) and remove/fix the parameter for archive destination.  Then you do:

startup pfile=c:\temp\init.ora

to get your database started, and then issue:

create spfile from pfile = 'c:\temp\init.ora';

and you should be good to go.


How to maintain order when modeling outline in graph database? (TaskPaper) outlines have a natural hierarchy relationship, so a graph database seems like a great fit. Each item in the outline is a node of the graph that points to its parent and children. I also really like the idea of modeling TaskPaper tags as nodes that any tagged items point to.

However, there is one problem: the order of items is important in an outline, but a simple graph doesn't keep that information. What is the best way to maintain the order of items when an outline is stored as a graph?

The method should be efficient when items are added/removed. New items may be inserted before existing items, so a simple increasing counter/timestamp won't work.

The goal is to store and query a giant outline that can't fit in memory (just the necessary items are streamed to/from the user). Two common ways of querying this outline would be:

Linear: get the first n=100 items when everything is "expanded."
Breadth first, with depth first traversal of certain items: only get the top level items, fulling expanding a few selected items. (If fully expanding an item would exceed n=100 items, stop expanding.)

Common updates would be:

Add/delete a new item somewhere in the hierarchy. Most inserts will probably be appending after the last child of an item. (Basic editing.)
Add/delete a new sub-tree (Copy-paste a section of an outline.)

I don't think other database types would be better than a graph database, but I'm open to using other databases (relational, NoSQL, etc).

This is a challenge I have faced multiple times here recently. There are a few ways that I have solved ordering which seems dependent upon the implementation of the graph dbms. Personally, I use Dgraph's Slash GraphQL service. There are some challenges with working with the GraphQL standardized endpoint vs. some database features that are only still available in DQL (Dgraph Query Language extending GraphQL).
Having that said, here are a few ways that I have found to solve such cases:
With a next and previous edge.
This is covered more in detail by MichelDiz on Dgraph's Community post.
DQL has added a @recurse and @normalize directive that helps make the results possible to flatten a deep nest of nest edges. Adding a new one in the hierarchy involves creating a new node with 2 edges to the next and previous nodes and then updating the next edge on the previous node and updating the previous edge on the next node. The query to get a series of edges would look like:
{
  var(func: eq(title, "Series ABC" )){
    G as articles @filter(not has(<~next>))
  }
  q(func: uid(G)) @recurse  {
    uid : uid
    title : title
    next @normalize
  }
}

While this is a brilliant answer, it only works from the DQL endpoint or with a custom query and mutations added to the GraphQL endpoint.
With a parent node holding an array of the order.
At the time of writing this, Slash GraphQL list type [...] functions as a set and not like a JSON array. With this in mind, it does not store duplicates nor preserves order. However, GraphQL can support JSON inside of a string type. (Dgraph has a current feature request to support JSON as a new type as well.) With this fact and the ease of JSON manipulation, we can create a structure to store an order of a list of articles.
type Paper {
  id: ID
  articles: [Article]
  title: String! @search(by: [term])
  order: String
}
type Article {
  id: ID
  content: String! @search(by: [fulltext])
  isDone: Boolean @search
}

With this schema and the auto generated queries and mutations, you would be could add a new article and then update the parent paper's order predicate using your favorite client side JSON manipulation language such as javascript to splice the array of ids inserting your new id as needed.
You can also then make a request to get this order predicate and paginate it on the client side splicing it into chunks and then provide those chunks to the queryArticle query to paginate through your page as needed.


How well, if at all, does Oracle NoSQL database integrate with Oracle RDBMS? I currently have an application which runs on Oracle RDBMS and an area of new functionality which would be better supported by a NoSQL store.  I am wondering if Oracle NoSQL Database may be a good place to store this information and more concretely, can a single query span Oracle NoSQL and Oracle RDBMS.

Oracle NoSQL Database integrates well with the Oracle database.  You can run an SQL query in Oracle Database that can reference data in the Oracle database as well as NoSQL Database.

Please see the Oracle NoSQL Database documentation for more details on external tables support.

You can also get an overview of the latest release here:
http://www.youtube.com/watch?v=VT2n1QhIprQ

I'd suggest that you also post your questions relating to Oracle NoSQL Database to the OTN forum: https://forums.oracle.com/forums/forum.jspa?forumID=1388.  We monitor the forums regularly...

Happy Holidays.

Ashok Joshi
Senior Director, Oracle NoSQL Database development


MongoDB hashtable namespace index max chain reached error after machine crash I have a database machine running standalone MongoDB v2.6.12 on Ubuntu 14.04. It is running well until the machine crashed (by power failure).

MongoDB won't start since then outputting the following error in log.

2017-11-07T16:19:12.149+0800 ***** SERVER RESTARTED *****
2017-11-07T16:19:12.151+0800 [initandlisten] MongoDB starting : pid=1978 port=27017 dbpath=/edx/var/mongo/mongodb 64-bit host=external-db
2017-11-07T16:19:12.151+0800 [initandlisten] db version v2.6.12
2017-11-07T16:19:12.152+0800 [initandlisten] git version: d73c92b1c85703828b55c2916a5dd4ad46535f6a
2017-11-07T16:19:12.152+0800 [initandlisten] build info: Linux build5.ny.cbi.10gen.cc 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Jan 3 21:39:27 UTC 2014 x86_64 BOOST_LIB_VERSION=1_49
2017-11-07T16:19:12.152+0800 [initandlisten] allocator: tcmalloc
2017-11-07T16:19:12.152+0800 [initandlisten] options: { config: "/etc/mongod.conf", net: { port: 27017 }, security: { authorization: "enabled" }, storage: { dbPath: "/edx/var/mongo/mongodb", journal: { enabled: true }, smallFiles: true }, systemLog: { destination: "file", logAppend: true, path: "/edx/var/log/mongo/mongodb.log" } }
2017-11-07T16:19:12.154+0800 [initandlisten] journal dir=/edx/var/mongo/mongodb/journal
2017-11-07T16:19:12.154+0800 [initandlisten] recover begin
2017-11-07T16:19:12.154+0800 [initandlisten] info no lsn file in journal/ directory
2017-11-07T16:19:12.155+0800 [initandlisten] recover lsn: 0
2017-11-07T16:19:12.155+0800 [initandlisten] recover /edx/var/mongo/mongodb/journal/j._0
2017-11-07T16:19:12.155+0800 [initandlisten] recover cleaning up
2017-11-07T16:19:12.155+0800 [initandlisten] removeJournalFiles
2017-11-07T16:19:12.156+0800 [initandlisten] recover done
2017-11-07T16:19:12.466+0800 [initandlisten] error: hashtable namespace index max chain reached:1335
2017-11-07T16:19:12.469+0800 [initandlisten] error: hashtable namespace index max chain reached:1335
2017-11-07T16:19:12.469+0800 [initandlisten] error: hashtable namespace index max chain reached:1335
2017-11-07T16:19:12.472+0800 [initandlisten] waiting for connections on port 27017
2017-11-07T16:19:12.483+0800 [IndexRebuilder] Fatal Assertion 16253
2017-11-07T16:19:12.487+0800 [IndexRebuilder] 0x121df81 0x11bd689 0x11a019d 0xa2d5e5 0xa2d703 0x904f70 0xba15e4 0xba24b8 0x11a3532 0x1262d49 0x7f8f424c9184 0x7f8f417cdffd

It is unable to run with --repair which will result in this error BadValue Can't have journaling enabled when using --repair option.

And I don't think it is really a namespace problem, because a clone machine from the snapshot before the crash will run without any problem.

The other question you guys marked that I'm "duplicateing" is in completely different situation. As I'm not even able to start MongoDB, how can I run command in it?

Also I tried to disable journal and get the following error.

You specified --repair but there are dirty journal files. Please
restart without --repair to allow the journal files to be replayed.
If you wish to repair all databases, please shutdown cleanly and
run with --repair again.

And if I remove --repair I get the same error: hashtable namespace index max chain reached:1335 errors in the log as in the beginning.

OK I found my own answer.

Because the journal file seems corrupted, this solution will abandon all changes in the journal file so the database might be restored to a slightly earlier state before the crash

According to MongoDB documentation,

  At default intervals of 60 seconds, MongoDB asks the operating system to flush the shared view to disk.

the period of losing data should be within less than 60 seconds before crash by default.

The steps are as follows:

Turn off journal in the config. For me it's in /etc/mongod.conf and change journal=true to journal=false.
In the MongDB data directory (e.g. /var/lib/mongodb/, or for me it's /edx/var/mongo/mongodb/), remove both journal folder and mongod.lock file.
Run mongod with --repair option like sudo -u mongodb mongod -f /etc/mongod.conf --repair and wait. This operation may need a lot of disk space.
Turn on journal to journal=true as opposite to step 1, and start the server normally. E.g. sudo service mongod start.


How to use an alias name that has RANK() OVER in a WHERE clause I have a query that has a RANK() OVER function inside it but I would like to use the results displayed on this column in the WHERE clause that follows. How do I write that as all the other questions I have looked at did not have RANK() OVER and seemed easier to do. Here is the statement:

USE SMSResults

SELECT Student_No,Result,Module_Name,Semester,Year,RANK() OVER (PARTITION BY Student_No ORDER BY Semester  DESC) AS Rnk
FROM tbl_results
WHERE Student_No = '201409'

ORDER BY Year DESC

I would like to use the Rnk column in the WHERE clause

I would like to use the Rnk column in the WHERE clause

The Rnk is a column computed in the SELECT clause. It's not avaiable in the WHERE clause of the same level, as the logical order of execution a query is FROM -> WHERE -> SELECT.

You have to wrap the query in a subquery. You can use either a CTE (Common Table Expression):

USE SMSResults ;
go
with CTE as
    ( SELECT Student_No,Result,Module_Name,Semester,Year,
             RANK() OVER (PARTITION BY Student_No ORDER BY Semester  DESC) AS Rnk
      FROM tbl_results
      WHERE Student_No = '201409')
select * from CTE
where rnk > 1   -- change here with whatever you want ... !!
ORDER BY Year DESC ;

or a derived table:

USE SMSResults ;
go
select * from
    ( SELECT Student_No,Result,Module_Name,Semester,Year,
             RANK() OVER (PARTITION BY Student_No ORDER BY Semester  DESC) AS Rnk
      FROM tbl_results
      WHERE Student_No = '201409')
  AS derived_table
where rnk > 1   -- change here with whatever you want ... !!
ORDER BY Year DESC ;

As a side note for future readers - worth reading - What's the difference between a CTE and a Temp Table? by JNK♦


MySql Innodb corruption recovery I have a MySql database, just containing Innodb tables, that seems to have corrupted itself.

It replicates to another server and it has corrupted that one too!

The symptom is that I can restart the server but as soon as I try to connect to it, it crashes.

The log file talks about a possible index page corruption to a specific table and that fits in terms of the table being accessed when the problem occurred.

Following the InnoDB recovery guidelines in the MySQL manual, I have set the innodb_force_recovery option to 6 which lets me restart the database and dump the tables.

Anything less than 6 and it will not stay up.

My questions are:

How do I know the extent of the innodb corruption?
Is the only safe option to recreate the innodb files from scratch (I assume via a re-install) or is there a smarter way to do this?
Can I trust the table dumps or should I go back to the most recent backup (also a table dump) and accept the data loss?
If it is an index page corruption, can I rebuild the primary index of the affected table and then restart the DB without the innodb_force_recovery option?

In general case once InnoDB tablespace is corrupt you need to re-create tablespaces from scratch. This is the safest way especially if you're not familiar with InnoDB files/format.

In some cases InnoDB writes to the error log file information about corrupted page number and index it belongs to. For example,

InnoDB: Page may be an index page where index id is 2575
InnoDB: (index "idx_actor_last_name" of table "sakila"."actor")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.

InnoDB crashed at that page but it doesn't mean other pages are OK. To estimate how badly a tablespace is corrupt innochecksum might help. Note however it's often misleading. innochecksum checks individual pages, but not the tablespace structure. For example, all-zero page is valid in innochecksum's opinion while the page should have contained some data.

To check that all tablespaces are good you can read data from all indexes (PRIMARY and all secondary) of all tables. For example, to check sakila.actor you need two selects:

SELECT * FROM actor FORCE INDEX ('PRIMARY')
SELECT * FROM actor FORCE INDEX ('idx_actor_last_name')

If you suspect that only a secondary index is corrupt you may drop and re-create it. I describe this case in https://twindb.com/repair-corrupted-innodb-table-with-corruption-in-secondary-index/ .

If PRIMARY index is corrupt you may want to dump the table drop it and reload again. I.e. you don't reload whole database, just one table.


Proper way to store lots of links? I'm doing a small crawl over multiple sites and I have a lot of links which are represented by ID's (example.com/foo = 354).

I'm currently storing the link -> link references and the link text. So in the following table page "2845" contains a link to 4479 with the text "About Us". Nothing big, just 3NF.

+----------+----------+-----------------+
| url_1_id | url_2_id | text            |
+----------+----------+-----------------+
|     2845 |     4479 | About Us        |
|     2845 |     4480 | Who We Are      |
|     2845 |     4481 | What We Do      |
|     2845 |     4482 | Core Principles |
|     2845 |     4483 | Research Staff  |
+----------+----------+-----------------+

However, by my calculations (most of the pages containing 500-1000 links each) I should have 6GB of link data by the time I've only parsed 200k pages.

Is there a better way to store link data? Especially if there is a good way to normalize repeated navigation menus for all site pages.

Possible approaches:

If your problem is well represented by a graph (and I think it is), I'd suggest that you consider using a Graph Database. It's optimised for this kind of scenarios: you have pages (nodes), you have links between pages (verticies), and you have some attributes associated to either one of them. Check Neo4J or OrientDB to get a feeling for what they could do in your scenario.

If you want to save some space, consider a way to represent menus in a compact way. That is, there will be a very large number of cases like:

page1 - links_to - home
page1 - links_to - legalese
page1 - links_to - the_company
page1 - links_to - our_products
page1 - links_to - our_services
page1 - links_to - contacts

page1 - links_to - something unique or not common 1
page1 - links_to - something unique or not common 2

and then again

(same for page2)
(same for page3)

if you identify this structure, you can create some "meta-structure" such as "standard_menu". (The difficult part of this approach is deciding what collection of links should be considered a menu and which are not, and small differences such as a page containing a menu with a slight variations [such as "I don't link to myself"]).

This data structure should help you:

CREATE TABLE urls
(
    id_url serial PRIMARY KEY,
    url text NOT NULL,
    UNIQUE(url)
) ;

CREATE TABLE menus
(
    id_menu serial PRIMARY KEY
) ;

CREATE TABLE menu_links
(
    id_menu integer not null REFERENCES menus(id_menu),
    id_url_to integer not null REFERENCES urls(id_url),
    link_text text not null,
    PRIMARY KEY (id_menu, id_url_to)
) ;

CREATE TABLE from_page_to_page
(
    id_url_from integer not null REFERENCES urls(id_url),
    id_url_to integer not null REFERENCES urls(id_url),
    link_text text not null,
    PRIMARY KEY (id_url_from, id_url_to, link_text)
) ;

CREATE TABLE from_page_to_menu
(
    id_url_from integer not null REFERENCES urls(id_url),
    id_menu integer not null REFERENCES menus(id_menu),
    PRIMARY KEY (id_url_from, id_menu)
) ;

And finally, you can have a view that puts together the equivalent of your existing table by making a UNION of the "menu links" and the "non-menu links":

CREATE VIEW all_links AS

SELECT
    id_url_from, id_url_to, link_text
FROM
    from_page_to_page
UNION ALL
SELECT
    id_url_from, id_url_to, link_text
FROM
    from_page_to_menu
    JOIN menu_links ON menu_links.id_menu = from_page_to_menu.id_menu ;

Alternatively, you could always change the name "menu" by "link_group", every time you have something like:

page1 - links_to - page_a
   page1 - links_to - page_b
   page1 - links_to - page_c
   page1 - links_to - page_z

page2 - links_to - page_a
   page2 - links_to - page_b
   page2 - links_to - page_c
   page2 - links_to - page_y

page3 - links_to - page_a
   page3 - links_to - page_b
   page3 - links_to - page_c
   page3 - links_to - page_x

(ignoring text labels, these are actually 12 pairs of numbers)

you could convert it into:

link_grup_1  - page_a
   link_grup_1  - page_b
   link_grup_1  - page_c

page_1 - link_grup - link_grup_1
   page_1 - links_to - page_z
   page_2 - link_grup - link_grup_1
   page_2 - links_to - page_y
   page_3 - link_grup - link_grup_1
   page_3 - links_to - page_x

(these are only 9 pairs of numbers).

The savings will grow as soon as the number of items common to many pages is more than just two or three.


That makes total sense.

Since you said the base_id is VARCHAR(255), your first query

SELECT sid FROM rf_fo.scald_atoms WHERE base_id = 493457;

really behaves like this

SELECT sid FROM rf_fo.scald_atoms WHERE CAST(base_id as unsigned) = 493457;

This causes every base_id to experience a CAST and a comparison. Full table scan required.

Your second query is a pure string comparison and the index has string-based values.


prevent MySQL Workbench to add index for foreignkey constraint when it is first part of primary I know for each foreign key we should make an index on its column, But it is not necessary when that column in first part of PRIMARY index.

MySQL accepts this query:

CREATE TABLE IF NOT EXISTS `poll_option_i18ns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poll_option_id` int(11) NOT NULL,
  `lang` char(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `title` varchar(127) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`poll_option_id`,`lang`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `poll_option_i18ns`
  ADD CONSTRAINT `poll_option_id` FOREIGN KEY (`poll_option_id`) REFERENCES `poll_options` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

But in MySQL workbench I couldn't add foreignkey without index!
It is MySQL Workbench bug or I do it wrong way?

That's currently a limitation of MySQL Workbench and has been mentioned recently already on Stackoverflow (can't find the question right now, sorry). MySQL Workbench doesn't use the special case where a foreign key uses columns that start the PK in that table. Please file a feature request at http://bugs.mysql.com if you think it would be worth implementing it.


Why is my query returning an unexpected result? Here are the tables I am using:

parts(pid,pname,color)
suppliers(sid,sname,address)
catalog(sid,pid,cost)

I need to find the name of suppliers that supply two different parts with the same price. I keep getting the incorrect answer.

SELECT S.sname
FROM Supplers S
WHERE S.sid IN (SELECT C.pid
FROM Catalog C, Catalog C1, Parts P, Parts P1
WHERE C1.sid = C.sid  AND P.pid = P1.pid AND C1.cost = C.cost);

My best guess is that what you are actually trying to query is:

SELECT S.sname
FROM Suppliers S
WHERE S.sid IN (SELECT C.sid
FROM Catalog C, Catalog C1
WHERE C1.pid <> C.pid  AND C1.cost = C.cost);

I changed the subselect to return C.sid, and removed the parts table altogether, since in your original body text you stated no conditions on the table.

Is this what you are looking for?


What data type should I use: ENUM, TINYINT, or CHAR? I'm creating a new table and I would like to do it right. The table will be a list of members with their Professional Type and Decile associated.

The Professional Type is a 3-character string. There are only 8 allowable strings for this field. Should I use ENUM or CHAR(3) or something better?

The Decile can be any number between 1-10, but only these numbers. Should I use ENUM or unsigned TINYINT or something better?

Which are the best options for efficiency?

ENUMs scare me because of any issue regarding adding/removing type values. You are probably better off representing both ProfessionalType and Decile as TINYINT or CHAR(3). Anything but ENUM.

See my past posts on ENUM :

Ocy 19, 2011 : Advantages and Disadvantages to using ENUM vs Integer types?
Jan 24, 2012 : Is it possible to change ENUM() lists?

For efficiency, please do not index this field alone, given such a low cardinality (8,10).


Connection to MySQL Remotely I want to connect to MySQL Server hosted on a Linux Machine from my Windows Machine using Command Prompt with the following command:

prompt>mysql -u root -p -h remote_hostname

Connections, ports, user accounts and credentials are all fine. But it is throwing the below error:

  ERROR 1130 (HY000): Host 'my_hostname' is not allowed to connect to this MySQL server

I am wondering is it possible to communicate with Linux Shell from Windows Command Prompt? or is it not at all in the very design of prompt to connect to Linux Shell for any remote operations? Or is it special in case of MySQL.

I observed that I can connect from Linux to Windows but the reverse is not possible when I am trying just like I explained above.

Anybody know the theoretical explanation to this?

You state that "Connections, ports, user accounts and credentials are all fine.", but I have my doubts.

Mentioning the exact error from the documentation:

  [The error] occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host

Look in your mysql.user table on the Linux MySQL server for a user entry with the user/host combination of your windows machine:

SELECT User, Host FROM mysql.user WHERE User='my_user' AND Host='windows_ip';

If that doesn't return a result, you'll need to add that user. If it does return a result, can you add that to your question?


How can I create new login in SSMS 2008? When I attempt to create new login in MSSQL Server 2008, I get the following errors:

I have tried everything I know, but I can't get it to work.

What should I do and how I can solve these issues?

Open the Window again to create a new login.  Use the "Search" button to find the Windows login you wish to create an account for.  Then you can click the "Script To" button at the top and get the actual T/SQL Script.  It should look something like

CREATE LOGIN [KAWMIN-PC\Classic .NET AppPool] FROM WINDOWS

The UI doesn't always work correct for creating logins with spaces or other reserved characters.

Once you have the T/SQL script correct any issues and run the script and the login should be created.  If there is an error post the error message to the site.


UUID as primary key and download ID I'm designing a Django Rest Framework API with PostgreSQL. In the database I'll be recording information about documents which can be requested for download. I want the request to include a unique id, so the server knows which document to retrieve. Every document can only be requested once by the one user who has its id value.
However, I read that using primary key values (in my case would be a UUID generated by Python) outside of the database is insecure.
The question
So, can I use a UUID as primary key in the database and let users request the document by this UUID or should I generate another id for the user to request the document by? Any help is appreciated!

It doesn't matter much if you use an uuid or a bigint.

If you generate the keys in one database, using a bigint column with a sequence has the advantage that it uses only 8 bytes instead of 16. Also, counting an integer up is cheaper than generating a UUID.

UUIDs shine if keys are generated in several places independent from each other and the resulting identifier should still be globally unique.


SQL update multiple columns matching a pattern I have a table in a postgresql database in which I have multiple columns matching a pattern /\w*(?:_open|_close)/g.
Those are timefields.

I would like to substract 1 hour from all the rows on those columns. Is there a way to do without running an UPDATE query separately for each column?

I think you're trying to avoid writing:

  UPDATE mytable SET
   door_open = door_open + INTERVAL '1' HOUR,
   door_close = door_close + INTERVAL '1' HOUR,
   car_open = car_open + INTERVAL '1' HOUR,
   car_close = car_close + INTERVAL '1' HOUR,
   ... blah blah ...
  WHERE
   ...

and instead want a way to match column names by wildcard.

If that is the case, then no, there is no built-in way to do that.

You can construct a query dynamically using PL/PgSQL: Use a query against the information_schema.columns view to get column names, then string concatenation to form the query. Then you can run it with EXECUTE. There are many examples of such dynamic SQL elsewhere on Stack Overflow.

e.g.

DO
$$
DECLARE
  sqlstring text;
BEGIN
  SELECT INTO sqlstring
   'UPDATE blah SET '
   || string_agg(format('%I = %I + INTERVAL ''1'' HOUR', column_name, column_name), ', ')
   || ' WHERE true'
  FROM information_schema.columns
  WHERE table_name = 'blah'
    AND table_schema = 'public';

  EXECUTE sqlstring;
END;
$$;


Using TEXT or even MEDIUMTEXT for all text columns in a MySQL table? I'm using a PHP script to insert millions of rows into a MySQL. Each of the rows contain a diversity of columns that are very different across the rows.

Basically I'm using MySQL as a key-value pair store.

While I can make the column to auto-adjust its size according to the length of the string to be inserted on the fly, it may not be a good idea when the table has millions of rows.

For example, with 5 million rows in the table, the next insert has a column with over 5,000 characters while that column is defined as VARCHAR(127) in the table. Re-defining the column in the job would be anything but desirable. Is it? Cause' it bogs things down and it has to change that particular column of all the 5 million rows. Right?

So I'm thinking of creating each column as a TEXT or even MEDIUMTEXT column in the first place. It is after I have done all the inserts that the columns will be re-defined according to the longest value in the column.

Therefore my question is could there be any performance issues with all the columns of the table defined as TEXT / MEDIUMTEXT? Especially for large amounts of inserts?

Drawbacks of what you propose:

Temp tables (for complex SELECTs) cannot use MEMORY, which is faster than the fallback of MyISAM.
Storing numeric 'values' VARCHAR makes it difficult or slow to test for.
Key-value schema leads to ugly JOINs.  And they are inefficient.
In InnoDB, depending on the ROW_FORMAT, TEXT fields may be stored in other blocks.  This may slow you down (INSERTs and SELECTs) due to extra disk hits.

Yes/no -- the ALTER TABLE to change the width of a VARCHAR may be costly.  In some very recent version, increasing the size of a VARCHAR became virtually free.  (There may be more caveats.)


Data Warehouse design for reporting against data for many time zones We are trying to optimize a data warehouse design that will support reporting against data for many time zones. For example, we might have a report for a month's worth of activity (millions of rows) that needs to show activity grouped by the hour of the day. And of course that hour of the day has to be the "local" hour for the given time zone.

We had a design that worked well when we just supported UTC and one local time. The standard design of Date and Time dimensions for UTC and local time, id's on the Fact tables. However, that approach does not seem to scale if we have to support reporting for 100+ time zones.

Our Fact tables would get very wide. Also, we'd have to solve the syntax issue in SQL of specifying which date and time id's to use for grouping on any given run of the report. Perhaps a very large CASE statement?

I've seen some suggestions to get all the data by the UTC time range you are covering, then return it to the presentation layer to convert to local and aggregate there, but limited testing with SSRS suggests that will be extremely slow.

I've consulted some books on the subject as well, and they all seem to say just have UTC and convert on display or have UTC and one local. Would appreciate any thoughts and suggestions.

Note: This question is similar to: Handling time zones in data mart/warehouse, but I cannot comment on that question, so felt this deserved its own question.

Update: I selected Aaron's answer after he made some significant updates and posted sample code and diagrams. My earlier comments on his answer won't make much sense anymore as they referred to the original edit of the answer. I'll try to come back and update this again if warranted

I've solved this by having a very simple calendar table - each year has one row per supported time zone, with the standard offset and the start datetime / end datetime of DST and its offset (if that time zone supports it). Then an inline, schema-bound, table-valued function that takes the source time (in UTC of course) and adds/subtracts the offset.

This will obviously never perform extremely well if you are reporting against a large portion of data; partitioning might seem to help, but you will still have cases where the last few hours in one year or the first few hours in the next year actually belong to a different year when converted to a specific time zone - so you can never get true partition isolation, except when your reporting range does not include December 31 or January 1.

There are a couple of weird edge cases you need to consider:

2014-11-02 05:30 UTC and 2014-11-02 06:30 UTC both convert to 01:30 AM in the Eastern time zone, for example (one for the first time 01:30 was hit locally, and then one for the second time when the clocks rolled back from 2:00 AM to 1:00 AM, and another half hour elapsed). So you need to decide how to handle that hour of reporting - according to UTC, you should see double the traffic or volume of whatever you're measuring once those two hours get mapped to a single hour in a time zone that observes DST. This can also play fun games with sequencing of events, since something that logically had to happen after something else could appear to happen before it once the timing is adjusted to a single hour instead of two. An extreme example is a page view that happened at 05:59 UTC, then a click that happened at 06:00 UTC. In UTC time these happened a minute apart, but when converted to Eastern time, the view happened at 1:59 AM, and the click happened an hour earlier.
2014-03-09 02:30 never happens in the USA. This is because at 2:00 AM we roll the clocks forward to 3:00 AM. So likely you will want to raise an error if the user enters such a time and asks you to convert that to UTC, or design your form so that users can't pick such a time.

Even with those edge cases in mind, I still think you have the right approach: store the data in UTC. Much easier to map data to other time zones from UTC than from some time zone to some other time zone, especially when different time zones start / end DST on different dates, and even the same time zone can switch using different rules in different years (for example the U.S. changed the rules 6 years ago or so).

You will want to use a calendar table for all of this, not some gargantuan CASE expression (not statement). I just wrote a three-part series for MSSQLTips.com on this; I think the 3rd part will be the most useful for you:

http://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server--part-1/
http://www.mssqltips.com/sqlservertip/3174/handle-conversion-between-time-zones-in-sql-server--part-2/
http://www.mssqltips.com/sqlservertip/3175/handle-conversion-between-time-zones-in-sql-server--part-3/

A real live example, in the meantime

Let's say you have a very simple fact table. The only fact I care about in this case is the event time, but I'll add a meaningless GUID just to make the table wide enough to care about. Again, to be explicit, the fact table stores events in UTC time and UTC time only. I've even suffixed the column with _UTC so there is no confusion.

CREATE TABLE dbo.Fact
(
  EventTime_UTC DATETIME NOT NULL,
  Filler UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()
);
GO

CREATE CLUSTERED INDEX x ON dbo.Fact(EventTime_UTC);
GO

Now, let's load our fact table with 10,000,000 rows - representing every 3 seconds (1,200 rows per hour) from 2013-12-30 at midnight UTC until sometime after 5 AM UTC on 2014-12-12. This ensures that the data straddles a year boundary, as well as DST forward and back for multiple time zones. This looks really scary, but took ~9 seconds on my system. Table should end up being about 325 MB.

;WITH x(c) AS
(
  SELECT TOP (10000000) DATEADD(SECOND,
    3*(ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1),
    '20131230')
  FROM sys.all_columns AS s1
  CROSS JOIN sys.all_columns AS s2
  ORDER BY s1.[object_id]
)
INSERT dbo.Fact WITH (TABLOCKX) (EventTime_UTC)
  SELECT c FROM x;

And just to show what a typical seek query will look like against this 10MM row table, if I run this query:

SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, EventTime_UTC), 0),
  COUNT(*)
FROM dbo.Fact
WHERE EventTime_UTC >= '20140308'
AND EventTime_UTC < '20140311'
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, EventTime_UTC), 0);

I get this plan, and it returns in 25 milliseconds*, doing 358 reads, to return 72 hourly totals:

* Duration as measured by the free SentryOne Plan Explorer, which discards results, so this does not include network transfer time of the data, rendering, etc.

It takes a little longer, obviously, if I make my range too large - a month of data takes 258ms, two months takes over 500ms, and so on. Parallelism may kick in:

This is where you start thinking about other, better solutions to satisfy reporting queries, and it has nothing to do with what time zone your output will display. I won't get into that, I just want to demonstrate that time zone conversion is not really going to make your reporting queries suck all that much more, and they may already suck if you are getting large ranges that aren't supported by proper indexes. I'm going to stick to small date ranges to show that the logic is correct, and let you worry about making sure your range-based reporting queries perform adequately, with or without time zone conversions.

Okay, now we need tables to store our time zones (with offsets, in minutes, since not everybody is even hours off UTC) and DST change dates for each supported year. For simplicity, I'm only going to enter a few time zones and a single year to match the data above.

CREATE TABLE dbo.TimeZones
(
  TimeZoneID TINYINT    NOT NULL PRIMARY KEY,
  Name       VARCHAR(9) NOT NULL,
  Offset     SMALLINT   NOT NULL, -- minutes
  DSTName    VARCHAR(9) NOT NULL,
  DSTOffset  SMALLINT   NOT NULL  -- minutes
);

Included a few time zones for variety, some with half hour offsets, some that don't observe DST. Note that Australia, in southern hemisphere observes DST during our winter, so their clocks go back in April and forward in October. (The above table flips the names, but I'm not sure how to make this any less confusing for southern hemisphere time zones.)

INSERT dbo.TimeZones VALUES
(1, 'UTC',     0, 'UTC',     0),
(2, 'GMT',     0, 'BST',    60),
     -- London = UTC in winter, +1 in summer
(3, 'EST',  -300, 'EDT',  -240),
     -- East coast US (-5 h in winter, -4 in summer)
(4, 'ACDT',  630, 'ACST',  570),
     -- Adelaide (Australia) +10.5 h Oct - Apr, +9.5 Apr - Oct
(5, 'ACST',  570, 'ACST',  570);
     -- Darwin (Australia) +9.5 h year round

Now, a calendar table to know when TZs change. I'm only going to insert rows of interest (each time zone above, and only DST changes for 2014). For ease of calculations back and forth, I store both the moment in UTC where a time zone changes, and the same moment in local time. For time zones that don't observe DST, it's standard all year long, and DST "starts" on January 1.

CREATE TABLE dbo.Calendar
(
  TimeZoneID    TINYINT NOT NULL FOREIGN KEY
                REFERENCES dbo.TimeZones(TimeZoneID),
  [Year]        SMALLDATETIME NOT NULL,
  UTCDSTStart   SMALLDATETIME NOT NULL,
  UTCDSTEnd     SMALLDATETIME NOT NULL,
  LocalDSTStart SMALLDATETIME NOT NULL,
  LocalDSTEnd   SMALLDATETIME NOT NULL,
  PRIMARY KEY (TimeZoneID, [Year])
);

You can definitely populate this with algorithms (and the upcoming tip series uses some clever set-based techniques, if I do say so myself), rather than loop, populate manually, what have you. For this answer I decided to just manually populate one year for the five time zones, and I'm not going to bother any fancy tricks.

INSERT dbo.Calendar VALUES
(1, '20140101', '20140101 00:00','20150101 00:00','20140101 00:00','20150101 00:00'),
(2, '20140101', '20140330 01:00','20141026 00:00','20140330 02:00','20141026 01:00'),
(3, '20140101', '20140309 07:00','20141102 06:00','20140309 03:00','20141102 01:00'),
(4, '20140101', '20140405 16:30','20141004 16:30','20140406 03:00','20141005 02:00'),
(5, '20140101', '20140101 00:00','20150101 00:00','20140101 00:00','20150101 00:00');

Okay, so we have our fact data, and our "dimension" tables (I cringe when I say that), so what's the logic? Well, I presume you're going to have users select their time zone and enter the date range for the query. I will also assume that the date range will be full days in their own timezone; no partial days, never mind partial hours. So they will pass in a start date, an end date, and a TimeZoneID. From there we will use a scalar function to convert the start/end date from that time zone to UTC, which will allow us to filter the data based on the UTC range. Once we've done that, and performed our aggregations on it, we can then apply the conversion of the grouped times back to the source time zone, before displaying to the user.

The scalar UDF:

CREATE FUNCTION dbo.ConvertToUTC
(
  @Source   SMALLDATETIME,
  @SourceTZ TINYINT
)
RETURNS SMALLDATETIME
WITH SCHEMABINDING
AS
BEGIN
  RETURN
  (
    SELECT DATEADD(MINUTE, -CASE
        WHEN @Source >= src.LocalDSTStart
         AND @Source < src.LocalDSTEnd THEN t.DSTOffset
        WHEN @Source >= DATEADD(HOUR,-1,src.LocalDSTStart)
         AND @Source < src.LocalDSTStart THEN NULL
        ELSE t.Offset END, @Source)
    FROM dbo.Calendar AS src
    INNER JOIN dbo.TimeZones AS t
    ON src.TimeZoneID = t.TimeZoneID
    WHERE src.TimeZoneID = @SourceTZ
      AND t.TimeZoneID = @SourceTZ
      AND DATEADD(MINUTE,t.Offset,@Source) >= src.[Year]
      AND DATEADD(MINUTE,t.Offset,@Source) < DATEADD(YEAR, 1, src.[Year])
  );
END
GO

And the table-valued function:

CREATE FUNCTION dbo.ConvertFromUTC
(
  @Source   SMALLDATETIME,
  @SourceTZ TINYINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
 RETURN
 (
  SELECT
     [Target] = DATEADD(MINUTE, CASE
       WHEN @Source >= trg.UTCDSTStart
        AND @Source < trg.UTCDSTEnd THEN tz.DSTOffset
       ELSE tz.Offset END, @Source)
  FROM dbo.Calendar AS trg
  INNER JOIN dbo.TimeZones AS tz
  ON trg.TimeZoneID = tz.TimeZoneID
  WHERE trg.TimeZoneID = @SourceTZ
  AND tz.TimeZoneID = @SourceTZ
  AND @Source >= trg.[Year]
  AND @Source < DATEADD(YEAR, 1, trg.[Year])
);

And a procedure that uses it (edit: updated to handle 30-minute offset grouping):

CREATE PROCEDURE dbo.ReportOnDateRange
  @Start      SMALLDATETIME, -- whole dates only please!
  @End        SMALLDATETIME, -- whole dates only please!
  @TimeZoneID TINYINT
AS
BEGIN
  SET NOCOUNT ON;

  SELECT @Start = dbo.ConvertToUTC(@Start, @TimeZoneID),
         @End   = dbo.ConvertToUTC(@End,   @TimeZoneID);

  ;WITH x(t,c) AS
  (
    SELECT DATEDIFF(MINUTE, @Start, EventTime_UTC)/60,
      COUNT(*)
    FROM dbo.Fact
    WHERE EventTime_UTC >= @Start
      AND EventTime_UTC <  DATEADD(DAY, 1, @End)
    GROUP BY DATEDIFF(MINUTE, @Start, EventTime_UTC)/60
  )
  SELECT
    UTC = DATEADD(MINUTE, x.t*60, @Start),
    [Local] = y.[Target],
    [RowCount] = x.c
  FROM x OUTER APPLY
    dbo.ConvertFromUTC(DATEADD(MINUTE, x.t*60, @Start), @TimeZoneID) AS y
  ORDER BY UTC;
END
GO

(You may want to have a go at short circuiting there, or a separate stored procedure, in the event that the user wants reporting in UTC - obviously translating to and from UTC is going to be wasteful busy work.)

Sample call:

EXEC dbo.ReportOnDateRange
  @Start      = '20140308',
  @End        = '20140311',
  @TimeZoneID = 3;

Returns in 41ms*, and generates this plan:

* Again, with discarded results.

For 2 months, it returns in 507ms, and the plan is identical other than rowcounts:

While slightly more complex and increasing run time a little bit, I am fairly confident that this type of approach will work out much, much better than the bridge table approach. And this is an off-the cuff example for a dba.se answer; I'm sure my logic and efficiency could be improved by folks much smarter than me.

You can peruse the data to see the edge cases I talk about - no row of output for the hour where clocks roll forward, two rows for the hour where they rolled back (and that hour happened twice). You can also play with bad values; if you pass in 20140309 02:30 Eastern time, for example, it's not going to work too well.

I might not have all of the assumptions right about how your reporting will work, so you may have to make some adjustments. But I think this covers the basics.


SSIS - How do I execute a single OLE DB Command for mutiple paths? I have an SSIS package (2008 R2) that I'm working on and I find myself duplicating the same OLE DB commands repeatedly and whenever there is a change its a nightmare to go back through and update all the duplicate OLE DB command tasks.

Is there a way to execute a single OLE DB command for multiple paths?

I don't think your reason justifies using this solution (sisyphean labor is part of a programmer's life...)
It is not efficient and i would not recommend it in your situation.
If, however, your aim was to perform one logical operation for several streams, and your streams have similiar data structure... , there is a way to do it.

Your best option is to use the Union All Data Flow Transformation component.
You can map the overlapping input columns to the same output columns, or map them to different columns for easier split in the next step if necessary.
You can also add a derived column for each of the inputs befor the union, which will hold an input identifier, again for the split to come.

There is another way to do it, using a Script Component (one for each path) instead of the OLE DB Command and passing a variable to it.
The variable holds the command to execute, and is initialized in the control flow once.
I find it too cumbersome, maybe because I'm just a DBA (not programmer) and lack scripting knowledge...


How does MySQL store data, can I detach a mysql Database? I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to the actual database size. They can compressed and considerably reduced in size, but things seems to be different in MySQL.

I look up the files of MySQL (InnoDB) where they are stored in /var/lib/mysql/ folder. There is files for each database (I can't share that) and there are these three files which seems to contain data

-rw-rw---- 1 mysql mysql 50M Feb 24 16:17 ibdata1
-rw-rw---- 1 mysql mysql 48M Feb 24 16:17 ib_logfile0
-rw-rw---- 1 mysql mysql 48M Feb 22 23:49 ib_logfile1

First I am not sure if all the database data is kept in one file, that ibdata1, second some of my database are large (1.4 GB one one of them) but these files are very small. I am wondering how data is maintained in MySQL.

Here is my query to find my database size Engine wise

SELECT Engine "DB Engine",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY engine;

And the result

+--------------------+---------------+
| DB Engine          | DB Size in MB |
+--------------------+---------------+
| NULL               |          NULL |
| CSV                |           0.0 |
| InnoDB             |        3695.8 |
| MEMORY             |           0.0 |
| MyISAM             |        3120.5 |
| PERFORMANCE_SCHEMA |           0.0 |
+--------------------+---------------+

Obviously my database a lot larger than the datafiles that stores that data. On a side note, I also want to know if I can detach a database in mySQL just like in SQL Server. Part of the question is because I want to optimize my database, remove the ones that are no longer used. Also currently there is only 30GB on this drive and I want to free up some space too. Note I have additional drives on my Linux (centos) server as well (extra). Thanks

1.4GB is a tiny database.

To remove ones that are not of use any more, back them up with mysqldump and DROP DATABASE.

On files:
 - ib_log files are
   redologs
 - ibdata1 is a system
   tablespace.


How to get max of combined columns? I have a table with the following columns:
filename, filetype, version and revision.

I want to code a query that will find the highest version AND revision for a filename and filetype. For example, the following will work:

select
 filename
,filetype
,max(version + revision) as combined
from dmkr_asline.cmires_lbyi
group by filename, filetype
order by filename, filetype;

However, I want to be able to return version and revision as separate columns so I can join another table. I cannot use max on each column because I want the max revision of the max version.

Using top with ties with row_number() to get the row with the highest revision for the highest version for each filename and filetype.

select top 1 with ties
    filename
  , filetype
  , version
  , revision
from dmkr_asline.cmires_lbyi
order by row_number() over (
  partition by filename, filetype
  order by version desc, revision desc
  );

Alternative using a common table expression with row_number()

;with cte as (
  select
      filename
    , filetype
    , version
    , revision
    , rn = row_number() over (
        partition by filename, filetype
        order by version desc, revision desc
        )
  from dmkr_asline.cmires_lbyi
)
select
      filename
    , filetype
    , version
    , revision
from cte
where rn = 1
order by filename, filetype

cross apply() version:

select distinct
    t.filename
  , t.filetype
  , x.version
  , x.revision
from dmkr_asline.cmires_lbyi t
  cross apply (
    select top 1
        i.version
      , i.revision
    from dmkr_asline.cmires_lbyi as i
    where i.filename = t.filename
      and i.filetype = t.filetype
    order by i.version desc, i.revision desc
    ) as x

inner join version:

select
    t.filename
  , t.filetype
  , x.version
  , revision = max(t.revision)
from dmkr_asline.cmires_lbyi t
  inner join (
    select
        i.filename
      , i.filetype
      , version = max(i.version)
    from dmkr_asline.cmires_lbyi i
    group by
        i.filename
      , i.filetype
    ) as x
    on x.filename = t.filename
   and x.filetype = t.filetype
   and x.version  = t.version
group by
    t.filename
  , t.filetype
  , x.version


Unable to open database (Datafile corrupted) I am unable to open the database, but i am able to mount it.

When i try to open the database, getting following error

ORA-03113 - end-of-file on communication channel

I am using Oracle 12C.

On further analysis below is the trace log created using adrci

> *** 2016-05-02 09:12:27.769 DDE rules only execution for: ORA 1110
> ----- START Event Driven Actions Dump ----
> ---- END Event Driven Actions Dump ----
> ----- START DDE Actions Dump ----- Executing SYNC actions
> ----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) ----- Successfully dispatched
> ----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) ----- Executing ASYNC actions
> ----- END DDE Actions Dump (total 0 csec) ----- ORA-01110: data file 111: 'V:\DB\CST001.DBF' ORA-01114: IO error
> writing block to file 111 (block # 1) ORA-27041: unable to open file
> OSD-04002: unable to open file O/S-Error: (OS 5) Access is denied.
> Read of datafile 'V:\DB\CST001.DBF' (fno 111) header failed with
> ORA-01208 Rereading datafile 111 header failed with ORA-01208 DDE:
> Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
> ORA-01110: data file 111: 'V:\DB\CST001.DBF' ORA-27091: unable to
> queue I/O ORA-27041: unable to open file OSD-04002: unable to open
> file O/S-Error: (OS 5) Access is denied. ORA-01208: data file is an
> old version - not accessing current version error 1114 detected in
> background process ORA-01114: IO error writing block to file 111
> (block # 1) ORA-01110: data file 111: 'V:\DB\CST001.DBF'
> ORA-27091: unable to queue I/O ORA-27041: unable to open file
> OSD-04002: unable to open file O/S-Error: (OS 5) Access is denied.
> ORA-01208: data file is an old version - not accessing current version
> 2016-05-02 09:12:27.778758 :kjzduptcctx(): Notifying DIAG for crash
> event

There is no permission issue. This is windows 2012 R2 Strandard edition. Database in no archive log mode, we have only backup's taken from expdp. The data is in SSD.

Ok, so you have no backup.

You made sure it is not a permission issue.

But you have Data Pump dumps, at least that is something.

First try to recover the datafile without data loss:

startup mount
alter database datafile 'V:\DB\CST001.DBF' online;
recover datafile 'V:\DB\CST001.DBF';

Answer the prompts accordingly, if any. If this succeeds, you can open the database with:

alter database open resetlogs;

I highly doubt the above would work in your case, so below is the data loss scenario.

startup mount
alter database datafile 'V:\DB\CST001.DBF' offline drop;
alter database open;

Using OFFLINE DROP allows you to open the instance (as long as the affected datafile is not critical) without it trying to access that file. Since you can not restore and recover the datafile without backup, you can drop the tablespace (I am just guessing the name from the datafile):

drop tablespace cst including contents and datafiles;

Recreate the tablespace:

create tablespace cst datafile 'V:\DB\CST001.DBF' size ..;
alter tablespace cst add datafile 'V:\DB\CST002.DBF' ...;
...

Regrant any tablespace quotas if you had them before (check DBA_TS_QUOTAS before dropping the tablespace).

And import what you have in your Data Pump dumps.

The not so easy way would be trying to salvage data from the other datafiles in the tablespace before dropping it.


what's practical example / use case SELECT SLEEP() for DBA I don't know how to use SELECT SLEEP(). It looks like a dirty hack.

I found an interesting question How and when to use SLEEP() correctly in MySQL?. The answer is developer oriented:

  If you don't want to SELECT SLEEP(1);, you can also DO SLEEP(1); It's
  useful for those situations in procedures where you don't want to see
  output.

what are examples for DBA?

I have used it to create backups where I sleep until I can get a process ID from the processlist that is holding a database lock. I mentioned this idea back on Apr 17, 2011 in my answer to the post How can I optimize a mysqldump of a large database?

I wrote the answer to another post Jun 20, 2012 that has the actual code where I demonstrated the use of SELECT SLEEP (Safest way to perform mysqldump on a live system with active reads and writes?)

I have done things like this in the OS making physical backups using bash. I simply did the same in MySQL itself.


What are the main reasons to split a Data Warehouse into multiple databases? I'm working with a colleague who's proposed to split our 1 instance-database into about 7 databases (divided by data domain) for development and 7 identical databases for production. I get the test-production duality logic, but in what case or what advantages are there to splitting our 1, relatively straightforward database into 7 databases? Our data warehouse is only consumed/ used by ONE business intelligence application, period.

I am concerned about this direction so hopefully you can discuss the general reasons proposed for this split, and I can give you a run down of the current properties of the database.

1 database Data Warehouse: 352 GB total, 203 tables, 170 views

Proposed split:

A: 280 GB
B: 43 GB
C: 28 GB
D: 1 GB
E,F,G: < 1 GB combined

As you can see, this is already a head-scratcher in terms of proposed benefits as the storage won't even be remotely evenly split, with 80% remaining on 1 database. Apparently partitioning our db by schema isn't possible (from a hardware perspective) because we do not have Enterprise level SQL Server.

Reasons given for split:

Current db is poorly optimized, little documentation, sub-optimal datatypes, sub-optimal indices.

My rookie thoughts: Aren't these problems irrelevant to splitting the database? They are simply problems that need to be solved on their own either way.

There are 372 objects in the current database which makes it slow.

My thoughts: This hardly seems large in my opinion.

One database is harder to document and draw schema diagrams for than 7 databases (we will have views that will span multiple database).

My thoughts: .... This seems completely ridiculous to me, but maybe I'm wrong. We've already organized our data warehouse by 13 'source system' schemas.

One database will lead to more database deadlocks.

-- Isn't this problem also completely irrelevant to having multiple databases? It's my understanding that deadlocks occur at the table level (actually usually even just the row level, but eh). Even then, all our data inserts happen at midnight, all our selects downstream to the BI happen at 2 am. Having two processes update the same table is irrelevant to multiple databases, is it not (deadlock would happen either way)? Also, I personally have seen no evidence of table deadlocks occurring during normal operations.

Database technical ownership/ ownership.

It's only the two of us that work on the database. It's possible he wants to really segregate our 'fiefdoms'. Really, hasn't been an issue, but can't user permissions be determined at the schema level anyway?

What ARE valid reasons for splitting a Data Warehouse into multiple databases?

Would love to further my knowledge here about databases in general. Yes, I happen to be doing a lot of work on one with gaps in my knowledge, but well the job is what it is, what I've been thrust into. Stuff has been working great so far (knock on wood).

You are definitely on the right track! 320GB is not huge for a database, particularly a DW.

  1) Current db is poorly optimized, little documentation, sub-optimal
  datatypes, sub-optimal indices.

  My rookie thoughts: Aren't these problems irrelevant to splitting the
  database? They are simply problems that need to be solved on their own
  either way.

This is bang on the money. Splitting one large(ish) poorly organised, optimised and documented database into 7 poorly organised, optimised and documented databases is a waste of time! You need to tackle the root of the problem!

  2) There are 372 objects in the current database which makes it slow.

  My thoughts: This hardly seems large in my opinion.

Again, you are correct! 372 is positively small in terms of number of objects - many large servers have 10's of thousands. From here

  The sum of the number of all objects in a database cannot exceed
  2,147,483,647.

Your 370 divided by ~ 2E9 ~= 1.7E-7 - so no worries on that score! :-)

  3) One database is harder to document and draw schema diagrams for than 7 databases (we will have views that will span multiple
  database).

  My thoughts: .... This seems completely ridiculous to me, but maybe
  I'm wrong. We've already organized our data warehouse by 13 'source
  system' schemas.

Again, you're correct. If there are 372 entities with inter-relationships between them, you'll need to document and diagram them. It's going to have an inherent degree of complexity. What you can do is try to split your overall system into subsystems and document them and then try and fit them into the bigger picture - great oaks from little acorns grow!

  4) One database will lead to more database deadlocks.

  -- Isn't this problem also completely irrelevant to having multiple databases? It's my understanding that deadlocks occur at the table
  level (actually usually even just the row level, but eh). Even then,
  all our data inserts happen at midnight, all our selects downstream to
  the BI happen at 2 am. Having two processes update the same table is
  irrelevant to multiple databases, is it not (deadlock would happen
  either way)? Also, I personally have seen no evidence of table
  deadlocks occurring during normal operations.

What you will lose in the multiple database scenario is ACID transactions within the same schema - OK, you can have 2-phase commit, but it's not as robust as transactions within the same schema (IMHO). I'm not sure of a valid reason to hive off tables if they're necessary for your requirements.

You appear to be talking about writes blocking reads? Well, you also appear to have a batch process at midnight followed by a querying process at 02:00? If you can make transactions/tables read only, this will take some load off the server engine as it is processing your data. Only you can tell if this can be applied to your scenario!

  5) Database technical ownership/ ownership.

  It's only the two of us that work on the database. It's possible he
  wants to really segregate our 'fiefdoms'. Really, hasn't been an
  issue, but can't user permissions be determined at the schema level
  anyway?

Certainly, ownership is at the table level and access can, depending on your server/version, be granted on a column and/or a row level - so the business of ownership is a complete red herring! If you are a server DBA performing a reorganisation (as opposed to simply scheduling backups and other mundane tasks), then you will need "access all areas"!

You should have a comment on every table and field in your system - you can put "ownership" (in the organisational as opposed to database sense of things) in there - commenting tables and fields is an excellent first step to documenting a system - it becomes self-documenting!

  What ARE valid reasons for splitting a Data Warehouse into multiple
  databases?

There can be many reasons - some are associated with multi-tenancy (both in terms of machine resources (CPU, RAM, HDD and Network) and client confidentiality or requirements. Have a look here and also google "database multi-tenancy" or similar.

Everyone says it, but it is a struggle - "documentation is very important"! As a first step, document your tables and fields in the comments. Produce ERD diagrams for all of your subsystems. Don't let anything new into the system without these steps being implemented. Best of luck in your new role!


For accessing Oracle 12c RAC database by JDBC to ensure high availability, should I use TAF or FCF? The case I encounter is that if one of the database on RAC array is suddenly down, the transaction is rolled back and connections between JBoss and Database are broken.

What I want is to preserve the JDBC connections and redirect them to another oracle database on the array. Also, can transactions preserved?

In this case, should I use TAF or FCF?

Besides, if TAF is suggested to be used, is it true that thick driver has to be used in JBoss?

In this case, should I use TAF or FCF?

TAF- Transparent Application Failover is a database session-level connection failover mechanism and works only for OCI clients.

FCF- Fast Connection Failover is a application-level failover mechanism. It depends on the implicit connection cache feature and implicit connection caching must be enabled.

TAF would be very useful for long running sessions whereas FCF is useful for pooled connections.

  What I want is to preserve the JDBC connections and redirect them to another oracle database on the array. Also, can transactions preserved?

TAF will fail over standard SQL SELECT statements that have been caught during a node crash in an in-flight transaction failure. Transactions involving INSERT, UPDATE, or DELETE statements are not supported by TAF.

  Besides, if TAF is suggested to be used, is it true that thick driver has to be used in JBoss?

Yes.

Reference:
Client Failover Best Practices - Oracle
Understanding How Fast Connection Failover Works
Comparison of TAF and Fast Connection Failover


mysql, select and sorting by group key + with first row that has that key in another column I'm trying to make a select that outputs everything, but is sorted by a group column with a "header column" that matches said group column on another column.

I have no idea how this type of query should be written.  For example, I want the data sorted like this.

id,  unit,  group, col2,  col3,  col4,  col5
---------------------------------------------
99,  ZZX,   null,  test1, test7, test5, test
6,   AAA-B, ZZZ,   test1, test7, test5, test8
77,  AAA-C, ZZX,   test1, test7, test5, test9
101, ZZZ,   null,  test1, test7, test5, test
122, AAB-A, ZZZ,   test1, test7, test5, test11
176, AAB-B, ZZZ,   test1, test7, test5, test12

So basically, the unit should be sorted per each group key, but begins with the row that matches the group key. There are also rows with null for group, but are just header rows. You can even just tell me the high level, and I can write the query, I'm just not sure the best way to approach it.

It was fairly brutal to figure out this answer, but i think a left outer join on the same table matching group to unit column worked. I also had to deal with group column being possibly empty or null.

SELECT
a.id,
a.unit,
a.`group`,
a.col2,
a.col3,
a.col4,
a.col5
FROM tablename AS a
LEFT OUTER JOIN tablename AS b
  ON nullif(a.`group`, '') <=> a.unit
ORDER BY COALESCE(b.unit, a.unit);


Does SQL Server handle databases on SMB shares different than on Hard Drives? Does it, for example, automatically recover from network hiccups, perhaps? If so, would masking the file share under a symbolic link change that behavior, or does SQL support symlinks to file shares, too?

As far as I know SQL server does nothing extra when using databases on a network share. The network link between it and the machine hosting the share going down would most likely be treated the same way as a disk error: the affected databases would be dropped into a "suspect" state until someone takes restorative action.

Recent improvements in the OS fileshare layer (i.e. in Windows, not SQL Server directly) with the introduction of SMB3.0 and related updates make problems less frequent and less severe though, assuming you are using Windows 2012 on both the SQL box(es) and the one(s) hosting the file share(s). See here and here for some more detailed relevant discussion.


Check if postgresql database exists (case insensitive way) Is there a "elegant built-in" case-insensitive way to check if db is exists?

I've found only SELECT datname FROM pg_catalog.pg_database WHERE datname='dbname', but this is a CS check.
The first thing that comes to mind to retrieve all db names and filter them by hand, but I think there is more elegant way to do it.

You can use:

SELECT datname FROM pg_catalog.pg_database WHERE lower(datname) = lower('dbname');

... however, database names are case sensitive, so attempting to connect to the DB name won't actually work unless you match the case correctly. This won't use any indexes on dbname, but it's rather unlikely that you'd have enough databases for this to matter.

Folks don't realize that because PostgreSQL case-folds unquoted identifiers to lower-case, so most of the time it looks case-insensitive for clients. In fact, "DatabaseName" and "databasename" are different things.


Correlated subquery to find total larger than state average I have tables Vendors (VendorName, VendorState,....) and Invoices (InvoiceID, InvoiceTotal,...). I want to get the Invoices (as InvoiceId) that are larger than the Avg state InvoiceTotal.

I know I first find the avg total per state:

SELECT  VendorState, Avg(InvoiceTotal) AS AvgStateInvoice
from Invoices I join Vendors V on V.VendorID= I.VendorID
group by VendorState

So I now have the list of avg InvoiceTotal by state. Now I need to figure out:

How to do an outer query to select those invoices larger than the state average and here is where I am lost, since I don't remember the syntax to do the comparison . I guess it would be something of the sort:

SELECT InvoiceId from Invoices where InvoiceTotal > .....?

Any ideas, please?

You can do it like this:

select *
from
    dbo.Invoice I1
    join dbo.Vendors V1 on V1.VendorID = I1.VendorID
where
    I1.InvoiceTotal > (
        SELECT
            Avg(I2.InvoiceTotal)
        from
            dbo.Invoices I2
            join dbo.Vendors V2 on V2.VendorID = I2.VendorID
        where
            V1.VendorState = V2.VendorState
    );

Or using window functions, which may be faster since it doesn't need an extra join:

SELECT X.*
from (
    select
        *,
        Avg(I.InvoiceTotal) over (partition by V.VendorState) as AvgInv
    from
        dbo.Invoices I
        join dbo.Vendors V on V.VendorID = I.VendorID
) X
where
    X.InvoiceTotal > X.AvgInv;

The window function option might not be faster. Though it saves a join in the query, the execution plan will feature a subexpression spool (with two extra joins). The table spool is needed to compute and save the average for the current partition. The spooled result is replayed once per partition.


Designing a user table, FName LName v.s Username I did some research on different ways of getting user's name. Some database designers prefer to have a username that contains first name and last name with space separator, and some other designers have it in two separate fields such as fname and lname. When it comes to searching using mySql, which approach makes it easier to search for a user based on his full name, or only either his last name or first name ? Any advice on the design approach ? My application is probably going to have about 5,000,000 users registered, I'm a bit concerned about in what way to approach user full name.

This is how I'm doing it, assuming that the user enter "alex coner" in one input textbox and press enter, then I parse the string based on space separation and then run the query like this which doesn't know which one is fname and lname :

select * from User
where UserFName in ("alex", "coner")
   OR UserLName in ("alex", "coner")

is there a way to run this and then get the one that matches the closest ? Assuming there is these users as below:

alex buche
coner johnson
alex coner
david rudd

and the result appears as :

alex coner
alex buche
coner johnson

First of all full text searches are often slower than searching on indexed columns.  But full text searches offer some semantics that column searches do not.  However, full text search is primarily valuable in larger sets of text, such as indexing documents, or when the likely order of the text is fluid. (e.g. John Johnson; Johnson, John; John, Johnson; etc)

In this constrained task of matching names, I would choose the two columns, both indexed, as likely the better performing option.  (But testing is the only way to know for sure.)

If you are using standard indexed columns, then you can quickly search the fname column and/or the lname column and when needed join them on a matching identifier. This is more accurate than a full text search, because the search is very bounded.

This does require that the search query be made knowing which name is the fname and the lname, of course, or adding additional conditions to check both ways.  (Not always so simple in a multi-language world.)

The full text search syntax will support "phrases" (words in a certain order) or the softer version of words in any order.  In the latter case 'John Johnson' would be satisified by either or both of those names in any order.

(Of course, for the exact search syntax options see the mySql documentation.)


Postgresql: Optimize finding all distinct JSON keys from a colulmn I'm looking for a way to index / optimize finding only the keys in a JSONB column.  The use case is an ETL operation where I need to take data stored in the JSONB column and output it as delimited text with headers (the keys in this case). The tables have millions of rows, so caching or iterating over all data to discover the complete set of keys isn't practical. The library I'm using needs the headers up front.

I can do the following query which works but will be impractically slow at scale.

SELECT DISTINCT jsonb_object_keys(jsondata)
FROM foo

This query on a 200k row table takes about 3 minutes to run.  I have tables an order of magnitude larger than that.  An explain shows an obviously undesirable full table scan:

Unique  (cost=42283418.97..42372645.97 rows=198454 width=1282)
  ->  Sort  (cost=42283418.97..42328032.47 rows=19845400 width=1282)"
        Sort Key: (jsonb_object_keys(jsondata))"
        ->  Seq Scan on "foo" (cost=0.00..121283.40 rows=19845400 width=1282)

Is there a way to index only the keys in a JSON/JSONB field?

You could add a trigger on insert and update of each row which walks the keys of the new value and makes sure each key is in a side-table which lists all keys in any row of the main table.

Making sure that a key gets atomically deleted from the side-table when its last entry in the main table goes away would be much harder to do efficiently.

The GIN index created with the jsonb_ops operator class does contain the data you want, but as far as I know it provides no way for you to extract that info.  (It also has the same problem with atomic deletes).


MySQL - Small table popping up in slowlog, refuses to use index So I have two very very small tables (~20 rows) that are hit very frequently and pop up in the slowlog (I'm also logging tables not using indexes).

I tested using a large multipart index (that's just all the columns being selected) on this query:

SELECT type_templates.name, type_templates.is_default, type_templates.created_at, type_templates.updated_at, type_templates.id FROM type_templates;

However the query is still being logged (the QEP in this case shows an index hit):

+----+-------------+----------------+-------+---------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table          | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------+-------+---------------+------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | type_templates | index | NULL          | idx_typetmpl_mp1 | 785     | NULL |   13 |   100.00 | Using index |
+----+-------------+----------------+-------+---------------+------------------+---------+------+------+----------+-------------+

Now, I understand 100% that the sequential scan is probably faster, but I think the problem I'm having is...  why in the world are these being logged?

By default, MySQL logs ALL queries not using an index, but you can change that by setting a nonzero value for min_examined_row_limit - set it to 100 and these tables won't pollute your log anymore.


MySQL MyIsam Compressing tables Im packing big MyISAM tables with myisampack and came up with the following issue. (MariaDB 10.1.14 64bit windows)
Indexes are rebuilt after packing the tablewhile running myisamchk -rq, so, it doesnt make sense to create/have them beforehand. After compressing a table without indexes and trying to recreate all indexes at once, i've noted that the table is uncompressed back.

Is there a way which i could modify the frm in a way i could rebuild the indexes without uncompressing the table?

Does MySQL use the uncompressed index in order to build the compressed index?

Thanks!

Alex

I will try to :

Create empty table with indexes
DISABLE KEYS
Load Data
myisampack table
myisamchk -rq

Hopefully myisamchk will build the indexes without ENABLE KEYS


postgres 9.6 GRANT requirements for stored procedure I have a role that will execute a stored procedure (function) to update a table.
I give the user acccess to the role, and the role execute on the stored procedure.
Do I also have to give the role update on the table?
Thanks

You have three options.  You can grant update on the table to the role.  You can grant update on the table to the user.  Or you can do neither of those and create the function with the "SECURITY DEFINER" option.

In the last case, then the user will be able to update the table via the function, but will not be able to update the table outside of the function.


Why is % forbidden char in THROW message? Simple question:

THROW 50000, 'Because of this ->%<- is this message invisible.', 1;

you can't pass %s, %d parameters as in RAISERROR
you have to use FORMATMESSAGE
but THROW failed anyway if % is not doubled (correct displaying)

Tested on Microsoft SQL Server 2012 (SP1) 11.0.3381.0 (X64), 11.0.3000.0 (X64)

Real world example:

DECLARE @ProcParam VARCHAR(30) = 'Hehe%x!';
DECLARE @Message VARCHAR(500) = FORMATMESSAGE('Procedure input %s is wrong', @ProcParam);
PRINT 'Message: ' + @Message;
THROW 50000, @Message, 1;

It appears that they left the printf functionality (or at least part of it) in the THROW command but did not include a way to pass in a parameter to it.  I tried each of the characters listed in BOL for RAISEERROR as possible "types" %(d, i, o, s, x, X, or u) and each of them gave back a blank.  Any other character after the % and you get back a valid string.  My guess is that they are replacing the value with a NULL which of course causes any string concatenation to return a NULL and when printed looks blank.

Unfortunately without the code behind it (or at least a MS Developer) to confirm there is no way to be certain.


How to do differential backups in a SQL Server RDS instance How to do differential backups in SQL Server rds instance?

From https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/

  To set up a native backup of the SQL Server database, use the
  following services:

  An Amazon S3 bucket to store your backup files
  An AWS Identity and Access Management (IAM) role to access the bucket
  The SQLSERVER_BACKUP_RESTORE option added to an option group on the DB
  instance

  exec msdb.dbo.rds_backup_database  @source_db_name='database_name',
  @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',
  @overwrite_S3_backup_file=1;

After that you can use similar command to create diff backup:

exec msdb.dbo.rds_backup_database
@source_db_name='database_name', @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/diff_file_name_and_extension',
@overwrite_S3_backup_file=1,
@type = 'DIFFERENTIAL';