Finding login or user name of last person to use a DB The boss has asked for a list of DB names along with size and name of last person to use that DB. I've found resources for when a DB was last accessed but for by whom. How would I solve this?

SQL2008R2 being used and so far I have this:

exec sp_MSForEachDB '
use ?
select ''?'', (SUM(df.size)*8)/1024 as ''Size (MB)''
from sys.database_files as df
'

It is acceptable for the last person aspect of the answer to be fuzzy or loose. i.e. as long as it's someone who went near it sometime vaguely recently that is "OK".

If your server has been up for a reliable amount of time (e.g. at least one business cycle), you can tell when there has been any read or write against any table in a database using DMVs like sys.dm_db_index_usage_stats.

There isn't really a reliable way to tell who last "used" a database. SQL Server doesn't log this information unless they do something substantial (e.g. you might see that a user created or dropped an object in the default trace, but this will by no means tell you they were the last person to access the database, or if that was the last time they accessed the database).

For the database file sizes, you don't really need to use sp_MSforeachdb at all; and you shouldn't use that method anyway. Here's why:

https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb
https://sqlblog.org/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior
http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

In this case, you don't need to loop through all of the databases anyway; this information is replicated in the view master.sys.master_files.

SELECT
  d.name,
  [Size (MB)] = SUM(mf.size)*8/1024
FROM master.sys.databases AS d
INNER JOIN master.sys.master_files AS mf
ON d.database_id = mf.database_id
GROUP BY d.name;

