How can I change the value of min word length for specific query in fulltext search? I can set it a new value by adding this line under the [mysqld] section in my.cnf configuration file:

ft_min_word_len = 3 // or any other length

But I don't want that. I want to know, can I do that in a specific query? I have this query:

select * from mytable where match(mycol) against(:val)

And I want to change ft_min_word_len just for query above. I don't want to change it in MySQL configuration file. I mean I want to have another min word length for other queries.

Short answer:  Not possible.

Long answer...

That setting is used when any MyISAM FULLTEXT index is being built.  You should change to InnoDB, in which case innodb_ft_min_token_size is the variable to set.  (It defaults to 3.)

If you change the setting after the index is built, it has no effect on existing indexes or queries using those indexes.

The only way to change it is to (1) change it in my.cnf, then (2) rebuild the index on any table for which you want the new length.


Joining Tables in the FROM vs. the WHERE clause First time caller here.

I have a question regarding best practices when joining tables. For example, both of the following queries return the same results:

SELECT i.id, p.first_name, p.last_name
FROM individuals i, profiles p
WHERE i.id = p.individual_id;

SELECT i.id, p.first_name, p.last_name
FROM individuals i INNER JOIN profiles p ON i.id = p.individual_id;

What are the pros / cons of these two approaches? Please let me know what you think. I'm interested in performance differences, but also in readability of the query, portability from one RDMS to another, etc.

Thank you!

Both queries are exactly the same, should return exactly the same result set and should produce exactly the same Execution plan.

BUT the second query is using ANSI-92 SQL syntax . The first one is using the older (very old) version of SQL Joins.

I can think of a few reasons why you should use the ANSI-92 SQL Syntax.

It separates the Join Conditions and the actual filters in the WHERE clause if you have any.
With the old syntax, you can only have the equivalent of INNER and CROSS joins. Outer joins were not possible except with complicated UNION queries. Some DBMS had added proprietary syntaxes for outer joins, (+) in Oracle, *= and =* in SQL Server, but these are being or have been deprecated. The explicit LEFT, RIGHT and FULL outer joins were added in the standard and have been adopted from almost all DBMS.
It make query much more readable hence easier to debug.
Now days most of the developers, DBAs and also ORMs use the Join syntax.
When using ANSI-92 SQL Syntax for joins if you miss out a join condition you get a compile time error, hence it forces you to provide a valid join condition whereas in older version it doesn't throw any error but it simply returns a CROSS JOIN, in my experience this is one of the worst cons of the older approach.
When using older version of joins and your query is mixing inner join and outer joins the result set may always not what you expect it to be.


Postgres FATAL: could not open relation mapping file I installed postgress.app on mac os
when I try to connect to console :
psql -h localhost
I got this problem .

  psql -h localhost psql: FATAL:  could not open relation mapping file
  "global/pg_filenode.map": No such file or directory

Any ideas how to solve this ?

I had the same error when I tried to reinstall PostgreSQL on RHEL6 using a Chef cookbook. In my case this seems to have been due to an incorrect manual uninstallation of PostgreSQL. I used this command to find the offending files that disrupted my installation:

find / -user postgres

And then I deleted all the files I found as they were owned by postgres and I did not even have a postgres user. To be on the safe side I also rebooted the machine and after that it worked like a charm.


Backup and restore SQL Server database with encrypted columns: what should I backup along with database? Assume I've backed up a SQL Server database with Management Studio, and that database have some encrypted columns. Now I want to restore this database into another fresh-enrolled MSSQL server. To use encrypted columns I had to:

Create database master key;
Create certificate;
Create symmetric key.

Which of those are stored along with backup, and which I should backup manually and restore on the other server manually as well? I see there is SQL statements backup master key, restore master key, backup certificate, create certificate ... from file. So, along with database itself, there are:

Master key;
Master key password;
Certificate;
Certificate private key;
Symmetric key.

So what (and how) should I deal with when I restore my database on a fresh server? Thank you!

You are using column encryption. This is not always-encrypted. When you backup your data base and restore it, you only need to recover the master key. Anything else will be restored through the backup file.
You can restore the master key with this command:
USE [Your_DB_Name]
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Your_MasterKey_Password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY


Obtaining gratis copy of the SQL specification? There used to be a public link to the JTC 1/SC 32 working drafts of 9075:2003, but the link I had has not worked since they started on the post-2003 revisions. Given that there are publicly available copies of many of their other specifications (11179-1:2004, for example), I'm wondering if any one knows of any ways to legally get access to the ISO/IEC 9075 specifications. Perhaps there is a link to the working drafts, or a Foundation that provides access to it for something less than the roughly $3000 USD it would cost me to learn SQL from the standards body instead of relying on implementation-specific books and documentation.

Here they are:

SQL:2008 draft
ISO/IEC 9075:1992 2nd Draft

But I'd rather read read a book:

SQL-99 Complete, Really
Joe Celko's SQL for Smarties


Sequence object blocking We have been experiencing an issue with a sequence object we have that is called a lot. It is used to assign a value prior to a record being inserted into a table.
Today I saw that 700+ sessions (all trying to get the next value from the sequence object) were being blocked by a session that was trying to get the next value from the sequence object with a wait type of PAGELATCH_EX. The other 700+ sessions were waiting on LATCH_EX.
When I looked into the wait resource it was referring to sys.sysobjvalues.
I'm curious as to why this may be happening and if other people have seen this. We recently changed the CACHE value for this sequence from the DEFAULT (50) to 200. I assumed this would improve the performance of calling the sequence given we use it so heavily but possibly I was wrong.
We are using SQL Server 2012 SP4.

The other 700+ sessions were waiting on LATCH_EX....We recently changed the CACHE value for this sequence from the DEFAULT (50) to 200.

If you've got 700 waiting sessions, my first thought is that the cache needs to be much higher given the workload. I would recommend trying something in the range of 500-1000 (and maybe keep increasing) until you see the waits either stabilize or drop off. There should be a sweet spot you can find with a larger cache value.


Is the eager spool operator useful for this delete from a clustered columnstore? I'm testing deleting data from a clustered columnstore index.

I noticed that there is a large eager spool operator in the execution plan:

This completes with the following characteristics:

60 million rows deleted
1.9 GiB TempDB used
14 minutes execution time
Serial plan
1 rebind on spool
Estimated cost for scan: 364.821

If I trick the estimator into underestimating, I get a faster plan that avoids use of TempDB:

Estimated cost of scan: 56.901

(This is an estimated plan, but the numbers in comments are correct.)

Interestingly, the spool disappears again if I flush the delta stores by running the following:

ALTER INDEX IX_Clustered ON Fact.RecordedMetricsDetail REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

The spool appears to only be introduced when there are more than some threshold of pages in the delta stores.

To check the size of the delta stores, I'm running the following query to check for in-row pages for the table:

SELECT
  SUM([in_row_used_page_count]) AS in_row_used_pages,
  SUM(in_row_data_page_count) AS in_row_data_pages
FROM sys.[dm_db_partition_stats] as pstats
JOIN sys.partitions AS p
ON pstats.partition_id = p.partition_id
WHERE p.[object_id] = OBJECT_ID('Fact.RecordedMetricsDetail');

Is there any plausible benefit to the spool iterator in the first plan? I have to assume it is intended as a performance enhancement and not for halloween protection because its presence is not consistent.

I'm testing this on 2016 CTP 3.1, but I see the same behavior on 2014 SP1 CU3.

I've posted a script that generates schema and data and walks you through demonstrating the problem here.

The question is mostly out of curiosity about the optimizer's behavior at this point as I have a workaround for the issue that prompted the question (a large spool filled TempDB). I'm now deleting by using partition switching instead.

Is there any plausible benefit to the spool iterator in the first plan?

This depends on what you regard as "plausible", but the answer according to the cost model is yes. Of course this is true, because the optimizer always chooses the cheapest plan it finds.

The real question is why the cost model considers the plan with the spool so much cheaper than the plan without. Consider estimated plans created for a fresh table (from your script) before any rows have been added to the delta store:

DELETE Fact.RecordedMetricsDetail
WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE())
OPTION (RECOMPILE);

The estimated cost for this plan is a huge 771,734 units:

The cost is almost all associated with the Clustered Index Delete, because the deletions are expected to result in a great deal of random I/O. This is just the generic logic that applies to all data modifications. For example, an unordered set of modifications to a b-tree index are assumed to result in largely random I/O, with an associated high I/O cost.

Data-changing plans may feature a Sort to present rows in an order that will promote sequential access, for exactly these cost reasons. The impact is exacerbated in this case because the table is partitioned. Very partitioned, in fact; your script creates 15,000 of them. Random updates to a very partitioned table are costed especially high since the price to switch partitions (rowsets) mid-stream is given a high cost as well.

The last major factor to consider is that the simple update query above (where 'update' means any data-changing operation, including a delete) qualifies for an optimization called "rowset sharing", where the same internal rowset is used for both scanning and updating the table. The execution plan still shows two separate operators, but nevertheless, there is only one rowset used.

I mention this because being able to apply this optimization means the optimizer takes a code path that simply does not consider the potential benefits of explicitly sorting to reduce the cost of random I/O. Where the table is a b-tree, this makes sense, because the structure is inherently ordered, so sharing the rowset provides all the potential benefits automatically.

The important consequence is that the costing logic for the update operator does not consider this ordering benefit (promoting sequential I/O or other optimizations) where the underlying object is column store. This is because column store modifications are not performed in-place; they use a delta store. The cost model is therefore reflecting a difference between shared-rowset updates on b-trees versus columnstores.

Nevertheless, in the special case of a (very!) partitioned columnstore, there might still be a benefit to preserved ordering, in that performing all updates to one partition before moving to the next might still be advantageous from an I/O point of view.

The standard cost logic is reused for column stores here, so a plan that preserves partition ordering (though not order within each partition) is costed lower. We can see this on the test query by using undocumented trace flag 2332 to require sorted input to the update operator. This sets the DMLRequestSort property to true at the update, and forces the optimizer to produce a plan that provides all rows for one partition before moving to the next:

DELETE Fact.RecordedMetricsDetail
WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE())
OPTION (RECOMPILE, QUERYTRACEON 2332);

The estimated cost for this plan is very much lower, at 52.5174 units:

This reduction in cost is all due to the lower estimated I/O cost at the update. The introduced Spool performs no useful function, except it can guarantee output in partition order, as required by the update with DMLRequestSort = true (the serial scan of a column store index cannot provide this guarantee). The cost of the spool itself is considered to be relatively low, especially compared with the (probably unrealistic) reduction in cost at the update.

