xenforo vulnerability, limit max open connections by ip address Trying to get some emergency help because my website is being attacked. It isn't as much as an actual attack, as it is a small bot to exploit xenforo vulnerability.

https://xenforo.com/community/threads/mysql-attack.89082

That's a link to the original problem. I thought this was the best place to ask so I will ask here as well.
Here is proof that this is an actual attack.

This is the error that the website gives me

Zend_Db_Adapter_Mysqli_Exception: Too many connections - library/Zend/Db/Adapter/Mysqli.php:333
Generated By: Unknown Account, Today at 7:26 AM
Stack Trace
#0 /home/squadcra/public_html/library/Zend/Db/Adapter/Abstract.php(315): Zend_Db_Adapter_Mysqli->_connect()
#1 /home/squadcra/public_html/library/XenForo/Application.php(719): Zend_Db_Adapter_Abstract->getConnection()
#2 [internal function]: XenForo_Application->loadDb(Object(Zend_Config))
#3 /home/squadcra/public_html/library/XenForo/Application.php(960): call_user_func_array(Array, Array)
#4 /home/squadcra/public_html/library/XenForo/Application.php(991): XenForo_Application->lazyLoad('db', NULL)
#5 /home/squadcra/public_html/library/XenForo/Application.php(1561): XenForo_Application::get('db')
#6 /home/squadcra/public_html/library/XenForo/Model.php(161): XenForo_Application::getDb()
#7 /home/squadcra/public_html/library/XenForo/Model/DataRegistry.php(138): XenForo_Model->_getDb()
#8 /home/squadcra/public_html/library/XenForo/Model/DataRegistry.php(97): XenForo_Model_DataRegistry->_getMultiFromDb(Array)
#9 /home/squadcra/public_html/library/XenForo/Dependencies/Abstract.php(147): XenForo_Model_DataRegistry->getMulti(Array)
#10 /home/squadcra/public_html/library/XenForo/FrontController.php(127): XenForo_Dependencies_Abstract->preLoadData()
#11 /home/squadcra/public_html/index.php(13): XenForo_FrontController->run()
#12 {main}
Request State
array(3) {
["url"] => string(22) "http://squadcraft.net/"
["_GET"] => array(0) {
}
["_POST"] => array(0) {
}
}

I think this is an exploit where he has a bot that spam opens mysql connections, or just opens and holds open mysql connections. I don't know I'm not an expert. Anyway I need to limit the maximum number of open connections per ip address.

I have an old post from March 2012 that limits a specific user's connections by the hour

How can I limit MySQL connections?

You also need to remove anonymous users : Cannot drop anonymous user from mysql.user

Get rid of test user access : MySQL : Why are there "test" entries in mysql.db?

Get rid of all users with no password with

DELETE FROM mysql.user WHERE password='';
FLUSH PRIVILEGES;

You could add a blanket rule on a specific IP addr (like 10.20.30.40)

INSERT INTO mysql.user SET
user='',host='10.20.30.40',
max_user_connections = 1,
max_connections = 1;
FLUSH PRIVILEGES;

Give Them a Try !!!


To what extent do the "non-durability features" affect COPY's performance? COPY is documented as not being WAL-logged if it's loaded on the same transactions..

  In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7). Operations in which this optimization can be applied include: CREATE TABLE AS,
  CREATE INDEX,
  CLUSTER,
  COPY into tables that were created or truncated in the same transaction

With that said, do any of the non-durability features speed up a pg_restore -1?

  -1 --single-transaction Execute the restore as a single transaction (that is, wrap the emitted commands in BEGIN/COMMIT). This ensures that either all the commands complete successfully, or no changes are applied. This option implies --exit-on-error.

COPY is a single transaction, will synchronous_commit do anything? Or does that only speed up batch-INSERTS?
Does making the initial table UNLOGGED matter if COPY ignores the WAL log anyway?
What about max_wal_size and checkpoint_timeout? I assume they would do nothing without WAL-logging?
To what extent does turning off fsync do anything other than playing with fire? Are we likely to see a speed up by turning it off?

question inspired by @Craig Ringer's answer on SO

The answer you refer to is talking about bulkloading a DB, not restoring a dump.

synchronous_commit will have minimal to no effect on COPY, since there's only one commit at the end.

COPY can only reduce WAL, not ignore it, and only if wal_level = minimal. Most production systems run with wal_level = hot_standby (in pg10, wal_level = replica) or higher. UNLOGGED tables absolutely do benefit COPY at higher wal_level, but only if you do not later plan on making the table logged. If you do, you're just deferring the WAL cost, since it'll be paid when you convert the UNLOGGED table to LOGGED instead of at COPY time. Use UNLOGGED tables if you can regenerate and reload the data later in case of crash and you don't need it to replicate. pg_restore will restore unlogged tables as unlogged, and logged tables as logged, so it's kind of a moot point.

Don't turn fsync off unless you really know what you're doing, or you don't care about your data. For bulk loading a new blank PostgreSQL instance, wal_level = minimal and synchronous_commit = off are probably going to be just as good. Better, even, since you avoid a whole bunch of WAL writing.

max_wal_size won't make much difference with bulk loading if wal_level = minimal. If you're using a higher WAL level you do need to tune checkpoints for best results. checkpoint_timeout doesn't really matter since it generally only comes into play on idle-ish systems.

So if you're using pg_restore to a system configured for physical replication (hot standby/warm standby/archive PITR) you've got to pay the costs for that, and there's not much you can do to speed things up on top of what COPY already does. pg_restore uses COPY.


postgresql updating integer column taking more space than expected I have two tables, A(a1 serial, a2 varchar(8)) and B(b2 varchar(8));

My query looks like,

alter table B add column b1 integer;
update B t1 set b1 = t2.a1 from (select b1, b2 from B) t2 where t1.a2 = t2.b2;

Here's my problem:

B was originally around 46 GB and had 6*10^7 entries. According to the documentation integers take up 8 bytes. Thus, 4 * 6 * 10^7 = 24 * 10^7 < 1 GB. The query is still executing. When I log in from another console and do

select pg_size_pretty(pg_relation_size('B'));

I see 60 GB. I'm expecting the size to be more than this when the query ends.

I'm confused by this hike in size. Is it because of the temporary data structures that it creates? If so could I expect the size to go down if i use VACUUM?

I'm using postgres 9.1.

Well, you are running this giant UPDATE statement of all rows in table B:

update B t1 set b1 = t2.a1 from (select b1, b2 from B) t2 where t1.a2 = t2.b2;

which will more-or-less double the size of the table, since the UPDATE has to keep old row versions around. You may want to read up a bit on how PostgreSQL implements MVCC and how vacuuming works, but to answer this question:

  If so could I expect the size to go down if i use VACUUM?

The space utilized by the table should go down if you run a VACUUM FULL or CLUSTER; probably a VACUUM alone will not be sufficient to immediately reclaim space.


Batch for PostgreSQL query 1) The pgpass.conf (of PostgreSQL) is:

localhost:5432:*:postgres:mypassword

I have no experience with batch and I have spent the day searching in the net.
I'm using Windows - if that matters.

2) I tried with a file.bat as:

cd C:
start "" "C:\Program Files\PostgreSQL\9.4\bin\psql.exe" "C:\Users\utente\Desktop\1Giswater impNode.sql"
pause
start "" "C:\Program Files\PostgreSQL\9.4\bin\psql.exe" "C:\Users\utente\Desktop\2Giswater impArc.sql"
pause
start "" "C:\Program Files\PostgreSQL\9.4\bin\psql.exe" "C:\Users\utente\Desktop\3assegnazione enet.sql"
pause

After typing my password the result is:

3) I've written a simple file.bat, which is:

cd C:\Program Files\PostgreSQL\9.4\bin\
pause
rem psql --help
pause
psql   -e -a -f="C:\Users\utente\Desktop\1Giswater impNode.sql"   -d=postgres  -U=postgres

The result is:

What am I missing?

You are passing the username incorrectly:

The "short" parameter names don't use the = sign:

So it should either be:

psql -e -a -f"C:\Users\utente\Desktop\1Giswater impNode.sql" -dpostgres  -Upostgres

or, when using the long parameter names:

psql -e -a --file="C:\Users\utente\Desktop\1Giswater impNode.sql" --dbname=postgres  --username=postgres


innodb_thread_concurrency vs slave_parallel_workers I am new in rds, but I am facing server performance issue. I got these two variable but I am confused what exactly is difference between      innodb_thread_concurrency and slave_parallel_workers.

These two variables have nothing to do with each other. One is at InnoDB level, the other is at SQL level (and there are many engines aside from InnoDB). They may seem similar because in the case of a very crowded server they could limit the concurrency, sacrifying if for better latency. A higher value for both values (or 0, which means unlimited, for innodb_thread_concurrency) will often result in a higher number of threads running on a particular node. This is then only thing where I can think they can relate.

innodb_thread_concurrency is used by the innodb scheduler to change the number of active foreground threads (the ones retrieving your data from the buffer pool or writing to it an committing to the transaction log). You can read a more in depth explanation about the InnoDB scheduler here: MySQL Performance Blog. Please note that this blog post is ancient, mysql concurrency has improved a lot in 5.6 and 5.7, and my recommendation is to leave this variable as 0 and, if necessary, control the concurrency at used level with a pool of connections.

slave_parallel_workers is a replication slave-only thing. It tells how many sql threads should be executing at the same time trying to apply in parallel the pending changed brought from the master with the io thread. This will not work in stable releases (5.6 series) very well unless you have a multi-tenant database, as changes will only be applied in parallel for diferent schemas with no cross-database queries (so, if you have 2 databases, concurrency will always be 2 or less).

Of course, theoretically, too many slave workers could end up producing too much innodb concurrency, but the odds of that are very low. It makes no sense, however, to setup innodb_threads_concurrency to 1 and slave_parallel_workers to 100, because you will limit your slave change application.

Typically, you will want to not touch innodb_thread_concurrency (leave it to 0, no maximum concurrency limited) and set slave_parallel_workers to the number of real different schemas you have (not having into account mysql, information_schema, etc.) and can write in parallel (useful, for example, if you had one schema per wordpress installation and lots of writes).


Fastest way to count how many dates' ranges cover each date from series I have a table (in PostgreSQL 9.4) that looks like this:

CREATE TABLE dates_ranges (kind int, start_date date, end_date date);
INSERT INTO dates_ranges VALUES
    (1, '2018-01-01', '2018-01-31'),
    (1, '2018-01-01', '2018-01-05'),
    (1, '2018-01-03', '2018-01-06'),
    (2, '2018-01-01', '2018-01-01'),
    (2, '2018-01-01', '2018-01-02'),
    (3, '2018-01-02', '2018-01-08'),
    (3, '2018-01-05', '2018-01-10');

