Error: There is a column named in table "*SELECT*", but it cannot be referenced from this part of the query Assume we have a simple table,

CREATE TABLE foo ( a int PRIMARY KEY, b int );

I'm wanting to write a complex query inside and refer to that query

INSERT INTO foo
  SELECT 5 AS z
ON CONFLICT (a)
DO UPDATE SET b = z;

However when I execute that I'm getting an error like this,

ERROR:  column "z" does not exist
LINE 4:     DO UPDATE SET b = z;
                              ^

  HINT:  There is a column named "z" in table "SELECT", but it cannot be referenced from this part of the query.

Is there a way to make a structure like this work?

PostgreSQL doesn't see the column by the alias you've given it. That's not how it's parsed. It sees the z by the name you've tried to insert it as a on a psuedo-table EXCLUDED. You want to do this instead,

INSERT INTO foo
  SELECT 5 AS z
ON CONFLICT (a)
DO UPDATE SET b = EXCLUDED.a;

For more information see the docs on INSERT,

  The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.


Serial Plan in plan cache I have a query which was executing slowly earlier. Later I found out that it was not running parallel and this is making the query execution slow.

The query involves a big view and then querying the view with lots of temp tables and sub query.

I removed one UDF from the view and used inline functions and also used a scalar TVF and then it started running fast in parallel execution.

It was going well for few days and one fine day I noticed that the query ran slowly. So I checked the execution plan and found out that the query is executing in serial mode. I checked the plan cache for the query and I saw lots of cached plans involving that view. I deleted plans which are not parallel and then the query runs quickly.

Now I do this every morning to force the query to run parallel.

Additional Details:

SQL Server 2016 Standard
Query gets generated via LINQ-SQL from the dot net application. So ad hoc query.

How can I force the query to run parallel forever?

At first glance, this sounds like a classic parameter sniffing problem.

SQL Server builds an execution plan for the first set of parameters that get called when the plan needs to be compiled, and then reuses that plan over and over through the day. You can see what parameters they are - when you're viewing the serial plan, right-click on the select statement, and go into properties. On the properties window, look for Parameters, and then Compiled Values. That'll show you which values produce the serial plan.

To force the plan to always run parallel, you have several different options (many of which Erland covers in his excellent post that I linked to above), including:

Tune the indexes so everything gets a better plan (to get advice on that, post the plan & server details as described in Getting Help with a Slow Query)
Temporarily, use a plan guide to pin the parallel plan in cache (but just know that if the query changes by a single letter, the plan guide will silently fail since it no longer matches the query)
Use an OPTIMIZE FOR hint in the plan to guide it towards values that produce a parallel plan
Use the new ENABLE_PARALLEL_PLAN_PREFERENCE hint in SQL Server 2016

That's just a quick answer - but for much, much more, read Erland's excellent post, Slow in the App, Fast in SSMS that explains how one query can get different plans, and how to fix it.


How to suppress activation procedure output from the error log? I found so many below informational messages (bulk messages with no any error) are logged in the SQL Server error log.

  The activated proc 'x' running on queue 'y' output the following:  'z'

I want to stop this logging. Is there any trace flag that can help?

Seems like you have an activation procedure that explicitly outputs the string y (probably using print but possibly raiserror, select, or output).

Why don't you change the procedure so that it doesn't have any output? Output shouldn't be needed when run through service broker. Or if the procedure is also sometimes run interactively, and output is required in that case, you could require a param for output (or create a wrapper procedure and call that instead).

There is no trace flag to suppress activation procedure output from the error log. This was a design decision to assist in troubleshooting activation procedures (since they are not typically run interactively).


How do I select the count of a specific value without grouping in postgresql? I have a view that's a result of a UNION of two selects. It has contents similar to this:

id foreignkey
1  a
2  a
3  b
4  c

I'm looking to add a column in the view's definition that will show a count for the values, but without aggregating them. Somthing like this:

id foreignkey count
1  a          2
2  a          2
3  b          1
4  c          1

my existing query is basically:

create or replace view x as
select * from a
union
select * from b

I want the count on the result of the union but I"m not sure how to do that.

EDIT - made some progress, with a normal table I can do this:

SELECT *,
 (select count(id) from mytable where t.data = data)
FROM mytable as t

The problem comes into play because the table is really a union of two tables. Not sure how to do it without doing the union twice?

You can use a window function:

create or replace view x
as
select id, foreignkey, count(*) over (partition by foreignkey) as fk_count
from (
  select id, foreignkey from a
  union
  select id, foreignkey from b
) x;

Unrelated, but:

UNION removes duplicates from the result, UNION ALL does not. If you know there are now duplicates, then you should be using UNION ALL because it's faster.


Oracle SQL- special characters delimited strings I have a following string in my table in the following format:
APPLE- GREEN- 40%; BANANA- YELLOW- 15%; APPLE- RED - 25%; GRAPES- GREEN - 20%
I want a output as:
Output
APPLE - 65%
Which is the sum of %quantity purchased of total.

select Col1, sum(Col3)
from
    (
    select
        regexp_substr(column_value, '\w+', 1, 1) as Col1,
        regexp_substr(column_value, '\w+', 1, 2) as Col2,
        regexp_substr(column_value, '\w+', 1, 3) as Col3
    from split('APPLE- GREEN- 40%; BANANA- YELLOW- 15%; APPLE- RED - 25%; GRAPES- GREEN - 20%',';')
    )
group by Col1

create or replace type split_tbl as table of varchar2(32767);
/

create or replace function split
(
    p_list varchar2,
    p_del varchar2 := ','
) return split_tbl pipelined
is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_list;
    l_value    varchar2(32767);
begin
    l_list := replace( l_list, ' ',  '' );
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row( substr( l_list, 1, l_idx-1) );
            l_list := substr( l_list, l_idx + length(p_del) );

        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split;


Can't stop execution of "ADD PERIOD" I'm trying to conditionally execute an ALTER TABLE command (this one, to be precise).  However, I can't get the ADD PERIOD to be conditional and I suspect this might be a bug.

Here's what I've tried to skip execution if the PERIOD already exists on the table.  All of these raise the same error:

  Msg 13597, Level 16, State 2, Line xx

  Temporal SYSTEM_TIME period is already defined on table 'msdb.dbo.sysjobhistory'.

Only add the PERIOD if it doesn't currently exist:

if not exists(select 1 from sys.periods where object_id = OBJECT_ID('sysjobhistory'))
    ALTER TABLE [dbo].[sysjobhistory]
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

This raised the error.
Since the above didn't work, I wanted to make sure my IF statement was evaluating correctly so I tried:

if 1=2
    ALTER TABLE [dbo].[sysjobhistory]
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

But that raised the error as well.
Use SET NOEXEC ON to stop execution of the code:

set noexec ON
    ALTER TABLE [dbo].[sysjobhistory]
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
set noexec OFF

Didn't work.  The ALTER TABLE still raised the error.
Admit defeat and wrap it in a TRY/CATCH and just ignore the error:

begin try
    ALTER TABLE [dbo].[sysjobhistory]
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
end try
begin catch
end catch

This raised the error too -- which I didn't expect since it was a level 16 and, according to the docs, a level 16 should go to the CATCH block.

Is this a bug in Azure SQL Server?  This is running on a Managed Instance.  The @@VERSION reports:

  Microsoft SQL Azure (RTM) - 12.0.2000.8   Nov  2 2018 21:17:06   Copyright (C) 2018 Microsoft Corporation

UPDATE:

I can use a similar technique to skip adding columns that already exist without any issues:

if not exists(select 1 from sys.columns where name = 'StartTime' and object_id = OBJECT_ID('sysjobhistory'))
    ALTER TABLE [dbo].[sysjobhistory]
    ADD StartTime DATETIME2 NOT NULL DEFAULT ('19000101 00:00:00.0000000')
GO

The above code only executes if the sysjobhistory does not contain the column StartTime.  If it already does, the code does nothing and continues executing.  The ALTER TABLE with ADD PERIOD always causes an execution error even if the code is inside an IF condition that evaluates to false.

You need dynamic SQL for DDL like this.

IF NOT EXISTS(SELECT 1 FROM sys.periods WHERE object_id = OBJECT_ID('sysjobhistory'))
    DECLARE @sql NVARCHAR(MAX) = N''
    SET @sql += N'
    ALTER TABLE dbo.sysjobhistory
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime);'

    EXEC sys.sp_executesql @sql


Measuring Character Set Conversion We have an Oracle 12c (12.2.0.1) database server set up with AL32UTF8 (Unicode 5.0 UTF-8 Universal character set) character set as per Oracle recommendations.

However, the application that uses this database comes with a vendor recommendation to use WE8MSWIN1252 (MS Windows Code Page 1252 8-bit West European) character set instead.

I know, at least in general terms, how to convert from one character set to another, using either the Data Migration Assistant for Unicode or via a full Export/Import.

But there are two areas that I'm not clear on.

One is that Oracle says there is a potential for data truncation issues (and it explains how/why). Is there any way to find out if that's an issue within my database, short of converting the database in test and trying to do a row-by-row comparison between the converted and non-converted data?

More importantly, since the vendor wants us in a character set that's not the Oracle-recommended default, is there any way to measure what, if any, performance impacts the conversion may have? From what I can tell, the server is having to convert incoming data from the WE8MSWIN1252 character set to AL32UTF8 on inserts/updates and reversing that on selects, right? Are there any views that would let me see how much time the database is spending handling these conversions? I really can't imagine the performance hit is sufficient to justify the conversion, but I'd like to know for sure.

The old csscan used to do this, but have you actually run DMA?  DMA does a scan of your whole database and then lists all the tables that will suffer a lossy conversion.  This is a preview before it actually goes ahead and does the conversion.  You will have a higher chance of lossy data if you go from a multi-byte character set like AL32UTF8 to a single-byte character set like WE8MSWIN1252.  There are many reasons why you would have lossy data, and part of these are due to databases being accessed by multiple applications and tools on multiple operating systems, with locale/NLS_LANG set correctly or not.  If NLS_LANG is set correctly then oracle does single-pass conversions between source and target database.  This depends on the application though.

