Need to create combinations of Parents/Children/Dogs, but can't get past 2nd level of CTE I've hit a brick wall and please ask help.

I'm building a table up for (in this example) a ticketing system
where e.g. Ticket 1 will allow
Parent: Either parent Peter or Sally,
Child: to come without a child or with a child (either Sam, Pete or John),
Dog: and either without a dog or with one of the dogs (DogMatix,Snowy,Bliksem)

The table layout is that the Parent/Child/Dog is a parameter, and I can get (with CTE) to the Parent/Child relationships to all show, but not the next level showing the dogs too

declare @Ticket table
(
TID int
)

declare @Param table
(PID int IDENTITY(1,1) not null,
TID int,
ParamType varchar(20),
Val  varchar(40)
)

Insert into @Ticket(TID) Select(1)
Insert into @Ticket(TID) Select(2)

Insert into @Param(TID,ParamType,Val) values(1,'Parent','ParentPeter')
Insert into @Param(TID,ParamType,Val) values(1,'Parent','ParentSally')
Insert into @Param(TID,ParamType,Val) values(1,'Child','ChildSam')
Insert into @Param(TID,ParamType,Val) values(1,'Child','ChildPete')
Insert into @Param(TID,ParamType,Val) values(1,'Child','ChildJohn')
Insert into @Param(TID,ParamType,Val) values(1,'Dog','DogDogMatix')
Insert into @Param(TID,ParamType,Val) values(1,'Dog','DogSnowy')
Insert into @Param(TID,ParamType,Val) values(1,'Dog','DogBliksem')

Insert into @Param(TID,ParamType,Val) values(2,'Parent','ParentParker')
Insert into @Param(TID,ParamType,Val) values(2,'Parent','ParentScarlett')
Insert into @Param(TID,ParamType,Val) values(2,'Child','ChildBrett')
Insert into @Param(TID,ParamType,Val) values(2,'Child','ChildBritney')
Insert into @Param(TID,ParamType,Val) values(2,'Child','ChildBronwyn')
Insert into @Param(TID,ParamType,Val) values(2,'Dog','DogRover')
Insert into @Param(TID,ParamType,Val) values(2,'Dog','Dog2')
Insert into @Param(TID,ParamType,Val) values(2,'Dog','Dog3')

;with cte  as (
Select TID,Val as 'Parent'
from @Param p
where  p.ParamType = 'Parent'
 )
Select cte.TID,cte.Parent,p.Val as 'Child'
from cte
left outer join @Param p on p.TID = cte.TID and  p.ParamType = 'Child'

--The required results will be (for ticket 1)
--TID       Parent          Child           Dog
--1     ParentPeter     ChildSam        DogDogMatix
--1     ParentPeter     ChildSam        DogSnowy
--1     ParentPeter     ChildSam        DogBliksem
--1     ParentPeter     ChildPete       DogDogMatix
--1     ParentPeter     ChildPete       DogSnowy
--1     ParentPeter     ChildPete       DogBliksem
--1     ParentPeter     ChildJohn       DogDogMatix
--1     ParentPeter     ChildJohn       DogSnowy
--1     ParentPeter     ChildJohn       DogBliksem
--1     ParentPeter     null            null
--1     ParentPeter     ChildSam        null
--1     ParentPeter     ChildPete       null
--1     ParentPeter     ChildJohn       null

--1     ParentSally     ChildSam        DogDogMatix
--1     ParentSally     ChildSam        DogSnowy
--1     ParentSally     ChildSam        DogBliksem
--1     ParentSally     ChildPete       DogDogMatix
--1     ParentSally     ChildPete       DogSnowy
--1     ParentSally     ChildPete       DogBliksem
--1     ParentSally     ChildJohn       DogDogMatix
--1     ParentSally     ChildJohn       DogSnowy
--1     ParentSally     ChildJohn       DogBliksem
--1     ParentSally     null            null
--1     ParentSally     ChildSam        null
--1     ParentSally     ChildPete       null
--1     ParentSally     ChildJohn       null

First off, I think you should consider rethinking your data model a little, and look into some normalization techniques before going ahead with your project.

But, using your model ( only slightly altering it ), you could accomplish what you're after using the following method.
Simply add nulls for the child / dog levels and use a simple cross apply query to create the Cartesian product you seem to be after. There's no need for recursive queries, since your data isn't defined recursively.
But, this will work :

declare @Ticket table
(
TID int
)

declare @EntityTypes table
(
 id int primary key,
 name varchar(20)
)

declare @TicketEntities table
(ID int IDENTITY(1,1) not null,
 TicketId int,
 TypeId INT,
 EntityName varchar(40)
)

Insert into @Ticket(TID) values(1)
Insert into @Ticket(TID) values(2)

insert into @EntityTypes (id, name) values (1, 'Parent');
insert into @EntityTypes (id, name) values (2, 'Child');
insert into @EntityTypes (id, name) values (3, 'Dog');

Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,1,'Peter')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,1,'Sally')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,2,'Sam')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,2,'Pete')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,2,'John')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,2,NULL)
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,3,'Matix')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,3,'Snowy')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,3,'Bliksen')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,3,NULL)

Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,1,'Parker')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,1,'Scarlett')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,2,'Brett')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,2,'Britney')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,2,'Bronwyn')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,2,NULL)
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,3,'Rover')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,3,'Sam')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,3,'Laika')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,3,NULL)

Select par.ticketId, par.EntityName as Parent, child.EntityName as Child, dogs.EntityName as Dog
from @TicketEntities as par
    CROSS JOIN @TicketEntities child
    CROSS JOIN @TicketEntities dogs
where
    par.TypeId in (select id from @EntityTypes where name = 'Parent') AND par.TicketId = 1
    AND child.TypeId in (select id from @EntityTypes where name = 'Child') AND child.TicketId = 1
    AND dogs.TypeId in (select id from @EntityTypes where name = 'Dog') AND dogs.TicketId = 1


How to dynamically generate rows based on value in a column I'm attempting to write a query to denormalize some data for integrating to our data warehouse, specifically around generating some time slots.

In my source system, I have table that has a start time and and end time and then a column that designates how many appointment slots are available per 15 minute interval.

Example:

    Location | Start Time | End Time | Appointment Slots
    A        | 9:00 am    | 5:00 pm  | 2
    B        | 9:00 am    | 5:00 pm  | 1

I have already been able to generate a list of 15 minute blocks using a CTE. However, each 15 minute slot just says that 2 slots are available for location A and 1 slot is available for location B. What I'd like is to be able to generate 2 rows per 15 minutes for location A and 1 row per 15 minutes for location B.

This is an example of when a "Number" table becomes very handy! If you are not familiar with this concept, read this blog post from Adam Machanic: You REQUIRE a Numbers Table

The numbers table is used to join to the Appointment Slots column (in this case) to duplicate each row AppointmentSlots number of times.

-- A simple recreation of your CTE data...
CREATE  TABLE #AppointmentPeriods
(
    AppointmentLocation CHAR(1),
    AppointmentStartTime TIME,
    AppointmentEndTime TIME,
    AppointmentSlotsAvailable SMALLINT
)

INSERT  INTO #AppointmentPeriods VALUES
    ('A', '09:00', '09:15', 2),
    ('A', '09:15', '09:30', 2),
    ('A', '09:30', '09:45', 2),
    -- ....
    ('A', '16:30', '16:45', 2),
    ('A', '16:45', '17:00', 2),
    ('B', '09:00', '09:15', 1),
    ('B', '09:15', '09:30', 1),
    ('B', '09:30', '09:45', 1),
    -- ....
    ('B', '16:30', '16:45', 1),
    ('B', '16:45', '17:00', 1)

-- Numbers table
CREATE  TABLE #Numbers (NumberValue SMALLINT)

INSERT  INTO #Numbers
-- TOP value should be changed so it is greater than the
-- maximum number of potential appointment slots any location can have
SELECT  TOP 20
        ROW_NUMBER() OVER (ORDER BY object_id)
FROM    sys.objects

SELECT  #AppointmentPeriods.*, NumberValue AS AppointmentSlotNumber
FROM    #AppointmentPeriods
        INNER JOIN #Numbers
            ON AppointmentSlotsAvailable >= NumberValue

Query output:

Location | StartTime | EndTime | AppointmentSlots | SlotNumber
A        | 09:00     | 09:15   | 2                | 1
A        | 09:00     | 09:15   | 2                | 2
A        | 09:15     | 09:30   | 2                | 1
A        | 09:15     | 09:30   | 2                | 2
B        | 16:30     | 16:45   | 1                | 1
B        | 16:45     | 17:00   | 1                | 1

PS: There are many ways to generate a numbers table: SO: What is the best way to create and populate a numbers table?


SQL Server Data Tools - Data Link Properties - Server Name Drop Down Empty I'm using VS 2019 with the latest version of SQL Server Integration Services Projects plugin. I have 2 local SQL Server instances installed on the same machine which are up and running (including Browser service). SQL Server is 2017 and 2019 (default and named instance respectively) and their Hide Instance flag is set to No.
When adding Data Link for Data Flow SSIS tasks (Destination or Source Assistant) server drop down not showing my SQL instances like that:

At the same time if I type in SQL Server instance name I can connect just fine. What can be causing my SQL instances not showing up here?

Just to close this off - you may see this problem depending on selected provider, if your provider set to "Native OLE DB\Microsoft OLE DB Driver for SQL Server" you may observe empty server drop down in Data Link Properties (exactly what we have on the screenshot in the question above), and just switching your provider to "Native OLE DB\SQL Server Native Client 11.0" resolves this problem. Actually this change changes UI slightly - you won't have "Data Links..." button next to OLE DB Provider which opens up Data Link properties as per screenshot in the question but it instead you will have Server Name drop down directly in Connection Manager window under Provider drop down as shown below:


WITH RECURSIVE with union among the recursive term Is it possible to use WITH RECURSIVE queries in PostgreSQL where the recursive term is a union of different tables? My usecase is to extract some tuples from a table and some logical rules:

And I would like to exploit recursive queries having the following syntax:

WITH RECURSIVE cte_name(
    CTE_query_definition -- non-recursive term
    UNION
    CTE_query definion  -- recursive term
) SELECT * FROM cte_name;

where p1 and p3 can be tuples from different possible types. This means that my non-recursive term will be:

SELECT * from TUPLES

While the recursive term generates new tuples from our data but does not have to generate tuples that already exist (CTE with UNION without ALL).

SELECT
       'ex' AS type_event,
       t1.arg2 AS arg1,
       t1.arg6 AS arg2,
       null AS arg3,
       null AS arg4,
       null AS arg5,
       null AS arg6
    FROM cte_name t1
    WHERE t1.type_event = 'Transfering'
      AND t1.arg2 = t2.arg1
      AND t1.arg6 = t2.arg2