Now I want to calculate for the given dates and for every kind, into how many rows from dates_ranges each date falls. Zeros could be possibly omitted.

Desired result:

+-------+------------+----+
|  kind | as_of_date |  n |
+-------+------------+----+
|     1 | 2018-01-01 |  2 |
|     1 | 2018-01-02 |  2 |
|     1 | 2018-01-03 |  3 |
|     2 | 2018-01-01 |  2 |
|     2 | 2018-01-02 |  1 |
|     3 | 2018-01-02 |  1 |
|     3 | 2018-01-03 |  1 |
+-------+------------+----+

I've come up with two solutions, one with LEFT JOIN and GROUP BY

SELECT
kind, as_of_date, COUNT(*) n
FROM
    (SELECT d::date AS as_of_date FROM generate_series('2018-01-01'::timestamp, '2018-01-03'::timestamp, '1 day') d) dates
LEFT JOIN
    dates_ranges ON dates.as_of_date BETWEEN start_date AND end_date
GROUP BY 1,2 ORDER BY 1,2

and one with LATERAL, which is slightly faster:

SELECT
    kind, as_of_date, n
FROM
    (SELECT d::date AS as_of_date FROM generate_series('2018-01-01'::timestamp, '2018-01-03'::timestamp, '1 day') d) dates,
LATERAL
    (SELECT kind, COUNT(*) AS n FROM dates_ranges WHERE dates.as_of_date BETWEEN start_date AND end_date GROUP BY kind) ss
ORDER BY kind, as_of_date

I'm wondering is it any better way to write this query? And how to include pairs date-kind with 0 count?

In reality there is a few distinct kinds, period of up to five years (1800 dates), and ~30k rows in dates_ranges table (but it could grow significantly).

There are no indexes. To be precise in my case it's a result of subquery, but I've wanted to limit question to one issue, so it's more general.

And how to include pairs date-kind with 0 count?

Build a grid of all combinations then LATERAL join to your table, like this:

SELECT k.kind, d.as_of_date, c.n
FROM  (SELECT DISTINCT kind FROM dates_ranges) k
CROSS  JOIN (
   SELECT d::date AS as_of_date
   FROM   generate_series(timestamp '2018-01-01', timestamp '2018-01-03', interval '1 day') d
   ) d
CROSS  JOIN LATERAL (
   SELECT count(*)::int AS n
   FROM   dates_ranges
   WHERE  kind = k.kind
   AND    d.as_of_date BETWEEN start_date AND end_date
   ) c
ORDER  BY k.kind, d.as_of_date;

Should also be as fast as possible.

I had LEFT JOIN LATERAL ... on true at first, but there is an aggregate in the subquery c, so we always get a row and can use CROSS JOIN as well. No difference in performance.

If you have a table holding all relevant kinds, use that instead of generating the list with subquery k.

The cast to integer is optional. Else you get bigint.

Indexes would help, especially a multicolumn index on (kind, start_date, end_date). Since you are building on a subquery, this may or may not be possible to achieve.

Using set-returning functions like generate_series() in the SELECT list is generally not advisable in Postgres versions before 10 (unless you know exactly what you are doing). See:

What is the expected behaviour for multiple set-returning functions in select clause?

If you have lots of combinations with few or no rows, this equivalent form may be faster:

SELECT k.kind, d.as_of_date, count(dr.kind)::int AS n
FROM  (SELECT DISTINCT kind FROM dates_ranges) k
CROSS JOIN (
   SELECT d::date AS as_of_date
   FROM   generate_series(timestamp '2018-01-01', timestamp '2018-01-03', interval '1 day') d
   ) d
LEFT   JOIN dates_ranges dr ON dr.kind = k.kind
                           AND d.as_of_date BETWEEN dr.start_date AND dr.end_date
GROUP  BY 1, 2
ORDER  BY 1, 2;


All manual settings get wiped out when saved in My.cnf, why? When I open my.cnf to enter manual setting like:

innodb_buffer_pool_size = 50M
innodb_log_file_size = 15M
bulk_insert_buffer_size=25M
log-slow-queries = mysql-inja-slow.log

All of my settings get vanished when I hit Save! My server has mysql  5.1.

I solved the problem with :

chattr -i /etc/xxxx/x/x/my.cnf


Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM? I am considering scaling up instead of scaling out. Therefore, I am interested to hear whether it is feasible to utilize the MySQL in-memory storage engine for a database that is 500+ GB, given that one has a server, with this kind of memory? I should mention that the queries towards the table are mostly ad-hoc queries.

One issue I believe I have found that would arise is that queries towards the MEMORY table could cause temporary tables to get created. Temporary tables have a hard limit of utilizing up to 4 GB of RAM before they get converted to a MyISAM table. This would of course kill performance completely. The memory limit of temporary tables could be worked around by setting the tempdir to a ramdisk.

What other kinds of problems do you foresee?

The MEMORY storage engine can either be a blessing or a curse depending on

what you intend to store
how often you plan to perform DML
how much RAM you leaving for the

Database caches (MyISAM Key Cache, InnoDB Buffer Pool)
OS caches
OS operation

The MEMORY storage engine

uses full table locking for INSERTs, UPDATEs, and DELETEs
Cannot perform concurrent INSERTs
uses the hash indexes instead of BTREE indexes by default
can use BTREEs indexes, but must be explicitly specified at CREATE TABLE time
has no transaction support
Single row queries are just great against MEMORY tables, especially using HASH indexes -- Ranged queries and sequential access are just horrific unless you use BTREE explicitly (more memory consumption required)

Even though you have data in RAM, mysqld will always hit the .frm file to check for the table existing as a reference point, thus always incurring a little disk I/O. Proportionally, heavy access to a MEMORY storage engine table will have noticeable disk I/O.


select query to fetch json column internal objects I have a json  like {"disks":{"value":[{"Type":"NA"},{"Type":"NA"}]}}.

How can I write a query to fetch value in Type

You can use jsonb_array_elements() for that:

with sample_data (json_col) as (
  values ('{"disks":{"value":[{"Type":"NA"},{"Type":"NA"}]}}'::jsonb)
)
select x.t ->> 'Type' as type
from sample_data, jsonb_array_elements(json_col -> 'disks' -> 'value') as x(t);

returns

type
----
NA
NA


How to make distributor highly available in Always On Configuration We have clients requested us to configure Always on with Replication. Microsoft does not support it. "The distributor should not be a host for any of the current (or intended) replicas of the availability group that the publishing database is (or will become) a member of. " We do not want distributor to be on the same server as Publisher.
Has anyone faced this change? Do you know the limitation for not having distributor as replica of the availability group. What other options do you recommend to have high availability for distributor in transactional replication in Always on Configuration.
Thanks

Put the distributor in a virtual machine, and use the hypervisor's high availability tools. This lets you survive host hardware failure. OS problems will still cause issues, though, so for an additional level of protection, you can run clusters inside virtualization.


Lots of short term blockings on SQL Server 2008-R2 database Seeking experts advise on short term blocking which we are seeing for almost a week now.

DB is 15 TB and this Table B where we have lots of blocking - almost 300 in no is 2 TB.

While using sp_whoisactive,  I see queries getting blocked for shorter fraction of 3-4 secs and some in 9-10 secs but because these come and go almost through the day for connections over 2-3 thousand, this causes lots of errors in application.

Troubleshooting done:

Updated stats on NC index, We just have one NC and one C index.
While checking from sentry we can see that these blocked statements
are all insert statements for that table mentioned above.

Below are some metrics:

Wait stats for past 3-4 hours where blocking is till on

PAGELATCH_EX --> 51.1 % Total waits 730 513 153.5
PAGELATCH_SH  --> 23 % Total waits 343 538 769.7
THREADPOOL --> 18 % Total waits 257 222 894.4

BATCHES/sec on Avg: 8612.6
CPU and memory --> well under the threshold as we have 256 logical processors and 612 GB RAM
CHECKPOINT pages/sec --> 1706
Lazy writes --> 0
Log flushes/sec 1844.4
Transactions/sec 9074

results as per david's query: Note@ below was run 15 mins after we did node failover

 wait_type  wait_time_ms_per_sec
PAGELATCH_EX    733024.4
PAGELATCH_SH    328846.4
CPU_USED    7131.4
WRITELOG    6484.4
DBMIRROR_EVENTS_QUEUE   4973.6
DBMIRRORING_CMD 1456.6
OLEDB   990.2
TRACEWRITE  984.2
SOS_SCHEDULER_YIELD 280.6
THREADPOOL  254
ASYNC_NETWORK_IO    45.4
LATCH_EX    9.4
PAGEIOLATCH_EX  7.6
PREEMPTIVE_OS_GETPROCADDRESS    3.4
MSQL_XP 3.2
DBMIRROR_SEND   1.4
PREEMPTIVE_OS_DELETESECURITYCONTEXT 1
PAGELATCH_UP    0.4
PREEMPTIVE_OS_WAITFORSINGLEOBJECT   0.4
PREEMPTIVE_OS_AUTHORIZATIONOPS  0.2
PREEMPTIVE_OS_AUTHENTICATIONOPS 0.2
SOS_RESERVEDMEMBLOCKLIST    0.2
CMEMTHREAD  0.2

Please provide additional troubleshooting suggestions and let me know if there are any further metrics needed.

Edit@ We are using MAXDOP 8 and Cost threshold of par'sm is set default-5

Update: Did not made any changes as such and when we saw drop in connections during the night time, suddenly issue seem to have vanish and no blocking as checked for now. Below is the stats as of now

OLEDB   11883.8
WRITELOG    11732.2
DBMIRROR_EVENTS_QUEUE   4969.8
CPU_USED    4093.75
DBMIRRORING_CMD 1457.2
TRACEWRITE  1080.2
PAGEIOLATCH_SH  333.4
PAGELATCH_EX    237.4
PAGELATCH_SH    31.4
ASYNC_NETWORK_IO    30.2
SOS_SCHEDULER_YIELD 6
LATCH_EX    4.4
DBMIRROR_SEND   1.6
PREEMPTIVE_OS_AUTHENTICATIONOPS 1.4
MSQL_XP 1.2
PREEMPTIVE_OS_GETPROCADDRESS    1.2
PAGELATCH_UP    0.4
PREEMPTIVE_OS_DECRYPTMESSAGE    0.4
SOS_RESERVEDMEMBLOCKLIST    0.4
PREEMPTIVE_OS_DELETESECURITYCONTEXT 0.2
PREEMPTIVE_OS_WAITFORSINGLEOBJECT   0.2
CMEMTHREAD  0.2
PREEMPTIVE_OS_AUTHORIZATIONOPS  0.2
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT   0.2
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY 0.2

Not sure how and why it went as when seeing today we dont see those many connections and neither the blocking. But thats for sure it will pop up soon

Update@ Its back and looks with same waits

This:

