What 'resets' the transaction log I'm trying to create a backup plan which creates a full backup twice a day.
A differential backup every hour and a log backup every 5 minutes.

It works fine but I've noticed the log backup (.trn file) grows and grows in size each time.

If I use backup log with init it overwrites and I'm getting the too recent error.

I thought that after another full or differential backup the next log backup would only include the log back-ups "since the last full / diff backup"?

BACKUP DATABASE
    lagerdb
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts.bak'
WITH INIT, NOUNLOAD, NAME = N'lagerdb full', NOSKIP, STATS = 20, NOFORMAT

BACKUP DATABASE
    lagerdb TO DISK = N'c:\SQLBACKUP\lagerdbd-rechts.bak'
WITH INIT, NOUNLOAD, NAME = N'lagerdb differential', NOSKIP, STATS = 20, NOFORMAT, DIFFERENTIAL

BACKUP LOG
    lagerdb
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts.trn'
WITH NAME = N'lagerdb logs'

I did something with the suggestion below to backup the log to separate files with a datetime stamp:

 -- Declares a string
 declare @sql nvarchar(255)
 declare @ParmDefinition NVARCHAR(500)

 -- declares the path name. Seperated by disk drive and directory.
 declare @bkName varchar(255)

 -- Declares the filename.
 declare @bkdbName varchar(50)
 declare @bkdbDateTime varchar(20)

 --Get Timestring
 set @sql = N'select @parm1OUT = CONVERT(varchar(20),CONVERT(DATE,GETDATE(),104),104)     + ''_'' + Substring(CONVERT(varchar(8),getdate(),114),1,2) + Substring(CONVERT(varchar(8),getdate(),114),4,2)'
 SET @ParmDefinition=N'@parm1OUT varchar(20) OUTPUT'

 exec sp_executesql
 @sql,
 @ParmDefinition,
 @parm1OUT=@bkdbDateTime OUTPUT

 set @bkdbName = 'LagerDB_' + @bkdbDateTime

 -- Enter the Drive letter below. Please remove C: as current value.
 set @bkName = 'C:'

 -- Enter the folder name below (with lead and end '\' back slashes.
 -- Ensure that you change the folder from Temp below.
 set @bkName = @bkName + '\SQLBackup\'
 set @bkName = @bkName + @bkdbName

 -- Sets the backup running by executing the @sql string.
 set @sql = 'BACKUP DATABASE lagerdb TO DISK = '''+@bkName+'.bak'''

 print @sql
 exec sp_executesql @sql

(e.g.: LagerDB_06.12.2011_1418.bak)

But the answer I was really looking for is that I thought it would reset after a full backup. Maybe I should schedule a backup with init after a full or differential backup?

You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:

BACKUP LOG
    lagerdb
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn'
WITH NAME = N'lagerdb logs'

Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.


Convert MyISAM to InnoDB in PXC master master I have 3 nodes PXC 5.6 master master cluster. I have few tables that are using MyISAM.

Total MyISAM table = 120
Total MyISAM table size = 80MB

I have also read few recommendations where it suggests not to run ALTER command on cluster.

How can I safely convert those tables to InnoDB without risking the cluster?

My comments on converting from MyISAM to InnoDB:  http://mysql.rjweb.org/doc.php/myisam2innodb

My comments on Galera (PXC):  http://mysql.rjweb.org/doc.php/galera

ALTER, on Galera/PXC, comes in two flavors;

⚈  Rolling Schema Upgrade = manually do one node (offline) at a time
⚈  Total Order Isolation = Galera synchronizes so that it is done at same time
     (in the replication sequence) on all nodes.

Further discussion (in addition to the manual, and my doc):  http://www.codership.com/wiki/doku.php?id=rolling_schema_upgrade


What SQL execution time should I expect when querying 4k records in a 150M records table? We currently have performance issues with a SQL query on RDS Postgres 11.8. Here is the query:
SELECT id FROM answer_submission WHERE student_id = 3983137;

The table "answer_submission" has about 150M records. The query returns about 4k ids.
The query takes ~2s to execute which is problematic for the user experience in our app. (Note that this query is for debug purpose. The real query select most of the columns and thus adding all these columns in the index would make the index heavy)
An analysis of the query confirms that the B-TREE index that we created is correctly used:
Index Scan using answer_submission_student_id on public.answer_submission (cost=0.57..2505.35 rows=842 width=16) (actual time=2.356..2346.005 rows=4604 loops=1)
Output: id
Index Cond: (answer_submission.student_id = 3983137)
Buffers: shared hit=3 read=4392 written=1118
I/O Timings: read=2298.117 write=16.805
Planning Time: 2.370 ms
Execution Time: 2349.484 ms

I understand from this analysis that the bottleneck is the time to read on the disk.
I don't have a critical opinion about this response time. Is it in the range of time that one should expect for querying 4k records in a 150M records table? Or is there an issue somewhere? Do you have any clues on how to improve the execution time?
I already read this question Slow index scans in large table but before implementing this kind of solutions I would like to make sure I can't fix the root cause of this long execution time.
Our server has 64GB of RAM.  The size of the index is ~4Gb and we have other tables with similar index sizes.
This behavior is not an occasional behavior. It can be consistently reproduced at any time. Also there are many records saved at any time in this table.
Thanks for you help

It looks like you were measuring the query right after importing a lot of data into the table, so your query has to set hint bits, or like shared_buffers is so small that the query finds no free buffers and has to write out some (written=1118).
Try an index that includes id so you can get an index only scan and VACUUM the table:
CREATE INDEX ON answer_submission (student_id) INCLUDE (id);
VACUUM answer_submission;

That should make the query fast.


Can old collation type impact database performance? In my project we use SQL_1xCompat_CP850_CI_AS as collation setting for SQL Server instance and for our application's databases. As far I know this is one of very old collations still supported just for backward compatibility purposes. I would like to know whether usage of such "old" collation type may impact overall SQL Server performance?

In addition, is there any difference between SQL_1xCompat_CP850_CI_AS and SQL_Latin1_General_Pref_Cp850_CI_AScollation?
Description on MSDN looks very similar:
Both are CI_AS_KI_WI, use code page 850, and have the same sort order name nocase34.850.
Will we have any benefits if we will change our collation setting to SQL_Latin1_General_Pref_Cp850_CI_AS?

I would like to know whether usage of such "old" collation type may impact overall SQL Server performance?

Well, there shouldn't be a performance difference between SQL_1xCompat_CP850_CI_AS and SQL_Latin1_General_Pref_CP850_CI_AS. Both are the old, simple sort order lists and don't take into account any of the Unicode linguistic rules which the newer Windows Collations (those not starting with SQL_) can do (even for VARCHAR data).

There probably is a slight performance decrease, in typical situations, for using the newer Windows Collations as they are not just simple lists but do tap into the Unicode linguistic rules. However, whatever performance degradation there may be is worth it due to the far better functionality.

One area where there is a performance gain is when comparing NVARCHAR data to an indexed VARCHAR column using a SQL Server Collation (one starting with SQL_) as a conversion needs to take place. An indexed VARCHAR column using a Windows Collation does not get this hit when compared to NVARCHAR data. But this is more of a technical note and not something that should be expected to be an issue for most queries / scenarios.

  Is there any difference between SQL_1xCompat_CP850_CI_AS and SQL_Latin1_General_Pref_CP850_CI_AS?

As denoted in the documentation presented in the question: yes. The sort order is different between them. That should be the only difference as the Code Page and sensitivities are the same between them.

Please note that there are two mistakes in that documentation (but these differences do not change the fact that there are definitely two different sort orders between these two Collations):

Sort Order ID for SQL_Latin1_General_Pref_CP850_CI_AS is 43, not 44
Sort Order Name cannot be the same between both listed Collations as they are different IDs (even if one of the listed IDs is incorrect, the correct ID is still different).

To see the difference please try the following test:

SETUP

CREATE TABLE #CollationTest
(
  [Value] TINYINT NOT NULL PRIMARY KEY,
  [1xCompat] VARCHAR(10) COLLATE SQL_1xCompat_CP850_CI_AS NOT NULL,
  [Latin1] VARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
  [Latin1Pref] VARCHAR(10) COLLATE SQL_Latin1_General_Pref_CP850_CI_AS NOT NULL
);

;WITH nums AS
(
  SELECT TOP (256) (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1) AS [num]
  FROM   [master].[sys].[all_columns] ac
)
INSERT INTO #CollationTest ([Value], [1xCompat], [Latin1], [Latin1Pref])
  SELECT [num], CONVERT(VARBINARY(1), [num]), CONVERT(VARBINARY(1), [num]),
         CONVERT(VARBINARY(1), [num])
  FROM nums
  ORDER BY [num] ASC;

TEST 1: Verify that all characters are the same

SELECT * FROM #CollationTest;

TEST 2: Compare the sort ordering between them

;WITH compat AS
(
  SELECT ct.[Value], ct.[1xCompat],
         ROW_NUMBER() OVER (ORDER BY ct.[1xCompat] ASC) AS [ord]
  FROM   #CollationTest ct
), lat AS
(
  SELECT ct.[Value], ct.[Latin1],
         ROW_NUMBER() OVER (ORDER BY ct.[Latin1] ASC) AS [ord]
  FROM   #CollationTest ct
), pref AS
(
  SELECT ct.[Value], ct.[Latin1Pref],
         ROW_NUMBER() OVER (ORDER BY ct.[Latin1Pref] ASC) AS [ord]
  FROM   #CollationTest ct
)
SELECT compat.[ord], compat.[1xCompat], lat.[Latin1], pref.[Latin1Pref]
FROM   compat
INNER JOIN lat
        ON lat.ord = compat.ord
INNER JOIN pref
        ON pref.ord = compat.ord
WHERE  compat.[Value] <> lat.[Value]
OR     compat.[Value] <> pref.[Value]
OR     lat.[Value] <> pref.[Value]
ORDER BY compat.[ord] ASC;

The differences start at row / value 49, and the WHERE clause filters out the first 48 rows where they all match (boring stuff).

It is fairly obvious that there are numerous differences in the sort ordering. One of those differences is that with SQL_1xCompat_CP850_CI_AS the 52 non-accented characters are grouped together with the upper-case always being first, while the accented characters are not only spread out among the rest, but the upper-case and lower-case versions of the same letter aren't even kept together:

m, N, n, O

On the other hand, with SQL_Latin1_General_Pref_CP850_CI_AS both the non-accented and accented characters are all together, grouped by accent:

m, N, n, Ñ, ñ, O

  Will we have any benefits if we will change our collation setting to SQL_Latin1_General_Pref_CP850_CI_AS?

Most likely the only real benefit is probably longevity. I would expect Microsoft to get rid of SQL_1xCompat_CP850_CI_AS at some point.

You should test moving to SQL_Latin1_General_Pref_CP850_CI_AS and if the difference in sort order doesn't adversely affect the app, then probably a good idea to do.

It is probably even better to move to a Windows Collation, but that involves more testing as there are a few other functional differences. Unfortunately, using the following I found that none of the Windows Collations use Code Page 850:

SELECT *
FROM sys.fn_helpcollations()
WHERE  COLLATIONPROPERTY([name], 'CodePage') = 850;

So, while it is still preferred to be using a Windows Collation, changing from your existing Code Page 850 Collation to a newer one (most likely using Code Page 1252) is a much larger testing effort. IF you have the time and resources I say go for it, else just do the change you were asking about :-).


