Cannot build correct SELECT in MySql Please help me to create a correct MySql query, which I failed to do.

I have 2 tables in our DB:

CREATE TABLE IF NOT EXISTS `user_audit` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `audit_event` varchar(128) NOT NULL,
  `data` varchar(256) NOT NULL DEFAULT '',
  `source_ip` varchar(256) NOT NULL DEFAULT '',
  `user_agent` varchar(256) NOT NULL,
  `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27181 ;

CREATE TABLE IF NOT EXISTS `user_balance_transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `audit_event_id` int(10) unsigned NOT NULL,
  `private_tournament_id` int(10) unsigned DEFAULT NULL,
  `regular_tournament_id` int(10) unsigned DEFAULT NULL,
  `points` int(11) NOT NULL,
  `description` varchar(256) NOT NULL DEFAULT '',
  `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `private_tournament_id` (`private_tournament_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1001 ;

I have 2 audit events that I'm interested in:

user_audit.audit_event = "RGL_TOURNAMENT_ENTRANCE_PAID"
user_audit.audit_event = "RGL_TOURNAMENT_ENTRANCE_REFUNDED"

These audit events are opposite to each other, meaning user can pay, then can be refunded, then use pays again and so on.

On top of these 2 table I have to build one SQL select statement that will return DISTINCT user_id, regular_tournament_id, create_timestamp of ALL USERS with:

user_balance_transactions.regular_tournament_id is not NULL
user_balance_transactions.audit_event_id is pointing to user_audit table where user_audit.audit_event = 'RGL_TOURNAMENT_ENTRANCE_PAID'
and it's the last event (most important!) that happened chronologically.

IMPORTANT: In user_audit table 2 possible audit events are related to each other: 'RGL_TOURNAMENT_ENTRANCE_PAID' and 'RGL_TOURNAMENT_ENTRANCE_REFUNDED',
but we need user_id only of such users who has  'RGL_TOURNAMENT_ENTRANCE_PAID' event as last.
create_timestamp is a timestamp of this last event -  'RGL_TOURNAMENT_ENTRANCE_PAID'

A. Test data for "user_balance_transactions" table:

id, user_ID, audit_event_id, private_tournament_id, regular_tournament_id, points, description, create_timestamp

2, 23, 1711, null, 77, 10, "credit", "2015-06-12T17:23:44"

3, 23, 1712, null, 77, -10, "debit", "2015-06-12T17:41:44"

4, 23, 1713, null, 77, 10, "credit", "2015-06-12T18:11:44"

B. Test data for "user_audit" table:

id, user_ID, audit_event, data, source_ip, user_agent, create_timestamp

1711, 23, "RGL_TOURNAMENT_ENTRANCE_PAID", "","","", "2015-06-12T17:23:44"

1712, 23, "RGL_TOURNAMENT_ENTRANCE_REFUNDED", "","","", "2015-06-12T17:41:44"

1713, 23, "RGL_TOURNAMENT_ENTRANCE_PAID", "","","", "2015-06-12T18:11:44"

C. Expected SQL output:

user_id, regular_tournament_id, create_timestamp

23, 77, "2015-06-12T18:11:44" (last RGL_TOURNAMENT_ENTRANCE_PAID event)

"user_balance_transactions" stores user transaction and "user_audit" stores all user audit events, of which some of them related to the balance transactions.

That's why "user_balance_transactions" table has a Foreign Key to "user_audit" table "audit_event_id"

Try this:

SELECT
       t.user_id,
       t.audit_event
FROM
        (SELECT ua.user_id, ua.audit_event FROM user_audit ua, user_balance_transactions ubt WHERE
        (audit_event = 'RGL_TOURNAMENT_ENTRANCE_PAID' OR audit_event = 'RGL_TOURNAMENT_ENTRANCE_REFUNDED')
        AND ubt.regular_tournament_id='{$tournament_id}'
        AND ubt.audit_event_id=ua.id
        ORDER BY ua.create_timestamp DESC LIMIT 1) t
        WHERE t.audit_event != 'RGL_TOURNAMENT_ENTRANCE_REFUNDED'


Where do this Constant Scan and Left Outer Join come from in a trivial SELECT query plan? I have this table:

CREATE TABLE [dbo].[Accounts] (
    [AccountId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(),
    -- WHATEVER other columns
);
GO
CREATE UNIQUE CLUSTERED INDEX [AccountsIndex]
    ON [dbo].[Accounts]([AccountId] ASC);
GO

This query:

DECLARE @result UNIQUEIDENTIFIER
SELECT @result = AccountId FROM Accounts WHERE AccountId='guid-here'

executes with a query plan consisting of a single Index Seek - as expected:

SELECT <---- Clustered Index Seek

This query does the same:

DECLARE @result UNIQUEIDENTIFIER
SET @result = (SELECT AccountId FROM Accounts WHERE AccountId='guid-here')

but it's executed with a plan where result of Index Seek is Left Outer Joined with result of some Constant Scan and then fed into Compute Scalar:

SELECT <--- Compute Scalar <--- Left Outer Join <--- Constant Scan
                                      ^
                                      |------Clustered Index Seek

What's that extra magic? What does that Constant Scan followed by Left Outer Join do?

The semantics of the two statements are different:

The first does not set the value of the variable if no row is found.
The second always sets the variable, including to null if no row is found.

The Constant Scan produces an empty row (with no columns!) that will result in the variable being updated in case nothing matches from the base table. The left join ensures the empty row survives the join. Variable assignment can be thought of as happening at the root node of the execution plan.
Using SELECT @result
-- Set initial value
DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'};

-- @result does not change
SELECT @result = AccountId
FROM Accounts
WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'};

SELECT @result;

Using SET @result
-- Set initial value
DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'};

-- @result set to null
SET @result =
(
    SELECT AccountId
    FROM Accounts
    WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'}
);

SELECT @result;

Execution plans
No row arrives at the root node, so no assignment occurs.
A row always arrives at the root node, so variable assignment occurs.

The extra Constant Scan and Nested Loops Left Outer Join are nothing to be concerned about. The join in particular is cheap since it is guaranteed to encounter one row on its outer input, and at most one row (in your example) on the inner input.
There are other ways to ensure a row is generated from the subquery to ensure a variable assignment occurs. One is to use a redundant scalar aggregate (no group by clause):
-- Set initial value
DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'};

-- @result set to null
SET @result =
    (
        SELECT MAX(AccountId)
        FROM Accounts
        WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'}
    );
SELECT @result;

Notice the scalar aggregate produces a row even though it receives no input.
Documentation:

SET @local_variable (Transact-SQL)
SELECT @local_variable (Transact-SQL)

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.
For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.

Further reading:

Fun with Scalar and Vector Aggregates


Is this right decision to define 5-6 foreign key in a single table...? If i define 5-6 foreign keys on a single table, are there any performance concerns?

CREATE TABLE [dbo].[PS_CLASSROOM_MST](
    [CLASS_ID] [int] NOT NULL,
    [CLASS_NAME] [varchar](50) NOT NULL,
    [CLIENT_ID] [int] NOT NULL,
    [SCHOOL_ID] [int] NOT NULL,
    [COURSE_ID] [int] NULL,
    [BRANCH_ID] [int] NOT NULL,
    [SESSION_ID] [int] NOT NULL,
    [SECTION] [varchar](10) NULL,
    [STATUS] [bit] NOT NULL,
    [IP_ADDRESS] [varchar](30) NULL,
    [LANGUAGE] [varchar](50) NULL,
    [CLASS_FILE_PATH] [varchar](200) NULL,
    [REMARK] [nvarchar](max) NULL,
    [SECTION_FILE_PATH] [nvarchar](max) NULL,
    [LOCATION_ID] [int] NOT NULL,
 CONSTRAINT [PK_PS_CLASSROOM_MST] PRIMARY KEY CLUSTERED
(
    [CLASS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST]  WITH CHECK ADD  CONSTRAINT [FK_PS_CLASSROOM_MST_PS_BRANCH_MST] FOREIGN KEY([BRANCH_ID])
REFERENCES [dbo].[PS_BRANCH_MST] ([BRANCH_ID])

GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST] CHECK CONSTRAINT [FK_PS_CLASSROOM_MST_PS_BRANCH_MST]
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST]  WITH CHECK ADD  CONSTRAINT [FK_PS_CLASSROOM_MST_PS_COURSE_MST] FOREIGN KEY([COURSE_ID])
REFERENCES [dbo].[PS_COURSE_MST] ([COURSE_ID])
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST] CHECK CONSTRAINT [FK_PS_CLASSROOM_MST_PS_COURSE_MST]
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST]  WITH CHECK ADD  CONSTRAINT [FK_PS_CLASSROOM_MST_PS_LOCATION] FOREIGN KEY([LOCATION_ID])
REFERENCES [dbo].[PS_LOCATION] ([LOCATION_ID])
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST] CHECK CONSTRAINT [FK_PS_CLASSROOM_MST_PS_LOCATION]
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST]  WITH CHECK ADD  CONSTRAINT [FK_PS_CLASSROOM_MST_PS_SCHOOL_MST] FOREIGN KEY([SCHOOL_ID])
REFERENCES [dbo].[PS_SCHOOL_MST] ([SCHOOL_ID])
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST] CHECK CONSTRAINT [FK_PS_CLASSROOM_MST_PS_SCHOOL_MST]
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST]  WITH CHECK ADD  CONSTRAINT [FK_PS_CLASSROOM_MST_PS_SESSION_MST1] FOREIGN KEY([SESSION_ID])
REFERENCES [dbo].[PS_SESSION_MST] ([SESSION_ID])
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST] CHECK CONSTRAINT [FK_PS_CLASSROOM_MST_PS_SESSION_MST1]
GO

Foreign keys exist to enforce referential integrity i.e. to keep the data clean. This is a good thing.

However, there are no free lunches. The constraint will be checked on writes, adding latency. A FK may have an associated index. As such there will be an overhead during writes. The more FKs, the more overhead. Plus the extra disk and maintenance time.

If you have done design diligently and arrived at this table then I see no prima face problem. If testing at scale shows problems that's the time to consider alternate implementations.


See content of temporary Table in Oracles' EXPLAIN I'm executing a Query in Oracle SQL Developer and eventually call
SELECT * FROM TABLE ( dbms_xplan.display );

to get an impression what Oracle just did. There's a column Name in the output that states the name of the index that is used in an operation or the name of the table that is being accessed.
Every once in a while it says something like SYS_TEMP_0FD9D6B47_384FBF5. I'm not sure what of make of it. I guess this is a temporary table created from a WITH clause. How can I see what's the content or the SQL code behind this table?

As @pmdba mentioned, no way to peek at that table, and if you could, it would likely be raw data in some strange format.  But if you want more detail, find out the SQL_ID for the statement in question (via v$session and v$sql for example), and then query the v$sql_plan table for the column OTHER_XML.  For running in SQL*Plus, you'll want to include the noted set commands to see the output.
set long 65000
set linesize 200

select sql_id, other_xml from v$sql_plan where sql_id='26upzh3jd5fd7'
and other_xml is not null;