wait_type  wait_time_ms_per_sec
PAGELATCH_EX    733024.4
PAGELATCH_SH    328846.4
CPU_USED    7131.4
WRITELOG    6484.4

Shows that you have a latch contention problem, and only a latch contention problem.

Since

  @ below was run 15 mins after we did node failover

The CPU_USED row should be accurate.  And so your workload is only managing to use 7sec of CPU time per second.  This is like only running on 7 cores.  In the mean time you have 1000sec of page latch time, meaning that you have, on average 1000 sessions waiting to latch a page, and only 7 performing useful work.

You are also running very old software on a very large server.  The throughput on this server is pretty high, suggesting that the operation it supports is important.  Also consider your username. So you should consider, strongly, engaging Microsoft Support or a partner with expertise in this sort of thing.

That said, the next step is to identify the source of your latch waits.  Generally these are either on a hot page of a table in your database, or a system page in TempDb.  The sys.dm_exec_requests.wait_resource should tell you which.

If tempdb, then Recommendations to reduce allocation contention in SQL Server tempdb.

If a table in your database, then post the DDL (including indexes) for the table and a the details of the queries that are blocking.

There is a whitepaper on diagnosing and resolving latch contention on SQL 2008 here: Diagnosing and Resolving Latch Contention on SQL Server


`t2.t1_id IN (select t1.id from t1 ...)` is slower then `t2.t1_id IN (1,2,3,...,150)` Query with subquery is much slower:

SELECT COUNT(*)
FROM `t2`
WHERE `t2`.`t1_id` IN
    (SELECT `t1`.`id`
     FROM `t1`
     WHERE `t1`.`site_id` = 5978)
  AND (t2.created_at >= '2016-02-13 08:00:00')
  AND (t2.created_at <= '2016-02-14 07:59:59');

returns 34038 in 9 seconds

subquery

SELECT `t1`.`id` FROM `t1` WHERE `t1`.`site_id` = 5978

returns 150 ids like: 1, 2, 3, ... 150

without subquery:

SELECT COUNT(*)
FROM `t2`
WHERE `t2`.`t1_id` IN (1, 2, 3, ..., 150)
  AND (t2.created_at >= '2016-02-13 08:00:00')
  AND (t2.created_at <= '2016-02-14 07:59:59');

returns 34038 in 0.1 seconds

EXPLAIN for query with subquery:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY,index_t1_on_site_id_and_created_at
          key: index_t1_on_site_id_and_created_at
      key_len: 4
          ref: const
         rows: 158
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ref
possible_keys: ...,index_t2_on_t1_id_and_and_created_at,...
          key: index_t2_on_t1_id_and_and_created_at
      key_len: 4
          ref: production.t1.id
         rows: 622
     filtered: 11.11
        Extra: Using where; Using index

EXPLAIN for query without subquery:

  *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: range
possible_keys: ...,index_t2_on_t1_id_and_and_created_at,...
          key: index_t2_on_t1_id_and_and_created_at
      key_len: 9
          ref: NULL
         rows: 68239
     filtered: 100.00
        Extra: Using where; Using index

Tables:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
...
  PRIMARY KEY (`id`),
  KEY `index_t1_on_site_id_and_created_at` (`site_id`,`created_at`)
)

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
...
  PRIMARY KEY (`id`),
...
  KEY `index_t2_on_t1_id_and_type_and_created_at` (`t1_id`,`created_at`),
)

MySQL 5.7.11 (the same behaviour in 5.6.23)

Is there any way to make the first query faster?

This should be best:

SELECT  COUNT(*)
    FROM  t1
    JOIN  `t2`  ON t2.t1_id = t1.id
    WHERE  `t1`.`site_id` = 5978)
      AND  (t2.created_at >= '2016-02-13 08:00:00')
      AND  (t2.created_at  < '2016-02-13 08:00:00' + INTERVAL 24 HOUR);

with these indexes, with the columns in the order given:

t1: INDEX(site_id, id)
t2: INDEX(created_at, t1_id)

Caveat:  If t2.t1_id = t1.id is "many:1", you will get the right answer.  If it is "many:many", you will get an inflated COUNT.

This formulation may also be efficient:

SELECT  COUNT(*)
    FROM  `t2`
    WHERE  EXISTS   ( SELECT  *
              FROM  t1
              WHERE  `id` = t2.t1_id
                AND  `site_id` = 5978
                    )
      AND  (t2.created_at >= '2016-02-13 08:00:00')
      AND  (t2.created_at  < '2016-02-13 08:00:00' + INTERVAL 24 HOUR);

with these index, with the columns in the order given:

t2: INDEX(created_at, t1_id)


Does MySQL or MariaDB support GIS KNN Index Queries (k-nearest neighbors)? Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <->

SELECT ST_AsText(geom), city
FROM person.address
ORDER BY geom <-> 'POINT(-121.626 47.8315)'
FETCH FIRST 7 ROWS ONLY;

SQL Server has a similar method,

DECLARE @g geography = 'POINT(-121.626 47.8315)';

SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address
WHERE SpatialLocation.STDistance(@g) IS NOT NULL
ORDER BY SpatialLocation.STDistance(@g);

Both PostgreSQL and SQL Server have KNN. What is the MySQL equivalent of <-> to do KNN on their spatial index?

If you don't know what KNN is, you can see a description of it here

  The KNN system works by evaluating distances between bounding boxes inside the PostGIS R-Tree index.

  Because the index is built using the bounding boxes of geometries, the distances between any geometries that are not points will be inexact: they will be the distances between the bounding boxes of geometries.

The kicker here is that the KNN approach,

Does not require you to confine the query to a distance with a WHERE clause
Does not calculate all of the distances between all of the points
Can find the nearest point on the index.

This question is specifically about the method in which the job gets done. I realize I can get the same result, with substantially more work by calculating the distance from each point to every other point*, then ORDERing, and LIMITing.

In short, neither MySQL or MariaDB do. They do use R-Trees so it doesn't sound impossible to implement. I haven't seen this feature request in either of their bug trackers.


What is SSIS in relation to SQL Server? I'm finding acronomes such as 'SSIS', 'SSDT', etc etc a little confusing as to how they related to SQL Server. It seems to me that SQL Server implements the T-SQL language, and that a package such as 'SSIS' is a 3rd party 'wrapper' over the T-SQL language, and effectively a plug-in to SQL Server. Is this correct?

This blog post (here) mentions the benefits of SSIS vs T-SQL (which is why I assume that SSIS is a wrapper of T-SQL). Also, I can see that jobs created as SSIS packages can be found under SQL Server Agent in SSMS.

SSIS (SQL Server Information Services) is a first-party (Microsoft) component/platform for Microsoft SQL Server. It isn't an add-on product, you will be given the option to include components when installing SQL server.

Don't think of it as a wrapper for T-SQL, think of it as a place to run T-SQL (and do all kinds of other very useful things).

