GROUP BY two different columns I store hits. My table looks like this:

    ID |   time   |   Country
--------------------------------
    1  | 01:00:00 |    France
    2  | 01:00:00 |    France
    3  | 01:00:00 |    Brazil
    4  | 01:00:00 |    USA
    5  | 02:00:00 |    USA

This is my query:

SELECT COUNT(*) as total_hits, HOUR(time) as hour, Country
FROM hits
WHERE time >= CURDATE()
GROUP BY HOUR(time)

It does count how many hits I got and groups it by hours:

    time   |   total_hits
---------------------------
  01:00:00 |    4
  02:00:00 |    1

I want one result grouped by time column and another grouped by countries column.
This is what I need:

1) Grouped by hours like this (same like above)

    time   |   total_hits
---------------------------
  01:00:00 |    4
  02:00:00 |    1

2) AND grouped by countries like this

  country  |   total_hits
---------------------------
 France    |    2
  USA      |    2
 Brazil    |    1

I could do:

SELECT
       COUNT(*)
     , HOUR(time)
     , COUNT(IF( Country = 'France', Country, null)) AS France
     , COUNT(IF( Country = 'USA', Country, null)) AS USA
     , COUNT(IF( Country = 'Brazil', Country, null)) AS Brazil
FROM hits
WHERE time >= CURDATE()
GROUP BY HOUR(time)

Or alternatively with CASE or SUM(Country = 'France') AS France.

But in the country column there are more than just 3 countries. If I would do this with every country my query would be very long.

I could do this:

SELECT COUNT(*), Country, HOUR(time)
FROM hits
WHERE time >= CURDATE()
GROUP BY Country, HOUR(time)

But the ouput will be something like this:

  time     | country   |   total_hits
---------------------------------------
  01:00:00 |  France   |    2
  01:00:00 |  USA      |    1
  01:00:00 |  Brazil   |    1
  02:00:00 |  USA      |    1

However I need an output like mentioned above.

So basically I need these queries in one query:

1) Group by HOUR (same like mentioned above)

SELECT COUNT(*) as total_hits, HOUR(time) as hour, Country
FROM hits
WHERE time >= CURDATE()
GROUP BY HOUR(time)

2) AND Group by Country

SELECT COUNT(*) as total_hits
FROM hits
WHERE time >= CURDATE()
GROUP BY Country

Performance is important. The database has millions of entries.
Maybe MySQL is not the best way for this problem?

I don't think I can help you with the formatting, but the query you need is this one:

PROPOSED QUERY

SELECT
    IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Country,Hour WITH ROLLUP;

YOUR SAMPLE DATA

USE test
DROP TABLE IF EXISTS hits;
CREATE TABLE hits
(
    id int not null auto_increment,
    time datetime,
    country varchar(20),
    PRIMARY KEY (id)
);
INSERT INTO hits (time,Country) VALUES
('2014-06-19 01:00:00','France'),
('2014-06-19 01:00:00','Brazil'),
('2014-06-19 01:00:00','USA'),
('2014-06-19 02:00:00','USA');

