How do I check if a VARCHAR column has control characters in it? How do I check if a VARCHAR(MAX) column has ASCII Control Characters in SQL Server?

Specifically I am looking for the presence of 0x01 SOH. Will this code work, it is copied from an example where they check for LF character i.e CHAR(13)

SELECT * FROM notes where content LIKE '%' + CHAR(1) + '%';

In any case I would like to get a generic query  for all the Control Characters instead of a hard-coding like this.

Well I found others that might have had a similar questions,

Before I go on... dont forget about Collation.

That alone can make your life a nightmare,
If you have differences between Server level / Database level / Column level / Expression level, can cause strange behaviour and incorrect query results,if not properly managed.

I would suggest a regex expression to find what you are looking for.

Found on: How to compare Unicode characters in SQL server?

SELECT COUNT(*) FROM t_question WHERE question LIKE N'%é%'

Found on: How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?:

CREATE TABLE dbo.Numbers
(
    number  INT NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (number)
)
GO
DECLARE @i INT

SET @i = 0

WHILE @i < 1000
BEGIN
    INSERT INTO dbo.Numbers (number) VALUES (@i)

    SET @i = @i + 1
END
GO

SELECT *,
    T.ID, N.number, N'%' + NCHAR(N.number) + N'%'
FROM
    dbo.Numbers N
INNER JOIN dbo.My_Table T ON
    T.description LIKE N'%' + NCHAR(N.number) + N'%' OR
    T.summary LIKE N'%' + NCHAR(N.number) + N'%'
and t.id = 1
WHERE
    N.number BETWEEN 127 AND 255
ORDER BY
    T.id, N.number
GO

Some Documentation from Microsoft: Pattern Matching in Search Conditions

And alternatively: Search Text with Regular Expressions

In my experince with mysql there are build in functions like so:

SELECT '\0x01' REGEXP '[[:graph:]]'; -- returns true
SELECT '\0x01' REGEXP '[[:print:]]'; -- returns false

As a start, I Hope this helps!


How to aggregate over a "multi source" with recursive, in postgres? Imagine I have a table "departments" like this:

name TEXT
parent_department TEXT (nullable)

I also have a "budgets" table like this:

department_name TEXT
budget INTEGER

I need to "traverse" this departments table from "leaf to root", following the parent department column. The end result is that I need to sum up the budgets of each department, but the twist is that the "budgets" table that I'll be using at any given time is very sparse (10s to 100s of rows), while departments is very dense (100m rows).

Normally, I would approach this by creating a "with recursive" CTE that "flattens" the department relationship into a temporary table of just "root department", "leaf department". Then I would join that table on leaf department to budgets, and group by root. The problem is that the "department" table is FAR too big now (the analogy to departments breaks down here a little bit I admit, no company has >100m departments).

Concretely, if I had departments like this (child -> parent):

C -> B
B -> A
A -> NULL
E -> D
D -> NULL
F -> G
G -> NULL

And budgets like this:

A 1
C 3
E 5

I would want to get as output:

A 4
D 5

And ideally, F and G, for example, would never be touched, because there are hundreds of millions of rows like F and G that shouldn't come up. But in my current query, they do. How can I fix this so that I only "traverse" the departments that I actually need to?

It took a while, but I figured it out:

WITH RECURSIVE
initial AS (
    SELECT
        department_name,
        department_name,
        FALSE
    FROM
        budgets
    GROUP BY department_name
),
tmp (child, parent, root) AS (
    SELECT
        *
    FROM
        initial
    UNION ALL
        SELECT
            tmp.child,
            CASE WHEN
                departments.parent_department IS NULL THEN tmp.parent
                ELSE departments.parent_department
            END,
            departments.parent_department IS NULL
        FROM
            tmp
            INNER JOIN departments
                ON tmp.parent = departments.name
        WHERE
            NOT tmp.root
)
SELECT
    tmp.parent,
    SUM(budget)
FROM
    tmp
    INNER JOIN budgets
        ON tmp.child = budgets.department_name
WHERE
    tmp.root
GROUP BY tmp.parent


Best way(s) to time ad hoc queries and stored procedures What are the most accepted ways to time your queries, whether they are ad hoc or stored procedures?

Is it more accurate to use SQL Profiler than SET STATISTICS TIME ON?

Profiler is easier to understand (all in one line) and will capture hidden IO, CPU etc in UDFs (scalar and multi-statement TVFs)

SET STATISTICS TIME ON and SET STATISTICS IO ON will not show you IO and CPU from scalar UDFs and multi-statement TVFs: only the "outer" query statistics

From SO:

SQL Server Profiler discrepancy
Table Valued Function where did my query plan go?
Does query plan optimizer works well with joined/filtered table-valued functions?

And no doubt some folk will disagree with me, so here is Adam Machanic on the subject too

Scalar functions, inlining, and performance


How should I distribute my tables for better performance from fragmentation point of view I have a database on SQL Server 2008 R2 with about 20 non-indexed tables that are continuously  undergoing INSERTs AND DELETEs.  There are about 400,000 records @ 4KB/record being inserted or deleted each time.

This much action on the tables will undoubtedly cause fragmentation.

Along with the previous tables, I have about other 20 tables that hardly change inside the same filegroup.  They are historic tables and grow month after month by approximately 400,000 records @ 4K/record per month.

Even if I make the filegroup files big enough to hold the data and reduce physical fragmentation by disabling auto growth, the internal fragmentation will eventually get out of control, causing performance to drop.

I am not talking about physical fragmentation of the database files either, nor vertical/horizontal fragmentation of tables.

Should I make one filegroup for the small change tables and one for the high change tables or should I distribute the tables evenly?  For example; 10 high change tables with 10 small change tables in one group and the other half in a second group?

I want to reduce performance losses due to fragmentation of the tables (heaps).

Moving 400k records per month into and out of tables shouldn't cause all that many problems from a performance perspective.  As SQL removes all the data from pages those pages will be reused for something else.

You shouldn't be getting inserts or updates in the middle of pages all that often as SQL should just be putting the new rows at the end of the table and deleting the old rows from where ever they are. (I'm assuming you are just deleting the oldest records when you delete records.)

If it is that much of a worry, I would put the historical tables into one filegroup and the other tables into another file group.


Trying to limit stored procedure results based on date I have the following stored procedure:

ALTER PROCEDURE [dbo].[TW_spRPTAllReceiverInventory]
@SiteId varchar(50),
@ReceiverId int,
@UserName varchar(50)

AS

BEGIN

DECLARE @SQL varchar(8000),
        @Index int,
        @Value varchar(500),
        @Source varchar(100),
        @tmp DATETIME

SELECT @Index = CHARINDEX('Initial Catalog=', ConnectionString) + 16,
       @Value = ConnectionString
FROM   CustomerSetting
WHERE  SiteId = @SiteId

SET @Source = '['
               + SUBSTRING(@Value , @Index, CHARINDEX(';', @Value, @Index) - @Index)
               + ']'

SET @SQL = 'SELECT R.SiteId, L.* FROM '
           + @Source
           + '.[dbo].[vwReceiver] R JOIN '
           + @Source
           + '.[dbo].[vwInventory] L ON R.ReceiverId = L.ReceiverId
        WHERE R.ReceiverId = '
           + CAST(@ReceiverId as varchar)
           + ' AND L.ModifiedUserName = '
           + Cast(@UserName as varchar)

EXEC(@SQL)

END

What I need to do is filter the results based on one of the columns (the column is ModifiedDate, which is a DateTime column) to only show records created today. How do I accomplish this?

Here is one method for doing that.  But it only gets records that are for the current day.  If you needed for the last 24 hours, it would be slightly different.

Per suggestions in the comments, changed the query to better use any potential indexes on the base table.  However, it does assume that ModifiedDate does not contain future dates.

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TW_spRPTAllReceiverInventory]
@SiteId varchar(50),
@ReceiverId int,
@UserName varchar(50)

AS

BEGIN

DECLARE @SQL varchar(8000), @Index int, @Value varchar(500), @Source varchar(100), @tmp DATETIME

SELECT @Index = CHARINDEX('Initial Catalog=', ConnectionString) + 16, @Value = ConnectionString
FROM CustomerSetting
WHERE SiteId = @SiteId

SET @Source = '[' + SUBSTRING(@Value , @Index, CHARINDEX(';', @Value, @Index) - @Index) + ']'

SET @SQL = 'SELECT R.SiteId, L.*
        FROM ' + @Source + '.[dbo].[vwReceiver] R JOIN ' +
            @Source + '.[dbo].[vwInventory] L ON R.ReceiverId = L.ReceiverId
        WHERE R.ReceiverId = ' + CAST(@ReceiverId as varchar) + ' AND L.ModifiedUserName = '
            + Cast(@UserName as varchar) + ' AND ModifiedDate >= CAST(CAST(GETDATE() AS DATE) AS DATETIME)'

EXEC(@SQL)

END


Fill Factor: Will the fill factor change automatically after it detects the changes in the index pages? So lets say that the default fill factor is left at 0 initially. Data has been loaded up into the server and one fine weekend, the index rebuild job is run. Now if it detects any new data which must be mapped into a already full index page, it will split the page into half and position the record accordingly. So, now, essentially the fill factor of the database is not 0 as there are two pages which are half full. Will this reflect when we run sp_configure the next time ?

So, now, essentially the fill factor of the database is not 0 as there are two pages which are half full

You are correct, but this is actually caused by the fill factor value. You cannot say that now fill factor is not 100 (assuming your case) that would be an incorrect statement because fill factor was 100 this page split event was forced. Suppose you have index page which was almost full and new row needs to be accommodated now this would cause page split and move half rows to new page there by causing fragmentation. So what caused this: it was precisely the 100 fill factor value. That is why you must be cautious with what fill factor value you choose

A low, nonzero fill-factor value may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance

Also note that whatever fill factor value you use you CANNOT avoid page split even if you have ever increasing columns. There are 2 types page splits good and bad. The bad one is when data is inserted in middle creating a gap.

Some information about Full Factor in Paul's Myth Series about Fill Factor

Read more about fill factor

  Will this reflect when we run sp_configure the next time ?So when we restart the SQL Server and check the current value of the fill factor using sp_configure or any other option, does it reflect the current calculated value? Or will it just remain with the same value?

No after restart the value of fill factor in sp_configure will not change even if database has many page splits and many pages are half filled. This is called as fragmentation.


How to get only the strings before a character in SQL Server I am trying to write a sentence that gets only the strings which are just before a certain character.
for example:

DECLARE @TEST VARCHAR(MAX)
SET @TEST =  'CAMP1 = 4 AND CAMP2 = 5 OR CAMP3 = 7 AND CAMP4 = 9'

I am trying to to parse the string @TEST and get only the camps one by one and check if this camps (CAMP1, CAMP2, CAMP3, CAMP4) realy exist.

I don't know if loop would be necessary to sweep the string and get the strings before '=' and after the logic operators 'AND, OR'.

Any help pls ?