(SSIS is the successor/replacement for DTS (Data Transformation Services) in SQL 2000 and prior, if you're familiar with that.)

To use SSIS you create a "package", which is a standalone file with a dtsx extension. You create the package in SSDT (SQL Server Data Tools), a customized version of MS Visual Studio (this used to be called BIDS).

Your SSIS package can be executed on any SQL server, by calling it directly from a SQL Agent job. You can also run it on a separate server that has SSIS server components installed, or from a development workstation using either SSDT or the "SSIS Package Execution Utility".

Here's an example of typical use:

The SQL Agent allows you to create scheduled jobs, in which you might run a bit of T-SQL code to perform some database maintenance or something. Paste the T-SQL code into the step detail window, set the job schedule, and you are good to go.

But what if you need to do something more complex than you'd normally attempt in T-SQL? Or something that T-SQL can't do at all?

SSIS allows you to run T-SQL, but you can also do a ton of other stuff:

Easily manage connections to multiple SQL servers (or other ODBC sources), for imports/exports or other operations
Create high-performing data flows and transformations (fastest possible bulk imports, column lookups, robust error handling, including piping failed rows into a different dataset or destination table)
Supports a branching flow, which can launch truly parallel tasks, something that normally isn't possible using T-SQL
Supports other script/code languages for more sophisticated logic
Easy mechanisms for looping through objects
Detailed error logging and/or package progress logging
Lots of other cool stuff.

So you create your more sophisticated "nightly data processing" SSIS package that imports and validates data from 2 other SQL servers, 1 Oracle instance, and 5 text files, then processes the data per your business rules, all using parallel processing to take advantage of your multiple CPU cores. Then call that from your job step instead of your T-SQL code.

Note: installing the Server Component of SSIS isn't strictly necessary, that is a tool to store and manage SSIS packages, or (if desired) run SSIS packages on a dedicated host.

EDIT: SSDT is the tool used to create and edit SSIS packages (thanks, @JonathanFite!). It is basically a customized version of MS Visual Studio. It used to be called BIDS (Business Intelligence Design Studio).


How do I add a database in IBM DB2 I'm supposed to migrate a MySQL database into IBM DB2, but I thought I'd take baby steps first before I do something like that. So for now, I'd just like to add a database.

I'll admit that I have no experience at all with IBM DB2. Here's what I did so far:

I've installed the software, IBM Data Server Client Version 9.7 Fix Pack 7. Went without a hitch, although I must say, it appears simpler than either MySQL or MS SQL.
I've ran Control Center under General Administration Tools. I've had to run it as administrator for it to work.
I read somewhere that the flow is System -> Instance -> Database, so I created a system with the following details:

System Name: RNOLD-PC
Host Name: RNOLD-PC
Node Name: RNOLDNOD
OS: NT
Protocol: TCP/IP

Hitting the OK button with those details produces no error. For reference, the Computer Name of the laptop I'm using is RNOLD-PC.
I created an Instance under that with the following details:

Instance Name: testInstance
Instance Node Name: RNOLDNOD
OS: NT
Protocol: TCIP/IP
Host Name: RNOLD-PC
Service Name: RNOLD-PC
(Everything else is left blank)

Clicking the Discover button near Instance Name gets me an SQL4404N error: The DB2 Administration Server does not exist, while clicking the Retrieve Button near Port Number (which I left blank) gets me a "The Service Name RNOLD-PC was not found." If I try to add a database under that Instance, it gives me the same error.

Can I have some help on this? Where do I go from here, and how do I create a database?

EDIT: I added a Port Number (12345), which then allowed me to create a database, albeit with an error:

  [IBM][CLI Driver] SQL30081N  A communication error has been  detected.
  Communication protocol being used: "TCP/IP".   Communication API being
  used: "SOCKETS".

EDIT: Additional information:
 - Using Windows 7 32-bit

This is a typical error when configuring DB2 the first time. There are many docs about that in the web.

Check instance is up
Check service name (/etc/serices)
Check instance configuration (svcename)
Check ports (netstats -nato)
Check instance local connectivity (db2 attach to db2inst1)
Check database local connectivity (db2 connect to sample)
Check remote access (telnet db2server 50000)
Check instance remote connectivity (db2 attach to db2inst1)
Check database remote connectivity (db2 connect to sample)


Oracle import dmp file: ora exceptions I'm trying to import a .dmp file to my oracle RAC database using the following command:

C:\app\ptmp>impdp [user]/[password]@'DEVRAC-SCAN:1521/DB' DUMPFILE=UPG_E_7d291ff.dmp
 LOGFILE=UPG_E_7d291ff.log TABLE_EXISTS_ACTION=REPLACE

And I get the following output from oracle:

Import: Release 12.1.0.2.0 - Production on Thu Dec 17 09:43:13 2015

Copyright (c) 1982, 2015, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Advanced Analytics and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

I've looked around a little bit and some people (https://community.oracle.com/thread/3788748?start=0&tstart=0 && https://stackoverflow.com/questions/11878631/expdp-from-oracle-rac-environment) have said it could be a permissions thing so I checked permissions with:

C:\app\ptmp>icacls c:\app\oracle\admin\pldb\dpdump
c:\app\oracle\admin\pldb\dpdump Everyone:(R,W)
                                BUILTIN\Administrators:(OI)(CI)(F)
                                NT AUTHORITY\SYSTEM:(OI)(CI)(F)
                                PL\PLORACLE:(OI)(CI)(F)
                                DEVRAC1\Administrator:(OI)(CI)(F)
                                DEVRAC1\ORA_GRID_LISTENERS:(OI)(CI)(F)
                                BUILTIN\Administrators:(I)(OI)(CI)(F)
                                NT AUTHORITY\SYSTEM:(I)(OI)(CI)(F)
                                PL\PLORACLE:(I)(OI)(CI)(F)
                                DEVRAC1\Administrator:(I)(OI)(CI)(F)
                                DEVRAC1\ORA_GRID_LISTENERS:(I)(OI)(CI)(F)

the oracle tnslistener runs under PL\PLORACLE user. <-- this is actually not true, see edits.

I used these commands to create/change the data pump directory:

echo create or replace directory DATA_PUMP_DIR as 'C:\app\oralce\admin\pldb\dpdump\'; >1_UPDATE_DUMP_LOC.SQL
echo grant read,write on directory DATA_PUMP_DIR to public; >>1_UPDATE_DUMP_LOC.SQL

They were ran using this command in a batch file:

sqlplus -S [user]/[password]@'DEVRAC-SCAN:1521/PLDB' <1_UPDATE_DUMP_LOC.SQL >1_UPDATE_DUMP_LOC.LST

The user that is running the commands has sysdba privileges for the database. So, with all this, I can't really understand why the import of the .dmp file isn't working.

EDIT:

The oracle tnslistener is actually running as local service rather than PL\PLORACLE as I thought. The OracleJobSchedulePLDB_1, OracleServicePLDB_1 and OracleVssWriterPLDB_1 run under PL\PLORACLE though. The rest of the oracle services run as Local System/Service

Looks like it's the write to the directory that's failing, but at the OS level.

Does the OS user runing impdp have OS permissions to write to the directory?


How to see the list of the Postgres databases via the linux terminal? I have connected a database using the command
sudo -i -u postgres

And after I have tried \l  and \d too, to view the list of all available databases, it is not working. Instead, I get the following output:
d:command not found and
l:command not found error is showing

What to do, how to get the list of the postgresql databases?

If you want to execute the command from the shell, you can use the -c option in psql:

psql -c '\l'

Example:

peter@blackrain:~$ sudo su  postgres
postgres@blackrain:/home/peter$ psql -c '\l'
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------+----------+----------+-------------+-------------+-----------------------
 blackrain  | peter    | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 postgres   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 redpepper  | peter    | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 x          | peter    | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
(6 rows)

postgres@blackrain:/home/peter$


Should I deploy Mongodb sharding for 50 collections? I have a 10 node clusters that runs about 50 concurrent jobs. Each job needs to read/write a separate collection. So I have about 50 collections roughly. Each collection has about 20 M records. Most of the time, jobs only need to do sequential read/write.

For simplicity, I could deploy a single instance of mongodb that has no replication, no sharding. And have 50 separate collections. But the single node where mogodb is running becomes a hotspot and the rest 9 nodes can't share the read/write load.

So I would like to leverage the resource and balance the load. But I guess a 20M record collection is not worth sharding? Especially I only need to do sequential read/write. I thought about merging 50 collections into one big collection. But I am stumbled on the document size limitation which is 16 MB.

Any suggestions? Thanks,

You could use a sharded cluster to distribute the collections more evenly across your available hosts.  In general I would recommend not going beyond 5 shards (with at least 2 data bearing nodes for redundancy, plus an arbiter to break ties).

Once you have your 5 shards (or however many you end up with), you can then "pin" the collections to a particular shard with tagging.  I could explain that process in detail, but as usual, Kristina Chodorow has beaten me to it:

http://www.kchodorow.com/blog/2012/07/25/controlling-collection-distribution/

Although it is not required, and you can have a single database if you wish, I would suggest having more than a single database in order to benefit as much as possible.  Locking is at the database level, so concurrent writes will still be competing if you have more than one collection on a shard in the same database.

In fact, there is a simpler solution to this than tagging (though tags are the most scalable solution).  If you create separate databases for all of your collections (or at least a subset), you can have each database reside on a different shard and not shard the collections at all.

To explain: without enabling sharded collections, all data for a given database will reside on the "primary" shard for that database (primary shards are designated in a round-robin fashion).  Hence if you create 5 databases on a 5 shard cluster (for example) each will have  a different shard for its primary and you will have achieved a crude (but effective) distribution of load by using separate databases.  I added more detail regarding this in a previous answer.


What would cause an UPDATE statment to hang when executing from an .mdb file to SQL Server 2008 R2 over ODBC? Is there anything that would cause an UPDATE statement INNER JOINed between a separate MS-Access linked table and an SQL Server 2008 R2 table to hang, when the values are being set on the SQL Server side of the join?

I already checked the MS-Access linked table to see if there was an issue with corrupted records (sorting the table by various columns to find one), but I didn't find any.

I also tried compacting the .mdb that makes the calls.

I'm thinking about re-indexing the table in question in SQL Server 2008 R2, I'm not sure if that will help.  Is there anything else I can try?

If I understand your question correctly, you are doing a join in one MDB, based on a table in a different MDB and another table in SQL Server.

In this scenario, I would expect Access might need to read all of the data in both tables to be able to complete the join, and may not be able to do any optimizations or filtering until it has both datasets in memory. How big are the two tables? If either of them are large (10,000s of rows), it may take a while to run - although I would not expect it to hang permanently.

One option that may or may not be practical in your situation is to import the data to local tables before doing the join.  Another generic approach I would recommend is to offload as much of the heavy-lifting to SQL as possible - it is much better suited for working with large datasets.

If you can give some more details about the nature of the hang (temporary? permanent?) and the data (how many rows & columns) that would help us troubleshoot.

A troubleshooting step you can consider is to eliminate all the data columns from your query - just have the Keys. If that works, then slowly add the data columns back in until you can establish which one is causing the hang.


Best DB approach for a lot of updates Recently I built an app that gets that from SVN repositories to run a few metrics.

What I do is basically:

1) Read data from an existing SVN repository and save this data to a database (with only two tables Commit and Modification - which includes the list of files that were changed in an specific commit)

2) After, I go through all the data on the database, looking for information like average time between commits of a particular developer, how many files each developer changes on average, etc.

3) Then, I store those information on spreadsheets. For example: in the case of time between commits, I create an CSV file for each developer, and each line of this file represents a commit, storing the data of the commit and how many days since the last commit:

Having this info, I calculated the average by adding the numbers on column B and dividing the sum by the number of rows. So, I have another CSV file that sums up the average number of days that each developer on the team takes to make a new commit.

I follow this approach for many other information, like how many commits on average each developer makes by month, etc.

The main issue here is that whenever a new commit is made, it is not only stored in the database, but the CSV file for the developer that made the commit needs to be updated with the new information (and so does the file that sums up the averages of the team). I've been thinking that having spreadsheets might not be the best approach, since it's not very easy to update them. I'd considered discarding the spreadsheets and expanding the MySQL database that I'm using for commit information, but it didn't seem the best option.

My question is: what would be the best approach to use in this case? Relational DB? Document, Key-Value, Column Family? Keep the spreadsheets but in a different way (store less information, maybe)? I do a lot of updates and this is probably the main problem with spreadsheets. What would you suggest?

Sorry for the very long post and thank you in advance :)

Relational DB seems a perfect fit for your needs.

MySQL will probably give you some hard time on some of the queries you want to run or you need to off-load that to the application side but that doesn't mean it cannot be done. Depends on the size of your tables too.

Databases with window function capabilities (practically anything else than MySQL) will help you with queries like "time between commits". I'd personally recommend PostgreSQL which is great for analytical queries as well.

Document stores and key-value stores are not meant to be backends for serving analytical data retrieval. You might gain some performance on read/write operations but since your application has to do the heavy lifting instead of the database the number of operations increases significantly. So with those it might even be slower at the end.

How you represent that data at the end is more of a business questions than technical. What your users want. Do they prefer excel sheets over for example an internal websites where they can check? Google Sheets might be a middle ground there. Just an idea.


Quorum read consistency in Cassandra I'm new to distributed databases and there are a couple of things I couldn't figure out.
We're developing an application and need to select the right database,
this application needs strong consistency on financial data related to users, like deposit and withdraws.
I am wondering about tunable consistency in Cassandra, specifically quorum level of consistency.
Imagine the following scenario:
Replication factor is 3.
Initial balance of a user John is 10 on all replicas.
At time t1, an update is happening on John's balance reducing it to 7 (CL=quorum),
Simultaneously, at time t1, a read operation is being run with consistency level set on quorum which is 2 in this case.
Is it possible that the replica on the node where update query is executing on, is not selected for the quorum?
If so, Is it right to assume that the result of read operation would be 10 because there's no write lock?
Overall I'd like to know if it is possible for John to somehow exploit this and spend more than his account's balance.
Thanks.

With having QUORUM for both read & write requrests you achieve strong consistency per following equation R + W > N, where R - read replica count, W - write replica count, and N - replication factor.

If you write with QUORUM, then 2 nodes will get fresh data, and when you read with QUORUM, then one node may have the old data, and one will have fresh data - Cassandra will detect discrepancy & use the newest version.


Shrink the database only up to its initial size which is set after creating database In SQL server every database has a property Initial Size (MB) which can be seen in properties of database in SSMS. By default it will be 3 MB for mdf and 1 MB for ldf file.

