Reconstructing row from record Based on Northwind schema:

Assume we have a query like this (please disregard the actual usefulness of this, as the real use case is more involved)

SELECT unnest(r.agg) FROM (
  SELECT array_agg(x) AS agg
  FROM (
     SELECT *
     FROM northwind.u_suppliers s LEFT JOIN northwind.u_products p ON s.guid = p.supplier
     ) x
  GROUP BY x.supplier
  ) r LIMIT 1;

the result is

(9Y_03ogA1mOixVtJTqB8zih9Lu5-DB-l--fYvnHhyMQ,22,"Dirk Luchte","Accounting Manager",6j7qZ3Pjad5irKAM1MKX1WU7hvkgIOhZoe9OeRfTX4Q,"(12345) 1210",,northwindSupplier,jp9VvzFBbklHelD_ybS1oDsJbILda1duNhMtCdGBZqg,0,do4_k8zCsOog5tYROlsouF81Uj9I5LbTFawvuPw9Q-0,"2017-10-11 08:45:14.982",47,"Zaanse koeken",9Y_03ogA1mOixVtJTqB8zih9Lu5-DB-l--fYvnHhyMQ,AkOUoKYOpHFWIkNcoPX3CLtTl4n_ACN6TwWxqd3gzYQ,"10 - 4 oz boxes",9.5,36,0,0,f,,northwindProduct)

which is a record.

What would be the way to create a table from this with all columns and unnested records as rows?

So there'd be:

u_suppliers_column1 | ... | u_suppliers.columnN | u_products.column1 | ... | u_products.columnN
------------------------------------------------------------------------------------------------
9Y_03og...          | 22 | "Dirk Luchte" ...

The snippet is for illustrational purpose only - of course, creating aggregate just to unnest it again is non-sensible under these circumstances, but for sake of simplicity & reproducibility I have decided to write it as such,.