ORDER BY that doesn't contain repeating values Relevant SQL Fiddle (postgres 9.2)

How do I state a SELECT .. ORDER BY .. that avoids having repeated values for a column?

That is, for rows of the column "rank" (1,3,3,1) how do I get a result so that rows of that column are ordered as (1,3,1,3), garuanteeing that a row with column value 1 is never follow by a row with column value 1, and similar for other rows values.

You cannot avoid in it in all cases (if more than 50% of the rows have the same value in the column.) One way to achieve a "shuffle" result, similar to what you want is by using window function:

WITH cte AS
  ( SELECT *,
           ROW_NUMBER() OVER (PARTITION BY colX) AS rn
    FROM tableX
  )
SELECT *
FROM cte
ORDER BY rn, colX ;

The above will not avoid all cases though. If for example, the values in the column are 1,1,1,1,2,2,3, you'll get:

1,2,3,1,2,1,1

and not the (better):

1,2,1,3,1,2,1


What is the difference between XtraBackup and rsync? Yes, I'm going to run strace on them both... However, I would like to know ...

What is XtraBackup is doing over and above rsync -ac src dest ?
What's so special about XtraBbackup ?
How is XtraBackup interacting with mysqld ?

rsync is just a brute force byte-by-byte copy of data from place to another. You would have perform multiple rsyncs until the last rsync, which is very quick. Then, you would do a full shutdown of MySQL and perform one more rsync.

XtraBackup is a comprehensive tool that behaves like rsync but has directed purpose in life. It can start off copying all InnoDB data and tablespaces. It has the ability to create checkpoints internally and perform in-place an InnoDB crash recovery to help get perfect point-in-time backup. XtraBackup also has an extra feature which allows for the creation of incremental backups. Another additional feature is the creation of InnoDB log files, also built by an in-place crash recovery. There is also some wrapper software to provide frozen copying of MyISAM tables as well.

Both methods are great. XtraBackup simply implements InnoDB transactional features into most of the initial copying. Xtrabackup creates files that can dropped into an established MySQL infrastructure. Fair to say, XtraBackup provides instrumentational backup and materialized, very usable InnoDB files.

Using rsync forces you to manage the copy process multiple times and cap it off with a mysql shutdown to require one intervention of rsync.

Using one over the other may be a personal preference. One thing must be acknowledged: XtraBackup makes backups that are somewhat bigger that the data that was copied. I leave the choice of method to the reader of this question.

I prefer rsync because it has simplicity of use, I can determine the specific point in time before the process begins, I also have total control as to locking mysqld or shutting it down, when such control can be performed, and in whatever order I designate.

Both backups styles have one thing in common : With XtraBackup, the actual point-in-time is a moving target until the backup process is complete and you have to trust XtraBackup (hey 200,000 downloads thus far. Facebook is one of the biggest users of it.  It has earned a lot of trust). In other words, if I start XtraBackup at midnight and the backup lasts till 2:20 AM, then the backup's actual point-in-time is 2:20 AM. Using rsync has the same moving target problem because you have to do multiple rsyncs manually and then determine when to issue a 'service mysql stop' prior to the final rsync.

These methods differ in that mysqld must be shutdown using the rsync method, whereas XtraBackup acts independently of mysqld. In fact, XtraBackup does not interact with mysqld very much at all, except when dealing with MyISAM tables via the wrapper software. XtraBackup tries to build fully functional InnoDB data and log files using InnoDB storage engine operations separate and distinct from mysqld.

Shutting down mysql is very necessary for the rsync method for a very big reason: The InnoDB Buffer Pool can hold "dirty pages", which are data and index pages (1 Page = 16KB) that have not been written back to disk. Using rsync does not catch the data changes pending in the InnoDB Buffer Pool. To speed up the shutdown of mysqld for the final rsync, please run this command:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

This command will keep the number of dirty pages to a bare minimum. This command should be run about one hour before you perform the rsync method of backup. This might even help XtraBackup as well though XtraBackup does not require a mysql shutdown.


SQL Server: Programatically trigger export of db schema SQL Server 2014, Std edition

We have seen and used, over the years, a variety of sprocs and non-MS tools to export the DB schema.

The way we really like to do this is the built in utility, in SSMS, as described here on SO: https://stackoverflow.com/a/12036513/147637

We strongly prefer the MS tool, because it is more complete than hand-rolled tools, and provides super clean, properly organized and structured output.

However, we want to automate this.

How can the MS engine be driven programatically?

These days, by far the best way is both open source and from Microsoft (what a world we live in):

mssql-scripter

The README says it all, so I'll just quote it:

  mssql-scripter is the multiplatform command line equivalent of the
  widely used Generate Scripts Wizard experience in SSMS. You can use
  mssql-scripter on Linux, macOS, and Windows to generate data
  definition language (DDL) and data manipulation language (DML) T-SQL
  scripts for database objects in SQL Server running anywhere, Azure SQL
  Database, and Azure SQL Data Warehouse. You can save the generated
  T-SQL script to a .sql file or pipe it to standard nix utilities (for
  example, sed, awk, grep) for further transformations. You can edit the
  generated script or check it into source control and subsequently
  execute the script in your existing SQL database deployment processes
  and DevOps pipelines with standard multiplatform SQL command line
  tools such as sqlcmd.


SQL Server 2014 accounts for Daylight Saving Time now? My computer/SQL Server 2014 uses Eastern Time Zone which is 5 hours behind UTC time. But when I run the following code, I get a 4-hour difference.

CREATE TABLE datetimeoffsetfunction(
                                    servertime DATETIME2,
                                    utc_servertime DATETIME2,
                                    offset_datetime DATETIMEOFFSET,
                                    switch_offset DATETIMEOFFSET,
                                    to_datetime DATETIMEOFFSET
                                      );

INSERT INTO datetimeoffsetfunction
VALUES (  SYSDATETIME(),
          SYSUTCDATETIME(),
          SYSDATETIMEOFFSET(),
          SWITCHOFFSET(  SYSDATETIMEOFFSET(),  '+05:00'),
          TODATETIMEOFFSET( SYSDATETIME(), '+05:00')
          );

The result I got is

servertime:         2016-07-09 23:59:16.4770556
utc_servertime:     2016-07-10 03:59:16.4770556
offset_datetime:    2016-07-09 23:59:16.4770556 -04:00
switch_offset:      2016-07-10 08:59:16.4770556 +05:00
to_datetime:        2016-07-09 23:59:16.4770556 +05:00

The servertime is my system time, and considering the daylight saving time, I can explain why there were only four hour difference between servertime and utc_servertime. But I did expect offset_datetime to be 2016-07-09 23:59:16.4770556 -05:00 unless SQL Serve now accounts for Daylight saving time.

So my question is Is daylight saving automatically accounted for now is SQL Server, at least starting from 2014?

Is daylight saving automatically accounted for now is SQL Server, at least starting from 2014?

No, SQL Server 2014 is not "DST-aware", nor is your test showing it to be.

First, your 4th and 5th tests -- SWITCHOFFSET( SYSDATETIMEOFFSET(), '+05:00') and TODATETIMEOFFSET( SYSDATETIME(), '+05:00') respectively -- are showing +05:00 because that is exactly what you told them to do.

Next, SYSDATETIMEOFFSET() is showing -04:00 because that offset is currently the true difference between your server's TimeZone (which does change, else it wouldn't show the correct time for approximately half of the year) and UTC, which doesn't change during the year. This, however, does indicate DST-awareness: it is the OS that is DST-aware, which is how the server's current time changes based on DST.

Being DST-aware means that the DST date boundaries for prior periods and for other TimeZones can be taken into account when switching TimeZones. And this requires knowledge of TimeZones. However, SQL Server (at least prior to SQL Server 2016) has no concept of TimeZones. Offsets are just that: a specific number of hours and minutes away from UTC. In contrast, a TimeZone is a rule that states a particular offset and if Daylight Saving is ever applied (some TimeZones do not participate in DST) and if DST is applied, what those date boundaries are (which are a matter of law and change over time, per each region).

Hence, being DST-aware means you have the ability to convert a date in the past to, or from, your current TimeZone, or any date between any two TimeZones, neither of which are the server's current TimeZone. Meaning, SQL Server being DST-aware would indicate that you could convert from:

UTC to Eastern Time (your server's current TimeZone), for any date in the year, for 20 years ago, and get the correct time, even though the DST date boundaries changed in the US during that time. But you can't since you can only calculate the current offset, which is -04:00 and have no way to determine when that offset would be -05:00.
Any non-Eastern TimeZone to any other non-Eastern TimeZone, for any date, and get the correct time. This is not possible because there is no chart of TimeZones that details which TimeZone is what offset for which dates, including historical shifts in those date boundaries, so that any particular date, of any particular year, will be adjusted correctly.

More info and some examples

As mentioned above, time zone offsets are not time zones: they are merely a property of a time zone. Meaning, the "US/Eastern" (a.k.a. "America/New_York") time zone (i.e. EST / EDT) is not identified as -04:00 any more than as -05:00; it is an area that has boundaries to the East and to the West, has a base offset of -05:00, and participates in Daylight Saving and so has an offset -- +01:00 -- and a specific start date and end date for when to apply that DST offset. You can check the info on TimeZoneDB.com.

The main database of this Time Zone info is maintained by the Internet Assigned Names Authority (IANA), and can be found at:

http://www.iana.org/time-zones

Time zone information is determined by individual governments. Some countries participate in DST, other do not. Some cities within various countries / states opt in or out of the parent regions rule. See the page for Daylight Saving Time Around the World 2016 on TimeAndDate.com for a chart the various start and end dates around the world (at least for 2016). And check out their page for Eastern Time (ET) for details including exceptions.

Also, whether or not a particular area observes DST, and if so, what the data boundaries are, changes over time. For example, in the United States, the DST start and end dates changed in late 2005, effective starting in 2007: US Daylight Saving Time Changed in 2005. Meaning, even if you know that the current rule is to apply the +01:00 offset to EST between March 13th and November 6th, then if you convert a DATETIME value of 2006-11-02 18:00:00 from UTC to ET, you would get 2006-11-02 14:00:00 which is wrong, because in 2006 DST ended on October 28th, making the correct Eastern Time be 2006-11-02 13:00:00.

Additional explanation in my DBA.StackExchange answer here: How to check the timezone for a given datetime?

How to handle DST (and converting between time zones) in SQL Server 2005 - 2014

The two main ways I know of to properly handle DST-awareness as well as convert between different time zones are:

Matt Johnson's SQL Server Time Zone Support project:

This is a pure T-SQL approach. Please see the ReadMe at the bottom of the main page linked above (i.e. the project name). Pay close attention to the ConvertZone and SwitchZone functions, as they convert between actual time zones, not mere offsets!

Please also note that it states:

  Microsoft SQL Server 2008 R2 and higher are supported, including Azure SQL Database

SQLCLR:

Converting back and forth, between the server's local time zone and UTC is fairly simple using DateTime.ToLocalTime and DateTime.ToUniversalTime. This would work starting with SQL Server 2005.

Stay away from the TimeZoneclass that has methods of the same names, due to the following warning shown on the TimeZone.ToLocalTime page:

  The ToLocalTime method recognizes only the current daylight saving time adjustment rule for the local time zone. As a result, it is guaranteed to accurately return the local time corresponding to a particular UTC time only during the period in which the latest adjustment rule is in effect. It may return inaccurate results if time is a historic date and time value that was subject to a previous adjustment rule.

Converting between time zones is a bit trickier. There is a built-in .NET class that handles this, BUT it requires the calling Assembly to be marked as UNSAFE do to the TimeZoneInfo class having a Host Protection Attribute of MayLeakOnAbort (i.e. memory leak). People have used this class, and it might be fine when not calling its methods terribly frequently. But I did see at least one case of someone reporting what appeared to be a memory-leak when running this across many (hundreds of thousands or millions) of rows.

Regarding the safe ToLocalTime and ToUniversalTime methods in the DateTime class: precompiled versions of these are available in the SQL# SQLCLR library that I created, but only in the Full version (not in the Free version). To show that these 2 methods do work, consider the situation mentioned above regarding the date boundaries change that occurred in the United States, starting in 2007:

SELECT SQL#.Date_ToLocalTime('2007-10-31 20:23:45') AS [CurrentRule],
       SQL#.Date_ToLocalTime('2006-10-31 20:23:45') AS [PriorRule];

Returns:

CurrentRule                PriorRule
2007-10-31 16:23:45.000    2006-10-31 15:23:45.000


Win-Loss Record in SQL Server I'm building a database on Microsoft SQL Server to teach myself SQL. I am a big UFC fan so I'd like to build a database on UFC fighters.

I'm building two tables fight and fighter. The fighter table will have stats like weight, height, birthday, etc. as well as a column for wins, losses, draws, and NC (no contest). The fight table will have a winner_id column and a loser_id column as well as is_draw and is_nc columns.

How can I best create a link between the winner_id/loser_id columns from the fight table to the wins and losses columns for the fighter table?

There is one caveat. Usually MMA fighters will come to the UFC with experience in other organizations. So they will already have a record of wins and losses prior to the UFC. My database will only record UFC fights. So I can't have those previous wins and losses in the [fight] table. For that reason, I've decided to use triggers for now.

Question: is there a better way to do this other than triggers? I'm thinking setting the wins column = a base number + the number of times their fighter_id is listed in the winner_id column. But how would I do that? Would I need to use a view?

Here is the trigger I have created.

CREATE TRIGGER tr_fight_ResultUpdateFighter
ON fight
FOR INSERT
AS
BEGIN
    -- Win
    DECLARE @winner_id int
    DECLARE @loser_id int
    DECLARE @is_draw bit
    DECLARE @is_nc bit
    SELECT
        @winner_id = winner_id,
        @loser_id = loser_id,
        @is_draw = is_draw,
        @is_nc = is_nc
        FROM inserted

    --Wins
    UPDATE fighter
    SET wins = wins+1
    WHERE fighter_id = @winner_id

    --Loss
    UPDATE fighter
    SET losses = losses+1
    WHERE fighter_id = @loser_id

    --Draw
    UPDATE fighter
    SET draws = draws +
        CASE
            WHEN @is_draw = 1
            THEN 1
            ELSE 0
        END
    WHERE fighter_id = @winner_id
    AND fighter_id = @loser_id

    --NC
    UPDATE fighter
    SET nc = nc +
        CASE
            WHEN @is_nc = 1
            THEN 1
            ELSE 0
        END
    WHERE fighter_id = @winner_id
    AND fighter_id = @loser_id

END

I would start by determining the stats at run-time. Only if that turns out to be too expensive, I would denormalize the statistics:

select f1.fighter_id
     , count(case when f2.is_draw = 0 and f1.fighter_id = f2.winnner_id then 1 end) as #wins
     , count(case when f2.is_draw = 0 and f1.fighter_id = f2.looser_id then 1 end) as #loss
     , count(case when f2.is_draw = 1 and f1.fighter_id in (f2.winner_id, f2.looser_id) then 1 end) as #draws
     , ...
from fighter f1
join fights  f2
    on f1.fighter_id in (f2.winnner_id, f2.looser_id)
group by f1.fighter_id;


Sql Server Performance Degradation in moving from 2005 to 2008 I have sql server 2005 setup for my performance bench marking exercise and I have put same database on another server which has sql server 2008 and exact replica in terms of hardware configuration. Sql server memory allocation is also same in both servers. But my benchmarking results are worse on 2008 than 2005. Will somebody help me to find the reasons for performance degradation in sql server 2008. What are the thing I need to take care, or parameters to set for getting the better or same result sets.

Did you rebuild indexes and statistics after setting up the database on SQL Server 2008?
It's mentioned on MSDN (see "Next steps")

...
Next Steps
After you upgrade to SQL Server ...
Update statistics — To help optimize query performance, we recommend that you update statistics on all databases following upgrade. Use the sp_updatestats stored procedure to update statistics in user-defined tables in SQL Server databases.
...


sys.dm_db_index_physical_stats is extremly slow I have a database that is around 4.5TB, since we have parallel inserts (to reduce daily load time) on one table (partitioned by month) the clustered index on this table tends to be heavily fragmented. When I do a select from sys.dm_db_index_physical_stats (Limited) on this table it takes ages (> 4-5 hours). Is there a faster and better way to check the fragmentation levels on the partitions of this table, the current time this takes is completely inacceptable.

Dont run the DMV for whole database instead run it for particular table or index . With database being huge in size it is bound to take time.

You must read Paul Randal's Explanation about why this DMV could take more time.

  The idea of the DMV is to display physical attributes of indexes (and
  the special case of a heap) - to do this it has to scan the pages
  comprising the index, calculating statistics as it goes. Many DMVs
  support what's called predicate pushdown, which means if you specify a
  WHERE clause, the DMV takes that into account as it prepares the
  information. This DMV doesn't. If you ask it for only the indexes in
  the database that have logical fragmentation > 30%, it will scan all
  the indexes, and then just tell you about those meeting your criteria.
  It has to do this because it has no way of knowing which ones meet
  your criteria until it analyzes them – so can't support predicate
  pushdown.

Did you tried Ola Hallengren solution for index rebuild.

There are various modes you can try as mentioned in his article. But as you said limited also took 4-5 hours I guess this is what DMV is. Its actually slow and even MS believes the same about it.

Index rebuild is considered as maintenance activity and should be done when load on server is relatively less or during maintenance window.


Safe way to stop/abort REINDEX I am running a REINDEX but it takes long time and still working.  The application using the DB is not responding right now. Is there a safe way to stop it and do it another time?
PostgreSQL version is 8.4.

Yes, it's safe. Might take a while, though.

In future you might want to use "CREATE INDEX CONCURRENTLY", like dezso suggested, but first create new index, and then drop old one.


Using PostgreSQL both with (not only) SQL Workbench - how to apply alternate delimiters I have created a PostgreSQL database. Now I try to work with it in SQL Workbench, but I have a problem with the way it understands statement delimiters. The documentation on alternate delimiters sais that SQL Workbench takes semicolon as the end of statement, which causes problems for functions and similar code blocks with more than one semicolon. That's why there's the 'alternate delimiter' option. However, the default alternate delimiter (/) makes the code invalid elsewhere - I picked SQL Developer for a workaround and as an alternate front-end, I can't sacrifice pgAdmin for it and I can't afford maintaining two versions of my source code just to avoid this limitation.

I tried to set --/ as the alternate delimiter instead - this disables the default '/', but it doesn't apply - seems that SQL Workbench can't handle multi-char delimiters.

Is there any way how to solve this problem? Is there any char that wouldn't make me trouble in pgAdmin but would be suitable for SQL Workbench alternate delimiter? Or is there any way to enable alternate delimiters consisting of some harmless char sequence?

EDIT: I tried to define the alternate delimiter both at global and connection settings, and with and without the "single line" option, and no success.

I have asked in the SQL Workbench forum and Thomas Kellerer (author of SQL Workbench) helped me. This is a summary of his answer.

First, my sequence was wrong because -- is a comment, but the delimiter must be a valid (not commented out) character or a valid sequence of characters. This makes finding something suitable for a delimiter in SQL Workbench and still not breaking sql code for psql and pgAdmin impossible.

However, there is a workaround: to execute the script through following command:

WbInclude -file=/some/dir/my_function.sql -delimiter=XXXXXX;

That XXXXXX can be anything that is not in the code. This way you can only execute scripts, not edit them, but that's sufficient for me. Currently, there's no better way to operate the code both in and out SQL Workbench.


Approaching database design I have 1000 sensors, and I need to hoard the value of each sensor at every second, for a month. This is just theory, the most realistically achievable unit of time will be determined when I start testing, though 5 seconds maximum.

If I do a single entry for each sensor at each second, I can get away with just 5 columns, entry_id, dim_id, dim_second, dim_date, sensorValue. However, that equates to (86,400 * 1000) * 31 or 2,678,400,000 rows in a month. That is a whole lot of rows.

If I had 1004 columns, I could do 86,400 * 31 or 2,678,400. That is a ton of columns.

Most of what I've read online tells me it is always undesirable to use columns in this way for RDS. However, if I were to do an entry per sensor, do I dump in a single fact table? Of course I wouldn't make 1000 tables, but should sensors be grouped together in fact tables, maybe by unit (flow, amperage, etc) to make the data set smaller for queries?

Or maybe RDS is not the proper choice for this application, and I should consider NoSQL? I have never worked with it.

I'm using a 16GB, Intel Core i7-4650u machine with a 1TB SSD for the development environment.

EDIT:
I should note that my data pipeline is summarizing this data and dumping it in other tables. The issue is that this has to be an on-prem solution, and I want to be able to bring back big batches of raw data for analysis. Hence the long period and small grain.

I don't know about mysql/mariadb, but a postgresql DB allows you to store arrays.  An array of 86400 sensorValues would allow you to have one record per entry_id, dim_id,  dim_date.

create table sensorvalues (
  entry_id serial primary key,
  dim_id int,
  dim_date date,
  sensorvalue int[86400]
);


Non-Blocking MySQL Backup I'm running a MySQL server with a couple of customer databases, some of which are quite large. We are doing a complete mysql dump once a day. It takes about half an hour to complete.

During this time, every request to the MySQL server gets a ridiculously slow response (sometimes around 1 s instead of something like 10 ms). Even though there are no timeout errors, I would love to see the queries being completed in a decent amount of time, so customer websites are not slowed down by hanging MySQL queries.

I researched a lot and learned about data replication, LVM snapshots, various mysqldump flag options and other work-arounds, but nothing did really improve the situation.

Is there any option to set a query priority in MySQL?

If there was a way to define query priorities, I could give the backup process a lower priority to be sure all the other queries are executed faster. The mysqldump process could be temporarily paused (SIGTSTP) when it's challenged by another request and continued (SIGCONT) when the query is finished. However, I couldn't find a feature like this in MySQL natively.

Giving the mysqldump process a higher nice value doesn't work, because it just increases the time both, the backup process and the table locks will be present. (I'm locking per-table.) Also, limiting IO write rates just leads to longer pain.