and you will see all sorts of interesting things, if you are patient in reformatting it into something readable.  Note that information is basically a hint used in invoking the SQL; what really happens may or may not match that.
Now if you want to get into what actually happens, including what "guesses" the Oracle optimizer does before actually running a query, you need to (assuming you have DBA privileges), turn on SQL trace for the session, run your SQL, then turn off SQL trace after.  Then go to your diagnostics directory, and find the *.trc file associated with your session.  And make sure you have some food nearby.  A lot of data there, but it has helped me several times to fix long-running queries.  Brief example:
execute DBMS_SESSION.SESSION_TRACE_ENABLE(binds=>true);
-- invoke your SQL after a brief SQL to help you find it later in the trace
select 'HEY my sql start' from dual;
-- your select query goes here
select 'HEY my sql stop' from dual;
execute DBMS_SESSION.SESSION_TRACE_DISABLE;

The trace file will be in a directory that in Linux is likely named something like
/u01/app/oracle/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace


How can I change the target server type for a trace template? I've got a .tdf SQL Server Profiler trace template someone wants me to run but the template is targeting SS 2008 R2. While my SSMS is 2008 R2 the server I need to trace is SS 2005. When attempting to trace the server the server type is locked (generated from the actual server) so I can't just select the template I need while it's marked as a different SS version..

How can I change which server type the trade template is targeting? I've tried File > Templates > Edit Templates... but there doesn't seem to be an option to change this. Is it possible to change the target server version or does the whole trace have to be made again from scratch?

If you open Profiler, go to File > Templates > Import and choose your .tdf file.

You will have a new user trace template with the name [filename] (without the .tdf). Go to File > Templates > Edit and pick the trace template you imported. You should then be able to edit the server type by pulling the dropdown:

Then you can save the template and that should be the server type when you start a new trace using that template.


Determining what query was called I want to reverse engineer what exactly happens to the MySQL database when a product is added in Magento. I will have to reproduce the query, because I would like to manually enter new records using a triggered procedure. Is it possible to somehow capture how the changes were made on the database?

Thanks in advance.

You could enable binary logging. If you have not done so already.
To find out if binary logging is enabled, run this:
SHOW MASTER STATUS;

If you get back, Empty set, it is not enabled.
If you have not, please add this to my.cnf under the [mysqld] section
[mysqld]
log-bin = mysql-bin

Then, restart mysql.
From that point forward, you could look for all changes within timeframes.
For example, suppose you run SHOW BINARY LOGS; and get something like this:
mysql> show binary logs;
+----------------+------------+
| Log_name       | File_size  |
+----------------+------------+
| bin-log.000645 |    5933323 |
| bin-log.000646 | 1073745654 |
| bin-log.000647 | 1073759862 |
| bin-log.000648 | 1073757229 |
| bin-log.000649 | 1073776952 |
| bin-log.000650 | 1073791824 |
| bin-log.000651 | 1073743420 |
| bin-log.000652 | 1073785139 |
| bin-log.000653 |  338795678 |
+----------------+------------+
9 rows in set (0.00 sec)

You could then use the mysqlbinlog utility to dump a text representation of all the SQL commands executed within specified timeframes along with the timestamps.
The options to use are

--start-datetime
--stop-datetime

What to do next
Go find the folder where the binary logs are kept. From the command line run this
ls -l bin-log.*
total 7737092
-rw-rw---- 1 mysql mysql    5933323 Aug 17 17:26 bin-log.000645
-rw-rw---- 1 mysql mysql 1073745654 Aug 17 19:04 bin-log.000646
-rw-rw---- 1 mysql mysql 1073759862 Aug 17 20:45 bin-log.000647
-rw-rw---- 1 mysql mysql 1073757229 Aug 17 22:18 bin-log.000648
-rw-rw---- 1 mysql mysql 1073776952 Aug 18 00:05 bin-log.000649
-rw-rw---- 1 mysql mysql 1073791824 Aug 18 05:18 bin-log.000650
-rw-rw---- 1 mysql mysql 1073743420 Aug 18 08:59 bin-log.000651
-rw-rw---- 1 mysql mysql 1073785139 Aug 18 11:31 bin-log.000652
-rw-rw---- 1 mysql mysql  338795678 Aug 18 12:30 bin-log.000653
-rw-rw---- 1 mysql mysql        351 Aug 18 11:31 bin-log.index

The datetime stamp for each file represents the last time the binlog received SQL commands.
You would request the commands by the timeframe and by the binlog names ordered by timestamp
To see all SQL commands executed from midnight to 8:00AM today, you run this
SQLCMDS=/tmp/SQLCommands.txt
DT1="2014-08-18 00:00:00"
DT2="2014-08-18 08:00:00"
BINLOGS="bin-log.000649 bin-log.000650 bin-log.000651"
mysqlbinlog --start-datetime="${DT1}" --stop-datetime="${DT2}" ${BINLOGS} > ${SQLCMDS}