The decision about whether to require ordered input to the update operator is made very early on in query optimization. The heuristics used in this decision have never been documented, but can be determined through trial and error. It seems that the size of any delta stores is an input to this decision. Once made, the choice is permanent for the query compilation. No USE PLANhint will succeed: the target of the plan either has ordered input to the update, or it does not.

There is another way to get a low-cost plan for this query without artificially limiting the cardinality estimate. A sufficiently low estimate to avoid the Spool will probably result in DMLRequestSort being false, resulting in a very high estimated plan cost due to the expected random I/O. An alternative is to use trace flag 8649 (parallel plan) in conjunction with 2332 (DMLRequestSort = true):

DELETE Fact.RecordedMetricsDetail
WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE())
OPTION (RECOMPILE, QUERYTRACEON 2332, QUERYTRACEON 8649);

This results in a plan that uses per-partition batch-mode parallel scan and an order-preserving (merging) Gather Streams exchange:

Depending on the run-time effectiveness of partition ordering on your hardware, this may perform best of the three. That said,large modifications are not a great idea on column store, so the partition-switching idea is almost certainly better. If you can cope with the long compilation times and quirky plan choices often seen with partitioned objects - especially when the number of partitions is large.

Combining many, relatively new, features, especially near their limits, is a great way to obtain poor execution plans. The depth of optimizer support tends to improve over time, but using 15,000 partitions of column store will likely always mean you live in interesting times.


Modify text file with Oracle PL/SQL I'm trying to open and replace a string in a text file with the following procedure:

BEGIN
v_file_name := 'text_file.txt';
v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W');
v_id_nextval := v_id_nextval + 1;
BEGIN
LOOP
    utl_file.get_line(v_filehandle, v_new_line);
    v_new_line := replace(v_new_line, 30690, v_id_nextval);
    EXIT WHEN v_new_line IS NULL;
  END LOOP;
END;
utl_file.fclose(v_filehandle);

The problem is that after this procedure runs, the text file becomes empty.
If I replace the 'W' by 'R' in the

v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W');

The value of the variable is replaced correctly, but there are no changes made in the text file, because it has only read access.

Am I doing something wrong or is it not possible at all?

PS: I'm using Oracle 9i.

Regards.

Yes, 'W' will do that -- replace the file. Use 'A' to append. I don't think you can "modify in place"; you might have to copy the file to a new one making the changes you desire, delete the original and then rename the copy.

All of this begs the question why you're trying to do all of this in PL/SQL. It might be better -- certainly easier -- to do it in an external program written in eg Python and invoke that.


Date pattern regex doesn't match the records in PostgreSQL query? I am working with a dataset in PostgreSQL which is quick messy. I have written a few regex operations to find date patterns in the records of a table. Since I don't have a generalized REGEX defined for finding any kind of date pattern!, so, I have created a few using some examples found in the database such as:

9/18/2010 6:17:00 AM
 05/21/2014
Date 2008-02-13
7/18/2016 9:54 PM
 2000-04-20
Booked:1/16/2011 6:45:00 AM (2008-09-01 12:35:45 )
on January 3, 2008
on July 3, 2002
23 Mar 2017
5 Feb 2004
 January 3, 2008
January 31, 2001
December 19, 2007
February 2009
February 30
- Feb 3, 2011
9 Jun 2010

This REGEX matches a few date formats and is able to find the records in the database!:

select count(*) from gallery_image where description ~* '(\d+)\/(\d+)\/(\d+)|(\d+)-(\d+)-(\d+)';

But, this REGEX matches the example, but unable to find a single record in the database, even though they are present in the DB since I have taken all the examples from it.?

select count(*) from gallery_image where description ~* '(\b\d{1,2}\D{0,3})?\b(?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|(Nov|Dec)(?:ember)?)\D?(\d{1,2}\D?)?\D?((19[7-9]\d|20\d{2})|\d{2})';

Could anyone point out the mistake I have made in the second query or REGEX pattern that it works normally but not when used in the PostgreSQL query?

PostgreSQL does not implement Perl regular expression, it implements POSIX regular expressions.  One difference among many is that \b represents a backspace character, not a word boundary. You can use \y to represent a word boundary, although I don't know if that has the exact same meaning as \b has in Perl.

If you want to use Perl regular expressions exactly, you could use the PL/Perl language


weight of replication on SQL Server 2008 R2 instance How can I see what impact replication is having on a server/instance, replication vs all other things.

I assume replication is already running here. My general go to is to use the sp_whoisactive script. I will have the procedure run every few minutes and insert into a history table. After a few days you'll get an idea of what's running on your server. Ideally, I'd run that script for a few days with replication off and then turn it back on again to see the impact.

The table collects information based on your DMV's which is where I look for performance tuning. You need to get an idea though of what's normal or what regularly runs in your environment. If a query is using a lot of CPU, how do you know if that's the issue? Has it always used a lot of CPU or has something changed?

Paul Randal has a great article that talks about using DMV's to tune and it can help you here as well.

Keep in mind replication is using the Transaction logs here, so CDC or CDT can have a huge impact pending configuration.

There are a few warning signs to your DB if it's under pressure. Is the page life expectancy frequently dropping? Do you see your Buffer Cache Ratio dropping? What do your IO reads and writes look like during high transaction times? Do you have latency alerts configured on replication?


Pointers to multiple index allocation mappings In sql server 2008, the sysindexes table contains a pointer to the first page of the index allocation mapping. If a table consists of multiple IAM pages, will the sysindexes table contain a pointer to each one of those as well, or will the first IAM page point to the second and the second point to the third and so on... ...I checked the books online but wasn't able to find any information on this.

sysindexes points only at first IAM page in IAM pages chain. IAM pages chain is a doubly linked list - ie. each page in chain (obviously except first and last ones) has pointers to the next page and to previous page in its header.

Look here: Managing Space Used by Objects

  An IAM page covers a 4-GB range in a file and is the same coverage as
  a GAM or SGAM page. If the allocation unit contains extents from more
  than one file, or more than one 4-GB range of a file, there will be
  multiple IAM pages linked in an IAM chain. Therefore, each allocation
  unit has at least one IAM page for each file on which it has extents.
  There may also be more than one IAM page on a file, if the range of
  the extents on the file allocated to the allocation unit exceeds the
  range that a single IAM page can record.


MS Access - Creating a report based on a pass-through query that requires a parameter I am attempting to make a report in Access that uses parameters as well as a pass-through query.

I'm open to using a temporary table but the guides I can find using temporary tables all use an Insert query which doesn't work with my pass-through query.

I see a lot of mention of creating a recordset from the pass-though query and then copying the recordset to a temporary table but have not had luck location instructions on how to do that.

Some background. I have a PostgreSQL server that is aggregating multiple databases via a foreign data wrapper. Across those databases I have multiple table names that a reused.

I can do a normal pass-through query using the front end query properties but this doesn't handle parameters for the pass-through query.

I can build a form using ADO objects by setting the record set for the form to equal a record set created from a pass-through query. (Set Me.Recordset = MyRecordset) This allows me to create the query string dynamically so I can use parameters! One step closer but not a report.

Reports do not support setting themselves to a record set the way forms do so I'm back to the drawing board.

Any suggestion on how to accomplish my goal? Should I give up and have Postgres store the results in a table, link that table, and call it a day?

Here is how I approach this kind of task.  I use Microsoft Access with SQL Server back end all the time, and I either use parameters for stored procedures in pass-through queries, or dynamic sql in a pass-through queries all the time.

If you are calling a stored procedure in a pass-through query from Microsoft Access and you need to set the parameters, here is how I do it.

In your pass-through query, use special formatting to assist with dynamic search and replace, such as the following example.  This is a pass-through query that I created in Microsoft Access.  Note, that in a pass-through query, sql server (in my case) syntax applies, so I have put special comments in the query so that I can use code to search and replace the parameter values in between those comments.  In this example, my stored procedure takes two parameters, and the name of this pass-through query is qryInCode_SQL_uspProject_Search.

EXEC dbo.uspProject_Search

@Active =
-- R01_BEG
-1
-- R01_END
, @Search =
-- R02_BEG
''
-- R02_END
-- R03_BEG
-- R03_END
-- R04_BEG
-- R04_END
-- R05_BEG
-- R05_END

Notice the careful placement of the parameter names and separaters (commas) so that only the parameter value appears in between the begin/end markers.

Now, I have written a subroutine to automate replacement (setting) of parameters in any pass-through query.

Public Sub sitsUpdateSQLParams(stQName As String, ParamArray Params() As Variant)
On Error GoTo Err_PROC
    Dim ErrMsg As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim mssql As String
    Dim x As Long
    Dim y As Long

    Set db = CurrentDb()
    If stQName <> "" Then
        Set qdf = db.QueryDefs(stQName)
        For x = 0 To UBound(Params)
            y = InStr(qdf.SQL, "-- R" & Format(x + 1, "00") & "_BEG")
            mssql = Left(qdf.SQL, y + 9)
            mssql = mssql & vbCrLf & Params(x) & vbCrLf
            y = InStr(qdf.SQL, "-- R" & Format(x + 1, "00") & "_END")
            mssql = mssql & Mid(qdf.SQL, y)
            qdf.SQL = mssql
        Next x
    End If

Exit_PROC:
    Set qdf = Nothing
    db.Close
    Set db = Nothing
    Exit Sub

Err_PROC:
    Select Case Err.Number
        Case Else
            ErrMsg = "Error: " & Err.Number & vbCrLf
            ErrMsg = ErrMsg & "Line: " & Erl() & vbCrLf
            ErrMsg = ErrMsg & Err.Description
            DoCmd.Hourglass False
            MsgBox ErrMsg, vbOKOnly + vbCritical, "Sub sitsUpdateSQLParams"
            Resume Exit_PROC
            Resume
    End Select

End Sub

You will notice that this subroutine takes the name of the pass-through query as the first parameter, and then it take a Variant parameter array for all subsequent parameters.  This allows you to specify as many parameter values for replacement in the pass-through query as needed.

You would call this routine for my sample pass-through query as follows:

sitsUpdateSQLParams "qryInCode_SQL_uspProject_Search", 0, "'def'"

That will cause 0 to be passed for the @Active parameter and 'def' to be passed for the @Search parameter.

Also note, that your parameters in this function call must be listed on the above function call in the order they appear in your pass-through query.  My code is relying on the first replacement parameter value being inserted into the R01 block, and the second being inserted into the R02 block, and so forth.

So, in your case, for a report, I would do the following:

Create a pass-through query and format it like my example, so that the parameter replacement routine can correctly replace those parameters.

For this example, lets call your Microsoft Access pass-through query qryReport_SQL_PT_01