Thanks to @ypercubeᵀᴹ , one can go about it by first creating intermediary type with all the required columns (from both tables, since it's join) such as:

CREATE TYPE suppliers_products AS (s_guid VARCHAR(64), supplierId, ...)

The following query can be then written such as:

SELECT (unnest(r.agg)).*
  FROM (
     SELECT array_agg(x :: suppliers_products) AS agg
     FROM (
            SELECT *
            FROM northwind.u_suppliers S LEFT JOIN northwind.u_products p ON S.guid = p.supplier
          ) x
     GROUP BY x.supplier
   ) r
  LIMIT 1;

If there was no anonymous record (coming from join), we would not need intermediate type.

As far as I'm aware there is no way to do it without manually preparing such type upfront.


How to Create Trigger Insert with a SELECT LEFT OUTER JOIN I want this trigger to be fired after an insert is made with the text, this is what I have so far

ALTER TRIGGER [xxx]
   ON  [dbo].[Main]
   AFTER INSERT
AS
BEGIN

    SET NOCOUNT ON;

    INSERT INTO dtsx.dbo.main(refno,subject,recdate,dateind,officefrom,author,status)
    SELECT refno,subject,datereceived,'REC','TEST',author,StatDesc
    FROM dbo.Main LEFT OUTER JOIN
        dbo.DocStat ON dbo.Main.Status = dbo.DocStat.StatID

END

I just want the last record i created to be inserted but instead all the records in the source table (dbo.main) are inserted to the target table (dtsx.dbo.main). Where do place the INSERTED clause, if any? TIA

Every trigger have some internal tables , INSERTED and DELETED.
In this case, we use only the INSERTED table, which hold the records inserted.

ALTER TRIGGER [xxx] ON [dbo].[Main]
AFTER INSERT
AS
BEGIN

    SET NOCOUNT ON;

    INSERT INTO dtsx.dbo.main(refno,subject,recdate,dateind,officefrom,author,status)

    SELECT refno,subject,datereceived,'REC','TEST',author,StatDesc
    FROM INSERTED AS I
    LEFT OUTER JOIN dbo.DocStat AS DS
    ON I.Status = DS.StatID

END


Fastest way to get the length of the intersection of two text arrays in PostgreSQL This is what I am currently using:

CREATE FUNCTION array_intersect(a1 anyarray, a2 anyarray)
RETURNS anyarray AS
$$
  SELECT ARRAY(
    SELECT unnest($1)
    INTERSECT SELECT unnest($2)
    ORDER BY 1
  );
$$ LANGUAGE sql
IMMUTABLE STRICT;

--get the length:
select array_length ( array_intersect(array[...], array[...]), 1);

Is there a faster way?

You say you're looking for "Jaccard Similarity between two arrays"

You may consider MadLib which provides this as dist_jaccard()

  Jaccard distance between two varchar vectors treated as sets.

You may want to try it without STRICT. That may be slowing it down.
You may want to use INTERSECT ALL Especially if you know you don't have dupes in $1.
As Craig Ringer said in the comments you may want out intarray and consider using &.

To give a more accurate answer here,

What are the datatypes?
How many elements are we talking about on each array?
How much exclusion does the INTERSECT provide?


Help to create indexes to improve query performance and reduce report generation time The database in SQL Server 2008 has these tables below - the main table being the Master:

Master

A_ID (PK)
SYSSerialNumber
Bootlog
SequenceNumber
FileName
Cfg
Unique(serialnumber,sequencenumber)

SYSTEM  (Master:SYSTEM -1:1)

SYS_ID
A_ID (FK)
SYS_Product
SYS_Source
SYS_iVer

MEMORY (Master:Memory - 1 to N)

MEM_ID
A_ID (FK)
MEM_Instance
MEM_Name
MEM_Size
MEM_CE
MEM_UE
unique(ahs_id,mem_instance)

Processor  (Master:Processor - 1 to N)

PROC_ID
A_ID (FK)
PROC_Instance
PROC_FamilyID
PROC_Version
unique(a_id,proc_instance)

BC (Master:BC - Master.SYSSerialnumber = BC_SerialNumber )

BC_ID
BC_WWSiteName
BC_SerialNumber

The view was created for tables Master,System, BC and Memory: MSBC_Mem

select
    M.a_id, M.SYSSerialNumber, M.FileName, M.Cfg,
    M.SYS_Product, M.SYS_Source, M.SYS_iVer,
    B.BC_WWSiteName, B.BC_SerialNumber,
    MEM_Instance, MEM_Name, MEM_Size, MEM_CE,MEM_UE
from
    dbo.BC AS B
LEFT OUTER JOIN
    dbo.MASTER AS M ON M.SYSSerialNumber = B.BC_SerialNumber
LEFT OUTER JOIN
    dbo.SYSTEM AS S ON M.A_ID = S.A_ID
LEFT OUTER JOIN
    dbo.Memory AS Mem ON M.A_ID = Mem.A_ID_
WHERE
    (M.A_Cfg = 1)

Using the above view as a base for all queries for the report:

Query:

 Select
    count(distinct(sysserialnumber)), BC_WWSitename
 from dbo.MSBC_Mem
 where sys_product = 'xyz'

My question: SYS_Product & SYS_Source are commonly used in the WHERE clause in almost all the queries. What kind of index do I have create on the parameters?
To get a normalized values based on MEM_Name for each SYS_Product where MEM_CE > 0 :

View: NRMEM_NameCE

SELECT
    A.SYS_Product, A.MEM_Name AS NMCEMEM_Name, A.MEM_NameCount,
    CAST(A.MEM_NameCount AS float) / B.MEM_NameCount * 100 AS VendorPercentage,
    CAST(A.MEM_NameCount AS varchar(8)) + ' / ' + CAST(B.MEM_NameCount AS varchar(8)) AS somestring,
    C.MEM_NameCount AS TotalUnits
FROM
    (SELECT
        SYS_Product, MEM_Name,
        SUM(CASE WHEN MEM_CE > 0 THEN 1 ELSE 0 END) AS MEM_NameCount
     FROM
        dbo.MSBC_Mem
     WHERE
        (MEM_Name IS NOT NULL)
     GROUP BY
        SYS_Product, MEM_Name) AS A
INNER JOIN
   (SELECT
       SYS_Product, MEM_Name, COUNT(MEM_Name) AS MEM_NameCount
    FROM
       dbo.MSBC_Mem
    WHERE
       (MEM_Name IS NOT NULL)
    GROUP BY
       SYS_Product, MEM_Name) AS B ON A.MEM_Name = B.MEM_Name AND A.SYS_Product = B.SYS_Product
INNER JOIN
    (SELECT
        SYS_Product, COUNT(MEM_Name) AS MEM_NameCount
     FROM
        dbo.MSBC_Mem
     WHERE
        (MEM_Name IS NOT NULL)
     GROUP BY
        SYS_Product) AS C ON A.SYS_Product = C.SYS_Product

From the results above:

select * from NRMEM_NameCE where SYS_Product='xyz'

The above result which is :

**SYS_Product   NMCEMEM_Name    MEM_NameCount   VendorPercentage somestring TotalUnits**
DL          DIMM             10 0      0              0 / 79    32909
DL          DIMM             4  2      0.04419        2 / 4525  32909
DL          DIMM             8  0      0              0 / 995   32909

This entire query takes long since there are no indexes on the Memory and System tables.

Could you please suggest as to how I can include indexes on the memory table and improve the performance?

Thanks.

As you are running a lot of (MEM_Name IS NOT NULL) against the memory table I would look to create a filtered index on this table to begin with.

CREATE NONCLUSTERED INDEX Memory_MEM_Name
ON MEMORY (Index Fields here)
WHERE MEM_Name IS NOT NULL

in most occations every table should have a least 1 Index.

You should capture a work load trace via Profiler and then run it through the database tuning advisor this will tell you where you should see gains with indexs (and even create them for you)

http://msdn.microsoft.com/en-us/library/ms181091.aspx

When running the DTA it might be a good idea to run it against a test system if you have one rather than an online system. Make the changes it sugests and then run it again to see if the improvements appear.

Note - I would not have called a table master. Master should be reserverd for the important master database.


Add columns to all tables in a database if the columns don't exist I have to implement auditing in a database with many (50+) tables. I am looking to add the user name and the time when the user altered the record as columns to all tables. However, some tables have the columns already, so I don't need to add the columns to all tables. Assuming the column names are DateTime_Table and UserName_Table, how can I add DateTime_Table column and UserName_Table columns to all tables in the database if the columns don't exist?

I saw this loop for Postgres but I need help in SQL Server.

If you only have around 50 tables, I personally wouldn't bother with a solution that uses a cursor. You can write a SQL query that looks at SQL Server object catalog views to generate the SQL code that you need to run to add the columns. One nice benefit is that you can easily review your code before running it. I think that the below query will be close to what you need for the DateTime_Table column:

SELECT 'ALTER TABLE ' + QUOTENAME(ss.name) + '.' + QUOTENAME(st.name) + ' ADD [DateTime_Table] DATETIME NULL;'
FROM sys.tables st
INNER JOIN sys.schemas ss on st.[schema_id] = ss.[schema_id]
WHERE st.is_ms_shipped = 0
AND NOT EXISTS (
    SELECT 1
    FROM sys.columns sc
    WHERE sc.[object_id] = st.[object_id]
    AND sc.name = 'DateTime_Table'
);

I used QUOTENAME to avoid issues with special characters in table or schema names. Once you've tested the generated code and are confident that it is correct, you can simply copy and paste the SSMS query results into another query window and run them.

If you needed a more automated approach or needed to deal with many more objects, you could use a cursor to find and loop through each table missing the column and EXEC to execute dynamic SQL to add the column.


Query with join and concatenation of one joined table's column I have the following structure:

Table R     Table RZ (n-m)    Table Z
-------     -----------------  ---------
R1          R1 | Z1           Z1 | "A"
R2          R1 | Z2           Z2 | "B"
R3          R2 | Z1           Z3 | "C"
R4          R2 | Z3           Z4 | "D"
...         R3 | Z1             ...
...         R3 | Z2             ...
...         R3 | Z4             ...

(I left out some additional complications and further joins to simplify)

I need to query all rows from "R" with some of the columns or "R" including a column in the result that has the concatenated "Name" column of table "Z".

Here is a sample following the given table contents above:

ID of R | Name of Z
--------------------
R1      | "A,B"
R2      | "A,C"
R3      | "A,B,D"

So far a scalar function was in place, used by every row of R, re-querying the R- RZ-Z join again, concatenating and returning the names, but I figured this is very slow.

I tried FOR XML, but I can't manage to combine the XML result delivering the string I want with the rest of the columns required from table R.

What is the best way to solve this?

Try with CROSS APPLY , like this:

SELECT
    R.idR AS [idR]
    ,'"'+ REPLACE(STUFF(CA.NameOfZ,1,1,''),'"','') + '"' AS NameOfZ
FROM
    @tableR AS R
    CROSS APPLY
    (  SELECT
            ','+Z.colX
        FROM
            @tableZ AS Z
            INNER JOIN @tableRZ AS RZ
            ON  Z.colZ = RZ.colZ
        WHERE
            R.idR = RZ.idR
        FOR XML PATH('')
    )CA(NameOfZ)
WHERE
    CA.NameOfZ IS NOT NULL

Output:

idR  NameOfZ
R1   "A,B"
R2   "A,C"
R3   "A,B,C,D"


Are there performance benefits to turning off archivelogging? Is it wise? The first part of the question is pretty simple; has anyone run a DB in both archivelog and noarchivelog mode? Were there any measurable performance benefits to turning off logging that you could share?

The second half is a guess more specific to my circumstances but if anyone has any thoughts that'd be good. Currently our Master ( main writing box ) is running in archivelog mode complete with standby and table-level backups. The standby is tested twice weekly, and the backups more rarely. Not as evil as some but still not good. So, we've finally persuaded senior management to shell out for a shiny new box. It's lots better and hopefully won't blow up or fry the motherboard like the last one etc etc.

On this box we'll have hourly backups, replicated offsite down redundant fibre optics, and the backups will be tested at least daily, i.e. everything we can throw at it.

In this situation, and if the answer to the first part is positive, is it actually wise to run a DB in noarchivelog mode? If the box, or the discs die, it'll be quicker to start something up to takes it's place than it was previously and we don't mind losing an hours work if it means a significant improvement in speed.

It still seems a little bit dodgy though? I understand that we'd lose an hours work and are less worried about this than anything else we might have missed in our analysis of the implications of not having a standby.

The normal answer is: "Use archivelog mode". Unless you are taking cold backups you will not be able to recover from them without the log files archived during the backup.

If you are taking hourly cold backups and are running in noarchivelog mode, you risk losing an hours worth of committed transactions (which you don't mind). The purpose of archiving the logs is so that you can recover all (or nearly all) of the committed transactions after a media failure.

Whichever mode you choose Oracle still writes logs to the online redo log files - in archivelog mode you enable the archiving of those files as they fill up.

Performance benefits will not normally be significant - here is a list of special cases where there is a difference in redo generation.

Seeing as your 'backups' are ZFS snapshots, you do not need log files for restoring the database - reverting to a snapshot will be similar to recovery after a power loss and will use the snapshotted online redo logs to roll forward to a consistent state. In that specific case, and seeing that you do not mind losing recent data, noarchivelog mode may be acceptable to you.

Not having the standby will still increase your recovery time in the case of hardware failure on the primary however - you may wish to consider that.


Can I update an Access table with an Oracle table? I have an Access database with a table with a unique key and price field among other fields.  I have been told I have access to an Oracle database with those prices in it and the linking Key.

Can I run an update query in Access to update those price fields with values in the Oracle table or do I need to import the Oracle table into Access first and then run the update query from that access table?

I'm pretty well up on Access but have never had to link it with another SQL database system.

It's been a long, LONG time since I did any Access development, but if Access still supports linked tables, you can.

In that case the linked table is really just a view (select * from some_schema.some_table_in_oracle) and you can write an Access query that you use to update the Access table using the linked Oracle table.


SSMS 32bit can't connect SSIS 64bit I can't find the solution and searching for info doesn't give desired results

Environment:

Server: W2K8 EE 64 bit + MSSQL SE 2K8 R2 64 bit
Client: Windows XP Prof 32 bit

Using SSMS on client machine I get the following error:

TITLE: Connect to Server
------------------------------

Cannot connect to <<server-name>>.

------------------------------
ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------

A security package specific error occurred. (Exception from HRESULT: 0x80070721) (Microsoft.SqlServer.DTSRuntimeWrap)

------------------------------

A security package specific error occurred. (Exception from HRESULT: 0x80070721) (Microsoft.SqlServer.DTSRuntimeWrap)

------------------------------
BUTTONS:

OK
------------------------------

When I connect from another 64bit W2K8 server using SSMS it works correctly.

Is there some workaround to make it work? For now I'm using SSMS on remote 64-bit machine, but would like to use it on local 32-bit PC.

Finaly got it working. Not quite sure of explanation, but I think it had something to do with Kerberos / NTLM authentication.

SSIS was running under domain account that was not registered with SPN. Changing to "Log On as Built-in Account" as Network Service and restarting SSIS service solved the problem.

But also I'm (almost) sure that I've tried it earlier when I found his thread: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/bba0d02d-67d0-42d4-af20-5ea8a4ae443b

Also these articles explaining Kerberos, NTLM and checking SPN settings were helpful:

http://sql-articles.com/articles/dba/linked-server-part-2/
http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx


Oracle 12c, Will dropping partition which has 25% of total data, rebuild the indexes? I have Oracle 12c database and partitioned table. There is a old partition which has 68734MB which is 27% of total.

I need to drop this partition. This is a production database and I does not have a downtime window for this.

I want to know if I drop this partition, will the indexes of this table be unusable or rebuild ? What would be the best strategy to drop this partition with minimum impact on the live traffic?

When you drop one or more partitions, local indexes are also dropped.
You need to rebuilt Global indexes unless the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause is specified in the ALTER TABLE DROP PARTITION statement.

For example, I have a table called TBL1 with range partition and global indexes.

SQL> select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i
where i.index_name=p.index_name
and table_name='TBL1';

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_INDEX    GI3        USABLE
T_INDEX    GI2        USABLE
T_INDEX    GI1        USABLE

SQL> alter table tbl1 drop partition p0 UPDATE GLOBAL INDEXES;

Table altered.

SQL> select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i
where i.index_name=p.index_name
and table_name='TBL1';

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_INDEX    GI3        USABLE
T_INDEX    GI2        USABLE
T_INDEX    GI1        USABLE

As you can see, if I specify UPDATE GLOBAL INDEXES clause, Global indexes don't become unusable.

After dropping the partition, indexes entries belongs to those partitions remain. When a query uses these indexes, they simply ignore such entries(orphaned). You can clean up such orphaned entries in your regular maintenance window. Details are also given in the following documentations.

  The partition maintenance operations DROP PARTITION and TRUNCATE PARTITION are optimized by making the index maintenance for metadata only.

  In any case, the dropping or truncation of a partition completes immediately, the index is always available for use, and the maintenance of the index is deferred

Documentation:

Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
On Oracle Database 12c


restore database using bak file - resorting to old date? Last night I made a full backup of my DB in SQL server management, when I restore the DB instead of being complete - it is missing all its data from the last few month.

The full backup was taken last night and is market as - 07/05/2014 16:54
However the information in the tables has restored itself to 24/02/2014

any help would be greatly appreciated -

Does your backup file contain multiple backups (known as backup sets), and are you restoring just the earliest backup set from the backup file?

To see the backup sets in a file run the following and note the Position column, which denotes the backup set:

RESTORE HEADERONLY FROM DISK='mybackup.bak'

To restore a specific backup set from a backup:

RESTORE DATABASE ... WITH FILE = <position from previous statement>

Why does this happen?

If you use the same filename every time in your backup script (e.g. BACKUP DATABASE MyDb TO DISK='backup.bak'), and that file is not deleted or changed between backups, then your backup is added to the backup sets in that file. If you then run RESTORE DATABASE ... without a WITH FILE=<position> clause, you will always restore the first backup set in the backup media - usually your earliest one.

The details:

The RESTORE HEADERONLY command is useful for a backup file (if you are looking at it on a different server to the one it was created on). You can also see the backups on the server that created them by running the command:

SELECT backup_set_id, media_set_id, position, backup_finish_date
FROM msdb.dbo.backupset

If you want to check which backup set you just restored from a multi-file media then you can run:

SELECT * FROM msdb.dbo.restorehistory

Note the backup_set_id which corresponds to the msdb backupset table and in turn to the media file and position within that file.

References:

From MSDN: RESTORE Arguments (Transact-SQL):

  Backup Set Options

  FILE ={ backup_set_file_number | @backup_set_file_number }

  ...

  When not specified, the default is 1.


How to capture execution plan from sql profiler for filtered queries only Using sql profiler, we trace all slow queries (filter on duration/reads) to see where we can optimise.
Events used:

RPC:Completed
SQL:BatchCompleted

Filter on Duration.
If I add the event ShowPlan XML, then I cannot filter on the duration of the underlying query, creating a huge load as hundreds/thousands of queries arrive each second
How to only capture the execution plan for the entries that match the filter duration/reads of the captured queries from the other events?

The easiest way would be to stop using Profiler and go to Extended Events. The query_post_execution_showplan event has duration built right in and you can use that to filter capture quite neatly. Here's a simple example:
CREATE EVENT SESSION ExecPlansDuration
ON SERVER
    ADD EVENT sqlserver.query_post_execution_showplan
    (WHERE ([duration] > (10000)))
    ADD TARGET package0.event_file
    (SET filename = N'ExecPlansDuration');

Just know that capturing plans can be very expensive.


SQL Server Query For Getting data in Specific Format We have two tables. One is PriceGroupTable and other is PricesTable. There is a one-to-many relationship between them. Please refer to the attached pic for more details.

I am looking for a SQL query which will give the result in the specific format which is highlighted in yellow in the attached pic.

Here is an example.

        Declare @PriceGroup TABLE
    (   id INT
        ,Catalog_Code INT
        ,Name VARCHAR(50)
    )

    INSERT INTO @PriceGroup (id,Catalog_Code,Name)
    VALUES (1,1,'PriceGroup1'),(2,1,'PriceGroup2'),(3,1,'PriceGroup3')

    Declare @Price TABLE
    (
        id INT
        ,articol_id INT
        ,catalog_code INT
        ,price_group_ID INT
        ,price DECIMAL(10,2)
    )

    INSERT INTO @Price (id,articol_id,catalog_code,price_group_ID,price)
    VALUES  (89,57,1,1,11.01)
            ,(90,57,1,2,11.01)
            ,(91,58,1,3,11.01)

    ;WITH PriceSource
    AS (
        SELECT
            articol_id
            ,Name
            ,price
        FROM
            @Price as P
            INNER JOIN @PriceGroup AS PG
            ON P.price_group_ID  = PG.id
            AND P.catalog_code = PG.Catalog_Code
    )

    SELECT *
    FROM PriceSource AS PS
    PIVOT
    (   MAX(Price)
        FOR Name IN ([PriceGroup1],[PriceGroup2],[PriceGroup3])
    )P

and the output:

articol_id  PriceGroup1 PriceGroup2 PriceGroup3
57  11.01   11.01   NULL
58  NULL    NULL    11.01


Slow query when having 'contains' and '=' together in where clause The following query takes about 10 seconds to finish on a table with 12k records

select top (5) *
from "Physician"
where "id" = 1 or contains("lastName", '"a*"')

But if I change the where clause to either

where "id" = 1

or

where contains("lastName", '"a*"')

It'll return instantly.

Both columns are indexed and the lastName column is also full-text indexed.

CREATE TABLE Physician
(
   id         int identity    NOT NULL,
   firstName  nvarchar(100)   NOT NULL,
   lastName   nvarchar(100)   NOT NULL
);

ALTER TABLE Physician
  ADD CONSTRAINT Physician_PK
  PRIMARY KEY CLUSTERED (id);

CREATE NONCLUSTERED INDEX Physician_IX2
   ON Physician (firstName ASC);

CREATE NONCLUSTERED INDEX Physician_IX3
   ON Physician (lastName ASC);

CREATE FULLTEXT INDEX
    ON "Physician" ("firstName" LANGUAGE 0x0, "lastName" LANGUAGE 0x0)
    KEY INDEX "Physician_PK"
    ON "the_catalog"
    WITH stoplist = off;

Here is the Execution Plan

What could be the problem?

Your execution plan

When looking at the query plan, we can see that one index is touched to serve two filter operations.

Very simply put, due to the TOP operator, a row goal was set.
Much more information & prerequisites on row goals can be found here

From that same source:

  A row goal strategy generally means favouring non-blocking
  navigational operations (for example, nested loops joins, index seeks,
  and lookups) over blocking, set-based operations like sorting and
  hashing. This can be useful whenever the client can benefit from a
  quick start-up and steady stream of rows (with perhaps a longer
  overall execution time – see Rob Farley's post above). There are also
  the more obvious and traditional uses e.g. in presenting results a
  page at a time.

The entire table gets probed into the filters with the use of a left semi join that has a row goal set, hoping to return the 5 rows as fast and efficient as possible.

This does not happen, resulting in many iterations over the .Fulltextmatch TVF.

Recreating

Based on your plan, I was able to somewhat recreate your problem:

CREATE TABLE dbo.Person(id int not null,lastname varchar(max));

CREATE UNIQUE INDEX ui_id ON  dbo.Person(id)
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Person(lastname)
   KEY INDEX ui_id
   WITH STOPLIST = SYSTEM;
GO

INSERT INTO dbo.Person(id,lastname)
SELECT top(12000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
REPLICATE(CAST('A' as nvarchar(max)),80000)+ CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as varchar(10))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
CREATE CLUSTERED INDEX cx_Id on dbo.Person(id);

Running the query

SELECT TOP (5) *
FROM dbo.Person
WHERE "id" = 1 OR contains("lastName", '"B*"');

Results into a query plan comparable to yours:

In the above example, B does not exist in the fulltext index. As a result it depends on the parameter & data how efficient the query plan can be.

A better explanation of this can be found in Row Goals, Part 2: Semi Joins by Paul White

  ...In other words, on each iteration of an apply, we can stop looking at
  input B as soon as the first match is found, using the pushed-down
  join predicate. This is exactly the sort of thing a row goal is good
  for: generating part of a plan optimized to return the first n
  matching rows quickly (where n = 1 here).

For example, changing the predicate so the results are found way sooner (at the beginning of the scan).

select top (5) *
from dbo.Person
where "id" = 124
or contains("lastName", '"A*"');

the where "id" = 124 gets eliminated due to the fulltext index predicate already returning 5 rows, satisfying the TOP() predicate.

The results show this as well

id lastname
1  'AAA...'
2  'AAA...'
3  'AAA...'
4  'AAA...'
5  'AAA...'

And the TVF executions:

Inserting some new rows

INSERT INTO dbo.Person
SELECT 12001, REPLICATE(CAST('B' as nvarchar(max)),80000);
INSERT INTO dbo.Person
SELECT 12002, REPLICATE(CAST('B' as nvarchar(max)),80000);

Running the query to find these previous inserted rows

SELECT TOP (2) *
from dbo.Person
where "id" = 1
or contains("lastName", '"B*"');

This again results in too many iterations over almost all of the rows to return the last but one value found.

id   lastname
1     'AAA...'
12001 'BBB...'

Resolving

When removing the row goal by using traceflag 4138

SELECT TOP (5) *
FROM dbo.Person
WHERE "id" = 124
OR contains("lastName", '"B*"')
OPTION(QUERYTRACEON 4138 );

The optimizer uses a join pattern closer to implementing a UNION, in our case this is favourable as it pushes the predicates down to their respective clustered index seeks, and does not use the row goaled left semi join operator.

Another way to write this, without using the above mentioned traceflag:

SELECT top (5) *
FROM
(
SELECT *
FROM dbo.Person
WHERE "id" = 1
UNION
SELECT *
FROM dbo.Person
WHERE contains("lastName", '"B*"')
 ) as A;

With the resulting query plan:

where the fulltext function is applied directly

As a sidenote, for op, the query optimizer hotfix traceflag 4199 resolved his problem. He implemented this by adding OPTION(QUERYTRACEON(4199)) to the query. I was not able to reproduce that behaviour on my end. This hotfix does contain a semi join optimization:

  Trace Flag: 4102 Function: SQL 9 - Query performance is slow if the
  execution plan of the query contains semi join operators Typically,
  semi join operators are generated when the query contains the IN
  keyword or the EXISTS keyword. Enable flag 4102 and 4118 to overcome
  this.

Source

Extra

During cost based optimization, the optimizer could also add an index spool to the execution plan, implemented by LogOp_Spool Index on fly Eager  (or the physical counterpart)

It does this with my dataset for TOP(3) but not for TOP(2)

SELECT TOP (3) *
from dbo.Physician
where "id" = 1
or contains("lastName", '"B*"')

  On the first execution, an eager spool reads and stores the entire
  input before returning the subset of rows that is requested by the
  Predicate Later executions read and return the same or a different
  subset of rows from the worktable, without ever having to execute the
  child nodes again.

Source

With the seek predicate applied to this index eager spool:


Documentation on usage of brackets() in mysql queries I have an application where i am needed to write queries with multiple conditions and i am using several other keywords such as between, and, or etc

I have this two conditions

select * from countries where date between (x and y) and filter = filter and column choosen like '%col%';

and

select * from countries where date between (x and y) and filter = filter

I haven't actually run the queries but i want the date condition using between to be executed first and i thought the brackets would help.

What is the exact usage of brackets () in mysql and where is that documented?.

Putting parentheses in a BETWEEN leads to strangenesses:

date between (x and y) and filter1 = filter2

is treated as

(date between (x and y) and filter1) = filter2

Why?

BETWEEN expects 2 'arguments'; you have given it (1) (x and y), which returns true or false, which are numerically 1 or 0, and filter1.
The result of that (probably 0) is then compared:  0 = filter, which is likely to be false (0).

Recommendations:

Do not use parens that way in BETWEEN.  These are OK:

(date between x and y)

(date) between (x) and (y)  -- but this is clutter

When mixing AND and OR use parens to make it clear what you want to do.  AND will be done before OR, but too many SQL programmers fail know that, so be explicit:

a AND (b OR c)  -- Without parens, you get  (a AND b) OR c

x = 1 AND ( y = 2 OR z = 3 )

Don't clutter:  Rarely do you need (x) instead lf x.
When in doubt, add parens, even if it clutters.
When using function calls, no space before the (; there are cases where the parser decides whether it is a function call versus something else by using that spacing:  DATE(foo), not DATE (foo).

The documentation spells all of this out, but not necessarily in an obvious way.  Here are clues:

The BETWEEN syntax does not show the parens you asked about.
Any expression can have parens around it.
There is a "precedence" that says what happens when mixing AND and OR -- which is just like * and +.  (You do understand that 1+2*3 is 7, not 9 because * is evaluated before +?)


Galera cluster into Google cloud platform We have a galera cluster with 3 nodes, on 3 different physical machines but all located in the same datacenter.

From what I understood, the reason they deployed this in the past was to increase availability and reliability, DC failures are not a concern. Each node is installed on a VM using 12 cores and 4Gb RAM. (from the monitoring I did we can decrease the amount of cores used to 4)

We are asked to migrate this to Google Cloup Platform in order to get rid of the ops tasks. I could create 3 compute engine instances and deploy the galera cluster, GCP even have a Percona XtraDB Cluster one click deployment service, but I have difficulties to see the added value compared to Cloud SQL instances with replication and backup. I am not very familiar in scaling heavy load systems.

The db hosted in these nodes is kind of critical and should ensure maximum availability and reliability.

What strategy should I adopt in order to migrate this architecture to GCP ?

but I have difficulties to see the added
  value compared to Cloud SQL instances with replication and backup. I
  am not very familiar in scaling heavy load systems.

Galera-based clusters like Percona XtraDB Cluster support true active/active multi-master, so failover is seamless because you can actually be writing on any node at any time (tho it's recommended to write only on a single node to avoid performance issues due to lock-conflicts that can happen due to the optimistic locking  strategy used by Galera).

This type of cluster can also do distributed fully-synchronous reads so you can scale heavy read loads without problems;  One shining example is Magento, which is read-heavy and has many reads that are critical -think money- and traditionally needed to be done on master to guarantee consistency, and here they can be distributed.

Keep in mind that as with any distributed CP system, Galera-cluster will add latency to the writes as it has to verify the write is valid on the other nodes, and hence it's not designed to scale writes;  That said:  it can usually withstand same write load than a regular master, as long as you keep transactions small (in terms of amounts of rows affected) and short (in regards to time spent by active transaction holding locks).

Also make sure to read list of limitations here: https://www.percona.com/doc/percona-xtradb-cluster/LATEST/limitation.html

  What strategy should I adopt in order to migrate this architecture to
  GCP ?

To decrease downtime you can migrate by setting up the GCP cluster as an asynchronous slave of the current cluster (yes, Galera cluster nodes can act as a master and/or slave in traditional, binlog based, asynchronous replication).  To prime the slave cluster you can use XtraBackup, which can take fully lock-less backups from current cluster (make sure to use latest 2.3 XtraBackup) which you can then restore on one of the nodes and allow the other two nodes to perform Snapshot State Transfer.  Then simply designate one of the nodes to become the slave, and start replication using binlog coordinates from xtrabackup_binlog_info.

Overview of steps would be:

Enable log-bin, log-slave-updates, server_id on one of the nodes from the current cluster
Take a backup of that node using xtrabackup (make sure to desync the node before: https://www.percona.com/blog/2013/10/08/taking-backups-percona-xtradb-cluster-without-stalls-flow-control/).
Bring up one of the nodes in GCP cluster and restore the backup into it, and bootstrap the cluster with this node. Most likely use cloud storage to feed the backup to the cluster.
Bring up the other two nodes in GCP, one at a time and allow them to perform SST
Setup one of the GCP cluster nodes as async slave to the current cluster (this node also requires log-bin, log-slave-updates, server-id); this node will feed the other nodes with updates
Failover to new cluster once the slave node is caught up

Hope that helps! :)

Full disclosure: am a member of Percona Support team.


Stack trace in SQL Server for Linux Does somebody know what´s the reason of this stacktrace?

Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) - 14.0.3015.40 (X64)
Dec 22 2017 16:13:22
Copyright (C) 2017 Microsoft Corporation
Web Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)

Jan 30 10:18:51 cambalacheBD sqlservr[5445]: #015This program has encountered a fatal error and cannot continue running.
Jan 30 10:18:51 cambalacheBD sqlservr[5445]: The following diagnostic information is available:
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:        Reason: 0x00000006
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:        Status: 0xc0000005
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:       Message: Last Chance
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:       Address: 0x6a446912
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:    Parameters: 0x70373f270
Jan 30 10:18:51 cambalacheBD sqlservr[5445]: #015               #015               #015   Stacktrace: 000000006a4e84f6 000000006a43fb1e 000000006a43f9ca
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:                000000006a518473 000000006a446912
Jan 30 10:18:51 cambalacheBD sqlservr[5445]: #015      Process: 5447 - sqlservr
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:        Thread: 5662 (application thread 0x132c)
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:   Instance Id: c0074e4f-7ce0-43ef-bbc1-c9f5caecdfb6
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:      Crash Id: 1839d4f4-07d4-4a59-b4f8-894c6f19a995
Jan 30 10:18:51 cambalacheBD sqlservr[5445]:   Build stamp: 0fa9143612e88b9b2379174bbdc492bbfae2bec2f0278445d22e11dd124604ab
Jan 30 10:18:51 cambalacheBD sqlservr[5445]: Capturing core dump and information...
Jan 30 10:18:53 cambalacheBD sqlservr[5445]: Hint: You are currently not seeing messages from other users and the system.
Jan 30 10:18:53 cambalacheBD sqlservr[5445]:       Users in the 'systemd-journal' group can see all messages. Pass -q to
Jan 30 10:18:53 cambalacheBD sqlservr[5445]:       turn off this notice.
Jan 30 10:18:53 cambalacheBD sqlservr[5445]: No journal files were opened due to insufficient permissions.
Jan 30 10:18:53 cambalacheBD sqlservr[5445]: Hint: You are currently not seeing messages from other users and the system.
Jan 30 10:18:53 cambalacheBD sqlservr[5445]:       Users in the 'systemd-journal' group can see all messages. Pass -q to
Jan 30 10:18:53 cambalacheBD sqlservr[5445]:       turn off this notice.
Jan 30 10:18:53 cambalacheBD sqlservr[5445]: No journal files were opened due to insufficient permissions.
Jan 30 10:18:53 cambalacheBD sqlservr[5445]: tail: no se puede abrir '/var/log/syslog' para lectura: Permiso denegado
Jan 30 10:18:53 cambalacheBD sqlservr[5445]: Attempting to capture a dump with paldumper
Jan 30 10:18:58 cambalacheBD sqlservr[5445]: Core dump and information are being compressed in the background. When
Jan 30 10:18:58 cambalacheBD sqlservr[5445]: complete, they can be found in the following location:
Jan 30 10:18:58 cambalacheBD sqlservr[5445]:   /var/opt/mssql/log/core.sqlservr.01_30_2018_10_18_51.5447.tbz2
Jan 30 10:18:58 cambalacheBD systemd[1]: mssql-server.service: Main process exited, code=exited, status=1/FAILURE
Jan 30 10:18:58 cambalacheBD systemd[1]: mssql-server.service: Unit entered failed state.
Jan 30 10:18:58 cambalacheBD systemd[1]: mssql-server.service: Failed with result 'exit-code'.
Jan 30 10:18:58 cambalacheBD systemd[1]: mssql-server.service: Service hold-off time over, scheduling restart.
Jan 30 10:18:58 cambalacheBD systemd[1]: Stopped Microsoft SQL Server Database Engine.

There is never enough information with these crash logs. Often, all you can do is drop the database and start over -- access to the tools that would otherwise assist in trouble shooting this are disabled by Microsoft, such as strace and ltrace. You can roll your own kernel if you're in need of more information.

You may also try running this to reset the permissions on the system if they're misconfigured.
Remember that the right user for the server is always mssql. You must use that mssql user.
You have to have the server licensed properly (even if you just use the configuration utility to tell it you're a developer). Without proper licensing, you may get weird and unexplainable errors including segfaults.


Storing personal data on an invoice to preserve data integrity I want to store invoice data like addresses of people. But the problem is addresses can change over time - for example someone will move out and change his address. The problem of storing such data arises, because we can't change the data that is referenced on some invoice.

I have 3 tables: AddressInfo (contains StreetAddress and so on), Person which has one-to-many relation with AddressInfo, and Invoice table.

I have two solutions for this, but I'm not sure which direction is the right way to go:

Create AddressInfo and make it soft-deletable, adding some column like IsActive or IsDeleted so my Invoice table can reference this address info by it's id and it's guaranteed to remain the same unless someone directly changes this data without using my application.

So when someone changes his address, I soft-delete (perhaps it should be called soft-update?) the current address and add new one without touching old record at all. It will be probably good to check if it's referenced on some invoice first to prevent pollution.

My Invoice table would look somewhat like:

| InvoiceId | AddressInfoId |
Add needed columns from AddressInfo to my Invoice table. I will have to copy the data each time I create new Invoice, addresses don't change frequently that would pollute my database a little bit with duplicated records.

My Invoice table would look somewhat like (greatly simplified):

| InvoiceId | StreetAddress |

Do you have any experience with databases(perhaps of some ERP software) that solve this particular problem?

Interesting reads:

https://martinfowler.com/eaaDev/timeNarrative.html

The solution I have seen is copying address data to the InvoiceHeader table.  This is redundancy, but it is not harmful*.  Update anomalies do not occur, because the address on the invoice should not be changed later.

An analogous point could be made about copying product price data into the InvoiceDetail table.

* Update anomales only occur for harmful redundancy.  Still, if the customer address changes in the customer table at a later point in time, but remains unchanged in the InvoiceHeader table, then they will no longer be redundant.


What is the best way to check if a value in a table has changed when writing to a database? Let me provide you with an example. Let's say I have an html_tag with two columns, tag_type and tag_value

For example, let's say I have a record with the value "link" under thetag_type field and "https://dba.stackexchange.com" under the tag_value field.

Now, how would I approach this for auditing purposes? I want to only INSERT into this table when one of the tag_value fields corresponding to a tag_type has changed, because it would not make sense to add redundant records to this table.

Is this a database design problem I'm having, or is there advanced SQL I'm unaware of that would accomplish this? Or, rather, would the Java I am getting these values from initially be the place to handle such a thing? Example of how I would do this in Java (psuedo code):

var newTag = extract the tag's value from my webpage;

var tag_value = SELECT tag_value from html_tag
WHERE tag_type = 'link'

if (new_tag != tag_value) then (INSERT INTO html_tag)...

Should I handle this at the database level, or java level, like directly above?

If you need combination of (tag_value,tag_type) to be unique , the only guaranteed way to do it is to add unique constraint or unique index, e.g.
alter table  html_tag add constraint UQ_HTML_TAG_COMPOSITE UNIQUE(tag_value, tag_type) .  In addition you can modify INSERT statement so it will not insert a record if it already exists. Something like

INSERT INTO html_tag(tag_value,tag_type)
SELECT 'dba.stackexchange' as tag_value, 'link' as tag_type FROM DUAL
WHERE NOT EXISTS
(
  SELECT NULL FROM  html_tag a WHERE a.tag_value = 'dba.stackexchange'
  and a.tag_type = 'link'
)

will greatly reduce (not 100% though due to concurrency) chances of inserting duplicate records.  You  may also try using MERGE. However, no matter how you insert the record, the only way to completely avoid duplicates is to enforce it on DB level by adding unique constraint /index.


Creating UUID extension as the non super privileged user I need to load the uuid extension on my database, which is being created in runtime. The user that created the db is not superuser, she only has the "CREATEDB" permissions.

Is there a way to do this?
Thanks.

I see two possibilities:

1.

Install the extension into a template database (just a regular database) and use it as template for CREATE DATABASE:

CREATE DATABASE new_db TEMPLATE my_template_db;

That copies all installed extensions into the new database. Your user obviously can do that.

Also, if she created the database she should have the necessary privileges to run CREATE EXTENSION anyway. I quote the manual:

  For most extensions this means superuser or database owner privileges are needed.

2.

Create a SECURITY DEFINER function executing all necessary DML statements, owned by a superuser. Revoke all privileges from public and grant EXECUTE to the user role:

CREATE FUNCTION foo() ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION foo() FROM public;
GRANT EXECUTE ON FUNCTION foo() to my_user;

Read the chapter "Writing SECURITY DEFINER Functions Safely" in the manual.


How to architect an analytical BI platform? I am working on a new BI project for a medium sized corporation. Right now there is no Analytical infrastructure in place and the reporting is done manually in Excel. There are a few different data sources (from different systems like Billing) that need to be integrated to perform the reporting and analytics. Some of them are data dumps that need some custom conversions to get into a database ready form. These have a large number of columns. These need to be processed, required columns filtered and aggregations done etc. Typically there's about 50 GB of data produced daily and will be inserted to the existing tables each day.

We've identified that an analytical database like Vertica can be worth looking into. We don't have any prior experience with non-OLTP databases. My understanding is that Vertica (and other like it) are read-optimised and good for analytical tasks. My question is how does it fair at the initial stage where the raw data are loaded and processed? Should we use a traditional OLTP database like Oracle for that and then use the Vertica for Star Schema, Dimensional modelling type datastore? Is Vertica suited for ETL scenarios?

How is a typical architecture of this kind of scenario?

With respect to your question about using Vertica during the ETL, it's very rarely necessary (although not unknown) to use a different type of database for the ETL.  I would not do that unless you perceive a specific need to do so.  The only times I've ever heard of this being done due to interactions with legacy data sources.  Although @Kermit works with Vertica and will be more familiar with the platform, I don't see any reason to assume you need another DBMS platform for your ETL.

The reason Vertica and other systems are ill suited to transactional applications is that they use data structures that are fast to read from and batch load, but have a significant overhead per load operation.  A batched ETL process that requires index rebuilds or other such operations will be a non-issue on these systems.  The operations are too expensive for a high-volume OLTP platform but will not be too slow for a batched ETL job.

A common architecture used in telcos and other VLDB shops is to use flat files for intermediate storage and then load into the database.  Ab Initio and Ascential Datastage are tools that are designed to work in this way and this type of architecture is common on telcos.  However, ETL tools tend not to support complex transformations well, so you may well end up with a layer of data manipulation taking place within the database.

All VLDB platforms are expensive, and some are tied to proprietary hardware.  Be prepared to spend up big on your server.  3 years data at 50GB/day is around 50TB, which is getting into the region where you would need specialised kit to perform well on these data volumes.


Db schema migrations in MySQL I am wondering what is the correct way to arrange a db schema migration script/scripts.

My current script is a c# code made out of {version}.cs files which I am running one by one in an incremental way.

Is it recommended to put your schema migrations script in a C# code or should I always keep it as pure sql file?
When using Alter table I need to add a condition and check whether the Alter had been done already before. So if creating a SQL script I should add something like IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME='table' AND column_name='col'). looks like a lot of code, Is this the way to go?
One of the guys suggested using a Stored-procedure and write the schema migration script inside it to leverage on the advantages of a Stored-procedure, What do you think about this approach?

There is obviously no rule, but it's probably a good idea to be consistent. It's also a question of whether these schema changes will be executed by someone perhaps less skilled in running SQL scripts, e.g. if the database and its application is a product you're distributing to others.
While MariaDB supports statements like ALTER TABLE my_table DROP COLUMN IF EXISTS col_name; (see here) , MySQL doesn't have this. You could create a stored procedure to do this, perhaps something like this.
Stored procedures are great for a number of tasks, but this sounds like more work than it's worth. I won't reject the idea completely, as it all depends on your specific use-cases. When migrating big tables you may instead want to use a tool like pt-online-schema-change in Percona Toolkit.


MySQL to SQL Server, How to replicate data in a set interval? What would be the best way to replicate a database with several tables from a MySQL server to a MSSQL server, seeing as they are in different devices on the same network with a set interval of 10 minutes?

By best way, I mean a way that wouldn't completely copy all the rows with each iteration, a way that would only copy the latest entries since the last iteration.

As listed in the comments Linked Server is the best connection method, it integrates into your views and stored procedures.

The way I have done it in situations like this is create a view or a job on the source database that filters out the changed rows.  I have gone as far as using temporary tables on the MySQL side, then from the MSSQL side read the temporary table you created. If you created joins in a query on the MSSQL side looking at the MySQL, the MySQL will just send all of the data over and MSSQL will have to process it, that's what you are trying to avoid.  You just want the changed data.

Another method probably is the easiest is to create matching tables on the MSSQL side and use a product list SQL Data Examiner, then set the command line utility to run a project in the task scheduler. http://www.sqlaccessories.com/sql-data-examiner/  This will compare the actual data on both tables.  If you are talking millions of rows or a slow connection this isn't the way.  Just like doing it all in MSSQL you will have the issue where it pulls all of the data for the comparison.

So the best way depends on how many tables, how much data, and the connection capabilities.  The easiest would be SQL Data Examiner.


Using T-SQL, is it possible to a split a result set or table and then insert into two different temp tables? I have one table that holds payment data. I'd like to insert orders that ended in a declined state into #tmp_Declined. Then I'd like to insert payments that were originally declined but then authorized when using an alternative paymethod into #tmp_Authorized.

I can do this using 2 select into statements with slightly different where clauses but using the same base table.

I considered an except example like the following:

select *
into #tmp_Declined
from Payments

except

select *
from Payments
where WasOrginallyDeclined = 1
and MostRecentPaymentAccepted = 1

This approach though still populates one temp table at time.Is there a reserved word or query "trick" that will insert into #tmpA where conditions met and then insert into #tmpB whatever wasn't inserted in #tmpA?

I'd like to be able to write to 2 tables with one transaction.

The feature that you're describing is sometimes called multi-table insert. Oracle is one RDBMS that implements it. It is not implemented in SQL Server.

If you need to split your data doing just a single pass of the data you have a few workarounds but all of them add complexity and have other negative side effects. You could define a partitioned heap and insert into that heap. The partitioning column would be a 0/1 computed value that defines the target table. You can partition switch out of the heap into two separate tables essentially for free. Overall this requires a sort of the data before insert.

You could define a partitioned view, insert your data into the view, and drop the view after. The requirements of a partitioned view don't seem to fit very well with your scenario. Among other things, you'd need primary keys on both target tables that contain the computed column which determines the target table. The query plans for inserting data can look pretty ugly (although every query plan looks astoundingly beautiful in SSMS 17.4):

A third option is to define an INSTEAD OF INSERT trigger on a target table. The trigger code can send rows to the right table. As you might imagine this has some overhead.

If the requirement is to simply insert data in a single transaction you can do that with an explicit transaction and two INSERT queries. I would just negate the WHERE clause instead of joining back to the same table. Just make sure that you handle NULLs correctly. Example code:

BEGIN TRANSACTION;

INSERT INTO #tmp_Declined
select *
from Payments
where NOT (WasOrginallyDeclined = 1 and MostRecentPaymentAccepted = 1);

INSERT INTO #tmp_not_Declined
select *
from Payments
where WasOrginallyDeclined = 1 and MostRecentPaymentAccepted = 1;

COMMIT TRANSACTION;


Convert the table PROCESSLIST from InnoDB to MYISAM I use MySQL. My database's name is "db", and I have a table called "Test".

I want recover the last update, so I used  this query:

SELECT update_time
FROM information_schema.tables
WHERE table_schema='db'
AND table_name='Test';

But the result is  NULL, so I converted my Table from InnoDB to MYISAM by using this query:

mysql> ALTER TABLE db.Test ENGINE=MYISAM;

and it worked.

But the problem is that when I want to convert the table PROCESSLIST from InnoDB to MYISAM I get this message:

mysql> use information_schema
Database changed
mysql> ALTER TABLE PROCESSLIST ENGINE=MYISAM;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

So, is there another solution to know what is the last update for this table (PROCESSLIST)?

There is no way, because they are virtual tables. This is also the reason why you can't do anything with them, except for SELECT.


postgresql on windows won't install with data and executables on different disks I am trying to install postgresql 9.4.4 on a windows 2008 rc2 machine that has 2 HDs.
The first HD is, in theory, used only for executables/code/etc while the second one, being faster, should be used for data.

When I try to install postgresql the only way in which I can get an installation is if I select to put postgresql's exec and data on the same disk (the exec one). If I decide, as I want, to install postgresql "split" (with the 'data' directory on the second disk) the installation tells me that the cluster could not be initialized.

How can I solve this? I think it's related to some setting in Windows 2008 but I don't know what or which.

Ideas or pointers or RTFMs? :)

(I have noticed other similar questions but they don't apply in this case....)

Thanks!

The problem was not with Postgresql, but rather with the permissions on the second disk.

Enabling the relevant users to write on the disk (at the disk level) solved the situation.

Sorry for the noise, but I thought to post an answer, since it appears this kind of problem has fooled quite a bit of people on the Net. :)


Oracle XE/SQLPlus query display more items Is there a setting to allow Oracle XE/SQLPlus to display more items before beginning a new header?

For example:

First Name           SURNAME              Subject                    MARK
-------------------- -------------------- -------------------- ----------
Rollo                Aslaug               Databases                    55
Rollo                Aslaug               Programming                  50
Rollo                Aslaug               Graphics                     65
Loki                 Laufeyson            Operating Systems            45
Loki                 Laufeyson            Programming                  40
Loki                 Laufeyson            Databases                    55
Ragna                Lothbrock            Databases                    80
Ragna                Lothbrock            Operating Systems            50
Ragna                Lothbrock            Programming                  65
Thor                 Odinson              Programming                  75
Thor                 Odinson              Graphics                     60

First Name           SURNAME              Subject                    MARK
-------------------- -------------------- -------------------- ----------
Thor                 Odinson              Databases                    60
Floki                Rafna                Databases                    75
Floki                Rafna                Operating Systems            45
Floki                Rafna                Graphics                     70

Is there a setting to remove the 2nd header in the middle of the query results?

In SQL*Plus, you can change the pagesize

SQL> set pagesize 100

will repeat the headings every 100 rows rather than every 15.  You can set the pagesize value to (just about) whatever you'd like.  If you want to ensure that the headers are only written once, you can set it to a number much greater than the number of rows that the query will return.


Add Data from Columns Together I am working on importing data from an old non-relational database, to a new relational one.  I am currently working on importing data into the following table:
Table: job_part
    job_id   INT NOT NULL --References ID Column of Job table
    part_id  INT NOT NULL --References ID Column of Part table
    quantity INT NOT NULL --Amount of parts required for the job
    PK = job_id & part_id

The data I have is in a .csv file which has the following columns;

Part Number - Value will be referenced with Part table to get part_id value
Job Number - Value will be referenced with Job table to get job_id value
Amount - Value to be stored in Quantity column

My problem is when there are data rows like the following:
CDDM 42, P0164, 1
CDDM 42, P0164, 2

As you can see, the Part Number, and Job Number are the same, while the quantity value is different.  Because the table I want to insert this data into has the PartID and JobID as a composite primary key, these pieces of data will give me an error when I try to import it.
What I want to do, is to take this data, and before I put it in my table, put it into a temporary table that combines all of the matching key pairs.  So for my problem example above, instead of having the two rows that will cause and error I want the result to be:
CDDM 42, P0164, 3

Essentially, find every matching pair of key values, and add their quantities together so they can be a single row of data.
However I am unsure how to go about solving this problem.  I am using SQL Server.

You answered your own question, by importing it into a temporary or staging table.

CREATE TABLE #TempTable(
    job_id   INT NOT NULL
    ,part_id  INT NOT NULL
    ,quantity INT NOT NULL)

BULK INSERT #TempTable
FROM 'C:\example.csv'
WITH
(FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n') --any other options like FIRSTROW, etc

Then, just use the SUM() aggregate when inserting into your actual table

INSERT INTO MyTable
SELECT
   job_id
   ,part_id
   ,quantity = SUM(quantity)
FROM #TempTable
GROUP BY
   job_id
   ,part_id


Increasing the decimal precision abruptly increased the size of table I have a table like:

CREATE TABLE grid_rows(
    [grid_row_id] [int] NOT NULL,
    [grid_column_id] [smallint] NOT NULL,
    [decimal_val] [decimal](18, 6) NULL,
    [datetime_val] [datetime] NULL,
    [integer_val] [int] NULL,
    [string_val] [varchar](1024) NULL
)

This table has some  1,037,560 rows

exec sp_spaceused "grid_rows" gives:
rows         reserved    data
1,037,560    461,768KB  302,648KB`

After changing the precision from (18, 6) to (24,6) i.e
ALTER TABLE grid_rows ALTER COLUMN decimal_val decimal(24, 6)

exec sp_spaceused "grid_rows" gives:
rows         reserved     data
1,037,560    641,352KB  560,832KB

The space allocated by decimal(18,6) is 9 bytes and that of (24, 6) is 13 bytes. MSDN reference

The reserved space has increased by around 179,584 KB and data space by 260,000KB. Shouldn't it be increased by 1,037,560 * 4/1024 = 4052 KB

Keep in mind that the dropped column is still physically present in the row.

But the real killer is probably that, if this is a heap (table w/o clustered index), then row updates that increase the row size may cause an avalanche of forwarding. Consider that if you start with pages at nearly 100% full then any row increase will cause the row to no longer fit in the page, so a forward record has to be left in place and the row has to be placed somewhere else. Repeat this for every row (extreme case) and you have a pretty nasty scenario.

If is a B-Tree then the same scenario will cause page splits which are better because during the split the copied rows reclaim the space between the rows on the page, which the heap forwarding case does not.


Recommended maximum memory setting for PostgreSQL 9.4+ with Huge Pages Since PostgreSQL 9.4+ supports Linux Huge Pages, I have configured a Ubuntu 16.04 server with 16GB of RAM and 8 CPU cores to use Huge Pages with a dedicated PostgreSQL 9.5 instance.

The estimate in https://www.postgresql.org/docs/devel/static/kernel-resources.html#LINUX-HUGE-PAGES is based on VmPeak value wich is rougly a little more that the shared_buffers value.

So the question is: Since the recommended value for shared_buffers is between 25% and 40% and since PostgreSQL also relies on the operating system cache.

What is the recommended value for Huge Pages? Should I configure almost all memory to Huge Page use of PostgreSQL (12G-14G) and shared_buffers to 6G, or, which I think is more correct, configure Huge Pages with ~6.5G and shared_buffers to 6G.

If there are no other applications on the server potentially benefiting from huge pages, just set vm.nr_hugepages to rounded up shared buffers / pagesize. When you start PostgreSQL check its log. If it reports fall back to normal pages, increase it slightly.
But I'm not sure if 16GB RAM (i.e. shared buffers 4-6GB) is enough to see the significant difference. Probably you need to set it and compare.


Namespacing database names on my server in order to test multiple different SSIS packages An SSIS package that I'm building has two OLE DB connections, to databases named "core" and "staging". I created test versions of these two databases on my local SQL Server installation. I named them "test1_core" and "test1_staging". The "test1_" namespace is necessary because I have many SSIS different packages in active development at one time, and many of them refer to the same production databases. But it need each local SSIS package to refer to its own version of the database.

This works okay until I need to refer to the database name in the package specifically like in an Execute SQL task that includes two connections.

Also, I have SQL scripts that say use [core] at the top and I wish I could just use them on my local server without always having to open them up and change that line to use [test1_core] first, then having to change it back.

Is there some way to namespace the database name from within SQL Server so I can just refer to the local database as "core" from the SSIS package?

When l was testing a large SSIS package l switched the data connections using the first technique here.  The second technique in the second answer l have not tried but looks simpler to implement.  The main advantage was that l could easily switch between test and production.  Just wondering if that is easier than changing the namespace?


SQL-Server performance metrics: What exactly is Lock Waits/Sec (or: Why is it that unrealistically high?) I have a production SQL-Server, which seems to have increasing numbers of request taking extremely long (median response time is low, average response time is growing steadily). Therefore I started monitoring internal stats like the "lock waits per second".
The production instance returns nearly 2.000.000 Lock Waits/Sec, the test instance only ~3.000 Lock Waits/Sec. (This discrepancy is of course not unexpected, since the production instance has all the load, the test instance only a few requests of devs testing the new version of the application.)
Lock Waits/Sec is defined by Microsoft as "Number of lock requests per second that required the caller to wait."
This definition doesn't help me at all though, how could there be 2 million lock waits per second?? There are no even 2 million DB-requests at all, by far? Also this number fluctuates only by single digits over periods of time with very different load; if it is per second, it should move much, much more, shouldn't it?
Therefore my question: What exactly does Lock Waits/Sec stand for, and how do I have to interpret the numbers above?

The name "Lock Waits/Sec" is terribly misleading.
When you read this counter from sys.dm_os_performance_counters, it is a cumulative value that is reset when the instance is restarted.  Note that this is true of many (or maybe all?) of the "per second" counters here.
If you view it in Perfmon.exe, though, that application does the work of taking diffs and showing a "per second" view of the value.
In your case, 2 million lock waits over the entire uptime of the server might be pretty innocuous.  Of course, a lot of that probably depends on how long things waited on the locks, so this metric won't help you much in isolation.
To your broader issue, if you want to get a sense of what's going on with this server, running sp_BlitzFirst is a good place to start.  It will take a 5 second sample of a number of performance-related metrics (including perfmon counters), and give you a summary of the good, the bad, and the ugly.


BDBID in DBCC PAGE command output in SQL Server There is a famous undocumented DBCC PAGE command in SQL Server (as well as in SAP ASE) which if successful sends output to errorlog.
Noticed that it has 4 main sections: Buffer, Page Header, Data, and Offset Table.
The question is we notice dbid in the Page Header section.
Similarly, there is a bdbid in the buffer section.
Can we unquestionably say that both these ids will be the same? If anyone has experience around these.
For reference: https://techcommunity.microsoft.com/t5/sql-server/how-to-use-dbcc-page/ba-p/383094 https://www.itprotoday.com/sql-server/using-dbcc-page

I don't think its very much documented on Microsoft Site and other places. Possibly someone from Microsoft could shed some light on this, doing some checking on Paul Randal's site and other related information, most likely it is database id. You can read more details on this command at this page by him.
There are few more details at this site as below:
The BUFFER: section indicates the buffer page number (bpage) which represents exactly where in the buffer pool the page exists. Also included are:

bhash – The hash of the page
bpageno – the file and page number
bdbid – the database ID
references – the number of processes that have referenced the buffer page.
bcputicks – the amount of time latches have waited to access the page
bsampleCount – the number of times SQL Server has sampled the latch wait time for this page
bUse1 – the duration in seconds since the bcputicks and bsampleCount counters have been reset. This is perhaps a reasonable proxy for the amount of time the page has been in the buffer pool.
bstat, bstat2 – the status of the buffer page.
blog – the transaction log record where the page was last modified
bnext – the address of the next page in the buffer pool for this object
bDirtyContext – I’m guessing, but this looks like a pointer to the memory structure used by SQL Server’s storage engine to keep track of dirty pages.

As far Sybase ASE is concerned, I checked myself on multiple databases by running this command and could figure out that bdbid is dbid itself. Even if you pass a page number which is not present, it would still give you the same bdbid as that of dbid.
Below is the output from Sybase ASE when page is not present:
------------------------ Execute ------------------------

Page not found in Cache: default data cache.
Page read from disk.

BUFFER:
Buffer header for buffer 0xc00000039cd98000 (Mass head)
    page=0xc00000039cd96000 bdnew=0x0000000000000000 bdold=0x0000000000000000 bhash=0x0000000000000000 baccess_count=0
    bmass_next=0x0000000000000000 bmass_prev=0x0000000000000000 bdbid=6 bmass_encr=0x0000000000000000
    bvirtpg=[ 0xc00000039cd980d8 vpgdevno=0 vpvpn=45540 vdisk=0xc00000010939f230 ]
[Logical device name = xxxx]
    bmass_head=0xc00000039cd98000 bmass_tail=0xc00000039cd98000
    bcache_desc=0xc00000039cd9f178 (cid=0 cache name='default data cache')
    bpool_desc=0xc00000039cda4b30 bdbtable=0x0000000000000000
    Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0
    bmass_size=8192 (8K pool) bunref_cnt=0
    bdestroy_spid=0 bdestroy_curcmd=0 (GENERIC_TOKEN)
    bmass_stat=0x800(0x0000000000000800 (MASS_NOTHASHED))
    blfb_status=0x0000000000000000(0x00000000)
    bbuf_stat=0x0 (0x00000000)
     bcas_wait_cum:0 bcache_spin:0 bversion:0 bhot_keep:0    Buffer blpageno=120 bpg_size=8k Mass blpageno=120 Mass blocallockkeep=0    bxls_pin=0x0000000000000000 bxls_next=0x0000000000000000 bspid=0
    bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x0000000000000000 bssdbuf_ptr=0x0000000000000000     LFB delta=0x0000000000000000

Latch and the wait queue:
Latch (address: 0xc00000039cd98040)

Latch wait queue:

PAGE HEADER:
Page header for page 0xc00000039cd96000
pageno=120 nextpg=120 prevpg=120 ptnid=28 syspartitions timestamp=0000 00000227
oampgcount=1 attrcount=10 indid=4: totalentries_lo=1 entrycount=1
page status bits (pstat): 0x800c (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB), 0x0004 (PG_OAMSORT))

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
------------------------- Done --------------------------

Below is scenario when page is present in the database:
------------------------ Execute ------------------------

Page found in Cache: default data cache. Cachelet: 2

BUFFER:
Buffer header for buffer 0xc00000039cd98000
    page=0xc00000039cd96000 bdnew=0xc000000c677bf780 bdold=0xc000000c677bf780 bhash=0xc000000c8c1ff8c0 baccess_count=57
    bmass_next=0xc000000c73fcbbc0 bmass_prev=0xc000000c8c1ff8c0 bdbid=4 bmass_encr=0x0000000000000000
    bvirtpg=[ 0xc00000039cd980d8 vpgdevno=4 vpvpn=484 vdisk=0xc0000001093a01b0 ]
[Logical device name = xxxx]
    bmass_head=0xc000000c677bf780 bmass_tail=0xc000000c677bf780
    bcache_desc=0xc00000039cd9f178 (cid=0 cache name='default data cache')
    bpool_desc=0xc00000039cda4b30 bdbtable=0xc0000001692ffa80
    Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0
    bmass_size=8192 (8K pool) bunref_cnt=0
    bdestroy_spid=133 bdestroy_curcmd=260 (ONLINE_DB)
    bmass_stat=0x4001010(0x0000000004000000 (MASS_DONT_DISCARD), 0x0000000000001000 (MASS_HASHED), 0x0000000000000010 (MASS_INWASH))
    blfb_status=0x0000000000000000(0x00000000)
    bbuf_stat=0x1 (0x00000001 (BUF_PG_REF))
     bcas_wait_cum:0 bcache_spin:0 bversion:10566 bhot_keep:0    Buffer blpageno=121 bpg_size=8k Mass blpageno=121 Mass blocallockkeep=0    bxls_pin=0x0000000000000000 bxls_next=0x0000000000000000 bspid=0
    bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x0000000000000000 bssdbuf_ptr=0x0000000000000000     LFB delta=0x0000000000000000

Latch and the wait queue:
Latch (address: 0xc00000039cd98040)

Latch wait queue:

PAGE HEADER:
Page header for page 0xc00000039cd96000
pageno=121 nextpg=122 prevpg=0 ptnid=6 syscomments timestamp=0005 54dcda6e
nextrno=47 level=0 indid=0 freeoff=8010 minlen=20
page status bits (pstat): 0x911 (0x0800 (PG_XHEADER), 0x0100 (PG_ADDEND), 0x0010 (PG_RNOFREE), 0x0001 (PG_DATA))
second set of page status bits (dol_stat2): 0x02 (0x02 (PG2_DOL_DATAPG))
dol_pprivstat status bits: 0x01 (0x01 (DOLPG_PRIV_COMMITTED))
pagesize=8192, page version=1, ncfs=38, ndeleted=1, insert free space=124
pagetailts=0xda6e

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
------------------------- Done --------------------------


Why is parallelism not good in this query? I was looking for missing indexes in the cached execution plans then I found a very nice article by Jonathan Kehayias:

Finding Key Lookups inside the Plan Cache

and from there I borrowed the following script:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--IF OBJECT_ID('TEMPDB..#MissingIndexInfo') IS NOT NULL
--   DROP TABLE #MissingIndexInfo;

WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
    n.query('.'),
    i.value('(@PhysicalOp)[1]', 'VARCHAR(128)') AS PhysicalOp,
    i.value('(./IndexScan/Object/@Database)[1]', 'VARCHAR(128)') AS DatabaseName,
    i.value('(./IndexScan/Object/@Schema)[1]', 'VARCHAR(128)') AS SchemaName,
    i.value('(./IndexScan/Object/@Table)[1]', 'VARCHAR(128)') AS TableName,
    i.value('(./IndexScan/Object/@Index)[1]', 'VARCHAR(128)') as IndexName,
    i.query('.'),
    STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)')
       FROM i.nodes('./OutputList/ColumnReference') AS t(cg)
       FOR  XML PATH('')),1,2,'') AS output_columns,
    STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)')
       FROM i.nodes('./IndexScan/SeekPredicates/SeekPredicateNew//ColumnReference') AS t(cg)
       FOR  XML PATH('')),1,2,'') AS seek_columns,
    i.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)') as Predicate,
    cp.usecounts,
    query_plan
FROM (  SELECT plan_handle, query_plan
        FROM (  SELECT DISTINCT plan_handle
                FROM sys.dm_exec_query_stats WITH(NOLOCK)) AS qs
        OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
      ) as tab (plan_handle, query_plan)
INNER JOIN sys.dm_exec_cached_plans AS cp
    ON tab.plan_handle = cp.plan_handle
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS q(n)
CROSS APPLY n.nodes('.//RelOp[IndexScan[@Lookup="1"] and IndexScan/Object[@Schema!="[sys]"]]') as s(i)
OPTION(RECOMPILE, MAXDOP 1);

I noticed the MAXDOP 1 but I am not sure as how it can improve the performance of the query.

It doesn't, generally. It's there because XML queries are misery made by the devil hisself, and Jonathan is attempting to not cause observer overhead on your server.

As an alternative, sp_BlitzCache will warn you about expensive key lookups only.


MySQL LEFT JOIN with date filter Could you please help me with this subject.
We are using date filter since many time now and discovering a behaviour.

I made an exemple here : http://sqlfiddle.com/#!9/8f01f4/3

As you can see the join query returns 'a' and 'b' data.
When just adding a left join I loose 'b' data.

Could you please explain why this behaviour ?

If I change the where like that :  a.jma = '2020-05-18' OR a.jma = 20200518
it works. But I would like to understand to not reproduce this "bug".

Thanks

The codes copied from the fiddle:

CREATE TABLE `a` (
  `clef` INT(11) NOT NULL AUTO_INCREMENT,
  `jma` DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`clef`),
  UNIQUE KEY `jma` (`jma`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1
;

INSERT INTO a VALUES (1,20200518);

CREATE TABLE `b` (
  `clef` INT(11) NOT NULL AUTO_INCREMENT,
  `jma` DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`clef`),
  UNIQUE KEY `jma` (`jma`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1 ;

INSERT INTO b VALUES (1,20200518);

SELECT a.clef , b.clef
FROM a AS a
JOIN b AS b ON a.jma = b.jma
WHERE a.jma = '20200518';

SELECT a.clef , b.clef
FROM a AS a
LEFT JOIN b AS b ON a.jma = b.jma
WHERE a.jma = '20200518' ;

I checked around.  The bug seems to be in both MyISAM and InnoDB, MySQL 5.5 and 5.6.  It seems to be fixed in 5.7 and 8.0, plus MariaDB 10.

I did not find the bug report in order to identify what, exactly, was the cause, but here are some recommendations:

Don't use DEFAULT '0000-00-00'; "zero dates" are, by default disallowed in later versions.  (There is a switch to allow them.)
Don't use dates looking like 20200518, use instead '2020-05-18'
It may be that 20200518 works, but '20200518' fails (in 5.5 and 5.6).  That is, the quotes caused trouble.
Upgrade the version.
Reconsider the need for an AUTO_INCREMENT since you have a "natural" PRIMARY KEY (jma).


mariadb: replication master and slave have equal MySQL server ids I'm a novice mariadb user trying to set up a master with multiple slaves using mariadb-10.4.17 on fedora33.
I believe at one point I inadvertently ran the "change master" command on the actual master to be itself in addition to running it on the slaves. It appears that it was running properly for a while, but I've just noticed the following in the logs on the master:
2020-12-17  8:27:57 0 [Note] Reading of all Master_info entries succeeded
2020-12-17  8:27:57 0 [Note] Added new Master_info '' to hash table
2020-12-17  8:27:57 21 [Note] Slave I/O thread: Start asynchronous replication to master 'repuser@havoc.myserver.com:3306' in log 'mysql-bin.000674' at position 465296935
2020-12-17  8:27:59 21 [Note] Slave I/O thread: connected to master 'repuser@havoc.myserver.com:3306',replication started in log 'mysql-bin.000674' at position 465296935
2020-12-17  8:27:59 21 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Internal MariaDB error code: 1593

The "mysql-bin.000674" file does not exist anywhere on the filesystem. I've now stopped the slave, but is there something else I should do?
None of the current slaves are using the same IDs and the server itself is not shown among the lists of slaves.
MariaDB [(none)]> show slave hosts;
+-----------+-------------------------------+------+-----------+
| Server_id | Host                          | Port | Master_id |
+-----------+-------------------------------+------+-----------+
|        22 | armor.myserver.com            | 3306 |       590 |
|        11 | xavier.myserver.com           | 3306 |       590 |
|         2 | mail03.myserver.com           | 3306 |       590 |

Use RESET SLAVE ALL on the master to forget about is slave replication setting to itself.


Crosstab Pivot or not? I am have difficulty wrapping my head around the following issue.  I currently have a set of data in one table as shown below:

As you can see, there is an invoice ref number along with a date, and two value fields.  Each row has either a Y or N for the desired group the record belongs to.

Unfortunately I don't have access to re-format the data (coming out of an ERP system), and ideally I want to pivot the date, Value1, Value2 fields into columns per group.  At the moment I have a rather long winded approach of creating 6 small temp table vars with the InvoiceRef and Date/Value1/Value2 for each of the 3 groups.

I then read the main table, joining (left join) on InvoiceRef to each of the InvoiceRef fields within each of the temp table vars to get the following output:

So as you can see, I get the Date/Value1/Value2 fields for each InvoiceRef in the desired group across the columns..

My question is I feel there is a more efficient way to do this?  although just trying to grapple with pivot/crosstab is proving a challenge - I feel I need some pointers or even confirmation the temp table solution is generally an acceptable solution in this case?

Many thanks in advance.

P.S: For reference I am using MS-SQL 2008 R2

This has nothing to do with pivoting, you simply want to replicate your three columns conditionally three times, according to the number of groups. A series of CASE expressions would do the job perfectly:

SELECT
  InvoiceRef,
  Group1Date   = CASE Group1 WHEN 'Y' THEN Date   END,
  Group1Value1 = CASE Group1 WHEN 'Y' THEN Value1 END,
  Group1Value2 = CASE Group1 WHEN 'Y' THEN Value2 END,
  Group2Date   = CASE Group2 WHEN 'Y' THEN Date   END,
  Group2Value1 = CASE Group2 WHEN 'Y' THEN Value1 END,
  Group2Value2 = CASE Group2 WHEN 'Y' THEN Value2 END,
  Group3Date   = CASE Group3 WHEN 'Y' THEN Date   END,
  Group3Value1 = CASE Group3 WHEN 'Y' THEN Value1 END,
  Group3Value2 = CASE Group3 WHEN 'Y' THEN Value2 END
FROM
  dbo.atable
;


Storing data as rows as opposed to columns Here's a typical way I would store data (obviously not storing the password in plain text)

    USER TABLE

    | UserID | UserName | FullName | EMail   | Password |
    |--------|----------|----------|---------|----------|
    |1       |userAAA   |User Aaa  |aa@aa.com|aAaaA     |
    |1       |userBBB   |User Bbb  |bb@bb.com|bBbbB     |
    |1       |userCCC   |User Ccc  |cc@cc.com|cCccC     |
    |--------|----------|----------|---------|----------|

Is there anything wrong with storing it in the following manner?

    USER TABLE                 ATTRIBUTES TABLE

    | UserID | UserName |      |AttributeID | Attribute |
    |--------|----------|      |------------|-----------|
    |1       |userAAA   |      |1           |Full Name  |
    |1       |userBBB   |      |2           |E-Mail     |
    |1       |userCCC   |      |3           |Password   |
    |--------|----------|      |------------|-----------|

    ATTRIBUTE_VALUES TABLE

    |UserID | AttributeID | AttributeValue |
    |-------|-------------|----------------|
    |1      | 1           |User Aaa        |
    |1      | 2           |aa@aa.com       |
    |1      | 3           |aAaaA           |
    |2      | 1           |User Bbb        |
    |2      | 2           |bb@bb.com       |
    |2      | 3           |bBbbB           |
    |3      | 1           |User Ccc        |
    |3      | 2           |cc@cc.com       |
    |3      | 3           |cCccC           |
    |-------|-------------|----------------|

The huge benefit I see here is the ability to easily add additional attributes to the user. But I really wanted to get another opinion on this.

This is known as the Entity-Attribute-Value design. See Best Practices for Semantic Data Modeling for Performance and Scalability for a lengthy discussion of pros and cons.

The main problem is querying becomes both difficult to express at design time and unperformant at runtime.

Better approaches are to have a true schema, properly indexed, for known attributes, and allow EAV for future, unknown, custom, non-search critical attributes.

Schema-free databases, like MongoDB, are also alternatives to EAV.


Innodb buffer pool indexes versus data usage The server has 128GB of RAM. The innodb_buffer_pool_size is set to 106GB.

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+--------------+
| Variable_name           | Value        |
+-------------------------+--------------+
| innodb_buffer_pool_size | 113816633344 |
+-------------------------+--------------+

When looking at the information_schema tables, it looks like I'm utilizing ~85GB for indexes.

mysql> SELECT ist.SCHEMA db, ((SUM(data_size) / 1024) / 1024) / 1024 size_in_gb FROM INNODB_BUFFER_POOL_PAGES_INDEX ibppi JOIN INNODB_SYS_INDEXES isi ON(isi.INDEX_ID=ibppi.INDEX_ID) JOIN INNODB_SYS_TABLES ist ON(ist.TABLE_ID=isi.TABLE_ID) GROUP BY ist.SCHEMA ORDER BY size_in_gb DESC;
+-----------+-----------------+
| db        | size_in_gb      |
+-----------+-----------------+
| databaseB | 78.940829364583 |
| databaseA |  5.648017468862 |
+-----------+-----------------+

Is it safe to assume the remaining 20GB's is being used for data?

semi-related-note: information_schema tables are really slow. I've waited on some queries for many hours, only to come back the next day with the query still running. I've even went so far as creating another db, copying the accompanying information_schema tables, changing the table engine to innodb/myisam, and insert into <table> select * but this doesn't give me live results. If anyone has insight into making these information_schema queries faster, that would be appreciated as well.

I have some heartbreaking news for you that will solve your problem.

If you are using MySQL 5.5/5.6, I can assume you are using innodb_file_per_table. There is some disk I/O going on when accessing the INFORMATION_SCHEMA to get information on InnoDB.

What I am about to tell you I have personally eyewitnessed: I worked with a client that had a 2TB MySQL Instance with dozens of database and 10's of thousands of tables. Using MySQL 5.5, I was able to run these queries:

QUERY #1 : Disk Usage by Storage Engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

QUERY #2 : Disk Usage by Storage Engine

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(
FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM
(SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

I got responses 10-20 seconds. How? The client uses innodb_file_per_table=0.

I REPEAT : innodb_file_per_table=0

WHAT DO YOU DO NOW ???

Here is why I said "heartbreaking": You would have put all your InnoDB data back inside ibdata1. Would you like to know all the steps to do this ? Here we go...

STEP #1

Add these to /etc/my.cnf

[mysqld]
innodb_file_per_table = 0
innodb_fast_shutdown = 0

STEP #2

service mysql restart

STEP #3

Create a script that converts InnoDB to InnoDB

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > RemakeInnoDB.sql
cat RemakeInnoDB.sql

STEP #4

Execute the script

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} < RemakeInnoDB.sql

EPILOGUE

When everything is done, all the .ibd files will be gone. All the data and index pages will be inside ibdata1. You will find accessing the INFORMATION_SCHEMA against all InnoDB tables shockingly fast.

Here is the InnoDB Architecture

Note that ibdata1 (a.k.a. System Tablespace will house)

Data Pages
Index Pages
Data Dictionary
Double Write Buffer (Needed for crash recovery)
Insert Buffer (changes to secondary indexes)
Rollback Segments
Undo Space

Don't worry about putting 106G inside ibdata1. MySQL was running with innodb_file_per_table=0 before MySQL 5.5. It can still handle it.

Give it a Try !!!


MySQL slow query log file time elapse Does anybody know when the mysql slow log file delete slow query records?

based on my daily observation. The log file starts at 7am. I have searched for this but nothing answers me clearly. My server version if 5.6.14. Thank you.

This is probably done by your log rotation software. On my Ubuntu machine its defined in /etc/logrotate.d/mysql-server.

logrotate is run daily by a cron job. Take a look in /etc/crontab. In my case I have the following:

25 6    * * *   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily )

So daily jobs get run at 6:25AM.

I also have a file /etc/cron.daily/logrotate which configures log rotate as a daily job.

Most Linux installations would have something similar. If you're running on Windows I'm afraid I have no idea.


How to set up where to store data in SQL Server My C:\ drive has a size of 99GB and my D:\ drive has a size of 2TB

Here comes the problem: where does SQL Server store data in the database? Can I control it?

Actually, my C:\ has been full of data now..... and I don't know how to deal with it.

I want to configure SQL Server right so that I can store data on D:\

Thank you very much.

You could also control it with:

detaching the database in SQL management studio
move the *.mdf and *.ldf file to the location you desire (C:/ or D:/)
reattaching the database by selecting the new path.


How to make mysqldump use /*!40101 SET character_set_client = utf8mf4 */;? When I use mysqldump to export mysql database, it always produce a dump.sql containing

...some other things...
/*!40101 SET character_set_client = utf8*/;
...some other things...

This is the mysqldump command I use:

mysqldump -u root -p databaseName -R -E --single-transaction --default-character-set=utf8mb4 > dump.sql

The charset of the mysql database is utf8mb4 rather than utf8, the characters related variables is:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8mb4_unicode_ci         |
| collation_database       | utf8mb4_unicode_ci         |
| collation_server         | utf8mb4_unicode_ci         |
+--------------------------+----------------------------+

Why does mysqldump always add
/*!40101 SET character_set_client = utf8*/ rather than
/*!40101 SET character_set_client = utf8mb4*/?

What happens if /*!40101 SET character_set_client = utf8*/ is used?

Can we have mysqldump use /*!40101 SET character_set_client = utf8mb4*/?

That special type of comment says "leave this as a comment if running a version older than 4.1.1; else execute it".

utf8mb4 came in in 5.5.3, so the 'correct' version would be

/*!50503 SET character_set_client = utf8mb4*/

If your version of mysqldump and mysql are newer than 5.5.3, no harm is done by leaving it 40101.  If you do try to load it on a mysql older than 5.5.3, the SET will probably complain.

As for why mysqldump does not say 50503 -- I suspect that is a bug.  File it at http://bugs.mysql.com.


How to filter on multiple values for a given set of keys using JSONB, and an index Suppose we have a table (named rel_1) with an info field of type JSONB. The content of this field is client specific and may be changed every once in a while, but let's assume it to be of the form {"ki":"vi"} / i=0->N.

Now suppose we want to search for specific rows that have k0="foo" AND k1="bar". From the documentation, One can simply use the containment @> operator like so:

SELECT * FROM rel_1 WHERE info @> '{"k0":"foo", "k1":"bar"}';

While being elegant and working perfectly fine, it can also make use of a GIN index. It's also agnostic to what actual data for info might be. As long as the clients use the same keys for writes and reads, the DB side can completely ignore it and still do a fine job at optimizing requests using the index.

The only problem is that this approach fails if we change the requirement a little bit so that multiple values are allowed for a given key: (k0="foo" OR k0="bar") AND k1="baz".

Is there any way to achieve this while retaining the elegance and efficiency of the first solution?

I see 2 rather straight forward approaches depending on how many values we're looking for.

The first option is to look for a document containing the single valued keys (in this example: where info @> '{"k1":"bar"}'; and wrap that with additional filtering for all the other values: info->>k0 in <the values>

The 2nd option would be to create full documents for the various options, in your example it means creating 2 documents to search by. If the number of values is small them it's not too much of a problem and the index will be used.