Are there any solutions I missed, other than LVM and replication?

Indeed the bottleneck was IO. Since I was using tar -czf to compress the files and the CPU was so performant, the drive simply couldn't handle all the data coming so fast (although we use mirrored RAID volumes).

The mysqldump itself is no problem at all, but tar was slowing down the whole system massively.

I'm now using pv to shape the pipe stream:

tar -czf - ./ | pv -q -L 10m > output.tgz

-q disables output of pv.
-L 10m limits the write operation to 10 MB per second. Just test which value you should use on your server by watching the gzip process via top. I wanted it to have around 50% CPU usage, so the IO impact is lowered to 50% as well.
CPU impact is lowered implicitly, because pv slows down everything (except of the dump itself, it is done before compression).

Many thanks to @Michael - sqlbot and others for pushing me into the right direction.


Your join has to describe a condition where rows in actual are matched to rows in test. In your example, you're just filtering rows using variables; the join is effectively a CROSS JOIN (a cartesian product), which is why you get SUM(actual)=122 on every row.

The following query should solve your problem, with the assumption that rows in actual cannot span multiple weeks in test.

SELECT SUM(a.Actual), w.StartDate, w.EndDate
FROM actual AS a
INNER JOIN test AS w ON
    a.StartDate<=w.EndDate AND
    a.EndDate>=w.StartDate
GROUP BY w.StartDate, w.EndDate;

If rows in actual can span multiple week, you may need to calculate some type of distribution of those. Here's a simple idea:

WITH cte AS (
    SELECT a.Actual,
           1+DATEDIFF(dd, a.StartDate, a.EndDate) AS actualDays,
           (CASE WHEN a.StartDate<w.StartDate THEN w.StartDate ELSE a.StartDate END) AS weekStartDate,
           (CASE WHEN a.EndDate>w.EndDate THEN w.EndDate ELSE a.EndDate END) AS weekEndDate,
           w.StartDate, w.EndDate
    FROM actual AS a
    INNER JOIN test AS w ON
        a.StartDate<=w.EndDate AND
        a.EndDate>=w.StartDate)

SELECT SUM(a.Actual*(1+DATEDIFF(dd, weekStartDate, weekEndDate))/actualDays) AS Actual,
       StartDate, EndDate
FROM cte
GROUP BY StartDate, EndDate;

Explanation, using an example: assume Actual=1000, StartDate='2014-05-02', EndDate='2014-05-13':

The common table expression (cte) will match this with three rows in test.

a.Actual  w.StartDate  w.EndDate
1000      2014-05-01   2014-05-03
1000      2014-05-04   2014-05-10
1000      2014-05-11   2015-05-17

Note the CASE expressions: If a.StartDate is within the week, this is the weekStartDate, otherwise, use w.StartDate. Same thing for the end dates, but the other way around.

a.Actual  w.StartDate  w.EndDate   weekStartDate  weekEndDate
1000      2014-05-01   2014-05-03  2014-05-02     2014-05-03   -- start date the 2nd
1000      2014-05-04   2014-05-10  2014-05-04     2014-05-10
1000      2014-05-11   2015-05-17  2014-05-11     2014-05-13   -- end date the 13th

Now all we have to do is calculate the number of days between weekStartDate and weekEndDate for each row. Divide this number of days by the total number of days for the Actual (actualDays=12), and you get a distribution key:

a.Actual  w.StartDate  w.EndDate   (distribution key)
1000      2014-05-01   2014-05-03  2/12 =0.167
1000      2014-05-04   2014-05-10  7/12 =0.583
1000      2014-05-11   2015-05-17  3/12 =0.250

... and multiply that distribution key by the original Actual:

w.StartDate  w.EndDate   (Actual)
2014-05-01   2014-05-03  167
2014-05-04   2014-05-10  583
2014-05-11   2015-05-17  250
                       =1000


Creating a After Insert Trigger I created a trigger in table tbl_user_order.

and I want to  insert data in retailer_deliver_customer table.
CODE:-

create or replace trigger insert_record
after insert on tbl_user_order
for each row
BEGIN
insert into retailer_deliver_customer
(
prod_name,product_quantity,message,delivery_date,delivery_address,pin_code
)
select
  :new.prod_name,
  :new.prod_quantity,
  :new.meessage,
  :new.delivery_date,
  :new.delivery_address,
  :new.pin_id
 from tbl_user_order where pin_id=:new.pin_id;
commit;
end insert_record;
/

I got mutating error once.

If I pass any value in :new.pin_id then it inserted  and if we declare variable for it then get error.

You don't need the select:

create or replace trigger insert_record
  after insert on tbl_user_order
  for each row
BEGIN
  insert into retailer_deliver_customer
    (prod_name,product_quantity,message,delivery_date,delivery_address,pin_code)
  values(
    :new.prod_name,
    :new.prod_quantity,
    :new.meessage,
    :new.delivery_date,
    :new.delivery_address,
    :new.pin_id);

end insert_record;
/

You can't commit in a trigger. The actions inside a trigger are always part of the transaction that fired the trigger.


Drop service hangs indefinitely I have a mirrored database that was using a service broker to push notifications to a client.  The broker creation framework started making duplicates and this caused serious problems so i rolled back to a different framework.

The problem is i cannot cleanup the the existing services that are not longer in use (or should be).

This line hangs indefinitely.

DROP SERVICE [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271]

I've tried stopping the broker but i cannot because it is a mirrored database.
I've tried killing every single transaction in the database to free the lock but that didn't work.
I've tried working my way up by disabling the queue and then deleting the contracts and message types but i cannot while the service still exists.
I was able to disable the queues and delete the activation stored procedures but this didn't seem to do anything.
Most of the queues do appear to have some rows in them.
SELECT * FROM sys.transmission_queue hangs indefinitely as well.
EDIT: i was able to empty the queue with the following, but it didn't actually fix the issue.

DECLARE @dialog UNIQUEIDENTIFIER

WHILE EXISTS (SELECT 1 from <QUEUE>)
BEGIN
    WAITFOR( RECEIVE TOP (1)
        @dialog = conversation_handle
        FROM <QUEUE>
    ), TIMEOUT 500

    end conversation @dialog with cleanup
end

I'm afraid i'm going to have to break mirroring in order to make a new broker. This is a live production system and i'd rather not do that..  Anything else i can try?

BIG EDIT

So i was able to finally figure out a way to delete most of them.

I first issued this to empty out the queue

-- Create variables used to hold information
DECLARE @dialog UNIQUEIDENTIFIER

WHILE EXISTS (SELECT 1 from [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271])
BEGIN
    WAITFOR( RECEIVE TOP (1)
        @dialog = conversation_handle
        FROM dbo. [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271]
    ), TIMEOUT 500

    end conversation @dialog with cleanup
end

Then i altered the queue with ACTIVATION ( DROP )

ALTER QUEUE [dbo]. [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  DROP  ), POISON_MESSAGE_HANDLING (STATUS = OFF)

After this i was able to drop the service and then the queue. It only seems to take 3-4 minutes per queue to delete.

DROP service [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271]
DROP QUEUE [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271]

At this point all but 2 of the 15 services that refused to drop to have been deleted after stopping and retrying over and over again.

There obviously is something blocking here but as discussed in the comments there isn't anything blocking when i run SELECT * FROM sys.dm_exec_requests

Unfortunately i had to go the route i didn't want to. I had to break mirroring for the database and then create a new broker using:

ALTER DATABASE <db> SET NEW_BROKER WITH ROLLBACK IMMEDIATE

Once this completed i was able to instantly delete the services that were stuck.

My attempts to drop the services without setting the new broker actually completely broke my mirroring anyways so i was kinda forced into this method.


Is it recommended to monthly planned restart MongoDB production server to clean non-mapped virtual memory? We have a replica set of production mongoDB 3.6.10 , large volume of 5.5 TB , over 10,000 connections anytime , applications read both from primary and secondary servers , we see almost every month a crash in the primary server while every month we see increase in the non-mapped virtual memory.
Is it recommended to do a planned restart every month in order to clean the non-mapped virtual memory and to avoid server crashes?
Thanks
alon

Is it recommended to do a planned restart every month in order to clean the non-mapped virtual memory and to avoid server crashes?

No, scheduled restarts are not a recommended practice. You should investigate and address the source of your issue rather than restarting your server periodically.

If your deployment is correctly configured, MongoDB shouldn't crash when out of memory. Restarting your mongod processes will temporarily free up memory, but it is expected that a database server will try to take advantage of available memory as needed for your workload.

If you are running Linux, one likely cause for unexpected process shutdown is the Out-of-Memory (OOM) Killer which looks for processes using a lot of memory (like a database server) and shuts them down if there is insufficient memory or swap space. If the OOM Killer decides to kill mongod, the telltale log message is normally in your system logs  -- try grepping for "oom-killer" or "killed process" in /var/log/messages. See Taming the OOM Killer for more background if this sounds potentially applicable to your environment.

