texts:
I'm trying to move data from one table to another, but having an error:

mysql> INSERT INTO activities_article (category_id, created_at, title, promo, content, promo_image_url, coordinates, vk_id, updated_at, hot)
SELECT category_id, created_at, title, promo, content, promo_image_url, coordinates, vk_id, NOW(), False
FROM dorogirussia.activities_article WHERE category_id=4;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dorogi`.`activities_article`, CONSTRAINT `activities_articl_category_id_632c9272_fk_activities_category_id` FOREIGN KEY (`category_id`) REFERENCES `activities_category` (`id`))

Is there any way to fix that?


texts: shoud we normalize user profile table with fields like interests,musics,videos,sports, etc?

texts: I've read in an article about social networking database schema and saw that they've used varchar for those columns that I mentioned in the question title. It's not normalized! Isn't it better to have a table for sports and then put the foreign keys in user's profile table? In this case we'll have much less redundancy? Please correct me if I'm wrong. Which approach is better have another tables for fav_sports,fav_videos,fav_music, etc or put them all in user's table?


texts: Storing revisions and relations between revised items in 1:N and N:N scenarios for audits

texts:
I have several of the following situations in a MySQL schema that I design for a custom CRM/ERP solution.

1:N related tables SO and SO_ITEMS (SO is just one example I have several) that both need to have revisions. The data sets stored tend to be get pretty big for some of these tables and, according to the business rules I gathered, there will be many changes that need auditing so copying all the child items for each revision of the parent will not scale very well I predict.
N:N related tables SO and PO tables (where I use a pivot table of course) that needs to be audited also so that changes from at least one way imply (or may imply) storing an updated revision of the data entry form the other table. (We need no worry about the child _items data entries for each of these tables because although they may be related, I don't require auditing for now).

My strategy for now is to use a separate table suffixed with _revision for each of the above tables and store the current revision number in the normal table and a revision number for each revision, at least for the parents.

So my question are:

is my schema flawed in some major way that I don't predict for this kind of scenario? (I suppose there are many other applications that have solved this problem out there).
should I make a direct relationship between x_revison and y_revision or use the revision number and query through the normal (current revision?) table? I want the variant that reduces complexity the most and I'm pretty sure querying through is simpler on most operations (I suspect there will not be a big disparity between SELECT/INSERT/UPDATE in normal operation, so I don't need to optimise for one type of DB operation). Am I missing something?
is it common practice to copy-on-write the revisions or should I just lazily insert entries in the revisions table when a change appears only? Any possible pros/cons to these two options.


texts:
I have the following two tables:

buynsell

CREATE TABLE `buynsell` (
    `id` VARCHAR(20) NULL DEFAULT NULL,
    `msg` VARCHAR(255) NULL DEFAULT NULL,
    `date` VARCHAR(25) NULL DEFAULT NULL,
    `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`fid`),
    INDEX `fid_index` (`fid`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=6523;

bsmsgs

CREATE TABLE `bsmsgs` (
    `id` VARCHAR(20) NULL DEFAULT NULL,
    `msg` MEDIUMTEXT NULL,
    `date` VARCHAR(25) NULL DEFAULT NULL,
    `subfid` BIGINT(20) NULL DEFAULT NULL,
    `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`fid`),
    INDEX `fid_subfid_index` (`fid`, `subfid`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=11408;

The field bsmsgs.subfid is referenced to bunsell.fid. I was fed up using these tables, and hence created the following two new ones(Engine is still MYISAM):

buynsell

CREATE TABLE `buynsell` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `type` ENUM('B','S','D','H','T') NOT NULL DEFAULT 'B',
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `dated` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `msg_nick` (`msg`, `nick`),
    INDEX `type` (`type`),
    FULLTEXT INDEX `msg` (`msg`)
)
COMMENT='The table storing information about users selling/buying/hiring things etc.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

replies

CREATE TABLE `replies` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `bns_id` INT(10) UNSIGNED NOT NULL,
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `dated` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `bns_id_msg` (`bns_id`, `msg`),
    INDEX `bns_id` (`bns_id`),
    FULLTEXT INDEX `msg` (`msg`)
)
COMMENT='Replies to buynsell messages from interested users.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Now, my problem is even though the AUTO_INCREMENT values are so high, there are actually less than 2000 entries in buynsell table and around 8000 entries in bsmsgs table.

What I can do is use the same entries from previous tables in my new ones but I want an elegant design and hence, would like to insert from scratch.

I can reinsert data from older buynsell by skipping to AUTO_INCREMENT key, fid and generate new rows with ids starting from 1 to 2000.

What I am facing trouble with is referencing the subfid field in bsmsgs to new id values in buynsell while correctly referencing and storing them into bns_id of replies table.

NOTE

If it can not be achieved by purely MySQL, I can use LuaSQL for this task.


texts: What is better? Delete and insert or update?

texts: I have a python script in which I have to update a column in 67000 rows. The value of the desired column can be unique for each row. I found that inserting 67000 rows takes about 5 seconds but updating takes around 2 minutes! Since I have to update large number of rows and there is huge speed difference I am thinking of deleting the 67k records and inserting fresh. Are there any dangers with this approach that I should be aware of?


texts: Are there any mechanisms to limit the size of a single database on MongoDB 2.6? I found some options like quota, quotaFiles and nssize but am unsure how to use them.


texts: SSIS package with log provider configured using expression - creates multiple log files, fails if I change original target folder

texts:
I have created a small SSIS solution to illustrate my problem.  Unfortunately it looks like I can't attach it here, so I will try to explain.  I can send the solution to people individually.

SSIS SQL Server 2008.  I have a package called "Parent_Package" with a single variable: "User::Log_Path".  During design time I assigned this value to it: "C:\temp\Log_Design_Time".  The package has a log provider configured (simple text file).  The connection string of the log file has an expression: @[User::Log_Path]  + "\" +  @[System::PackageName] + [expression for timestamp] + ".txt".   As you can see, I would like to create a new log file for each package execution.

If I now run this package, I will have two log files generated!  One will have a timestamp from around the time I created the package.  This file only has the header fields and no other other information.  The second file has the correct timestamp and correct output information:

It appears that the first file gets created during validation (even though I set DelayValidation = True for the log file connection manager).  So my first question is why is this happening?  It doesn't seem like the right behavior.
Now to take this to the next step, I create a child package "Package_Child".  I add an execute package task to the parent package to run this child package.  In the child package I add a variable by the same name as above: "User::Log_Path".  Next, I add a configuration to the child package, so that the value of "User::Log_Path" is obtained from the parent package variable by the same name.  Finally I add log provider to the child package and set it up the same way as described above for the parent package.

Let's now run the parent package again.  This time I will end-up with three log files: two for parent and one for child:

For my next test, I now would like to change the location of the log files, so I modify the value of the [User::Log_Path] variable in the parent package to "C:\temp\Log_Run_Time".  If I run now, I will have four log files!  Two for the parent package in the  "Log_Run_Time" directory and two for the child package - one in "Log_Design_Time" and one in "Log_Run_Time" directory:

What's going on?
Finally, if I simply remove the original "Log_Design_Time" directory, my child package will start failing complaining that it cannot find the path specified:

Obviously it's still looking for the path specified earlier, but why?  Again, I set "Delay Validation" = True on all my connection managers and the package itself.
I appreciate any help on this.

Thank you!

P.S.  Here is a complete expression for log path: @[User::Log_Path]  + "\\" +  @[System::PackageName] + "_" +
(DT_STR, 4, 1252)DATEPART("yyyy", @[System::ContainerStartTime]) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::ContainerStartTime]), 2) + "_" +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::ContainerStartTime]), 2) + ".txt"


texts:
I will preface this by saying that this is a cross post from an unanswered question on Stack Overflow.
I am not doing this simply to get more views on the question, I would like the DBA community to share their view on whether this might even be a bug in SQL Server. I don't think the SO community has the expertise to decide that, so I'm reposting here.

Suppose I have a table T, and I have an indexed view V on it:
CREATE TABLE dbo.T (id int PRIMARY KEY, b bit NOT NULL, txt varchar(20));
GO
CREATE VIEW dbo.V
WITH SCHEMABINDING AS
  SELECT T.Id, T.txt
  FROM dbo.T AS T
  WHERE T.b = 1;
GO
CREATE UNIQUE CLUSTERED INDEX idx_V ON dbo.V (Id);

In this trivial example it's basically just a filtered index, but it could also have joins and such like.
I would now like to select some rows in T where b = 1, the filtered view here is very useful, and I'm on Standard so have to use NOEXPAND (or it's too complex for view matching):
SELECT Id, txt
FROM V WITH (NOEXPAND);

This works nicely.
Now I want to update these rows to some value. The view qualifies as updatable so I can do this:
UPDATE V
SET txt = 'Foo';

This does not use the indexed view to find the rows to update, even though it needs them to actually update the view. What I would like it to do is use the view like a normal table index, and identify the rows to update from it, pass them to the Clustered Index Update on T, followed by an Update on the view. So I try this:
UPDATE V WITH (NOEXPAND)
SET txt = 'Foo';

This fails with "Hint 'noexpand' on object 'V' is invalid.".
I know I can get round it with a query such as this:
UPDATE T
SET txt = 'Foo'
FROM T
JOIN V WITH (NOEXPAND) ON V.Id = T.Id;

But this means an extra Seek. Not only that, it adds a Filter on the subsequent indexed view update to check the rows match the view (a joined view would require the joins to be evaluated) when clearly they must match the view.
Is there any way of getting this to work in the way I would like?
I believe there is an optimization called rowset-sharing which may make a difference here, does anyone know about this?

Update:
It does not help to put the view in a FROM clause or even in a derived table. As soon as it looks to the parser that it's being used for an update, it fails.
There is no indication that NOEXPAND should not work either in the Table Hints, Indexed View, or Updatable Views documentation. The docs for the UPDATE statement specifically mention that certain table hints are not allowed, but only NOLOCK and READUNCOMMITTED are excluded.

So, maybe this is even a bug in SQL Server, and I should file it on Azure Feedback. What do people think?


texts: Why evaluates "IS NULL" in SSMS interactive mode to "TRUE" but the same statement being executed in a SP to "FALSE"?

texts:
Given

DECLARE @IN__CUST_NO    INT
DECLARE @OUT__USER_ID   INT
SET @IN__CUST_NO = 1000006660

CASE 1: IS NULL evaluates TRUE

Since no CUSTOMER_NUMBER with the value of 1000006660 exists in our dummy USERS-Table, stating a SELECT restricting to tuples of CUSTOMER_NUMBER-Columnvalues of 1000006660 leads to a zero-sized resultset. Hence IS NULL evaluates to TRUE in following statement issued to SSMS interactivly; resulting to PRINT 'null'

SELECT @OUT__USER_ID = u.ID
FROM USERS u
WHERE u.CUSTOMER_NUMBER = @IN__CUST_NO

IF (@OUT__USER_ID IS NULL)
BEGIN
    PRINT 'null'
END
ELSE
BEGIN
    PRINT 'set'
END

CASE 2: IS NULL evaluates FALSE

As above no CUSTOMER_NUMBER with the value of 1000006660 exists. But if one encapsulates the IS NULL part in a stored procedure like below and executes it, IS NULL will result to FALSE! (at least on my configuration; using SQL-Server 2005 and SSMS 2008). Endresult: PRINT 'set'

CREATE PROCEDURE SP_NULL_TEST
    @IN__CUST_NO        INT,
    @OUT__USER_ID       INT OUTPUT
AS
BEGIN
    SELECT @OUT__USER_ID = u.ID
    FROM USERS u
    WHERE u.CUSTOMER_NUMBER = @IN__CUST_NO

    IF (@OUT__USER_ID IS NULL)
    BEGIN
        PRINT 'null'
    END
    ELSE
    BEGIN
        PRINT 'set'
    END
END

EXEC SP_NULL_TEST @IN__CUST_NO, @OUT__USER_ID=@OUT__USER_ID

Please, can someone explain this strange behaviour? Many thanks in advance.


texts: Restore database with different filename

texts:
I have to restore a new back up file daily (as an agent job, after first deleting the existing db) but the file name will vary as it will include the timestamp as well.

How can I achieve this so that the restore operation will identify the new file? The name of the file will be something like this and always in the same directory: AnyLetterGoesHere_TimeStamp.bak

Thanks all


texts: Reloading .sql file from java into PostgreSQL - Windows

texts:
I am trying to reload a .sql script file into PostgreSQL from my sample Java application.

Script is successfully executed from console as well as from pgAdmin tool. But from Java, it hangs...

This is the code I am using:

C:/Program Files/PostgreSQL/9.2/bin/psql.exe -U postgres -d sampledb -f "H:/Data/samplescript_postgres.sql"

Can anyone help me? I am using PostgreSQL 9.2

Java code:

String path = "H:/Data/samplescript_postgres.sql";
final String cmd = "C:/Program Files/PostgreSQL/9.2/bin/psql.exe -U postgres -d sampledb -f " + "\""+ path + "\" ";

try {
    reloadProcess = Runtime.getRuntime().exec(cmd);
    if(null!=reloadProcess){
        if(reloadProcess.waitFor()==0){
            System.out.println("Reloaded");
        }
    }
} catch (IOException e) {
    e.printStackTrace();
} catch (InterruptedException e) {
    e.printStackTrace();
}

My pg_hba.conf file:

# TYPE  DATABASE    USER    ADDRESS         METHOD
# IPv4  local   connections:
host    all         all     127.0.0.1/32    md5
# IPv6  local   connections:
host    all         all     ::1/128         md5


texts: T-SQL, find projects' salaries (budget) from a created View

texts:
everyone.

I have played with a test database and wanted to calculate the budget (salaries) allocated to each ProjectID.

CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NULL,
[JobTitle] [varchar](50) NOT NULL,
[DepartmentID] [int] NOT NULL,
[ManagerID] [int] NULL,
[HireDate] [smalldatetime] NOT NULL,
[Salary] [money] NOT NULL,
[AddressID] [int] NULL,CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[EmployeesProjects](
    [EmployeeID] [int] NOT NULL,
    [ProjectID] [int] NOT NULL,
 CONSTRAINT [PK_EmployeesProjects] PRIMARY KEY CLUSTERED
(
    [EmployeeID] ASC,
    [ProjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeesProjects]  WITH CHECK ADD  CONSTRAINT [FK_EmployeesProjects_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO

ALTER TABLE [dbo].[EmployeesProjects] CHECK CONSTRAINT [FK_EmployeesProjects_Employees]
GO

ALTER TABLE [dbo].[EmployeesProjects]  WITH CHECK ADD  CONSTRAINT [FK_EmployeesProjects_Projects] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Projects] ([ProjectID])
GO

ALTER TABLE [dbo].[EmployeesProjects] CHECK CONSTRAINT [FK_EmployeesProjects_Projects]
GO

I have created the below VIEW called Budget:

CREATE VIEW Budget AS (SELECT Employees.EmployeeID, Employees.Salary, EmployeesProjects.ProjectID
FROM Employees
JOIN EmployeesProjects ON EmployeesProjects.ProjectID = Employees.EmployeeID)

I have found a solution that lists the budget allocated to eac ProjectID:

SELECT SUM(Salary) AS BudgetedSalary, ProjectID FROM Budget
GROUP BY(ProjectID)

My questions to you guys:
Is there a way to get the solution in a more elegant way, e.g. with a sub-query instead of using a VIEW (with a single query)?

Thanks much in advance!

p.s. I can add the SQL script for both tables' data insert, if requested...


texts: How to install Microsoft SQL Server 2014 Standard Edition on trial?

texts:
I want to install SQL Server 2014 Standard and Enterprise editions on two separated servers with same hardware in order to test the performances differences running internal set of procedures.

The DBA gave me two ISOs with the following name:

SW_DVD9_SQL_Svr_Standard_Edtn_2014w_SP1_64Bit_English_-2_MLF_X20-29010
SW_DVD9_SQL_Svr_Ent_Core_2014w_SP1_64Bit_English_-2_MLF_X20-28988

claiming they are downloaded from Microsoft , but once I have installed them and run:

SELECT @@VERSION

I get the following for the Enterprise edition:

  Microsoft SQL Server 2014 - 12.0.4213.0 (X64)     Jun  9 2015 12:06:16
    Copyright (c) Microsoft Corporation     Enterprise Evaluation Edition
  (64-bit) on Windows NT 6.3  (Build 9600: )

and the following for the Standard edition:

  Microsoft SQL Server 2014 - 12.0.4213.0 (X64)     Jun  9 2015 12:06:16
    Copyright (c) Microsoft Corporation     Enterprise Evaluation Edition
  (64-bit) on Windows NT 6.3  (Build 9600: )

So, what I am doing wrong? Why, when I am using the Standard iso again Enterprise is istanlled?

I guess this should be SP1 issue, because I have checked that I had successfully install standard edition of SQL Server 2014 without SP1.


texts: Managing SQL Server 2008 R2 transaction logs when using principal/mirror databases

texts:
My system has the main database as a principal (reads/writes only to that database) and then a mirror database on a different site that receives all database updates from the principal database.

This is all working fine, but the transaction log on the principal database is close to 80GB and growing. I have tried shrinking the log but that task fails every time. In my opinion I would not need the log at all, since all updates are mirrored to the other site.

Principal database needs to be in full recovery mode for the mirroring to work.

Can you give me some steps on how to get rid of that annoying transaction log or how to get it into a manageable size? Since the mirror site is my recovery method, can I just delete in?


texts: How do I join two tables on two different instances of DB2?

texts:
There are two instances of DB2 running on the same machine. One has a table aliased to the a table in the other one.  Is there a way to do this in DB2 or does it have to be done via an external tool?

This is with an older version of DB2


texts: Does the keyword NEW have the per-connection basis aspect to it?

texts:
I am watching a mysql table with an after insert trigger and i have started to receive many inserts.I am using NEW.message to get the last inserted message.

Since i am receiving many inserts from many clients,i am thinking there could be a time that two records are inserted concurrently.I know that the OLD and NEW keywords enable you me access columns in the rows affected by a trigger,but does NEW have per-connection uniqueness just like LAST_INSERT_ID()?.


texts:
my question is:

Should business logic be stored in the data warehouse data store? Or should it only exist in the reporting applications?

To give a more concrete example:

In the CRM operational system there are abstract concepts of leads and opportunities, and lead statuses and opportunity stages. These concepts are very common, well understood, and used across different CRM tools.

And business users can legitimately ask questions like, what is the conversion rate for all leads from status "raw lead" to status "inside sales qualified lead".

Then for the data warehouse designer, the question becomes:
1. When someone changes a lead status from x to y after a call, should that be a row in a fact table somewhere? (perhaps in the same row as the call)
If yes, then we are storing abstract business concepts, rather than just real world events, in a fact table.
2. And the bigger question is, should the data warehouse even be aware of terms like "leads" and "opportunities"?
If yes--
2. Should the fact table have columns for old_status and new_status?
3. Or, should status change be handled as a slowly changing dimension?

If business logic is stored directly in the data warehouse, a lot of business questions become easier to ask. (lead status conversion metrics, opportunity stage advancement velocity metrics, and etc) But it seems more brittle to changes, is more complex to implement, and perhaps pollute the "verbs are facts and nouns are dimensions" paradigm.

How should I approach this design, and what are the best practices and guiding principles here?

Max


texts:
I read one article which shows how to use different aggregations effectively.

https://sqlkiwi.blogspot.com/2011/12/is-distinct-aggregation-still-considered-harmful.html

I am facing a similar situation where one of my query is performing very badly. Below is my original query which is taking > 3.5 hrs to complete.

Original query:

SELECT COUNT(AT.HK) HM
      ,COUNT(DISTINCT AT.HK) DST_HM
      ,SUM(BT.CTB) WC
      ,Count(CT.PSMI) TS
      ,COUNT(DISTINCT AT.CI) CTS
      ,BT.PN
      ,BT.CN
      ,DT.AD
FROM S1.BigTable1 AT JOIN S1.SmallTable1 BT
        ON (AT.CCI = BT.CCI)
     LEFT JOIN S1.SmallTable2 CT
        ON (CT.CCI = AT.CCI
                AND CT.PHI = AT.PHI)
     LEFT JOIN S1.BigTable2 DT
        ON (DT.CI = AT.CI
                AND DT.PHI = AT.PHI
                AND DT.CCI = AT.CCI)
GROUP BY BT.PN
        ,BT.CN
        ,DT.AD
ORDER BY BT.PN
        ,BT.CN
        ,DT.AD

I have modified it as below and now it is taking ~8 mins to complete. Basically with original query it was causing table spool and was using stream aggregate which was taking way longer. In modified query, I split the query using 2 CTEs and hash group query hint. This now runs faster(~8 mins) compared to original query.

Modified query:

WITH CTE_1 as
(SELECT COUNT(AT.HK) HM
      ,COUNT(DISTINCT AT.HK) DST_HM
      ,SUM(BT.CTB) WC
      ,Count(CT.PSMI) TS
      ,BT.PN
      ,BT.CN
      ,ISNULL(DT.AD, '') AD
FROM S1.BigTable1 AT JOIN S1.SmallTable1 BT
        ON (AT.CCI = BT.CCI)
     LEFT JOIN S1.SmallTable2 CT
        ON (CT.CCI = AT.CCI
                AND CT.PHI = AT.PHI)
     LEFT JOIN S1.BigTable2 DT
        ON (DT.CI = AT.CI
                AND DT.PHI = AT.PHI
                AND DT.CCI = AT.CCI)
GROUP BY BT.PN
        ,BT.CN
        ,DT.AD
)
,
CTE_2 as
(select COUNT(DISTINCT AT.CI) CTS
        ,BT.PN
      ,BT.CN
      ,ISNULL(DT.AD, '') AD
        FROM S1.BigTable1 AT JOIN S1.SmallTable1 BT
        ON (AT.CCI = BT.CCI)
     LEFT JOIN S1.SmallTable2 CT
        ON (CT.CCI = AT.CCI
                AND CT.PHI = AT.PHI)
     LEFT JOIN S1.BigTable2 DT
        ON (DT.CI = AT.CI
                AND DT.PHI = AT.PHI
                AND DT.CCI = AT.CCI)
GROUP BY BT.PN
        ,BT.CN
        ,DT.AD
)
SELECT C1.HM,
        C1.DST_HM,
        C1.WC,
        C1.TS,
        C2.CTS,
        C1.PN,
        C1.CN,
        C1.AD
FROM CTE_1 C1 JOIN
        CTE_2 C2 ON C1.PN = c2.PN AND
        C1.CN = C2.CN AND
        C1.AD = C2.AD
ORDER BY C1.PN
        ,C1.CN
        ,C1.AD
option (HASH GROUP)

Below is my environment details:

Version: SQL server 2012 SP2
Row count details:
BigTable1 : 224,080,819
SmallTable1 : 2,685
SmallTable2: 235,606
BigTable2 : 58,110,556

My question is is my re-write correct? Is there any better way to rewrite the query so that can further reduce the execution time?

Please let me know if you need any further details.


texts: Is there any reason not to use foreign keys in MySQL?

texts:
I've never really used foreign keys before but I am building a project where they could come in handy. I have looked at some other large software such as some forum systems and they don't use foreign keys, so I'm wondering why wouldn't they!?

I understand that it would come in handy for situations where they have the username in the users table and then on the posts table for example they have the users username in the author field rather than an id (my assumption is they do this to avoid expensive joins to the users table); so for this situation you would think adding a foreign key to the posts table to reference the username would be a good idea.

I'm wondering - when you do something such as this:

// Start transaction
SELECT * FROM users WHERE username='some_username' LIMIT FOR UPDATE;
// End transaction

Because of the foreign key placed on the posts table with reference to the username in the users table, does that then cause not only that row in the users table to be locked but also all rows in the posts table that reference the users username to be locked as well?

If not, are there any other reasons why one would not use foreign keys for instances such as this?


texts: Find Table Variables definitions in Stored Procedures

texts:
As part of tuning of a large database, I am first of all interested in finding all the table variables.

I am using this script:

SELECT [Schema]=schema_name(o.schema_id), o.Name, o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like '%DECLARE @%TABLE%'

It returns many results but most of them are irrelevant.

How can I change the search pattern to match an exact definition?


texts:
For example select * from a inner join b on a.i=b.i ; is definitely inner join.

But can you let me know whether the below statements belongs to cross join or inner join ?

select *
from a ,b
where a.i=b.i;

select *
from a ,b
on a.i=b.i;

Thanks,
Kasi.


texts: Default value for SQL Server CE 4.0 column

texts:
I am writing a tool to convert a system from XML data files into a SQL Server CE format. Not the most efficient way to do this, but essentially it will be digesting XML files created by another program, and I want to be able to use this tool to create a comparable DB from the files created externally.

What I discovered while writing it, is that when I went to parse column default constraints, I just single quoted everything quick and dirty, but it worked.

The end result is when I open the .SDF file in Visual Studio, I see the column is of type integer, and the value is ('1')

Were I creating it by hand I would put it in as (1), SQL Server CE does not seem to care either way, and when using the DB, not specifying the value does indeed yield a 1 in that column just as one would expect. It only seems wonky when looking at the table schema editor.

I will re-code my tool just because I do not like assumptions, and get it closer to what I would have done by hand, but the question is now stuck in my head, is would there be any issue had it been left like this, and what did it think I was trying to do (perhaps something I am unaware of is why it allowed it to happen?)

Essentially it looks like it allows me to define the type as integer, and the default as a string, and was doing me the courtesy of converting my mistake?

Is that correct or is there a situation where such would be preferable, required, or is the question simply moot?


texts: Which version of AdventureWorks has a column called CustomerId on sales.vIndividualCustomer?

texts:
The SQL Native Client MOF Files here come with a Visual Studio Solution that queries adventure works for testing purposes. One of the first queries that application performs when it is compiled and run is

SELECT CustomerID, Title, FirstName, MiddleName, LastName, Suffix,
    Phone, EmailAddress, EmailPromotion, AddressType, AddressLine1,
    AddressLine2, City, StateProvinceName, PostalCode,
    CountryRegionName, Demographics
FROM Sales.vIndividualCustomer

This returns the following error on Adventureworks 2008, 2012 and 2014.

Msg 207, Level 16, State 1, Line 3
Invalid column name 'CustomerID'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Phone'.

I downloaded these three AdventureWorks editions off of CodePlex today. For 2008 annd 2012 I downloaded a zip file with an LDF and MDF that I attached via CREATE DATABASE FOR ATTACH. For the 2014 version of the database I restored a bak file with RESTORE DATABASE WITH MOVE. What file should I be downloading from CodePlex?


texts: Keep H2 in-memory database between connections

texts:
I create and use a H2 database in-memory (no writing to storage) for demos and quick tests using this code in Java:

 Connection conn = DriverManager.getConnection( "jdbc:h2:mem:example_db" ) ;

This works the first time, but then the database seems to disappear, unavailable for further work. How can I make use of the same in-memory database over time?


texts: SQLPlus "no listener" only with a remote connection from Windows

texts:
The Oracle database is running on a linux server.
The lsnrctl service is running.
Locally, I can connect to database from sqlplus and sql developer.
Remotely, from windows, I can't connect.
sqlplus on windows says that image above shows.
sql developer says that Network Adapter could not establish the connection.
I have done ping from the windows machine to the linux server and is ok.

lsnrctl status:
[oracle@oracledb ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 21-SEP-2015 19:30:58

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                21-SEP-2015 16:43:32
Uptime                    0 days 2 hr. 47 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracledb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "mondev" has 1 instance(s).
  Instance "mondev", status READY, has 1 handler(s) for this service...
Service "mondevXDB" has 1 instance(s).
  Instance "mondev", status READY, has 1 handler(s) for this service...
The command completed successfully

listener.ora content:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle


texts: Conversion Error after Comatibility Level upgrade. Removing the order by resolves the error... why?

texts:
I have a SP that I can't share the entire contents of, but I'll do my best to facilitate test queries.
The context: we had been running on compat level 100 up until this week when I changed the level to 130. It was only after that happened that we started to see the ol' conversion error (see bottom of post) whenever trying to run the SP. I was pretty floored by that so to compare I dropped the DB to level 100 and sure enough it worked. I am currently working in a restore db with level 130 to debug this.
I have also seemingly narrowed down the part of the SP that's causing trouble, and I am STUMPED. You can see the query below. When running this by itself outside of the SP, there is no issue at all, and the query completes. I have also found that commenting out/removing the ORDER BY allows the query to run without issue in the SP. This doesn't make any sense to me. My understanding is that the ORDER BY statement is the very last thing to be executed, so any values used in the order by have already been filtered down. Additionally, in the case that I am testing the whole SP in, there is only one row, and it does indeed convert to a datetime.
I have tried a number of things like aliasing the column and using the alias in the order by. I have tried putting everything but the top and the order by in a subquery and then using the outer query to filter down to the top 1. I have tried using CAST instead of CONVERT. I have tried converting to DATE instead of DATETIME.
What does seem to work is using a subquery with a ROW_NUMBER() window and then using the row num to filter in an outer query. This feels inelegant and is extra work for the engine to perform, so I would like to figure out the why.
FYI: the below query is using the exact value ('2020-12-29') that is being used when the error is thrown. This is also the only value being used.
declare @cycle_date table(result varchar(200) )

insert into @cycle_date values('2020-12-19')

select top 1 convert(datetime,result)
from @cycle_date
where isdate(result)=1
order by convert(datetime,result)

Conversion failed when converting date and/or time from character string.
Any help with this is greatly appreciated. Thanks!
Quick edit: I also did try converting and casting to DATETIME2, as well. No dice.


texts: Merging table records across TabName for each PersonID

texts:
Consider the following table,

CREATE TABLE temp (
    [TabName] VARCHAR(255),
    [PersonID] VARCHAR(255),
    [FirstName] VARCHAR(255),
    [Gender] VARCHAR(255),
    [BenefitType] VARCHAR(255),
    [HealthStatus] VARCHAR(255));

  INSERT INTO temp VALUES
    ('TAB1', 'C103','John', 'M', 'Benefit Type 1', NULL),
    ('TAB2', 'C103','John', 'M', NULL, 'Healthy'),
    ('TAB3', 'C103', 'John', 'M', NULL, 'Healthy'),
    ('TAB1', 'C104', 'Mary', 'M', 'Benefit Type 2', NULL),
    ('TAB2', 'C104', 'Mary', 'M', NULL, 'Sick'),
    ('TAB3', 'C104', 'Mary', 'M', NULL, NULL)

TabName     PersonID     FirstName    BenefitType      HealthStatus
TAB1        C103         John         Benefit Type 1   NULL
TAB2        C103         John         NULL             Healthy
TAB3        C103         John         NULL             Healthy
TAB1        C104         Mary         Benefit Type 2   NULL
TAB2        C104         Mary         NULL             Sick
TAB3        C104         Mary         NULL             NULL

I would like to merge the information across TabName in a new table, such that the new table contains all the unique information for each PersonID.

For example, based on the above table, I would like to find the new table

PersonID   FirstName    BenefitType      HealthStatus
C103       John         Benefit Type 1   Healthy
C104       Mary         Benefit Type 2   Sick

I am not too sure where to start with this problem.


texts: What is the simplest way of excluding the current row from the result of an analytic?

texts:
I want to do something like the following:

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

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

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

What is the simplest way of achieving this?


texts:
We have an OLTP database (running SQL Server 2008 R2) that has 6 tempdb files.

All of them have an Autogrowth by 10%, restricted growth to 10000mb. At one point, someone changed one of the growths to restrict by 120000mb which caused that tempdb file to grow exponentially larger than the others. We are trying to track down when this change could have occurred.

Is it possible to see, either, when a setting was changed or a history of tempdb growth?

One issue is that the issue has been fixed and the tempdb file is back down to normal size.


texts: Exported CSV from MySQL doesn't appear in specified directory

texts:
I'm trying to figure out the syntax to export the results of a MySQL query to the desktop as a CSV file via SSH. Here's what I'm trying:

 SELECT * INTO OUTFILE 'C:\Users\Jim Smith\Desktop\zyzyz.csv'
 FIELDS TERMINATED BY ','
 OPTIONALLY ENCLOSED BY '\"'
 LINES TERMINATED BY '\r\n'
 from <table> limit 5;

The query itself works fine, but the file doesn't appear on the desktop. What should I change to get it to show up?


texts: What does the repmgr the symbol "*" mean in the repmgr cluster status output?

texts:
I'm new to postgres administration and was wondering what does the "*" symbol means in the repmgr cluster show command output.
 ID | Name    | Role    | Status    | Upstream | Location | Connection string
----+---------+---------+-----------+----------+----------+------------------------------------------------------------------
 1  | node-1  | standby |   running | node-2   | default  | host= [...]
 2  | node-2  | primary | * running |          | default  | host= [...]
 3  | node-3  | witness | * running | node-2   | default  | host= [...]

I didn't find anything about it in the documentation (https://repmgr.org/docs/4.0/repmgr-cluster-show.html)
Using repmgr cluster show --csv as mentioned in the documentation, I can see that the node with ID 1 is in recovery state "in recovery". Is it related ?
postgresql version : 10.5
repmgr version : 4.0


texts: What happens to non clustered index when included column is updated using update statement?

texts:
Question on non clustered index with included columns (DB - MS SQL Server).
I read blog Optimized Non-clustered Index Maintenance which gives information on query plans when update statements is executed and clustered index and non clustered index are defined for table.
I have question on non clustered index with included columns.
I'm referring same example provided by blogger

CREATE TABLE T (PK INT, A INT, B INT, C INT, D INT, E INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)

CREATE INDEX TB ON T(B)
CREATE INDEX TCD ON T(C,D)
CREATE INDEX TE ON T(E)

--  this is new non clustered index with included columns

CREATE INDEX TF ON T(E) INCLUDE(A)

INSERT T VALUES(0, 10, 20, 30, 40, 50)

UPDATE T SET A = 19

If no index TF is defined then only update on clustered index will be performed and nonclustered index insert and delete operations will not be performed.
But what will happen when TF is defined?


texts: How should I restructure a table that contains multiple columns that contain similar, but not same data?

texts:
I currently inherited an InnoDB table that describes a product.  In the table there are a few columns that describe the maximum height at which the product may be mounted.

These are the columns:

HeightMaxExterior
HeightMaxExteriorInsect
HeightMaxInterior
HeightMaxConcession
HeightMaxPassThru

I was thinking perhaps of creating a new table: MaxHeight that had the following columns:

Id, Height, Type, ItemCode, Context

Id, Height, and ItemCode are pretty self explanatory.  Type would be an enum, of either 'max', or 'min', and Context would be an enum for 'interior', 'exterior', 'concession', 'passthru', and 'insect'.

Should I create this new table?  Or should I just leave it as it is?


texts: SQL Server : huge log file, we know why, but what can we do about it

texts:
We have this SQL Server database that is fully backed up daily. Recently we noticed that the LOG file for this database is huge (> 50 GB). I did some research and I found out that it is because we weren't taking transaction logs.

So I know why my log is so big, but I'm wondering what I can do about it.

Clarification: we want to use the 'full' recovery option with regular transaction log backups. We realize we aren't right now and this will be fixed as soon as we can get the transaction log to a reasonable size.

The ideal route would be to take a log backup and then do a DBCC Shrink to get the file down to a normal size. However, if I do this on a full backup of the database, this takes a few seconds and creates a log backup of 5 GB and shrinks the transaction log way down to a few MB.

If I do this on the production environment, it takes really long and it seems to create a file far bigger. I estimate this will be about 50 GB. Is there anything I can do about this, because the server just doesn't have another 50 GB, so taking the log backup in this way isn't an option.

Since we weren't taking regular log backups, we only have one recovery point per day I suppose (the full backup taken every night), so is there a way to delete the log and free up the 50 GB of the log file in another way?

Or are there any other options you can think of that might help me in this situation?


texts: How to maintain unique sequence across table columns?

texts:
I hava two tables registed_products and unregisted_products. Both have autoincrement primary key which can be overlapping which if 100 id exist reg table,
it can exist in unreg table as well. Insert can come from many places with in the application

There is third service expecting unique id for both type of products.

In oracle , I could have introduced one more column in each table. Create a sequence. Now could create the trigger before_insert on both the tables so that
whenever there is an insert in any table, trigerr will get the next_val from sequence and insert in to new column. This way I can ensure unique
number across table columns.

I am not sure how to achieve the same in mysql as there are triggers but not sequences that can be used across tables ?


texts: how to write code for logon/logoff triggers in oracle?

texts:
I have written the code for a logon/logoff triggers. Logon is working but the logoff trigger is created with an error. Please give some suggestions.

Logging table:

create table logon_off_audit(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar(32),
last_module varchar(32),
logon_date date,
logon_time varchar2(30),
logoff_date date,
logoff_time varchar2(10),
elapsed_minutes number(8));

Login trigger:

create or replace trigger
   logon_audit_trigger
after logon on database
begin
insert into logon_off_audit values(
   user,
   sys_context('userenv','sessionid'),
   sys_context('userenv','host'),
   null,
   null,
   null,
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   null,
   null
);
end;
/

Logoff trigger:

create or replace trigger
   logoff_trigger
before logoff on database
begin
update logon_off_audit set last_action = (select action from v$session where sys_context('userenv','action') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
last_program = (select program from v$session where
sys_context('userenv','program') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
last_module = (select module from v$session where
sys_context('userenv','module') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
  logon_off_audit
set
   logoff_date = sysdate
where
   sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
   logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
   sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
elapsed_minutes =
round((logoff_date - logon_date)*1440)
where
sys_context('userenv','sessionid') = session_id;
end;
/


texts: SQL Server Latching from Multithreading/Parallel Inserts and Hash Partitioning

texts:
We are conducting parallel, multithreading inserts in SQL server tables, and want to reduce latching.

What are the disadvantages of utilizing hash partitioning to reduce latching?  Is it reduced query slowness from essentially querying all these split up partition tables?

We have around 120 Table Inserts Per Second, financial system.

Other Notes: SQL 2016 System will use around 50 GB of SSD Hard drive space a year.
Currently, have 50 Core processors, and 150 GB of RAM.

Platform is not built, so no baseline to test; but I need to develop testing plan and strategies.

Hash Partitioning Example:
http://www.madeiradata.com/how-to-solve-the-tail-insert-problem-2/

CREATE PARTITION FUNCTION pf_hash (TINYINT)
AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8);

CREATE PARTITION SCHEME ps_hash
AS PARTITION pf_hash ALL TO ([PRIMARY]);

CREATE TABLE dbo.UserEntries_RegularWithHash
(
Id BIGINT IDENTITY NOT NULL,
UserId INT NOT NULL ,
CreatedDate DATETIME2 NOT NULL,
HashId AS CAST(Id % 9 AS TINYINT) PERSISTED NOT NULL,
CONSTRAINT PK_UserEntries_RegularWithHash
PRIMARY KEY CLUSTERED (Id,HashId)
)
ON ps_hash(HashId);


texts: basic question about user-/role concept in postgres-databases

texts:
I hope this question is specific enough to be allowed and not doomed as opinion-based:

I'm fairly new at administrating postgres databases. My basic approach was to create a new user / role for each real-world user. I thought it would be advantageous for use-cases, where users simultaneously access the same database. However, I realised this has some disadvantages in terms of granting access-right to future tables.

I am now suspecting that my concept (separate user-account for each real-word user ) is not the "usual way". I could imagine, that it is sufficient to have just a generic user / role, and each real-world user uses that same generic role to connect, create, read, write, modify data and tables?

Can postgres handle such a setup just fine even in the case of concurrent read/write-actions? Is this second concept (one user-account for many users) just fine and frequently applied?

Thanks in advance for your help (or for pointing out appropriate www-sources I have missed).


texts: Correlated subquery and join: still the same exectution plan?

texts:
I have a correlated subquery like this (from BOL):

SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
GO

When I rewrite this query using joins

select c.LastName, c.FirstName, e.BusinessEntityID, d.Bonus
from Person.Person as c
    inner join HumanResources.Employee as e on e.BusinessEntityID = c.BusinessEntityID
    inner join Sales.SalesPerson as d on d.BusinessEntityID = c.BusinessEntityID
where Bonus = 5000.00

And look the actual execution plan, it looks exactly the same in both queries. Why? I was thinking that correlated subquery is much slower because of the nested loop and the execution plan looks different? Is it because there is not much data in these tables?


texts: What is a failsafe way to embed Oracle12c database with a product?

texts:
We are planning to embed Oracle12c as the default data source
for our application. That means, Oracle12c and our application
are going to reside on the same server.

Our application runs on 64-bit Windows and Linux platforms. So
far, from an installation perspective, we have two choices:

choice-1: We perform a silent installation of Oracle12c database
during our product installation followed by user account creation,
table creation for our application.

choice-2: We install Oracle12c server software alone in our in-house
64-bit windows, linux servers. Then we zip up our ORACLE_HOME and
perform a clone of the oracle database in production followed by a
silent run of database configuration assistant to create the database.

I would typically select choice-2 since it is less time consuming and
easier than a full installation everytime. But, I have the following
questions with regard to this :

If I choose the clone approach, is it necessary that my source and
destination servers have the same machine architecture. For example, if
I install Oracle12c server software alone in a 64-bit windows 2008 server VM,
will I be able to clone this source ORACLE_HOME to any 64-bit windows server
irrespective of the underlying processing architecture ? (like Intel,AMD or any
bare metal physical server).
Which is a recommended approach from a fail-safe perspective ? (I know it is
subjective ; nevertheless)

[N.B] The idea is to use a single instance non-CDB, so no plug gable databases


texts: How to do Row Level Security in SSRS 2008 R2 (or 2012) with Active Directory Groups?

texts:
I would like to create a single report that filters data based on what Active Directory group the user is in.

Through various searches I see that Row Level Security is possible in SSRS in the Report
Model.  However, I can't seem to find any instructions for SSRS 2008 R2.  They all seem to be for 2005 and it seems that Microsoft has removed any sort of feature like this in 2008 along with most of their articles.

Is this even possible anymore?  If not, what's the alternative?  I don't want to have to create a report per group and I don't want people to see data that is not available to them.

Do I need to somehow handle this in a View?  If so, how do I get TSQL and AD to talk?  Will the user's SSRS credentials be passed to the view?


texts: Data warehousing (ETL): Scheduled data migration from MySQL to PostgreSQL

texts:
I've been assigned task of data warehousing for Reporting and data analysis. Let me first explain what we are going to do.

Step 1. Replicate production server MySQL database.

Step 2. Scheduled ETL: Read replicated database (MySQL) and push data to PostgreSQL.

Now I need your help on Step 2.

Note: I want saveOrUpdate functionality. If id is available then update it or save it. Data will be picked up based on modified date.

So is there any tool available for scheduled data push in PostgreSQL?, Considering my requirements.

If there ain't any tool available then which programming language I should use for ETL? And other pointers you can provide me to achieve this.


texts: Import from csv file to SQL table - Double quotes on the very end

texts:
I was able to import a .CSV file into my sql table, here is my BULK INSERT script:

BULK INSERT TLS_Sysprocess
FROM 'D:\Dennis\DevAPPServers2.csv'
WITH (
FirstRow=2
,FIELDTERMINATOR = '","'
,ROWTERMINATOR = '"\n"'

)
GO

Notice in the picture, the last column has double quotes:
e.g. MyAppServer2"

Unfortunately, you can only have ONE FIELDTERMINATOR, e.g. FIELDTERMINATOR = '","'

What would be the simplest way of removing this double quote?

I also created this format.fmt to be used for my BULK INSERT, it cannot be read for some reason, here is a snip:


texts: What files are safe to delete after upgrade?

texts:
This question is very similar to Recommended procedure to remove old files after major version PostgreSQL database cluster upgrade, but I did the upgrade in a different way, so the answer might be/probably is different.
I administer a database on a headless Ubuntu system with disks very near capacity.  Therefore, when I upgraded my database from Postgres 10 to Postgres 12, I used the --link option.
/usr/lib/postgresql/12/bin/pg_upgrade --old-datadir=/var/lib/postgresql/10/main \
--new-datadir=/var/lib/postgresql/12/main [stuff deleted] --link

This worked great, and now I have Postgres 12 running nicely.  Time to cleanup.
I now have 2 folders in /var/lib/postgresql, called 10 and 12.  The documentation suggests that these folders (or the files within them) are joined by a hard link, but doing the following suggests otherwise:
[deleted output from du that was misleading and did not say what I thought it said]

I'd like to clean this up as best I can, both because space is at a premium, and I know that there is a danger that future me will come along and delete 10 because we're on 12.
In this situation, what do I need, and what can I delete?  How can I make the folder structure as clear as possible to my future self?
Are there things elsewhere I can delete?


texts: Consequences of cascade drop on sequences/functions/views

texts:
I am migrating some data to another server while dropping all sequences/functions/views. When I try to do simply

DROP VIEW | SEQUENCE | FUNCTION | AGGREGATE without CASCADE, errors are thrown to indicate dependencies. The goal is that if no data (=tables?) will be affected by using CASCADE, then I can use it.

Assumption: if no table is dependent on views/functions/sequences, then no table will be affected by using CASCADE.

Then I guess using CASCADE for views will be fine since they always depend on some base tables.

For functions and sequences I am not very sure. I haven't written that many of sql functions myself and I don't know if the data in a table are generated by some function, whether it means the table depends on the function.

For sequences, it seems that a sequence will be generated automatically by postgres if I use serial as column type. My first guess would be that in this case, the table that is using serial depends on the sequence. However, by testing on a dummy database on my laptop, I found that the data seems to be unaffected. (I just use SELECT COUNT(*) FROM TABLE_NAME after dropping the sequence)


texts: Select DISTINCT records with extra information

texts:
I have a MS Access database full of customer names and addresses. I want to send out a mailing but only one per address. Therefore if there are 3 different customers at the address I want only one row. Here is the basic query I have so far:

Group   Fname   Lname   Address

So my first attempt was:

SELECT DISTINCT Customers.[Address 1]
FROM Customers

This works as I wanted and returns only unique addresses. However I also need the group, fname and lname fields. When I add these in I lose the DISTINCT functionality as often customers in the same household will have different Lname and grouping.

Whats the best way to select the data I need but still use DISTINCT on the [Address 1] field.


texts: SQL Server logins "un-sync" when Availability Group fails over

texts:
I have 2 SQL Servers in an Availability Group.  The logins are manually synced – same SID, same Password Hash, etc. on both replicas – and the user can log in via the listener to the primary replica.  (Note: password policy & expiration policy are on.)

Later, the AG fails over to the other replica, and the user now gets a login failure.  The new Primary’s sys.sql_logins table shows a different Password Hash and a different Modify_Date: the date / time of the login failure!    When the original Password Hash is re-migrated to the new Primary, the user can again log in successfully.

Can anyone tell me why the login’s password hash “spontaneously” changes after failover at login time, and how to avoid this happening so the users can log in after a failover?

My hunch is that it has something to do with the password/expiration policy, but that's a requirement of our auditors so can't be turned off.


texts: Should domain "Users" group be removed from the SQL logins for database security?

texts:
I am coming from a security standpoint. I see quite a lot of SQL DBs with the entire domain "Users" group ("XXX Domain\Users") added as a login account in SQL.

Does this mean that all users in the XXX domain can now access the SQL server? Is this a security risk? Will removing it impact my applications?

I have DBAs telling me that this is not a security risk.


texts:
A recent question got me looking at MS Documents and wondering.

Is there a limit to how many backups can be appended to a single file?

By default SQL Server uses NOINIT to append new backups to the old backup file.

  { NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT). Source

The documents clearly say that lack of free disk space will cause an appended backup to fail.

  If a disk file fills while a backup operation is appending a backup to the media set, the backup operation fails. The maximum size of a backup file is determined by the free disk space available on the disk device; therefore, the appropriate size for a backup disk device depends on the size of your backups. Source

The answer at SQL Recover from .bak file with NOINIT indicates that the Position from RESTORE HEADERONLY indicates the individual backup in the file. Which is a "smallint" field, which should max out at 32,767

For the most part when googling around, you find people who are accidently appending their backups, and can't understand why the backups are so big.

I don't find any clear references on how many backups can be appended, assuming sufficient disk space.  Is the limit 32,767 or something else entirely?


texts: Stored Procedure: table-Value parameters

texts:
We have a number of stored procedures where we pass in a user defined table type parameter. As per the MS doc below, my understanding is that SQL Server doesn't retain statistics on these (and therefore should only estimate a single row?).

https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017

Recently, we've seen issues with plan generation where the estimated number of rows in the table is a high as 2000 (normally only 1 or 2 rows are in passed in).  This is caching a plan which is highly inefficient (wrong join order with a largish table ~160M rows) and causing some issues.

Am I wrong here about what the estimates should be? I could force the join type/order but I'd rather avoid this if at all possible.

Would really appreciate any thoughts/feedback people might have.

Thanks
Dave


texts:
Can I feed some SQL that creates a view to Slony?

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

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

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


texts: Extracting a field from RESTORE HEADERONLY

texts:
I'm trying to use 'RESTORE HEADERONLY' to get the date when the backup I'm about to restore was made.

The command:

RESTORE HEADERONLY FROM DISK = '<path to .bak file>'

works fine in Query Analyzer and gives a resultset with something like 50 columns.

The problem is actually accessing this from code.

I can get this into a temp table by declaring every single one of the 50:ish columns, inserting into it with exec and getting the value I want from there.

The problem is that I really want to avoid having to declare the entire resultset as a temp table as it seems like a very brittle solution if they ever add columns to it in future versions.

Is there any way to just get a single column out of this resultset without declaring all the columns?


texts: T-SQL Statement Task choose specific database name on server

texts:
I try to create a simple T-SQL Statement Task by Sql Server 2014 GUI.

My flow:

Right click on Maintenance Plan
Choose Create new Maintenance Plan
Choose the type (T-SQL Statement Task)
I add by toolbox an activity for SQL Statement (a simple SELECT * INTO mytable FROM anothertable)

Now I want to choose the databases where I want to execute my task.

By property I've changed the DatabaseSelectionType to SPECIFIC but I haven't any form to choose database.

If I try with another task type (Index maintenance) I can choose the databases.

Surely, I'm wrong in some steps but I don't understand where.


texts: How to troubleshoot orm queries interfering with other applications

texts:
Well, it's hypothetical scenario but what I'm trying to understand is the path to go from a post-morten log (say, an SQL Server Profiler trace) to identify the code on a ORM situation. To make it not too vague, consider an scenario like that:

SQL Server 2008
Entity Framework as a ORM

So, in that scenario, what is the common path to a DBA (which is also an VB.Net developer) make from the log to diagnosing which code(s) (in this case, Linq queries) are creating the trouble?
In this case, the app is ok but is affecting other apps response time using the same DB/server.

That would be absurdly different from a Java+Hiberate process?

EDIT: I want to understand the path from the trace to the culprit metaquery. If the app have SQL in it, this means that an "Find in Files" session (maybe with some regex, in extreme cases) could reduce the inspect task' targets to some dozen suspects instead of tenths or even hundreds of source files.
Using an ORM, how to get to that stage using an ORM (in this case: EF)?


texts:
I am working on a student project and I need some guidance on what solution I can use.

The goal of the project is to create a dummy web page that would send
dummy data to 2 databases(or read from one of them).
If at any point one of them is unavailable it should keep sending
data to the one that still works.
When the unavailable database returns to function it should check
with the one that was still working, synchronize all changes made to
data and continue on. It should work both ways.
Both DB1 and DB2 can shutdown but when they return to function they
should synchronize with the one that was working.

Professor recommended MS SQL server 2019 Developer, so far I managed to make database replication so any change made to DB1 is replicated on DB2, but I can only send data to DB1 because it doesn't work the other way. I followed this How to Configure MS SQL Server Replication: Complete Walkthrough tutorial. Basically it is transactional replication between 2 SQL Server dbs that run on 2 VMs.
I can't figure out the way to do this so I would appreciate if anyone has any suggestions or tutorials that could help me. One thing that I found and I think would work is MS SQL peer to peer replication, but it is only available for Enterprise edition.


texts: Why would increase in innodb_buffer_pool_size slow down MySQL?

texts:
5.1.68-cll - MySQL Community Server on CentOS

The system has 32GB of RAM.

I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB).

Time taken for a series of identical operations increased from 720 to 822 seconds (14% increase).

This was the result only a single test at each setting. But 4 previous tests performed a few months ago resulted in times between 726 and 740s.

I just tried running it again with 8GB, and the time taken was 719s.

Why would more memory result in a slower process?

EDIT: More details on process

The process that I'm testing involves emptying some tables and rebuilding them from data from existing tables. I'm not sure if it's using SELECT INSERT or if it's SELECTing the data, then using PHP to create long INSERT statements. If that matters then I can find out.

There are no schema definition changes being made.

Here is the output of numactl --hardware while the server is relatively idle:

root@server [~]# numactl --hardware
available: 1 nodes (0)
node 0 cpus: 0 1 2 3 4 5 6 7
node 0 size: 32740 MB
node 0 free: 6216 MB
node distances:
node   0
  0:  10

And free -m

root@server [~]# free -m
             total       used       free     shared    buffers     cached
Mem:         32081      25864       6216          0       2591      12791
-/+ buffers/cache:      10482      21599
Swap:        15994         16      15977

Edit by RolandoMySQLDBA

Please run this query

SELECT
    InnoDBSpace / POWER(1024,1) InnoDB_KB,
    InnoDBSpace / POWER(1024,2) InnoDB_MB,
    InnoDBSpace / POWER(1024,3) InnoDB_GB
FROM
(
    SELECT SUM(data_length+index_length) InnoDBSpace
    FROM information_schema.tables
    WHERE ENGINE='InnoDB'
) A;

RESULT:

InnoDB_KB InnoDB_MB InnoDB_GB
8413536 8216.34375 8.02377319335938

and this one

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages%';

RESULT:

Innodb_buffer_pool_pages_data
410035
Innodb_buffer_pool_pages_dirty
204
Innodb_buffer_pool_pages_flushed
826954
Innodb_buffer_pool_pages_free
99231
Innodb_buffer_pool_pages_misc
15022
Innodb_buffer_pool_pages_total
524288

During running of process:

root@server [~]# numactl --hardware
available: 1 nodes (0)
node 0 cpus: 0 1 2 3 4 5 6 7
node 0 size: 32740 MB
node 0 free: 5461 MB
node distances:
node   0
  0:  10

and:

             total       used       free     shared    buffers     cached
Mem:         32081      26658       5423          0       2603      12948
-/+ buffers/cache:      11106      20975
Swap:        15994         16      15977


texts:
Whenever INSERT is happened in the CUSTOMER table,I need to call the "StoredProcedure1"and
UPDATE is happend in the  CUSTOMER table,I need to call the "StoredProcedure2" in the Trigger.
How to determine if insert or update in the trigger  from SQL Server 2008.

Some one can please help me how to solve?

Code:

CREATE TRIGGER Notifications ON CUSTOMER
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @recordId varchar(20);
set @recordId= new.Id;
    //if trigger is insert at the time I call to SP1
        EXEC StoredProcedure1 @recordId
    //if trigger is Upadeted at the time I call to SP2
        EXEC StoredProcedure2 @recordId
END


texts: Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan

texts:
Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan

-- create objects
CREATE DATABASE ConvertTest
GO

USE ConvertTest
GO

CREATE TABLE Person
(
    VarcharId   NVARCHAR(4),
    IntId       INT
)

-- insert data
INSERT INTO Person
SELECT  TOP 1000
        CONVERT(NVARCHAR(4),ROW_NUMBER() OVER (ORDER BY a.object_id)),
        ROW_NUMBER() OVER (ORDER BY a.object_id)
FROM    sys.objects a
        CROSS JOIN sys.objects b

-- create indexes
CREATE INDEX IX_Varchar ON Person
(
    VarcharId,
    IntId
)

CREATE INDEX IX_Int ON Person
(
    IntId,
    VarcharId
)

DECLARE @id NVARCHAR(4) = 100
-- statement 1
SELECT * FROM Person WHERE VarcharId = @id
-- index seek

-- statement 2
SELECT * FROM Person WHERE IntId = @id
-- index seek
GO

DECLARE @id INT = 100

-- statement 3
SELECT * FROM Person WHERE VarcharId = @id
-- index scan

-- statement 4
SELECT * FROM Person WHERE IntId = @id
-- index seek

Query 3 implicitly converts the VarcharId column to int (which has higher precendence) and this causes the predicate to be non SARGable and thus causes a table scan.

However, when I run a similar test, I did not get the results I expected:

-- create objects
CREATE DATABASE ConvertTest2
GO

USE ConvertTest2
GO

CREATE TABLE Ids
(
    NvarId  NVARCHAR(4),
    VarId   VARCHAR(4)
)

-- insert data
INSERT INTO Ids
SELECT  TOP 1000
        CONVERT(NVARCHAR(4),ROW_NUMBER() OVER (ORDER BY a.object_id)),
        CONVERT(VARCHAR(4),ROW_NUMBER() OVER (ORDER BY a.object_id))
FROM    sys.objects a
        CROSS JOIN sys.objects b

-- create indexes
CREATE INDEX IX_NvarId ON Ids
(
    NvarId,
    VarId
)

CREATE INDEX IX_VarId ON Ids
(
    VarId,
    NvarId
)

DECLARE @id NVARCHAR(4) = N'10'
SELECT * FROM Ids WHERE NvarId = @id
SELECT * FROM Ids WHERE VarId = @id
GO

DECLARE @id VARCHAR(4) = '10'
SELECT * FROM Ids WHERE NvarId = @id
SELECT * FROM Ids WHERE VarId = @id

All four queries show an index seek (although query two runs the seek through a nested loop with a computer scalar)

Given that NVARCHAR has a higher data type precendence than VARCHAR, I expected to see the VarId column implicitly converted to a VARCHAR and therefore causing a table scan.

Why / how does datatype precendence bahave differently when converting between varchar / nvarchar types?


texts: Mysql & multi-tenant db: the best way to organize?

texts:
I've never completed any multi-tenant project before...

What is the best way to organize multi-tenant db using mysql? and to seperate access between different companies?

sure it can be done in a different ways:

different database for each company
different tables for each  company
one db each table with column tenant_id

i prefer 3rd option.

but: is it possible somehow to limit access between different companies data, for example using mysql schema (or something another) and not using back-end code?

i heard something, that in postgresql its possible using schema...

but is this possible in mysql? (for example tenant1 cannot view data of tenant2)


texts: `CHANGE_TRACKING = AUTO` fails to populate full-text index

texts:
I have created full-text indexes on tables with the following options (simplified):
CREATE FULLTEXT INDEX ON [schema].[table]
(
    ColumnA Language 'English',
    ColumnB Language 'English'
)
KEY INDEX pk_table ON database_ft_catalog
WITH
    CHANGE_TRACKING = AUTO,
    STOPLIST = OFF
GO

This works, and rows are searchable after the index builds.
Select Top 100 * from [schema].[table]
  where contains(*, '"searchTerm*"');

My problem is that rows are not automatically populated into the full-text index like they should be.
At one point, I saw in the logs that the full-text daemons died, so I restarted those using sp_fulltext_service 'restart_all_fdhosts'. (sys.fulltext_indexes showed that a full population was still underway.)
This worked for a while, but now new rows are once again not being indexed. This is my latest error message from the Full Text Log:

A full-text retry pass of Auto population started for table or indexed view
Error '0x80004005' occurred during full-text index population for table or indexed view
Informational: Full-text retry pass of Auto population completed for table or indexed view '[db].[schema].[table]'... Number of retry documents processed: 1. Number of documents failed: 1.

Any information on how to fix this would be greatly appreciated. The rows are failing four times, then are ignored after that, i.e., they are no longer tracked and will not make it into the full-text index until I rebuild it.
FYI, I did try to increase the ism_size as listed in this article.  However, that did not fix the issue. (I know that's an old version, but it was worth a shot.)
UPDATE:
I found some additional posts today that said to look in Event Viewer. I'm getting Event ID 30089:

The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during full-text indexing or query processing. The process will be restarted automatically.

The Windows Service (SQL Full-text Filter Daemon Launcher - FDHost) is restarting anywhere between 1 and 30 seconds. The full text service is using the default SQL Server account NT Service\MSSQLFDLauncher, which I made sure has READ & EXECUTE access to the C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn folder and all child objects, which is where sp_help_fulltext_system_components 'wordbreaker' reported the wordbreaker packages were all located.
Still no change.
Update 2019-10-07
It turns out these are using EC2 in AWS, with Amazon's Windows 2016 image. We installed SQL Server Standard 2017 ourselves, though.  It looks like the AMI is Windows_Server-2016-English-Full-Base-2019.07.12
Update 2019-10-08
As I'm in a testing environment, I'm using SQL Server Edition. The database is at compatibility level 140 (2017) and the SQL Server instance and databases are all fresh installs, no upgrades.
The database file is set to unrestricted max size, logs file is set to max of 2TB, both grow by 64MB. The database file is currently at 1.288 GB and the logs are 776 MB. There is enough room on the configured disk for it to grow another 20-40 GB.
One tweak I could make for performance issues is put the full text indexes on a separate disk, but I'm currently the only one using this database. I just want the process to complete without errors and be able to add newly inserted or modified records to the index.
Note that my primary key is an identity column per performance recommendations, and all of my columns are built-in data types (varchar(255) seems to be the max), and that these tables have system versioning enabled, if that affects anything.


texts: Sync update during semi-sync/async replication

texts: Using MySQL, eventual consistency is realized within semi-sync/async replication. But is there any way to force strong consistency (while using semi-sync/async replication) for some concrete INSERTS/UPDATES? If not, maybe I should provide any kind of middleman storage? What is the practical approach?


texts:
I have a DELETE statement running against a table with a full-text indexed column, a handful of foreign keys with cascade enabled. It looks like this:

DELETE FROM dbo.STUDENTS WHERE STUDENTID=@STUDENTID

Occasionally a plan is compiled which includes very high row estimates for all index operations, such that the DELETE takes a very long time and causes locking.

I have attempted to force a good plan in QueryStore but this doesn't actually work,  showing last forced plan failure description of NO_PLAN.

I have ensured that there are no schema changes that could be invalidating the plan.

Looking at the execution plan, I see that the DELETE involves a join to a system table holding the FT index:

Does this join to the FT index mean that plan-forcing is not supported?


texts:
This is a question regarding general DB inner workings, not particular to an implementation or paradigm, though answers for certain technologies are welcome.
I am asking if there is a way to listen to what commands the database has received, or something like read an inner log of the database, at least the last changes.
I need such a functionality to be able to find if there were changes to a table and if so, read the particular row that has changed. It is assumed changes to the columns are not to happen.
I am only a listener of the database, hence I am not able to program triggers.


texts: I Don't have PFILE or SPFILE just have a backup of init.ora and database is down

texts:
I have lost PFILE  and don't have backup also, just have a backup of init.ora and database is down and not starting up. Does init.ora backup is useful? If yes then how to use it to create pfile or to startup database??
please any solution,
any help will be highly appreciated.


texts: Can I merge duplicate results into a single result? (SQL)

texts:
This is my query:

SELECT
    CONCAT((SELECT LEFT (Event_Start_Time, LEN(Event_Start_Time) -3)),
            ' ','-',' ',
           (SELECT LEFT (Event_End_Time, LEN(Event_End_Time) -3))) AS 'Time',
    CASE WHEN Course_Code IS NULL
        THEN Course_Title
        ELSE Course_Code
    END AS 'Course',
    CASE WHEN User_Last_Name
           IN ('Faculty', 'Students', 'Study') OR Course_Title = 'Meeting'
        THEN NULL
        ELSE CONCAT(substring(User_First_Name, 1, 1), '. ', User_Last_Name)
    END AS 'Instructor',
    Room_Name AS 'Room'
FROM
    Events
    JOIN Courses ON Courses.Course_ID = Events.Course_ID
    JOIN Users   ON Users.User_ID = Events.User_ID
    JOIN Rooms   ON Rooms.Room_ID = Events.Room_ID
WHERE
    Events.Event_Date = (SELECT CAST(GETDATE()AS Date))
ORDER BY
    Event_Start_Time, Event_End_Time, Users.User_Last_Name;

Current Output:

08:00 - 11:30     NURS3405 F.Lastname1     LabA
08:00 - 11:30     NURS3405 F.Lastname2     LabB
08:00 - 11:30     NURS3405 F.Lastname3     LabC
08:00 - 11:30     NURS3405 F.Lastname4     LabD

Expected output:

 08:00 - 11:30 NURS3405 F.Lastname1 LabA
                        F.Lastname2 LabB
                        F.Lastname3 LabC
                        F.Lastname4 LabD

And just be one row? I'm not sure if this is possible.


texts:
When is the SGA created in an Oracle database environment?

A.) When the database is created

B.) When a user process is started

C.) When the database is mounted

D.) When the instance is started

E.) When a server process is started

Answer given is D.) When the instance is started

But oracle can have multiple database and system global has shared pool+ log file+control file for each and every database.

I am Confused and expect the correct answer as A.) When the database is created.

Please explain if I'm wrong.


texts: ALTER TABLE engine from MyISAM to InnoDB: why does mysqldump --order-by-primary help me?

texts:
I found an interesting mysqldump option in MySQL 5.7 Reference Manual. In internet there are no further details and explanations.

--order-by-primary

  Dump each table's rows sorted by its primary key, or by its first
  unique index, if such an index exists. This is useful when dumping a
  MyISAM table to be loaded into an InnoDB table, but makes the dump
  operation take considerably longer.

Can you explain me why this helps in migrating MyISAM to InnoDB? How this works?


texts: Why Mysql Fabric is end of life, and what is an alternative?

texts:
Recently I am looking for an application level Sharding solution for MySql, and come across Mysql Fabric, but after some investigation, I find it was already announced to be end of life. It seems Mysql Fabric is not suggested to use any more. If so, my questions are,

Is Mysql Fabric ever a mature tool for production usage?
Are there any successful examples of using Mysql Fabric for sharding?
Is there any specific reason Mysql Fabric is no longer maintained since 2017?
What could be alternatives?


texts:
I am setting up a mysql master-master GTID replication. I configured all prerequisites and my current replication config looks like
Node 1:
CHANGE MASTER TO MASTER_HOST = 'NODE2_IP',MASTER_PORT = 3306,MASTER_USER = 'repl_user',MASTER_PASSWORD = 'password',MASTER_AUTO_POSITION = 1;

Node 2:
CHANGE MASTER TO MASTER_HOST = 'NODE1_IP',MASTER_PORT = 3306,MASTER_USER = 'repl_user',MASTER_PASSWORD = 'password',MASTER_AUTO_POSITION = 1;

Is it possible to use a dedicated network for replication traffic. What are the prerequisites for that and what are the changes required from mysql replication perspective.
Thanks in advance


texts: Why do MySQL MyISAM table indexes go out of date?

texts:
I have few MyISAM tables, where only the auto_increment primary key is up to date with the number of columns, but not other indexes.

Any idea, why this happens?


texts: MySQL: Shouldn't these two DATE queries be the same?

texts:
I have this query, it tells me what order has expired, immediately after it expires.
(
  SELECT
    *
  FROM
    orders as ord
  WHERE
    DATE_ADD(paid_on, INTERVAL ord.duration HOUR) < NOW()
    AND DATE(DATE_ADD(paid_on, INTERVAL ord.duration HOUR)) BETWEEN DATE(NOW() - INTERVAL 5 DAY)
    AND DATE(NOW())
)
UNION
  (
    SELECT
      *
    FROM
      orders as ord
    WHERE
      DATE_ADD(paid_on, INTERVAL ord.extended_duration HOUR) < NOW()
      AND DATE(
        DATE_ADD(paid_on, INTERVAL ord.extended_duration HOUR)
      ) BETWEEN DATE(NOW() - INTERVAL 5 DAY)
      AND DATE(NOW())
  )

I have decided to optimize it by rewriting it like this
(
  SELECT
    *
  FROM
    orders as ord
  WHERE
    DATE_ADD(ord.paid_on, INTERVAL ord.duration HOUR) < NOW()
    AND DATE_ADD(ord.paid_on, INTERVAL ord.duration HOUR) BETWEEN (CURDATE() - INTERVAL 5 DAY)
    AND CURDATE()
)
UNION
  (
    SELECT
      *
    FROM
      orders as ord
    WHERE
      DATE_ADD(ord.paid_on, INTERVAL ord.extended_duration HOUR) < NOW()
      AND DATE_ADD(ord.paid_on, INTERVAL ord.extended_duration HOUR) BETWEEN (CURDATE() - INTERVAL 5 DAY)
      AND CURDATE()
  )

All I did is using CURDATE() instead of DATE(NOW()), but the queries are retrieving different results, the first query is working correctly,  the second query sometimes waits 24h before showing something as expired. DBFiddle


texts: Replicate MySQL INSERT to SQL Server table

texts:
I'm running into a challenge here trying to "sync" a MySQL table with a SQL Server table. My scenario involves my linux-based website submitting form data submissions to a MySQL database on that web server.

What I'm shooting for is to copy any inserts on the MySQL table to the SQL Server table, and I'm not finding a whole lot of support for this. I'm using SQL Server Management Studio and I have my SQL Server database and I've added the MySQL as a linked server.

I had the thought of creating a trigger on that linked server, but it doesn't look like that will work coming from the SQL Server side.

Does anyone have any experience with this? I'm looking for the "best practice" scenario for this type of situation. The syncing between these tables needs to be as close to real time as possible.

Thanks!


texts: Select All Field (if field not exist, make field with null value)

texts:
I have a table with this field and condition
table cc_queue_detail
ticket_id    field_name_id             data_content
1            category_ticket_1         33
1            category_ticket_2         44
2            category_ticket_1         55

I want to select for table cc_queue_detail and make them into like this:
ticket_id   category_ticket_1    category_ticket_2
1           33                   44
2           55

i haved tried this query, but its only selected with the ticket_id 1
SELECT DISTINCT
a1.ticket_id AS 'ticket_id',
a2.data_content AS 'category_ticket_1',
a3.data_content AS 'category_ticket_2'
FROM
cc_queue_detail a1
JOIN cc_queue_detail a2 ON a1.ticket_id = a2.ticket_id
JOIN cc_queue_detail a3 ON a1.ticket_id = a3.ticket_id
WHERE a2.field_name_id = "category_ticket_1"
AND a3.field_name_id = "category_ticket_2"

result query:
ticket_id   category_ticket_1    category_ticket_2
1           33                   44

i tried change  AND a3.field_name_id = "category_ticket_2" into OR a3.field_name_id = "category_ticket_2" but it not work.
can someone help me to fixed this?


texts: SQL Server Always On and Replication Merge

texts:
How can replication Merge be done in a highly available environment? I tried to create the publisher in a partially contained database and it generates the error 21147 of sql,

Can the Express version of the SQL server be within a high availability group in the secondary nodes?

not have my database partially contained within a group of high availability that has consequences?

I hope you can help me with this topic, I thank you in advance for any contribution.


texts: SQL Server: query performance (search 2 million rows)

texts:
Got an interesting one for all of you SQL guru's out there. Now this search is only taking a couple of seconds, but it's quite intensive and there must be a better way. Maybe I'm expecting too much?

Simple holiday search app. 2 million holidays. Paging/Sorting around 600,000 rows.

This is the schema of the table

CREATE TABLE [dbo].[Holiday](
        [Id] [int] NOT NULL,
        [PropertyId] [int] NOT NULL,
        [Price] [int] NOT NULL,
        [Rating] [int] NOT NULL,
        [Country] [char](2) NOT NULL,
        [ResortId] [int] NOT NULL,
        [DepartureAirport] [char](3) NOT NULL,
        [DestinationAirport] [char](3) NOT NULL,
        [DepartureDate] [datetime] NOT NULL,
        [Basis] [char](2) NOT NULL,
        [Duration] [int] NOT NULL,

     CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED ([Id] ASC)
  )

As you can see, quite simple. We have a property, a price, duration, departure/destination airports, etc. Now, the more field that are provided the faster the search. If I have a Departure Airport, Property and Date then the search is very fast. However, if I just have a Country and nothing else, there is a lot of data to work through.

Using this CSV export of my table, there are 2 million rows in total, and around 666k just with a country code of FR, which is my example.

This, is the search query. Which returns two tables. The first is a summary, so total number of holidays that match your criteria and how many unique properties. The second table contains the actual results from the search.

--Build a temp table, and store everything we need in it
CREATE TABLE #Pricing (PropertyId int, Duration int, HolidayId int, Rating int, Price int, StartDate datetime, PropertyRow int);

INSERT INTO #Pricing
  SELECT
    PropertyId, Duration, [Id], [Rating], [Price], DepartureDate,
    ROW_NUMBER() OVER (PARTITION BY PropertyId ORDER BY Price ASC) as PropertyRow
  FROM
    dbo.Holiday
  WHERE
    DepartureDate > GETDATE() AND Country = 'FR'

--Get a total number of holidays, and total number of properties
SELECT
    COUNT(*) AS TotalHolidaysCount,
    COUNT(DISTINCT PropertyId) AS PropertyCount
FROM
    #Pricing

--Build the final table, which will contain all the holidays we actually want to return
DECLARE @FinalResults TABLE (HolidayId int, RowNumber int);

INSERT INTO
    @FinalResults
  SELECT
    HolidayId, RowNumber
  FROM
    (SELECT
         PropertyRow, HolidayId,
         ROW_NUMBER() OVER (order by (CASE WHEN StartDate <= '01/Apr/2013' THEN 1 ELSE 0 END) ASC, [Price] ASC) as RowNumber
     FROM
        #Pricing
     WHERE
        PropertyRow = 1) as SearchResults
WHERE
    (RowNumber > (10 * (1 - 1)) and RowNumber <= (1 * 10))
ORDER BY
    RowNumber;

SELECT
     *
FROM
    @FinalResults
    INNER JOIN dbo.Holiday ON HolidayId = Holiday.Id

DROP TABLE #Pricing

Now, I could look into Indexing which would obviously improve performance. But what worries me, is the incredible use of Temporary tables. Surely this shouldn't be how it's done? Taking 5 seconds to search through what is ultimately a tiny amount of data. They only reason they are used is because of the need to reference the data later.

Would it maybe be worth running the query twice, instead of storing all of the data in memory? It seems a waste to select over 25% of the table into memory again and again.

Any helpful feedback would be appreciated. Not looking for the 'answer', just some assistance.

Many thanks,
Dean


texts: Relation between first name and last name

texts:
I have some troubles understanding the relation between the a person's First name and Last name in a table. (In a Relational Model)

I've read that the relation is of type: 1-1:
They are linked together.

But the same first name can be attributed to multiple persons. And a single person may have multiple first names...

What I understood is:

  Even if "John Doe" has many homonyms around the world, in a database,
  his first name would be directly linked to his last name.

  To avoid this question, we can regroup the last name and first name in
  a single row but that would end with humans problems (Formatting
  errors: Which order, in capital letters ? separed by spaces or
  comas... ?)

  In the case of a mail generation (for example), I should use the following format:
  Title + Last Name (Mr. Doe) instead of (Mr. DOE John).

  Then I should atomize my two values.

This reflexion should seems useless as my research should be based on ID or others criterias but as I'm still learning, I try to understand the best way possible each details of a databases's conception.


texts: What circumstances could a SQL DateTime column hold an invalid date?

texts:
I have been asked to write a series of tests within SQL.
Many of them are to test if a certain DateTime columns held in the system is a valid date.

Given that the all the fields in question are Datetime column and any changes made to them by end-users are through a GUI front end that already has date validation tests, not to mention that the SQL connector would through up an error if an invalid was passed through anyway.

My question is that under what circumstances could a DateTime column end up holding an invalid date, i.e 20/40/2018.

We are running SQL Server 2008

Kind regards

Matt


texts: Can converting MyISAM to InnoDB cause problems?

texts:
I am changing an old MySQL database table which is still in MyISAM. I'd like to change it to InnoDB, but I don't know whether it can cause problems and it's a pretty important table so I don't want to break anything.

So my question: can it cause problems when I convert a MyISAM table to InnoDB?


texts:
Throughout MS documentation for AlwaysOn Availability Groups (AG) and AlwaysOn Failover Cluster Instances (FCI) I see the following pattern:

FCI is for HA scenarios.
An AG synchronous secondary replica, co-located with the primary, is for HA scenarios.
An AG asynchronous secondary replica, in a different datacenter, is for DR scenarios.

Here is an example MS link discussing this.
Since option #1 and #2 are both for HA scenarios, how do I decide between them?
If MS published costs and RPO/RTO metrics for both #1 and #2, it would be fairly easy to decide which I want.
Or perhaps there is a different way to understand the ROI differences between these options.  For example, perhaps option #2 is best suited for VLDB's and option #1 is best suited for very high transaction volumes.  I don't know.
So again, what is the selection criteria a DBA uses to choose between options #1 and #2?
To further complicate things, I know that options #1 and #2 can be combined!  When is it wise to combine the two options?  When is it pointless to combine the two options?  I know when these options are combined, the AG no longer supports automatic fail-over.  It's interesting trivia, but doesn't answer my question.
Incidentally, I intend on provisioning my final solution into Azure IaaS.  If I use an Always On FCI, I will likely create the quasi-SAN using Storage Spaces Direct (S2D).
Update
I've found two articles that give a comparison.  The first is MS docs, and the other is Choosing the Right Availability Tech.  Both have a chart like this:
╔═════════════════════════════╦══════════════════════════╗
║ FCI                         ║ AG                       ║
╠═════════════════════════════╬══════════════════════════╣
║  * Server Level             ║  * Database Level        ║
╠═════════════════════════════╬══════════════════════════╣
║  * Requires shared storage  ║  * Uses direct           ║
║     (SAN or Storage Spaces  ║     attached storage     ║
║     Direct)                 ║                          ║
╠═════════════════════════════╬══════════════════════════╣
║  * RTO from 30              ║  * RTO typically less    ║
║     seconds to 20 minutes.  ║     than 30 seconds      ║
╠═════════════════════════════╬══════════════════════════╣
║  * RPO: no data loss.       ║  * RPO: ???              ║
╠═════════════════════════════╬══════════════════════════╣
║  * Only Passive Secondaries ║  * Active or Passive     ║
║                             ║     Secondaries          ║
╠═════════════════════════════╬══════════════════════════╣
║  * One SQL Server           ║  * Multiple SQL Server   ║
║     instance/license        ║     instances / licenses ║
╚═════════════════════════════╩══════════════════════════╝

The article didn't comment on AG RPO, but I read elsewhere that there is no data loss when recovering from a synchronous secondary replica.  I don't know if that is true and I don't know what the RPO of asynchronous secondary replica might be.  In Azure there is an AG resource that requires two domain controllers are created with the AG (regardless if you have your own domain controllers).  I don't know if an Azure FCI has the same heavy-handed requirement.
Most significantly - I still don't know why it is valuable to combine the two techniques.  I've only read that it "improves" or "maximizes" availability.  That is a vague claim IMO.
More trivia: I also spotted a discussion suggesting that an FCI of two nodes should be avoided.


texts: how to access hadoop slaves which have the same IP address but different port numbers

texts:
My question is, I'm wondering if it's possible Hadoop can do ssh access with a different port number(not 22) for slaves when it makes a Hadoop cluster.

Detail)
I tried to make Hadoop cluster with two slaves.
The master node is on my local computer, but the other nodes are on my AWS ec2 as Docker containers.

local : master node
AWS EC2(33.162.168.105) : first Hadoop container as slave #1, second Hadoop container as slave #2
(btw, this ip addr is fake.)

The first slave container opened 1111 port number for 22 with -p 1111:22 option,
The second slave container opened 1112 port number for 22 with -p 1112:22 option
so that remote computer can do ssh access into slave#1 with 1111 port number.

Actually, when I tried to do ssh access into slave#1 with 1111 port number from my local computer terminal, it worked well!

The problem is Hadoop ssh connection as slaves to make cluster.
I'm using Hadoop 3.1.1 version so I had to put slaves ip into /etc/hadoop/workers like,

33.162.168.105:1111

But after run start-all.sh, I showed errors.

server@9130cf720e0f:/usr/local/hadoop/etc/hadoop# start-all.sh
Starting namenodes on [master]
Starting datanodes
33.162.168.105:1111: ssh: Could not resolve hostname 33.162.168.105:1111: Name or service not known
Starting secondary namenodes [9130cf720e0f]
Starting resourcemanager
Starting nodemanagers
33.162.168.105:1111: ssh: Could not resolve hostname 33.162.168.105:1111: Name or service not known

I know I can't add ":1111" at the end of the ip addr.
So I really wanna know if it's possible Hadoop can do ssh access with a different port number(1111 in this case) for slaves when it makes a Hadoop cluster.
If you know how to, help me, please.

Thank you for your reading and help in advance.


texts: Obtain the sum of count of multiple schema and tables in one query in mysql

texts:
The query belows return 4 rows and it's good. But i need also to sum the results of all the counts in the same query. How to achieve it? I've tried different things but i get sintax errors.
SELECT COUNT(*) FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1


texts:
I want to have a table column which will be filled with wildcard-type DNS entries, like

*.example.org
*.popularisp.net

such that I can make fast queries against actual reverse DNS hostnames, matching foobar.example.org to the first one above and 12-34-56-78.routers.popularisp.net to the second, for instance.

What is the best way to achieve this in a PostgreSQL index?


texts: Oracle Script For Getting Long Active Sessions

texts:
Is there any script that you could provide for v$session? I seem to be encountering an issue in which my oracle active sessions are filled up. This is when I do

SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE';

Here is my problem : I need a script which could determine how long the connection was held and who was holding it.

I wonder where I'm getting it. If possible, it would be really nice if I could autodial these sessions. I'd like to know how long they were held since the last command was done. Another thing to note: There is no current statement associated with the SQL sessions I'm seeing.

Thanks


texts: Arithmetic Overflow at RunningPackages (SSIS) problem

texts:
I've developed an SSIS Package with the C# Code.All the Package were running fine.But for the past 2 days, we are experiencing

public void Main()
        {
            // TODO: Add your code here
            try
            {
                MessageBox.Show("PAckage Starts");
                Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

                MessageBox.Show("BEFORE ERROR");

                string a=Dts.Variables["System::MachineName"].Value.ToString();
                RunningPackages pkgs = app.GetRunningPackages(null);
               RunningPackages abc = app.GetRunningPackages(a);
                MessageBox.Show("AFTER ERROR");
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                MessageBox.Show(e.Message.ToString());
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

This is my Stack Trace.
I've already browsed regarding this issue but not getting any solution. Some Reference here.

Investigating into this with Process Monitor tool. The Issue is coming from MsDtsSrvr.dll (C:\Program Files (x86)\Microsoft SQL Server\DTS\Binn\MsDtsSrvr.dll)

Arithmetic operation resulted in an overflow. (MsDtsSrvr)

Unfortunately, this Issue is not happening in other Environments.

Anybody, please guide me to solve this error

Thanks in advance,
Jay


texts:
A while back, a friend told me of a facility that would allow us to "shadow" a production database using a local database. The term shadow may be incorrect (so please correct me if so)

What I understand, and want to do is:

When I make an update / insert, it only impacts the database that is local.
I make a select statement, the return is first the data from the database that is shadowing, then the production data is filled in.
table modifications only impact the database that is shadowing
clear out (roll back transactions?) to get the local to a neutral point.
would prefer not to have to copy the database over, but pull from the live data if possible.

Since this is not designed for production, performance is not really an issue.

The use case in mind is that I would have said database for local / testing, and a quick way to revert all the changes that I had done, if they need to be.