Performance schema: 'stage/sql/Sending data' Background

I am using MariaDB 10.0 but MySQL 5.6 answers are also of interest.

Two machines cloned from the same image, same MariaDB configuration as far as I could tell, same dataset. One machine is way slower than the other (the production machine, of course).

I have enabled performance profiling to investigate (performance_schema=1 in /etc/my.cnf), restarted.

A query on development host:

SELECT
  event_name AS Stage,
  TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
FROM
  performance_schema.events_stages_history_long
WHERE
  NESTING_EVENT_ID=23;

Output:

+--------------------------------+----------+
| Stage                          | Duration |
+--------------------------------+----------+
| stage/sql/init                 | 0.000122 |
| stage/sql/checking permissions | 0.000005 |
| stage/sql/Opening tables       | 0.000610 |
| stage/sql/After opening tables | 0.000004 |
| stage/sql/System lock          | 0.000012 |
| stage/sql/Table lock           | 0.000002 |
| stage/sql/After opening tables | 0.000006 |
| stage/sql/init                 | 0.000042 |
| stage/sql/optimizing           | 0.000022 |
| stage/sql/statistics           | 0.000591 |
| stage/sql/preparing            | 0.000040 |
| stage/sql/executing            | 0.000003 |
| stage/sql/Sorting result       | 0.000003 |
| stage/sql/Sending data         | 0.345838 |
| stage/sql/end                  | 0.000006 |
| stage/sql/query end            | 0.000003 |
| stage/sql/closing tables       | 0.000012 |
| stage/sql/freeing items        | 0.000009 |
| stage/sql/cleaning up          | 0.000001 |
+--------------------------------+----------+
19 rows in set (0.00 sec)

Same query on production host:

SELECT
  event_name AS Stage,
  TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
FROM
  performance_schema.events_stages_history_long
WHERE
  NESTING_EVENT_ID=7;

Output:

+--------------------------------+-----------+
| Stage                          | Duration  |
+--------------------------------+-----------+
| stage/sql/init                 |  0.000124 |
| stage/sql/checking permissions |  0.000005 |
| stage/sql/Opening tables       |  0.000024 |
| stage/sql/After opening tables |  0.000006 |
| stage/sql/System lock          |  0.000005 |
| stage/sql/Table lock           |  0.000003 |
| stage/sql/After opening tables |  0.000007 |
| stage/sql/init                 |  0.000043 |
| stage/sql/optimizing           |  0.000029 |
| stage/sql/statistics           |  0.003686 |
| stage/sql/preparing            |  0.000061 |
| stage/sql/executing            |  0.000003 |
| stage/sql/Sorting result       |  0.000004 |
| stage/sql/Sending data         | 11.537281 |
| stage/sql/end                  |  0.000010 |
| stage/sql/query end            |  0.000003 |
| stage/sql/closing tables       |  0.000016 |
| stage/sql/freeing items        |  0.000012 |
| stage/sql/logging slow query   |  0.000007 |
| stage/sql/cleaning up          |  0.000002 |
+--------------------------------+-----------+
20 rows in set (0.00 sec)

Which is over 3000% slower. The data returned by the queries is the same in both cases, only 33 rows and four columns, about 300 bytes in all.

Questions

What is the stage/sql/Sending data metric actually measuring?
What could possibly be the reason for such a slow performance?

Sadly, stage/sql/Sending data (something that you will see on SHOW PROCESSLIST as Sending data) does not tell us much about the query internals.

  The thread is reading and processing rows for a SELECT statement...

Is like saying nothing, why is it different in both cases?

Your effort is not in vain, you have proved what is NOT affecting the query- permissions, optimization, opening tables, locking, logging and other support bits. There is something wrong with the query itself, likely either a different query plan or a configuration/hardware issue.

In order to continue answering, please provide the output of the following commands on both servers:

EXPLAIN SELECT ... [REST OF YOUR QUERY] \G

and

FLUSH STATUS;
SELECT ... [REST OF YOUR QUERY, do not show the actual results, we do not care];
SHOW STATUS like 'Hand%';

Those are 2 commands that will tell us more about query execution before checking other more complex possibilities. It is the most common cause so there is a high chance of getting something interesting out of it. Also please show us the table structure SHOW CREATE TABLE (in both servers) for reference.


Managing CTE execution order under uncertainty I am writing a large, multi-step CTE for performance reasons.

In one query, data must be moved from one table to another, but the quantity of rows moved is uncertain and could be zero.

In a subsequent table, the origin from the previous query is deleted but must be after the previous query is completed.

Finally, rows must be written in place of the deleted rows after the second query above is completed.

In the first two queries, I am using RETURNING to enforce execution order.

In the second query, I'm determining that the first query is completed by this subquery

(SELECT COUNT(*) FROM first_query) >= 0

In the the third query, I'm determining that the second query is completed by this subquery

SELECT EXISTS (SELECT 1 FROM second_query)

Is the subquery to determine that the first query has completed correct?

Is the subquery to determine that the second query, which must return rows, has completed optimal for accuracy, precision, and performance?

Using the above subqueries to enforce execution order is giving duplicate key value violations.

Query subsection

WITH copy_to_other_table AS (
    INSERT INTO other_table (column_a, column_b)
        SELECT column_a, column_b
            FROM main_table
        WHERE column_a = $1::bigint
        RETURNING *
),
main_table_deleted AS (
    DELETE FROM main_table WHERE column_a = $1::bigint
        AND (SELECT COUNT(*) FROM copy_to_other_table) >= 0
        RETURNING *
)
INSERT INTO main_table (column_a, column_b)
        SELECT column_a, column_b
            FROM another_table WHERE column_a = $1::bigint
             AND EXISTS (SELECT 1 FROM main_table_deleted)

It is the final query that is violating the unique constraint.

This should work but I'm not really sure if it's the best regarding efficiency:

WITH copy_to_other_table AS (
    INSERT INTO other_table (column_a, column_b)
        SELECT column_a, column_b
            FROM main_table
        WHERE column_a = 1
),
main_table_deleted AS (
    DELETE
    FROM main_table
    WHERE column_a = 1
      AND NOT EXISTS (SELECT 1 FROM another_table
                      WHERE column_a = 1
                        AND column_b = main_table.column_b)

)
INSERT INTO main_table (column_a, column_b)
        SELECT column_a, column_b
            FROM another_table WHERE column_a = 1
        EXCEPT
        SELECT column_a, column_b
            FROM main_table WHERE column_a = 1 ;

But what is wrong with the original query?

First, the (SELECT COUNT(*) ...) >= 0 is completely redundant. A count aggregate will always return a values of 0 or more so that condition is always true.
Second, there is no need at all to have any condition at all there, because all the rows from main that you want copied to the other table, you also want them tobe deleted from main. There is no reason to "check" if they have copied before you delete them . All 3 subqueries, (the 2 CTEs and the main query) will "see" the same tables with the same exact number of rows and data.
The third part is more tricky. It might seem at first glance, that no checking is needed either for the "interaction" between the 2nd (delete) and the 3rd (insert) part. Both are to the same table but if the 2nd cte is performed before the main query then all should be well.
Alas, the order of execution is not consecutive. From Postgres docs:

  Data-modifying statements in WITH are executed exactly once, and always to completion, independently of whether the primary query reads all (or indeed any) of their output. Notice that this is different from the rule for SELECT in WITH: as stated in the previous section, execution of a SELECT is carried only as far as the primary query demands its output.

  The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" each others' effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.

As a test, you could altering the order of the 3 sub-statements. The result will be the same:

WITH main_table_deleted AS (
    DELETE
    FROM main_table
    WHERE column_a = 1
      AND NOT EXISTS (SELECT 1 FROM another_table
                      WHERE column_a = 1
                        AND column_b = main_table.column_b)

),
 copy_to_other_table AS (
    INSERT INTO other_table (column_a, column_b)
        SELECT column_a, column_b
            FROM main_table
        WHERE column_a = 1
)
INSERT INTO main_table (column_a, column_b)
        SELECT column_a, column_b
            FROM another_table WHERE column_a = 1
        EXCEPT
        SELECT column_a, column_b
            FROM main_table WHERE column_a = 1 ;

The related issue is when the unique constraints are checked. I'm not 100% sure about the detail of those checks in combination with CTEs but unique constraints should be checked at the end of statements. It appears that they are also checked concurrently for each modifying cte.
(Note: This behaviour seems like a bug to be honest - unless I missed something in the documentation.)
Regarding your last question, setting isolation level to SERIALIZABLE would not have solved the issue as the whole operation is one statement, with 3 sub-statements. You could however, split the actions into 2 or 3 statements and then they would be executed one after the other. So, the 2nd would see the results of the 1st and the 3rd, the results of the first two. (If you do that, put the 2 or 3 statements inside a transaction, to isolate the operation from other executing statements.)

Another way - that is more close to your original query - would be to use the RETURNING clause to force the execution of the sub-statements in a specific order, i.e. the 3rd after the 2nd (The 1st can stay without RETURNING and executed concurently). Test in SQLFIddle-3:

WITH copy_to_other_table AS (
    INSERT INTO other_table (column_a, column_b)
        SELECT column_a, column_b
            FROM main_table
        WHERE column_a = 1
),
main_table_deleted AS (
    DELETE FROM main_table WHERE column_a = 1
        RETURNING *
)
INSERT INTO main_table (column_a, column_b)
        SELECT column_a, column_b
            FROM another_table WHERE column_a = 1
        EXCEPT
        (TABLE main_table_deleted EXCEPT TABLE another_table) ;

or slightly improved by doing the delete (2nd) cte first and then using its RETURNIING output in both the other two:

WITH main_table_deleted AS (
    DELETE FROM main_table WHERE column_a = 1
        RETURNING *
),
copy_to_other_table AS (
    INSERT INTO other_table (column_a, column_b)
        TABLE  main_table_deleted
)
INSERT INTO main_table (column_a, column_b)
        SELECT column_a, column_b
            FROM another_table WHERE column_a = 1
        EXCEPT
        (TABLE main_table_deleted EXCEPT TABLE another_table) ;


Replications fails with a vague message: "The last step to run was step 3: Detect nonlogged agent shutdown" In Transactional Replication of SQL Server, Synchronization job starts well and proceeds a while. However, in middle of the work it shows this error:

The job failed.  The Job was invoked by User WIN-XXXXX\Administrator.
The last step to run was step 3 (Detect nonlogged agent shutdown.).

It's a very generic error message. How can I get more details about the error?

I found the SQL job (under SQL Server Agent, Jobs folder at the left pane of SQL Server Management Studio) associated to Distribution Agent which is in charge of running Synchronization job. I edited the 2nd step of the job i.e. Run agent step (available in the Job Properties dialog that appears by double clicking the job or by right click on the job and selecting Properties).