UNION ALL

       'ex' AS type_event,
       t1.arg2 AS arg1,
       t1.arg6 AS arg2,
       null AS arg3,
       null AS arg4,
       null AS arg5,
       null AS arg6
    FROM cte_name t1
    WHERE t1.type_event = 'Replacement'
      AND t1.arg2 = t2.arg1
      AND t1.arg6 = t2.arg2
UNION ALL

       'ex' AS type_event,
       t1.arg2 AS arg1,
       t1.arg1 AS arg2,
       null AS arg3,
       null AS arg4,
       null AS arg5,
       null AS arg6
    FROM cte_name t1
    WHERE t1.type_event = 'ex'

By the way, WITH RECURSIVE does not allow to express recursive statements where the recursive element is a union of different selects. Is there an efficient solution that does not involve to programmatically implement this in C++/Java/Python, so that all the data "stays" in the database and I do not need to serialize and desirialize the data multiple times?

Example of input data for TUPLES:

+-------------+------+------+------+------+------+------+
| type_event  | arg1 | arg2 | arg3 | arg4 | arg5 | arg6 |
+-------------+------+------+------+------+------+------+
| ex          | A    | B    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| Transfering | A    | B    | C    | D    | E    | F    |
+-------------+------+------+------+------+------+------+
| Replacement | G    | H    | I    | J    | K    | L    |
+-------------+------+------+------+------+------+------+

Expected output after the third and final iteration:

+-------------+------+------+------+------+------+------+
| type_event  | arg1 | arg2 | arg3 | arg4 | arg5 | arg6 |
+-------------+------+------+------+------+------+------+
| ex          | A    | B    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| Transfering | A    | B    | C    | D    | E    | F    |
+-------------+------+------+------+------+------+------+
| Replacement | G    | H    | I    | J    | K    | L    |
+-------------+------+------+------+------+------+------+
| ex          | B    | F    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| ex          | H    | L    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| ex          | B    | A    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| ex          | F    | B    | null | null | null | null |
+-------------+------+------+------+------+------+------+
| ex          | L    | H    | null | null | null | null |
+-------------+------+------+------+------+------+------+

The three parts of the desired recursive term select rows from the same CTE, so you can transform them into a single SELECT:

SELECT ...
FROM cte_name t1
WHERE (t1.type_event = 'Transfering' AND t1.arg2 = t2.arg1 AND t1.arg6 = t2.arg2)
   OR (t1.type_event = 'Replacement' AND t1.arg2 = t2.arg1 AND t1.arg6 = t2.arg2)
   OR (t1.type_event = 'ex')

Reordering the columns can be done with a CASE expression:

SELECT 'ex' AS type_event,   -- same for all three
       t1.arg2 AS arg1,      -- same for all three
       CASE t1.type_event
         WHEN 'Transfering' THEN t1.arg6
         WHEN 'Replacement' THEN t1.arg6
         WHEN 'ex'          THEN t1.arg1
       END AS arg2,
       ...


How to implement 'temporal database' to keep Sales records truthful in POS? I'm developing a PHP/MySql retail shop management solution (for the first time!) that keeps records of sales and inventory. I have a problem, which will be better understood by the following chronology of events:
2021-01-19: Shopkeer_Joe purchases 2 chairs from supplier_Tom and enters the record into the software which creates produc_id '1' in the database.
2021-01-20: Shopkeeper_Joe sells both chairs to customer_Alicia, with sale_id, '1' referencing product_id '1' in database.
2021-11-20: (10 months later) With zero chairs sold since customer_Alicia bought the last lot, Shopkeeper_Joe decides to order Desks this time (not chairs) from the same supplier_Tom, But this time, instead of inserting a new record for newly supplied Desks, shopkeeper_Joe, for whatever reasons edits the zero-quantity old chair product entry (product_id '1'), and simply renames the product name from Chair to Desk, thus creating a problem now my software is showing that on 20th-January-2021, Alicia bought two Desks when indeed, she bought two chairs!
My question is how can I ensure that the sale records, in my software stay truthful ??
UPDATE:
I don't want to ban users from editing records because they genuinely may have to correct some mistake in their product record. I am actually trying to research if there is any way, I could keep the sale records truthful while keeping the product edit option open. Maybe by keeping a history of records, like 'temporal database', like querying to get 'what was the name of the product on sale date?' and then displaying 'Original Sale' (as issued on sale date), and 'Modified/edited Sale' because of foreign-key referenced product renaming. BUT I don't know much (anything?) about temporal databases, any help/clue would be appreciated.

This problem is essentially that of slowly changing dimensions. Typically you would not allow editing such records in place; instead you create a new version of it (if your user is stupid enough not to realize that a desk is a different product), while the old sales record still references the previous version.


How to resolve Error 600122, severity 11, state 1 was raised My SQL Server 2008 R2 keeps recording these error in the SQL Server log:

  Error 600122, severity 11, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Some are:

  Error 400006, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

I also got similar errors in server event log as follow:

  Error 600122, severity 11, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Are they with regard to compatibility level of Databases as https://www.experts-exchange.com/questions/28523045/Error-55555-severity-16-state-1-was-raised-but-no-message-with-that-error-number-was-found-in-sys-messages-If-error-is-larger-than-50000-make-sure-the-user-defined-message-is-added-using-sp-addm.html said?

Is there anybody resolved it? Because it's in my Prod environment so I want to get more info.

Thanks a lot!

They are generated in your code, try this to locate them

SELECT  *
FROM    sys.sql_modules SM
WHERE   [definition] LIKE '%600122%' or [definition] LIKE '%400006%'

Or this will also bring all custom error

SELECT  *
FROM    sys.sql_modules SM
WHERE   UPPER([definition]) LIKE '%RAISERROR%' or UPPER([definition]) LIKE '%THROW%'

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql

Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.


Is using innodb_file_per_table improve speed of alter table command? As innodb_file_per_table option separates table files instead of putting all data and indexes of DBs into one ibdata file, is using this option improve speed of alter table?

I have a table of 40M rows and when I alter a specific table it takes about 5 to 6 hours.

Does this solution help? Is there other ways around to improve alter table speed on heavy tables?

innodb_file_per_table is convenient option to manage disk space used by innodb, but it won't improve ALTER TABLE time.

As of 5.6 InnoDB supports online DDL for some kind of ALTERs. Check Online DDL page to see what's currently supported.

If not, you may consider using pt-online-schema-change or similar tool(https://github.com/github/gh-ost, https://github.com/facebookincubator/OnlineSchemaChange). It will run long time, but won't block the table.


Split database to fit on SSDs I have a database that's about 3TB in size and want to upgrade to SSDs. The SSDs I found that are reasonably priced are only 512GB so I'll need to purchase multiple drives.

What are some ways to split the database file so that it can be accessed from multiple drives without much code changes or needing a RAID controller.

The database software in question in sql server 2014 enterprise edition, but it would be best to find a solution without requiring database engine specific features.

In SQL Server, you can create file groups that consist of 1 or more files.  You can then migrate data into those file groups individually by rebuilding the clustered index for each table on that file group.

You can also do the same for indexes by rebuilding the index by specifying the desired file group.  You can use file size caps to ensure the files do not grow larger than 512 GB.

You will want to do this prior to migrating to the new drive array so that you can do a backup/restore or a detach/attach to the new array with no issues.  You will also want to shrink the PRIMARY file group after you're finished migrating data to the new file group(s). (I'm assuming everything is in PRIMARY currently).

This will be your easiest solution that will not require any coding changes but it will incur some significant performance hits while the data is being moved.

Your performance will likely go up markedly by splitting the data into multiple files and moving to SSDs. I would suggest doing a backup first, and don't do detach-attach if you need to physically move files around - backup/restore is FAR safer.

Multiple filegroups are superior to multiple PRIMARY files as they allow piece-meal restores, which can help get the database up and running far more quickly in a DR event.


Display two digits after decimal point in SQL from average value This is my code

SELECT  AVG(`page_rate` )
FROM `pages`
   INNER JOIN `languages` ON `pages`.`lang_id`=`languages`.`id`
WHERE `abbr` IN ("EN-UK", "EN-US", "EN-AU")

How can I display only 2 digits after decimal point?

I tried this:

SELECT AVG( cast(`page_rate` as decimal(10,2)))
FROM `pages`
    INNER JOIN `languages` ON `pages`.`lang_id`=`languages`.`id`
WHERE `abbr` IN ("EN-UK", "EN-US", "EN-AU")

but result is 20.166667 - not only 2 digits

Switch your AVG and CAST. Instead of

SELECT AVG( cast(`page_rate` as decimal(10,2)))

Use

SELECT CAST(AVG(`page_rate`) as decimal(10,2))

Then you are casting your average with 2 decimal places.


Can I find out current disk response time in MS SQL Server using DMOs? using sys.dm_io_virtual_file_stats DMV I get io_stall which return response time in ms, but it is an accumulated result. Using Activity Monitor Data File I/O Pane, in the Response Time(ms) column we get aggregated but instant results. Though this column points to a corresponding field in the DMV, the results widely differ. In Activity Monitor there are relatively small numbers. I want to know how can I get that same instant response time via script. Is it possible to get that information in SQL Server using tsql or should I use server monitoring tools?

What is Activity Monitor Doing

You're right, those values from sys.dm_io_virtual_file_stats accumulate the entire time the SQL Server service is running:

  The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started.

Activity Monitor is just taking a snapshot of the results, waiting 10 seconds, taking another snapshot, and then showing you the difference.  This is why the numbers are small.

It can be useful to view these diffs, or log them to a table on a schedule, in order to help you know what's "normal" and what's "slow" for your server.  Some tools that can help you do this:

Paul Randal's script: Capturing IO latencies for a period of time
sp_BlitzFirst (which conveniently has parameters that will log the results to a table for your automatically): Logging sp_BlitzFirst to Tables

If your goal is to see the same numbers from Activity Monitor via T-SQL, I would go with one of these options.

Disk Response Time vs File Response Time

The sys.dm_io_virtual_file_stats dynamic management function returns these stalls (AKA latency or disk response time) on a per-file basis.  If you're looking for a pattern of slow response times, or a way to get latency, at the disk / LUN level, you'll have to be careful to consider which disk each of these files is on.

To the direct point of your question, SQL Server is not a great source of information about disk-level I/O issues.

What is Slow?