Oracle does state that there is a performance overhead (maybe 5%) on using a multi-byte character sets, but specifies AL32UTF8 as the default as that covers all character sets world-wide.  I don't know of any view that calculates conversion times; that's part of the RDBMS.

We personally have chosen WE8MSWIN1252 on our databases for two reasons:

  It was going to take too long to convert from US7ASCII to AL32UTF8
  It covers most European character sets and we have separate AL32UTF8 databases in our eastern countries

Hope that helps!


How to properly kill MySQL? I have CentOS 64bit with CPanel installed and I use:

service mysql stop

It just keeps ticking periods and never seems like it stops. In the logs it just posts a lot of:

  130303 17:42:38 [Warning] /usr/sbin/mysqld: Forcing close of thread

In the err.log file, I see lots of these:

  [Warning] /usr/sbin/mysqld: Forcing close of thread

It used to be instant. Any idea why it does that and how to fix?

Right now I have to do killall -9 mysql but is there a better way?

The server is also very very active.

Is this a config issue? Do I have memroy settings too high?

[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
skip-networking
max_connections = 500
max_user_connections = 20
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 64M
read_buffer_size = 12M
sort_buffer_size = 12M
read_rnd_buffer_size = 12M
table_cache = 2048
thread_cache_size = 16K
wait_timeout = 30
connect_timeout = 15
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
tmpdir=/home/mysqltmp
myisam_repair_threads=4
[mysqld_safe]
open_files_limit = 8192
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

The slickest way to shutdown mysql when it does that is simply to run

mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown

Here is why:

The mysql service file (/etc/init.d/mysql) relies on the presence of the socket file. Historically speaking, going way back to MySQL 4.0, the socket file sometimes disappears inexplicably. This hampers a standard service mysql stop from working.

It is not enough to say

mysqladmin -uroot -p -h127.0.0.1 shutdown

because mysqld will route a user coming in as root@127.0.0.1 to root@localhost if TCP/IP is not explicitly enabled. By default, mysqld will choose the least path of resistance and connect root@127.0.0.1 to root@localhost via the socket file. Yet, if there is no socket file, root@localhost will never connect.

Even the MySQL Documentation on mysqladmin says this:

  If you execute mysqladmin shutdown when connecting to a local server using a Unix socket file, mysqladmin waits until the server's process ID file has been removed, to ensure that the server has stopped properly.

That is why it is imperative to enable TCP/IP:

mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown

Back on Sept 30, 2011, I scripted my own version of mysqld_multi called mysqlservice (See my post : Running multiple instances on the same host). It serves as a virtual engine for connecting to mysqld from different ports. You just have to bring your own my.cnf with customized parameters. In that script, I issue shutdowns like this:

stop() {
  ${ECHO} -n $"Stopping ${PROGNAME}"
  ${MYSQLD_STOP}
  ATTEMPTS=0
  STOPPING_MYSQLD=1
  MINUTES_TO_TRY=10
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STOPPING_MYSQLD} -eq 1 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    MYSQLD_HAS_BEEN_SHUTDOWN=`${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "Shutdown complete$"`
    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY}   ] ; then STOPPING_MYSQLD=0 ; fi
    if [ ${MYSQLD_HAS_BEEN_SHUTDOWN} -eq 1 ] ; then STOPPING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STOPPING_MYSQLD} -eq 2 ]
  then
    ${ECHO} "Stopped ${PROGNAME}"
  else
    ${TAIL} -30 ${MYSQL_ERROR_LOG}
  fi
}

But what is ${MYSQLD_STOP} ?

MYSQL_CONN="-uroot -p<rootpassword> -P${MYSQLD_PORT} -h127.0.0.1 --protocol=tcp"
MYSQLD_STOP="${MYSQLADMIN} ${MYSQL_CONN} shutdown"

Please notice I use 127.0.0.1 and an explicit port. That way, I am not relying on a socket file.

I have always used mysqladmin --protocol=tcp shtudown as the proper alternative to shutdowns of mysql if service mysql stop hangs. Doing kill -9 on mysqld and mysqld_safe should the last of the last of the last resorts. (Yes, I said last three times).

Many times, mysqld has deleted mysql.sock without warning. Other people have had this issue as well over the years:

Problem With Shutdown

Original Post : http://forums.freebsd.org/showthread.php?t=28924
Solution using mysqladmin shutdown : http://forums.freebsd.org/showpost.php?s=8d095ca69da3daf2ad4b157c8ad95f1f&p=161653&postcount=10

http://forums.cpanel.net/f354/cant-connect-local-mysql-server-through-socket-var-lib-mysql-mysql-sock-111-a-78444.html

EPILOGUE

The secret is just as I stated: Connect to mysql using mysqladmin via TCP/IP (--protocol=tcp) and issue shutdown. This has to work because the shutdown privilege is in mysql.user for the exclusive purpose of authenticated shutdowns. This has saved my workday a few times when I was able to issue a remote shutdown from my Windows machine when shutting down mysqld on a Linux server.

UPDATE 2013-03-06 22:48 EST

If you are worried about what's going on during the shutdown, there is a way to manipulate  the shutdown time and the way the data is flushed to disk, especially if you have lots of InnoDB data in the Buffer Pool

SUGGESTION #1

If you have a lot of dirty pages, you can lower the innodb_max_dirty_pages_pct to 0:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

Set this about 15-30 minutes before shutdown. This will give mysqld the least possible amount of dirty pages to write to disk.

SUGGESTION #2

By default, innodb_fast_shutdown is 1. There are three values for this option

0 : InnoDB does a slow shutdown, a full purge and an insert buffer merge before shutting down.
1 : InnoDB skips these operations at shutdown, a process known as a fast shutdown.
2 : InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.

Documentation further says this :

  The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.

  Use innodb_fast_shutdown=2 in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.

Defaults for innodb_max_dirty_pages_pct and innodb_fast_shutdown should be just fine in most cases.


getting records for yesterday day starting till ending PostgreSQL Currently my application is running over 2 dbs one is snapshotDB one is evercamDB. In snapshotDB we are saving records now it want to retrieve them , currently am using this query

select camera_id,count(*) as snapshot_count
from snapshots
where snapshot_id in (select snapshot_id
                      from snapshots
                      where created_at = 'now'::date - 1)
group by camera_id

what i want is to get records from snapshots such as am considering today is 2016-01-21 and i want to get all records from yesterday. '2016-01-20 00:00:00' to '2016-01-20 23:59:59'. i really dont know how to bind time with 'now'::date - 1 in such format as i have written.

And i want to place this date and time in (select snapshot_id from snapshots where created_at = 'now'::date - 1) so that i can get records as i want.

Any suggestion or help will be appreciated

You need to cast created_at as a date - that will remove the time.

now() also contains a time, so you need to use current_date

And finally your IN (..) is not needed, you can apply that condition directly in the where clause:

select camera_id, count(*) as snapshot_count
from snapshots
where created_at::date = current_date - 1
group by camera_id;

Note that the above will not use an index on created_at. If the performance is not acceptable and the condition would benefit from using an index, you can use something like this:

select camera_id, count(*) as snapshot_count
from snapshots
where created_at >= date_trunc('day', current_timestamp) - interval '1' day
  and create_at < date_trunc('day', current_timestamp)
group by camera_id;


Use connection string to connect to Azure database from SSMS? I have a connection string for an Azure database. I'm hoping there is a place in SSMS where I can plop in that string and connect to it. Alternatively, if there is a way to do it with a T-SQL query, that would be even better.

Given the connection string:

Data Source=tcp:xxxxxxx.database.windows.net,1433;Initial Catalog=DatabaseName;User ID=zzzzz@xxxxxxx;Password=aaaaaa;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;Application Name=YourAppName;

Server name: xxxxxxx.database.windows.net,1433 "xxxxxxx" represents the server name
Login: zzzzz@xxxxxxx "zzzzz" represents your username, and "xxxxxxx" the server name
Password: aaaaaa represents your real password

As Pedro mentioned you will only be able to connect to an Azure database if your IP is white listed. The Microsoft has a step by step write up on how to do this in their new portal.


Create/Update Test Database duplicated timely from Production database on same server I want to create a test environment database that is refreshed from the production database every month or as needed (they would both be on the same server). It would not just be data refreshing because if I make changes to the schema I would like that to be updated as well. What would be the best way?
I have tried some powershell commands but most of them are for one time duplications  like this. If powershell is the way to go I would like it to look for that database and if it exists I would like it to update.

Here is a very quick TSQL prototype that you could put into a SQL Agent job

USE MASTER
GO

--IMPORTANT - Use COPY_ONLY when backing up production to prevent messing up recovery for production
--Backup production
BACKUP DATABASE zzz TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\zzz.bak'
WITH NOFORMAT
    ,INIT
    ,SKIP
    ,NOREWIND
    ,NOUNLOAD
    ,STATS = 10
    ,COPY_ONLY

--Restore to new database name using the backup from the previous command
RESTORE DATABASE zzzNew
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\zzz.bak'
WITH FILE = 1
    ,MOVE N'zzz' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\zzzNew.mdf'
    ,MOVE N'zzz_LOG' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\zzzNew_LOG.ldf'
    ,NOUNLOAD
    ,REPLACE
    ,STATS = 10
    ,RECOVERY

--optional command to put test database into simple recovery mode, if
--production had been in FULL and you don't need the test database in FULL
--ALTER DATABASE zzzNew SET RECOVERY SIMPLE WITH NO_WAIT


Named instance configured on dynamic default port I was recently asked to take a look at at some SQL Servers for a friend and carry out a few config and security audits.

There were a couple of servers belonging to the same application group which interested me because there were many inconsistencies and generally odd configuration options set.

One thing which attracted my interest: Named instances configured on a dynamic default port (i.e tcp port 1433). SQL Browser was running but clients connected using ,.

My thoughts on this were that this was totally wrong. I mean, why bother creating a named instance only to put it on a default port, and why make that port dynamic?

I contacted the vendor who very assertively told me that their documentation and installation guide stated that instances should be named and configured on dynamic port 1433 ("As you know, this is the default port that connections expect SQL Server to be listening on and changing this would void your client's support contract with us...")