Now, create a regular Microsoft Access SELECT query that simply does:

SELECT * FROM qryReport_SQL_PT_01 and call it qryReport_01

Now, your report should have qryReport_01 as the RecordSource.

For your user interface, you now need to use a form, or some other method, to prompt the user for the parameter values so that you can have those values availble in VBA code.  For this to work, you can no longer rely on Microsoft Access to auto-prompt the user for parameters you reference in this query.  You must gather those from the user yourself.

Once you have those values, you can call the routine to update the pass-through query and then open the report.  The report will now reflect the parameters you collected from the user.


What is the difference between `ORDER BY` and `PARTITION BY` arguments in the `OVER` clause? These queries below both give me exactly the same results, which I assume is because of my dataset rather than how the arguments work.

When using an OVER clause, what is the difference between ORDER BY and PARTITION BY.

On a slightly different note, why not use the term GROUP BY instead of the more complicated sounding PARTITION BY, since it seems that using partitioning in this case seems to achieve the same thing as grouping. I know you can alter these inner partitions and that those changes then reflect in the table. Is that the reason?

Query 1:

SELECT
    CP.iYear,
    AVG(AVG(CP.mUpgradeCost)) OVER(ORDER BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP
GROUP BY CP.iYear

Query 2:

SELECT
    CP.iYear,
    AVG(AVG(CP.mUpgradeCost)) OVER(PARTITION BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP
GROUP BY CP.iYear

The PARTITION BY works as a "windowed group" and the ORDER BY does the ordering within the group. However, because you're using GROUP BY CP.iYear, you're effectively reducing your window to just a single row (GROUP BY is performed before the windowed function). The average of a single row will be the value of that row, in your case AVG(CP.mUpgradeCost).

As for query 2, are you trying to create a running average or something? Not even sure what you would expect that query to return.

If you remove GROUP BY CP.iYear and change AVG(AVG(...)) to just AVG(), you'll see the difference.

Here's an example that will hopefully explain the use of PARTITION BY and/or ORDER BY:

Random table:

a     b
----- ------
X     1001
X     1002
X     1003
Y     1001
Y     1002

Now,

SELECT a, b, COUNT(*) OVER (PARTITION BY a) FROM r;

will return

a     b
----- ------ ------
X     1001   3
X     1002   3
X     1003   3
Y     1001   2
Y     1002   2

So you can see that there are 3 rows with a=X and 2 rows with a=Y. The ORDER BY clause comes into play when you want an ordered window function, like a row number or a running total.

SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY b),
             SUM(b) OVER (PARTITION BY a ORDER BY b ROWS UNBOUNDED PRECEDING)
FROM r;

... gives you:

a     b
----- ------ ------ ------
X     1001   1      1001
X     1002   2      2003
X     1003   3      3006
Y     1001   1      1001   <- row number and running total resets here; new partition
Y     1002   2      2003


Intermittent SQL Delegated Authentication Errors Here is an odd issue that is causing me to lose all my hair.
Every few days or so our delegated authentication from one SQL server to another fails with the error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. The SQL server logs show: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.1.x.x] Error: 18456, Severity: 14, State: 5."

Our environment consists of 6 Domain controllers ranging from Windows 2012 to Windows 2019, The SQL servers are Windows Server 2016 running SQL Server 2017.
After a few hours the issue goes away by itself, unless the Kerberos tickets are purged on either one of the affected SQL servers which resolves the issue in about 15 minutes.
At first we thought it was an issue with a particular SQL server so we replaced it, however over the last year it has affected several servers with several different delegated connections.
To date we have tried updating SQL server, deleting and recreating SPNs, upgrading the bulk of our DCs to Windows 2019, updating the domain functionality level, and multiple Microsoft tickets. We have a suspicion that leaving SSMS sessions to the SQL Servers overnight triggers the Kerberos delegated connections to fail more often but it may be superstition at this point.
Any ideas? It works 80% of the time which has made it especially tricky to hunt down. The domain controllers appear to be syncing normally, but I'm not sure what to look for in the case that the DCs are the problem.
Oddly, we are able to connect to the individual servers via protected AD accounts during the outage and the log ins are listed as Kerberos, it's just the AD delegated permissions that stop working.
We do the DC maintenance over the weekends and the issue seems to pop up at any time of any day.

If your SQL Servers are clustered, and their service accounts are configured to allow them to manage their own Service Principal Names, you might be running into a known issue.
In an environment with physically distanced Domain Controllers, a planned failover  event can cause the Kerberos delegation for double-hop authentication to fail with symptoms matching the error message in your question.
When SQL Server shutdown occurs, the SQL Server service attempts to deregister the Kerberos SPN with a Domain Controller.  Then cluster failover occurs, and the SQL Server service is started up on one of the other nodes.  This can happen quickly enough that the other Domain Controllers in the organization haven't yet received the synchronization for the removal of the SPN.  At this point, SQL Server will try to register the SPN, but can't because it hasn't yet been removed on all Domain Controllers.  As a result, the attempt at registering the SPN fails.  SQL Server starts up normally, accepting auth requests via NTLM and no one is any wiser unless someone attempts double-hop authentication.
There isn't a great solution to this, other than having permanent SPN entries created in the Active Directory domain, and preventing SQL Server from managing them automatically.


Is there any way to add a witness without restarting the witness afterwards? I want to add a third instance of SQL server to my mirroring pairs to act as a Witness. When I set the witness, the mirror remains disconnected from the witness and the mirror's log accumulates errors. There are two distinct errors that occur with the same timestamp repeating every ~24 seconds.

Mirror Error Message 1:

  Error: 1438, Severity: 16, State: 2.

Mirror Error Message 2:

  The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.

There are no corresponding errors on the witness server that I could find.

Database Mirroring Monitor will show the witness connection as disconnected on the mirror

In this scenario, I have three instances of SQL server. SqlServer01 is the principle. SqlServer02 is the mirror. The operating mode is high safety. I am attempting to transition to high safety with automatic fail-over by adding SqlServer03 as a witness.

All instances are running the same version SQL Server 2005, 9.00.5292.

Authentication is via SQL Server users with certificate.

The only solution I've found thus far is to restart SQL Server on the witness. Is there a better way? Why does the witness need to be restarted?

I would really like to avoid restarting witnesses because when I set this up in my production environment, witnesses are also part of separate mirroring pairs with their own separate witnesses. Therefore, restarting SQL server on one server will cause a fail-over of all the principal databases on that server.

Here's a way to avoid restarting SQL server. It restarts the mirroring endpoint.

Run on the witness server:

--Run the following on a witness server after configuring a witness
USE [master];
DECLARE @Endpoint NVARCHAR(128);
DECLARE @Cmd NVARCHAR(MAX);
SELECT @Endpoint = (SELECT name
                    FROM sys.endpoints
                    WHERE type = 4);
SELECT @Cmd = 'ALTER ENDPOINT [' + @Endpoint + '] STATE = STOPPED;';
EXEC (@Cmd);
SELECT @Cmd = 'ALTER ENDPOINT [' + @Endpoint + '] STATE = STARTED;';
EXEC (@Cmd);

My servers are already in mirroring pairs with endpoints configured only for PARTNER roles.
My witness deployment procedure is as follows:

Add the witness role to each server's endpoint. Each server only supports one endpoint of type four.

--Run the following on a each witness server before configuring it as a witness to enable WITNESS and PARTNER roles
USE [master];
DECLARE @Endpoint NVARCHAR(128);
DECLARE @Cmd NVARCHAR(MAX);
SELECT @Endpoint = (SELECT name
                    FROM sys.endpoints
                    WHERE type = 4);
SELECT @Cmd = 'ALTER ENDPOINT [' + @Endpoint + '] FOR DATABASE_MIRRORING (ROLE = ALL);';
EXEC (@Cmd);

For Each Server:

Set the witness for each principal database:

USE [master]
ALTER DATABASE [TODO] SET WITNESS = N'TCP://WITNESS_FQDN:ENDPOINT_PORT';

For Each server configured to be a witness:

Manually fail-over all principal databases so that the server only contains mirrors
Restart the mirroring endpoint on the server to fix connection issues (refer to the script at the beginning of this answer)

After the mirrors have finished any log synchronization backlog, manually fail-over databases as needed so that the primary server is as it was initially.


PostgreSQL Query and/or Session CPU Usage Is there any way in PostgreSQL to know how much the cpu cost was per a query and/or by session. It doesn't have to be the exact system cpu usage more of a relative cost like we get with disk I/O. For Windows or Linux.

The auto_explain module might be what you are looking for.

The statistics collector is probably the closest you can get to the Oracle v$ views.

Some more might be available in the pg_stat_statements extension.


You may need to elaborate on this a bit more but yes it is possible to store that data as you've described it. Here's an example, using your document model,  of such an array of dates in a document in a collection. Personally, I would look at modifying this as I've described at the end but of course it all depends on what your queries need to do with the data.

{
"_id" : ObjectId("55474c322e8f6b5a9dda1a0f"),
"dates" : [
    ISODate("2015-01-01T00:00:00.000Z"),
    ISODate("2015-01-02T00:00:00.000Z"),
    ISODate("2015-01-03T00:00:00.000Z"),
    ISODate("2015-01-04T00:00:00.000Z"),
    ISODate("2015-01-05T00:00:00.000Z"),
    ISODate("2015-01-06T00:00:00.000Z"),
    ISODate("2015-01-07T00:00:00.000Z"),
    ISODate("2015-01-08T00:00:00.000Z"),
    ISODate("2015-01-09T00:00:00.000Z"),
    ISODate("2015-01-10T00:00:00.000Z")
]
}

Are you perhaps asking "how" to actually do so or how to work with them? Pending what your use case is, you may want to alter that document model and opt for a different approach than a pure array of dates. I may instead use an array of subdocuments as shown in this example and I've included an example of a query that retrieves an element as well. Multiple people could, and likely will herein, suggest doing this in different ways, without more clarity regarding the use case.

