Does large table variables fill up logs in tempdb? I'm running into an issue with a DBA who claims that table variables reside in ldf of the tempdb and when large amount of data is loaded into the table variable, the tempdb's logs fill up.

The DBA's solution is to use temporary table instead of table variable. While I see the justification to use the temp table in case of large data sets, I don't understand how temp table is created and stored in the mdf of tempdb where as the table variable is stored in ldf. Can someone please throw some light?

Your DBA is correct that actions on table variables are logged to the tempdb transaction log but so are operations on #temp tables so the proposed resolution doesn't help. Indeed if there is any surrounding user transaction the #temp table version may be worse as the tempdb log cannot be truncated until that completes.

Both have their pages allocated in the data file (mdf).

The understanding that the table variable is "stored in the ldf" is completely incorrect. All that gets written to the log is the information necessary to roll back the statement (required in case the statement encounters an error and needs to be rolled back)

You can see examples of the logging involved using sys.fn_dblog. The script below inserts a couple of rows in a table variable and #temp table and updates them.

Example Results

(The log records relating to the table variable show up with the system generated name #162F4418 in the output below)

+-----------------+----------+---------------------------------+-------------------+
|    Operation    | Context  |          AllocUnitName          | Log Record Length |
+-----------------+----------+---------------------------------+-------------------+
| LOP_MODIFY_ROW  | LCX_PFS  | dbo.#162F4418                   |                80 |
| LOP_FORMAT_PAGE | LCX_IAM  | dbo.#162F4418                   |                84 |
| LOP_MODIFY_ROW  | LCX_IAM  | dbo.#162F4418                   |                88 |
| LOP_FORMAT_PAGE | LCX_HEAP | dbo.#162F4418                   |                84 |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#162F4418                   |                72 |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#162F4418                   |                72 |
| LOP_MODIFY_ROW  | LCX_HEAP | dbo.#162F4418                   |               172 |
| LOP_MODIFY_ROW  | LCX_HEAP | dbo.#162F4418                   |               172 |
| LOP_MODIFY_ROW  | LCX_PFS  | dbo.#T___ ... _____000000000056 |                80 |
| LOP_FORMAT_PAGE | LCX_IAM  | dbo.#T___ ... _____000000000056 |                84 |
| LOP_MODIFY_ROW  | LCX_IAM  | dbo.#T___ ... _____000000000056 |                88 |
| LOP_FORMAT_PAGE | LCX_HEAP | dbo.#T___ ... _____000000000056 |                84 |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#T___ ... _____000000000056 |                72 |
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#T___ ... _____000000000056 |                72 |
| LOP_MODIFY_ROW  | LCX_HEAP | dbo.#T___ ... _____000000000056 |               172 |
| LOP_MODIFY_ROW  | LCX_HEAP | dbo.#T___ ... _____000000000056 |               172 |
+-----------------+----------+---------------------------------+-------------------+

Script

USE tempdb
GO

CHECKPOINT

GO

/*Table Variable*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, Description CHAR(100))

INSERT INTO @T VALUES (1, REPLICATE('A', 100)), (2, REPLICATE('A', 100))

UPDATE @T
SET    Description = REPLICATE ('B', 100)

/*Temporary Table*/
CREATE TABLE #T ([9E2E9F95-2B75-456B-BF2F-BAE7BCA4109F] INT, Description CHAR(100))

INSERT INTO #T VALUES (1, REPLICATE('A', 100)), (2, REPLICATE('A', 100))

UPDATE #T
SET    Description = REPLICATE ('B', 100)

/*Check the transaction log*/
SELECT  Operation,
        Context,
        AllocUnitName,
        [Log Record Length]
FROM   fn_dblog(NULL, NULL)
WHERE AllocUnitId IN (SELECT a.allocation_unit_id
                                FROM   tempdb.sys.partitions AS p
                              INNER JOIN tempdb.sys.system_internals_allocation_units AS a
                                         ON p.hobt_id = a.container_id
                                       INNER JOIN tempdb.sys.tables AS t
                                         ON t.object_id = p.object_id
                                       INNER JOIN tempdb.sys.columns AS c
                                         ON c.object_id = p.object_id
                                WHERE  c.name IN ('C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3',
                                                  '9E2E9F95-2B75-456B-BF2F-BAE7BCA4109F'))

DROP TABLE #T


How can i select columns from two tables using REF I'm new to Oracle's PL/SQL i'm using 11g. I have 2 Type Objects each one of them with has a table and third table where i keep references of this 2 objects, how can i retrieve values from objects tables using references in the third table.

First Type Object looks like:

CREATE OR REPLACE TYPE Teacher_T AS OBJECT
(
  teacher_id VARCHAR2(10),
  teacher_name VARCHAR2(20),
  teacher_address VARCHAR2(10),
  MEMBER PROCEDURE Insert_Teacher(new_teacher_id IN VARCHAR2,
                                  new_teacher_name IN VARCHAR2,
                                  new_teacher_address IN VARCHAR2),
  MEMBER PROCEDURE Delete_Teacher
)
/

And its table:

CREATE TABLE Teacher OF Teacher_T
(
  teacher_id NOT NULL,
  PRIMARY KEY (teacher_id)
);

Procedures definition:

CREATE OR REPLACE TYPE BODY Teacher_T AS
  MEMBER PROCEDURE Insert_Teacher(new_teacher_id IN VARCHAR2,
                                  new_teacher_name IN VARCHAR2,
                                  new_teacher_address IN VARCHAR2) IS
  BEGIN
    INSERT INTO Teacher
     VALUES (new_teacher_id, new_teacher_name,new_teacher_address);
  END Insert_Teacher;

  MEMBER PROCEDURE Delete_Teacher IS
  BEGIN
    DELETE FROM Teacher
     WHERE Teacher.teacher_id = self.teacher_id;
  END Delete_Teacher;
END;
/

Example to insert data:

declare
  tea Teacher_T;
begin
  tea := new Teacher_T('','','');
  tea.Insert_Teacher('1','Jhon tea1','Cty');
end;

Second Type Object looks like:

CREATE OR REPLACE TYPE School_T AS OBJECT
(
  sch_id VARCHAR2(10),
  sch_name VARCHAR2(20),
  sch_address VARCHAR2(30),
  sch_type VARCHAR2(15),

  MEMBER PROCEDURE Insert_Sch(new_sch_id IN VARCHAR2,
                              new_sch_name IN VARCHAR2,
                              new_sch_address IN VARCHAR2,
                              new_sch_type IN VARCHAR2),
  MEMBER PROCEDURE Delete_Sch
)
/

And its table:

CREATE TABLE School OF School_T(
  sch_id NOT NULL,
  sch_type CHECK (sch_type IN ('Primary', 'Secondary', 'TechC')),
  PRIMARY KEY (sch_id)
);

Procedures definition:

CREATE OR REPLACE TYPE BODY School_T AS
  MEMBER PROCEDURE Insert_Sch(new_sch_id IN VARCHAR2,
                              new_sch_name IN VARCHAR2,
                              new_sch_address IN VARCHAR2,
                              new_sch_type IN VARCHAR2) IS
  BEGIN
    INSERT INTO School
    VALUES (new_sch_id, new_sch_name, new_sch_address, new_sch_type);
  END Insert_Sch;

  MEMBER PROCEDURE Delete_Sch IS
  BEGIN
    DELETE FROM Teach_In
    WHERE Teach_In.school IN (SELECT REF(a)
                                FROM School a
                               WHERE a.sch_id = self.sch_id);
    DELETE FROM School
    WHERE School.sch_id = self.sch_id;
  END Delete_Sch;
END;
/

Example to insert data:

declare
  sch School_T;
begin
  sch := new School_T('','','','');
  sch.Insert_Sch('2','schName','NYC','Secondary');
end;

Finally references table:

CREATE TABLE Teach_In
(
  teacher REF Teacher_T,
  school REF School_T
);

Procedure def:

CREATE OR REPLACE PROCEDURE Insert_Teach_In(new_teacher_id IN VARCHAR2,
                                            new_sch_id IN VARCHAR2)
AS
  teacher_temp REF Teacher_T;
  school_temp REF School_T;
BEGIN
  SELECT REF(a) INTO teacher_temp
    FROM Teacher a
   WHERE a.teacher_id = new_teacher_id;

  SELECT REF(b) INTO school_temp
    FROM School b
   WHERE b.sch_id = new_sch_id;

  INSERT INTO Teach_In
  VALUES (teacher_temp, school_temp);
END Insert_Teach_In;
/

Example to insert data:

begin
  Insert_Teach_In('1','2');
end;

So my goal from this example is to retrieve teacher_name,teacher_address,sch_name,sch_type,sch_address using Teach_In table(references table).

you can do a simple join

select t.*,s.*
from Teacher t
join Teach_In r on r.teacher = ref(t)
join School s on r.school = ref(s)
;


SSMS: Restart the values on a IDENTITY field I am starting to use SSMS to manage a database and I am having a problem I would appreciate some help with.

I have a table that was created with

CREATE TABLE [dbo].[Machine](
        [Machine_id] [int] IDENTITY(1,1) NOT NULL,
        [Machine_alias] [nvarchar](50) NULL,
        [Machine_ip] [nvarchar](50) NULL,
        [Machine_stockWarning] [int] NOT NULL,
    .....
CONSTRAINT [PK_machine] PRIMARY KEY CLUSTERED
(
    [Machine_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Then I tried to add a row manually and the Machine_id became 1, but there was something wrong with this line so I deleted it.
Then I tried to add another row, but this time Machine_id is 2. How can I reinitiate the count so that it starts on 1 again?


How will large index INCLUDE fields affect system performance? This question is about SQL Server index performance with a varchar(2000) as an INCLUDE in a covering index.

I’m trying to improve performance in an slow and unstable database application. In some cases, data is accessed through large varchar strings, with the queries including multple string operations like SUBSTRING(), SPACE(), and DATALENGTH(). Here is a simplified example of access;

update fattable set col3 =
   SUBSTRING(col3,1,10) + '*' +
   SUBSTRING(col3,12,DATALENGTH(col3)-12)
from fattable where substring(col3,10,1) = 'A' and col2 = 2

The schema looks like this:

CREATE TABLE [dbo].[FatTable](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [col1] [nchar](12) NOT NULL,
    [col2] [int] NOT NULL,
    [col3] [varchar](2000) NOT NULL, ...

The following index has been defined, with a covering field on the large text column.

CREATE NONCLUSTERED INDEX [IndexCol2Col3] ON [dbo].[FatTable]  ( [col2] ASC )
    INCLUDE( [col3] )

From what I’ve read it is BAD to put large data fields in an index. I’ve been reading several articles, including http://msdn.microsoft.com/en-us/library/ms190806.aspx which discuss the impact of paging and disk size on index performance. This being said, the query plan definitely uses the covering index.  I don’t have enough information to determine how much this is actually costing me in terms of system load. I do know that overall, the system is performing poorly, and I am concerned that this is one of the issues.
Questions:

Is putting this varchar(2000) column in the index INCLUDE ever a good idea?
Since the INCLUDE fields are stored in leaf nodes, do they have much
impact index performance?

Update: Thanks for the excellent replies! This is an unfair question in some ways - as you guys say, there is no absolute right answer without actual statistics and profiling. Like so many performance issue, I guess the answer is "it depends".

Ever is a big word, but, in general, no, I wouldn't put a varchar(2000) field into an INCLUDE.

And yeah, the way that data is stored at the page level can seriously impact performance of the index, depending on how the index is used.

The thing is, the more rows of data you can cram into a page, the fewer pages have to get accessed, the faster your system is, for the most part. Adding a really large column means less information stored on a page, so, in the event of range seeks or scans, more pages have to be read to retreive the data, seriously slowing stuff down.

To know for sure if this is an issue on your query, or on your system, you'd have to monitor the reads, especially the number of pages that the query uses.


What is the system view for sys.syscolpars? I am checking system base table at https://msdn.microsoft.com/en-us/library/ms179503.aspx and system internal views at https://technet.microsoft.com/en-us/library/ms189600(v=sql.105).aspx

For base table sys.syscolpars, is there a corresponding system view for it so that I can access its data without loginning in DAC mode?

Thanks

The closest equivalent is sys.columns.

You can use

exec sys.sp_helptext 'sys.columns'

to see the underlying query used and how the columns are mapped.

CREATE VIEW sys.columns
AS
  SELECT c.id                                                             AS object_id,
         c.NAME,
         c.colid                                                          AS column_id,
         c.xtype                                                          AS system_type_id,
         c.utype                                                          AS user_type_id,
         c.length                                                         AS max_length,
         c.prec                                                           AS PRECISION,
         c.scale,
         CONVERT(SYSNAME, CollationPropertyFromId(c.collationid, 'name')) AS collation_name,
         sysconv(bit, 1 - ( c.status & 1 ))                               AS is_nullable,-- CPM_NOTNULL
         sysconv(bit, c.status & 2)                                       AS is_ansi_padded,-- CPM_NOTRIM
         sysconv(bit, c.status & 8)                                       AS is_rowguidcol,-- CPM_ROWGUIDCOL
         sysconv(bit, c.status & 4)                                       AS is_identity,-- CPM_IDENTCOL
         sysconv(bit, c.status & 16)                                      AS is_computed,-- CPM_COMPUTED
         sysconv(bit, c.status & 32)                                      AS is_filestream,-- CPM_FILESTREAM
         sysconv(bit, c.status & 0x020000)                                AS is_replicated,-- CPM_REPLICAT
         sysconv(bit, c.status & 0x040000)                                AS is_non_sql_subscribed,-- CPM_NONSQSSUB
         sysconv(bit, c.status & 0x080000)                                AS is_merge_published,-- CPM_MERGEREPL
         sysconv(bit, c.status & 0x100000)                                AS is_dts_replicated,-- CPM_REPLDTS
         sysconv(bit, c.status & 2048)                                    AS is_xml_document,-- CPM_XML_DOC
         c.xmlns                                                          AS xml_collection_id,
         c.dflt                                                           AS default_object_id,
         c.chk                                                            AS rule_object_id,
         sysconv(bit, c.status & 0x1000000)                               AS is_sparse,-- CPM_SPARSE
         sysconv(bit, c.status & 0x2000000)                               AS is_column_set -- CPM_SPARSECOLUMNSET
  FROM   sys.syscolpars c
  WHERE  number = 0
         AND has_access('CO', c.id) = 1


Try this:

SELECT z.id, z.name, z.step, jsonb_array_elements(z.magnitude)
FROM (
SELECT aa.id, aa.name, ao.key AS step, ao.value AS magnitude
FROM todos aa
CROSS JOIN LATERAL (SELECT * FROM jsonb_each(tags)) AS ao) z
ORDER  BY z.id;


Teradata: How to design table to be normalized with many foreign key columns? I am designing a table in Teradata with about 30 columns.  These columns are going to need to store several time-interval-style values such as Daily, Monthly, Weekly, etc.  It is bad design to store the actual string values in the table since this would be an attrocious repeat of data.  Instead, what I want to do is create a primitive lookup table.  This table would hold Daily, Monthly, Weekly and would use Teradata's identity column to derive the primary key.  This primary key would then be stored in the table I am creating as foreign keys.

The table would be designed like this:

ID  Type         Value
--- ------------ ------------
1   Interval     Daily
2   Interval     Monthly
3   Interval     Weekly
4   TimeFrame    24x7
5   TimeFrame    8x5

This would work fine for my application since all I need to know is the primitive key value as I populate my web form's dropdown lists.  However, other applications we use will need to either run reports or receive this data through feeds.  Therefore, a view will need to be created that joins this table out to the primitives table so that it can actually return Daily, Monthly, and Weekly.

My concern is performance.

I've never created a table with such a large amount of foreign key fields and am fairly new to Teradata.  Before I go on the long road of figuring this all out the hard way, I'd like any advice I can get on the best way to achieve my goal.

Disclaimer: I have never built a Teradata system, so I can't claim this from first-hand experience, but I will explain the reasoning.

I think that Teradata will be able to produce this view efficiently.  From what you say, it appears to do little more than join some very small dimension tables against a fact table.  The join operations will be relatively efficient.  Unless I misunderstand your requirements these columns are allowing your application to select various rollups of data from a multi-grain fact table.

Even though Teradata is a shared-nothing system, I can't see any requirement for the view to push large semi-joins across nodes or anything like that.

Beyond that, all I can suggest is that you suck it and see.  If you don't have anywhere to experiment you could download the express version of Teradata off their web site and see if you can prototype this structure to see what the query plan actually is.


Why are the initial queries up to minute slow on a 400M record PostgreSQL table? Consider the following query. The table consists of almost 400 million records and has appropriate indexes.
select
      r.id as id,
      r.text as body,
      r.answer as answer
from result r
where
  r.p_id = 12345 and
  r.dirty is null

p_id is the variable part of the query which selects the results of that poll. By querying a random p_id, the server takes as long as 60s to fetch results (~300K records). On consecutive calls with the same p_id, it replies almost instantly. I have validated those numbers with explain analyze, and they are both reported under Execution time (i.e. I'm not accounting for network transmission). It kind of makes sense, even though I don't know the internals of the database.
What I'm trying to improve is the initial time, but I'm not sure where to start. Can the query be further improved? Should I look into the configuration of the server? For the record, the machine seems idle with not a lot IOPS and load.

That are caching effects. The first time, data are read from disk, the other executions already find the data in RAM.
There are several options:

Have enough RAM in the machine to cache the whole table and the index. Then use pg_prewarm to load it into the cache before you begin.

Get faster storage.

Use a covering index (which may get large):
CREATE INDEX ON result (p_id) INCLUDE (id, text, answer) WHERE (dirty IS NULL);

See that the index is cached in RAM.


Count Assets Barcodes and Group by Rooms I have 2 tables Asset Register and LocationRoom. I want to count the number of asset barcodes in each room.

I want the output to be the list of Asset Barcodes, Description,RoomNo and count for assets by room.

for example Room R0001 has 4 assets (01780,01781,01782,01783).

http://sqlfiddle.com/#!18/c2cb3/2

This does Query uses XML string concatenation to get the result in the format you need.

If AssetID or Description can be NULL then you would have to add additional logic

SELECT  '('+reverse(stuff(reverse((
        SELECT A.[AssetID] +','
        FROM [dbo].[AssetRegister] A
        WHERE A.LocationRoomID = AR.LocationRoomID
        FOR XML PATH(''))), 1, 2,''))+')'
        ,
        '('+reverse(stuff(reverse((
        SELECT A.[Description] +','
        FROM [dbo].[AssetRegister] A
        WHERE A.LocationRoomID = AR.LocationRoomID
        FOR XML PATH(''))), 1, 2,''))+')'
      ,LR.[RoomNo]
      ,COUNT(*)
FROM [dbo].[AssetRegister] AR
INNER JOIN [dbo].[LocationRoom] LR
on AR.LocationRoomID = LR.LocationRoomID
GROUP BY LR.[RoomNo],AR.LocationRoomID;

Result

DB<>Fiddle

SQLFiddle

Edit:

  Thank you very much is it possible to get all the 19 rows each one
  with their AssetBarcode 01780, Dell Computer, R001, 4

SELECT  AR.AssetID,
        AR.[Description],
        LR.[RoomNo],
        COUNT(*) OVER(PARTITION BY LR.RoomNo)
FROM [dbo].[AssetRegister] AR
INNER JOIN [dbo].[LocationRoom] LR
on AR.LocationRoomID = LR.LocationRoomID

Result

AssetID Description RoomNo  (No column name)
01780   DELL LAPTOP R0001   4
01781   DELL MONITOR    R0001   4
01782   DELL CPU    R0001   4
01783   DELL KEYBOARD   R0001   4
01784   HP LAPTOP   R0002   4
01785   HP MONITR   R0002   4
01786   HP CPU  R0002   4
01787   HP KEYBOARD R0002   4
01788   APPLE LAPTOP AIR    R0003   8
...*(19 rows)*

DB<>Fiddle


Fast Backup and Restore of Growing Databases I work in Tech Support and the some of the products I support are heavily database dependent (PostgreSQL and MySQL, but most of the big ones are MySQL). Before we do an upgrade, we always take a full backup of the database, in case we need to roll back. Upgrades often perform modifications to the schema and there is no mechanism to reverse them if problems are discovered after upgrade.
The problem is that most of our customers have short windows to take the backup, do the upgrade, test the heck out of it, and potentially roll back. There are some long-term customers where the amount of time required to take a backup is now taking up a significant portion of the upgrade window.
Much of the data is no longer updated. A lot of it is not even used but, because reasons (let's just assume they're all justified), it is not purged from the database.
Is there a method where a large portion of the database could be set as read-only and treated separately? It would still need to have schema massage done when required but could be done to a copy outside of the upgrade window.
From what I've read of partitioning, it seems like a candidate. If I understand correctly, large tables can be split and remain the same table. Or does it make more sense to move the old data to different "archive" tables that are unchanging, except for when more data is added.
Would differential backups also do what I want?
I'd like a more fully formed pitch than what I have now before I suggest it to R&D. I'm fine with doing more research, but lack the practical experience to know what avenues to investigate.
Thanks.
2020-11-21 UPDATE: LVM and Storage Spaces
With some research complete, Storage Spaces is Microsoft's current (fairly well reviewed) implementation of what Linux has been good at for a while, LVM. We have both Windows and Linux servers in use.
The nice thing about that approach is that it shouldn't require any modification to or accommodation by our applications; the magic is done at a level below their awareness. Of course, everything would still need to be tested, but I haven't encountered anything discouraging.

Concerning PostgreSQL, I can think of two options:

Low level solution:
Use a storage system or a file system that supports snapshots and take a snapshot before the upgrade.
After the test, either discard the snapshot or restore to it.

Database level solution:
Build up a streaming replication standby database and stop replication before you upgrade.
If the test is fine, discard the standby. If the test fails, discard the master database, promote the standby and continue using that.


Can this be further normalized? I have to define a system where we will be storing Image data and which can have an arbitrary number of attributes for the Image added per client. I have designed the following table

Image

ImageID ImageTypeID Data
1       1           0x123...
2       1           0x123...
3       2           0x123...

ImageType - a predefined list of supported Image types

ImageTypeID IsoCode Name
1           12      Hitachi
2           1       Signotec

PropertyType - metadata for each arbitrary property. Can include columns for IsMandatory, datatype, minlength, maxlength etc but not needed at the moment

PropertyTypeID Name
1              AccountNumber
2              PIN

Property - binds PropertyType with Image

PropertyID PropertyTypeID ImageID
1          1              1
2          2              1
3          1              2

PropertyString - actual value of the property, right now we only care for string values as there is no need to do any computation on the values

PropertyID Value
1          0123456789
2          0123
3          9876543210

Given the above design I can query my database like so

DECLARE @ImageCode INT = 12;
DECLARE @PropertyName1 VARCHAR(64) = 'AccountNumber';
DECLARE @PropertyValue1 VARCHAR(1024) = '201602101821';
DECLARE @PropertyName2 VARCHAR(64) = 'PIN';
DECLARE @PropertyValue2 VARCHAR(1024) = '1234';

WITH prop1 AS (
    SELECT P.ImageID, PS.Value
    FROM PropertyType PT
    INNER JOIN Property P ON PT.PropertyTypeID = P.PropertyTypeID
    INNER JOIN PropertyString PS ON P.PropertyID = PS.PropertyID
    WHERE PT.Name = @PropertyName1
), prop2 AS (
    SELECT P.ImageID, PS.Value
    FROM PropertyType PT
    INNER JOIN Property P ON PT.PropertyTypeID = P.PropertyTypeID
    INNER JOIN PropertyString PS ON P.PropertyID = PS.PropertyID
    WHERE PT.Name = @PropertyName2
)
SELECT I.ImageID, I.Data
FROM Image I
INNER JOIN ImageType IT ON I.ImageTypeID = IT.ImageTypeID
INNER JOIN prop1 P1 ON I.ImageID = P1.ImageID
INNER JOIN prop2 P2 ON I.ImageID = P2.ImageID
WHERE IT.IsoCode = @ImageCode
    AND P1.Value = @PropertyValue1
    AND P2.Value = @PropertyValue2;

Each client will have a predefined set of extra Image attributes which will be used to narrow down the search (as shown above). The query itself will be generated based on the configured PropertyTypes.

Can this be further normalized? Is there any other way to provide the flexibility of having arbitrary number of field/values?

I don't see any way you could normalize this further. In fact unless each property could have more than one value I would denormalize a bit by combining the Property and PropertyString tables.

select I.ImageID, I.Data
  from Image I
  join ImageType IT on I.ImageTypeId = IT.ImageTypeId
  join Property P1 on I.ImageID = P1.ImageID
  join PorpertyType PT1 on P1.PropertyTypeId = PT1.PropertyTypeId
  join Property P2 on I.ImageID = P2.ImageID
  join PorpertyType PT2 on P2.PropertyTypeId = PT2.PropertyTypeId
 where IT.IsoCode = @ImageCode
   and PT1.Name = @PropertyName1 and P1.Value = @PropertyValue1
   and PT2.Name = @PropertyName2 and P2.Value = @PropertyValue2

I haven't tested it, but my guy says that this will perform better than having separate subqueries per property you are checking. If you're really want to maintain the PropertyString table you still could include it in this join.


Discrepancy in CPU time and reads in SQL Server profiler I had performance degradation in our production database server which is SQL server 2016 and I saved the long running query using profiler as you can see
in the following picture:

CPU time = 65141, Reads = 36474959, Duration = 114546

I have saved the trace file and open later, so I'm not sure if duration is millisecond or microsecond.

But, now when I run the same query on prod outside of business hours, the query run fast and "set statistics IO on" shows only few data pages being read, my assumption was CPU time and reads of a query should be constant regarding of execution time of a query despite of duration, any thought?
Should I consider this query as inefficient based on the numbers?

@DanGuzman correctly pointed you to Sommarskog's excellent article, which you should read.

Then, if the below simple steps don't work, try looking at the plan cache and seeing what's in there.  Or use the Query Store, since you're on SQL 2016, to see how many different execution plans there are.

You're comparing SET STATISTICS (anything) to Profiler.  Don't do that.  It doesn't work.  Profiler and Extended Events capture relatively accurate numbers.  SET STATISTICS (anything) does NOT - as a single example, it doesn't capture function cost!
You're trying to match plans done at different times, probably with different statistics.  Stop that.
Go ahead and update statistics for ALL tables involved in the suspect query, preferably with FULLSCAN, bother to see if that changes the behavior, and before your "run it myself" tests.
Look at the query itself.

Is parameter sniffing involved?
Is it thousands of lines long once reasonably formatted, with dozens of joins, often to the same table?  If so, quit having Entity Framework try to create complex SQL statements on its own.  It does a terrible job.


Percona Xtradb Cluster : How to speed up insert? I recently installed a 3 full master node cluster based on Percona Xtradb (very easy install). But now i need to make some tuning to increase INSERT/UPDATE requests. Actually, i made around 100 insertions every 5 minutes, but also made around 400 update in the same time. All this operation take less than 3 minutes when i was on a single server architecture. And now, with 3 node server, it takes more than 5 minutes ...

Is there any tuning i can do to speed up this operations ?
Here is my actual cnf configuration :

[mysqld]
datadir=/var/lib/mysql
user=mysql

wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://dbnode01,dbnode02,dbnode03

binlog_format=ROW
default_storage_engine=InnoDB
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
wsrep_node_address=1.2.3.4
wsrep_cluster_name=my_cluster
wsrep_sst_method=xtrabackup
wsrep_sst_auth="user:password"

Here are the 3-server hard config :

Node#1

CPU: Single Processor Quad Core Xeon 3470 - 2.93Ghz (Lynnfield) - 1 x 8MB cache w/HT
RAM: 8 GB DDR3 Registered 1333
HDD: 500GB SATA II

Node#2

CPU: Single Processor Quad Core Xeon 1270 V2 - 3.50GHz (Ivy Bridge) - 1 x 8MB cache w/HT
RAM: 4 GB DDR3 1333
HDD: 1.00TB SATA II

Node#3

CPU: Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz (4-Cores)
RAM: 32G
HDD: 2T

UPDATE

Actualy there's around 2.4M records (24 fields each) in the table concerned by the INSERT/UPDATE statements (6 fields indexed).

By its Nature in a Galera Cluster a DML statement is expected to run a bit slower than a DML statement on a normal MySQL node because the response time of the DML statements includes not only the commit time on the local node but also sending the the write-set (ws) to the Group and receiving back the GTID of the ws.

So the total response time is: query time + 2 x round-trip to the group.

A simple insert will run possibly within 1 ms. And the round-trip to the group is possibly about 400 - 600 us. So 5 instead of 3 minutes I am not surprised to see...

Now, what can we improve?

a) Reduce query time:
Write performance is influenced by: innodb_buffer_pool_size (big enough to cache all data in memory), innodb_log_file_size (as bigger as better) and innodb_flush_log_at_trx_commit (0 or 2 are faster than 1). With your setting above you have not followed the Codership recommendation (innodb_flush_log_at_trx_commit=0).

b) Reduce number of round trips. When you batch your DML statements into transactions you have bigger but less write-sets which should cause less round trips. So your time spent in the network should become smaller.

c) Make your network faster. Use smallest possible network latency: dedicated network. 1 or 10 Gbit. Not much hardware in between (firewall, switches, routers, etc).

d) Parallelize your inserts?

Regards,
Oli

PS: There are still some places free in our Galera Cluster trainings in 2 weeks!


MySQL show data from Rows to columns I am stuck at getting a query with joining 2 tables. Below is the database structure.
What I want to achieve is to list all the information from one table with join on the second table but it has 3 conditions. I have written the following SQL but it's not working.I want only 2 rows returning all rows data in each 3 columns.
Please advice.

Tables are:

My SQL:

SELECT b.*,
       CASE WHEN pc.caster_type = '1' THEN pc.name END as  primary_caster ,
       CASE WHEN pc.caster_type = '2' THEN pc.name END as  secondary_caster ,
       CASE WHEN pc.caster_type = '3' THEN pc.name END as  caster
FROM broadcasts b
left join rel_braodcast_casters pc ON pc.fk_broadcast_id = b.broadcast_id ;

Getting this result:

I do not want multiple records for each broadcast. I want to replace NULL values with corresponding rows of data.

Thanks in advance.

Finally I got the answer. Below is the fiddle.

sqlfiddle.com/#!9/83f530/6 or sqlfiddle.com/#!9/83f530/7

Thanks to Akina who helped me a lot. I will prefer to use single join SQL over multiple join fiddle.


MySQL Tables Require Daily Repairs - Server, Table or HD? I've been experiencing a weird issue with one of MySQL DBs. Every day, sometimes 2-3 times per day, I'll need to repair the tables. The MySQL DB has 25 tables with 5.6m rows in total.

The bigger ones are:

Table A - 599k rows / 867MB
Table B - 2.1m rows / 146MB
Table C - 2.2m rows / 520MB

It seems table C needs to be repaired pretty frequently, Tables A & B not as much.

When the table needs to be repaired, I'm not seeing it being marked as crashed or in use. But through other tools, I can see the data is not what it should be.

When I do repair the table, I'll see a message similar to:

[table c] repair info Wrong bytesec:  54-55-102 at 368251940; Skipped
[table c] repair warning Number of rows changed from 2127934 to 2127931

or

[table c] repair info Wrong bytesec:  171-30-101 at 341237312; Skipped
[table c] repair warning Number of rows changed from 1984585 to 1984582

I've tried making adjustments in my.cnf but no difference.

The server is a cloud server running both MySQL and Apache. Plenty of space available on all HDs:

Filesystem            Size  Used Avail Use% Mounted on
/dev/xvda2             99G   14G   80G  15% /
tmpfs                 1.9G     0  1.9G   0% /dev/shm
/dev/xvda1             97M   49M   44M  53% /boot
/dev/xvdc1            296G   25G  257G   9% /data

I'm not sure if this is a problem with the cloud HD, the server or the tables themselves. The problem didn't start happening until about 2 months ago and the size of the DB has only changed by 300-400MB until now.

Any idea what I should be looking at to verify where the problem might be?

Using MySQL v5.1.66 and MyISAM

Thanks in advance.

Best, Cent

You should get away from MyISAM as soon as possible. Why ???

MyISAM caches only index pages in the Key Buffer
InnoDB caches Data Pages and Index Pages in the InnoDB Buffer Pool

I have mentioned this many times before

May 06, 2013 : MySQL high CPU usage (MyISAM table indexes)
Apr 08, 2013 : If MySQL isn't caching, then what is?
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options
Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Apr 04, 2011 : What are the main differences between InnoDB and MyISAM?

This is very critical because data pages must rely solely on the OS for cached writes to the .MYD of a MyISAM table. In a highly trafficked website, the OS will experience a glut in memory with swapping as a consequence and foregone conclusion. An OS crash or a mysqld crash  will yield corrupt MyISAM tables for every MyISAM table that was open and not fully flushed.   A crashed MyISAM table could simply be just a bad open file count in it header. In other case, it could be incomplete writes to the .MYD file. In the two repair messages in the question, you lost three row both times.

IMHO this problem is very apparent when running MySQL in Windows (Imagine : Windows handling disk writes ???) and no less different for Linux. MyISAM will suffer just the same.

I highly recommend three(3) things:

Switch to InnoDB
Increase installed RAM
Configure as much buffer pool as possible

Give it a Try !!!


DBCC CHECKDB ran out of memory We have a routine DBCC CHECKDB running at night when the database is still accessible but with very little traffic.  Since last month we had several occasions of DBCC CHECKDB crashing to this error:

  DBCC CHECKDB (Database1) WITH no_infomsgs executed by sqladmin terminated abnormally due to error state 5. Elapsed time: 0 hours 47 minutes 9 seconds.

This is preceded by several SQL Alert Severity 17, which is insufficient resources, as well as output from DBCC MEMORYSTATUS in the SQL server log.  Thus I believe DBCC CHECKDB crash due to insufficient memory.

Running DBCC CHECKDB again would return no error.  A non DBA even did it once during business hour, while it drag down the performance and it took nearly 3 hours to complete, it did not result in a memory problem.  (He was told not to do it again).

The server itself has 12GB of RAM, but there is no minimum and maximum limit set for SQ server.  SQL Server itself use about 10GB of memory, while all other processes use 1GB.  I am not aware of anything else taxing the server at that time.

edit:

there is no min or max memory limit set, original question is missing the key word "no"
there is enough room for temp DB to grow
there was no disk space alert on any of the volume, so I assume disk space is not an issue
database is about 50GB in size
I am not aware of any system changes other than Windows Update
Normally CHECKDB would take 60-90 minutes to complete at night

The statement "Since last month we had several occasions of DBCC CHECKDB crashing to this error..." makes me think that there was a change done on your system last month that may have caused this.  Was anything implemented last month to your knowledge?  For example any patches, updates to SQL Server, and applications were installed or upgraded on the SQL Server, etc...

Have you tried the below post to see if the issue is an OS Memory issue or if the error is caused by SQL?

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ff936d46-d210-4326-a112-0501a366ea41/dbcc-checkdb-failure-sql-2012-there-is-insufficient-system-memory-in-resource-pool-internal-to?forum=sqldatabaseengine

You could try to split out the DBCC CheckDB command to help alleviate any resource constraints.

Dividing DBCC CHECKDB over multiple days


Synchronizing two MySQL Server both serving clients after reconnect I am not a pro in DB...here is my case.

I have 2 MySQL databases on ServerA and ServerB and clients in 2 buildings, with only network connection between them and no more outer network available.

I am requested to sync the data and use each of them as backup:

When ServerA dies, clients in A will go to ServerB
When ServerA is up, it will sync back the data from ServerB.

The problem is when the connection between ServerA and ServerB is down, ServerA and ServerB should continue serving clientsA and clientsB making data not sync.

What should I do to sync them?

If you set them up with dual-master replication, they will be continually in sync.  But don't write to both at the same time.


Does partitioning help with lookups and inserts/updates by primary key? I'm using MariaDB 10.1 and the default InnoDB storage storage, and I have a few tables with currently 10 to 100 million rows. These tables will keep growing a few  million per month, and it's mostly caching.

They either have a single-column primary key (BIGINT) or a composite primary key (two BIGINT columns) and no AUTO_INCREMENT, and I always insert, select or update by primary key. I also do a lot of joins by primary key, or selects WHERE PK IN (1, 2, 3, 4...).

Also, these tables receive lots of updates per hour, and I usually update them in batches of 5000 or 10000 at a time. We have more inserts and updates than selects for some of these tables.

I have 3 questions:

It seems to me that simple selects by PK returning 1 row (SELECT x, y FROM table WHERE pk = 123) will have no actual difference in performance with partitioning. Is that right?
What about joins or selects as WHERE PK IN(SELECT PK FROM ...)? Will it cause more scans to join a partitioned table than a single table?
Considering I usually do a lot of concurrent batches (multiple servers may send data at the same time) using:

INSERT INTO X VALUES (1, 'A'), (2, 'B'), ... ON DUPLICATE KEY UPDATE ... or
REPLACE INTO X VALUES(1, 'A'),  (2, 'B'),...

Will partitioning help with concurrent inserts and updates, say by being able to affect multiple partitions at the same time?

Thanks in advance.

Your questions

Q1:  If PK is the Partition key, a "point query" (WHERE PK = constant) on the Partition key will do "pruning", then (hopefully) use an index in the single partition it found.  No improvement over having a simple table with a suitable index.
Q1:  If PK is the PRIMARY KEY, but not the Partition key, then the query must open and look in every partition to find the row, hence slower.

Q2:  Generally IN ( SELECT ... ) performs poorly, very poorly in some cases, and should be avoided.  Use a JOIN instead.  For partitioning, "pruning" will probably not be used.  Hence slow.

Q3:  INSERT may be about the same speed, regardless.  A plain table may be faster due to less complexity.
Q3:  REPLACE is DELETE + INSERT.  INSERT ... ON DUPLICATE KEY UPDATE ... is usually a better construct; see if it applies.  But, again, no performance improvement.

IN

WHERE PK IN (1, 2, 3, 4...) -- I think I heard that if the list is 'short', it will use partition pruning.  Or skip the pruning if the list is long.  In either case, a simple table with an index on PK will be at least as fast.

Pruning

Think of pruning this way:  First it must find the partition (which acts very much like a "sub table"), then it must use an index (if available) to drill down to the desired row(s).  Without partitioning, it skips the pruning step; but the BTree index is slightly deeper.  So it is a trade-off.

Note:  This often means that the optimal set of indexes for a partitioned table and the equivalent non-partitioned table are different.

Case 3

The caveat "A table's index is too big to be cached, but the index for one partition is cacheable" does not seem to apply to your situation.  Case 3 came from a time-series wherein most of the activity was in the latest partition (and PARTITION BY RANGE(TO_DAYS(...))) and that partition fit in the buffer_pool but the entire table did not.

(That is, I agree with Natan, not Jarwad, about the relevance of Case 3.)

BY HASH

BY HASH -- not useful.  (From the link:  "PARTITION BY RANGE is the only useful method.")  (Or at least, I have yet to see a use case of Hash where performance benefits.)

Ingestion

"growing a few million per month" That's not very fast.  "growing a few million per day" would begin to be challenging, at which point I would point you at my high speed ingestion blog .  Even so, you might take one tip from there -- Load the changes into a transient table, then do the insert/replace/iodku/update/etc from it to the 'real' table.


SQL Job fails due to QUOTED_IDENTIFIER = OFF I have a SQL agent job that runs nightly at 2am successfully for the last year;  We haven't made any change to that job specifically but it suddenly gives error:

  MERGE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operation

I read around and looks like a quick fix is to insert the command "SET QUOTED_IDENTIFIER ON" at the very top of this job.

However, I really want to know why this happens? the job does not call any store procedure, instead it uses merge, insert, update using some views, tables.

I verified all my views and tables have quoted_identifier set to true which I believe is the default value.

I ran the following script and I see that my SQLAgent - Job Invocation Engine has quoted_identifier set to off.

SELECT *
FROM   sys.dm_exec_sessions
WHERE  is_user_process = 1
AND    quoted_identifier = 0 ;

This perhaps explains why my job fails?  But why it fails all of a sudden with the same job that ran successful the night before?

Could a script that adds new index or alter a store procedure caused this to quoted_identifier value to false? can a database restart do it?

Clearly something has caused this to be changed

SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods.

This can be set at the ODBC/OLEDB/ADO.NET layer, or at the SQLAGENT

MSSQL Client Config

Something changed the SQLAgent script or modified the Job parameters

If there is script running that alters the stored procedure and dropped the SET command.  Yes that could do it..  Why would this stored procedure need to be altered??


Same statement yields NULL when in a stored function, and non-NULL valid result otherwise I have the following stored function for calculating ELO ranking:

CREATE FUNCTION spodziewanyWynik(mój INT, przeciwnika INT)
RETURNS DOUBLE DETERMINISTIC
BEGIN
  DECLARE wynik DOUBLE;
  SET wynik =
    1.0E0 / (1.0E0 + POWER(1.0E1, przeciwnika - mój + 0.0E0) / 4.0E2);
  RETURN wynik;
END//

CREATE FUNCTION nowyRanking(mój INT, przeciwnika INT, wynik DOUBLE)
RETURNS INT DETERMINISTIC
BEGIN
  DECLARE wynik INT;
  SET wynik =
    mój + ROUND(3.0E1 * (wynik - spodziewanyWynik(mój, przeciwnika)));
  RETURN wynik;
END//

Now I want to test it. I run:

SELECT nowyRanking(1500, 1500, 0.0E0);

And I get a NULL, for reasons unknown to me.

And now the fun part. I run:

SELECT 1500 + ROUND(3.0E1 * (0.0E0 - spodziewanyWynik(1500, 1500)));

And I get: 1470. Whew, that’s quite an improvement from a NULL, isn’t it?

Why on Earth does this happen? How should I fix my stored function?

Thanks in advance.

Your problem is due to the same variable name is used for the parameter and local variable. i.e, wynik used as INT in the local variable as well as DOUBLE in the parameter of the nowyRanking function.

In this case changing the parameter name will solve the issue. In the below code I renamed the parameter name wynik DOUBLE to wynikDouble DOUBLE and handled the same in the query to solve the problem.

Answer for How should I fix my stored function?

CREATE FUNCTION nowyRanking(mój INT, przeciwnika INT, wynikDouble DOUBLE)
RETURNS INT DETERMINISTIC
BEGIN
  DECLARE wynik INT;
  SET wynik =
    mój + ROUND(3.0E1 * (wynikDouble - spodziewanyWynik(mój, przeciwnika)));
  RETURN wynik;
END

Working fiddle: http://sqlfiddle.com/#!9/76b5b/3

Answer for Why on Earth does this happen?

Update: In your actual code you declared the wynik as INT and used in the SET wynik = mój + ROUND(3.0E1 * (wynik - spodziewanyWynik(mój, przeciwnika))); block, here the wynik value is NULL only.

So the query is execute as SELECT 1500 + ROUND(3.0E1 * (NULL - spodziewanyWynik(1500, 1500))); that's why you are getting NULL as result.


Match phone numbers efficiently For a mobile application we are considering validating a user based on their mobile phone number.
In the app we will restrict the user input so that it will be stored in the following format:

+46700112233

That would mean in this case a Swedish phone number (+46), remove the initial 0 and then keep the remaining 9 digits (mobile phone numbers here are 10 digits including the leading 0).
However, the data currently saved in database include a lot of rows and no validation was made upon input. Meaning that the numbers saved could be in many different formats including

0700112233

070-0112233

460700112233
and so on.

So the type of the column in the database would probably be varchar.
At this time I'm unable to actually update the numbers already saved in the database to one general format, using for instance libphonenumber. I would prefer to limit the number of actual queries to the database as well.
The database currently includes phone numbers for about 10+ countries all saved in different formats. The DBMS used is MySQL.
What would be an efficient way to match the numbers accurately without querying the database for any of the possible formats?

The right thing to do is to fix the data as it goes in.  Then you have only digit strings in a 'canonical' format.  This includes prefixing with the country code.  (However, you may want to keep the leading '+' just so users looking at the database will know that the country code is there.)

You will not get reasonable performance if you must repeatedly fix the numbers on the SELECT side.


postgresql logical replication -- subscriber size discrepancy I have a postgres 11 database in Amazon RDS with a little over 2 TB data in it whose tables have incrementing integer ids that are nearing exhaustion.  I've been vetting using logical replication to have the existing db synch data to a new db with a schema modified to have bigint ids.  Everything looks ok with a lot of due diligence and I am about ready to make the cutover.

One thing I have noticed is that the subscriber database being a smaller overall size, even though many of its columns and indexes have been switched to using bigint ids vs ints.  Does anyone have any idea why this might be the case?  This is the one question I have not been able to discover a confirmation for vs my guesses (fragmentation, etc...).

Using the queries described here, I have the following usage:

total: 2.171 TB in publisher, 2.020 TB in subscriber
indexes: 0.737 TB in publisher, 0.581 TB in subscriber
toast: 0.212 TB in publisher, 0.215 TB in subscriber
tables: 1.223 TB in publisher,1.223 TB in subscriber

This is to be expected and no problem.

When logical replication starts, it copied the existing data from the source tables. The data in the target tables will then be densely packed and consume less space.

As replication proceeds, the target tables get modified and “dead tuples” will accumulate and be removed by autovacuum. The tables will become slightly “bloated” over time, which is normal and healthy.

Since you don't plan to use logical replication for a long time, the replica will probably never grow as big as the original.


MySQL Seconds behind Master fluctuation I have two servers set up in Master-Master with one additional Slave connected to the first master. Lately I've started getting alerts from monyog that Master 1 has a seconds_behind_master value of 1109. Oddly enough, it's ALWAYS 1109. If I log into the server and just do show slave status\G over and over, every 10-15 seconds it'll show 1109 in seconds_behind_master and then immediately goes back to 0. Then 10-15 seconds later it'll go back to 1109, then back to 0.

I found this article about the issue I'm experiencing, Unfortunately the resolution was unrelated to what's happening on my server. I did show slave status until I got it to show 1109 again, then I looked at the relay log position, looked it up in the relay log and checked all events for several seconds before and after and all events came from master 2, as expected. All of the timestamps were in order, too.

Any ideas?

I have dealt with this pesky 0 to infinity and back to 0 issue with Seconds_Behind_Master. Here are my past posts where I have addressed this:

Jun 17, 2011 : MySQL Replication Lag Behaving Erraticly
Sep 24, 2012 : MySQL replication - slave is continuously lagging behind master
May 04, 2014 : MySQL 5.6 showing wrong "second_behind_master"

Although there is a bug fix in place based on binlogs not being flushed properly and if you configure sync_binlog=1 on Master1 and Master2, it may exacerbate the problem.
Even in a perfect world for Binary Logs, sometimes there will be occasions when queries will bunch up on one Master and have the same timestamp. When those queries are completed in parallel on a Master, every Slave processes them in a serialized manner.
EXAMPLE
If 60 queries take one second to run on a Master and all of them complete at the same time...

all 60 queries will be written to the Master's Binary Logs on-by-one
all 60 queries will have the same timestamp
each of the 60 one-second queries are executed one-by-one of the Slave
The timestamp of each query won't move while NOW() on the Slave is moving (The difference between NOW() on the Slave and the timestamp of the query from the Master is Seconds_Behind_Master)
When the first 59 queries are processed, Seconds_Behind_Master will be 59
When the 60th query is processed, Seconds_Behind_Master drops to 0

Given this computation of Second_Behind_Master and Master1 records multiple queries in the same timeframe, Master2 will always experience this time warp.
SUGGESTION
You could try zapping all the relay logs on the Master2.

On Master1, STOP SLAVE;
On Master2, SHOW SLAVE STATUS\G
On Master2, get LOGFIL from Relay_Master_Log_File
On Master2, get LOGPOS from Exec_Master_Log_Pos
On Master2, CHANGE MASTER TO master_log_file='LOGFIL',master_log_pos=LOGPOS;
On Master2, START SLAVE;
On Master1, STOP SLAVE;

Once you run these steps, you have a clean set of relay logs on Master2, but you still subject to Slave Lag emanating from Master1 due to multiple queries completing at the same time.
All the numbers I mentioned in the Example assumes that Master1 and Master2 have identical  Storage Engine configs, identical OS, identical hardware, identical disks, and excellent network communication. If Master2 is inferior to Master1 in any of these aspects, perhaps the 60 seconds to process the queries on the Slave will be more like 70 or 80 seconds.
If the hardware, software, and Storage Engine configs are not identical, then make them identical and restart MySQL as needed.
These are the facts. There is no "Magic Bullet" for this phenomenon so long as your hammer multiple INSERT, UPDATEs, and DELETEs on Master1.
LOOKING OUTSIDE THE BOX

What you need to look for in the Master1's Binary Logs is 15-second Windows of Time among the timestamps and see how many INSERT, UPDATEs, and DELETEs and being done.
If your application performs these writes with the same rhythmic pattern, then you need to look at the application as to how it does writes in InnoDB and MyISAM.
Look around for any cronjobs that perform any scheduled writes


Why is VACUUM FULL locking ALL databases on the cluster? From everything I have read about VACUUM FULL, I would expect it to lock the database I'm running it on, but it renders every database in the cluster inaccessible. Is there perhaps something we might have wrong in our configuration? I do have autovacuum on, but we have a few unwieldy databases I'd like to clean up thoroughly. This is PostgreSQL v10. Thanks for any insights.

I'm considering below is the reason from PostgreSQL v10 documentation:(https://www.postgresql.org/docs/10/sql-vacuum.html)

The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM would.

VACUUM causes a substantial increase in I/O traffic, which might cause poor performance for other active sessions. Therefore, it is sometimes advisable to use the cost-based vacuum delay feature. See Section 19.4.4 for details.

https://www.postgresql.org/docs/10/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

In your case, it might have happened because all databases in the cluster shares common resource, if it's on the same server.


ADDM report dba_hist_snapshot How does ADDM report work? When I take two snapshots
one for

START: 05-OCT-2011 09:00:00,
END  : 05-OCT-2011 10:00:00

and another one for

START: 06-OCT-2011 09:00:00,
END  : 06-OCT-2011 10:00:00,

what does it do?

Does it show the difference between 05 and 06 October during that hours(9,10)?

Or it will show you the whole interval from 05-OCT-2011 09:00:00 to 06-OCT-2011 10:00:00 compared to some baseline? (Note: I do not have any baseline..is there any default baseline?)

I was confused a little bit when I saw the dates on the generated report, there was written:

Analysis Period: from 05-OCT-2011 10:01 to 06-OCT-2011 10:00

Can you explain me? Thank you very much

The AWR repository stores the snapshots you see listed in DBA_HIST_SNAPSHOT. The END_TIME is when the Snapshot data was recorded. This is the raw data about performance on your DB instance. They are usually taken every hour and retained for 7 days.

2 snapshots are required to generate a report. They don't really mean much on their own. In this case for the 2 snaps you specified you are getting a report that covers the END time of the 1st snapshot until the END time of the 2nd snapshot.

As far as the times on a generated report not being exactly at :00 I am thinking some rounding might be going on. The snapshots are triggered as close to top of the hour as possible, but it may take a few seconds to actually record the data. If you look at FLUSH_ELAPSED time in DBA_HIST_SNAPSHOT you will see that some data recording operations take a few seconds, some may take a minute or more. This is why you are seeing 10:01 instead of exactly 10:00 (the END of the 1st snap).


Data read from linked server returns different data-type on production server I have a SQL Server grabbing data from a linked Oracle server inside a SP.

On my development server, EF6 insists the SP is returning a double datatype for one of the columns, but in prod, it insists the same column is a decimal.

The SQL Server version is slightly different:

Prod: 11.0.5582.0
Dev : 11.0.5613.0

There are no (obvious) differences in how the linked servers are configured (i.e. I've compared the connection properties in SSMS).

Being a new feature, the production database has very few (zero, even) rows in the database, while the dev server has many. I have a feeling that might be related, because I recall having to change the datatype in the middle of development (though I didn't think much of it at the time).

What might be the problem here?

The observed difference in behavior is due to the difference in patch levels between the two servers.

Per the resolution of KB3051993, which is included in Cumulative Update 6 For SQL Server 2012 SP2):

  After you apply this fix, NUMBER values with unknown precision/scale
  are treated as double values with OLE DB provider. If the precision is
  important and the range of the values is not large enough, you can
  enable the new Trace Flag 7311 that would start treating such values
  as numeric (38, 10).


List all permissions for a given role? I've searched around all over and haven't found a conclusive answer to this question.

I need a script that can give ALL permissions for an associated role.

Any thoughts, or is it even possible?

This gets me CLOSE - but I can't seem to flip it around and give the summary for roles, rather than users.

http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

 WITH    perms_cte as
(
        select USER_NAME(p.grantee_principal_id) AS principal_name,
                dp.principal_id,
                dp.type_desc AS principal_type_desc,
                p.class_desc,
                OBJECT_NAME(p.major_id) AS object_name,
                p.permission_name,
                p.state_desc AS permission_state_desc
        from    sys.database_permissions p
        inner   JOIN sys.database_principals dp
        on     p.grantee_principal_id = dp.principal_id
)
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc,
    p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM    perms_cte p
right outer JOIN (
    select role_principal_id, dp.type_desc as principal_type_desc,
   member_principal_id,user_name(member_principal_id) as member_principal_name,
   user_name(role_principal_id) as role_name--,*
    from    sys.database_role_members rm
    INNER   JOIN sys.database_principals dp
    ON     rm.member_principal_id = dp.principal_id
) rm
ON     rm.role_principal_id = p.principal_id
order by 1

We came up with this, which seems to work:

SELECT DISTINCT rp.name,
                ObjectType = rp.type_desc,
                PermissionType = pm.class_desc,
                pm.permission_name,
                pm.state_desc,
                ObjectType = CASE
                               WHEN obj.type_desc IS NULL
                                     OR obj.type_desc = 'SYSTEM_TABLE' THEN
                               pm.class_desc
                               ELSE obj.type_desc
                             END,
                s.Name as SchemaName,
                [ObjectName] = Isnull(ss.name, Object_name(pm.major_id))
FROM   sys.database_principals rp
       INNER JOIN sys.database_permissions pm
               ON pm.grantee_principal_id = rp.principal_id
       LEFT JOIN sys.schemas ss
              ON pm.major_id = ss.schema_id
       LEFT JOIN sys.objects obj
              ON pm.[major_id] = obj.[object_id]
       LEFT JOIN sys.schemas s
              ON s.schema_id = obj.schema_id
WHERE  rp.type_desc = 'DATABASE_ROLE'
       AND pm.class_desc <> 'DATABASE'
ORDER  BY rp.name,
          rp.type_desc,
          pm.class_desc


Mongo Auth fail when using mongod.conf Im trying to learn about mongo and how to secure it. I manage to make it work using command line but when I try to use a conf file I get erros on authentication.

I have created an admin user like:

use admin
var admin = {
    user:"admin",
    pwd:"123456",
    roles:[
        {
            role: "userAdminAnyDatabase",
            db:"admin"
        }
    ]
}
db.createUser(admin)

Starting mongod mongod --auth and connecting to it using mongo 127.0.0.1:27017 --authenticationDatabase admin -u admin -p works.

I would like to move from command line to a conf file.
For that purpose, I create the following mongod.conf file:

mongod.conf

systemLog:
  destination: file
  path: /Users/me/Web/blog/setup/logs/mongodb/mongo.log
  logAppend: true
storage:
  dbPath: /usr/local/var/mongodb
net:
  port: 27017
  bindIp: 127.0.0.1
security:
  authorization: enabled

I run the daemon by invoking mongod -f mongod.conf.
I try to authenticate in mongo by invoking the previous command that used to work but this time it does not work:

mongo 127.0.0.1:27017 --authenticationDatabase admin -u admin -p

MongoDB shell version: 3.2.5
Enter password:
connecting to: 127.0.0.1:27017/test
2018-05-11T08:50:53.908+0300 E QUERY    [thread1] Error: Authentication failed. :
DB.prototype._authOrThrow@src/mongo/shell/db.js:1441:20
@(auth):6:1
@(auth):1:2

exception: login failed

Am I typing something wrong? I dont see what is the issue in here. Something missing in the conf file or wrong params invoking mongo?

As per your log statement it seems like that you are committing mistake in your authentication statement such as in your statement

mongo 127.0.0.1:27017 --authenticationDatabase admin -u admin -p

As per MongoDB BOL documentation here With access control enabled, ensure you have a user with userAdmin or userAdminAnyDatabase role in the admin database. This user can administrate user and roles such as: create users, grant or revoke roles from users, and create or modify customs roles.

Connect and authenticate as the user administrator

Using the mongo shell, you can:

Connect with authentication by passing in user credentials, or
Connect first withouth authentication, and then issue the  db.auth()
method to authenticate.

For example, the following creates the user myUserAdmin in the admin database:

use admin
db.createUser(
  {
    user: "myUserAdmin",
    pwd: "abc123",
    roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
  }
)

To authenticate during connection

Start a mongo shell with the -u , -p , and the --authenticationDatabase  command line options:

mongo --port 27017 -u "myUserAdmin" -p "abc123" --authenticationDatabase "admin"

To authenticate after connecting

Connect the mongo shell to the  mongod:

mongo --port 27017

Switch to the authentication database (in this case, admin), and use db.auth(<username>, <pwd>) method to authenticate:

use admin
db.auth("myUserAdmin", "abc123" )

As I am able to see that in your below mention mongod.conf file the authorization : enable . It's means your authentication is enable. So, there is no issue in your mongod.conf file.

systemLog:
  destination: file
  path: /Users/me/Web/blog/setup/logs/mongodb/mongo.log
  logAppend: true
storage:
  dbPath: /usr/local/var/mongodb
net:
  port: 27017
  bindIp: 127.0.0.1
security:
  authorization: enabled

For your further ref here and here


ORA-31626: job does not exist during schema export I'm exporting schema with following command -
expdp system/system@xxx.xxx.xxx.xxx:1521/orcl schemas=schema-name directory=test_dir dumpfile=Schema.dmp logfile=SchemaLog.log

but it results into following error -
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 1009
ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL"

I googled a lot and tried solutions provided around ORA-31626: job does not exist and ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors but none helped to solve the problem.
Could you please help to resolve this?

Try exp in stead of expdp
exp system/system@xxx.xxx.xxx.xxx:1521/orcl owner=schema-name file=/test_dir/Schema.dmp log=SchemaLog.log


Oracle Linked Server Long Preemptive_COM_Cocreateinstance Waits I have a Linked Server using the OraOLEDB.Oracle provider from the 11g Oracle Client and it was working fine for a while. However, an Oracle 12c Heterogeneous Connection was setup recently. This caused the 11g Client to not be able to connect to the Oracle Instances because of the PATH variable using the 12c TNSnames instead of the 11g TNSnames.

The PATH variable was corrected and the 11g Client can now connect to the Oracle Instances using TNSPing or SQLPlus but when trying to use the linked server the SQL Server will go into PREEMPTIVE mode with the wait type PREEMPTIVE_COM_COCREATEINSTANCE. The linked server query will not make a connection to Oracle or error out and cause the SQL Server to not allow any new connections. They will get stuck trying to login with the wait PREEMPTIVE_OS_GETPROCADDRESS and wait for the Linked Server query to finish in order to login.

Any thoughts on why the Linked Server would be causing long PREEMPTIVE_COM_COCREATEINSTANCE waits?

These calls are all part of loading the Oracle OleDb provider.  Not sure why they would block instead of failing, but it suggests that your Oracle client setup is still not not right.

The 11g OraOLEDB.Oracle provider not only needs to access the correct TNSNAMES.ORA file.  It also needs to load the correct OCI.DLL, which it does through the path.

OraOLEDB.Oracle, like all OleDB providers, is a COM component.  So it's loaded with a call to CoCreateInstance().  The OleDb provider in turn calls calls LoadLibrary() to find OCI.DLL, and then (in the normal DLL-loading process) calls GetProcAddress() to find the expected DLL entry point functions.  Any time SQL Server calls one of these Win32 functions it enters a 'PREEMPTIVE_...' waits.

So you need to ensure that the LoadLibrary function call finds and loads the correct copy of OCI.DLL.  You typically do this by setting the system path so that the folder containing your desired OCI.DLL appears first, and rebooting.

You can verify you're loading the expected OleDB provider from an elevated powershell prompt by running

PS C:\WINDOWS\system32> ls  hklm:\SOFTWARE\Classes\OraOLEDB.Oracle

to discover the CLSID of the OleDb provider, and then looking it up

PS C:\WINDOWS\system32> ls  "hklm:\SOFTWARE\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}"

Hive: HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}

Name                           Property
----                           --------
ExtendedErrors                 (default) : Extended Error Lookup Service
InprocServer32                 (default)      : c:\oracle\odac64\bin\OraOLEDB12.dll
                               ThreadingModel : Both
OLE DB Provider                (default) : Oracle Provider for OLE DB
ProgID                         (default) : OraOLEDB.Oracle.1
TypeLib                        (default) : {0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}
VersionIndependentProgID       (default) : OraOLEDB.Oracle

The full process is ProgId `OraOLEDB.Oracle' is resolved to a CLSID.  The CLISD is resolved to a .dll (InProcServer32).  The .DLL is loaded and it, in turn, runs LoadLibrary to load oci.dll.  Any link in that chain can break, including finding a copy of OCI.DLL of the wrong "bitness".  CoCreateInstance is bitness-aware, but LoadLibrary is not.

You can also test connectivity in powershell.  First ensure you are in a process with matching "bitness" to SQL Server.

PS C:\WINDOWS\system32> [system.environment]::Is64BitProcess

Then

PS C:\WINDOWS\system32> $con= new-object system.data.oledb.oledbconnection
PS C:\WINDOWS\system32> $con.connectionstring="provider=OraOLEDB.Oracle;data source=//server/xe;user id=xxx;password=xxx"
PS C:\WINDOWS\system32> $con.Open()


Oracle if/else in results Just wondering if there's a way of doing something like the below in Oracle, where if a returned result is one value you output one thing, else you output something else?

For example:

select if(COL1,1:'TRUE',0:'FALSE') from TABLE1;

Thanks in advance C

SQL> create table t1 (c1 number);

Table created.

SQL> insert into t1 values(1);

1 row created.

SQL> insert into t1 values(0);

1 row created.

SQL> commit;

Commit complete.

SQL> select decode(c1, 1, 'TRUE', 0, 'FALSE') from t1;

DECOD
-----
TRUE
FALSE


Solution on a complex sub select query SQL Server I created a SQL query that requires me to pull data from 2 separate databases, and I noticed that what I did is this:

Select
    *,
    (Select Z.f From DatabaseZ.TblZ Z where Z.w = A.w) as valueZA,
    (Select Z.k From DatabaseZ.TblZ Z where Z.w = A.w) as valueZB,
From
    DatabaseA.TblA A
LEFT JOIN
    DatabaseA.TblB B A.x = B.x
where
    A.isActiveBit = '1';

The problem I encounter is that the query is slow, due to the fact that Databasez.TblZ contains about 200K records,and that there is new data every 30 minutes. I think that using a subquery here is the main issue, since DatabaseA.TblA also contains a lot of records, the database is already expending a lot of memory and computing power just for the external query, and it takes more time since the subquery also takes a while.

My question is how can I speed up the query, should I change the way I use my sub queries?
I am currently thinking of running the query without the subqueries, then do this:

//PHP Code, just a sample not actual code

//$queryResult is the result of my first query

$subqueryArray = array();

//I make a loop depending on the number of records I got
for($x=0;$x <count($queryResult);$x++)
{
$subqueryArray[] = $this->Main->query("select X.f from Databasez.TblZ Z where ",$queryResult['w']);
//w because it's the field name in the previous query, I'll also do the same for the other subquery
}

I am not sure if the solution I am thinking of is appropriate, since I think the query would take longer if I do it this way. I am also thinking of using indexes but I think that wont make any significant impact on my speed(I am still fairly new with databases) since I need to finish the entire query within 3 seconds(already giving it a lot of time). My current query currently runs at 37 seconds.

What should I do about this?

For starters, you can remove multiple references of the same table:

Select A.w,
    oa.f as valueZA,
    oa.k as valueZB,
From DatabaseA.TblA A
    LEFT JOIN DatabaseA.TblB B A.x = B.x
    outer apply (
        Select Z.f, Z.k From DatabaseZ.TblZ Z where Z.w = A.w
    ) oa
where A.isActiveBit = '1';

You can replace outer with cross, depending on the logic you need.

Also, looking for index suggestions from SQL Server definitely wouldn't hurt. Either check execution plan suggestions, or even better, google for diagnostic scripts referring sys.dm_db_index_usage_stats() DMV.


In Oracle, what grants do I need as a non-schema user to view rows in all_objects? I have two instances of an oracle 11g database, both have the same object definitions in the schema: tables with indexes and constraints, a sequence, and a stored procedure.

The facts of the case:

The first instance I login as the schema user "MYDBOWNER".
The second instance I login as a role-permissioned user "MYDBUSER", though the schema is still owned by "MYDBOWNER".
I run the following query to see the objects owned by user "MYDBOWNER":

  select * from all_objects where owner='MYDBOWNER';

As MYDBOWNER, I get a list of all the objects including the indexes (but not the constraints).
As MYDBUSER, I don't see the indexes, but I see the table, sequence and procedure.

The permissions for the role is correct (table has select, delete, update and insert, sequence has select, and procedure has execute). So why don't I see the indexes? Is there an additional grant that needs to be provided to the role for the table?

Oracle data dictionary views that have the ALL_ prefix provide information about objects that your user has access to.  Views that have the DBA_ prefix provide information about all objects in the database.  And views that have the USER_ prefix provide information about objects that you own.

In this case, MYDBUSER does not and can not have privileges on the indexes owned by MYDBOWNER because there are no privileges one can grant on individual indexes so it makes sense that MYDBUSER would not see the indexes in ALL_OBJECTS.  If MYDBUSER were granted access to DBA_OBJECTS or DBA_INDEXES (either individually or via the SELECT ANY DICTIONARY privilege), you could query DBA_OBJECTS (or DBA_INDEXES) to get a list of all the indexes owned by MYDBOWNER.


Which Edition Of SQL Server Is On The Disc? I have several SQL Server 2008 R2 setup discs, but they're not labelled with which edition (Standard, Developer, Enterprise etc.) they are for.

Running them doesn't give much help as it just shows that it's a SQL Server 2008 R2 install, nothing more specific (unless I'm looking in the wrong place).

I hope that one of you smart cookies will be able to point me in the right direction to find out the edition of SQL Server on the discs.

I believe that all discs are the same, only the Product key will make the difference once entered during the setup.


MySQL SSL replication error 2026 I've been searching and testing for hours and still can't figure this out. MySQL version 5.7.23 on both slave and master.

Replication over SSL is failing with the following error message:

  Slave I/O for channel '': error connecting to master 'replslave1@stuff.stuff.com:3306' - retry-time: 60 retries: 1, Error_code: 2026

Replication works fine without SSL, so I know that the replication user and password are correct, and port 3306 is not blocked. I can connect with SSL from the command line with the same user and password combination:

mysql -u replslave1 -pxxxx -h stuff.stuff.com --ssl --ssl-ca='/etc/mysql/master-ca.pem' --ssl-capath='/etc/mysql' --ssl-cert='/etc/mysql/repl1-pub.pem' --ssl-key='/etc/mysql/repl1-priv.pem' --ssl-cipher='DHE-RSA-AES256-SHA'

When I do this from the command line the cipher shows as DHE-RSA-AES256-SHA.

Any ideas are welcome.

Finally! 6 - 8 hours of work later:

The problem in my case was that the slave server has 2 network interfaces and the usable/public IP address is assigned to the second interface. The first, which I assume that MySQL/OpenSSL is using as a default, does not have an IP address.

I solved by adding MASTER_BIND to my CHANGE_MASTER statement.


Looking for an efficient way to build some kind of "FIFO" table I need to store measured data on a daily basis for a given period in the future, lets say 7 days. The current day should be always day 1.
Basic Example:

| id | day    | value |
|----|--------|-------|
| 1  | 1      | 10    |
| 2  | 2      | 20    |
| 3  | 3      | 30    |
| 4  | 4      | 40    |
| 5  | 5      | 50    |
| 6  | 6      | 60    |
| 7  | 7      | 70    |

So at night the next day (day 2) becomes day one and all data will move up one day, and new data can be added.

Basic Example:

| id | day    | value |
|----|--------|-------|
| 1  | 1      | 20    |
| 2  | 2      | 30    |
| 3  | 3      | 40    |
| 4  | 4      | 50    |
| 5  | 5      | 60    |
| 6  | 6      | 70    |
| 7  | 7      | 80    |

So the principle is kind of FIFO. I always look at 7 days in the future every night the perspective changes because tomorrow becomes today and day 7 can hold a new value.

What would be an efficient way to build something like this in postgres.
As it is now I would have to update all rows at night to move the values.
Is there a better way?

I would store day as a DATE. You can easily query the next seven days from that and generate the "day number" using a window function:
select id, row_number() over (order by day) as day_nr, value
from the_table
where day >= current_date
  and day <= current_date + 7

To get rid of old rows, you simply run a DELETE statement with the necessary WHERE condition. To make that even more efficient, you can define the table with a range partition. Dropping a partition is a lot faster then running a DELETE on a large table.


Database 'MyDb' is being recovered. Waiting until recovery is finished I got so error:

  Database 'MyDb' is being recovered. Waiting until recovery is
  finished.

I have had a critical operation at that time.

What is the cause of this situation? And how can it be avoided?

Crash recovery is running on 'MyDb'. Possible causes:

The SQL Server service was restarted manually, due to a crash or a server stop/start.
A severe error in the database caused the database to be shut down and recovered.
Recovery was initiated deliberately by someone executing a RESTORE WITH RECOVERY.
The Auto-Close option is set, causing the database to shut down when the last user exits and run recovery to restart when the next connects.

If recovery is taking a long time to complete and there doesn't appear to be any activity you may need to restore from backups. If you're feeling brave you should start reading everything you can find about repairing/recovering suspect databases.

More information on what happened before the database ended up in this state would make for better answers e.g. what actions you carried out, what errors are in the SQL error log etc.


Remove extra email characters to enforce uniqueness Gmail ignores . inside an email address e.g abc@gmail.com and a.b.c@gmail.com are same
Gmail also considers same account if account has any number after + symbol. e.g xyz@gmail and xyz+1@gmail.com are same

I want to put constraint for uniqueness on emails which can handle above case in postgres but not successful so far. Simple unique and saving in lower case doesn't work

To answer your question (+1 for an interesting, challenging and relevant question). I did the following:

Created a table:

CREATE TABLE regexp_test (my_email_string VARCHAR(255));

Populated it with sample data (as per the question, the only place a +sign can appear in the email address is at the end of the first (local) part with 1 or more digits between it and the @ sign:

INSERT INTO regexp_test
VALUES
('as.dfWEWd.fs+4@stuff.com'),
('aS.Sdf.34343a.sfs@yahoo.com'),
('adsFFdsf"£"££$£$+15@tester.cn'),   <-- non-standard characters!
('test_dots.asdf.+12345@blah.au.com')

-- multiple dots after the @ sign
-- this is the tricky part!

And then ran this query:

SELECT
  LOWER
  (
    REGEXP_REPLACE
    (
      REPLACE
      (
        SPLIT_PART(SPLIT_PART(my_email_string, '@', 1), '+', 1)
        , '.', ''
      )
        ||
        '@'
        ||
        SUBSTRING(my_email_string, '[^@]*$'),
      '[^a-zA-Z0-9@.]', '', 'g'
    )
  ) AS "New email"
FROM regexp_test;

Explanation of query below.

Result:

               New Email
    asdfwewdfs@stuff.com
assdf34343asfs@yahoo.com
     adsffdsf@tester.cn
testdotsasdf@blah.au.com

All this is available on a fiddle here.

TL;DR - this will work for an email which contains multiple full stops (dots or periods) either in the first (local) part of the email (these will be removed as per the question) OR in the second (domain) part - this part can legitimately have multiple dots - say blah.au.com. Any symbols which are not alphanumeric will be removed from the local part. Anything between a + and the @ sign in the local part will also be removed.

In particular, emails like john.m.doe+15@blah.au.com will be converted to johnmdoe@blah.au.com.

To explain the query, it is best to go from the inside out (you can see some of the logical progression on the fiddle itself).

The first inner-most part uses SPLIT_PART() twice:

SPLIT_PART(SPLIT_PART(my_email_string, '@', 1), '+', 1)

Which gets (chomps) the email string up to the first @ sign (and only @ signe in a valid email address). Then the second SPLIT_STRING chomps the string still further and removes anything from a + sign to the @ symbol inclusive.

Then the outer

REPLACE
(
  SPLIT_PART(SPLIT_PART(my_email_string, '@', 1), '+', 1)
  , '.', ''
)

removes (replaces '.' with '' - nothing) all the dots in the first (local) part of the string as per the question, but not in the second (domain) part (addresses such as @blah.com.au are valid).

Now,  I get the email domain with the SUBSTRING() function using a regular expression as explained here:

SUBSTRING(my_email_string, '[^@]*$'),

this gives us the domain from after the @ sign to the end of the string.

I then use the SQL string concatenation operator (|| - double pipe) to join the two strings and also reinsert the removed @ sign.

So, my data now looks like this (result_1):

asdfWEWdfs@stuff.com
aSSdf34343asfs@yahoo.com
adsFFdsf"£"££$£$@tester.cn
test_dotsasdf@blah.au.com

So, the only dots and at signs remaining in the email string are valid, but we still have to get rid of the + signs (from the question) and other symbols (for checking) from the string. For this we use REGEXP_REPLACE.

So, the query now looks like this (result_1 substituted in for clarity).

REGEXP_REPLACE(result_1, '[^a-zA-Z0-9@.]', '', 'g')

This replaces all ('g' for global) characters not (^) in a-z or A-Z or the @ symbol or a dot with nothing ('').

In fact, this part might be redundant. If there are no symbols other than .s (dots) anywhere in the local part of the string, or a + sign followed by  digits and then an @ sign, then the REGEXP_REPLACEis unnecessary! I threw it in just in case!

The LOWER() just tidies it up - I prefer emails in lower case!

Et voilà!


Are PostgreSQL dynamic arrays more wasteful than plain text? Today, someone was helping me to make a function in PostgreSQL. I was wanting the function to return in arrays of 100+ geometries per two columns instead of delimited text to make it easier to understand what is being returned. His response was that it was going to use more resources if it was array instead of text and that I should focus on my programming instead of wasting resources.

I don't know enough about PostgreSQL to know if the use of arrays in this case would be concern.

My questions are: do PostgreSQL dynamic arrays use up more memory than text? And if so, is it a significant concern to drop the use of arrays on large queries?

His response was that it was going to use more resources if it was array instead of text and that I should focus on my programming instead of wasting resources.

Evidence?

If he's arguing performance against what'd otherwise be a clarity and correctness improvement, the onus is on him to provide evidence.

Delimited text is usually the wrong way to do it - see e.g. this related question.

  I don't know enough about PostgreSQL to know if the use of arrays in this case would be concern.

It depends on how you're constructing the arrays. Try to create them in a single run using array_agg or an ARRAY[] constructor or similar. Iteratively building them with concatenation in a loop tends to be very slow because at the SQL level arrays are immutable, so they have to be copied to append to them.

Of course, the same is true of text at the SQL level, and the same principles apply.

  My questions are: do PostgreSQL dynamic arrays use up more memory than text?

It depends on the underlying type. In general, if t1 uses more memory than t2, then an array of t1 uses more memory than an array of t2.

The internal storage of arrays depends a little on how they're created, but in general they add little or no memory overhead over the base type.

  And if so, is it a significant concern to drop the use of arrays on large queries?

Yes, but no more of a concern than using delimited text.

Try to return result sets, not huge single results.


The OLE DB provider "SQLNCLI" for linked server X reported a change in schema version between compile time X and run time X for table [tablename] The below error starts happening once a week at the same time every week.

The OLE DB provider "SQLNCLI" for linked server X reported a change in schema version between compile time X and run time X for table [tablename].
[SqlException (0x80131904): The OLE DB provider "SQLNCLI" for linked server "ABCDEF" reported a change in schema version between compile time ("175385004624084") and run time ("175393583420554") for table ""ABCDEF"."dbo"."TheViewOnLinkedServer"".]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +83
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteReader() +89
   XXXX.XXXX.Data.SqlHelper.ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) in E:\WorkingFolderVSSNET\Lesa\Databases\Lesa.Net.Data\Lesa.Net.Data\SQLHelper.cs:671
   XXXX.XXXX.Data.SqlHelper.ExecuteReader(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) in E:\WorkingFolderVSSNET\Lesa\Databases\Lesa.Net.Data\Lesa.Net.Data\SQLHelper.cs:803
   XXXX.XXXX.XXXXX.XXXX.GetAttTypes(String ID, String App) +279

[ApplicationException: Error in GetAttTypes]
   XXXX.XXXX.XXXX.XXXX.XXXXXXX(String ID, String App) +507
   XXXX.XXXX.XXXX.XXXXX.XXXXX.Load(Int32 XXXX) +14717

[Exception: Error in XXXXXX.Load( XXXXX=XXXXXX) at XXXXX- for.]
   XXXX.XXX.XXX.XXXXXX.XXXXXX..ctor(String XXXXX, String XXXXX) +312
   XXXXXXX.XXXXXXXX.Page_Load(Object sender, EventArgs e) +2481
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +50
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

To fix the problem I just run the alter statement for the view without any changes.

The view selects from another view on a linked server and does list the column names (doesn't select by *).

I am not able to duplicate the problem after it has been fixed yet because I can't figure out what could be causing it. I have tried rebuilding indexes to see if that is causing it but it doesn't seem to.

Any ideas on how to troubleshoot this or what the cause could be?

Are you using a synonym? The few cases I have seen people complaining of this, this fix worked - http://support.microsoft.com/kb/2498818 - What version/build are you on?


SELECT ... FOR UPDATE returns old value after waiting for lock It seems that if SELECT ... FOR UPDATE has to wait for a lock, and meanwhile another thread changes what would be the result, and commits, then the initial query returns the old result, i.e. from before the change.

Is this expected behavior or a bug? It certainly does not seem useful.

It is easy to demonstrate. Let's take the following table:

CREATE TABLE `orders`(
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(50),
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

And this sequence of statements:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;

SELECT MAX(id)
FROM orders
FOR UPDATE;

# PAUSE HERE

INSERT INTO orders (description)
VALUES ('bla');

COMMIT;

Now we do the following:

Open two connections.
On connection 1, execute up to the pause. This succeeds.
On connection 2, execute up to the pause. This gets stuck waiting for a lock.
On connection 1, execute the rest. This succeeds.
Connection 2 has now returned the previous id!
On connection 2, execute the SELECT once more. Now it returns the updated value.

So to get behavior that seems sensible and desirable, it seems that we are forced to execute the SELECT twice.

Is this expected behavior or a bug?

(Tested on MySQL 5.7.25)

I have figured out the explanation for the behavior, by doing some experiments. I have used connection 1 to perform some other changes rather than insert a new row. The combined results reveal how the process works.

If connection 1 updates the description for the id, connection 2 will see the updated description. (The index scan has halted before the locked row.)
If connection 1 updates the id to a smaller  value, e.g. from 10 to 9, connection 2 will return the new value, 9. (The index scan has halted before the locked row, sees it disappear, and continues in a descending manner, thus arriving at 9.)
If connection 1 updates the id to a greater value, e.g. from 10 to 11, connection 2 will skip that value and instead return the greatest id that is smaller than what its original value was. In this example, it returns id 9 if that exists. (The index scan has halted before the locked row, sees it disappear, and continues in a descending manner, thus never passing 11, but rather arriving at 9.)
If connection 1 updates the id to a greater value, e.g. from 10 to 11, and then inserts a new row in place of the old one, e.g. with id 10, connection 2 will select the newly inserted row. (The index scan has halted before the locked row, and will see something inserted in its place.)
TODO: If connection 1 updates the id to a greater value, e.g. from 10 to 20, and then inserts a new row in place of the old one, but with a greater value than the original, e.g. 11, will connection 2 see it? Most likely not, but this should be tested.
If connection 1 locks a MAX(id) of 10, and connection 2 selects a hard-coded id of 9, then the locks do not overlap, and both can proceed independently.

We can conclude that the MAX(id) is determined using a reverse scan, and that the locks  are on actual (index) rows. This makes sense. Our isolation level of READ COMMITTED does not acquire gap locks. REPEATABLE READ might, but it causes the transaction of connection 2 to fail entirely, so there is no exploring that scenario any deeper.
Concluding the exact workings

Connection 2 starts reverse scanning until it hits a lock on a certain row.
It will wait for that lock to be released, and continue its scan from (and including) whatever id it left off at. Not whatever row, but whatever id.

As a result, if there is no longer a row at that id, the scan continues beyond it, to smaller ids.
As a result, if the row has been updated, the updated values are visible (if selecting more than just the id).
As a result, if the row has been moved and a new one inserted for its id, that new row is selected.


Truncate temporary table commits transaction? Is this correct that a "CREATE TEMPORARY TABLE IF NOT EXISTS.." does not commit current transaction (I like that), but a TRUNCATE TABLE, which is temporary, does implicitly commit any transaction?

Does this mean that the only way to have a fresh temporary table without committing the current transaction is to run (after above DDL statement):

DELETE FROM temp-table-name;

This TRUNCATE auto-commit behavior caused a rather nasty bug in my app -- the rollback didn't go all the way up because of the "TRUNCATE barrier" ;)

You can use DROP TEMPORARY TABLE instead of TRUNCATE, then CREATE TEMPORARY TABLE(...) again, it will not commit the transaction but you gain the speed of TRUNCATE statement.

"CREATE TABLE and DROP TABLE statements do not commit a transaction if the TEMPORARY keyword is used"
Statements That Cause an Implicit Commit


Upgrade in-place with low disk space on C: drive I am due to upgrade SQL Server 2005 Express to 2014 Express. This will be an in-place upgrade.

It's not my preferred way of upgrading, but being a physical machine and in a specific secure network zone, it has to stay put & keep its instance name, so it must be an in-place upgrade.

The only small issue is there is just under 10% free space available on the C: drive (the drive has 3GB space left). I hope to upgrade in-place and then once complete, go through getting disks replaced for bigger disks etc. (this is due to project upgrade deadlines).

Could someone tell me how much extra space will be put onto the C: drive with a SQL Server Express 2014 upgrade, please? Note no system databases live on the C: drive. Sadly no housekeeping can be performed by the Windows team etc.

Can you do it? Yes, you can. Should you do it? No, no shouldn't.

You're playing with fire, less than 10% free space on C drive is an urgent/critical flag in any decent company.

If you spot anything worth deleting, go for it. But in any case, since this is a physical server and you said Wintel team can't help you, present the scenario as an issue and a possible emergency in the future.


Getting Indirect parents I have following different tables
LeafChilds
ChildId, ChildName
1, Child1
2, Child2
Group
GroupId, GroupName
1, Group1
2, Group2
3, All
GroupChildMapping
GroupId, ChildId
1, 1
2, 1
2, 2
GroupGroupMapping
GroupId, ParentGroupId
1, 3
1, 3
I am looking for query to find indirect parents for LeafChilds.
In above example - child1 is in Group1 and Group1 has parent group as ALL. So indirectly child1 is in ALL group as well.
OUTPUT
Child1, Group1
Child1, ALL

I believe the below will work for you.  I made the final result return ID's rather than names so that you could see how to grab additional attributes from the base tables if necessary.
I also just realized that this only handles one level of group to parent, would need to slide in a recursive CTE after CTE_RawData to handle that scenario.  Is that something your query needs to do?  Or is one level of parent group enough?
/** The Setup - table variables, sample data **/

DECLARE @LeafChilds TABLE
    (
    ChildID INT NOT NULL PRIMARY KEY
    , ChildName NVARCHAR(100) NOT NULL
    )

INSERT INTO @LeafChilds
    (ChildID, ChildName)
VALUES (1, 'Child 1')
    , (2, 'Child 2')

DECLARE @Group TABLE
    (
    GroupID INT NOT NULL PRIMARY KEY
    , GroupName NVARCHAR(100) NOT NULL
    )

INSERT INTO @Group
    (GroupID, GroupName)
VALUES (1, 'Group 1')
    , (2, 'Group 2')
    , (3, 'ALL')

DECLARE @GroupChildMapping TABLE
    (
    GroupID INT NOT NULL    --FK to @Group (GroupID)
    , ChildID INT NOT NULL  --FK to @LeafChilds (ChildID)
    )

INSERT INTO @GroupChildMapping
    (GroupID, ChildID)
VALUES (1, 1)
    , (2, 1)
    , (2, 2)

DECLARE @GroupGroupMapping TABLE
    (
    GroupID INT NOT NULL            --FK to @Group (GroupID)
    , ParentGroupID INT NOT NULL    --FK to @Group (GroupID)
    )

INSERT INTO @GroupGroupMapping
    (GroupID, ParentGroupID)
VALUES (1, 3)
    --, (1, 3)  --DUPLICATE PAIR??

/** Get the Data
    CTE_RawData - Gets the raw data
    CTE_UnPivot - unpivots the data and formats it as desired.
    **/
;WITH CTE_RawData AS
    (
    SELECT C.ChildID
        , C.ChildName
        , GM.GroupID AS DirectGroupID
        , GI.GroupName AS DirectGroupName
        , GMP.ParentGroupID AS IndirectGroupID
        , GP.GroupName AS IndirectGroupName
    FROM @LeafChilds AS C
        LEFT OUTER JOIN @GroupChildMapping AS CM ON CM.ChildID = C.ChildID
        LEFT OUTER JOIN @GroupGroupMapping AS GM ON GM.GroupID = CM.GroupID
        LEFT OUTER JOIN @Group AS GI ON GI.GroupID = GM.GroupID
        LEFT OUTER JOIN @GroupGroupMapping AS GMP ON GMP.GroupID = CM.GroupID
        LEFT OUTER JOIN @Group AS GP ON GP.GroupID = GMP.ParentGroupID
    WHERE GM.GroupID IS NOT NULL
    )
, CTE_UnPivot AS
    (
    SELECT ChildID
        , GroupInheritanceType
        , GroupID
    FROM (SELECT ChildID, DirectGroupID, IndirectGroupID FROM CTE_RawData AS R) AS C
        UNPIVOT (GroupID FOR GroupInheritanceType IN (DirectGroupID, IndirectGroupID)) AS unpvt
    )
SELECT P.ChildID
    , L.ChildName
    , P.GroupInheritanceType
    , P.GroupID
    , G.GroupName
FROM CTE_UnPivot AS P
    LEFT OUTER JOIN @LeafChilds AS L ON L.ChildID = P.ChildID
    LEFT OUTER JOIN @Group AS G ON G.GroupID = P.GroupID