I can't bring myself to fashion a reply at the moment. Part of me feels it's not worth the argument as I'm doing this for a favour really.

To arrive at the point, my question is two-fold:

When would it ever be useful to operate named instances on default ports, and why would it ever make sense for them to be dynamic?

When would it ever be useful to operate named instances on default ports?

I cannot think of one and Microsoft does not recommend doing so.  When you are connecting to a named instance through a firewall, it is recommended that Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall. That should be something other than 1433.  With dynamic port does not guarantee that SQL Server will always listen to the port you specify.  If some other process already using that port dynamic port will be set to zero and another port will be assigned.

  We do not recommend that you have a named instance listen on port
  1433.

What happens when a named instance listening on port 1433?

  If a Named Instance of SQL Server is listening on port 1433, the
  Microsoft OLE DB Provider for SQL Server (Provider=SQLOLEDB) and the
  SQL Server ODBC Driver (Driver={SQL Server}) may be unable to connect
  to the server. This failure to connect occurs when both the instance
  name and the port number from the connection string are omitted. If no
  port number is specified, the drivers validate the server instance
  name. If the server instance name is not the default instance,
  MSSQLSERVER, the connection is unsuccessful. To enable a successful
  connection, you must specify the instance name or the port number in
  the connection string or in a SQL Alias.

If somebody want to use a specific port number for SQL Server named instance they should consult this and pick a number not listed here. 1433 is listed for SQL Server default instance.

why would it ever make sense for them to be dynamic?

I am just making a wild guess. If they install a default instance 1433 will be used by that so dynamic port setting will pick some other port.

Ref:

https://support.microsoft.com/en-us/help/823938/how-to-configure-sql-server-to-listen-on-a-specific-port
https://www.iana.org/assignments/service-names-port-numbers/service-names-port-numbers.xhtml


Moving SQL Server to another computer I am in the process of running a lot of data on a computer that has more power than the one I am currently using. However the data is in a SQL Server instance that I have already created and do not want to change. Is there a way to set up the same SQL Server on the other computer?

Note that the following assumes that you are a SQL Server administrator on both of the instances...

Generally, you can follow these steps:

On the source computer, note the location of your database's files
On the source computer, detach the database from the source computer's SQL Server instance
Move the database files to the proper directories on the target computer
On the target computer's SQL Server instance, attach the database
Repeat for each of the databases you wish to migrate


Find entries, meant to only be available on specific days, being set on incorrect days FYI: I'll be using different table names and column names to the ones actually used in the live system throughout this question, and (obviously) leaving out any tables I don't believe are relevant to the question.

So, this is a tricky one for me, and I'd like to get some help with this issue. I'm probably missing an easy solution here as I might just be a bit burnt out at the moment:

Tables and their relationship to each other:

Table "Cycle": This has "BeginDate" (type DATE), and "NumberOfWeeks" (type TINYINT). NumberOfWeeks will be either 4 or 5, and BeginDate will be some date, from which everything goes forward. For this question, let's say the BeginDate is 2017-10-23, and NumberOfWeeks is 5. This means that there's a cycle from the day set in BeginDate until 2017-11-26 (inclusive), and the next cycle then begins on 2017-11-27 (5 weeks after the initial BeginDate). These cycles continue forward like this indefinitely.

Table "Entity": This has EntityID as BIGINT Primary Key, TypeID as Foreign Key to next table Type, and EntityDate as type DATETIME.

Table "Type": This has TypeID as BIGINT Primary Key.

Table "TypeAvailable": This has TypeAvailID as BIGINT PrimaryKey, TypeID as Foreign Key to Type, Week as TINYINT, and Day as TINYINT.

Each table has other columns in them too, but none which relate to my problem at hand.

How it works:

Each person chooses Entities which are available to them on particular days. The Entity is chosen by the user, and has a particular Type. Their availability is determined by the following:

Say a cycle is 5 weeks long for a particular location. You want to offer entities of a new type (let's call it TypeA) on every day on the first week of each cycle, and on Wednesday on every third week of each cycle. TypeA would be added with, let's say, TypeID 1 to table Type.

TypeA is then also created in TypeAvailable twice. One entry, TypeAvailID 1, has Week set to 1, and Day set to 0 (zero denoting that it's available every day that week). The second entry, TypeAvailID 2, has Week set to 3, and Day set to 3.

Lets say you wanted another type, TypeB, and you wanted this type available on every Monday of every week of the cycle. It would have TypeID 2. Its corresponding TypeAvailable  (with TypeAvailID 3) has a Day set to 1, and Week set to 0 (again, denoting that it's available on every week of the cycle).

The user is looking at a particular day, and is presented with Types which are available to be picked on that day of the cycle. When they select one, it's added as an Entity, with the TypeID of the Type they chose and the EntityDate for the date they chose it on, to the Entity table.

The Problem:

At some point, junk data got introduced to the system. Specifically, some users now have Entities booked in their name on dates that the Type isn't actually set to be available.

For one example, a random user might have an Entity booked in their name for for an EntityDate which corresponds to week 2 of the cycle, on a Friday. However, the Entity is TypeA, which is only available all week on Week 1, and Wednesday on Week 3. I.e. it shouldn't be there in the Entity table, on week 2 of the cycle, on a Friday, at all.

I need to find the IDs of all Entities which are booked in for invalid dates.

I've been at this problem for a little while now and as of yet haven't cracked the problem (as mentioned, I think I'm a bit burnt out at the moment, which probably isn't helping, either). Any help?

I know that "Restore From Backup" is usually an ideal solution, but the issue is that this is a problem which has been building up for many months now, and the business only recently began to notice the issue. As such, it's not really a good option as a solution to this problem, so please don't offer that up as an answer. Thank you :)

There are a number of ways to do this. Here's one suggestion:

First, you need to be able to compare the dates for a given Entity to the available dates for its Type.

The available dates for a Type are given in terms of cycle week and week day. The dates for an Entity are actual calendar dates.

So, to my way of thinking, we should convert the calendar dates to cycle week and week day. You could do the reverse, of course, but you'd have to determine different dates for different cycles, which could get confusing.

Not sure how you're calculating days of the week and weeks; I would look at getting the number of days from Cycle.BeginDate to Entity.EntityDate, using % to get the number of days that is into the current cycle (this assumes that the Cycle.BeginDate is set once and left alone - the modulo may be unnecessary if the begin date is updated to match the current cycle). Then, that number of days should also determine the week in the cycle for that EntityDate ((NumDays + 1 / 7) + 1, assuming NumDays is an integer, should work).

I would, in addition, create a temp table with the expanded weeks and days when each Type is available. That is, replace rows with Week = 0 with 4 (or 5, depending on the Cycle.Weeks) rows (Week = 1, Week = 2, etc.), and then rows with Day = 0 with 5 (or 7, if you can book on weekends) rows, (Day = 1, Day = 2, etc.). This will allow a direct one-to-one match between the week and day values for each EntityDate, and the values from TypeAvailable. It's certainly possible to set up the comparison to handle the Week = 0 and Day = 0 values, but creating the temp table shouldn't be too expensive (depending on the number of rows in TypeAvailable), and leaves thing smuch easier for the next person to follow.

Now that you can directly compare the available weeks and days to the scheduled weeks and days, it's simply a matter of doing so, and reporting the entities without an available match. LEFT OUTER JOIN from the Entity values to the TypeAvailable temp table, and record all rows where there's no match in the temp table.


Oracle 11 rman db restore file corruption First to mention that I came to the need of asking help after furious research I reattempts for about 3 weeks. I checked previous threads in Oracle community but nothing works for me. I am trying to restore Oracle 11.1.0.7. multi HDD cluster production on single hdd locally on amazon ec2.

Restore is done from full back-up copy I downloaded from ftp location (I have no access to production,export and etc.) just have access to the files, i.e. I am not able to change anyhow export procedure.

So here are my steps in brief (I guess that problem might be in some of them):

rman target /
set DBID ...
startup nomount
RESTORE CONTROLFILE FROM 'file destination'
shutdown;
startup mount;

Catalog the backup files to rman, for all AL and BK files:

catalog backuppiece 'C:/BKUP/AL_2851492942_20151016_893271615_PGQJSFHV_1_1';

Rename all redo files to match my local directory structure

rman:crosscheck backup; Run{
SET UNTIL SEQUENCE 50511;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

sqlplus sys/sys as sysdba

shutdown immediate; startup mount;
alter database noarchivelog;

Drop the no longer used log files.
Drop these in state UNUSED left Active and Inactive.
Drop all temp files(they point to other drive letter):

ALTER DATABASE OPEN;
ALTER TABLESPACE mytempspace
ADD TEMPFILE 'c:\oracle............dbf' SIZE 1G REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

My problem is that on my second query (get) to db through my app I receive:

ORA-01578: ORACLE data block corrupted (file # 64, block # 2559928)
ORA-01110: data file 64: 'C:\ORACLE\ORADATA\mydb\mydbLOBDATA2_1.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "mydbUSER.POBJECTSTATE", line 78
ORA-06512: at line 1

What I was able to find with validation and it is LOBSEGMENT - Lob_ObjectState and it logically corrupted.

I tried DBMS_REPAIR and with rman advise failure (which suggest to restore my copy from backup - nothing useful).

Since backup are refreshed every week I try with new ones from the next week, again the same problem.

Is there a possibility that the problem starts from export (how can I check this - I do not have access but i may ask db admin to check anything)?

Your only solution for this problem is to take a backup of the datafiles that are hit by a direct load operation, or prevent force_logging operations totally.
Force_logging operations may seem like a good idea, performance wise but if you care about your data ..... don't do it.

alter database noforce logging;


Need for indexes on foreign keys I'm struggling with indexes, primary keys and foreign keys... And the need of having them all.

If I have two tables, both of them have an integer as a primary key.
The first table references through a FK to the second table's primary key.

On both tables I have a primary key index on the ID column
I created a FK constraint on the table1.ref_field referencing the PK of the second table (table2.id)
and I added an index on table1.ref_field

Is this the best way to organize these indexes, primary and foreign keys?

Your design is good. If you are having a performance problem (which you can't know at design time), you should create an index on column table1.ref_field, in the same order (ASC) as the table2.id column. This will improve performance on joins between those to tables/columns. There is overhead to maintaining any index, so you want to weigh that cost against the benefit of improved performance.

PostgreSQL doesn't automatically create such indexes on foreign key columns that reference other columns, unfortunately, so you have to do it yourself.

Here is a StackOverflow question on the same topic:

Postgres and Indexes on Foreign Keys and Primary Keys

Here is a query to help determine where you might benefit from adding such an index:

Postgresql: Indexes on Foreign Keys


Get actual Job Name from Program Name column of the results while running sp_whoisactive or sp_who2 I am running sp_whoisactive on my Prod and offload the data for analysis.
When I run sp_whoisactive or sp_who2 and if there is any SQL Agent Jobs running it would always display the Program Name like SQLAgent - TSQL JobStep (Job 0x984ECB0C94C14943B787BDE548F77311 : Step 1).
I want  the job name to be displayed there instead of those numbers.
I have to  manually copy that value and run it against select * from msdb..sysjobs where job_id = 0x984ECB0C94C14943B787BDE548F77311
Can any help me with this.
PS: tried to substring only the hexadecimal and convert it to uniqueidentifier and then run it to on the sysjobs tables as a variable but didn't work.
--This is what I tried to do

declare @substring nvarchar(200)
select @substring = (SUBSTRING(@program,30,34))
select @substring

declare @jobname nvarchar(200)
select @jobname = (select name from msdb..sysjobs where job_id = convert(uniqueidentifier,@substring))

select @jobname

--Error message

Msg 8169, Level 16, State 2, Line 22
Conversion failed when converting from a character string to uniqueidentifier.

You can do this easily with sp_WhoIsActive.
EXEC dbo.sp_WhoIsActive
    @get_additional_info = 1,
    @get_task_info = 2;

If you click on the additional_info column, you'll see this:

You don't explicitly need the @get_task_info parameter to retrieve the extra information, it's just a very useful general combination.


Lost connection to MySQL server during query Here is a part of my query:

SELECT *
INTO   OUTFILE 'Target_File_Name' fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ''
FROM   (
              SELECT 'Start time',
                     'Calling no',
                     'Called no',
                     'Calltype',
                     'Cust. name',
                     'Total Dur.',
                     'Call stat'
              UNION
                    (
                           SELECT cdr_startdatetime,
                                  cdr_a_number ,(
                                  CASE cdr_b_number
                                         WHEN 0 THEN ''
                                         ELSE (
                                                CASE cdr_calltypewhen 1 THEN concat('011',cdr_b_number)
                                                       ELSE concat('1',cdr_b_number)
                                                end)
                                  end ),
                                  cdr_calltype_name ,
                                  cdr_customername ,(
                                  CASE
                                         WHEN cdr_totalduration>0 THEN round(cdr_totalduration/60,1)
                                         ELSE 0.00
                                  end),
                                  cdr_callstat
                           FROM   tlb_cdrdetails_4 FORCE INDEX(index_callstatus)
                           WHERE  cdr_startdatetime >= cast('2012-04-01 00:00' AS    datetime)
                           AND    cdr_startdatetime <= cast('2012-04-30 23:59:59' AS datetime)
                           AND    cdr_callstat IN (42,
                                                   52)
                           AND    cdr_customerid IN (123,1000,1001)
                           AND    cdr_carrierid  IN (114,1039,115))) tbl

It fetches 8 million records. It's causing a "lost connection to MySQL during query" error. Is the network speed an issue while downloading huge amount of data?

8 million rows? - network speed will determine how long it takes to download the rows, but as to why you are losing connection, you'd have to dig deeper to see if you have a timeout setting?


How can I force a bushy tree join in Oracle SQL? As far as I know the default join is the deep left join. How can I force a bushy tree join on 4 tables?


why update a table take so long on oracle I have a table A which has 3800+ rows, another table B with 300+ rows.

when I execute a update like this:

update A set some_colum = '2' where another_column not in (select id from B)

it takes 33 seconds to finish. Is that too bad performance?

how to improve?

I add a non-unique index on A.another_column, but it does't help. B.id is primary key.

here is the explain :

Looks like my short answer was not to everyone's taste, so let me show you the long answer

First of all, let's create our lab.

@WestFarmer mentioned a couple of tables

SQL>create table A (
  2  some_column    number,
  3  another_column number
  4  );

Table created.

SQL>create table B (
  2  id number
  3  );

Table created.

Also a mentoned that he create an index over table A (another_column) and that B(id) was the primary key.

SQL>create index idx1 on A (another_column);

Index created.

SQL>alter table B add constraint b_pk primary key (id);

Table altered.

Now let's populate both tables, 3800+ for A and 300+ for B. I fix it to 4000 and 400.

SQL>declare
  2    i number;
  3    j number;
  4  begin
  5    for i in 1..400 loop
  6      for j in 1..10 loop
  7        insert into A values (j, i+100);
  8      end loop;
  9      insert into B values (i);
  10   end loop;
  11 end;
  12 /

PL/SQL procedure successfully completed.

Also I am going to add this extra record, just to show you why I suggest NOT EXIST instead NOT IN

SQL>insert into A values (1,NULL);

1 row created.

SQL>commit;

Commit complete.

As you will see below, ANOTHER_COLUMN include values from 101 to 500 for table A

SQL>select max(another_column), min(another_column) from A

MAX(ANOTHER_COLUMN) MIN(ANOTHER_COLUMN)
------------------- -------------------
                500                 101

And id store values from 1 to 400

SQL>select max(id), min(id) from B;

   MAX(ID)    MIN(ID)
---------- ----------
       400          1

Now the logic said that NOT IN should be the same as NOT EXIST, but if we count the record using not in you will see that it does not include NULL values:

SQL>select count(*) from A where another_column not in (select id from B);

  COUNT(*)
----------
      1000

However if we use not exists it include the null record I inserted.

SQL>select count(*) from A where not exists (select null from B where another_column = id);

  COUNT(*)
----------
      1001

Now the question does not mention nothing about NULL values but performance. So, let's remove the null record

SQL>delete A where another_column is null;

1 row deleted.

SQL>commit;

Commit complete.

SQL>select count(*) from A where another_column not in (select id from B);

  COUNT(*)
----------
      1000

SQL>select count(*) from A where not exists (select null from B where another_column = id);

  COUNT(*)
----------
      1000

Now both options looks similar, right? No matter if we use NOT IN or NOT EXIST.

I will show you why is better option NOT EXISTS from the performance perspective

I have activated autotrace option in order to see the execution plan.

First, let's run NOT IN option

SQL>update A set some_column = '2' where another_column not in (select id from B);

1000 rows updated.

Elapsed: 00:00:00.39

Execution Plan
----------------------------------------------------------
Plan hash value: 3794573283

------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | UPDATE STATEMENT          |      |  4000 |   152K|     4 |
|   1 |  UPDATE                   | A    |       |       |       |
|*  2 |   HASH JOIN RIGHT ANTI SNA|      |  4000 |   152K|     4 |
|   3 |    INDEX FULL SCAN        | B_PK |   400 |  5200 |     1 |
|   4 |    TABLE ACCESS FULL      | A    |  4000 |   101K|     2 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ANOTHER_COLUMN"="ID")

Note
-----
   - cpu costing is off (consider enabling it)
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          7  recursive calls
       1022  db block gets
         44  consistent gets
          0  physical reads
     248832  redo size
        397  bytes sent via SQL*Net to client
        584  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Now, let's run NOT EXIST option

SQL>update A set some_column = '2' where not exists (select null from B where another_column = id);

1000 rows updated.

Elapsed: 00:00:00.33

Execution Plan
----------------------------------------------------------
Plan hash value: 2312933728

------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |  4000 |   152K|     3 |
|   1 |  UPDATE             | A    |       |       |       |
|   2 |   NESTED LOOPS ANTI |      |  4000 |   152K|     3 |
|   3 |    TABLE ACCESS FULL| A    |  4000 |   101K|     2 |
|*  4 |    INDEX UNIQUE SCAN| B_PK |     1 |    13 |     1 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ANOTHER_COLUMN"="ID")

Note
-----
   - cpu costing is off (consider enabling it)
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          7  recursive calls
         15  db block gets
         47  consistent gets
          0  physical reads
      94068  redo size
        394  bytes sent via SQL*Net to client
        601  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Looks pretty similar if we check the Elapsed time, however if you check the execution plan you will see one little difference:

NOT IN use HASH JOIN meanwhile NOT EXISTS use NESTED LOOPS which in this case is a better way to run this query, in fact the cost is different 4 vs 3. probably in this case does not look to much, but imagine millions of records. Big difference!

NOTE: The index over A (another_column) is not usefull no matter what option you use.


Insert multiple rows into a table with only an IDENTITY column I have a table called dbo.Groups defined like this:

CREATE TABLE dbo.Groups
(
  GroupID int NOT NULL IDENTITY (1,1) PRIMARY KEY
);

The table really consists of just the one IDENTITY column.

Sometimes I want to insert into it multiple rows at once and get the generated IDs. (I already have a pre-defined table variable called @output with a single ID column to be used in the OUTPUT clause.)

Now I know how I would proceed if it was a single row:

INSERT INTO
  dbo.GroupID
OUTPUT
  inserted.GroupID INTO @output (ID)
DEFAULT VALUES
;

But I want to be able to insert two or more in one go. The actual number is determined by the number of rows returned by this query:

SELECT
  *
FROM
  dbo.MySource
;

So if the query returns one row, I want to insert one row into dbo.Groups and return the generated GroupID. If it is a hundred rows, then I would expect a hundred rows inserted and a hundred IDs generated and returned at once.

One obvious method is to insert one row at a time in a loop. I would like to avoid that and use a set-based approach instead, something along the lines of

INSERT INTO
  dbo.GroupID
OUTPUT
  inserted.GroupID INTO @output (ID)
SELECT
  ...  -- what?
FROM
  dbo.MySource
;

Is there a way to insert multiple rows into a table with just an IDENTITY column in (preferably) a single statement?

As of writing this, there is no way to insert multiple rows into just an IDENTITY column using an INSERT statement. The DEFAULT VALUES placeholder stands for just one row. And the INSERT ... SELECT syntax has no extension to support the same functionality as the DEFAULT VALUES clause.

Instead, you can use a MERGE statement to achieve the goal:

MERGE INTO
  dbo.Groups AS tgt
USING
  dbo.MySource AS src  -- << use your source row set here
ON
  1 = 0
WHEN NOT MATCHED THEN
  INSERT DEFAULT VALUES
OUTPUT
  INTO @output (ID)
;

The ON 1 = 0 clause basically turns the MERGE into a pure INSERT, because the explicitly false condition causes all the source rows not to be matched and thus trigger the WHEN NOT MATCHED THEN branch of the statement. Now, the WHEN NOT MATCHED THEN branch expects a single-row insert definition, where the familiar DEFAULT VALUES is perfectly valid. As the result, you effectively get an INSERT ... SELECT statement with the functionality of INSERT ... DEFAULT VALUES for an arbitrary number of rows.


How to add slots for Slot table such that there are no clash I have a Slot Table which has following fields :

   CREATE TABLE `Slot` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `starttime` datetime NOT NULL,
      `endtime` datetime NOT NULL,
      `assigned_To_Id` int(10) unsigned DEFAULT NULL,

      PRIMARY KEY (`id`),
      KEY `appointment_assigned_to_id_foreign` (`assigned_To_Id`),
      CONSTRAINT `appointment_assigned_to_id_foreign` FOREIGN KEY (`assigned_To_Id`) REFERENCES `Employee` (`id`),
    )
 ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1

Now, the admins can add slot for a employee(assigned_To_Id) for a particular day by specifying the slots :
e.g. :

7 July Slots :
11:45-12:00
12:00-13:00
and slot time = 15 mins (above time range will be divided in slot time intervals of 15 mins and assigned_To_Id = 1

Now before adding the slots we need to check if there are any clashes for the slots with existing data in db which are assigned to 1.

select count(*) from Slots
where
(
    (starttime >= '2018-07-07 11:45:00' and  ('2018-07-07 11:59:59' between starttime and endtime))
     or
    (starttime Between '2018-07-07 11:45:00' and  '2018-07-07 11:59:59' and endtime <= '2018-07-07 11:59:59')
    or
    ('2018-07-07 11:45:00' Between starttime and endtime  and '2018-07-07 11:59:59' Between starttime and endtime)
)

or
(
    (starttime >= '2018-07-07 12:00:00' and  ('2018-07-07 12:59:59' between starttime and endtime))
     or
    (starttime Between '2018-07-07 12:00:00' and  '2018-07-07 12:59:59' and endtime <= '2018-07-07 12:59:59')
    or
    ('2018-07-07 12:00:00' Between starttime and endtime  and '2018-07-07 12:59:59' Between starttime and endtime)
)
 and assigned_To_Id = 1
 so if there are 'n' slots --> check for each slot

Like for every time slot give by admin we have 3 conditions which we can use to check if there is any time clash or any existing slot

if the query returns a count of 0, that means we are good to add slots
like divide 11:45-12:00 into slots of 15 mins and 12:00-13:00 also in slots of 15 mins.

My question is : How can I run this insert along with the count(*) query such that if two admins try to do similar thing only one of them succeeds.

Can we combine insert statement with condition ?
Is stored procedure a good idea for this one ?

BEGIN;
SELECT ... FOR UPDATE;
check for no overlaps (or whatever)
INSERT (or update) new entry
COMMIT;

In InnoDB, locking the whole table is equivalent to locking every row.  Do I need to explain that that is a lot more costly than locking a single row?

Your SELECT ... FOR UPDATE should pessimistically lock whatever rows you don't want mucked with until the end of the transaction.  Often it is a single row (lock one row ... update that row).  Or, it could involve locking several rows before deciding which row(s), if any, will actually be updated.

You should minimize the number of rows locked -- for speed of the SELECT, for speed of the ROLLBACK/COMMIT, and for minimizing interference with other queries.

Unless you have hundreds of UPDATEs happening per second, locking too many rows is not a big deal -- after all, your transaction should be designed to finished in only a few milliseconds, not the default `innodb_lock_wait_timeout=50 (seconds).


Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output.  Some sample output of SHOW ENGINE INNODB STATUS from our DB:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-09-29 17:00:30 7f9eb7410700
*** (1) TRANSACTION:
TRANSACTION 2842247584, ACTIVE 0 sec, thread declared inside InnoDB 4998
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 26 row lock(s)
MySQL thread id 69517881, OS thread handle 0x7f9ee07bd700, query id 11929163563 cfsender01.cf.intela.local 192.168.20.116 cfsender01 updating
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
...

Notice that there is no SQL statement listed in transaction (1).

Does anyone know why this might happen, or what the lack of a sql statement in the show engine innodb status would indicate about the transaction?

FWIW, transaction (1) was chosen as the victim by MySQL and rolled back.

Also, we're running MySQL Server version: 5.6.19-67.0-log Percona Server (GPL), Release 67.0, Revision 618.

What language is the client written in?  You should test for errors after every statement (including the COMMIT) in every transaction.  That is the way to locate the problem.  SHOW ENGINE InnoDB STATUS should not, as you see, be depended upon.  More significantly, it won't necessarily catch all deadlocks.


How to group a view by the tables from which it gathers its data? I have four different tables, let's say "LAMPS", "CARS", "DRONES" and "OVENS". Each of these tables has a column called "STATUS", which has a value of either 'ON' or 'OFF'. I want to create a view that show the data from all these four tables.

I then want to filter this view by the "STATUS" column. I am interested in only seeing the entries with the 'OFF' value.

Finally, I want to group the results by the table from which it came. I'm thinking of creating a new column in the view that would get the original_table_name for each of the rows and group by that value.

+------------+------------+-----------------+--------+
| TABLE_NAME |  SOMECOL1  |    SOMECOL2     | STATUS |
+------------+------------+-----------------+--------+
| LAMPS      | somevalue1 | someothervalue1 | OFF    |
| CARS       | somevalue2 | someothervalue2 | OFF    |
+------------+------------+-----------------+--------+

This is what I have so far:

CREATE VIEW my_view
  AS SELECT * FROM LAMPS l, CARS c, DRONES d, OVENS s WHERE l.STATUS = 'OFF' OR c.STATUS = 'OFF' OR d.STATUS = 'OFF' OR s.STATUS = 'OFF';

Without modifying any of the original tables, how can I group a multi-table view by the tables from which it gathers its results?

Maybe you need

CREATE VIEW my_view
AS
SELECT *, 'LAMPS' AS TABLE_NAME FROM LAMPS WHERE STATUS = 'OFF'
UNION ALL
SELECT *, 'CARS'                FROM CARS WHERE STATUS = 'OFF'
UNION ALL
SELECT *, 'DRONES'              FROM DRONES WHERE STATUS = 'OFF'
UNION ALL
SELECT *, 'OVENS'               FROM OVENS WHERE STATUS = 'OFF';

?


Maximum Partitioning PostgreSQL I have 400,000 records I need to partition. The records will increase 100,000 per year and those new records might need to have 1000 new partitions added.

Here are some of my concerns:

How many partitions are too many
Is having small partitions bad (could have less than 150 records per partition)
Large partitions will be 10,000 or more records
partitions are created by field, not a date
new records could be added daily to the larger partitions but the smaller partitions might be monthly
possibly need to separate partitioned data legally (some cases but not all)

Example:

Let's say 10,000 out of 100,000 will need to be broken into partitions of 100 records, so 1000 partitions will be created and 9 additional partitions will be created with 10,000 records each, for a total of 1009 partitions.

Questions:

is this approach going to work
is this the best approach, if not what are some other ideas

Notes:

Most records will fall into the larger partitions

You need one partition for that many records. Not 1000. Certainly not 1000/year. This is not a problem that requires partitioning. It looks to me like you've decided on the solution before fully stating and analysing the problem.

Reading between the lines, it sounds like you're implementing a mulit-tenant system and have already decided that partitioning is the way to do that. Right?

If so: wrong approach. Start with a single table. Partition if/when you need to for performance and maintenance reasons. With a DB of this scale it is very unlikely that you will ever need to, it's tiny.

  How many partitions are too many

Because the constraint exclusion code isn't super smart, try to stick to low partition counts. I prefer tens or hundreds at most.

  Is having small partitions bad (could have less than 150 records per partition)

Yes, it's very wasteful in terms of planning and execution time.

  possibly need to separate partitioned data legally (some cases but not all)

What's the difference between a partition and a single table with a composite key? I've never seen a legal or regulatory code that goes down to the level of actually specifying database structure, other than maybe PCI, and not in this way.

Details please.

  is this the best approach, if not what are some other ideas

Use one table, a composite key, and some composite indexes. If useful/necessary, use partial indexes for sub-ranges.


How to suppress specific warnings in mysql error log when ignoring a table to replicate Is there a way to make the mysql error log less chatty with certain warnings? I just threw in the replicate-ignore-table option in my.cnf so our health check table doesn't get replicated. However now I'm getting a couple warnings every minute telling me something that I specifically want to happen... and it's annoying, not to mention that the log will grow at a much faster rate now.

I thought adding slave-skip-errors=1237 would fix it, but it's not an actual error that's stopping replication.

Any ideas?

2015-07-16 21:51:02 31210 [Warning] Error log throttle: 4 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:51:02 31210 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237
2015-07-16 21:53:01 31210 [Warning] Error log throttle: 3 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:53:01 31210 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237
2015-07-16 21:54:01 31210 [Warning] Error log throttle: 2 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:54:01 31210 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237
2015-07-16 21:55:01 31210 [Warning] Error log throttle: 3 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:55:01 31210 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237
2015-07-16 21:56:02 31210 [Warning] Error log throttle: 2 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:56:02 31210 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237


Optimizing key lookup in subquery with UNION ALL I am trying to optimize following query (little bit more complex actually, but this is important part):

SELECT Id, StatusDate, [...Lot Of Columns...]
FROM ( (
        SELECT Id, StatusDate, [...Lot Of Columns...]
        FROM Results_201505
        WHERE A = 0, B = 1, C = 3
    ) UNION ALL (
        SELECT Id, StatusDate, [...Lot Of Columns...]
        FROM Results_201504
        WHERE A = 0, B = 1, C = 3
    ) UNION ALL (
        SELECT Id, StatusDate, [...Lot Of Columns...]
        FROM Results_201503
        WHERE A = 0, B = 1, C = 3
    )
) as result
ORDER BY StatusDate DESC
OFFSET xxx ROWS
FETCH NEXT 25 ROWS ONLY

On every table I've created index covering 'where' part:

CREATE NONCLUSTERED INDEX ix_asdf ON Results_asdf (StatusDate DESC)
INCLUDE (A, B, C)

I can't use index A, B, C, StatusDate, because the WHERE conditions are changing depending on filters that are user selected. It can be WHERE A=, B=, C=, but also WHERE B=, D= or WHERE C= or without WHERE at all).

My idea is that SQL Server will perform fast index scan with my index, and delay key lookup until last possible moment (fetching top 25 rows) - this seems to me like most reasonable thing to do.

And indeed, if I comment out [... Lot of Columns...] everything goes as planned - index is used, no key lookup is necessary and everything is very fast.

But in full form, SQL Server (according to execution plan, as shown by SSMS) tries to do key lookup for each row, before OFFSET ... FETCH 25.

The reason is obvious (I believe) - Sql Server realizes that UNION ALL concatenates multiple tables, so after UNION it is too late to do key lookup (because after UNION SQL Server knows only ID, but doesn't know which results table it came from). ID is globally unique, but query optimizer probably doesn't know that.

Of course I'm able to work this around with something like:

SELECT Id, StatusDate, [ some way to select columns from appropiate table depending on result.tbl ]
FROM (
    (
        SELECT Id, StatusDate, 'Results_201505' as tbl
        FROM Results_201505
        WHERE A = 0, B = 1, C = 3
    ) UNION ALL (
        SELECT Id, StatusDate, 'Results_201504' as tbl
        FROM Results_201504
        WHERE A = 0, B = 1, C = 3
    ) UNION ALL (
        SELECT Id, StatusDate, 'Results_201503' as tbl
        FROM Results_201503
        WHERE A = 0, B = 1, C = 3
    )
) as result
ORDER BY StatusDate DESC
OFFSET xxx ROWS
FETCH NEXT 25 ROWS ONLY

But my question is - is there a better way? Some kind of hint to optimizer would be perfect, but if it's not possible - what's most elegant/fastest/best way to handle this query?

Have you tried this?

SELECT Id, StatusDate, [ some way to select columns from appropiate table depending on result.tbl ]
FROM (
        SELECT Id, StatusDate, 'Results_201505' as tbl
        FROM Results_201505
        WHERE A = 0, B = 1, C = 3
    UNION ALL
        SELECT Id, StatusDate, 'Results_201504' as tbl
        FROM Results_201504
        WHERE A = 0, B = 1, C = 3
    UNION ALL
        SELECT Id, StatusDate, 'Results_201503' as tbl
        FROM Results_201503
        WHERE A = 0, B = 1, C = 3
    ORDER BY StatusDate DESC
    OFFSET xxx ROWS
    FETCH NEXT 25 ROWS ONLY
) AS result ;


Postgresql: set default psql client encoding When I connect to a Postgresql DB using psql, I often get these messages:

=> SELECT * FROM question_view ;
ERROR:  character with byte sequence 0xd7 0x9e in encoding "UTF8" has no equivalent in encoding "LATIN1"

Following this SO answer, I understand that I should change the client encoding accordingly:

SET client_encoding = 'UTF8';

Changing the client_encoding every time I connect to the DB is cumbersome. Is there a way to permanently configure this setting, either in the .pgpass file or anywhere else?

Is there a way to permanently configure this setting, either in the .pgpass file or anywhere else

Yes there is: it's ~/.psqlrc (or %APPDATA%\postgresql\psqlrc.conf in Windows)

See the manual for details: http://www.postgresql.org/docs/current/static/app-psql.html#AEN88713


What is a good way to store a large number of columns? I have a problem deciding how to store this data in my database. Any suggestions on the best way to do it? I don't know a hell of a lot about databases, I might add.

I have data coming in formatted like so, but rather than 4, the number of columns is approx 240, so each date has 240 unique values associated with it:

Date/Time 200,00 202,50 205,00
2010.11.12  13:34:00  45,8214 43,8512  41,5369
2010.11.12  13:35:00  461,9364  454,2612  435,5222

Also, rows are associated with DataSites.

My first thought was to have a table like so:
DataID (pk), DataSiteID, ParameterID, Date, Value, with an index on DataSite, Parameter and Date. The ParameterID refers to another table that stores the input column headers (200,00 202,50 205,00 ...).

My second thought was simply to have a table with all 240-odd columns. I have come up with a few other ways, but they are pretty unsatisfactory as well.

The problem I have with my first solution (not such a huge problem, but I don't like it), is that the Date and DataSiteID are going to be repeated for all 240 values in that input row, so it uses quite a bit of extra space.

There will be about 40gb of data a year coming in (in the above text format), and the data will be searched by DataSite, Parameter and Date. The amount of data coming in will most likely quadruple in a year or so.

Any good ideas?
Thanks, James

edit: This is time series data, with the columns being measurements at different wavelengths. Data will want to be analysed within a relatively narrow range of wavelengths. There could also be extra wavelengths added in at some point in the future.

edit: Thanks for the answers guys, I really appreciate it :) I think I can probably find time to run some experiments with 500gb or so of test data. I'll post back with any conclusions ;)

You could make a case either way, but if the data is going to be used for analysis and you often want to see multiple columns from that data at the same time, go with the wide table. Make sure you know your databases column quantity and row size limits. Make sure you get the datatypes right. If many of the columns are null, SQL Server allows you to optimize the table for that. You could also consider using a NOSQL (Not Only SQL) solution for analysis of this type of data.

If this data is going to be less for analysis, you might want to normalize it as stated in your question.


SQL Server Maintenance plan that goes to next step when the condition passes I am trying to create a maintenance plan in SQL Server. What I want to do is I have an Execute T-SQL Task and inside the task I have condition below:

Use master
go
DECLARE @dbname varchar(1000)
set @dbname = 'myTEstDB'
If sys.fn_hadr_is_primary_replica ( @dbname ) = 1
 BEGIN
 END

If the If condition brings 1 I want the maintenance plan to go to next step.
Can we do this? Please let me know if there is a way to implement it like this.

As mentioned in the comments you got to refer Ola Hallengren's  maintenance solution.

However, following conditional script might be helpful for any other purpose that need consider only primary replica:

Declare @is_DB_HADREnabled bit;
Declare @isPrimaryReplica bit;
Declare @Databasename varchar (120) = DB_NAME();

select @is_DB_HADREnabled = IIF(group_database_id IS NULL, 0,1) from sys.databases where [name] = @DatabaseName;

        BEGIN
            IF EXISTS (select db.name
                        from sys.dm_hadr_database_replica_states as hadr
                            join sys.databases as db on hadr.group_database_id = db.group_database_id
                        where is_local = 1 and is_primary_replica = 1 and db.name = @DatabaseName
                        )
                SET @isPrimaryReplica = 1
            ELSE
                SET @isPrimaryReplica = 0
        END

    IF @is_DB_HADREnabled = 0 or @isPrimaryReplica = 1

        BEGIN
            Print 'This block able to run on only Primary replica when a database part of AG. Also, run for the databases which are not part of AG'
        END

        ELSE
        PRINT 'This has to be either Primary replica of DATABASE or DATABASE must not be part of any AG'


Is using CLR for regular expressions safer than using external scripts? Problem
The main problem we need to use regular expression on MS SQL Server 2019, with the capability
of at least the level on the POSIX Regular expression.
Possible solutions
This Q/A from stackoverflow rightly concludes that if you query must rely on regular expressions you shuould use CLR. This Readgate article elaborates this approach more. So one of my colleagues and I proposed this solution, but my other colleague categorically stated that using CLR here would be a huge risk to security and stability, and using external script (Python or R) is more secure.
This seems to be dubious claim, since the user code in the CLR can be managed, so perhaps the opposite is true, but I was not able to persuade my colleague.
In my other question which I wrote in my desperation because I was forced to use external script and still produce a blazing fast query. SQLpro user states in his comment that:

Using Python or R can be worst in terms of security rather than using CLR!

Which I tend to believe.
Questions
So I have two questions:

Which Regexp solution is more secure external script or CLR based (as described here)? And why?

I also proposed to run the python code on the same Windows Server (must be the same server, because of a policy) but with python intrepeter installed on the OS. Because the results are exported into CSV files either way and stored in the SQL Server. So then I would able to use Python's multiprocessing module to achieve the right performance. The answer was the same that running Python inside SQL Server is more secure than in a outside application. Which is also a questionable claim.

my other colleague categorically stated that using CLR here would be a huge risk to security and stability

Well, your colleague is categorically wrong (unless they can offer up actual proof of such claims).
Security
Ever since SQLCLR was introduced in SQL Server 2005 people have been saying that it is "unsafe". However, I have yet to see anyone actually prove that it is indeed unsafe. The only supposed "proof" I have seen is someone loading an assembly that writes a file to disk with the claim that since it can write to disk, it is a security vulnerability. But, that's a false claim due to:

the person already had permission to create the assembly
the assembly already had permission to access external resources via TRUSTWORTHY being enabled or a signature / certificate being used.

That's not proof of gaining elevated permissions if you already had elevated permissions in order to a) create and b) execute the code required to make it look like you were gaining elevated permissions.
SQLCLR is not inherently insecure. Sure, it can be use incorrectly to accidentally allow someone to gain elevated permissions, etc, but by itself it is safe and secure. And, it's easy enough to introduce a security vulnerability simply by using EXECUTE AS 'dbo' in a stored procedure or function, and that's pure T-SQL.
Here are several articles that I wrote on the topic of SQLCLR security:

Stairway to SQLCLR Level 3: Security (General and SAFE Assemblies)
Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies)
SQLCLR vs SQL Server 2017, Part 8: Is SQLCLR Deprecated in Favor of Python or R (sp_execute_external_script)?
SQLCLR vs SQL Server 2017, Part 9: Does PERMISSION_SET Still Matter, or is Everything Now UNSAFE?

That being said, within the context of using regular expressions, both SQLCLR and External Scripts are "secure" (assuming that you are not allowing ad hoc access to users to submit whatever random Python code they desire).
Usability
Here is a major difference: External Scripts execute via a stored procedure. Meaning, you can perform your RegEx on a single value. If it's possible to integrate the RegEx function into the SELECT and/or WHERE clauses (I know you can pass in a query and return a result set), then it's at best clunky and certainly not easy to maintain. Whereas with SQLCLR, you can create scalar functions and table-valued functions that are quite easy to integrate into queries for proper set-based solutions (and remember: properly coded SQLCLR scalar functions that don't do any data access can participate in parallel plans).
If you want RegEx functions without having to compile anything, there are quite a few available in the Free version of SQL# (SQLsharp), a SQLCLR library that I wrote.

For more info on working with SQLCLR in general, please visit my site: SQLCLR Info


How do I query to pull all data in row based MIN and MAX service date? I'm very new to Oracle SQL especially in the Pentaho report builder.  It lets you do some things and block others.  PL/SQL is not available so I must do this in a query.

I need to be able to pull all the data in each row for a client for their MIN(service_date) and their MAX(service_date).  Below are the table data and the result I'd like to get.

Table Data

client_id | service_date  | other_stuff
---------------------------------------
        1 | 2/24/2010     | Bob
        1 | 3/23/2010     | Jane
        1 | 4/23/2010     | Sam
        2 | 1/1/2000      | Julie
        2 | 2/2/2000      | Tina
        3 | 3/28/2005     | D'Shaun
        3 | 4/27/2005     | Leisha
        3 | 5/29/2005     | Tonay

Result data

client_id | service_date | other_stuff
--------------------------------------
        1 | 2/24/2010     | Bob
        1 | 4/23/2010     | Sam
        2 | 1/1/2000      | Julie
        2 | 2/2/2000      | Tina
        3 | 3/28/2005     | D'Shaun
        3 | 5/29/2005     | Tonay

There might be other better ways but does following work for you?

select client_id
      ,service_date
      ,other_stuff
  from (
        select client_id
              ,service_date
              ,other_stuff
              ,row_number() over (partition by client_id order by service_date) rn_min
              ,row_number() over (partition by client_id order by service_date desc) rn_max
          from tbl
       )
 where (rn_min = 1 OR rn_max = 1) ;


Data Pipeline load bulk tables in one time I am using AWS Data Pipeline for copying my RDS MySQL Database to Redshift. I need to create separate pipeline for each table and each pipeline create new EC2 instance. (Process takes time).

Problem

Is there any way to load whole database table into Redshift with single Data Pipeline . so i can schedule it and sync all my data daily with single pipeline.

NOTE: I have almost 250+ tables in DB that needs to be sync with redshift db (Almost daily). Or suggest any other better way for that purpose.

I did this... i made a shell script and include all my tables there into a loop and store it in S3 and in pipeline ...i mentioned the path to script ...at scheduled time...the script runs and sync the data daily to Redshift. :) :Y


Little trouble with MySQL LEFT JOIN I ask your help on a subject that I do not really master.

I have two tables:
tb_user (NIC_Handle, Pseudo)
tb_user_messages_recap (Auteur, Intervenant)

I have to retrieve the NIC_Handle of each 'Pseudo' that corresponds to either 'Auteur' or 'Intervenant'

Can you help me please ?

<?php try{
$MyPDO=$pdo->query("
SELECT
tb_user_messages_recap.Auteur,
tb_user_messages_recap.Intervenant,
tb_user_messages_recap.Numero,
tb_user_messages_recap.Sujet,
tb_user_messages_recap.Creation,
tb_user_messages_recap.Edition,
tb_user_messages_recap.Etat,
tb_user.NIC_Handle,
tb_user.Pseudo

FROM tb_user_messages_recap

LEFT JOIN tb_user

ON tb_user_messages_recap.Auteur = tb_user.Pseudo

WHERE tb_user_messages_recap.Auteur='".$_SESSION_PSEUDO."' OR
      tb_user_messages_recap.Intervenant='".$_SESSION_PSEUDO."'

ORDER BY Edition DESC
");
    while($data=$MyPDO->fetch(PDO::FETCH_ASSOC))
    {
?>

<?php echo $data["NIC_Handle"]." - ".$data["Auteur"]; ?>
<?php echo $data["NIC_Handle"]." - ".$data["Intervenant"]; ?>
<?php echo $data["NIC_Handle"]." - ".$data["Intervenant"]; ?>

<?php }}catch(PDOException $e){echo "
".$e->getMessage()."
";} ?>

Thank you for your help,

Cordialy

Change the join condition to:

ON tb_user_messages_recap.Auteur = tb_user.Pseudo OR
   tb_user_messages_recap.Intervenant= tb_user.Pseudo

But really why not:

SELECT NIC_Handle
FROM tb_user
WHERE Pseudo =$_SESSION_PSEUDO

I could be wrong. Question is a bit ambiguous.


Can't deploy SSIS package - The certificate chain was issued by an authority that is not trusted I have an Azure Windows Server in a managed Domain (AD Domain Services) running SQL Server Enterprise.

I am trying to simply deploy a local (laptop) SSIS project to the remote SSIS server. When the deployment dialog appears and I enter the IP address of the VM, I get the dreaded:

  A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

Importantly/Potentially related, I am using "Active Directory Password Authentication" and passing the credentials of a domain user (on the remote AD domain) which is an admin on the VM.

I tried also using SQL Authentication, but you cannot deploy a package using SQL Authentication (SSIS requirements).

I have searched long a wide for a solution to this, and a lot of hints point to changing a connection string, which doesn't apply to me since I'm using the VS2017 SSDT deployment wizard (no advanced options either).

Try this:

Open Windows CMD.EXE and type and enter the following and restart your computer:

netsh winsock reset

Reference:
https://stackoverflow.com/questions/21681762/how-do-i-fix-an-error-connecting-to-sql-server-ssl-provider-error-0-the-mes


Are 'per second' Performance Counters measured over a certain period of time? This may be a very silly question, but when I'm looking at the current value of a 'per second' counter in PerfMon, for example SQL Statistics\Batch Requests/sec - how is that value actually measured?

Let's say I use PowerShell's Get-Counter method to return a result for a specific server, as follows:

(Get-Counter -ComputerName $server -counter $countername -SampleInterval 1 -MaxSamples 1).CounterSamples.CookedValue

And this returns: 32

How can that value actually be contextualized? Is it an "average of 32 batch requests per second in the last x minutes"? Or is it "in the last second there were 32 batch requests"

It is like it says, per second. Great explanation of what constitutes a 'batch' here: https://www.brentozar.com/archive/2017/02/what-is-batch-requests-per-second/


OPTION FORCE ORDER improves performance until rows are deleted I have a somewhat complex SQL Server 2008 query (about 200 lines of fairly dense SQL) that wasn't performing as I needed it. Over time, performance dropped from about .5 seconds to about 2 seconds.

Taking a look at the execution plan, it was pretty obvious that by reordering the joins, performance could be improved. I did, and it did... down to about .3 seconds. Now the query has the "OPTION FORCE ORDER" hint, and life is good.

Along comes me today, cleaning up the database. I archive about 20% of the rows, taking no action in the relevant database except deleting rows... the execution plan gets TOTALLY hosed. It completely misjudges how many rows certain subtrees will return, and (for example) replaces a:

<Hash>

with

<NestedLoops Optimized='false' WithUnorderedPrefetch='true'>

Now the query time spikes from about .3s to about 18s. (!) Just because I deleted rows. If I remove the query hint I'm back to about 2s query time. Better, but worse.

I've reproduced the issue after restoring the database to multiple locations and servers. Simply deleting about 20% of rows from each table always causes this issue.

Is this normal for a forced join order to make the query estimates to be
completely inaccurate (and thus query times unpredictable)?
Should I just expect that I'll have to
either accept sub-optimal query performance, or watch it like a hawk
and frequently manually edit query hints? Or maybe hint every join as well?
.3s to 2s is a big hit to take.
Is it obvious why the
optimizer blew up after deleting rows? For example, "yes, it took a sample
scan, and because I archived most of the rows earlier in the data
history the sample yielded sparse results, so it underestimated the need for
a sorted hash operation"?

If you'd like to see execution plans, please suggest a location I can post them. Otherwise, I've sampled the most stunning bit. Here's the fundamental mis-estimate, numbers in parens are (estimated:actual) rows.

                             /  Clustered Index Scan (908:7229)
Nested Loops (Inner Join) --<
                             \  NonClustered Index Seek (1:7229)

Note the inner loop is expected to scan 908 rows, but instead scans 52,258,441. If it had been accurate, this branch would have ran about 2ms, rather than 12sec. Before deleting the rows, this inner join estimate was only off by a total factor of 2, and was performed as a hash match on two clustered indexes.

Is this normal for a forced join order to make the query estimates to
  be completely inaccurate (and thus query times unpredictable)?

The use of FORCE ORDER isn't making estimates inaccurate, the deletion of rows did. Forcing an update of statistics on the table may improve the estimation accuracy.

  Should I just expect that I'll have to either accept sub-optimal query
  performance, or watch it like a hawk and frequently manually edit
  query hints? Or maybe hint every join as well? .3s to 2s is a big hit
  to take.

Preferable would be to ensure the optimiser is given the information it needs to generate the best plan, without using the FORCE ORDER hint. By doing so, it should cope better with  changes to the underlying data distribution without requiring manual intervention. That said, if the nature of the data is such that cardinality could vary significantly hour by hour or day by hour, consider using a plan guide to ensure the plan is fixed.

  Is it obvious why the optimizer blew up after deleting rows? For
  example, "yes, it took a sample scan, and because I archived most of
  the rows earlier in the data history the sample yielded sparse
  results, so it underestimated the need for a sorted hash operation"?

You didn't mention the row counts in the problem tables but it's likely that the deletions either:

didn't remove enough rows to to trigger a statistics update. This should occur when 20% of rows have been modified but there is the option to use trace flag 2371 to enable a dynamic threshold.
did trigger a statistics update but the sample gathered wasn't representative. Correct this by running a manual update WITH FULLSCAN.

You could also be running into good old fashioned parameter sniffing problems, for which there are myriad options to work around. WITH RECOMPILE might be an expensive option to specify with a query this large but it's worth investigating at both procedure and statement level.


SQL Server 2014 Batch Transaction Log Management I have recently taken over a system which is going live in March on SQL Server 2014 after running on Sybase for years.  The system is 24 x 7 OLTP and heavy batch at night.  Prior years during batch in Sybase the transaction logs would fill up freezing the system.  I have not managed a system such as this and was looking for some advice.

I would propose sizing the transaction log appropriately and running log backups every 15 minutes.  Does this sound as though it would be the best procedure?  Also, how should I best size the log?  Should it be 2 x the largest transaction size?  What if the size of the transaction varies?

Prior years during batch in Sybase the transaction logs would fill up freezing the system.

Sybase before ASE 15 did not have the concept of autogrowth. SQL server has autogrowth and there are ways wherein you can presize your transaction log and choose a sensible autogrowth value.

Refer to : 8 Steps to better Transaction Log throughput and Manage the Size of the Transaction Log File

Also, Why Does the Transaction Log Keep Growing or Run Out of Space? has excellent answers detailing majority of techniques and scenarios.

  I would propose sizing the transaction log appropriately and running log backups every 15 minutes.

Initial sizing would be a trial and error method, but you can use my script to detect autogrowth events and then adjust your log size.

Running log backups will allow you to reuse your existing log file in FULL and bulk logged recovery modes.

A checkpoint (manual or automatic) will clear the log file in simple recovery.

  heavy batch at night.

You should refer to data loading performance guide (Referenced in my answer) to optimize your nightly load batch. Also, you should check Recommended updates and configuration options for SQL Server 2012 and later versions with high-performance workloads


What is the relation between Tablespaces, .frm & .ibd files and database pages in MySQL? I looked in the documentation but there was not a clear answer regarding their relation, can someone give an real life example of their relation in MySQL?

Database pages are smallest read write unit in databases and is usually 8KB, but:

What is the relation between MySQL pages and .frm & .ibd files?
Is .frm files consists of many pages coming together?
If so then does the .frm files have any type of info on the pages that are inside them?
Like the location of each page and stuff like that?
Where can i read more about this?
And what about tablespaces?
Is every .frm file a tablespace?

I read their documentation for hours but it got me even more confused

(I'm talking about the InnoDB storage engine BTW)

What is the relation between MySQL pages and .frm & .ibd files? Is .frm files consists of many pages coming together?

.frm used to contain metadata about tables- that means, the structure (think of the output of SHOW CREATE TABLE- columns and data types) in its own format. Now a days (Since MySQL 8.0) that data is stored on InnoDB tables on the mysql database to allow transactional schema changes. .ibd contained mostly data (not that innodb also had its own internal data dictionary, supporting MySQL one, but different), organized in namespaces>extents>pages, and inside pages, records "rows".

There is other stuff (structures) related to InnoDB for it to work, but the main take away is that there are 2 different planes, the SQL layer, which takes care of connection handling, query parsing and optimization for execution, and the Engine layer (InnoDB, MyISAM, MyRocks, ...) which decides how data is actually stored. Both layers communicate with common API called Handler API, which contains very basic row-level actions like "Search the first record in Primary Key order" or "get the following record".

.frms were an SQL-level functionality (so found for every engine), .ibds were an InnoDB-only structure. Because InnoDB also had its own internal dictionary, aside from the MySQL one, if you deleted a .frm improperly (from the filesystem), InnoDB got confused as the table was still there from its point of view.

  If so then does the .frm files have any type of info on the pages that are inside them? Like the location of each page and stuff like that?

No, the MySQL data dictionary, be it on .frm or in its new form only contains basic SQL-level information (what is the table definition, which columns are there, which types, etc.). InnoDB information is stored on InnoDB related files- ibdata1, .ibds, transactiona logs. When InnoDB engine receives, through the Handler API the need to read a particular row, it does that independently of the SQL later- data may be on cache (buffer pool, or it may be on disk only, or it may be on disk but not available because it is hidden for the given session). To find a particular page, it implements a series of memory and disk structures (headers on files, tree-like structures, lookup tables, hashes that will let InnoDB access the right page). This is very little space to give an proper overview of InnoDB internals, but think of it like a very complex set of pointers and references that allow accessing quickly data while maintaining consistency to disk and access control.

  Where can i read more about this?

I already shared with you the InnoDB: A journey to the core which describes more in detail how it works. Aside from that, probably the best way is the source code itself- MySQL source code is available and you can check the details of how InnoDB implements its own data structures. This is not simple- and almost impossible to summarize here. For a very simplistic approach, each table creates a B+tree structure organized in 16K pages ordered by the primary key (there is always a primary key, even if one has not been explicitly created). Other indexes are implemented as separate tree structures that record the value of the PK of the row they index. If you just want a simplified approach about the logical view of this, without the technical details, you can read my slides on Query Optimization for MySQL/InnoDB.

  And what about tablespaces? Is every .frm file a tablespace?

No, as we said, .frm is a MySQL table structure. However, InnoDB usually creates a different tablespace per table (there are exceptions, like partitioned tables), and each tablespace normally is on a separate file (.ibd). However, this is not necessarily true, there could be several tables on the tablespace0 (it's complicated :-)).


Mysql performance for a nullable unique id column in a large table that references a record in another table We have a large table of contacts (8 million records) that we promote to with mailings. When these people register we store them in a different members table. But we want to go back and update the contacts table each day with the members table ID for each conversion. I added a member_id column to the contacts table but I can't really use a member_id foreign key on that column as most will have a null value.

I'm trying to determine what the best type of index might be used on the member_id column to speed up queries on those people. Currently i have the column nullable with a unique index but queries on those people are taking several minutes which isn't very performant. Wondering if some more experienced people out there have dealt with this type of issue?

Perhaps a cross-reference table would be the most prudent way to go.  One that has the ID from contacts, and the ID from members.  This way you know who has converted, and can keep the table as small as necessary.

As an example (I use SQL Server, not MySQL):

CREATE TABLE Members
(
    MemberID INT
    , MemberData varchar(255)
);

CREATE TABLE Contacts
(
    ContactID INT
    , ContactDate datetime
    , ContactData varchar(255)
);

CREATE TABLE MembersContactsXRef
(
    MemberID INT
    , ContactID INT
);

I would insert rows into MembersContactsXRef each time a conversion occurred for a given member.  Both fields in MembersContactsXRef could be foreign keys.


SSAS Date Dimension Relationship In Data Source View When a fact table is at a higher granularity to the date dimension (e.g. monthly) how should this be represented as a relationship in the data source view?

With a date dimension containing columns DateKey and MonthDateKey and fact table containing MonthDateKey should the relationship be:

Fact.MonthDateKey -> Date.DateKey or Fact.MonthDateKey -> Date.MonthDateKey

What are the implications if this is set incorrectly in the data source view?

This relation is not necessarily expressed in the data source view, but in the dimension usage tab, but you can define it in the dsv if you want.

The implications of setting it incorrectly in the DSV are that the dimension usage tab will propose the wrong default and you have to change it manually, but whatever you put in the dimension usage tab on your measure group is what is actually defining the relation.

Without knowing your exact data model I would think your relation has to be Fact.MonthDateKey -> Date.MonthDateKey in your case.

There is an example of how this is done in the AdventureWorks Analysis services project where the date dimension is linked to the Sales Target measure group on Calendar Quarter:

You basically just pick another Granularity Attribute from your dimension and link it to the correct column in your fact table like this:

The dsv relation for this setup looks like this (which would be monthdatekey in your scenario)

The caveat is displayed as a warning when you don't select the key attribute as your granularity attribute:

  If you select a non-key granularity attribute, the server will not be
  able to aggregate data properly unless you make sure all other
  attributes are directly or indirectly related to it by specifying them
  as related attributes

Which is basically saying you need to define attribute relationships.

This is also explained in more detail in this walkthrough Defining Dimension Granularity within a Measure Group

  To specify a grain for a cube dimension other than the default grain,
  you modify the granularity attribute for a cube dimension as used
  within a particular measure group on the Dimension Usage tab of Cube
  Designer. When you change the grain of a dimension within a specific
  measure group to an attribute other than the key attribute for that
  dimension, you must guarantee that all other attributes in the measure
  group are directly or indirectly related to new granularity attribute.
  You do this by specifying attribute relationships between all other
  attributes and the attribute that is specified as the granularity
  attribute in the measure group. In this case, you define additional
  attribute relationships rather than move attribute relationships. The
  attribute that is specified as the granularity attribute effectively
  becomes the key attribute within the measure group for the remaining
  attributes in the dimension. If you do not specify attribute
  relationships appropriately, Analysis Services will not be able to
  aggregate values correctly, as you will see in the tasks in this
  topic.


SQL Server bulk insert on VM This is mostly likely not a SQL server issue but the setup seems to only be affecting BULK INSERTS to SQL Servers.

We have recently moved VM Hardware and all the guests that were moved had their virtual switches changed from standard to distributed.

I then started receiving
A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 64, output error: 0)
on a two SQL servers during BULK INSERT operations. One of the SQL servers was a VM with the new configuration and the other was a physical server. Both BULK INSERT operation originated from a VM with the new configuration. The BULK INSERTs would not fail every time, it was very random when it would.

When we changed the virtual switch to be a standard switch instead of a distributed switch the issue goes away.

I am looking for more of an explanation to why it doesn't work with a distributed switch instead of a resolution. My guess would be that the BULK INSERT operation is serial and with a distributed switch the packets are being routed through different hosts, some of which may be busier than others, and are arriving at the destination server beyond some latency threshold. (note: there is nothing in the windows event log at the times of the errors on either the source or destination server)

https://www.sqlservercentral.com/Forums/Topic1394090-391-1.aspx

Check what virtual NIC the VMs are using? if you are using E1000 NIC, change it to the VMXNET3.  This resolved my issue when doing large transfers using VMWare distributed switches.