Very common mysql query sometimes does not use index As far as I can tell, for some reason a small fraction of very common queries fail to use indexes. If all such queries failed to use indexes, the server would crash within 1 minute hence only a few such queries behave like this.

How should I prevent these occasional slow queries which in vast majority of cases work fine?

My create tables are:

CREATE TABLE msgs (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  sender text NOT NULL,
  receiver text NOT NULL,
  cont blob NOT NULL,
  img text NOT NULL,
  orient text NOT NULL,
  d_t datetime NOT NULL,
  convo text NOT NULL,
  u_code text NOT NULL,
  viewed datetime NOT NULL,
  stat int(11) NOT NULL,
  device text NOT NULL,
  addr text NOT NULL,
  PRIMARY KEY (id),
  KEY msg_u_code (`u_code`(24)),
  KEY receiver (`receiver`(24)),
  KEY sender (`sender`(24)),
  KEY img (`img`(28)),
  KEY convo (`convo`(49))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE usrs (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  usr_name text NOT NULL,
  img text NOT NULL,
  orient text NOT NULL,
  `password` text NOT NULL,
  u_code text NOT NULL,
  d_t datetime NOT NULL,
  stat int(11) NOT NULL,
  device text NOT NULL,
  addr text NOT NULL,
  PRIMARY KEY (id),
  KEY img (`img`(28)),
  KEY usr_code (`u_code`(24))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

And my slow query log entry is:

 # Time: 171115  6:26:37
 # User@Host: xxx[xxx] @ localhost []
 # Thread_id: 25524888  Schema: xxx  QC_hit: No
 # Query_time: 32.423430  Lock_time: 0.000425  Rows_sent: 1  Rows_examined: 30998008
 # Rows_affected: 0
 use xxx;
 SET timestamp=1510723597;
 select msg_cont, msg_u_code, msg_d_t, msg_viewed, usr_u_code, usr_name from
        (select
            msgs.id as msg_id,
            msgs.cont as msg_cont,
            msgs.u_code as msg_u_code,
            msgs.d_t as msg_d_t,
            msgs.viewed as msg_viewed,
            usrs.u_code as usr_u_code,
            usrs.usr_name as usr_name
            from msgs
            left join usrs on msgs.sender = usrs.u_code
    where  msgs.convo = 'aaaaaaaaaabfbaghdgcigfid_aaaaaaaaaabeiaccjfhjfach'
      and  (msgs.sender = 'aaaaaaaaaabfbaghdgcigfid'
              or  msgs.receiver = 'aaaaaaaaaabfbaghdgcigfid'
           )
      and  msgs.stat = '1'
      and  usrs.stat = '1'
      and  usrs.u_code not in('aaaaaaaaaabfaagfbgggiejh',
                              'aaaaaaaaaabfabgbjdfjigbd',
                ...... !!!!![here go 400 more usr_u_codes]!!!!!
                          )
      and  msgs.id > 30997997
        ) a order by msg_id asc;

While I am unable to explain why MySQL occasionally decided not to use index on a very obvious and very common query, the solution was to simply force the index.

In my particular case:

select msg_cont, msg_u_code, msg_d_t, msg_viewed, usr_u_code, usr_name from
        (select
            msgs.id as msg_id,
            msgs.cont as msg_cont,
            msgs.u_code as msg_u_code,
            msgs.d_t as msg_d_t,
            msgs.viewed as msg_viewed,
            usrs.u_code as usr_u_code,
            usrs.usr_name as usr_name
            from msgs FORCE INDEX (convo)
            left join usrs FORCE INDEX (u_code) on msgs.sender = usrs.u_code
    where  msgs.convo = 'aaaaaaaaaabfbaghdgcigfid_aaaaaaaaaabeiaccjfhjfach'
      and  (msgs.sender = 'aaaaaaaaaabfbaghdgcigfid'
              or  msgs.receiver = 'aaaaaaaaaabfbaghdgcigfid'
           )
      and  msgs.stat = '1'
      and  usrs.stat = '1'
      and  usrs.u_code not in('aaaaaaaaaabfaagfbgggiejh',
                              'aaaaaaaaaabfabgbjdfjigbd',
                ...... !!!!![here go 400 more usr_u_codes]!!!!!
                          )
      and  msgs.id > 30997997
        ) a order by msg_id asc;

Slow query log proved this solution to be effective as no new slow entries emerge.


Seemingly simple query...excruciating response time I am in the process of running what seems to be a simple query and have yet to receive a result set. The purpose of the query is to obtain a list of all records within a table that share the same PostalCode. The table has roughly 75K rows. The PostalCode column is an indexed varchar (I had considered making this table an integer type but wanted to keep our options open for countries that use letters within their postalcodes). The query has been running about 10 minutes now and has yet to return a result. Have I done something horribly wrong???

SELECT * FROM t502locations AS t502
JOIN(

    SELECT PostalCode FROM t502locations
    GROUP BY PostalCode
    HAVING(COUNT(PostalCode) > 1)

) AS vt502

ON t502.PostalCode = vt502.PostalCode

I would use LEFT JOIN with vt502 on the left side

SELECT t502.* FROM
(
    SELECT PostalCode FROM t502locations
    GROUP BY PostalCode
    HAVING COUNT(PostalCode) > 1
) vt502 LEFT JOIN t502locations t502
ON vt502.PostalCode = t502.PostalCode;

or

SELECT t502.* FROM
(
    SELECT PostalCode FROM t502locations
    GROUP BY PostalCode
    HAVING COUNT(PostalCode) > 1
) vt502 LEFT JOIN t502locations t502
USING (PostalCode);

Chances are, the number of PostalCodes with multiple records is probably so high that the MySQL (MariaDB) Query Optimizer does not like.

At the very least, I would run OPTIMIZE TABLE t502locations; and try these queries.


UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job In brief, I added a computed column to a table and then Agent jobs began failing and reporting the error:

  UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).

An earlier question suggests that stored procedures must be created with QUOTED_IDENTIFIER set ON. In my case, however, there are no stored procedures. One example Agent job has one step and that contains straight SQL to carry out two updates - each update is against the table I altered. None of that SQL contains a double-quote character (which is the subject of QUOTED_IDENTIFIER).

Microsoft documentation says:

  SET QUOTED_IDENTIFIER must be ON when you are creating or changing
  indexes on computed columns or indexed views.

... however I simply added the column (persisted). I have not created or altered any indexes.

Why am I getting this error in this scenario?
How do I resolve this? Do I need to re-create all the Agent jobs, switching QUOTED_IDENTIFIER ON before I do? Or do I need to switch QUOTED_IDENTIFIER ON before altering the table to add the computed column?

Possibly related, the following SQL shows which objects in the database had QUOTED_IDENTIFIER ON at compile time (ie. which will be interpreted with that value when used). From this I see that triggers are also affected by this setting. Perhaps the Agent job update is invoking a trigger which is somehow related? I do note, however, that all objects are listed as uses_quoted_identifier = 1. In other words, if the resolution was to re-create objects while setting QUOTED_IDENTIFIER ON prior to re-creating them, I don't see what difference it would make; all objects already seem to have been created that way.

SELECT uses_ansi_nulls, uses_quoted_identifier, name, *
 FROM sys.sql_modules -- WHERE object_id = object_id('SampleProcedure')
 left outer join sys.sysobjects on sql_modules.object_id = sysobjects.id

Why am I getting this error in this scenario?

The agent itself is switching QUOTED_IDENTIFIER to OFF.

I have written about it here (mind the unreadable font, not my choice).

Here are the tests run on sql server 2012 to sql server 2017:

If I run this query in a ssms query window:

DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF';
IF ( (256 & @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON';
SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;

The result shows as on:

QUOTED_IDENTIFIER
ON

When I put the query to check the quoted identifier in a job and run it, this happens:

create table dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER varchar(3));

DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF';
IF ( (256 & @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON';
SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;

insert into dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER)
select @QUOTED_IDENTIFIER;

View the results:

select * from dbo.QUOTEDIDENTIFIER_CHECK;

Result:

QUOTEDIDENTIFIER
OFF

When tracing the agent, you can clearly see the statement that turns quoted identifier off.

  How do I resolve this? Do I need to re-create all the Agent jobs,
  switching QUOTED_IDENTIFIER ON before I do?

To resolve this you have to add explicit SET QUOTED_IDENTIFIER ON; statements to the agent job steps.


What is the SQL Server equivalent of MySQL's utf8mb4 character set? We have some web applications based on databases using utf8mb4 as character set and utf8mb4_Standard as collation.

We saw that we can use any character we want with this setting.

In SQL Server Express the situation is not very clear to me.

When I switch to Standard it chooses the Latin1_General_CI_AS collation.

But I do not know which character encoding this is and how it would affect the scenario if we would like to take over some data from utf7mb8 MySQL tables into SQL Server.

When I look into the datatype definition in SQL Server, I can see that there are Unicode and non Unicode types. So I am wondering if the collation actually has impact into how it is stored:

It seems that if you use nchar, nvarchar or nvarchar(max) you are on the safe side as you use UTF-16.

But, what does the collation Latin1_General_CI_AS mean?

Especially how would this behave if you had Chinese characters, for example?

First: the particular "edition" of SQL Server (i.e. Express, Standard, Enterprise, etc) does not matter. All editions for a particular version will behave the same.

  When I switch to "Standard" it chooses the Latin1_General_CI_AS collation.

Well, that is better than a collation starting with SQL_, but still not ideal. If you are using SQL Server 2008 or newer, then you should be using a version 100 collation (or a  version 140 collation IF using SQL Server 2017 or newer and specifying a Japanese collation). AND, if using SQL Server 2012 or newer, then you should be using a collation that supports supplementary characters, which means your choices are:

version 100 collations with names ending in _SC, or
version 140 collations (only Japanese collations have a version 140 set, but none of these end in _SC since supplementary character support is built into all version 140 collations)

In your case, most likely you want: Latin1_General_100_CI_AI_SC

Technically speaking, the closest equivalent to utf8mb4_general_ci (there is no utf8mb4_Standard, and your screen shot even shows utf8mb4_general_ci) is Latin1_General_CI_AI. Reason being:

The utf8mb4 character set allows you to store supplementary characters (which NVARCHAR also does, regardless of collation),
the general part of the MySQL collation means that supplementary characters all have the same weight. This pre-level 100 SQL Server collation is similar in that all supplementary characters have the same weight, it's just that they have no weight at all.
the ci in the MySQL collation implies ai since as isn't specified.

Still, you want to stick with: Latin1_General_100_CI_AI_SC.

  I do not know which character encoding this is and how it would affect the scenario if we would like to take over some data from utf7mb8 MySQL tables into SQL Server.

The encoding is determined by a combination of the datatype and the collation:

NVARCHAR (and NCHAR / NTEXT) is always UTF-16 LE (Little Endian).
VARCHAR (and CHAR / TEXT) is always an 8-bit encoding, the specific encoding determined by the code page associated with the collation being used.

That said, it shouldn't matter what the source encoding is as long as the destination encoding can handle all of the incoming characters (and behave in a similar manner, of course, which is where the culture and sensitivities come in). Assuming you will be storing everything in NVARCHAR (maybe occasionally NCHAR, but never NTEXT as that has been deprecated since SQL Server 2005), then the data transfer tool will handle any necessary conversions.

  what does the collation Latin1_General_CI_AS mean?

It means:

Because the name does not start with SQL_, this is a Windows collation, not a SQL Server collation (and this is a good thing as the SQL Server collations — those starting with SQL_ — are mainly for pre-SQL Server 2000 compatibility, although quite unfortunately SQL_Latin1_General_CP1_CI_AS is very common due to it being the default when installing on an OS using US English as its language)
Latin1_General is the culture / locale.

For NVARCHAR data this determines the linguistic rules used for sorting and comparison.
For VARCHAR data this determines the:

linguistic rules used for sorting and comparison.
code page used to encode the characters. For example, Latin1_General collations use code page 1252, Hebrew collations use code page 1255, and so on.

{version}, while not present in this collation name, refers to the SQL Server version in which the collation was introduced (for the most part). Windows collations with no version number in the name are version 80 (meaning SQL Server 2000 as that is version 8.0). Not all versions of SQL Server come with new collations, so there are gaps in the version numbers. There are some that are 90 (for SQL Server 2005, which is version 9.0), most are 100 (for SQL Server 2008, version 10.0), and a small set has 140 (for SQL Server 2017, version 14.0).

I said "for the most part" because the collations ending in _SC were introduced in SQL Server 2012 (version 11.0), but the underlying data wasn't new, they merely added support for supplementary characters for the built-in functions. So, those endings exist for version 90 and 100 collations, but only starting in SQL Server 2012.
Next you have the sensitivities, that can be in any combination of the following, but always specified in this order:

CS = case-sensitive or CI = case-insensitive
AS = accent-sensitive or AI = accent-insensitive
KS = Kana type-sensitive or missing = Kana type-insensitive
WS = width-sensitive or missing = width insensitive
VSS = variation selector sensitive (only available in the version 140 collations) or missing = variation selector insensitive

Optional last piece:

_SC at the end means "Supplementary Character support". The "support" only affects how the built-in functions interpret surrogate pairs (which are how supplementary characters are encoded in UTF-16). Without _SC at the end (or _140_ in the middle), built-in functions don't see a single supplementary character, but instead see two meaningless code points that make up the surrogate pair. This ending can be added to any non-binary, version 90 or 100 collation.
_BIN or _BIN2 at the end means "binary" sorting and comparison. Data is still stored the same, but there are no linguistic rules. This ending is never combined with any of the 5 sensitivities or _SC. _BIN is the older style, and _BIN2 is the newer, more accurate style. If using SQL Server 2005 or newer, use _BIN2. For details on the differences between _BIN and _BIN2, please see: Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2).
_UTF8 is a new option as of SQL Server 2019. It's an 8-bit encoding that allows for Unicode data to be stored in VARCHAR and CHAR datatypes (but not the deprecated TEXT datatype). This option can only be used on collations that support supplementary characters (i.e. version 90 or 100 collations with _SC in their name, and version 140 collations). There is also a single binary _UTF8 collation (_BIN2, not _BIN).

PLEASE NOTE: UTF-8 was designed / created for compatibility with environments / code that are set up for 8-bit encodings yet want to support Unicode. Even though there are a few scenarios where UTF-8 can provide up to 50% space savings as compared to NVARCHAR, that is a side-effect and has a cost of a slight hit to performance in many / most operations. If you need this for compatibility, then the cost is acceptable. If you want this for space-savings, you had better test, and TEST AGAIN. Testing includes all functionality, and more than just a few rows of data. Be warned that UTF-8 collations work best when ALL columns, and the database itself, are using VARCHAR data (columns, variables, string literals) with a _UTF8 collation. This is the natural state for anyone using this for compatibility, but not for those hoping to use it for space-savings. Be careful when mixing VARCHAR data using a _UTF8 collation with either VARCHAR data using non-_UTF8 collations or NVARCHAR data, as you might experience odd behavior / data loss. For more details on the new UTF-8 collations, please see: Native UTF-8 Support in SQL Server 2019: Savior or False Prophet?

  Especially how would this behave if you had Chinese characters, for example?

If you are storing those characters in a VARCHAR column or variable, you would most likely lose those characters as they get converted to ?. There are a few Chinese locale collations that use a double-byte character set (DBCS) for VARCHAR that can store more than 256 different characters, but it's still not even close to what is available in Unicode.
If you are storing those characters in an NVARCHAR column or variable, there would be no data loss. However, with the Latin1_General culture / locale (Western European / US English), you won't get any Chinese-specific linguistic rules, so sorting and comparisons of the Chinese characters (anything different than what is in the default definition) will likely not behave as would be appropriate for that language. In which case you simply use a Chinese collation, version 100, and with _SC in the name.


After Oracle 11gR2 restore & recover database, "ghost" NOLOGGING corrupt blocks I recently restored (from full, level 0 cold backup) an ARCHIVELOG database & recovered it to a point in time about 24 hours after backup. I ran RMAN validate and numerous blocks turned up in V$DATABASE_BLOCK_CORRUPTION with corruption of NOLOGGING.  This was anticipated as database has frequent NOLOGGING load operations.  The game-plan was to map V$DATABASE_BLOCK_CORRUPTION blocks to segments (table partitions), truncate partition, and replay the loads of those data sets from source files.

In most cases, this worked perfectly; most entries in V$DATABASE_BLOCK_CORRUPTION mapped directly to NOLOGGING table partitions which would've been loaded via INSERT /*+APPEND*/ after the cold backup.

Only wrench: V$DATABASE_BLOCK_CORRUPTION shows about 100 blocks with NOLOGGING corruption that do not match any entries in DBA_EXTENTS (they are, presumably, unused blocks).

1) Any idea what caused these blocks to become corrupt?
2) As they do not map to any extents in use, can I safely ignore them?
3) The OCD question: if these blocks aren't problematic, is there a way to clear them from V$DATABASE_BLOCK_CORRUPTION?