So now if create a new database then it will be set to default size(i.e 3 MB for mdf and 1 mb for ldf file).
But after creating the database  I change the initial size to some other value say For mdf 10 MB and ldf 5 MB.(In real-time some database administrator may want to change initial after creating database)

But now if shrink the database it will shrink beyond the initial size I have set after creation(consider that no data is there in database otherwise it will shrink to its actual content size). It will shrink till the initial size it had during creation of database(i.e 3mb for mdf and 1 mb for ldf). I was expecting to shrink till the initial size which I have set(i.e 10 mb for mdf and 5mb for ldf). Can it be possible to do? If possible then how?

In some article i saw like dbcc SHRINKFILE can be used to shrink beyond initial size and dbcc SHRINKDATABASE cannot shrink beyond initial size. But I want to shrink only till the initial size to which I have set.

Note: I know that shrink is bad and should not be done.But I want to know how it can be done? If sql server considers only the initial size set during creation of database then what is the use of setting the initial size after creating database?

EDIT:

From microsoft shrink related article:
Using DBCC SHRINKDATABASE you can not shrink database below its initial size.

My question is Can I change the initial size of existing database itself?

EDIT:

I have about 5 sql server instance. All the database where created with default size of 3 mb for data and 1 mb for log(about 3 years back). Now all the databases are in the range of 100-1000 mb. I wont shrink the database normally as it is not good practice. But some database grow more than 10 GB(mdf file itself as i insert so much data in it). So when database is large I will shift unimportant and old contents from this database to another server as it is not required in that server for me. So that database size reduces from 10 GB to 1 GB. Now the remaining 9 Gb left unused. Now I  want to specify database size to be reduced to 2 GB so that i will get 1 GB free space(Also 2 Gb is the ideal size for this database), Which can be used to avoid autogrowth till next 1 Gb of data is filled. Also i have set 100 MB auto-growth for this kind of large database which has more writes. So instead of specifying size as 2 GB in shrinkfile command,if it was possible to change initial size to 2 GB (which shrink considers) then it would have been better. During this database creation there was no plan about size ,autogroth etc. As it was created about 3 years back. Now i am planning to set autogrowth etc. I cant again create new database with new initial size and shift data. So I was asking whether I can change the Initial size for existing database?

Initial size is not just 3MB, it is taken from the model database (if not specified during the creation of your user database.) So assuming you haven't specified a initial size during the creation of your user db and you haven't altered the model database file sizes after you have created your userdb you can do the following:

--Create testDB
CREATE DATABASE [TEST_100]
GO

--grow your database file size
ALTER DATABASE [Test] MODIFY FILE ( NAME = N'Test', SIZE = 100MB )
GO

--switch context
USE [TEST_100]
GO

--Find size of modelDB mdf file, this is your initial file size used for the userdb
DECLARE @TargetFileSize int

SELECT  @TargetFileSize = (size * 8 / 1024)
FROM    sys.master_files
WHERE   database_id = 3 --model database
AND     file_id= 1 --first file is mdf, assuming you have a model with just one mdf. If you have multiple files, change for the one you need to find.

--shrink the the first file of your current database to the target size that you just found.
DBCC SHRINKFILE (1,@TargetFileSize)

EDIT

Okay, some extra info is needed after your edits and comments.

First of all. I feel that the "Initial size" label that you see when you look at the file properties in SSMS is a misnomer. Basically, your intial size is just a concept. It's the first size that is used during creation of the database. You can either explicitly specify this in the CREATE DATABASE statement, or you can have SQL Server implicitly copy it from the model database by ommiting that information during creation.

However, once the database is created, from a DBA perpective there is no such thing as a "initial size" there is only one property visible for a DBA and that is: the actual size. Even the "Initial size" property in SSMS just shows actual size, not the initial size.

Well how come that DBCC SHRINKFILE or DBCC SHRINKDATABASE "know" the initial size then? Even after you have changed the size. Interesting question.

The first page of a dattabase file is the file header page. In there you have, amongst others, 2 properties: size and minsize.

At creation of the file, both file header properties get filled with the inital value:

DBCC TRACEON(3604)
--parameters for DBCC PAGE: (Dbname, fileID, pageID, outputTypeID)
DBCC PAGE('Test_100',1,0,3)with tableresults

Both sizes are in the amount of data pages. In this case. 288 data pages.

Now if I alter the file size:

ALTER DATABASE [test_100] MODIFY FILE ( NAME = N'test', SIZE = 50MB )

You can see that the "size" property is changed to reflect the new size. However, the "MinSize"  property still contains the "Initial" size. It's the minimal size to which the shrink command will go.

However, having said all this. I still don't understand why you want to complicate things by first altering the initial size and then shrink to that initial size. Instead of just shrinking directly to a targetsize.

Anyway, to answer your question. The "initial" size is not exposed as a property to the user/dba.


How to index a many to many table most effectively Should a many to many table be indexed? What kind of index would be best?

Here's an example table:

CREATE TABLE user_role (
  userId INT,
  roleId INT
)

--edit: drachenstern - I added the table def based on the original comments, and assumed ints.

a clustered index on (userid, roleid)
another index on (roleid, userid)

You don't need a surrogate key (unless you use a braindead ORM) and you almost always need the 2nd index


Cannot get LOB data from SQL Server transaction logs I am trying to extract all information (Insert, Update and Delete Operations) from the transaction log file of SQL Server manually. For this work, I am using the undocumented functions fn_dblog and fn_dump_dblog for SQL Server.

There are 3 allocation units (sys.allocation_units) in transaction log file Partition (sys.partitions).

IN_ROW_DATA
ROW_OVERFLOW_DATA
LOB_DATA

In all of these allocation units, we found a RowLogContents0 column that contains the data of that row in hexadecimal format. I can get real data from the RowLogContents0 column, when I have an IN_ROW_DATA allocation unit type in my table. But when I have ROW_OVERFLOW_DATA or LOB_DATA allocation unit type in my table then I am unable to get data from RowLogContents0 column.

Does anyone know how I can get data using RowLogContents0 column which is stored in ROW_OVERFLOW_DATA and LOB_DATA allocation unit type?

I am following these links to get inserted and updated data from logs:

This one for inserted record
This one for modified record

Now I am able to get these records and queries. But facing problem when trying to get LOB records as describe above.

Note : I don't want to use any third party tool to do this, because I am trying to make a third party tool for my program. Right now we are only focusing on SQL Server 2008 R2.

If you absolutely want to do this then I would advise you use a 3rd party product that does this for you. These undocumented functions are just that, along with the log structure.

  I can get real data from RowLogContents0 column when I have IN_ROW_DATA allocation unit type in my table.

Each type of log record may have its own way of storing the data and could change between operations. There may be additional considerations for each record type as well... for example, in-memory records will not be properly processed using your above methods. There also may be additional considerations between versions of SQL Server.

  Note : I don't want to use any third party tool to do this, because I am trying to make a third party tool for my program.

Then I wish you the best in your trial and error to figure out what works and what doesn't work for each specific type of interaction of DML, row store, etc.

The only other recourse you may have is talking with Microsoft if you're a partner and attempting to obtain help with this endeavor.

Disclaimer: I work for Microsoft.


Finding five-minute interval prior to current time I want to find the five minute interval just before the current time. So if the current time is 8:43 I want to return 8:35 and 8:40. Here is the query that I have written and it is working as expected. I want to know if there is any better way...

mysql> select now() ;
+---------------------+
| now()               |
+---------------------+
| 2012-03-11 08:43:06 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT date_sub(from_unixtime(unix_timestamp(curdate()) + floor(TIME_TO_SEC(now())/300)*300), interval 5 minute) as ten_minute_interval,  from_unixtime(unix_timestamp(curdate()) + floor(TIME_TO_SEC(now())/300)*300) as five_minute_interval;
+---------------------+----------------------+
| ten_minute_interval | five_minute_interval |
+---------------------+----------------------+
| 2012-03-11 08:35:00 | 2012-03-11 08:40:00  |
+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> SELECT some better query...

Try this query

    select date_add(now(), interval -5 minute) five_mins_interval,
    date_add(now(), interval -10 minute) ten_mins_interval, now()
    currentTime


How to establish a Two-to-One table relationship (PK-FK-CK)? My question is about DB design and its principles. I am working on a Python application where objects are mapped to database tables.

I have Projects and People objects, mapped to their respective tables. Then I have Messages, also mapped to its own table.

A message can belong to either a person (people table) or a project (projects table) (n-1).

My question:

Should I create two columns -- belongs_to_id and belongs_to_type -- for Messages and query to get them? or;
Should I create an association table for each -- people_have_messages and projects have messages?
What is the correct way to work in this case scenario?
How can I create FK constraints and restraints if I go with the first option?

My problem is, as both a Project and a Person (people table) can have the same PK (id), it wouldn't be enough to reference without stating the table and I don't know what exactly to do.

Tables:

People:
--------------
ID (PK unique)
Name

Projects:
--------------
ID (PK unique)
Title
Description

Message:
--------------
ID (PK unique)
Text

I'd say it depends on whether there's a connection between People and Project?

From what you've mentioned, I'd properly go with a:
Projects -> ProjectsToMessage <- Message
and
People -> PeopleToMesseage <- Message
type structure

(your bullet point 2)

Unless there's a releation between Projects and People in which case you might end up with some circular dependency which the database wouldn't like.
However in that case, I would then question whether a Message can be associated to a Project without it going through People.


Join two subquery in hql How can I write this sql query in as hql:

select count(distinct s.id) as prepaid, count(distinct ss.id) as postpaid
from (select * from subscriber where subscriber.ispostpaid=0) s
join (select * from subscriber where ispostpaid=1) ss
where s.subscriber_status='active';

Or can make this query without using join by using only subquery.

Assuming I understand what you're looking to return, and assuming subscriber.id is unique in the table (seems likely), here is a SQL Server answer (though it should be adaptable to MySQL):

select
    isnull(sum((case when s.ispostpaid = 0 and s.subscriber_status = 'active' then 1 else 0 end)), 0) as prepaid,
    isnull(sum((case when s.ispostpaid = 1 then 1 else 0 end)), 0) as postpaid
    from subscriber s;

This selectively counts the rows of interest with one table access instead of an expensive cross join and grouping in the original query. The use of ISNULL is to still return counts of 0 if the table is empty.

Using NHibernate, I believe this could also be written using ICriteria... but it's been a while for me on that. Check out my answer here, as it might help if that's what you're looking for.


Using N-Tier in Business Intelligence I have read that you also can apply N-Tier for Businesss Intelligence
based on these criteria:
*Presentation
*Functional logic
*Data

What is your experience when you are using N-Tier?

// Fullmetalboy

N-Tier may be a disingenuous term

I think the term 'N-tier' is disingenouos whe used in the context of a business intelligence system.  In transactional systems 'N-Tier' describes a distributed system with an application server, ESB or some other networked middle tier.  Data warehouse systems don't work in a way analogous to this, so the term is likely to breed confusion.