In the Job Step Properties dialog, I added two parameters to get more log messages:
-OutputVerboseLevel 2 -Output F:\log_dir\snapshotagent.log

I re-run the Synchronization job and after seeing the error, the details were available in F:\log_dir\snapshotagent.log. I hope it is useful for you.


FillFactor: IndexOptimize script by Ola Hallengren We are using Ola Hallengren's IndexOptimize script for index rebuild on SQL Server 2008 and 2012. These maintenance scripts are very good and have worked for our DBs for months now. However, very recently I noticed that it changed the FillFactor to 90 after it executed index rebuild on the DB even when the FillFactor parameter was NULL.

Prior to executing the script, I checked sys.indexes had FillFactor of 0 for all indexes but I am not sure why the script changed FillFactor to 90 after it completed. This caused a lot of issues on the database.

Do you know if this is the intended behavior in IndexOptimize script where the FillFactor gets changed to 90?

Here is what I executed:

EXECUTE dbo.IndexOptimize
 @Databases = 'dbname',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @LogToTable = 'Y'

Ola's script will only change Fill Factor if you assign a value to the @FillFactor variable.

IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', FILLFACTOR = ' + CAST(@FillFactor AS nvarchar)

Which means someone:

Modified the stored procedure to use 90 as a default value;
Ran it out-of-band from maintenance; or
Changed the values using a different script or the GUI.

You can script out the stored procedure to see if someone changed the default value,

Or you can query the dbo.CommandLog table to see if a REBUILD command was run with a Fill Factor of 90.

SELECT *
FROM dbo.CommandLog AS cl
WHERE cl.CommandType = 'ALTER_INDEX'
AND cl.Command LIKE '%FILLFACTOR%'

If someone changed it by using a different script, or the GUI, that would be much more difficult to track down.


SQL Server - SQL Technical test its been a few years since i used SQL i mostly use R and sas now and i have an interview thats states sql understanding is required
The job role is for Risk Assessment Model Developer
The job role states -
•   The role holder will have the ability to leverage  analytics to support the development of models and interpretation of regulations, statutes, and conduct matters in business context and Intelligence, delivering complex information in a way that is simple and easy to understand.
Im comfortable with my skills when im pulling data from a formatted database or live system  but it seems on this test they want knowledge on taking unformatted data and showing indepth formatted MI as a result.
Formatting will be my downfall here if i cant get up to date as i had the easy life of using sas eg where the tools really helps me with my  informatting
Below is all i have been given as what to expect and prep for so any help would be appreciated
What to expect?
• The questions are technical and include database interrogation.
• The data is on separate lines which needs to be concatenated into different columns. The length and end aren’t the same.
• Look up or refresh - order by Rand() limit function

I don't fully follow all of your example questions, but knowing what the position is you're interviewing for may help in us giving you advice on what to study and how deep to go down the rabbit hole on certain topics. For example a Database Administrator role would certainly need to study more technical management things about databases such as backups, Transaction Log and growth, SQL security, maybe HA/DR features like Availability Groups, etc. Whereas a Data Analyst role would probably not need to know any of that stuff, but have a basic understanding of the Data Query Language (DQL) in SQL Server and general database structure. And a Database Developer would have some kind of skillset in between both roles, in addition to things neither role necessarily needs to know.
I'm going to assume since your existing skillset involves R, then you're probably applying to a position somewhere around Data Analyst. In that case, a quick review of W3Schools SQL Walkthrough is a good refresher on the basics of DQL and SQL in general. It also goes through some of the functions in SQL Server so that'll be good to know for some of the potential questions.
You should also review and make sure you understand the fundamentals of keys and referential integrity.
You should also make sure you thoroughly understand the different types of JOINS in SQL Server, which the previous W3Schools link will go through, but this webpage on SQL Server Joins also does a good breakdown specifically on them, with visual representations of each one. Understanding how to use JOINS in a relational structure are very important. The only type of JOIN that doesn't cover is the CROSS JOIN but you can read up on it here.
You should also understand CROSS APPLY and OUTER APPLY as well, which you can read up on in this article. Though they're not as common and probably slightly less important than understanding the previous JOIN types, so don't stress as much over them.
It sounds like one of the example questions involves transforming rows into columns which means you should understand how to use the PIVOT and UNPIVOT operators. (Quick tip, use PIVOT to turn row values into columns, and UNPIVOT to turn columns into row values.) Here's a good walkthrough with examples on using the PIVOT operator.
Understanding Window Functions is a very useful thing but also a little bit of an advanced topic depending on the position you're applying for. Particularly DENSE_RANK(), RANK(), and ROW_NUMBER() are good ones to know. More information and examples here.
If you update your post with more details such as the specific job role you're applying for, I'll update my answer with more relevant information accordingly.


How to perform a while loop on the query result I created a trigger AFTER  an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine.

CREATE TRIGGER `order_gereed` AFTER UPDATE ON `oc_order`
FOR EACH ROW IF new.order_status_id = "15"
THEN
UPDATE push_voorraad SET actie = '1';
END IF

But now I need to do some more SQL when new.order_status_id matches 15. I don't know how to do this in pure SQL, but I do now how to in php. So I hope with posting my php code, someone can tell me how to do this in SQL.

$query = "SELECT
            oc_order_product.quantity,
            oc_order_option.option_value_id,
            product.id
          FROM oc_order_product
            JOIN oc_order_option
              ON oc_order_option.order_id = oc_order_product.order_id
            JOIN product
              ON product.oc_product_id = oc_order_product.model
          WHERE oc_order_product.order_id = '25' AND oc_order_product.store_url = 'http://www.someurl.com/'";

$result = $mysqli->query($query);
while($row = $result->fetch_object()){
  $query = "UPDATE product_option_value SET quantity = quantity - {$row->quantity} WHERE product_id='{$row->id}' AND option_value_id='{$row->option_value_id}'";
  $mysqli->query($query);
}

25 in the query above should actually be new.order_id.

http://www.someurl.com/ in the query above should actually be new.store_url

Oke, I found my own answer after reading the internet en doing some trail and error.

I created a stored procedure which I will execute on the update trigger.

The trigger

CREATE TRIGGER `order_gereed` AFTER UPDATE ON `oc_order`
 FOR EACH ROW IF new.order_status_id = "15"
THEN
CALL VooraadUpdate(new.order_id, new.store_url);
UPDATE push_voorraad SET actie = '1';
END IF

The procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `VooraadUpdate`(IN `orderID` INT(11), IN `storeURL` VARCHAR(255))
    NO SQL
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE a, b, c INT;
    DECLARE cur1 CURSOR FOR SELECT
                              oc_order_product.quantity,
                              oc_order_option.option_value_id,
                              product.id
                            FROM oc_order_product
                              JOIN oc_order_option
                                ON oc_order_option.order_id = oc_order_product.order_id
                              JOIN product
                                ON product.oc_product_id = oc_order_product.model
                            WHERE oc_order_product.order_id = orderID AND oc_order_product.store_url = storeURL;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur1;
    read_loop: LOOP
      FETCH cur1 INTO a, b, c;
        IF done THEN
          LEAVE read_loop;
        END IF;

      UPDATE product_option_value SET quantity = quantity - a WHERE product_id=c AND option_value_id=b;

    END LOOP;

    CLOSE cur1;
END


Count bookings and cancellations per member (select in subquery) That will be my very first query using subquery ever but I already have problems.

Let's take a look at my tables:

user
   - id
   - name

user_visit
   - id
   - user_id
   - visit_type_id (BOOKED, CANCELED)
   - visit_starts_at
   - visit_ends_at

type_of_visit
   - id
   - type

And the result of it should be as follows:

['name' => 'John Doe', 'bookings' => 138, 'cancellations' => 30],
['name' => 'Jane Smith', 'bookings' => 47, 'cancellations' => 12],
['name' => 'Fleur V.', 'bookings' => 12, 'cancellations' => 1]

So it should count how many bookings and cancellations had user.

So far I have started with the following query:

SELECT
    u.name AS `name`,
    COUNT(booking.id) AS `bookings`
FROM `user` AS `u`,
      (SELECT
            *
        FROM `user_visit` AS `uv`
        INNER JOIN type_of_visit AS `tov`
            ON uv.visit_type_id = tov.id
        WHERE tov.type = 'BOOKED'
      ) AS `booking`
 GROUP BY u.id

Unfortunately it does not work properly because it was returning only one user, so something went wrong. Anyway you guys have more experience and you probably know how would you handle this, so anyone?

I hope that query is self-explaining enough.

SELECT u.name AS `name`
     , IFNULL( z.bookings, 0 ) AS bookings
     , IFNULL( q.cancellations, 0 ) AS cancellations
  FROM user AS u

  LEFT JOIN
     (
       SELECT uv.user_id AS user_id
            , COUNT(*) AS `bookings`
         FROM user_visit AS uv
        WHERE uv.type = 'BOOKED'
        GROUP BY uv.user_id
     )
     AS z
     ON z.user_id = u.id

  LEFT JOIN
     (
       SELECT uv.user_id AS user_id
            , COUNT(*) AS `cancellations`
         FROM user_visit AS uv
        WHERE uv.type = 'CANCELED'
        GROUP BY uv.user_id
     )
     AS q
     ON q.user_id = u.id
;


SSMS 2016 Dark Theme Does anyone know how to change the color of the right click menu in SSMS 2016 with the dark theme enabled?

The Dark Theme is still under active development and the work-around to enable it should be used for testing only. If you are testing it, then check Microsoft Connect for the issues you find, if they have not been raised feel free to raise the issue yourself. You should expect problems with it until Microsoft enable it by default and as such I would recommend not using it until that point.


Why does SQL Injection not happen on this query inside a stored procedure? I made the following stored procedure:

ALTER PROCEDURE usp_actorBirthdays (@nameString nvarchar(100), @actorgender nvarchar(100))
AS
SELECT ActorDOB, ActorName FROM tblActor
WHERE ActorName LIKE '%' + @nameString + '%'
AND ActorGender = @actorgender

Now, I tried doing something like this. Maybe I am doing this wrong, but I want to be sure that such a procedure can prevent any SQL Injection:

EXEC usp_actorBirthdays 'Tom', 'Male; DROP TABLE tblActor'

The image below shows the SQL above being executed in SSMS and results being displayed correctly instead of an error:

Btw, I added that part following the semicolon after the query was done executing. Then I executed it again, but when I checked to see if the table tblActor exists or not, it was still there. Am I doing something wrong? Or is this really injection-proof? I guess what I am trying to ask here also is that is a stored procedure like this safe? Thank you.

This code works properly because it is:

Parameterized, and
Not doing any Dynamic SQL

In order for SQL Injection to work, you have to build a query string (which you are not doing) and not translate single apostrophes (') into escaped-apostrophes ('') (those are escaped via the input parameters).

In your attempt to pass in a "compromised" value, the 'Male; DROP TABLE tblActor' string is just that, a plain-ol' string.

Now, if you were doing something along the lines of:

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT fields FROM table WHERE field23 = '
          + @InputParam;

EXEC(@SQL);

then that would be susceptible to SQL Injection because that query is not in the current, pre-parsed context; that query is just another string at the moment. So the value of @InputParam could be '2015-10-31'; SELECT * FROM PlainTextCreditCardInfo; and that might present a problem because that query would be rendered, and executed, as:

SELECT fields FROM table WHERE field23 = '2015-10-31'; SELECT * FROM PlainTextCreditCardInfo;

This is one (of several) major reason to use Stored Procedures: inherently more secure (well, as long as you don't circumvent that security by building queries like I showed above without validating the values of any parameters used). Though if you need to build Dynamic SQL, the preferred way is to parameterize that as well using sp_executesql:

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT fields FROM table WHERE field23 = @SomeDate_tmp';

EXEC sp_executesql
  @SQL,
  N'SomeDate_tmp DATETIME',
  @SomeDate_tmp = @InputParam;

Using this approach, someone attempting to pass in '2015-10-31'; SELECT * FROM PlainTextCreditCardInfo; for a DATETIME input parameter would get an error when executing the Stored Procedure. Or even if the Stored Procedure accepted @InputParameter as NVARCHAR(100), it would have to convert to a DATETIME in order to pass into that sp_executesql call. And even if the parameter in the Dynamic SQL is a string type, coming into the Stored Procedure in the first place any single apostrophe would automatically get escaped to a double apostrophe.

There is a lesser known type of attack in which the attacker tries to fill up the input field with apostrophes such that a string inside of the Stored Procedure that would be used to construct the Dynamic SQL but which is declared too small can't fit everything and pushes out the ending apostrophe and somehow ends up with the correct number of apostrophes so as to no longer be "escaped" within the string. This is called SQL Truncation and was talked about in an MSDN magazine article titled "New SQL Truncation Attacks And How To Avoid Them", by Bala Neerumalla, but the article is no longer online. The issue containing this article — the November, 2006 edition of MSDN Magazine — is only available as a Windows Help file (in .chm format). If you download it, it might not open due to default security settings. If this happens, then right-click on the MSDNMagazineNovember2006en-us.chm file and select "Properties". In one of those tabs there will be an option for "Trust this type of file" (or something like that) which needs to be checked / enabled. Click the "OK" button and then try opening the .chm file again.

Another variation of the Truncation attack is, assuming a local variable is used to store the "safe" user-supplied value as it had any single-quotes doubled so as to be escaped, to fill up that local variable and place the single-quote at the end. The idea here is that if the local variable is not properly sized, there won't be enough room at the end for the second single-quote, leave the variable ending with a single single-quote that then combines with the single-quote that ends the literal value in the Dynamic SQL, turning that ending single-quote into an embedded escaped single-quote, and the string literal in the Dynamic SQL then ends with the next single-quote that was intended to begin the next string literal. For example:

-- Parameters:
DECLARE @UserID      INT = 37,
        @NewPassword NVARCHAR(15) = N'Any Value ....''',
        @OldPassword NVARCHAR(15) = N';Injected SQL--';

-- Stored Proc:
DECLARE @SQL NVARCHAR(MAX),
        @NewPassword_fixed NVARCHAR(15) = REPLACE(@NewPassword, N'''', N''''''),
        @OldPassword_fixed NVARCHAR(15) = REPLACE(@OldPassword, N'''', N'''''');

SELECT @NewPassword AS [@NewPassword],
       REPLACE(@NewPassword, N'''', N'''''') AS [REPLACE output],
       @NewPassword_fixed AS [@NewPassword_fixed];
/*
@NewPassword          REPLACE output          @NewPassword_fixed
Any Value ....'       Any Value ....''        Any Value ....'
*/

SELECT @OldPassword AS [@OldPassword],
       REPLACE(@OldPassword, N'''', N'''''') AS [REPLACE output],
       @OldPassword_fixed AS [@OldPassword_fixed];
/*
@OldPassword          REPLACE output          @OldPassword_fixed
;Injected SQL--       ;Injected SQL--         ;Injected SQL--
*/

SET @SQL = N'UPDATE dbo.TableName SET [Password] = N'''
           + @NewPassword_fixed + N''' WHERE [TableNameID] = '
           + CONVERT(NVARCHAR(10), @UserID) + N' AND [Password] = N'''
           + @OldPassword_fixed + N''';';

SELECT @SQL AS [Injected];

Here, the Dynamic SQL to be executed is now:

UPDATE dbo.TableName SET [Password] = N'Any Value ....'' WHERE [TableNameID] = 37 AND [Password] = N';Injected SQL--';

That same Dynamic SQL, in a more readable format, is:

UPDATE dbo.TableName SET [Password] = N'Any Value ....'' WHERE [TableNameID] = 37 AND [Password] = N';

Injected SQL--';

Fixing this is easy. Just do one of the following:

DON'T USE DYNAMIC SQL UNLESS ABSOLUTELY NECESSARY! (I'm listing this first because it really should be the first thing to consider).
Properly size the local variable (i.e. should be twice the size as the input parameter, just in case all characters passed-in are single-quotes.
Don't use a local variable to store the "fixed" value; just put the REPLACE() directly into the creation of the Dynamic SQL:

SET @SQL = N'UPDATE dbo.TableName SET [Password] = N'''
           + REPLACE(@NewPassword, N'''', N'''''') + N''' WHERE [TableNameID] = '
           + CONVERT(NVARCHAR(10), @UserID) + N' AND [Password] = N'''
           + REPLACE(@OldPassword, N'''', N'''''') + N''';';

SELECT @SQL AS [No SQL Injection here];

The Dynamic SQL is no longer compromised:

UPDATE dbo.TableName SET [Password] = N'Any Value ....''' WHERE [TableNameID] = 37 AND [Password] = N';Injected SQL--';

Notes about the Trunction example above:

Yes, this is a very contrived example. There is not much one can do in only 15 characters to inject. Sure, maybe a DELETE tableName to be destructive, but less likely to add a back-door user, or change an admin password.
This type of attack probably requires knowledge of the code, table names, etc. Less likely to be done by random stranger / script-kiddie, but I did work at a place that was attacked by a rather upset former employee who knew of a vulnerability in one particular web page that nobody else was aware of. Meaning, sometimes attackers do have intimate knowledge of the system.
Sure, resetting everyone's password is likely to be investigated, which might tip the company off that there is an attack happening, but it might still provide enough time to inject a back-door user or maybe get some secondary info to use/exploit later.
Even if this scenario is mostly academic (i.e. not likely to happen in the real world), it's still not impossible.

For more detailed information related to SQL Injection (covering various RDBMS's and scenarios), please see the following from the Open Web Application Security Project (OWASP):
Testing for SQL Injection

Related Stack Overflow answer on SQL Injection and SQL Truncation:
How safe is T-SQL after you replace the ' escape character?


The UPDATE permission was denied on the object I'm connecting to a remote Microsoft server running MsSQL via PHP.  The code looks something like:

$con = mssql_connect('0.0.0.0:123', 'user', 'pass')
    or die(log_mssql_error(__FILE__, __LINE__, mssql_get_last_message()));
mssql_select_db('ShipWorks', $con)
    or die(log_mssql_error(__FILE__, __LINE__, mssql_get_last_message()));

mssql_query($query = "UPDATE [Foo].[dbo].[Bar] SET Biz = 'ABC' WHERE Baz = 'XYZ'")
    or die(log_mssql_error(__FILE__, __LINE__, mssql_get_last_message(), $query));

I receive the following error

  The UPDATE permission was denied on the object 'Bar', database 'Foo', schema 'dbo'.

In the object explorer, in 'Server' > Databases > 'Foo' > Security > Users > 'user' I've set GRANT on all the securables.
And in 'Server' > Databases > 'Foo' properties, I'ved added the 'user' user and given all GRANT permissions as well.

I feel like I've covered all the bases but I'm still getting the permission denied error. What did I miss?

I've set GRANT on all the securables.

You want to assign roles to write data for a specific database. When I read this correct you clicked everthing to do this.

Now I think that your user is member of the role db_datawriter and db_denydatawriter. Please remove the role db_denydatawriter and try it again.


Trigger function to update column I'm a newbie to PL/pgSQL ... I use Postgres 9.5.0, and need to update a column every time a new record is inserted. The column shall be filled in from the values entered in area_pol and area_ofi.

I'm trying to create this function to suit my case:

CREATE OR REPLACE FUNCTION sch_cap.fc_atualiza_dif_area()
  RETURNS trigger AS
$$
BEGIN
    UPDATE
        sch_cap.tbl_cap
    SET
        dif_area = abs(100 - (tbl_cap.area_pol / (tbl_cap.area_ofi * 100)));
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER tg_atualiza_dif_area
  BEFORE INSERT OR UPDATE ON sch_cap.tbl_cap
  FOR EACH ROW EXECUTE PROCEDURE sch_cap.fc_atualiza_dif_area();

But when I try to insert a record, the following error appears:

  ERROR: stack depth limit exceeded
  HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

How to get it right?

This isn't how a trigger works. You should fire the TRIGGER before the update, then you just use SET on the NEW record.

SET new.dif_area = abs(100 - (OLD.area_pol / (OLD.area_ofi * 100)));

But even better, just drop this entirely and use a VIEW.

CREATE VIEW sch_cap.tbl_cap AS
  SELECT *, abs(100 - (area_pol / (area_ofi * 100))) AS dif_area
  FROM tbl_cap;


Need advice for table design for multi-user access I have an inventory application that needs to support multi-user. As of right now, only one user can access and manage their items. I've gotten a lot of requests to support multiple users so they can all manage the same inventory.

I have a table called user_items that stores item information. To keep it simple, I'll include just the relevant column names.

mysql> select primary_item_id, user_id, item_name from users_item limit 1;
+-----------------+---------+-----------------+
| primary_item_id | user_id | item_name       |
+-----------------+---------+-----------------+
|             100 |       4 | Stereo Receiver |
|             101 |       5 | Couch           |
+-----------------+---------+-----------------+

I've created a mapping table to map the items to users.

+-------------+-------------+----------------+------------+-----------+
| map_user_id | map_item_id | unique_item_id | item_owner | privilege |
+-------------+-------------+----------------+------------+-----------+
|           4 |       100   |              1 |          1 |      NULL |
|          13 |       100   |              1 |          1 |      NULL |
|           5 |       101   |              1 |          5 |      NULL |
+-------------+-------------+----------------+------------+-----------+

The unique_item_id column is the item_id that's displayed to the users. So item #1 for user #4 is "Stereo Receiver." Item #1 for user #5 would be a couch. The item_owner field doesn't mean much for the time being. I'm not sure if I need it but it's there for now as I play with the schema and code.

Anyways, this works fine, except I need multiple users to track the same item(s). Instead of offering the opportunity to share items AND track their own items, my version of "multi-user" means they have to track the same exact number of items. If user #13 adds a new item, user #4 also has access to said item.

Any suggestions? I think I shot myself in the foot by offering unique ids for each item. It is what it is so now I have to work with what I have.


Which is more efficient, trigger or PHP code? My requirement is to move EMR data for a patient to a set of history tables when the patient has been discharged. I proposed to use an after trigger on the table housing the discharge flag, checking new\old values on the flag to ensure I only take action when the flag is initially set to discharged. The Sr. app developer writing the PHP side says that doing this in PHP is at least 4 times faster than the trigger. To me this doesn't make any sense but my background is in SQL Server, not MySQL so I am looking to the community for help.

Additional info:
Table housing the discharge flag is heavily used even after the patient has been discharged. There are currently 7 narrow tables (total of about 70 columns) that would have data inserted by trigger.

I wonder where "at least 4 times faster" came from?  There are too many variables at play to even come up with numbers like that, and in any event, the performance difference (however slight) should be in the other direction.

The presence of a trigger on a table does come with a performance penalty, but if your trigger is intelligently-written, the word "negligible" should apply.  A well-behaved trigger does only what it needs to do and then gets out of the way, and in the case of triggers that test multiple conditions, the tests are nested so that as few logical comparisons are done as possible for each run of the trigger.

Some developers seem to resist triggers on the principle that they're "black magic" -- "Hey, something changed in the database, and the application didn't change it. That's bad."  Granted, the presence of triggers is something developers and DBAs alike have to keep in mind when troubleshooting, and that's especially true if they are uncommon in your environment.  If the structure of one of the tables modified by the triggers were to change -- say, a renamed column -- then updates to the discharge flag suddenly start throwing errors about columns that weren't even referenced in the update query, someone who doesn't have the possibility of "there might be triggers involved" at least in the back of their minds may be completely flummoxed.  I've been on that side of it, too, and I was the one who originally wrote -- then forgot about-- the trigger in question.  That's one of those lessons you learn the hard way, hopefully just once.

The issue you bring up is, I would say, as much as matter of principle as performance: as one of the answers to this question points out, the database should be in charge of looking after the database... not the application.  The application shouldn't be in charge of referential integrity, so why should it be in charge of operations which -- like this one, from what I can tell -- should be atomic?

If, by definition, when the discharge flag is set, the EMR data should thenceforth be in the history tables, then I would argue on principle that the setting of the flag should be what puts the data there.  That requires either a trigger... or a call to a stored stored procedure that both sets the discharge flag and migrates the records before returning.

I'm imagining that no matter how you end up migrating the records, the actual queries executed to do the migrating would be the same... so in truth it's hard to imagine the difference in performance being substantial, since the execution time for the queries that move the records is not going to be different, whether the queries are run from a trigger, run from a stored procedure, or run separately from the application... so, almost comically, the most significant factor I'd expect to find would likely be small: the round trip time between the system running PHP and the system running MySQL, multiplied by the number of separate queries that need to run, will likely be the determining factor in just how much faster a trigger (or stored procedure)-based implementation would be... and on a LAN with sub-millisecond RTT... not much.


Do you set an index on JOIN clauses or where clauses, or both? Say my query looks like:

SELECT t1, t2
FROM t1
  LEFT JOIN t2 ON (t1.id = t2.id AND t2.userid = @userid)
WHERE t1.enabled = 1 AND
      t1.startDate <= ??? AND
      (t1.counter = -1 OR
       t2.counter IS NULL OR
       (t1.counter > t2.counter)

Now this table might have a few hundred thousand rows in it.

Would you suggest I put an index on the JOIN clause only like this?

  t2.id t2.userid

What about the where clause?  Or is the join clause more important?

I realize testing is important, but in theory what should be done?

(This is for SQL Server 2000)

I would recommend to always put a non-clustered index on the columns that will be used in JOIN conditions - the foreign key columns. This helps in several ways - JOIN operations will be faster, and enforcing the FK constraint (checking whether there's a child row attached when attempting to delete the parent row) will also benefit from those indices.

Then check to see how your system performs. If it performs below your expectations - carefully add one index and see if the overall system performance improves. If not: remove the index again. Repeat over until you're happy with the performance. Columns used in WHERE or ORDER BY clauses are the prime candidates for those indices - but don't over-index! That's even worse than having no indices at all.

See Kimberly The Queen of Indexing Tripp's excellent blog post - Indexes: just because can, doesn't mean you should! on that very topic.


Good, Bad or Indifferent: WHERE 1=1 Given this question on reddit, I cleaned up the query to point out where the issue was in the query. I use comma first and WHERE 1=1 to make modifying queries easier, so my queries generally end up like this:

SELECT
     C.CompanyName
    ,O.ShippedDate
    ,OD.UnitPrice
    ,P.ProductName
FROM
               Customers       as C
    INNER JOIN Orders          as O  ON C.CustomerID = O.CustomerID
    INNER JOIN [Order Details] as OD ON O.OrderID    = OD.OrderID
    INNER JOIN Products        as P  ON P.ProductID  = OD.ProductID
Where 1=1
--  AND O.ShippedDate Between '4/1/2008' And '4/30/2008'
    And P.productname = 'TOFU'
Order By C.CompanyName

Someone basically said that 1=1 is generally lazy and bad for performance.

Given that I don't want to "prematurely optimize" - I do want to follow good practices. I've looked at the query plans before, but generally only to find out what indexes I can add (or adjust) to make my queries run faster.

The question then really... does Where 1=1 cause bad things to happen? And if so, how can I tell?

Minor Edit: I've always 'assumed' as well that 1=1 would be optimized out, or at worst be negligible. Never hurts to question a mantra, like "Goto's are Evil" or "Premature Optimization..." or other assumed facts. Wasn't sure if 1=1 AND would realistically affect query plans or not. What about in subqueries? CTE's? Procedures?

I'm not one to optimize, unless needed... but if I'm doing something that is actually "bad", I'd like to minimize the effects or change where applicable.

The SQL Server parser optimizer has a feature called "Constant Folding" that eliminates tautological expressions from the query.
If you look at the execution plan, nowhere in the predicates you will see that expression appear. This implies that constant folding is performed anyway at compile time for this and other reasons and it has no effect on query performance.

See Constant Folding and Expression Evaluation During Cardinality Estimation for more info.


Is it possible to rewrite this Function for optimization? I need help optimizing this function as it takes more than 2 minutes currently which is far from optimal. This function basically gives you the name of column, corresponding table and list of unique values contained in the column.

Function Definition:

CREATE OR REPLACE FUNCTION get_pubcol(
    included_tables name[] default '{}',
    included_schema name[] default '{}'
)
RETURNS table(schema_name text, table_name text, column_name text, column_value text)
AS $$
begin
  FOR schema_name, table_name, column_name IN
      SELECT c.table_schema, c.table_name, c.column_name
      FROM information_schema.columns c
      JOIN information_schema.tables t ON
        (t.table_name = c.table_name AND t.table_schema=c.table_schema)
      WHERE (c.table_name=ANY(included_tables) OR included_tables='{}')
        AND (c.table_schema=ANY(included_schema) OR included_schema='{}')
        AND t.table_type='BASE TABLE'
  LOOP
    EXECUTE format('SELECT ARRAY_AGG(DISTINCT %I) AS column_value FROM %I.%I',
       column_name,
       schema_name,
       table_name
    ) INTO column_value;
    IF column_value is not null THEN
      RETURN NEXT;
    END IF;
 END LOOP;
END;
$$ language plpgsql;

Function call:

SELECT * FROM get_pubcol('{}', '{public}')

Explain Analyze Result:

"Function Scan on get_pubcol  (cost=0.25..10.25 rows=1000 width=128) (actual time=121235.178..121235.199 rows=73 loops=1)"
"Planning time: 0.049 ms"
"Execution time: 121237.436 ms"

Query Result:

schema_name table_name column_name   column_value
"public"    "MARA"  "Lab/Office"    "{" ",BL1,GL1,GP1,KL1,KP1,NaN,SL1,SP1,WL1,WL2,WP1}"

You are running one query for each column and table. One way to optimize this, is to only run a single query for each table that retrieves the distinct values for all columns in one go. This result can be put into a JSONB which in turn can be returned as individual rows using jsonb_each:

CREATE OR REPLACE FUNCTION get_pubcol(
    included_tables name[] default '{}',
    included_schema name[] default '{}'
)
RETURNS table(schema_name text, table_name text, column_name text, column_value text)
AS
$$
declare
  l_value jsonb;
  l_sql   text;
begin
  FOR schema_name, table_name, l_sql IN
    SELECT c.table_schema, c.table_name,
           concat('select to_jsonb(t) from ( select ',
                  string_agg(format('array_agg(distinct %I) as %I', c.column_name, c.column_name), ', '),
                  format(' from %I.%I) as t', c.table_schema, c.table_name)) as sql
    FROM information_schema.columns c
      JOIN information_schema.tables t
        ON t.table_name = c.table_name AND t.table_schema=c.table_schema
    WHERE t.table_type = 'BASE TABLE'
      AND (c.table_name = ANY(included_tables) OR included_tables='{}')
      AND (c.table_schema = ANY(included_schema) OR included_schema='{}')
    group by c.table_schema, c.table_name
  LOOP
    EXECUTE l_sql into l_value;
    IF l_value is not null THEN
      return query
        select schema_name, table_name, x.*
        from jsonb_each_text(l_value) as x(column_name, column_value);
    END IF;
 END LOOP;
END;
$$ language plpgsql;

The outer query assembles a single query for each table. E.g. if you have a table person with the columns id, firstname, lastname the expression in the query for the loop would generate something like:

select to_jsonb(t)
from (
   select array_agg(distinct lastname) as lastname, array_agg(distinct id) as id, array_agg(distinct firstname) as firstname
from public.person
) as t

That query returns a single jsonb value that contains the information for all columns. This is stored in the local variable l_value, something like:

{
    "id": [42,43,44,45],
    "lastname": ["Beeblebrox","Dent","McMillan","Prefect"],
    "firstname": ["Arthur","Ford","Tricia","Zaphod"]
}

This is then turned back into rows using jsonb_each_text.

The display will be different though.

Your example

{BL1,GL1,GP1,KL1,KP1,NaN,SL1,SP1,WL1,WL2,WP1}

will be displayed as

["BL1","GL1","GP1","KL1","KP1","NaN","SL1","SP1","WL1","WL2","WP1"]


Limiting sp_whoisactive output All,
I am currently putting together a knowledge based article that we can hand to our helpdesk to run through in order to do some preliminary troubleshooting or information gathering. One of the things I did was create the ability (thanks to Brent, https://www.brentozar.com/askbrent) to provide non-sysadmin's the ability to run sp_whoisactive to output some details they could provide to dbas like myself in the ticket. The problem I am running into is I want to exclude "sql_text" from results, but I cannot seem to figure out how?
I understand you have the ability to filter the output when it goes to a table (@output_column_list param) but I am not having the agents export it to a table. Initially, I was under the impression that the "@output_column_list" was where I could adjust the columns I wanted to see in the results, but it is clearly only a parameter used to filter the output to a table. In my desperate attempt, I tried the above parameter anyways and would leave out "sql_text" from the output, but all that did was reposition the column order.
EXEC sp_WhoIsActive @output_column_list = '[dd%][session_id][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]'

Therefore, I want them to be able to copy the contents (with headers) to an excel sheet to attach. The less complicated work, the better as I fear if I provide the help desk with too much info it will confuse them.
Am I missing something? They will not have the ability to run any jobs or even look at logs, so their rights are already VERY limited.

There's an error in your command. If you remove the [%] at the end, you'll get the correct output.
Current:
EXEC sp_WhoIsActive @output_column_list = '[dd%][session_id][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]'
Should be:
EXEC sp_WhoIsActive @output_column_list = '[dd%][session_id][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks]'
As a side note: if you want to get the query plan, and associated locks (since you've got them in your column list), you'll need to use the @get_plans and @get_locks parameters in combination with the output list.


What is the pgAgent service account? Im installing pgAgent using Stack Builder.

In one step ask me for service account. Should I use PC, Domain or DB user? Or create a new one?

I try using my DOMAIN\my.user.name  also using the postgres user and return same error.

I dont have the PC_Name\Administrator password

  User creation failed. The password specified is incorrect or length of the user name is longer than supported

You should specify

System username

System password

e.g. username and password one of the windows account


How to fix mysql uppercase query script in php I am currently working on the website that uses ADODB library. In entire website all the queries are written in UPPERCASE.
The problem is when I run the query it doesn't work because of table name which is UPPERCASE. But when I change the table name to lowercase it works.
$sql = "SELECT * FROM MEMBERS where USERNAME = '$username'";

$db = ADONewConnection('mysql');
$db->debug = true;
$db->Connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME);

$resultFriends = $db->Execute($sql);

while ($row = $resultFriends->FetchRow()) {
        var_dump($row);
        die;
}

Here is the error I get:

ADOConnection._Execute(SELECT * FROM MEMBERS where USERNAME = 'fury', false) % line 1012, file: adodb.inc.php
ADOConnection.Execute(SELECT * FROM MEMBERS where USERNAME = 'fury') % line   15, file: index.php

I have read some articles about it but that doesn't help. e.g.
How to make MySQL table name case insensitive in Ubuntu?
Bear in mind I don't want to change the scripts. There are 1000 files and 10000 places.
Is there any library or are there any way that I can run this queries without error?

The version for live sire was linux kernel. but the new dev site is ubuntu.

I have done this on ubuntu/ mysql CML and it didn't work.

The solution is I had to reconfigure the mySql database in AWS/rdbs

You have to modify the “lower_case_table_names” parameter for your DB Instance(s). Prior to today, the lower_case_table_names parameter was not modifiable, with a system default of zero (0) or “table names stored as specified and comparisons are case sensitive.” Beginning immediately, values of zero and one (table names are stored in lowercase and comparisons are not case sensitive) are allowed. See the MySQL documentation for more information on the lower_case_table_names parameter.

The lower_case_table_names parameter can be specified via the rds-modify-db-parameter-group API. Simply include the parameter name and specify the desired value, such as in the following example:

  rds-modify-db-parameter-group example --parameters "name=lower_case_table_names, value=1, method=pending-reboot" --region us-east-1

Support for modifying parameters via the AWS Management Console is expected to be added later this year.

setting the lower_case_table_names parameter via a custom DB Parameter Group and doing so before creating an associated DB Instance. Changing the parameter for existing DB Instances could cause inconsistencies with point-in-time recovery backups and with Read Replicas.

Amazon RDS


Transaction in a stored procedure I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest this transaction in a stored procedure I run into lots of syntax errors.

How can I encapsulate the following code so it can be easily called?

BEGIN TRANSACTION AssignUserToTicket
GO

DECLARE @updateAuthor varchar(100)
DECLARE @assignedUser varchar(100)
DECLARE @ticketID bigint

SET @updateAuthor = 'user1'
SET @assignedUser = 'user2'
SET @ticketID = 123456

    UPDATE tblTicket SET ticketAssignedUserSamAccountName = @assignedUser WHERE (ticketID = @ticketID);
    INSERT INTO [dbo].[tblTicketUpdate]
           ([ticketID]
           ,[updateDetail]
           ,[updateDateTime]
           ,[userSamAccountName]
           ,[activity])
     VALUES
           (@ticketID,
           'Assigned ticket to ' + @assignedUser,
           GetDate(),
           @updateAuthor,
           'Assign');
GO
COMMIT TRANSACTION AssignUserToTicket

You need to wrap that code in CREATE PROCEDURE ... syntax, and remove the GO statements after BEGIN TRANSACTION and before COMMIT TRANSACTION.

GO
CREATE PROCEDURE dbo.AssignUserToTicket
(
     @updateAuthor varchar(100)
    , @assignedUser varchar(100)
    , @ticketID bigint
)
AS
BEGIN
    BEGIN TRANSACTION;
    SAVE TRANSACTION MySavePoint;
    SET @updateAuthor = 'user1';
    SET @assignedUser = 'user2';
    SET @ticketID = 123456;

    BEGIN TRY
        UPDATE dbo.tblTicket
        SET ticketAssignedUserSamAccountName = @assignedUser
        WHERE (ticketID = @ticketID);

        INSERT INTO [dbo].[tblTicketUpdate]
            (
            [ticketID]
            ,[updateDetail]
            ,[updateDateTime]
            ,[userSamAccountName]
            ,[activity]
            )
        VALUES (
            @ticketID
            , 'Assigned ticket to ' + @assignedUser
            , GetDate()
            , @updateAuthor
            , 'Assign'
            );
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION MySavePoint; -- rollback to MySavePoint
        END
    END CATCH
END;
GO

Also note, I have added a TRY...CATCH statement block to allow performing a ROLLBACK TRANSACTION statement in case some error occurs.  You probably need better error handling than that, but without knowledge of your requirements, that is difficult at best.

Some good reading:

Always specify the schema
Stored Procedure Best Practices
Bad habits to avoid


When inserting utf8 strings, an invalid byte combination will cause truncation.

You have two different functions; I assume one blindly left a D7 at then end, leading to truncation; and the other stripped it off?


Can't start mongodb in ArchLinux I have installed mongodb using sudo pacman  -S mongodb  in my 32-bit arch .
At first I run this sudo mkdir -p /data/db to create /data/db   ,and then  run $ mongod  ,then I got the following message :

mongod --help for help and startup options
Sat Oct  6 11:45:29
Sat Oct  6 11:4
5:29 warning: 32-bit servers don't have journaling enabled by default. Please use --           journal if you want durability.
Sat Oct  6 11:45:29
Sat Oct  6 11:45:29 [initandlisten] MongoDB starting : pid=1146 port=27017    dbpath=/data/db/ 32-bit host=ftp
Sat Oct  6 11:45:29 [initandlisten]
Sat Oct  6 11:45:29 [initandlisten] ** NOTE: when using MongoDB 32 bit, you are limited to about 2 gigabytes of data
Sat Oct  6 11:45:29 [initandlisten] **       see    http://blog.mongodb.org/post/137788967/32-bit-limitations
Sat Oct  6 11:45:29 [initandlisten] **       with --journal, the limit is lower
Sat Oct  6 11:45:29 [initandlisten]
Sat Oct  6 11:45:29 [initandlisten] db version v2.2.0, pdfile version 4.5
Sat Oct  6 11:45:29 [initandlisten] git version: nogitversion
Sat Oct  6 11:45:29 [initandlisten] build info: Linux brynhild 3.5.3-1-ARCH #1 SMP   PREEMPT Sun Aug 26 09:14:51 CEST 2012 i686 BOOST_LIB_VERSION=1_49
Sat Oct  6 11:45:29 [initandlisten] options: {}
Sat Oct  6 11:45:29 [initandlisten] exception in initAndListen: 10309 Unable to   create/open lock file: /data/db/mongod.lock errno:13 Permission denied Is a mongod instance  already running?, terminating
Sat Oct  6 11:45:29 dbexit:
Sat Oct  6 11:45:29 [initandlisten] shutdown: going to close listening sockets...
Sat Oct  6 11:45:29 [initandlisten] shutdown: going to flush diaglog...
Sat Oct  6 11:45:29 [initandlisten] shutdown: going to close sockets...
Sat Oct  6 11:45:29 [initandlisten] shutdown: waiting for fs preallocator...
Sat Oct  6 11:45:29 [initandlisten] shutdown: closing all files...
Sat Oct  6 11:45:29 [initandlisten] closeAllFiles() finished
Sat Oct  6 11:45:29 [initandlisten] shutdown: removing fs lock...
Sat Oct  6 11:45:29 [initandlisten] couldn't remove fs lock errno:9 Bad file descriptor
Sat Oct  6 11:45:29 dbexit: really exiting now

what does exception in initAndListen: 10309 Unable to create/open lock file: /data/db/mongod.lock errno:13 Permission denied Is a mongod instance already running?, terminating mean?

You ran sudo mkdir -p /data/db as not yourself.  Now that directory is owned by root.

You need to chown the directory back to yourself before starting mongod otherwise mongod cannot access the directory write the files into it that are needed.

Please also find a 64-bit platform to run mongo - it's not really a very good idea to run it on 32-bit system unless this is just for playing around with.


mysql assign different partition for specific DB I am using mysql version 5.5.54-0ubuntu0.12.04.1 (Ubuntu) with few DB & one of the DB is growing very rapidly and eating up disk space.

I have mounted secondary disk & would like this specific DB only to use this new added disk.

can someone please provide some suggestions how it can be done? can I move single DB to new disk or I have to move all DBs / mysql to new ? how?

You should try this:
How can I move a MySQL database to another drive?

  Shutdown mysql.
  Move all the files in your current data directory to
  the new location (check out the location in step 3 - datadir
  parameter).
  Locate my.ini file (it is in the mysql installation
  directory). Change datadir parameter value to point to the new
  location.
  Start mysql.


Add database-level collation to PostgreSQL So I have added a en_US collation to my system:

# locale -a
C
en_US.utf8
POSIX
sk_SK.utf8

Based on Postgres' documentation I have added it to my Postgres instance:

postgres=# select * from pg_collation;
  collname  | collnamespace | collowner | collencoding | collcollate | collctype
------------+---------------+-----------+--------------+-------------+------------
 default    |            11 |        10 |           -1 |             |
 C          |            11 |        10 |           -1 | C           | C
 POSIX      |            11 |        10 |           -1 | POSIX       | POSIX
 sk_SK      |            11 |        10 |            6 | sk_SK.utf8  | sk_SK.utf8
 sk_SK.utf8 |            11 |        10 |            6 | sk_SK.utf8  | sk_SK.utf8
 ucs_basic  |            11 |        10 |            6 | C           | C
 en_US      |          2200 |        10 |            6 | en_US.utf8  | en_US.utf8

As you can see the 'collnamespace' of newly added collation is different than the others. My assumption is that this is why I don't have a choice of  'en_US' while creating new database.

Question is: what I need to do to change collnamespace '2200' to '11' or straightly say to create a database with 'en_US' collation? For database creation I'm using pgAdmin tool.

pg_collation is a system table inside pg_catalog system catalog schema. It lists all available collations: https://www.postgresql.org/docs/9.5/static/catalog-pg-collation.html

SELECT * FROM pg_collation;
SELECT * FROM pg_collation WHERE collname='en_US';

pg_collation table belongs to pg_catalog schema. To list all tables in the pg_catalog schema, use

\dS+

One of the columns of pg_collation table is 'collnamespace'. The information of collnamespace can be obtained from pg_namespace table. https://www.postgresql.org/docs/9.5/static/catalog-pg-namespace.html

pg_namespace table contains a hidden column 'oid'. oid is called object identifier used internally to identify postgres objects. Namespace is also known as Schema in postgres. Essentially, the oid number seen in pg_namespace table will be the same oid seen in collnamespace column of pg_collation table for any particular row (Eg: 'en_US).

postgres=# select * from pg_namespace;
      nspname       | nspowner |               nspacl
--------------------+----------+-------------------------------------
 pg_toast           |       10 |
 pg_temp_1          |       10 |
 pg_toast_temp_1    |       10 |
 pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
 public             |       10 | {postgres=UC/postgres,=UC/postgres}
 information_schema |       10 | {postgres=UC/postgres,=U/postgres}
(6 rows)

postgres=# select oid,nspname,nspowner,nspacl from pg_namespace ;
  oid  |      nspname       | nspowner |               nspacl
-------+--------------------+----------+-------------------------------------
    99 | pg_toast           |       10 |
 11320 | pg_temp_1          |       10 |
 11321 | pg_toast_temp_1    |       10 |
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
  2200 | public             |       10 | {postgres=UC/postgres,=UC/postgres}
 12453 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
(6 rows)

postgres=# select * from pg_collation where collname='en_US';
 collname | collnamespace | collowner | collencoding |   collcollate   |    collctype
----------+---------------+-----------+--------------+-----------------+-----------------
 en_US    |            11 |        10 |            6 | en_US.utf8      | en_US.utf8
 en_US    |            11 |        10 |            8 | en_US           | en_US
 en_US    |            11 |        10 |           16 | en_US.iso885915 | en_US.iso885915
(3 rows)

You see the number 11 in oid column of pg_namespace table matches with collnamespace column of pg_collation. This is because, pg_collation table belongs to pg_catalog namespace or schema.

Now, in your case, pg_collation table belongs to public schema (2200) instead of pg_catalog schema(11). I do not know how this happened to you. But, it is safe to say that Schema is below database level in the postgres architecture hierarchy, so you should not have any issue with creating a new database with required collation.

When you initialize a database cluster using initdb command, the default encoding is set during this time and it is applied to template1 default database. Template1 database is used to create new database by default and it will not allow changes to encoding, because it has been already set by initdb during cluster initialization. To create a database with a new encoding, you would have to use template0 database, which will allow you to specify the different collation and the relevant encoding while creating a new database as follows.

postgres=# CREATE DATABASE mydb ENCODING 'LATIN1' LC_COLLATE 'en_US' LC_CTYPE 'en_US' TEMPLATE template0;
CREATE DATABASE
postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 mydb      | postgres | LATIN1   | en_US       | en_US       |                       | 6681 kB | pg_default |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7100 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 6681 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 6804 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(4 rows)

Notice the size of mydb is same as template0, because template0 is used to create mydb with LATIN1 encoding and 'en_US' collation.

To see the character encoding mapping to internal number encoding (ranging from 0 to 41), use

mydb=# select pg_encoding_to_char(8);
 pg_encoding_to_char
---------------------
 LATIN1
(1 row)

Hope this helps.


Recovering MySQL data from files in windows I had a server fail (disk failure, old server; no raid) at the weekend and am unable to recover the MySQL databases from the backup.

After exhausting all the usual recovery options, I've performed a disk recovery and have managed to retrieve the contents of the MySQL data folder located at c:\programdata\MySQL Server 5.5\data.

The folders corresponding to each of the databases are there and are populated with .frm and .myi/.myd files... these seem quite small in size.... between 10 and 20k each. In the root of the data folder there are various files...

ib_logfile0
ib_logfile1
ib_logfile0_000
ib_logfile1_000
ibdata1 ibdata1_000
WIN-8KT3MJ6T2PS.err
WIN-8KT3MJ6T2PS.pid
WIN-8KT3MJ6T2PS_000.err
WIN-8KT3MJ6T2PS_000.pid

So far I have tried installing a new clean server with 2008 R2 (the original host OS) and MySQL 5.5, then stopping the service and replacing the contents of the data directory with that of my recovered data, but when I try this, the MySQL service will no longer start, telling me The description for Event ID 100 from source MySQL cannot be found....

Is there any other way in which I can recover the databases from these files?

Install the same version of MySQL you had before.  Reload my.cnf (my.ini) if possible.  Reload all the files in that tree.  Start mysqld.  It might work.

If it does not work, look at the error file; there may be clues.  A common one has to do with the size of iblog; this is 'easily' fixed.


Order to restore system and user databases We are going to move an sql instance to a new physical server (SQL SERVER 2008 r2). The answer to this question explains how Agent jobs and ssis-packages will be moved by restoring the master and msdb-databases to the new server.

My question is if the order of restoring system vs user databases has anything to say?
Is it OK to restore master and msdb before user databases, even though this will restore agent jobs and ssis-packages that point to user databases not yet restored/created?

Thanks for any help.

br
Gert

my understanding is that the order is not important... as long as the agent is not running until all db's are restored that have jobs interacting with them.

Yes, there is a dependency, but that dependency only exists upon execution of the jobs.

I would be interested in feedback subsequent to the restore of the master db.

I assume also that you are moving an existing (SQL SERVER 2008 r2) instance...


Why does WAL on postgresql takes so long to replay? Context:

I'm running postgresql 11 using the official postgresql docker image. The configuration is the default one without any tweak. Sometimes, my docker server crashes and kills all the container with it. As a result the postgresql gets stopped in a dirty way and postgresql has to replay the WALs. It takes more than 1h to replay the logs thought the server is mostly idling. I mean when the server crashed it hardly had any writes in the logs. And when I check in pg_wal  I only see 65mb of data in the directory so it shouldn't take so much time to replay.

Here's some configurations:

max_wal_size: 1gb
min_wal_size: 80gb
checkpoint_timeout: 3min

So how come it take so long to restart? I have a different server with much less databases but similar configurations. Both are mostly idle all the time but the other one quickly restart while they have the same configuration.

Docker shouldn't be crashing all the time.  Maybe whatever is making it do that is also making it very slow when it isn't crashed.  Look in the log files for the OS and for docker (and for PostgreSQL) to see what they say.

Find the pid of the startup process, and do:

strace -y -ttt -T -p <pid>

And see what it is doing.  This will show you, for example, if it is getting stalled on reads or writes or other system calls.  If it isn't, you can also use gdb -p <pid> to see what it is doing internally.  To get the most out of that, you will have to install the debug symbols, which I don't know how to do on the official docker image.


AWS RDS responds with (Got an error reading communication packets). Works locally I've been on this issue for a couple of days now, I will cover things I've tried at the end. So I'm running a NodeJS server from a GitHub repo using netlify-lamda. I am connecting to an Amazon RDS (MySQL). So the strangest thing is this works locally however when I bring it over to Netlify I get an error.

I'm sending the HTTP requests using Postman.

My db.js file:

var mysql = require("mysql");
var db;
require("dotenv").config();

function connectDatabase(){
    if(!db){
        db = mysql.createPool({
            connectionLimit: process.env.CONN_LIMIT,
            host: process.env.DB_HOST,
            user: process.env.DB_USER,
            password: process.env.DB_PASS,
            database: process.env.DB_NAME,
            port: process.env.DB_PORT
        });
    }
    return db;
}

module.exports = connectDatabase();

To query the DB I do the following:

router.post('/login',(req,res) => {
    var username = req.headers.username;
    var password = req.headers.password;
    var sql = `SELECT password FROM admin WHERE username= ?`;
    db.query(sql,[username],function(err,results,query){
        if(err){
            res.status(401);
            res.send("Unauthorized-sql err"); // This is where the error occurs.
        }
        else if(!err){
            if(crypt.decrypt(results[0].password).match(password)){
                res.status(200);
                res.send("Authorized");
            }else{
                res.status(401);
                res.send("Unauthorized");
            }
        }
    });
});

RDS Error Log:

2020-04-03T14:11:31.874911Z 22 [Note] Aborted connection 22 to db: 'db' user: 'admin' host: 'xxx.xxx.xx.xx' (Got an error reading communication packets)
2020-04-03T14:11:31.877847Z 29 [Note] Aborted connection 29 to db: 'db' user: 'admin' host: 'xxx.xxx.xx.xx' (Got an error reading communication packets)
----------------------- END OF LOG ----------------------

NodeJS Error Log

6:17:18 PM: 2020-04-03T17:17:18.120Z    1d561b89-f8a1-4e78-9d7c-61dac84ea37f    INFO    Error: connect ECONNREFUSED 127.0.0.1:3306    at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1137:16)

at Protocol._enqueue (/var/task/admin.js:29255:48)
at Protocol.handshake (/var/task/admin.js:29162:23)
at PoolConnection.connect (/var/task/admin.js:9041:18)
at Pool.getConnection (/var/task/admin.js:27212:16)
at Pool.query (/var/task/admin.js:27366:8)
at /var/task/admin.js:27556:6
at Layer.handle [as handle_request] (/var/task/admin.js:7030:5)
at next (/var/task/admin.js:6641:13)
at Route.dispatch (/var/task/admin.js:6616:3)
at Layer.handle [as handle_request] (/var/task/admin.js:7030:5)
{
errno: 'ECONNREFUSED',
code: 'ECONNREFUSED',
syscall: 'connect',
address: '127.0.0.1',
port: 3306,
fatal: true
}

Things I've tried:

Changing the PORT.
Within the DB instance I'm allowing all connections from any IP address.
Setting a different PORT for the NodeJS instance.
I've now switched to using a MySQL pooling technique, the error is still there.
Preventing the minification/encoding of the code (for the DB instance in particular as that's an issue with the mysql npm package)

Sidenote: It's my first time working with AWS RDS so it's highly likely that the error is something very very stupid.

I encountered this in our QA environment. I spent a great deal of time enabling AWS RDS Audit plugin, improving performance and setting logs everywhere.
The way to ressolve it was to do a proper handling of the JDBC connections from the Java client app.
Please refer to the following question that relates to what you are asking here.
RDS has a lot of messages got an error reading communication packets


Delete rows from report based upon specific field value I have found how to hide rows based upon a specific field value.
But is it possible to actually delete the rows from the report and not just hide them?
Steve

There is no concept of DELETE for a report itself, specifically SSRS, because a report is meant to be read-only. If by DELETE you mean you don't want to bring those rows into your report's dataset at all, then you need to filter the dataset appropriately to eliminate the rows upstream.
For example if your dataset is a SQL query, you can add a predicate in the WHERE clause of that query to filter out the data (or if it's a stored procedure you could do the same via a parameter). We'd need to see the query that populates your SSRS report's dataset.

If instead, you're asking how to have a "delete button" in an SSRS report, so a user can choose which rows to delete from your database, then that's a little more complicated.
It would require you creating a stored procedure that deletes from the underlying tables appropriately and takes in parameter(s) of the key(s) for the rows being deleted. Then you'd make that a hidden parameter of your report, and would have to add a column to your report with a link that has an expression that references the report itself but with the additional hidden parameter. Then it would re-run the same report but passing in the key of the row that the link was clicked on to the stored procedure, so it knew which row to delete from your database, before returning the rest of the rows needed for the report.


Get total number of rows when offset is greater than max I have a table users that has, say, 42 records. To get the total rows and some other details, I use a window function:

select count(*) over() as count, name, id from users

And since the consumer app is paginated, I can utilise limit and offset to get the desired data:

select count(*) over() as count, name, id from users limit 5 offset 20

This gives me a (sample) result:

 count |  name   | id
-------+---------+----
    42 | Dummy05 | 27
    42 | Dummy06 | 30
    42 | Dummy07 | 31
    42 | Dummy08 | 32
    42 | Dummy09 | 33
(5 rows)

Of course, this can be optimised as described in this answer.

However when the offset value overshoots, I get 0 rows which is correct.

select count(*) over() as count, name, id from users limit 5 offset 50

Is there a way that gives me the total count despite the offset returning 0 rows? Something on the similar lines:

 count |  name   | id
-------+---------+----
    42 |         |
(0 rows)

You can adapt something like this:

select c.count, u.*
from
  (
    select count(*) as count
    from users
  ) as c
  left join
  (
    select name, id
    from users
    order by id
    limit 5 offset 50
  ) as u
  on true
order by u.id
 ;


Safe to delete old data after pg_upgrade? I am not a DBA but I am responsible for a modest pg installation. Recently as part of a larger program of system upgrades I upgraded from 9.5 to 10. Everything went great, except that our monitoring system is now regularly sending warnings about disk space. I see that there is still a big chunk of space taken up by /var/lib/postgresql/9.5/ -- is there any good reason not to delete all that? What if any additional steps should I take to verify that it's safe to delete the contents of that directory?

They generally compress well.  I'd just tar and compress it and remove the original, and your monitoring system will probably be happy. (And anything trying to use it should start throwing errors once it is no longer there, and then you can fix them). But you should have a bigger-picture data retention policy, and that should govern how long you keep the compressed copy.

At some point enough activity has happened in v10, that if a major problem surfaced you would just have to fix it rather than rolling back to v9.5 and losing the changes made in v10.  At that point, the old copy is pretty useless except for PITR purposes.


Firebird: table and null data I'm working with Firebird database. I have a big table with many thousand of rows. Each line records the status of an object at regular intervals for many objects. I need to register new properties for one object only.
I think I have 2 possibilities:

add the new columns to the table even if I have a lot of null values
create a table with the only properties that interest me and bind it appropriately to the first

which solution is the best for not having a loss of performance?

Adding a new column to a table will not modify existing data. Records in the database are versioned with a so-called format version at the time the record was written. This format version describes the columns (and other column related metadata).

If you alter a table to add a column, then a new format version is added. When reading records, Firebird will read the row using the original format version, and then - in memory - 'upgrade' the row to the latest format version, eg adding new columns with a null value (or the default value), removing columns that have been dropped, or changing sizes, etc.

In other words, adding columns is cheap. There is one exception: since Firebird 3, adding a column with a NOT NULL-constraint if there are existing records will require a default value, otherwise the addition fails. In earlier versions this would silently add a NOT NULL column containing NULL for existing records, leaving fixing of the data for those rows to the database owner.

Regarding storage itself, Firebird uses a simple form of RLE compression to compress records on disk. Null values written using the new format version do take up some space because of this, but max 2 bytes for most types, and multiples of 2 bytes for column types longer than 127 bytes. As an extreme form of optimization, it might make sense to group columns that are usually NULL together (but the performance benefit is likely very small).

Regarding runtime performance, since Firebird 3 (and assuming a client that supports wire protocol 13 or higher), columns that are NULL are skipped in the wire protocol (meaning that no data is sent for that column), which reduce the number of bytes sent over the network.


How to convert MySQL master-slave to master-master I've searched around, but can't find the exact details to point me in the proper direction.  I currently have 2 MySQL instances under 5.5.41 running as a master-slave setup.  I followed these directions to set it up.  Since both of these nodes are on the same VM storage I'd like to migrate one to a different datacenter as a master-master config then use JDBC load balancing where the current master is primary and the slave is converted to a secondary master with replication enabled between both.  Can anyone provide the steps I'd need to perform on the MySQL nodes?  I believe I now the correct JDBC line to use for my app connection.

Master-Master has a lot of problems if you are writing to both. It is better to write to only one of them until you need to failover.

Setting up Dual-Master is simply setting up both servers as being both Master and Slave, much like you already did to make your current Master-Slave topology.


Can't connect to MySQL DB I've been trying to install Apache because I want to do PHP development work.

The install didn't work so I aborted it and wanted to make sure that my other installs using MySQL were still working.

I can no longer seem to connect to my SQL server though.

HeidiSQL returns:

SQL Error (2003): Can't connect to MySQL server on '127.0.0.1' (10061)

Ruby on rails on Server start up returns:

Can't connect to MySQL server on 'localhost' (10061)

MySQL Command Line Client returns:

Error 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

I've tried the suggestions found in this thread:
https://stackoverflow.com/questions/385292/mysql-cant-connect-to-mysql-server-on-localhost-10061

and have come with the following results:

telnet localhost 3306
Connecting to localhost... Could not open connection to host, on port 3306: Connect failed

sc query mysql
[SC] EnumQueryServiceStatus:OpenService Failed 1060:
The specified service does not exist as an installed service.

Yet when I look in SQL Server Configuration manager, it says SQL Server (SQLExpress) is in a running State.

I can't seem to troubleshoot this issue. Please help!

I suspect you are running MySQL on Windows, although you did not mention it. If so, look in your Services and makes sure the MySQL service is started. Here is a link explaining what to do at dev.mysql.com.

If you are trying to connect to SQLExpress, the process is the same as above to make sure the service is running. Then, here is a link explaining PHP SQLExpress connectivity using PDO.


How to avoid a "duplicate key" error? This statement:

INSERT INTO deleteme
SELECT #t.id, 'test' FROM #t
LEFT JOIN  deleteme  ON deleteme.id = #t.id
WHERE deleteme.id IS NULL;

...will fail with a primary key violation in the concurrent scenario (i.e. when inserting the same key that is absent in deleteme from several threads at the same time) with the default transaction isolation level of READ COMMITTED:

  Error: Violation of PRIMARY KEY constraint 'PK_DeleteMe'. Cannot insert duplicate key in object 'dbo.deleteme'.

What is the best way to prevent that?

The table looks something like this:

CREATE TABLE [dbo].[DeleteMe](
    [id] [uniqueidentifier] NOT NULL,
    [Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe] PRIMARY KEY ([id] ASC));

Update

From comments:

  Why do you have multiple sessions, which can obviously pull the same id from somewhere, using the same permanent table without having some kind of session key to tell them apart? Even without a duplicate error, how are you going to know which rows belong to which session?

This is a stored procedure that is called by an external service that populates data in this table. The service generates the record id and it provides no guarantee that it won't send the same data twice. Or send the same data at the same time.

The database code is supposed to discard all the records with the same id, if one already exists. The service never sends two records with the same id in the same batch.

If you really need to run multiple threads at the same time you can enable the ignore_dup_key option on the primary key.

This will just give a warning instead of an error when an insert would result in a duplicate key violation. But instead of failing it will discard the row(s) that if inserted would cause the uniqueness violation.

CREATE TABLE [dbo].[DeleteMe](
[id] [uniqueidentifier] NOT NULL,
[Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe]
PRIMARY KEY ([id] ASC)
WITH (IGNORE_DUP_KEY = ON));

Example on sqlfiddle

Paul White's detailed explanation on the IGNORE_DUP_KEY option. Thanks Paul.


Execute `sp_spaceused` for all tables in all databases How to execute sp_spaceused for all tables in all databases?

i.e. how to execute sp_spaceused inside sp_msforeachdb.

e.g:

 sp_msforeachdb 'sp_msforeachtable'sp_spaceused"?"''

Use dynamic SQL with a cursor (for these cases it is ok)

Here is the code:

DECLARE @DBName varchar(200)
DECLARE @CMD varchar(max)
DECLARE @TV TABLE ( name_table varchar(500),ROWSCOUNT int, reserveder varchar(50), datasize varchar(50), indexsize varchar(50), unused varchar(50))
DECLARE @TV2 TABLE ( DBName varchar(200),name_table varchar(500),ROWSCOUNT int, reserveder varchar(50), datasize varchar(50), indexsize varchar(50), unused varchar(50))
DECLARE @TV3 TABLE ( DBName varchar(200),SchemaTAble varchar(500), TableName varchar(200))
DECLARE C Cursor for select quotename(name) as dbname from sys.databases where name <> 'tempdb'  and state = 0

OPEN C

FETCH NEXT FROM C INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    DELETE FROM @TV3
    SET @CMD =
    @DBName+'..sp_msforeachtable ''exec sp_spaceused [?]'''
    insert into @TV
    EXEC (@CMD)

    SET @CMD = 'select '''+@DBName+''' as DatabaseName, quotename(s.name)+''.''+quotename(t.name) as SchemaTable, quotename(t.name) TableName from '+@DBName+'.sys.tables t inner join '+@DBName+'.sys.schemas s on s.schema_id = t.schema_id '
    insert into @TV3
    EXEC (@CMD)

    insert into @TV2
    select @DBName ,t.name_table,t.ROWSCOUNT,t.reserveder,t.datasize,t.indexsize,t.unused from @TV t
    inner join @TV3 s on quotename(t.name_table) = s.TableName and s.DBName = @DBName

    FETCH NEXT FROM C INTO @DBNAME
END
CLOSE C
DEALLOCate C

select * from @TV2

There is a difference in usage for sp_spaceused in SQL Server 2016 and SQL Server 2012. Note that this code will only work for SQL Server 2012.


How full is my innodb shared table space? How can I find out how much data is in the innodb shared table space.

I have a massive shared idb file but I suspect that it contains only about 350 Gigs of data. I need to know how full it is so I can prevent it from expanding as I have very little disk space left. Any ideas ?

It's in ibdata1?  How much free space do you currently have?  What is the output of

 SHOW TABLE STATUS;

And

 SHOW CREATE TABLE  -- for the largest table

Is this 'your' machine?  Or a Cloud server?  Do you have access to another machine with a big disk?

Knowing how "full it is" won't necessarily help you avoid running out of disk space.

With answers to the above questions, we may be able to plot a path that does not end in disaster.


Postgres shared_buffers and pooled processes I have read several articles (including Postgres docs) that suggest setting the shared_buffer's config value to 25% of your total physical memory.

Using my 4 GB memory as an example... If I understand correctly, when postgres starts up, it will request ~1 GB of physical memory from the OS. It will use this for buffering purposes. Thus, when I look at a utility like "top" I see the size/RES of the postgres process to be ~1GB.

So my question is - what happens when I have POOLED connections to Postgres? i.e. A Tomcat server which keeps N number of open, idle connections to postgres? I can see there are N number of postgress processes waiting in the background for requests from Tomcat. Each process is the same size (~1Gb). They're forks of the original process, I suppose. So does that mean Postgres as a whole is taking up N*1GB of memory?? Isn't this a bad thing to make shared_buffer this large??

No, it's still only 1GB of total memory.

The buffer is allocated as shared memory and it only allocated once, but shared between all server processes.

It's basically an "error" in the memory display of the top command, which simply reports the shared memory (that only exists once) for each process.


SQL Server Memory Shrinking I had a strange issue today on a 3rd party vendor's SQL Server. This is a VM running SQL Server 2012 SP1. The min and max memory settings are set to 9GB. Task manager showed SQL Server was getting the 9GB. SQL Sentry shows over time the buffer pool was getting smaller until eventually we were not able to connect with SSMS. PLE was in the single digits.

The CPU was at 40% for a couple of days prior and I singled it down to Resource Monitor. This appears to be a symptom of the memory issue.

The vendor restarted the service and everything is back to normal.

I found this KB2845380 and think this might be related but I'm not sure. Has anyone run into something like this before? From what I've been told this has happened every month or so.

I ended up applying the hotfix for KB2845380 and the issue appears to be resolved.


Foreign Keys with Composite Key Imagine I have a table with the following dependency:

  (Name, BoughtFrom, TimeBought) --> Cost

Where the three attributes in bold form a composite primary key for the table. Now then, I want to link (form a relationship) from another table to this one. How do I do that? If I had an ID column I'd know what to do, but I've never come across a scenario like this.

Would I have to add all three columns (Name, BoughtFrom, TimeBought) to the other table? Or is there another way?

Yes, you'd add all three columns. Assuming they have the same names in both tables, you'd use something like

foreign key (Name, BoughtFrom, TimeBought)
  references the_other_table_name (Name, BoughtFrom, TimeBought)

If you decide to use a surrogate ID number, you'll still need a unique constraint on {Name, BoughtFrom, TimeBought}.  You can do that with something along these lines.

create table your_table (
  your_id_number integer primary key,
  Name ... ,
  BoughtFrom ... ,
  TimeBought ... ,
  unique (Name, BoughtFrom, TimeBought)
);

The surrogate key doesn't have to be an ID number. It doesn't have to be any kind of number. But an automatically incrementing ID number is the most common.

Surrogate means takes the place of. A surrogate key, like an ID number, takes the place of a natural key. Kind of like a surrogate mother takes the place of a natural mother.


SQL query to get the user, counting the minimum number of records I have an table with some records and a column LAST_EDIT_USER.
If I want to select all users with the number of appearances for every user, I would do

SELECT   LAST_EDIT_USER,COUNT(LAST_EDIT_USER)
FROM     EDITED_ELEMENTS_TABLE
GROUP BY LAST_EDIT_USER
ORDER BY COUNT(LAST_EDIT_USER) ASC;

and this will return something like

NULL   0
user1  5
admin  7
USER2  11

How can I get from a SQL filter the user1 (with the minimum of occurrences) - excepting NULL?
LATER EDIT: or a list of the users with minimum number of occurrences

Add a WHERE clause to filter the rows where last_edit_user is null. Then put another query around it, to get the minimum count. Use this as a subquery in the HAVING clause to get only the ones with the count equal to the minimum.

SELECT last_edit_user,
       count(last_edit_user)
       FROM edited_elements_table
       WHERE last_edit_user IS NOT NULL
       GROUP BY last_edit_user
       HAVING count(last_edit_user) = (SELECT min(c)
                                              FROM (SELECT count(last_edit_user) c
                                                           FROM edited_elements_table
                                                           WHERE last_edit_user IS NOT NULL
                                                           GROUP BY last_edit_user) x);


Group by repeating values Table:

select
    MainStationID,
    CONCAT(SUBSTRING(SubStationID,1,2),' STN - ',MainStationID) as Station,
    count(SubStationID) as [COUNT],
    CAST(EndDateTime-StartDateTime AS TIME(0)) AS Duration
from
    VHASM2.LineStoppageTrans
where
    SubStationID like 'RH%'
group by
    MainStationID,
    SubStationID,
    CAST(EndDateTime-StartDateTime AS TIME(0))
order by
    MainStationID

Output:-

How to get output like this, please help

Mainstation   Station     count   Duration [total duration]
   1          RH STN - 1    2     00:01:09
   2          RH STN - 2    3     00:00:13

DECLARE @t TABLE (MainStationID int, SubStationID varchar(3), StartDateTime datetime, EndDateTime datetime);
INSERT INTO @t VALUES
(1, 'RH1', '20181219 17:24:08.440', '20181219 17:25:04.523'),
(1, 'RH1', '20181219 17:25:52.283', '20181219 17:26:05.280'),
(2, 'RH2', '20181219 18:36:51.530', '20181219 18:36:57.107'),
(2, 'RH2', '20181219 18:37:03.080', '20181219 18:37:05.090');

;WITH ct AS
(
    SELECT
        MainStationID,
        CONCAT(SUBSTRING(SubStationID, 1, 2), ' STN - ', MainStationID) as Station,
        COUNT(*) as [Count],
        SUM(DATEDIFF(SECOND, StartDateTime, EndDateTime)) as Duration
    FROM
        @t
    WHERE
        SubStationID LIKE 'RH%'
    GROUP BY
        MainStationID,
        SubStationID
)
SELECT
    MainStationID,
    Station,
    [Count],
    CONCAT(RIGHT('0' + CAST(Duration / 3600 AS varchar(3)), 2), ':',
           RIGHT('0' + CAST((Duration / 60) % 60 AS varchar(3)), 2), ':',
           RIGHT('0' + CAST(Duration % 60 AS varchar(3)), 2)) AS Duration
FROM
    ct
ORDER BY
    MainStationID,
    Station;

| MainStationID | Station    | Count | Duration |
|---------------|------------|-------|----------|
| 1             | RH STN - 1 | 2     | 00:01:09 |
| 2             | RH STN - 2 | 2     | 00:00:08 |


AlwaysOn Read Only Routing Configuration for Different Listeners I have 5 sql servers. 3 of them are working sync and 2 are async. Load Balance Read-Only Routing is a great feature on SQL Server 2016. But if I add async servers into the load balancing configuration it becomes unreliable because of data loss.

I want to add a listener for only queries that data loss is unimportant for a short time. Like reporting queries. How can I configure the sql server load balance routing configuration for different listeners?

But if I add async servers into the load balancing configuration it becomes unreliable because of data loss.

There are two different things going on here that I believe are being confused. The first is dealing with data that has not yet been sent or received by the replica, the second is the data being available to be read by transactions because it has been redone and following the rules of snapshot isolation.

Synchronous commit does not guarantee that the data is immediately available, both sync and async must go through redo - and while sync commit does acknowledge when the data has been hardened on the remote replica, it does not mean the data is immediately available to be read - it still needs to go through redo and follow snapshot isolation requirements.

Really, data loss comes into play during a forced failover event - which should be very infrequent.

  I want to add a listener for only queries that data loss is unimportant for a short time. Like reporting queries. How can I configure the SQL Server load balance routing configuration for different listeners?

Read only routing is configurable per replica per availability group, it doesn't matter if there is one listener or three listeners.

To do this, you're going to want to use a hardware load balancer (or DNS round robin, etc.) to create these specific requirements


SQL from/where or join? I was wondering performance wise what would be faster a join or a from and where query.
I am trying merge and update query but performance is terrible?

If you are asking whether it is more efficient to use SQL 92 join syntax

SELECT empno, ename, dname
  FROM emp
       JOIN dept USING (deptno)

or to use the older join syntax

SELECT empno, ename, dname
  FROM emp  e,
       dept d
 WHERE e.deptno = d.deptno

the answer is that it doesn't matter.  The optimizer is automatically going to do an internal transformation from the SQL 92 syntax to the older syntax anyway.

If you have performance problems, it is highly unlikely that they are related to your choice of join syntax (though when Oracle first added support for SQL 92 join syntax, there were bugs relating to its ability to transform the SQL into the older style).