Presumably there was data in them at one point, which has since been deleted.
If the corrupted blocks are in free space, there is no need to take any action. Oracle will reformat the block when it attempts to use the space. So you can ignore the alerts.
If you want to clear the corrupt blocks to prevent more alerts, create a nologging table in the relevant tablespace. Insert a lot of rows until the corrupt blocks disappear, then drop the table.


Find out where length semantics gets changed I've code to create tables that doesn't specify length semantics:

CREATE TABLE CHAR_TEST (
    CHAR_TEST_ID NUMBER(*,0) NOT NULL,
    FOO VARCHAR2(6) NOT NULL,
    BAR VARCHAR2(6) NOT NULL,

    CONSTRAINT CHAR_TEST_PK PRIMARY KEY (CHAR_TEST_ID) ENABLE
);

Actual code is fairly large and contains hundreds of objects. I have two versions of the code that correspond to different source control revisions separated by a couple of years.

I've created 2 users/schemas on the same server with identical settings. I then run each revision in each respective schema. To my surprise, the latest revision gets an unexpected CHAR semantic:

Name         Null     Type
------------ -------- -----------
CHAR_TEST_ID NOT NULL NUMBER(38)
FOO          NOT NULL VARCHAR2(6)
BAR          NOT NULL VARCHAR2(6)

Name         Null     Type
------------ -------- ----------------
CHAR_TEST_ID NOT NULL NUMBER(38)
FOO          NOT NULL VARCHAR2(6 CHAR)
BAR          NOT NULL VARCHAR2(6 CHAR)

I can't figure out where those CHAR come from!

I can't reproduce it if I run CREATE TABLE code isolately
I've been unable to find ALTER TABLE CHAR_TEST statements in my code base that change anything but keys or indexes
SELECT value FROM V$PARAMETER WHERE NAME LIKE '%nls_length_semantics%'; prints BYTE in both schemas

Could you please suggest where to look further?

I forgot the most obvious thing: session parameters can be changed!

I had this on the latest revision:

ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR';

... but not the older one. I was running the scripts in a different session thus I couldn't spot the value change.


AddWithValue performance and plan cache implications Recently I noticed my company's application using AddWithValueto pass parameter values to dynamic, parameterized queries. Example:

cmd.Parameters.AddWithValue("@VehicleId", Vehicles.VehicleId);

In the database, the data type for VehicleID is INT.

As I understand it, because AddWithValue (which is deprecated) does not specify the data type/length, this '@VehicleID' is converted and potentially converted incorrectly. Does that conversion affect SQL Server performance in the case of an 'INT'?

Would this then cause issues beyond plan cache pollution? Is there a performance hit caused by the conversion?

I strongly recommend always using the method that allows you to explicitly set the data type and precision. Yes, this is more work, both up front and if the data type in the database ever changes (which is common when, say, an IDENTITY column exceeds the range of int and it is upsized to bigint).

cmd.parameters.Add("@VehicleId", SqlDbType.Int).Value = Vehicles.VehicleId;

This is simply better practice, since other forms of AddWithValue() can cause issues. For example, the wrong types will be inferred here:

cmd.parameters.AddWithValue("@intParam", 5);        -- tinyint
cmd.parameters.AddWithValue("@intParam", 257);      -- smallint
cmd.parameters.AddWithValue("@varchar20", "bob");   -- nvarchar(3)
cmd.parameters.AddWithValue("@varchar20", "frank"); -- nvarchar(5)

In the int range you're unlikely to come across much in the way of problems, but if you extend this to other types, and are using ad hoc queries as opposed to stored procedure calls with strongly typed parameters, things can get very bad very quickly. As David pointed out, you can get bad plans, for example implicit conversions that can make seeks switch to scans. Allowing the default nvarchar to be passed through to a varchar column can cause massive performance issues, as Jonathan Kehayias demonstrates here. And allowing .NET to determine the length of the parameter based on the length of the string can - depending on version of SQL Server and parameterization settings - lead to a different plan for every unique length of string passed in. This won't make those specific queries slower, but they can waste a lot of memory, since there will be a redundant plan stored and used for each length.