Separating data and functional logic

You could build a system in terms of data that's as raw as possible and then put a transformation layer on it, which is then consumed by a reporting layer.  The transformation layer could take the form of:

A series of database views.
A metadata layer from the reporting tool (e.g. a Report Model or a Business Objects Universe).  A category of tools known as 'Enterprise Information Integration' extend the notion of the metadata layer into something like an in-memory ETL tool, allowing the reporting or functional logic layer to implement complex transformations.  However, this approach is fiddly to implement and not widely used unless something precludes implementing a more conventional ETL architecture.
A suite of data marts with their own ETL.

This would give you 'Data', 'Functional Logic' and 'Presentation' layers as per your example.  Some data warehouse systems are implemented a bit like this, although it (IMHO at least) would be something of an anti-pattern.  Some issues with pushing business logic downstream of your ETL include:

Depending on the capabilities of your functional logic medium, the possible transformative capabilities may be limited, resulting in leaky abstractions that limit the value of any ad-hoc reporting facility placed above them.  Ad-hoc tools need clean data that behaves consistently, and the data really has to be in a format that plays nicely with the tool.If the data is not consistent, clean and in the right format you are effectively limited to bespoke SQL reports of the data or building supplementary data marts to support ad-hoc facilities.  Relying on data marts has a tendency to proliferate a large volume of ad-hoc tertiary ETL processes with overlapping but subtly inconsitent functionality.  This tends to generate reconciliation and data quality issues and fails to provide a 'single source of the truth.'Generally this situation will generate a large maintenance workload and erode confidence amongst the user community.
A database schema that is not optimised for reporting may perform poorly.
'Raw data' implies that the data is kept in a form that is fairly isomorphic to the source, which is mutually exclusive with any notion of conformed data.
'Raw data' is also typically available from staging and archival areas if needed for auditing.  You will get a class of power users who 'just want the data', and these people may have a lot of political influence.  Allowing this to continue alongside a data warehouse project fails the 'single source of the truth' goal.If the warehouse has a proper conformed data mechanism (e.g. an operational data store) then this is supposed to provide the single source.  Dealing with conflicting definitions and user communities who insist on their own stovepiped systems is a whole topic in itself.

Nonetheless, this is more common than might be expected.  I think the main reason one encounters data warehouse projects implemented in this manner is that ETL tools are quite clumsy to work with if your requirements for tranformation logic are complex.  ETL tools often have the effect of dumbing down the architecture and fobbing off logic into the reporting layer, which significantly degrades the effectiveness of the data warehouse initiative.  The volume of work and presence of a central database gives the illusion of a data warehouse while providing little of the benefit.

Another N-tier view of a data warehouse

One could interpret 'Data', 'Functional Logic' and 'Presentation' as an ETL and reporting process in a more well organised data warehouse system.  In this case, 'Data' could be interpreted as the staging layer, 'Functional Logic' implemented in the ETL, presenting a dimensional data store and/or suite of data marts, and 'Reporting' implemented through a reporting and ad-hoc query suite.

Considered harmful

For this reason, I think the concept of 'N-tier' is unhelpful and even a bit disingenuous.  It sounds a lot like something a middleware company or consultancy might describe in a white paper - a flawed and even somewhat misleading theoretical notion that sounds good on paper.


Can CURRENT_TIMESTAMP be used as a PRIMARY KEY? Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

Is there a possibility that two or more different INSERTs, get the same CURRENT_TIMESTAMP?

As per the documentation, the precision of the CURRENT_TIMESTAMP is microseconds. Thus, the probability of a collision is low, but possible.

Now imagine a bug which happens very rarely, and causes database errors. How hard is to debug it? It is a far worser bug than one which is at least deterministic.

The more broad context: you probably want to avoid these little nuances with the sequences, which is particularly annoying if you are accustomed to MySQL.

Furthermore, if you are using transactions (most web frameworks, particularly the Java ones, do!), then the timestamps will be the same inside a transaction! A demonstration:

postgres=# begin;
BEGIN
postgres=# select current_timestamp;
       current_timestamp
-------------------------------
 2018-08-06 02:41:42.472163+02
(1 Zeile)

postgres=# select current_timestamp;
       current_timestamp
-------------------------------
 2018-08-06 02:41:42.472163+02
(1 Zeile)

See you? Two selects, exactly the same result. I don't type so fast. ;-)

--

If you want easily IDs, avoiding the usage of the sequences, then generate some hash value from the real identifiers of the records. For example, if your database has humans, and you know that their birthdate, mother's maiden name and real name uniquely identifies them, then use an

md5(mother_name || '-' || given_name || '-' birthday);

as id. Beside that, you can use a CreationDate column, after what you index the table, but it is not a key (which is the id).

P.s. In general, it is a very good practice to make your DB so deterministic, as it is possible. I.e. the same operation should create exactly the same change in the DB. Any timestamp-based ID fails this important feature. What if you want to debug or simulate anything? You replay an operation and the same object will be created with a different id... it is really not hard to follow, and it spares a lot of work hours.

P.s.2 Anybody checking your code in the future, won't have the best opinion  seeing timestamp-generated ids, on the reasons above.


Very large SQL Server 2016 result sets (over 75 GB) to a CSV file? What is the best way to get very large SQL Server 2016 result sets (over 75 GB) to a CSV file? The engineers need this output to look for correlations.

The bcp route for a 73.5 GB file filled up tempdb and started crashing other applications, including the ETL process.

Our users want to export up to 500 GB.

What process would use the least amount of resources so that other applications keep running?

I, too, used to have problems exporting large result sets (7 - 8 GB) to delimited files. Neither SQLCMD nor BCP nor SSIS could handle dynamic result sets, dynamic text-qualification, adding a column header row, etc. So, I built my own tool to handle this. It currently exits as the DB_BulkExport Stored Procedure in the SQL# SQLCLR library (that I wrote), though the plan is to break it out into a stand-alone export utility and expand on the features.

Please note that the DB_BulkExport Stored Procedure is only available in the Full (paid) version of SQL# (i.e. it is not in the Free version), but I am not aware of any free utility to do this type of thing (hence why I wrote my own).

Regarding the export of large result sets: I so far have not run into problems with memory since it writes each row out to the file as it is read from the result set.

If you only need this export for a single table / query that doesn't really change in terms of structure / schema, then it might be best to write a small, specialized app yourself, either in .NET or PowerShell. The tricky part is in handling dynamic requirements. But if you know what the columns are named and the datatypes, etc, then it should be a simple-enough matter of opening the output file, executing the query, and then for every row in the SqlDataReader just String.Concat everything together, including text-qualification where needed and applying appropriate Format specifiers when needed (i.e. Date(Time) values).


PostgreSQL bad plan: the frequency of a tag depends on the project! I try to simplify a problem that I've been investigating for weeks.

Suppose that you have a large table like this:

SUBSCRIBERS
----------------------------
id | project_id | tags | ...
----------------------------
1  | 1          | ['en', 'something']
2  | 1          | ['en']
3  | 1          | ['de']
4  | 2          | []
5  | 2          [ []
...

Now suppose that project 1 has millions of rows and most of them have the tag en.

Also project 2 has millions of rows, but there isn't any row with the tag en.

The following query generates terrible execution plans:

SELECT * FROM subscriptions WHERE project_id = 2 AND tags @> ARRAY['en']::varchar[];

Indeed the planner sees the following:

project = 2 is very common
tag = 'en' is very common

Thus the planner thinks that there are many rows to return! However no rows match the actual condition.

That creates crazy choices in the indexes or even seq scans! I mean, I verified that it takes 20 minutes instead of a few ms, due to the wrong choices!

I have already tried the following (without any benefit):

SET STATISTICS 10000 for project_id and tags
CREATE STATISTICS on project_id, tags
SET random_page_cost = 1
make sure that the perfect indexes (compound, btree, gin, etc.) exist on project_id and tags
frequent ANALYZE and much more...

I think that the problem is that PostgreSQL should collect separate stats on tags for each project id. However it seems that there is no way to achieve that...  Any idea?

Also I think that CREATE STATISTICS doesn't work properly on array columns (maybe works on the whole column instead of the single values). Any specific information about this?

You are correct that there is no way to do this, and that CREATE STATISTICS only looks at arrays in their entirety, not their individual components.

Perhaps CREATE STATISTICS can be expanded in the future to do that, but someone would have to volunteer to implement it, and I am not aware of anyone working on such a thing.

Note that even if "tag" were a scalar, CREATE STATISTICS wouldn't help you in versions 10 or 11, as the mcv type of statistics were not added until v12, and that is what you need. So presumably what you would need is a new type of statistics, call it arrray_mcv.


Partition Key questions in SQL Server 2008 We are planning to use table partitioning for our database based on an integer column. I have read about it on MSDN at http://msdn.microsoft.com/en-us/library/ms190787.aspx and have few queries.

a) Is it required that partition key be part of primary key? Even if it is not required still would it be of any benefit if it is part of primary key or clustered index?

b) In our case, we would be performing JOIN operation of partitioned tables with non-partitioned master data. Will I loose the performance gained by partition elimination? In general, if any of the tables used in JOIN operation is non-partitioned then will I loose the benefit of Partitioning?

c) Does adding filter criteria in JOIN criteria over Partition key help in any way in partition elimination? Or only the filter criteria in WHERE clause over Partition column is used for Partition elimination?

d) Should partition key be part of non-clustered index?

Thanks in advance.

Assuming that you have the primary key on a clustered index then the partitioning key needs to be part of the primary key.

You will not loose the benefit of partitioning by joining to non-partitioned tables, providing that the queries are designed to make use of the partitioned table, for example the following query WILL benefit from partitioning

SELECT F.Col1, F.Col2, D.Col3
FROM Fact_Partitioned F
    INNER JOIN Dim_MyDim D ON F.Col1 = D.Col1
WHERE F.Col1 = 5

But the following query WILL NOT benefit from partition elimination

SELECT F.Col1, F.Col2, D.Col3
FROM Fact_Partitioned F
    INNER JOIN Dim_MyDim D ON F.Col1 = D.Col1
WHERE D.Col1 = 5

It is a subtle difference, but in the first query, the join key is filtered in the partitioned table, taking advantage of elimination and then joined to the dimension. In the second query, the key is filtered in the dimension and then joined against the whole of the fact table, rather than just required partitions.

It goes without saying that the partitioning key needs to be in the WHERE clause for elimination to work, otherwise SQL Server does not know which partition(s) the data is in.

Adding a filter criteria on the JOIN clause will not help you. It needs to be in the WHERE clause to benefit from elimination.