The output file, /tmp/SQLCommands.txt, will contain every INSERT, UPDATE, DELETE, and DDL command executed in the timeframe you requested. Just make sure you supply the necessary binlogs. If you include all binlogs, the mysqlbinlog dump will still work but would take longer because it would parse through binary logs it does not need (i.e., you don't need to parse bin-log.000645 - bin-log.000648, bin-log.000652, bin-log.000653).
Give it a Try !!!
CAVEAT
If you want to see everything, including the SELECTs, you go with Craig Efrein's answer.
UPDATE 2014-08-21 10:45 EDT
@CraigEfrein made a excellent point in his comment

Activating bin-logs also have an impact on performance and disk space.

To reduce disk I/O based on binary logging, you should config binlogs to be stored on a separate disk. See my old post How do I determine how much data is being written per day through insert, update and delete operations? (under the subheading SSD LIFESPAN)


Cannot restart or repair mongodb database after the crash (***aborting after invariant() failure) After an unexpected crash, I can't restart or --repair the mongodb database anymore.
While restarting the server with systemctl restart mongodb I am getting the following:

2020-09-16T20:25:23.835+0200 E STORAGE  [initandlisten] WiredTiger error (-31802) [1600280723:835687][23452:0x7f4a58059a40], file:sizeStorer.wt, WT_SESSION.open_cursor: __wt_block_read_off, 302: sizeStorer.wt: fatal read error: WT_ERROR: non-specific WiredTiger error Raw: [1600280723:835687][23452:0x7f4a58059a40], file:sizeStorer.wt, WT_SESSION.open_cursor: __wt_block_read_off, 302: sizeStorer.wt: fatal read error: WT_ERROR: non-specific WiredTiger error
2020-09-16T20:25:23.835+0200 E STORAGE  [initandlisten] WiredTiger error (-31804) [1600280723:835692][23452:0x7f4a58059a40], file:sizeStorer.wt, WT_SESSION.open_cursor: __wt_panic, 523: the process must exit and restart: WT_PANIC: WiredTiger library panic Raw: [1600280723:835692][23452:0x7f4a58059a40], file:sizeStorer.wt, WT_SESSION.open_cursor: __wt_panic, 523: the process must exit and restart: WT_PANIC: WiredTiger library panic
2020-09-16T20:25:23.835+0200 F -        [initandlisten] Fatal Assertion 50853 at src/mongo/db/storage/wiredtiger/wiredtiger_util.cpp 420
2020-09-16T20:25:23.835+0200 F -        [initandlisten]
***aborting after fassert() failure
2020-09-16T20:25:23.851+0200 F -        [initandlisten] Got signal: 6 (Aborted).

When I am trying repair with mongod --dbpath **/** --repair I am getting the following:

2020-09-16T20:05:09.921+0200 I STORAGE  [initandlisten] repairDatabase admin
2020-09-16T20:05:09.921+0200 I STORAGE  [initandlisten] Repairing collection admin.system.users
2020-09-16T20:05:09.921+0200 I STORAGE  [initandlisten] Verify failed on uri table:collection-0-4776197172926482174. Running a salvage operation.
2020-09-16T20:05:09.922+0200 F -        [initandlisten] Invariant failure rs.get() src/mongo/db/catalog/database_impl.cpp 228
2020-09-16T20:05:09.922+0200 F -        [initandlisten]
***aborting after invariant() failure
2020-09-16T20:05:09.929+0200 F -        [initandlisten] Got signal: 6 (Aborted).`

After an unexpected crash, I can't restart ... the mongodb database

Time to exercise your Recovery Strategy, put your Recovery Plan into operation and get your database back from the Backups you've taken of it.
Your database is actually remarkably resilient.
Left to its own devices (and all other things being equal) it will recover from a server crash all by itself.  This is called Instance Recovery and all the "real" DBMSs do it completely automatically.
However, if your database can't recover on its own, then it's time to seriously consider putting that database out of it's misery and resurrecting it from Backup.  Blithely jumping with "repair" tools is a Sure-Fire(TM) way to lose Data.
Now, don't get me wrong - losing Data is not always bad ...   but only if it's written down in your Recovery Strategy, which would define, amongst other things:

How long you can take to recover your database, and
How much Data you're allowed to lose in doing so.

Your Backup plan should support your Recovery Strategy.
NB: No sensible Recovery Strategy should ever rely on being able to "repair" the database which, by that point, is basically broken.  Always make sure you can get back to a stable, known, Good State (i.e. the last Backup) and that you have practised performing that recovery work.


Mystery Backup Database Job I'm running SQL Server 2014. I was checking logs to ensure the automated nightly Backup Database job that was scheduled was completing okay. I noticed another BACKUP DATABASE job had been running nightly. I would like to see if I can disable this job, but I have no idea where it is scheduled. It is not listed in the Jobs folder under SQL Server Agent. The message in the log states "BACKUP DATABASE successfully processed 0 pages in 15.770 seconds". When I look at the "physical_device_name" for the jobs I'm aware of it shows the filepath of the backups. The mystery backups just show a long alpha-numeric string in curly brackets. Anybody know what this is? Is it safe to disable? How do I disable?

The mystery backups just show a long alpha-numeric string in curly brackets. Anybody know what this is?

This might be a 3rd party software using Volume Shadow Service (VSS) backup .

  I would like to see if I can disable this job, but I have no idea where it is scheduled.

if you are using native TSQL backup, then disable the SQL Server writer service using services.msc

you can use sysinternal's process explorer to find out what the process or just ask your windows admin if they are doing snapshot of entire folders or machine.

Related question with some more info : Can't find process that is creating backups


Do MySQL sub-queries essentially use as much overhead as separate queries? If I have a query like this:

SELECT name FROM
(
 SELECT * FROM `table`
 WHERE id IN (1,40,300)
 ORDER BY AnotherColumn
)

From a bandwidth and overhead standpoint, is that the same thing as doing three separate queries?  Or is it significantly less overhead?

The amount of data you want could be fast to send over a wire, but what is mysql doing to process it and prepare it for transamission ???

Let's first look at the original query

SELECT name FROM
(
 SELECT * FROM `table`
 WHERE id IN (1,40,300)
 ORDER BY AnotherColumn
)

If id is a primary key, this should go fast. Yet, you do not need to say get all columns in a subquery, and then read on the name from that subquery. You could just craft the SQL as

SELECT * FROM
(
 SELECT name,AnotherColumn FROM `table`
 WHERE id IN (1,40,300)
)
ORDER BY AnotherColumn

or even better, no subquery at all

 SELECT name FROM `table`
 WHERE id IN (1,40,300)
 ORDER BY AnotherColumn

You could benchmark each of these and find negligible to nominal differences but nothing really to publish a Whitepaper over. If thousands of these queries were executed, a query digest program such as pt-query-digest or mk-query-digest could easily pick out a winner as to who is the fastest in terms of running time per call.

With regard to your original question, think of it in these terms:

If going to a movie costs $8/person and you had to bring three people to see a movie, which is cheaper ?

CASE01) taking three people to see a movie
CASE02) taking each person to see the movie at a different time

Well, do the math.

CASE01) $32, you and the three people
CASE02) $48, you (three times) and the three people

Obviously, 50% more overhead in CASE02.

If the puzzle involved

4 people, CASE01) $40 vs CASE02) $64
5 people, CASE01) $48 vs CASE02) $80
6 people, CASE01) $56 vs CASE02) $96
7 people, CASE01) $64 vs CASE02) $112
N people, CASE01) 8(N+1) vs CASE02) 16N

As N goes to infinity, the average overhead is doubled.

Calling three queries in a subquery or getting three rows in one subquery sure beats three separate queries anyday.


What is the best way to create a table with 800 columns in a single script? I prepared a script to create a table with 800 columns but SQLPlus and SQL Developer are not reading the whole script as they probably read only 7499/2499 characters max. As an alternative I am thinking to divide the script into an initial create script with 200 columns and then alter table to add remaining columns in 200 chunks. My question is that what is the best practice to achieve this? Are there any cleaner alternatives. I searched google but could not find the relevant answers and most of them got closed by suggestion that the table should at first place not have these many columns and it is a design issue.

Note: I am trying to replicate a scenario in a legacy system by creating a local database. So I don't have the option to denormalize.

What is the best way to create a table with 800 columns in a single script?

The best way to run a very long script is not to load it into the editor, any editor.

Just reference it via @ and run it

So @my_script.sql and execute that.


How mysql (innodb) handles auto increment column I am working on a OLTP environment with multiple parallel write operation. I found this in documentation about auto increment locking but still i am interested to know much about it in details. Can any one tell me how mysql handles auto_increment column in below cases by using AUTO-INC locking.

1.When there are multiple mysql users performing inserts in parallel ?

2.When a user updating maximum value of the auto increment column and the same time another user is inserting a new row ?

3.how mysql handles before insert trigger when there is an update column inside the trigger changes auto increment value to be inserted ?

4.If any extended insert is in progress and let assume it run for 2 seconds then any other inserts on the same table executed at the same time will wait to acquire auto-inc lock ?

Also tell me is a there any work around to get rid of this bug related to auto increment and triggers.

Auto increment values are assigned sequentially.  In the event there is a rollback or deadlock you will find gaps in the auto inc value.
The auto inc is always assigned previous_auto_inc_value+1.  If a value is manually bumped you will get another gap in ids according to this rule.  If there are gaps you could manually specify a value for a "missing" id but that won't bump the next generated auto inc value.

If you delete rows with max auto inc values that will not reset the next generated value unless you do an alter table specifying the lower auto increment value that has no values larger than it.

Values for all columns in a trigger are accessible in a NEW object in the event of a before trigger or in the event of an after trigger you also have an OLD object.
Since ids are assigned sequentially there is a shared lock and there may be some contention between concurrent inserts but one will not completely block another.  You shouldn't expect the auto inc values for an insert to all be sequentially +1 of each other, although they likely may be.


I want to perform multiple date adds on a date range and select those individual dates I have a table of schedules with an effective date, discontinue date, and seven boolean fields indicating days of the week when that schedule is in use. Here's a sample:

effDate    discDate   opMon opTue opWed opThu opFri opSat opSun
---------- ---------- ----- ----- ----- ----- ----- ----- -----
2012-10-28 2012-11-27 0     1     0     0     0     0     1

What I need to do is select, for each date range, a table of the individual dates where each record is a date in that range. For the above data set, it would look like this:

dates
----------
2012-10-28
2012-10-30
2012-11-04
2012-11-06
2012-11-11
2012-11-13
2012-11-18
2012-11-20
2012-11-25
2012-11-27

I can't seem to wrap my brain around this one for some reason.

Having a days generator function like the following:

create FUNCTION [DateFuncs].[DateRange]
(
    @DateFrom date,
    @DateTo date
)
RETURNS TABLE
AS
RETURN
(
    WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
    Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
    Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
    Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
    Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )

    SELECT
        dateadd(d,n-1,@DateFrom) as Date,
        datepart(yyyy,dateadd(d,n-1,@DateFrom)) as YearNum,
        datepart(mm,dateadd(d,n-1,@DateFrom)) as MonthNum,
        datepart(dd,dateadd(d,n-1,@DateFrom)) as DayNum,
        datepart(dw,dateadd(d,n-1,@DateFrom)) as WeekDayNum,
        n-1 as Offset
    FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
    FROM Nbrs )
    D ( n )
    WHERE n <= datediff(d,@DateFrom,@DateTo)+1
)

check the following query

--preparation code
declare @data table
(
    effDate date,
    discDate date,
    opMon bit,
    opTue bit,
    opWed bit,
    opThu bit,
    opFri bit,
    opSat bit,
    opSun bit
)

insert into @data (effDate,discDate,opMon,opTue,opWed,opThu,opFri,opSat,opSun)
values  ('2012-10-28','2012-11-27',0,1,0,0,0,0,1)

-- your query
select
     dr.Date
from
    @data as da
cross apply
    DateFuncs.DateRange(da.effDate,da.discDate) as dr
where
    case
        when dr.WeekDayNum=1 and da.opSun=1 then 1
        when dr.WeekDayNum=2 and da.opMon=1 then 1
        when dr.WeekDayNum=3 and da.opTue=1 then 1
        when dr.WeekDayNum=4 and da.opThu=1 then 1
        when dr.WeekDayNum=5 and da.opWed=1 then 1
        when dr.WeekDayNum=6 and da.opFri=1 then 1
        when dr.WeekDayNum=7 and da.opSat=1 then 1
    end = 1


Postgres partition avoiding redundant column Say I have a table, say project_data with lots of rows where the data may be grouped by a project_id integer column, and partition it onto one table for each project_id value.
Do I then need the project_id column in every partition table, when the value is implicit?
It seems that having an extra column in project_data_5 project_id integer not null default 5 would be redundant as it will never have a value other than 5 in that table.
This is for postgresql 10.

Yes, the partitioning column must be part of the table.
I assume that you are using list partitioning. Now with list partitioning it is possible to have several values for the partitioning column in the same partition (FOR VALUES IN (1, 2, 27)), so one couldn't omit the partitioning column from the table.
Also, one of the key features of partitioning is that you can quickly attach and detach partitions. Now if the partitioning key were not stored, both attaching and detaching partitions would require a table rewrite.


SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype I have a package that imports excel data into a SQL table. One of the columns in the table is of "smallint" data type and it allows for nulls. The excel file column that has the data that is suppose to go into this column will always contain either small int or in some cases "NULL".

I have noticed that when my source excel file has a NULL in this particular column I get the following error

  There was an error with input column "Copy of Place of Service" (8582)
  on input "OLE DB Destination Input" (8459). The column status returned
  was: "The value could not be converted because of a potential loss of
  data.".

If my source excel file only contains small int values then the package runs fine. I am not sure how I can fix this problem. I have tried changing data types in the Physical table as well as using different DataTypes (via the DataConversion component in SSIS) and I still keep getting the same error message.

Does anyone have any ideas of how I can get around this issue? Even though the column in the  SQL table is checked to allow NULL I guess it is reading the NULL from the Excel source file  as text or string and not as an actual NULL. Any suggestions?

My original answer was working (changing int to nvarchar) but I ran into another column which contained dates in the excel source file where some cells contained the string "NULL" (I did not want to have dates in a NVARCHAR column.) When SSIS got to this date column it was generating an error because it could not convert the string "NULL" to a date. It was not properly reading the "NULL" as a NULL value, instead it was reading it as a string. The way I was able to resolve this issue is by adding a derived column component to the package that replaced the date field by using the following expression

(DT_WSTR,255)Auth_from_date == "NULL" ? NULL(DT_WSTR,50) : (DT_WSTR,255)Auth_from_date

this is just an IF statement that will change the string "NULL" to an actual NULL value. The date is then passed thru a data conversion component and converted into DATETIME format.


Join on unnested integer array column and count occurrences in array I am trying to join two tables after I have unnested one of them. The tables are:

CREATE TABLE teachers (id SERIAL, first_name VARCHAR(60), last_name VARCHAR(60));

CREATE TABLE training_modules (id SERIAL, course VARCHAR(60), teachers_id INTEGER[]);

I want to write a query that will return the teachers names and the number of training_modules they've participated in (a count of their IDs), like so:

teacher_name  id_count
bob teacher   4
sally lady    3
jimbo jones   5

So far, I've been able to get a count of each ID with this query:

SELECT tid, count(*) as id_count
FROM training_modules tm, unnest(tm.teachers_id) as tid
GROUP BY tid;

All fine and good. I tried to apply that to another query, where I join the teachers table, and it doesn't work as expected. This query:

SELECT t.id, concat(t.first_name, ' ', t.last_name) AS teacher_name, tm.id_count
FROM (
   SELECT unnest(training_modules.teachers_id) AS id_count, count(*)
   FROM training_modules
   GROUP BY id_count
   ) AS tm
INNER JOIN teachers t ON tm.id_count = t.id;

Results in the table below, where the id_count column is no longer a count, but returns the same value as the t.id column.

 id |     t_name      | tc
----+-----------------+----
  5 | Jimbo Jones     |  5
  4 | Frank McGee     |  4
  6 | Sara Sarason    |  6
  2 | Joshua Jesps    |  2
  1 | Larry Bucatin   |  1
  3 | Natalie Fatali  |  3

Any help to get the right count appearing while joining the tables would be much appreciated.

Basically, you attached the column alias to the wrong expression in your 2nd query.

SELECT id, concat_ws(' ', t.first_name, t.last_name) AS teacher_name, tm.id_count
FROM  (
   SELECT unnest(tm.teachers_id) AS id, count(*) AS id_count
   FROM   training_modules tm
   GROUP  BY 1
   ) AS tm
JOIN   teachers t USING (id);

Aside:
concat_ws(' ', t.first_name, t.last_name) is typically better, as it only adds the separator where it makes sense (when both first_name & last_name are NOT NULL).


PostgreSQL WAL Archiving: Does having archive_mode set to 'on' without copying logs cause performance issues? Using PostgreSQL 8.4/CentOS 6: if I set the archive_mode parameter to on in postgresql.conf, but don't actually store the generated WAL files anywhere via the archive_command parameter, is there any performance impact?

This is for testing the potential for use of WAL archiving on a high-throughput production server: I'm hoping to set archive_mode to on, and set archive_command to something like

echo `date` %f %p >> archive_log.txt

so that I can monitor how many WAL files would get created during a given time period without actually copying them and risking disk space overruns. Will this affect my server's performance in any way?

There would not be performance impact there.  Your only limitation will be disk space.

Note this is actually used for things like incremental backups and point in time restore.  It's pretty well tested and used.


SQL Server 2008, database completely locked Our database becomes completely locked, and it seems that many tables not related to each other are locked at once. When this happens, it becomes locked for approximately 2 minutes, and when this happens it is not related to the amount of traffic that our application has at that time.

We have tried to replicate the issue on our test servers by

increasing the activity on the database (Inserts, Selects, Updates, Deleting),
increasing the connections to the database simultaneously,
increasing latch time,
generating temporary locks on a particular table

But could not arrive to a conclusion were this is coming from.

Is there any type of configuration, or background running tasks of SQL Server, that may cause this (Backups, Transaction Log related stuff etc)?

Is it possible to trace locks, by a date range (For example : Taking a snapshot of the current locks present in the database for a particular time)?

Whilst not answering your question re. tracing locks by date range, all sorts of things can have this effect on the database.

I would first check whether the problem is environmental. Use Windows Perfmon to see what the system resources are doing at the time. For example, I have seen this type of thing occur when SQL Server checkpoint operations takes an overly long time to complete. The aforementioned issue can normally be solved by configuring a "recovery interval" of 1 minute or so.

To be sure of what's going on though, I would setup Windows Perfmon counters to monitor CPU usage, memory usage, and disk queue lengths of any disks on which your DB / Log files may be residing. In my experience (High OLTP) disk queue lengths should be no more than 2 for any disk. You can normally configure a separate server to log these counters to disk every 30 seconds or so in order for the monitoring not to adversely affect your DB server's performance. The number of concurrent connections and locks can also be monitored in this way.


Table JOIN and CREATE new table extremely slow I'm using something like this to create a new table:

CREATE TABLE result AS
(SELECT calls.*,
      targ_with_planned_calls.*
 FROM calls
      INNER JOIN planned
              ON calls.first_id = planned.another_id);

The two tables have around 60k and 80k rows. One table has 5 columns and the other around 15. I'm using AWS RDS db.t2.medium instance. I've let this query run for as long as 10 minutes and it's still going.

Should this be taking so long? I'm wondering what the performance issue is. I would have thought it would be fairly quick.

You are doing DDL along with the SELECT

Doing CREATE TABLE AS SELECT mechanically does two commands

CREATE TABLE
INSERT INTO ... SELECT

This will produce locks on both calls and planned

I have written about this behavior in some of my older posts

Mar 23, 2012 : MySQL Locks while CREATE TABLE AS SELECT
Aug 08, 2014 : MySQL consistent nonlocking reads vs. INSERT ... SELECT


MySQL date range if data not available display 0 I've a table here here is the structure : id,id_berita,ip_user,tanggal

I try to use this query :

select count(id) as jumlah,tanggal from tb_trafik_berita WHERE (tanggal BETWEEN '2020-01-02 00:00:00' AND '2020-01-07 23:59:00') group by tanggal

and the output of that query,like this :

tanggal             | jumlah  |
____________________|_________|
2020-01-02 04:13:25 | 1       |
2020-01-02 04:13:39 | 1       |
2020-01-02 05:02:44 | 1       |
2020-01-03 13:23:22 | 1       |
2020-01-03 13:29:20 | 1       |
2020-01-04 01:36:45 | 1       |
2020-01-04 01:41:30 | 1       |
2020-01-07 14:37:08 | 1       |
2020-01-07 15:05:21 | 1       |

the output only showing data that available on table (only these date : 02,03,04,07)

if I want the output also showing date that not available on table : 02,03,04,05,06,07 , but put value 0,

so the expected output is :

tanggal             | jumlah  |
____________________|_________|
2020-01-02 04:13:25 | 1       |
2020-01-02 04:13:39 | 1       |
2020-01-02 05:02:44 | 1       |
2020-01-03 13:23:22 | 1       |
2020-01-03 13:29:20 | 1       |
2020-01-04 01:36:45 | 1       |
2020-01-04 01:41:30 | 1       |
2020-01-05 00:00:00 | 0       | <<< set 0 cuz data on table not available
2020-01-06 00:00:00 | 0       | <<< set 0 cuz data on table not available
2020-01-07 14:37:08 | 1       |
2020-01-07 15:05:21 | 1       |

I've try this query :

SELECT XX.dm AS 'Tanggal',IFNULL(YY.jumlah,0) AS 'Jumlah' FROM
(SELECT dm FROM
(SELECT CONCAT_WS('-',df,CONCAT(b,c)) dm FROM
(SELECT DATE_FORMAT(CURDATE(), '%Y-%m') df) a,
(SELECT 0 b UNION SELECT 1 b UNION SELECT 2 UNION SELECT 3) b,
(SELECT 0 c UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION
SELECT 9) c ) d WHERE DAY(dm) BETWEEN 1 AND DAY(LAST_DAY(dm))) XX
LEFT JOIN
(SELECT COUNT(id) AS jumlah,tanggal
FROM tb_trafik_berita where id_berita = '15'
GROUP BY tanggal) YY
ON XX.dm=DATE(YY.tanggal)
#add WHERE clause here
WHERE XX.dm BETWEEN 2020-01-02' AND '2020-01-07'
ORDER BY XX.dm;

but it's still has bug if i put WHERE (XX.dm BETWEEN '2019-12-13' AND '2020-01-11' ) filter date range in different year it showing like this :

the bug is begin from date 01 at 2020 only...

Anyone can help me to fix the bug ?

You should use a RECURSIVE CTE  available in Mysql8.0 and above.

WITH RECURSIVE abc AS (
  select CAST('2020-01-01' as DATE) as a
  union all
  select ADDDATE(a,interval 1 day)
  from abc where a<'2020-01-31')
SELECT * FROM abc;

Above will output all dates between '2020-01-01' and '2020-01-31'.

If you join this to your query, you should see 0 for dates that have no value in your table.

WITH RECURSIVE abc AS (
  select CAST('2020-01-01' as DATE) as a
  union all
  select ADDDATE(a,interval 1 day)
  from abc where a<'2020-01-31')
select SUM(x.jumlah), x.tanggal  from (
select
 count(id) as jumlah,
 tanggal
from tb_trafik_berita
WHERE (tanggal BETWEEN '2020-01-02 00:00:00' AND '2020-01-07 23:59:00')
group by tanggal
union all
select 0,a from abc
) x
GROUP BY x.tanggal


Sql 2000 attach Error 5172 When I attach a database file from SQL Server 2000 Standard Edition to SQL Server 2000 Database  on another  machine ,
I am receive the following error.

"Error 5172: The heather for file 'D:...' is not a valid database file header.
 The PageAudit property is incorrect."

I can not transfer data.What should I do?

The file's corrupt.  You either need a backup, or you need to engage with a consultant who can try manually editing the file.  (I'm not one of those consultants, and they're not cheap - you're in five-figure territory.)


MySQL - Many to Many relationship, or one to many with additional columns Our product contains some constraints that made us contemplate which table design will yield the best performance.
Our model:

Question table which contains fields like id, body, difficulty, and tags.

Each question can have up to five different tags.

Tags table which contains fields like id, and tag name.

The question table will have significantly more reads than writes.
We considered two design options:

Many to many relations where we'll have the following tables

Question - TagsQuestion - Tags

One to many relation where we'll have the following tables

Question - Tags

In this option, the question table will contain five tags columns (TagA, TagB, etc)
The "read" queries will mostly fetch questions by tag or tags.

Basically you can only use
Question - TagsQuestion - Tags

With the bridge table, if one tags is set for many questions.
It wuld also save some space, if you have a lot of questions.
The sencond option has the problem, that if you wqant all tags t be displayed you need five INNER JOINs, for every Tag one, while you can make the same query with two joins, with the first option..
Overall, the first option with bridge table, is the more flexible and space consuming option.


How to compare two tables that have no primary key? So I got two sets of tables at work that I have to compare the data.
The fields are identical, but there is no column that has unique entries.
(Employee ID, Assignment ID, Employee's last name, Employee's First Name,
Dependent Last name, Dependent First name, Dependent Date of Birth)
An employee can have multiple assignments, and each employee can have multiple dependents.

The data of the two tables were entered by different people and so they are messy.. and I was asked to do some data cleanup.
Some of the dependents can be missing for an employee, or the dependent's name is spelled wrong /blanks etc...

Is there a way to compare the two tables for unmatched records, so that I can create a table for all data?

I tried to use Find Unmatched Fields query in Access but it can only compare one field?

Thanks in advance!:)

==edit==

I'm adding some sample data here:

this is a start

select table1.*, table2.*
from table1
full outer join table2
 on table1.EmployeeID   = table2.EmployeeID
and table1.AssignmentID = table2.AssignmentID
where table1.EmployeeID is null or table2.EmployeeID is null
order by isnull(table1.EmployeeID, table2.EmployeeID)

But that order by may break the outer so if you get no rows remove it

Note this data does not appear to be properly normalized.  This would allow for the same EmployeeID with different name. It is odd that dependent is dependent on assignmentID. If you are going to clean it up then consider improving the data design.

Pick one to fix (add missing data) in the case table1

Then go after data the does not match

select t1.*, t2.*
from table1 t1
join table2 t2
  on t1.EmployeeID   = t2.EmployeeID
 and t1.AssignmentID = t2.AssignmentID
 and (    t1.DependentLastName <  t2.DependentLastName
       or t1.DependentLastName is null
       or t2.DependentLastName is null )
left table2 t2b
  on t1.EmployeeID        = t2b.EmployeeID
 and t1.AssignmentID      = t2b.AssignmentID
 and t1.DependentLastName = t2b.DependentLastName
where t2b.EmployeeID is null


Function in JOIN condition I have big dynamically constructed query with

FROM main_table t
LEFT JOIN table1 t1 ON my_func(t.col1, 1) = t1.col1
...
LEFT JOIN table6 t6 ON my_func(t.col1, 6) = t6.col1

Query is very slow, but if I remove my_func() from JOIN condition, it works ok. Next variant has the same time:

FROM (
  SELECT *
    , my_func(col1, 1) AS join_cond1
    ...
    , my_func(col1, 6) AS join_cond6
  FROM main_table
) t
  LEFT JOIN table1 t1 ON join_cond1 = t1.col1
  ...
  LEFT JOIN table6 t6 ON join_cond6 = t6.col1

How I can speed up the query?

Update

I wrote that I can't create 6 indexes on all the table1...table6, but indexes are needed on main_table only. Sorry for my mistake.

PostgreSQL has functional indexes, and I tried dynamic generation like

EXECUTE 'CREATE INDEX ix_main_table_my_func_'||_number||' ON main_table (my_func(col1, '||_number||'))';

and it was much more quickly than CTE. But I have troubles with deadlock if function is called concurrently from multiple places since CREATE INDEX sets lock. PostgreSQL can CREATE INDEX CONCURRENTLY ..., but it does not work in transaction blocks (and functions because functions are executed in transaction blocks).

Yeah, that's going to be slow. Best would be to pre-chew the data by adding those JOIN fields as computed columns to MainTable. If you don't control that table, you can try rewriting your query with a CTE, though I'm not sure how much it will help:

WITH NewMainTable
AS (
    SELECT t.*
        , my_func(t.col1, 1) as MyJoinField1
        , = my_func(t.col1, 6) as MyJoinField6
    FROM MainTable t
    )
SELECT *
FROM NewMainTable nmt
    JOIN table1 t1
        ON nmt.MyJoinField1 = t1.col1
    JOIN table6 t6
        ON nmt.MyJoinField6 = t6.col1;


How to connect an MSSQL database engine to a local network I need to let my public IP connect to a database that is located within the same local network.

Ex: MY public IP is: 190.222.222.222 The local IP of that PC is: 1.1.1.4 And this server already has an MSSQL (Localhost) engine. This means that if I connect to the Public IP it automatically calls me the LOCALHOST database engine.

I need that at the time of connecting to the public IP this call / connect to the local server database engine 1.1.1.7

The connection between server 1.1.1.4 and 1.1.1.7 is working. The firewall ports are open

the proper way - you need run Configuration Manager and check if tcp/ip support enabled for all necessary interfaces.


SQL Server - Does adding a subscriber to an existing publication generate a new snapshot or create locks on the publisher DB? I need to add a new subscriber with snapshot and transactional replication and I'm trying to determine if I need to add the subscriber after-hours or not.
This particular database is already being replicated on a different server, so there is an existing publication.

Does adding a subscriber to an existing publication generate a new snapshot?
If not, will the publisher DB have any locks while the subscriber is being created?

Thank you for your help.

No it directly does not, but a new snapshot needs to eventually be generated in case the data or schema has changed since the last snapshot was generated before replication begins for that subscriber, and regardless a snapshot needs to be initialized in the new Subscriber database as a starting point for further transactions that occur. BUT it doesn't have to happen at the point that you add the Subscriber. There's an option you can select for when to initialize and instead of selecting "Immediately" you can select "Manual". Then at your convenience (off-hours if needed) you can right click on the Publication and manually generate a new snapshot for that subscriber.

The locks on the Publisher from initializing a snapshot should be rather minimal as replication is a fairly efficient feature of SQL Server. But the part that adds the actual Subscriber doesn't cause any locks to the Publisher.


In MySQL User table and Login history log table with Left join gives an error for contains nonaggregated column I am trying to get all user information data that is on the USER table and also only the user's last login information(which is on another table called LOGIN_HISTORY).
I tried with Left join but MySQL is giving me the error which shows "Expression #15 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'login_history.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"
Here is the USER table

Please help!
and this is my LOGIN_HISTORY table.

The output which I want is

I have tried this query, I don't know what I am missing but it is not giving me the desired output. I only want all the user information with his LAST login_datetime column. I tried the below query but it is showing all the user records with duplicate and all the login_datetime.
I tried:
select u.id, u.firstname, u.lastname, u.email, u.username, u.password, lh.login_datetime
from user u
left join login_history lh on u.id = lh.user_id
group by u.id
order by DATE(lh.login_history) desc;`

Please help!

An alternative method to that suggested above would be to do the following (fiddle here):
CREATE TABLE user
(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR (256) NOT NULL
);

The number of fields is irrelevant, since you won't be grouping by them. This is just a sample for demonstration purposes.
Then:
CREATE TABLE login_history
(
  lh_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  login_dt TIMESTAMP NOT NULL,

  CONSTRAINT lh_user_dt_uq UNIQUE (user_id, login_dt),  -- same user can't login at the same time

  CONSTRAINT lh_uid_fk FOREIGN KEY (user_id) REFERENCES user (id)

);

Populate:
INSERT INTO user (email)
VALUES
('111@xyz.com'),
('222@xyz.com'),
('333@xyz.com'),
('444@xyz.com'),
('555@xyz.com'),
('666@xyz.com');

and:
INSERT INTO login_history (user_id, login_dt)
VALUES
(1, '2011-01-11'), (1, '2011-02-11'), (1, '2011-03-11'),
(2, '2012-01-12'), (2, '2012-02-12'), (2, '2012-03-12'),
(3, '2013-01-13'), (3, '2013-02-13'), (3, '2013-03-13'),
(4, '2014-01-14'), (4, '2014-02-14'), (4, '2014-03-14'),
(5, '2015-01-15'), (5, '2015-02-15'), (5, '2015-03-15');

And the query would be:
SELECT u.*, tab.lh_id, tab.ldt
FROM user u
JOIN
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
    lh_id,
    lh.user_id AS id,
    lh.login_dt AS ldt
  FROM login_history lh
) tab
ON u.id = tab.id
WHERE tab.rn = 1
ORDER BY u.id;

Result:
id  email        lh_id  ldt
1   111@xyz.com     3   2011-03-11 00:00:00
2   222@xyz.com     6   2012-03-12 00:00:00
3   333@xyz.com     9   2013-03-13 00:00:00
4   444@xyz.com    12   2014-03-14 00:00:00
5   555@xyz.com    15   2015-03-15 00:00:00

which, by inspection, is correct!
Now, in the fiddle, I've done a performance analysis of the query proposed above and @Akina's answer - and you'll see that my query is less performant. "So, what's the point?" you might ask?
Well, imagine a scenario where you want the last 2 logins or 3 or the last x of them... - just change the desired values of rn (put tab.rn <= 2 or tab.rn IN (1,2)- shown at bottom of fiddle)!
Using MySQL's window functions, in this case the ROW_NUMBER() function (examples here), you can answer many more questions of your data.
I would strongly urge you to become familiar with window functions like those used in the second piece of SQL - they are very powerful and well worth the effort to learn - they will repay that effort 10 times over...
EDIT:
Following a query by the OP concerning users who have never logged in, I did the following (fiddle - same as above).
In order to JOIN the user table to the login_history table with a field that doesn't exist in the login_history, we have to do a LEFT [OUTER] JOIN (the left join is automatically an outer one, so the OUTER keyword is optional).
I added a user (6, 666@xyz.com) (see above) who has no records in the login table.
Then, I used the following SQL:
SELECT
  u.id,
  u.email,
  ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
  lh.lh_id,
  lh.user_id AS luid,
  lh.login_dt AS ldt
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
ORDER BY u.id DESC;

Result (snipped for brevity):
id  email         rn    lh_id   luid    ldt
6   666@xyz.com     1    NULL    NULL   NULL
5   555@xyz.com     1      15       5   2015-03-15 00:00:00
5   555@xyz.com     2      14       5   2015-02-15 00:00:00
...
...

So, we see that through the LEFT JOIN we now have the user 6, but the corresponding records from the login history are NULL (as expected)!
Now we simply wrap the above query in another to get the latest (or 2 latest or 3...) login time(s) for a given user as follows:
SELECT * FROM
(
  SELECT
    u.id,
    u.email,
    ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
    lh.lh_id,
    lh.user_id AS luid,
    lh.login_dt AS ldt
  FROM user u
  LEFT JOIN login_history lh
  ON u.id = lh.user_id
) AS tab
WHERE tab.rn = 1  -- or tab.rn IN (1,2) - shown at the bottom of the fiddle
ORDER BY tab.id;

Result:
id  email          rn  lh_id    luid    ldt
1   111@xyz.com     1      3       1    2011-03-11 00:00:00
2   222@xyz.com     1      6       2    2012-03-12 00:00:00
3   333@xyz.com     1      9       3    2013-03-13 00:00:00
4   444@xyz.com     1     12       4    2014-03-14 00:00:00
5   555@xyz.com     1     15       5    2015-03-15 00:00:00
6   666@xyz.com     1   NULL    NULL    NULL

I also added the results for EXPLAIN ANALYZE (available from MySQL >= 8.0.18), and I also profiled the query (fiddle).
As with the previous solution using the ROW_NUMBER() window function, it's a bit slower and the plan is more complex but, as I said, it is also potentially far more powerful down the line. I would urge you to test with your own system (H/ware & S/ware) and data to see which solution best suits your requirements.


To minimize Cache misses in PostgreSQL? You can calculate cache misses as described here.
However, I am interested in how to minimize the phenomenon in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit.
I have some algorithms that are based on hash tables, which cause much cache misses by having random accesses.
I am interested in how you can minimize cache misses in PostgreSQL.

How can you minimize Cache misses in PostgreSQL by design?

Keeping things cached with PostgreSQL is a balance between what you can have in shared_buffers and what you have in the filesystem cache, and avoiding both of them being pushed out by other processes.

One of the first things you can do on PostgreSQL 9.4 is set huge_pages=on in your postgresql.conf.

This also involves doing some OS changes to allow PostgreSQL to start with this enabled. The appropriate sysctl variable is vm.nr_hugepages

You can do a quick and dirty calculation of what you should set vm.nr_hugepages to with the following bit of shell scripting.

echo $(grep ^VmPeak /proc/$(cat /var/run/postgresql/9.4-main.pid)/status | sed -e 's/VmPeak://' | sed -e 's/kB//' | sed -e 's/[ \t]//g')/$(grep Hugepagesize /proc/meminfo | sed -e 's/Hugepagesize://' | sed -e 's/kB//' | sed -e 's/[ \t]//g') | bc -l | awk '{print int($1+1)}'

This line grabs the VmPeak from the running PostgreSQL process in /proc/$pid/status, which tells you the peak virtual memory size for the PostgreSQL process. This number is in kB. It cleans up the spacing and extra output, then gets the measurement of Hugepagesize from /proc/memstat (which in my case was 2048 kB), then does the appropriate division, rounds the output to the nearest integer, then adds one for a safety margin.

In my case, it was 2147.

Make it permanent by doing the following:

echo "vm.nr_hugepages=2147" >>/etc/sysctl.d/postgresql.conf

This is important for the following reasons, excerpted from the documentation:

  /proc/sys/vm/nr_hugepages indicates the current number of "persistent" huge
  pages in the kernel's huge page pool.  "Persistent" huge pages will be
  returned to the huge page pool when freed by a task.  A user with root
  privileges can dynamically allocate more or free some persistent huge pages
  by increasing or decreasing the value of 'nr_hugepages'.

  Pages that are used as huge pages are reserved inside the kernel and cannot
  be used for other purposes.  Huge pages cannot be swapped out under
  memory pressure.

So, pages grabbed by the postgres process for shared_buffers won't be swapped out under memory pressure. And you'll have less stress on the TLB for your processor too.

Following that, we adjust the OOMKiller, because something randomly murdering postgres processes is not a good citizen. These settings strongly encourage it to not fire.

echo "vm.overcommit_memory=2" >>/etc/sysctl.d/postgresql.conf

echo "vm.overcommit_ratio=100" >>/etc/sysctl.d/postgresql.conf

Next we set a couple of kernel scheduler sysctls:

echo "kernel.sched_autogroup_enabled=0" >>/etc/sysctl.d/postgresql.conf

echo "kernel.sched_migration_cost=5000000" >>/etc/sysctl.d/postgresql.conf

The migration cost is the total time the scheduler will consider a migrated process "cache hot" and thus less likely to be re-migrated.

Autogroup enabled basically groups tasks by setsid() so perceived responsiveness is improved. But on server systems, large daemons like PostgreSQL are going to be launched from the same setsid(), and be effectively choked out of CPU cycles in favor of less important tasks.

After that, we can adjust the swappiness, which is a knob that controls the degree to which a system swaps processes out of physical memory. A higher number more aggressively swaps out, and a lower number keeps things in the filesystem cache longer. Setting this to zero will increase the chance of OOM killing under memory and I/O pressure.

echo "vm.swappiness=10" >>/etc/sysctl.d/postgresql.conf

These control how often dirty data is synced back to disk from the filesystem cache. Since dirty objects in the pagecache can't be freed, more aggressively writing back can keep more pages available for use, and help avoid large bursts of disk IO.

echo "vm.dirty_ratio=2" >>/etc/sysctl.d/postgresql.conf

echo "vm.dirty_background_ratio=1" >>/etc/sysctl.d/postgresql.conf

Since your PostgreSQL version output shows GCC 4.9, I'm assuming that you're running Debian Jessie or a derivative, so the 3.16 kernel will avoid some truly dreadful I/O issues that the 3.2 kernel had, so that's most certainly good news.

Once you've adjusted all these settings, reboot the system. This will help make sure that the hugepages aren't fragmented when allocated. The best and most reliable way though is to put hugepages=2147 (the number I needed on my server), in /etc/default/grub under GRUB_CMDLINE_LINUX, and run update-grub, then reboot. But I would test the necessity of this, as it is much harder to change than the sysctl.

These should give you a reasonable amount of pages in the filesystem cache and in shared_buffers. After this, making sure the fewest number of processes are running on your server (because they all compete for resources), should give you much of the page cache miss minimization you're looking for.

Further reading on huge pages and their effects are in the hugetlbpage documentation.

The vm settings and their effects are in the vm documentation.

This is a very approachable treatment of the Linux Memory system Understanding and Optimizing Memory Utilization

The PostgreSQL documentation goes into detail on the effects of huge pages at the bottom of the kernel resources page

Hope that helps. =)


Migrating Large Database from SQL Server 2008 to 2014 I currently have on a large database (~500GB) on SQL Server 2008 that is going to be relocated to a new SQL Server 2014 server.

My aim is to keep migration time down to a minimum.

Migrating the database by backing it up to a network share and then restoring is going to be very time-consuming.

My plan is trying to find some way of keeping the databases in sync until the cut-over.

I’ve been looking into log shipping, but I’m not sure of the process completely (I’m not a DBA, I don’t even play one on TV), and since it doesn’t leave the database in an available state, I’m not sure that’s practical.

I’m open to suggestions, as well any tool recommendations that might assist.

I'd suggest setting up database mirroring.

Assuming you setup mirroring in synchronous, high-safery, mode, the 2008 server will send data over to the 2014 server, as it is modified in the source database.

Once you decide to cut over to the new machine, you simply initiate a failover to the mirror database, "et voila", after a minute or two required for the 2014 machine to "upgrade" the database, it will come online and be ready to use.


What is the most efficient way to exclude records with a particular ID in a MySQL query for random records? I have an api endpoint that returns X amount of random posts from a table called "posts". This endpoint is used by a mobile app. It retreives random posts by doing a SELECT COUNT(*) on the posts table and returning the amount of posts in the table. It then enters a for loop in which, at the start of each loop, a random number from 0 to the COUNT(*) is generated. A random post is then obtained using the handy OFFSET. This for loop goes until X amount of random posts obtained.

pseudocode:

FUNCTION getRandPosts :

$numOfPosts = queryExecute("SELECT COUNT(*) from posts");

for (iterations < numOfRandomPostsNeeded) {
    $randomNumber = rand(0, $numOfPosts)

    $randomPost = queryExecute("SELECT * from posts LIMIT 1, OFFSET $randomNumber")

}

Now, for each call to a getRandPosts function I want them to always retrieve a unique post that wasn't retrieved before. For this current getRandPosts call AND FOR PASTS CALLS.

I've thought of several ways of going about doing this:

I could store the ID's of already seen posts in an array IN the app and then send this array to the getRandPosts function. The getRandPosts function then uses a "NOT IN" clause.

FUNCTION getRandPosts(ARRAY $idsOfPostsAlreadySeen) :
...
for ...

$randomPost = queryExecute("
    SELECT * from posts
    WHERE id NOT IN $idsOfPostsAlreadySeen
    LIMIT 1, OFFSET $randomNumber")

$idsOfPostsAlreadySeen.addToArray($randomPost.id)
...

After each random post is retrieved, save that post id to a table called "seenPosts" where there is also a column called idOfApp which is used to distinguish users.

FUNCTION getRandPosts(STRING appInstallID) :
...
for ...

$randomPost = queryExecute("
    SELECT posts.* FROM posts
    JOIN seenPosts.postID FROM seenPosts
    ON posts.id = seenPosts.postID
    WHERE seenPosts.postID = null AND seenPosts.appInstallID = $appInstallID
    LIMIT 1, OFFSET $randomNumber")

queryExecute("INSERT INTO seenPosts SET postID = $randomPost.id, appInstallID = $appInstallID")
...

I'd also like to ask about the idea of, for idea number one, creating a temporary table from the array and using JOIN with that temp table vs using the array.

I'm avoiding using RAND() because it has been said it doesn't work for large tables. I wonder how large is large?

Consider the idea that, at most, 600 posts will have to be excluded for any given user (so an array of 600 ids in the case of the first idea $idsOfPostsAlreadySeen... or 600 records in the seenPosts for a particular person).

I'm avoiding using RAND() because it has been said it doesn't work for large tables. I wonder how large is large?

As for "doesn't work", I say that is false.  RAND() does not repeat after 64K, like some random number generators.

As far as "how large", time the first query below -- if it is "too slow" then the table is "too large".  Then skip to the link at the bottom.

Case 1 Done all at once, this gives you $N random, not repeated, rows:

$array = SELECT id FROM ... WHERE ... ORDER BY RAND() LIMIT $N;

Case 2 If you need to fetch the rows individually, such as with separate requests and minimal "memory" between requests:

$rnd = UNIX_TIMESTAMP();
$offs = $offs + 1;
$array = SELECT id FROM ... WHERE ... ORDER BY RAND($rnd) LIMIT $N OFFSET $offs;

By remembering $rnd and $x (eg passing via a URL or SESSION), you can get the "next" one in a predictable sequence.

Caveat 1:  If rows are inserted/deleted, this sequence can mess up.
Caveat 2:  If the table is 'large', the SELECT could be costly.

8 Faster ways to get random rows.  None is 'perfect', and each depends on something not in evidence, such as whether there is an auto_increment id and whether that has gaps.


Redshift postgresql time series data index DISTKEY and SORTKEY I have around 6 billion rows of data to move to a redshift DB from MSSQL. The data will have three columns.

I plan to move the data one tag at a time as there are 4 years of history at one-minute intervals so the data will be out of time order, as this will be easier than moving all 4000 at once for a given time period in chunks.
I have read about DISTKEY and SORTKEY although not sure how I would implement for the best performance. Would anyone have any advice, should I split the table up into multiple tables so that it is not so long and how would I use DISTKEY and SORTKEY to improve performance.
Note: Redshift postgresql is the only option I have been given at this points.
Thank You

The choice of distribution key and sort key are driven by the queries you intend to issue on the table, for the choice made defines what queries can be issued in a timely manner on the table, and you need those queries to be the business queries you need to run.  You cannot chose either of these keys simply by taking a table on its own, without any consideration for how the table will be queried.
As an aside, I would strongly advise you, if your example value column here is representative, to convert your value column to an integer, by multiplying its values by 1000, and converting it back to a float when you need to use it.
This is because the sorting value for floats is the integer part of the number, and so all those example numbers have a sorting value of 1; which is to say, if most of your values are like that, min-max culling (the zone map) will have basically no effect, and that will make your queries behave much as they did on MS SQL - the entire column will be scanned - only the and expensive brute power of the hardware in the cluster will provide an improvement in performance.
Ah, and, finally, if you insert your data out of order, and you have six billion columns, you'd going to have a multi-day VACUUM after you've finished inserting your data.
Redshift is a knowledge-intensive database, which can handle Big Data in a timely manner when and only when it is operated correctly, and although I may be wrong, I think the people having you perform this work do not understand what they are getting into.


Analyze too slow after postgres upgrade I'm working on a testing server to upgrade a large database,  700GB with around 20000 tables.

The upgrade was fast enough using the --link option, but the analyze only script takes 8 hours, which is more downtime than I can justify. (9.3 to 9.4)

Is there anyway to speed up the analysis, or just run it in the background on the live production database after the upgrade?

What performance impact might this have?

8 hours to analyze 700GB seems like a really long time.  So, what makes it so slow?  Do you have expression indexes which use slow user-defined-functions?  That will certainly slow things down.  Do you have a very high setting for default_statistics_target?  Do you have a massively under-provisioned disk subsystem?

Are you using analyze-in-stages or just analyze-only?

Are you doing it in parallel with -j option?  Probably not if you are only upgrading to 9.4 (which is itself a mistake--if the upgrade is traumatic for you, why not leap several versions at once so you don't need to do it as often?)

As for running it in the background, often a database can get by with no statistics or crude statistics for ordinary operations, and only needs detailed statistics for reporting or analytics such as weekly, end-of-month, or end-of-year reports.  So then you can get away with opening the database for ordinary use before the analyze is done. (Or if you do analyze-in-stages, open it after the first stage is complete).  On the other hand, some databases need good statistics right from the beginning because otherwise even routine queries will use such horrible plans that they essentially never finish, and will hog up so much CPU and disk resources that they slow down the vacuumdb script.

There is no way to know which case you will fall into without an intimate knowledge of the particulars of your database.  If you have a sampling of some typical and/or critical queries from your production system, you can replay them against a test server with no statistics and see what happens.


SQL Service failed to start master.mdf is in use I've got this issue which has me baffled at the moment.

When I came in this morning there were 2 instances on a server with 4 SQL Server instances who had a backupthread process which got stuck when I killed this SQL Server process this stayed in the killed/rollback state. When trying to kill the process which was the culprit this showed to be a process which was non-existing. So after some googling and consulting with co workers we thought it best to restart the SQL Server instance.

So far so "good".

When stopping and then starting the SQL Server service again, the service refused to start. with the following error: The process cannot access a file because it used by another process.

And it was using the master.mdf file.

When checking which process was using the file it was the SQLservr.exe process which was using the file. (Using process explorer https://technet.microsoft.com/en-us/sysinternals/bb896653.aspx)

When I looked over to services, the service of the instance was successfully stopped. The Process however was still running according to task manager and process explorer although it was not using any resources.

When trying to kill this process ID it gave me an access denied error, when trying to kill it through command line with using taskkill /pid XXXX /F it still gives me access denied error, even when running as administrator with local admin and domain admin account.

The only shortterm solution was rebooting the Server, this solved the issue. Until I tried to restart the service again. When we got the same problem again.

EDIT: They rebooted again without my knowledge, now the issue cannot be recreated

SQL Server is patched until the latest version, however the Windows server has not been patched since 2013.

We are running on SQL Server 2012 and Windows server 2012

Is this a known issue with windows server? Is this a SQL Server issue? Any guidance or information would be helpful.

I have found the issue, it was something called a ghost process.

If you want then use the extra tool to kill the process SQL Server will start.

more information on http://amorvita.github.io/2013/08/finding-a-zombie-process/


How to create a function(table_name) that return a collection in Oracle? I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted.

I've been doing some search, many examples are of the form:

first, define a table type;
then, fill a table of that type and return it.

But I won't know the structure of the collection that need to be returned until the function is called, so I can not define a table type at the time of programming.

How can I make it?

I haven't syntax checked this but it should put you on the right path.

This should be put in a package but for demonstration purposes...

PROCEDURE GET_COLUMNS(table_name_in IN VARCHAR2, v_query_out OUT sys_refcursor)
IS
v_count NUMBER(9);
CURSOR the_columns IS
Select COLUMN_NAME
from all_tab_columns
where table_name='table_name_in';
v_query VARCHAR2(4000):= 'Select  ';
BEGIN
IF table_name IS NOT NULL THEN
--requires permission to access dba_tables, if not available use ALL_TABLES
--or USER_TABLES
   SELECT count(*)
   INTO v_count
   FROM dba_tables
   where table_name = table_name_in;
--might need the schema name too in a multi schema environment
   IF v_count <> 1 THEN
      RAISE bad_table_name;
   END IF;
ELSE
     RAISE bad_table_name;
END IF;

for all_columns in the_columns LOOP
v_query := vquery || all_columns.COLUMN_NAME ||',';
END LOOP;
--trim off the last comma
v_query := SUBSTR(v_query, 1, LENGTH(v_query) - 1);
v_query := v_query ||' from ' || table_name_in;

OPEN v_query_out for v_query;

EXCEPTION
when bad_table_name THEN
--log an error message
DBMS_OUTPUT.PUT_line('Table name is not accessible or does not exist');
RAISE; --could raise an custom exception if more detailed logging required
END;

This should work on Oracle 9,10 and 11 and could be simplified for 12 by using RETURN_RESULT.


MS SQL Server 2012 Log Shipping Questions I have a few questions regarding Log Shipping in SQL Server 2012.

If the primary database server dies, is it possible to allow users to connect to the secondary database so as to continue working with Read/Write access?
Once the primary server comes back online, is it a difficult process to switch back to the primary database server?
Will changing the Recovery model to Full (from simple) have any issues?
Will my existing backups (using Symantec Backup Exec) be affected by enabling log shipping and switching to a full recovery model?

1) If the primary database server dies, is it possible to allow users to connect to the secondary database so as to continue working with Read/Write access?

You have to manually failover to the secondary server as logshipping does not have a built-in mechanism to support automatic failover.

Once you failover, your application/s (provided they are using ADO.NET or SQL Native Client) can leverage the Failover Partner=Secondary_server

Brent has blogged about failover partner:

  you don’t need to use database mirroring to use Failover Partner.  Whether you’re using database mirroring, replication, log shipping, or duct tape, much like the honey badger, your applications don’t care.  They’ll just try to connect to the Failover Partner name whenever the primary server is down.

.

  2) Once the primary server comes back online, is it a difficult process to switch back to the primary database server?

Traditionally, depending on the size and number of database that are invloved in logshipping, it may or may not be difficult.

Database size and your network bandwidth matters a lot. Normally, you just have to resetup log-shipping. Meaning - point all your apps to primary and in background, do a full backup of primary, copy that to secondary server and restore it with no-recovery with subsequent log backups.

To minimize the downtime, you can use "Reverse logshipping" will prove to be a huge help.

  3) Will changing the Recovery model to Full (from simple) have any issues?

Yes. Changing recovery model breaks the log chain. Paul Randall talks in his myths section. Also, see below chart :

  4) Will my existing backups (using Symantec Backup Exec) be affected by enabling log shipping and switching to a full recovery model?

Once you setup logshipping, there is no need to take any additional log backups. In fact, any adhoc log backups will break the log chain. Use COPY_ONLY backups.

As I mentioned above, any change to the recover model should be followed by a FULL backup. That will be your base backup. Any adhoc backups (even full) should be taken with a COPY_ONLY option as if you are relying on differential backups then it will be an issue if someone takes an adhoc full backup.

As a side note, always fully document, test, test and test + automate (as much possible) your disaster recovery strategy. See Paul's DR Poster for more details.


Moving tables to new tablespace needs temp space or any kind of addictional space? (Oracle 11g) I've been trying to search for this info on oracle documents but they just don't talk about it.

I need to move about 2TB of different tables from 3 different tablespaces to a new tablespace (encrypted one). Do I need to have temp space to do it (2TB)?

Moving a table does not use the temporary tablespace.

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     64
pga_aggregate_target                 big integer 256M
sga_target                           big integer 1G

SQL> select bytes/1024/1024 as bytes_mb, tablespace_name from user_segments where segment_name = 'SALES2';

  BYTES_MB TABLESPACE_NAME
---------- ------------------------------
      2240 EXAMPLE2

SQL> select file_id from dba_temp_files;

   FILE_ID
----------
         1

SQL> alter database tempfile 1 drop;

Database altered.

SQL> select file_id from dba_temp_files;

no rows selected

SQL> select sys_context('userenv', 'sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
191

SQL> select serial# from v$session where sid = 191;

   SERIAL#
----------
       415

SQL> alter table sales2 move tablespace example3;

Table altered.

SQL> select bytes/1024/1024 as bytes_mb, tablespace_name from user_segments where segment_name = 'SALES2';

  BYTES_MB TABLESPACE_NAME
---------- ------------------------------
      2240 EXAMPLE3

SQL> select max(temp_space_allocated), count(*) from v$active_session_history where session_id = 191 and session_serial# = 415;

MAX(TEMP_SPACE_ALLOCATED)   COUNT(*)
------------------------- ----------
                        0         33


Database compatibility level We are on SQL Server 2008 R2 and have DBs compatibility level set to SQL 2000 or 2005.

What are differences in compatibility level across the versions ?

Are there any advantages changing it to 2008 or higher?

There are plenty of differences.  One that comes to mind right away is the MERGE statement, which first became available in 2008.


SQL Network Name owned by Available Storage OwnerGroup in Server 2012 FCI I did a simple get-clusterresource and noticed the 'owner group' is 'available storage'.  Not SQL Server (instance)for the SQL Server Network Name (instance).  I want to fix this ASAP but I'm having a hard time finding out how to move a owner group only.

Or actually from what I can tell the SQL Network Name (instance) isnt even supposed to be a 'network name'.  It's a instance.  I think this is a extra line I don't need and should remove this:

SQL IP Address 1 (clsnwname... Online                        SQL Server (I1)             IP Address
SQL IP Address 1 (clsnwname2... Online                        SQL Server (I2)              IP Address
SQL Network Name (clsnwname... Online                        SQL Server (I1)             Network Name
SQL Network Name (clsnwname2... Online                        SQL Server (I2)              Network Name
SQL Network Name (I1)       Failed                        Available Storage             Network Name
SQL Server (I2)              Online                        SQL Server (I2)              SQL Server
SQL Server (I1)             Online                        SQL Server (I1)             SQL Server
SQL Server Agent (I2)        Online                        SQL Server (I2)              SQL Server Agent
SQL Server Agent (I1)       Online                        SQL Server (I1)             SQL Server Agent

I was testing a new server that will be released at some point and noticed the Windows Updates were not installing SQL updates.  It stated it cannot update because the IP is offline.

I tested failover repeatedly and it always has worked, and continues to work as shown in the GUI with no issues and IP is up.  Replication and all connections are working fine.

It looks like a rogue client access point was added or someone manually moved/changed something.

Your two instances are as such:

SQL Server (I2)              Online                        SQL Server (I2)
SQL Server (I1)             Online                        SQL Server (I1)

They have corresponding network names in the proper groups:

SQL Network Name (clsnwname... Online                        SQL Server (I1)
SQL Network Name (clsnwname2... Online                        SQL Server (I2)

Each client access point also has at least one online IP:

SQL IP Address 1 (clsnwname... Online                        SQL Server (I1)
SQL IP Address 1 (clsnwname2... Online                        SQL Server (I2)

The outlier (failed) client access point:

SQL Network Name (I1)       Failed                        Available Storage

Which you've stated isn't part of any of the SQL Resource Groups, isn't online (failed) doesn't have an IP associated with it (as far as we can tell from your output).

This leads me to believe:

Someone attempted to make this and didn't know what they were doing
Incorrect automated setup was run (with DSC, powershell, or other tooling [chef, puppet,etc])
Testing was done and not cleaned up properly

I doubt it was malicious. In any event, check out the dependency report - which I'm guessing will be blank. If it is, feel free to remove it and the associated computer object in AD. If it isn't blank, update the post with more information please.


ROW_FORMAT - COMPRESSED vs COMPACT vs DYNAMIC In MySQL InnoDB, what is the difference between COMPRESSED, COMPACT and DYNAMIC for ROW_FORMAT?

What are the benefits between each other?

COMPACT is format supported by Antilope. It stores first 768 bytes of BLOB in case its value doesn't fit in page.

DYNAMIC is almost the same as COMPACT except only 20 bytes for each BLOB field is used. Benefits - more BLOB fields are possible in a record.

COMPRESSED is used for compressed tables. Hence its benefits.


What am I missing for my SSRS Service account local server permissions? I have a relatively new instance on SSRS 2008R2 SP3 running on Windows Server 2008 R2. I am trying to set up separate domain service accounts for the separate SQL Services i.e. agent, engine, reporting, etc. I have done this successfully for the Agent and Engine services using simple Domain accounts and following the required permissions from docs.microsoft.com.

At first I had made local accounts for all services because of a video I watched that used that as an example. So now that I actually wanted to start using reporting services I switch the account to a newly created Domain account. Using the pertinent section in the above article I configured, via GPO, the account to have "Log On As A Service" right. I ensured that right is present in the groups I created.

I then went into the Reporting Services Configuration Manager to change the account which was successful except that the step for starting the service ended in error.

System.InvalidOperationException: Cannot start service ReportServer on computer 'SQLSERVER01'. ---> System.ComponentModel.Win32Exception: Access is denied
   --- End of inner exception stack trace ---
   at System.ServiceProcess.ServiceController.Start(String[] args)
   at System.ServiceProcess.ServiceController.Start()
   at ReportServicesConfigUI.Panels.ServerInformationPanel.StartStopServiceTask(Boolean start)

Event Viewer has a similar error which I include to save the questions.

The SQL Server Reporting Services (MSSQLSERVER) service failed to start due to the following error:
Access is denied.

So this is a Windows permission issue but I don't see what the problem could be since the Log On As a Service permissions are set correctly. That is verified by the security logs that show a successful logon of the account. If I make my service account a local admin on the server then the service starts correctly but I should not have to do that.

As part of the troubleshooting I was doing to try and address the issue I ran Process Monitor while I was trying to start the service.

I tracked as event that also had a result of "Access Denied" which was the service trying to read files inside the directory where reporting services was installed an running from. In my case it was: "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services"

I check the security of the folder and the service account I was using had no rights to the folder. That is why giving it local admin rights fixed it because that group did have access.

I gave my service account Modify access to the folder and its contents. After that I was able to start the service.


Monthly Growth - Bigquery I need your help again. I need to calculate the monthly growth for the trips.
The query that I have written is below:
SELECT EXTRACT(YEAR FROM DATE (stoptime)) AS Year, EXTRACT(MONTH FROM DATE (stoptime)) AS Month,
    COUNT(bikeid) AS Trips,
    ROUND(100* (COUNT(bikeid)- LAG(COUNT(bikeid),1,0) OVER (PARTITION BY 'Year' ORDER BY 'Year', 'Month'))/lag(COUNT(bikeid),1) OVER
    (PARTITION BY 'Year' ORDER BY 'Year', 'Month')) ||'%' AS growth
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE end_station_name LIKE '%University%'
GROUP BY Year, Month
ORDER BY Year DESC, Month DESC

However, the query below cannot give me the right output. The output from the query above as below:

I do not know which part of my query is wrong. Kindly help me with this.
My desired output example:

Thank you!

and if you are calculating monthly growth, why aren't you calculating the number of trips in eahc month :
SELECT
    EXTRACT(YEAR FROM DATE (stoptime)) AS YEAR
    , EXTRACT(MONTH FROM DATE (stoptime)) AS MONTH
    , COUNT(bikeid) AS Trips
    , ROUND(100* (COUNT(bikeid)- LAG(COUNT(bikeid),1,0) OVER (PARTITION BY 'Year','Month' ORDER BY 'Year', 'Month' desc))
                 /lag(COUNT(bikeid),1) OVER (PARTITION BY 'Year','Month' ORDER BY 'Year', 'Month' desc)) ||'%' AS growth
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE end_station_name LIKE '%University%'
GROUP BY Year, Month
ORDER BY Year DESC, Month DESC


How do I tell what agent profile is being used? When you change from one replication agent profile to another using the SSMS GUI, you often have to stop and restart the agent for the change to take effect.

I'm trying to debug an issue where it looks like someone changed the active profile, but replication is behaving as if a different profile was being used. How do I determine the actual replication agent profile being used?

A parallel - when using sp_configure, both the configured values and the run values are returned. The Replication GUI seems to be returning the configured values. How do I determine the running values for the agent profile?

We have a profile that is set to skip certain errors. We turn on that profile briefly, only if needed. And then we turn the default profile back on. It looks like someone turned on the error skipping profile and left it on. But when I open the agent profile dialog box, it's set to the correct, default profile.

Ref: Replication Agents: Profiles and Parameters

Use the below query to find out which agent is using which profile.

SELECT
    [publication] as Publication
    ,c.srvname as SubscriberName
    ,b.profile_name as Profile
    ,[name] as DistributionJobName
FROM
    [distribution].[dbo].[MSdistribution_agents] a
INNER JOIN
    msdb.[dbo].[MSagent_profiles] b
    ON a.profile_id = b.profile_id
INNER JOIN
    master..sysservers c
    ON a.subscriber_id = c.srvid
ORDER BY
    b.profile_name;

If you want to see what profile your agent job was using previously best place to see is job history.


One user affects all other users I'm completely new to working with databases (other than retrieving data from them as a front end developer.) and have an idea I was toying with. However, I'm struggling to find exactly what to Google due to my lack of knowledge of DBs and the terminology surrounding them.

I have a database that houses user's ratings of various items. One user can have one rating per item and can rate as many different items as they want. The goal would be to find other users who have similar items rated similar ratings and use their data to recommend new things to the first user. This means any single rating has some level of reach to every user in the database. (A similarity score of some kind). I.e. when I say I like item 1, the ratings of anyone else who liked item 1 are now of higher value to me. And anyone who didn't like it, their ratings are of lesser value to me.

I know for sure people do these things (Netflix, Amazon, Google) but I'm honestly not sure where to begin learning how. Any tips are appreciated.

A Graph Table in SQL Server should suit this purpose. You can use a graph table to create complex many-to-many relationships using Nodes and Edges then perform complex association queries with relative ease. Below is a rough example of a 'Netflix-style' recommendation based on previously liked movies:

Setup:

USE master
GO
IF EXISTS (SELECT * FROM sys.databases WHERE [name] = 'RatingsDB')
    DROP DATABASE RatingsDB
GO
CREATE DATABASE RatingsDB
GO
USE RatingsDB
GO
CREATE TABLE Users (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    UserName NVARCHAR(255)
) AS NODE
GO

INSERT INTO Users (Username)
VALUES ('Bob'), ('Carol'), ('Frank'), ('Beth'), ('Jan'), ('Marco')
GO

CREATE TABLE Movies (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    MovieTitle NVARCHAR(255)
) AS NODE
GO

INSERT INTO Movies (MovieTitle)
VALUES ('Red Dawn'), ('Titanic'), ('Jurassic Park'), ('Clerks'), ('Four Weddings & A Funeral'), ('Saving Private Ryan'), ('Mallrats')
GO

CREATE TABLE Likes AS EDGE
GO

CREATE TABLE SimilarMovies AS EDGE
GO

INSERT INTO SimilarMovies
VALUES (
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Red Dawn'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Saving Private Ryan')
), (
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Four Weddings & A Funeral'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Titanic')
), (
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Clerks'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Mallrats')
)

INSERT INTO Likes
VALUES (
    (SELECT $node_id FROM Users WHERE UserName = 'Bob'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Red Dawn')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Carol'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Titanic')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Frank'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Mallrats')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Beth'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Jurassic Park')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Jan'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Four Weddings & A Funeral')
), (
    (SELECT $node_id FROM Users WHERE UserName = 'Marco'),
    (SELECT $node_id FROM Movies WHERE MovieTitle = 'Clerks')
)

Query Example:

SELECT Users.UserName,
    RecommendedMovie.MovieTitle AS Recommendation,
    WatchedMovie.MovieTitle AS BasedOn
FROM Movies WatchedMovie, Users, Likes, Movies RecommendedMovie, SimilarMovies
WHERE MATCH(Users-(Likes)->WatchedMovie -- Find movies that a user likes
    AND WatchedMovie-(SimilarMovies)->RecommendedMovie) -- Find similar movies to the one the user liked
UNION
SELECT Users.UserName,
    RecommendedMovie.MovieTitle AS Recommendation,
    WatchedMovie.MovieTitle AS BasedOn
FROM Movies WatchedMovie, Users, Likes, Movies RecommendedMovie, SimilarMovies
WHERE MATCH(Users-(Likes)->WatchedMovie -- Find movies that a user likes
    AND WatchedMovie<-(SimilarMovies)-RecommendedMovie) -- Find similar movies to the one the user liked

Results:

UserName    Recommendation              BasedOn
-----------------------------------------------------------------
Bob         Saving Private Ryan         Red Dawn
Carol       Four Weddings & A Funeral   Titanic
Frank       Clerks                      Mallrats
Jan         Titanic                     Four Weddings & A Funeral
Marco       Mallrats                    Clerks


What is the date time format for msdb.dbo.agent_datetime scalar value function? Looking at msdb.dbo.agent_datetime scalar value function.  What date / time format is this expressed in, UTC?  Same question for msdb.dbo.sysjobs columns : run_date, run_time

Are you asking about the format or the value?
By "value" I mean if it is in UTC or the local datetime value.
The function doesn't have a stored value, it work with what you pass into it and returns a datetime value based on that. I.e., there's no UTC relevance for this.
The columns, for instance in sysjobhistory, are not in UTC. They are in the local timezone (for your SQL Server instance).
As for the format:
The tables uses a pair of int columns - one for date and one for time. So the int 20191223 means 2019-12-23 (I'm using ISO 8609 format when communicating datetime values).
The function accepts two parameters, both int. One for the date and one for the time, as described above. It returns a datetime value (which has no format - it is just a value).
In case you wonder how I know it returns a datetime and not datetime2(3): You can investigate a value by casting it to sql_variant and then investigate properties of that value using the SQL_VARIANT_PROPERTY function:
DECLARE @a sql_variant
SET @a = dbo.agent_datetime(20201023,154500)
SELECT SQL_VARIANT_PROPERTY(@a, 'BaseType')

Above returns:
datetime


Performance impact of Splitting Table across files EDIT: I think the original question (below) was not worded very well, and probably caused confusion. The basic question is:

If I have a SQL Server 2005 database using a single .mdf file and I change it to use 2 files, should I expect performance to increase, decrease or remain the same?

We have an archive database that stores all reports sent out from our company since the beginning of time. As you can imagine the size of the database is large and grows quite quickly (currently 150GB and a few GB a week).

The only table in this database has a numeric primary key and a varbinary(MAX) column holding the file. All documents are retrieved via the primary key and performance is not currently an issue.

The problem we are facing is from the network team who backup and transfer the .mdf file over to our DR site every night. For them it would be better if we had multiple smaller files, rather than one big file. They are also saying it would make it easier to manage allocating space for future growth from our virtual infrastructure that it is sitting on.

Are there likely to be any performance loss/gain in splitting the table across multiple files on different virtual drives (potentially different physical drives).

I don't want to explore the problems of storing documents in a database, or running SQL Server on a VM, and we don't really want to change the database schema if we can help it, due to the cost in rewriting chunks of multiple apps.

The problem we are facing is from the network team who backup and
  transfer the MDF file over to our DR site every night.

Are they really transferring the mdf file to the DR site? If so, stop, now.

If thankfully backups are being taken, or you can persuade them to start taking backups instead of shifting the mdf, split the backup to multiple files.

BACKUP DATABASE [MyDatabase]
TO
    DISK = 'D:\MyDatabase_file1.bak'
    , DISK = 'D:\MyDatabase_file2.bak'
    , DISK = 'D:\MyDatabase_file3.bak'
    , DISK = 'D:\MyDatabase_file4.bak'
WITH
    NAME = 'MyDatabase backup'
    , STATS = 10

  They are also saying it would make it easier to manage allocating
  space for future growth from our virtual infrastructure that it is
  sitting on.

If you want to add additional storage to the database by way of additional files, that is also possible.

ALTER DATABASE [MyDatabase]
ADD FILE
(
    NAME = MyDatabase_file2,
    FILENAME = 'D:\MSSQL\Data\MyDatabase_file2.ndf',
    SIZE = 64GB,
    FILEGROWTH = 8GB
)

For this particular situation however, one does wonder why the administrators can't manage storage by way of the (storage) virtualisation layer.