This question has been asked in other places:

MSDN Forums
Stack Overflow

And Joel Coehoorn has blogged about the type conversion issues too, recommending you stay away from AddWithValue() (even in cases where you "know" the problem doesn't exist):

Can we stop using AddWithValue() already?


More trouble with STUFF I posted this a while back: Can't seem to understand why STUFF isn't working and @bluefeet was able to help me out very graciously...

Now I've got a similiar issue with this STUFF()!  Basically I have three tables one table has Company names Company.  Another table has Issues Issue and finally the third table has Issues that happened in a Company, call it PAndCIssueLocation.

For instance assume you have an issue that happened at two plants...so you end up with one Issue record like so:

Issue Table
ID  Title
1   Robot is down

And you've got a company table:

Company Table
ID  Name
1   Auburn Hills
2   Detroit

And finally the third table:

IssueID  CompanyID
1        1
1        2

That is Issue ID = 1 happened at two company locations (Detroit, Auburn Hills).  I was trying to use STUFF in a report so I can comma seperate these two items (company names) however, what is happening is the same company name is repeating itself X amount of times (I believe X is the total number of issues in my database).  Here is the code I have:

SELECT
    i.IssueID AS IssueID,
    i.PAndCAuditDate AS AuditDate,
    i.PAndCValidationCompletedOn AS ValidationCompletedOnDate,
    i.CustomerIssueNumber AS OldId,
    i.PAndCValidatedBy AS ValidatedBy,
    i.PAndCValidatedComments AS ValidatedComments,
    i.Title AS Title,
    i.OpenDate AS IssueOpenDate,
    i.PAndCAuditors AS Auditors,
    i.PAndCAuditees AS Auditees,
    i.Description AS Description,
    i.PAndCRootCause AS RootCause,
    i.PAndCContainmentAndCorrectiveActions AS ContainmentAndCorrectiveActions,
    i.PAndCPreventiveActions AS PreventiveActions,
    DATEDIFF(d, i.TargetDate, GetDate()) AS DaysOverdue,
    i.TargetDate AS IssueTargetDate,
    i.ClosedDate AS IssueClosedDate,
    CASE WHEN i.ClosedDate IS NULL AND DATEADD(d, DATEDIFF(d, 0, i.TargetDate), 0) < DATEADD(d, DATEDIFF(d, 0, GetDate()), 0) THEN 1 ELSE 0 END AS IsOverdue,
    s1.CompanyHasType
    --s1.Name
FROM
    Issue i
LEFT JOIN
(
SELECT c.IssueID, STUFF((
    SELECT ', ' + ct.Name + ' - ' + ct.ShippingAddressCity + ', ' + ct.ShippingAddressRegion + ' ' + ct.CountryID FROM Company ct
     INNER JOIN PAndCIssueLocation cht
     ON cht.CompanyID = ct.CompanyID INNER JOIN Issue c
     ON c.IssueID = cht.IssueID
         FOR XML PATH(''), TYPE).value('.[1]',
       'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM dbo.Issue AS c
GROUP BY c.IssueID
) s1 ON
s1.IssueID = i.IssueID

Notice this portion where the STUFF is happening:

LEFT JOIN
(
SELECT c.IssueID, STUFF((
    SELECT ', ' + ct.Name + ' - ' + ct.ShippingAddressCity + ', ' + ct.ShippingAddressRegion + ' ' + ct.CountryID FROM Company ct
     INNER JOIN PAndCIssueLocation cht
     ON cht.CompanyID = ct.CompanyID INNER JOIN Issue c
     ON c.IssueID = cht.IssueID
         FOR XML PATH(''), TYPE).value('.[1]',
       'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM dbo.Issue AS c
GROUP BY c.IssueID
) s1 ON
s1.IssueID = i.IssueID

This is the portion that is giving me an issue.  I know I have to somehow relate the issue back but I don't know exactly how!

Any help is much appreciated...

You could move the subquery into the main query and add where cht.issueid = i.issueid. Optionally you could add distinct and an order by depending on how the data are related.

SELECT
    i.IssueID AS IssueID,
    i.PAndCAuditDate AS AuditDate,
    i.PAndCValidationCompletedOn AS ValidationCompletedOnDate,
    i.CustomerIssueNumber AS OldId,
    i.PAndCValidatedBy AS ValidatedBy,
    i.PAndCValidatedComments AS ValidatedComments,
    i.Title AS Title,
    i.OpenDate AS IssueOpenDate,
    i.PAndCAuditors AS Auditors,
    i.PAndCAuditees AS Auditees,
    i.Description AS Description,
    i.PAndCRootCause AS RootCause,
    i.PAndCContainmentAndCorrectiveActions AS ContainmentAndCorrectiveActions,
    i.PAndCPreventiveActions AS PreventiveActions,
    DATEDIFF(d, i.TargetDate, GetDate()) AS DaysOverdue,
    i.TargetDate AS IssueTargetDate,
    i.ClosedDate AS IssueClosedDate,
    CASE WHEN i.ClosedDate IS NULL
      AND DATEADD(d, DATEDIFF(d, 0, i.TargetDate), 0)
        < DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
        THEN 1 ELSE 0 END AS IsOverdue,
    --s1.CompanyHasType
    --s1.Name
    , STUFF((
    SELECT distinct ', ' + ct.Name + ' - ' + ct.ShippingAddressCity
        + ', ' + ct.ShippingAddressRegion + ' ' + ct.CountryID
    FROM Company ct
     INNER JOIN PAndCIssueLocation cht ON cht.CompanyID = ct.CompanyID
     --INNER JOIN Issue c ON c.IssueID = cht.IssueID
     where cht.IssueId = i.IssueId
     --order by ct.name
         FOR XML PATH(''), TYPE).value('.[1]',
       'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM
    Issue i
/*LEFT JOIN
(
SELECT c.IssueID, STUFF((
    SELECT ', ' + ct.Name + ' - ' + ct.ShippingAddressCity + ', '
        + ct.ShippingAddressRegion + ' ' + ct.CountryID FROM Company ct
     INNER JOIN PAndCIssueLocation cht
     ON cht.CompanyID = ct.CompanyID INNER JOIN Issue c
     ON c.IssueID = cht.IssueID
         FOR XML PATH(''), TYPE).value('.[1]',
       'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM dbo.Issue AS c
GROUP BY c.IssueID
) s1 ON
s1.IssueID = i.IssueID
*/


Move database from windows server 2003 to windows 7/8? I am working with this company currently running their SQL Express database in a virtualized Windows Server 2003.

What would you experts say about replacing that for a virtualized slimmed-down windows 7 or 8.1?

I am aware this is not ideal but they are not willing to pay for a new server licence. (Please understand that this cost can be a higher concern depending on where you are).

This is a small network with only 8 local users and some RDP users (2 regular users, 2 very casual users).

If I understand correctly, Windows 7 has raised TPC connections limits to 20, but  I am not sure how this or other limits will affect an SQL instance on top of it.

The databse is purged at the beginin of the year and then its size is ~1.5 GB, by the end of the year it grows (*) to ~10 GB.

() It grows unnecesarily as there is absolutely not that much valuable data in it, it is moslty logs...*

What would you suggest?
Thank you.

It looks like your question revolves around 3 parts to me:

1-Licensing compliance/support/(security compliance?).

2-Best Practices to serve this.

3-Test it. It's a uncommon scenario.

For #1 I contacted MS Support and they said:

"MS REP: Hello, how may I assist you today?
Ali: at 11:49:20
Hello MS REP, may I use SQL Express in a corporate environment for commercial purposes and will it be supported by MS in that scenario?
MS REP: at 11:50:41
I am glad to assist, as long as you don't go over a 10 gigabyte database, only have a 1 processor/CPU computer running it, and don't need any of the additional features of the full editions, then yes it will be fine.
Ali: at 11:50:55
Perfect.
Ali: at 11:51:02
Let's see what they do when it hits 10gb lol. TY"
You're welcome, how large of a database are you using now?  The server will actually start crashing and failing a bit before you hit 10 GB, so if you are already at 9 GB, it would be a good idea to invest in a paid edition.
Ali: at 11:52:24
that's a great idea, they should do that even before 9GB IMO. do you know if the transaction log also counts as part of the DB Size or just the data files?
Danforth T: at 11:53:13
MS REP: data will count towards the total for as long as it is in the system.  If it gets stored permanently, then yes, transactional logs, applications, other servers, and data files will all contribute to the total.
Ali: at 11:54:22
thank you. thats good to know!"

So according to the rep not only do your data files count towards it but so does the log and temp data as well.  You're probably aware of the limitations of express though if you're already using it.

CPU:
As far as best practices go, can you give it a really good CPU in your hypervisor of choice since yo're limited on that.  Perhaps setup your own log shipping using powershell or another option to have some sort of 'HA' if needed.  This is more to ensure you don't have data loss since it seems with so few users and such small database size restraints that restore time might not be an issue.

RAM:
You are limited to 1GB of RAM so make sure your execution plan cache isn't full and you work around that, otherwise or also make sure you have fast disks.  10GB disk with 1GB ram means we're back to the 32bit days of optimizing disks.  Now we have SSDs so it's a nice cheat.

Disks/IOPS: They don't want to pay licencing?  Fine, throw down $200 on a nice Samsung 850 which has excellent life span.  Put your tlog and data onto there if they want to be really cheap and lose the tail of the log backups.  You'll still get the IOPS and you are doing frequent log backups off disk and preferably off VM, correct?  You are because if you aren't, you now will lose everything.  Backup on to a disk on another VM all together if possible or a NAS device just in case the actual VM get's corrupted and the virtual harddrive file for some reason isn't accessible.  Remember to test restore.

Monitoring:
Monitor your data size and disk sec/read and write.  Windows Server has optimization in many areas in the kernal that Windows 7 just doesn't have.  It could creep up in all places.  Grab a good baseline of performance from the old system and compare the performance to the new system.

Test Test Test: Can you replay some of the load on the new VM while you migrate?  Replay the production load on the new machine. Put up a new parallel instance and restore last nights backup to then document the security and configuration steps needed so you can script them and repeat it if the VM goes down or you need to redo.  Once it's scripted well restore the EOD backup and test with the end users.  Have them all go on your new test instance and hammer at it hard.  Make load, collect metrics.  Have them do the same thing on the current prod server and collect metrics.  How's it doing?  Good?  You're closer to implementing this properly, or as 'properly' as one can get using sql express on windows 7 in prod...

Cover Your Butt: Let the business know this is Windows 7 and might actually cost them more in support over the long term.  You might want to collect metrics to show them in 6 months.  Let them know their SLAs such as time to recovery, acceptable data loss, and make sure to test your backups/log shipping also preferred.  Homebrew log shipping on express also tests tlog backups.  If you put tlogs on 1 disk let them know there is a maximum x minutes of potential data loss due to no tail of the log backups.


I can´t change innodb_buffer_pool_instances I recently updated my mariadb database to 10.5.5 and I can't set innodb_buffer_pool_instances
innodb_log_file_size = 3G
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12

But when
    MariaDB [(none)]> SHOW VARIABLES LIKE "%innodb_buffer_pool_%";
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 12884901888    |
+-------------------------------------+----------------+

Do I need other vars to configure pool instances? Which?
Thank you very much for any ideas.

innodb_buffer_pool_instances where removed in 10.5 because they offered no benefit due to internal restructuring of the code.
There is no replacement setting.
The variable, that serves no purpose, is there for compatibility only and will be removed in 10.6


How to find MySQL creating temporary table in memory or disk? I have a query, after running it if i run the show status in the same session like below

show status where Variable_name like '%Created_tmp_disk_tables%';

It got incremented by one value. So it means temporary table is created on disk right? Not sure whether it is correct or not.

But if I enable profiling for that query. Below is the status I got.

| status                | duration  |
|---------------------- |---------- |
| starting              | 0.000246  |
| checking permissions  | 0.000035  |
| Opening tables        | 0.000098  |
| System lock           | 0.000044  |
| init                  | 0.000209  |
| optimizing            | 0.000075  |
| statistics            | 0.000336  |
| preparing             | 0.000138  |
| Creating tmp table    | 0.000435  |
| executing             | 0.000038  |
| Copying to tmp table  | 2.923929  |
| Sorting result        | 0.267926  |
| Sending data          | 0.000945  |
| end                   | 0.000071  |
| removing tmp table    | 0.077263  |
| query end             | 0.000022  |
| closing tables        | 0.000055  |
| freeing items         | 0.000092  |
| logging slow query    | 0.000074  |
| cleaning up           | 0.000022  |

Here I have found Copying to tmp table but not Copying to tmp table on disk.

Why there is a conflict between these two?

Whether Show status variable is correct or profiling is correct?

Need to know whether temporary table is created on disk or not.

Use the variable Created_tmp_disk_tables either on session or global scope to know when you have created implicit temporary tables on disk:

MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 0     | <--- session starts with 0 tmp tables
+-------------------------+-------+
3 rows in set (0.01 sec)

MariaDB [(none)]> SELECT 1 UNION SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     | <--- one tmp table created, 0 on disk
+-------------------------+-------+
3 rows in set (0.01 sec)

MariaDB [(none)]> SELECT 1 UNION ALL SELECT 1;
+---+
| 1 |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.00 sec)

MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     | <--- no tmp table created
+-------------------------+-------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create table test.test (a blob);
Query OK, 0 rows affected (0.18 sec)

MariaDB [(none)]> SELECT * FROM test.test UNION SELECT * FROM test.test;
Empty set (0.00 sec)

MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     | <--- tmp table created on disk
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

If you enable performance_schema, the default configuration (very very low overhead) provides you a summary of everything you probably need:

MariaDB [(none)]> SELECT * FROM performance_schema.events_statements_summary_by_digest\G
[...]
*************************** 26. row ***************************
                SCHEMA_NAME: test
                     DIGEST: f0aad09130b2e108d8dd1c58d1713678
                DIGEST_TEXT: SELECT * FROM `test` UNION SELECT * FROM `test`
                 COUNT_STAR: 1
             SUM_TIMER_WAIT: 716448000
             MIN_TIMER_WAIT: 716448000
             AVG_TIMER_WAIT: 716448000
             MAX_TIMER_WAIT: 716448000
              SUM_LOCK_TIME: 308000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 1 <---- 1 disk tmp table created
     SUM_CREATED_TMP_TABLES: 1 <---- 1 tmp table created (including the disk one)
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 3
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 1
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-01-06 21:18:18
                  LAST_SEEN: 2018-01-06 21:18:18

Edit: I've just noticed you are still on 5.5- I strongly suggest the upgrade, only P_S is worth it for the time not lost on debugging. For now, the session/global variable should work for you, the profiling will not work as well:

MariaDB [(none)]> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT * FROM test.test UNION SELECT * FROM test.test;
Empty set (0.00 sec)

MariaDB [(none)]> SHOW PROFILES;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00055224 | SELECT * FROM test.test UNION SELECT * FROM test.test |
+----------+------------+-------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000053 |
| checking permissions | 0.000011 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000026 |
| After opening tables | 0.000009 |
| System lock          | 0.000008 |
| Table lock           | 0.000196 |
| optimizing           | 0.000010 |
| statistics           | 0.000015 |
| preparing            | 0.000013 |
| optimizing           | 0.000007 |
| statistics           | 0.000006 |
| preparing            | 0.000009 |
| executing            | 0.000005 |
| Sending data         | 0.000019 |
| executing            | 0.000004 |
| Sending data         | 0.000009 |
| optimizing           | 0.000006 |
| statistics           | 0.000006 |
| preparing            | 0.000005 |
| executing            | 0.000004 |
| Sending data         | 0.000016 |
| removing tmp table   | 0.000057 |
| Sending data         | 0.000006 |
| query end            | 0.000008 |
| closing tables       | 0.000004 |
| Unlocking tables     | 0.000008 |
| freeing items        | 0.000005 |
| updating status      | 0.000009 |
| cleaning up          | 0.000012 |
+----------------------+----------+
30 rows in set (0.00 sec)

Sometimes you will see a step like "copying from heap to myisam", when it bails on on-memory execution, but that will not happen everytime, like shown above. You mention innodb on the tags, but implicit temporary tables only started being InnoDB (vs HEAP + MyISAM) since 5.7.


SSIS Load using SQL Command I'm trying to insert the below query using SSIS. I'm doing this just because there are some overlapping ID's that are already existing ID's in the destination table. I tried using the following to get the Min and Max IDs for both tables in order to insert only the non existing rows:

/*
--SourceTable
 MinID  MaxID
select 552596452-633166940

--destination table
MinID        MaxID
450485204  596693972
*/

Then I now used that to implement the insert using SSIS Package. I couldn't get this package to run. Is there anything that i'm doing wrong? is there any other SSIS data flow that I could use to work around this? I chose to use SSIS because the record is aroung 30 million rows which will take too long to run on SQL and it's going to also fill up the logspace pretty quicky which will affect the performance of the server.

INSERT INTO dbo.destinationtable
           ([id]
           ,[userId]
           ,[serviceId]
           ,[keyId]
           ,[wappush]
           ,[builturl]
           ,[datestamp]
           ,[pocessingTime]
           ,[UserAgent]
           ,[Referer]
           ,[fallBack]
           ,[Error]
           ,[ClientIP]
           ,[AdvertUrl])

SELECT  id, userId, serviceId, keyId, wappush, builturl, datestamp, pocessingTime,
          UserAgent, Referer, fallBack, Error, ClientIP, AdvertUrl
FROM  sourcetable a (nolock)
WHERE (datestamp < DATEADD(d, 0, DATEDIFF(d, 0, GETDATE() - 60)))
AND id > 596693972 and id <= 633166940
ORDER BY id

If you're going to use SSIS, may I suggest you use it? ;)

What I believe you are attempting to do is insert some data that doesn't already exist in the target. You have identified what data needs to go using your min/max logic and you attempted to do a similar operation for the target.

For starters, the OLE DB Command is something you should use sparingly. It performs singleton operations and so the volume of rows you want this thing handling should be in the tens, maybe hundreds. Definitely not 80 million.

Your data flow should look something like this

I have an OLE DB Source (ADO.NET can be used but if you need to parameterize your query, it's a little more complex). You can use your existing query or if you need to chunk it up because you have active systems, so be it.

The Lookup Component, LKP RM_DimEmplo... is the key to this operation. When the package starts, it is going to run the source query in that component and cache all the values. That sounds expensive, so don't let it be. Don't just select dbo.destinationTable. Write your query to bring back only the keys you need to make a match. It should be those IDs. That will be very narrow and shouldn't be so bad. The idea here is that you will have your source rows and you will compare those to the cache of the target table based on those keys. Since you're not worried about detecting differences, you know that anything that doesn't match needs to go, so on the "General" tab, change the "Specify how to handle rows with no matching entries" to use Redirect rows to no match output. Otherwise, this will blow up when it doesn't make a match.

If you find it's still impactfull on your systems, then you need to go to a more advanced approach of using Expressions on the Lookup Component to filter the range of data to match the IDs coming in. Thus, you have identified the source range is 100 to 200. There's no need to pull in the full 0 to 1000 range of keys in the destination, you would simply make the lookup cache the keys that are in the operational range. 80M shouldn't be terribly taxing unless you have just no RAM to spare on the machine.

The OLE DB Command does singleton, one-off operations. The OLE DB Destination can either do singleton inserts (default for 2005 - Table or view) or set/batch based updates (Table or View - fast load) which can be screaming cheetah wheelies fast. Since you're loading a lot of data, unless you want your admins to hate you, change the Max insert commit size to something other than the default of 2147483647. That basically says don't commit nothing until it's all been inserted. That can cause your transaction log to get big in a hurry.

The nice thing about your problem is that you can run this to your heart's content. Say 1M rows get loaded and something bad happens. Just restart. However much got committed and when it restarts, it might pull the same source data but your lookup will have the most recent set of keys from the target so the result will be no operation performed for those. I know, you'd think everyone loves duplicates in their target system but for some reason, that just doesn't fly at some places...

Wrapup

I can't recommend the Stairway to Integration Services series enough. Specifically for this problem, Level 3


Nested Stored Procedure Connections I have a Stored procedure that simply looks for Imported files through a set of other processes. The main SP runs every 5 mins and looks for new files in a directory and imports them into a staging table if there is a newer file.
At this point, if it does import a newly added file, there is another accompanying SP that ETL's the records from staging into another production DB. The ETL (EXEC dbo.ETL) is happening inside the monitoring SP that runs every 5 minutes.
My main question is, can I have the ETL stored Procedure run independently of the monitoring SP?
If an ETL SP takes longer than 5 mins, when the monitoring SP is called again, will it use another connection if the last call 5 mins ago is still running the ETL procedure?
IOW, I want to run a SP (SP #1) every 5 mins that calls other SPs (that may take more or less than 5 mins) without interrupting SP #1

A solution could be to build a simple SSIS package that runs the stored procedure you need; then call it from tsql in this way:
declare @execution_id bigint
 exec ssisdb.catalog.create_execution
  @folder_name = 'folder'
 ,@project_name = 'project_name'
 ,@package_name = 'run_sp.dtsx'
 ,@execution_id = @execution_id output

 exec ssisdb.catalog.start_execution @execution_id

This call is asyncronous by default


Function extract() from date returns double precision value Why does the Postgres function extract() return double precision from date value?

Only reason I could imagine is, there are some locales using calendars (those not Gregorian calendars and supported by Postgres), calculate date/time differently and possible return float number.

Because the return values can be large or a decimal number.
Double precision will accept a wide range of values

Looking at other numeric types, you only have decimal which will have overhead:  you don't know the return scale or precision needed beforehand so it would have to wide

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
Result: 28.5

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
Result: 982384720.12

See PG docs


Is $IDENTITY documented and reliable in SQL Server? I learned that SQL Server can return the value of the (single) identity column of a table using the pseudo-column $IDENTITY:

SELECT $IDENTITY FROM Table

Is this feature documented and reliable? The only official mention of it is on the IDENTITY page but it's buried in a code sample. This suggest that it might be intended to be undocumented. There are remarkably few Google matches for this feature as well.

$IDENTITY (and $ROWGUID) are documented in SELECT Clause (Transact-SQL).

I would caution against using it because it will throw an error when a table doesn't have an identity column. Take these two examples - the first one works (check your Results tab), but the second one throws an error:

CREATE TABLE #EmployeesWithIdentity (EmployeeID INT IDENTITY(1,1), EmployeeName VARCHAR(50));
SELECT $IDENTITY FROM #EmployeesWithIdentity;
GO

CREATE TABLE #Employees (EmployeeName VARCHAR(50));
SELECT $IDENTITY FROM #Employees;
GO

Here's what the second one produces:

Msg 207, Level 16, State 1, Line 6
Invalid column name '$IDENTITY'.

If you're looking to find which columns have an identity field, I'd use the method described in this Stack Overflow post instead:

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME

Or, using the catalog views:

SELECT s.name,t.name,c.name
FROM sys.schemas AS s
JOIN sys.tables AS t ON s.[schema_id] = t.[schema_id]
JOIN sys.identity_columns AS c ON t.[object_id] = c.[object_id];


Inner Join or what else? What I have

Table A                                 Table B

ID    ID_Fruit    ,,, so on             ID      ID_Fruit   ID_CRATE  DateTime
1     1002                              i_1     1002          1      2014-07-28 00:00.000
2     4534                              i_2     1002          2      2014-06-25 00:00.000
3     1243                              i_3     1243          NULL   2014-07-09 00:00.000
4     1003                              i_4     4534          1      2014-07-23 00:00.000`

Result I want:

ID    ID_Fruit    ,,, so on             ID      ID_Fruit    ID_CRATE     DateTime
1     1002                              i_1     1002           2      2014-06-25 00:00.000
2     4534                              i_4     4534           1      2014-07-23 00:00.000

What I am trying,

SELECT *
FROM TABLEA
    OUTER APPLY
        (SELECT *
         FROM TableB
         WHERE TableB.ID_Fruit = TableA.ID_Fruit
           AND TableB.ID_CRATE IS NOT NULL
        ) As Table_B

I want only 1 row from several rows in Table B based on closest DateTime

Yes, shouldn't be too difficult, try:

SELECT a.ID, a.ID_Fruit,,, so on b.ID, b.ID_Fruit, b.ID_Crate, b.[DateTime]
FROM TABLEA a
INNER JOIN TABLEB b ON a.ID_Fruit = b.ID_Fruit
INNER JOIN (SELECT ID_Fruit, MAX([DateTime]) FROM TABLEB GROUP BY ID_FRUIT) maxtime
      ON b.[DateTime] = maxtime.[DateTime]

Its not quite clear if you mean that it should be the most recent, but I have assumed that is what you mean.


Does innodb guarantee unique data in unique indexes even with UNIQUE_CHECKS=0? We have a suspicion that sometimes there could be non-unique data even with a UNIQUE index. Question is, can we rely on InnoDB guaranteeing that there won't ever be non-unique data, even in the presence of UNIQUE_CHECKS=0, so there is no reason to check it?

Edited to add: If it is possible to have non-unique index entries for a UNIQUE index in InnoDB (even with cheating), how is such a test-case created?

Or is it like foreign key constraints and InnoDB, where that guarantee can be broken by setting FOREIGN_KEY_CHECKS=0, so that if we want to be sure, we should check it with SELECT statements?

The documentation for UNIQUE_CHECKS:

  Setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still permitted to check for them and issue duplicate-key errors if it detects them.

documentation for Bulk Data Loading for InnoDB Tables:

  If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

  For big tables, this saves a lot of disk I/O because InnoDB can use its change buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.

These seem to imply that UNIQUE_CHECKS=0 can be used to break the uniqueness guarantee.

MariaDB Enterprise documentation for unique_checks:

  Set to 0 to speed up imports of large tables to InnoDB. The storage engine will still issue a duplicate key error if it detects one, even if set to 0.

This seems to imply that the guarantee holds, even with UNIQUE_CHECKS=0.

This example SQL demonstrates UNIQUE_CHECKS=0 is ignored and uniqueness seems to be enforced still:

DROP TABLE IF EXISTS `FOO`;

CREATE TABLE `FOO` (
    `foo` VARCHAR(255) NOT NULL,
    UNIQUE INDEX (`foo`)
) ENGINE=InnoDB;

INSERT INTO FOO (foo) VALUES ('A');

SET UNIQUE_CHECKS=0;
-- This generates the error:
-- ERROR 1062 (23000) at line 11: Duplicate entry 'A' for key 'foo'
-- even though UNIQUE_CHECKS=0
INSERT INTO FOO (foo) VALUES ('A');

(Edit: Simplified example to only demonstrate that UNIQUE_CHECKS=0 is ignored.)

So which is it: Can we rely on InnoDB guaranteeing that there won't ever be non-unique data, even in the presence of UNIQUE_CHECKS=0, so there is no reason to check it?

We're using MySQL version 5.7.23.

Normally, index changes are put into the "change buffer" for later writing to disk.  This happens for non-unique indexes.  (This also applies to UPDATEs that change a UNIQUE index, and DELETEs, which necessarily remove a 'row' from the index.)

For UNIQUE indexes (and UNIQUE_CHECKS=1), each row being inserted is immediately verified as not being a dup.  That way, the row can be rejected quickly.  The drawback is that it must fetch the index block that contains the value in question.  For huge tables, this may involve a read from disk, which can be relatively slow.  And that disk read is blocking, so the INSERT must wait.

For a batch load of rows, it would be faster to load all the rows, populate the change buffer, and postpone having to actually look at the disk index.

UNIQUE_CHECKS was implemented in the days before fast SSDs and when RAM was small.  That is, it used to be an important optimization.

The "Change Buffer" is a portion of the buffer_pool (default: 25%).  It holds secondary index changes awaiting consolidation and insertion into the disk copy of the index.

When an index lookup is needed, it must check both the change buffer and the real index.

The real index is a bunch of 16KB blocks in a B+Tree organization based on the secondary index.  Those blocks are cached (one-by-one, as needed) in the other 75% of the buffer_pool.

With UNIQUE_CHECKS=ON, the uniqueness test can be immediate; with it off, I suspect that the test is not done until either the bulk INSERT is finished, or the change buffer needs some flushing.

The change buffer is written to disk in the background.  Note:  This is one explanation for I/O when no queries are running.  Logically, the CB is sorted; a block from disk is read; entries are merged into that block; then the block is left in the buffer_pool as "dirty".  Eventually, another thread worries about writing dirty blocks to disk (another cause for strange I/O).


Triggers to copy information I have 2 Tables.

Bank (Original Table):

ID
Bank_Code
Bank_Transaction
Delete

Bank_History:

ID
Bank_ID
CodeH
TransactionH

I need for the Bank table to copy into Bank_History using triggers. Here is what I have so far:

CREATE TRIGGER `Bank_after_update` AFTER UPDATE ON `Bank`
 FOR EACH ROW BEGIN

    IF NEW.Deleted THEN
        SET @Change_Type = 'DELETE';
    ELSE
        SET @Change_Type = 'EDIT';
END IF;
INSERT INTO Bank_History (bank_id, Change_type) VALUES (NEW.id, @Change_Type);
END

It creates a new record but it doesn't transfer all the info.

When you say, "it doesn't transfer all the info" what exactly do you mean? Do you want to insert into the history table before the data is updated AND after for auditing purposes?

  ...the mysql statement delimiter so that you can use the ; statement
  delimiter within the trigger definition.

Check the MySQL trigger reference section at MySQL for more info.

Either way, try adding he DELIMITER // at the beginning and the end.

DELIMITER //
CREATE TRIGGER `Bank_after_update` AFTER UPDATE ON `Bank`
 FOR EACH ROW BEGIN

    IF NEW.Deleted THEN
        SET @Change_Type = 'DELETE';
    ELSE
        SET @Change_Type = 'EDIT';
END IF;
INSERT INTO Bank_History (bank_id, Change_type) VALUES (NEW.id, @Change_Type);
END
//

I hope this helps.


SQL Server report based on stored procedure not showing columns I'm working in SQL Server 2008 R2. Created a stored procedure (EXEC dynamic SQL with CASE THEN ELSE ) on server with parameters works ok. But Report Builder on insert table is not showing columns for arrange!

I found WORKAROUND http://forums.yessoftware.com/posts.php?post_id=115829:

But there is a easy way?

simpified code

ALTER PROCEDURE [dbo].[VetCertTest] @Store char(5), @Date char(8)
AS
BEGIN
DECLARE @Log char(7) = 'MOS_DIS'
DECLARE @sqlQuery VARCHAR(8000) = 'SELECT
  rtiersS.alias store
, rfampr.lb as type
FROM
 presta_ru01.rul
, presta_ru01.rtiers rtiersS
, presta_ru01.pporte
, presta_ru01.rub
, presta_ru01.rfampr
WHERE
    rfampr.rdoss_code = ''''' + @Log + '''''
AND rfampr.code = rub.rfampr_code
AND rub.code = rul.rub_code
AND rtiersS.code = pporte.rtiers_code_dest
AND dhr_mvt >= to_date(''''' + @Date + ''''',''''DDMMYYYY'''')
AND dhr_mvt < to_date(''''' + @Date + ''''',''''DDMMYYYY'''')+1
' + CASE WHEN @Store = 'MA000' THEN '' ELSE 'AND rtiersS.alias = ''''' + @Store + '''''' END + '
ORDER BY rtiersS.alias, rtiersS.nom1, rfampr.lb'
DECLARE @finalQuery VARCHAR(8000) = 'SELECT * FROM OPENQUERY(WMSFELRU,' + '''' + @sqlQuery + '''' + ')'
EXEC(@finalQuery)
END

solved - add defaults to parameters and Report Builder can detect columns

Its a Report Builder bug - it not ask for parameters when refresh fields

IF @Store IS NULL SET @Store = 'MA000'
IF @Date IS NULL SET @Date = '19092014'

Add Fields manually which not refreshed under Dataset properties.

Datasets --> Right Click --> Properties --> Fields --> Add.

mentioned column name and parameter name as per query.


user mapping is droped after swithing from principle server to mirror server I have principal server:A and mirror server:B with DB Name: "DBTest"
On the server A there is a username (SQL auth.) call: user1 and is mapped to "DBTest".
the Same is on Server B, same username and same DB Name (but is in recovery mode due to mirroring).
After Fail-over in Principle Server, the Witness switch the DB to the mirror server and everything is fine. except the user: user1 on the server B is not mapped to the "DBTest" on server B.
i should every time reassign manually the user1 to the "DBTest". The same i should do when i switch from server B again to server A.

This issue blocks the systems and applications which are connecting with the DB, because the user1 is not mapped automatically to the DB name after fail-over.
please help!

The issue you're running into most likely has to do with your users becoming orphaned on failover. Even though you're using the same logins, the SIDs are different, and don't match up with the users in the database. Basically, you need to recreate the user exactly as it is on server A.

You need to do a CREATE LOGIN with SID = xxx that will create an exact copy of the login on server A. Syntax can be found here. I use sp_help_revlogin to do this; see this knowledgebase article for more information. This will give you a CREATE LOGIN script with the same SID and password to execute on server B.

If you move to SQL Server 2012, you'll have the option of using contained databases. Contained databases can store their own logins, meaning the instance doesn't need to have its own set and preventing orphaned users. These are especially good for databases that get moved around a lot and may have users changing somewhat frequently.


msdb.dbo.backupset.compressed_backup_size is inaccurate? I thought the value in msdb.dbo.backupset.compressed_backup_size represented the size, in bytes, of the full, differential, or log backup file (that is, the BAK, DIFF, or TRN file).  However, although it is either exact, or very close, for full backup (BAK) files, for log backup (TRN) files, it's so far off that the results seem random.

Steps to reproduce (SQL Server 2008 R2 SP3):

(1).  Create a database named 'MyDatabase'.  Set it to Full Recovery Model.

(2).  Take a compressed full backup of 'MyDatabase'.

(3).  Take a log backup of 'MyDatabase'.

(4).  Run the following query:

SELECT
      bs.[database_name]
    , bs.[backup_start_date]
    , bs.[backup_finish_date]
    , bs.[type]
    , bs.[compressed_backup_size]
    , bmf.[physical_device_name]
FROM
    [msdb].[dbo].[backupmediafamily] AS bmf
    INNER JOIN [msdb].[dbo].[backupset] AS bs ON bmf.[media_set_id] = bs.[media_set_id]
WHERE
    bs.[database_name] = N'MyDatabase'
ORDER BY
    bs.[backup_start_date] DESC

(5).  Compare the value in the compressed_backup_size column to the actual size of the files on disk.  An example of my results for a log backup are that the 'compressed_backup_size' value displayed is 9505 bytes; the size of the TRN file on disk is 28.0 KB (28,672 bytes).

I've tried this on several servers (with existing databases, as well as with newly created ones) and in all cases the TRN file sizes are reported incorrectly. Is anyone able to reproduce this?  Am I misunderstanding the meaning of the compressed_backup_size column?

I am seeing similar results (from SQL Server 2008 R2), even though I am not using compression (so compressed_backup_size == backup_size), so I do not think this is a compressed size issue.

For example, for a .bak, backupset reports 117,648,384 while size on disk is 117,678,080, and for a .trn, backupset reports 203,776 while size on disk is 233,472.

I think the documentation's claim that backup sizes are reported as "Total Byte count of the backup stored on disk" is misleading.  I suspect that at some level it is reporting the true size of the backup as far its internals are concerned, but the actual size on disk will be larger than that.  Although the figures do not seem to divide exactly, I see the disk size as being up to 32KB larger than the reported size.

I just looked at a SQL Server 2012 backup, and it reports 7MB .bak and .trn each being ~10KB smaller than disk size.  So this is not just a 2008 R2 issue.

You see the .bak size as "very close" but the .trn size as "so far off" because your .bak is a large size while your .trn is a small size.  If it is within ~32KB, the results are not too far off when the .bak/.trn are a decent size, which is what is important.


Parking Spots Availability Design I have a reservation table that has from_date, to_date, and parking_location_id. What is the best way to calculate location spots availability for each location and each hour?

I tried to write a query that calculates availability from reservation table but this query is very slow when the period is long even though the from_date and to_date columns are indexed.

Another approach to speed things up was to create a location availability table

The contains all locations and all dates and hours in a range of years and whenever a reservation is saved a trigger updates the reserved spots in this table. However, in this approach, the table rows size grows exponentially and we need to add a range of dates with location ids whenever a new location is added. Any ideas how can I improve this design or what alternatives do we have?

The slow query tries to calculate number of reserved spots per date and then we find the max number in that date range.

select  sum(r.number_of_slots) as reserved_spots
from location l
inner JOIN reservation  r on r.location_id=  l.id  , time_dimension t
where t.db_date between r.from_date and r.to_date and
 t.db_date < r.to_Date and t.db_date > r.from_date  t.id=type_id and group by db_date;

Time dimension table contains a list of all dates in 10 years.

I'd suggest to use a calendar or schedule table.

In my example I've set up a 3-day table calendar with intervals of one hour:

(Do a google search to find out some date series generator, you'll find a lot)

    Hours table
===================
2017/01/01 00:00:00
2017/01/01 01:00:00
2017/01/01 02:00:00
2017/01/01 03:00:00
2017/01/01 04:00:00
2017/01/01 05:00:00
...
...

Then simply LEFT JOIN your table with the calendar table and get NULL rows:

    Hours table               Reservations
===================        ===================
2017/01/01 00:00:00        2017/01/01 00:00:00
2017/01/01 01:00:00  LEFT  2017/01/01 01:00:00
2017/01/01 02:00:00  JOIN  NULL                 <-- Free
2017/01/01 03:00:00        2017/01/01 03:00:00
2017/01/01 04:00:00        NULL                 <-- Free
2017/01/01 05:00:00        NULL                 <-- Free

SELECT    hdate
FROM      calendar
LEFT JOIN reservations
ON        hdate = from_date
WHERE     parking_id is null;

dbfiddle here


pt-table-checksum not finding differences I have a very simple setup: 1 master with 2 slaves. I stopped replication on one of the slaves (with the slave mysqld still running) and changed a row on the master. I have also kept all slaves and the master running and just changed data on the slave.

I ran pt-table-checksum --databases=db_main --user=user --pass='pass' but do not get any differences.

I read the documentation a few times, but can't seem to find what I might be missing.

Extra info: the storage engine is InnoDB, the master is on MySQL and slaves on MariaDB. Full replication with slaves set as read-only. Permissions are correct.

I want to make sure the tool is working as expected. We had a slave stop running and I ran pt-table-checksum. It told me there was no difference in data (which was obviously false). I want to make sure pt-table-checksum is working as expected, but I have not been able to create a test where pt-table-checksum actually detects a difference.

Just now I changed data on the running slave (so data is different in slave and master). When I run pt-table-checksum on that database, I don't see any "diffs". Because it is a mature tool, I assume I am doing something wrong, but can't figure out what it is.

When you stop a slave the master has no idea what its slaves are.
pt-table-checksum doesn't see your slaves.

pt-table-checksum is a mature tool and the most popular in the Percona toolkit. The data inconsistency is easy to reproduce. Just write something on a slave. By the way, there is a vagrant master->slave configuration in https://github.com/twindb/twindb_table_compare, you can test pt-table-checksum on it.

By default pt-table-checksum connects to localhost. Do you run it on the master? Can you connect to the slave with mysql client with same user/password?

There were two issues preventing the check from working correctly:

When replication is stopped on a slave, the master will not see it as a slave (and therefore not check it).
Permissions allowed pt-table-checksum to connect to the master, but not to the slaves (since that account was restricted to localhost). However, the tool will not show permission denied. Instead it just ignores the slave.


MySQL backing up MyISAM tables by copying the physical files - and automating the process I have 2 databases with MyISAM tables running on a Windows 2008 Server. These databases are about 20GB in size with a few tables with millions of rows. My question regards backing them up on a weekly basis. Currently I do some updates once a week, and then I go to the data folder and copy the physical folders representing the databases to another drive on the server, and then rar everything up.

This process takes about 45 minutes and during that time certain functionality of my website cannot be used as during the copying, the tables get blocked. I have seen that you can LOCK and FLUSH tables so that they can still be used while they are being copied. So does LOCKing the tables allow concurrent SELECTs?

I do not know exactly how to go about this and I would greatly appreciate if anyone could help me with how I could synchronize the lock/flush statements with the copying of the physical data and then the subsequent unlocking, and how I could possibly automate (possibly script to a dos batch file) this process?

Thanks in advance, Tim

Basically, you need the following paradigm

Session #1 (MySQL)

Lock all tables
Read Lock
Sleep for a Long Time

Session #2 (Windows) : This you are already doing

Copy the .frm, .MYD, .MYI files for every MyISAM table

Session #3 (After Session #2 is Complete)

Look for Sleeping Connection of Session #1
Kill the SLEEP Query (Session #1 Will Terminate)

Create a File Called C:\KillLock.bat with these lines

@echo off
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
set KILL_SCRIPT=C:\KillSleepLock.sql
mysql %MYSQL_CONN% < %KILL_SCRIPT%

Create a File Called C:\FlushTablesWithReadLock.bat with these lines

@echo off
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
set KILL_SCRIPT=C:\KillSleepLock.sql
start mysql %MYSQL_CONN% -ANe"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)"
mysql %MYSQL_CONN% -ANe"SET @x=SLEEP(180); SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE state='User sleep' AND info='SELECT SLEEP(86400)'" > %KILL_SCRIPT%

This will lock all tables, pause for 3 minutes, then create the file C:\KillSleepLock.sql

Backup Process

Here are your steps to automate

STEP 01 : Run C:\FlushTablesWithReadLock.bat
STEP 02 : Run your process to backup the .frm, .MYD, .MYI files
STEP 03 : Run C:\KillLock.bat

I will leave it to you to implement this paradigm

Give it a Try !!!


How to Create users in sql server for accessing only one database using Management Studio How to Create users in sql server 2008 R2 for accessing only one database using Management  Studio.

regards,
salman

Your answer is right here in SO already: Restrict SQL Server Login access to only one database. Just in case the link is broken:
1. Connect to your SQL server instance using management studio
2. Goto Security -> Logins -> (RIGHT CLICK) New Login
3. fill in user details
4. Under User Mapping, select the databases you want the user to be able to access
   and configure

You can add an user either using SSMS or by using CREATE USER. You could use sp_adduser but it's not recommended as it will be removed in a future version of Microsoft SQL Server.
You also need a login to match that user. Again, you can use SSMS or CREATE LOGIN for the task.
Check BOL's page for database roles on SQL Server 2008R2, use the dropdown menu on the top of that page to switch to other version if need to.
Here is the list of the roles that can be assigned:

db_owner Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.


Combining InnoDB with MyISAM Possible Duplicate:
  Is it common practice to mix InnoDB and MyISAM tables on same server?

I have a movie clips and photos table in an application; these tables contain a tag column, which allows users to search for the movies/photos by tag; however, the whole database is designed using InnoDB tables specifically for data integrity.

Since the tables are not MyISAM, I cannot use the full-text feature (which sucks).  I was thinking of changing those 2 tables into MyISAM tables; however, I read somewhere that combining MyISAM and InnoDB tables is a very bad idea.

I really would want to use the fulltext feature on those tables.

My questions are these:

Is it really a bad idea (performance-wise, data integrity-wise, etc) to combine those 2 tables?
Is there another way of achieving the same result (the results obtained using fulltext feature) in InnoDB tables?

As long as you recognize the tradeoffs you're making, there is nothing inherently "wrong" about introducing a MyISAM table into a primarily InnoDB database for the purposes of supporting full-text searches.

Tradeoffs:

No enforcement of referential integrity on MyISAM tables. Referential integrity may be enforced at the application level instead if this is critically important.
Database has a mix of InnoDB and MyISAM storage engines which may go unnoticed or cause confusion for future developers and DBAs. At worst, an enterprising dev or DBA may try to "improve" the schema by changing the MyISAM table to InnoDB, thereby breaking full-text searching the application.
MyISAM full text search requires MySQL-specific SQL syntax which will reduce the portability of your application.


How to find if my innodb row uses overflow/off pages? I use INNODB engine.

My row format is Dynamic.

For variable-length columns like TEXT, BLOB and varchar etc. , data is stored in off page and not in the same page itself.

Do I have a way to determine if my INNODB row uses additional overflow pages?

If yes, can I also get the total number of off pages used?

SHOW TABLE STATUS LIKE 'tablename'; -- There are 3 length columns (Data, Index, Free).  These are in bytes; divide by 16KB to get pages.

It is more complex to discover on-record vs off-record storage.

If all you want is whether it uses off-record pages, then that can probably be determined by the definition of the ROW_FORMAT.  For DYNAMIC, I think these are the rules:

Any column up to 40 bytes is stored on-record.
If all the rest of the columns will fit (in about 8KB), then put them on-record.
Spin off the longest rows first until the rest fit.

The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page.  ("all or none")

When a column is stored off-record, a 20-byte "pointer" is left on-record.  (There may be a 2-byte length in addition; I am not sure.)

Depending on what version of MySQL/MariaDB/Percona you are using, there are detailed statistics on the data and index BTrees.


Stored Procedures under Source Control, best practice I am currently using Tortoise SVN to source control a .NET Web Application. What would be the best way to bring our SQL Server stored procedures into Source Control? I am currently using VS 2010 as my development environment and connecting to an off-premise SQL Server 2008 R2 database using SQL Server Data Tools (SSDT).

What I have been doing in the past is saving the procs to a .sql file and keeping this files under source control. I'm sure there must be a more efficient way than this? Is there an extension I can install on VS2010, SSDT or even SQL Server on the production machine?

There are tools out there, such as this from Redgate, but I have always found that best is to save as SQL files, perhaps even in a Database Project (SSDT?) in your solution.

Along with this, I suggest the following guidelines:

Always assume the SVN version as the "current" / "latest"
Ensure that every script you run has an appropriate "if exists then drop" at the start
Remember to script your permissions, if any

You can initially create these SQL files by scripting directly from SSMS, and you can set SSMS to script all your "drop" and "create" as well as your permissions.


How to avoid log space error while executing SSIS packages? I'm executing ssis package with more than 70 million data's. But I'm facing issue as
log space full error message while executing the package.

Is there any option to resolve this issue?

There may be nothing you can do except allow your log file to grow further. Without the exact message we can't be sure, but there are two reasons you might be runing out of log space:

You log file is not set to a large enoguh size (or not set to auto-grow), in which case you need adjust these properties.
The partition your log file is stored on is full so even if you have log growth enabled it has no-where to grow too. In this case you need more drive space.

Everything your package does will go via the log file: this is an important part of how SQL Server manges transactions in order to keep your data consistent and it can not simply be switched off or temporarily moved. You might find that your package is taking more fully logged action than it needs to in order to acheive the goal. You give no detail of the process your package follows so we can only give general guidance:

Could the same rows be being modified many times in the process? If so you could try to merge these steps. UPDATE table1 SET fiedl1=1 WHERE ID=1; UPDATE table1 SET fiedl2=2 WHERE ID=1; is two row updates that will be logged where UPDATE table1 SET fiedl1=1, fiedl2=2 WHERE ID=1; is one. Over millions of rows this could stack up quite quickly. Likewise if you insert rows and later update them in the process.
Are you refreshing a table completely by wiping it down and refilling it? In which case use TRUNCATE TABLE <tablename> instead of DELETE <tablename>. TRUNCATE is a minimally logged operation so will use less log space (and be much faster) then wiping the table by DELETEing every row.
Better still: if you are deleteing a bunch of rows then replacing them, are many of the replacement rows identical to the ones you initially delete? If so you could update your logic to only UPDATE rows that need updating, INSERT rows that are missing and DELETE rows that are superfluous. For instance one of our apps gets a daily update feed from a client that contains state from another application: this is hundreds of thousands of rows but only a few hundred are added/changed/deleted each day so it is a lot more efficient to detect which few hundred need action instead refreshing everything.


Is it possible to switch queries based on an input in mySQL? Is there a way to switch queries based on values? Let's say I have the below query:

select pdate as 'Payment Date', ROUND(@sum_amount := @sum_amount + `amount`) AS 'Total Amount' from
(
select @sum_amount := 0, date(issue_date) pdate, sum(amount) amount from payments group by YEAR(date(issue_date))
) inner_tbl

I want to change query interval (yearly, monthly, daily) based on user input. Something like below:

SELECT CASE input WHEN 1 THEN **RUN YEARLY QUERY**
     WHEN 2 THEN **RUN MONTHLY QUERY** ELSE **RUN DAILY QUERY** END;

This should be possible with basic logical operations (IF, THEN, ELSE etc)

In this situation you'd want to get the INPUT value and check it against your conditions (pseudocode):

IF INPUT = 'YEARLY' THEN
BEGIN
INSERT QUERY HERE
END
END IF

IF INPUT = 'MONTHLY' THEN
BEGIN
INSERT QUERY HERE
END
END IF

For more information on this check the manual:

https://dev.mysql.com/doc/refman/5.7/en/if.html


What is a better way to keep "user online"? I'm working on software for social network and I plan it to scale a lot. Actually, I'm updating timestamp column every time user clicks, to be sure they're marked as online. When they do not click for 30 seconds, they're marked as offline. So simply, my queries are

UPDATE Users SET timestamp = UNIX_TIMESTAMP WHERE uid = 1; -- UNIX_TIEMSTAMP is current unix timestamp given by PHP's time()
SELECT Name FROM Users WHERE timestamp > (UNIX_TIMESTAMP - 30); -- (UNIX_TIMESTAMP - 30) is given directly by PHP (time() - 30)

In this answer, answerer said that

  Will you be updating this table continuously? I doubt that would scale

is there a better way to update user's online status that would scale? Or do you think this is fine?

The fastest and easiest to scale query is the query you don't run.

Move your session state to a cache tier like memcached or Redis.
Update the cached value on every user interaction, not the database value.
Persist to the database via a background task that periodically scans the cache for updates.


Really big LOG backups on a DB with Image field Normally, our log backups has < 10MB size In a big database with a lot of transactions per hour ).

But now we have a database, that is storing Images on a Image field.

the problem is, the database is HUGE ( it has now 500GB MDF, and 700GB on LDF file )and every log backup has more than 70GB !

What is the best way to store Images on a database? I've seen a lot of posts saying about store the Physical patch of the image on a VARCHAR field. But this way, we need to store the images in some place, right? It will fill the storage in the same way. Or am I wrong?

We have full backups every day at 22:00 and log backups every hour.

This is the sizes of the log backups.

EDIT1:

Each "Image" has a binary code, and it has 43679 characters ).
1 millions rows for this table ( 1.141.947 images ( with coduser, image, and etc )

EDIT2:
 We are now using varbinary(max), but it's not solving our problems. It has now almost 700mb after 20 minutes.

If you have a high volume of changes (inserts, updates or deletes) to those image BLOBs, then you would be best served to NOT store them within the database in the long run.  Assuming your not having performance issues, which some features like FileStreaming and FileTables can help with, your problem is going to be the "velocity" of change to those BLOBs of data, which will manifest a single byte change within the BLOB into a full recreation of the entire BLOB in the transaction.

One possible "work-around" if you MUST keep them in the database AND you can tolerate some data lose of the binaries is instead store them in a separate database on the same instance.  Set that database's recovery model to SIMPLE, and just do some extra differential or multi-day full backups.  Then to query the data, utilize cross-database joins and/or views that do the same.  Not an ideal situation, but at least a way to minimize data lose if your recovery needs can vary by individual data item (as opposed to the entire database).


What am I doing wrong with pg_hba.conf? I'm trying to make it so that local connections to the database don't require a login. So I added the line local    all   all    trust to the pg_hba.conf file, but then when I load pgAdmin and try to connect to the server it just says FATAL: could not load pg_hba.conf. What am I doing wrong?

There is likely an invalid entry in pg_hba.conf file. Reload it and then check the PostgreSQL server log for any pg_hba.conf error:

pg_ctl reload -D $PGDATA


Increasing the size of the Oracle datafile I have a third party application using Oracle 10g database with 1 tablespace and 1 datafile. They have assigned 10GB as the size of the tablespace which is almost full now. I know we can can increase size of this.
I want to clarify what if I add one more datafile and assign 10GB space to that?
Do I need to modify anything on application or will it work normally?
So all I need to do would be:
alter tablespace USER_DATA_TBS add datafile '/oradata/ovbs/user_data04.dbf' size 1000m;

Please help, I am a total beginner.

That does not increase the size of a data file, it adds one more datafile to the tablespace.
To enlarge an existing data file (for example to 20G):
alter database datafile '/oradata/ovbs/user_dataXX.dbf' resize 20g;

To add a new datafile to that tablespace (10G)
alter tablespace user_data_tbs add datafile '/oradata/ovbs/user_dataYY.dbf' size 10g;

Another option is to make the original datafile auto extensible:
alter database datafile '/oradata/ovbs/user_dataXX.dbf' autoextend on;

That way, the database will automatically extend the datafile as necessary, until the file system it resides on becomes full
The size of datafiles is totally transparent to applications. See https://docs.oracle.com/en/database/oracle/oracle-database/21/admin/managing-tablespaces.html#GUID-C207AC96-0F99-4F05-B3DD-E4A4FA17F98C for details


How to determine master in mysql master-slave I am setting up MySQL Master-slave replication and I am trying to figure out how to handle the failover situation where I promote the slave to master (in the event that the master goes down).

My application server needs to direct all writes to the current master, but I cannot use server level HA between the master and slave (heartbeat, keepalived) since the two db servers are on completely different subnets in different physical locations.

I think this is something that I need to handle at the application level. I can query the two servers and ask which one is a master, then perform all queries to that one.

Is there a query in MySQL to see if the current server is a master in a master-slave replica?

@RolandoMySQLDBA has answered the question accurately ... but he also pointed out that his solution was "quick-and-dirty."

And that is a very true statement. :)

The thing that concerns me here is not with that answer, but rather is that the original question appears to make an incorrect assumption:

  I can query the two servers and ask which one is a master, then perform all queries to that one.

The problem is that in MySQL replication, the master is never truly aware that it is the master.

The concept of "promotion to master" is not really a concept in MySQL asynchronous replication.  "Promoting" a MySQL server to the master role is something that happens "external to" MySQL servers, as opposed to something that happens "internal to" MySQL servers.

"Promotion to master" not done by any kind of server provisioning, because, technically speaking, every MySQL server that has binary logging enabled is a master, even if it never has a slave.  SHOW MASTER STATUS works exactly the same way and returns exactly the same result, slaves or not, and a master with 2 slaves is no more or less a master than a master with 1 slave or 0 slaves.  Similarly, a master whose slaves are all offline is still just as much a master, because when the slaves come back online, they'll pick up replicating where they left off.

In a sense, the only "awareness" on the part of either server is not whether it is a master, but rather, whether it is a slave (or "not").

That's what Rolando's solution asks: "are you a slave?"  If the answer is no, then the assumption is that this must be the master... which he also pointed out as a flawed assumption if STOP SLAVE; is issued.  But a stopped slave is still a slave, so "not a slave" (at any moment in time) doesn't equate to "being a master."

A similar test could be done on the presumed master:

SELECT COUNT(1) FROM information_schema.processlist
 WHERE user = 'the_username_used_by_the_slave';

or

SELECT COUNT(1) FROM information_schema.processlist
 WHERE command = 'binlog dump';

If the value is zero, then the slave's IO thread is not connected.  This test has a similar defect, in that if the slave is disconnected administratively, isolated, or failed, then it won't be connected.  So this doesn't really solve anything either.

Worse still (for either of these scenarios) the information_schema.processlist "table" is a virtual table that gets materialized every time it is selected from, and this takes time and costs resources.  The busier your server, the more it costs, because each thread's activity has to be peered-into.

A more lightweight solution would be:

SELECT @@global.read_only;

On a slave, you could/should set the global variable read_only so that users without the SUPER privilege cannot unintentionally write to it (and your application should not have SUPER).  If you manually "promote" the slave to the master role, you SET GLOBAL read_only = OFF to enable writes.  (Replication can always write to the slave, no matter how this is set).

But this still, I think, misses an important point:

I would propose that the application should not be making this decision heuristically in a master/slave setup, and certainly not on a connection-by-connection basis.  The application should use either a hard configuration option, or the application should remain unaware and have the database connection destination handled by something else.

Or, at minimum, the application should never switch over until the master fails, and then it should never switch back on its own.

Here's why I say that: once the "decision" is made -- by whoever or whatever -- to make another server the master, the application cannot be allowed for any reason to switch back to the original master, even after it comes back online, without intervention.

Let's say you hit a bug and there's a software-forced crash; mysqld_safe dutifully restarts mysqld, and InnoDB crash recovery performs flawlessly.  But that takes a few minutes.

Meanwhile, the master is down so your application has switched to the slave.  Transactions have been created, orders placed, funds transferred, comments posted, blogs edited, whatever your system does.

Now, the original master comes back online.

If your application switches back to the original master, you are in an absolute world of hurt, because the very next thing that is likely to happen is that replication stops due to an inconsistency, because your application has changed data on the slave in the mean time.  You now have two database servers with inconsistent data that you will have to reconcile manually.  If there are dollars or points or credits involved, you now have mismatched balances.

So it's critical that the application not be allowed to switch back to the original master without your intervention.

Wait, did you just find the problem with this scenario as I described it?  The master has failed but your application won't be using the slave, because it thinks the slave is still the slave and not the master... the information_schema.processlist query on the slave will still return non-zero even if the master server is powered down.

So there isn't much point in the application discovering anything, since you'll have to manually STOP SLAVE for that test to be useful.

Perhaps a better approach if you want the application to be able to switch would be to  configure the servers with circular replication.

Circular replication has inherent problems of its own, but as long as your application is only always ever writing to one server at a time, most of those issues become non-issues.  In other words, both machines are always and simultaneously both master and slave, in a replication sense, but your application, via some mechanism, is always only pointing to one machine at a time as the "master" to which it can and should write.

You can't deploy HA tools on the MySQL servers due to their separation, but you could implement it with HAProxy running on the application server(s).  The application connects to "MySQL" on localhost, which isn't MySQL at all, but is actually HAProxy ... and it forwards the TCP connection to the appropriate MySQL machine.

HAProxy can test the connections to the MySQL servers and only offer traffic to a MySQL machine that is accepting connections and allowing authentication.

The combination of HAProxy running on the application server (its demand for resources won't be substantial compared to everything else the application server has to do -- it's pretty much just tying sockets together and ignoring their payload) ... and MySQL circular replication would be the approach I would probably take in this instance, based on what's known from the question.

Or, for a strictly manual setup, go with something much simpler than "discovery," like an entry in the app server's /etc/hosts file with a hostname that the application uses to connect to MySQL, which you could manually update -- assuming promotion of slave to master is intended to be a manual process.

Or, something more complex, using Percona XtraDB Cluster.  For this, though, you'd want to add a third server, because with 3 nodes in PXC, if 2 servers can see each other but get isolated from 1 server (if all three are still running) the 2 servers keep running happily but the 1 server curls up into a little ball and refuses to do anything since it realizes it must be the odd one out.  This works because the 2 realize they still constitute a majority of the nodes that were online before the network split and the 1 realizes that it isn't.  With PXC, it doesn't really matter which server your application connects to.

I say all of this is to say "don't have the application poll the servers to see which one is the master" because it will bite you sooner or later and it will nibble away at your performance up until the day it bites.


SQL Server Table Backup A friend of mine has run into this problem on SQL Server 2008, please suggest a solution:

He came to know that auto backup scheduled on server were not taking place since last couple of days, when he tried manual backup of our database it gave following error:

  System.Data.SqlClient.SQLError: Read "D:\DB\data.mdf" failed: 1 (Incorrect function.) (Microsft.SqlServer.Smo)

To safeguard our data he thought of rebooting the server in safe mode & copy the .mdf & .ldf files manually to a safer location but it gave an error after completing 80% of process (MS DoS invalid ...) which looked as if hard disk has some issue & it's not able to read the files from specific location.

He thought of copying the data table by table after creating a fresh database from DB script, during the process one of our developer by mistake dropped two important tables & he has not used BEGIN TRANSACTION option also

Now he is looking forward for an expert advise if we have some recovery tool to recover those tables & data in those tables.

I would run DBCC CheckDB and see what corruption you have in the database.  It sounds like it is disk corruption.  If that is the case you will likely have to restore from backup.

After you restore from backup you can compare the two tables in question.

You can also try BCP'ing the bad table out but if it is a i/o disk error it will likely die if the corruption is in the heap.


Update rank on a large table I have a mysql table that has now 12 million records and I'm trying to update a column (rank) with the following UPDATE code:

SET @r=0;
UPDATE records SET rank= @r:= (@r+1) where type = 2 ORDER BY seconds DESC;

Almost every minute new rows are inserted. So, after one hour, I have several rows not ranked and I need to update the whole table again.

However, it takes too much time. Is there any way to optimize or a different way to update the table faster? Maybe a different approach? Partitioning the table?

Rhetorical Question: Do you really need to rank all 12,000,000 records?

At my employer's web hosting company, we have a gaming client that ranks its top 10,000 players per game platform. They use the same construct you do. You should just limit it to the top 10,000 or whatever number is reasonable

SET @r=0;
UPDATE records SET rank= @r:= (@r+1) where type = 2 ORDER BY seconds DESC LIMIT 10000;

I would run your original query at midnight to get them all

If you must try to rank all, maybe you can try something like this:

DROP TABLE IF EXISTS ranktable;
CREATE TABLE ranktable
(
    rank INT NOT NULL AUTO_INCREMENT,
    rec_id INT NOT NULL,
    PRIMARY KEY (rank)
);
INSERT INTO ranktable (rec_id)
SELECT rec_id FROM records WHERE type=2 ORDER BY seconds DESC;
UPDATE records A INNER JOIN ranktable B USING (rec_id) SET A.rank = B.rank;

What may further help is to index your records table like this:

ALTER TABLE records ADD INDEX type_seconds_ndx (type,seconds);