The Partition Key does not need to be part of a non-clustered index (NCI) but if the NCI is unique, then it needs to contain the partitioning key in order to align the index. This is where the NCI is built on the same partition scheme as the table.  NCIs should also be partition aligned unless there is an exceedingly good reason not to.  I have never come across a good enough reason!


Can MongoDB be configured to sit behind a load balancer? According to this post:

  In a single replica set, you cannot distribute writes, they all must
  go to the primary. You can distribute reads to the secondaries
  already, via Read Preferences as you deem appropriate. The driver
  keeps track of what is a primary and what is a secondary and routes
  queries appropriately.

According to the Mongo docs:

  You may also deploy a group of mongos instances and use a proxy/load
  balancer between the application and the mongos. In these deployments,
  you must configure the load balancer for client affinity so that every
  connection from a single client reaches the same mongos.

So basically, it seems like if you've got a single replica set of 3 nodes, you can't really use a proxy/load balancer since all writes need to go to the primary and you need client affinity... so all reads also need to go to the primary.

What I'm thinking though is that it might be possible to have applications connect to a load balancer. The load balancer would route all requests to the primary (not very balanced, but whatever)... until/unless the primary went down - at which point the load balancer would start routing requests to a "new primary".

I'm not sure if this is possible however since, how would the load balancer know which mongo server had been elected the new primary (and thus where it should route new requests)?

Assuming it was possible, this would achieve a degree of redundancy, in case the primary ever goes down... I'm also hoping it would also have the side effect of avoiding stale writes when a network partition occurs, since the load balancer (and thus all DB clients) would only ever connect to a single primary.

Or is this a stupid question...

recently I attended the online course provided by mongo university M202 and in the CHAPTER 3 FAULT TOLERANCE AND AVAILABILITY it specifically talks about these 2 questions:

Load balancer with a replica set;
Load balancer in a sharding environment.

Within a  replica set having a load balancer is discouraged because the load balancer should be aware about the actual primary server and also it should know about read preferences in the cluster.
So the answer given about the firs question is that no, it is not possible to have an external load balancer over a replica set cluster.
For the second question the answer given is that is possible to have a load balancer in front of a sharded system but with only one caveat, which is that the load balancer should stay sticky to the mongos addressed, something very similar to the session affinity in web application.
The reason is that during a request, i.e. a read from a cursor, the retrieval of the next elements will fail if the load balancer would use a different mongos from the one chosen before.


Why is PostgreSQL very slow when I execute a SELECT query with OR in WHERE clause? The query is like:

select count(*)
from test1 as r
WHERE r.testid in (select p.testid from test2 p where p.testname IS NULL)
OR r.testid > (SELECT MAX(p2.testid)FROM test2 p2);

but when I rewrite it as UNION it works. What is the problem of this query?