YOUR SAMPLE DATA LOADED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS hits;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE hits
    -> (
    ->     id int not null auto_increment,
    ->     time datetime,
    ->     country varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO hits (time,Country) VALUES
    -> ('2014-06-19 01:00:00','France'),
    -> ('2014-06-19 01:00:00','Brazil'),
    -> ('2014-06-19 01:00:00','USA'),
    -> ('2014-06-19 02:00:00','USA');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM hits;
+----+---------------------+---------+
| id | time                | country |
+----+---------------------+---------+
|  1 | 2014-06-19 01:00:00 | France  |
|  2 | 2014-06-19 01:00:00 | Brazil  |
|  3 | 2014-06-19 01:00:00 | USA     |
|  4 | 2014-06-19 02:00:00 | USA     |
+----+---------------------+---------+
4 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->     IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    ->     COUNT(1) TotalHits
    -> FROM
    -> (
    ->     SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    ->     FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
    -> ) AA
    -> GROUP BY Country,Hour WITH ROLLUP;
+-------------------------+-----------+
| Statistic               | TotalHits |
+-------------------------+-----------+
| 2014-06-19 01:00:00     |         1 |
| Total for Brazil        |         1 |
| 2014-06-19 01:00:00     |         1 |
| Total for France        |         1 |
| 2014-06-19 01:00:00     |         1 |
| 2014-06-19 02:00:00     |         1 |
| Total for USA           |         2 |
| Total for All Countries |         4 |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql>

I'll leave the formatting to you :-)

CAVEAT: Please make sure you have a time index on the table

ALTER TABLE hits ADD INDEX time_index (time);

An additional query could be

SELECT
    IFNULL(Country,CONCAT('Total for ',IFNULL(Hour,DATE(NOW())))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Hour,Country WITH ROLLUP;

whose output would be

+-------------------------------+-----------+
| Statistic                     | TotalHits |
+-------------------------------+-----------+
| Brazil                        |         1 |
| France                        |         1 |
| USA                           |         1 |
| Total for 2014-06-19 01:00:00 |         3 |
| USA                           |         1 |
| Total for 2014-06-19 02:00:00 |         1 |
| Total for 2014-06-19          |         4 |
+-------------------------------+-----------+
7 rows in set (0.00 sec)

UPDATE 2014-06-21 19:07 EDT

Perhaps the the two queries combined with UNION will work for you

SELECT
    IFNULL(Country,CONCAT('Total for ',IFNULL(Hour,DATE(NOW())))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Hour,Country
UNION
SELECT
    IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Country,Hour WITH ROLLUP;

I removed the WITH ROLLUP from the first one so that the total comes out once


Sql server 2014: backup log to 2 destinations We want a single job to back up log to azure and file system every time.

So we can log ship from file system and from azure at the same time.

I seem to recall that the redgate sql backup tool has a "backup and copy to..." feature. In sql natively yet?

Can this be done?

I've never used SSIS to use the backup task so I'm not sure exactly how great this solution is however it should theoretically work and it took me about 10 minutes instead of 10 days.

First off create a package and make your backup database task. Set it up however you need to set it up like this.

Then make a for each file loop and have it search through the directory you're adding it to.

Map the results to a variable.

Then use file System tasks to move it wherever the hell you want.


How can I find age difference using birthdates in MYSQL? •Students(stuID: String, stuName: String, gender: String, birthdate: Date, enterYear: Year, gpa: Float)

•Departments(deptName: String, numPhDs: Integer)

•ProfessorWorks(profID: String, profName: String, deptName: String)

•CoursesOffer(cNo: String, cTitle: String, deptName: String)

•Majors(deptName:  String, stuID: String, degreeProgram: String, attendYear: Year, attendSemester: String)

•Sections(cNo: String, semester: String, sectNo: String, profID: String)

•Enrolls(stuID: String, cNo: String, sectNo: String, semester: String, grade: String)

I want to know the age difference between the oldest and youngest student(s) in MYSQL.

SELECT MAX(DATEDIFF(NOW(),'BIRTHDATE') - MIN(DATEDIFF(NOW(),'BIRTHDATE')
FROM STUDENTS;

age difference is the same as the difference in YEARS between the MIN and MAX birthdates:

SELECT TIMESTAMPDIFF(YEAR, MIN(birthdate), MAX(birthdate))
FROM STUDENTS;


MySQL indexing on 11 million rows I have a development and production server, both with different amounts of RAM. Development machine has 8G, while production has 64G.
I'm importing a large table from development to production, but before I started the process I dropped all the indexes to speed the import process, because I'm using stored procedure and with indexes it was very very slow.
On the development machine, while I was playing with creating/dropping indexes, I noticed that mysql used temporary space on the disk (default /tmp) for creating the indexes and it got to almost 8G.
The question is, since the production server has 64G of ram, will the mysql use the RAM for storing the temporary table while creating the indexes or will it use /tmp for the same thing?

What "import" mechanism was used? Some will automatically postpone the secondary-index creation for you.
I don't understand "I'm using stored procedure and with indexes it was very very slow". SPs being used for the import? Or indexes interfering with SPs? What?
Is /tmp on the same disk and in the same disk 'partition'? This is critical when it needs to "move" the result to the mysql directory.
You really need to upgrade -- 5.6 has some indexing improvements; 5.7 and 8.0 have more.
MySQL uses a combination of RAM and disk -- trying to minimize I/O.  So, the question of "will it use RAM" is moot.  It will "do what is optimal".
To create a secondary, non-UNIQUE, BTree index, MySQL probably

reads the entire data, writing the secondary keys and primary keys to a file
sorts that file (OS file sort is quite efficient)
reads the file and builds the BTree for the INDEX; this will involve writing to disk.

However. "read" and "write" and "file" are cached in based on InnoDB's whim and the OS's whim.  Furthermore, step 3 may delay the actual write to disk of the BTree.
UNIQUE requires a check; FULLTEXT and SPATIAL were not available in InnoDB in 5.5.  If you are using Engine=MyISAM, some of what I have said is incorrect.
A related question -- Is that OS sort faster than inserting the rows one at a time and building the index on the fly?  The answer is "probably".  On-the-fly requires keeping index entries in the in-RAM key_buffer or buffer_pool, plus flushing such as it goes along.  That gets into another long discussion.
More
The main part of ALTER TABLE (at least on 5.5 and before) involves reading the entire table and (depending on the specific Alter) rewriting the data BTree and/or writing index BTree(s).
If tmpdir refers to a directory that is on the same disk partition as the main mysql/ directory then various ALTER TABLEs can "move" a temp file (which such is required) from tmpdir to mysql/databasename/ at the end of the Alter.  The "move" is accomplished (at least on Linux) by a couple of quick directory tasks.  If different disk partitions are needed, a physical "copy" is needed; this requires a big read and write.
A file sort is likely to be used as one step in building an index.  How this is done is up to the OS.  But it is equivalent to reading and writing the data being sorted (the index's BTree) one or more times (depending on number of rows in table).  For a tiny table, the OS will effectively cache the effort in RAM.  For a huge table multiple "sort-merge" passes will be required.
Sorting is "O(N*logN)".  That is, if you double the number of items, it takes more than twice as long to complete the sort.  (However, I/O dominates the time consumed, so one can estimate time very precisely.)
If the server is configured with multiple not-big-enough physical disks, the above should help with understanding that part of the Alter.
It is optimal (for MySQL) to give most of RAM to MySQL.  But, when sorting is necessary, the OS needs some room, too.  The tradeoff here is beyond my understanding of the details.


Determining how a schema change occurred? Something bad happened yesterday.

A view that was created sometime back ago was modified by someone which eventually broke the reports. Unfortunately. somebody (knowingly or unknowingly) did this modification in PRODUCTION database.

My Question: Is there a way (script/software/freeware etc) by which we can come to know who (username) did this modification, so that i can revoke the access to production database for that user.

If my question is unclear, please comment.

This gets logged to the default trace so, as long as it is enabled and hasn't rolled over in the meantime it should appear in the "Schema Changes History" report.

To access this in Management Studio right click the database then from the context menu choose Reports -> Standard Reports -> Schema Changes History

To retrieve the same information via TSQL you can use

SELECT StartTime
       ,LoginName
       --,f.*
FROM   sys.traces t
       CROSS APPLY fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path),
                                                       CHARINDEX('\', REVERSE(t.path)),
                                                       260)
                                             ) + N'log.trc', DEFAULT) f
WHERE  t.is_default = 1
       AND ObjectName = 'FOO'
       AND EventClass IN (46, /*Object:Created*/
                          47, /*Object:Dropped*/
                          164 /*Object:Altered*/ )


What is the simplest way of excluding the current row from the result of an analytic? I want to do something like the following:

with w as ( select level as foo,
                   decode(level,8,1,mod(level,4))*100 as bar
            from dual connect by level<9 )
select foo, bar,
       min(foo) keep (dense_rank first order by foo) over (partition by bar) as lowest
from w;
/*
FOO                    BAR                    LOWEST
---------------------- ---------------------- ----------------------
4                      0                      4
8                      100                    1
5                      100                    1
1                      100                    1
2                      200                    2
6                      200                    2
3                      300                    3
7                      300                    3
*/

but I want lowest to show the next lowest if the current row is the lowest for a given bar, or null if it is the only one. In other words I want this result:

/*
FOO                    BAR                    PSEUDOLOWEST
---------------------- ---------------------- ----------------------
4                      0
1                      100                    5
5                      100                    1
8                      100                    1
2                      200                    6
6                      200                    2
3                      300                    7
7                      300                    3
*/

What is the simplest way of achieving this?

There may be a simpler way, but here is a solution.  If the current row is the minimum foo for the bar, then we always want the next (lead) foo.  When there is only one foo for a bar the lead will always be null and for every other case we really do want the minimum foo for the bar.

with w as (select level as foo, decode(level,8,1,mod(level,4))*100 as bar
   from dual connect by level<9)
select foo, bar
   , case
        when foo = min(foo) over (partition by bar)
           then lead(foo) over (partition by bar ORDER BY foo)
        else min(foo) over (partition by bar)
     end pseudolowest
from w;

Unfortunately rows excluding current row doesn't exist.


Is there any side effect if select statements are wrapped in BEGIN TRAN ... COMMIT? I am working on a rather old .NET project and have introduced some new features (on top) that have generated the following side-effect: all generated SELECTs (or groups of) are wrapped in BEGIN TRAN ... COMMIT statements.
This sounds silly, but getting rid of this requires a lot of changes and I cannot afford to do so. My assumption is this basically means a small overhead for each group of SELECTs (roundtrips between application and SQL Server for BEGIN TRAN and COMMIT).
I am wondering if there is more to this (extra locking?).
Question: Is there any side effect if select statements are wrapped in BEGIN TRAN ... COMMIT?

The answer depends on what isolation level your .NET project is setting when it accesses SQL Server.
If it's READ COMMITTED (the default), then there's not really any additional overhead (other than the additional BEGIN TRAN and END TRAN statements being executed).  You might think additional information would be written to the transaction log, but transactions don't really "begin" until something happens that needs to be logged.  I've written about this here: Transactions Don't Start At BEGIN TRAN
If you're using the SERIALIZABLE isolation level, you could get burned by the fact that those SELECTqueries will hold the locks they take for the duration of the transaction.  This could be happening without you realizing it if you're using TransactionScope in your .NET project, since that uses SERIALIZABLE by default (see my blog post for a demo: TransactionScope Considered Annoying).
I'd say all in all, if the default isolation level is being used, there's not much downside to leaving things the way you've described.  You'll want to be careful about the isolation levels though.


SQL Server 2000 Hitting 8000 Byte Limit Exporting BLOB I'm trying to help out my security by exporting user data from a legacy access system. The back end is SQL server 2000. The data I'm particularly interested in is the user photograph, which is stored as a BLOB in the database.

With a bit of Googling I found a stored procedure to help with this, but when I tried to create it I was getting errors when declaring a variable as

DECLARE @ImageData VARBINARY (max)

A similar error occurred in this line;

SELECT convert (VARBINARY (max), Face, 1)

I was getting an error stating "max" was invalid. After a bit more Googling I realised that server 2000 wouldn't accept "max" and instead I used 8000, which is the maximum varbinary size.

My export worked to some extent, in that the image data exported, but only the first 8KB. Obviously this is due to the maximum varbinary size being 8000. the images them selves are OK for the top of the picture, but then just flat grey.

Is there a way to get the whole picture out? the stored procedure I'm using is as follows;

CREATE PROCEDURE dbo.JB_Export_Photos (
   @PicName NVARCHAR (100)          -- name of picture in db USE cardID
   ,@ImageFolderPath NVARCHAR(1000) -- name of folder which will be saved to
   ,@Filename NVARCHAR(1000)            -- filename after export add .jpg
   )
AS
BEGIN
   DECLARE @ImageData VARBINARY (max);
   DECLARE @Path2OutFile NVARCHAR (2000);
   DECLARE @Obj INT

   SET NOCOUNT ON

   SELECT @ImageData = (
         SELECT convert (VARBINARY (max), Face, 1)
         FROM picTable-- needs changing to real database name
         WHERE picturename = @PicName                       -- 'pictureName' needs changing to match field in db, probably cardID
         );

   SET @Path2OutFile = CONCAT (
         @ImageFolderPath
         ,'\'
         , @Filename
         );
    BEGIN TRY
     EXEC sp_OACreate 'ADODB.Stream' ,@Obj OUTPUT;
     EXEC sp_OASetProperty @Obj ,'Type',1;
     EXEC sp_OAMethod @Obj,'Open';
     EXEC sp_OAMethod @Obj,'Write', NULL, @ImageData;
     EXEC sp_OAMethod @Obj,'SaveToFile', NULL, @Path2OutFile, 2;
     EXEC sp_OAMethod @Obj,'Close';
     EXEC sp_OADestroy @Obj;
    END TRY

 BEGIN CATCH
  EXEC sp_OADestroy @Obj;
 END CATCH

   SET NOCOUNT OFF
END
GO

Thanks in advance.

You should be using the Bulk Copy Program (BCP), a command-line utility that ships
with SQL Server 2000, for importing and exporting large amounts of data in
SQL Server 2000 databases.

For more information see the Database Journal article
Utilize BCP with SQL Server 2000.


Postgres Combine Summed Values from 2 Queries / Tables into Single Row Say I had the following 2 queries, summing values from separate tables.
I would like the sum of recorded time
SELECT
    SUM(minutes) as recorded_minutes,
    SUM(hours) as recorded_hours
FROM recorded_time
WHERE
    project_id = 1

To be combined with the sum of budgeted time in a single row
SELECT
    SUM(minutes) as budgeted_minutes,
    SUM(hours) as budgeted_hours
FROM budgeted_time
WHERE
    project_id = 1

Is it possible to do this in a single query?

Looks like I can use CROSS JOIN
SELECT recorded_minutes, recorded_hours, budgeted_minutes, budgeted_hours
FROM
    (SELECT
        SUM(minutes) as recorded_minutes,
        SUM(hours) as recorded_hours
    FROM recorded_time
    WHERE
        project_id = 1) rt
CROSS JOIN
    (SELECT
        SUM(minutes) as budgeted_minutes,
        SUM(hours) as budgeted_hours
    FROM budgeted_time
    WHERE
        project_id = 1) bt

http://www.sqlfiddle.com/#!17/e2388/4/0


Do ORMs provide interfaces for working with window functions? Many of my friends as well as previous workplaces emphasized use of ORMs over working with SQL directly. I've been exposed to Laravals ORM (Eloquent), Silverstripe's ORM, Active record, and Entity Framework (kinda).

Now I'm writing SQL code that uses the OVER clause quite frequently and wondering if that were possible from typical ORMs as mentioned above. Do ORMs make the OVER clause accessible - or are there any ORMS that do that?

If so, would those ORMs make accessible all the OVER clause's aruguments (which I guess would be database specific)?

Not really.

While some ORMs may very well be able to utilize windowing functions you yourself write none of them will be able to generate window functions. ORMs map data to data. They are an Object Oriented to relational data-translation layer. Window functions are a data-processing feature. It's a declarative way to process a result set, they run in the last-step of result-set generation, docs:

  If the query contains any window functions, these functions are evaluated after any grouping, aggregation, and HAVING filtering is performed. That is, if the query uses any aggregates, GROUP BY, or HAVING, then the rows seen by the window functions are the group rows instead of the original table rows from FROM/WHERE.

At the point a Window Function works, the authors of an ORM may as well map the syntax into their own result-set processing features.


SQL Server 2012 mirror setup - how to reduce transaction log files We just upgraded our Windows Server 2003\SQL Server 2005 setup to Windows Server 2012/SQL Server 2012. Both configurations have a mirror with NO witness server.

On the old setup the transaction logs remained small, about 22 GB total. Now they're over 160 GB and the partition of the new server is getting full. I need to reduce the transaction log file sizes.

However, all of the descriptions I've read say to switch the recovery mode from Full to Simple then back to Full. This won't work because we have a mirror so the recovery model MUST remain in Full recovery mode. I couldn't change recovery modes unless I break the mirror.

What would be the best course of action to reduce the log file sizes? Should I go ahead and break the mirror, switch from Full -> Simple then back? Does anyone have experience with this kind of setup?

Direct answer to your question

  What would be the best course of action to reduce the log file sizes?

In full recovery model only transaction log backup, perhaps frequent transaction log backup in your case, would truncate the logs and keep check on log file size. There is no other way to truncate logs. I strongly suggest you to read Why transaction log keeps growing and run out of space

  Should I go ahead and break the mirror, switch from Full -> Simple then back? Does anyone have experience with this kind of setup?

No please dont do that this will break the mirroring and you would have to re-establish it and it would be big hassle for you if you have got big database and mirror is located far from principal. Anyways if you do that and somehow configure mirroring after few days you would again face this issue because you actually did not solved it.


Tail log backup query in log shipping reversal I am doing log shipping failover and failback configuration in SQL Server 2008 R2 environment. During the Failover, I am taking the tail log backup of primary database with query:

Script to backup Log:----------

DECLARE @filepath VARCHAR(200)
SET @filePath=N'\\ClusterName\SQL_Tail_Backup\DBNAME_Tail_Log.trn'
BACKUP LOG [DBNAME]
TO DISK = @filePath WITH NO_TRUNCATE, NOFORMAT, INIT,
NAME = N'DBNAME-Tail Log Backup',
SKIP, NOUNLOAD, NORECOVERY, STATS = 10

And script for Log restore is:

Script to restore Log:--------

DECLARE @filepath VARCHAR(200)
SET @filePath=N'\\ClusterName\SQL_Tail_Backup\DBNAME_Tail_Log.trn'
RESTORE LOG [DBNAME]
FROM DISK = @filepath WITH RECOVERY;
GO

But after making secondary database writable, my applications are not connecting to the database. While, trying backup and Restore from GUI works well. Please Help!

If I had to make a guess your database is just fine, but you never created the server principals (logins) associated with the database principals (users) in your database.  It's deprecated but try this and see if you get a result.

EXEC sp_change_users_login 'report'

You can also try this piece of code:

USE dbname;
SELECT logins.name as logins, users.name as users, users.type_desc
FROM sys.database_principals users
LEFT OUTER JOIN sys.server_principals logins
    ON users.sid = logins.sid
WHERE users.type IN ('U','S','G')

If there are any NULLs in the logins column then that's probably your problem.  Create the login (if it's a SQL ID then make sure you specify the correct SID).


Implement "account" transactions in postgresql I am looking into on different ways a simple account functionality that can be implemented in Postgresql 9.5. I would like to be able to do it with maximum of "Repeatable read" as transaction isolation level. The reason why it should be "Repeatable read" isolation level is that we have detected a high level of collisions "serializable" isolation level and even with a retry strategy around 10 times within 10 seconds it still fails because of the amount of collisions.

Deposit money
Withdraw money
Cannot withdraw money if balance of the user is less than zero (sum of amount for user)
Balance must never be able to go below zero (sum of amount for user)

It should handle multiple connections and multiple users and the same user can initiate deposits and withdraws in different connections.

The only solution I currently have is to do something like the following:

For deposits:

Allow deposit money directly as an insert.

For withdraws:

Check balance > amount of withdraw before continue.
Insert withdraw money transaction with status initiated.
Update withdraw money transaction status failed if sum of transactions for user in status (initiated, completed) is less than zero.
Update withdraw money transaction status completed if status is initiated.

Any materials or examples are welcomed.

Good Idea For deposits: Allow deposit money directly as an insert.
Bad Idea For withdraws: Check balance > amount of withdraw before continue.

Consider instead having a CHECK (balance >= 0), then just allow the transaction to fail.

I'm not sure about the rest of them.. You mention all this STATUS stuff, but what does it matter?

BEGIN;
UPDATE accounts
  SET balance = balance - 100
  WHERE name = 'Bob';
UPDATE accounts
  SET balance = balance + 100
  WHERE name = 'Sally';
END;

If the balance on accounts would go under 0 with the deduction, it'll simply fail there.
If it passes then you will see bob's balance go down for your transaction.
Adding 100 to Sally's account is a no-brainier, but you have a lock on that too now.

The default transaction levels permit phantom reads, but here that's not an issue. The first statement is an UPDATE. It either works, or it doesn't. If it works, UPDATE gets ROW EXCLUSIVE lock. No other UPDATEs will work on that record until the transaction is committed.


Is using multiple foreign keys separated by commas wrong, and if so, why? There are two tables: Deal and DealCategories. One deal can have many deal categories.

So the proper way should be to make a table called DealCategories with the following structure:

DealCategoryId (PK)
DealId (FK)
DealCategoryId (FK)

However, our outsource team stored the multiple categories in the Deal table this way:

DealId (PK)
DealCategory -- In here they store multiple deal ids separated by commas like this: 18,25,32.

I feel that what they did is wrong, but I don't know how to clearly explain why this is not right.

How should I explain to them that this is wrong? Or maybe I'm the one who's wrong and this is acceptable?

Yes it's a terrible idea.

Instead of going:

SELECT Deal.Name, DealCategory.Name
FROM Deal
  INNER JOIN
     DealCategories ON Deal.DealID = DealCategories.DealID
  INNER JOIN
     DealCategory ON DealCategories.DealCategoryID = DealCategory.DealCategoryID
WHERE Deal.DealID = 1234

You now have to go:

SELECT Deal.ID, Deal.Name, DealCategories
FROM Deal
WHERE Deal.DealID = 1234

Then you need to do stuff in your application code to split that comma list into individual numbers, then query the database seperately:

SELECT DealCategory.Name
FROM DealCategory
WHERE DealCategory.DealCategoryID IN (<<that list from before>>)

This design antipattern stems from either a complete misunderstanding of relational modelling (You don't have to be scared of tables. Tables are your friends. Use them), or a bizarrely misguided belief it's faster to take a comma-separated list and split it in application code than it is to add a link table (it never is). The third option is that they're not confident/competent enough with SQL to be able to set up foreign keys, but if that's the case they shouldn't have anything to do with the design of a relational model.

SQL Antipatterns (Karwin, 2010) devotes an entire chapter to this antipattern (which he calls 'Jaywalking'), pages 15-23. Also, the author has posted on a similar question over at SO. Key points he notes (as applied to this example) are:

Querying for all deals in a specific category is rather complicated (the easiest way to solve that problem is a regular expression, but a regular expression is a problem in and of itself).
You can't enforce referential integrity without foreign key relationships. If you delete DealCategory nr. #26, you then, in your application code, have to go through each deal looking for references to category #26 and delete them. This is something that should be handled at the data layer, and having to handle it in your application is a very bad thing.
Aggregate queries (COUNT, SUM etc), again, vary from 'complicated' to 'almost impossible'. Ask your developers how they'd get you a list of all categories with a count of the number of deals in that category. With a proper design, that's four lines of SQL.
Updates become much more difficult (i.e. you have a deal that's in five categories, but you want to remove two and add three other ones). That's three lines of SQL with a proper design.
Eventually you'll run into VARCHAR list length limitations. Although if you have a comma-seperated list that's over 4000 characters, chances are parsing that monster is going to be slow as hell anyway.
Pulling a list out of the database, splitting it up, and then going back to the database for another query is intrinsically slower than one query.

TLDR: It's a fundamentally flawed design, it won't scale well, it introduces additional complexity to even the simplest queries, and right out-of-the-box it slows your application down.


How to update a single record in a table based of foriegn key ID in another table? I am still very new to sql. I am having troubles updating one table. I am sending information from a web application into Microsoft SQL 2008 database. I have a table called EmployeePermissions that has employee ID and Permission ID. The Permission ID links this table to the Permissions table where different permissions are located and given a true or false value. If I am sending SQL the employee ID and the true or false values of the specific permissions, how do I update the Permissions table by sending the information through the EmployeePermissions table? Hopefully the diagram below can help explain a little better. Thanks!

OK, so I'm not going to comment on the table design as I assume that this is just part of the tables.

To do the update that you are looking for there's a couple of options.

You can get the Permission ID and put it into a variable, then use that against the permission table.

DECLARE @PermissionId INT

SELECT @PermissionId = PermissionId
from Employee
where EmployeeId = 2

UPDATE Permission
SET Read='T', Write='T', Delete='T'
where Permission = @PermissionId

Or you can do it all with a join in your update statement.

UPDATE p
SET Read='T', Write='T', Delete='T'
FROM Permission p
JOIN Employee e ON p.PermissionId = e.PermissionId
WHERE e.EmployeeId = 2

In either case you'll want to properly parameterize the query in your application layer to prevent against SQL Injection.


Replication successful but slave skips certain queries I am testing a MySQL master-slave replication. Here is the master my.cnf:

log-bin=/var/log/mysql/mysql-bin.log
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Here is slave my.cnf:

server-id=3
log-bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/slave-relay.log
relay-log-index=/var/log/mysql/slave-relay-log.index
log-slave-updates=1
replicate-wild-do-table = ads.%
replicate-ignore-db = mysql
replicate-ignore-db = test

Everything works fine but if I execute something like this on master, it won't replicate to slave.

use test;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

If I omit use test, the replication is successful.

I would like to skip test db from replication but also need to avoid the above mentioned issue. I have searched a number of posts but couldn't find a solution. Am I missing anything simple?

--replicate-ignore-db applies only to the default database (determined by the USE statement). Because the ads database was specified explicitly in the statement, the statement has not been replicated.

My advice is to change the context with a USE instead of explicitly specify the database in the statement.

Max

EDIT (for questions)

1.

  Ok, got it but then what should be the my.cnf values if I want to log all databases on the master but selectively replicate on slave (like ignoring test and replicating ads)?

Note that your syntax is correct, all statements will be written on binary log (master) and the slave will ignore the mysql and test databases BUT YOU SHOULD EXIT THE CONTEXT mysql and test before run queries on other databases.

The context is ads : REPLICATED

USE ads;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

The context is test: IGNORED

USE test;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

The context is toto: REPLICATED because of your replicate-wild-do-table and toto is not ignored by a replicate-ignore-db

USE toto;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

2.

  Looks like "replicate-wild-do-table" means "replicate only this db and
  ignore everything else".

replicate-do-table=ads will replicate statements on ads database (your context should be ads, USE ads;) and ignore the statements on other databases:

The context is ads : REPLICATED

USE ads;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

The context is test : IGNORED

USE test;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

replicate-wild-do-table=ads will replicate statements on ads database even if your are in an other context (test for instance), and ignore the statements on other databases.

The context is ads : REPLICATED

USE ads;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

The context is test : REPLICATED

USE test;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );


MongoDB: Can I perform a second query if the first doesn't match When querying MongoDB is it possible to perform a query and if it isn't matched perform a second query. I could perform an $or which will do booth queries and return the results for booth but I only need to perform the second query if the first doesn't match.

Let say my BSON documents look like:

[
  {"id": 1, "value": "foo"},
  {"id": 2, "value": "bar"}
]

I need to return "id": 1 but if it doesn't exist I need to return "id": 2

No, you can't do conditional returns within a single query. You seem to understand that, based on the wording of the question. Maybe if you can explain the exact use case more we might be able to think of another idea.

With the general description you give, I would just make two round trips to the server, if you'd usually find something the first try. Or do an $or, as you mention, if it's more often that you'd return the 2nd option.


What could be preventing updates and inserts to a table without anything visibly blocking the process? The following has happened on more than one occasion.

Updates or inserts to a certain table (I'm not 100% sure but there seem to be a couple of tables it always happens to) fail due to a timeout. If I run the same query from Management Studio, it just keeps running without ever ending or failing (I've let it try up to an hour, on one occasion).

SSMS's Activity Monitor, as well as sp_who2 don't show the process as being blocked by anything. It seems to be running with no issues, except that it isn't doing anything or ending.

This is the same if it's only one row being updated, selected by its primary key; it's not an issue of the query affecting many rows or the row being difficult to be "found", as far as I can tell.

I've tried to look for other questions here and using Google, but all of them seem to point to the process being blocked by something else that's visible by sp_who2, but this isn't the case here.

EDIT 2015-12-15: The problem has resurfaced, and this time I was able to run sp_whoisactive as well. It shows my process, but nothing in the wait_info or blocking_session_id columns. The data it returns is the following (sensitive information removed):

dd hh:mm:ss.mss: 00 00:00:29.016
session_id: 85
sql_text: <?query --
insert into MSmerge_cpmv_66969A68BA3C4DFE9CFC3A88E87970FD (publication_number, tablenick, rowguid, partition_id)
            select distinct 1, @tablenick, bt.rowguid, bt.partition_id
            from (  select [BlockedTable].[blocked_table_id], [BlockedTable].[for_replication], [BlockedTable].[rowguid], msp.partition_id from inserted [BlockedTable] , dbo.MSmerge_partition_groups msp where  (msp.publication_number = 1 and ( ((blocked_table_id in (select blocked_table_id from dbo.GetFilteredIDsForUser(msp.[SUSER_NAME_FN] )) and for_replication = 1)) )) ) as bt

--?>
login_name: MyUserName
wait_info: NULL
CPU: 28,813
tempdb_allocations: 0
tempdb_current: 0
blocking_session_id: NULL
reads: 25,507,251
writes: 9
physical_reads: 0
used_memory: 3
status: runnable
open_tran_count: 2
percent_complete: NULL
host_name: MyHostName
database_name: MyDatabaseName
program_name: .Net SqlClient Data Provider
start_time: 2015-12-15 10:44:49.200
login_time: 2015-12-15 10:44:49.183
request_id: 0
collection_time: 2015-12-15 10:45:18.237

It's possible that I haven't understood how to read the output of sp_whoisactive, but I've not found any information on that. The existing answer here (as of 2015-12-15) explains how to gather data and how to fix the blocking, but not how to read the output.

The query shown in the output seems to mean it has trouble inserting the new data in partitions for our merge publication. The publication is set to precompute partitions. Could it be that this is what's blocking it?

What else may be blocking the query in such a way?

I will prefer to track blocking with any of 2 below rather than SP_who2 or Activity monitor:

Use Sp_whoisactive which is a procedure written by Adam Machanic. Once created , you can gather the data to analyse the blocking using:

a. Create one destination table

b.Create Your Loop to Periodically Log Data

c.Set Up Your Query to Look at the Results

On how to get these details captured, read here.

You can use SQL Server Blocked Process Report Viewer written by Michael.J swart which apart from finding help you in analyzing the blocking root caused in very helpful manner.

You can read the article Okay, You’ve Found Blocking, Now What? to get more detailed info.

Hope these 2 will help as they have been doing us for quite long.


Database Engine Tuning Advisor - The minimum storage space required exceeds the default I created a test database with one table

CREATE TABLE [dbo].[TestTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TimeStamp] [datetime] NOT NULL
) ON [PRIMARY]

I have a sql script that consist of one line.

INSERT INTO TestTable ([TimeStamp]) VALUES(CURRENT_TIMESTAMP);

When I run the script in SSMS it works. But when I use it as a workload in DTA the the Analysis is Aborted with this error

  The minimum storage space required for the selected physical design
  structures exceeds the default storage space selected by Database
  Engine Tuning Advisor. Either keep fewer physical design structures,
  or increase the default storage space to be larger than at least 3
  MB.Use one of the following methods to increase storage space: (1) If
  you are using the graphical user interface, enter the required value
  for Define max. space for recommendations (MB) in the Advanced Options
  of the Tuning Options tabbed page; (2) If you are using dta.exe,
  specify the maximum space value for the -B argument; (3) If you are
  using an XML input file, specify the maximum space value for the
   element under

Using Windows 10 (latest update), SQL Server 2016 SP1.

Why do I get this error?

You have to go to Tuning Options and then Click Advanced Options. On the popup, define the max. space for recommendations.

https://www.sqlservercentral.com/Forums/Topic1383773-391-1.aspx


High CPU usage by GHOST CLEANUP after DROP FK and DELETE I am trying to replace data in table referenced by FKs in other tables on SQL Server. There are more than 1M rows in both referenced and referencing table and I want to make the replacement atomic. Obvious solution would be:

BEGIN TRAN
ALTER TABLE TabReferencing NOCHECK CONSTRAINT MyForeignKey
DELETE FROM TabReferenced
INSERT INTO TabReferenced (...)
ALTER TABLE TabReferencing CHECK CONSTRAINT MyForeignKey
COMMIT

If those statements (executed one-by-one or in batch, over network on on local SQL Server) take longer than a few seconds to complete, things go nasty. GHOST CLEANUP background thread on server starts up few moments after DELETE and starts using 100% CPU and quite a lot of IO, degrading overall performance to unacceptable level (500 inserts/min compared to 50000 inserts/min without cleanup).

So far I have tried following workarounds:

Disabling FK before beginning transaction and re-enabling it after COMMIT works fine (quick enough), but if process (or network) fails during inserts, I end up with table with no FKs.
Deleting data from both referencing and referenced table and NOT touching FK works fine, but I don't want to repopulate referencing table due to it's size.
Dropping and re-creating FK instead of dis/enabling in transaction makes no difference.
Dropping and re-creating FK and using TRUNCATE instead of DELETE works fine - at least until I need DELETE with a WHERE clause.
Disabling GHOST CLEANUP thread (DBCC TRACEOFF(661, -1)) makes original batch complete at full speed, but it's not the real solution for production environment.

Why does disabling FK and deleting table's content trigger GHOST CLEANUP and just deleting table or truncating table does not? Why does cleanup with disabled/dropped FK takes way longer than same cleanup with FK enabled?

There are no triggers in DB. All FKs have empty ON UPDATE/DELETE action.

Why does disabling FK and deleting table's content triggers GHOST
  CLEANUP and just deleting table or truncating table does not?

From your question it looks like you understand ghost records and the ghost cleanup process but I'll summarise for future readers.

When a row is deleted, it isn't physically removed from the page. Instead, it is marked as ghosted and a background task (ghost cleanup) comes along later and removes the records entry from the page slot array (note the data isn't overwritten or zero'd out).

When you TRUNCATE or DROP the table records aren't deleted/ghosted individually, entire pages are de-allocated. See the two excellent answers from @NickChammas and @MartinSmith describing the mechanics of drop and truncate in Why use both TRUNCATE and DROP?

That's why you aren't seeing the ghost cleanup task working over time following a TRUNCATE or DROP.

  Why does cleanup with disabled/dropped FK takes way longer than same
  cleanup with FK enabled?

If you've stated the sequence of events accurately, this process is not what is triggering the ghost cleanup process. The ghost cleanup task doesn't start removing deleted records until the transaction that deleted them commits (it can't as they're locked).

So, either something else is at work or you've encountered a bug. KB978430 could apply, if that doesn't work I'd be inclined to log a support call.


Is there any way to tell which derived column failed? In an SSIS dataflow task we have a derived column transformation with approximately 100 columns (basically converting raw input string data into typed variables). When this task fails, is there any way to tell which column caused the failure, for logging purposes? The only other alternatives I can think of are a custom script task to perform each conversion individually (yuck) or a separate derived column transformation for each data point (double yuck).

Basically I just want to be able to re-direct failure rows and know why they failed.

So an example. Our package is being used to allow users to bulk-upload to our database using Excel spreadsheets. So let's say the spread sheet coming in looks like this (except there's hundreds of columns):

+--------+-----------------+---------+------------+---------+
| Text1  |     Number1     | Number2 | DateTime1  |  Text2  |
+--------+-----------------+---------+------------+---------+
| Spring | 1               |       1 | 1/1/0001   | Flowers |
| Summer | 2               |       2 | 6/1/2015   | Sweaty  |
| Fall   | N/A             |       3 | 10/31/2099 | Crunchy |
| Winter | This is garbage |       4 | 12/12/2020 | Icy     |
+--------+-----------------+---------+------------+---------+

In this instance we want Spring, Summer, and Fall to succeed. Fall with have a null value for Number1. The derived column will have logic that looks something like this (not valid syntax, just the logic)

sanitizedNumber1 = Number1 == "N/A" ? null : cast(Number1 as int)

Winter will be redirected down the error path and logged. Is there any way to know which derived column failed? Again, we have about 100 inputs that are being processed in this transformation in a similar fashion. I'd like to be able to log something like:

  Import record "Winter" failed due to invalid data in "Number1"

It doesn't necessarily have to be this format, but anything that would allow a user to be able to uniquely identify the bad data point would be acceptable. I know this would be possible using a script component and performing the conversions manually (which is what we're going to have to do if there's no better option) but if it's possible to just modify the Derived Column Transformation to provide something along these lines I'd rather do that instead of re-implementing the entire component in a script.

You have to manually identify the failures.

Redirect the rows (as you currently are) into a table that matches your columns, with the single addition of an IDENTITY column. This table should be all VARCHAR data types, so that you retain all original values.

Now you can identify your failures using TRY_CAST when querying the table.

For example:

SELECT
Identity,
TRY_CAST(Winter As INT) As WinterConverted,
Winter
--------------------------------------
Identity  |  WinterConverted   | Winter
   1      |    NULL            | NaN

TRY_CAST failed to cast the value in Winter column to an int leaving you with a null. You can do this for all columns.

If it's worth your time, you could make a more complex query to return each column name that contains a NULL. If not, you should be able to relatively easily visually inspect.

Of course, if most of your errors are 'NaN' or 'N/A', there's a good chance you're wasting time identifying errors you already knew about. Cleanup the issues before they become one:

Winter == "NaN" || Winter == "N/A" ? -1 : (DT_I4)Winter

As a side note, you can add a TRY_CAST to 100 columns very easily by using vertical editing: hold Alt+Shift in SSDT or SSMS (some other apps too) and then ↑ or ↓, then edit hundreds of lines at the same time.


MySQL general log as table -- Scheduling cleaning Rolando had posted as an answer in other questions how to enable the general log as a table, I have done this but I would like to schedule cleaning in order to only keep the last 3 days. He posted this, but I'm unsure on how to use it, could I use this as a bash script and add it to cron as a scheduled task?

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
CREATE TABLE mysql.general_log_new LIKE mysql.general_log;
INSERT INTO mysql.general_log_new
SELECT * FROM mysql.general_log WHERE event_time > NOW() - INTERVAL 3 DAY;
DROP TABLE mysql.general_log;
ALTER TABLE mysql.general_log_new RENAME mysql.general_log;
SET GLOBAL general_log = @old_log_state;

It won't cause problems, but you will lose all the old log entries, so advice above is good.

You can do a backup of logs if you want with:

mysqldump -p --lock_tables=false mysql general_log > genlog.sql

Do you need to have the general log on all the time? I usually only turn it on when I'm troubleshooting performance issues. MySQL logs EVERYTHING there (client connects, disconnects, and EVERY statement). In most systems, the logs get very big very quickly. There is also some performance overhead for this.


select from database with special characters I am using php

select * from `outstanding` where `lcocode`='PN007\00128'

select * from `outstanding` where `lcocode` LIKE 'PN007\00128'

kindly suggest code for fetching data with backslash

I used above code but did not got results.

Use the following query :

select * from outstanding where lcocode LIKE 'PN007\\\\00128'

Backslash is an escape prefix for both strings and LIKE patterns. So you need to double it once for LIKE, and again for string literal syntax.


Check if master is up from slave server(replication) postgresql I want to check whether my master server is up by querying from slave server ?
Is there any view or command ?

I wanted to write this as comment but I don't have enough reputation.
Did you tried, pg_stat_wal_receiver? It came with PostgreSQL-9.6.

dbs=# select * from pg_stat_wal_receiver ;
-[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 12345
status                | streaming
receive_start_lsn     | B0/F0000000
receive_start_tli     | 1
received_lsn          | CE/13C7B18
received_tli          | 1
last_msg_send_time    | 2018-09-26 16:15:25
last_msg_receipt_time | 2018-09-26 16:15:25
latest_end_lsn        | CE/13C7B18
latest_end_time       | 2018-09-26 16:15:25
slot_name             |
conninfo              | connection info

https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#PG-STAT-WAL-RECEIVER-VIEW


Edit pg_hba.conf file using pgAdmin 4 This other Question has Answers about viewing and editing settings in postgresql.conf file using pgAdmin 4. But those Answers did not mention editing the pg_hba.conf file used for authentication of users.

➥ How to use pgAdmin 4 for editing the pg_hba.conf file?

No, pgAdmin cannot edit pg_hba.conf

Unfortunately, there is no mechanism by which pgAdmin can edit a pg_hba.conf file.  The reason why it's possible to edit postgresql.conf via pgAdmin is that the ALTER SYSTEM syntax is something that can be entered into a client interface.  Therefore, pgAdmin was able to "edit" postgresql.conf because it could send an ALTER SYSTEM query to the database.  No such syntax exists for editing access control.


InnoDB Buffer Pool Hit Rate As given at http://cherry.world.edoors.com/COBFKUqnUdBY one can obtain buffer hit ratio by following sql query:

SELECT round ((P2.variable_value / P1.variable_value),4),
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';

QUESTIONS

From what period are buffer hit ratio given by that query? From start of database engine till now?
Is there possibility to obtain buffer hit ratio from given period of time? (for example: last 10 minutes)

That's the Hit Rate since Uptime (Last MySQL Startup)

There are two things you can do to get the Last 10 Minutes

METHOD #1

Flush all Status Values, Sleep 10 min, Run Query

FLUSH STATUS;
SELECT SLEEP(600) INTO @x;
SELECT round ((P2.variable_value / P1.variable_value),4),
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';

METHOD #2

Capture innodb_buffer_pool_read_requests, innodb_buffer_pool_reads, Sleep 10 minutes, Run Query with Differences in innodb_buffer_pool_read_requests and innodb_buffer_pool_reads

SELECT
    P1.variable_value,P2.variable_value
INTO
    @rqs,@rds
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_read_requests'
AND P2.variable_name = 'innodb_buffer_pool_reads';
SELECT SLEEP(600) INTO @x;
SELECT round (((P2.variable_value - @rds) / (P1.variable_value - @rqs)),4),
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_read_requests'
AND P2.variable_name = 'innodb_buffer_pool_reads';

Give it a Try !!!


What's the difference between group roles and login roles? It looks like for single user I should create a login role and than respective group roles because I can't directly assign privileges on login role. Is it true? What's the purpose of such separation?

Also as I understand it's only possible to authenticate with login role. So what's the purpose of the password field in a group role? Or it's possible to authenticate as group role? Then why anyone would need login roles?

Also it looks like I can set both login and group roles in pg_hba.

All these questions are basically answered in the documentation.  To be specific:

  A role is an entity that can own database objects and have database privileges; a role can be considered a "user", a "group", or both depending on how it is used.

That implies that internally there is no difference between these, except the LOGIN option.  If you specify LOGIN (or use CREATE USER):

CREATE ROLE dezso WITH LOGIN PASSWORD 'bla';

dezso will become a login role, otherwise not.  You can specify passwords for non-login roles, too - if you later decide to make it a login role, use

CREATE ROLE non_login_role PASSWORD 'bla';

--later
ALTER ROLE non_login_role WITH LOGIN;

You are right when saying 'it's only possible to authenticate with login role'.  Note, however, that a password is not always necessary - if you use anything else than the password or md5 authentication methods.  On the other hand this means that a role without the LOGIN option set cannot log in in any way.

Finally, you may set rules in pg_hba.conf for non-login roles, but as it's used only for authentication, those roles will have no effect (unless you enable login later, as above).


overstating field size in database design I have some fields for my tables that are strings and at the moment, most of the field size have pretty high character limits. For instance, 100 char for the street name. Is there a penalty for using large field size? If I change the limit to 30 char for this field for instance, will there be a performance gain or an efficiency with size? There would about 50 fields that could be candidates for shrinkage.

Thanks for your suggestions.

If you're talking about varchar and nvarchar then no, there is no penalty for allowing a higher field length.

Some caveats to bear in mind, though:

There is a 2 byte overhead per row for variable length fields (per field).  If you have a very short field it may make more sense to use a CHAR.  Varchar(2) for instance actually uses between 2-4 bytes per row, while CHAR(2) always uses 2.
Very long fields cannot be indexed.  The maximum length for all fields in an index key set is 900 bytes.
If you allow more data than you expect, you will eventually get unexpected results.  If you allow 100 characters for a street name, at some point other data is likely to get into that field without you being aware of it (for instance the entire address).  If you had it appropriately sized, you would likely get an error on insert instead.
Allowing very wide rows could lead to page splits and fragmentation.  If you have a row longer than 8k it will need to be split onto multiple data pages.  A lot of these can really hurt performance.  Narrower in general is more efficient.


What memory should be given to the variables of my.cnf ? Installing a MySQL server is quite easy. However, after installing, configuring the server is an important task for a DBA.

Having 2GB of RAM and 250GB of disk space, how should I tune MySQL server so that it performs well?

There should also be a proper configuration of the InnoDB Engine.

Here are some of my recommendations for InnoDB.  In my experience, the buffer pool size is the most important because the more data you can keep in cache, the less time your system will spend using disk IOs.

http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
This is the buffer pool, where data and index are cached

innodb_buffer_pool_size=(In Mb, 80% of available RAM), for 2GB, I would put 1536.

Monitor your system carefully and be prepared to reduce this variable if you start to see swapping.

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_additional_mem_pool_size
This is where metadata is stored.  If you had a lot of tables, it would be useful to increase this.

innodb_additional_mem_pool_size=16M

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_data_file_path
Here you can define the location of the ibdata file.  If you can put the ibdata file on a separate disk that uses a seperate controller, it will reduce contention.  Be careful with this option.  Make sure that you read up on this variable before you use it.  If your ibdata file needs more than 29G, MySQL will stop whatever its doing and wait for you to add space.

innodb_data_file_path = ibdata1:128M:autoextend:max:29G

This manages in what increments the ibdata file is increased.  So if you put a really small number like 10MB, that would mean that innodb would spend a lot of time increasing its ibdata file if you had a lot of inserts.

innodb_autoextend_increment = 128M

where to put the transaction logs.  If you can put the transaction logs on a different disk with a separate controller, this will also reduce contention

innodb_log_group_home_dir = /tlog/

What is the size of the log files

innodb_log_file_size = 500M

25% of the buffer pool size, allows large transactions to run without the need to write to the disk

innodb_log_buffer_size = 384M

I also define where the temp directory should go.  Defines where MySQL creates temporary files.  Yet another way to reduce contention.

I typically leave enough space to allow reindexing of the largest table on the server.

tmpdir=/opt/tmp/

And an error log is useful just in case something goes wrong and you need to trouble shoot.

log-error=/var/log/mysql.err

Also, if you have more than 4 cpus, these variables can be increased
innodb_thread_concurrency = 8
innodb_file_io_threads=4

This has nothing to do with innodb.  There are other variables you can play with that have nothing to do with innodb.  I added them at the bottom, but they were taken directly from a server I use for testing.  Avoid just copying and pasting them.  Reading MySQL's documentation based on the version your using is the best way to make the right choices.

# Fine Tunning
key_buffer              = 512M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
join_buffer_size        = 64M
sort_buffer_size        = 16M
myisam_max_sort_file_size = 1024M
tmp_table_size = 256M
max_heap_table_size = 257M
myisam_sort_buffer_size=1024M

table_cache            = 1000
thread_concurrency     = 8
# * Query Cache Configuration
#
query_cache_limit       = 16M
query_cache_size        = 16M

skip-name-resolve


How to avoid errors in server log when conflict fails within a transaction before retry? We have a table such as
create table pages (
  id uuid primary key not null,
  parent_id uuid not null references pages(id),
  shortname text not null,
  shorturl text not null,
  constraint globally_unique_shorturl unique (shorturl)
  constraint locally_unique_shortname unique (parent_id,shortname)
)

where the logical intent is to keep a collection of web pages where the full URL for each page is constructed from shortname fields defined by parent_id references. Root of the system points to itself with parent_id. As such, any given child must have unique shortname per parent (constraint locally_unique_shortname). In addition, we have globally unique shorturl (think bit.ly) that obviously must be globally unique for the whole table. The target is to keep both shortname and shorturl as short as possible and as such, we generate short random identifiers for shorturl. In addition, the shortname is selectable by user to allow system to create semantic URLs for every page.
When I'm trying to add a new page to the system (or modify existing page), we do it in a transaction and we create new SAVEPOINT immediately before trying to add a new page. If we get error
ERROR: duplicate key value violates unique constraint 'XYZ'

we know that we need to rollback to savepoint and then figure out if shorturl or shortname should be modified before retrying. Note that if shorturl fails, we can generate a new one automatically, but if shortname fails, we need to prompt user for a new value, so we really need to know which constraint failed. (In addition, this query fails pretty seldom so I'd prefer not having to try SELECT with the new value to figure out if it's already taken because this mostly succeeds without any extra queries. Also note that such SELECT would still race against other concurrent transactions and I'd still end up with the same issue every now and then.) We're using serializable transactions, in case it makes a difference.
However, this causes PostgreSQL to always store this error in the server log even when the code obviously handles this correctly (rollback is emitted immediately the query fails). These errors spam the log to make other possible real problems harder to notice. Obviously, I can just fall back to filtering the log before trying to monitor it, but that would feel pretty poor solution.
To workaround this, Disable log ERROR: duplicate key value violates unique constraint suggests that I should use syntax INSERT ... ON CONFLICT DO NOTHING. This would be otherwise fine (I could try to do insert and if no rows were affected I can assume that I have a conflict and nothing will be added to server log) but I'm missing info about which constraint failed when there are multiple possible constraints.
Is there any nice way to solve this without having to modify source code of PostgreSQL or load some custom extension in PostgreSQL?

You have two unique constraints, and you say that the one on shortname causes an error only rarely.
So use
INSERT ... ON CONFLICT ON (shorturl) DO NOTHING;

Then check if you got an error and how many rows were inserted.

if you got an error, prompt the user for a new shortname

if no row was inserted, generate a new shorturl

Then you will only get error messages about duplicate shortnames in your log, which should reduce the log spam to a level that is not bothersome.


Using `min_active_rowversion` for global temporary tables I use global temporary tables for easy integration testing of my SQL Server backed application.

But the function min_active_rowversion does not seem to include global temporary tables, like seen here:

using (var connection = new SqlConnection("data source=.;Integrated Security=True"))
{
    connection.Open();

    connection.Execute("create table ##mytable ( Id int, rv rowversion )");

    var a = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

    var x = ToUInt64(connection.Query<byte[]>("insert into ##mytable (Id) output Inserted.rv values (1)").Single()); // => 22647

    var b = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001
}

I can't see this limitation documented anywhere. Can this be made to work? And if so, how?

The explanation would appear to be that the global temporary table is in fact in TempDB, not the database your code is running against. This is because the MIN_ACTIVE_ROWVERSION() function is scoped to the database level.

The min_active_rowversion() function seems to work when you use the following code

USE [db]

CREATE table ##mytable ( Id int, rv rowversion )

USE [tempdb]

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

dbfiddle


Does the PostgreSQL COPY command have the option of choosing with fields to map the CSV columns to? Does the PostgreSQL COPY command have the option of choosing with fields to map the CSV columns to?

The PostgreSQL COPY command appears to expect that the target table matches its columns exactly. Am I missing something or is that how it actually works?

Is there some alternative command that enables that?

It is absolutely possible - the ever helpful documentation comes to the rescue, again:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

Which means you can do something like this:

COPY my_table (mt_id, mt_name, mt_created_by, ...)
    FROM 'filename' [...]

What you cannot do is to refer columns of the CSV file.  To overcome this, one can create an intermediate table with the matching number and type of columns, do the COPY into it, then do an INSERT ... SELECT ... to the final destination.  Based on an important remark from Patrick7, the intermediate table can be defined as UNLOGGED, saving a lot of WAL overhead when the table is big.


Elegant way for concatenation I am looking for the most elegant solution to prevent the addition of the last/ final separator ';' after the last row.

For example the result should be

  'Peter,Paul,Jill'

instead of

  'Peter,Paul,Jill,'

DECLARE @p varchar(max) = '';
SELECT @P = @P + col2 +',' FROM tab1 WHERE coli = 1
RETURN @P

The solution

RETURN LEFT(@P, LEN(@P) -1);

works only if @P is long enough and so is not a solution...

How can I solve this elegant and reliable?

I think a lot of people solve this with the STUFF function.  I found an example from this link and adapted it.

SET NOCOUNT ON

DECLARE @Table TABLE (StringText VARCHAR(100))
DECLARE @ConcatString VARCHAR(100)

INSERT INTO @Table VALUES ('Tom')
INSERT INTO @Table VALUES ('Dick')
INSERT INTO @Table VALUES ('Harry')

SELECT @ConcatString = STUFF((
            SELECT ',' + StringText
            FROM @Table
            ORDER BY StringText
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

PRINT @ConcatString

As Dan Guzman points out in the comments, specifying TYPE and value method avoids entity references in the resultant string for special characters like ampersand.


Failed Upgrade from SQL Server 2005 sp2 to SQL Server 2005 sp4 SQL Server Standard 2005 sp2 32bit running on Windows 2003 Standard 32bit service pack 2.

I attempted a service pack upgrade from sp2 to sp4 using SQLServer2005SP4-KB2463332-x86-ENU.exe and received the following error:

Property(S): UpgradeInstruction = Connect to SQL instance MYSERVER as sysadmin
Launch SQL statement USE master
Launch script file d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\sysdbupg.sql
Launch script file d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\DbEngine_hotfix_install.sql
Launch script file d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\repl_hotfix_install.sql
Stop service MSSQLSERVER
Property(S): SqlUpgradeMessage = SQL Server Setup failed to parse the SQL script "d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\DbEngine_hotfix_install.sql". The error code is The system cannot find the file specified.
. To continue, correct the problem, and then run SQL Server Setup again.

The file d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\DbEngine_hotfix_install.sql is missing and I have tried this upgrade from two different SQLServer2005SP4-KB2463332-x86-ENU.exe files.

After the service pack install completed, I was able to start SQL Server and the version was 9.0.5000.  There were no other errors in the logs, but I did rollback the server using a vmware snapshot taken before installing SP4.

I googled around but didn't find anything useful.

Why would the file DbEngine_hotfix_install.sql file be missing from the service pack installation ?

Almost every time I've had an SP installation issue on a stand-alone server, it was because the resource database was moved or it was not in the same directory as the master database.

Many articles I'm finding correlate your error to this exact conclusion including this one.

I would start there and if you find it to be the case, move the system databases back to where the resource database is.

If that doesn't work, try re-applying SP2.  If all of the components weren't patched during the SP2 upgrade, you could experience this issue.

Lastly, verify service account permissions and permissions of the account you are running the SP4 with.


MariaDB Columnstore unexplained behaviour using CASE We were updating column value based on col 'string_hash_id' value, but we couldn't get through:
 Error Code: 1815. Internal error: CAL0002: Update Failed: TreeNode::getDecimalVal: non-support conversion from string

sql:

UPDATE table1 JOIN table2 USING(id)
SET table1.calc_col = table_1.col1 * table_1.col2 * table.col3 * ((100 - (
CASE col_id WHEN 'string_hash_id' THEN 2.00 ELSE 1.5 END)) * 0.01)

col1 is signed int, rest of cols are decimal

We thought that if we cast these cols it will work but nothing happens error was the same. But when we changed case values to string it works but it does not make any sense.

  THEN 2.00 ELSE 1.5 ==> THEN '2.00' ELSE '1.5'

UPDATE table1 JOIN table2 USING(id)
SET table1.calc_col = table_1.col1 * table_1.col2 * table.col3 * ((100 - (
CASE col_id WHEN 'string_hash_id' THEN '2.00' ELSE '1.5' END)) * 0.01)

And my question is, how is this possible and why it shows TreeNode::getDecimalVal: non-support conversion from string when there is no string.

And works without issues when there is string.

WHEN 'string_hash_id' THEN

-->

WHEN `string_hash_id` THEN

Apostrophes and double-quotes are for strings; backtics are for column names.


Importing a mysql database after drop filling up limited / in /tmp deleted file - A problem? I'm importing a fairly large set of databases into a system which has a / partition only 10GB large. After a previous failed import, I dropped the database, but noticed /var/lib/mysql didn't exactly empty out - Now I'm watching the root partition fill up with this file:

$ sudo lsof / | grep deleted
mysqld    53058     mysql   41u   REG  253,0 951599104   5523 /tmp/MLR7HVLj (deleted)

This file is growing large enough (2GB) that I'm starting to worry that it'll crash my system. Is this a known problem (Old centos 6 mysql version 5.1) with trying to import a database after it's been dropped already, and is mysql aware of limited space in the partition it's filling up with a deleted file?

SHOW FULL PROCESSLIST -- there is probably a complex query running, creating a huge tmp table.

Kill that process (go into mysql commandline tool, do SHOW PROCESSLIST to get the pid, then do kill, in the tool, with that number.)

Then scratch your head as to why it is taking so much room.  A likely case:

SELECT ... FROM a, b;

and failing to restrict how a and b should be JOINed.  This is called a CROSS JOIN; it can be huge.


Find tables with multiple indexes on same column I am having an issue with scaffolding PostgreSQL DB because npgsql does not handle tables with two index on the same column well. Here's a link to my project on github.

I am trying to find these indexes / tables so I can exclude them from my scaffolding command.

Example:

CREATE TABLE public.altreproc (
  apcod integer NOT NULL,
  aptip character(3),
  apcam character(3),
  CONSTRAINT altreproc_pkey PRIMARY KEY (apcod)
);

CREATE INDEX ialtreproc1
  ON public.altreproc
  USING btree
  (aptip COLLATE pg_catalog."default");

CREATE INDEX ialtreproc2
  ON public.altreproc
  USING btree
  (apcam COLLATE pg_catalog."default");

CREATE INDEX ialtreproc3
  ON public.altreproc
  USING btree
  (aptip COLLATE pg_catalog."default" DESC);

My problem is when a table has 2 different index with the same column (one ASC and one DESC);
I am trying to query tables / indexes like this in db which I am trying to scaffold.

My goal is to find tables which contain more then one index on same column.

This query retrieves a complete list of indexes build on the same (list of) table column(s in the same order):
SELECT indrelid::regclass::text AS table
     , idx_columns
     , indexrelid::regclass::text AS index
FROM   pg_index i
     , LATERAL (
   SELECT string_agg(attname, ', ') AS idx_columns
   FROM   pg_attribute
   WHERE  attrelid = i.indrelid
   AND    attnum  = ANY(i.indkey)  -- 0 excluded by: indexprs IS NULL
   ) a
WHERE  EXISTS (
   SELECT FROM pg_index
   WHERE  indrelid = i.indrelid
   AND    indkey = i.indkey
   AND    indexrelid <> i.indexrelid  -- exclude self
   )
AND    indexprs IS NULL -- exclude expression indexes
ORDER  BY 1, 2, 3;

pg_index.indkey is data type int2vector and contains, quoting the manual:

an array of indnatts values that indicate which table columns this
index indexes. For example a value of 1 3 would mean that the first
and the third table columns make up the index key.

So the match on this column asserts the same columns in the same order.
Sort order, operator class, collation or any other decorators can differ.
Constraints:

Multicolumn indexes only qualify if all index columns match in the same order.
Indexes including any expressions other than plain table columns are excluded.

Aside: index ialtreproc3 in your example is dead freight, you should remove it. Postgres can scan indexes in both directions with practically the same speed. Exception: multicolumn indexes with mixed sort order. See:

Optimizing queries on a range of timestamps (two columns)

While the only difference is sort order, it makes no sense to maintain multiple btree indexes on the same single column.


Is there a data type I can use for RA & Dec in astronomy I need to have fields in my database for the following:

RA (hh:mm:ss.00)
Dec (Degrees:arcseconds:arcminutes.00)

I need to have the facility to search for a range within each field.

Any ideas?

If you just want to store the coordinates, for later searching, your best bet is to convert from degrees-minutes-seconds to decimal degrees, and store as DECIMAL. Here is an answer giving mysql functions that do that conversion, in both directions.

If you want to do more complex things like "Is this point within the constellation Orion" then you probably need to use spatial data extensions, as @RLF suggested in a comment.
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html
http://dev.mysql.com/doc/refman/5.0/en/using-spatial-data.html


Select distinct multiple columns with one result column I have a table like the following:

CREATE TABLE aschema.atable
 (
  id       BIGSERIAL,
  col_a    aschema.anenum,
  col_b    aschema.anenum,
  col_c    aschema.anenum
)

and I would like to do something like

SELECT DISTINCT unnest(array_agg(col_a, col_b, col_c)) as anenum_value
  FROM aschema.atable
  WHERE anenum_value IS NOT NULL

but I do not know if it makes sense.
Maybe with an index over each of the three column I can simply select independently the three columns like that:

SELECT DISTINCT value FROM (
SELECT col_a as value
  FROM aschema.atable  WHERE col_a IS NOT NULL
UNION
SELECT col_b as value
  FROM aschema.atable  WHERE col_b IS NOT NULL
UNION
SELECT col_c as value
  FROM aschema.atable  WHERE col_c IS NOT NULL
)

But I am not sure about the performance.

Actually I have rows like

id  col_a  col_b  col_c
1   ABD    CDE    XYZ
2   CDE    null   null
3   ABD    null   null
3   FGH    LMN   null

And I expect as a result

ABC
ABD
CDE
FGH
LMN
XYZ

Any recommendation and good example?

SELECT DISTINCT value FROM (
SELECT col_a as value
  FROM aschema.atable  WHERE col_a IS NOT NULL
UNION
SELECT col_b as value
  FROM aschema.atable  WHERE col_b IS NOT NULL
UNION
SELECT col_c as value
  FROM aschema.atable  WHERE col_c IS NOT NULL
)

The UNION there is UNION [DISTINCT]. You don't need to wrap it in SELECT DISTINCT. Though the previous pattern may be faster if it matters if you do UNION ALL, and wrap that in one SELECT DISTINCT FROM ()

SELECT col_a as value
  FROM aschema.atable  WHERE col_a IS NOT NULL
UNION
SELECT col_b as value
  FROM aschema.atable  WHERE col_b IS NOT NULL
UNION
SELECT col_c as value
  FROM aschema.atable  WHERE col_c IS NOT NULL

Why doesn't that work to do what you want?

If you ever find yourself writing a query like this though, I would think the problem would be in the schema itself.


SQL query question - tricky one I have two tables, one with a single payment information and an id (LFN), on the other hand there are payment positions:
single payment:

payment positions:

goal is to get a table like this:

with our_recno there is one match, through customer id i can find all associated positions. I do this with a subquery. The best solution I get is this:

Do you have an idea how I can repeat the LFN Number instead of getting NULL?
Thank you very much for your ideas.

You have to build a bridge, which you should already have, so you should think about your design
The only useful and not redundant information that has the table payment is LFN and to be honest put this into the payment_positions, every information you want you can get from payment_positions then:
CREATE TABLE [payment] (
  [LFN] INTEGER,
  [OUR_RECNO] INTEGER,
  [TOTAL] FLOAT
);

INSERT INTO [payment]
  ([LFN], [OUR_RECNO], [TOTAL])
VALUES
  ('1234', '36622016', '71.89');
GO

1 rows affected

CREATE TABLE [payment_positions] (
  [OUR_RECNO] INTEGER,
  [CUSTOMER] INTEGER,
  [TOTAL] FLOAT
);

INSERT INTO [payment_positions]
  ([OUR_RECNO], [CUSTOMER], [TOTAL])
VALUES
  ('36617800', '2566303', '47.34'),
  ('36622016', '2566303', '10.51'),
  ('36625936', '2566303', '14.04');
GO

3 rows affected

SELECT [CUSTOMER],[OUR_RECNO]
FROM [payment_positions]
WHERE [OUR_RECNO] IN (SELECT [OUR_RECNO] FROM [payment] )

CUSTOMER | OUR_RECNO
-------: | --------:
 2566303 |  36622016

SELECT r.[LFN], ac.[OUR_RECNO], ac.[CUSTOMER], ac.[TOTAL]
FROM [payment] AS r
INNER JOIN
(SELECT [CUSTOMER],[OUR_RECNO] FROM [payment_positions] WHERE [OUR_RECNO] IN (SELECT [OUR_RECNO] FROM [payment] )) AS t1
ON r.[OUR_RECNO] = t1.[OUR_RECNO]
INNER JOIN [payment_positions]  AS ac
ON ac.[CUSTOMER] = t1.[CUSTOMER]

 LFN | OUR_RECNO | CUSTOMER | TOTAL
---: | --------: | -------: | ----:
1234 |  36617800 |  2566303 | 47.34
1234 |  36622016 |  2566303 | 10.51
1234 |  36625936 |  2566303 | 14.04

db<>fiddle here


How can I position the element generated by XMLTEXT in a FOR XML query? I'm trying to use FOR XML to simulate an OData feed. One of the requirements I have is that somewhere I need an XML element which is constant for all records; I was planning to use the XMLTEXT directive for this.

My XML needs to look like this; for every record in the database,

<entry>
    <id>1</id>
    <test type="dummy" />
    <!-- some more nested elements -->
</entry>

When I use the following query, the <test> element appears before the <id> element, even though the declaration appears after it:

CREATE TABLE #temp (id int);
INSERT INTO #temp VALUES (1), (2);

SELECT
  1    AS Tag,
  NULL AS Parent,
  id   AS [entry!1!id!ELEMENT],
  '<test type="dummy" />'
       AS [entry!1!test!XMLTEXT]
  -- other columns
  FROM #temp
  FOR XML EXPLICIT;

DROP TABLE #temp;

This gives the following results (fiddle):

<entry>
    <test type="dummy"/>
    <id>1</id>
</entry>

Am I doing something wrong here, or is this a limitation of XMLTEXT? A workaround might be to treat it as a regular nested element, but would that work even though I have more nested elements further down in the <entry> element?

After some more fiddling, I found out that there is an XML directive which does the job (if I omit the attribute name, test):

SELECT
  1    AS Tag,
  NULL AS Parent,
  id   AS [entry!1!id!ELEMENT],
  '<test type="dummy" />'
       AS [entry!1!!XML]
  FROM #temp
  FOR XML EXPLICIT;

This produces the results I want:

<entry><id>1</id><test type="dummy" /></entry>

(demo)

This directive is mentioned briefly here but I couldn't find more documentation or any examples.


Redundant plans in cache because of missing type length Today I realized that our sql server's plan cach is stuffed with hundreads and thousands of nearly identical Compiled Plans.

The total number of plans is around 30.000 on a live system consuming around 4500 MB.
Lookin at them there are thousands that are nearly same.

Some samples:

(@ID uniqueidentifier,@GSS nvarchar(663))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(664))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(665))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(666))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(669))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID

(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(10))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID
(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(11))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID
(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(12))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID
(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(13))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID

The application uses sp_executesql almost everywhere and passes values as parameters, that is why I was sure the plans are getting reused.

But now it looks like the app does not care at all for string type's length and the length is added automatically depending on the in-fact length of the values causing a different plan for each combination of string length for each passed string value. So mostly it looks like the UPDATE and INSERT statements are the issue here. I guess some developers have saved some time at this point simply leaving the size away.

The use counts of these redundant UPDATE/INSERT kinds of plans are relatively low (ofthen 1, some up to 10, only few of then up to 40 usages).

Their size ranges between 0,05 MB and 2 MB. It is hard to tell exact values and amounts for possible savings because the statements can't be sorted properly because of the changing length constants in between. However I think an average plan size here is 0,17 MB in around 15000 Update/Insert plans where I guess a minimum of 80% could be saved, roughly that might be a potential saving of 2040 MB of the 2550 MB actually in use for those 15000 plans.
This would reduce the total plan cache usage from 4500 MB to sth. around 2500 MB.

Looks like we could easily save some memory here, right?

We are talking about sql servers with not a lot of memory (32 GB) that could really benefit from some additional free memory, however, I guess this is also a question of genreal system health.

So my question is, what can I do about it? Do I have ANY options on database side to get control about this? Are there any adhoc query optimization options I do not know yet to let's say ignore this?

Or is it really required that the developer correctly adds the type and typelength to the input parameters?

In case of (n)varchar types, the leading length is already defined in the table definition, so are there any downsides to expect from using exactly those?

Additional Info:

I meanwhile found K.Tripps post about plan cache getting wild... and her query shows me that I have 1500 MB & 20300 plans with use count of 1 in cache...

Is "optimize for ad hoc workloads" a silver bullet in my particular case?

Some more Info:
I fould out that the queries are created by SqlDataAdapter and SqlCommandBuilder. The Parameter Size for all the created parameters are not derived anyhow from the origin table by the commandbuilder. If they are left unset, the size of the actual values passed in us used as size value. This can be found here: "If not explicitly set, the size is inferred from the actual size of the specified parameter value."

Take a look at Plan cache, adhoc workloads and clearing the single-use plan cache bloat.

To summarize:

If you're running SQL Server 2008 and you have cache being wasted by
single-use plans, be sure to use the new "optimize for adhoc
workloads."
If you still find that you're wasting 100s of MB or GB of cache,
consider creating a job that programmatically checks cache and then
clears the 'SQL Plans' from cache based on one of the options/code
below.

  OPTION 1 - Clearing JUST the 'SQL Plans' based on just the amount of
  Adhoc/Prepared single-use plans (2005/2008):

DECLARE @MB DECIMAL(19, 3)
    ,@Count BIGINT
    ,@StrMB NVARCHAR(20)

SELECT @MB = sum(cast((
                CASE
                    WHEN usecounts = 1
                        AND objtype IN (
                            'Adhoc'
                            ,'Prepared'
                            )
                        THEN size_in_bytes
                    ELSE 0
                    END
                ) AS DECIMAL(12, 2))) / 1024 / 1024
    ,@Count = sum(CASE
            WHEN usecounts = 1
                AND objtype IN (
                    'Adhoc'
                    ,'Prepared'
                    )
                THEN 1
            ELSE 0
            END)
    ,@StrMB = convert(NVARCHAR(20), @MB)
FROM sys.dm_exec_cached_plans

IF @MB > 10
BEGIN
    DBCC FREESYSTEMCACHE ('SQL Plans')

    RAISERROR (
            '%s MB was allocated to single-use plan cache. Single-use plans have been cleared.'
            ,10
            ,1
            ,@StrMB
            )
END
ELSE
BEGIN
    RAISERROR (
            'Only %s MB is allocated to single-use plan cache – no need to clear cache now.'
            ,10
            ,1
            ,@StrMB
            ) —

    Note: this IS ONLY a warning message
        AND NOT an actual error.
END
GO

OPTION 2 -

Clearing ALL of your cache based on the total amount of wasted by single-use plans (2005/2008):

DECLARE @MB DECIMAL(19, 3)
    ,@Count BIGINT
    ,@StrMB NVARCHAR(20)

SELECT @MB = sum(cast((
                CASE
                    WHEN usecounts = 1
                        THEN size_in_bytes
                    ELSE 0
                    END
                ) AS DECIMAL(12, 2))) / 1024 / 1024
    ,@Count = sum(CASE
            WHEN usecounts = 1
                THEN 1
            ELSE 0
            END)
    ,@StrMB = convert(NVARCHAR(20), @MB)
FROM sys.dm_exec_cached_plans

IF @MB > 1000
    DBCC FREEPROCCACHE
ELSE
    RAISERROR (
            'Only %s MB is allocated to single-use plan cache – no need to clear cache now.'
            ,10
            ,1
            ,@StrMB
            )
GO


SQL Server stored procedure naming We have started naming our stored procedures as [16_TestStoredProc]. Are there any implications naming a stored procedure like this?

I am not going to explain why we are doing this. It is not that I have issues with this, but would to any implications.

There are no technical issues with this.

It isn't going to make any difference to SQL Server.

From a usability point of view identifier names that begin with a number must always be quoted so

exec some_schema.16_TestStoredProc

would not be valid and you must always use

exec some_schema.[16_TestStoredProc]

Also when viewing the stored procedures alphabetically sorted - e.g. in Object explorer this prefix may well not be helpful in trying to find a stored procedure and as they are ordered lexicographically it may be somewhat inconvenient that 18_TestStoredProc precedes 2_TestStoredProc.

  I am not going to explain why we are doing this

Well perhaps you have good reasons. As you aren't going to share them it isn't possible for us to comment on them.


Database collation different from tempdb collation I just received the source code and database of an application from my client (it was developed by another company, from a different country) and the application is throwing some exceptions related to the objects collation:

  Cannot resolve the collation conflict between
  "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal
  to operation.

I saw that in my case this is caused when a Stored Procedure creates a #temp table and this #temp table is used in a comparison with tables from the application (the application database uses SQL_Latin1_General_CP1_CI_AS and tempdb uses Latin1_General_CI_AS).

I'm adding COLLATE SQL_Latin1_General_CP1_CI_AS to the CREATE TABLE #TEMPTABLE statement, however this database has many Stored Procedures that may use #temp tables.

How can I fix this faster without breaking the other databases/applications?

Does this database need to be on this Instance? If not, then as @Max Vernon suggested in a comment on the question, you could install this on an Instance that is created with a default Collation of SQL_Latin1_General_CP1_CI_AS. Perhaps you could even get away with using SQL Server Express Edition, if the database is < 10 GB and doesn't require any Enterprise Edition features.

But if that is not an option, then what you are already doing,

  adding COLLATE SQL_Latin1_General_CP1_CI_AS to the CREATE TABLE #TEMPTABLE statement

is pretty much the only reliable thing you can do.

Since you do not want to adversely affect the operation of the app that was delivered by your client, you cannot change the Collation of the new database. And in fact, changing the DB's default Collation wouldn't fix your current problem as it wouldn't update the Collation of the column in the tables in that DB. It would only affect the Collation of string literals and variables, and you would only notice a difference when those two things are the only things being compared (since the Collation of a column would override the implied Collation of string literals and variables).

Since you don't want to adversely affect your existing DBs on your SQL Server, it wouldn't make sense going through the trouble of changing the current Instance's Collation to SQL_Latin1_General_CP1_CI_AS.

Considering that the new DB and related code were developed (and hopefully tested!) on an Instance that had SQL_Latin1_General_CP1_CI_AS as the default Collation, you really need to make it so that the queries in the new DB behave as they did in the environment in which they were created. You can only do this by forcing the Collation of string columns in temporary tables by adding the COLLATE clause (as you doing). Well, technically speaking you can change the temporary tables into table variables since their default Collation will be taken from the current database, but that is a more intrusive change as the Query Optimizer doesn't handle table variables in the same way as temporary tables (and there are other notable differences).

In the COLLATE clause, you could use either DATABASE_DEFAULT or continue using SQL_Latin1_General_CP1_CI_AS. Both are equivalent here since it is not likely that the Collation of the new DB will ever change (and if it does, that means you will have a new DB from the client that does not include your changes anyway ;-).

FYI: There are not many differences between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS. They both use the same Locale -- 1033 -- and same Code Page -- 1252. The main differences are:

The SQL_ Collations have been deprecated for a long time and should be avoided if possible
They treat apostrophes and dashes within words differently
The non-SQL_ Collations allow for some expansions / normalization offered by Unicode that will equate more characters than the SQL_ Collations.


Troubleshooting spid suspended ASYNC_NETWORK_IO Recently we moved our database to a new server

The old server was running

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) Jul 12 2019 17:43:08
Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)