You'll see varying rules of thumb online, here's a common one (from SQLPerformance.com):

  If you ask SQLskills, we will tell you something along the lines of:

  Excellent: < 1ms
  Very good: < 5ms
  Good: 5 – 10ms
  Poor: 10 – 20ms
  Bad: 20 – 100ms
  Really bad: 100 – 500ms
  OMG!: > 500ms

  If you do a Bing search, you will find articles from Microsoft making the recommendations similar to:

  Good: < 10ms
  Okay: 10 – 20ms
  Bad: 20 – 50ms

Take those values with a grain of salt though.  What matters is that performance is good enough, not whether a number has a certain value.  Make sure to treat the patient, not the monitor.

Outside of SQL Server

To get truly useful information about about disk I/O, you'll really want to go outside of SQL Server.  Windows Performance Monitor (PerfMon) is a good way to see some of these stats in "real time."  You can see them aggregated across the whole machine, or by disk.  The counters you want are Avg. Disk sec/Read and Avg. Disk sec/Write, which are both part of the Physical disk object.

I've heard that xperf is also a good tool for investigating this.  I haven't used it, but I'll refer you to an excellent Microsoft blog post that provides a lot of details on usage and analysis: Analyzing Storage Performance using the Windows Performance Analysis ToolKit (WPT)

Maybe It's Not the Disk

It's important to note that high disk response times are not necessarily due to a "slow disk."  As the MS blog post above mentions, It could be SAN issues (you might have unplugged or low throughput cabling, downgraded to slower disks than expected on the SAN, the network might be overloaded - even by traffic from other servers and sources - causing slow response times, etc).

It could be that the workload is doing too much I/O, and the disk just can't keep up.  This type of problem can potentially be solved with query and index tuning, rather than trying to improve or upgrade a disk.

It could even be high CPU or oversubscribed VMs, or many other things.


UPDATE from subquery updates rows that shouldn't be updated Using PostgreSQL, I want to update a column. The information how to update is in another table.

My try was this, but it also updates rows I don't want to update:

update table_1
set column_x = (
    select table_2.rename
    from table_2
    where table_2.rename IS NOT NULL
        and table_1.id = table_2.id)

How to modify the query?

Maybe this helps to explain my problem: http://www.sqlfiddle.com/#!15/318fb/1

Use a FROM clause to join to more tables:

UPDATE table_1 t1    -- alias optional
SET    column_x = t2.rename
FROM   table_2 t2
WHERE  t2.rename IS NOT NULL  -- assuming you actually meant t1
AND    t2.id = t1.id;

This joins rows from table_2 in a CROSS JOIN - filtered by the condition t2.id = t1.id which makes it an INNER JOIN effectively.

This way, rows without a match in the other table are not touched at all - unlike your query with a correlated subquery.

SQL Fiddle with extended demo.

You must also understand the difference between NULL, empty string and "no row" (which results in NULL in your correlated subquery).

Details in the manual.


MySQL cannot create file (permission denied). even my folder permission is 1777 (Ubuntu) hello I have some issue with my MySQL db. I've try so many ways to fix it, but it cannot solve my problem. I tried this Cannot output MySQL data to file. and still have same error.

this is my query and error result :

  mysql> select * from categories into outfile
  '/var/www/html/project_pfm/output/file2.txt';

  ERROR 1 (HY000): Can't create/write to file
  '/var/www/html/project_pfm/output/file2.txt' (Errcode: 13 - Permission
  denied)

and this is my file permission in OS :

  root@WebPFI01:/var/www/html/project_pfm# ls -al

  drwxrwxrwx  3 mysql mysql      4096 Apr  6 09:35 output

thank you for your help and answer.

Ubuntu ships with AppArmor which may be causing your issues. You might want to look at some of the discussion at https://stackoverflow.com/questions/2783313/how-can-i-get-around-mysql-errcode-13-with-select-into-outfile


Availability Group with log backups to NUL We currently have a database in SIMPLE recovery and are planing on setting up an Availability Group for reporting purposes (Enterprise SQL 2016).  My understanding is that this will require moving the database to FULL recovery.  We have no need of the log backups and no interest in storing them so I was thinking about setting up the log backups to NUL (bit bucket).  Nothing I've read has said I'll have any problems with this but I wanted to make sure I'm not missing anything.

No, you aren't missing anything except the opportunity to do a point in time recovery of the database.  AG's protect you from MOST hardware failures but they don't protect from user errors that drop a table or truncate data. Even if the business didn't have a SLA for RPO I would have log backups running as a DBA just to cover things, but you can backup to DISK='NUL' and the log will clear just like you backed up to a regular disk file.


Query to find Maximum friends not giving correct resuts Table Schema for the Likes table:

id id1
1  2
1  3
2  1

I count the total number of Likes for each student with this:

select id,count(*)
from friends
group by id

I have to find the students with the maximum number of Likes, so I use this query as a temp table:

select * from (
  select f1.id,count(*) as count1
  from Likes f1
  group by id
) temp
where not exists (
  select f2.id,count(asterick) as count2
  from Likes f2 group by f2.id having count2 > temp.count1
)

It returns all of the records. Can anyone point out what I am doing wrong in this query?

Your query is correct and works fine in other DBMS (SQL-Server, Postgres, Oracle, MySQL). In SQLite, it appears there is a bug, possibly due to the correlated subquery and/or the grouping in both the main and the sub query. Here is another way to write the query:

SELECT id, COUNT(*) AS count1
FROM Likes
GROUP BY id
HAVING COUNT(*) =
      ( SELECT COUNT(*) AS cnt
        FROM Likes
        GROUP BY id
        ORDER BY cnt DESC
          LIMIT 1
      ) ;

Tested in SQL-Fiddle


Select rows with same id but null and some other value in another column for that id I want to get only rows having a value NULL and some other value than NULL for a particular username column.

If both rows have null for that particular username or both have some values other than null then it should not appear in output.  If there are more than two rows for same username with null and some other value then they should appear.

Below is example sample and output. How it can be done using sql query?

+----------+-------+
| username | col2  |
+----------+-------+
| a        | abc   |
| a        | ef    |
| b        | null  |
| b        | null  |
| c        | der   |
| c        | null  |
+----------+-------+

output

+----------+------+
| username | col2 |
+----------+------+
| c        | der  |
| c        | null |
+----------+------+

You should be able to use conditional aggregation to get the username with both a value in col2 as well as null.

I'd suggest using a HAVING clause with the conditions. The query would be similar to:

select username
from yourtable
group by username
having sum(case when col2 is not null then 1 else 0 end) = 1
  and sum(case when col2 is null then 1 else 0 end) = 1

See SQL Fiddle with Demo. This query groups your data by each username and then uses conditional logic to check if col2 meets both conditions you want - where col2 is not null and col2 is null.

You can then use this in a subquery, etc to get the username and col2 values:

select
  t.username,
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) = 1
    and sum(case when col2 is null then 1 else 0 end) = 1
) d
  on t.username = d.username

See SQL Fiddle with Demo.

If you have more than one col2 row with both null and another value, then you just need to alter the HAVING clause slightly:

select
  t.username,
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) >= 1
    and sum(case when col2 is null then 1 else 0 end) >= 1
) d
  on t.username = d.username;

See SQL Fiddle with Demo


Package deployed in SSIS 2012 catalog fails with timeout error I created an SSIS package in Visual Studio 2008 and deployed it to SQL Server 2012 by using Integration Services Catalogs. When I run the package from BIDS it completes successfully. When I schedule the package to run using a job the execution of the job fails (sometime randomly) with the following message:

  Environment reference Id: NULL. Description: Timeout expired. The
  timeout period elapsed prior to completion of the operation or the
  server is not responding. Source: .Net SqlClient Data Provider

I tried to add retry for this step and at second retry it works fine. I don't know the reason,  this job worked for 2 years with no issues.

delete Unused Connection String(specially those have invalid connection string) search for child Package reference that are disable but has invalid references.


Merge Join Performance Tuning I have been looking at an issue with performance with a vendor SQL query, usually with this vendor I can see indexes that will improve performance but this query is currently a little out of my league in terms of performance tuning. Hopefully after this request I will learn something new.

The issue with the SQL appears to be a Merge Join (Inner Join) and I am not sure on the best way to try and improve the performance of these.

I would really appreciate someone pointing me in the right direction or if there is any more information I can provide then please let me know.

Here is the execution plan and the SQL query.

There are not many indexes on this one as I have tried applying indexes but they seem to have no effect reducing the Merge Join. Therefore the plan I have given you is using the vendor's default indexes.

Update 1: I have added indexes to query but they have little affect, here are the indexes and new plan

New SQL Plan and the Indexes

I also updated statistics on all tables involved in the query.

Using the option (hash join) reduces the run time down from 30 seconds to 15 seconds. However this is vendor SQL run from behind a screen so I cannot force this.

MAXDOP on the server is set to 0 and Cost Threshold is 50 so not sure why it doesn't want to go parallel.

UPDATE 2: It appears that having the following unique indexes on the table atstsehourdetmap causes the issue. If I drop these then the query runs in less than a second.

CREATE UNIQUE INDEX [aiatstsehourdetmap2] ON [dbo].[atstsehourdetmap] ( [oid] ) ;

CREATE UNIQUE INDEX [aiatstsehourdetmap1] ON [dbo].[atstsehourdetmap] ( [tsehourdet_id] ) ;

Any ideas why?

The issue with the SQL appears to be a Merge Join...

The optimizer's estimate is that the Merge Join will account for 85.8% of the cost of running the query, but you should always treat these figures with a healthy degree of suspicion.

They are just estimates based on a fairly simple costing model that helps the optimizer make general choices between alternatives, but the numbers almost certainly do not correlate with the performance characteristics of the execution plan on your particular hardware. The cost estimates are a broad hint at things the optimizer felt were costly, but there's no substitute for analysis by a skilled and experienced database professional.

That said, the main reason for the optimizer's high cost estimate for the merge join in this case is that the join is assumed to be many-to-many. This is because the database schema does not provide any constraints or unique indexes to guarantee a one-to-many relationship. The assumed many-to-many join is also the reason for the outrageously high join output cardinality estimate. Given incomplete information, the optimizer produces an inaccurate estimate.

The question does not provide schema DDL for the tables involved, but we can infer some of the columns from the execution plan, and something about the relationships from the joins. The data types are unknown for the most part, and we cannot know about foreign keys or other constraints, of course.

An update to the question while I was writing this answer states that the query cannot be changed, but for the sake of analysis, I had already rewritten it to at least use modern JOIN syntax:

SELECT
    a.wf_state,
    b.oid,
    p.wf_user_id,
    p.error_no,
    item_followup = 1,
    p.element_type,
    selected = 0,
    p.version_no,
    p.p_description,
    p.s_description,
    p.proc_node_id,
    p.step_node_id,
    p.distr_type,
    f.project,
    d.reg_period,
    d.resource_id,
    d.last_update,
    a.used_hrs