This is a common issue - PostgreSQL planner cannot use indexes (it's not able to optimize OR, when OR is used for subselects) and rewriting with UNION helps. Last year Tom Lane wrote a patch, but as far as I know, this patch was not merged yet.


Should a partial index with WHERE NOT NULL be used if queries use a JDBC prepared statement? Suppose we create a partial index in Postgres that avoids useless NULL data:

 CREATE INDEX my_ix ON my (col1) WHERE col1 IS NOT NULL;

Should I write the SELECT  query as:

SELECT * FROM my WHERE col1 = 'abc';

or as:

SELECT * FROM my WHERE col1 = 'abc' AND col1 IS NOT NULL;

In case of JDBC connection libraries it may look like this:

SELECT * FROM my WHERE col1 = ?;

and I am not sure if Postgres can infer index applicability without:

 AND col1 IS NOT NULL

Also it is interesting if Postgres can infer transitivity, for index:

 CREATE INDEX my_ix ON my (col1) WHERE col1 > 0;

I expect I don't need to write:

SELECT * FROM my WHERE col1 > ? AND col1 > 0;

if parameters are 10, 23, etc (> 0).

You can easily try it out with the following script:
CREATE TABLE my (col1) AS
   SELECT * FROM generate_series(1, 100000);

ANALYZE my;

CREATE INDEX my_null ON my (col1) WHERE col1 IS NOT NULL;

/* use a prepared statement similar to JDBC */
PREPARE stmt1(integer) AS SELECT * FROM my WHERE col1 = $1;

/* the first five executions use custom plans */
EXPLAIN (COSTS off) EXECUTE stmt1(1);
EXPLAIN (COSTS off) EXECUTE stmt1(2);
EXPLAIN (COSTS off) EXECUTE stmt1(NULL);
EXPLAIN (COSTS off) EXECUTE stmt1(4);
EXPLAIN (COSTS off) EXECUTE stmt1(5);
/* from the sixth execution on, a generic plan is used */
EXPLAIN (COSTS off) EXECUTE stmt1(6);
EXPLAIN (COSTS off) EXECUTE stmt1(NULL);

DROP INDEX my_null;

CREATE INDEX my_pos ON my (col1) WHERE col1 > 0;

/* use a prepared statement similar to JDBC */
PREPARE stmt2(integer) AS SELECT * FROM my WHERE col1 > $1;

/* the first five executions use custom plans */
EXPLAIN (COSTS off) EXECUTE stmt2(99997);
EXPLAIN (COSTS off) EXECUTE stmt2(99997);
EXPLAIN (COSTS off) EXECUTE stmt2(1);
EXPLAIN (COSTS off) EXECUTE stmt2(NULL);
EXPLAIN (COSTS off) EXECUTE stmt2(99998);
/* PostgreSQL continues to use a custom plan! */
EXPLAIN (COSTS off) EXECUTE stmt2(0);
EXPLAIN (COSTS off) EXECUTE stmt2(NULL);
EXPLAIN (COSTS off) EXECUTE stmt2(99997);

DROP TABLE my;
DEALLOCATE ALL;

Here is an SQL fiddle that tuns the code.
Explanations:
With a prepared statement, PostgreSQL can cache query plans for the database session.
Using such a generic plan that stays the same regardless of the parameter values has the advantage that plan time is saved.
But PostgreSQL doesn't always use a generic plan, because sometimes generating a custom plan that respects the parameter value can be better.
To decide what to do, PostgreSQL uses the following heuristic:

The first five executions will always use a custom plan.

If the estimated cost of the custom plans is not cheaper than the estimated cost of the generic plan, PostgreSQL will use the generic plan from the sixth execution on.

From PostgreSQL v12 on, you can configure the behavior with the plan_cache_mode configuration parameter.
Why does the first statement use a generic plan?
For the first statement, an index scan can always be used, even if the argument is NULL (because nothing has to be done in this case) . So PostgreSQL will use the generic plan after the fifth execution.
You can recognize the generic plan from the $1 in the EXPLAIN output.
Why does the second statement continue to use a custom plan?
With a generic plan, PostgreSQL can only use a sequential scan, because the index can only be used for some parameter values. For some of the first five executions the custom plan is significantly cheaper than the generic plan, so PostgreSQL continues using custom plans.
To answer your question:
If it is necessary to add an extra WHERE condition that makes sure that PostgreSQL knows it can use an index depends on the individual case.
You can add the extra condition without a disadvantage, it might help the optimizer and cannot do harm.
But even with the extra condition you cannot be certain that the index will be used.


How to backup the current code of a stored procedure and its permissions as well? I often get requests to alter stored procedures, in both test, development and production environments.

I would like a simple way to save the current code of the stored procedure, before the changes, into a table in my "tablebackups" database - which is a database where I save records prior to update or delete them.

I am thinking about something like this:

sp_helptext 'sp_myprocedure'

the idea of usage is something like this:

select * into tablebackups.dbo.my_procedure_20150827_1220
from ss_save_my_procedure 'my_procedure'

has someone got a solution for this already developed?

Rather than saving your procedures in user tables, why don't you just set up a SSDT database project and use version control (TFS, Git, Mercurial, SVN or whatever you find appropriate) to take care of versioning?

SSDT or other tools such as Red Gate's SQL Source Control will help you in keeping track of versions and deploying your changes to your envirnoments.


Cassandra multidatacenter configuration with 1 external ip I'm trying to setup a multi-datacenter Cassandra cluster.
The problem is that my datacenters have only 1 external IP (wan IP), I can setup port forwarding on the data centers switchs to access each node from the outside world using a different port, but I don't know how to setup the cassandra.yaml file properly.

Is there a way to setup a multidatacenter cassandra cluster in this scenario?.

Thanks in advance!

You can try to make each node unique for some of the ports, but this isn't really a good path to go down. Cassandra nodes need to see each other on the ports listed here for the best setup: http://www.datastax.com/documentation/cassandra/1.2/index.html#cassandra/security/secureFireWall_r.html

One option that I have seen work well is a host VPN like Openswan: https://www.openswan.org/projects/openswan/


SQL Server: What securables to enable a server role for read-write access to all DBs In SQL Server, you can add a server role and give that role membership to other roles such as:

bulkadmin
dbcreator
diskadmin
processadmin
securityadmin
serveradmin
setupadmin
superadmin

However none of these roles seem to allow for only interacting with databases (SELECT, INSERT, UPDATE).

What securables should I enable to specifically allow for this kind of access? I would not want this server role to be able to create and drop databases.

To give access to a database you need to use database roles and database users but not server roles.  Please check difference between a user and a login

You can create a server role, add logins to that. Then you can add that role as a user in each database and add it as a member of a database role that has select, insert and update rights.

If you do this in the model database and then that would be copied to all user databases created after that.


Oracle11g password expiry issue I have a user which has a default profile in oracle db.
I want to reproduce the following warning
ERROR:
ORA-28002: the password will expire within X days

In dba_profile I set following values.

FAILED_LOGIN_ATTEMPTS            PASSWORD 10
PASSWORD_LIFE_TIME               PASSWORD 1
PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
PASSWORD_REUSE_MAX               PASSWORD 1
PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
PASSWORD_LOCK_TIME               PASSWORD 1
PASSWORD_GRACE_TIME              PASSWORD 1

but however by using above value i am able to generate ORA-28002 error for system user. but for CUSUSER users it is not giving this error.

When i look into dba_users table it shows following values.

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
CUSUSER                        OPEN
SYSTEM                         EXPIRED(GRACE)

I am not able to update account_status for CUSUSER to EXPIRED(GRACE)

Please any one let me know how would i reproduce this error for CUSUSER.

According to the Oracle documentation here (see heading Setting the PASSWORD_LIFE_TIME Profile Parameter to a Low Value) the account status is changed from OPEN to EXPIRED(GRACE) if the user is currently logged in when you change PASSWORD_LIFE_TIME to a low value. I guess you were logged in as system when you changed the PASSWORD_LIFE_TIME. Try logging in as CUSUSER and then changing it.

The following method will set a user's account status to EXPIRED(GRACE):

create profile EXPTESTPROF limit
failed_login_attempts             1
password_life_time                1/24/60/60
password_reuse_time               unlimited
password_reuse_max                1
password_verify_function          null
password_lock_time                1
password_grace_time               1;

create user EXPTEST identified by EXPTEST;

alter user EXPTEST profile EXPTESTPROF;

grant "CONNECT" to EXPTEST;

Then login as EXPTEST. You'll get the message that the password will expire within 1 day. If you check the ACCOUNT_STATUS now it should say EXPIRED(GRACE):

select ACCOUNT_STATUS
  from DBA_USERS
 where USERNAME = 'EXPTEST'


Can I avoid Rollback with drop table? I had a very long query running that was causing everything to get stuck. (was running for around 24 hours before being killed)
INSERT INTO tbl_new SELECT * FROM tbl_old

So I killed it. Now I don't really need tbl_new and it was empty before. The query is now stuck at query end stage which I assume means he's doing a rollback. Any way for me to avoid it?

If I DROP TABLE tbl_new will that stop the rollback

Yes in InnoDB killing the session  is causing the rollback and the the rollback can take even more time than the actual insert command issued on the table.

Refer the following:

  Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer
  to save disk I/O in inserts, but no such mechanism is used in a
  corresponding rollback. A disk-bound rollback can take 30 times as
  long to perform as the corresponding insert. Killing the database
  process does not help because the rollback starts again on server
  startup.

So you can use the InnoDb Recovery:

  You can kill the mysqld process and set innodb_force_recovery to 3 to
  bring the database up without the rollback, then DROP the table that
  is causing the runaway rollback.

Hope this solves your problem.


Optimizing performance of INSERT INTO table from INNER JOIN between two large tables I have the following query:

DECLARE @accountCard TABLE (
    numUsu VARCHAR(10)
    ,quantity INT
    )

/*Takes less than a minute*/
INSERT INTO @accountCard
SELECT a.numUsu
    ,count(*)
FROM tableB a
INNER JOIN tableA b ON b.numUsu = a.numUsu
GROUP BY a.numUsu

/*This one takes the time*/
INSERT INTO finalTable
SELECT tarjeta = 'FIXED'
    ,fieldA = c.fieldA
    ,fieldB = c.fieldB
    ,fieldC = convert(BIGINT, b.numUsu)
    ,fieldD = ''
    ,fieldE = ''
    ,fieldF = rtrim(fieldF)
    ,fieldG = rtrim(fieldG)
    ,fieldH
    ,fieldI
FROM @accountCard a
INNER JOIN tableB b ON a.numUsu = b.numUsu
INNER JOIN tableA c ON b.numUsu = c.numUsu
WHERE quantity = 1

tableB has 2.7 million registers, and tableA has 1.7 million. Both tables have non-clustered, non-unique indexes for the column with which I do the join. Currently the second INSERT is taking too long: we have left it running for over 2 days and it was still not finished.

I ran the Estimated Execution Plan feature in SQL Server and it returned that the most expensive process was Searching for Keys (Clustered) in tablaA, but using the primary key, which isn't numUsu.

What can I do to speed it up?

Update 1:

select session_id,
    status,
    command,
    blocking_session_id,
    wait_type,
    wait_time,
    last_wait_type,
    wait_resource
from sys.dm_exec_requests r
where r.session_id >= 50
and r.session_id <> @@spid;

There's only one session running and the result is:

+------------+-----------+---------+---------------------+----------------+-----------+----------------+---------------+
| session_id |  status   | command | blocking_session_id |   wait_type    | wait_time | last_wait_type | wait_resource |
+------------+-----------+---------+---------------------+----------------+-----------+----------------+---------------+
|         59 | suspended | INSERT  |                   0 | PAGEIOLATCH_SH |         0 | PAGEIOLATCH_SH | 5:1:2431256   |
+------------+-----------+---------+---------------------+----------------+-----------+----------------+---------------+

The easiest way to speed up your query is to insert data from the first query into a #temp table instead of a table variable. Table variables do not get statistics in SQL Server. The query optimizer will always think that your table variable has one row which can lead to very poor performance when the table variable has many more rows than that. Based on your description of the plan, it's possible that the query optimizer is doing full scans of tableA many times which could explain why your query did not finish after 2 days.

Adjusting your code to use a temp table is straightforward. It would look something like this:

CREATE TABLE #accountCard
( numUsu        varchar(10),
quantity int);

INSERT INTO #accountCard
SELECT a.numUsu, count(*)
FROM tableB a
INNER JOIN  tableA b ON b.numUsu = a.numUsu
GROUP BY a.numUsu;

In my experience, table variables can be useful when inserting logging data that you do not want to be deleted when rolling back a transaction. For example, you might detect an error in your application and need to roll back a query. Putting troubleshooting data into a table variable will be preserved after the rollback but putting it into a temp table would not be. Table variables can also be useful when you have a process that runs many times per day (in the thousands at least) that inserts a small number of rows into table variables. Any queries that reference the table variable should not benefit from statistics on the table.

If you think that there's any chance that your queries would benefit from statistics on the table I would err on the side of caution and use a temp table. The query that you have in the question looks something that processes a large amount of data and you definitely can benefit from statistics on the table so I would use a temp table.

If you'd like to learn more about the differences between table variables and temp tables @Martin Smith wrote an excellent summary here.

There is potentially another performance problem with your query. You said that the numUsu column is not unique but you are joining to it twice. Are you sure that your insert query is returning the correct number of rows? Consider the case where you have 100 rows for a single value of numUsu in both tableA and tableB. With the joins that you have you will end up with 100 X 100 = 10000 rows for the single row in your temp table. Is that what you want to happen?

Here's a simple demo to demonstrate the kind of trap that you can fall into:

CREATE TABLE #accountCard
( numUsu varchar(10));

CREATE TABLE #tableA
( numUsu varchar(10));

CREATE TABLE #tableB
( numUsu varchar(10));

INSERT INTO #accountCard
VALUES ('1');

INSERT INTO #tableA
VALUES ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1');

INSERT INTO #tableB
VALUES ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1'), ('1');

SELECT COUNT(*) -- result is 100 rows
FROM #accountCard a
INNER JOIN #tableB b ON a.numUsu = b.numUsu
INNER JOIN #tableA c ON b.numUsu = c.numUsu;

Of course, depending on your data it's possible that you don't have this problem. I just wanted to mention it as a possible cause for the long running query.


Most efficent DELETE Method I must choose between three possible DELETE methods in SQL Server. The table: RawData stays at around 500GB.

Here is the table:

CREATE TABLE dbo.rawData (
    rowId INT IDENTITY PRIMARY KEY,
    AreaId INT,
    MeasureId INT,
    someData UNIQUEIDENTIFIER DEFAULT NEWID(),
    DateEnergy DATETIME DEFAULT GETDATE(),
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(),
    ts ROWVERSION
    )
GO

CONSTRAINT [PK_RawData] PRIMARY KEY CLUSTERED ([AreaId] ASC, [MeasureId] ASC, [DateEnergy] ASC)

CREATE INDEX IX_RawData_AreaId
  ON RawData (AreaId);
GO

CREATE NONCLUSTERED INDEX [IX_RawData_DateEnergy]
ON [dbo].[RawData] ([DateEnergy])
INCLUDE ([AreaId],[MeasureId])

GO
CREATE NONCLUSTERED INDEX [IX_RawData_MeasureId_DateEnergy]
ON [dbo].[RawData] ([MeasureId],[DateEnergy])
INCLUDE ([AreaId])

Method 1: Add a column IsDeleted type boolean.

Would require that this column be indexed as well (exclude rows
deleted by the SELECT)
Update this boolean column (update = rowlock)?

Method 2: Add a new table that will store rows that need to be deleted

Outer join between the two tables for the SELECT
No update necessary on RawData

Method 3: Use an existing column to detect rows to be deleted

Update the AreaID to a negative value to mark it for deletion (update
= rowlock)?
Use the existing suppression system that removes rows by batches of
10000

Constraints:

The deletion might need to be cancelled at any given time

Questions:

Which method would be the most efficent?
Are there any other methods not mentionned that might be better?

Update July 11th 2019

The number of rows being deleted depends on their collection date. So there could be millions or hundreds or none.

The best solution to this problem is nearly always method 2, but with two tables UNION ALL instead of LEFT JOIN together (one to hold the deleted, one for the active). There are several reasons why this is superior:

You can maintain separate statistics on active and deleted rows. Assumption: you will have more deleted than active  over time. This means that the "active rows" table stays small and doing fullscans is easier. It also makes things like index rebuilds (if you do them) faster on the active rows.
Statistics of old rows will not affect news rows. This helps you avoid skew problems.
Each table can live on it's own filegroup. This means you can move deleted rows off to cheaper storage.
You can have different indexing/partitioning strategies on the deleted rows vs. the active ones. For example, you may choose to use a column store index on the old rows if they are read via a scan often, but changed very rarely.
The deleted table can be taken offline with a table SWITCH (for maintenance) without disrupting the system too much.

The two table solution can be implemented with a DELETE instead of trigger that moves the rows to deleted instead of deleting them.

The only downside of solution 2 is that you will need to modify your queries to distinguish between deleted and not deleted rows and tables. This can be done via a view, but it is safer to avoid this if possible. The view can confuse the optimiser and there are cases where using a view instead of a table gives you horrible execution plans. If you do use a view, you should add an `IsDeleted' column to both tables and put a check constraint on it. This ensure that the optimiser will not try to seek BOTH table for every query (which would double your IOPS).

For completeness, and to answer your last question, there are some other ways to "solve" this:

Method 4: Add an IsDeleted column and partition on this column. Drawback: Does not allow separate statistics on the two partitions (statistics are table, not partition based, in SQL Server)

Method 5: Use a filtered index to apply different indexing (and secondary storage) on deleted and active rows. Drawback: Good luck getting the optimiser to behave properly with this method.


How would I index a text field that requires explicit characters and wildcards on both ends (postgres)? I would like to search for a tracking number while only having partial of the value.

I have a tracking number "1Z1234abcde" in my table.

I want to be able to:
select * from tracking_numbers where value ILIKE '%abc%';

I currently have this as a 'text' field type in my table but I am not able to index it correctly so I'm doing a Seq Scan when I use those wildcards.

                                             Table "public.tracking_numbers"
          Column          |            Type             | Collation | Nullable |                   Default
--------------------------+-----------------------------+-----------+----------+----------------------------------------------
 id                       | integer                     |           | not null | nextval('tracking_numbers_id_seq'::regclass)
 value                    | character varying           |           |          |

Full text search and trigram do not solve my problem because I need the characters to be exact, not variations. (ie. an exact tracking_number)

trigram indexes are exactly what you do want.  In ILIKE '%abc%', the '%' invite variation, while the 'abc' are exactly 'abc'.  Maybe you are thinking of the <-> and % operators, which trigram indexes also support.  But you don't need to use them if you don't want to, you can just use ILIKE.