In Between causes my query to return "wrong" values I have a little problem right here...

So all in all I have exactly two tables:

* resources (ID, resourcetype_ID, chunk_ID, position)

* chunks (ID, x, y)

The first one stores some rows which belongs to the certain chunk.
The second one just stores all the chunks with a different set of x and y as some kind of position.

My goal is to select all the resources that belong to a certain range of chunks.
But I have made a mistake somewhere. I always get the wrong x and y positions, when I select the resources.

SELECT resources.*, chunks.x, chunks.y
FROM resources, chunks
where chunk_ID in (SELECT ID
                   FROM chunks
                   where x between 17185-1 and 17185+1
                     and y between 10846-1 and 10846+1)
group by ID

This query above returns all the resources that belongs to the different chunks. But the returned x and y position is for all of them the same. Why does this happen?

(As you can see the resources with "chunk_ID" = 1 have the same x and y as the resources with "chunk_ID" = 5 ... even if those chunks have completely different x and y pairs...)

Normally all resources should have different x and y values regarding to the chunk they belong to.

Your query:

SELECT resources.*, chunks.x, chunks.y
FROM resources, chunks
where chunk_ID in (SELECT ID
                   FROM chunks
                   where x between 17185-1 and 17185+1
                     and y between 10846-1 and 10846+1)
group by ID

I see several issues:

The two tables in the FROM clause are not joined (that's the reason for the irrelevant results you get, the two tables are correlated through the subquery* but the FROM clause introduces an additional non-correlated chunks reference which produces all these unrelated results):

FROM resources, chunks

Join them:

FROM resources JOIN chunks ON resources.chunk_id = chunks.id

Since chunks is in the FROM clause, we don't need* the subquery no more, to check x and y:

where chunk_ID in (SELECT ID
                   FROM chunks
                   where x between 17185-1 and 17185+1
                     and y between 10846-1 and 10846+1)

Simplify WHERE:

WHERE chunks.x BETWEEN 17185-1 AND 17185+1
  AND chunks.y BETWEEN 10846-1 AND 10846+1

You have SELECT * combined with GROUP BY. This is a recipe for spurious, wrong results.

Just remove the GROUP BY.

The query becomes after using all the above comments (and some polishing with table aliases):

-- Suggested query
SELECT r.*, c.x, c.y
FROM resources AS r
     JOIN chunks AS c
     ON r.chunk_id = c.id
WHERE c.x BETWEEN 17185-1 AND 17185+1
  AND c.y BETWEEN 10846-1 AND 10846+1 ;

* Just for completeness, we could have kept the IN subquery and remove chunks from the FROM clause.

This would have the restriction that we couldn't have any column from chunks in the SELECT list - and more likely worse performance than the JOIN query:

-- Don't use this one
SELECT r.*
FROM resources AS r
WHERE r.chunk_d IN
      ( SELECT c.id
        FROM chunks AS c
        WHERE c.x BETWEEN 17185-1 AND 17185+1
          AND c.y BETWEEN 10846-1 AND 10846+1
      ) ;


Select blocking issue The database was not responding to simple select statements like

select * from table

I noticed in sys.dm_os_waiting_tasks that one session is blocking, blocking session was pointing to a stored procedure which use lock & Tran like below

    BEGIN TRAN

declare @min numeric ,@com numeric

select top 1  @com=companyid from tblcompanies
order by newid()

select @min=min(productid) from tblproducts(UPDLOCK)
where companyid=@com and (lockstatus<>1 or lockstatus is null )

update tblproducts
set lockStatus='1'
where productid>=@min and productid<@min+4

select distinct top 4 s.productname,s.productid,s.companyid,r.Type,r.requestType,r.UserID
from tblcompanydetail rd inner join tblproducts s
on rd.productid=s.productid
inner join tblcompanies r
on r.companyid=s.companyid
where s.productid>=@min and s.productid<@min+4

if @@error >0 rollback tran

commit tran

Please advise me if I have written it wrong.

Here's how I would re-write this. Various comments inline. These changes may help reduce blocking but it's really hard to see how even the original would block for very long unless these are all heaps and/or every table access yields a scan...

DECLARE @min INT, @com INT; -- these are INTs, right?

SELECT TOP 1 @com = companyid
  FROM dbo.tblcompanies -- always use schema prefix
  ORDER BY NEWID(); -- random company? really?

-- Minimize transaction duration. Any real need to select the
-- random company & perform final select inside the transaction?

BEGIN TRANSACTION;
  BEGIN TRY

    SELECT @min = MIN(productid)
      FROM dbo.tblproducts WITH (UPDLOCK)
      WHERE companyid = @com
      AND (lockstatus <> 1 OR lockstatus IS NULL);

    UPDATE dbo.tblproducts
      SET lockStatus = '1' -- is this column numeric or string?
      WHERE productid >= @min AND productid < @min + 4; -- values guaranteed contiguous?

    COMMIT TRANSACTION;

    SELECT -- don't believe DISTINCT or TOP are required here
      s.productname, s.productid,   s.companyid,
      r.Type,        r.requestType, r.UserID
    FROM dbo.tblproducts AS s
    INNER JOIN dbo.tblcompanies AS r
      ON r.companyid = s.companyid
    WHERE s.productid >= @min AND s.productid < @min + 4
    AND EXISTS -- since no columns from rd are needed
    (
      SELECT 1 FROM dbo.tblcompanydetail
      WHERE productid = s.productid
    );

  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
  END CATCH


Why is my SQL .mdf file 30GB when my data is only 4GB I used this query to find the size of the data in each of my SQL tables and then aggregate it into one number:

SELECT sum(t.totalspacemb) FROM (
SELECT
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages,
    sum(a.used_pages) as UsedPages,
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND
    i.index_id <= 1
GROUP BY
    t.NAME, i.object_id, i.index_id, i.name) t

I've truncated millions of rows of data from a large audit table and subsequently shrunk the db and the files, but the size of the .mdf stays the same.  I switched to simple logging and shrunk again which reduced the log file, but the .mdf is still the same.  Why am I unable to recover the free DB space?

Shrink file has to move allocated pages situated at the end of the file to the beginning of the file. The file can be shrunk only as much as the offset of the last allocated page. If you did this correctly things should work:

DBCC SHRINKFILE(<DataFile>, 4096);

If you believe you did this correctly but the file stays at the original size, open a support case.


Sending email from SQL Server without using DBMail Is there a way to send email from SQL server stored procedures without using DBMail? DBMail is hard coded by default to use base64 encoding for email messages. As far as I can tell there's no way to override this.

We need to parse/process messages on our mail server and base64 encoded messages can't easily be parsed. Rather than trying decode them we'd like to send email using some other method.

Preferably one that doesn't use CDOSYS or CDONTS either? Why? Because we're migrating to Windows Server Core and Server Core doesn't support CDOSYS or CDONTS.

See here: https://technet.microsoft.com/en-us/library/hh922965.aspx

Specifically This:

However, before you attempt to host a Web site on Server Core, it’s worth noting
code that makes use of System.Web.Mail will fail because of the dependency on
CDOSYS (which does not exist in Server Core).

Microsoft recommends modifying Web application code to use System.Mail.Net
instead of System.Web.Mail.

This is refering asp/.net code on a web server but presumably the same restriction applies to SQL server installed on a Core server.

So with those two limitations in mind (no dbmail, and no cdonts/cdosys) how can we send mail from stored procedures? Any ideas? :)

Thanks!
Brad

Configure the Smart Host:

Click Start, point to Programs, point to Administrative Tools, and then
click Internet Services Manager to open the IIS Manager.
Open the tree for your server. Right-click Default SMTP Virtual Server, and then click Properties.
Locate the Delivery tab, and then click Advanced.
Type the name of your SMTP mail server in the Smart Host text box. If you do not know the name of your SMTP mail server contact your mail administrator.
Make sure that the Simple Mail Transfer Protocol (SMTP) service is running. The SMTP service is part of the IIS Admin Service. Therefore, the IIS Admin Service must also be running.

Create a Stored Procedure to Send CDONTS E-Mail:
You can use code that is similar to the following to create a stored procedure in your database that sends e-mail by using the SQL Server OLE Automation stored procedures to invoke the CDONTS object model.
CREATE PROCEDURE [dbo].[sp_send_cdontsmail]  @From varchar(100),  @To varchar(100),  @Subject varchar(100),  @Body varchar(4000),  @CC varchar(100) = null,  @BCC varchar(100) = null  AS  Declare @MailID int Declare @hr int  EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT EXEC @hr = sp_OASetProperty @MailID, 'From',@From  EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body  EXEC @hr = sp_OASetPropert, @MailID, 'BCC',@BCC  EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject EXEC @hr = sp_OASetProperty @MailID, 'To', @To  EXEC @hr = sp_OAMethod @MailID,'Send', NULL  EXEC @hr = sp_OADestroy @MailID