//Example using subdocs
{
"_id" : ObjectId("55474e332e8f6b5a9dda1a10"),
"dates" : [
    {
        "date" : ISODate("2015-01-01T00:00:00.000Z")
    },
    {
        "date" : ISODate("2015-01-02T00:00:00.000Z")
    },
... etc

  //using embedded docs
  db.rangeTest.find(
 {'dates.date': {$gte:ISODate("2015-01-08T00:00:00.000Z"), $lte:ISODate("2015-01-09T00:00:00.000Z")}},
{_id:0, 'dates.$':1} )


Preferred database solution for storing sensor data As a hobby project I am developing a system which stores data from different kind of sensors around the house. Currently it is implemented using ASP.Net MVC 6 and MongoDB for the database. (I choose MongoDB because I was curious about how it works) Every sensor peroidically posts data to the server on it's own.

For the sake of not having to store the same information possibly hundreds of times I am using references for redundant data. This data includes the type of a sensor, the data fields used by the sensor(and information about fields like it's name, etc).

Here is an example in JSON format:

// Sensors collection
{
    _id : 1,
    TypeId : 1
}

// SensorTypes
{
    _id : 1,
    GenericName : "Test sensor",
    FieldIds : [1, 2]
}

// Fields
[{
    _id : 1,
    Name : "field1",
    DecimalPlaces : 2
},
{
    _id : 2,
    Name : "field2",
    DecimalPlaces : 3
}]

Questions:

Is this good practice? Am I using correct and preferred way to avoid redundancy in my database?
"Schema" of my documents can be displayed like a graph. Does a graph database like Neo4j would be more suitable for my problem?

Thank you for your help!

In general you do not want to normalize the data you are stuffing into whichever NoSQL solution you are using, in other words, you want to leave the redundant information there. Otherwise you should be using a relational database system like MySQL,Postgres,SQLite, or MSSQL (Express edition is free, but has database size limits). If you use any of those systems, reducing redundant information would be beneficial.

Honestly, if you are looking to grab a bunch of sensor data, then display it as a graph at a later point, you might want to look into setting up a box with Elasticsearch + Kibana on it. You can get something up and running in a matter of minutes with VERY minimal amounts of configuration: https://www.elastic.co/products/kibana

When Neo4j calls itself a "graphing database" it is not graphs in the visual sense of the word, it is graphs in the relationship sense: https://en.wikipedia.org/wiki/Graph_database

Hope some of this helps.


Slow Query Gets Even Slower After Indexing I have a MySQL database with just one table containing 8 million records. I need to query the table to find a single record that matches a specific number:

select * from my_table
where
  (from_field <= 1533177608)
and
  (to_field   >= 1533177608)

Without an index, the query runs in 5 seconds. But surprisingly, when I create an index on two queried columns, the query takes 35 seconds to complete:

alter table my_table
add index test (from_field, to_field);

It is very important for me to speed up the query to reach a fraction of a second. What can I do to improve it?

If you know that the result will be no more than one rows - or if you don't care, you just want one row even if there are more than one - you can add LIMIT 1 to the ORDER BY clause (you can even leave it without ORDER BY):

select * from my_table
where
  (from_field <= 1533177608)
and
  (to_field   >= 1533177608)
ORDER BY
    from_field DESC
LIMIT 1 ;

or

select * from my_table
where
  (from_field <= 1533177608)
and
  (to_field   >= 1533177608)
ORDER BY
    to_field ASC
LIMIT 1 ;

or just:

select * from my_table
where
  (from_field <= 1533177608)
and
  (to_field   >= 1533177608)
LIMIT 1 ;

The first version above (and probably the 3rd one) will do an index scan on the (from_field, to_field) index, until it finds one match and then stop (and read the related row from the table).

The above queries will still be not very efficient when there is no match at all, as a large part of the index will have to be scanned.

If you are 100% sure that there are no overlaps of the intervals, you can use this version, which will only need an index on (from_field) and do an index seek only:

SELECT * FROM my_table
WHERE from_field =
  ( select MAX(from_field) from my_table
    where
      (from_field <= 1533177608)
  )
  AND
    (to_field   >= 1533177608) ;


SQL Server tempdb question From what I understand the system database is used to hold temporary tables, intermediate results and other temporary information.

On one of my database instances I have a tempdb that is seems very large (30GB).  This database has not been modified (as in "last modified date" on the mdf file) in over a week.  Is it normal to have the temp db remain that large for that long of a period?  It seems to me that it should be updating fairly often and returning space that it is using fairly quickly...

Am I way off here or is SQL Server doing something weird?

FYI:  This is a SharePoint 2010 database, not sure if that makes a difference.

UPDATE:

Martin, your query returns the following:

usr_obj_kb|internal_obj_kb| version_store_kb| freespace_kb  | mixedextent_kb
6272      |1984        | 64              | 30418048      | 4416

You should understand how SQL Server data storage works. It isn't like you are used to with client applications.

For performance and other reasons, SQL Server reserves blocks of space on the HD (in the MDF file) for the each database's use (including tempDB). That space as seen by the file size is still reserved even if it is not actively being used.

Whenever the database exceeds the allocated space (depending on the file growth settings), it may grow the amount of reserved space (never to give it back). So 30GB is either the initially reserved space or you did some huge operation at some point that caused it to grow really big.

Tips:
You can use sp_spaceUsed to see how much of that file is actually in use.
You can shrink the database back to size (if it isn't all used) with DBCC_SHRINKDATABASE
As tempting as it may be, don't turn on the DB's AutoShrink property.

As for the last modified date, here is what I found:

  The modified dates change when SQL
  Server closes the files (SQL Server
  shut down or the database is
  detached), or when the file is grown
  (either automatically or manually).
  All other times, SQL Server
  essentially bypasses the file system
  when performing the writes, and so the
  modified dates aren't updated.


Postgres: Performance: Slow index scan I have a really big query, that queries data from various tables. I have tried various approaches to optimize the query.

Made possible LEFT JOIN as LEFT LATERAL JOIN, so that the lateral join queries are computed after fetching results from the main query
Removed GROUP BY since the aggregation took more time.

But even the final query that I had come up with is slow when I try to order it by an indexed column. Please suggest to me how I should get started on optimization.
Here is the query and the explain analyze detail
https://explain.depesz.com/s/kTBh
Here is the EXPLAIN (ANALYZE, BUFFERS) with indexscan ON:
https://explain.depesz.com/s/SyRu
Here is the EXPLAIN (ANALYZE, BUFFERS) with indexscan OFF:
https://explain.depesz.com/s/TPDv
Indexes on the tables:
CREATE INDEX idx_screens_name ON public.screens USING btree (name);
CREATE INDEX idx_screens_number ON public.screens USING btree (((data ->> 'number'::text)));
CREATE INDEX idx_screens_tags ON public.screens USING gin (tags);
CREATE INDEX idx_screens_theatre_id ON public.screens USING btree (theatre_id);

CREATE INDEX idx_theatre_companies ON public.theatres USING gin (company_ids);
CREATE INDEX idx_theatres_chain_name ON public.theatres USING btree ((((data -> 'chain'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_city_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'city'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_country_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'country'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_name ON public.theatres USING btree (name);
CREATE INDEX idx_theatres_province_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'province'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_qwc_is_active ON public.theatres USING btree (qwc_is_active);
CREATE INDEX idx_theatres_tags ON public.theatres USING gin (tags);

CREATE UNIQUE INDEX company_screen_mappings_key ON public.company_screen_mappings USING btree (company_id, screen_id) WHERE (is_deleted = false);

CREATE UNIQUE INDEX idx_scheduling_times_company_id_screen_id_content_type_id ON public.scheduling_times USING btree (company_id, screen_id, content_type_id);
CREATE INDEX idx_screen_right_mappings_date_range ON public.screen_right_mappings USING gist (daterange(valid_from, valid_till));
CREATE INDEX idx_screen_right_mappings_time_range ON public.screen_right_mappings USING gist (public.tmrange(start_time, end_time));

Looking at the query: most of your LEFT JOINs are not really LEFT JOINs because of the predicate ON TRUE which makes them behave as CROSS JOINs. Your query is considering every row that comes out of:
FROM screens s
INNER JOIN theatres t ON s.theatre_id = t.id
LEFT JOIN company_screen_mappings AS csm ON csm.screen_id = s.id AND csm.is_deleted = FALSE AND csm.company_id = 'f5003aa3-3621-498a-ab0f-526706fda88f'

against every combination of rows coming out of the sub-queries that get called r, p, c, ste, activation, and rc. This is going to be time-consuming if any or all of those parts return a significant number of rows. As it is needing to compare everything against everything with little or no constraining predicates, scans are unavoidable (it needs to read everything to do the comparisons).
To me that looks like a logic flaw which the GROUP BY existed to try work around (much like the common anti-pattern of fixing accidental cartesian products by adding DISTINCT), furthermore it looks like a query that has been generated by an ORM which has been given some incorrect joining options, though not knowing the application or what the query is trying to do I of course don't know either of those things with any certainty.
For how to proceed in such cases: first I'd ask the developer to look at what the application is asking for (if it is working through an ORM), it may be that the fix or simplification is to be done there. If this is a hand-written query (I doubt it would have been written in one go like this, but it may have grown over time as more and more features were added to the report/screen it supports) rather than ORM generated then my first thought would be to return to the spec and rewrite it to those requirements from scratch.


Disk queue length very high in SQL Server 2016 I have two Servers A and B with the same settings, but on Server A, the query runs in 6 minutes and on Server B the same query runs in 15 minutes.
what I noticed is that on Server B is that disk queue length very high.

While on Server A, the same query does not have a large number of disk queue length.

What could be different for the same query to run in less time on Server A.
Could it be some problems with the Discs?
Thanks.

Disc queue is an indication that you have saturated the bandwidth to that disc. It indicates that you might have

slow disk
slow connection to a disk (or vhd)
contention

(or any combination of these)
Look at where those discs are, how they are connected, and what is on them. Troubleshooting techniques depend on whether this is a VM (with VHD) or physical hardware.
I usually have the most success by asking my SAN admin (or VM admin) to issue me a drive (letter) "pinned to a spindle" (and not shared), and "scaling-out" (move my busy(est) DB to the new storage), but again, it depends on your equipment/situation.


How can I model this problem in a graph database? I have a project that I'm working on where I extract data from PDFs and map/visualize the relationships between the extracted pieces.

Here's an example of my problem:

file: 11425646.pdf
  author: bob
  company: abc co
  date: 1/1/2011
  mentioned_users: [alice,sue,mike,sally]
  images: [1958.jpg,535.jpg,35735.jpg]

file: 15421484.pdf
  author: betty
  company: ionga
  date: 2/15/2011
  mentioned_users: [john,alex,george]
  images: [819.jpg,9841.jpg,78.jpg]

file: 11975748.pdf
  author: micah
  company: zoobi
  date: 9/26/2011
  mentioned_users: [alice,chris,joe]
  images: [526.jpg,5835.jpg,355.jpg]

How can I model this in a graph database like Neo4j?

I would like to be able to be given one piece of data (like a person's name) and find all related (images, co-mentions, authors, etc.) at up to 10 depth. Here's what I'm thinking for the structure, but I'm not sure if it's a good approach: (this isn't any kind of actual syntax)

[file: 11425646.pdf date:1/1/2011] -written_by-> bob
[file: 11425646.pdf date:1/1/2011] -from_company-> abc co
[file: 11425646.pdf date:1/1/2011] -mentions-> alice
[file: 11425646.pdf date:1/1/2011] -mentions-> sue
[file: 11425646.pdf date:1/1/2011] -mentions-> mike
[file: 11425646.pdf date:1/1/2011] -mentions-> sally
[file: 11425646.pdf date:1/1/2011] -has_image-> 1958.jpg
[file: 11425646.pdf date:1/1/2011] -has_image-> 535.jpg
[file: 11425646.pdf date:1/1/2011] -has_image-> 35735.jpg

Is this the right way to structure this data in a graph database?

How can I model this in a graph database like Neo4j?

Modeling neo4j graphs from relational data is quite simple:

Decide your vertexes (nodes, objects) and edges (relationships).
Convert relational data to cypher, declaring all items and all relationships explicit.

Note: Mapping from relational to graph could take only selected entities from relational model, and single table rows can explode into multiple vertexes and multiple edges.

  Is this the right way to structure this data in a graph database?

Yes, it looks OK. Assuming that file, author, company, user, and image are nodes, and date is only an attribute, this

file: 11425646.pdf
  author: bob
  company: abc co
  date: 1/1/2011
  mentioned_users: [alice,sue,mike,sally]
  images: [1958.jpg,535.jpg,35735.jpg]

should convert to this

MERGE (f :File {name:'11425646.pdf', date:'1/1/2011'})
MERGE (a :Author {name:'bob'})
MERGE (c :Company {name:'abc co'})
MERGE (u1 :User {name:'alice'})
MERGE (u2 :User {name:'sue'})
MERGE (u3 :User {name:'mike'})
MERGE (u4 :User {name:'sally'})
MERGE (i1 :Image {name:'1958.jpg'})
MERGE (i2 :Image {name:'535.jpg'})
MERGE (i3 :Image {name:'35735.jpg'})
MERGE (f)-[:WRITTEN_BY]->(a)
MERGE (f)-[:FROM_COMPANY]->(c)
MERGE (f)-[:MENTIONS]->(u1)
MERGE (f)-[:MENTIONS]->(u2)
MERGE (f)-[:MENTIONS]->(u3)
MERGE (f)-[:MENTIONS]->(u4)
MERGE (f)-[:HAS_IMAGE]->(i1)
MERGE (f)-[:HAS_IMAGE]->(i2)
MERGE (f)-[:HAS_IMAGE]->(i3)

Useful links: data modeling guide and  Cypher reference


Data sync between two local Mysql databases in different system in same netwrok I am using mysql as database for my application and for the purpose of fail over and I need to sync the data from  one local mysql database in system one to other local database in system 2 connected through same network.So I want to know how to connect local database in other system and sync data automatically

You need MySQL replication.
Here is the official documentation https://dev.mysql.com/doc/refman/8.0/en/replication.html
I like to use step by step guides, so here's a more simple explanation: http://www.clusterdb.com/mysql-cluster/get-mysql-replication-up-and-running-in-5-minutes
Those docs should work for MySQL 5.X and 8.X.


Azure SSIS Deploying in SQL Azure Does deploying SSIS really required an azure data lake house when deploying on Azure database?. I am currently working on an SSIS project that its database is currently deployed on Azure and I am currently having an issue deploying it because it sends me directly in the "Integration Services Project or Packages" page and having this I think will require me to turn on Azure Data Lake Storage and this will cost us. is there anyway that I can deploy an SSIS package for free?

You will incur some expenses if you want to run ETLs (packages) from Azure. You can create Integration runtime via Azure Data factory and deploy package to the SSIS catalogue for Azure sql database.

Createing Azure SSIS Integration Runtime


Mongo connection failing with 'Transport endpoint is not connected' I have a 2 server installation.

A web server with Apache and a DB server with MongoDB.

I am load testing it, and on ~300 RPS I am getting this error:

  PHP Fatal error:  Uncaught exception 'MongoConnectionException' with
  message 'Transport endpoint is not connected'.

The only thing I am noticing is that right before the fail, I am getting a lots of connections on Mongo:

insert  query update delete getmore command flushes mapped  vsize    res faults locked % idx miss %     qr|qw   ar|aw  netIn netOut  conn       time
     0    659      0      0       0       1       0   208m  1.28g    40m      0        0          0       0|0     0|0    62k   217k   486   03:57:20

Almost 500 connections here... but never more than that!

Mongo is 2.0.3. PHP is 5.3.x (latest of Debian install...)

Help!

I'd really need to see the mongod logs to be sure here, but the two most common causes are covered by doing 2 things:

Check your ulimits on the client and the server (the too many open files page is a good start)
Lower your tcp_keepalive_time from the default (again client and server)

For load testing you should just set the ulimit settings (-n -u etc.) to anything over 20,000 - that is the limit internally in MongoDB currently for open file descriptors.  You may also want to make sure that the Apache user you are running under is similarly OK in terms of its ulimit settings give that you are using PHP.

Looking at the output of lsof should give you clues as to where the open file descriptors are coming from.


If I assign a NVL constraint to a nullable column will it display an error or it will retrieve the records? I have a database in which some tables have to be joined to fetch the data. The problem is that in one table the column is nullable (nullable is set to yes or no constraint given for that column) and the select query is given as NVL(column name,0).

So will the command retrieve it normally or give an error?

Example:

Select  eid,ename ,nvl(sal,0),nvl(comm,0)
    from employee,sal
   where sal>1500
         AND
         comm=500

In the above query sal column is nullable but I am passing the query as nvl(sal,0) so will it retrieve the records or pass an error? If an error then how do you suggest I change the query?

I have already tried and getting neither the output nor any error in sql developer.

When tried in Plsql it gives PLS-00103: Encountered the symbol "OPEN" when expecting one of the following:

:= . ( @ % ;

Update:-

 cursor c_alerts is
select function_code               as function_code,
       nvl(latitude,0.0)           as latitude,
       nvl(longitude,0.0)          as longitude,
       nvl(gps_timestamp,0)        as gps_timestamp,
       nvl(violation_maxspeed, 0)  as violation_maxspeed,
       nvl(violation_start_time, 0)as violation_startspeed,
       nvl(violation_stop_time, 0) as violation_stopspeed,
       nvl(duration_sec, 0)        as duration_sec,
       cs_rec_insert_time          as rec_insert_time,
       nvl(location1, 'not found ')         as location1
  from t_vmu_alerts_hist
 where vmu_id = lv_vmu_id
   and gps_timestamp between lv_from_date and lv_to_date
union
select function_code               as function_code,
       nvl(latitude, 0.0)          as latitude,
       nvl(longitude, 0.0)         as longitude,
       gps                         as gps_timestamp,
       nvl(violation_maxspeed, 0)  as violation_maxspeed,
       nvl(violation_startspeed, 0)as violation_startspeed,
       nvl(violation_stopspeed, 0) as violation_stopspeed,
       nvl(duration_sec, 0)        as duration_sec,
       rec_insert_time             as rec_insert_time,
       loc_info                    as location1
  from p_error_vmu_alerts
 where vmu_id = lv_vmu_id
   and gps_timestamp between lv_from_date and lv_to_date
union
select function_code               as function_code,
       nvl(latitude, 0.0)          as latitude,
       nvl(longitude, 0.0)         as longitude,
       gps_timestamp               as gps_timestamp,
       nvl(violation_maxspeed, 0)  as violation_maxspeed,
       nvl(violation_startspeed, 0)as violation_startspeed,
       nvl(violation_stopspeed, 0) as violation_stopspeed,
       nvl(duration_sec, 0)        as duration_sec,
       rec_insert_time             as rec_insert_time,
       loc_info                    as location1
  from p_vmu_alerts
 where vmu_id = lv_vmu_id
   and gps_timestamp between lv_from_date and lv_to_date;

t_alert
    open c_alerts();
    FETCH c_alerts BULK COLLECT
      INTO t_alert;
    CLOSE c_alerts;
    LOG_ERROR := 13;

So to answer your question.
"So will the command retrieve it normally or give an error?"
Yes it will work and won't return an error.

@update
You forgot your ; after the query.


What is the difference between Aurora Mysql 5.7.x and Aurora 2.x.x I have an RDS instance with Aurora Mysql 5.7.12 and I noticed that there are other versions of Aurora Mysql 5.7, but I don't understand what is the difference between them.

Some of the version listed are:

Aurora (MySQL)-5.7.12   <- The one that I have.
Aurora (MySQL 5.7)-2.03.2
Aurora (MySQL 5.7)-2.03.3
Aurora (MySQL 5.7)-2.03.4
Aurora (MySQL 5.7)-2.04.1
Etc...

I attach an image of the AWS Console:

I want to chose the right version (the one that have most bug fixes in order to have better stability in my system).

They're differences in the minor/patch versions of Aurora.
If you spin up an instance of Aurora (MySQL)-5.7.12, then run:
SELECT @@aurora_version; -- Returns 2.02.5

It's essentially the first version of MySQL 5.7 compatible Aurora.
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Updates.html


Clarification for database creation and relations I've trying to make database with few tables and relations between them. As I'm not very good in databases at all I need some clarifications. The idea is to connect table with table_1, table_2 and table_3 Here are two options:

Option 1 - table is directly connected to other tables and in query will be joining them.

Option 2 - will use middle tables to connect them with foreign keys..

Currently my database schema is a mess I have relations with both methods. Some are with middle table and some are without. This is my current view of relations which I think is not very good.

In option 3(current) you can see that connection are double - one from middle table and one directly. What is the best solution here?

Les's say:
table = hotel, table_1 = rooms, table_2 = reservation and table_3 = food.

When user make reservation the system shows him free rooms. He can also see what kind of food has restaurant in this hotel.
So hotel table should be connected with rooms, food and reservation.

update:
One of the query's that I use to show meals is

    $hotels_id = $_GET['meal_id'];
    $strSQL = "SELECT m.meal_id, meal_name, meal_image, meal_image_big, meal_weight, meal_price, meal_description
        FROM meals m
        JOIN meal_hotel mr
        ON m.meal_id = mr.meal_id
        WHERE hotels_id = '$hotels_id'";

So the table meal_hotel has two columns meal_id and hotels_id and this two columns are FK for hotels and meals. Am I right?

Just for a start:

Usually, this should become clear from normalizing the data. You need a separate table for each entity. And you require an intermediate table for many to many (m:n) relations.

With hotel and room/reservation, it's unlikely that a specific room/reservation relates to more than a single hotel. For meals, you need to decide, what exactly defines one. If all you care is, e.g., whether or not cheese sandwiches are served in some particular hotel, it'd look like m:n. You will need to ask you(r domain expert), what will be true for the lifetime of the system, you are working on.

Foreign keys will be needed, no matter, whether you have intermediate tables or not - the difference is, where they are stored.

Looking at your current schema: Two things come to mind. Either you are storing optional attributes in the intermediate tables - and you take the shortcut, if no optional attribute is filled in. Or you do actually have multiple relations between these entities. You would need to check your data to differentiate between the two. I'd usually vote for consistency - thus using one approach for storing a particular type of fact.

Update regarding "hotel_meal": With all this detail in the meal record, I'd doubt one dish to be offered in more than one hotel (unless you have hotel chains in your system, or several hotels' kitchen is run by the same caterer).
You could check:

SELECT
  meal_id
  , COUNT(hotels_id)
FROM meal_hotel
GROUP BY meal_id
HAVING COUNT(COUNT(hotels_id)) > 1;

If it returns any record, you have a reason for an intermediate table - if you decide the records returned do actually make sense in your domain (hotel chains, same caterer - you should know it).

Update II regarding "hotel_meal": If no meal is ever served in more than a single hotel, you should not need the additional table; just have the hotel_id as foreign key in the meal table. Just as with rooms and reservations…
To list all meals for a particular hotel:

SELECT * FROM meals WHERE hotel_id=1;

Feel free to come back with more detail.


Named Pipe Provider Error code 40 I have literally tried everything, from enabling named pipe to adding exception to ports in the firewall, to everything possible in surface configuration. I can connect to the SQL instance(using TCP and Named Pipes) with SQL Server Management Studio. But sqlcmd throws an error:

Login timeout expired

Help!

You didn't give us a lot of detail, but this article covers most of the basics: http://msdn.microsoft.com/en-us/library/ms190181(v=sql.105).aspx


Difference data between adjacent rows I have a table (called Visits) with ReqTime DATETIME NOT NULL column AND Duration INT UNSIGNED column.

Duration should be set to the difference of ReqTime for two adjacent rows. But Duration is not set when inserting new rows to the table. It is calculated afterward.

I calculate the "duration" of a row R as the interval (in seconds) between R.ReqTime and N.ReqTime where N is the next row (first row, inserted later the current row).

Thus for each row R (except of the last inserted row, as for it N is undefined) we have Duration value.

See pseudocode for updating the table with the correct Duration value (where R is the current row and N is the next (inserted later) row):

UPDATE Visits SET R.Duration=TIMEDIFF(N.ReqTime, R.ReqTime) WHERE R.Duration IS NULL

Should I use cursors to solve this problem? Or are MIN/MAX/ORDER BY fine?

I am not yet comfortable with cursors.

MySQL 5.

This SQL works fine in SQL Server (with appropriate syntax modifications):

UPDATE Visits
SET Duration = TimeDiff(
         ( SELECT ReqTime FROM Visits N WHERE n.ReqTime > R.ReqTime ORDER BY ReqTime LIMIT 1)
        ,R.ReqTime
    )
FROM Visits R ;

Unfortunately, the syntax hits a limitation of MySQL that an updated table cannot be referenced (again) in the WHERE clause of the UPDATE. A common workaround is to rewrite with a JOIN:

UPDATE Visits R
  JOIN Visits U
    ON U.pk = ( SELECT N.pk                 -- the Primary Key of the table
                FROM Visits N
                WHERE N.ReqTime > R.ReqTime
                ORDER BY N.ReqTime
                LIMIT 1
              )
SET R.Duration = TimeDiff(U.ReqTime, R.ReqTime) ;


drop postgresql schema public without losing extensions As part of my development workflow, I often use
drop schema public cascade;
create schema public;

in order to get a clean state of the test database before re-loading my table and stored procedure definitions.
With the right setup, these statements work without superuser permissions.
My table definitions depend on extensions such as
create extension "pgcrypto";
create extension "uuid-ossp";

and whenever I drop the schema, they are lost...
which wouldn't be a problem, if they didn't require super-user permissions to re-create.
Possible solutions I have considered:
Make my user super-user
alter user myuser with superuser;

Sadly, this is the number-one suggestion when you google for any sort of permission-related issue. I would very much like to avoid super-user permissions, even on my development and testing system.
Manually re-create the extensions every time I drop the schema
To my best knowledge (and experimental results), the problem here is that I have to re-connect to the database as regular and super-user several times...

connect as regular user to run drop schema / create schema
connect as superuser to run create extension
connect as regular user to run the various create table / create function statements

This is a lot of hassle and I'd rather avoid it.
It'd be acceptable to switch once, but twice is definitely too much. When I run create schema as superuser, the regular user cannot create tables anymore.
Revoke permission to drop the extensions
update pg_extension set extowner=0;

... that's what I tried, but it didn't seem to change anything.
?
Is there an other, creative solution for this issue? Maybe

dropping all individual parts of the schema but not the schema itself
granting permissions to re-create extensions to my user
creating the extensions in a different schema, if such a thing is possible?
something else entirely?

My searches there didn't yield anything useful, but that may be due to my limited experience in the topic.

I question your desire to routinely drop the public schema, but it should be no problem if you do it like this:

create a new schema extension and put it on the search_path

use CREATE EXTENSION ... SCHEMA extension to create the extension objects in that schema

I personally use the public schema for extensions and put my data in a different schema.


Adding Timestamp to Exported Filename in SQL Server Management Studio I am exporting to a CSV using a job with xp_cmdshell. I need the filename to include a timestamp. The code I currently have is...

EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.tablename" queryout "C:\filename-timestamp.csv" -T -c -t, '

As you can see, I need the 'timestamp' to be replaced with the actual timestamp, and for the format to be .csv, how can I achieve this?

This gives date and time both and make it windows compatible filename as well

DECLARE @dt AS VARCHAR(30)
SELECT @dt = REPLACE( CONVERT(VARCHAR(26),getdate(),120),':','-')
EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.tablename" queryout "C:\filename-' + @dt +'" -T -c -t, '


Generate DACPAC diff using predefined config I would like to generate a diff file based on the database vs DACPAC comparison. Currently what I am doing is extracting DACPAC from the target database and trying to create a diff file based on that (I am not sure if the DACPAC extraction is the needed step). Everything is OK until the moment when I am trying to use the same config I am using for publishing (to make sure that I am generating the script with the same configuration as I am publishing) using the following command:

"C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe"
/a:Script /sf:F:\tmp\o.dacpac /tf:F:\tmp\n.dacpac /tdn:ScreenerDb
/op:F:\tmp\ScreenerDb.sql
/p:SqlPublishProfilePath="F:\TMap\Root\Application\Dev\Database\Financials.SQL\Financials.SQL\Publish Configs\Financials.SQL_Dev.publish.xml"

I am getting the following error:

  'SqlPublishProfilePath' is not a valid argument for the 'Script' action.

To be fair, I am just trying to achieve following:

Create a diff file based on the DB DACPAC and the real database using the same configuration as for publishing.

How can I achieve this?

Per the documentation, use /Profile instead of /p:SqlPublishProfilePath:

  Parameter:  /Profile
  Description:
  Specifies the file path to a DAC Publish Profile. The profile defines a collection of properties and variables to use when generating outputs

So this should work:

/Profile: "F:\TMap\Root\Application\Dev\Database\Financials.SQL\Financials.SQL\Publish Configs\Financials.SQL_Dev.publish.xml"


SSIS - Disable Task Based on Variable Value I have an SSIS package that runs a number of Data Flow Tasks all at the same time.

Each Data Flow Task is placed inside a Sequence Container.

Before the Data Flow Tasks are run, a Script Task is used to check what Data Flow Tasks are required to run.

Each Data Flow Task also has a related variable, which is set to true or false by the Script Task. The Data Flow Tasks use the variable value as a Disable Expression property.

When run initially, the process works as expected. Those Tasks that have variable value = true do run, and those that have a variable value = false do not run.

However, the next time I go to run the package all Data Flow Tasks are disabled initially (probably because the related variables are set to false).

But even when some variables are then set to true at runtime, the Tasks remain disabled. The Disable Expression does not seem to pick up the latest variable value. (in the Watch window I can see the variable values have updated correctly)

I tried setting 'DelayValidation' = true on each Task but no change.

Any reason why this happens?

I would add a script task at the beginning of the package, or right before the tasks in questions.

The script task should:

Take in the Variables (like User::IsCounterpartyReportRequired)
Display those variables in a message box (via MessageBox.Show(Variables.IsCounterpartyReportRequired.ToString())

This way you can see the variables at any stage of the package you wish, and find out if there is some other task that's making them change value.  It's a "quickwatch" style debugging but with MessageBox.

Just make sure you don't leave the messageBox Commands running before you deploy, as your package will fail.


MySQL id not saving in order (Laravel in production) I'm really confused about this..
So, I created a Laravel app and hosted it on Heroku. I'm using ClearDB extension to be able to use MySQL. Problem is: when I save a new User on my DB, it is not being saved in ID order.
I got id 1, then id 11 for the second register, then id 21 for the third... Then I deleted them and tried again, and I got id 31. I think, there's a pattern, huh? It's going +10, +10... But why?
Look, the code I'm using to save a new register is only: DB::table('registers')->insert($registerData);
On the $registerData variable, I have only the following data: name, e-mail, a url for the picture and birth date.
Even my "migrations" table is in this order: 1, 11, 21, 31...

I think the auto_increment on your tables is set to 10 instead of 1.


How to add a new column if this doesn't exist I'd like to create a new column in my table if that column doesn't exist. But if the column exists, I would like to update it.

I haven't found a suitable answer yet. Can anyone help me?

You can use INFORMATION_SCHEMA.COLUMNS to check if the column exists or not.

CREATE TABLE t(rid int, foo int);

INSERT INTO t VALUES (1,1),(2,2),(3,3);

CREATE FUNCTION fnn(id int, new_val int) RETURNS void AS $$
BEGIN

  IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't' AND COLUMN_NAME = 'bar') THEN
      ALTER TABLE t
        ADD bar int;
  END IF;

 UPDATE t
 SET    bar = new_val
 WHERE  id = rid;

END; $$
LANGUAGE plpgsql;

SELECT fnn(1, 15);

SELECT * FROM t;

rid | foo |  bar
--: | --: | ---:
  2 |   2 | null
  3 |   3 | null
  1 |   1 |   15

db<>fiddle here


ERDs: How do you indicate mutually-exclusive encounters? If you have a character_instance and character entity in an ERD for a video game, and you want to indicate that something happens when a character entity encounters a character_instance entity, how do you do that?  Here's the catch, though: a character_instance doesn't have the same relationship to its own character.  Is there any special way to indicate this complicated relationship?

I don't know about ERD but, if I have understood correctly, this kind of business rule is very common when considering relationships between entities e.g. consider this example in SQL:

CREATE TABLE People
(
 person_ID VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE Marriages
(
 spouse_1 NOT NULL UNIQUE REFERENCES People (person_ID),
 spouse_2 NOT NULL UNIQUE REFERENCES People (person_ID),
 CHECK (spouse_1 < spouse_2)
);

In addition to enforcing the rule that a person cannot marry themselves, it also ensures that the same marriage is not entered a second time 'in the other direction' i.e. 'Albert is married to Victoria' is the same as 'Victoria is married to Albert' but only the row  `('Albert', 'Victoria') may be entered into the database.

If this is too complex for ERD, consider using another more expressive model e.g. object role modelling.


How to install SSIS in SQL Server 2014? I already have SQL Server 2014 on my computer. I don't remember installing SSIS.

How can I check if I do have it if not how can I add it ?

Thank you

Check your Windows Services for a service named "SQL Server Integration Services 12.0".

If you find you need to add just run the SQL Installer and choose "Add features to existing instance" and mark the option "Integration Services"


Remove unused Indexes MariaDB I have a big database with 50 tables and each table can contain between 100,000 to 100 million records. On these tables are there are 1000s of indexes, but many of the indexes are now not in use.
How to find those indexes and remove them?
I am a beginner with MariaDB and MySQL, so with the answer, if you could mention resources to study this, that would be great.

Refer to https://mariadb.com/kb/en/user-statistics/
An optional feature of MariaDB collects statistics on usage of indexes (and also tables, users, and clients).
If you enable the userstat plugin (see the documentation link), it records a counter of the rows that were read via each given index. If that number remains 0 for a given index, then it was not used by any query.
Keep in mind these counters are reset to 0 when the MariaDB server restarts, so you don't know if the index was used prior to that.


Stored procedure to set value to a column must accept returned values from functions I created a very simple table in Data Mart to store ETL diagnostic information:

CREATE TABLE [internal].[etl_status]
(
    [property_name] VARCHAR(100) NOT NULL PRIMARY KEY,
    [property_value] VARCHAR(MAX) NULL
)

Then I created a stored procedure to handle insertions/modifies into this table. Besides convenience, I had to do this because Microsoft are yet to add T-SQL statement activities to Azure data factory.

Problem is, I can't figure how to make the store procedure accept T-SQL function as parameters. In fact I believe it might not be possible. So here is my little workaround:

CREATE PROCEDURE [internal].[sp_set_etl_status]
    @propertyName VARCHAR(100)
   ,@propertyValue VARCHAR(MAX)
AS

DECLARE @query AS VARCHAR(MAX)

-- Case statement to handle functions passed as propertyValue parameter
-- NOT IDEAL, see solution by Scott Hodgin below
SET @propertyValue = CASE @propertyValue
    WHEN 'GETDATE()' THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME2(2), GETUTCDATE()))
    ELSE @propertyValue
    END

SET @query = '
BEGIN
    IF NOT EXISTS ( SELECT * FROM [internal].[etl_status]
                        WHERE [property_name] =''' + @propertyName + ''')
    BEGIN
        INSERT INTO [internal].[etl_status]
        VALUES (''' + @propertyName + ''',  NULL)
    END
END

UPDATE [internal].[etl_status]
SET [property_value] = ''' + @propertyValue+ '''
WHERE [property_name] = ''' + @propertyName + '''
;'

EXEC(@query)

This works as expected. But with the current solution I would need to have a CASE statement for each function I would like to handle.

EDIT: The suggestion below by Scott Hodgin is an acceptable solution. However I added a default value of FALSE to @propertyValueIsFunction as follows: @propertyValueIsFunction BIT = FALSE.

If it's possible to add an additional parameter to your stored procedure (PropertyValueIsFunction bit), you could test for that condition and use REPLACE to strip out the quotes.  Here's an example:

CREATE PROCEDURE [dbo].[sp_set_etl_status]
    @propertyName VARCHAR(100)
   ,@propertyValue VARCHAR(MAX)
   ,@propertyValueIsFunction bit
AS

DECLARE @query AS VARCHAR(MAX)

SET @query = '
BEGIN
    IF NOT EXISTS ( SELECT * FROM [internal].[etl_status]
                        WHERE [property_name] =''' + @propertyName + ''')
    BEGIN
        INSERT INTO [internal].[etl_status]
        VALUES (''' + @propertyName + ''',  NULL)
    END
END

UPDATE [internal].[etl_status]
SET [property_value] = ''' + @propertyValue+ '''
WHERE [property_name] = ''' + @propertyName + '''
;'
if @propertyValueIsFunction = 1
    set @query = replace(@query,'''' + @propertyValue + '''',@propertyValue)
print @Query
--EXEC(@query)

Assuming (notice the extra parameter value of 1 to indicate function):

exec [dbo].[sp_set_etl_status] 'admission_last_import_start_dttm', 'GETDATE()',1

the resulting query generated would look like this:

BEGIN
    IF NOT EXISTS ( SELECT * FROM [internal].[etl_status]
                        WHERE [property_name] ='admission_last_import_start_dttm')
    BEGIN
        INSERT INTO [internal].[etl_status]
        VALUES ('admission_last_import_start_dttm',  NULL)
    END
END

UPDATE [internal].[etl_status]
SET [property_value] = GETDATE()
WHERE [property_name] = 'admission_last_import_start_dttm'
;

When you print the @query text, you'll see that the quotes are stripped from the function being passed in.

You may have to play around with different scenarios, but this was my first thought to solve the original problem.


Constraining data based on related data in a different table. Should this be done on the database level or the application level? I am working on a database that contains our customer information as well as information about our vendors. Part of that includes matching our customers with our vendors, the account numbers those vendors provide to us for our customers and what we charge our customers to use those vendor's services.

Background

In the situation I'm working on now, I have a table that contains our customer's account number, the vendor ID and the account number assigned to our customer by that vendor.

I also have a table that contains all of our vendor's information and a related table that list's all of their services and a "base cost" for that service.

The Problem

The problem that I have is associating a customer with a vendor's service.

If I just create a table that has our customer's account number, a vendor's service ID and the cost of that service, then I could potentially run into issues where a service is assigned to a customer by a vendor that doesn't offer that service.

Here's a diagram of what I'm talking about (though crude):

Because there is no "check" between VendorServices and CustomerVendorServices, I could potentially insert a record where the VendorServiceID in the CustomerVendorServices table for a service that a vendor doesn't provide.

This is obviously a problem and I could prevent it from happening through the interface of the application that interacts with this database, but I would feel much more comfortable if there was a way for the database to "check" that the VendorServiceID being inserted into the CustomerVendorServices table matches a service that is offered by the vendor who is associated with the VendorAccountsID/VendorAccountNumber.

I'm sure this is beyond confusing and I'd honestly be surprised if anyone tried to understand it, but I thought I would give it a shot. :)

Any help and/or suggestions would be very much appreciated.

From my understanding of your question, I feel this might work.

Remove CustomerVendorServices table from your design.
Add ServiceID and CustomerCost in VendorAccounts table.
Remove the FK reference for VendorID in VendorAccounts table from Vendors table.
Add a FK reference for VendorID in VendorAccounts table from VendorServices table.
Also have a separate Services table identified by ServiceID having ServiceDescription and DefaultCost.
VendorServices will then have a composite primary key of VendorID and ServiceID.


SELECT column for a row only if its value is not empty I want to display a certain column only if its value is not empty.

Pseudo code:

SELECT column1, IF_NOT_EMPTY(column2), ... FROM table;

Will display all columns except column2 if column2 is empty.

Not sure there is a way to do that with standard sql you could use case to change that blank to a value https://www.geeksforgeeks.org/sql-case-statement/

The consensus here was that this shouldn't be done on the DB side rather, in whatever mechanism is presenting the data:  Hide column if any value is null or zero via proc sql or view


Oracle Active Dataguard and User passwords I support a database that uses an active dataguard physical standby.  The standby database is configured to let users access the read only standby for read only queries and reporting.  We do this allow querying of production data, without risking user queries creating problems with locking/blocking.
The one problem with this, is users are unable to reset their own passwords, because they are going given the connection details for the read only database.  I am trying to think of a way that would allow the users to reset their own passwords from the read only standby, but I am coming up late.  I'm wondering if anyone has dealt with a similar issue has found a work around.
Right now, every time one of the users who only has access to the standby database, has their password expire, they reach out to a member of the dba team and have their password reset manually.
I am trying to find a way to allow users to be, logged into the standby database, but reset their password in the primary copy, without having to give the users the production connection details.

You can use the same "ugly" mechanism Oracle uses to generate AWR reports for standby hosts. Create a database link that actually points back to primary and execute PL/SQL procedure on primary, through DB link.


Could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT? Could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT using MySQL Workbench's  SQL Query window or MySQL Connection Navigator instead of in a c# Mono, .NET 4.5 MySQLCommand constructor on Ubuntu Linux 15.10?

If not, could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT using the MySQL server startup script? Where is that located on the Ubuntu Linux 15.10, and what mysql command line arguments should I put in the MySQL server startup scripts?

Here is my SQL script which I wish to run in the SQL Query window:

-- Current Database: `scdvr`
--

CREATE DATABASE IF NOT EXISTS `scdvr`

USE `scdvr`;

SET SQL_SAFE_UPDATES = 0;

SET GLOBAL INNODB_FLUSH_LOG_AT_TRX_COMMIT = 2;
--
-- Table structure for table `ActionSequence`
--

DROP TABLE IF EXISTS `ActionSequence`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ActionSequence` (
  `ASID` varchar(64) NOT NULL,
  `EAID` varchar(64) NOT NULL,
  `SequenceOrder` smallint(6) NOT NULL DEFAULT '0',
  `Enabled` tinyint(1) NOT NULL DEFAULT '1',
  `Toast` tinyint(1) NOT NULL DEFAULT '0',
  `SendToCentral` tinyint(1) NOT NULL DEFAULT '0',
  `LogAsEvent` tinyint(1) NOT NULL DEFAULT '0',
  `CameraGroupID` int(11) DEFAULT NULL,
  `AudioAlertFile` varchar(800) DEFAULT NULL,
  `EmailAddresses` varchar(1500) DEFAULT NULL,
  PRIMARY KEY (`ASID`),
  UNIQUE KEY `ASID` (`ASID`),
  UNIQUE KEY `EAID` (`EAID`),
  KEY `IX_ActionSequence_EAID` (`EAID`),
  KEY `FK_ActionSequence_CameraGroups` (`CameraGroupID`),
  CONSTRAINT `FK_ActionSequence_CameraGroups` FOREIGN KEY (`CameraGroupID`) REFERENCES `CameraGroups` (`CameraGroupID`) ON DELETE SET NULL ON UPDATE NO ACTION,
  CONSTRAINT `FK_ActionSequence_EventAction` FOREIGN KEY (`EAID`) REFERENCES `EventAction` (`EAID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

What version are you running?  5.6 doc gives the details:

innodb_flush_log_at_trx_commit can be set (1) on the commandline, (2) in my.cnf, or (3) via SET GLOBAL.

sql_safe_updates can only be set via SET SESSION or SET GLOBAL.

Note that SET GLOBAL does not impact anyone currently connected (including yourself).  It will effect anyone connecting later, but before the next shutdown of mysqld.

my.cnf affects those who connect after the next restart.

Your question asks how to set them, but fails to say what the ultimate goal is, so I cannot recommend a particular approach.  (Do you care about Workbench?  Your C# connection?  Others currently connected?  Future connections?  What?)

I do not know whether Workbench re-connects between actions.  It probably does.  This implies that SET SESSION may not have any impact.

Update (based on update to Question)

SET GLOBAL INNODB_FLUSH_LOG_AT_TRX_COMMIT = 2;
Note: that SET will affect other users.
Now disconnect and reconnect.
SET SQL_SAFE_UPDATES = 0;
and do the rest of your task.

But...  Neither of those settings impact a mere CREATE TABLE.  I assume you will proceed to do INSERTs and UPDATEs?

Side notes:  These two indexes are redundant and should be removed:

UNIQUE KEY `ASID` (`ASID`),
KEY `IX_ActionSequence_EAID` (`EAID`),

A PRIMARY KEY is a UNIQUE KEY is a KEY.

Why have both ASID and EAID?  Both are UNIQUE; both are VARCHAR(64); either could be the PRIMARY KEY.


Will large inserts crash a 3-node MySQL database cluster? We have a 3 node MySQL database cluster than runs on Percona. I have a stored procedure that inserts a lot of rows in one hit, potentially over a million rows.

I've been warned (by our DBA) this might cause the cluster to crash, but I can't find anything online regarding this or that would suggest this might happen.

Can anyone provide some insight?

Some general considerations, first:

MySQL, and I am talking about InnoDB specifically, is optimized for OLTP loads, specially for good performance for point SELECTS and writes. That doesn't mean that you cannot do bulk inserts (I get 600 000 row insertions/second on my desktop-grade SSD), but that has an impact on the performance of other operations, mainly by taking extra locks and extra resources on transaction procesing/index merging/etc.
Large transaction processing overhead exacerbates when distributing those over the network on a tight clustering solution (for a MySQL model). The fact that you have to load synchronously a large number of rows over several nodes usually requires a large amount of memory and makes them a problem for tightly-attached-node solutions such as NDB and Galera.

Now into Galera specifics:

The first limitation that you will face is that by default, ws_max_size_rows is 128K (128 000 rows) and wsrep_max_ws_size is 1GB. Although those values are configurable, the load process will directly fail if the transactions are larger than the values configured.
I wouldn't be surprised that the cluster could crash - You can see an example here that happened during an import- ending up with 2 nodes getting an OOM even if that specific problem was later fixed. Extra memory is needed in order to certify every transaction among all nodes before actually executing it.
If you want the cluster to work adequately, I would recommend loading the files in chunks, using several transactions. There are event tools like [pt-fifo-split][1] that will help you do that. As an alternative, you could load those files individually on each node one at a time while they are temporally desynced.

This is one question that should not be argued about- If you have a production cluster, you should have a test cluster in which you can try your thesis first. Best case scenario, you will get a horrible performance/a deadlock. Normally, you will run out of memory and 1+ nodes will crash, leading to a full cluster crash.

To know more about the reasons behind galera behavior, and how to avoid downtime at the cost of sacrificing performance, you should read:

Galera flow control in Percona XtraDB Cluster for MySQL
Galera: Excessive memory use on sizable transactions
http://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/10106-xtradb-limitations-wsrep-max-ws-size-and-wsrep-max-ws-rows


FK to table with non-unique values via a on commit refreshing materialized view? I would like to create a FK to a table without unique values. So I cannot create a simple FK. [to clarify, the parent table has duplicates - dont ask why, not in my control ]

I tried creating a materialized view:

CREATE MATERIALIZED VIEW UniqueView
REFRESH ON COMMIT
AS SELECT distinct currency from currencies

.. and then creating a FK to UniqueView. This works however, I cannot use ON COMMIT refresh because "distinct" function is used.

I would like the materialized view to be always up to date with the underlying table.

Any other methods of achieving this?
Many thanks in advance!

You can still use GROUP BY.

create table t1 (c1 varchar2(3), c2 number);
insert into t1 values ('EUR', 100);
insert into t1 values ('EUR', 200);
insert into t1 values ('USD', 150);
commit;

select * from t1;
C1          C2
--- ----------
EUR        100
EUR        200
USD        150

create materialized view log on t1
  with rowid (c1) including new values;

create materialized view v1
refresh fast on commit as
select c1 from t1 group by c1;

select *from v1;
C1
---
EUR
USD

insert into t1 values ('CHF', 180);
commit;

SQL> select * from v1;
C1
---
EUR
USD
CHF


Find the identity of the client firing a query in SQL Server without using triggers? I'm currently using Change Data Capture (CDC) to track data changes, and I wish to track the host name and IP address of the client submitting the query that made the changes. If there are 5 different clients logged in via the same user name, one faces the conundrum of tracking which of the 5 fired the query. Other specious solutions that I found include altering the CDC table with following command:

ALTER TABLE cdc.schema_table_CT
ADD HostName nvarchar(50) NULL DEFAULT(HOST_NAME())

However, this returns the host-name of the server on which the query was fired, and not the host-name of the client that fires the query.

Is there a way around this problem? Something that would help to log the host-name or the IP address (or some other kind of unique identity) of the client.
I do not want to use triggers, as it slows down the system, also CDC generates system tables, so having a trigger on that is apparently not possible.

I am not sure about CDC, but if the login has view server state permission you can use DMVs to get some information.

This is given in Books Online here. I changed the query to add columns which would give you the IP address:

SELECT
    c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme,
    s.host_name, s.program_name, s.client_interface_name,
    c.local_net_address, c.client_net_address, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time, s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = SPID;  --session ID you want to track


Affinity side effects / TF 8002- how to measure Environment: We have Microsoft SQL Server 2016 (SP2-CU15) Standard Edition (64-bit) on Windows Server 2016 Standard 10.0.
We have multiple SE instances on one physical server.
We use processor affinity mask to tell which instance use what cores. Some cores are dedicated to instance, but most cores are shared between more than one instance. (Without using affinity mask we would not be able to use all physical machine power)
Problem: we have noticed that one instance is disturbing another one in case there is some heavy CPU load is running. Especially if that is one (!) big query running for long period of time. There always seems to be enough CPU power left available to affected instance not to experience visible slowdowns.
I do believe that trace flag 8002 would help in this situation as described in this link.
What is the the wait stats (or other metrics) that I should monitor to ensure that trace flag really gives desired positive effect?

Since these instances are SQL Server Standard Edition, this will have to be without the functioning or performance counters provided by the SQL Server Resource Governor, which is an Enterprise Edition feature.
I recommend capturing the [\Process(sqlservr*)% Processor Time] perfmon counters (on a scale of logical processors * 100%), and the [\Processor Information(*)% Processor Time] counters.  You'll want to note which SQL Server process is affnitized to which logical processors, since sharing is happening.  Within the SQL Server instances, I recommend capturing wait_time_ms for SOS_SCHEDULER_YIELD type, as well as signal_wait_time_ms across all wait types. SOS_SCHEDULER_YIELD is the wait that is incurred when a task has used up its quantum on the scheduler and must yield, waiting for its next turn to get on the scheduler and run again.  You'll notice that the signal_wait_time_ms for SOS_SCHEDULER_YIELD is almost if not equal to wait_time_ms.  That's because once it's yielded that task/worker really is ready to get back on the scheduler as soon as possible.  And the signal_wait_time_ms across all wait types (including SOS_SCHEDULER_YIELD) indicates how much time is spent ready to run but just waiting for a turn after the wait resources are available.
The hope is that under a similar workload, trace flag 8002 and the "opportunistic" binding of schedulers to underlying logical CPUs will result in increased rate of work, higher CPU utilization, and decreased times for SOS_SCHEDULER_YIELD and signal wait time across all waits.


Oracle - Exchange partitions between two List partitioned tables Need some help here.
I have an ETL process which loads the data into a target table A. We have created another table B which is same as the target table in structure and this table is accessed by reporting team to generate reports. This is done to minimize the downtime for report generation. This way the reports are always accessing the latest data.
Target table B is a List partition table, partitioned on Client ID. ETL team loads data for each client in the respective partition, in Table A.
I tried doing it with Exchange partition: exchange partition mechanism to swap the segments of A and partitioned table B. But couldn’t do it as both tables are List partitioned and Oracle doesn’t like that. I created partitions so as to avoid creating multiple tables (TableA_ClientId) for each Client.
My other option: whenever data is loaded into target table A, rename the table B as table Temp, table B as table A and table A as table Temp.
Can you please suggest a better approach.

Answer left in a comment by Andrew Sayer:
You can create a non-partitioned staging table with the right structure. You then partition exchange from Table_A to the staging table, then from the staging table to Table_B. What are you going to do with the old data now in Table_A?


Is there any limit for IN results in SQL Server? Is there any limit for the content that the IN filter can handle? For example:

SELECT Name
FROM People
WHERE Job IN (All the values goes here)

Microsoft docs for IN says:

  "Explicitly including an extremely large number of values (many
  thousands of values separated by commas) within the parentheses, in an
  IN clause can consume resources and return errors 8623 or 8632. To
  work around this problem, store the items in the IN list in a table,
  and use a SELECT subquery within an IN clause."

but is there any exact or approximate number for

  many thousands of values

It depends. Seriously. That’s why the docs can’t be specific about where you will notice degradation in your environment.

The solution is to just stop doing this (and worrying about it) and use a table-valued parameter.

If you have the values in C# in such a way that you can build a comma-separated list  as a string to concatenate together in a query, you have the values in C# in such a way that you could stuff them in a DataTable or other structure (maybe that's where they're coming from in the first place), and pass that structure as a parameter to the stored procedure.