If your overall data size is 5.5TB, I assume there is also general contention for server resources unless you have a relatively small working set or very large host instances.

One source of non-mapped memory usage is your current connection count. mongod allocates up to 1MB per connection, so 10,000+ connections is 10GB+ of RAM. Other consumers of RAM are server-side processing such as aggregation queries, in-memory sorts, Map/Reduce, and JavaScript evaluation. Allocated non-mapped virtual memory or memory outside of the WiredTiger cache will normally be freed when no longer needed

The MongoDB Production Notes and Operations Checklist include some advice that is worth reviewing for applicability to your environment.


SQL Server - Very high disk activity after running query? I'm running SQL Server Express 12.0.4100 on an AWS instance with 16 cores and 64 GB of RAM with a 3TB and 9000 IOPS EBS attached. This has been running for 2 years perfectly with no issues until this week.

The server is running a web app that receives 5-10 requests per second and each request is translated to the same query to the database, only with different parameters (this is all handled by an ORM). These queries (let's call it the GetProduct query) are kind of large because they retrieve data from 13 tables to build a single JSON response that is sent back to the user. The query usually takes between 800 and 1500ms to run.

This week I noticed that when I executed a query, a simple select top 1000 with a single where condition in a table that is also scanned by the GetProduct query the Database I/O usage reported by SQL Management Studio goes up fluctuating between 30MB/s and 50MB/s even when my query already returned (the normal database I/O is between 0.1MB/s and 1MB/s). I detected that suddenly my GetProduct query is taking now more than 60 seconds to complete! Causing timeouts and all the queries executed to the database becomes really slow. This is fixed if I restart the entire server box, everything goes back to normal until I run a query again which is very weird.

I'm far from a DB expert, I'm a programmer that is also in charge of maintaining this database, none of the team knows much of databases. I have updated statistics and noticed that fragmentation is really high on most of my indexes (between 95% and 99% as reported by SSMS). I'm planning a maintenance window so I can take down the app and rebuild all the indexes.

Does fragmentation cause this kind of behavior? I executed sp_whoisactive and I'm sure there's nothing else running on my DB, just calls to the GetProduct query taking a lot of time to complete.

UPDATE

I got the query plans using the "Recent Expensive Queries" section in the Activity Monitor of SSMS because sp_whoisactive @get_plans = 1 was returning a NULL query_plan for this queries.

I can't use Pastebin because the files are too big so I uploaded them to my Google Drive account:

Query plan when everything is working fine, query doesn't take more than 1500ms to run: https://drive.google.com/open?id=1x3esroDgkdwz5XeRXbDjA5ygmQc_sZOF
Query plan after running sp_updatestats, for some reason now other queries that were causing the issue doesn't make it happen anymore. After running sp_updatestats however it causes the issue, query plan with slow query: https://drive.google.com/open?id=1lWrlljgtrGnYPGLjCZe_Hq_uCNqaRHko

Here is the query that is being executed, the only parameter that changes is @p__linq__0 that is replaced by different UUIDs: https://pastebin.com/YnrCJVLW

Finally, here you can see the output of sp_whoisactive @get_plans = 1 when the queries are taking a lot of time to complete:

I wanted to post the solution that apparently worked for my case. I took down the app for maintenance last weekend and did a rebuild of all the indexes of the database (through the SMSS UI) followed by a statistics update and I'm happy to say we didn't experience the issue anymore since then.

I had rebuilt statistics before this and it didn't help and if the problems were related to index fragmentation then the queries should be slow all the time, but for some reason, it worked in my case.

Thank you, everybody, for your time and suggestions!


"show procedure status" displaying the non existing databases stored procedures in mysql server i have taken a mysql server backup using mysqldump and imported the backup file in another mysql server.Import failed because of disk full issue. Only 11 databases are created out of 15 . when i issue show procedure status command,
i can see the procedures related to the missing databases( 4 databases) also along with the created databases in the server.when i debug this , i felt like when i have  backedup mysql database and import it, these procedures would have come through mysql.proc table and show procedure status is referring to mysql.proc table not the existing databases and their procedures in the server.
My question is please let me know whether my understanding is correct or not regarding the above.

Yes. There is not much else to say about your question. If you have recovered the mysql.proc table, but not the databases, you may see the procedures but not the databases.

This is because you can export and import them logically "--routines", which will create CREATE PROCEDURE statements, or physically, recovering the mysql.proc table. In any case, they are not real physical objects of each schema subdirectory (unlike tables, partitions and triggers).

If they annoy you, you can safely delete them from the table, as they are just rows.


Easy way to check connectivity to SQL Server from client For troubleshooting purposes, I would like to be able to check if a client can connect to a SQL Server instance, independent of the application that possibly can't connect to the SQL Server.

Is there an easy way (that means, not having to install 3rd party software) to do this using the default Windows system tools? Perhaps using scripts or network applications?

A simple test method for SQL connectivity is to create an empty text file, with a file extension of "UDL".  You can create it in Notepad.  And it can have any name.  I use "TestSQL.UDL"

Save it on the desktop of a windows PC and double click it.

A "Data Link Properties" dialog box will pop up where you can enter the IP address of the SQL server and also a SQL username and password.

Click the "Test Connection" button to see if you can connect.


Joining of two tables, one containing a large amount of data. Need help optimising the query I have been given a job of taking two tables (trimmed down create scripts below) and merging their data into a third table.
CREATE TABLE dbo.Ztest_nominal
(
  BMCode       varchar(20)     NOT NULL,
  nomcode      varchar(500)    NOT NULL,
  Description  nvarchar(4000)  NULL,
  LanguageID   int             NOT NULL,
  CONSTRAINT PK_Ztest_nominal PRIMARY KEY CLUSTERED (BMCode ASC)
)

CREATE TABLE dbo.Ztest_Participation
(
  ParticipationID       int IDENTITY(1,1)  NOT NULL,
  BMCode                varchar(20)        NOT NULL,
  LatestVersionNo       varchar(5)         NOT NULL,
  LastVersionSubmitted  varchar(5)         NOT NULL,
  CONSTRAINT PK_Ztest_Participation PRIMARY KEY CLUSTERED (BMCode ASC)
)

The third tables columns to take the contents of above (basically the above two tables):
[ParticipationID] [int] IDENTITY(1,1) NOT NULL,
[BMCode] [varchar](20) NOT NULL,
[LatestVersionNo] [varchar](5) NOT NULL,
[LastVersionSubmitted] [varchar](5) NOT NULL,
[nomcode] [varchar](500) NOT NULL,
[Description] [nvarchar](4000) NULL,
[LanguageID] [int] NOT NULL

Ztest_nominal has about 63,000,000 rows and Ztest_Participation about 62,000
The sql I was going to use (with an insert into) is (I did have the inner select outside before I gave up and posted here!):
SELECT
  p.ParticipationId,
  p.LatestVersionNo,
  n.*
FROM
  Ztest_nominal n
INNER JOIN
  (
    SELECT
      ParticipationId,
      LatestVersionNo,
      BMCode
    FROM
      Ztest_participation
    WHERE
      LatestVersionNo = LastVersionSubmitted
  ) p
    ON p.BMCode = n.BMCode

However i feel its going need some optimisation so its not slow, also I've already had out of memory errors when running it.
Any help would be appreciated

So I re-wrote your query (under a couple of assumptions) to hopefully set it up for "great success". The three main things the below queries do are:
1: Pre-filter the data from Ztest_Participation into a temp table. By doing this filtering upfront you simplify your final INSERT query for the query optimizer by pre-materializing just the data you need to JOIN to first. Also materializing it into a temp table can leverage some of the performance benefits in TempDB later on (such as the potential of the data being materialized in memory).
2: The final INSERT query leverages the TABLOCK hint which results in less log generation and removes the potential of many row lock escalations, leading to optimized performance on INSERT. (Additional information on the benefits of TABLOCK here.)
3: I simplified your JOIN query between #Ztest_Participation_Filtered and Ztest_nominal by using a better relational solution. This should also help the query optimizer when generating a performant execution plan.
-- Pre-filter Ztest_Participation down into a temp table
SELECT P.ParticipationID,
       P.BMCode,
       P.LatestVersionNo,
       P.LastVersionSubmitted
  INTO #Ztest_Participation_Filtered
  FROM Ztest_Participation AS P
 WHERE P.LatestVersionNo = P.LastVersionSubmitted

-- Create the appropriate index needed for our JOIN later on
CREATE CLUSTERED INDEX IX_TEMP_Ztest_Participation_Filtered_BMCode
    ON #Ztest_Participation_Filtered (BMCode)

-- Final insert into table "FinalResults" (replace this with your actual new table's name)
INSERT INTO FinalResults WITH (TABLOCK) -- TABLOCK hint to reduce log generation and eliminate potential for many row lock escalations
     ( ParticipationID,
       BMCode,
       LatestVersionNo,
       LastVersionSubmitted,
       nomcode,
       [Description],
       LanguageID
     )
SELECT PF.ParticipationID,
       PF.BMCode,
       PF.LatestVersionNo,
       PF.LastVersionSubmitted,
       N.nomcode,
       N.[Description],
       N.LanguageID
  FROM #Ztest_Participation_Filtered AS PF
 INNER JOIN Ztest_nominal AS N-- Simplified JOIN logic to make the query optimizer's job easier
    ON PF.BMCode = N.BMCode

*Note one assumption my above query makes is that you want to use the ParticipationID from Ztest_Participation in your new 3rd table. You won't be able to INSERT this field with your current table schema where it defines ParticipationID as an IDENTITY in the new table. You'll either need to remove that table attribute before the INSERT and then add in after the INSERT or you can temporarily turn on IDENTITY_INSERT for your new table. (But you should turn it back off after the INSERT.)
On my humble laptop, with the schemas you provided above, and 63,000,000 and 62,000 semi-randomly generated records to fill the tables respectively, the above batch of queries runs in a total runtime of 0 seconds. You can find the execution plan it generated for the FinalResults INSERT query on my end here.


SP2-0003 Ill for timestamp. Please help May I know what's the problem with this? I keep on getting this "SP2-0003 Ill formed" on the timestamp. How can I solve this?

ACCEPT v_cheindatetime timestamp FORMAT 'DD-MON-YY HH:MI:SS'
  PROMPT 'Enter Checkin Date & Time : '
ACCEPT v_cheoutdatetime timestamp FORMAT 'DD-MON-YY HH:MI:SS'
  PROMPT 'Enter Checkout Date & Time : '

--execute the INSERT statement
INSERT INTO booking VALUES
('&v_cheindatetime','&v_cheoutdatetime');

timestamp is not a valid type when using ACCEPT. Use date.

SQL*Plus® User's Guide and Reference - ACCEPT

SQL> !cat 1.sql
ACCEPT v_cheindatetime date FORMAT 'DD-MON-YY HH:MI:SS' PROMPT 'Enter Checkin Date and Time : '
ACCEPT v_cheoutdatetime date FORMAT 'DD-MON-YY HH:MI:SS' PROMPT 'Enter Checkout Date and Time : '

--execute the INSERT statement
INSERT INTO booking VALUES
('&v_cheindatetime','&v_cheoutdatetime');

SQL> @1
Enter Checkin Date and Time : 06-AUG-16 12:00:00
Enter Checkout Date and Time : 07-AUG-16 12:00:00
old   2: ('&v_cheindatetime','&v_cheoutdatetime')
new   2: ('06-AUG-16 12:00:00','07-AUG-16 12:00:00')

1 row created.


Rerunning query in the same connection takes significantly longer I am having a weird issue when a running a query that joins some semi-large tables (one table is about 2 million rows and the other about 21 million) together.  What happens is the query does not run within the same time frame consistently.  If I run the query on a new connection in SSMS it takes about 45 seconds to complete.  After it completes if I hit the execute button again it takes almost 4 minutes to complete.  If I then open a new query (connection) and execute the query it takes about 45 seconds again.  These queries are being run from the server and I can consistently reproduce this.

Any help on what is going on and how to fix is helpful.

With that many rows, moving the results from the server to the client will be a significant part of the elapsed time.  Eliminate variablity in that part by wrapping your query in some way such as:

select
    count(*)
from (
    {your query here}
) as xx

This way the network transfer will be a minimal part of the elapsed time and you can eliminate it from your enquiries.


HAProxy - Load-Balancing in 2 Node setup - Performance Gain possible? I was speaking with my boss about Load-Balancing a 2 node Galera Cluster and we weren't sure if there was any reason.

For writes, his argument was, even if we balance the writes, it has to write to each server to do the replication.

For reads, we could balance the reads across the servers, but would this really save time if everything now goes thru a single VM on another server?

We have two dedicated SQL Servers which are in an Active-Active Galera setup.

The only way I can think of doing a HA-Proxy would be a 3rd VM on another server, is this really worth the performance gain to have everything go thru this one VM which will be on a server congested with other traffic?

Is it possible/Would it make sense to put HA-Proxy right on the SQL Server(s) and load balance the reads, but it would still go thru the Primary server w/ HA-Proxy to get to Server B.

Just looking for some general thoughts and advice for this simple setup.

There are several different topics you comment on your question, and with many "IF"s, depending on your specific workload and architecture. Let's start with the things you are right:

It is true that Galera by itself should not give you better write workload- it is a shared-nothing, non-sharded cluster, which means you will have to write everywhere. The only way to improve significantly your write performance is if you can share the write load between several nodes, while in your case you are duplicating them. In fact, as it is shared nothing architecture, the more servers you add, the slower the whole usually will be, as the slowest or further away server will be the bottleneck of your replication. If you need write performance, galera is not for you (it will usually have less throughput than a single server). You normally setup galera to get high availability(survivability of data and service if a node goes down), not better write performance.

There are some buts:
* I had some clients that claimed better write performance, probably because horrible SQL queries + Galera requiring row-based replication, and in some special cases, you could get some gains with that (if you do 30-second writes but you only write a few records, you will get some extra scalability). That is normally very rare: you should fix first your queries, but I am just pointing to a (very) specific exception.

The meat of your question is that if the fact that you are adding a proxy in the middle will not be worth the improvement you get from load balancing queries. To answer that, you need to say 6 things:

What is your current average round-trip time between the client and the servers
What is your average round-trip time between the client and the proxy
What is your average round-trip time between the proxy and the servers
What is the overhead of the proxy processing time
What is the local latency of the actual mysql query time
How loaded is your average client?

This will tell you if you are interested on using a proxy or not for performance reasons.

You can find out the first 3 by using ping, the last 3 by profiling the actions. Normally, query time is much larger than round time within a datacenter, but that depends on which queries you are doing and how far located (physically) are those VMs. To cancel some of those times, people install the proxy on the same machine than the clients, so any overhead is mostly canceled. Also, HAproxy being mostly an IP proxy, the overhead is very low.

Now, if your servers are not very loaded, you may not get any advantage in latency- querying the server will double your throughput- if that has an impact on latency or not will depend on your current load.

There is usually a more important reason to use a proxy, which is high availability- using HAProxy will allow you to switch to a secondary galera node in case the active one goes down, automatically. It will also simplify manual switchovers. Of course, the proxy itself can be a single point of failure.

I hope that helps you decide- but the most important advice is try it yourself and measure!

Edit: BTW, with 2 nodes only, I hope you use galera as a replication solution, not as a cluster (requires special configuration) or with garbd, because if you don't, and a node goes down, the second will go down too to avoid a slit brain (no node has 50%+ quorum).


Transactions, references and how to enforce double entry bookkeeping? (PG) Double entry bookkeeping is

  a set of rules for recording financial information in a financial
  accounting system in which every transaction or event changes at least
  two different nominal ledger accounts.

An account can be "debited" or "credited", and the sum of all credits must be equal to the sum of all debits.

How would you implement this in a Postgres database? Specifying the following DDL:

CREATE TABLE accounts(
    account_id serial NOT NULL PRIMARY KEY,
    account_name varchar(64) NOT NULL
);

CREATE TABLE transactions(
    transaction_id serial NOT NULL PRIMARY KEY,
    transaction_date date NOT NULL
);

CREATE TABLE transactions_details(
    id serial8 NOT NULL PRIMARY KEY,
    transaction_id integer NOT NULL
        REFERENCES transactions (transaction_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
        DEFERRABLE INITIALLY DEFERRED,
    account_id integer NOT NULL
        REFERENCES accounts (account_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
        NOT DEFERRABLE INITIALLY IMMEDIATE,
    amount decimal(19,6) NOT NULL,
    flag varchar(1) NOT NULL CHECK (flag IN ('C','D'))
);

Note: the transaction_details table does not specify an explicit debit/credit account, because the system should be able to debit/credit more than one account in a single transaction.

This DDL creates the following requirement: After a database transaction commits on the transactions_details table, it must debit and credit the same amount for each transaction_id, e.g:

INSERT INTO accounts VALUES (100, 'Accounts receivable');
INSERT INTO accounts VALUES (200, 'Revenue');

INSERT INTO transactions VALUES (1, CURRENT_DATE);

-- The following must succeed
BEGIN;
    INSERT INTO transactions_details VALUES (DEFAULT, 1, 100, '1000'::decimal, 'D');
    INSERT INTO transactions_details VALUES (DEFAULT, 1, 200, '1000'::decimal, 'C');
COMMIT;

-- But this must raise some error
BEGIN;
    INSERT INTO transactions_details VALUES (DEFAULT, 1, 100, '1000'::decimal, 'D');
    INSERT INTO transactions_details VALUES (DEFAULT, 1, 200, '500'::decimal, 'C');
COMMIT;

Is it possible to implement this in a PostgreSQL database? Without specifying additional tables to store trigger states.

First, this is exactly the question I had in mind when I asked Modelling constraints on subset aggregates? which is certainly the place to start.  That question is more general though than this and so my answer here will have a bit more information regarding practical approaches.

You probably do not want to do this declaratively in PostgreSQL.  The only possible declarative solutions either break 1NF or are extremely complicated and so this means doing it imperatively.

In LedgerSMB we expect to do this enforcement in two stages (both of which are strict).

All journal entries will come in via stored procedures.  These stored procedures will accept a list of line items as an array and check that the sum is equal to 0.  Our model in the db is that we have a single amount column with negative numbers being debits and positive numbers being credits (if I was starting over, I would have positive numbers as debits and negative numbers as credits because this is just a little more natural but the reasons here are obscure).  Debits and credits are merged on storage and separated on retrieval by the presentation layer.  This makes running totals a lot easier.
We will use a deferred constraint trigger which will check on commit based on the system fields on the table.  This means that the lines entered in a given transaction must balance, but we can do this beyond the lines themselves.


What does a SQL Server DBA need to know about Windows Server? Analogous to my previous question What does a DBA have to know about SSAS?, which generated a really wonderful answer (thank you, @ConcernedOfTunbridgeWells!), I now ask a similiar question:

What does a SQL Server DBA need to know about Windows Server in order to be able to manage it efficiently and understand what is going on behind the scenes and to which degree is it important to know the underlying OS in which SQL Server runs to in order to become a great DBA?

It can be individual features, books, from experience, you name it!

You definitely need to know your way around...

Performance Monitor
Disk Management (mount points, 64k clusters, RAID levels etc)
Group Policy + Active Directory + Security
Shares + NTFS permissions
Cluster Service + resource groups
Powershell
Physical/Logical sockets/cores + affinity
NUMA architecture

Without these (and others) you're ineffective outside of raw SQL Server work (eg setting up logins). As soon as you want backups, one or more of the above starts to apply...


SQL Server database stuck on DB STARTUP I have a ~300 GB database on a development server (SQL Server 2016 SP2 - Developer) that is stuck on DB STARTUP.  I am unable to restore over or drop the database. Would shutting down the SQL Server service, deleting the files, and restarting the service work to bring the instance back online and leave me able to restore the database from a backup?

Backstory: After a failed application level data import the database was left in an inconsistent state. I restarted the SQL Server service and all other database except this one came back online. This is a development instance to I was able to leave it alone to see if the process ever completed. Two weeks later when I run sp_who2 there is still an entry for this database with the command DB STARTUP and ever increasing CPUTime and DiskIO. I now need to make this database available. Would deleting the files directly work or are there other options?

Stopping SQL Server and deleting the files will most likely cause the database to go into "recovery pending" at next startup, and my experience is that I've never had any problems dropping a database in that state.

Of course assuming that this is indeed an OK operation for you (restore from prior backup).


Ensuring 1 to 1 mapping through a many to many table I've currently got a database with two tables, "Product" and "Option". Each parameter is tied to one product. We want to extend this data with a new concept of a "Project". A product will have many projects and each project will list what the default values for each option are for that product. The trouble I'm having is thinking how to ensure that each project lists every option for the product it's tied to.

E.g. if a product has options A, B, and C. Each project needs to define the default value for A, B, and C. Adding a new option should require updating all the projects.

Any pointers?

First: you need somewhere a link table that stores a reference to both every product (or project) and its options' values. Let's call that one ProjectOptions, and let's assume it's structured like (ProjectId, OptionId, ValueForOption).

  The trouble I'm having is thinking how to ensure that each project lists every option for the product it's tied to.

You can have an ON INSERT TRIGGER on table project that, whenever you insert a new project, creates all the corresponding entries into the ProjectOptions table.

  Adding a new option should require updating all the projects.

Actually you need to add entries to your ProjectOption table, not just update the Project table itself.

You can have another ON INSERT TRIGGER on table Options. Whenever a new Option is inserted, for a certain ProductId, look for all projects having that ProductID, and add the corresponding (ProductId, OptionID, DefaultValueForOptionID /* or NULL */) entries into your ProjectOption table.

Apart from doing this via triggers, you may consider doing the same at the application layer. If the user inputting the new options needs to perform something (like setting specific values for existing projects), this cannot be handled at the database layer.


Convert rows into columns MySQL DataBase I have two tables and I would like to generate a query relating the tables.
Until now I have this query:

SELECT Table1.*, table2.* FROM Table1 INNER JOIN table2 ON table2.FK_ID = Table1.ID

but I return the 3 rows repeating the data in table 1.

I would like my query to return the data as follows (see image)

Any suggestions to see if it is possible to make my requirement?

Thank you!

===============================

UPDATE

I Tried with this query

SELECT  Table1.*,
    (SELECT Table2.Columna2 WHERE Table2.Columna4='ALTA') AS Name1,
    (SELECT Table2.Columna2 WHERE Table2.Columna4='ATENCION') AS Name2
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.FK_ID
GROUP BY Table1.ID

but in my Name2 always is NULL

SELECT  Table1.*,
    (SELECT Table2.Columna2 WHERE Table2.Columna4='ALTA') AS Name1,
    (SELECT Table2.Columna2 WHERE Table2.Columna4='ATENCION') AS Name2
    (SELECT Table2.Columna2 WHERE Table2.Columna4='CLIENTE') AS Name3
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.FK_ID

is what you want.

But there are other ways to achieve this.


New to mySQL Relationships I'm new to mySQL relastionships and I'm wondering if you can help me out.

This is what I want to do:

Users Table

user_id
user_name
pass_word
permission_id

Permissions Table

p_id
permission_name
permission_type

I want to create a relationship between p_id (permissions table) & permission_id (user table) so when I query the user table it also brings through the corresponding permission name & type?

Is this possible or am I getting it all wrong?

Should I just use joins?

SELECT  u.user_id,
        u.user_name,
        u.pass_word,
        p.permission_name,
        p.permission_type
FROM Users u
  INNER JOIN Permissions p
ON u.permission_id  = p.p_id;

Max.


Is it possible to Increase storage performance in azure vm using disk striping? Given the following Azure VM:

E8s_v3 (8 cpu, 64 GB RAM, 12800 IOPS, 192 MBps)
I added 2 SSD Premium drives, one for data (1024 GBs / 200 MBps / Host Caching enabled read-only) and one for Logs (256 GBs / 125 MBps / No Cache enabled)

When I perform a simple test with CrystalDiskMark I get 134 MBps for the data disk and 127 MBps for the log file. I assume that the data disk is not getting closed to 192 MBps (Azure VM limit) because of the enabled host caching.

My question is: if I use disk striping, let's say, instead of 1 TB disk, I add 2 512 GBs (150 MBps); would that give me around ~300 MBps?? Or simply there's no way that one volume (stripe or not), inside the azure VM, runs faster than the limit set by the VM size, 192 MBps for our example VM model: E8s_v3?

Any idea on how to improve storage performance? The only way I see would be to increase the VM size and the disk size but of course, that would increase our cost a lot. In our local servers, we have really fast SANs that are around thousands of MBps and it's impossible to get those speed in the cloud at a rational cost, if ever.

Thanks!

I'm going to preface my answer by indicating I tried this out a few years ago during a demo of Azure's initial IaaS offerings so things have almost certainly changed.

Yes, you can provision multiple disks and create a spanned volume but I'd also check the segment size of the LUNs. If you're looking for high throughput to scan in tables like for building OLAP cubes on entire fact tables, be sure to indicate a high segment size like 256K. My first IaaS test was with the default 64k segment size better used with OLTP workloads and performance was negatively impacted as a result.


See actual results of SQL UPDATE command Is there a way to get the actual results of SQL UPDATE command (ie the table rows themselves), not just get the number of rows (other than by converting the UPDATE command to a SELECT)? I'm using mainly Postgres and Informix, but I'd be interested to hear about other database systems that have this feature, or perhaps a particular client that supports this.

Here's how you'd do something like that in SQL Server.  Because an UPDATE is essentially old data and new data, SQL Server exposes the deleted and inserted dynamic tables.  You can work with them to get what the data looked like prior to the UPDATE, and after the UPDATE:

update HumanResources.Department
set GroupName = 'Testing Group Name'
output
    deleted.DepartmentID as Old_DepartmentID,
    inserted.DepartmentID as New_DepartmentID,
    deleted.Name as Old_Name,
    inserted.Name as New_Name,
    deleted.GroupName as Old_GroupName,
    inserted.GroupName as New_GroupName
where DepartmentID > 12


CHARINDEX equivalent in DB2 I am trying to select the domain from the column where I have email addresses.

For example, I have a table email_table and a column email with email addresses as below.

johndoe@gmail.com
jhefid@yahoo.com
ipw@gmx.com

Expected result

gmail.com
yahoo.com
gmx.com

Here is the query I tried with CHARINDEX that works in SQL Server but not in DB2.

select CHARINDEX('@', email) from email_table

It's LOCATE (in DB2) which is equivalent to CHARINDEX in SQL Server.

Here is the complete description of it with example.

Thanks!


Backup TO URL WITH NOINIT fails because file already exists There's a similar question here but I cannot work out the solution because the circumstances are slightly different, so please bear with me; I've been wresting with the official help docs all day.
My goal is to have one backup file per database per day, with 15 minute recovery objective.  I am scheduling agent jobs as follows:

Once a day at 00:00, create a full backup of each database specified
Every 15 minutes, create a transaction log backup of each database specified

Ignoring all the code to iterate through the required databases, this is the core logic of part 1 above:
    DECLARE @dbName nvarchar(50) = (SELECT DatabaseName FROM #DbsToBackup WHERE (Id = @current));
    DECLARE @dbNameAndDate nvarchar(200) = @dateString + '_' + @dbName;
    DECLARE @dbSpecificContainerUrl  nvarchar(MAX) = @containerUrl + @dbNameAndDate + '.bak';
    DECLARE @verifyError nvarchar(200) = N'Verify failed. Backup information for database ' + @dbName + ' not found.';

    BACKUP DATABASE @dbName
         TO URL = @dbSpecificContainerUrl
         WITH CREDENTIAL = @containerCredential
        ,NOINIT
        ,NAME = @dbNameAndDate
        ,COMPRESSION
        ,CHECKSUM
        ;

Then, the transaction log backup logic is as follows (same steps for grabbing the database name, date strings, etc.:
    BACKUP LOG @dbName
         TO URL = @dbSpecificContainerUrl
         WITH CREDENTIAL = @containerCredential
        ,NOINIT
        ,NAME = @dbNameAndDate
        ,SKIP
        ,COMPRESSION
        ,CHECKSUM
    ;

Part 1 works on the first attempt, but part 2 (and any subsequent re-run of part 1) immediately fails with this:

A nonrecoverable I/O error occurred on file "https://.....Test.bak:"
The file https://....Test.bak exists on the remote endpoint, and WITH
FORMAT was not specified.  Backup cannot proceed..  [SQLSTATE 42000]
(Error 3271)  BACKUP LOG is terminating abnormally.  The step failed.

I get that the .bak file is already there, but I thought that NOINIT forced an append which would add the transaction log data into the existing .bak file.
I'm probably missing something simple but can somebody please advise?

Based on the documentation, it seems that is not supported.

Appending to existing backup blobs is not supported. Backups to an
existing blob can only be overwritten by using the WITH FORMAT option.
However, when using file-snapshot backups (using the WITH
FILE_SNAPSHOT argument), the WITH FORMAT argument is not permitted to
avoid leaving orphaned file-snapshots that were created with the
original file-snapshot backup.


MariaDB Frequently Restarting MariaDB is frequently (multiple times a day) restarting.

This is what I get in the mariadb.log

200411 18:38:51 mysqld_safe Number of processes running now: 0
200411 18:38:51 mysqld_safe mysqld restarted
200411 18:38:53 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.HWm8QZ' --pid-file='/var/lib/mysql/db-recover.pid'
200411 18:38:54 [Note] /usr/sbin/mysqld (mysqld 10.0.33-MariaDB-wsrep) starting as process 16103 ...
200411 18:39:01 mysqld_safe WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1
200411 18:39:01 [Note] /usr/sbin/mysqld (mysqld 10.0.33-MariaDB-wsrep) starting as process 16162 ...
200411 18:39:01 [Note] InnoDB: Using mutexes to ref count buffer pool pages
200411 18:39:01 [Note] InnoDB: The InnoDB memory heap is disabled
200411 18:39:01 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
200411 18:39:01 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
200411 18:39:01 [Note] InnoDB: Compressed tables use zlib 1.2.7
200411 18:39:01 [Note] InnoDB: Using Linux native AIO
200411 18:39:01 [Note] InnoDB: Using CPU crc32 instructions
200411 18:39:01 [Note] InnoDB: Initializing buffer pool, size = 4.4G
200411 18:39:02 [Note] InnoDB: Completed initialization of buffer pool
200411 18:39:02 [Note] InnoDB: Highest supported file format is Barracuda.
200411 18:39:02 [Note] InnoDB: 128 rollback segment(s) are active.
200411 18:39:02 [Note] InnoDB: Waiting for purge to start
200411 18:39:02 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.36-82.2 started; log sequence number 523546041827
200411 18:39:02 [Note] Plugin 'FEEDBACK' is disabled.
200411 18:39:02 [Note] Server socket created on IP: '::'.
200411 18:39:02 [Warning] 'proxies_priv' entry '@% root@site-mariadb' ignored in --skip-name-resolve mode.
200411 18:39:02 [Note] WSREP: Read nil XID from storage engines, skipping position init
200411 18:39:02 [Note] WSREP: wsrep_load(): loading provider library 'none'
200411 18:39:02 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.0.33-MariaDB-wsrep'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server, wsrep_25.21.rc3fc46e

Any ideas what could be going on?

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section

max_connect_errors=10  # from 1 Million to frustrate hackers/crackers at 10 attempts
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
read_buffer_size=256K # from 128K to reduce handler_read_next RPS of 13,569
aria_pagecache_age_threshold=900  # from 300 to delay age out and reduce aria_pagecache_reads RPS of 1
connect_timeout=20  # from 10 to reduce aborted_connects RPhr of 60

There are more opportunities to improve your configuration.  Make contact, please.


Why can't I insert this value using timestamp but can with datetime? I want to insert this value: 2020-03-29 01:00:00

If I set up a column called date with data type datetime I can insert the value. But if I use the data type timestamp I cannot insert and get an error message:

  Incorrect datetime value: '2020-03-29 01:00:00' for column 'date' at row 1

Furthermore, if I insert 2020-03-29 02:00:00 using data type timestamp it works... seriously, what is going on here?

I am using the GMT timezone. I'm creating a 2020 calendar table where I want to insert every hour in a year. Should I skip that time?

If you want to catch every hour that exists, have a sequence of integers and do

x + INTERVAL y HOUR

Where x is some constant base time and y is the sequence of hours.  The result of the sum is a valid hour, suitably converted according to DST hours, leap years, etc.


How can I retrieve the column names of a table in mysql? I have a table with n columns.

I want to retrieve the first 3 column names of that table.

Should be something like this:

Select column_name(3) from table_name="sample";


How to count all relationships in a database? I am developing a web application, which stores data in a Microsoft SQL Server database. My project manager has to deliver a report to non-tecnhical higher-ups, and thinks that it will read impressive if we include the sentence "Our database has X tables, connected by Y relationships".

I just counted the tables by hand in the treeview of Management Studio. But how do I get a count of all relationships in the database? I'd prefer something quicker than generating the schema and ticking off each connector by hand.

SELECT COUNT(*) FROM information_schema.tables WHERE table_type = 'BASE TABLE' --counts all tables
SELECT COUNT(*) FROM information_schema.referential_constraints --counts all FK relationships


SSIS How can I write to a Specific Cell in an Excel Sheet I am trying to complete what I thought would be a very simple task but after hours of looking thru various articles and attempting different methods, I still have not been able to Write to a specific Cell using SSIS.

All I am trying to do is write "DOB" in cell D2 in an excel sheet.

I tried using SQL COMMAND in the Execute SQL Task componenet to do the UPDATE of the 1 cell but kept getting error messages. Below is the code I tried. SSIS came back with an error saying it was expecting at least 1 paramater...

update [Sheet1$D2:D2] SET F1='DOB'

I also tried

INSERT INTO [Sheet1$D2:D2] VALUES ('DOB')

but got the following error message:

  This table contains cells that are outside the range of cells defined in this spreadsheet.

I tried a few different C# and VB scripts but none of them did the trick.  Any ideas or suggestions?

I tried modifying the script in the below article to accomplish my task but was unsuccessful

http://bidn.com/blogs/KeithHyer/bidn-blog/2475/updating-a-single-excel-cell-using-ssis

I'm thinking there's got to be an easier way.

INSERT INTO [Sheet1$D2:D2] VALUES ('DOB')

Worked. It appears that you can only use UPDATE if there is already data in the cell and you can only use INSERT if the cell is blank (which it was in my case).

There was a lot of very useful information relating to this topic in the following link

http://www.winautomation.com/forum/updating-excel-through-sql-statement


How to select data out of an Oracle collection/array? Due to needing some complex data manipulation and not being able to use PHP, I crafted a procedure (not stored, it's just run when we run the query) in Oracle to manipulate the data and store it in an associative array:

TYPE changedData IS RECORD (id int, name varchar2(255), vendor_id int);
TYPE changedDataArray IS TABLE OF changedData INDEX BY varchar2(255);

And I've populated it and it has all the data, awesome.

So how do I get that sent back to the client resembling rows? I could do DBMS_OUTPUT but that doesn't help when I'm trying to run this by a program expecting a tabular response. I've looked all over the internet and I'm at a loss.

Is making a temporary table and inserting the data into that and then selecting the only method? I'd do that except I don't have access to create a temporary table on the server, and getting permissions here is pulling teeth so if I can avoid that, it'd be awesome. I mean, I have the array right there - changedDataArray. And yet there seems to be no way to actually output that in a tabular fashion to the client?

You can create a pipelined function.
Some examples here.


Will large transaction log file slow db recovery? Did a manual failover after windows updates to passive node today and recovery was fast for all but one db. The one slow db had a transaction log size of 203200.00 mb. Log Space percent used = 0.25.  Will the large transaction log file size slow down recovery?

Not directly, but if there was a large transaction running at the time of failover that could cause both a large log file and a slow failover.


Remove duplicate records from table, leaving one copy I have inherited a MySQL table from one of my colleagues, and am in need of a little advice.

The table has the following structure:

| PHONE_NO   | ACC_NO | GET_MAIL | DATETIME_CRE         | INT_ACC | MAIL |
| 0113313313 | 000001 |   YES    | 2003-01-01 19:00:00  | 9000001 | N/A  |

There is no primary key, as the table was not created with one. I cannot recreate the table as:

It would take far too long to complete (over 2 million records and counting)
This is a mission critical table, meaning taking it down would result in my client losing money for the duration.

I need to be able to remove duplicated rows from the table with a query, without inserting a primary key. "Duplicated" in this sense refers to all columns within the row being identical to another row.

EDIT
This is the result of show create table query, as requested by  ypercube;

CREATE TABLE `callerQue` (
  `PHONE_NO` varchar(30) DEFAULT NULL,
  `ACC_NUMBER` varchar(40) DEFAULT NULL,
  `GET_MAIL` varchar(6) DEFAULT NULL,
  `DATETIME_CRE` datetime DEFAULT NULL,
  `INT_ACC` varchar(40) DEFAULT NULL,
  `MAIL` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

Not sure if this would work in mysql since i am a MSSQL-dba but you could add an identity column -> auto_increment.
This would make all rows unique.
Then you could do a group by on duplicate records and select the highest ID for example. This way you will only delete one row.
Repeat this until no more duplicates.


SQL Calculated Field in both SELECT and GROUP BY clause Often in querying my MS SQL Server databases, I need to create a calculated field, such as this

(CASE WHEN A.type = 'Workover' THEN 'Workover'
      ELSE (CASE WHEN substring(C.category, 2, 1) = 'D' THEN 'Drilling'
                 WHEN substring(C.category, 2, 1) = 'C' THEN 'Completion'
                 WHEN substring(C.category, 2, 1) = 'W' THEN 'Workover'
                 ELSE 'Other'
            END)
END)

and then I need to group my results by this calculated field (among others).  Hence, I have the same calculation in both the SELECT and GROUP BY clauses.  Is SQL server actually performing these calculations twice, or is it smart enough to only do it once?

I have the same calculation in both the SELECT and GROUP BY clauses. Is SQL server actually performing these calculations twice, or is it smart enough to only do it once?

The simple answer is that SQL Server makes no general guarantees about when, and how many times, a scalar expression will be evaluated at execution time.

There are all sorts of complicated (and undocumented) behaviours within the optimizer and execution engine regarding the placement, execution, and caching of scalar expressions. Books Online doesn't have much to say about this, but what it does say is this:

This describes one of the behaviours I alluded to before, deferred execution of expressions. I wrote about some of the other current behaviours (which could change any time) in this blog post.

Another consideration is that the cost model used by the query optimizer doesn't currently do much in the way of cost estimation for scalar expressions. Without a robust costing framework, current results are based on broad heuristics or pure chance.

For very simple expressions, it probably doesn't make much difference whether the expression is evaluated once or many times in most cases. That said, I have encountered large queries where performance has been adversely effected when the expression is redundantly evaluated a very large number of times, or the evaluation occurs on a single thread where it would have been advantageous to evaluate in a parallel branch of the execution plan.

In summary, the current behaviour is undefined, and there is nothing much in execution plans to help you figure out what happened (and it will not always be convenient to attach a debugger to examine the detailed engine behaviours, as in the blog post).

If you encounter cases where scalar evaluation issues matter to performance, raise the issue with Microsoft Support. This is the best way to provide feedback to improve future versions of the product.


SQL Server: affect other transactions? I'd be surprised if this existed, but will ask just in case. Is there some mechanism which would allow one transaction to affect another (outside) transaction? For example:

BEGIN TRY
  BEGIN TRAN
    -- Run updates 01
    INSERT INTO dbo.Log (Text) VALUES ('Updates 01 - big success');

    -- Run updates 02
    INSERT INTO dbo.Log (Text) VALUES ('Updates 02 - big success');

    -- and so on...
  COMMIT
END TRY
BEGIN CATCH
  ROLLBACK
  INSERT INTO dbo.Log (Text) VALUES ('At a certain step there was a problem: ' + ERROR_MESSAGE());
END CATCH

Of course, in case of errors we lose the success log entries done inside the transaction and are left only with the last (error) message inserted after the rollback took place.
Is there a way to make those INSERT INTO dbo.Log statements "persistant" before the transaction finishes?
I guess, if it were, it would fly in the face of proper transaction control, so I on some level hope this doesn't exist, but maybe somewhere in the category of tricks?

Thank you.

Assuming they are running on the same session, no, as you've already said. Are there ways around it? Sure, take it out of that session by plopping the messages in a service broker queue with an activated stored procedure. The only part I'm not quite sure on is if the WITH LOG part of raiserror would be subject to that or not - though you probably don't want to write the results to the errorlog.

It could also be shortened so that each part is its own transaction but that may not be suitable for your needs.


With PostgreSQL getting ERROR: COPY format "CSV" not recognized Whever I try to copy from

test=# \COPY table FROM 'file.csv' WITH ( FORMAT 'CSV', HEADER true );
ERROR:  COPY format "CSV" not recognized

What's the reason for this? I know I've used this before.

If format is a string, the string is case sensitive,

test=# \COPY table FROM 'file.csv' WITH ( FORMAT 'csv', HEADER true );

But, the format doesn't have to be a string,

test=# \COPY table FROM 'file.csv' WITH ( FORMAT CSV, HEADER true );

You're probably use to using the non-stringified version. The syntax for the copy_options is pretty hectic,

FORMAT format_name
OIDS [ boolean ]
FREEZE [ boolean ]
DELIMITER 'delimiter_character'
NULL 'null_string'
HEADER [ boolean ]
QUOTE 'quote_character'
ESCAPE 'escape_character'
FORCE_QUOTE { ( column_name [, ...] ) | * }
FORCE_NOT_NULL ( column_name [, ...] )
FORCE_NULL ( column_name [, ...] )
ENCODING 'encoding_name'

A lot of them only accept strings, but some of them accept regular tokens which get lowercased.

This specific thing isn't documented explicitly but you can find more information about COPY here