FROM dbo.awfenquiry AS p
JOIN dbo.atstsehourdetmap AS b
    ON b.oid = p.oid
JOIN dbo.atstsehourdet AS a
    ON a.tsehourdet_id = b.tsehourdet_id
JOIN dbo.atstsegldetail AS c
    ON c.tsegldetail_id = a.tsegldetail_id
JOIN dbo.atstseheader AS d
    ON d.tseheader_id = c.tseheader_id
JOIN dbo.atschargecode AS e
    ON e.client = c.client
    AND e.chargecode_id = c.chargecode_id
JOIN dbo.atsproject AS f
    ON f.project = e.project
    AND f.client = e.client
WHERE
    p.element_type = N'TS'
    AND a.client = N'GB'
    AND
    (
        a.wf_state = N'U'
        OR p.distr_type = N'U'
    );

One of the main improvements you can make is to give each table a clustered index. Almost all tables benefit from having a clustered index, either for space management reasons, or simply because it provides an extra 'free' index. In many cases, the clustered index will also be the primary key, though this is not required. All tables should have a key. Many people would argue that a table is improperly formed without one. As a starting point, I have given each table a primary key based on my best guess. The primary key is clustered by default.

Putting all that together, a first pass at a schema is:

CREATE TABLE dbo.awfenquiry -- p
(
    oid integer PRIMARY KEY,
    element_type nvarchar(50) NULL,
    wf_user_id integer NULL,
    error_no integer NULL,
    version_no integer NULL,
    p_description nvarchar(50) NULL,
    s_description nvarchar(50) NULL,
    proc_node_id integer NULL,
    step_node_id integer NULL,
    distr_type nvarchar(10) NULL
);

CREATE TABLE dbo.atstsehourdetmap -- b
(
    oid integer NOT NULL,
    tsehourdet_id integer NOT NULL,

    PRIMARY KEY (oid, tsehourdet_id)
);

CREATE TABLE dbo.atstsehourdet -- a
(
    tsehourdet_id integer PRIMARY KEY,
    tsegldetail_id integer NULL,
    wf_state nvarchar(10) NULL,
    used_hrs integer NULL,
    client nvarchar(10) NULL
);

CREATE TABLE dbo.atstsegldetail -- c
(
    tsegldetail_id integer PRIMARY KEY,
    tseheader_id integer NULL,
    client nvarchar(10) NULL,
    chargecode_id integer NULL
);

CREATE TABLE dbo.atstseheader -- d
(
    tseheader_id integer PRIMARY KEY,
    reg_period integer NULL,
    resource_id integer NULL,
    last_update datetime2 NULL,
);

CREATE TABLE dbo.atschargecode -- e
(
    chargecode_id integer NOT NULL,
    client nvarchar(10) NOT NULL,
    project integer NULL,

    PRIMARY KEY (chargecode_id, client)
);

CREATE TABLE dbo.atsproject -- f
(
    project integer NOT NULL,
    client nvarchar(10) NOT NULL,

    PRIMARY KEY (project, client)
);

Statistics were not provided either, but we can at least see the overall table cardinalities in the execution plan. The following statements provide this information for the table definitions above, without any information about value distribution:

UPDATE STATISTICS dbo.awfenquiry WITH ROWCOUNT = 51826;
UPDATE STATISTICS dbo.atstsehourdetmap WITH ROWCOUNT = 2748620;
UPDATE STATISTICS dbo.atstsehourdet WITH ROWCOUNT = 2743040;
UPDATE STATISTICS dbo.atstsegldetail WITH ROWCOUNT = 1223270;
UPDATE STATISTICS dbo.atstseheader WITH ROWCOUNT = 328822;
UPDATE STATISTICS dbo.atschargecode WITH ROWCOUNT = 23983;
UPDATE STATISTICS dbo.atsproject WITH ROWCOUNT = 18169;

Based on the query, there are few obvious index candidates beyond the keys already added. Two that may be of use are:

CREATE NONCLUSTERED INDEX i
ON dbo.awfenquiry (element_type);

CREATE NONCLUSTERED INDEX i
ON dbo.atstsehourdet (client)
INCLUDE (wf_state, tsegldetail_id, used_hrs);

The resulting execution plan is not obviously a massive improvement over the original, aside from the (now one-to-many) merge join, but it is a step in the right direction perhaps:

All the tables are relatively small, so I would probably check next to see if query execution is being blocked by another process, fighting for resources with other queries, or struggling with an overwhelmed storage system. Fifteen seconds seems entirely unreasonable for the task.


New Cluster Testing - Best Practice We've finished setup of a 4-node SQL Server 2005 cluster. We are using Windows 2008 R2 as the underlying OS.

We are looking for suggestions on a set of tests we could perform to test failover of the SQL Instances?

Not even close to comprehensive, but I'd start with:
1.  Pull the ethernet cable(s) for your public ip interface on your primary/active node.  Confirm the failover.
2.  Pull the SAN fibre cable(s) for your active node. Confirm the failover.
3.  Pull the power cable(s) for you active node.  Confirm the failover.

Those represent the main types of failure that MS Clustering will compensate for in the first place...

I think I'd have my real/prod db detached or offline while I played these games.*


Join two tables and return data and count in a single query I have two tables in an SQL Server database, one with two columns and one with four:

tbl_email_list

email_list_id int (PK)
email_list_name varchar

tbl_email-details

email_uniq_id int (PK)
email_list_id int (FK)
email_address varchar
blacklist bit

I want to retrieve data in one query which should return

All the email lists from tbl_email_list;
The total number of email_address associated with a specific email_list_id;
The total number of whitelisted email addresses (where blacklist=0);
The total number of blacklisted email addresses (where blacklist=1).

select l.email_list_id, l.email_list_name,
    count(d.email_uniq_id) as full_count,
    count(case when d.blacklist = 0 then d.email_uniq_id end) as white_count,
    count(case when d.blacklist = 1 then d.email_uniq_id end) as black_count
from tbl_email_list as l
left join [tbl_email-details] as d on d.email_list_id = l.email_list_id
group by l.email_list_id, l.email_list_name;

By counting things which can be null, we let zeroes appear in the result set, which is a very useful technique. Here I do it for both the blacklist results and the overall (based on the outer join).


member access to couchDB browser gui fauxton I'm using Couchdb v3.0.0, and testing the browser GUI.

I have created users in the database "_users", and have also assigned a role to that user.

I have tested by getting related documents via curl or browser with that user on a particular database with no problems.

But if I want to login to the browser GUI with that user, it will just keep loading in the "databases" section. Choosing other sections will also be prompted "You are not a server admin" in 1 step or 2.

The above happens regardless if the tester account has been assigned member or admin access, neither of them can use the browser GUI to browser documents inside the database.

Is it true that the browser GUI is only usable by admins (which are defined in the local ini files)? or am I missing anything?

In CouchDB 3.0.0, the /_all_dbs endpoint became admin-only. This has the side-effect of Fauxton, the “Browser GUI”, not loading properly for non-admin users, as it starts out by reading /_all_dbs.

The release notes have a section that go into the changes.

This is the code change: https://github.com/apache/couchdb/issues/2576

You can get the previous behaviour by setting the config variable [chttpd] admin_only_all_dbs to false, but that allows even unauthenticated users to get a full list of your databases.


Efficient way to join two tables with one to many relationship This is interview question asked to me in interview and i was not able to answer. Please help..

What is the most efficient way to join two table with one to many relationship between them

Most effective way is to use an INNER JOIN, like this:

SELECT   *
FROM        Parent P
INNER JOIN  Child  C
    ON      C.ParentId = P.ParentId

And when you need aggregates on child level, you can add a GROUP BY and SUM and/or AVG or other aggregate functions:

SELECT      P.Name
            , P.Address
            , SUM(C.Amount)
FROM        Parent P
INNER JOIN  Child  C
    ON      C.ParentId = P.ParentId
GROUP BY    P.Name
            , P.Address


SQL Server AlwaysOn Table and Archiving We have a number of tables that will process a large amount of inserts each day.  For this reason, the tables are archived on a weekly basis using a labour intensive process of backing up the database and restoring to another server and then inserting the data from this backup into another archive database.

My plan is to introduce AlwaysOn for High Availability and use Table Partitioning to switch the archive partitioned data to another table.  Has anyone any experience of using table partitioning in this way with AlwaysOn?  Does it present any issues with AlwaysOn traffic to the Secondary when switching?  Has anyone any other recommendations other than Table Partitioning?

Thanks!

Suppose you set up AlwaysOn but something goes wrong with your partition switching process and you accidentally delete all of the data. With your current proposal you'll automatically delete all of the data from your archive database as well. So even with AlwaysOn you'll still need to take backups, right?

Now consider the state of your current database immediately after you perform the partition switching. That's what you want on the archive database, right? So if you still need to perform backups anyway why not skip AlwaysOn and just back up and restore that database immediately after the switching?

You might have other reasons for using AlwaysOn so I can cover a few pointers but I'm not really an expert. At a high level, SQL Server keeps the databases in sync by replaying the transaction log so the secondary database is a byte-for byte-copy. A decent approximation of how an operation will affect redo traffic can be found by looking at how much data is logged to the transaction log. Partition switching doesn't require data to be moved so very little information should be logged. This means that you should not expect it to cause issues with AlwaysOn traffic.

One problem that you might run into is that you can't pick and choose which tables or filegroups get synced. It must be the entire database. So you'll be sending the transaction log activity for the "large amount of inserts per day" and anything else that you do through redo to the secondary. Your active database will also continue to grow in size over time switch you can't partition switch across databases as Sean Gallardy pointed out. So right now if your active database is 100 GB and your archive database is 1 TB you'll need to move all of that data from the archive database to the active database if you want to put them in an availability group.


MongoDB Not Using All Available RAM I have something around 200 GB worth data stored in a mongo cluster. The physical memory on one of the instances which runs mongo is 8GB. Nothing else of any consequence runs on this instance. As near as I can understand based on Mongo's docs (like this one: http://www.mongodb.org/display/DOCS/Checking+Server+Memory+Usage) this means that the mongod process should be using about 100% of the available physical memory. But if you look at the following output from the top command you'll see that the mongod instance is only using 2GB of resident memory and theres a full 2GB of free physical memory available which isn't being used at all.

Can someone explain this behavior to me? Why is there 2GB of free memory?

top output:

top - 23:19:43 up 89 days, 20:05,  2 users,  load average: 0.41, 0.55, 0.59
Tasks: 101 total,   1 running, 100 sleeping,   0 stopped,   0 zombie
Cpu(s):  2.0%us,  1.3%sy,  0.0%ni, 93.9%id,  2.6%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:   8163664k total,  6131764k used,  2031900k free,    54976k buffers
Swap: 16771848k total,    10604k used, 16761244k free,  5367700k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 1401 mongodb   20   0  174g 2.0g 1.9g S   23 26.2  18070:55 mongod
 ...

System Info:

$ uname -a
Linux aluminum 2.6.32-31-server #61-Ubuntu SMP Fri Apr 8 19:44:42 UTC 2011 x86_64 GNU/Linux

Notes:

There is another instance in this cluster where mongod is behaving as I would expect and utilizing all available memory.
Looking at mongostat it looks like were consistently having some page-faulting, so the amount of memory used should be growing:
(I asked this same question on the mongodb-user google group but got no response.)

The resident memory size represents the number of pages in memory actually touched by the mongod process.  If that is significantly lower than the available memory and data exceeds the available memory (yours does), then it could be a case of simply not having actively touched enough pages yet.

To determine if this is the case, you should run free -m, the output should look something like this:

free -m
             total       used       free     shared    buffers     cached
Mem:          3709       3484        224          0         84       2412
-/+ buffers/cache:        987       2721
Swap:         3836        156       3680

In my example, cached is not close to the total, which means that not only has mongod not touched enough pages, the filesystem cache has not yet even been filled by pages being read from the disk in general.

A quick remedy for this would be the touch command (added in 2.2) - it should be used with caution on large data sets as it will attempt to load everything into RAM even if the data is far too large to fit (causing a lot of disk IO and page faults).  It will certainly fill up the memory effectively though :)

If your cached value is close to the total available, then your issue is that a large number of pages being read into memory from disk are not relevant to (and hence not touched by) the mongod process.  The usual candidate for this kind of discrepency is readahead.  I've already covered that particular topic elsewhere in detail, so I'll just link those two answers for future reading if necessary.


Is it possible to reduce the duplication in this simple query? I'm new to SQL and so this is probably a stupid question but can I reduce the duplication in this query? My actual query involves more complex expressions. Right now I'm just doing string concatenation from the language I'm using to generate the long query but I think there must be a way to do this with plain SQL?
select
  x + y as a,
  x + z as b,
  (x + y) / (x + z) as c
from
  integers
order by
  z + (x + y) / (x + z) desc
limit
  10;

What I'd like:
select
  x + y as a,
  x + z as b,
  a / b as c
from
  integers
order by
  z + c desc
limit
  10;

-- Fails with: Query Error: error: column "a" does not exist

Sample table:
create table integers as
select x, y, z
from
  generate_series(1, 10) x,
  generate_series(x, 10) y,
  generate_series(y, 10) z;

Fiddle: https://www.db-fiddle.com/f/u3cVh7QsafWNE6ELWhvBc1/0

Two levels of derived tables (subquery or CTE) to avoid all repeated calculations:
SELECT a, b, c
FROM  (
   SELECT z, a, b, a / b AS c
   FROM (
      SELECT z
           , x + y AS a
           , x + z AS b
      FROM   integers
      ) i
   ) i
ORDER  BY  z + c DESC
LIMIT  10;

With simple expressions, it won't matter for performance.
What might matter with repeating more expensive expressions is that all involved functions have (correct!) volatility STABLE or IMMUTABLE. See:

Can declaring function volatility IMMUTABLE harm performance?


ORA-01461 in a msaccess linked table In an Oracle 11 database I have a table that all fields are varchar2. This table is linked in an access database, and when I try to do an insert from a data aggregate query, it throws the error ‘can bind a LONG value only for insert into a LONG column’. If I try the same insert in sqlplus it works perfectly. If I try copy the row (Ctrl+C and Ctrl+V) without the field I think is incorrect, it inserts correctly, and then, if I type the same string trying to insert, don’t throw the error. Reading other posts, it seems it’s an encoding error, but I don’t know where can change it.

The problem was that all oracle linked tables in msaccess database, have the link description of an odbc of the previous database version (DBA=W;APA=T;EXC=F;FEN=T;QTO=F;). One of this parameters (Enable LOBs) was diferent and causes the error.
To solve the problem have to relink all the tables of de msaccess database. It's important to relink all, because masccess make the connection to oracle for all the linked table in the first access of any table.


How can I write to replicated environments without impacting production? Are there any methods (mirroring, snapshot replication, change tracking, etc...) available in SQL Server 2k8 which would allow me to replicate production data to several individual development databases AND allow separate data to be written to each of those dev db?

In other words: Once a full copy of data is replicated to a dev database, replication would be turned off so new data can then be written to the dev database without impacting the production database.

Thanks in advance

Wouldn't simple backup/restore be enough? Just restore the production backup on all your dev servers and you should be fine. Btw. Sql server does not allow writes to replica servers as it would corrupt the data and it would no longer be able to figure out what should be updated.


What is wrong with this statement? 'Unknown column'? I'm not used to the 'as' keyword and I thought it would be a nice idea to use it.
So why do I get an 'unknown column' (eid) error? I use mysql...

select t1.index as eid, t1.col1 as col1, t1.col2 as col2,
t2.index, t2.col1, t2.col2,
t3.index, t3.col1, t3.col2
from table1 t1
left join table2 t2 on t2.index=eid
left join table3 t3 on t3.index=eid;

The full statement is too long to present here without boring everybody.
The column and table names are random.

A relational sub-select is logically processed in the following sequence:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause
LIMIT...OFFSET clause or the equivalent

Subsequently, the column labels defined in the SELECT clause are not yet known when the FROM clause, containing the joins in your example, is dealt with.


Create a view showing total minutes per customer per month and change from previous month I have an existing database (SQL Server 2012, on premise installation) and need to add a column to a view which gets displayed via a Crystal Report.

The issue I am having is with restricting the scope of the minute sum to a single month and a single customer. Currently I am either having an error due to including a column in the select clause but not the group by clause or an aggregate function, or else the query will return the total minutes per customer as 'MinuteTotal' across all months in the referenced table.

My question is, how can I restrict this query in order to get the total minutes per CustomerName and per Month, so that I can compute the difference from month to month?

USE Masscomm_XT;

WITH C0 AS (
SELECT CustomerName ,Minutes. ,year(Postingdate) AS PostingYear ,month(postingdate) AS PostingMonth ,PostingDate
FROM dbo.reptview_LDUsageTrend
),

C1 AS (
SELECT CustomerName ,SUM(minutes)OVER(PARTITION BY CustomerName ,PostingMonth ,PostingYear) AS MinuteTotal
FROM C0
)

SELECT C0.CustomerName ,C1.MinuteTotal ,C0.PostingMonth ,C0.PostingYear
FROM C1
JOIN C0 ON C0.CustomerName = C1.CustomerName
;

Seems like you're right on the tip of the answer. Just group by the customer, the year, and then the month.

USE Masscomm_XT;

WITH C0 AS (
  SELECT CustomerName ,Minutes. ,year(Postingdate) AS PostingYear ,month(postingdate) AS PostingMonth ,PostingDate
  FROM dbo.reptview_LDUsageTrend
)

SELECT C0.CustomerName ,C0.PostingMonth ,C0.PostingYear, SUM (Minutes) AS MinuteTotal
FROM C0
GROUP BY C0.CustomerName, C0.PostingYear, C0.PostingMonth
;


Find rows containing all keywords within a string across multiple columns I'm trying to create a universal search functionality. The goal is to compare the values of multiple columns and see if all of the provided keywords appear in those columns. For various reasons, using a Full Text Index and CONTAINS is not an option, nor is utilizing a CLR function. Are there any other options to solve this problem?

Example:

----------------------------------------------------
|    Row    |    Col1    |    Col2    |    Col3    |
----------------------------------------------------
|     1     |    Foo     |    Bar     |    Stack   |
----------------------------------------------------
|     2     |    Foo     |    Blitz   |    Stack   |
----------------------------------------------------
|     3     |    Foo     |     Foo    |     Bar    |
----------------------------------------------------

Search text "Foo Bar" matches Rows 1 and 3.

Search text "Foo Stack" matches Rows 1, and 2.

Search Text "Foo" matches Rows 1, 2 and 3.

Search text "Foo Bar Stack Blitz" matches no rows.

Search text "Foo Blitz" matches Row 2.

You can use a string splitter and do this. I use Jeff Moden's splitter but you can use your favorite. This essentially splits the string on the space, and then does a FULL JOIN and only returns rows where there is a mach for each word. This is handy since it doesn't care the order of the words in the string. i.e. Foo Bar is the same as Bar Foo.

SQL FIDDLE

declare @table table ([Row] int identity (1,1), Col1 char(3), Col2 varchar(6), Col3 varchar(6))
insert into @table
values

('Foo','Bar','Stack'),
('Foo','Blitz','Stack'),
('Foo','Foo','Bar')

declare @search varchar(64)

set @search = 'Foo Bar'

select distinct
    t.*
from @table t
full join
    dbo.DelimitedSplit8K(@search,' ') s on
    s.Item = t.Col1
    or s.Item = t.Col2
    or s.Item = t.Col3
where
    t.Row is not null
group by
    t.Row
    ,t.Col1
    ,t.Col2
    ,t.Col3
having
    count(*) = (select max(ItemNumber) from dbo.DelimitedSplit8K(@search,' '))


how to upload a file on a mysql server if access to temp folder is not present I have a CSV file that i want to enter into a table in my database which is present on a remote server. But presently I don't have access to temp folder so I cannot copy my file on that folder. Is there a way I can upload file from any random folder onto the database?

This is my command in sql server:

load data local  infile 'C:/Users/trainee_aiyer/Downloads/cash_ord.csv' into table cm_ord fields terminated by ',' enclosed by '"' lines terminated by '\n';

and my file path is this:

C:\Users\trainee_aiyer\Downloads\cash_ord.csv

I switched '\' to '/' as the prior was causing syntax errors. but I get an error saying

ERROR 2 (HY000): File 'C:/Users/trainee_aiyer/cash_ord.csv' not found (Errcode: 2 "No such file or directory")

From MySQL documentation, it seems it is not possible, since it is operating systems temporary directory and not it is not configured in MySQL.

  When using LOCAL with LOAD DATA, a copy of the file is created in the
  server's temporary directory. This is not the directory determined by
  the value of tmpdir or slave_load_tmpdir, but rather the operating
  system's temporary directory, and is not configurable in the MySQL
  Server. (Typically the system temporary directory is /tmp on Linux
  systems and C:\WINDOWS\TEMP on Windows.) Lack of sufficient space for
  the copy in this directory can cause the LOAD DATA LOCAL statement to
  fail.

