MySQL temporary column in SELECT statement I'd like to do some calculations in a MySQL query.
I've created some temporary columns which I'd like to use for the calculation.

SQL fiddle for currently working (but discouraged) SQL query:
http://sqlfiddle.com/#!2/5dc99/14

According to the MySQL docs 9.4 User-Defined variables(http://dev.mysql.com/doc/refman/5.0/en/user-variables.html): "As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement."

SELECT
  /** Product details **/
  product.id AS product_number,
  product.name AS product_name,

  /** Price details **/
  @redemption_price := product.redemption_price AS redemption_price,
  @customer_price_increment := (
    CASE
      (SELECT COUNT(1) FROM customer_price_increment AS cpi WHERE cpi.product_id = product.id AND cpi.customer_id = 4)
    WHEN 0 THEN
      0
    ELSE
      @redemption_price * (SELECT cpi.increment_percentage / 100 FROM customer_price_increment AS cpi WHERE cpi.customer_id = 4 AND cpi.product_id = product.id)
    END
  ) AS customer_price_increment,
  @general_price_increment := (
    CASE
      (SELECT COUNT(1) FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
    WHEN 0 THEN
      0
    ELSE
      @redemption_price * (SELECT gpi.increment_percentage / 100 FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
    END
    ) AS general_price_increment,
  @sale_price := @redemption_price + (
    CASE
      @customer_price_increment
    WHEN 0 THEN
      @general_price_increment
    ELSE
      @customer_price_increment
    END
  ) AS sale_price
FROM
  product

I'd like to add HAVING sale_price > 250 to only show products with a product price higher than $250.

Adding HAVING sale_price > 250 doesn't work, because of the usage of the user defined variables (see http://sqlfiddle.com/#!2/5dc99/19).

I've removed the user defined variables as much as I could, but I'm not getting it to work without all the user defined variables.
http://sqlfiddle.com/#!2/5dc99/24 shows results, because he HAVING clause isin't there.
http://sqlfiddle.com/#!2/5dc99/23 doesn't show any results while it should, because there are products that costs more than $250.

Simply removing all the user defined variables doesn't solve the problem, because the temporary columns cannot be reused (http://sqlfiddle.com/#!2/5dc99/25). Unknown column 'general_price_increment' in 'field list'.

SELECT
  /** Product details **/
  product.id AS product_number,
  product.name AS product_name,

  /** Price details **/
  product.redemption_price AS redemption_price,
  product.redemption_price * (
    CASE
      (SELECT COUNT(1) FROM customer_price_increment AS cpi WHERE cpi.product_id = product.id AND cpi.customer_id = 4)
    WHEN 0 THEN
      0
    ELSE
      (SELECT cpi.increment_percentage / 100 FROM customer_price_increment AS cpi WHERE cpi.customer_id = 4 AND cpi.product_id = product.id)
    END
  ) AS customer_price_increment,
  product.redemption_price * (
    CASE
      (SELECT COUNT(1) FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
    WHEN 0 THEN
      0
    ELSE
      (SELECT gpi.increment_percentage / 100 FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
    END
    ) AS general_price_increment,
  redemption_price + (
    CASE
      customer_price_increment
    WHEN 0 THEN
      general_price_increment
    ELSE
      customer_price_increment
    END
  ) AS sale_price
FROM
  product
HAVING
  sale_price > 250

What can I do to make this work?

Based on your current code, it appears that a product can have at most one match in either customer_price_increment or general_price_increment. With that fact in mind, I would probably try a different approach.

First, I would rewrite the base query returning all the prices like this:

SELECT
  p.id AS product_number,
  p.name AS product_name,
  p.redemption_price,
  COALESCE(p.redemption_price * cpi.increment_percentage / 100, 0) AS customer_price_increment,
  COALESCE(p.redemption_price * gpi.increment_percentage / 100, 0) AS general_price_increment,
  p.redemption_price * (1 + COALESCE(cpi.increment_percentage, gpi.increment_percentage, 0) / 100) AS sale_price
FROM product AS p
LEFT JOIN customer_price_increment AS cpi ON cpi.id = p.id AND cpi.customer_id = 4
LEFT JOIN general_price_increment AS gpi ON gpi.id = p.id

To filter on sale_price, I would just use the above query as a derived table, so that I could reference the sale_price alias and avoid repeating the entire expression in the WHERE clause:

SELECT *
FROM (
    SELECT
      p.id AS product_number,
      p.name AS product_name,
      p.redemption_price,
      COALESCE(p.redemption_price * cpi.increment_percentage / 100, 0) AS customer_price_increment,
      COALESCE(p.redemption_price * gpi.increment_percentage / 100, 0) AS general_price_increment,
      p.redemption_price * (1 + COALESCE(cpi.increment_percentage, gpi.increment_percentage, 0) / 100) AS sale_price
    FROM product AS p
    LEFT JOIN customer_price_increment AS cpi ON cpi.id = p.id AND cpi.customer_id = 4
    LEFT JOIN general_price_increment AS gpi ON gpi.id = p.id
) AS s
WHERE sale_price > 250
;

I believe you could also make the first query a view and then just select from it filtering the results as necessary:

SELECT *
FROM sale_prices_view
WHERE sale_price > 250
;


Oracle: ORA-00942: "table or view does not exist" in SQL Developer in SQL Developer when i get session information he return me this error message: (ORA-00942: "table or view does not exist"). Can i resolve this with add grant to privilege of my user? Wich command use for wich table or view system?
Best Regards to all.
Fabio.

SQL Developer runs the below query (which you can check by clicking the Run Report in SQL Worksheet button) for its session browser:
with vs as (select rownum rnum,
                            inst_id,
                            sid,
                            serial#,
                            status,
                            username,
                            last_call_et,
                            command,
                            machine,
                            osuser,
                            module,
                            action,
                            resource_consumer_group,
                            client_info,
                            client_identifier,
                            type,
                            terminal,
                            sql_id,
                            sql_child_number
                       from gv$session)
           select vs.inst_id, vs.sid ,serial# serial, vs.sql_id, vs.sql_child_number,
                  vs.username "Username",
                  case when vs.status = 'ACTIVE'
                            then last_call_et
                       else null end "Seconds in Wait",
                  (select command_name from v$sqlcommand where command_type = vs.command ) "Command",
                  vs.machine "Machine",
                  vs.osuser "OS User",
                  lower(vs.status) "Status",
                  vs.module "Module",
                  vs.action "Action",
                  vs.resource_consumer_group,
                  vs.client_info,
                  vs.client_identifier
             from vs
            where vs.USERNAME is not null
              and nvl(vs.osuser,'x') <> 'SYSTEM'
              and vs.type <> 'BACKGROUND'
              order by 1,2,3

Based on that, grants needed:
grant select on sys.gv_$session to your_user;
grant select on sys.v_$sqlcommand to your_user;

You may need additional privileges for other options.


Use WHERE on result of SUM So, I have this query working great

SELECT u.username,  SUM(kort) as total
FROM user as u
LEFT JOIN userresult as r
on u.userID = r.userID
WHERE r.date >= '$date'
group by u.userID
order by total DESC

But I want to remove those which the total is 0.

I tried this without any result

WHERE r.date >= '$date' AND total > 0

But am I not allowed to use WHERE on the result of SUM?

Just change it to INNER JOIN

SELECT u.username,SUM(kort) as total
FROM user as u
INNER JOIN userresult as r
on u.userID = r.userID
WHERE r.date >= '$date'
group by u.userID
order by total DESC;

or put the WHERE clause in the HAVING aggregation

SELECT u.username,SUM(kort) as total
FROM user as u
LEFT JOIN userresult as r
on u.userID = r.userID
WHERE r.date >= '$date'
group by u.userID
HAVING SUM(kort) > 0;

or put the whole thing in a subquery and test the total outside the subquery

SELECT * FROM
(
    SELECT u.username,SUM(kort) as total
    FROM user as u
    LEFT JOIN userresult as r
    on u.userID = r.userID
    WHERE r.date >= '$date'
    group by u.userID
    order by total DESC
) A WHERE total > 0;

The simplest and most sensible is the INNER JOIN


Estimated vs. Actual Row counts in a trivial case I'm working on optimizing some queries in a database I've inherited.  I'm not permitted to disclose the query, but I can present an anonymized version of a query plan that is showing some very strange behavior (to me):

My question is about the 'Clustered Index Scan' denoted (1) in the images.  As I understand it, the 'Clustered Index Scan' highlighted in yellow doesn't have any dependencies.  The operation doesn't have any predicates (although I don't show it here) and it's sibling in the tree (2) is executed the same number of times as the number of rows (1) returns -- I take this to mean that (1) is executed first, and (2) is executed row by row for the join (which makes sense, since the join is a 'Nested Loop').

Here's the issue:  The 'Estimated Rows' for (1) is 46.  The 'Actual Rows' is 283972, which is the number of rows in that table exactly...which makes sense since there's nothing to trim it down. I know the query is very complex (4 levels of nested views), but how in the world does SQL Server get this wrong?  Why isn't the estimate for the number of rows that come back in an Index Scan with no predicate equal to the number of rows in the table?

I've updated all of the relevant table statistics with no success.  I've also noticed that reducing the nesting level of the queries has the effect of the problem occurring less often, though the results are 'unstable'.  There are a number of queries that reference this view, and if I partially reduce the nesting of this view, a few queries may be fixed, but it may break a few more.

It's important to realize that a clustered index scan doesn't always mean that SQL Server expects to read all of the rows from the table. For some queries SQL Server is able to stop early after it finds all of the rows that it needs. The low number of estimated rows that you're seeing in the plan is likely due to a row goal.

Let's walk through a simple example. Suppose that I create two heap tables without any shared values:

CREATE TABLE X_HEAP_1 (ID INT NOT NULL);

INSERT INTO X_HEAP_1 WITH (TABLOCK)
SELECT N FROM dbo.GetNums(1000000)
WHERE N % 10 <> 5;

CREATE TABLE X_HEAP_2 (ID INT NOT NULL);

INSERT INTO X_HEAP_2 WITH (TABLOCK)
SELECT N FROM dbo.GetNums(1000000)
WHERE N % 10 = 5;

I know that the following query will not return any rows:

SELECT TOP (10) H1.ID
FROM X_HEAP_1 h1
INNER JOIN X_HEAP_2 h2 ON h1.ID = h2.ID;

Because there are no indexes on the tables, SQL Server will be forced to read all of the rows from both tables. However, SQL Server does not know that the query will not return any rows. In fact, based on statistics and modeling assumptions it expects the query will return the full 10 rows. It also expects that it will not need to scan every row in both tables to get to 10 rows. Here is an estimated plan:

The query optimizer thinks that after it gets 89 rows from X_HEAP_1 that it will have a total of 10 matching rows from both tables. However, we already know that in the actual plan it will need to scan all 900000 rows from X_HEAP_1:

That's a simple example of how a row goal can lead to an estimated number of rows which is 10000X off.


Log file on SQL Server 2014 grown out of control - LOP_SHRINK_NOOP Server is SQL Server 2014 running in SQL Server 2008 compatability mode.
Recovery Model is Full. We have auto shrink turned on (seems this is related to LOP_SHRINK_NOOP)

We have about 4.5 million rows in the log database of which roughly 4 million rows look identical and continue to grow. These rows all have the same information:

Incrementing: Current LSN
Operation: LOP_SHRINK_NOOP
Context: LCX_NULL
Transaction ID: 0000:0000000
LogBlockGenerated: 0
TagBits: 0
Log Record Fixed Length: 24
Log Record Length: 12316
Previous LSN: 00000:00000000:00000
Flag Bits:0
Every other column past this is NULL

Does anyone know what causes this?

As has been mentioned in the comments auto shrink is a generally bad idea for a number of reasons (see the links they've provided) and this is most likely the cause of your transaction log issues

Judging by what you've posted you may have ran something similar to this, but its a useful bit of code which will list out all of the databases in an instance along with the recovery model and what currently is preventing the transaction log from shrinking. the reasons are usually self explanatory, like checkpoint, Log_backup

Using this you should be able to narrow down what is preventing your transaction log from shrinking and hereby having all other transactions making it grow

SELECT instance_name
        , recovery_model_desc [Mode]
        , convert(decimal(12,3),[Data File(s) Size (KB)] * 1.0 / 1024 ) [Data File(s) Size (MB)]
        , convert(decimal(12,3),[Log File(s) Size (KB)] * 1.0 / 1024 ) [Log File(s) Size (MB)]
        , convert(decimal(12,3),[Log File(s) Used Size (KB)] * 1.0 / 1024 ) [Log File(s) Used Size (MB)]
        , [Percent Log Used]
        , log_reuse_wait_desc
    FROM (
        SELECT os.counter_name
            , os.instance_name
            , os.cntr_value
            ,db.recovery_model_desc
            , db.log_reuse_wait_desc
        FROM sys.dm_os_performance_counters os
        JOIN sys.databases db
            ON os.instance_name = db.name
        WHERE os.counter_name IN
        (
            'Data File(s) Size (KB)'
            , 'Log File(s) Size (KB)'
            , 'Log File(s) Used Size (KB)'
            , 'Percent Log Used'
        )
        --AND os.instance_name = 'Titan'
    ) as SourceTable
    PIVOT (
        MAX(cntr_value) FOR counter_name IN
        ([Data File(s) Size (KB)]
        , [Log File(s) Size (KB)]
        , [Log File(s) Used Size (KB)]
        , [Percent Log Used]))
        as PivotTable
    order by case WHEN log_reuse_wait_desc = 'NOTHING' THEN 0 ELSE 1 END desc
        , [Percent Log Used] desc

Personally I have this in a proc in my adminDB that can be ran whenever we see any issues relating to the transaction log to find out the exact state of it and what is the main cause of those issues

Its worth noting additionally for the auto shrink on the tlog, but mainly on the inevitable growth that you will have that the DB will be locked out while it grows the file and re-writes over that area of disk space


How to structure IF condition in MySQL trigger? I am trying to write a MySQL trigger. I have two tables like this:

Table A------------------------------Table B

order_id--------sku---------------order_id----order_#----sku_copy

    568---------AAA---------------568---------2345
    567---------BBB---------------567---------6789-------empty column
    566---------CCC---------------566---------1234

When a customer makes a purchase a new record is added to each table. I have added column ‘sku_copy’ to Table B, so it does not get populated when a new record is created.

When a new record is created, I want my trigger to copy the ‘sku’  field in Table A to the ‘sku_copy’ field in Table B. However, the problem I am having is how to structure the following condition in the trigger.

IF: ‘order_id’ in Table A matches ‘order_id’ in Table B. THEN: copy ‘sku’ from that Table A record to the record in Table B with the matching ‘order_id’. The data should be added to Table B ‘sku_copy'.

I am using the following SQL trigger but it gives this error when it's run:

"#1363 - There is no OLD row in on INSERT trigger"

Here is the trigger:

DELIMITER $$
CREATE TRIGGER trigger_name
    AFTER INSERT ON tableA
    FOR EACH ROW BEGIN

    INSERT INTO tableB
    SET sku_copy = OLD.sku,
         order_id = OLD.order_id,
        order = OLD.order;
END $$
DELIMITER ;

Can some one show me how to correct the error in this code or suggest a better one?

Thank you for any help you can give.

Here is an update:

I tried this trigger (this is the live data instead of simplified as in the above examples) but get an error code:

  "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE virtuemart_order_id=new.virtuemart_order_id; END IF; END' at line 7"

Here is that trigger:

DELIMITER $$
CREATE TRIGGER `sku_after_update` AFTER UPDATE ON `uau3h_virtuemart_order_items`
  FOR EACH ROW
  BEGIN
    IF (old.order_item_sku_copy != new.order_item_sku)
    THEN
    UPDATE uau3h_virtuemart_orders
        SET order_item_sku_copy=new.order_item_sku,
    WHERE virtuemart_order_id=new.virtuemart_order_id;
    END IF;
  END$$
DELIMITER ;

Does anyone have any suggestions on how to make this trigger work?

I assume you will value TableB first as it contains the order_no. In that case you need to use an update statement in your trigger instead of an insert statement:

SQL Fiddle

MySQL 5.6.6 m9 Schema Setup:

CREATE TABLE TableA(order_id INT, sku VARCHAR(10));

CREATE TABLE TableB(order_id INT, order_no VARCHAR(10),sku_copy VARCHAR(10));

GO
CREATE TRIGGER trigger_name
    AFTER INSERT ON TableA
    FOR EACH ROW BEGIN

    UPDATE TableB
    SET sku_copy = NEW.sku
    WHERE order_id = NEW.order_id;
END;
GO
INSERT INTO TableB(order_id, order_no)VALUES(1,'111');
INSERT INTO TableB(order_id, order_no)VALUES(2,'222');
INSERT INTO TableB(order_id, order_no)VALUES(3,'333');
GO
INSERT INTO TableA(order_id, sku)VALUES(1,'AAA'),(2,'BBB');

(The GO in this example is used as a batch separator and not send to MySQL.)

Query 1:

SELECT * FROM TableB;

Results:

| ORDER_ID | ORDER_NO | SKU_COPY |
----------------------------------
|        1 |      111 |      AAA |
|        2 |      222 |      BBB |
|        3 |      333 |   (null) |

If you also want to react to updates, just create an AFTER UPDATE trigger like this:

SQL Fiddle

MySQL 5.6.6 m9 Schema Setup:

CREATE TABLE TableA(order_id INT, sku VARCHAR(10));

CREATE TABLE TableB(order_id INT, order_no VARCHAR(10),sku_copy VARCHAR(10));

GO
CREATE TRIGGER TableA_AfterInsert
    AFTER INSERT ON TableA
    FOR EACH ROW BEGIN

    UPDATE TableB
    SET sku_copy = NEW.sku
    WHERE order_id = NEW.order_id;
END;
GO
INSERT INTO TableB(order_id, order_no)VALUES(1,'111');
INSERT INTO TableB(order_id, order_no)VALUES(2,'222');
INSERT INTO TableB(order_id, order_no)VALUES(3,'333');
GO
INSERT INTO TableA(order_id, sku)VALUES(1,'AAA'),(2,'BBB');
GO
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
GO
CREATE TRIGGER TableA_AfterUpdate
    AFTER UPDATE ON TableA
    FOR EACH ROW BEGIN

    IF (OLD.sku != NEW.sku)
    THEN
      UPDATE TableB
      SET sku_copy = NEW.sku
      WHERE order_id = NEW.order_id;
    END IF;
END;
GO
UPDATE TableA
  SET sku = 'NEW'
WHERE order_id = 2;
GO

Query 1:

SELECT * FROM TableB;

Results:

| ORDER_ID | ORDER_NO | SKU_COPY |
----------------------------------
|        1 |      111 |      AAA |
|        2 |      222 |      NEW |
|        3 |      333 |   (null) |

In both cases the NEW and OLD virtual tables refer to the table the trigger is defined on. NEW contains the new version of the row that was inserted or changed. OLD contains the pre-change version of the row. OLD is only defined in an update trigger as there is no old version on an insert.


Unable to restore backup taken on one server onto another server I am using SQL Server 2014 version 12.0.2000.8 on both systems. When I try to restore the backup taken from the first system onto the second system it says:

  No backup set selected to be restored

However I can restore it on the first system without any errors. I also tried to restore using the following command:

RESTORE DATABASE EBS_ATM
FROM DISK = 'G:\ebsBkup\ebs_bkup.bak'

The above command threw the following error message:

  Msg 3013, Level 16, State 1, Line 3
  RESTORE DATABASE is terminating abnormally.

I have checked rights and permissions on the folder as well as the .bak file.

Database version was checked through the query DetermineVersionOfSQLServer.sql in the Microsoft Knowledge Base article, How to determine the version, edition and update level of SQL Server and its components.

The database does not already exist on the second instance
RESTORE FILELISTONLY and RESTORE VERIFYONLY also throw error 3103
I know there should be another message before the 3013 eror, but I am getting only the messages I have posted.

The bak file is quite big ~70 GB. First I 7zipped and copy-pasted. Didn't work. Then I 7Zipped in 700 MB tranches. Didn't work. Finally I copied without zipping. Didnt work.

I would appreciate any help on this.

No backup set selected to be restored

As SqlBak Blog  Here the above error comes in two scenario.

  1)You are trying to restore your database to a lower version of SQL
  Server. If you make a database backup on a higher version, let’s say
  SQL Server 2012 and then you try to restore it on SQL Server 2004, you
  will get this error message: No Backupset Selected to be Restored.

  2)The backup file is unreadable or corrupted. You can simply check
  your database backup using the following command:

RESTORE HEADERONLY FROM DISK='D:/DatabaseBackupFileName.bak';

I think as my knowledge it's a incomplete error message.  Because most of the time along with Msg 3013, Level 16, State 1, Line 3, the operating system errors  (like Operating system Error 3,5 etc) or some  error come.

  Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating
  abnormally.

For this error i would recommend to Pinal Dave blog Here,Here and MSDN Blog Here

Even  Remus Rusanu answer also in dbastackstange related to Network drive with same error message Here


SSRS Label Orientation I'm trying to print labels on a Zebra printer in SSRS 2014 and the orientation is coming out wrong. In report builder, I set the page size to 4.15 x 1.15 with correct margins. Also, in report builder, if I print it will typically come out the correct orientation (landscape), but occasionally I have to go into Page Setup, change the Orientation from Portrait to Landscape & back again to get it to print correctly (Not sure why it requires a Portrait orientation to print in Landscape in Report Builder. However, when I try to print from SSRS directly, it comes out in portrait and prints across several labels. I've done some research and saw recommendations to change the printer to print directly and bypass the print spooler, which I've attempted to no avail. Any assistance is greatly appreciated.

I found the solution for my situation...

I went into Printer Settings & checked the "Always Use Driver Settings" under the "Override Application Default Settings." This caused the printer to always print in the correct orientation.


Reporting Services Database on different version Just wondering if anyone has come across this scenario and can provide some advice.

We have a vendor application that only runs on SQL Server Reporting Services 2014. However, all of our other applications support SQL Server 2017.

Can I setup SSRS 2014 on a separate server, but have the database residing on the SQL Server 2017 server?

Cheers
Phil

According to Docs, you can run a version of SSRS and host the database on lower versions of SQL Server, but there doesn't appear to be explicit support for running the DB on higher versions.


Is a 5+ column primary key bad for large (100 million+) table? I was reading about some real life DB issues, and one project had a 100 million row plus table that had 5 columns as its primary. I'm thinking this is bad, but can anyone tell me exactly why?

The table was kind of a micro rollup/aggregation table, so the 5 columns were like (day, market_id, product_id...). At first I thought that a 5 column primary key wasn't ideal, but the more I thought, I couldn't really come up with a good reason why it was bad.

This was in a late night discussion with half the company engineers. Someone just mentioned this was a bad design, one senior engineer agreed, but no one really jumped in as to why. Thus trying to research the matter for myself!

There are performance issues with very complex primary keys.  And It may not be defending against duplication as well as a simpler primary key might.

However, there is one design pattern that frequently yields tables with a primary key made up of six or so components.  It's star schema fact tables.  If the fact table of a star schema has six dimensions, then the primary key will have six components.  I've never seen a fact table with no declared primary key, and I think it's well worth the overhead, even though the ETL process still has to be quite carefully written.

Some reporting databases imitate the pattern of star schema even if it's not explicitly designed that way.

100 million + rows is not overly big for a fact table, especially with today's big data.


Numeric data-type for integers I need to store exact sums of bigint columns in a summary table. So I must use data type numeric for those columns.

Now I am wondering if setting the scale to 0 in this case makes sense (e.g. maybe some performance benefit?)

If so, then how can I set the scale to 0 and still keep the default precision:

according to the docs the data-type numeric() (without explicit precision and scale) supports:

  up to 131072 digits before the decimal point;up to 16383 digits after the decimal point

But when we explicitly set the precision and scale, the max. allowed value for precision is only 1000: i.e. 1000 digits before the decimal point, right?

  The maximum allowed precision when explicitly specified in the type declaration is 1000;

Yes, that is true (and a bit surprising).

You need to add quite a lot of bigint values to exceed numeric(1000,0), so I think you should be safe with that.


The InnoDB log sequence number is in the future Our site was getting intermittent database errors and I asked my webhost to check what's happening. After some inspection they found that the database has few issues and tried repairing it. Finally I got following message from them-

  I have attempted every repair on the InnoDB database and we are still
  getting  the InnoDB log sequence number is in the future. At this
  point to get the  ibdata and the iblogfile to match up once again we
  will need to restore the  MySQL directory (which includes databases)
  from the backup we have on the  server. The process should not take to
  long but there will be some downtime  associated with a restore like
  this. If this is not the best time to restore  the MySQL directory I
  can schedule this for a different time. Please let me  know how you
  would like to proceed with this.

Can someone tell me what'd be the best way to address this issue. I really don't want to lose out on any data and want the dB to be repaired.

PS: If you need more information, please let me know and I'll get it from our web hosts.

Would really appreciate your help.

What was suggested to you thus far is what can be done to bring the database to a consistent state.

Here is what you need to know about InnoDB.

First of all here is the InnoDB Architecture in Pictorial Form

Look at the Picture. What components are essentially for InnoDB's self healing (sounds better that crash recovery)?

The Double Write Buffer has the cache of changed blocks to be used for recovery.
The Insert Buffer handles updates to nonunique indexes
The InnoDB transaction logs (ib_logfile0,ib_logfile1) contain Redo Playback Info also used in recovery.
There are undo logs (1023 of them, the maximum number of concurrent transactions)

You need three files for recovery

ibdata1
ib_logfile0
ib_logfile1

The whole datadir folder (/var/lib/mysql) needs to be restored from the same moment in time it was being backed up. If there are no physical copies of datadir from the same moment in time, then log sequence numbers for future transactions can never be referenced correctly.

If you do not trust your host in this matter, perhaps you can get MySQL started with innodb_force_recovery set to an appropriate value.

Here are the values from the MySQL Documentation

  1 (SRV_FORCE_IGNORE_CORRUPT)

  Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

  2 (SRV_FORCE_NO_BACKGROUND)

  Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.

  3 (SRV_FORCE_NO_TRX_UNDO)

  Does not run transaction rollbacks after crash recovery.

  4 (SRV_FORCE_NO_IBUF_MERGE)

  Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics.

  5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

  Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

  6 (SRV_FORCE_NO_LOG_REDO)

  Does not do the redo log roll-forward in connection with recovery.

Once you select the value you want, start up MySQL with it. Then, perform a mysqldump of all the data. Keep that mysqldump somewhere.

I would set innodb_force_recovery to 6. Restart mysql. Do the mysqldump (MyData6.sql)
Change it to 5. Restart mysql. Do the mysqldump (MyData5.sql)
Change it to 4. Restart mysql. Do the mysqldump (MyData4.sql)
Change it to 3. Restart mysql. Do the mysqldump (MyData3.sql)
Change it to 2. Restart mysql. Do the mysqldump (MyData2.sql)
Change it to 1. Restart mysql. Do the mysqldump (MyData1.sql)

You now have 6 snapshots of the data based on how much could be recovered. You would then have to load each MySQLDump into a separate instance of MySQL. You would have to then peruse the data and determine if enough of the data has been recovered. Percona has a Data Recovery Toolkit that would do all this way more efficiently than I am saying it.

My answer is simply a poor man's approach to this.

I hope this helps !!!


How can we clean up/backup of .DBF files in Oradata folder in ORACLE on windows We have very less space on our C Drive where the DBF files are stored in windows server.
Can we clean up some space any logs can we removed on observation could see that DBF files are taking huge sapce

In general Oracle doesn't make it easy to reclaim disk space, other than through cleaning up log and trace files. If you haven't already, consider rotating the listener and alert logs, or removing old trace files from the ADR subdirectories. If you have much free space within your data files, also consider reorganizing your tablespaces (online reorg of tables, rebuiling indexes) to defragment them, then attempting to shrink your data files.
These articles - Automatic Diagnostic Repository (ADR) in Oracle Database 11g Release 1 (ADRCI) and Reclaiming Unused Space in Datafiles - will help you get started.


Index Maintenance - Reorganize After BIG SQL Server Shrink I'm planning to execute the below index maintenance created by Ola Hallengren in a 1TB Database.

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REORGANIZE',
@FragmentationLevel1 = 50,
@FragmentationLevel2  = 80,
@UpdateStatistics = 'ALL'
,@FillFactor = 100

So basically I know that shrinking is a very bad idea and not a common practice,
 I'm Shrinking because is really needed and it has been working good.

So due to the fragmentation that the shrinking is causing, I'm planning to use the mentioned stored procedure to Reorganize all the affected indexes and avoid getting the freed space back with a Rebuild

So the specific question would be, does this look like a good approach or have you faced something similar and used another approach for it ?, or if you have suggestions are gonna be highly appreciated.

If you have to shrink your DB ola's script for reorganizing is probably the best approach for handling the fragmentation.

please note, this is a costly operation- plan it to run on maintenance hours if possible and make sure that if you're in a full recovery model you have enough space on your disk that holds the log file and that you're backing up transaction log files properly.


Stopping SQL Services on Active Node I have an active passive SQL Cluster. When I stop the SQL services on active node from SQL Server Configuration Manager, the SQL cluster doesn't failover instead shows as Partially online.

If SQL services are stopped from Configuration Manager, it will not failover to another node?

I have to prepare test cases for my client, so it is really urgent for me to clear this doubt.

When configured as a Cluster in Windows, you need to control the SQL Server Services from the Failover Manager, not SQL Server Configuration Manager. In Windows Failover Manager, you can fail the resources/services to a particular node and then carry on with your needed activity.

The database engine and SQL Agent are cluster aware by default. Other SQL Server services (SSRS, SSAS) can be added as cluster aware resources, but their default behavior is not cluster aware. As far as I know, SSIS is still the only service that cannot be made cluster aware (i.e. it has to be installed on all nodes and started/stopped with SSCM on each node).

We accidently stopped the SQL Agent from the Configuration Manager, and it showed Partially Online as you mentioned.  For the cluster to get happy again, we had to offline the Agent from Cluster Manager, and then bring it back online from the same place.  The point to be made: always control failover or instant restarts or offlines from the FCM.


Should I use SQL or NoSQL for this specific design (surveys)? Building a small web application to create satisfaction surveys with a default list of questions and responses. The application is for a small audience of people (5,000 max) with surveys sent out when work orders are completed.

Given the small size and relatively simple requirements, I'm thinking I could utilize either a SQL or NoSQL solution (I have easy access to both).

Types of entities

Survey Templates (probably 3 of them)
Questions (multiple questions per survey template)
Question Types (free form, specific-choice responses, etc.)
Survey Responses (the responses to the questions on a specific survey template)

Querying / Reporting Needs

display responses of individual surveys
display aggregated responses across a range of surveys by certain aspects

work order # (multiple surveys per work order)
date range of work orders
group that performed the work

Change/Growth Needs

Survey templates, template questions, and possible responses can potentially change at any given time (dictated by the customers and evolving)

could conceivably create a new template when necessary but would be great to not have to do that where applicable.

No worry about growth in terms of storage. This is currently being done with a (horribly designed) Access DB and so we're not talking anything major here.

Other Needs

Structure should be able to accommodate requiring more information / notes if the responses are below a certain threshold (E.g. 3 or below out of 5)

Stack Considerations

This is being built on the Microsoft ASP.NET stack (MVC4, WebAPI, etc.)

Personal Preferences / Thoughts

I've wanted to work with NoSQL databases more for a while
I am more familiar with SQL databases but it seems like a lot of schema and server overhead for something like this
RavenDB and CouchDB are both appealing to me for this

Raven moreso because it's built so nicely to work with .NET and via HTTP, but I'm not sure if the licensing will work out.
Would also consider MongoDB, but I was thinking straight JSON docs might be easier to work with than BSON

with a NoSQL solution (particularly RavenDB), to an extent my objects become my schema because I'm persisting them to a data store. I have to build that structure in SQL server first (unless I go with Entity Framework). Does this lend an advantage to NoSQL or is it a wash taking into consideration things like EF?

Interested in your thoughts and providing any information that would help someone make an objective decision.

Typically No-SQL solutions were implemented due to massive scale and not needing 100% ACID compliance.  Flattening data out and de-normalizing it for performance gains were critical in getting us to where we are today with solutions such as GFS, Hadoop, Mongo, Basho RIAK, etc.

Your case doesn't scream No-SQL, if anything you'd probably be better served with MS SQL server since you're using the ASP.NET stack and MS SQL Server provides a lot of functionality with ASP.NET through CLR, powershell (which can also call .Net objects) and playing very nice with IIS (I assume you'll also be hosting it on IIS).  This also sounds like something you can use SQL Server 2012 Express thus saving licensing costs.

MS SQL Server will also let you easily 'join' data across non related tables in cases where you come up with a new idea, and on a No-SQL solution that would be more difficult to implement.

But in reality, looking at your requirements and what you're going to be using it for.  You can go either way.  If it makes you happier to use a No-SQL solution and get your feet wet, awesome!  Go for it!  End result probably won't matter, but to strictly answer the question, which is better for a .Net app with the usage patterns you described, I'd personally go with SQL 2012 Express.

I'm curious to see what others think!


SQL Multi Subnet Always on High Availability I am configuring a SQL Always on High Availability solution using SQL Availability groups, this will be spanning two subnets.
Using Windows 2019 and SQL 2019 one node in AWS and one on Premise.
When I start the create cluster wizard I am able to add both nodes into the cluster but when I get to the next section 'Access Point for Administering the Cluster'  I only see a single network where I expected to see two, one for each subnet.

We have now resolved this issue, was caused by the AWS DNS suffixes it appends to the domains. This caused issues with our domain names.
Have changed this setting to append primary and connect specific DNS suffixes and I was able to see both networks when building the cluster.


Non-unique multicolumn foreign key I have a "comments" table that models a conversation on a topic, like this:

id serial
topic_id integer
parent_comment_id integer
body text

So, every comment has a reference to its topic AND eventually its parent comment (if it's not the first comment on the topic).
I'd like to add a constraint that would prevent adding rows having mismatched topic/parent (for example by referencing a topic that doesn't have the required comment, or conversely a comment that references the wrong topic).
Is this possible? Is a trigger required?

(For the record, I tried

ALTER TABLE comments ADD FOREIGN KEY (parent_comment_id, topic_id)
                 REFERENCES comments (id, topic_id)

but it complains that there is no unique constraint matching given keys for referenced table "comments")

You need to add super-key (unique index/constraint) on both (id,topic_id). This gives you the  "target" uniqueness to create your foreign key. This acts like a CHECK constraint in this case.

ALTER TABLE comments ADD
    FOREIGN KEY (parent_comment_id, topic_id) REFERENCES comments (id, topic_id)

Note: id remains as primary key to preserve the model. Even if id is serial, it's would be wrong from a modelling perspective to change the PK to (id,topic_id)


Too many mysql instances. How to kill? I should be ashamed of myself, for asking, I know.

Check out this screenshot attached.

My server, which is indeed not seeing HUGE amounts of traffic, should not be this overloaded. Mysql is having a party and everyones invited. The thing is I don't like these kinds of wild parties.

How do I kill this stuff? How do I regulated it so that it doesn't keep happening? Pretty soon my host is going to call the police and the party will be over for everyone.

Don't be fooled by what you see.

mysqld will not multiple instances on the same datadir and ibdata1. How do I know this?

On Sep 26, 2014 I answered MySQL got signal 11 error and slave is down. In my answer, I mentioned how InnoDB Architecture only allows one mysqld process to open a Master Thread to ibdata1.

Therefore, if you do not see the following lines in your error log

6 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2014-09-26 12:51:53 14166 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2014-09-26 12:51:53 14166 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

In light of these things, I assure you, you do not have multiple mysqld processes.

What, then, are you seeing? Those are actual DB Connections emanating from the mysqld instance. I have seen this freakish display come from source-compiled versions of mysqld. I have mentioned it in passing in my old post innodb_buffer_pool_size not changed and Identical mysql processes (ServerFault). My suspicion is that the OS is manifesting DB Connections as if they were mysqld processes. On the contrary, they are just ordinary DB Connections.

You should run this in Linux

ps -ef | grep mysqld | grep -v grep | wc -l

You should get an output of 2 (1 for mysqld and 1 for mysqld_safe)

If you get just 1, you probably got a very old version of MySQL.


Listing IDs that match values in two columns of a table I'm using SQLite 3.27.2-3 on Debian Buster/stable. I could use some other DB, but for my use case (LuaTeX), something with low overhead seems to make sense.

My use case is that I have a letter (in the sense of correspondence) which refers to other letters in the text. I have a table that is automatically built from references in the text.

Let's say the table looks like this

RowID           From     To
=====           ====     ==
2019.06.05       PC      SW
2019.06.11       PC      SB
2019.06.12
2019.06.15       SB      PC
2019.06.22       DO      SW

and that it corresponds to some rows from a larger table, which I'll call attachments.

And let's suppose there is an abbreviations table which looks like

Abbreviation     Name
============     ====
B                Bashful
D                Dopey
DO               Doc
H                Happy
G                Grumpy
PC               Prince Charming
SB               Sleeping Beauty
S                Sleepy
SN               Sneezy

Then I want a table (abbreviations) that has a list of the abbreviations that occur in my short list of correspondence. This would look like

Abbreviation     Name
============     ====
DO               Doc
PC               Prince Charming
SB               Sleeping Beauty
SW               Snow White

Something like the query below works, approximately. But I also get blank output for when it doesn't match. I'm getting errors from SQLite (or LuaSQL, to be precise) so I'm wondering if it is because of the
blank output.

Better (and simpler) approaches appreciated.

SELECT abbreviations.Abbreviation,
       abbreviations.Name
FROM attachments
LEFT OUTER JOIN abbreviations ON attachments.`From`=abbreviations.Abbreviation
WHERE attachments.RowID IN ('2019.06.05',
                            '2019.06.11',
                            '2019.06.12',
                            '2019.06.15',
                            '2019.06.22')
UNION
SELECT abbreviations.Abbreviation,
       abbreviations.Name
FROM attachments
LEFT OUTER JOIN abbreviations ON attachments.`To`=abbreviations.Abbreviation
WHERE attachments.RowID IN ('2019.06.05',
                            '2019.06.11',
                            '2019.06.12',
                            '2019.06.15',
                            '2019.06.22');

SELECT DISTINCT abbreviations.*
FROM attachments
JOIN abbreviations ON abbreviations.Abbreviation IN (attachments.`To`, attachments.`From`)
WHERE attachments.RowID IN ('2019.06.05',
                            '2019.06.11',
                            '2019.06.12',
                            '2019.06.15',
                            '2019.06.22');

?


Why is this PostgreSQL not null constraint not catching this insert? I have a table for users that includes two columns for contact information. A user can provide a phone number, an email, or both, but not neither.

I have the table set up with the following constraints

create table application_user(
    -- other columns, primary key, etc.
    phone text,
    email text,
    constraint valid_email check (((email IS NULL) AND (phone IS NOT NULL)) OR (email ~ '(?:[a-z0-9!#$%&''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])'::text)),
    constraint valid_phone check (((phone IS NULL) AND (email IS NOT NULL)) OR (phone ~ '\d{10}'::text))
);

The idea is to make sure the email is valid if present and the phone is valid if present. However, when I run the following insert statement:

INSERT INTO application_user (email, phone) VALUES (null, null);

The insert still completes. What am I doing wrong here? I'm using PostgreSQL 12 if that matters.

As the check constraint with the regex will only validate non-null values, I would split this up in three different check constraints:

create table application_user(
    -- other columns, primary key, etc.
    phone text,
    email text,
    constraint exactly_one check (num_nonnulls(phone, email)=1 ),
    constraint valid_email check (email ~ '(?:[a-z0-9!#$%&''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])'::text),
    constraint valid_phone check (phone ~ '\d{10}'::text)
);

check (num_nonnulls(phone, email)=1) only validates that either one of the column is specified. The other two only check the format of the respective column. This - I think - makes things a bit easier to understand.


Optimizing index creation One thing that has bothered me for awhile is creating many many indexes for the one table where the query only differs slightly or contains many of the same columns.

An example case would be:

table1
--------------
id
state
first_name
last_name
city
phone
cat_id

Now, imagine the below queries:

SELECT * FROM table1 WHERE state = 'WA' AND cat_id = 124;
SELECT * FROM table1 WHERE state = 'WA' AND cat_id = 124 AND first_name = 'bob';
SELECT * FROM table1 WHERE state = 'WA' AND cat_id = 124 AND phone = '12345' AND first_name = 'bob';
SELECT * FROM table1 WHERE city = 'seattle' AND last_name = 'jones' AND phone = '12345';
SELECT * FROM table1 WHERE city = 'seattle' AND cat_id = 124 AND phone = '12345' AND last_name = 'jones';
SELECT * FROM table1 WHERE city = 'seattle' AND cat_id = 124 AND phone = '12345' AND first_name = 'bob' AND last_name = 'jones';

Normally I would just create an index for each type, unless there are far too many combinations. However, this generally feels like I am just creating one big mess, and on large tables, substantially increasing the INSERT/UPDATE/DELETE times.

Is there a better method to handle things like this?

Note: I don't have a table like this and I am also aware of leftmost indexing, this is purely an example case.

Indexing the columns that only have yes and no values (cardinality of 2) would never get use. You would quickly subject table1 to table scans. Index merges would be out of the question : See Combining columns in index

In the given table, note that you could separate the clothing types from the person

Here is your table. Let's call it person

CREATE TABLE person
(
    id INT NOT NULL AUTO_INCREMENT,
    state CHAR(2),
    jacket CHAR(3),
    coat CHAR(3),
    shoes CHAR(3),
    raincoat CHAR(3),
    sweater CHAR(3),
    pants CHAR(3),
    PRIMARY KEY (id),
    KEY (state)
);

Let's create two tables

Here is one to hold types of clothing

CREATE TABLE articles_of_clothing
(
    id INT NOT NULL AUTO_INCREMENT,
    article VARCHAR(20),
    PRIMARY KEY (id)
);
INSERT INTO clothing_types (article) VALUES
('jacket'),('coat'),('shoes'),('raincoat'),('sweater'),('pants');

Here is one to hold types of clothing associated with a person

CREATE TABLE clothing
(
    id INT NOT NULL AUTO_INCREMENT,
    person_id INT NOT NULL,
    article_id INT NOT NULL,
    PRIMARY KEY (id),
    KEY person_article (person_id,article_id),
    KEY article_person (article_id,person_id)
);
INSERT INTO clothing (person_id,article_id)
SELECT A.id,,B.id FROM person A,
(SELECT id FROM articles_of_clothing WHERE article='jacket') B
WHERE A.jacket='yes';
INSERT INTO clothing (person_id,article_id)
SELECT A.id,,B.id FROM person A,
(SELECT id FROM articles_of_clothing WHERE article='coat') B
WHERE A.coat='yes';
INSERT INTO clothing (person_id,article_id)
SELECT A.id,,B.id FROM person A,
(SELECT id FROM articles_of_clothing WHERE article='shoes') B
WHERE A.shoes='yes';
INSERT INTO clothing (person_id,article_id)
SELECT A.id,,B.id FROM person A,
(SELECT id FROM articles_of_clothing WHERE article='raincoat') B
WHERE A.raincoat='yes';
INSERT INTO clothing (person_id,article_id)
SELECT A.id,,B.id FROM person A,
(SELECT id FROM articles_of_clothing WHERE article='sweater') B
WHERE A.sweater='yes';
INSERT INTO clothing (person_id,article_id)
SELECT A.id,,B.id FROM person A,
(SELECT id FROM articles_of_clothing WHERE article='pants') B
WHERE A.pants='yes';

Let's remove the clothing columns from the person table

CREATE TABLE person_new LIKE person;
ALTER TABLE person_new DROP COLUMN jacket;
ALTER TABLE person_new DROP COLUMN coat;
ALTER TABLE person_new DROP COLUMN shoes;
ALTER TABLE person_new DROP COLUMN raincoat;
ALTER TABLE person_new DROP COLUMN sweater;
ALTER TABLE person_new DROP COLUMN pants;
INSERT INTO person_new (id,state) SELECt id,state FROM person;
ALTER TABLE person RENAME person_old;
ALTER TABLE person_new RENAME person;
DROP TABLE person_old;

Now that they are separated, let's look at your queries. We'll use just the first one.

SELECT * FROM table1 WHERE pants = 'yes' AND sweater = 'yes';

How can you accomplish this under the new design? Do it in stages

Create a Table to collect person_ids from each pass
Query the clothing table for all person_ids that have pants and collect them
Query the clothing table for all person_ids that have sweater and collect them
Count how many have person_id written twice
Join all person_ids found twice back to person table

Here are those steps

CREATE TEMPORARY TABLE queryids (person_id INT NOT NULL,KEY (person_id));
INSERT INTO queryids
    SELECT B.person_id FROM
    (SELECT id article_id, FROM articles_of_clothing WHERE article='pants') A
    INNER JOIN clothing USING (article_id) B
;
INSERT INTO queryids
    SELECT B.person_id FROM
    (SELECT id article_id, FROM articles_of_clothing WHERE article='sweater') A
    INNER JOIN clothing USING (article_id) B
;
SELECT B.* FROM
(
    SELECT COUNT(1) rcount,person_id
    FROM queryids GROUP BY person_id
    HAVING COUNT(1)=2
) A LEFT JOIN person B;

Since you only have 6 articles of clothing this may not be the best solution. If you have over twenty articles of clothing, the Query Optimizer stands a better chance of using indexes. I say this because when the Query optimizer has to read more than index entries for more than 5% a table, the Query Optimizer gives up an does a full table scan.

I have given this rule-of-thumb as a root cause to many index searching misadventures:

Dec 11, 2012 : How do I force a JOIN to use a specific index in MySQL?
Jun 18, 2012 : Why does MySQL only sometimes use my index for a range query?
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Mar 22, 2012 : Why does MySQL choose this execution plan?
Jan 18, 2012 : MySQL status variable Handler_read_rnd_next is growing a lot
Oct 20, 2011 : In MySQL, should I add an index even if the query that scans the table is only ran once a month?
Jul 12, 2011 : MySQL very slow query when changing one WHERE field despite no index/key


How to debug deadlocks in  SQL Server 2008 R2? I'm using SQL Server 2008 R2 that automatically lock database items based on my queries.

Sometimes this deadlocks - two threads with different queries never ends.

Is there any way to debug how SQL Server locks and unlocks resources? I have tried SQL Trace but it has no information about resources locks.

"Sometimes this deadlocks - two threads with different queries never ends"

What you are experiencing is blocking, not deadlock. When a deadlock condition occurs, SQL server automatically detects it and choose one of the queries as a victim and throws error 1205.

If you are really experiencing deadlock errors, turn on trace flag 1222. It will log the deadlock errors to the SQL server log.

If you are experiencing blocking, configure the "block process threshold" (http://msdn.microsoft.com/en-us/library/ms181150(v=SQL.90).aspx)

Also see http://www.mssqltips.com/sqlservertip/2429/how-to-identify-blocking-in-sql-server-2005-and-2008/


Without more information, we can only guess why these two almost identical queries run with so much different efficiency (about 500x).

But we can make a few guesses based on the facts:

There is no index on SID and UPDATE_STAMP so both queries do a table scan.
The difference must lie in the mediumtext type.
This difference in speed suggests that the first query can do the table scan relatively quickly (the table is small) and find that no rows satisfy the condition. But the second query is very slow compared to the first one. My guess is that the mediumtext column has some long texts in it. As text and long varchar column values are not stored in the table/index pages but in different locations, this would explain the difference. In the 2nd query, the engine fetches these values as well while running the full table scan (so many more pages have to be read from disk) and thus the slow performance.

A suggestion to improve efficiency. Find first the primary key of the wanted row, using a subquery, then fetch the whole row:

select file_data
from bb_table
where pk = ( select pk            -- where pk is the PRIMARY KEY
             from bb_table
             where update_stamp > '0'
             limit 1
           ) ;


SQL Server 2005, non-yielding scheduler Spinloop on Snapshot I was running dbWarden's procedure for CheckFiles on my server.

The job never ended and my logs started to fill up with message at a rate of 1 per minute: 2014-01-07 08:40:04.07 Server Process 58:0:0 (0x1438) Worker 0x00000000D62B41C0 appears to be non-yielding on Scheduler 4. Thread creation time: 13033413586324. Approx Thread CPU Used: kernel 46 ms, user 2421 ms. Process Utilization 10%. System Idle 82%. Interval: 79691009 ms.

From what I understand is that it means the thread on the CPU is just locked up.

The SSMS GUI just doesn't work with the instance any longer. In TSQL most things work, unless they reference the snapshot, which nixes most dmvs and system procedures. SP_Who2 doesn't work, but SP_Who does.

SP_WHO shows me 58 alternating between ready and spinloop.

58 is running the code to check files (locations, growth information, etc) on a snapshot.

When I KILL 58, it says it's rolling back, but nothing happens and it's still in a spinloop.

When I try SP_Who2, it gets blocked by the spinloop. When I drop the snapshot, it's blocked by the spinloop. When I restart or Stop/Start the instances service, the errors return to the log and the spinloop resumes.

Any ideas or advice?

The default is the problem, as a rogue process would utilize all the available threads and will lead to thread starvation. Better is to adjust the maxdop setting as described in
What is a good, repeatable way to calculate MAXDOP on SQL Server?

Also, let the rollback complete as if you restart sql server when a rollback is in progress, it will redo everything that it has already done when the service is restarted.

A similar case can be found Need to understand parallel query execution error. This has more details that I put in and will explain you better.


Query plan do not get invalidated after update statistics I'm running some tests to find out when query plans get invalidated after an update statistics. The machine I'm using for testing is a SQL Server Developer 2016 SP1 CU7.

I found an article on BrentOzar.com how I can trace the recompiles but I never get a recompile.
Auto Update Statistics and Auto Create Statistics are both enabled

This is my test:

/* Create a table and put over 1k rows in it (to get past the 500 row stats threshold) */
CREATE TABLE dbo.MyTable (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, StringField VARCHAR(50));
GO
INSERT INTO dbo.MyTable(StringField)
SELECT 'Stuff' FROM sys.all_objects;
GO

/* Start a trace monitoring recompiles */
exec sp_BlitzTrace @Action='start', @TargetPath='c:\temp\', @SessionId=@@SPID, @TraceRecompiles=1;
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere';
GO
/* Increment the row mod counters to encourage a stats update */
BEGIN TRAN
DELETE dbo.MyTable;
GO
ROLLBACK
GO
UPDATE STATISTICS dbo.MyTable WITH fullscan
GO

/* We don't strictly need to wait, but makes different executions easier to see: */
WAITFOR DELAY '00:00:10';
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere';
GO
EXEC sp_BlitzTrace @Action='stop'
GO
EXEC sp_BlitzTrace @Action='read'
GO

As I understand it, the plan should get invalidated when you call UPDATE STATISTICS WITH FULLSCAN and there are rows changed.
Or am I missing something?

Your original sample code wasn't re-runnable (the table creation wasn't in there, or the population of rows) so I rewrote it to flesh it out and make it re-runnable.

The problem is that your query is trivially simple: it doesn't matter whether stats are updated or not, it's going to produce the same plan. This tweak will make your SELECT query bypass trivial optimization:

SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere' AND 1 = (SELECT 1);

Try the test again with that:

/* Start a trace monitoring recompiles */
exec sp_BlitzTrace @Action='start', @TargetPath='c:\temp\', @SessionId=@@SPID, @TraceRecompiles=1;
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere' AND 1 = (SELECT 1);
GO
/* Increment the row mod counters to encourage a stats update */
BEGIN TRAN
DELETE dbo.MyTable;
GO
ROLLBACK
GO
UPDATE STATISTICS dbo.MyTable WITH fullscan
GO

/* We don't strictly need to wait, but makes different executions easier to see: */
WAITFOR DELAY '00:00:10';
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere' AND 1 = (SELECT 1);
GO
EXEC sp_BlitzTrace @Action='stop'
GO
EXEC sp_BlitzTrace @Action='read'
GO

And now you get recompiles - note the third line that shows recompile_cause = "Statistics changed"

This is one of the reasons that sp_BlitzCache (disclaimer: I'm a very small author on that open source script) shows warnings for trivial plans - you don't get all of the cool stuff you get on fully optimized plans, like missing index recommendations.

I'm certainly not saying that adding "WHERE 1 = (SELECT 1)" is a good idea in your queries - just saying that when building repro scenarios, you gotta watch out for trivial queries. To know if your query is trivially simple, view the execution plan, right-click on the root node (like the SELECT), click Properties, and look at the Optimization Level. If it says Trivial, you're not getting the full genius of those folks at Microsoft.


HSQL DB grows large and is full of zeroes I have an application whose HSQL database grows awfully large for some users, but not others. When I received one example where this problem happenned, I discovered that the database is nearly empty - it's almost 16GB of zeroes and only a couple tens of megabytes of real data. Upon performing CHECKPOINT DEFRAG, it shrinks to its real, small size.

Unfortunately, I'm not very familiar with this database engine and my online searches have produced little useful information. One interesting thing was that there is an option to automatically perform the defragmentation upon reaching a certain size, which I didn't use. However, I would expect the DB to reuse dead rows and not keep them around, and definitely not zeroed out as this must have a bad effect on performance.

Why does the database keep producing so much empty space, which is only interrupted by data here and there? How can I find out what's happening?

One explanation is the case where lots of rows of data are created but later deleted by the application. If these rows happen to be in the memory cache throughout, they are never written to disk.

The space for deleted rows is reused for up to 500 most recent items, the rest discarded.

The CHECKPOINT DEFRAG command can be used from time to time to compact a database that has lots of empty spaces.

The database engine does not write zeros. The zeros in the file are there when some file space is allocated for use.


WAMP server performance tuning for MySQL We have a Drupal site with nearly 2000 pages running on WAMP server. The pages also contain images, and flash videos embedded into them. So, they are taking some time to load under normal settings.

Nearly 200-300 users are expected to use the system concurrently (the number is expected to grow later).

The dev server (on our company intranet) has 16 GB RAM, and runs Windows7 - 64 bit OS. I can see that in wamp/bin/mysql/mysql5.5.16/ has files

my.ini
my-huge.ini
my-innodb-heavy-4G.ini

I had a look into both of them, and I am not sure what is the purpose of each one of them and which one is used by my the WAMP server currently. Also, I needed to know the difference in using my-huge.ini and my-innodb-heavy-4G.ini. I have also configured Apache SOLR for indexing the search (I don't know if this is relevant).

P.S : Drupal stores pages (along with the link to images and videos) into the database and images and other media are stored in folder under the site root.

The differences have to do with the expected capacity mysqld is requested to handle

my_huge.ini

does not have max_connections set
has innodb settings commented out
values preset

sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

my-innodb-heavy-4G.ini

has max_connections set at 100
has innodb settings enabled
values preset

max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M

Default values are used if not specified. Whichever one you want to use, you must copy it over my.ini and restart mysqld to take effect.

If using my-huge.ini, you must shutdown mysqld, copy my-innodb-heavy-4G.ini to my.ini then startup mysqld.
If using my-innodb-heavy-4G.ini, you must shutdown mysqld, delete the innodb log files (ib_logfile0,ib_logfile1), copy my-innodb-heavy-4G.ini to my.ini then startup mysqld.

Since you said the number of DB Connections is expected to grow, you must raise max_connections to higher values but you must balance the usage of RAM. The best way to balance it would to run some diagnostic program like mysqltuner.pl and get recommendations adjusting settings per connection and per instance.

Since you are running MySQL in a WAMP environment, I would not surpass 25% of RAM for total memory because you must give Windows, Apache, and PHP (or Perl or Python) their due in terms of RAM. (I am glad you are using Apache and not IIS, otherwise this would be a WIMP environment !!!)


Character varying index overhead & length limit I have a unique constraint on a character varying column that will mostly have lengths of approximately 600 but could get to 10 million.

I have read about B-Trees, but I can't determine how much disk space the index will consume or if such large-sized values will prevent the index from functioning performantly.

What are the impacts upon disk space and time of a B-Tree index on character varying columns with such potentially large lengths?

In Postgres, a unique constraint is implemented with a unique B-tree index. Per documentation:

  Adding a unique constraint will automatically create a unique btree
  index on the column or group of columns used in the constraint.

Indexes use the same basic storing mechanisms as tables: arrays of data pages. Indexes have some minor additional features. Per documentation:

  In a table, an item is a row; in an index, an item is an index entry.

  Every table and index is stored as an array of pages of a fixed size
  (usually 8 kB, although a different page size can be selected when
  compiling the server).

The maximum size of an index entry is a third of a data page, I quote Peter Eisentraut on this:
PostgreSQL primary key length limit

That's 2730 bytes (or a bit less). Meaning your UNIQUE constraint is not possible.

I would consider adding a redundant column with a hash value and create a unique index on that.
You could use the built-in function md5() or something more efficient for huge values.

Here is a related solution with a focus on performance:
Index max row size error


FREETEMPORARY on blob with no reference In Oracle 11g, we are using XMLELEMENT to get records from tables. The problem seems to be, however, that we are calling SELECT XMLELEMENT(...).getClobVal() from DUAL; which is eating our temp tablespace like a sandwich.

I read that we can kill blob records using FREETEMPORARY(blob_ref), but in the format shown above, we have no reference to the blob. Is there any way to get one for it?

Because this is being run from a program and the library we use generates the PL/SQL block for us, it really cannot be restructured as a PL/SQL statement.

The DBCP is the connection pool we use with Tomcat and Spring's JdbcTemplate is what we're using to run the queries. Considering that JdbcTemplate swaps out bind variables for us, we're assuming that it is creating an anonymous block behind the scenes.

Running select * from v$temporary_lobs; reveals that for the current session I still have 4 in the ABSTRACT_LOBS column. This is after running the query by itself (no anonymous block) four times in SQL Developer.

Answer originally left in comments by David Mann:

If you are unable to run FREETEMPORARY then I believe your only option is to disconnect/reconnect the session. This will cause a cleanup of any temp blob space used by that session. Had a similar issue here where we couldn't touch the app code, so we ended up tweaking their connection pool to expire and reconnect more frequently.

Oracle Metalink Doc ID 802897.1 "How to Release the Temp LOB Space and Avoid Hitting ORA-1652" covers this, for 11g your option is to invoke FREETEMPORARY or to exit session. They also mention a new event 60025 which is supposed to free temp segments when all the temp LOBs for a session are no longer used, but I didn't see any other detail on that.


Why we can FLASHBACK DROP table where it is DDL command and hence autocommited? DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider backup).

We need to understand how DB performs both the operations. Database in itself maintains record for each table. For simplicity, you can consider a database as a table, and all the tables information of database as data of that table.

So now when you DROP a table (which actually deletes the data and structure of table), Database doesn't delete the entry of that table and it maintains an entry of that table (in other words, sort of a soft delete operation). When you PURGE, then only database start looking for those table entries which are marked as soft delete and start deleting them permanently. Hence when it is purge (or permanently deleted) then you cannot recover it.

OTOH when you execute a TRUNCATION query, then it deletes the data but it is also combined with PURGE command. So when you TRUNCATE then it removes the data permanently.

In simple words, you can relate this with example of DEL and Shift+Del. When you DEL a file, then it goes to RecycleBin and you can recover it from there. But when you delete a file with Shift+Del then it permanently deletes. I hope it helps.


MySQL Workbench doesn't show relationships between tables when using Reverse Engineering(Mac OS) After running this and successfully creating the database, tables and both the primary and foreign keys, when I reverse engineer my database it won't show the relationships between tables. I'm using a MacBook Air(2017) Mojave 10.14.6, no dark mode and I've tried changing relationships notation from craw's foot to the others but still no luck.

create database test_asdf;
use test_asdf;

create table Department
(id int not null unique auto_increment primary key,
name varchar(30));

create table Professor
(id int not null unique auto_increment primary key,
name varchar(30),
adress varchar(50),
department_id int,
section_id int,
foreign key (department_id) references Department(id));

Now the thing is when I run the following the connections show:

create database test_asdf8;
use test_asdf8;

create table table1
(id int not null unique auto_increment primary key,
name varchar(30),
table2_id int);

create table table2
(id int not null unique auto_increment primary key,
name varchar(30),
new_id int,
foreign key (new_id) references table1(id));

Could someone please point out what I'm doing wrong here?

After further research it seems this is actually a bug.
Instead of directly reverse-engineering your database:

create a new model

save all your code in an .sql file

while in the model tab go to file-import-reverse engineer create script

check "place imported objects on diagram" and execute and there you go

Still unsure why the first above example won't work(with the straightforward approach) and the second one will but well... hope this helps.


Postgresql: delete all comments from database I have a database for which the comments on objects (PSQL command COMMENT) are just pollution for us: they are obsolete and in a language that no one reads in our organisation. Hence, I'd like to delete all these comments at once. There are hundreds of them and I don't want to write hundreds of command lines "COMMENT ON xxx IS NULL".

Anybody knows how to do this ?

Erwin Brandstetter gave a very good answer on how to do this on this question Removing COMMENT ON from all objects in PostgreSQL on Stack Overflow.

It basically consists of removing the comments directly from the system catalogues like so (disclaimer - the following code is copied directly from his answer):

DELETE FROM pg_description WHERE description = 'something special';

Or you could delete the comments based on any other criteria you may choose.


IBM FlashSystem V9000 storage - should the os, mdf and ldf and tempdb be separated onto different volumes? Is there any benefit that justifies separating the os, mdf, ldf and tempdb onto separate volumes?

RAID and redundancy is already included.

Connection options to the host are:

16 x 16/8/4 Gb Fibre Channel
8 x 10 Gb Fibre Channel over Ethernet (FCoE); and
8 x 10 Gb iSCSI

...so I don't think there is a concern with that being a bottleneck.

The FlashSystem datasheet is here:

IBM FlashSystem V9000

One reason for separate drives even with SAN is that a given disk or set of disks can only do so much I/O at once. Any of the things on the drive could saturate the I/O on the SAN by keeping OS, TempDB, logs, page file, and the user database on the same drive. Depending on the query and server configuration, TempDB, the page file, and the LDF could all be in contention for the I/O. By separating those, the I/O for those set of disks is exclusive to where they are allocated.

Another thing to consider is any one of those things could also use all the available space if sharing disk space and instead of one component shutting down, the whole server could be shut down.


MySQL: Is it possible to have an IF subquery? I'm attempting to run a subquery inside of an if statement used in the SELECT, but I'm getting an error. I've never needed to do something similar before so I've never tried this until now. Here is what I'm running:

SELECT t1.*, IF(t1.tin > 0, (SELECT name FROM special_packaging WHERE id = t1.tin), 0), t2.productname, t2.imagepath FROM cartitems AS t1 INNER JOIN products AS t2 ON t1.productid = t2.id WHERE t1.cartid = 8

I'm getting a syntax error. Is it possible to run a subquery inside of an IF statement?

Any advice on what I'm doing wrong is greatly appreciated.

EDIT:

This is the error message I am getting:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

I would probably convert it to a more-standard JOIN and a CASE statement, personally:

SELECT
     t1.*
    ,CASE WHEN t1.tin > 0 THEN sp.name ELSE 0 END
    ,t2.productname
    ,t2.imagepath
FROM cartitems AS t1
JOIN products AS t2
  ON t1.productid = t2.id
LEFT JOIN special_packaging sp
       ON sp.id = t1.tin
WHERE t1.cartid = 8


Why is Mongo stuck in STARTUP2? I have a Mongo replica set with a few secondaries. A box, which hosts a secondary instance, crashed and lost the database.

I started the secondary Mongo instance again and now it's stuck in STARTUP2 for more than 12 hours. Does it make sense ? The docs say Mongo should be in STARTUP2 for a short period of time before entering the RECOVERING state

What does STARTUP2 exactly mean ? Is it copying the database from the primary ? How can I verify it (assuming the Mongo is running in Linux) ?

The answer of eoinbrazil is partly incorrect. A new Node can be in STARTUP2 for a long time. The link the posted says:

  Each member of a replica set enters the STARTUP2 state as soon as mongod finishes loading that member’s configuration, at which time it becomes an active member of the replica set. The member then decides whether or not to undertake an initial sync. If a member begins an initial sync, the member remains in STARTUP2 until all data is copied and all indexes are built. Afterwards, the member transitions to RECOVERING.

I'm administering a 700 GB collection and, when I add a new node the STARTUP2 state remains well over 24 hours. But you can still see if there is something happening, by watching if the database grows. You can see the size of the database on the new node with

show databases

or you can also observe the data directory, to see if it is still growing. (on linux with the commands ls, df, du, iotop, etc ....)


DBPATH, LOGPATH and STORAGEPATH Assuming that there are only two available paths, say /log and /data. Would it be better to set DBPATH to /log or /data. I.e.

CREATE DATABASE WHATEVER
AUTOMATIC STORAGE YES
ON /data DBPATH ON /log;
UPDATE DB CFG WHATEVER USING newlogpath /log;

or

CREATE DATABASE WHATEVER
AUTOMATIC STORAGE YES
ON /data DBPATH ON /data;
UPDATE DB CFG WHATEVER USING newlogpath /log;

I noticed the other day a system that used /data for both DBPATH and tablespace storage, and I'm wondering whether /log would have been a better choice for DBPATH?

Any thoughts?

It almost certainly doesn't really matter.

The only significant I/O going to files in the database path would be to the recovery history file (and even that isn't much).

Considering that DB2 uses the first storage path by default if you do not specify DBPATH ON, I would put it on /data if that was the only choice.

My SOP is to keep DBPATH in the instance owner home directory.  This is obviously another important location and perhaps makes more sense than the other two locations.


Import 2 column CSV files to some sort of DB.... preliminary planning Every day I have a CSV file generated by a script. It has two columns. Column 1 is a name, column 2 is the size of their mailbox.

I have a years worth of these files. I would like to be able to import them into a database (We have SQL in house, or I could install MySQL, or anything else for that matter)

I want to be able to see growth patterns for these users over time. Basic reports, which is another problem that I'll solve at a later time. For now I just want the data in a DB instead of hundreds of flat files.

What kind of DB is good for this? Simple is best. I'm not a DB guy.
What would you do? This is mostly a learning project for me.

A while ago I wrote a couple of blog posts on loading logfiles into SQLite for analysis. This is an incredibly easy database to use, zero administration, bindings to any language you can think of (e.g. Python and Tcl/Tk as well as exotic ones like OCaml) supports a decent subset of SQL for complex reporting and can handle surprisingly large datasets (millions of rows). You don't say what OS you're on, but on Linux it's just a yum or an apt-get away. I'd definitely choose it over MySQL for this kind of work.


Join on NULL Key Column Optimizing as Table and Index Scans I have a question about this query plan.

We have a table in a test environment, Order_Details_Taxes, that has 11,225,799 rows.  This table has a column, OrdTax_PLTax_LoadDtl_Key, which is NULL on every single row.  This test environment is configured in such a way that this column will always be NULL.  There is an index on this column.

I ran some queries against this table using a NULL value for a column.  A NULL INNER JOIN will never yield any results.

declare @Keys table (KeyValue decimal(15,0))
insert into @Keys (KeyValue) values (null)

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue

select *
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue

These are the first queries in the query plan. The first select starts from the hundred-million-row table and joins to @Keys.  The second select starts from @Keys, but it does a clustered index scan on this table.

I know temporary @Tables are questionable in most cases, so I changed my query to use a temporary #Table:

if object_id ('tempdb..#Keys') is not null
    drop table #Keys
create table #Keys (KeyValue decimal(15,0))
insert into #Keys (KeyValue) values (null)

select OrdTax_PLTax_LoadDtl_Key
from #Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue

select *
from #Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = null

These queries were optimized and ran exactly as I expected-- get the #Keys NULL value first and seek to Order_Details_Taxes.  They are the last queries in the query plan linked.

Why do the queries in which I used a @Table variable perform index and table scans on this large table, when I am joining using from a table that has a single NULL value to a table with only NULLs in this key value?

I assume the answer is statistics and/or cardinality limitations of @Table variables, but the resulting query plan was non-intuitive to me.

ANSI_NULLs is on for this table and my SQL session.

The behavior that you're seeing is caused by the lack of statistics on the table variable. When I want to learn more about why the query optimizer chose a particular plan I sometimes add hints and compare the queries side by side. That approach is helpful here.

First I'll create a table which is close enough in structure to yours to see the same behavior:

CREATE TABLE dbo.Order_Details_Taxes (
    OrdTax_PLTax_LoadDtl_Key decimal(15,0),
    FILLER VARCHAR(30)
);

INSERT INTO dbo.Order_Details_Taxes WITH (TABLOCK)
SELECT NULL, REPLICATE('Z', 30)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE INDEX [IX_OrdTax_PLTax_LoadDtl_Key] ON Order_Details_Taxes (OrdTax_PLTax_LoadDtl_Key);

To see how the query optimizer costs the different join types I can get an estimated plan for the following:

declare @Keys table (KeyValue decimal(15,0))
insert into @Keys (KeyValue) values (null)

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue;

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue
OPTION (LOOP JOIN, MAXDOP 1);

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
    on OrdTax_PLTax_LoadDtl_Key = KeyValue
OPTION (HASH JOIN, MAXDOP 1);

Here is a screenshot of the estimated plans:

SQL Server doesn't know anything about the value of the row in the table variable, so it creates the nested loop plan using the density of the statistics on OrdTax_PLTax_LoadDtl_Key. All of the rows have the same value in the stats so the density is 1. One of the general assumptions of the query optimizer's models is that data exists if the end user is looking for it. So your index seek is expected to return the same number of rows as the scan and has the same cost, despite the fact that the histogram only contains NULLs. In this case, the optimizer doesn't go back and apply special knowledge about NULLs to change the plan. You could argue that the optimizer could be improved to do this, but this does seem like an uncommon scenario.

The difference in costs of the plans ultimately comes down to the costs of the join operators themselves. For whatever reason the query optimizer costs the loop join higher than the merge join. The hash join is costed high as well, but for that one SQL Server expects to need to compute millions of hashes so the higher cost is more understandable imo.

What happens if you get the same plan with a temp table that doesn't have stats? The right way to do this is to disable automatic statistics creation for the table but I'll take a shortcut:

if object_id ('tempdb..#Keys') is not null
    drop table #Keys
create table #Keys (KeyValue decimal(15,0))
CREATE STATISTICS s1 on #Keys (KeyValue) WITH NORECOMPUTE;
insert into #Keys (KeyValue) values (null)

Everything looks the same as the table variable plan:

That's why I said the behavior is caused by the lack of statistics. When you use a temp table and allow auto stats creation the optimizer has a histogram on the temp table's column. It can use that information to generate more accurate cardinality estimates for the nested loop join plan and the index seek:

The histogram suggests that no columns will be matched so you end up with the minimum cardinality estimate of 1 row out of the seek. The costs of the loop join and the seek are reduced accordingly, and the nested loop join plan has by far the lowest cost out of the three join types.

Having some NULL values in the outer table of a join is a significantly more common scenario than joining to a table with all NULLs. In other words, I would expect more better model support for comparing two histograms that both contain NULL compared to a histogram to just NULLs compared to an unknown value. With better model support you can get better cardinality estimates, and in this case the better cardinality estimates result in a significantly more efficient query plan.


mysqldump from AWS RDS slowing down Im taking a db backup from AWS RDS (MySQL) slave from an EC2 (m5.large - 2c/8GB) in same AZ using -

mysqldump -hxx -u xx -p --extended-insert  --single-transaction  --max_allowed_packet=1G db > db.sql

The backup dies at around 60-70 minutes with error : mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table xx at row: 1743503

The error row changes but the table is the same at which mysqldump errors out
Table has 4M records and size is around 1.5 GB
max_allowed_packet on source is 4MB (although this is inconsequential I believe)

So I tried with single table backup with pv enabled :

mysqldump -hxx -u xx -p --extended-insert  --single-transaction  --max_allowed_packet=1G db tbl_xx | pv > db_tbl_xx.sql

The pv stream was interesting as I saw ~10 MiB/s speed for sometime, then froze to 0 B/s for a few seconds. When it resumed, it hovered around 700-800 KiB/s for the rest of the time till the backup finished (1.79GB - 35 minutes). Only in case of entire DB, the backup never finished but died throwing the "Error 2013".

Interestingly, a backup from SQLyog GUI for the same table finished in around 5 minutes over the internet.

I'm unsure if the mysqldump parameters are not supporting large backups or some network level configuration is the problem here.
Some help with pointers to check on either side will be great, thanks!

On a re:Invent conference I watched once, an AWS engineer suggested when we are going to use high IOPS processes, to increase the instance type. My suggestion would be to change your instance type to db.m5.4xlarge, wait 20 minutes and then try again the dump.

Chances are that it will work like a charm!


When do MySQL DBA need root access For a MySQL DBA, what operations require root access. I know that we need to be root for installation, starting and stopping services. With DBA’s not having root access, is there a way to perform majority of the operations?

Basically the mysqld is started as mysql user (in the init script is something like su mysql). Therefore there is no other option beside having access to this user.

But you can setup your system to run mysqld as a normal user.


Removing TDE from server I have removed Transparent Data Encryption (TDE) from my server, dropped the key and switched the databases to Simple, shrunk the log and then back to Full.

The LOG backups are now smaller as well as the full backups but the main mdf files are still the same size after removing TDE.

Is this normal? Is there a way to get these back to what they were before TDE?

I know the MDF files will stay the same size until you shrink them but the space available inside I would have expected to increase significantly but it didn't.
TDE seemed to double the size of them.

Yes this is normal. SQL Server will only automatically reduce the size of data files if you have AUTO SHRINK switched on and there is space available in the files. This setting is not recommended as it will introduce considerable fragmentation and consume considerable IO resources.

You could do a one time shrink with DBCC SHRINKDATABASE. See here for more info. This will introduce fragmentation but it can be fixed by rebuilding your indexes.

IMHO the main considerations would be:

Can you live with the current size of the database? If yes then shrinking is probably not worth the effort.
Do you have a long enough maintenance window to shrink and fix the fragmentation? You could test this process by restoring a backup to a similar specced machine and running through the process.

Currently shrinking a databse will fail if you have any columnstore indexes. These need to be dropped if you want to shrink the DB as much as possible.


Should I shrink the log file? My log file was too big, and I made it unrestricted in growth. Should I shrink it with SQL Server Management Studio?  What are the downsides, if any, of doing this?

Two thoughts come to mind when someone says "My log file is too big".  First and simplest is that something is running against your database that requires that much space.  Unless it happens to be a one off query someone wrote if you shrink your log file it's just going to grow right back.  And slow your queries down while it's doing it.  The solution here is to find out if there was a large ad-hoc query run that grew your log file.  If you don't find any evidence of that then I would shrink the log to a reasonable size and see if it grows back.  If it does grow back then leave it alone and add yourself some disk space.  That's just how big it has to be.  If you restrict it to a smaller size it's going to cause queries that need the extra space to fail.

The second and scarier thought is that your database is in FULL or BULK LOGGED recovery mode and you haven't been taking regular transaction log backups.  If this is the case then your log file will just grow and grow.  The solution here is to set up regular transaction log backups.  Once these are in place shrink the log file to a reasonable size.  If it grows again (hopefully not as large), then again that is just the size it has to be.


How to script server level objects in sql server? I regularly do sql server installations (mainly sql server 2016), in virtual machines or otherwise, and I have a bunch of things like permissions, and linked servers and endpoints that I have mostly all scripted so that in the new installs I can create all these objects.

On a brand new install that I am working on however,
it was supposed to be an exact copy of a specific server let's call it serverX.
serverX had a couple of endpoints not specified on my creation of sql server server level objects script
therefore it caused me troble - port 4022 was not listed, and not listening and the powershell connectivity test was not working
 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection my_sql_server -port 4022'
 EXEC xp_cmdshell @sql

although all the firewall was already set up.
to avoid this in the future, is there a way to script all the endpoints?
or better would be script all server level objects as per the picture above?
obviously to script this specific endpoint, you just need to right-click and script it:
USE [master]
GO

/****** Object:  Endpoint [ServiceBrokerEndpoint]
   Script Date: 20/03/2021 16:04:05
 ******/

CREATE ENDPOINT [ServiceBrokerEndpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
    FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = DISABLED)
GO

and after I did that the netstat command started to list port 4022 - the service broker default.
but just to automate future installs, is there a way to via code or powershell, script all or some server level objects?

One way to script server-level objects is with SMO and PowerShell. Below is a PS example that uses the SMO assembly included with SSMS to script server endpoints and triggers. SMO is also available on NuGet to avoid the dependency on an SSMS install.
# generate scripts for server-scoped object collection
Function Create-ServerObjectScripts($serverCollection, $rootFolder, $typeName) {

    # exclude system objects
    $serverCollection = $serverCollection | Where-Object {
        $_.IsSystemObject -eq $false -or $_.IsSystemObject -eq $null
    }

    if($serverCollection.Count -eq 0) {
        return
    }

    Write-Host "Scripting $typeName for server $($server.Name)..."

    $scriptingOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
    $scriptingOptions.IncludeIfNotExists = $true
    # specify desired SQL version below
    $scriptingOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version130
    $scriptingOptions.AppendToFile = $true
    $scriptingOptions.ToFileOnly = $true

    foreach ($serverObject in $serverCollection) {
        $scriptingOptions.FileName = "$rootFolder/$typeName.sql"
        [void](New-Item -Path "$($scriptingOptions.FileName)" -ItemType "file" -Force)
        $serverObject.Script($scriptingOptions)
    }

}

# ############
# ### MAIN ###
# ############

try {

    $serverName = "YourServer"
    $scriptRootFolder = "C:\Scripts\YourServer"

    $smoAssemblyDirectory = "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE"
    Add-Type -Path "$smoAssemblyDirectory/Microsoft.SqlServer.SMO.dll"

    $server = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerName)
    if ($server.ServerType -eq $null) {
        throw "Unable to connect to server '$ServerName' "
    }

    Create-ServerObjectScripts -serverCollection $server.Endpoints -rootFolder "$scriptRootFolder" -typeName "Endpoints"
    Create-ServerObjectScripts -serverCollection $server.Triggers -rootFolder "$scriptRootFolder" -typeName "Triggers"

    Write-Host "Scripting completed"

}
catch {
    throw
}


Are C++ vectors in a user-defined aggregate function a POD for Postgres? The official postgres documentation (docs) states in section "37.10.11. Using C++ for Extensibility" that

If calling backend functions from C++ code, be sure that the C++ call stack contains only plain old data structures (POD). This is necessary because backend errors generate a distant longjmp() that does not properly unroll a C++ call stack with non-POD objects

So, are std::vectors< uint64_t > considered POD or not? I wish I could tell from reading the above instructions but I cannot. Can someone help? Thanks.

Yes, they are, because they do not appear in plain C.


More Details for MySQL's errorno:150 When MySQL can't create a table due to a foreign key constraint problem, it returns the following error

  Can't create table 'dbname.table_name' (errno: 150)

Is there anyway to have MySQL tell you (or to automatically lookup/analyze) why it couldn't create the table.  For example, if you attempt to create a table with 10 foreign keys, and one key is invalid, is there a way to have MySQL tell you which one is the problematic key?  Or do you need to rely on human intelligence and debugging to figure this out for yourself?

Unfortunately, I would have to go with

  rely on human intelligence and debugging to figure this out for yourself

Here is why:

Suppose you have the following table

CREATE TABLE projects (
    StaffID INT NOT NULL,
    ProjectName VARCHAR(20) NOT NULL,
    Allocation INT NOT NULL,
    PRIMARY KEY (StaffID, ProjectName) ,
    FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
);

MySQL would have to look for a table called Staff. If it finds the table Staff, MySQL has to find a column in that table called StaffID.

For a table with 10 foreign keys, this algorithm would be followed 10 times. Sadly, MySQL does not generate a error stating which table or column name caused the CREATE TABLE to fail. You could just remove one of the foreign keys and try it with 9, then 8, and so on.

Here is a strange case that require intelligence:

What if you did a mysqldump of InnoDB data from a MySQL Databases running in Windows and try to import it into Linux ?
What if you did a mysqldump of InnoDB data from a MySQL Databases running in Linux and try to import it into Windows ?

There is a case sensitivity issue. The foreign key references may fail due to this.

I have discussed this back in March 2014 : creating table result in errno 150


How Can I Monitor SQL service with Zabbix? We are implementing Zabbix, monitoring a lot of processes.

But I think it would be great to monitor the SQL service ( if it's running, stopped...)

We are now monitoring Ping, conections services and etc.

im new in this Area, But it's something I was asked to do.

You can monitor status of any Windows service with a Windows-specific item key (when using Zabbix agent) service.info.

See Windows-specific agent key documentation for more detail.


Storage efficiency of Postgres index column In my PostgreSQL database, I have this table:

    Table "public.data"
  Column   |  Type  | Modifiers
-----------+--------+-----------
  group_id | bigint |
      item | text   |
     price | real   |
Indexes:
    "group_id_index" btree (group_id)

item's are organized into groups, and the group_id column will have individual values appearing many times in the table. Queries to this table have the form:

SELECT item, price FROM data WHERE group_id = 1234;

group_id_index speeds up these queries (as is its purpose).

My question is, does the presence of the index make Postgres realize it does not have to store the group_id column behind-the-scenes? If only one copy (for the index) of each value of group_id is stored, that would save a lot of space.

This is, if it matters, PostgreSQL version 9.4.1

My question is, does the presence of the index make Postgres realize it does not have to store the group_id column behind-the-scenes?

No. PostgreSQL uses heaps, not index-organised tables.