This looks like you are accepting a filtering clause that is going to be used as part of an ad-hoc query, and you want to try verify that the query won't produce a "column not found" error. This is almost certainly something you don't really want to do so I would question the design overall...

This sort of string parsing is not going to be very efficient or pretty in TSQL so would be probably better done in another layer, and as your business logic is no doubt putting the string together anyway it would be better to check as that happens and discover any error earlier in the process. If your business logic isn't making the string (i.e. you are accepting it from 3rd party code, or worse, a user) then you will have to contend with a lot of variations in the string (tabs instead of spaces, no spaces around the "=", extra whitespace including line breaks, names escaped with [ ] or " ", and so on).

But assuming you have no choice at all because this is someone else's design and you have no power to change it, you could try something like (pseudo code here, not feeling unlazy enough right now write detailed TSQL string manipulation!):

Set a "some not found" flag to 0
Copy @Test into @LeftToProcess
While there is at least one "=" character in @LeftToProcess (check with charindex) do the following:

Copy everything before the first "=" into @ColumnName
Take everything after the first "=" and put it back into @LeftToProcess
TRIM() @ColumnName
Replace AND with `` in @ColumnName
Replace OR with `` in @ColumnName (these two steps take out the logical operator, not that they make a massive set of assumptions like "there will always be exactly one space character between AND/OR and the name, if the input is coming from a user or third party you'll have to be a lot more clever than this)
Check using sys.objects joined to sys.columns that your table or view contains a column of the name now in @ColumnName
If not, set @SomeNotFound to 1
(end of WHILE loop, as we stripped off the part of the string we've just checked when we go back to step 3 the next "=" will be found or if there isn't any more we go to step 12)

If @SomeNotFound is still 0 at the end of the loop, everything mentioned exists in the target object

I hasten to make clear, in case my tone above does not, that I strongly advise against this sort of thing - it is hard to get absolutely correct for all possible inputs and the result will be difficult to maintain if it need to be expanded to deal with other sorts of clause.

There is unlikely to be any significantly cleaner or easier way to do exactly what you asking for in TSQL, your overall design needs a rethink to avoid it I feel.


Strange query optimization issue - logically the same, different performance I have a slow query and I don't understand why the DB is choosing to perform a sub-optimal execution plan. Server is MariaDB 5.5

Slow query (4-5 seconds) is:

SELECT VisitDate AS LASTDATE
FROM Visit
  JOIN Donor ON Donor.autoid = Visit.autoid
WHERE
  Donor.autoid = Visit.autoid AND
  LName LIKE '%%' AND
  FName LIKE '%bob%' AND
  Donor.autoid = '1234'
ORDER BY VisitDate DESC
LIMIT 1

EXPLAIN'd

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  Donor   const   PRIMARY PRIMARY 8   const   1   ""
1   SIMPLE  Visit   index   Visit_autoid_index  Visit_VisitDate_index   4       1   Using where

Fast query (sub 100ms) is:

SELECT VisitDate AS LASTDATE
FROM Visit
  JOIN Donor ON Donor.autoid = Visit.autoid
WHERE
  Donor.autoid = Visit.autoid AND
  LName LIKE '%%' AND
  FName LIKE '%bob%' AND
  Visit.autoid = '1234'
ORDER BY VisitDate DESC
LIMIT 1

EXPLAIN'd

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  Visit   ref Visit_autoid_index  Visit_autoid_index  12  const   1   Using index condition; Using where; Using filesort
1   SIMPLE  Donor   eq_ref  PRIMARY PRIMARY 8   NewDb.Visit.autoid  1   Using where

Donor Table (partial)

CREATE TABLE `Donor` (
  `autoid` bigint(20) NOT NULL AUTO_INCREMENT,
...
  `FName` varchar(15) DEFAULT '',
  `LName` varchar(25) NOT NULL,
...
  PRIMARY KEY (`autoid`),
...
) ENGINE=InnoDB AUTO_INCREMENT=3010 DEFAULT CHARSET=latin1

Visit Table (partial)

CREATE TABLE `Visit` (
  `VisitRecnum` int(11) NOT NULL AUTO_INCREMENT,
...
  `autoid` varchar(10) NOT NULL,
  `VisitDate` date DEFAULT NULL,
...
  PRIMARY KEY (`VisitRecnum`),
  KEY `Visit_autoid_index` (`autoid`),
...
  KEY `Visit_VisitDate_index` (`VisitDate`),
...
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Donor.autoid is a primary key and Visit.autoid has an index (a sort of poorly defined foreign key). VisitDate has an index.

It's like the query plan is ignoring the optimization it can make to ensure that Donor.autoid and Visit.autoid match, and scan a smaller set of rows where both autoid = '1234'.

You really need to provide SHOW CREATE TABLE.  A guess is that autoid is a different type in the two tables.  A strong clue is the 8 (BIGINT?) and the 12 (some kind of VARCHAR or DECIMAL?).

Redundant:  Donor.autoid = Visit.autoid is in both the ON (where it belongs) and the WHERE (where it is harmlessly redundant).

When JOINing, please qualify all columns -- which table are FName, VisitDate, etc, in??


MySQL - How do you enter a record with only whitespace " "? I want to enter the record with white-space as a record in MySQL. I had tried to enter it through shell and phpmyadmin, the record enters but when I try to retrieve the record it returns as null. I had also set the column null = no even then returns nothing.

code is below through shell and php:

insert into first_replace(to_replace, with_replace) values(".", " ");

You should use VARCHAR instead of CHAR for data type, if you want to store spaces. Space (whitespace) retention is documented in MySQL online documentation:

  The CHAR and VARCHAR types are similar, but differ in the way they are
  stored and retrieved. They also differ in maximum length and in
  whether trailing spaces are retained.


Is using SUM on multiple columns allowed in general SQL? I had a question in my GATE Mock Test Paper that was as follows:

Which of the following can appear in position marked as [..]?
"Select [..] from R group by A, B"
I. SUM(C+D)
II. A, Min(C)
III. A,B, Min(C)

I answered all of the above, but my friends and answer key stated that only II and III are correct. I ran the query on mysql with no problem, and saw this code listed on few websites as well.
So is this query valid for general SQL, or it is implemented only by certain applications such as MySQL?

NOTE: I have no idea what 'GATE Mock Test Paper' is, or if that's supposed to have a bearing on the answer, so fwiw ...

As others have pointed out, technically all of the answers could be true ... though this will depend on the RDBMS (ie, flavor of SQL) as well as any configs/settings.

If the intention is to pick answers that are ANSI compliant, then I'd say 'III' is the correct answer since all non-aggregates should be part of the group by.  Then again, if you start looking at some of the nitty-gritty ANSI details you'll find this requirement can sometimes be relaxed.

As you'll find out, most RDBMS products don't enforce such a strict standard: all non-aggregates being a member of the group by clause

As Michael Kutz has mentioned, without 'A' and/or 'B' in the select/projection list, answers 'I' and 'II' will provide some confusing answers. [And if you ever find yourself working in a real world RDBMS environment you'll find developers writing these types of queries all the time, but then not being able to describe what it is they're asking for let alone being able to explain the results they end up with.]

Again, answer 'III' is the only one I'd say can be clearly explained ... what I'm looking for ... the results I end up with.


ASM trying to mount droped diskgroup In my envoriment I am getting the following erro:

ORA-15032: not all alterations performed
ORA-15017: diskgroup "OCR" cannot be mounted
ORA-15040: diskgroup is incomplete

The problem is: I already has droped this diskgroup (I've changed the OCR diskgroup to another diskgroup OCR_NEW). I have two instances, in the first instance it's everything ok, but in the second instance I get this erro.

Where can be the problem? I didn't find anything.

I already tried to disable before to try remove in srvctl command, but not work as well.

[oracle@server2 trace]$ srvctl disable diskgroup -diskgroup OCR
PRCA-1000 : ASM Disk Group OCR does not exist
PRCR-1001 : Resource ora.OCR.dg does not exist

This can be caused by the asm_diskgroups parameter. On older releases (like 11g), this was a typical problem.  This parameter was automatically updated, except when a diskgroup was dropped, then its name was not removed from the parameter values.

You can check this if you have access to MOS:

Dropped Diskgroup Still Existing in ASM spfile (Doc ID 2241177.1)

BUG:19146721 - DROP DISKGROUP DOES NOT REMOVE THE ASM_DISKGROUP PARAMETER IN RAC

This parameter should not be set manually (unless you fix the incorrect value) and should be left empty. Below is only for demonstration.

Set asm_diskgroups for this ASM instance to include a diskgroup that does not exist:

SQL> show parameter diskgroups

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string
SQL> show spparameter diskgroups

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        asm_diskgroups                string
SQL> alter system set asm_diskgroups='DOES_NOT_EXIST' scope=spfile sid='+ASM4';

System altered.

Stop + start GI on this node:

# /u01/app/19.0.0/grid/bin/crsctl stop crs
# /u01/app/19.0.0/grid/bin/crsctl start crs

Check the parameter again after ASM was started:

SQL> show parameter diskgroups

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string      DOES_NOT_EXIST
SQL> show spparameter diskgroups

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
+ASM4    asm_diskgroups                string      DOES_NOT_EXIST

And this is from the alert log of this ASM instance:

2020-04-28T18:59:58.994866+02:00
SUCCESS: diskgroup DATA was mounted
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DOES_NOT_EXIST" cannot be mounted
ORA-15040: diskgroup is incomplete


how to ignore the SSMS (sql server management studio) sql server ansi settings? The options in Management Studio

On Management studio Tools->Options->Query Execution->SQL Server
there are options that are set and these prevail over the same options that are set on database level.

The Options at Database level

on management studio you right-click on a database and you can see what are the defaults for a number of options: (in yellow on the picture below)

using sys.databases you can get hold of these options using T_SQL.

The Options At Session Level

Using sys.dm_exec_sessions you can get hold of the options as they are set for the current session.

Questions

Is there a way to make sure the database setting will prevail over the management studio settings?

I thought about creating a plan, and use OPTION (USE PLAN)
but that has other consequences that is beyond the scope of this question.

If you are asking whether it is possible to enforce the database defaults for every connection that ever happens on the server, then no. Those defaults only take effect if something contradictory isn't set at the connection level.

In any individual batch in SSMS you can use statements like SET ANSI_NULLS ON, SET ANSI_NULLS OFF, SET QUOTED_IDENTIFIER ON, and so on, to control the way the following statements are interpreted.

With stored procedures it is a little more complicated:
When a procedure is created it remembers the current values for the ANSI_NULLS and QUOTED_IDENTIFIER options and uses them within any call to that procedure, but the same is not the case for other settings like ARITHABORT and ANSI_PADDINGS - for those you need to use SET <setting> ON/OFF in the procedure to fix the behaviour. See the Interoperability section of CREATE PROCEDURE (Transact-SQL) in the product documentation for more description of this situation.


Identical tables, identical columns, identical records, but why different record sizes? Identical tables, identical columns, identical records, but why different record sizes?

USE [test]
GO

CREATE TABLE [dbo].[mybit](
    [col1] [bit] NOT NULL,
    [col2] [bit] NOT NULL,
    [col3] [bit] NOT NULL,
    [col4] [bit] NOT NULL,
    [col5] [bit] NOT NULL,
    [col6] [bit] NOT NULL,
    [col7] [bit] NOT NULL,
    [col8] [bit] NOT NULL,
    [col9] [bit] NULL
)
GO

INSERT INTO [dbo].[mybit]
VALUES (1,1,1,1,1,1,1,1,NULL)
GO 2

DBCC IND(test, mybit, -1);
GO

DBCC TRACEON(3604);
DBCC PAGE(test, 1, '#pagenumber', 1);

Both records are 10 bytes in size.

CREATE TABLE [dbo].[mybit2](
    [col1] [bit] NOT NULL,
    [col2] [bit] NOT NULL,
    [col3] [bit] NOT NULL,
    [col4] [bit] NOT NULL,
    [col5] [bit] NOT NULL,
    [col6] [bit] NOT NULL,
    [col7] [bit] NOT NULL,
    [col8] [bit] NOT NULL,
)
GO

INSERT INTO [dbo].[mybit2]
VALUES (1,1,1,1,1,1,1,1)
GO

ALTER TABLE [dbo].[mybit2] ADD [col9] [bit] NULL
GO

INSERT INTO [dbo].[mybit2]
VALUES (1,1,1,1,1,1,1,1, NULL)
GO

DBCC IND(test, mybit2, -1);
GO

DBCC TRACEON(3604);
DBCC PAGE(test, 1, '#pagenumber', 1);

One record is 9 bytes in size and another is 10.

Thomas Cleberg is correct.

The first row you insert into bit2 shows up in DBCC page as

10000500 ff080000 63

This breaks down as follows

+-------+-----------------+--------+-------------------------------------------------------+
| Bytes | Hex (LSB order) |  Hex   |                                Comments               |
+-------+-----------------+--------+-------------------------------------------------------+
| 0     | 10              | 0x10   | TagA                                                  |
| 1     | 00              | 0x00   | TagB                                                  |
| 2-3   | 0500            | 0x0005 | Column Count offset 0x0005 = 5                        |
| 4     | ff              | 0xff   | The bit values. All are 1. 11111111 in binary = 0xff  |
| 5-6   | 0800            | 0x0008 | The column count. 0x0008 = 8. You have 8 columns.     |
| 7     | 00              | 0x00   | The Null bitmap. None are NULL.                       |
| 8     | 63              | 0x63   | Spare                                                 |
+-------+-----------------+--------+-------------------------------------------------------+

(If you wonder why there is a "spare" byte. This is explained here. It is to allow the row to be replaced by a forwarding pointer should the need arise. The value of it is arbitrary. For me it was 0x63. For you it looks like it was 0x00)

After you alter the table and add a new column with a NULL value (or a runtime constant default value in SQL Server 2012+ enterprise edition) then this is a metadata only change. SQL Server doesn't update existing rows. It can deduce from the column count stored in the row that the value for col9 should be NULL for that row. The change will be written out next time the row is updated.

Newly inserted rows will get written out in full though. Two extra bytes are needed because the single byte in the fixed data section is full up with values for bit columns 1-8 so an additional byte is needed for bit columns 9 to 16. Additionally the Null bitmap grows by a byte too. However there is no need to retain that "spare" byte to keep the rowsize at a minimum level so the net effect is an increase in one byte.

+-------+-----------------+--------+------------------------------------------------------------------------------------+
| Bytes | Hex (LSB order) |  Hex   |                                      Comments                                      |
+-------+-----------------+--------+------------------------------------------------------------------------------------+
| 0     | 10              | 0x10   | TagA                                                                               |
| 1     | 00              | 0x00   | TagB                                                                               |
| 2-3   | 0600            | 0x0006 | Column Count offset 0x0006 =6                                                      |
| 4-5   | ff00            | 0xff00 | The bit values. An extra byte has now been added to accomodate the 9th bit column. |
| 6-7   | 0900            | 0x0009 | The column count. You now have 9 columns.                                          |
| 8-9   | 0001            | 0x0100 | The Null bitmap. The 9th column is NULL. 100000000 in binary is 0x0100             |
+-------+-----------------+--------+------------------------------------------------------------------------------------+


How msdb.dbo.backupset works ( backup_size ) I Made this simple query to list the Backup_size:

select database_name, backup_finish_date, backup_size
from msdb.dbo.backupset
where day(backup_finish_date) = 26
and year(backup_finish_date) = 2017
and month(backup_finish_date) = 10

OK. I made a database backup, and in my select, I got this result:

  My_database | 2017-10-26 16:59:44.000 | 8509270016

As Seen Here ( Microsoft's website ), this field is a byte field. Ok. Converting 8509270016 bytes to KB I have: 8,309,834 Kilobytes.

but the database size inside the folder, has:

BACKUP DATABASE, by default, does not overwrite existing backup sets contained within a backup file.

According to Microsoft Docs on BACKUP DATABASE:

  If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device.

To test this, the following code will create a blank database, then take three backups of that database, without the INIT option.  The size of the backup file increases each time a backup is taken.

USE master;

IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'BackupTest')
BEGIN
    ALTER DATABASE BackupTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE BackupTest;
END
CREATE DATABASE BackupTest;
BACKUP DATABASE BackupTest TO DISK = 'C:\temp\BackupTest.bak';
EXEC sys.xp_cmdshell 'DIR "C:\temp\BackupTest.bak"';

 Volume in drive C is Windows
 Volume Serial Number is 1E06-5D89

 Directory of C:\temp

 05/15/2018  02:55 PM           399,360 BackupTest.bak
               1 File(s)        399,360 bytes
               0 Dir(s)  111,145,492,480 bytes free

BACKUP DATABASE BackupTest TO DISK = 'C:\temp\BackupTest.bak';
EXEC sys.xp_cmdshell 'DIR "C:\temp\BackupTest.bak"';

 Volume in drive C is Windows
 Volume Serial Number is 1E06-5D89

 Directory of C:\temp

 05/15/2018  02:55 PM           796,672 BackupTest.bak
               1 File(s)        796,672 bytes
               0 Dir(s)  111,145,091,072 bytes free

BACKUP DATABASE BackupTest TO DISK = 'C:\temp\BackupTest.bak';
EXEC sys.xp_cmdshell 'DIR "C:\temp\BackupTest.bak"';

 Volume in drive C is Windows
 Volume Serial Number is 1E06-5D89

 Directory of C:\temp

 05/15/2018  02:55 PM         1,193,984 BackupTest.bak
               1 File(s)      1,193,984 bytes
               0 Dir(s)  111,144,693,760 bytes free

If we look at the content of the backup file with RESTORE HEADERONLY we see the three backups contained within the file:

╔════════════╦═══════════════════╦════════════╦════════════════╦════════════╦══════════╦════════════╦══════════╦════════════╦══════════════╦═════════════════╦═════════════════════════╦════════════╦═══════════════════╦═══════════════════╦═══════════════════╦═══════════════════╦═════════════════════════╦═════════════════════════╦═══════════╦══════════╦═════════════════╦════════════════════════╦════════════════════╦══════════════════╦══════════════════════╦══════════════════════╦══════════════════════╦═════════════╦═══════╦══════════════════════════════════════╦══════════════════════════════════════╦══════════════════════════════╦══════════════════════════════════════╦═══════════════════╦════════════╦════════════╦══════════════╦════════════════════╦═══════════╦════════════════╦═══════════════════════╦════════════════╦════════════╦══════════════════════════════════════╦══════════════╦═══════════════╦═════════════════════╦══════════════════════╦═══════════════════════╦══════════════════════════════════════╦══════════════════════╦═════════════╗
║ BackupName ║ BackupDescription ║ BackupType ║ ExpirationDate ║ Compressed ║ Position ║ DeviceType ║ UserName ║ ServerName ║ DatabaseName ║ DatabaseVersion ║  DatabaseCreationDate   ║ BackupSize ║     FirstLSN      ║      LastLSN      ║   CheckpointLSN   ║ DatabaseBackupLSN ║     BackupStartDate     ║    BackupFinishDate     ║ SortOrder ║ CodePage ║ UnicodeLocaleId ║ UnicodeComparisonStyle ║ CompatibilityLevel ║ SoftwareVendorId ║ SoftwareVersionMajor ║ SoftwareVersionMinor ║ SoftwareVersionBuild ║ MachineName ║ Flags ║              BindingID               ║            RecoveryForkID            ║          Collation           ║              FamilyGUID              ║ HasBulkLoggedData ║ IsSnapshot ║ IsReadOnly ║ IsSingleUser ║ HasBackupChecksums ║ IsDamaged ║ BeginsLogChain ║ HasIncompleteMetaData ║ IsForceOffline ║ IsCopyOnly ║         FirstRecoveryForkID          ║ ForkPointLSN ║ RecoveryModel ║ DifferentialBaseLSN ║ DifferentialBaseGUID ║ BackupTypeDescription ║            BackupSetGUID             ║ CompressedBackupSize ║ Containment ║
╠════════════╬═══════════════════╬════════════╬════════════════╬════════════╬══════════╬════════════╬══════════╬════════════╬══════════════╬═════════════════╬═════════════════════════╬════════════╬═══════════════════╬═══════════════════╬═══════════════════╬═══════════════════╬═════════════════════════╬═════════════════════════╬═══════════╬══════════╬═════════════════╬════════════════════════╬════════════════════╬══════════════════╬══════════════════════╬══════════════════════╬══════════════════════╬═════════════╬═══════╬══════════════════════════════════════╬══════════════════════════════════════╬══════════════════════════════╬══════════════════════════════════════╬═══════════════════╬════════════╬════════════╬══════════════╬════════════════════╬═══════════╬════════════════╬═══════════════════════╬════════════════╬════════════╬══════════════════════════════════════╬══════════════╬═══════════════╬═════════════════════╬══════════════════════╬═══════════════════════╬══════════════════════════════════════╬══════════════════════╬═════════════╣
║ NULL       ║ NULL              ║          1 ║ NULL           ║          1 ║        1 ║          2 ║ me       ║ machine    ║ BackupTest   ║             706 ║ 2018-05-15 15:06:09.000 ║    2633728 ║ 35000000005500064 ║ 35000000008500001 ║ 35000000005500064 ║                 0 ║ 2018-05-15 15:06:10.000 ║ 2018-05-15 15:06:10.000 ║        52 ║        0 ║            1033 ║                 196609 ║                110 ║             4608 ║                   11 ║                    0 ║                 6260 ║ machine     ║   512 ║ 2E885286-1648-4085-96D8-0AFF92CCE995 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ SQL_Latin1_General_CP1_CI_AS ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║                 0 ║          0 ║          0 ║            0 ║                  0 ║         0 ║              0 ║                     0 ║              0 ║          0 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ NULL         ║ FULL          ║ NULL                ║ NULL                 ║ Database              ║ 94CA4CD7-659F-406B-9411-AF00DCC8E900 ║               397660 ║           0 ║
║ NULL       ║ NULL              ║          1 ║ NULL           ║          1 ║        2 ║          2 ║ me       ║ machine    ║ BackupTest   ║             706 ║ 2018-05-15 15:06:09.000 ║    2632704 ║ 35000000009500037 ║ 35000000011200001 ║ 35000000009500037 ║ 35000000005500064 ║ 2018-05-15 15:06:10.000 ║ 2018-05-15 15:06:11.000 ║        52 ║        0 ║            1033 ║                 196609 ║                110 ║             4608 ║                   11 ║                    0 ║                 6260 ║ machine     ║   512 ║ 2E885286-1648-4085-96D8-0AFF92CCE995 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ SQL_Latin1_General_CP1_CI_AS ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║                 0 ║          0 ║          0 ║            0 ║                  0 ║         0 ║              0 ║                     0 ║              0 ║          0 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ NULL         ║ FULL          ║ NULL                ║ NULL                 ║ Database              ║ 840F2E51-1805-4270-AF31-BB09F4799B4F ║               396242 ║           0 ║
║ NULL       ║ NULL              ║          1 ║ NULL           ║          1 ║        3 ║          2 ║ me       ║ machine    ║ BackupTest   ║             706 ║ 2018-05-15 15:06:09.000 ║    2632704 ║ 35000000012200037 ║ 35000000013900001 ║ 35000000012200037 ║ 35000000009500037 ║ 2018-05-15 15:06:11.000 ║ 2018-05-15 15:06:11.000 ║        52 ║        0 ║            1033 ║                 196609 ║                110 ║             4608 ║                   11 ║                    0 ║                 6260 ║ machine     ║   512 ║ 2E885286-1648-4085-96D8-0AFF92CCE995 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ SQL_Latin1_General_CP1_CI_AS ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║                 0 ║          0 ║          0 ║            0 ║                  0 ║         0 ║              0 ║                     0 ║              0 ║          0 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ NULL         ║ FULL          ║ NULL                ║ NULL                 ║ Database              ║ 448F651F-D847-463D-A5DA-8C2F8EA8ECC5 ║               396295 ║           0 ║
╚════════════╩═══════════════════╩════════════╩════════════════╩════════════╩══════════╩════════════╩══════════╩════════════╩══════════════╩═════════════════╩═════════════════════════╩════════════╩═══════════════════╩═══════════════════╩═══════════════════╩═══════════════════╩═════════════════════════╩═════════════════════════╩═══════════╩══════════╩═════════════════╩════════════════════════╩════════════════════╩══════════════════╩══════════════════════╩══════════════════════╩══════════════════════╩═════════════╩═══════╩══════════════════════════════════════╩══════════════════════════════════════╩══════════════════════════════╩══════════════════════════════════════╩═══════════════════╩════════════╩════════════╩══════════════╩════════════════════╩═══════════╩════════════════╩═══════════════════════╩════════════════╩════════════╩══════════════════════════════════════╩══════════════╩═══════════════╩═════════════════════╩══════════════════════╩═══════════════════════╩══════════════════════════════════════╩══════════════════════╩═════════════╝

However, if we now use the INIT option:

BACKUP DATABASE BackupTest TO DISK = 'C:\temp\BackupTest.bak' WITH INIT;
EXEC sys.xp_cmdshell 'DIR "C:\temp\BackupTest.bak"';

We see the size of the backup file reflects a single backup again:

 Volume in drive C is Windows
 Volume Serial Number is 1E06-5D89

 Directory of C:\temp

 05/15/2018  03:02 PM           398,336 BackupTest.bak
               1 File(s)        398,336 bytes
               0 Dir(s)  111,145,304,064 bytes free

And if we look at RESTORE HEADERONLY, we see a single backup is contained in the file:

╔════════════╦═══════════════════╦════════════╦════════════════╦════════════╦══════════╦════════════╦══════════╦════════════╦══════════════╦═════════════════╦═════════════════════════╦════════════╦═══════════════════╦═══════════════════╦═══════════════════╦═══════════════════╦═════════════════════════╦═════════════════════════╦═══════════╦══════════╦═════════════════╦════════════════════════╦════════════════════╦══════════════════╦══════════════════════╦══════════════════════╦══════════════════════╦═════════════╦═══════╦══════════════════════════════════════╦══════════════════════════════════════╦══════════════════════════════╦══════════════════════════════════════╦═══════════════════╦════════════╦════════════╦══════════════╦════════════════════╦═══════════╦════════════════╦═══════════════════════╦════════════════╦════════════╦══════════════════════════════════════╦══════════════╦═══════════════╦═════════════════════╦══════════════════════╦═══════════════════════╦══════════════════════════════════════╦══════════════════════╦═════════════╗
║ BackupName ║ BackupDescription ║ BackupType ║ ExpirationDate ║ Compressed ║ Position ║ DeviceType ║ UserName ║ ServerName ║ DatabaseName ║ DatabaseVersion ║  DatabaseCreationDate   ║ BackupSize ║     FirstLSN      ║      LastLSN      ║   CheckpointLSN   ║ DatabaseBackupLSN ║     BackupStartDate     ║    BackupFinishDate     ║ SortOrder ║ CodePage ║ UnicodeLocaleId ║ UnicodeComparisonStyle ║ CompatibilityLevel ║ SoftwareVendorId ║ SoftwareVersionMajor ║ SoftwareVersionMinor ║ SoftwareVersionBuild ║ MachineName ║ Flags ║              BindingID               ║            RecoveryForkID            ║          Collation           ║              FamilyGUID              ║ HasBulkLoggedData ║ IsSnapshot ║ IsReadOnly ║ IsSingleUser ║ HasBackupChecksums ║ IsDamaged ║ BeginsLogChain ║ HasIncompleteMetaData ║ IsForceOffline ║ IsCopyOnly ║         FirstRecoveryForkID          ║ ForkPointLSN ║ RecoveryModel ║ DifferentialBaseLSN ║ DifferentialBaseGUID ║ BackupTypeDescription ║            BackupSetGUID             ║ CompressedBackupSize ║ Containment ║
╠════════════╬═══════════════════╬════════════╬════════════════╬════════════╬══════════╬════════════╬══════════╬════════════╬══════════════╬═════════════════╬═════════════════════════╬════════════╬═══════════════════╬═══════════════════╬═══════════════════╬═══════════════════╬═════════════════════════╬═════════════════════════╬═══════════╬══════════╬═════════════════╬════════════════════════╬════════════════════╬══════════════════╬══════════════════════╬══════════════════════╬══════════════════════╬═════════════╬═══════╬══════════════════════════════════════╬══════════════════════════════════════╬══════════════════════════════╬══════════════════════════════════════╬═══════════════════╬════════════╬════════════╬══════════════╬════════════════════╬═══════════╬════════════════╬═══════════════════════╬════════════════╬════════════╬══════════════════════════════════════╬══════════════╬═══════════════╬═════════════════════╬══════════════════════╬═══════════════════════╬══════════════════════════════════════╬══════════════════════╬═════════════╣
║ NULL       ║ NULL              ║          1 ║ NULL           ║          1 ║        1 ║          2 ║ me       ║ machine    ║ BackupTest   ║             706 ║ 2018-05-15 15:06:09.000 ║    2632704 ║ 35000000014900037 ║ 35000000016600001 ║ 35000000014900037 ║ 35000000012200037 ║ 2018-05-15 15:06:11.000 ║ 2018-05-15 15:06:11.000 ║        52 ║        0 ║            1033 ║                 196609 ║                110 ║             4608 ║                   11 ║                    0 ║                 6260 ║ machine     ║   512 ║ 2E885286-1648-4085-96D8-0AFF92CCE995 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ SQL_Latin1_General_CP1_CI_AS ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║                 0 ║          0 ║          0 ║            0 ║                  0 ║         0 ║              0 ║                     0 ║              0 ║          0 ║ 9F2C5269-0971-4BD5-AEEE-D64D8810CF55 ║ NULL         ║ FULL          ║ NULL                ║ NULL                 ║ Database              ║ EA10F663-FAF3-422C-AE4E-3478AA7658BC ║               396310 ║           0 ║
╚════════════╩═══════════════════╩════════════╩════════════════╩════════════╩══════════╩════════════╩══════════╩════════════╩══════════════╩═════════════════╩═════════════════════════╩════════════╩═══════════════════╩═══════════════════╩═══════════════════╩═══════════════════╩═════════════════════════╩═════════════════════════╩═══════════╩══════════╩═════════════════╩════════════════════════╩════════════════════╩══════════════════╩══════════════════════╩══════════════════════╩══════════════════════╩═════════════╩═══════╩══════════════════════════════════════╩══════════════════════════════════════╩══════════════════════════════╩══════════════════════════════════════╩═══════════════════╩════════════╩════════════╩══════════════╩════════════════════╩═══════════╩════════════════╩═══════════════════════╩════════════════╩════════════╩══════════════════════════════════════╩══════════════╩═══════════════╩═════════════════════╩══════════════════════╩═══════════════════════╩══════════════════════════════════════╩══════════════════════╩═════════════╝

I have backup compression turned on, so msdb.dbo.backupset shows the following sizes for the backups I just took:

SELECT bs.backup_finish_date
    , bs.backup_size
    , bs.compressed_backup_size
FROM msdb.dbo.backupset bs
WHERE bs.database_name = 'BackupTest'
ORDER BY bs.backup_set_id DESC

╔═════════════════════════╦═════════════╦════════════════════════╗
║   backup_finish_date    ║ backup_size ║ compressed_backup_size ║
╠═════════════════════════╬═════════════╬════════════════════════╣
║ 2018-05-15 15:06:11.000 ║     2632704 ║                 396310 ║
║ 2018-05-15 15:06:11.000 ║     2632704 ║                 396295 ║
║ 2018-05-15 15:06:11.000 ║     2632704 ║                 396242 ║
║ 2018-05-15 15:06:10.000 ║     2633728 ║                 397660 ║
╚═════════════════════════╩═════════════╩════════════════════════╝

As you can see the backup_size is the uncompressed size of the backup, in bytes, and the compressed_backup_size closely matches the size reported by the filesystem, assuming you incrementally add the sizes as each backup is added to the backup file.


Possible aproach for MySQL replication This might be a duplicate questions asked before. But I need to understand a possible approach for my ENV running innodb engine.
Is it possible to upgrade a Master MySQL Server to 5.5 to 5.6 and  replication Slave which is on Version 5.7

If this is possible then how do we perform this activity, because we am getting this below error of Server_UUID when replicating the Slave (Slave version 5.7.11 and Master Version 5.6.29)

Errno: 1593
Fatal error: The slave I/O thread stops because master and slave
have equal MySQL server UUIDs; these UUIDs must be different for
replication to work.

Purpose is to UPGRADE both the server on the same version 5.7

Currently Version

Server1 and Server2 are on 5.6.29

Beginner in MySQL unable to understand the concept of replication with the new version, open for better suggestion.

Check out this article by the folks at Percona regarding duplicate UUIDs, it may be worth checking how the slave was created originally and checking out the auto.cnf settings on each server.
https://www.percona.com/blog/2014/01/21/beware-mysql-5-6-server-uuid-cloning-slaves/


Why does this procedure raise a privilege error? I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine.
QUESTIONS

Could somebody help me understand which specific part of this code raises this error, i.e. requires the said privilege?
Is there any way I could have corresponding functionality without a privileged call?

You need to examine the privileges of the connect user. The next time you connect run this:
SHOW GRANTS FOR CURRENT_USER();

This will tell you two things:

How you you were allowed to authenticate
What privileges you have with that user

Look at the beginning of the Stored Procedure definition
CREATE DEFINER = 'root'@'localhost' ...

The fact that it says DEFINER (the SQL Security) means that you assume the privileges of 'root'@'localhost' when you call the Stored Procedure. Nevertheless, there is a catch: you need the EXECUTE privilege. Why ?
According to the MySQL Documentation on Stored Procedures:

Definer and invoker security contexts differ as follows:
A stored program or view that executes in definer security context executes with the privileges of the account named by its DEFINER attribute. These privileges may be entirely different from those of the invoking user. The invoker must have appropriate privileges to reference the object (for example, EXECUTE to call a stored procedure or SELECT to select from a view), but when the object executes, the invoker's privileges are ignored and only the DEFINER account privileges matter. If this account has few privileges, the object is correspondingly limited in the operations it can perform. If the DEFINER account is highly privileged (such as a root account), the object can perform powerful operations no matter who invokes it.
A stored routine or view that executes in invoker security context can perform only operations for which the invoker has privileges. The DEFINER attribute can be specified but has no effect for objects that execute in invoker context.

Therefore, whatever you get as CURRENT_USER(), simply grant EXECUTE to that user. For example, if CURRENT_USER() is 'myuser'@'somedb', login as 'root'@'localhost' and run
GRANT EXECUTE ON *.* TO 'myuser'@'somedb';

Then, that user can run any Stored Procedure. If you want to restrict it to just the Stored Procedures in the medilife database, then do:
GRANT EXECUTE ON medilife.* TO 'myuser'@'somedb';

Give it a Try !!!


Recovering MYSQL databases I needed to reinstall the WAMP server on Windows and in the process it reinstalled MYSQL as well. Now when I login to phpmyadmin, I can see my databases with the tables listed on the left side navigation panel. But when I click on the individual tables, it throws an error saying that the table doesn't exist. Browsing to this location C:\wamp\bin\mysql\mysql5.6.12\data, I see the folders with the database names. I also see files ending with *.ibd. The filenames are the same as the table names. With different databases, the folder contents are different. Some have files ending with *.ibd only. Some have a mix of *.ibd and *.frm. There is also a *.opt file in some folders. I do not know much about the admin side of MySQL. I only know enough to use PHP to interact with it for simple SQL statements. I have gone through some posts on how to possibly recovering the databases. But most of what was discussed there doesn't seem to either directly relate to my specific issue or are beyond my understanding.

Is there a simple enough way to restore the databases that is easy for anyone to follow? Or does it require previous experience with recovering databases?
Please suggest a few options.

A couple of possibilities, not sure if any of them will help you

Force recovery

[mysqld]
innodb_force_recovery = 4

If you run Force Recovery, your database will still be in an incoherent state. This will however allow you to make a backup of the data that you do have access to.

  Usually, most of the data obtained in this way is intact. Serious
  corruption might cause SELECT * FROM tbl_name statements or InnoDB
  background operations to crash or assert, or even cause InnoDB
  roll-forward recovery to crash. In such cases, use the
  innodb_force_recovery option to force the InnoDB storage engine to
  start up while preventing background operations from running, so that
  you can dump your tables

Force InnoDB recovery

Permissions

Make sure the MySQL service user has full permissions on C:\wamp\bin\mysql\mysql5.6.12\data

The service that runs MySQL needs full control for the directory where you are storing your data files

Update

FS error

Problems reading from the C:\wamp\bin\mysql\mysql5.6.12\data directory ?

If you go to Control Panels-> Administrative Tools-> Events Viewer, then Windows Logs -> System, you might find more information about disk issues

One possible solution, would be to copy your data directory to another drive and/or directory.

Stop MySQL

Copy the data directory contents to the new drive and/or directory.

Open the C:\wamp\bin\mysql\mysql5.6.12\my.cnf file and change the datadir option under mysqld

[mysqld]

datadir=D:\some\other\directory

Start MySQL


Gap resolution of Logical Standby Database via Archive Log Backups Due to reasons outside the scope of this question, we had to flashback our Logical Standby Database (at the VM level) to the previous day.

So, now i'm trying to resolve gaps in missing archivelogs between the primary and the Logical Standby Databases.

Standby:

SQL> select * from gv$logstdby_process where status_code != 16116;
READER  ORA-16240: Waiting for log file (thread# 1, sequence# 54888)

Sequence 54888 no longer exists in the Logical Standby (due to the flashback), and since we take a nightly archive log backup, this sequence no longer exists as an archived log on the Primary. However, I verified that this sequence is in the archived log backup on the Primary.

Primary:

RMAN> list backup of archivelog sequence 54888;

  List of Archived Logs in backup set 6236
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  1    54888   42289020451 30-MAR-16 42290599951 30-MAR-16

Therefore, I copied the archive log backup to the Logical Standby and issued the following on the Logical Standby database:

RMAN> catalog start with 'L:\oracle\backups';
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> recover database;
RMAN> alter database open;
RMAN> list backup of archivelog scn between 42289020451 and 42289020451;

Thrd Seq     Low SCN    Low Time   Next SCN    Next Time
1    54888   42289020451 30-MAR-16 42290599951 30-MAR-16

RMAN> list backup of archivelog sequence 54888;

specification does not match any backup in the repository

SQL> alter database start logical standby apply immediate;
SQL> select * from gv$logstdby_process where status_code != 16116;

READER  ORA-16240: Waiting for log file (thread# 1, sequence# 54888)

So I have 2 questions:

1) Even though I can see the archivelog backup of sequence 54888 on the Logical Standby Database when performing a list backup of archivelog scn, why does the list backup of archivelog sequence 54888 return no records?

2) How can I get the log apply process to pick up sequence 54888 from the archivelog backup?

If you can see the backup file in the directory, try cataloging it:

RMAN> catalog starts with '/path/to/archive/directory';

It will list the backup and ask you if you want to catalog it.
Following which you should be able to continue with your gap recovery:

alter database recover managed standby database cancel;
recover standby database;
#after all existing logs are applied put standby database to recover managed
mode
recover managed standby database

If you don't have the archived logs anymore, you can do an incremental backup with:

"backup incremental <from scn> until scn"


MySQL: Queries limitations per host/user In MySQL 5.7, is there a way to limit specific type of queries up to a certain limit? select queries for instance to be max 100 query at a time.

And is it doable to limit queries per host or user?

Yes you can. You can create users with resource limits, e.g.:

mysql> CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'some_password'
    ->     WITH MAX_QUERIES_PER_HOUR 20
    ->          MAX_UPDATES_PER_HOUR 10
    ->          MAX_CONNECTIONS_PER_HOUR 5
    ->          MAX_USER_CONNECTIONS 2;

See here for more details:
https://dev.mysql.com/doc/refman/5.7/en/user-resources.html

Since every connection can run at most one query at a time, for the specific case you are describing, you probably want to set MAX_USER_CONNECTIONS to 100.


MySQL Calculate percentage difference (not change) between 2 columns The calculation formula for difference between 2 numbers should be:

Using the above formula I need to calculate the difference in percentage between N1 and N2.
Both numbers are the result of 2 separate SUM cases based on 2 ENUM values in col_type (please see below)

SELECT
  CONCAT('£ ',SUM(CASE WHEN t1.col_type = 'N1' THEN t2.qty * t1.price ELSE 0 END)) AS 'Total N1',
  CONCAT('£ ',SUM(CASE WHEN t1.col_type = 'N2' THEN t2.qty * t1.price ELSE 0 END)) AS 'Total N2'
FROM t2
  INNER JOIN t1
    ON t2.col_id = t1.col_id

The resulted output should be like:

+----------+----------+------------------------+
| Total N1 | Total N2 | Diff between N1 and N2 |
+----------+----------+------------------------+
|  3765.50 |  3246.15 | 14.81%                 |
+----------+----------+-----------------------

My question is how do I pass the values of N1 and N2 (already generated) to a 3rd column and calculate the difference between N1 and N2 using above formula?

Note: Please note that is percentage difference between 2 numbers and not the percentage change where (N2-N1)/N1 * 100 applies...

Thank you in advance for any reply...

With some help the solution was to use a subquery something like this:

SELECT
  CONCAT('£ ', t1.n1) AS N1,
  CONCAT('£ ', t1.n2) AS N2,
  CONCAT(((N1 - N2) / ((N1 + N2) / 2)) * 100, ' %') AS 'Difference'
FROM (SELECT
    SUM(CASE WHEN t1.col_type = 'N1' THEN t2.qty * t1.price ELSE 0 END) AS n1,
    SUM(CASE WHEN t1.col_type = 'N2' THEN t2.qty * t1.price ELSE 0 END) AS n2
  FROM t2
    INNER JOIN t1
      ON t2.col_id = t1.col_id) t2


postgresql: procedure to return the number of occurrences of given year and service I am creating a procedure to return the number of times a given service is done in a year. The procedure cannot return a NULL value. The table I am taking the data from is called "tb_orders" and has "created_dt" and "order_code" as columns; for the datetime the service was provided and the code of the service itself.
So far I've got this:
CREATE FUNCTION catalog_yearly_orders
  (year, order_code) RETURNS numeric AS $$
DECLARE
  n_service integer NOT NULL,
  year YEAR(tb_orders.created_dt)%TYPE;
BEGIN
  SELECT order_code INTO order_code
  FROM tb_orders
  WHERE order_code = order_code;
  n_service = n_service + 1;
  RETURN n_service;
END;
$$ LANGUAGE plpgsql;

I think what I am missing is some command, between BEGIN-END, for the parameters to match the tuples I am looking for so that I get the ones which coincide with year and service code.

Assuming the tb_orders table has an id, would you not be able to do something like the following?
RETURN (SELECT COUNT(tbo.id)
          FROM tb_orders tbo
         WHERE YEAR(tbo.created_dt) = year AND tbo.order_code = order_code);

In your DECLARE you are overwriting the year value, and I'm not at all sure what n_service is supposed to do. If all you want is a count, then COUNT() is all you need. Mind you, using YEAR() in the WHERE clause is not great, but it's sufficient for the sake of this example.

Note: The syntax may be a bit off. I'm a bit rusty with stored procedures in PostgreSQL.


Is there a version history for the Postgres upgrades made in the installation? I'm trying to determine when the last Postgres upgrade was made. I know my current version (through "show server_version" command, and other ways too). But I wasn't able to find a history of the last upgrades and the respective dates. Is there such a thing?

An analogy: this can be achieved with the file $II_SYSTEM/ingres/version.dat in an Ingres DBMS.

You're using Redhat, so it's stored under dnf history list. It's a function of the package manager, not the database. You can build your own PostgreSQL and install and that won't show under the package manager.

The database engine itself doesn't track revisions to itself in the database: and, it would be weird if it did. If Redhat issues an upgrade and restarts the server, the server is supposed to know that Redhat issued an upgrade? What if the upgrade was a minor fix that Redhat pushed out ahead of PostgreSQL?


SELECT RELEASE_LOCK causing "Mysql has gone away" During a long running process (90m+), our code calls SELECT RELEASE_LOCK('..');. However it always gives a "Mysql has gone away" error when we do this.

We never got to see the return value of the RELEASE_LOCK; the query didn't execute. We lost connection before the result was found.

We have never encountered this problem before. It's after an upgrade of Magento 1.12 to 1.13

We have tried a number of things including increasing various timeouts but to no avail. What might you suggest is a good debugging strategy or something that may be causing this issue?

The most likely explanation for this is that an intermediate piece of network hardware is tearing down or "forgetting" your idle TCP connection, assuming your wait_timeout system variable is not set too low.

Stateful firewalls maintain internal data structures of all current "flows" -- source/destination address/port quads -- and if a flow has no traffic for too long, the record of the flow is simply dropped from the firewall's memory.  The next TCP packet to arrive doesn't match any established flows, and doesn't have the SYN flag set, so it's not a new connection, and the firewall considers it invalid and either drops it or spoofs an RST packet from the destination.

Most likely, you would get this same error with any query SELECT NOW(); for example, would probably return the same result, once the connection has been totally idle for too long.  Note that running a query doesn't prevent the connection from being "idle" at the TCP layer.

The connection might not disappear from the processlist in the mean time, because, since it's completely idle, the server would not have sent anything and would therefore not have realized that at some point on the network, it is an invalid TCP connection.

This occurs, for just one example in my experience, within Amazon EC2.

The workaround is to enable TCP keepalives in the operating system itself on at least one end of the connection.  This will cause TCP traffic to continue to be exchanged by the hosts during the idle time, even though there's no payload on the connection.  Enabling these should mediate this issue.

Linux: http://tldp.org/HOWTO/TCP-Keepalive-HOWTO/usingkeepalive.html

Windows: https://technet.microsoft.com/en-us/library/cc957549.aspx

If curious, then you should also, by trial and error, be able to discover a period of time after which idle connections are no longer usable... for example, connections left idle for 14 minutes may be fine, but connections left idle for 16 minutes will always be dead, suggesting the firewall has a 15 minute flow expiration timer.


Adding Image to excel Export in SSIS I am exporting excel sheets through SSIS export, and creating excel sheet at run time using execute sql task.
Now the client needs a logo(image) to be attached to the excel sheet on top of data part. Do we have any way out to do that.

NB: we cannot use "file system task" to copy a pre-existing excel file as we have to create the task on runtime. cannot copy a excel sheet from any other location.

Thanks

You can use a script task to achieve that, you can benefit from the Microsoft.Interop.Excel library to achieve that, you can copy the image from a file using the following code:

Image ExcelImage = Image.FromFile(strFilePath);
System.Windows.Forms.Clipboard.SetDataObject(ExcelImage ,true);

And paste it to excel sheet using the following code:

Excel.Worksheet ThisSheet = (Excel.Worksheet)ThisWorkBook.Sheets[1];
Excel.Range oRange = (Excel.Range)ThisSheet.Cells[10,1];
ThisSheet.Paste(oRange,ExcelImage);

Detailed code provided in the following link:

How to programmatically add a Picture or Icon to an Excel WorkSheet using C#


Adding Foreign Keys to Composite Primary Key, referencing 2 tables I have a composite primary key
PRIMARY KEY (alpha, beta)
I want to add foreign keys to primary keys alpha and beta
I tried:
-- ADD FOREIGN KEY (alpha) REFERENCES green(alpha);
-- ADD FOREIGN KEY (beta) REFERENCES blue(beta);

This didn't work. I then tried:
ADD FOREIGN KEY (alpha, beta) REFERENCES green(alpha), blue(beta);

And this didn't work either. I can't find any examples online of adding different foreign keys to the composite primary key. Any tips would be welcome.

I had UNSIGNED on the referencing table but not on the referenced. That caused the issue it seems.


Can I replicate views with Slony? Can I feed some SQL that creates a view to Slony?

I have some SQL that does CREATE OR REPLACE VIEW name_of_view and so on, passes it to slonik_execute_script, and then tries to run slonik_create_set for a temporary Slony set containing only that view.

I get rejected with ERROR:  Slony-I: table "public"."name_of_view" has no primary key error. To my knowledge, views don't support primary keys.

Does this mean there is no way, using the Slony admin scripts, to add a view to replication, and that I should just install it manually on all members of my replication cluster?

Slony is trigger based.  Since you cannot have triggers on views you can not use Slony to replicate views.

However, you can achieve the desired effect by using Slony to replicate the tables used by your views and duplicate the view definitions on your slave servers.


Multiple Basic Availability Groups and Performance - SQL Server I'm migrating some of my company's SQL Server boxes to a new SQL 2016 setup. We're currently using database mirroring on SQL 2012. One of the instances I'm migrating currently has about 60 databases and despite the occasional hiccup, mirroring is working fine for us. And for various reasons this instance is running SQL Standard edition. As mirroring is being deprecated, I'm keen to try Basic Availability Groups.

However, I'm running into real performance issues putting together a lab with only 20 databases. During the initial setup, simply creating a new availability group will exhaust the 472 thread limit for Availability Groups (Max Threads - 40). On the primary server I'll see something like this:

time   active_workers idle_workers worker_limit worker_start_success
--------------------- ------------ ------------ --------------------
12:01  473            0            472          true
12:02  473            0            472          false
... multiple rows elided
12:05  472            451          472          false
12:06  22             16           472          true

Simply trying to view the dashboard for one of the availability groups will peg the CPU for several minutes. This isn't a super powerful VM, but VMWare tells me it has allocated 3014 MHz to it.

This is also without instrumenting any database operations or I/O on the database side.

Are there performance considerations I'm missing? At this point, I feel like Basic Availability Groups won't work for my situation, but I want to be sure I'm not overlooking something. I've leaned heavily on these articles/blog posts:

Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption
AlwaysOn Availability Groups – What Not to do when Adding Databases

Finally, this is the PowerShell script I've been using to automate the creation of availability groups. I may be doing something incorrect here:

Backup-SqlDatabase -Database $DatabaseName -BackupFile $DatabaseBackupFile -ServerInstance "vm-sqllab1"
Backup-SqlDatabase -Database $DatabaseName -BackupFile $LogBackupFile -ServerInstance "vm-sqllab1" -BackupAction 'Log'

Restore-SqlDatabase -Database $DatabaseName -BackupFile $DatabaseBackupFile -ServerInstance "vm-sqllab2" -NoRecovery
Restore-SqlDatabase -Database $DatabaseName -BackupFile $LogBackupFile -ServerInstance "vm-sqllab2" -RestoreAction 'Log' -NORECOVERY

$PrimaryReplica = New-SqlAvailabilityReplica -Name "vm-sqllab1" -EndpointUrl "TCP://vm-sqllab1.domain:5022" -FailoverMode "Manual" -AvailabilityMode "AsynchronousCommit" -AsTemplate -Version 13
$SecondaryReplica = New-SqlAvailabilityReplica -Name "vm-sqllab2" -EndpointUrl "TCP://vm-sqllab2.domain:5022" -FailoverMode "Manual" -AvailabilityMode "AsynchronousCommit" -AsTemplate -Version 13

$AgName = "vm-ag-" + $DatabaseName
New-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\vm-sqllab1\Default\" -Name $AgName -AvailabilityReplica ($PrimaryReplica, $SecondaryReplica)  -BasicAvailabilityGroup -Database @($DatabaseName)
Join-SqlAvailabilityGroup -Path SQLSERVER:\SQL\vm-sqllab2\Default -Name $AgName
Add-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\vm-sqllab2\Default\AvailabilityGroups\$AgName -Database $DatabaseName

There are two parts to this. First, make sure you install CU1. Notably it contains a fix for

Performance issues occur when using the sys.dm_hadr_availability_replica_states DMV on an AlwaysOn Availability Group that contains many databases in SQL Server 2016

The second part is to use T-SQL instead of PowerShell. When I do it this way, the availability groups get created much, much faster and I'm not seeing any of the CPU/thread issues I complained about above. This is the code I'm using to create the Availability Groups with direct seeding. I've adapted it from this blog post:

SQL Server 2016: Availability Groups, Direct Seeding, and You.

On the primary:

CREATE AVAILABILITY GROUP [vm-ag-TestDb21]
WITH (BASIC,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE)
FOR DATABASE [TestDb21]
REPLICA ON
N'vm-sqllab1' WITH (ENDPOINT_URL = N'TCP://vm-sqllab1.domain:5022',
    FAILOVER_MODE = MANUAL,
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
    SEEDING_MODE = AUTOMATIC),
N'vm-sqllab2' WITH (ENDPOINT_URL = N'TCP://vm-sqllab2.domain:5022',
    FAILOVER_MODE = MANUAL,
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
    SEEDING_MODE = AUTOMATIC)

GO

And on the replica:

ALTER AVAILABILITY GROUP [vm-ag-TestDb21] JOIN
GO
ALTER AVAILABILITY GROUP [vm-ag-TestDb21] GRANT CREATE ANY DATABASE
GO


Changing master database compatibility level We did an upgrade of SQL Server 2014 to 2016. In the development environment we did an in-place upgrade, which left the master database at compatibility level 120. But in production we did migration to new server so there master is compatibility level 130.

I would like to have both environments as similar as possible. Additionally we have one application which is running its queries from the master database, so theoretically they can behave differently between environments.

Is it safe to change compatibility level of the master database from 120 to 130?

I'm not certain what you mean by one application "running its queries from the master database."  As you likely know, this would fall into the category of "worst practices."

The upgrade worked as expected in regard to the master database compatibility mode.  See Change the Database Compatibility Level and use the Query Store.  Unfortunately, this documentation doesn't give any guidance on whether it should be changed after the upgrade.

So if everything is working fine in production with the higher compatibility mode, then it should be safe to change the compatibility mode in development.  The only reason I say should be safe is that I've been in IT way to long to ever say anything will be safe.  If you aren't sure of why this change should be safe, then I would recommend that you do a bit of reading on what the compatibility mode actually does, and then it should be clear that it is OK to change it--particularly since you've already tested the configuration in production.

Additionally, I have not been able to find anything to suggest that it's not OK to change it, and I have found a couple of references that suggest it should be done:

https://www.sqlrx.com/master-database-compatibility-level/
https://ask.sqlservercentral.com/questions/119827/rolling-upgrade-to-2014-master-compatibility-issue.html

Lastly--this question is a day old and nobody has commented or answered with any warnings, and I'm quite certain someone would have already commented if there was any known significant risk.

In your case, since it is a development system and there is a production server partner with the upgraded compatibility mode, I would certainly get it upgraded.  If the compatibility mode has a negative affect on any queries, you will want to find that in development, not in production.  For most systems this wouldn't matter as applications aren't using code in the master database, but it sounds like you have some stored procedures there or something that may be impacted by the master database compatibility mode.


Select results inclusively based on NOW() My query is the following:

SELECT count(db.user_data.page_url) AS total_urls, db.authors.author
FROM db.authors
LEFT JOIN db.user_data ON db.authors.author_id = db.user_data.id
WHERE (db.user_data.date > (now() - INTERVAL 30 MINUTE))
AND db.user_data.domain LIKE '%example.com%'
GROUP BY author;

It returns all results between now() and last 30 minutes, that's fine. But those results are based on period (interval) of 30 minutes starting from current moment (in seconds).

How to select all rows based on now() - INTERVAL 30 MINUTE ,but including all results from the last 30-th minute inclusively? Because now the interval is "moving" by current moment + 30 last minutes.
So each new second I am getting different results, because of "sliding", "moving" interval?

Subtract the seconds part of NOW() from the value of NOW() and you will get the timestamp marking the beginning of the current minute. Now you can subtract your interval to get the beginning of the minute that was 30 minutes ago:

WHERE (date > (NOW() - INTERVAL SECOND(NOW()) SECOND - INTERVAL 30 MINUTE))


Question on Design for User + User Groups + User Types + Subscriptions Model I am redeveloping an existing web application with both weak source code and a weak underlying database design.  I'm trying to develop things in a way that future expansion will be much easier, namely:

Supporting new login mechanisms
Supporting new user types (ie new subscriptions plans)
Supporting new user roles

Here are a few screenshots of my unfinished schema.  Do you see any problems / data inconsistencies that could arise from this design?

Orange squares are primary keys, and blue squares are foreign keys.  Had to split this up into two images.

Users / Subscriptions / Auth Groups:

User Groups / Subscription Types:

Thanks in advance!

I think you overdid normalization:

I dont see the purpose of the user_names table, i would store the names in the user
table
Same for basic_user table, i would store the email and password in the user table as well
A subscription can have multiple statuses? if not i would put id_status in the subscription table


How to manipulate the query plan so that I can have more control on memory grants? Let's say I have a complex query and for this query there are huge
Differences between the estimated and actual execution plans.

These differences are causing spillage into tempDb as described here.

The problem is that in this query there are tables that are too big for me to update the statistics.

How can I manipulate the query plan so that I can have more control on memory grants?

I want to avoid memory spillage to tempdb.

In order for this question to be objective and not too broad, we could concentrate for instance on the hash join operator, when can I replace it by a merge join operator?

Are there other operators with memory grant that could be replaced depending on the work load?

Understanding SQL server memory grant

-- Search cache for queries with memory grants:
SELECT t.text, cp.objtype,qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist('declare namespace n=''http://schemas.microsoft.com/sqlserver/2004/07/showplan''; //n:MemoryFractions') = 1

I'm not sure I understand how a table is "too big" to update the statistics. When people start calling tables "too big" for anything, they usually start being candidates for table partitioning. In which case you can use incremental statistics to focus your stats updates on the active partitions (the optimizer won't use the per-partition stats independently, but it will still be beneficial for queries and reduces your maintenance because, presumably, not all of your partitions will have significant, or any, data fluctuations between every maintenance window).

Anyway, instead of trying to manipulate which operators are used in order to affect how much memory grant you have (which may make your performance or resource usage worse), why not use hints that control min/max grants directly? KB #3107401 talks about new query hints MIN_GRANT_PERCENT and MAX_GRANT_PERCENT that control the grant a query will get (the article says 2012 SP3, but these were also added in 2014 SP2).

Tread lightly, though. My suspicion is you are trying to solve the wrong problem with the wrong solution. If the source of the problem is bad estimates, fix that problem.


Restore Database from Database with Different Name I have 2 databases, DB1 and DB2.  DB1 has production/live data.  A script runs daily (takes about 3 hours) to fill DB2.  The goal is to restore DB1 with DB2 so that DB1 is down for as little time as possible.  I've restored databases before but never from a differently named one.  How can I do this?

Are you looking to do something like a re-name swap?  Something like this:

Load DB2
Rename DB1 to DB_temp
Rename DB2 to DB1
Rename DB_temp to DB2

Then you re-load DB2 again the next time?

If so you can simply alter the name of the DB rather than deal with the time a restore takes (the file names will remain the same but that really doesn't matter in this case.)

ALTER DATABASE CurrentDBName MODIFY NAME = NewDBName;

Now on the other hand if you actually want to keep DB2 in place and just restore over DB1 it would look something like this:

RESTORE DB1 FROM DISK = 'DB2 backup file' WITH REPLACE

There are several methods you can use to improve your speed.  Probably the easiest is to stripe your backup.

BACKUP DATABASE DB2 TO DISK = 'loc1', DISK = 'Loc2', DISK = 'Loc3' WITH FORMAT
RESTORE DB1 FROM DISK = 'loc1', DISK = 'Loc2', DISK = 'Loc3' WITH REPLACE

Warning, you will need to have all of the files you stripe to in order to do your restore.  If you lose one the whole backup is useless.


What load does SQL Server Reporting Services (SSRS) have on the server? Does SQL Server Reporting Services use a lot of server resources?
Is it okay to install SSRS on the same server as the SQL Server instance? What should I take into consideration?
Is the SQL Server instance performance affected by SSRS say when reports are generated?

Reporting Services  can be demanding in terms of memory and CPU, so it often makes sense to separate it from the database server element of SQL Server - obviously this isn't cheap in terms of licencing. One thing to bear in mind is that the metadata for SSRS is stored in a SQL database, but this can be on a different box. Where SSRS gets the business data from depends on where that data resides - it could be anywhere, but it might be a SQL database somewhere.

If your SSRS deployment uses subscriptions to generate reports out-of-hours, and your SQL database is used for other requirements during business hours, then it might make sense to keep them on one box.

Depending on your SQL edition, and your requirements, you can also treat the separate SSRS boxes like webservers, and have them in a high availability Network Load Balanced cluster - you can't cluster SSRS in the same way that you can with the database engine for high availability.

Like anything, try to test it all in a pre-production or Proof Of Concept environment.


Oracle: index vs. table stats In Oracle, when would I want to gather index stats, instead of just gathering stats on the table?  In which use case would index stats make a difference?

IMHO, I would not gather stats on indexes, I would gather stats on tables with cascade=>TRUE. But in 11g if you enable to maintenance job, it should gather stats for you, so unless you need stats generated a certain way you are probably ok with the default stats jobs that runs in the maintenance program.


What parts make up pg_database_size? I'm trying to write a munin plugin to graph DB sizes. Alongside using pg_database_size I want to graph the components thereof as well.

So far, I've come up with the following:

SELECT
    SUM(pg_relation_size(oid, 'main')) AS main_size,
    SUM(pg_relation_size(oid, 'vm')) AS vm_size,
    SUM(pg_relation_size(oid, 'fsm')) AS fsm_size,
    SUM(
        CASE reltoastrelid
        WHEN 0 THEN 0
        ELSE pg_total_relation_size(reltoastrelid)
        END
    ) AS toast_size,
    SUM(pg_indexes_size(oid)) AS indexes_size
    FROM pg_class
    WHERE reltype != 0 -- 0=indices, covered by pg_indexes_size

However, summing up those 5 values returns me something which is not the same as the result of pg_database_size. The difference seems to be less significant for larger DBs.

Example on a larger DB:

┌──────────┬────────┬─────────┬─────────┬──────────┬───────────────┬──────────────────┬─────────┐
│   main   │   vm   │   fsm   │  toast  │ indexes  │ sum_of_values │ pg_database_size │  diff   │
├──────────┼────────┼─────────┼─────────┼──────────┼───────────────┼──────────────────┼─────────┤
│ 72441856 │ 753664 │ 2392064 │ 4677632 │ 41377792 │ 116 MB        │ 111 MB           │ 5222 kB │
└──────────┴────────┴─────────┴─────────┴──────────┴───────────────┴──────────────────┴─────────┘
(1 row)

Example on a smaller DB:

┌─────────┬────────┬─────────┬────────┬─────────┬───────────────┬──────────────────┬─────────┐
│  main   │   vm   │   fsm   │ toast  │ indexes │ sum_of_values │ pg_database_size │  diff   │
├─────────┼────────┼─────────┼────────┼─────────┼───────────────┼──────────────────┼─────────┤
│ 2809856 │ 385024 │ 1351680 │ 557056 │ 2924544 │ 7840 kB       │ 6642 kB          │ 1198 kB │
└─────────┴────────┴─────────┴────────┴─────────┴───────────────┴──────────────────┴─────────┘
(1 row)

What am I missing?

Maybe related, maybe not: I'm shocked to see the index size. They are HUGE. Is something in my query wrong?

Here is a script I used to inspect the different values:

SELECT
    SUM(pg_relation_size(oid, 'main')) AS main,
    SUM(pg_relation_size(oid, 'vm')) AS vm,
    SUM(pg_relation_size(oid, 'fsm')) AS fsm,
    SUM(
        CASE reltoastrelid
        WHEN 0 THEN 0
        ELSE pg_total_relation_size(reltoastrelid)
        END
    ) AS toast,
    SUM(pg_indexes_size(oid)) AS indexes,
    pg_size_pretty(
        SUM(pg_relation_size(oid, 'main'))::bigint +
        SUM(pg_relation_size(oid, 'vm'))::bigint +
        SUM(pg_relation_size(oid, 'fsm'))::bigint +
        SUM(pg_indexes_size(oid))::bigint +
        SUM(
            CASE reltoastrelid
            WHEN 0 THEN 0
            ELSE pg_total_relation_size(reltoastrelid)
            END
        )::bigint
    ) AS sum_of_values,
    pg_size_pretty(pg_database_size(current_database())) AS pg_database_size,

    pg_size_pretty(
        SUM(pg_relation_size(oid, 'main'))::bigint +
        SUM(pg_relation_size(oid, 'vm'))::bigint +
        SUM(pg_relation_size(oid, 'fsm'))::bigint +
        SUM(pg_indexes_size(oid))::bigint +
        SUM(
            CASE reltoastrelid
            WHEN 0 THEN 0
            ELSE pg_total_relation_size(reltoastrelid)
            END
        )::bigint - pg_database_size(current_database())::bigint
    ) AS diff

FROM pg_class
WHERE reltype != 0;

You are counting the toast tables twice.  Once under their owner, as pg_total_relation_size(reltoastrelid), and again under their own entry in pg_class.  You should use relkind, not reltype, to filter what you want.

Also, trying to micromanage your RDBMS to this extent rarely pays off.


Blocking caused by Query Store. Unable to clear or disable I recently updated our 2016 SQL Server to SP2 and the most recent CU (KB4458621) released in August 2018. Just in the last day or so, I noticed that I have some blocking going on. I can't kill the SPID b/c it isn't a user process. According to SP_WHO2, the command is "Query Store ASYN". I've tried purging the data and disabling the query store via Script and through the UI. Nothing seems to work and it just spins and then starts to cause more blocking. Is anyone else having this issue? Can anyone please help me figure out how to disable the query store successfully? SP_WhoIsActive @show_System_SPIDS = 1 results below (query store results only)

UPDATE - This is now caused TempDB drive to fill up. Going to try a reboot in a few hours and see if that resolves the issue. Will keep you posted.

Thanks,
Nate

Based on your explanation, I believe this is related to KB4461562, and was resolved in the latest Cumulative Update 5.

  FIX: Transactions and log truncation may be blocked when you use Query Store in SQL Server 2016 and 2017


Copy SQL table to other server identity becomes int I just built an Umbraco website. This uses a not-that-small SQL Server db. I copied this DB using the export data function built in in SQL Server 2008.

This copies all the tables and their content but it sets (for the one table i know) the Identity(1,1) column to "int". This way my umbraco installation fails over this.

As I tried to change the column type to identity,  obviously I can't edit the columns.
Even if there was a way to do this manually, I'd still have to change most of the tables in the new database.

Is there some option or so I missed when copying? I'd like to make it copy with the included identity.

I tried backup, and restore but my SQL server edition are not exactly the same. The one is x86 and the other is x64, which is giving me compatibility issues.

Thanks in advance!

If you want to copy a DB from A to B, use the backup and restore commands. This might be easier for what you need to do.

If you want to export the tables: during the export process at the Select Source Tables and Views screen, click on the Edit Mappings button, then check the Enable identity insert check box.


How to create a dummy Linked Server in SQL Server 2012 I have a database in two different production servers. I want to keep the schemas consistent between the two. One has jobs that call stored procedures that reference a Linked Server. The other does not (and should not) contain those jobs or a Linked Server of the same name as that in the first server, but I want both servers to have those same stored procedures. However, SQL Server refuses to create the procedures that reference the Linked Server on the server that has no Linked Server set up. I understand why that happens, but I need a workaround that does not involve changing the procedure code (such as to use Dynamic SQL to hide the reference from the compiler).

I tried creating a Linked Server of the expected name that points to the local server, but SQL Server is smart enough to see that the referenced tables do not exist on that Linked Server. If I create one to a non-existent server, SQL times out during the creation of the procedure saying that it couldn't connect to the remote server.

Is there any way to create a dummy Linked Server such that SQL Server will not try to validate the table names on the Linked Server?

Create dummy, empty tables on the local server so that the procedure creation can happen.
Change the procedure code to use two-part synonyms. On the server that needs to use the linked server:

CREATE SYNONYM dbo.whatever FOR linkedserver.dbo.whatever;

On the local server that doesn't need the remote references to exist:

CREATE SYNONYM dbo.whatever FOR dbo.emptydummytable;

The stored procedure using the latter can be created thanks to deferred name resolution (which doesn't work when creating a procedure that references a linked server). This will require changing the stored procedure code, but it will be a one-time change.


Trigger Not Updating Do I have an issue with my syntax?  I created this trigger as sometimes the sales personnel forget to input the sale date (I Know it should be automatically captured which is an update in progress) but we are still seeing a few rows in the table with a null value for saledate  Is this accurate syntax to update the field saledate with GetDate() when saledate is null?  And I want to run this on Insert Or Update

create trigger updatefield
on salerecords
for insert, update
as
begin
  update salerecords
  set saledate = getdate()
  where saledate Is null
end

One problem with your trigger is that it is potentially updating all rows in the table, even if only one row was updated or inserted. If someone accidentally sets the values to NULL, they'll then get updated to the current time, the next time the trigger runs. Does this make sense for your application? I would think it makes more sense to leave past values alone (deal with those separately, though it seems doubtful any still exist), and only bother updating rows that are currently impacted directly by the trigger. You do that by joining to the inserted pseudo-table:

UPDATE sr SET saledate = GETDATE()
  FROM dbo.salerecords AS sr
  INNER JOIN inserted AS i
  ON sr.key_column = i.key_column -- you'll need to update this
  WHERE sr.saledate IS NULL;

That said, I agree with @SoleDBAGuy, who suggested a default constraint. Here is how you could implement that:

ALTER TABLE dbo.salerecords
  ADD CONSTRAINT df_saledate DEFAULT (GETDATE()) FOR saledate;
GO
ALTER TABLE dbo.salerecords
  ALTER COLUMN df_saledate datetime NOT NULL;

And then drop the trigger. Note that the second command may fail; if you have null values in there already, it's hard to justify just giving them "right now" as their sale date, when they could potentially be years old. So you can look at those individually, or just apply some date to them all, like:

UPDATE dbo.salerecords SET saledate = GETDATE();
-- or
UPDATE dbo.salerecords SET saledate = '19000101'; -- beware magic number

Finally, please always use the schema prefix.


Database modelling best practice question I am modelling a database for an application I am developing and I am in front of a choice for which I would like to know what the best practice is.

Let's say I have a Documents table and a Users Table. Documents can be generic or related to a user in three ways:

Created by the user, in which case he has access to the document;
About the user, in which case access to the document may or may not have been granted to him;
Tailored for that user, in which case access to the document may or may not have been granted to him yet;

I could model a Many-to-Many relationship with a table containing a DocumentId field, a UserId field, and a bit telling whether the user has access to the document:

￼

Another approach would be to have a Many-to-Many table with a UserId field and a DocumentId field to link Documents to Users that have access to them, and to also have a UserId field in the Documents table that is Null if the document is generic or that contains the ID of the user it is related to.

￼

The first approach looks tidier because there is only one UserId FK. The second approach however allows to filter Documents by user without the need to join a table.

What is the best practice here?

The first method is cleaner.  I would make a few suggestions, however.  You don't need the UserDocumentID surrogate Primary Key.  A composite key of UserID and DocumentID would suffice, while also provide a unique constraint for the junction table.  Also, is HasAccess really necessary?  I believe the existence of the record in the junction table is enough to indiciate that access is granted.

Do you have different levels of access?  Read-only, read/write, delete?

Hope this helps,

Matt


MariaDB: Disable the GSSAPI plugin How can i disable the GSSAPI plugin? I do not need authentication via Kerberos on my server.

This is my environment:

Centos 7.3 with SELinux mode enforcing
MariaDB Server: 10.1.19

In my log file /var/log/mariadb/mariadb.log i see the next errors:

[Warning] GSSAPI plugin : default principal 'mariadb/locahost.local@' not found in keytab
[ERROR] mysqld: Server GSSAPI error (major 851968, minor 2529639093) : gss_acquire_cred failed -Unspecified GSS failure.  Minor code may provide more information. Keytab FILE:/etc/krb5.keytab is nonexistent or empty.
[ERROR] Plugin 'gssapi' init function returned error.

I cannot find any related to that plugin in the configuration:enter code here

> SELECT PLUGIN_NAME FROM information_schema.PLUGINS WHERE PLUGIN_TYPE = 'AUTHENTICATION';
+-----------------------+
| PLUGIN_NAME           |
+-----------------------+
| mysql_native_password |
| mysql_old_password    |
+-----------------------+

> SELECT * FROM mysql.plugin;
Empty set (0.00 sec)

I only found this configuration file:

/etc/my.cnf.d/auth_gssapi.cnf

File /etc/my.cnf.d/auth_gssapi.cnf contains the line

plugin-load-add=auth_gssapi.so

Comment it out and restart the service. The server should stop loading the plugin, it will make the errors go away.

If you don't need the plugin at all, you can also uninstall MariaDB-gssapi packages

MariaDB-gssapi-server-10.1.19-1.el7.centos.x86_64
MariaDB-gssapi-client-10.1.19-1.el7.centos.x86_64


How to JOIN two table to get missing rows in the second table In a simple voting system as

CREATE TABLE elections (
election_id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255),

CREATE TABLE votes (
election_id int(11),
user_id int(11),
FOREIGN KEYs

for getting the list of elections a user has voted, the following JOIN is used

SELECT * FROM elections
JOIN votes USING(election_id)
WHERE votes.user_id='x'

but how to get the list of elections a user has NOT voted?

Use your existing query to get the opposite of the list you want. That list can then be checked against via NOT IN to get the desired list.

SELECT * FROM elections WHERE election_id NOT IN (
    SELECT elections.election_id from elections
    JOIN votes USING(election_id)
    WHERE votes.user_id='x'
)


Joining same table vs Joining different table in Mysql? Is there any difference between joining same table again and again and joining different tables? Internally it is just another join. But will it make a difference? Like If I join same table again and again only index of that table alone will reside in memory. So is there any other things like this? Or Will joining same table again and again degrade performance?

As you suspect, a JOIN does not care whether it is the same table or another table.  (Of course, you will have to use aliases to distinguish them.)

Would you care to show us some SQL; the question seems like a rather odd choice.


Percona/XtraDB Database with many of writes to a small table uses less CPU than the same database with less writes? I'm trying to understand why Percona and xtradb InnoDB/MySQL behaves the way it does and if there's anything I can change in the configuration to fix it.

I've a InnoDB database with only one table with about 10 rows.

`ID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`randomnumber` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`ID`)

When I insert a single row, about 1-2 seconds after commit the mysql process causes 30-40% CPU usage for about one second.

INSERT INTO status.statustest (randomnumber) VALUES ('1111111');

But when inserting one row every 500ms for 10 seconds (so in total 20 rows), the CPU spikes only after the last commit.

I found a similar question, but there was no answer to it:

I see this behaviour only with Percona and XtraDB, not with MariaDB.

My system:
2GB RAM
2CPU Cores
20GB SSD
Debian 9.9 x64
mysqld  Ver 5.7.26-29 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release '29', Revision '11ad961')

Has anyone found a solution as how to get rid of those CPU spikes and what causes them?

In all versions (Percona, XtraDB, MariaDB) of InnoDB, writes and index updates are delayed.  A separate thread(s) takes care of doing the writes 'eventually' and in the 'background'.

Data writes are put into the desired blocks immediately, but not written until they get bumped out of the buffer_pool.

UNIQUE indexes are updated immediately but left cached in the buffer_pool (like data).

Non-unique indexes are stored in the "Change buffer".  Later they are collected together for putting into the actual index blocks and eventually written to disk.

Redo log and double-write writes are done more immediately.

There are many settings relating to the frequency of background actions, sizes of caches, etc.  These have an impact on both CPU and I/O.  Normally it is best to leave them alone.

To further address your question, please provide the RAM size of the servers being tested and a diff of the SHOW VARIABLES.  The diff may help zoom in on the cause of the difference you are seeing.