Alternative, you may try with mysqlimport with local parameter, but make sure, you have access to that directory, else you may encounter an error. I hope this answer will help you. Thank you.


How to use REPLACE in a dynamic way How can I make the REPLACE function dynamic, by dynamic I mean that if for example one entry has 3 quotes and an other on has 5, I want it to cater for all situations

BEGIN TRAN
       update contacts
       set address1 = REPLACE(address1,'''','')
       where address1 like '%''''%'

ROLLBACK

If you are willing to just handle cases where the number of single quotes is 15 or less, you can use this pattern:

update contacts
    set address1 =    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(address1,'''''''''''''''''',''),  -- Replace any instance of eight quotes
                                '''''''''',''),                           -- Replace any instance of four quotes
                            '''''',''),                                   -- Replace any instance of two quotes
                        '''','')                                          -- Replace any single quote
where address1 like '%''''%'

If someone has entered more than 15 quotes into one field in sequence, you probably need to verify if they accidentally died while holding down the key.


Airline reservation system I'm making a simple airline reservation system. But I'm not sure about the design decisions I made. Concretely, I'm trying to implement following constraints:

each Aircraft can have one or more TravelClasses
each Aircraft has many Seats.
each Flight has only one AircraftInstance (of Aircraft)
prices for Flight differ for TravelClasses that are known through AircraftInstance assigned to Flight
Client must know which seats are available for Flight booking and a price of the seat

What I have for now:

Please, leave suggestions. Thank you!

Two general principles I would commend to you.  One - name your relationships so you understand why one entity type connects to another.  You may find there are more than one relationships between any two, or that some you have imagined has do not represent anything concrete in the user's world.  Two - imagine some of the uses cases for this system.  Think about the queries you would need to write to fulfill each.  Walk through your data model to ensure it supports your queries and constraints.

The data model posted encodes many, many more rules than those you have listed.  It would be impossible for a third party to validate the model in its entirety without all the rules.  This is a large task which would typically take some days in a room with user representation, in my experience.


IDERA suggests to alter procedures in tempdb Idera analysis is suggesting I:

USE [tempdb]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

GO
ALTER PROC ##zDP_235302...

Is this really a good idea? Most of the "fixes" their diagnostic software suggests is spot on, but this kind of makes me take a step back. There is a long list of these procedures in tempdb they suggest altering.

There's no harm in doing it, but it'll just go away next time the service is restarted. If it's something that really needs to be done, you'd have to alter the code that is creating the procedure, wherever that lives. It could be they are in the model database.


Normalizing data with multiple products to a single ID? How can I normalize this data to remove the redundant tuples in the salesorderline database? I already normalized the first one, but cant seem to figure out the second. Also, are my databases already in third normal form? I believe they are, however, would like extra confirmation from most knowledgeable people.

There is no redundancy. You have the FK back to the sales order, how many items were ordered, the per item charge and the total for the line. You are, however, missing something rather important. What item was ordered? The top line show an order of 10 somethings and the next line shows an order for 5 something elses.

Another feature you may want to add is a way to uniquely identify each order detail. There first three items are details for order number 1111. Beyond that, there is no way to differentiate one line from another. That is to say, there in no natural key.

Such detail tables create a differentiator in several ways. One way is to add an incremental value on each line. This can also be done in a couple of ways.

Give the first line inserted for each order a value of one and increment each additional line by one. The disadvantage of this is, when another line must be added later, the highest value so far must be read in to see where to restart incrementing.
Implement a global incremental value. This has the advantage it can usually be automatically generated by the system as a new record is inserted. Of course, line 1 of order 1111 may be given the value 1, line 1 of the order 1112 may be given the value 2, line 2 of order 1111 the value 3, line 1 of order 1113 the value 4 and so on. Essentially, the line value would be a semi-random value. This is fine as these serve as differentiators only. They are only required to be distinct, not grouped in any logical manner. When using this method, use the composite (SO_ID, Increment) as the key even though Increment will be unique on its own. The indexing will improve queries.

Another way is to use the missing field, the item part number or SKU or whatever. The advantage over a generated value is

an additional increment field need not be created.
you can't accidentally enter the same part number more than once. The disadvantage is you can't deliberately enter the same part number more than once -- or instance, if the customer later orders more of the same item. You would have to update the existing line for that item with the new quantity. However, this may be exactly the behavior you want.


What program to use for the best compression ratio for DB2 log files? What linux program to use to compress DB2 log files with the best compression ratio?

UPDATE

I thought that these log files would be text type files. But viewing them didn't prove this. I tried to use gzip and zip with up to 88% compression ratio. Which is pretty good.

Still my question is valid. Is there any 'recommended' program to compress DB2 log files?

UPDATE2
- results of my own comparision using maximum/best compression

 5.1M  7zip.mx9.7zip
 7.0M  tarbzip2.bzip2
 8.8M  targzip.tgz
 8.8M  zip.zip

when compressing two files

41M S0045979.LOG
29M S0045980.LOG

In version 10.1, the logs are internally compressed, that means that more information is stored in those files.

But if you want to compress them in order to use less space in your disks, I do not see this like a very good practice, because logs are the only part where the latest data is stored in, and if you want to do a restore, you will have to use the log files, to restore the database with an extra process, and probably, to perform a restore you will need even more space than before (the compressed file + the expanded file).

Presonally, this compress operation adds "noise" to the disaster recovery strategy, how are you going to check if a compressed file is ok or not?

Finally, if you want to do a thing like this, you could use the userexit option in order to implement your own process to take the online logs and pass them as offline logs. In this process you could compress and extract the files.

BTW, why don't you use circular logs? Any enterprise knows the importance of archive logs.


How are auto_increment keys handled in INSERT (SELECT * FROM...) I have table1 and table2 in MySQL. Both have a primary auto_increment key id.

If the table schemas match and I do INSERT INTO table1 (SELECT * FROM table2) what happens with regards to the new rows inserted in to table1? Do they keep their old id values and generate conflicts when a row from table1 has the same id? Are new values generated by auto_increment? Does it depend on the storage engine or locking?

You can insert into an auto-increment column and specify a value. This is fine; it simply overrides the auto-increment generator.

If you try to insert a value of NULL or 0 or DEFAULT, or if you omit the auto-increment column from the columns in your INSERT statement, this activates the auto-increment generator.

So, it's fine to INSERT INTO table1 SELECT * FROM table2 (by the way, you don't need the parentheses). This means that the id values in table2 will be copied verbatim, and table1 will not generate new values.

If you want table1 to generate new values, you can't do SELECT *. Either you use null or 0 for the id column:

INSERT INTO table1 SELECT 0, col1, col2, col3, ... FROM table2;

Or else you omit the column from both the INSERT statement's column list and the SELECT statement's select-list:

-- No id in either case:
INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3, ... FROM table2;

Before you ask, there is no syntax in SQL for "select * except for one column". You have to spell out the full list of column names you want to insert.


How to get rid of "maximum user connections" error ? I am using MySQLi for my webapp but whenever I want to visit some particular page, I get mysqli_connect() [function.mysqli-connect]: (42000/1203): User ***_user already has more than 'max_user_connections' active connections.

I tried already to close all connections but this does not improve the situation.

Is there a way to know exactly what connections are open at any particular moment or any other useful data that can help me resolve this issue ?

BTW, I'm using PHP 5.2.17 and MySQL 5.1.

The option max_user_connections is a limit imposed, not on the total number of simultaneous connections in the server instance, but on the individual user account.
Let's say the user is called db_user@localhost. You can find out what this user's connection limit is by running this query:
SELECT max_user_connections FROM mysql.user
WHERE user='db_user' AND host='localhost';

If this is a nonzero value, change it back with:
GRANT USAGE ON *.* TO db_user@localhost WITH MAX_USER_CONNECTIONS 0;

or
UPDATE mysql.user SET max_user_connections = 0
WHERE user='db_user' AND host='localhost';
FLUSH PRIVILEGES;

This will cause mysqld to allow the user db_user@localhost to use the global setting max_user_connections as its limit.
Once you get to this point, now check the global setting using
SHOW VARIABLES LIKE 'max_user_connections';

If this is a nonzero value, you need to do two things
THING #1 : Look for the setting in /etc/my.cnf
[mysqld]
max_user_connections = <some number>

comment that line out
THING #2 : Set the value dynamically
SET GLOBAL max_user_connections = 0;

MySQL restart is not required.
CAVEAT
I have discussed this setting in the past

May 24, 2011 : What are user connections - when are the created and destroyed?
Jun 16, 2011 : Would it be sensible to create a MySQL user for each user account of a web application?
Mar 22, 2012 : How can I limit MySQL connections?
Feb 22, 2013 : Mysql : Maximum number of connections on a per user basis - Possible?

Give it a Try !!!


modeling advice for table which may have 2 father tables I am developing a ERP for a small cooperative with special needs (that'y why they need a non-general solution).

I'd like to model the following:
A "receipt" can either be incoming or outgoing
For that I'd like to have three tables:
collection | payment | receipt
So a receipt can belong to a Collection or a Payment

I'd like to model the Receipt table
with id, collection_id (fk), payment_id (fk)
so there would be a 0,1:n relation from both father tables to the receipt table.

Does that make sense from a DB development view?
Or is there another recommended way to model multiple inheritance?

The same question applies to a a document (image, PDF, etc.) - any uploadable resource:
A document can either belong to an activity, a receipt, a project, etc.

I would Simply add a receipt_id to the collection and payment table.

This is a better design (objectively normalised) that allows your model to grow should your requirements change

This would solve the problem for your document model also - activity, reciept, project would contain a document_id, and you could expand your model to include any number of tables containing a document_id.

If you need to differentiate between documents stored in the table itself, you could add a document_type to make that distinction.


Database snapshot "The requested operation could not be completed due to a file system limitation" Preface

At the company I'm working for we use database snapshots in our ETL process to ensure the data is in a consistent state. The snapshot files are placed on a separate disk (in our case the X:).

One of the source databases is a 3TB database with data updated frequently. When creating a database snapshot of this database we've encountered moments when the snapshot files increased and we've got the error:

  "Operating system error 112(There is not enough space on the disk.)
  encountered."

That error was always in the index maintenance windows, or when a bulk process ran in the source database. Since then we've increased the available disk space on the X:\ drive and changed the schedule of index maintenance/dbcc checkdb jobs. As expected, the errors didn't occur anymore.

Problem

From time to time we need an initial load of some large tables (600.000.000 rows / 300GB per table). This week we've encountered a different error in the load of these large tables:

  The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x000056fb976000 in file 'X:\Data\DatabaseName_snapshot.ss'.

  Additional messages in the SQL Server error log and system event log may provide more detail.

This error occurs only 18 - 25 hours after the snapshot is created.

Things already tried

Disabled all index maintenance/dbcc checkdb jobs and other processes changing a lot of data (except user input).
Monitored the available space on the snapshot disk (X:\ 400GB). About 80% of the available space is free when the error occurs.
Looked for simmilar questions on stackexchange. Found DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2) which was not answered.
Looking into article https://blogs.msdn.microsoft.com/psssql/2015/06/10/operating-system-error-665-file-system-limitation-not-just-for-dbcc-anymore/ right now.

Does anyone have more ideas to tackle this problem?

We are using SQL Server 2014 SP2 CU1, enterprise edition and Windows Server 2012.

The issue is around NTFS allocation maps and the amount of space they have for file allocations. In general, the more fragmented the file the more allocation mappings there needs to be.

By default when a volume is formatted for NTFS, small allocation map size is chosen (1024 bytes) whereas with /L the large allocation map size is chosen (4096 bytes).

Normally this doesn't come into play or show itself and any appreciable way... However once volumes get larger and larger or use technologies that create many small random allocations (such as database snapshots or checkdb) then it can rear its' ugly head.

To get around this, a few different options can be chosen:

Format the volume ntfs with /L:Enable
Make smaller volumes so that this doesn't happen
Use ReFS if on SQL 2014+

In most cases, choosing to format with /L:Enable will be the easiest and fastest option.


Select date ranges with a 50% overlap to another date range I have a series of date ranges with columns e.g.
start_date, end_date, period_name

   1         100     Roman
 -50          75     Iron Age b
-100          20     Iron Age a

My team have asked me to build 2 queries:

Select the Roman period and any which overlaps
same as 1 but only those ranges which overlap >40%

I'm assuming I would have to write a function in postgresql (PL/pgSQL), any help is very welcome.

This can be expressed using Postgres' range types quite nicely

First query: find all ages that overlap with the Roman age:

select *
from ages a1
where period_name <> 'Roman'
  and exists (select *
              from ages a2
              where period_name = 'Roman'
                and int4range(a1.start_date, a1.end_date) && int4range(a2.start_date, a2.end_date))

For the second one it's necessary to calculate the length of the overlap, this can be done using the intersection operator for ranges: *, e.g. int4range(0,100,'[]') * int4range(-50,75,'[]') yields 76. This result has to be compared to the total length of the Roman age to get the percentage.

As this requires access to both ages (the roman and the overlapping) at the same time, this is better done using a join.

To make the final query easier to read, I use common table expression to turn the two columns into a proper range, then I use that to make the join.

Once I have that. the overlapping percentage is easy to calculate:

with ranges as (
   select period_name,
          int4range(start_date, end_date, '[]') as period,
          upper(int4range(start_date, end_date, '[]')) - lower(int4range(start_date, end_date, '[]')) as years
   from ages
), overlapping as (
  select o.period_name as overlapping_period_name,
         r.period as roman_period,
         o.period as overlapping_period,
         r.period * o.period as overlap_length,
         r.years as roman_years,
         o.years as other_yers
  from ranges r
    join ranges o on r.period && o.period and o.period_name <> 'Roman'
  where r.period_name = 'Roman'
)
select overlapping_period_name,
       overlapping_period,
       (upper(overlap_length) - lower(overlap_length))::numeric / roman_years as overlap_percent
from overlapping;

The cast ::numeric is needed because otherwise it would be an integer division and the result would be 0 not 0.75

To display only those with a minimum of 40% overlap just add a where condition:

....
select *
from overlapping
where (upper(overlap_length) - lower(overlap_length))::numeric / roman_years >= 0.4

Online example: http://rextester.com/JIOAV33074

If you need to do a lot of calculations on the "periods", it might be a good idea to store them as a range directly, that saves a lot of converting back and forth when doing calculations.


Fixed length text file insert into SQL table I am not a DBA on this one and I will not have access to BCP or anything like that.  So I'm wondering if there is still a way to do it in SQL keeping it very basic.

I have 100's of text files that contain 1000's or lines of data.  Each line has a fixed length data format.

For example:

001     abc     test file ***
row 1     Test Data is here!       001
footerId 123 Footer for 001

Then I have a file format of

recordId starts at 1 length 8
recordName starts at 9 length 8
headerDesc starts at 17 length 10
rownumber starts starts at 27 length 3
rowNumber starts at 1 length 10
rowData starts at 11 length 25
rowId starts at 36 length 3
footerId starts at 1 length 12
footerref starts at 13 length 14

I hope that makes sense...  Obviously that is not real data.  Basically I need to load these files into a table (or many tables).  What would be the most basic way to do this in SQL?

I was playing around with the OPENROWSET command but that jammed everything into 1 row.  If there was a way to insert a temp table with each line of data I could maybe parse that out into more permanent tables.    But it would be great if there was a way to insert the data into an auto generated table.

Is there anyway to do this?

As mentioned in the comments above, your time would be much better spent using SSIS for this task. Here's a recent video post that discusses handling multi-line record data files much like yours.

Since you're okay with spinning your wheels, let's try to find an answer anyway--if you have the data in a single row, you have somewhere to start. My next step would be to apply a split function. Here is an excellent article about the popular options for such a function. You will need to choose or adapt a function to handle (n)varchar(max) if your files exceed the standard limits. You'll just use your single row of data as the input parameter, and the carriage return as the delimiter.

Once your data is broken into rows, you should be able to liberally apply substring() to slice out your columns for each row type.

This probably won't perform well, but if you need to show a proof of concept to get access to SSIS, it might suffice.


Need help with a Running Total Issue Using SQL-Server 2008 R2 and Report Builder 3.0

Here is my query without the running total column:

SELECT
   CostManagement_CommitmentDetails.Id
  ,CostManagement_CommitmentDetails.LineNumber AS LineNumber
  ,CostManagement_CommitmentDetails.Description
  ,CostManagement_CommitmentDetails.CostCodeId
  ,CostManagement_CommitmentDetails.TotalCost
  ,CostManagement_CommitmentDetails.PeriodId
  ,Periods.Id AS [Periods Id]
  ,Periods.LineNumber AS [Periods LineNumber]
  ,Periods.Period
  ,Periods.FromDate
  ,Periods.ToDate
  ,Projects.ProjectName
  ,[CostManagement_Commitments].ProjectId

,'COM' as RecordType

,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.[CostCodeId], 1) AS Group1
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 1) AS Group1Description
    ,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.CostCodeId, 2) AS Group2
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 2) AS Group2Description
    ,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.CostCodeId, 3) AS Group3
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 3) AS Group3Description
    ,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.CostCodeId, 4) AS Group4
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 4) AS Group4Description
    ,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.CostCodeId, 5) AS Group5
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 5) AS Group5Description

FROM
  CostManagement_CommitmentDetails
  LEFT OUTER JOIN Periods
    ON CostManagement_CommitmentDetails.PeriodId = Periods.Id
LEFT OUTER JOIN [CostCodes]
    ON [CostManagement_CommitmentDetails].CostCodeId = [CostCodes].Id
LEFT OUTER JOIN [CostManagement_Commitments]
    ON CostManagement_Commitments.Id = [CostManagement_CommitmentDetails].CommitmentId
LEFT OUTER JOIN [Projects]
    ON [Projects].Id = [CostManagement_Commitments].ProjectId

Where (Period <= @ToPeriod) and [CostManagement_Commitments].ProjectId IN (@Projectlisting)AND 'COM' IN (@RecordTypes)

Running totals work fine if my dataset is from the start date to end date for both the Graph and Matrix.  I run into trouble when I want to show just a subset of all the dates say a 3 month window.  The Matrix works fine in keeping the running total values  from the start of the project even though those months are not displayed in the Matrix, but the Graph sets the Running totals (cumulative) to zero which displays incorrect totals.  The Matrix has all the correct totals, the Graph does not.  Category in the Graph does not have a visibility option.  Looking for a solution either Graph wise or by adding a running cumulative total column that I can then graph because of the current graph limitations for Category.

If you look at the last Graph is has 2.4 million for the cumulative total for the yellow line but it should be 5.5 million for period 07-1403

Note:

We are restricted from creating any Stored Procedures.
The Bars in the Graph are Period/Month totals only.
The Line in the Graph is the cumulative running total.

All help is greatly appreciated.

You can calculate a running total in SQL 2008 R2 by using the ROW_NUMBER() window function along with a correlated sub-query to calculate the running total column.

Here is an example using some data I have available. You would need to amend the query accordingly to suit your tables/data and would likely need to use a PARTITION BY within the ROW_NUMBER function so you get a running total for the different projects/record types.

If you are able to post some sample data I can provide you with an example using your data structures.

WITH ContactMonthOrdered AS
(
    SELECT
            ContactMonthStartDate
        ,   COUNT(*) AS Total
        ,   ROW_NUMBER() OVER(ORDER BY ContactMonthStartDate) AS MonthOrder

    FROM
        Clinical.ContactDetail

    WHERE
        ContactFiscalYearLongName = '2014/2015'

    GROUP BY
        ContactMonthStartDate
)

SELECT
        ContactMonthStartDate
    ,   Total
    ,   MonthOrder
    ,   (
            SELECT  SUM(Total)
            FROM    ContactMonthOrdered
            WHERE   MonthOrder <= c.MonthOrder
        ) AS RunningTotal

FROM
    ContactMonthOrdered AS c

ORDER BY
    c.MonthOrder

UPDATED
The query below is derived from the example query you posted in your question. Provided I understand your schema correctly, this will give you the total for each period for a project and also a running total across the periods for a project. The assumption is that the sequence of PeriodId is meaningful.

;WITH CommitmentPeriodCost AS
(
    SELECT
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
        , SUM(CommitmentDetails.TotalCost) AS TotalCost

    FROM
        CostManagement_Commitments AS Commitments
        LEFT JOIN CostManagement_CommitmentDetails AS CommitmentDetails
            ON  Commitments.Id = CommitmentDetails.CommitmentId

    WHERE
        Commitments.ProjectId = 1

    GROUP BY
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
)

SELECT
    ProjectId
    , PeriodId
    , TotalCost
    , (
            SELECT  SUM(TotalCost)
            FROM    CommitmentPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentPeriodCost.ProjectId
                    AND PeriodId <= CommitmentPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    CommitmentPeriodCost

ORDER BY
    ProjectId
    , PeriodId


What is the data type 'name' in PostgreSQL I can not find any references to the data type 'name' in the postgres documentation but I am seeing it as a data type on the pgagent.pga_jobstep  table for the column 'jstdbname'. The udt_name is also 'name'. Selecting the rows from that table makes them appear as if they are strings.

It is not listed here: Postgres data types

Name is a 63 byte (varchar) type used for storing system identifiers.

Using psql you can get type information using \dT or \dT+

So for the name type:

# \dT name

                           List of data types
   Schema   | Name |                 Description
------------+------+---------------------------------------------
 pg_catalog | name | 63-byte type for storing system identifiers
(1 row)

or

# \dT+ name
                                                    List of data types
   Schema   | Name | Internal name | Size | Elements |  Owner   | Access privileges |                 Description
------------+------+---------------+------+----------+----------+-------------------+---------------------------------------------
 pg_catalog | name | name          | 64   |          | postgres |                   | 63-byte type for storing system identifiers
(1 row)


How to optimize the my.cnf of a MySQL server that mixes InnoDB and MyISAM tablespaces? I have a database that, because of some issues, I needed to convert some tables from MyISAM to InnoDB.

I basically did this:

set sql_log_bin = 0;

set sql_mode = 'STRICT_ALL_TABLES';

ALTER TABLE  `table1` ENGINE = INNODB;

ALTER TABLE  `table2` ENGINE = INNODB;

ALTER TABLE  `table3` ENGINE = INNODB;

ALTER TABLE  `table4` ENGINE = INNODB;

ALTER TABLE  `table5` ENGINE = INNODB;

ALTER TABLE  `table6` ENGINE = INNODB;

It worked right, but as far as I know now I should reconfigure the my.cnf and then restart the mysql server, correct?

How should I tune the my.cnf file if the server contains both MyISAM and InnoDB tables?

One of the most important variables for InnoDB is innodb_buffer_pool_size. This is the amount of memory allocated to load tablespace information for InnoDB only.

Since you are mixing MyISAM and InnoDB, you will need to find a good balance between key_buffer_size (for MyISAM indexes) and innodb_buffer_pool_size (for InnoDB reads).

In general, you want to fit your entire data into memory if possible, as disk reads are (obviously) very expensive.

You didn't mention which MySQL version you are running, but I would recommend reading through the documentation of the appropriate version for tuning InnoDB for more detailed information.


mysql query matching multiple elements from a single table I have this awkward query I'm trying to figure out. Basically it checks a table for matching / non-matching entries (data1, data2).

My Table is:

CREATE TABLE `test`.`tab1` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `cust` INT UNSIGNED NOT NULL,
  `type` TINYINT NOT NULL, #form
  `data1` INT UNSIGNED NOT NULL, #xml
  `data2` VARCHAR(45) NOT NULL, #custXML
  PRIMARY KEY (`id`));

Test Data:

INSERT INTO `test`.`tab1`
VALUES
(1,100,1,123,'OneTwoThree'),
(2,100,1,456,'FourFiveSix'),
(3,100,1,789,'SevenEightNine'),
(4,200,1,123,'OneTwoThree'),
(5,200,1,456,'FourFiveSix'),
(6,200,1,789,'SevenEightNine');

In the given example each type has two data elements data1 and data2.

I want to get the data elements for cust=100 AND type=1 and compare them to cust=200 AND type=1

So with the above Test Data I would end up with:

Desired Result:

cust | type | data1 | data2          | match | cust | type | data1 | data2
100  | 1    | 123   | OneTwoThree    | match | 200  | 1    | 123   | OneTwoThree
100  | 1    | 456   | FourFiveSix    | match | 200  | 1    | 456   | FourFiveSix
100  | 1    | 789   | SevenEightNine | match | 200  | 1    | 789   | SevenEightNine

This I can do using a self join:

 SELECT
    a.`cust`
   ,a.`type`
   ,IF(a.`data1`=b.`data1`,a.`data1`,CONCAT("<< ",a.`data1`," >>")) AS 'data1'
   ,IF(a.`data2`=b.`data2`,a.`data2`,CONCAT("<< ",a.`data2`," >>")) AS 'data2'
   ,a.`data2`,
   IF(a.`data1`=b.`data1` AND a.`data2`=b.`data2`,'MATCH','NO MATCH') AS Matched
   ,b.`cust`
   ,b.`type`
   ,IF(b.`data1`=a.`data1`,b.`data1`,CONCAT("<< ",b.`data1`," >>")) AS 'data1'
   ,IF(b.`data2`=a.`data2`,b.`data2`,CONCAT("<< ",b.`data2`," >>")) AS 'data2'
FROM `test`.`tab1` a
LEFT JOIN `test`.`tab1` b ON (a.`type`=b.`type`)
WHERE 1=1
AND a.`cust` = 100 AND b.`cust` = 200
AND (a.`data1`=b.`data1` OR a.`data2`=b.`data2`);

If data1 OR data2 changes that's fine, as the above query handles that.

UPDATE `test`.`tab1` SET `data1` = 7890 WHERE id = 6;

cust | type | data1     | data2          | match    | cust | type | data1       | data2
100  | 1    | 123       | OneTwoThree    | match    | 200  | 1    | 123         | OneTwoThree
100  | 1    | 456       | FourFiveSix    | match    | 200  | 1    | 456         | FourFiveSix
100  | 1    | << 789 >> | SevenEightNine | no match | 200  | 1    | << 7890 >>  | SevenEightNineZero

The problem I have is if both data1 and data2 change then my comparison falls apart.
e.g.

UPDATE `test`.`tab1` SET `data1` = 7890, `data2` = 'SevenEightNineZero' WHERE id = 6;

I am trying to get a result similar to:

cust | type | data1     | data2                | match    | cust | type | data1       | data2
100  | 1    | 123       | OneTwoThree          | match    | 200  | 1    | 123         | OneTwoThree
100  | 1    | 456       | FourFiveSix          | match    | 200  | 1    | 456         | FourFiveSix
100  | 1    | << 789 >> | << SevenEightNine >> | no match | NULL | NULL | NULL        | NULL
NULL | NULL | NULL      | NULL                 | no match | 200  | 1    | << 7890 >>  | << SevenEightNineZero >>

I figure I could put it into a MySQL Stored Procedure, and use a cursor to loop through the data and do the comparison.
Or I could simply break it up and put each customers data into temporary tables, and run each comparison as a separate query to build another temp table with the result.

But if there's a way to do it in a single Query it would be far better.

You can't have anything about the inner tables in the WHERE clause for the outer joins to work as expected - move those to JOIN conditions
You would need FULL JOIN which is something that MySQL sadly doesn't support - you can emulate FULL JOIN with two outer joins and UNION

 SELECT
    a.`cust`
   ,a.`type`
   ,IF(a.`data1`=b.`data1`,a.`data1`,CONCAT("<< ",a.`data1`," >>")) AS 'data1'
   ,IF(a.`data2`=b.`data2`,a.`data2`,CONCAT("<< ",a.`data2`," >>")) AS 'data2'
   ,a.`data2`,
   IF(a.`data1`=b.`data1` AND a.`data2`=b.`data2`,'MATCH','NO MATCH') AS Matched
   ,b.`cust`
   ,b.`type`
   ,IF(b.`data1`=a.`data1`,b.`data1`,CONCAT("<< ",b.`data1`," >>")) AS 'data1'
   ,IF(b.`data2`=a.`data2`,b.`data2`,CONCAT("<< ",b.`data2`," >>")) AS 'data2'
FROM `test`.`tab1` a
LEFT OUTER JOIN `test`.`tab1` b ON (a.`type`=b.`type`) AND (a.`data1`=b.`data1` OR a.`data2`=b.`data2`) AND b.`cust` = 200
WHERE 1=1 AND a.`cust` = 100
UNION
 SELECT
    a.`cust`
   ,a.`type`
   ,IF(a.`data1`=b.`data1`,a.`data1`,CONCAT("<< ",a.`data1`," >>")) AS 'data1'
   ,IF(a.`data2`=b.`data2`,a.`data2`,CONCAT("<< ",a.`data2`," >>")) AS 'data2'
   ,a.`data2`,
   IF(a.`data1`=b.`data1` AND a.`data2`=b.`data2`,'MATCH','NO MATCH') AS Matched
   ,b.`cust`
   ,b.`type`
   ,IF(b.`data1`=a.`data1`,b.`data1`,CONCAT("<< ",b.`data1`," >>")) AS 'data1'
   ,IF(b.`data2`=a.`data2`,b.`data2`,CONCAT("<< ",b.`data2`," >>")) AS 'data2'
FROM `test`.`tab1` a
RIGHT OUTER JOIN `test`.`tab1` b ON (a.`type`=b.`type`) AND (a.`data1`=b.`data1` OR a.`data2`=b.`data2`) AND a.`cust` = 100
WHERE 1=1
AND b.`cust` = 200;


MySQL running in safe mode but not in normal mode. Can't find file: './mysql/plugin.frm' (errno: 13) My server accidentally got hard rebooted yesterday and after that the MySQL service isn't starting.

On running sudo service mysql status

the response was stuck at mysql stop/waiting

On running mysqld --verbose this is the response I got

200305 12:47:59 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.

200305 12:47:59 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.

200305 12:47:59 [Note] mysqld (mysqld 5.5.47-0ubuntu0.14.04.1-log) starting as process 2536 ...

200305 12:47:59 [Warning] Can't create test file /var/lib/mysql/ip-172-31-12-215.lower-test

200305 12:47:59 [Warning] Can't create test file /var/lib/mysql/ip-172-31-12-215.lower-test

200305 12:47:59 [Warning] One can only use the --user switch if running as root

200305 12:47:59 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.

200305 12:47:59 [Note] Plugin 'FEDERATED' is disabled.

mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)

200305 12:47:59 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

200305 12:47:59 InnoDB: The InnoDB memory heap is disabled

200305 12:47:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins

200305 12:47:59 InnoDB: Compressed tables use zlib 1.2.8

200305 12:47:59 InnoDB: Using Linux native AIO

200305 12:47:59 InnoDB: Initializing buffer pool, size = 1.0G

200305 12:47:59 InnoDB: Completed initialization of buffer pool

200305 12:47:59  InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to

InnoDB: the directory.

InnoDB: File name ./ibdata1

InnoDB: File operation call: 'open'.

InnoDB: Cannot continue operation.

Currently I can run mysql in safe mode but I'm not sure if there are any issue's in doing that and since this is in production I can't play around with different solutions which would lead to a longer downtime.

TIA

Welcome.

Please do not use mysql in safe mode.
As it crashed at one point and after which it did not start, it is better to look at log files of that time rather than --verbose.
Most probably (since you report a missing file too), you could try re-installing.

But try with Item 2. If you do not understand please post the log (of that time) here to discuss further.