If you have to do something through a bunch of databases that isn't supported by something in master, I'd rather do this type of technique than use sp_MSforeachdb:

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'SELECT ''' + name + ''', (SUM(df.size)*8)/1024 as [Size (MB)]
  from ' + QUOTENAME(name) + '.sys.database_files as df;';
EXEC sp_executesql @sql;

(As an aside, don't use 'single quotes' to delimit an alias, use [square brackets]. The former is deprecated in some forms - see here and here - and makes your alias look like a string literal anyway.)

For the "when" part, copying from my answer here:

SQL Server doesn't really track database access in the way you want, at least going backward (you can set up things like server-side trace, extended events, auditing, etc. going forward).

There is one ballpark thing you can use: DMVs that track index usage and procedure/trigger/query stats. For example:

;WITH d AS
(
  SELECT d = database_id, name FROM sys.databases
  WHERE state = 0 AND database_id BETWEEN 5 AND 32766
),
index_usage(d,lsk,lsc,llk,lupd) AS
(
  SELECT database_id, MAX(last_user_seek), MAX(last_user_scan),
    MAX(last_user_lookup), MAX(last_user_update)
  FROM sys.dm_db_index_usage_stats
  WHERE database_id BETWEEN 5 AND 32766
  GROUP BY database_id
),
proc_stats(d,lproc) AS
(
  SELECT database_id, MAX(last_execution_time)
    FROM sys.dm_exec_procedure_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
),
trig_stats(d,ltrig) AS
(
  SELECT database_id, MAX(last_execution_time)
    FROM sys.dm_exec_trigger_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
),
query_stats(d,lquery) AS
(
  SELECT t.[dbid], MAX(s.last_execution_time)
    FROM sys.dm_exec_query_stats AS s
    CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
    WHERE t.[dbid] BETWEEN 5 AND 32766
    GROUP BY t.[dbid]
)
SELECT d.name,i.lsk,i.lsc,i.llk,i.lupd,p.lproc,t.ltrig,q.lquery
FROM d LEFT OUTER JOIN index_usage AS i ON d.d = i.d
LEFT OUTER JOIN proc_stats  AS p ON d.d = p.d
LEFT OUTER JOIN trig_stats  AS t ON d.d = t.d
LEFT OUTER JOIN query_stats AS q ON d.d = q.d;

Note that these statistics aren't completely reliable, since you may not have any stored procedures, and the queries found in sys.dm_exec_query_stats may reference more than one database, and may not ever reflect the one you are concerned about.

Also, they are reset when SQL Server is restarted, or a database is detached / attached or restored, or when a database is auto-closed, and can also depend in some cases on the plans still being in the cache (which another database could completely take over within minutes). So if you are looking into the past, unless you know none of these things have happened for an entire business cycle, I wouldn't rely on just these numbers to determine whether a database is used (there could also be automated processes that are making a database look current, even if you don't care that those automated processes will fail when you remove the database).

Another note is that certain index access may not be tracked in the index usage views; for example, in SQL Server 2014, which adds memory-optimized tables, activity against those hash indexes are not captured this way (and the views where you think the activity would be captured, like sys.dm_db_xtp_hash_index_stats, do not include any date/time columns). If you are using SQL Server 2014 and in-memory OLTP ("Hekaton"), you may want to add some research to cover those objects (in case they are the only ones being referenced in a database).

And one more note is that the queries captured by sys.dm_exec_query_stats could be false positive. For example, if your database has filestream/filetable, you will see these queries being run by the system occasionally:

select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
from [database].[sys].[filetable_updates_<some_id>] with (readpast) order by table_id

So, you may want to add additional filtering to the above query to filter those out (as long as the filter doesn't accidentally filter out queries you do care about). This is probably a safe addition to that derived table:

AND t.[text] NOT LIKE N'%oplsn_fseqno%'

In the end, the safest thing to do in a dev environment is to take the databases you're not sure about offline for a week. If nobody complains, back them up, and drop them. If it takes more than a week for someone to notice they're missing, you can always restore them (there or elsewhere).

I've blogged about this a bit as well:

https://sqlblog.org/2008/05/06/when-was-my-database-table-last-accessed


User-defined sort order for a query I'm storing user preferences for sort order in a table:

$queryHistory = "SELECT orderby,ascdesc FROM user WHERE UserID= ".$id;

$resultHistory = doQuery($connection, $queryHistory);
$numHistory = mysql_num_rows($resultHistory);
if ($numHistory > 0) {
  while($rowHistory = fetchRow($resultHistory, $queryHistory)) {
    $orderBy = getResult($rowHistory, $resultHistory, 1);
    $sortBy = getResult($rowHistory, $resultHistory, 2);
  }
}

And constructing a query based on that which looks like:

$otherQuery = "SELECT *
               FROM  user
               WHERE user.UserID = $id
               ORDER BY ".$orderBy." ".$sortBy;

Is it possible to do this with a single query, rather than first reading the preferences then running the actual query?

How about using a prepared statement to do it? Something like the below:

SELECT @orderBy := orderby, @ascDesc := ascdesc FROM user WHERE UserId = @id;
PREPARE cmd
FROM 'SELECT * FROM user WHERE user.UserID = ? ORDER BY ? ?';
EXECUTE cmd USING @id, @orderBy, @ascDesc;
DEALLOCATE PREPARE cmd;

EDIT

Apparently it is not possible to use parameters in the way that I have above (see comment below) in which case you should do the following:

SELECT @orderBy := 'Name', @ascDesc := 'ASC' FROM user WHERE Id = @id;
SET @s :=
CONCAT('SELECT * FROM user WHERE user.UserID = ? ORDER BY ',@orderBy,' ',@ascDesc);
PREPARE cmd FROM @s
EXECUTE cmd USING @id;
DEALLOCATE PREPARE cmd;

I hope this helps you.


Single table having large no of rows I am about to start a project and currently working on data design. While I am done in defining schema of db, I am just wondering to ask from experts.

I have only one table in which I am going to insert rows (no update) and from same table I am going to query the data set. Rows contains different columns. Columns for which I am going to query are kept in separate columns and the data which is needed to give as ouput will store in single column as JSON data.

I have experience of MySQL and I am looking for some other options to get fast query. My db size could contains billion of records.

I have seen many options like Cassandra, dynomodb etc but all these options make me confuse to decide on which db I could chose. I have also seen BIG query Postgre SQL.

I am looking for open source and where I could avoid any monetary expenses. I am looking for querying large datasets and want response to be max of 500ms.

Thanks in advance for help and suggestions.

Regards,
Harpinder

Whatever you do, if you go down the RDBMS route, I recommend that you choose PostgreSQL over MySQL - it is a far more sophisticated product and has lots more functionality.

Check constraints
Window (analytic) functions
CTEs - Common Table Expressions
Proper set operators - INTERSECT, EXCEPT as well as UNION (which MySQL does have)
Far superior support for JSON

As for response time, that depends on far more than your RDBMS server.

Your disk config (RAID &c) will be very critical in that domain, as will your server's RAM and CPU, although most database operations are HDD-bound.

It will depend on your queries also (obviously). If you are doing point SELECTs on PRIMARY KEYs, then 1/2s is not unrealistic - but if you are doing analytic work (GROUP BY, HAVING, range scans, subselects... the list goes on), then there's no way anybody can tell you in advance what your response time will be.

If you are always going to be performing the same query or small range of queries against your data, then a NoSQL solution might be the way to go - but SQL's ability to "slice and dice" data is beyond compare - plus, you never know when new requirements will arise.

Your question might be a bit broad for this forum - visit the help centre, take the tour and check out the "help us to help you" blog. p.s. welcome to the forum.


what are the conditions under which mysql replication might break? I am running mysql server in master/slave configuration mode. There are multiple slaves replicating from a master. They are working fine and their replication status is monitored.

One of the slaves serves for a special purpose. The devs have given me a set of update queries to be run on replicated slave db on slave A.

They are updating old records in a table in a db (modifying a column data for lot of records).

Questions

Is there any way I can figure out if the given query will break the replication ?
For the future, how do I determine if the query will break the replication or not ?

Given that MySQL Replication is dual-thread, it is importatnt to recognize how Replication looks when it is broken. There are four main topics is this area

SQL Thread Dies

The SQL Thread is responsible for

Getting the Next SQL Statement fromt the Relay Logs
Executing the SQL Statement
Rotating Relay Logs by Deleting any Relay Log that had all its SQL Entries Executed

If any SQL error happens, the SQL Thread simply dies and the following is posted to its Slave Status:

Error Number
Error Message
SQL statement that experienced the Error
Current database
Master Log File where the SQL Originated
Master Log Position where the SQL Originated

This gives an opportunity to troubleshoot, skip the error, run the SQL statement by hand, start replication back up. Sometimes it may be a SQL-based error, such as error 1062 (Duplicate Key). Other times, it may be related to the Storage Engine or the OS.

To figure out if an SQL statement will break replication, you should take any DML (INSERT, UPDATE, or DELETE) and make a corresponding SELECT using the WHERE clause of the DML. Then, run that SELECT to see if the data you are about to manipulate really exists or not.

I/O Thread Dies

The I/O Thread is responsible for four(4) things:

Downloading SQL from the Binary Log Entries of a Master
Recording SQL into its Local Relay Logs as a FIFO queue
Acknowledging Communication Failure
Attempting the Reestablish of I/O Thread

Any network latency may cause the I/O Thread to simply die and retry connection. Once a while under those circumstances, the Slave's viewpoint of the Master's log file and position (as logged in its relay logs) may be out-of-sync with what Master actually recorded in its binary logs.

Other side effects may include corrupt relay log entries

caused by bad network transmission, which can be corrected by running CHANGE MASTER TO from the last SQL statement from the Master that the Slave executed.
caused by corrupt binary log entries on the Master which was successfully transmitted to the relay logs, which can be corrected by

RESET MASTER; on the Master to Zap all binary logs
setting up replication from the new current binary log
using pt-table-sync to correct differences

Temporary Table Usage

Troubleshooting this is like playing "pin the tail on donkey". Most developers are unaware of this until it happens and you try to fix it not realizing where the cause of this began. Here is the scenation: If you use CREATE TEMPORARY TABLE on a Master, it will replicate to the Slave. During the time the table is in use, it will be kept in existence in the SQL Thread. If you issue STOP SLAVE;, the SQL Thread is voluntarily killed along with all temporary tables the SQL Thread was holding. You do not realize that this has occurred until you issue START SLAVE; and the SQL Threads dies again because the needed temp table no longer exists.

To fix this, you have perform surgery on the master's binary logs and replication as follows:

Step 01) Locate the exact log file and position the CREATE TEMPORARY TABLE was issued on the Master
Step 02) Locate the name of the database that the CREATE TEMPORARY TABLE was meant for
Create the table using CREATE TABLE instead of CREATE TEMPORARY TABLE
Step 03) Run CHANGE MASTER TO using the file and position from Step 01
Step 04) Run START SLAVE; until Replication catches up or another table's nonexistence (due to CREATE TEMPORARY TABLE) breaks replication for this same issue
Step 05) If replication breaks again because of CREATE TEMPORARY TABLE on a different table, go back to Step 01

Network Inconsiderations

Once upon a time, there was a tendency for MySQL to say Replication was running when, in fact, it was not. This can happen when the network has intermittency that may delay data transmission of binary logs but not severe enough to timeout the I/O Thread. Since the MySQL process can be inconsiderate by being a little insensitive to the network, I affectionately call this "Network Inconsideration". While the bug report on this is closed, it is good to have multiple ways to check MySQL Replication as to its ability to run, especially the I/O Thread. Using MySQL 5.5, you could adjust the sensitivity of the I/O Thread using the the heartbeat and timeout parameters centered around Semisynchronous Replication.


Is it possible to define a foreign key with duplication and cascading delete only if no values exist? I have the following scenario:

A survey table with the columns survey_type and survey_version.
(survey_type, survey_version) is a primary key of the survey table.

I also have a request table. It contains requestIDs that are associated with a survey_type. Whenever a user initiates a survey for a certain requestID, they will get the most up to date survey_version.

I wish to enforce the restriction that allows creation of requests only for survey_types that actually exist. I also wish for requests to be deleted if the associated request_type is removed from the system. A FOREIGN KEY with ON DELETE CASCADE is almost a perfect solution, except that I need the cascading to happen only if all records with a certain survey_type are removed. If we are only removing an old and no longer used version of a survey_type, but records of that survey_type still exist in the survey table, then the survey request should also remain in the system.

Is this possible to do with FOREIGN KEY constraints, or should I look for another way?

Is this possible to do with FOREIGN KEY constraints, or should I look for another way?

Look another way. I'm not 100% I understand what's happening from that description but ON DELETE CASCADE quite explicitly means "allow the deletion to cascade". There are no buts about it.

Or, just make sure in the app, you check -- in a transaction -- that there are no remaining survey_type's then you can delete and allow it to cascade naturally.


Database starts always in recovery mode Everytime I restart my server, the database is always in recovery mode, and it takes about 20 minutes for it to behave as normal.  This always and only happens when I restart the server, so I have a few questions...

I was told this could be caused by a large log file? Could that be correct? If not then what could be the other causes?
I need to lower the space of the log file to prevent recoveries.  What is better: shrinking or truncating?
How can I shrink or truncate a log file / database to lower the size? What is the syntax?

I am currently using Microsoft SQL Server 2008.

I have the same issue and I believe I have resolved it but I have not been able to fully test it to confirm.

I believe the issues is related to the number of VLFs you have in your log file and not its size. If you have a large logfile it is likely that it grew organically through auto growth events and that it was not an intentional planned growth. If that is the case the you might have thousands of VLFs inside log files.

Here is a query to see how many VLFs you have that I used from here:

    Create Table #stage(
    FileID      int
  , FileSize    bigint
  , StartOffset bigint
  , FSeqNo      bigint
  , [Status]    bigint
  , Parity      bigint
  , CreateLSN   numeric(38));

Create Table #results(
    Database_Name   sysname
  , VLF_count       int
);

Exec sp_msforeachdb N'Use ?;
            Insert Into #stage
            Exec sp_executeSQL N''DBCC LogInfo(?)'';

            Insert Into #results
            Select DB_Name(), Count(*)
            From #stage;

            Truncate Table #stage;'

Select *
From #results
Order By VLF_count Desc;

Drop Table #stage;
Drop Table #results;

For a further explanation of what VLFs are see this link.

I believe the issue is that with so many VLFs it takes SQL server a long time to assess their state and then bring the database out of recovery. If you shrink your log file to the smallest size you can, often the size of the first VLF that was created in the log file, then you can immediately intentionally grow it again and thereby have it create the right number of VLFs (something less than 16).

Once this is complete I believe you will be able to see that your database comes out of recovery much faster.

I have not had a chance to test fail over of our production instances after I resolved our own VLF issues so I would be very curious if you can confirm this is the root cause of the issue. Experimentally I have seen the time it takes to come out of restoring in our staging environment dramatically reduced due to this so hopefully that is it.


oracle how to call procedure This is very basic question, but I couldn't find an answer here...

So I have this very useful procedure without any parameters:

CREATE OR REPLACE PROCEDURE DISABLE_TRIGGERS
IS
  v_statement VARCHAR2(500);
  CURSOR trigger_cur
  IS
    SELECT trigger_name
     FROM   user_triggers;
BEGIN

  FOR i in trigger_cur
  LOOP
    v_statement := 'ALTER TRIGGER '||i.trigger_name||' DISABLE';
    EXECUTE IMMEDIATE v_statement;
  END LOOP;
END;

And I don't know how to call it...

This way:

begin
    DISABLE_TRIGGERS;
end;

I get a message that the procedure was compiled.

So maybe this way:

begin
    DISABLE_TRIGGERS()
end;

?

Which way is correct?

If it does not take any parameters in or out then

BEGIN
DISABLE_TRIGGERS;
END;

Or when using the sqlplus interface EXEC DISABLE_TRIGGERS;

I have to hope that this is not production code as a procedure that disables all the triggers could have many consequences.  This code as originally posted should be more comprehensive if used in production, particularly for a migration.  Triggers do things, whether you agree with their use or not, they can enforce business logic and stitch together poorly implemented business logic.

Does the migration depend on any business logic in the triggers?
Will users be able to access the database during the migration? Hope not, any actions they take will not use the trigger logic.
does the success or failure of the migration depend on triggers being disabled?

You need to know when the triggers were disabled and re enabled and that all transactions stopped at the start of the migration and only resumed after the migration. Before, during and after the migration you need a log of all the actions that were taken so you can improve the process.

A better way to do this would be to add a parameter: 0 or 1 to disable or enable the triggers

something like

CREATE OR REPLACE PROCEDURE CHANGE_TRIGGERS( action_in IN NUMBER)
IS
  v_statement VARCHAR2(500);
  CURSOR trigger_cur
  IS
    SELECT trigger_name
     FROM   user_triggers;
v_action VARCHAR2(200);
BEGIN
ASSERT (action_in IN (0,1));  --where assert is part of your own error package
IF action_in = 0
THEN
  v_action := 'DISABLE';
ELSIF action_in = 1
THEN
  v_action := 'ENABLE';
END IF;
  FOR i in trigger_cur
  LOOP
    v_statement := 'ALTER TRIGGER '||i.trigger_name||' '||v_action;
    EXECUTE IMMEDIATE v_statement;
  END LOOP;
--add a statement like
--if debugging then DBMS_OUTPUT.PUT_LINE(v_action||' triggers action taken on '||CURRENT_TIMESTAMP);
--ELSE
--log result to a table
--END IF;

END CHANGE_TRIGGERS;


Impact of using UUIDs with an UNIQUE index vs. PK? It's well known how the performance of random UUIDs as PKs in an InnoDB table degrades terribly as it increases in size. Would an UNIQUE index for a non-PK UUID column have the same impact?

UUIDs are Version 4, random, stored as binary(16).

I guess you are referring to the InnoDB clustered index performance. If I'm not mistaken, the PK is chosen as the clustered index by default, so any other, secondary index containing the UUID column should not suffer from the same problem.

Alternatively, I think you can create the table without declaring the PK at first, in which case MySQL will create an automatic clustered index based on row IDs. You can then alter the table and add the necessary primary key constraint.


SQL - Find top rows where parameter is the same I am learning sql and I am jumping into more complicated queries. I have the following tables:

players:
Columns: name, school_name, gender

simple:
Columns: player_name_fkey, school_name_fkey, category, tournament_number, score

My entries in the simple table will be similar to this. Each line is the score by a player at a specific tournament number.

Joe, Harvard, Junior, 1, 40
Joe, Harvard, Junior, 2, 30
Joe, Harvard, Junior, 3, 60
Anne, MIT, Senior, 1, 45
Anne, MIT, Senior, 2, 55
Anne, MIT, Senior, 3, 20 etc.

What I am looking to do is to get, for each player, the sum of his / hers top two scores of the season:

Joe, Harvard, 100
Anne, MIT, 90

I have the following query:

SELECT player_name_fkey,
       school_name_fkey,
       sum(t1.score) AS sum_scores
FROM (
  SELECT player_name_fkey,
         school_name_fkey,
         score,
         rank() OVER (ORDER BY score DESC) AS scorerank
  FROM simple
    LEFT JOIN players ON players.name = simple.player_name_fkey
  WHERE category = 'Benjamin'
    AND gender = 'Masculin'
  GROUP BY simple.player_name_fkey,
           simple.school_name_fkey,
           simple.score
) t1
WHERE scorerank <= 2
GROUP BY player_name_fkey,
         school_name_fkey

Unfortunately, this query returns only the top 2 scores over all players. How can I change it to get what I need?

You need to calculate the rank per player. You are currently calculating the overall rank.

To do it per player, you need to add a partition by to your window function:

rank() over (PARTITION BY player_name_fkey order by score desc) AS scorerank

You also need to remove the group by from the inner (t1) query:

SELECT player_name_fkey,
       school_name_fkey,
       sum(t1.score) AS sum_scores
FROM (
  SELECT player_name_fkey,
         school_name_fkey,
         score,
         rank() over (PARTITION BY player_name_fkey order by score desc) AS scorerank
  FROM simple
    LEFT JOIN players ON players.name = simple.player_name_fkey
  WHERE category = 'Benjamin'
    AND gender = 'Masculin'
) t1
WHERE scorerank <= 2
GROUP BY player_name_fkey,
         school_name_fkey


Does taking a log backup, truncating the log, and then deleting the tlog backup break the LSN chain? We have avamar tool taking backups in my environment. The backup plan avamar does is only taking trans backups once a day. I am convincing them to take the trans backup more frequent. But in the meanwhile, I have to deal with large log sizes because of this infrequent trans backups. Hence to reduce the log sizes, I am taking a quick trans backup followed by a truncate log size. Now I want to know if doing that breaks the log chain for avamar and it forces it to take full back up instead. Remember, I am deleting the trans backup file immediately after I truncate the log sizes. Does the fact that I deleted the trans backup file breaks the log chain for Avamar? Any input is appreciated.

If the avamar tool uses its own tracking method to know what log backup files it can restore, then it will fail.

If it uses SQL internal tables to know what log backup it needs, that is good, but if the file has been deleted, then it will fail.

Either way it will fail since you are deleting the files...so don't :)


SQL Server, ServicerBroker for asynchronized execution of stored procedures I followed this tutorial:
https://gallery.technet.microsoft.com/scriptcenter/Using-Service-Broker-for-360c961a

and it is working for me,
However,
I don't understand some thing:
At PROCEDURE proc_BrokerTargetActivProc we have infinite loop: WHILE (1=1). Why ? After all, during creating queue we bind messages with this procedure:PROCEDURE_NAME = proc_BrokerTargetActivProc.

In addition, I am not sure If I correctly understand way of working it:
ExecuteProcedureAsync push to queue message with name of procedure to execute.
What now ? How does it work that BrokerTargetActivProc will be called with exactly one message ?

What about parameter MAX_QUEUE_READERS = 5 ?

Thank in advance,
Regards

At PROCEDURE proc_BrokerTargetActivProc we have infinite loop: WHILE
  (1=1). Why ?

The process for receiving messages in SQL Server requires what I call a semiactive polling. The code makes a request to the queue for the next message, then waits for a response. That is what the WAITFOR block is doing.

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM TASK_QUEUE
), TIMEOUT 5000;

If the process was not in an infinite loop, the procedure would poll once, either receive a message or timeout, then quit. With the infinite loop, the procedure polls continuously until stopped.

  ExecuteProcedureAsync push to queue message with name of procedure to
  execute. What now ? How does it work that BrokerTargetActivProc will
  be called with exactly one message ?

When ExecuteProcedureAsync is called, it places the message in an asynchronous message queue. This is a process that is external to SQL Server, accepts messages from sending processes, and forwards them to the receiving process when it is ready for the next message (that is, when the receiver polls the queue for the next message on the queue). The reference to BrokerTargetActiveProc when putting the message on the queue is letting the queue know where the message is going. It is not an interrupt that triggers the BrokerTargetActiveProc procedure.

In an asynchronous queue like this, if the receiver is down, then the messages simply wait in the queue until the receiver comes back up or the queue runs out of resources to hold messages.


Why MySQL tends to use more and more swap while there is enough RAM? I have a server with 64GB RAM and 64 GB swap.
It's running MySQL 8.0.19 and few php scripts.
Here is my config:
innodb_buffer_pool_size=40G
innodb_buffer_pool_instances=4
innodb_buffer_pool_chunk_size=10G
sync_binlog=0
innodb_log_file_size=1G
innodb_ft_result_cache_limit=4000000000
max_heap_table_size=4G
tmp_table_size=4G
innodb_online_alter_log_max_size=512M
range_optimizer_max_mem_size=0
performance_schema="OFF"

This server is used for heavy calculations, maximum 5 scripts running at same time, no users.
The huge swap is necessary for rare cases when some script needs a lot of RAM for itself.
The more server works the more memory MySQL tries to consume. If I don't restart it during few weeks it ends up with 90GB total memory consumed (~60RAM + ~30 in swap). And all of this degrades the performance drastically.
For now I don't know any solution besides simple restart. It clears entire swap and makes MySQL restart with ~40GB RAM consumption.
Do you have any ideas how to make MySQL not use swap that much? Perhaps this happens when some PHP script allocates too much RAM and pushes MySQL's memory into swap, but once that program finishes, MySQL doesn't free swap as I supposed it should do.

Yes, you can make MySQL not use as much swap by making it use huge memory pages. Huge pages are unswappable.
In my.cnf, [mysqld] section:
large-pages = 1

Additional OS level configuration is required. For example:
groupadd -g 630 hugetlb
usermod -G hugetlb mysql
sysctl -w vm.nr_hugepages = 20480
sysctl -w vm.hugetlb_shm_group = 630

You may need to increase nr_hugepages a little, look at the logs while it is starting for clues on when it no longer needs to be increased.
Additionally, 10GB buffer pool chunk size is absurdly large. Comment it out. Also, optimal for buffer pool instances is between 1GB and 2GB per instance, so a setting of 40 would be more appropriate than 4 for a 40GB buffer pool.
And finally - if your PHP script can use upward of 40GB of RAM, I dare say you need to fix that. Unless you actually need to manipulate 40GB of data in memory at once, there is almost certainly a better way.


MySQL incredibly slow on WAMP and XAMPP I've installed WAMP on my new Windows 8 machine and for some reason when I use PHPMyAdmin to import an sql file (it's only about 5mb), it takes several minutes before timing out. And then when I check I can see it's only imported a fraction of the tables.

I've tried other sql files and it's the same story. These same sql files import perfectly fine (and in a few couple of seconds) on my Windows 7 machine also running WAMP.

I've since uninstalled WAMP and tried XAMPP, and the problem still exists. Even just browsing the databases with PHPMyAdmin takes a long time between page loads.

I am guessing it's a problem with MySQL. Any suggestions would be helpful.

When MySQL is really slow on windows (not 30ms slower) is nearly always a matter of a forgotten localhost instead of 127.0.0.1 in settings or URL. Very few times it is due to a very slow fragmented hard drive (acting slower due to windows complex ACL and parallel processes using it). MySQL is slower in some benchmarks on windows but the difference is so small that cant change your experience.


MySQL Query Cache - what type of queue is used? I am wondering what type of queue is MySQL Query Cache using.
When there is new query and cache is full which query is deleted? Oldest one? One with fewer number of hits? Or maybe there is some different logic behind that?

I tried to find something in documentation, but without success.

According to the manual, the query cache removes the Least Recently Used (LRU) Query from the Query Cache when the Query Cache is full. Read the general chapter on the MySQL Query Cache for a more detailed description of the logic and architecture of the Query Cache.


How to archive a transactional table on another server? (Feel free to tell me the correct name for this... it is a common scenario!)

We have a production SQL Server 2008 box. One table is heavily transactional. It is also purged every few days (old records are deleted).

We want to set up a 2nd SQL server to receive all the rows from this table and archive them for later analysis. We need to archive all rows, including rows that are purged from the production table. E.g. we want to push over insert and update, but not delete.

Some stats:

DB itself is about 1TB
The table we would like to archive: record size is about 1K
The table we would like to archive: 50K-150K records/day are created (and later purged)

We are looking for:

Data to move at least once each 24 hours. (more frequently is fine, but not reqd)
Simple to set up. (the three-box transaction replication looks rather top heavy)
If possible, the receiving system could be a different ver/flavor of SQL (e.g. receiving system could be SQL Server 2012 express -- yes, I know about the SQL Express db size limit--just looking for flexibility)

I thought there would be a proc out there that does this, but I haven't found one yet.

Caution:

Since you will be using SQL Server 2012 express edition (and sql 2008R2), it has a limit of 10GB per database size (for previous versions, it is still 4GB). So if your table is not going to grow that big with all the archive data for many years, you are good uptill it hits 10GB limit.

My first suggestion would be to use Transactional replication. It wont have that much of overhead as you are just replicating 1 table. If you set the agent to run continuously or every 5 mins, then you will have up-to-date data on the destination (subscriber). You just have to remember to not replicate delete statements.

Secondly, you can use SSIS and schedule it using SQL Agent Job to incrementally load the data into the destination server.


PostgreSQL 9.3 Amazon RDS gives error while using UUID-OSSP extension for generating uuid I have created DB instance on RDS and used below function. It gave error while with the same PostgreSQL version on a local development machine it works fine.

create or replace function uuid() returns uuid as 'uuid-ossp', 'uuid_generate_v1' volatile strict language C;

here is the error log:

ERROR:  permission denied for language c
********** Error **********

ERROR: permission denied for language c
SQL state: 42501

I'm using this function in ruby on rails 3.2 for generating uuid for my schema.

You appear to be attempting to load uuid-ossp extension by loading the .sql file directly. You should not be doing that on any modern PostgreSQL, and it won't work on RDS.

Use

CREATE EXTENSION "uuid-ossp";

instead.


Restore a database to different databases without losing any data I work on a database structure that has a lot of data in it.  This database structure is used by approximately 40 different servers.  I want to change some of the table structures, by adding some columns to tables, etc, but I do not have time to change 40 database structures.

How can I change all databases structure without losing any data with a script?

Can I backup a database structure without data and restore it to the other servers without them losing their data?

The servers are Microsoft SQL Server 2005.

You cannot backup the structure and restore that structure to another DB using SSMS; though there are third party tools that can help you, like Redgate's SQL compare which I have used previously for this.

One suggestion is to save your TSQL scripts, and then you can apply these scripts to the other DBs by using Powershell.

Another option is to use SQL CMS, but you will need at least one designated SQL 2008 instance.


Another question about *_buffer_size Mysql-5.6.30 running on Debian, uses InnoDB, DB size about 100Gb. Dual Xeon 5645 with 48Gb RAM, 4 HDD 15K in RAID 10 (/var) and system on RAID1

There're many "heavy" ORDER BY/GROUP BY, JOIN/sort queries, they create many TMP tables on HDD. And, OFC, there're many "unoptimal" queries, which doesn't use indexes and also creates TMP tables.

How can I determine good values for sorting/grouping? 'Cause now these kind of queries extremely slow. Reading tons of articles, using optimizing scripts like mysqltuner only made things confusing about value settings...

[mysqld]
port = 3306
connect_timeout=100
expire_logs_days=7

innodb_log_buffer_size = 8M
innodb_log_file_size = 900M
innodb_file_per_table
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 6
innodb_lock_wait_timeout = 200
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 8

join_buffer_size = 2M
key_buffer_size = 384M
max_allowed_packet = 200M
max_heap_table_size = 64M
open_files_limit = 10240
query_cache_limit = 2M
query_cache_type = 1
read_buffer_size = 2M
read_rnd_buffer_size = 14M

socket = /var/run/mysqld/mysqld.sock
sort_buffer_size = 48M
table_open_cache = 640
thread_cache_size = 32

### tmp_table_size = max_heap_table_size
tmp_table_size = 64M
thread_concurrency = 0

##### Replication #####
server-id= 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 5
max_binlog_size = 1024M
##### End replication #####

##### Logs #####
slow_query_log=1
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1
##### End Logs #####

query_cache_size = 96M
max_connections = 300
max-connect-errors = 1000
skip-name-resolve

Especially sort_buffer_size, join_buffer_size, tmp_table_size. And, any recommendations about config will be good and pleased :)

UPD: running next script:

SET @SleepTime = 300;
SELECT variable_value INTO @SMP1
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SELECT SLEEP(@SleepTime) INTO @x;
SELECT variable_value INTO @SMP2
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SET @SMP = @SMP2 - @SMP1;
SET @SMP_RATE = @SMP * 3600 / @SleepTime;
SELECT @SMP,@SMP_RATE;

gave @SMP=24, @SMP_RATE=288

Changed values to

join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M

Now server creates many on-disk tmp tables with zero-length. Then, re-run script and it gives:
@SMP=258, @SMP_RATE=3096

TUNING-PRIMER script gives advise to increase tmp_table_size/max_heap_table_size. Will set tmp_table_size=96M, max_heap_table_size=96M and see the reaction.


Is it possible to use a column value as the argument to a hypothetical-set aggregate function? Say I have a table like the one described below.

                                     Table "public.foo"
  Column   |            Type             |                    Modifiers
-----------+-----------------------------+-------------------------------------------------
 id        | integer                     | not null default nextval('foo_id_seq'::regclass)
 data      | text                        | not null
 score     | integer                     |

To calculate the percentile rank for a particular score, I can do something like this:

test=# select percent_rank(5) within group (order by score) as rank from foo;
        rank
--------------------
 0.0762917366163029
(1 row)

What I'd really like to do is have a select statement that shows the data, score, and rank for each row present. I tried the below query.

test=# select data, score, percent_rank(score) within group (order by score) as rank from foo group by data, score;

This produces a rank of 0 for every row. I'm assuming this is because the within group clause ends up being limited by group by clause, but I'm not enough of a SQL ninja to know for sure.

At any rate, is it possible to include the percentile rank of a row's score as part of a multi-row select?

If you include ID I would assume that you can join it back to itself on ID to get that information in one dataset.


Too many TIME_WAIT connections in PostgreSQL I am using PostgreSQL 9.0 on Centos 5.7 Final.

At any given there are around 1,500-2,200 connections open for PostgreSQL on port 5432 which are in TIME_WAIT state.

I have a feeling this is not good.
Is this wrong? If yes how to get rid of this?

EDIT

There are mainly 3 applications connecting to the database:

A Server which inserts around 5,000 rows every minute.
A web app.
A reporting server

1500 to 2200 concurrent connections are only useful when you need them. Otherwise, bring the total number of open connections down and start using a connection pool like pgBouncer or PgPool-II. See Number of Connections (on the wiki). We do about 2500 transactions per second on 150 connections.

Don't forget, each connection uses RAM, RAM that could be used to get some real work done. PostgreSQL gets slower when you use too many connections without a connection pool. What "too many" is depends on your hardware and workload, but it's very unlikely that your system will perform better with 2000 than it will with 100; probably much worse.


RAID Storage Technology Outdated? I am working on designing requirements for new SQL Server installation. I have been researching disk storage and everything seemed to point to using RAID.

However, I found this article, which suggests RAID is no longer relevant today.

Can anyone point me in the direction of where I can learn about the latest disk storage for SQL Server.

Before jumping at one article you should do a little research, after a short Google search "windows storage pools vs raid" I found this article which goes more in-depth. As far as I can tell this new tech doesn't completely replace raid but could be considered an alternative to raid ONLY if you are using windows server 2012. It also may not make sense if you already have the hardware to support hardware raid as (I'm guessing) using this new storage mechanism will increase the cpu/memory loads on your server since it has to do the storage calculations in software. So I wouldn't say RAID is no longer relevant, it just has some healthy competition.


Update selected data from same table column as currency I am trying to update selected data from a table column and format this data as currency.

My table column holds strings that have both mixed whole numbers and already formatted data. This was due to the fact that I had migrated old data which were whole numbers but any new data or updated old data gets formatted correctly in the application and database.

I would like to select all the non-formatted data from this table column  and then Update this data to currency format.
0 to 0.00,
200 to 200.00,
23702 to 23,702.00,
34000.00 to 34,000.00,
2954795.54 to 2,954,795.54

Any assistance would be highly appreciated.

Thanks

You really want to store data in fields of the correct Data Type.
If these values are monetary amounts, then hold them in a suitable Data Type, like Currency.

Any time you convert data from one type to another, you're make extra work for the database engine, having to do the conversion, and running the risk of losing the benefit on any Indexes you might have defined, because calling a function on a field in every row generally forces the database to Table Scan that table (unless you have function-based Indexes, but that's another story).

Using the "wrong" Data Type also means that some functionality "doesn't work" properly.  If you're storing Numerical values as Strings, then comparing these values "goes all wrong".

For example, in the Numeric World ...

10 > 2  =>  TRUE

... as you'd expect, but in the Character world ...

'10' > '2'  =>  FALSE


Set search path for Postgres in a Rails app I have a project in heroku. I have the URI that enables me to connect with the database in my external rails app. How do I change or set the search path in ruby code?

You can define it in your database.yml using the schema_search_path key with a string value

production:
  adapter: postgresql
  ...
  schema_search_path: 'schema1,public'

You can find out a bit more here: https://til.hashrocket.com/posts/5aa2892b43-set-schema-search-path


Prevent reverse engineering of encrypted views I have an encrypted view definition in SQL Server that has some information that I want to protect. The definition looks something like this:

CREATE VIEW myview WITH ENCRYPTION AS
SELECT name, age FROM customer WHERE c_city = 'NY'

Now I don't want to expose the fact that the view has a predicate on c_city with the value 'NY'. But when I run the query and see the execution plan for the following query:

SELECT * FROM myview

I can see the predicate on the underlying table with the constant value as well. So anybody can reverse engineer my view logic this way. How can I prevent this?

Bad news: check out dbForge SQL Decryptor. Anyone can decrypt your encrypted objects.


Allocating 8GB memory to MySQL on a 64bit system Specs -

MySQL 5.0.x  ,  Redhat 5.9  ,  Physical memory - 16GB

I am trying to set innodb buffer pool size to 8GB (innodb_buffer_pool_size=8G). When i do, and start mysql, i get following error -

InnoDB: Error: cannot allocate 8589950976 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 37679104 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.

Here is output of free -m -

             total       used       free     shared    buffers     cached
Mem:         15922      15269        653          0        248       4275
-/+ buffers/cache:      10744       5178
Swap:         2047          0       2047

Here is output of ulimit -a -

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 127352
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 127352
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

I checked file '/etc/security/limits.conf'. Nothing in there. All lines are commented (start with #). Checked directory '/etc/security/limits.d/', empty.

Something is preventing allocation of more than 4GB of memory to MySQL. Not sure what.

Any ideas?

Danke.

memlock was the issue.
turns out, memlock settings were at default (see above), and this might have been preventing the allocation of 4+gb of memory to mysql.

changed memlock settings in '/etc/security/limits.conf', and limit to 8GB.
then, allocated 7GB to mysql buffer pool. worked. mysql now starts and stops without throwing any errors in the log.

a few things to note here -

from what i have read online, the amount of memory allocated to
mysql is slightly more than the setting for buffer pool. so lets say
you assign 7GB to pool, actual memory assigned to mysql would be
closer to 8gb (this is an example, not real numbers).
turning the mysql service on/off has to be done very carefully. i
    have noticed that when i do a mysqld-start followed by quickly by a
    mysqld-stop, mysql responds with [FAILED]. logs says allocation of
    7GB failed.


mysql backup validation Need help regarding backup validations for MySQL, what is the best way / easiest way to validate Mysql backups.

we are using mysqldump backup for taking backup's and tar the backup file at the end, could not find any solution on my research.

Any help from this community, Thanks in advance.

The only way you can validate a backup is to restore that backup; plain and simple. Spin up a VM, install the same version of MySQL from which you took the backup, restore the backup and run some simple tests. If you make this new VM a slave of the original master, configure replication and use pt-table-checksum to validate all data is the same on the slave. At the very least you can use CHECKSUM TABLE <table>; on every table in every database to validate that MySQL is capable of reading every row.


How to get MAX() with an upper bound? I am using the query below to generate a report. I have a requirement to generate the report so it only applies up to a maximum timestamp (such as until yesterday, 2016-08-02). To do so, I need to modify the second line of the query so that I no longer get values of eventsink.blueboxevent.created that are greater than the limit of yesterday.

For example, if I ran the report on the sample data below with a requirement to only look at the timeframe up until yesterday (2016-08-02), I would want to see a record as follows:

Desired Result

device1 | 2016-08-02

Sample Data

DeviceID | eventsink.blueboxevent.created
device1 | 2016-08-03
device1 | 2016-08-02
device1 | 2016-08-01

Current query to be modified

SELECT eventsink.blueboxevent.deviceid     AS 'Device ID',
       Max(eventsink.blueboxevent.created) AS 'Last Connection',
       provisioned,
       Max(softwareversion)                AS 'S/W Ver.',
       buildingname                        AS 'Name',
       buildingaddr1                       AS 'Address',
       buildingcity                        AS 'City',
       buildingstate                       AS 'State',
       zipcode                             AS 'Zip Code',
       countrycode                         AS 'Cnty'
FROM   eventsink.blueboxevent
       JOIN md.elevator
         ON md.elevator.deviceid = eventsink.blueboxevent.deviceid
       JOIN eventsink.blueboxmasterdataevent
         ON eventsink.blueboxmasterdataevent.deviceid =
            eventsink.blueboxevent.deviceid
       JOIN (SELECT Min(eventenqueuedutctime) AS 'Provisioned',
                    deviceid
             FROM   eventsink.blueboxevent
             WHERE  id = '1'
             GROUP  BY deviceid) t
         ON eventsink.blueboxevent.deviceid = t.deviceid
WHERE  ( id = '9'
         AND softwareversion LIKE '[1-2][.]%' )
GROUP  BY eventsink.blueboxevent.deviceid,
          provisioned,
          buildingname,
          buildingaddr1,
          buildingcity,
          buildingstate,
          zipcode,
          countrycode
ORDER  BY provisioned DESC

Change:

MAX(created)

with a CASE expression:

MAX(CASE WHEN created <= '2015-12-31' THEN created ELSE NULL END)

The ELSE NULL is not needed as it is the default. Now, if you want to return that '2015-12-31 in case no date is before the threshold date, we can use ISNULL() or COALESCE:

COALESCE( MAX(CASE WHEN created <= '2015-12-31' THEN created END), '2015-12-31' )

or, if in that case, you want the next available date, even if it bigger than the threshold date:

COALESCE( MAX(CASE WHEN created <= '2015-12-31' THEN created END), MIN(created) )

Note: the answer assumes that created is a DATE (or a DATETIME that doesn't have a time part). If it does have time parts, I suggest you replace the use of <= with < (i.e. replacing the <= '2015-12-31' with < '2016-01-01'). See a detailed explanation of the problem with inclusive bounds at Aaron Bertrand's blog post: What do BETWEEN and the devil have in common?


Is a single batch request equivalent to a single tsql request? I'm looking into some performance metrics and every recommendation includes monitoring  Batch Requests/sec. But, maybe a silly question, does a singe batch request correspond to exactly one tsql statement? Say it contains exactly one select or one update, etc. So, for example, a stored procedure with 3 selects and 2 inserts triggers 5 batch requests. Or, as I think, a batch request can include for example a full stored procedure, a function, several select/update/insert statements, etc.

All I can find is similar to these descriptions:

TechNet Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so on). High batch requests mean good throughput.
Understanding how SQL Server executes a query Batch Request This request type contains just T-SQL text for a batch to be executed. This type of requests do not have parameters, but obviously the T-SQL batch itself can contain local variables declarations. This is the type of request SqlClient sends if you invoke any of the SqlCommand.ExecuteReader(), ExecuteNonQuery(), ExecuteScalar(), ExecuteXmlReader() (or they respective asyncronous equivalents) on a SqlCommand object with an empty Parameters list. If you monitor with SQL Profiler you will see an SQL:BatchStarting Event Class

As per the comment, then no - it's not a single statement.
A batch can cover multiple individual T-SQL statements in a single batch.


enum vs boolean in case of multiple yes/no fields I want to store information from a form where there are multiple yes/No question. For those how should I create table fields? I have experienced mysql doesn't support multiple columns to have same ENUM values. So I can't assign 'yes','no' for more than one column.

If I use boolean? how should I do it? Is it mandatory to have default values for boolean type field?


Is it good idea to repair sql file? We are having an ETL process which inserts lots of data into tables. This database is set to Simple Recovery Model and the transaction log is growing a lot. I was thinking that would it help to set this database into Bulk-Logged Recovery Model? We are taking full backups on daily basis. So is there some operations which are not logged in Bulk-Logged Recovery Model compared to Simple Recovery Model?

Logging occurs in all recovery models. The log is retained in bulk logged and full recovery models until the log is backed up. Bulk logged only minimally logs certain actions e.g. an index rebuild, meaning you can't do point in time restores when using bulk logged. As you are using simple recovery model, changing to bulk logged will be of no benefit to reducing log size. It would make your setup more complex actually as you would have to start taking regular log backups.

If you have a large transaction in simple recovery model, this transaction will still be logged and cause your log file to grow. This is what allows you to rollback the transaction in any recovery model. If you left that transaction open (don't commit or rollback) your log would grow and grow. Once committed (or rolled back) that logging information no longer has to be retained in simple recovery model and SQL server will reuse the log in a cyclic nature.

Your solution would be to minimise the size of your ETL insert transactions. You could perform smaller inserts in batches and commit those (if that is appropriate for data consistency). You could also look at transaction handling in your ETL tool. SSIS for example allows all inserts in a package to be part of the same transaction, you could change this behaviour so each transformation has it's own transaction.


Oracle function based index is not used in query I cannot figure out why the following is not working as I expect it :

CREATE TABLE table1(table1_id INT NOT NULL PRIMARY KEY,
      col1 INT NOT NULL,
      val1 VARCHAR(50));
CREATE INDEX IDX_table1_fnct ON table1(CASE WHEN col1 = 3 THEN val1 ELSE NULL END);

SELECT COUNT(val1) WHERE col1 = 3 ; shows full scan

I'd expect new index to be used for such queries, but it's not even if I try to force it with INDEX hint...
Could anyone clarify why it works this way (I'm using Oracle 10 if that matters)?

Thank you

You can logically see that the index would narrow the scope of the count, but the database can't make the same determination.  To use a function based index you need to use the function itself in your query.  Assuming the data is such that the index will be useful, something like this should cause it to be used:

SELECT COUNT(val1) WHERE CASE WHEN col1 = 3 THEN val1 ELSE NULL END IS NOT NULL;


MySQL: select a row if that row is equal for every user In my database I have 5 fix users, lets say user1, ..., user5.

Furthermore I have a table

month(name, finished, month, year)

where name is VARCHAR (the name of one of the five users) and the others are INTEGER. In row finished, 0 means not finished and 1 means finished (Note that finished does not mean 'the month is over' in this context). Every user has an entry for each month.
I'm struggling to find a query which returns me the months where every user has the entry finished = 1.

SELECT month FROM month

obviously returns months, where other users can have finished=0

SELECT month FROM month WHERE finished=1

returns the finished months (multiple times), while other users can have finished=0 in this same month and I would have to check in the code if there are months appearing five times (which would mean that month is finished by all users).

Now, without creating triggers or similiar constructs, is there a way to check the desired requirement directly in the SQL-query?

Addendum: I know that a table monthAll(month, year, finishedAll) would be a solution, but setting it to finishedAll=1 if all five users have finished a certain month would equally require additional code in my JavaScript due to the fact how the whole program is constructed.

If there are 5 rows per month (1 for every user), this will do:

SELECT m.year, m.month
FROM month AS m
GROUP BY m.year, m.month
HAVING SUM(finished) = 5 ;


Specifying MySQL user hosts with IPv6 addresses Inspired after making the move to IPv6 on my MySQL server, and intrigued by the lack of documentation regarding 'user'@'[IPv6]' nomenclature I proceeded to discover how to limit my users based on their IPv6 address.

My allocation is a /56 and so I wanted to limit my DB users to this prefix length.  My test host was 2001:DB8:111:6601::a01:120

The following didn't work at all:

2001:DB8:111:6600::
2001:DB8:111:6600::%
2001:DB8:111:6601::
2001:DB8:111:66%::
2001:DB8:0111:6601::%
2001:DB8:111:6600::/56
2001:DB8:111:6601::a01:120/56
2001:DB8:111:6601:0:0:a01:120/56
2001:DB8:111:6601::a01:120/128
2001:DB8:111:6601::/64
2001:DB8:111:6601::%/128
2001:DB8:111:6601::%/1
2001:DB8:111:6601::%/64
2001:DB8:111:6601::a01:0120

The following worked

2001:DB8:111:6601::% (too limiting as it's a /64, not /56)
2001:DB8:111:6601::a01:120 (too limiting as it's a host)
2001:DB8:111:66%::% (but may match 066x in the 7th and 8th bytes)
2001:DB8:111:66__::% (this appears to be the best fit)

Some notes:

don't use leading zeros in fields
don't bother with /prefixlen

(Sorry, I don't have definite answers, but these seem likely:)

:: is shorthand for any number of 0000 segments so as to end up with 8 groups.  So, ::% is ill-defined.

% is the wildcard available in MySQL, not _.

Perhaps % is only applicable for a 16-bit chunk, not part of such.

With ipv4, a mask is given using /.  Example:  198.51.100.0/255.255.255.0: Any host on the 198.51.100 class C network.  I suspect that ipv6 would be similar.  -- https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/account-names.html

So, I would guess

2001:DB8:111:6600::/FFFF:FFFF:FFFF:FF00::


Select multiple ranges in a WHERE clause I am trying to find a way to query a tables in my database. The table is called districts which contains a boroid and area column. Basically the community table looks like:

boroid  |  area
--------+---------------
101     | 41641860.1953
104     | 49300667.7004
106     | 38700249.8776
112     | 77981905.0100
210     | 174703337.756
301     | 132531300.780
306     | 131678770.100
303     | 246427540.049
302     | 167440618.596
402     | 171605041.803

I am trying to find all community districts within the ranges 101-108, 301-303 and only 402. Can someone help me achieve this? Below is my query:

SELECT boroid from districts where ('[101-108]','[301-303]','[402]');

BETWEEN is a nice method to help define ranges.

SELECT boroid
FROM districts
WHERE boroid BETWEEN 101 AND 108
  OR boroid BETWEEN 301 AND 303
  OR boroid = 402;


Time to import an sql dump I'm currently uploading one of Wikipedia's dump files. It has 1GB compressed and ~7GB uncompressed. It has been importing it for 6 hours already. The size of the imported table in MySQL is at the moment 1.5GB(data+indexes). Will it continue to import until the size of the table will reach 7GB?

Note: The data directory of the database in on an external HDD. Considering this is only a development, so I'm not concerned by the real performance.

I assume you are connected by ssh or you are sitting right at the computer.
Open a separate window and run this query in mysql:

SHOW PROCESSLIST;

I was just doing an import of a 3GB (uncompressed) db the other day and I believe that was the query that showed me exactly where the import was in its progress.

In my case, the tables were being imported in alphabetical order, so I knew exactly where it was in its progress.

Also, check out this stackoverflow question's answers for killing process vs killing query.


How to calculate Day on the base of Hours Condition.? SELECT
    Empcode,
    cast(Date as date) As Date,
    MIN(CASE WHEN INOUT = 1 THEN date END) AS INOUT_INTIME,
    MAX(CASE WHEN INOUT = 2 THEN date END) AS INOUT_OutTime,
    DATEDIFF(Hour ,MIN(Date), Max(Date)) AS Hours
FROM
    HR2 AS t
GROUP BY
    Empcode,
    CONVERT(date, date);

I want when Hours=>8 then Day=1 and Hours<=8 then Day =0

please guide..

I want when Hours=>8 then Day=1 and Hours<=8 then Day =0

So when it is 8 you want both 0 and 1?

I have chosen hours smaller than, or equal to 8 = 1 and hours < 8 = 0 as day.

Let me know if that is not what you want.

   SELECT Empcode,cast(Date as date) As [Date],
   MIN(CASE WHEN INOUT = 1 THEN date END) AS INOUT_INTIME,
   MAX(CASE WHEN INOUT = 2 THEN date END) AS INOUT_OutTime,
   DATEDIFF(Hour ,MIN(Date),Max(Date)) AS [Hours],
   case when DATEDIFF(Hour ,MIN(Date),Max(Date)) >= 8 Then 1
        when DATEDIFF(Hour ,MIN(Date),Max(Date))  < 8 then 0
        end as [Day]
   FROM @HR2 AS t
   GROUP BY Empcode,CONVERT(date, date);

Testing

DECLARE @HR2 table(empcode int, date datetime2,inout tinyint)
insert into @HR2(empcode,date,inout)
values(1,'2019-01-03 07:47:46.487', 1)

insert into @HR2(empcode,date,inout)
values(1,'2019-01-03 17:47:46.487', 2)

insert into @HR2(empcode,date,inout)
values(2,'2019-01-03 09:47:46.487', 1)

insert into @HR2(empcode,date,inout)
values(2,'2019-01-03 15:47:46.487', 2)

SELECT Empcode,cast(Date as date) As [Date],
   MIN(CASE WHEN INOUT = 1 THEN date END) AS INOUT_INTIME,
   MAX(CASE WHEN INOUT = 2 THEN date END) AS INOUT_OutTime,
   DATEDIFF(Hour ,MIN(Date),Max(Date)) AS [Hours],
   case when DATEDIFF(Hour ,MIN(Date),Max(Date)) >= 8 Then 1
        when DATEDIFF(Hour ,MIN(Date),Max(Date))  < 8 then 0
        end as [Day]
   FROM @HR2 AS t
   GROUP BY Empcode,CONVERT(date, date);

Result

Empcode Date    INOUT_INTIME    INOUT_OutTime   Hours   Day
1   2019-01-03  2019-01-03 07:47:46.4870000 2019-01-03 17:47:46.4870000 10  1
2   2019-01-03  2019-01-03 09:47:46.4870000 2019-01-03 15:47:46.4870000 6   0


Saving a variable globally in SQL server it self and use it when required We are using ENCRYPTBYPASSPHRASE to encrypt some columns of some database. To decrypt the values every time we need to pass passphrase with DECRYPTBYPASSPHRASE.

So I planned to create a stored procedure as shown below

CREATE function [dbo].[SQLEncrypt](@str varchar(max)) returns varchar(max)
as
 begin
 declare @test nvarchar(max)
 set @test=''

    set @test =   convert(varchar(MAX),DECRYPTBYPASSPHRASE ('password',@str))

 return @test
END

GO

I can use this stored procedure as shown below to decrypt a column

----usgae---

select master.dbo.SQLEncrypt(columnname) from tablename--
-----------------

Now I am planning to store this password somewhere globally in SQL server(Don't know if it is possible or not)  and then use it in stored procedure. IS it possible to do by anyway? Also can I hide the password from users?

Don't.

While is possible to use context_info, that is a really really really bad choice for a password. All users with VIEW SERVER STATE will see it in sys.dm_exec_sessions. SQL Profiler will not know what you're doing when you set it and will not obfuscate it in produced events. And more similar badness.

Instead use proper encryption hierarchy. Do not encrypt the data by passphrase, is never the right choice. Instead encrypt it with a symmetric key. Then open the key in the session using the password, see OPEN SYMMETRIC KEY. The key will become available in your session and the data can be automatically decrypted. This is the recommended way, don't try to outsmart it.


Linked Server - Double Hop - Kerberos - TCP I am having trouble with a Linked Server.

I would typically not use a linked server, but legacy issues are forcing my hand.

I am attempting to set up a linked server between two new production SQL servers. I have configured SQL on both machines with domain service accounts running MSSQLSvc on each respective SQL instance.

The domain service accounts have SPNs registered;
I have verified this through AD attribute editor(sp?). I can perform single hop queries on the actual servers, but no luck with the double hop from my laptop (domain admin).

I have used the Kerberos Config tool on each server and configured what I can.

This is where google falls short on this issue. When I use the Kerberos Config Tool on one of the servers I get this for my service account running MSSQLsvc:

TCP must be enabled to use Kerberos authentication.

In the SQL server config manager, I have TCP/IP enabled.

I have restarted services and the server on several occasions.

Any thoughts? I can provide more information.

Verify that both SQL Servers can use Kerberos

connect from your laptop to SQLSERVER1 using windows auth.
Run this query: select auth_scheme from sys.dm_exec_connections where session_id = @@spid It should return "Kerberos".
If it returns "NTLM":
Verify no duplicate spn's  (SETSPN -X)
Verify correct spn's (SETSPN -L)
Repeat for SQLSERVER2

Once you can connect to both servers using Kerberos, then you need to ensure that SQLSERVER1 is trusted to delegate to SQLSERVER2.  This is set in AD on the service account.


Verify if I can use a given directory Admin creates an Oracle directory:

CREATE OR REPLACE DIRECTORY EXAMPLE AS '\\server\share\test';
GRANT READ, WRITE ON DIRECTORY EXAMPLE TO JOHN;

Admin can test whether the directory exists:

SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='EXAMPLE'

How can John verify whether directory EXAMPLE exists? How can he find out what permissions he's been granted?

Permissions are exposed to a DBA in DBA_TAB_PRIVS.

SELECT * FROM dba_tab_privs WHERE table_name='EXAMPLE';

Your user John will be able to see his grants in ALL_TAB_PRIVS.


Find an object cause to invalid package in Oracle When DB admin drops then create a table, some packages became invalid. I can easily recompile them. But how to know exactly what is the table(dependant object) that is the cause that  a package to became invalid?

After the tables were dropped, views related to dependencies no longer contain information about the dropped table, but you can query DBA/ALL/USER_ERRORS.

SQL> create table t1 (c1 number);

Table created.

SQL> create or replace procedure p1 as
  2    l_c number;
  3  begin
  4    select count(*) into l_c from t1;
  5  end;
  6  /

Procedure created.

SQL> select status from user_objects where object_name = 'P1';

STATUS
-------
VALID

SQL> drop table t1 purge;

Table dropped.

SQL> select status from user_objects where object_name = 'P1';

STATUS
-------
INVALID

At this point, the procedure becomes INVALID automatically, but you can not query errors related to it yet:

SQL> select * from user_errors where name = 'P1';

no rows selected

So compile it, and query again:

SQL> alter procedure p1 compile;

Warning: Procedure altered with compilation errors.

SQL> select * from user_errors where name = 'P1';

NAME TYPE        SEQUENCE       LINE   POSITION TEXT                                            ATTRIBUTE MESSAGE_NUMBER
---- --------- ---------- ---------- ---------- ----------------------------------------------- --------- --------------
P1   PROCEDURE          1          4         33 PL/SQL: ORA-00942: table or view does not exist ERROR                  0
P1   PROCEDURE          2          4          3 PL/SQL: SQL Statement ignored                   ERROR                  0

Now you could view the source of P1 at line 4, position 33, and check what if refers to.


Sp_forEachDB ' DBCC Check DB' - why does it only check the first DB? I set up a checkDB job, and I used sp_foreachDB, thinking I was really clever. Looking at the job in detail, however, it only seems to do the Master Database then stops, reporting success.

I can grow my own like this:

Create procedure JM_CheckDB

as

declare @DB_Name varchar(255),
@sql nvarchar(max)

declare c cursor local for

SELECT name FROM master.dbo.sysdatabases

open c
fetch from c into @DB_Name

While @@FETCH_STATUS = 0
begin

set @sql = 'Use ' + @DB_Name + ' DBCC CheckDB'

print @sql
exec sp_executesql @sql

fetch next from c into @DB_Name

end

close c
deallocate c

But I'd rather use the same thing everyone else uses.

Question: What's the right way to do sp_foreachdb 'dbcc checkdb'?

Answer: From SpBlitzErik - use Ola hallengren's scripts, you dummy.

Followup Questions:

How often do I need to run it? It needs to run at the same interval that I keep my backups, right? If I keep the backups for 1 day, I need to run it every day?
Can I restore the databases to my test environment and checkDB there?
If I've got lots of sql servers, but they all use the same SAN, am I going to want to stagger my CheckDB's?

Question: What's the right way to do sp_foreachdb 'dbcc checkdb'?

Don't use sp_msforeachdb. If you're dead set on using it, though (given the many known drawbacks and issues) then you'll need to set the database context before attempting to run checkdb without a database name, or use the database name parameter.

Assume: exec sp_MSforeachdb 'SELECT DB_NAME()'

The output of this will be 'master' however many times as the number of databases that reside on the instance. Simple put, the stored procedure gives you the database name and will run it (hopefully for each database, though known to skip) but not set the context.

Output: master, master, master, master,...

Assume: exec sp_MSforeachdb ' use [?]; SELECT DB_NAME()'

The output of this will be the names of the actual databases (again, hopefully it didn't skip any) on the instance.

If you'd like to run CheckDB this way, you could use:

exec sp_MSforeachdb ' use [?]; DBCC CHECKDB()'
OR
exec sp_MSforeachdb 'DBCC CHECKDB([?])'

  Answer: From SpBlitzErik - use Ola hallengren's scripts, you dummy.

Also valid and a much better solution.

  1.How often do I need to run it? It needs to run at the same interval that I keep my backups, right? If I keep the backups for 1 day, I need to run it every day?

IMHO totally depends on the environment and the performance hit. Regardless I'd say once a week (for a total check, not necessarily all at once) is the minimum. Once a day is ideal but may be overkill, especially on large databases where a good portion of the data is read only/archive.

  2.Can I restore the databases to my test environment and checkDB there?

Yes, it'll test your backups and the database but won't test the primary server. This means you might not have any issues on the test server but the production server may have intermittent IO issues - this won't show that. Still better than nothing and you're testing backups :)

  3.If I've got lots of sql servers, but they all use the same SAN, am I going to want to stagger my CheckDB's?

Depends on how much you like/dislike/abhor your SAN admins. If you want to piss them off, fire them all off at the same time and listen to them complain about their enterprise class SAN not being able to handle an enterprise class workload. Otherwise, yes, stagger them.


MongoDB hidden node still receiving connections I'm not sure if this question been asked before or if the following behavior of MongoDB is normal. Searching online output no results to this scenario.

Initially, we had a 3 node deployment, 1 Primary, 1 Secondary, and 1 Arbiter.

We wanted to add a ReadOnly replica to the cluster and remove the Arbiter node as well in the process. We added the following to the new node:

priority: 0
hidden: true
votes: 1
And removed the Arbiter in the same reconfiguration process so we always have 3 voting members and it leaves us with 1 Primary and 1 Secondary and 1 ReadOnly Node.

The complete process went through smoothly, however, we still end up seeing connections to the ReadOnly replica.

But when checking via db.currentOp(), no queries show up.

Based on the documentation on MongoDB website,

  Hidden members are part of a replica set but cannot become primary and
  are invisible to client applications.

Is there a way to investigate why there are connections coming in? And if this is normal behavior?

EDIT: (for further clarification)

Assuming the following:

MongoDB A (Primary): 192.168.1.50

MongoDB B (Secondary): 192.168.1.51

MongoDB C (Hidden): 192.168.1.52

Client A: 192.168.1.60

Client B: 192.168.1.61

In the logs, we see the following:

2018-03-12T07:19:11.607+0000 I ACCESS   [conn119719] Successfully authenticated as principal SOMEUSER on SOMEDB
2018-03-12T07:19:11.607+0000 I NETWORK  [conn119719] end connection 192.168.1.60 (2 connections now open)
2018-03-12T07:19:17.087+0000 I NETWORK  [listener] connection accepted from 192.168.1.60:47806 #119720 (3 connections now open)
2018-03-12T07:19:17.371+0000 I ACCESS   [conn119720] Successfully authenticated as principal SOMEUSER on SOMEDB

So if the other MongoDB instances were connecting, that would be fine, but my question is regarding why the clients are able to connect even when the hidden option is true and if that behavior is normal.

EDIT2:

To further clarify, the connection string does not include MongoDB C.

Connection String Format:

mongodb://mongodb-a.domain:27017,mongodb-b.domain:27017/?replicaSet=MyReplica

Hence, the client does not know MongoDB C exists and by theory, it should be hidden, but in our case it's not.

Yes, it is normal behavior. Those connections are from other mongod nodes, because every node "watches" every other node to see who is reachable. This because election must be started even in the situation where secondary node disappear from replica set.


Oracle 11gR2 IMP Won't Import Table in Dump File From 10g I've got a screwy problem using imp on an Oracle 11gR2 instance to try and import export files created with exp on an Oracle 10g instance (different hardware, if that matters).

I've read through the Oracle documentation and I know that I can use imp from any higher version to import *.dmp files created from any lower version of Oracle - or at least it shouldn't be a problem going from 10g to 11gR2.

Note: I can't use DataPump (tried) because the 10g is 10.2.0.1 which has a known PL/SQL bug when trying to use DataPump from a remote DB; the solution is to patch to at least 10.2.0.2, but our environment is too unstable to do that at this time.

We have a nightly job that exports schema-level *.dmp files from the Oracle 10g instance for all of the active users and places them on a common network share.

We've previously exported the user schemas from the Oracle 10g instance and used those exports to create those same schemas on the Oracle 11gR2 instance, and while that was several months ago, most of the schemas haven't changed much - there are a couple of new tables, but the imp should be able to handle that just fine.

So, before I wrote a giant parameter file and ran a mass import, I decided to do a small-scale test with one table from one schema - the schema isn't mine, but ultimately I can fix that.  I just want to make this work first - so I do:

C:\Users\digitalnoise>imp digitalnoise/passwordispassword file=Y:\user1.dmp tables=(zomg_must_have_dataz)

But then I get this:

Import: Release 11.2.0.1.0 - Production on Tue Dec 10 21:32:17 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via direct path

Warning: the objects were exported by USERGOD, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing USERGOD's objects into DIGITALNOISE
IMP-00033: Warning: Table "ZOMG_MUST_HAVE_DATAZ" not found in export file
Import terminated successfully with warnings.

Which leaves me really confused, because:

C:\Users\digitalnoise>imp digitalnoise/passwordispassword file=Y:\USER1.dmp show=Y full=Y LOG=USER1.LOG

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via direct path

Warning: the objects were exported by USERGOD, not by you

 "ALTER SESSION SET CURRENT_SCHEMA= "USER1""
 "CREATE TABLE "ZOMG_MUST_HAVE_DATAZ" (<<SNIP>>)  PCTFREE 10 PCTUSED 40 INITRANS "
 "1 MAXTRANS 255 STORAGE(INITIAL 540016640 FREELISTS 1 FREELIST GROUPS 1 BUFF"
 "ER_POOL DEFAULT) TABLESPACE "ZOMG" NOLOGGING NOCOMPRESS"
. . skipping table "ZOMG_MUST_HAVE_DATAZ"

Clearly indicates that the table ZOMG_MUST_HAVE_DATAZ exists in the USER1.DMP file - but when I got to actually import it, imp says that it doesn't exist...

My user ID has the IMPORT FULL DATABASE system privilege on the target via Role assignment, so I don't think that's the issue.

Am I missing something rather obvious here?

Your table is owned by USERGOD, but you are importing as DIGITALNOISE.
Yes, you have the IMPORT FULL DATABASE, but you are not doing a full import.

Using FROMUSER/TOUSER is one solution.
Performing a full import by adding the FULL=Y option is another.


handle subquery with joins where same result is to be joined with multiple tables I have this query which is going through table B twice I want it go through tableB once as I should be able to select all necessary fields from a table once and use that selected result table in multiple joins.

    select tableA.aa,tableA.bb, tableK.address, tableP.address from tableA
        LEFT JOIN (
    SELECT
        tableB.ID,
        tableB.Address,
        tableD.Name,
        tableC.tableCName,
        tableC.Country
    FROM
        tableB
    INNER JOIN tableD ON tableB.tableDID = tableD.ID
    AND tableD.Deleted = 0
    INNER JOIN tableC ON tableD.tableCID = tableC.ID
    WHERE
        tableB.Deleted = 0
) AS tableP ON tableP.ID = tableA.aa
LEFT JOIN (
    SELECT
        tableB.ID,
        tableB.Address,
        tableD.Name,
        tableC.tableCName,
        tableC.Country
    FROM
        tableB
    INNER JOIN tableD ON tableB.tableDID = tableD.ID
    AND tableD.Deleted = 0
    INNER JOIN tableC ON tableD.tableCID = tableC.ID
    WHERE
        tableB.Deleted = 0
) AS tableK ON tableK.ID = tableA.bb

I have written same query subquery twice

Create VIEW (created once, it exists in database until ALTER or DROP, like tables):

CREATE VIEW my_subquery AS
SELECT tableB.ID
     , tableB.Address
--   , tableD.Name
--   , tableC.tableCName
--   , tableC.Country
FROM
    tableB
INNER JOIN tableD ON tableB.tableDID = tableD.ID AND tableD.Deleted = 0
INNER JOIN tableC ON tableD.tableCID = tableC.ID
WHERE
    tableB.Deleted = 0;

and then use it in Your query like a common table:

SELECT tableA.aa, tableA.bb, tableK.address, tableP.address
FROM tableA
LEFT JOIN my_subquery AS tableP ON tableP.ID = tableA.aa
LEFT JOIN my_subquery AS tableK ON tableK.ID = tableA.bb;


DENY insert,update,delete to user (PostgreSQL) I wonder if Postgres support the DENY command. I search in the documentation but I couldn't find anything about deny.

I have GRANT ALL ON table1 TO user1 WITH GRANT OPTION
but I don't want user1 to be able to grant insert/update/delete to user2.
(He can do whatever he want with everyone else!!!)

I want to use this command DENY INSERT,UPDATE,DELETE ON table1 TO user2;

Is there any way to accomplish this?

PostgreSQL does not have DENY ACLs.

Instead you must REVOKE any rights you want them to be missing, leaving only the rights you want.

e.g.

REVOKE ALL ON TABLE blah FROM USER fred;
GRANT SELECT ON TABLE blah TO USER Fred;


Common term for tenant-based keys Assume the following schema:

ID  | TENANT | TENANT_CUSTOM_ID
346 | 101    | 1
347 | 102    | 1
348 | 101    | 2
349 | 101    | 3
350 | 101    | 4
351 | 102    | 2
352 | 103    | 1

What is another name (or the proper classification) for TENANT_CUSTOM_ID? Perhaps something along the lines of a surrogate key? The purpose of this key is to make each tenant have their own numbering system instead of knowing that their records are mixed with all other tenants on the same tables.

A natural key (AKA business key or domain key) is a key that is used to identify information in the business domain.

A surrogate is a key that is "unseen", internal to the database, meaningless and not used in the business domain.

The distinction that really matters is whether the attribute in question is exposed to users / consumers and therefore serves some function and acquires meaning as part of a real world process.

It seems that there is already too much loose terminology and folklore associated with keys. I don't think it would be helpful to have another term for the kind of key you are referring to here.


Conditional join on a third table I have four tables - A, B, C and D.

Table A holds inventory movement records, table B holds sales records on a per item basis, table C holds non-sales records on a per item basis and table C holds the type of non-sales movement.

For each record in A there's either a line in B or a line in C. So to get a single row of the movements for each item I do the following:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C on C.Id = A.IdIntoC

So far so good. However, as I need to know what type of movement it was in C as I want to return the following:

ItemId, Date, Sales, Goods In, Returns to Supplier

i.e. have separate columns for good in and goods returned to a supplier.

The type (goods in or return to supplier) is stored in D and there is a many to one relationship between C and D as D is the "document" that groups the items in C.

If I do:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C as C1 on C1.Id = A.IdIntoC
    left join D on D.Id = C1.IdIntoD and D.Type = 1
left join C as C2 on C2.Id = A.IdIntoC
    left join D on D.Id = C2.IdIntoD and D.Type = 2

I just get duplicate rows as the two joins from A to C are effectively the same.

What I want to do is this (non-legal syntax):

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C as C1 on C1.Id = A.IdIntoC and D.Type = 1
    left join D on D.Id = C1.IdIntoD
left join C as C2 on C2.Id = A.IdIntoC and D.Type = 2
    left join D on D.Id = C2.IdIntoD

Basically I want separate out the joins between A and C based on a value in D. I know that I have to use a left join between C and D each time otherwise I'll get no data, but logically that's what I feel I need to do.

How do I do this?

I don't think you need to join the C table twice. This would work:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C on C.Id = A.IdIntoC
    left join D on D.Id = C.IdIntoD and (D.Type = 1 OR D.Type = 2) ;

The (D.Type = 1 OR D.Type = 2) can be further simplified to D.Type in (1, 2).

If you do want 2 joins to C (and D), you could alter the syntax in your second to last query to (do an inner join to D) and move the join condition to the end:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C as C1
    inner join D as D1 on D1.Id = C1.IdIntoD and D1.Type = 1
  on C1.Id = A.IdIntoC
left join C as C2
    inner join D as D2 on D2.Id = C2.IdIntoD and D2.Type = 2
  on C2.Id = A.IdIntoC ;

or more simpler, join C once and D twice:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C on C.Id = A.IdIntoC
    left join D as D1 on D1.Id = C.IdIntoD and D1.Type = 1
    left join D as D2 on D2.Id = C.IdIntoD and D2.Type = 2 ;


SQL Server Replication across firewall We would like to set up replication between two SQL Server 2012 instances.

Server "Internal" can see and make connections to server "External". External is completely firewalled from the internal network. No inbound connections are allowed on any port.

We want to set up transactional replication from internal to external for a read-only copy on server External. When configuring External as the subscriber, it wants to connect to the publisher which it cannot do.

All my searching leads me to articles on which ports to forward but that is not going to work for us. Basically what we want to do is have Internal initiate the connection and push updates to a copy on External. Up until now we have been using SSIS packages to push data but it's starting to border on unmanageable.

Are you able to set up a shared folder where only the SQL Service accounts have access? If so, since you are looking for a read-only subscriber, one option may be to use log shipping leaving the database in STANDBY. Of course, this is dependent on how often you need the data updated. Every time a log is restored, the users will be kicked off the secondary. Let's say every hour you restore a log backup, users will be kicked off during the restore, but the data will be pretty close to that time.


How can I launch SQL Developer? I recently installed Oracle 11g R2 on a Windows 8.1 machine, but for some reason SQL Developer does not seem to launch easily.  What specific steps do I need to take in order to launch a working SQL Developer instance?

Here are my steps so far (There are print screens in hyperlinks below because I do not have enough reputation points to embed the print screens in this posting.):

1.) I searched for SQL Developer in Windows 8 and clicked to launch the resulting entry for the program.

2.) The resulting dialog box then said it was searching for a named SQLDEVELOPER.bat file, and when it could not find the SQLDEVELOPER.bat file, it gave the following dialog box suggesting that it use a different selecthome.bat file instead:  http://s23.postimg.org/4nr1d1r8b/sql_developer_delete.jpg

3.) I hesitated to authorize what looks like a hard to un-do setting, so I looked in the windows file system and found the following items in the root directory of Sql Developer:  http://s30.postimg.org/5vtk3k269/sql_developer_directory.jpg

Note that there is a sqldeveloper.sh file in the directory.  There is also a sqldeveloper.exe file, but clicking on that file does not launch the program.

So what steps need to be taken in order to successfully launch Sql Developer?

ONGOING EFFORTS:

I followed the advice of @SoleDBGuy and downloaded and installed a newer version of SQL Developer.  But I am not yet able to make a database connection from it to validate that the new SQL Developer can work with my existing 11G R2 installation.

I validated that the 11G R2 installation is running and that I know the password by using the windows command line to connect by typing sqlplus system/password, where password is the real password.

But I am not yet able to connect to the same 11G R2 instance with the same credentials through the new SQL Developer because the xe listener is being rejected.  I looked this up and it seems that the xe listener is associated with the express edition.  This may be the problem as I installed the enterprise edition of Oracle 11G R2.  I did not install the express edition.

So my next step is to try to determine what listener to use to connect to the enterprise edition, and to try replacing xe with the name of the correct listener for the enterprise edition.  If this works, then I will have validated a solution and resolved this question.

RESOLUTION:

I was able to validate @SoleDBGuy's approach by creating a connection using orcl as the SID in the SQL Developer Add New Connection Wizard.  Initially, the connection wizard was not connecting because the default listener is ex, which is for the express edition.  I installed the enterprise edition, so the ex listener did not work and I had to use the orcl edition instead.

Once I connected to the database, I then validated that it was the correct database by running the query select tablespace_name from users_tablespaces;.  When I saw the correct table spaces, I was able to validate @SoleDBGuy's approach and mark his answer as accepted.

You can download the latest SQL Developer from Oracle and get the specific instructions here on what is required to do the installation.

Once SQL Developer starts, the next obvious step is to connect to a database and this will depend on how Oracle is configured. While I believe this is a separate issue, for sake of completeness I'll add that by default SQL Developer will use xe for the SID in the connection wizard, which is for express edition, and that may not be sufficient to make a connection. Another way is to try orcl instead of xe since that is default for "regular" Oracle.

It is possible this has been customized depending on the installation and if so you will have to find that information from the person/group that installed Oracle if you do not already know it.

If still having problems trying to connect, look at this link to see if any of the information there is helpful: Oracle 11g listener fails with ORA-12514 and ORA-12505 errors


Restore database to point in time from full backup only I have a database with recovery mode set to FULL. It's a dev database and I have two full backups dated 19th and 27th i.e. latest.

One of the developer did some changes on 26th and I want to rollback those changes and want to restore database at a state of 25th.

restore database MyDb from disk = 'I:\MSSQL\DATA\MyDb_20160127.bak'
with recovery, stopat = '2016-01-25 10:38:15.280',
move 'MyDb' to  'I:\MSSQL\DATA\MyDb.mdf',
move 'MyDb_log' to 'I:\MSSQL\DATA\MyDb.ldf'

  The STOPAT clause specifies a point too early to allow this backup set
  to be restored. Choose a different stop point or use RESTORE DATABASE
  WITH RECOVERY to recover at the current point.

I am using SQL Server 2005

No, you need to have taken transaction logs that cover the time frame you want to use with STOPAT. You can't do this from a full database backup only - that is just a one-time copy, and that is why we have different types of backups (full, log, diff).

If you have taken transaction log backups in between your full backups, please update the question with that information, and we can show you steps for restoring to a point in time. But those tutorials are easy to find, so I suspect you're asking here because you haven't taken log backups. If that is the case, you're kind of stuck - you may be able to use some kind of log reading utility to get at that information from the current transaction log, but I wouldn't be confident about success there - and it doesn't seem like that product, at least, has a feature called "restore to point in time" - it allows you to recover specific pieces of information, like restoring a table or undoing a truncate. So I think that would be quite a manual "undo transaction A, undo transaction B, undo transaction C, etc etc" to get at your eventual result. Probably still less work than trying to do that yourself from fn_dblog output.

This post is rather timely:

The Importance of a Full Backup in SQL Server

And these may be useful too:

Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN
Paul Randal's backup myths
Paul Randal's restore myths

To demonstrate that you can't now take a log backup and try to restore to some point in time prior to the last full backup. First, run this script:

USE master;
GO
CREATE DATABASE splunk;
GO
ALTER DATABASE splunk SET RECOVERY FULL;
GO
USE splunk;
GO
SELECT GETDATE(); -- use this in STOPAT
GO
WAITFOR DELAY '00:00:10';
GO
BACKUP DATABASE splunk TO DISK = 'c:\temp\sp_full.bak' WITH INIT;
GO
WAITFOR DELAY '00:00:10';
GO

The above simulates what you have: a full backup, taken after the time you want to restore to, and that's it.

Now, take a tail-log backup:

BACKUP LOG splunk TO DISK = 'c:\temp\sp_tail.trn' WITH INIT, NO_TRUNCATE;
GO

Now, let's start the recovery process. First, restore the full backup with norecovery:

USE master;
GO
RESTORE DATABASE splunk FROM DISK = 'c:\temp\sp_full.bak' WITH REPLACE, NORECOVERY;
GO

Now, attempt to restore the tail-log backup with the STOPAT parameter set to whatever was output above for GETDATE():

RESTORE LOG splunk FROM DISK = 'c:\temp\sp_tail.trn'
  WITH NORECOVERY, STOPAT = '<paste GETDATE() output from above>';

I get the following error messages:

  Msg 4335, Level 16, State 1
   The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.
   Msg 3013, Level 16, State 1
   RESTORE LOG is terminating abnormally.

In order to restore to a point in time, you need to have taken at least one transaction log backup after a full backup (not necessarily after the last full backup, but after one that is valid and can be restored), and after the point in time you want to restore to (but before the next full database backup).

This is why we typically take full backups nightly or weekly, and transaction log backups every 15 minutes or every hour (and sometimes diff/filegroup backups in there too, depending on business needs, RTO/RPO, etc). In full recovery model there is certainly no use case where you'd want to take a full backup once a week, and nothing in between. This can be a very expensive lesson to learn the hard way, but I bet it doesn't happen to you twice.

This post probably has a lot of useful points about backups, why we take them, the usefulness of log backups, etc. - I highly recommend bookmarking it and giving it a thorough read once you solve your current problem.

Why Does the Transaction Log Keep Growing or Run Out of Space?


MySQL Error: Authentication plugin 'caching_sha2_password' cannot be loaded I just installed MySQL Ver 14.14 Distrib 5.7.22 with Homebrew on my macOS v10.13.4.
I ran the command:
brew install mysql

After the installation completed, as directed by Homebrew, I ran the command:
mysql_secure_installation
and was returned the error: Error: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql/5.7.22/lib/plugin/caching_sha2_password.so, 2): image not found

I tried a few things like changing default_authentication_plugin to mysql_native_password in the my.cnf file but it still throws the same error.

Next I tried running:
mysql_upgrade -u root
and I was thrown the same error again mysql_upgrade: Got error: 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql/5.7.22/lib/plugin/caching_sha2_password.so, 2): image not found while connecting to the MySQL server
Upgrade process encountered error and will not continue.

Any help is appreciated.

So, I found the running process of mysqld by sudo lsof -i tcp:3306 then I killed it using sudo kill -9 <PID>.
After this I tried $ mysql_secure_installation again but ran into a new error Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)
After trying a few fixes for getting mysql.sock to work, I started the MySQL server with
$ sudo mysql.server start
then went ahead with $ mysql_secure_installation to set password for the root user.

This worked for me finally.

Note: Homebrew asks you to first do mysql_secure_installation before starting the MySQL server for the first time but that just made me run into endless loop of errors.


Can I use OpenLDAP to authenticate users in Oracle? I like to use OpenLDAP to authenticate my Oracle users. It should be possible to fetch at least the password from the directory.

There appearently is something for the Oracle LDAP servers, but would it work with OpenLDAP (plain, from i.e. Debian)?

Yes!

According to this post, you absolutely can, with significant speed improvements over OID.

Looking at this absolutely ancient post, it was apparently possible back in 2003.

Here's the Archive.Org transcript of the page they reference.

This is a Oracle documentation link about hooking OID up to an "external directory". Good luck.


Read massive flat file into relational database I have a massive flat text file with ~4M records that I would like to read into MySQL using Ruby/Rails, Python, or any other method.

The flat file is roughly this format:
business_name,address,city,state,zip,employee_name

If there are multiple employees at the business, there will be one row for each employee with the business name, address, etc. repeated.

I'd like to read this in to two tables structured with the employees table having a foreign key like so:

businesses (id int, name varchar, address varchar, city varchar, etc.)
employees (id int, name varchar, business_id int)

How do I go about efficiently reading this file in so that the employees/businesses are created and referenced as needed?

Note: it is NOT safe to assume that the file is sorted.

I've tried reading the file into SQL first and then using SQL to get a unique list of business_name/address/city/state combinations that I could then insert into the businesses table, but my server runs out of memory. And this is only with 1/4th of the records loaded.

Try using LOAD DATA INFILE into an ISAM table with few or no indexes. ISAM should reduce memory consumption and get all data in there the fastest possible way. I have never run out of memory when doing this with larger data sets than yours, on pretty old servers with alot less memory than is used today.

Once the data is imported, use ALTER TABLE to add required indexes, in essence everything needed to uniquely identify a business (name, address, state etc). This may take some time.

Create another ISAM table having an auto increment primary key as well as all columns from the first table which are required to uniquely identify a business (e.g the ones that were indexed, but nothing related to employees). Add a UNIQUE key which includes ALL all columns in the table except the primary key.

Use REPLACE INTO to fill the second table with data from the first, and you will in the process generate unique numeric keys for all the businesses. Add the key column to the first table and UPDATE it using data from the second, which then can be dropped afterwards.

From there you should have a table where all businesses have unique, numeric and indexed keys and should easily be able to split the data into the (InnoDb) tables you require.