The new server is running

Microsoft SQL Server 2019 (RTM-CU2) (KB4536075) - 15.0.4013.40 (X64) Feb 3 2020 16:40:57
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Linux (CentOS Linux 8 (Core))

We have a C#  Windows service that uses the database. It is intermittantly stopping or running extremely slow.

The windows service is using .Net Framework 4.7.2 and it calls a library written in .net standard 2.0 which also makes sql calls.

I found this question on why the status of a SPID is suspended and see that our Suspended Spids have wait_type ASYNC_NETWORK_IO

According to the docs this

  Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.

Restarting the windows service does temporarily solve the problem. However this does not help me troubleshoot the cause of the problem.

Mostly I am using EntityFramework 6.4

[Update]

I have taken to running

dbcc inputbuffer(sessionId)

to see the last running SQL

ASYNC_NETWORK_IO generally indicates that SQLServer has results ready to return to the client, but the client is not requesting them as fast as SQLServer can deliver them, or the network is going slow.

A common scenario with C# is if you are consuming results via an IEnumerable<> with a bunch of slow processing per row coming from the database. If you're not returning millions of rows, try putting a .ToList() or something in there to buffer all the results from the database prior to processing them.


`greatest` function makes the query run much slower I have a query which has the following WHERE clause (note that this is a simplification. In the actual case the second parameter would be passed from code. But the following explanation applies to when executing from sql developer).

WHERE O_DATE>= greatest(sysdate-1, sysdate - 30)

which takes about 100 seconds, whereas

WHERE O_DATE>= sysdate-1 takes very little time.

I would assume both to take the same amount of time because greatest(sysdate-1, sysdate - 30) should resolve to sysdate-1.

Can someone tell me why the difference in execution time? And a way to work around it?

To add more context I am using the logstash jdbc plugin and trying to use the sql_last_value

Look at the EXPLAIN PLAN and you will see the difference.
Oracle uses a cost based optimizer that takes into account the actual values that are used in the query to come up with the optimal execution plan.
I have not tested it in Oracle, but it is most likely that sysdate-1 is evaluated at compile/optimize time, and the optimizer is aware that your predicate only covers one day, and therefor it will make sense to use an index for example.
When you 'hide' the value in a function, the optimizer has no way to tell what value you use, as the function is only evaluated at execute time. If the general density of the column suggests that the average value will return many rows, and will benefit from a full table scan for example, you will get a full scan even for 1 day's worth of data, which may be less optimal for the value you are using.
As a rule of thumb - don't 'hide' predicate values from the optimizer by placing them in functions.