Next, use the stored procedure that you created and provide the correct parameters:
exec sp_send_cdontsmail 'someone@example.com','someone2@example.com','Test of CDONTS','It works'
Note Only members of the sysadmin fixed server role can run the OLE Automation stored procedures. If the SQL Server user is not a member of the sysadmin fixed server role, you cannot use the stored procedure that is mentioned in the example to send e-mail. In such a situation, you may have to develop a client application to send e-mail with CDONTS. For example, you can use a Microsoft Visual Basic application.
CDONTS [CDO for Microsoft Windows NT Server - It's a Simple Mail Transfer Protocol (SMTP) specific OLE server that is specifically designed to provide messaging functionality to Web-based applications], CDONTS sends the e-mail to the local SMTP virtual server. The server then routes the e-mail to the SMTP mail server that is specified in the Smart Host text box. The SMTP mail server sends the mail to the e-mail address that is specified in the To: argument ("someone2@example.com" in the example). The name that is specified in the From: argument appears as the sender of the e-mail ("someone@example.com" in this example) with the subject "Test of CDONTS" and the message "It works" as the body of the e-mail. No one is copied on the e-mail because you did not supply an argument for the CC or for the BCC field. You can modify the example so that it will send HTML-based e-mail or attachments.
For documentation about CDONTS, visit the following Microsoft Web site:

http://msdn.microsoft.com/library
https://support.microsoft.com/en-us/help/312839/how-to-send-e-mail-without-using-sql-mail-in-sql-server


Name of the product price, line-item database pattern? What is the database pattern called where (for example) the current price of a product is stored in one table, and when someone makes a purchase the current price of the product is copied to a line item table, and is redundant until the price changes?

Not every approach to common problems has a name.  Certainly at least not a commonly agreed name.  The term "design patterns" applies just as well to common database designs as it does to OOP.  I would say data modellers are a little less enamoured with the idea of design patterns than OOP programmers.

I'm not familiar with a commonly agreed name for the design approach that you're describing.  I call it transactional logging.

As an aside, the price in the sale item table is not technically redundant, even if the price hasn't changed.  The price in the product table is the current book price, the price in the sale item table is the actual price charged.  There is no way of knowing for sure what price was actually charged from looking at book price.  Even if you have a book price history table it might have been possible, depending on your system's rules, for the price to be overridden manually.  The two data attributes are semantically different, even if they happen to be coincidentally equal for some period of time.


Restoring dump(backup): ERROR 1022 (23000) at line 44: Can't write; duplicate key in table I'm doing tests to change a current table from my InnoDB database to Archive, but I had a problem trying to restore a dump after the change.

After several tests, the problem is related to the value declared in the AUTO_INCREMENT attribute in CREATE TABLE. But this script is created automatically.

How can I fix this error so that I can restore the dump quickly without having to change it?

...

--
-- Table structure for table `tabela`
--

DROP TABLE IF EXISTS `tabela`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tabela` (
  `cd_tabela` int(11) NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY (`tabela`)
) ENGINE=ARCHIVE AUTO_INCREMENT=3081487 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tabela`
--

LOCK TABLES `tabela` WRITE;
/*!40000 ALTER TABLE `tabela` DISABLE KEYS */;
INSERT INTO `tabela` VALUES (1,...), ... (in this line the error appears)

...

In the block you have added to your question it is clear that you create a table with ENGINE=ARCHIVE. The Archive storage engine in MariaDB (and the other flavors of the ecosystem) does not support indexes.

The error you get is because of a UNIQUE/PRIMARY index reporting a duplicate. This means you are not restoring your backup to an ARCHIVE table.

I would assume that your backup includes the schema, and that it creates the table as InnoDB (or another storage engine that supports indexes). Can you make sure that the table is still the ARCHIVE storage engine after trying to import the dump?

Remove this part from your dump, or create it as ARCHIVE without specifying any indexes and importing the data should work.

Alternatively, you can remove the creation of indexes from your dump file and later on changing it to the storage engine you want it to be.


Why am I getting blocked when I call OBJECT_NAME(object_id, database_id) from sys.dm_tran_locks? I'm working on a query to tell what objects have locks from a given session and I'm running into some blocking issues.

Here is my basic query:

SELECT request_session_id AS session_id,
    request_owner_id AS transaction_id,
    OBJECT_SCHEMA_NAME(resource_associated_entity_id, resource_database_id),
    OBJECT_NAME(resource_associated_entity_id, resource_database_id),
    COUNT(1) AS lock_count
FROM sys.dm_tran_locks WITH (NOLOCK)
WHERE resource_type = 'OBJECT'
GROUP BY request_session_id, request_owner_id,
    resource_database_id, resource_associated_entity_id

When I run this query I am occasionally blocked by one of the sessions actually holding the locks.  If I remove OBJECT_NAME and OBJECT_SCHEMA_NAME then I don't have any problems.  I've tried dumping the information into a table and then using the functions on the values in the table with similar problems.

This leads me to believe the problem is in the OBJECT_NAME and OBJECT_SCHEMA_NAME functions but I'm not sure why or how to get around it.  I'm also not certain why I am sometimes blocked and sometimes not.  Does anyone have any suggestions?

Because the metadata functions do not obey transaction isolation semantics. If you want to avoid getting blocked, join to sys.schemas and sys.objects instead of using the metadata functions. This will also allow you to set the isolation level in a single statement instead of peppering NOLOCK hints all over the query...

This was reported by Adam Machanic on Connect several versions ago:

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=432497

Closed as Won't Fix.


How can the System databases in SQL Server 2016 be moved using Powershell? I'm trying to move the system databases using Powershell ie. without using any T-SQL.

Using SMO :
Install-Module SQL-SMO
$smo = New-SMO -ServerName localhost
$smo.databases["TempDB"].PrimaryFilePath= "F:\Tempdb\"
$smo.databases["TempDB"].Alter()    gives an error:

  'PrimaryFilePath' is a ReadOnly property.

The logfile moves fine though.
$smo.databases["TempDB"].LogFiles[0].Filename = "F:\Tempdb\tempdb.ldf"

Couldn't locate any specific cmdlets in the SQLServer or DBATools modules either. Copy-DbaDatabase comes closest; but not exactly.

The Restore-DbaDatabase in dbatools right now blocks the ability to restore the system databases. It is on the roadmap at some point to work out the process. The block in that command is why Copy-DbaDatabase would fail as well, and you can't move any system database by simple detach/attach method.

Overall it could be worked out to perform the whole task in PowerShell using dbatools commands with a mix of just "manually prepared" code around SMO.


Why does postgres use more space for a time with timezone than a timestamp with timezone? The documentation on date/time data types state that timestamp with timezone takes 8 bytes, while time with timezone takes 12 bytes. They both have the same resolution (1 microsecond), and on the face of it timestamp with timezone is storing more information.

Can anyone explain this behavior?

I'm not planning on using time with timezone for reasons explained on the same page.

time with time zone stores microseconds (8 bytes) and the time zone (4 bytes).  timestamp with time zone stores just the microseconds and converts the time zone at display time.  Because of the conceptual weirdness of the time with time zone type, the time zone needs to be stored explicitly.  You don't actually need 8 bytes to store the number of microseconds in a day, but 4 bytes wouldn't be enough.  If you really wanted to, you could probably devise a more compact storage format for time with time zone, but in practice nobody cares.


Repeatable read: the transaction may not be serializable with respect to other transactions The text below is from 'Database System Concepts, Silberschatz. I can't understand the bolded part. How the transaction may not be serializable with respect to other transactions? And also how a transaction may find some of the data inserted by a committed transaction, but may not find other data inserted by the same transaction?

Repeatable read allows only committed data to be read and further
requires that, between two reads of a data item by a transaction, no
other transaction is allowed to update it. However, the transaction
may not be serializable with respect to other transactions. For
instance, when it is searching for data satisfying some conditions, a
transaction may find some of the data inserted by a committed
transaction, but may not find other data inserted by the same
transaction.

This is related to the concept of Isolation Levels. See https://en.wikipedia.org/wiki/Isolation_(database_systems) for details.
Isolation levels are defined by the presence (or absence) of one or more of the following phenomena:

Dirty Reads

This means the ability to see uncommitted data: a change made by another transaction, but not committed yet. That is seldom seen in real life - very few database systems allow that. It is risky because it may return inconsistent data. For example a bank transfer: the origin account has been debited, but the destination account has not yet been credited. If your application reads the accounts in between the two updates, it looks like the money has disappeared!

Non-repeatable Reads

This is what the sentence you quote is talking about. It means that you can see changes that have been committed by other transactions. It implies that, should you repeat the same query in a transaction, you may get different results, because someone may have changed the data since you read it previously. It is a problem for any application that involves checking a stock before confirming an order: the application finds that there are 5 gizmos in stock, then places an order for 4 of them - but in the meantime someone else has ordered them.
This phenomenon can be prevented by placing read locks on everything you read in a transaction. That prevents anyone else from updating the data while you are reading it. It is a bit like if you looked at a pair of shoes in a shop window, and that simple fact makes the shoes unsalable to anyone else.

Phantom Reads

Assume you do place read locks on everything you read. That would still not guarantee that you get the same answer when you repeat a read: everything you read first is safe: nobody can update or delete any of it. However simple read locks do not prevent anyone from inserting a new row. This can lead to inconsistencies in reporting. Say you print out a sales report with the detailed sales. Then you want to print the total and you do that by doing another query to compute the total. That total may not match the details: that is because while you were reading the individual lines, someone has inserted a new sale and committed it.
Preventing phantoms is much harder: placing read locks on the rows read in order to prevent updates is not possible: there is nothing to lock since the new rows do not exist yet. Typical techniques are to place locks on the entire table - which is really bad for concurrency and effectively single-threads inserts. Better techniques involve placing locks on index nodes, since an index node represents a range or rows, effectively preventing any insert in a range of rows read by some application.
Those three phenomenons are used to describe the isolation levels:
Read uncommitted is the lowest isolation level: essentially no consistency at all (dirty reads are allowed).
Read committed prevents dirty reads only. Reads are not repeatable: repeating the same read can have different results
Repeatable reads prevents non-repeatable reads, but does not prevent phantom reads. A read will return the same rows as the previous execution - but it may return more rows: those rows did not exist yet on the first read.
Serializable prevents phantom reads. It is the highest level of consistency. It guarantees that all reads done in a transactions always deliver the exact same result. It is called serializable because the effect of the execution of a series of concurrent transactions is identical to a serial execution of those transactions in one specific order.


Changing Default Values of Percona Audit Log Have I missed Something here? I am installing the Percona Audit Log Plugin (MySQL 5.5).

I can install it fine using:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

I can then see all the default variables with:

SHOW GLOBAL VARIABLES LIKE 'audit%';

Looking at the documentation it tells me that for the most part they are not Dynamic.

So how do I set them to what I need at the time I install the plugin?
From what I can tell the only option is to add them to my my.cnf file and restart the Database.

That's fine for a Test System, but I can't go round restarting my LIVE databases, just to install a plugin, surely.

There are a handful of options that are Dynamic, but for the most part they have to be added to the cnf file, which means you need to restart your live databases if you want to use it.


Why is != not working? how to compare with null values? I am having a hard time understanding why the following query is not working?

select id,sold_dealer_id from myi_corporate where sold_dealer_id != 36;

When I do the following I am getting the result

select id,sold_dealer_id from myi_corporate where sold_dealer_id = 36;

However for != I am not getting the opposite results.

I am returning to psql after a long time after using MongoDB and might have forgotten the basics. Any suggestion is much appreciated

You can use != rather than the SQL standard operator <>, but I recommend using the latter. That has no influence on your problem though.

Your table must contain some NULL values in sold_dealer_id.

Now NULL = 36 is not true, but NULL <> 36 is also not true, so such lines are excluded from both query results.

You can use sold_dealer_id IS DISTINCT FROM 36 to get the opposite of sold_dealer_id = 36. That operator will treat NULL values as if they were normal values.


Office Hours in PostgreSQL How to store office hours in PostgreSQL rows, so that I can calculate the office hours.
Example:

We have open from 9:00 to 18:00 from Monday till Friday.
Saturday we have open from 10:00 to 15:00
from 24. Dec to 31. Dec we have open from 10:00 to 13:00 (but not on Saturday and Sunday)
bank holidays like 25/26 December are closed.
All above rules are valid until 31. Dec 2021.
Starting from 01. January 2022 we have open from 10:00 to 18:00 from Monday till Friday. Saturday is open like before.

I would like to store this data in rows, so that we can develop an interface for it.
And then there needs to be a method which uses the rows/rules to calculate the specific opening hours.
Different timezone are not important in this context.
I use PostgreSQL version 12.6. But if needed I can upgrade to a newer version.

What you want to do is something like this (all the code below is available on the fiddle here):
CREATE TABLE work_calendar
(
  the_day    DATE NOT NULL PRIMARY KEY,
  day_name   TEXT NOT NULL,
  start_time TIME(0) NULL,
  end_time   TIME(0) NULL
);

I also did this:
CREATE INDEX st_ix ON work_calendar (start_time);  -- these indexes reduce the execution time
CREATE INDEX et_ix ON work_calendar (end_time); -- run EXPLAIN (ANALYZE, BUFFERS)

You can experiment with EXPLAIN (ANALYZE, BUFFERS) and performance on your own H/W and S/W setup - see the fiddle.
and for bank_holidays - some of which are variable (Easter for example):
CREATE TABLE bank_holiday
(
  the_day TEXT NOT NULL,
  bh_date DATE NOT NULL
);

INSERT INTO bank_holiday
VALUES
('New Year''s Day',    '2021-01-01'::DATE),
('St. Patrick''s Day', '2021-03-17'::DATE),  -- Irish feast day
('Easter Monday',      '2021-05-04'::DATE),
('May Day',            '2021-05-01'::DATE),
('Christmas Day',      '2021-12-25'::DATE),
('St. Stephen''s Day', '2021-12-26'::DATE);

And then you run the following SQL:
-- EXPLAIN (ANALYZE, BUFFERS) -- check with different indexing strategies.
WITH t (opening_day) AS
(
  SELECT  GENERATE_SERIES
  (
    '2021-01-01'::DATE,
    '2021-12-31'::DATE,
    '1 DAY'
  ) AS ds
)
INSERT INTO work_calendar
SELECT
  opening_day,
  TO_CHAR(opening_day, 'Day'),

  CASE

    -- Set start time = '00:00:00' for bank holidays and Sundays

    WHEN (opening_day IN (SELECT bh_date FROM bank_holiday))
      OR EXTRACT(DOW FROM opening_day) = 0 THEN '00:00:00'::TIME

   -- Ater this WHEN, the CASE statement is over - it's like BREAK; in C (or JAVA...)
   -- the code drops out of the CASE statement.

   -- So, now, we tackle Saturdays and the Christmas period dates:

   -- set start time = 10:00:00 for Saturdays that are not bank holidays and
   -- set start time = 10:00:00 for days from Christmas Eve to New Year's Day

    WHEN EXTRACT(DOW FROM opening_day) = 6
      OR (opening_day >= '2021-12-24' AND opening_day <= '2021-12-31') THEN '10:00:00'::TIME

    -- Now, we deal with the rest - i.e. Mondays to Fridays of those days which are
    -- not Bank Holidays or in the Christmas period.

    WHEN EXTRACT (DOW FROM opening_day) BETWEEN 1 AND 5 THEN '09:00'::TIME

    ELSE NULL

  END AS ot,

  CASE

    -- Set end time = '00:00:00' for Bank Holidays and Sundays

    WHEN (opening_day IN (SELECT bh_date FROM bank_holiday))
      OR EXTRACT(DOW FROM opening_day) = 0 THEN '00:00:00'::TIME

   -- Ater this WHEN, the CASE statement is over - it's like BREAK; in C (or JAVA...)
   -- the code drops out of the CASE statement.

   -- So, now, we tackle the Christmas period dates: - the Christmas period end time
   -- is 13:00 and not 15:00 - i.e. it's not (unlike for start time) the normal
   -- Saturday end time - so we need an extra WHEN in the CASE

   -- set end time =   13:00:00 for Saturdays that are not bank holidays and
   -- set start time = 10:00:00 for days from Christmas Eve to New Year's Day

      WHEN opening_day >= '2021-12-24' AND opening_day <= '2021-12-31'
        THEN '13:00:00'::TIME

    -- Now, we deal with normal Saturdays which are not Bank Holidays or which
    -- don't fall in the Christmas period.

      WHEN EXTRACT(DOW FROM opening_day) = 6 THEN '15:00:00'::TIME

    -- Finally, we have the normal working day - end time is 18:00

      WHEN EXTRACT (DOW FROM opening_day) BETWEEN 1 AND 5 THEN '18:00'::TIME

    ELSE NULL

  END AS ft
FROM t;

And to check the result, I had this query:
SELECT * FROM work_calendar
WHERE the_day >= '2021-01-01' AND the_day <= '2021-01-13'
OR    the_day >= '2021-03-13' AND the_day <= '2021-03-24'  -- St. Patrick's day Bank Holiday
OR    the_day >= '2021-04-01' AND the_day <= '2021-04-10'  -- Easter Monday
OR    the_day >= '2021-04-28' AND the_day <= '2021-05-05'  -- May Day
OR    the_day >= '2021-12-20' AND the_day <= '2021-12-31'  -- Christmas period
ORDER BY the_day;

The idea behind this is to verify that the SQL is doing what I hope it's doing - so I check the "edge" cases - i.e. beginning of year, end of year, around Bank Holidays and over the Christmas period to New Year's Eve.
There are 55 records in the result set - I'll only show those around the New Year and the Christmas period:
the_day     day_name    start_time  end_time
2021-01-01  Friday      00:00:00    00:00:00  -- BH - no work
2021-01-02  Saturday    10:00:00    15:00:00  -- Sat. st 10:00, et 15:00
2021-01-03  Sunday      00:00:00    00:00:00  -- Sun. day off - so far, so good
2021-01-04  Monday      09:00:00    18:00:00  -- Normal work resumes
2021-01-05  Tuesday     09:00:00    18:00:00  --        "
...
... gap  -- inspection shows that these are all OK
...
2021-12-20  Monday      09:00:00    18:00:00  -- Normal working day
2021-12-21  Tuesday     09:00:00    18:00:00  --        "
2021-12-22  Wednesday   09:00:00    18:00:00  --        "
2021-12-23  Thursday    09:00:00    18:00:00  --        "
2021-12-24  Friday      10:00:00    13:00:00  -- Christmas Eve - start of Christmas period 10 - 13
2021-12-25  Saturday    00:00:00    00:00:00  -- Christmas Day - BH, no work!
2021-12-26  Sunday      00:00:00    00:00:00  -- Sunday + St. Stephen's day - no work
2021-12-27  Monday      10:00:00    13:00:00  -- Christmas period working
2021-12-28  Tuesday     10:00:00    13:00:00  --          "
2021-12-29  Wednesday   10:00:00    13:00:00  --          "
2021-12-30  Thursday    10:00:00    13:00:00  --          "
2021-12-31  Friday      10:00:00    13:00:00  --          "
55 rows

A further check:
SELECT * FROM work_calendar WHERE start_time IS NULL;

returns no records - as we would expect!
So, we can see that we have the appropriate hours for the appropriate days - i.e. weekday, 09:00 - 18:00, Saturday, 10:00 to 15:00 and nothing on Sunday. Bank Holidays and the Christmas period are also catered for. Obviously, you will choose Bank Holidays for Germany/Saxony.
Re. performance. I would encourage you to check your own system for performance - but if it's only a year, then I don't imagine that it's going to be a huge problem - but it's always worth bearing in mind as good practice.


Mongodb Standalone Performs better than Replica Set I am currently using a standalone mongodb with high read operations. Since replica sets could provide high read performance, I setup a 3 node replica set on 3 vms with read preference as "secondary preferred". However I could not see any performance improvement, in fact the replica set runs a little slower(~2 secs) than the standalone.The configuration is as follows, 4GB of RAM, 7GB of data, 50 GB of hard disk on all 3 vms and for the standalone db and We are using aggregate queries. I would like to know what could be the real reason for the replica sets to function slower compared to the standalone db.

I would like to know what could be the real reason for the replica sets to function slower compared to the standalone db.

Replica sets provide high availability and data redundancy, but these benefits are best realized using separate server resources.

With multiple data-bearing replica set members on a single server you are adding contention for the underlying hardware resources (CPU, RAM, disk):

Every write has to be replicated and applied by each of the replica set members (which each have their own journal, oplog, and data files), so there will be significantly more I/O as compared to a standalone server.
A replica set on a single server will be using RAM to store multiple copies of the same data; a standalone can have more of your data & indexes in the same amount of RAM.

If the reason for your performance challenge is a lack of resources, you should be adding dedicated resources (such as more RAM or faster storage) rather than sharing existing resources.

Note that even with replica set members on multiple servers, secondary reads are not a panacea. For some considerations, see: Can I use more replica nodes to scale?.

Before adding additional server resources, I recommend starting with optimising indexes to support your common queries and aggregations. See: Index Strategies in the MongoDB documentation.


Transactional Replication between SQL Server 2017(Publisher+Distributor) and SQL Server 2019(Subscriber) So here's the replication scenario that we need to set up.
At organization A, We have SQL Server 2017 which is both the publisher and a distributor.
At organization B, on a remote server, we have a SQL Server 2019 which needs to be a subscriber.
When we are trying to set up the transactional replication between these two servers and while adding the subscriber, it throws this error: The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication.
According to the Replication compatibility matrix, it looks like it is supposed to be working fine, but then we cant seem to understand why this is still failing.

Similar Stack Question that I have referred to
Another link that talks about Microsoft Replication between two different versions of SQL servers

What are the Compatibility Levels for the replicated database of your SQL Server 2017 and SQL Server 2019 instances? Is the 2017 instance set to the default level of 140 and 2019 instance set to the default level of 150? This is a guess but if the 2017 server is set to an old enough level, perhaps that's the issue?
You can verify the Compatibility Levels of the database here: View or Change the Compatibility Level of a Database
Or using this query from the docs linked above:
SELECT compatibility_level
FROM sys.databases
WHERE name = 'YourReplicatedDatabaseName';


how to scaffold the database triggers I'm trying to find a way to include table triggers when reverse-engineering the database. but I can't seem to find a way to do it.
I'm using SQLserver and .netCore 3.1 with efcore.
and each time I scaffold the dbcontext , the triggers aren't scaffolded.
Here's my command
dotnet ef dbcontext scaffold "server={server};database={database};user={user};password={password}" Microsoft.EntityFrameworkCore.SqlServer --project  "{project}" --output-dir "Models" --force

It's not possible to scaffold Triggers, and it doesn't make much sense since they are a database specific type of object (not sure what they could map to in C#?). They don't directly describe the database model, as opposed to Tables, Columns, and Keys. What is your end goal that you wanted to scaffold the Triggers?
Please see Entity Framework Core - Reverse Engineering documentation for more information.
Particularly the How it works section details what is scaffolded from the database:

Reverse engineering starts by reading the database
schema. It reads information about tables, columns, constraints, and
indexes.
Next, it uses the schema information to create an EF Core model.
Tables are used to create entity types; columns are used to create
properties; and foreign keys are used to create relationships.
Finally, the model is used to generate code. The corresponding entity
type classes, Fluent API, and data annotations are scaffolded in order
to re-create the same model from your app.


MongoDB document insertion and referencing I am new in mongo and I have a sql background. I am currently working on  a project about migrating data from mysql to mongo. I have transformed my data into json. here is a sample.

{
"IMDBURL":"http:\/\/us.imdb.com\/M\/title-

 exact?Schrei%20aus%20Stein%20(1991)",
"release_date":"08-Mar-1996",
 "ratings":[{"user_id":916,"rating":3,"timestamp":"880845755"}],
 "genre":["Drama"],"video":"",
 "_id":1682,
 "title":"Scream of Stone (Schrei aus Stein) (1991)"
}

this is the first type of documents that I insert.Also in the same collection I insert some documents like this below. As i have read it is possible in mongo to insert different types of documents in the same collection

{
"zipcode":"77841",
"occupation":"student",
"gender":"M",
"_id":943,
"age":22
}

the user_id in the first document references to the _id in the second document.Because i insert many documents from each type I thought it would better to insert user informations on different documents and refer to them using their id. After reading the mongo documentation I am a little confused. Is this the best way to model the data or should I insert the second type of documents (user details ) on different collection. Also if I want to retrieve information of users or a user that makes a rating to a certain minutes how I am going to do it?

Why not put it all in the same document? You could also put it in a different collection. One way or another since you don't have any schemas to update, just try whatever suits your case. If this is a many to many relationship (like films-actors) then embedding everything wouldn't really work. You would have to reference other documents.

Film Collection:
    {
      {
          film1: ....
          actors: [actor_ids]
      },
      etc...
    }

Actor Collection:
{
     {
          actor1:....
          actor_id: objectId
      },
     etc...
}

https://docs.mongodb.com/manual/core/data-model-design/


Tracking changes to a table via the update/insert cmds executed against it daily We have an oracle database back end for our SAP ECC system.  In it we have a table with over a billion records.  We would like extract this table to our SAP BW, which happens to be on MSSQL Server.  The table we are trying to extract has no change pointer or column for which to track the changes made against it.  Given its volume we are unable to kill and fill it every day.  We have looked at Oracle GoldenGate but do not want to spend that kind of money.  Has anyone else done this kind of work before?

This sounds like a situation where Oracle Change Data Capture would be appropriate.  You would need to write some code that would subscribe to changes to this table and process them by pushing the changes to your SAP BW system.

You can also use Oracle Streams to do heterogeneous replication from Oracle to a non-Oracle database using an Oracle Transparent Gateway.


SentryOne timestamp out of sync We are using SentryOne to monitor the servers and noticed something very peculiar. When looking at the same server data but with SentryOne software running in different timezone, the data seems to be shifted and out of sync. I was wondering if this is due to some setting somewhere but couldn't find anything. Anyone know how to fix this.. or if this is a bug?

The SentryOne database stores data in UTC. When a user views data through the client, display the data from the S1 database, adjusted for the user's time zone. We don't really expect users to be able to do time zone conversions in their heads as they are reading charts, so if something happened at midnight their time, we show them that it happened at midnight their time instead of whatever UTC happened to be at that point.

So, I would expect users in different time zones to see different timestamps associated with events. For each of them that was n hours ago, but because the clock on their wall says different things now, they also said different things n hours ago when those events happened.

If you want users to see the same time for events regardless of time zone, you'll have to do the same kind of thing you have to do for Outlook meetings across an international team - run in UTC (or a specific time zone) instead of local time zone, or create a VM that does that. I don't know of a way to tell Outlook to show you calendar data in a time zone other than your own.

For future reference, we have an active and responsive support forum of our own; it is probably a quicker and more on-topic path to the answers you want about our software:

SentryOne Support


Alternative to CLUSTER without table lock I'm facing performance degradation and increasing storage usage as a result of frequent new and updated records causing index and storage fragmentation.

VACUUM doesn't help much.

Unfortunetely CLUSTER isn't an option as it causes downtime and pg_repack isn't available for AWS RDS.

I'm looking for hacky alternatives to CLUSTER. One that seems to work fine in my local tests is:

begin;

create temp table tmp_target as select * from target;

delete from target;
insert into target select * from tmp_target order by field1 asc, field2 desc;

drop table tmp_target;

commit;

The ordering of ctid looks correct with:

select ctid, field1, field2 from target order by ctid;

The question is: does this look fine? Is it going to lock the target table for SELECT queries causing downtime in apps?
Is there a way to list the locks involved in the transaction?

Related to Cluster command locking read only queries on replica

If it's just about table bloat, VACUUM FULL is the tool to do it. (VACUUM only shrinks the physical file if it can opportunistically truncate it at the end.) However, VACUUM FULL also takes an exclusive lock on the table, just like CLUSTER.

To do the same without exclusive lock pg_repack is the tool you are looking for. It's unfortunate that Amazon does not seem to allow it.

VACUUM returning disk space to operating system

Manual solutions introduce all sorts of corner cases and race conditions. You would need to specify your table and possible access patterns exactly before we can discuss the best solution.

Your current solution is no good either way. You do the sort operation after deleting all rows (locking them), which will not shorten the downtime (the duration of the lock) for SELECT queries.

If you have no concurrent write access and no depending objects, this might be better:

BEGIN;

CREATE TEMP TABLE tmp_target AS TABLE target ORDER BY field1, field2 DESC;

TRUNCATE target;
INSERT INTO target TABLE tmp_target;

-- DROP TABLE tmp_target;  -- optional; dropped at end of session automatically

COMMIT;

Sort in the background before taking the lock.
Strictly speaking, INSERT without ORDER BY is free to write rows in any physical order. Practically, though, it will copy the current physical order of the temp table with a plain SELECT * FROM (or TABLE for short).
Use TRUNCATE instead of DELETE, which is faster for big tables. Be aware of some implications like TRUNCATE does not work with FK constraints. Read the manual for full coverage.

You may want to drop existing indexes right before TRUNCATE and recreate after INSERT to make this faster.

Related:

Best way to populate a new column in a large table?
Why wouldn't VACUUM ANALYZE clear all dead tuples?
Configuring PostgreSQL for read performance


In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table On Windows, I upgraded from 5.1 to 5.5 no problem.

Copied my 5.1 data folder into my 5.5 instance
Started mysqld skipping grants
Ran mysql_upgrade

All good, but going from 5.5 to 5.6:

Copied 5.5 data folder to 5.6 instance
Started mysqld skipping grants
Ran mysql_upgrade

but I get:

C:\Users\QAdmin>mysql_upgrade
Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe
Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe
Running 'mysqlcheck' with connection arguments: "--port=3306"
Running 'mysqlcheck' with connection arguments: "--port=3306"
mysql.user_info                                    OK
Running 'mysql_fix_privilege_tables'...
Running 'mysqlcheck' with connection arguments: "--port=3306"
C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe: Got error: 1130: Host 'localhost' is not allowed to connect to this MySQL server when trying to connect
FATAL ERROR: Upgrade failed

If I look at the mysql.user table it is completely empty.

Has anyone seen this or know what is going on?
During the "upgrade"  the user table gets erased and when it tries to connect it can't?

Thanks.

This is not a new problem

Feb 07, 2007 : Upgrading from MySQL 3.23 to 5
Oct 26, 2010 : Upgrading from MySQL 4.1 to 5

On May 11, 2007. someone had "fun" with this upgrading MySQL 4.1 to 5.0.41. Notice his comment (Look for Posted by Phil Collett on May 11 2007 7:11pm in the Post):

  I had some fun upgrading from mysql-4.1.15-win32 (mysql 4.1) to
  mysql-5.0.41-win32. I figured I would write down what I did to save
  people time. The permissions table thing is still tricky. I used the
  no-installer to get the version 5 mysql. Then I just swapped the
  installs as I had done in the past. when I relaunched mysql
  (mysql5/bin/mysqld-nt --console) I kept getting Error 1130 [HYO 000x]
  cannot logon locally errors. So this was the mysql permission tables
  upgrade problem. After attacking the upgrade from a few differnt
  angles I found the way that worked for me. This is detailed below.

  The upgrade took me 15 minutes after I had my plan.

  download mysql 5 no install.
  unzip it.
  export you mysql.db and mysql.user tables using whatever tool you use (make import of old users permissions and table rights) so you get
  something like (I used EMS extract)...

  use mysql; INSERT INTO db (Host, Db, User, Select_priv,
  Insert_priv, Update_priv, Delete_priv, Create_priv,
  Drop_priv, Grant_priv, References_priv, Index_priv,
  Alter_priv, Create_tmp_table_priv, Lock_tables_priv) VALUES
  ('%','Database','Table','Y','Y','Y','Y','N','N','N','N','N','N','N','N'),
  next rec.. next rec ....); COMMIT;
  #

  Data for the db table (LIMIT 0,500)

  INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv,

  Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
  File_priv, Grant_priv, References_priv, Index_priv,
  Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv,
  Lock_tables_priv, Execute_priv, Repl_slave_priv,
  Repl_client_priv, ssl_type, ssl_cipher, x509_issuer,
  x509_subject, max_questions, max_updates, max_connections)
  VALUES
  ('%','parts','old-encrypted-password','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0),
  ...next rec.....; commit;

  save as a text file somewhere.
  4. open a command window and end the mysql service, if you run as service.
  5. go to mysql install folder/bin and unregister whatever mysql daemon you are using c:/mysql/bin/mysqld-nt --remove

  zip up all your old mysql (basedir and DataDir) for  backup and later use.
  find all your old my.cnf and my.ini files that were left over from previous installs and put them in you backup folder with the other
  ziped folders so the new install can't see them.
  place mysql 5's unzipped stuff where ever you want it to run from (mine is c:/mysql5.
  just let iot use the mysql and test databases at first to get mysql5 running.
  Set up your my.ini file for mysql 5 and just leave it in the mysql directory (none in %sysroot% any more I guess.
  start mysql5 in console c:/mysql5/bin/mysqld-nt --console.
  Try to log into the console with a blank root password (since this is a fresh install).
  assuming you got this far, type "SHOW DATABASES;" in mysql. Should only be Mysql and Test.
  Copy you old databases except of course the 4.1 mysql and 4.1 test dbs (if it is still there) to you c:/mysql5/data/ directory.
  check in mysql console if they can be seen by running "Show Databases;" again.
  Hopefully that worked, no import your old db and user permission from the sql file you made earlier.  in mysql console: SOURCE
  "c:/your-file-path.txt"; that should bring in your old user and
  password in the old format.
  check if you import worked by selecting from users (use mysql; select * from user;)
  I was fine at this point besides the old password format, so I ran the Mysql_upgrade.exe file that is mentioned in here a million times,
  but omly worked for me this way.
  I ran mysql_upgrade from a batch file: C:\mysql5\bin\mysql_upgrade.exe --datadir=c:/mysql5/data/
  --basedir=c:/mysql5/ -u root > @pause
  a whole bunch of output about bad table struct, 4.1 style passwords and duplicate columns. when it finsihed I ran it again, and
  only got the dup column warnings which can be ignored. Then I ran it
  again just for fun.
  at this point all the db's are in the right structure, my logins all worked.
  closed my console session running mysql, closed my console window mysql was running in (--mysqld-nt --console) and moved my data
  directory back to which it came (personal choice).
  Checked my cofig file (in mysql base dir ...c:/mysql5/my.ini) using "c/mysql5/mysqld-nt --print-defaults" and they looked fine,
  changed my datadir="x:/path", and re-ran the --print-defaults.
  I then reinstalled the msyql as a service with the my.ini file still only in my basedir (c:/mysql5/bin/mysqld-nt --install), and
  started mysql (net start mysql) or services gui.
  Last thing, make the root user a password, easily crackable of course like a bad word (don't really do this, but do make a password).

  Hope that saves somebody else some time.
  -Phil Citrus Motors

Right now, you say that mysql.user is empty. Ugh !!! This means you will have to run mysql with skip-grant-tables in my.ini for now.

Here is the next things you can do: Find out how many columns are in mysql.user

Run this query

SELECT COUNT(1) FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

If you get 43, the mysql.user table is MySQL 5.6 ready

If you get 42, the mysql.user table is MySQL 5.5 ready

I wrote about this before : Cannot GRANT privileges as root

Try This #1

Simply copy back the mysql.user table from the MySQL 5.5. dataset. That would mean copying

user.frm
user.MYD
user.MYI

from the mysql subfolder of MySQL 5.5 datadir into the mysql subfolder of MySQL 5.5 datadir.

Then, rerun the upgrade and go from there.

Try This #2

If you do not want to touch the mysql_upgrade, then add the user

REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = 'localhost',
              Password = PASSWORD('whateverpasswordiwant'),
             Select_priv = 'Y',
             Insert_priv = 'Y',
             Update_priv = 'Y',
             Delete_priv = 'Y',
             Create_priv = 'Y',
               Drop_priv = 'Y',
             Reload_priv = 'Y',
           Shutdown_priv = 'Y',
            Process_priv = 'Y',
               File_priv = 'Y',
              Grant_priv = 'Y',
         References_priv = 'Y',
              Index_priv = 'Y',
              Alter_priv = 'Y',
            Show_db_priv = 'Y',
              Super_priv = 'Y',
   Create_tmp_table_priv = 'Y',
        Lock_tables_priv = 'Y',
            Execute_priv = 'Y',
         Repl_slave_priv = 'Y',
        Repl_client_priv = 'Y',
        Create_view_priv = 'Y',
          Show_view_priv = 'Y',
     Create_routine_priv = 'Y',
      Alter_routine_priv = 'Y',
        Create_user_priv = 'Y',
              Event_priv = 'Y',
            Trigger_priv = 'Y',
  Create_tablespace_priv = 'Y',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;

Logout of mysql, remove skip-grant-tables from my.ini. Restart

CAVEAT

Personally, I hate mysql_upgrade. I actually dump the grants as SQL

Here is how to do it for Windows

set MYSQL_CONN=-uroot -prootpassword
mysql %MYSQL_CONN% -ANe"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" > GetGrants.sql
mysql %MYSQL_CONN% -AN < GetGrants.sql > MySQLUserGrants.sql

then in a newer installation. I execute the MySQLUserGrants.sql and all necessary columns are populated using SQL GRANTs only.

I actually wrote about this technique before

Jul 26, 2011 : Restoring an old backup to latest MySQL release
Sep 23, 2011 : Anyone have issues with a MySQL 5.1 to 5.5 in-place Upgrade in Production?
Dec 24, 2012 : Backup and restore "mysql" database
Mar 24, 2013 : MySQL export user with semi-colon ";" on the end


MyISAM Performance: Join Decomposition in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones:

Converting

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

To

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

And sort of doing the actual join yourself in your application.

My Question is whether this is stil such a good idea when the final query has a where-clause with a few thousand IDs it needs to match (the actual table itself has about 500k entries).

What I mean is, will there be a big penalty for having a query like

SELECT * FROM post WHERE post.id in (123,456,567, ... <a few thousand IDs here> ... ,9098,8904);

instead of the join-statement above? Would it help to move this logic to Stored Procedures inside the Database (while considering how poorly stored procedures are implemented in MySQL)?

breaking up complex queries into simple ones

Poppycock.  Why do extra effort when MySQL is quite willing to do it for you?  As for performance -- there is probably no difference except that the broken up queries require more round trips to the server.

OTOH, there are cases where you can outsmart the optimizer.  But your example was not one of those.

IN (thousands-of-ids) is possible, but painful, for the server.  It will sort and de-dup them, then leave them in some kind of structure for repeated binary searching.  I have seen lots of such queries, but only those over, say, 50K items raised any eyebrows.

There are times when this rewrite helps:

SELECT ... ORDER BY ... LIMIT ...

-->

SELECT b...
FROM tbl b
   JOIN ( SELECT id FROM TBL WHERE ... ORDER BY ... LIMIT ... ) a
   ON a.id = b.id

But that is to avoid hauling around extra junk that will be thrown away by the LIMIT.


Is checking for optional characters required in mysql REGEXP? I have a few rows, in a users table with a name column:

SELECT name FROM users;
+--------------------+
| name               |
+--------------------+
| Administrator Core |
| Marketing Guy      |
+--------------------+

Now I want to check if the name column contains specified strings. For example, I want to check if 'Mark' or 'ket' is present in any row, and if so return it.

Both of these seem to work:

SELECT * FROM users WHERE LOWER(name) REGEXP 'ket|Mark'\G;

SELECT * FROM users WHERE LOWER(name) REGEXP '(.+)?ket(.+)?|(.+)?Mark(.+)?'\G;

Does the first one implicitly check for optional characters around ket and Mark? That's not how it would work with many programming languages. That is why I ask.

From the quite clear examples here, I performed a few tests (below).
The tests give 1 for true (i.e. a match) and 0 for false (no match).

Note the use of ^ and $ for the start and end of the REGEXP.

To answer your specific question, your first REGEXP doesn't care about any characters around 'ket' or 'Mark' - it just checks to see if either exists anywhere in the string and returns 1 if there is any match. '^Mark' will search for 'Mark' and 'Market' and any other string beginning with 'Mark'. The same thing happens for the $ for the end of strings.

Check out the examples and try and follow them - Regular expressions are a great string to your SQL bow and it is very worthwhile learning them.

mysql> SELECT 'Mark' REGEXP '^Market|.*katerina.*';
+--------------------------------------+
| 'Mark' REGEXP '^Market|.*katerina.*' |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT 'Market' REGEXP '^Market|.*katerina.*';
+----------------------------------------+
| 'Market' REGEXP '^Market|.*katerina.*' |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT 'Market' REGEXP 'ket|Mark';
+----------------------------+
| 'Market' REGEXP 'ket|Mark' |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Market' REGEXP 'ket$|Bark';
+-----------------------------+
| 'Market' REGEXP 'ket$|Bark' |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Market' REGEXP 'ket|Bark';
+----------------------------+
| 'Market' REGEXP 'ket|Bark' |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Market' REGEXP '^ket|Bark';
+-----------------------------+
| 'Market' REGEXP '^ket|Bark' |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Market' REGEXP '^ket$|Bark';
+------------------------------+
| 'Market' REGEXP '^ket$|Bark' |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

mysql>


How should I design a specific needs mysql table I am planning out a mysql table to record page views. None of my options seem very efficient. I plan to have the pages send a +1 update to the table when loaded. What I want to be able to do is query the table for view count for the last 30 days, or a different query for view count last seven days. The best plan I can come up with is to insert a new record for each view with a date/time stamp. This would require that each of the two possible queries (30 days or 7 days) to scan the whole table and use a conditional clause. I would also need to remove rows older than 30 days old with a cron job or something.
It seems that there should be a much more efficient way to do this.
I use freelancers to do my work and have learned from past experience that I need to be specific in how I want things done.
If you can suggest a better way to accomplish what I need in a more efficient way than I described please do so.

I suggest you create a table with the following fields:

Page URL
Date (Not datetime)
Count

And add a unique key on (page_url, date).

Then, use INSERT INTO .. ON DUPLICATE KEY UPDATE.

So,

CREATE TABLE `page_view` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `page_url` varchar(512) NOT NULL,
  `date` date NOT NULL,
  `count` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `page_url` (`page_url`,`date`)
)

Then, use this insert:

INSERT INTO page_view (page_url, date)
VALUES ('www.example.com/page1.html', date(now()))
ON DUPLICATE KEY UPDATE count=count+1;


MySQL Replication Error, can't stop slave When I run command SHOW SLAVE STATUS\G, I see

Slave_IO_Running : 'Yes'
Slave_SQL_Running : 'No'

What I've usually done to fix this is to run

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

It did not work this time.

When I ran STOP SLAVE;, I get SQL Error (1192): Can't execute the given command because you have active locked tables or an active transaction."

I've never seen this before. What can I do to get past it?

Which version of MySQL you have installed in your slave server?

This behaviour seems to be a bug fixed starting from MySQL 5.5.0

see MySQL 5.5.0 Changelog

in:

  Important Change: Replication: MyISAM transactions replicated to a
  transactional slave left the slave in an unstable condition. This was
  due to the fact that, when replicating from a nontransactional storage
  engine to a transactional engine with autocommit disabled, no BEGIN
  and COMMIT statements were written to the binary log; thus, on the
  slave, a never-ending transaction was started.

  The fix for this issue includes enforcing autocommit mode on the slave
  by replicating all autocommit=1 statements from the master. (Bug
  29288)

Bug details here: http://bugs.mysql.com/bug.php?id=29288

If it is, "simply" before upgrade your slave server, then you can plan an upgrade of the master.


Running Oracle Export Utility within a Shell script I have wasted a lot of time on this.  Any ideas of what I am doing wrong here?

Script in bash

#!/usr/bin/bash
.
.
.

/export/home/app/oracle/product/10.2.0/bin/exp $uname/$passw \
    FILE="$WORKSPACE"/"$EXP_FILE" \
    LOG="$EXP_LOG" \
    TABLES=SYSTEM_PARAMETERS\
    CONSISTENT=Y \
    ROWS=Y \
    INDEXES=Y \
    CONSTRAINTS=Y \
    GRANTS=Y ;

Error Message

/export/home/app/oracle/admin/ukediuat/exp >./exp_syspar.sh
Username: USER
Enter DPDADMIN Password:

LRM-00116: syntax error at 'TABLES' following '='

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

Problem

The issue is with the dollar signs in the export command.  By replacing the variables which use the dollar sign with the full path the script runs.

Is there anyway to parse the shell variables into the export command?

Try changing the tables parameter to this.
You only need parentheses when you have more than one table.

TABLES='SYSTEM_PARAMETERS' \

$ exp tables=system.plan_table file=plan_table.dmp statistics=none

Export: Release 11.2.0.2.0 - Production on Tue Feb 17 08:59:05 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                     PLAN_TABLE          4 rows exported
Export terminated successfully without warnings.


MySQL: with CREATE_USER_PRIV, GETTING "Access denied" when attempting to change another user's password In MySQL 5.6.27, user 'bmcrae' has create user privileges and is able to create a new user.  However, when trying to change another user's password:

 SQL: SET PASSWORD FOR 'newuser' = PASSWORD('***************');

I am getting an "access denied" error:

  Access denied for user 'bmcrae'@'%' to database 'mysql'

I have re-read the relevant MySQL documentation and not found anyone else having such issues.  Any advice?

According to MySQL documentation, in order to change a password of a user, bmcrae must have UPDATE privilege for the mysql database.

To check the privileges of the user bmcrae, run the following command:

show grants for 'bmcrae'@'hostname';

OR

Show grants for bmcrae;

And check whether, the user bmcrae has UPDATE privilege on mysql database.

If the bmcrae doesn't have that privileges, grant update privilege to bmcrae user.

Login as a root user and run the following statement to grant update statement to bmcrae user.

grant update on mysql.* to 'bmcrae'@'hostname';

Warning: If you grant update privileges on mysql database, the bmcrae user can update all tables of mysql database.


SQL Server AG in primary site with separate WSFC AG in secondary site I would like some feedback from those experienced with SQL Server Availability Groups (AGs) in multiple sites.

We have a scenario where there are five SQL Server 2012 Enterprise Edition servers in a one-site AG under the same WSFC cluster.

We are looking to setup a secondary DR site and would like some guidance on whether the WSFC cluster should 'span' both sites or whether we should use Log Shipping to the remote DR site, targeting a separate AG in the DR site.

We are going to have a 1 Gbps pipe between the two sites with a very low < 15ms response time, but eventually we want to move to an alternate secondary site which will be ~70ms away and so I am uncertain whether we can make use of WSFC for such a latent connection.

This first DR site setup needs to be a 'template' for how we will be setting up the final DR site which is geographically disparate with the higher latency response time.  Therefore, please keep this in context in your response(s).

Some questions:

Can a separate AG be a 'recipient' (restore target) of Log Shipping?  In other words, can I have the primary server in a separate Availability Group be applying log backups (from log shipping) and when ready to cut over to this secondary AG, issue a RESTORE BACKUP WITH RECOVERY?
Ideally, it would be preferable to be able to more easily 'fail back' to the primary site.  If we had a WSFC spanning both sites and the same AG on said cluster, I gather this would be far easier to accomplish.  Network latency will be a concern.

Thanks for your advice.

1.Can a separate AG be a 'recipient' (restore target) of Log Shipping? In other words, can I have the primary server in a separate Availability Group be applying log backups (from log shipping) and when ready to cut over to this secondary AG, issue a RESTORE BACKUP WITH RECOVERY?

No, databases involved in an availability cannot be restored or recovered unless first removed from the availability group. You can log ship to a database NOT in an availability group, however, just fine.

  2.Ideally, it would be preferable to be able to more easily 'fail back' to the primary site. If we had a WSFC spanning both sites and the same AG on said cluster, I gather this would be far easier to accomplish. Network latency will be a concern.

All databases that belong to an AG must exist in side the SAME WSFC thus it would have to be "spanned". If network latency is a concern, work with your networking team to have dedicated bandwidth and QoS rules to make sure your traffic and the cluster traffic is getting through on time. Watch how often large bursts of log are generated, like rebuilding a very large index, and try to mitigate this as much as possible.


Tips on improving/refactoring slow query? Let's play lotto!

Ok. I have a numbers table with 80 rows (numbers 1-80):

create table dbo.numbers (knum tinyint primary key identity);

I have a table with the drawid, and each number drawn.

create table dbo.draws (drawid int, drawnumber tinyint);

Now, given that the draws table may have data like:

drawid   drawnumber
  1         10
  1         36
  1         54
  1         75
  2          9
  2         45
  2         46
  2         72

I want to find out when was the last time every possible three-number permutation occurred.

I'm using this:

declare @curdraw int
select @curdraw = max( drawid)-100 from draws;

select TOP 10 K1,K2,K3, @curdraw-max(d1.drawid)  from THREES
inner join DrawNumbers d1 WITH (NOLOCK) ON K1 = D1.DRAWNUMBER
INNER JOIN DRAWNUMBERS D2 WITH (NOLOCK) ON K2 = D2.DRAWNUMBER AND D1.DRAWID = D2.DRAWID
INNER JOIN DRAWNUMBERS D3 WITH (NOLOCK) ON K3 = D3.DRAWNUMBER AND D1.DRAWID = D3.DRAWID
WHERE D1.DRAWID < @CURDRAW

GROUP  BY K1, K2, K3
ORDER BY @curdraw-max(d1.drawid) DESC

(Oh, and table Threes is a table with all 492,000+ three-number combinations from 1-80)

Is there a better way to do what I'm trying to do here? This particular query is just very slow and I'm sure someone with better math/grouping skills could do better.

Answers to comments - clarifications:

It's for a keno game. Every draw has 20 numbers out of the 80. I just showed 4 each as an example.
Two years of accumulated draws, at 254 draws a day with 20 numbers per draw... Drawnumber table is currently 3.7 mil rows. Also, what I'm looking for specifically is "out of every 3-number combination possible can you give me a sorted list of how long it has been since any one of them has hit, ordered by that length".
The top 10 is merely because I don't need to see all 490K results.

The primary keys:

USE [OKD]
GO

ALTER TABLE [dbo].[DrawNumbers]
  ADD CONSTRAINT [PK_DrawNumbers]
  PRIMARY KEY CLUSTERED
    ( [DrawID] ASC, [DrawNumber] ASC )
  WITH
   (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
    ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
GO

USE [OKD]
GO

ALTER TABLE [dbo].[threes]
  ADD PRIMARY KEY CLUSTERED
    ( [THREEID] ASC )
  WITH
    (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
     SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
     ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
GO

Changing it to use a CTE will be faster probably - they normally give the optimizer a better chance at optimising the query.

with currentdraw as (
select k1,k2,k3 from THREES
inner join DrawNumbers d1 WITH (NOLOCK) ON K1 = D1.DRAWNUMBER
INNER JOIN DRAWNUMBERS D2 WITH (NOLOCK) ON K2 = D2.DRAWNUMBER AND D1.DRAWID = D2.DRAWID
INNER JOIN DRAWNUMBERS D3 WITH (NOLOCK) ON K3 = D3.DRAWNUMBER AND D1.DRAWID = D3.DRAWID
WHERE D1.DRAWID = @CURDRAW
), previousdraws as (
select d1.drawid, k1,k2,k3 from THREES
inner join DrawNumbers d1 WITH (NOLOCK) ON K1 = D1.DRAWNUMBER
INNER JOIN DRAWNUMBERS D2 WITH (NOLOCK) ON K2 = D2.DRAWNUMBER AND D1.DRAWID = D2.DRAWID
INNER JOIN DRAWNUMBERS D3 WITH (NOLOCK) ON K3 = D3.DRAWNUMBER AND D1.DRAWID = D3.DRAWID
WHERE D1.DRAWID < @CURDRAW
)
select previousdraws.*
from previousdraws p
inner join currentdraw c
on p.k1 = c.k1 and p.k2 = c.k2 and p.k3 = c.k3
order by drawid desc

Your best indexes will probably be:

create index ix1 on THREES (k1,k2,k3);
create index ix1 on DrawNumbers (drawid, drawnumber);


msdb..sp_send_dbmail does not work with @query only with @body I'm trying to execute the following:

msdb.dbo.sp_send_dbmail
--@body='Hi from sql server in a sproc'
@query='select 1'
,@recipients='mkohn@***.com'
,@subject = 'Test'

When I uncomment @body line, and comment the query... line it works.
With the query it returns:

    Msg 22050, Level 16, State 1, Line 0
    Failed to initialize sqlcmd library with error number -2147467259.

I have a developer edition on my own workstation and it all works no problem

I tried:

Installing .Net 3.5
Adding myself to db_owner and [DatabaseMailUserRole]
Adding [NT Service\MSSQLSERVER] to db_owner and [DatabaseMailUserRole]
Same as above with [NT SERVICE\SQLSERVERAGENT]

Have you created a mail profile and assigned an account to the profile?  See the how to by Pinal Dave @ SQL Authority

First create a mail profile, assign the profile a valid account, and enable  Database Mail XP's

Once those steps are done and tested, test your script

    msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileName'
    --@body='Hi from sql server in a sproc'
    @query='select 1'
    ,@recipients='mkohn@***.com'
   ,@subject = 'Test'


Cross Apply without a function I would like to run a query very similar to this on one of my production databases:

-- Setup the table (Already there in my real scenario)
create table dbo.MyTable(MyValue int, MyGroup varchar(5))
insert into dbo.MyTable values
(56,'I'), (12,'I'), (56, 'II'), (12, 'II'), (56, 'III'), (56, 'IV'), (56, 'V'), (12, 'V')

-- Create a function to cross apply with (I can't make this in my production env)
create function dbo.GetOrderGroup(@groupName varchar(15))
returns @ReturnTable table(GroupValue varchar(8000), GroupName varchar(15))
as
begin
    DECLARE @groupValue VARCHAR(8000)

    SELECT @groupValue = COALESCE(@groupValue + ', '
           + cast(MyValue as VARCHAR(10)), cast(MyValue as VARCHAR(10)))
    FROM MyTable
    where MyGroup = @groupName

    insert into @ReturnTable(GroupValue, GroupName)
    SELECT @groupValue, @groupName
    return;
end;
go

-- cross apply and get the distinct values
SELECT  grp.GroupValue, count(distinct (grp.GroupValue + grp.GroupName)) as 'Count'
from    MyTable tbl (NOLOCK)
        cross apply dbo.GetOrderGroup(tbl.MyGroup) grp
GROUP BY grp.GroupValue

But I don't have rights to create a User Defined Function on that database (I am a developer).

Is there a way to accomplish this query with out the use of a user defined function (or any other "Create" statement)?

Note: I tried doing a cross apply with the contents of my Function, but it failed (I don't think multi-statement expressions are allowed in the Cross Apply).

SELECT
  GroupValue = Val,
  [Count] = COUNT(DISTINCT MyGroup)
FROM
(
  SELECT MyGroup, Val = STUFF((SELECT ', ' + RTRIM(MyValue)
   FROM dbo.MyTable
   WHERE MyGroup = t.MyGroup
  FOR XML PATH(''), TYPE).value('.[1]','nvarchar(max)', 1, 2, '')
  FROM dbo.MyTable AS t
) AS x
GROUP BY Val;


Databases can't "see" other databases This is a bit of a newbie question I think, but all the answers I've found online are for other questions, so I ask here:

We have a server that we set up on a virtual machine to handle the passing of data from a VPN connection to the main database that end users will access.  This "Processing" server has a few databases for handling deltas and hosting sprocs.  I have recently tried to create a view in one of those databases and found that it cannot query data in any other database, which is not the normal experience that I have had.  On the other server all the databases can see eachother.  I've looked at all of the databases and they only have visibility limited to themselves.

I suppose there is a setting at the server level that isolates the databases, but I don't have the expertise to know what that is.  Can somebody help me?  I'm at an impasse until this is solved.

Thanks,

n8

--edit--
After extended testing I have found that the databases can write to each other and to the other server using 4-part naming, I just cannot execute queries or views that span the same databases that the sprocs are reading from and writing to, even using 4-part naming.  This is quite odd.

Please enable this setting:

http://msdn.microsoft.com/en-gb/library/ms188694.aspx
http://msdn.microsoft.com/en-us/library/bb669059(v=vs.110).aspx

This allows the security permission between databases to propagate. Additional details described well in this blog: http://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/


Best scenario for replication type We are using SQL Server 2012 and we have the Main office server. All modification on the product pricing table is done through the Main office.

We have 10 remote sales branches. Those remotes branches need to be updated by the products prices from the main office (read-only data, not changed by the branches). Each branch has an unique code to be identified.

All the sales process performed by the remote branches only. We need all the sales invoice tables (header & details) in the branches to be sent to the main office for consolidation and other activities.

In summary we need to send items table to the 10 remote branches and receive from the remote branches two tables (salesHeader and salesDetails tables).

Which replication type is good for our situation?

Sounds easy. You need transactional replication for read only pricing data and merge for salesHeader and salesDetails.


Why is array_agg() slower than the non-aggregate ARRAY() constructor? I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-array_agg() looked like. For review, modern aggregation is written like this.

SELECT array_agg(x ORDER BY x DESC) FROM foobar;

However, once upon a time, it was written like this,

SELECT ARRAY(SELECT x FROM foobar ORDER BY x DESC);

So, I tried it with some test data..

CREATE TEMP TABLE foobar AS
SELECT * FROM generate_series(1,1e7)
  AS t(x);

The results were surprising.. The #OldSchoolCool way was massively faster: a 25% speedup. Moreover, simplifying it without the ORDER, showed the same slowness.

# EXPLAIN ANALYZE SELECT ARRAY(SELECT x FROM foobar);
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Result  (cost=104425.28..104425.29 rows=1 width=0) (actual time=1665.948..1665.949 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on foobar  (cost=0.00..104425.28 rows=6017728 width=32) (actual time=0.032..716.793 rows=10000000 loops=1)
 Planning time: 0.068 ms
 Execution time: 1671.482 ms
(5 rows)

test=# EXPLAIN ANALYZE SELECT array_agg(x) FROM foobar;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=119469.60..119469.61 rows=1 width=32) (actual time=2155.154..2155.154 rows=1 loops=1)
   ->  Seq Scan on foobar  (cost=0.00..104425.28 rows=6017728 width=32) (actual time=0.031..717.831 rows=10000000 loops=1)
 Planning time: 0.054 ms
 Execution time: 2174.753 ms
(4 rows)

So, what's going on here. Why is array_agg, an internal function so much slower than the planner's SQL voodoo?

Using "PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.2.0-5ubuntu12) 6.2.0 20161005, 64-bit"

There is nothing "old school" or "outdated" about an ARRAY constructor (That's what ARRAY(SELECT x FROM foobar) is). It's modern as ever. Use it for simple array aggregation.

The manual:

  It is also possible to construct an array from the results of a
  subquery. In this form, the array constructor is written with the key
  word ARRAY followed by a parenthesized (not bracketed) subquery.

The aggregate function array_agg() is more versatile in that it can be integrated in a SELECT list with more columns, possibly more aggregations in the same SELECT, and arbitrary groups can be formed with GROUP BY. While an ARRAY constructor can only return a single array from a SELECT returning a single column.

I did not study the source code, but it would seem obvious that a much more versatile tool is also more expensive.

One notable difference: the ARRAY constructor returns an empty array ({}) if no rows qualify. array_agg() returns NULL for the same.


how to enalbe MySQL 8 innodb_dedicated_server option Initially, I was looking for tuning the MySql 8 instance then I found that there is something like autoconfiguration feature added with the 8th version. Then I visited the official MySQL website for documentation and found this but was unable to wrap my head around this.

As given in the official documentation when this option is enabled MySql autoconfigure certain cache and butter options so that I don't have to change the config when Scale the Server Instance.

When I tried to add innodb_dedicated_server = ON to my.cnf and mysqld.cnf, it
resulted in being unable to start the server.

got nothing in /var/log/mysql/error.log
and found following via journalctl -xe

Jan 08 17:41:50 fa-b2b-mysql mysql-systemd-start[31493]: Once the problem is resolved, restart the service.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: mysql.service: Control process exited, code=exited status=1
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: mysql.service: Failed with result 'exit-code'.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit mysql.service has failed.
--
-- The result is RESULT.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
-- Subject: Automatic restarting of a unit has been scheduled
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Automatic restarting of the unit mysql.service has been scheduled, as the result for
-- the configured Restart= setting for the unit.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit mysql.service has finished shutting down.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: mysql.service: Start request repeated too quickly.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: mysql.service: Failed with result 'exit-code'.
Jan 08 17:41:50 fa-b2b-mysql systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit mysql.service has failed.
--
-- The result is RESULT.

(I wrote a bug report about that doc page.)

You do not have any other apps (Apache, Java, etc) running on the machine, correct?

Plan A:  Manually with innodb_buffer_pool_size = 1200M, I think 2G is too big for 4G of RAM.

Plan B:  Write a bug report.  In it provide your entire my.cnf and the exact version you are using (bugs.mysql.com).  Then abandon innodb_dedicated_server = ON.


MS SQL: How to get Employee Status from two seperate tables I am new to MS SQL so please bear with me.

I want to get the Company Code and Company Short Name of an employee DEPENDS on it's each employee status and stored it into one table.

If Emp_Status = FT, it will get Co_Code and Co_Shortname from Table 1. ELSE if Emp_Status = PT, it will get Co_Code and Co_Shortname from Table 2 where USERNAME is equals to each other.

I have two tables.

Table 1:
EmpID  |Co_Code  |Co_ShortName |Emp_Status
WC11    |01          |AA1        |FT
KC22    |02          |BB1        |PT
MA33    |03          |CC1        |FT

Table 2:
EmpID  |Co_Code |Co_ShortName |Emp_Status
WC11     |04          |DD1        |FT
KC22     |05          |EE1        |PT
MA33     |06          |FF1        |FT

Output will be

EmpID  |Co_Code  |Co_ShortName |Emp_Status
WC11    |01          |AA1        |FT
KC22    |05          |EE1        |PT
MA33    |03          |CC1        |FT

I am not sure if I need to use CASE or IF statement as I am getting other fields from table 1.

I know this isn't correct but this is what I have:

SELECT EmpID ,  CASE
        WHEN Table1.EmpStatus = 'FT'
        THEN (SELECT Co_Code from Table 1)
        WHEN Table1.EmpStatus = 'PT'
        THEN (SELECT Co_Code from Table 2 where Table1.EmpID = Table2.EmpID)

    END

Please help

create table #Table1
( EmpID CHAR(4),
  Co_Code CHAR(2),
  Co_ShortName CHAR(3),
  Emp_Status CHAR(2)
 )
INSERT INTO #Table1
    ([EmpID], [Co_Code], [Co_ShortName], [Emp_Status])
VALUES
    ('WC11', '01', 'AA1', 'FT'),
    ('KC22', '02', 'BB1', 'PT'),
    ('MA33', '03', 'CC1', 'FT');

CREATE TABLE #Table2
    (EmpID CHAR(4),
     Co_Code CHAR(2),
     Co_ShortName CHAR(3),
     Emp_Status CHAR(2));

INSERT INTO #Table2
    ([EmpID], [Co_Code], [Co_ShortName], [Emp_Status])
VALUES
    ('WC11', '04', 'DD1', 'FT'),
    ('KC22', '05', 'EE1', 'PT'),
    ('MA33', '06', 'FF1', 'FT');
GO

Version1:

DECLARE @c_EmpID CHAR(4) = '';
DECLARE @c_Emp_Status CHAR(2) = '';

SET @c_EmpID = 'WC11'
--SET @c_EmpID = 'KC22'

SELECT
        CASE WHEN T1.Emp_Status = 'FT' THEN T1.Co_Code
             WHEN T1.Emp_Status = 'PT' THEN T2.Co_Code
             ELSE 'N/A'
        END AS Co_Code,
        CASE WHEN T1.Emp_Status = 'FT' THEN T1.Co_Shortname
            WHEN T1.Emp_Status = 'PT' THEN T2.Co_Shortname
            ELSE 'N/A'
        END AS Co_Shortname
    FROM #Table1 AS T1
        LEFT JOIN #Table2 AS T2
            ON T1.EmpID = T2.EmpID
            --AND T2.Emp_Status = 'PT'
    --WHERE T1.EmpID = @c_EmpID

output:

Co_Code Co_Shortname
01      AA1

Version2: You can put the logic inside stored procedures:

CREATE PROCEDURE #usp_Co_FT
(
  @i_c_EmpID CHAR(4)
)
AS
BEGIN
        SELECT
           T1.Co_Code ,T1.Co_Shortname
        FROM #Table1 AS T1
        WHERE
            T1.EmpID = @i_c_EmpID
            AND T1.Emp_Status = 'FT'
END
GO
CREATE PROCEDURE #usp_Co_PT
(
  @i_c_EmpID CHAR(4)
)
AS
BEGIN
        SELECT
           T2.Co_Code , T2.Co_Shortname
        FROM #Table2 AS T2
        WHERE
            T2.EmpID = @i_c_EmpID
            AND T2.Emp_Status ='PT'
END
GO

DECLARE @c_EmpID CHAR(4) = '';
DECLARE @c_Emp_Status CHAR(2) = '';

SET @c_EmpID = 'WC11'
--SET @c_EmpID = 'KC22'

SELECT @c_Emp_Status =  T1.Emp_Status FROM #Table1 AS T1 WHERE T1.EmpID = @c_EmpID

IF @c_Emp_Status = 'FT'
BEGIN
    EXEC  #usp_Co_FT @i_c_EmpID = @c_EmpID
END
ELSE IF @c_Emp_Status = 'PT'
    BEGIN
        EXEC  #usp_Co_PT @i_c_EmpID = @c_EmpID
    END
GO

Version 3 with UNION ALL

DECLARE @c_EmpID CHAR(4) = 'WC11';
--SET @c_EmpID = 'KC22'

SELECT
   T1.Co_Code ,T1.Co_Shortname
FROM #Table1 AS T1
WHERE
    T1.EmpID = @c_EmpID
    AND T1.Emp_Status = 'FT'

UNION ALL

SELECT
   T2.Co_Code , T2.Co_Shortname
FROM #Table2 AS T2
WHERE
    T2.EmpID = @c_EmpID
    AND T2.Emp_Status ='PT'

dbfiddle here


ALTER DATABASE command to set log filegrowth and size? Is it possible to alter the LOG file through script when creating a database?
I need to set the file size, growth etc, but NOT specify where the DB is to be created.
e.g.

CREATE DATABASE [NewDatabase]
GO
ALTER DATABASE [NewDatabase]
    MODIFY FILE
    (SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1MB);
GO
**ALTER DATABASE [NewDatabase]
    MODIFY LOG
    (MAXSIZE = 200MB,
    FILEGROWTH = 1MB
    );
GO**

You already wrote it, you just need to specify logical file name of the log file.

ALTER DATABASE [NewDatabase]
MODIFY FILE
(NAME = 'NewDatabase_log',
SIZE = 200MB,
FILEGROWTH = 1MB)


Mysql slow query on large table With Mysql 5.6.10, I have a table like this:
CREATE TABLE `es_user_action` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(11) unsigned NOT NULL,
  `company_id` bigint(11) unsigned NOT NULL,
  `work_id` bigint(11) unsigned NOT NULL,
  `action` tinyint(2) NOT NULL COMMENT '10, 20, 30, 40',
  `action_id` bigint(11) unsigned DEFAULT NULL,
  `apply_id` bigint(11) unsigned DEFAULT '0',
  `apply_display_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `action_time` datetime NOT NULL,
  `scout_time` datetime NOT NULL,
  `register_datetime_sorting` datetime DEFAULT NULL,
  `score` tinyint(2) DEFAULT '0',
  `is_pending` tinyint(2) DEFAULT '0',
  `apply_status` tinyint(2) DEFAULT '2' COMMENT '1: paid, 2: free',
  `has_response` tinyint(2) DEFAULT '0',
  `response_time` datetime DEFAULT NULL,
  `is_shown` tinyint(2) DEFAULT '1',
  `source` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_company_work` (`user_id`,`work_id`,`company_id`,`apply_id`,`source`),
  KEY `IDX_2` (`company_id`,`is_shown`,`apply_status`,`apply_id`,`work_id`,`action_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=436896779 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The full query I want to perform on the table would be the following:
SELECT
    `id` AS `seqNo`,
    `company_id` AS `companyId`,
    `work_id` AS `workId`,
    `action` AS `userActionType`,
    `action_time` AS `userActionDatetime`,
    `is_pending`,
    `apply_status`,
    `action_id`,
    `source`,
    `user_id` AS `userId`,
    `apply_display_id`
FROM
    `es_user_action`
WHERE
    `company_id` = 449664
    AND `is_shown` = 1
    AND `apply_status` = 1
    AND `apply_id` = 0
    AND `action_time` >= '2021-01-05 15:56:14'
-- AND `work_id` IN ( 1250160 )
ORDER BY
    is_pending ASC,
    action ASC,
    score DESC,
CASE
        source
        WHEN "entenshoku" THEN
        action_time
    END DESC,
CASE

        WHEN source <> "entenshoku" THEN
        action_time
END ASC
    LIMIT 100 OFFSET 0;

The table has around 15 million rows and the following query takes around 15 seconds. Can anyone help out? Thanks in advance.
UPDATED:
This is the explain query result:

UPDATED 4/2/2021:
Handler counts result:

IDX_2 is the best index for that query when the test for work_id is included.  The ORDER BY is too complex for any index to help.
If you need to exclude work_id, then make an index without it.
The table is probably under 2GB; how big is innodb_buffer_pool_size?
Most of the BIGINTs are likely to be overkill.  Shrinking them from that 8-byte size would shrink the table.  But that probably would not help the 15 seconds much.


Query store data maintenance background tasks Query store has been running for years on the server without any noticeable disk IO spikes on log backups. We run on SQL Server 2016 EE version 13.0.5622.0
Recently we experienced a sudden surge in disk I/O every twenty minutes or so. We backup logs every five minutes, and every third or fourth backup would increase dramatically (from ~100 MB to >3 GB in size).
It happened at 2 AM without any changes being done to the server.
I dumped the contents of some of the log files and noticed a huge surge in swappages on the Query store system data tables when these log backup IO spikes occured. Disabling query store entirely caused the disk IO to resume at normal levels when backing up the log.
I presume the reason for the large transaction log backups would be online index rebuilds on the query store tables. We had configured query store to capture all transactions, auto cleanup, retain data for 180 days and max the data cache to 15 GB.
So far so good. I re-enabled query store after changing the configuration to auto, auto, 120 days and 8 GB, but now we are starting to see the same type of spikes in disk IO every 20 minutes or so when the transaction log is being backed up.
Have anyone had an experience like this and what did they do to fix the issue?

For the query store to be consuming a lot of IO I would say, by my experience, that there is a lot of change in the data calling methods.

Are you aware of any reporting with new filters?
Any major change is Store Procedures parameters that might cause parameter sniffing
Any other change to an Application that consumes data in the sense of querying with parameter changes?

I'm asking since these situations create new execution plans and those create data in the Query Store, causing the IO.


Retrieve information about a recent copy only backup? I have taken a copy only full backup using the query

BACKUP DATABASE abc
TO DISK = N'E:\Backup\abc.bak'
WITH COPY_ONLY,COMPRESSION

And when I try to check this information via the query,

SELECT  top 100 *
FROM msdb.dbo.backupset a with(nolock)
INNER JOIN msdb.dbo.backupmediafamily b with(nolock)
ON a.media_set_id = b.media_set_id
WHERE is_copy_only = 1
ORDER BY  a.database_name desc,
a.backup_finish_date desc

...the name of the backup I just took can't be found in the name column, instead NULL is being returned. However the path to the backup is listed in the physical_device_name column. I am unable to figure out why name of the backupset is being returned as NULL.

Try querying without the "top 100":

SELECT *
FROM msdb.dbo.backupset
WHERE name = "abc"

OR

SELECT  bs.database_name,
                bs.backup_start_date,
                bmf.physical_device_name,
                  Ordinal = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ),*
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.[type] = 'D'
                  AND bs.is_copy_only = 0

Reference: Where is database .bak file saved from SQL Server Management Studio?

As Avarkx said, add  NAME = 'abc Name' to your WITH so that it shows a name when you do a backup. Otherwise it shows NULL. Then run the select without the TOP 100.


Storing user search terms I have php application like airbnb. I want to store search values each time user search for listings.

my number 1 solution would be storing it in mysql table, but I'm not sure its wise to do, if site has lots of searchs wouldn't it will slow things down.

should I use mysql or something else?

Storing in mysql table should be fine. You need to consider how the data is going to be accessed and retrieved when you're designing the table and indexes on it. I'd say that you're likely to be doing queries which return all of the past searches for a particular user (or perhaps even just the most recent by user) so it would probably make sense to cluster the table by UserID and SearchDate. This design should also help reduce contention if lots of inserts are happening at the same time since each user will be inserting into a different part of the table.


How to select two columns from two different tables using foreign keys from another third table? I am using MYSQL and here is the schema that I have:

Here is my first table user

+-----+------------------+
| id  | name             |
+-----+------------------+
| 101 | Daniel Radcliffe |
| 102 | Elijah Wood      |
| 103 | Robert Downey Jr |
+-----+------------------+

Here is my second table movie

+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Harry Potter 1      |
|  2 | Harry Potter 2      |
|  3 | Harry Potter 3      |
|  4 | Lord of the Rings 1 |
|  5 | Lord of the Rings 2 |
|  6 | Lord of the Rings 3 |
|  7 | Iron Man 1          |
|  8 | Iron Man 2          |
+----+---------------------+

Here is my last table list

+---------+----------+
| user_id | movie_id |
+---------+----------+
|     101 |        1 |
|     101 |        2 |
|     101 |        3 |
|     102 |        4 |
|     102 |        5 |
|     102 |        6 |
|     103 |        7 |
|     103 |        8 |
+---------+----------+

Now I want to display name and movie side by side something like this:

+------------------+--------------------+
| Daniel Radcliffe | Harry Potter 1     |
| Daniel Radcliffe | Harry Potter 2     |
| Daniel Radcliffe | Harry Potter 3     |
| Elijah Wood      | Lord of the Rings 1|
| Elijah Wood      | Lord of the Rings 2|
| Elijah Wood      | Lord of the Rings 3|
| Robert Downey Jr | Iron Man 1         |
| Robert Downey Jr | Iron Man 2         |
+------------------+--------------------+

The list table has foreign keys for user_id and movie_id which reference user.id and movie.id respectively. So how can I use JOINs to display the above data.

SELECT user.name, movie.name
FROM user, movie, list
WHERE list.user_id  = user.id
  AND list.movie_id = movie.id
ORDER BY 1,2;

Or, if you need to see the word JOIN in the query text

SELECT user.name, movie.name
FROM user
JOIN list  ON list.user_id  = user.id
JOIN movie ON list.movie_id = movie.id
ORDER BY 1,2;


Column size with datatype decimal According to BOL for SQL Server 2008 R2 the data type decimal requires the following
storage bytes:

Precision  Storage bytes

1 - 9      =>   5

10-19      => 9

20-28      => 13

29-38      => 17

However, when I do a datalength() on a column that is formatted decimal(19,5) and
has the value of 10999.99999 I get back 5 bytes ? Which is according to my understanding
a precision of 10 and not of 9 and should result in 9 bytes.

this results in two questions:

is the size of the table with this column not depending on the column definition,
instead the true values within the column define the table size ?
why is the information in BOL not matching with what datalength() returns ?

Don't mix storage (5 or 9 bytes) and what you get back
In a decimal(19,5) column it is always 9 bytes on disk. So zero will take nine bytes.
Your 10999.99999 is merely a representation of that stored number that is then parsed as 10999.99999 by DATALENGTH
Edit:
DATALENGTH will return the number of bytes needed to store the expression given. It ignores datatype mostly. So 10999.99999 is treated as decimal(10,5) which can be stored in 5 bytes not 9 as Martin pointed out in a comment.
So a decimal(19,5) column is always 9 bytes. DATALENGTH doesn't care. It looks at the expression 10999.99999 and decides it can be stored in 5 bytes. But of course it can't
Personally, I've never used DATALENGTH except to find trailing spaces or such.
Basically, don't use DATALENGTH on non string datatypes.

Returns the number of bytes used to represent any expression.
DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.

Note: LEN tells you how long the string representation is
DECLARE @i decimal(19,5)
SET @i ='10999.99999'
SELECT @i,LEN(@i),DATALENGTH(@i)
SET @i ='-90999.99999'
SELECT @i,LEN(@i),DATALENGTH(@i)