texts: Select ID from costumers that do not have contacts last 7 days and 2 times in the month

texts:
I have 2 tables: custumer, c_contact.
c_contact is all the e-mails I send to my custumers.
From now on I need to put a new rule that the costumer can't receive a new e-mail if:
1) It received an e-mail in the last 7 days
2) Have 2 or more e-mails sent in the current month

I thought something like that:

SELECT *
from costumer c inner join c_contact cc on cc.ID = c.ID
WHERE ID not in (select ID
                 from c_contact c1
                 where c1.ID = cc.ID
                   and c1.CONTDATE >= getdate()-7)
  AND ID not in (select count(ID)
                 from c_contact
                 where MONTH(contdate) = MONTH(getdate())
                   and YEAR(contdate) = YEAR(getdate())
                 HAVING count(ID) >= 2)

But the table c_contact is huge and it taking ages to run this.
Is there a way to do these 2 conditions in 1 "ID not in"? I thing it will run a lot faster.


texts: Integrated Security Success on Master, Fails on Other Database

texts:
I'm a bit perplexed as to why my connection string is selectively succeeding on the master database in SQL Server 2005. Here are the details of my app. Any ideas where to look? I'm clearly missing something. Also keep in mind that the IIS Server and SQL Server are on different machines within an Intranet environment where I am attempting to implement Windows Authentication. Thanks. :)

Connection strings:

Server=SQLServer;Database=master;Integrated Security=true --succeeds
Server=SQLServer;Database=other;Integrated Security=true --fails

Fail Error Message:

Cannot open database "other" requested by the login. The login failed.
Login failed for user 'DOMAIN\IIS_SERVERNAME$'.

My IIS Server App Pool and Authentication settings:

AppPool: .NET 4.0; Integrated Pipeline; Identity=Network Service
Authentication: Impersonation=Disabled; Windows Auth=Enabled; Kernel-mode=Enabled; Providers=Negotiate,NTLM

Update: Clearly there is a Login of some sort for the machine connecting to my SQL Server with the Integrated Security, otherwise it wouldn't succeed in connecting to the master database right? I would guess that to mean there's an issue at the database level, but I cannot figure out which user account would give it access to master because there are only a handful of users for it.


texts: MySQL Master-Master Replication Table Row COUNT() Inconsistency

texts:
I have two MySQL servers replicating to each other in a Master-Master typology.

This has been working fine for several months now.

Recently I've noticed some drift in one Master. There is a row COUNT(*) inconsistency on one table only in the database. The other tables in the database return the same row COUNT(*) on both Masters.

This table has approx. 120,000 rows and seems to be short by about ~80 rows after replicating from Master #1 (M1) to Master #2 (M2).

I have tried the following:

Duplicate table T1 on M1 to create T2. Then run count after T2 has replicated from M1 to M2. The count on M1 is higher than on M2.
Duplicating table T2 on M1 to create T3. Then run count after T3 has replicated from M1 to M2. The count is again short. The count on M1 is higher than on M2.
Duplicating table T3 on M2 to create T4. Then run count after T4 has replicated from M2 to M1. The count on M1 is higher than on M2.

Any clues as to why Master #1 (M1) appears to be able to count more rows in the same table each time?


texts: What is the exact Query To add two rows from different coloums?


texts: Running remote stored procedure with DDL locally

texts:
Sorry if my question is redundant, I searched Stack Exchange and the internet for hours but do not have a definitive answer to my question.

Database: SQL Server 2017 + Azure

The goal is to build a BI infrastructure

I have multiple (20+) database servers (clients) which all have one common database in the cloud (Azure). All these clients should be exactly the same when these are on the same DB version. To make life easier when doing an update (almost weekly, tuning), I want my 20+ databases to update automatically.

I thought of the following solution:

I create a table in the Azure DB which has the most recent client DB version (e.g. v1.2) check
If I have an update script, I will put this as a stored procedure on the Azure DB. check
I schedule a task on the client which checks the most recent client DB version once a day and compares whether an update is needed. check
If yes -> run the "update": this is the stored procedure on the Azure DB which have to run on the client DB. oh no

Here I get into trouble, running the stored procedure which resides on the Azure DB obviously runs the stored procedure on the Azure DB. I don't know how I can run the stored procedure of the Azure DB on the client DB.

Motivation: I thought it would be clever to do it this way so I only have to change the content of the stored procedure and all clients would update itself when getting a connection with the Azure DB.

Also, is this the correct way of tackling this problem? Any help is much appreciated!

If you need more information, please let me know.


texts: Create Range Partition on existing large MySQL table

texts:
I have inherited a database that contains a large table (relative to the rest of the database - 10 million plus rows) of auditing data that needs to be retained for 60 days. Anything older than 60 days can be deleted.

I'm looking to partition this table by range (preferably by day), then run an event to keep things tidy by dropping partitions outside of the retention period.

The data in my table goes back around 6 months, so the first thing I'm going to do is run a manual delete on anything older than 60 days so I''m starting with a clean slate.

Then I need to create the partitions, but do I really have to list all my partitions in the alter table statement? i.e.

ALTER TABLE AuditData
PARTITION BY RANGE( TO_DAYS(date) ) (
PARTITION part1 VALUES LESS THAN( TO_DAYS('2018-01-01 00:00:00') ),
PARTITION part2 VALUES LESS THAN( TO_DAYS('2018-02-01 00:00:00') ),
PARTITION part3 VALUES LESS THAN( TO_DAYS('2018-03-01 00:00:00') ),
PARTITION part4 VALUES LESS THAN( TO_DAYS('2018-04-01 00:00:00') ),
PARTITION part5 VALUES LESS THAN( TO_DAYS('2018-05-01 00:00:00') ),

... and so on

Is there a way to create the partition just by saying, partition the existing data by day?

I understand I will need to run a daily job to create a partition.

Thanks


texts:
My employer is currently using a database build in Borland Visual dBase 5.5 that has been running on a Windows 95 machine for the last 20 years. I am trying to figure out my best course of action to bring this database into the 21st century, but my knowledge of databases is beginner at best and I'm looking for advice. The problem is compounded by the fact that the original designer had a massive stroke before she was able to complete it. It was functional enough for my employer to use it and he has been by using work-arounds for the few things that just do not function 100% correctly. There are a good number of quality of life features that I'd like to implement as well.

Current options I've come up with:

Move the database to a virtual machine on my Win10 machine, learn Visual dBase 5.5 and attempt to fix all the things that do not work correctly. Pros: Would not be starting from scratch. Cons: My understanding of VMs is that there will be difficulties transferring data from VM to host since the VM OS is pre-Win 2000.
Learn a newer database system and rebuild the database, hopefully being able to import the existing data. Re-entering data is an option. It would be me doing it, I don't want to but I will. Pros: Will be functional on current OS and database system/software will still have support. Cons: A lot more work creating the database and possibly re-entering data.

Other info that may be helpful:

There are multiple databases, two of which have 13,000 records each and are currently gaining 1,000-1,500 new records per database per year.
We are using the native Crystal Reports for direct printing of documents, but I would like the ability to edit the individual documents prior to printing when necessary if possible.

I have some programming background, mostly C++, but no real database creation/support knowledge. However, I'm willing to dive into whatever I need to work on this project, just not sure what is the best route at this point. I'm also open to suggestions for software/languages if I need to just start over.

Any suggestions are greatly appreciated!


texts: Is it safe, and how can I delete mysql.sock before mysqld start?

texts:
I have CentOS running on virtual machine on my desktop. When my desktop unexpectedly restarts (blue screen, power failure, restart after updates), virtual machine faces the same problem - unexpected shutdown.
When VM starts again, /var/lib/mysql/=mysql.sock file (0 bytes) should be deleted, then mysqld service can be started.

Is there any way to avoid manual deletion of this file? Boot script


texts: sp_update_schedule says @schedule_id does not exist

texts:
I wrote a script that reads job schedules from msdb.dbo.sysschedules and runs sp_update_schedule to change the active_start_time for a given subset of jobs. In each case, sp_update_schedule fails with the message "The specified @schedule_id('xxx') does not exist".  (MessageID 14262, Severity 16)
Is the schedule id in sysschedules not what it is looking for? What am I missing here?


texts: Migrating TDE Protected Database - Cert Migrated, Manual Copy Works, But Not Copy Wizard

texts:
I'm attempting to migrate several DB from an SQL 2014 to 2016 server. They all have TDE enabled, and are protected by a certificate.

I successfully copied the certificate, and I am able to manually export and import the database without issues. However when attempting to use the "Tasks > Copy Database" Wizard from the SQL Management Studio, I receive the error

"InnerException-->Cannot change database encryption state because no database encryption key is set."

Everything I can find on this says the error is a result of the encryption key/cert not being copied across properly, however I assume the fact I am able to manually export/import the DB means the cert is copied and working?

I believe the copy wizard will copy DB & server accounts without orphaning anything or requiring password changes, and the manual import/export will result in orphaned accounts and require password changes. As well as just the fact that using the Copy Wizard will be routinely simpler.

What am I missing?


texts:
I would like to output some basic metric information about my MySQL database.  Such as the names and number of rows for all tables and views in a given database.  Tables are no problem as I simply have to query the information_schema.tables.table_rows, but views pose a different problem as most all it's results are all NULL in information_schema.tables.

Does anyone have a good method besides writing a procedure that queries each view each time it is called?  To do so each time is highly inefficient.  I would assume the database engine would store this information for performance reasons.


texts: ola hallengren Index Maintenance script - GUID Index

texts:
We been running ola hallengren Index Maintenance script and was ran into a problem with some indexes that have GUID as the Index Key, They are Frag and have a page count of 6,309, but they don’t get scanned to be rebuild or reorg.

Has anyone heard of this or know of a fix for this?

/****** Object:  Index [idx_GUID]    Script Date: 01/14/2015 10:54:47 ******/
CREATE NONCLUSTERED INDEX [idx_GUID] ON [dbo].[ckbx_InvitationRecipients]
(
      [GUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]


texts: Which SQL Power Architect type to specify for [PostgreSQL] UUIDs/GUIDs in new column property template/UI?

texts:
I have inherited maintaining and extending SQL Power Architect projects in source control which are used to generate the DDL for project DB schemas. We are currently using PostgreSQL 9.5 and SQL Power Architect 1.0.8. (We are using SQL Power Architect to attempt to keep the schema DB-agnostic, in the event the project will eventually migrate to a different type of DB.)

If it is possible to specify UUID columns (aka GUIDs) in Power Architect for table schemas we generate, can anyone tell me which Type needs to specified for the column property of those provided in the Power Architect UI's type list? (ARRAY, DISTINCT, LONGNVARCHAR/LONGVARCHAR, VARCHAR/NVARCHAR, OTHER, UNKNOWN, etc.)
Do any of these equate to/generate a UUID column?


texts: Find and replace/wrap certain terms in html tags

texts:
I'm trying to replace certain text in a html string with a html anchor tag surrounding that text, I've tried using REPLACE to simply find and replace the term but then it ends up finding matches inside words and html attributes which I don't want. The closest I've got to it actually working is using PATINDEX and STUFF shown in the example below however it only replaces the first occurrence and it replaces the boundary characters (i.e. the [ ,.] chars) as well as the actual term.

Example

DECLARE @start TABLE (
    id INT IDENTITY(1,1) PRIMARY KEY,
    val VARCHAR(100) NOT NULL
);

DECLARE @end TABLE (
    id INT PRIMARY KEY,
    val VARCHAR(100) NOT NULL
);

INSERT INTO @start
VALUES ('change me plz'),('change me and me plz too'),('dont change this meme'),('nope'),('not me and change me')

DECLARE @searchTerm VARCHAR(50) = 'me';
DECLARE @replaceTerm VARCHAR(50) = 'me';
DECLARE @pattern VARCHAR(50) = '%[ ,.]' + @searchTerm + '[ ,.]%';
DECLARE @currentVal VARCHAR(100)
DECLARE @currentId INT = (
    SELECT TOP 1 id FROM @start WHERE val like @pattern
);

SELECT * FROM @start

WHILE(@currentId IS NOT NULL)
BEGIN
PRINT @currentId;
    SELECT @currentVal = STUFF(val, PATINDEX(@pattern, val), LEN(@searchTerm)+2, @replaceTerm)
    FROM @start
    WHERE id = @currentId

    INSERT INTO @end (id, val)
    VALUES (@currentId, @currentVal)

    DELETE FROM @start
    WHERE
    id = @currentId

    SET @currentId = (
        SELECT TOP 1 id FROM @start WHERE val like @pattern
    );
END

SELECT * FROM @end

tl;dr;

how do you add html tags around text in a html string so that the html is still valid and you only replace the whole word/term not substrings of other words


texts: SQL Partition Function without actual values

texts:
I would like to partition a column by fiscalyear. How Do I create a partition function without harding coding values?  This is what I currently have below.

create table dbo.CustomerAccount
(
    CustomerAccountId bigint primary key identity(1,1),
    CustomerId int not null,
    FiscalYear int,
    AccountBalance numeric(15,2)
)

How Do I create a partition function without harding coding values?

CREATE PARTITION FUNCTION DateRangePF (datetime)
AS RANGE RIGHT FOR VALUES ('2015', '2016','2017')


texts:
I'd like to get a count(*) from a relatively small joined table (returned result is about ~30000 rows). I use Postgresql 9.3 so simply getting select count(*) from tasks_task; uses index-only scan, but joined query performs seq scan instead.

Simple-case scenario: explain analyze select count(*) from tasks_task;

Aggregate  (cost=875.99..876.00 rows=1 width=0) (actual time=6.284..6.284 rows=1 loops=1)
->  Index Only Scan using tasks_task_id on tasks_task  (cost=0.29..800.03 rows=30383 width=0) (actual time=0.042..3.944 rows=30383 loops=1)
     Heap Fetches: 0
Total runtime: 6.366 ms

The actual query:

SELECT COUNT(*)
FROM "tasks_task"
LEFT OUTER JOIN "taskaccess_groupaccessible" ON ("tasks_task"."id" = "taskaccess_groupaccessible"."task_id")
LEFT OUTER JOIN "taskaccess_accessible" ON ("tasks_task"."id" = "taskaccess_accessible"."task_id")
LEFT OUTER JOIN "divisions_topic" ON ("tasks_task"."topic_id" = "divisions_topic"."id")
INNER JOIN "tasks_task_unread_by_users" ON ("tasks_task"."id" = "tasks_task_unread_by_users"."task_id")
WHERE (("taskaccess_groupaccessible"."group_id" IN
          (SELECT U0."codename"
           FROM "user_group" U0
           INNER JOIN "user_group_users" U1 ON (U0."codename" = U1."group_id")
           WHERE U1."user_id" = 102)
        OR "taskaccess_accessible"."user_id" = 102
        OR "tasks_task"."topic_id" IN
          (SELECT U0."id"
           FROM "divisions_topic" U0
           LEFT OUTER JOIN "taskaccess_grouptopicaccessible" U1 ON (U0."id" = U1."topic_id")
           LEFT OUTER JOIN "taskaccess_topicaccessible" U3 ON (U0."id" = U3."topic_id")
           WHERE (U1."group_id" IN
                    (SELECT U0."codename"
                     FROM "user_group" U0
                     INNER JOIN "user_group_users" U1 ON (U0."codename" = U1."group_id")
                     WHERE U1."user_id" = 102)
                  OR U3."user_id" = 102)))
       AND "divisions_topic"."archived" = FALSE
       AND "divisions_topic"."is_template" = FALSE
       AND "tasks_task_unread_by_users"."user_id" = 102);

Explain: http://explain.depesz.com/s/2cT

That's the most troubling row, I guess:

Seq Scan on tasks_task (cost=0.00..10568.79 rows=30382 width=8) (actual time=0.052..28.744 rows=30383 loops=1)
Filter: (id <> 0)

Every column used in the query is indexed. I don't understand why index is not used at all (not to mention index-only scan). I tried to google possible explanations (like, the queried data set is too small and query planner decides it's more efficient to perform a seq scan), still no luck.


texts: How do I close log files that are open in OracleServiceORCL after inputting data into Oracle?

texts:
I inserted data into a table called cell_local_stage1. With this procedure:
CREATE TABLE cell_local_stage1
    (
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      g_batch VARCHAR2 (50),
      p_batch VARCHAR2 (50),
      c_batch VARCHAR2 (50),

      emptycol VARCHAR2(50),
      ind_var_number NUMBER,
      study_id VARCHAR2 (50),
      char_id NUMBER,
      concentration NUMBER (6, 2),
      valid NUMBER (1) DEFAULT 1,
      endpoint1 NUMBER (1),
      method1 VARCHAR2 (50),
      methodv1 VARCHAR2 (50))

    ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_cell_local_stage1_from_cell_local.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CELL_LOCAL.CSV')
    ) REJECT LIMIT UNLIMITED;

I got a log file that has this information:

 LOG file opened at 08/17/20 22:00:47

Total Number of Files=1

Data File: CELL_LOCAL.CSV

Log File: CELL_LOCAL_STAGE1_4908_22980.log

And when I try to delete that file this messages says that the file is still open:

I was able to delete the CSV files, but not the log files. I didn't know I could even open these log files in Oracle.
Sorry for such a basic question.


texts: How to restore the missing mysql db schema?

texts:
My DB schema is missing/deleted by someone without my knowledge from ASW EC2 windows machine. Seems some unauthorized user entered into the system illegally and did something. Since I can find some one tried multiple times to connect my DB and got failed with different usernames from the same IP address. Its recorded in the log. I couldn't find the files in Data folder related to my schema. I guess it is also deleted.

Still do we have a way to recover it and restore the dump?

I lost the data completely so not able to process anything.


texts: Why does ANSI SQL define SUM(no rows) as NULL?

texts:
The ANSI SQL standard defines (chapter 6.5, set function specification) the following behaviour for aggregate functions on empty result sets:

COUNT(...) = 0
AVG(...) = NULL
MIN(...) = NULL
MAX(...) = NULL
SUM(...) = NULL

Returning NULL for AVG, MIN and MAX makes perfect sense, since the average, minimum and maximum of an empty set is undefined.

The last one, however, bothers me: Mathematically, the SUM of an empty set is well-defined: 0. Using 0, the neutral element of addition, as the base case makes everything consistent:

SUM({})        = 0    = 0
SUM({5})       = 5    = 0 + 5
SUM({5, 3})    = 8    = 0 + 5 + 3
SUM({5, NULL}) = NULL = 0 + 5 + NULL

Defining SUM({}) as null basically makes "no rows" a special case that does not fit in with the others:

SUM({})     = NULL  = NULL
SUM({5})    = 5    != NULL + 5 (= NULL)
SUM({5, 3}) = 8    != NULL + 5 + 3 (= NULL)

Is there some obvious advantage of the choice that was made (SUM being NULL) that I have missed?


texts: How to attach an .mdf file that was not properly detached?

texts:
One of our clients had a SQL Server 2000 database, on a system which is no longer running. They have no idea what was in the database. They have given me the .mdf and .ldf files, and want us to investigate and extract information if any.

I don't think the database was properly detached when these files were backed up, since I am getting the following error when I try to attach these files to my SQL Server 2008 express edition:

  Could not continue scan with NOLOCK due to data movement. Converting
  database 'GRMStemp' from version 539 to the current version 655.
  Database 'GRMStemp' running the upgrade step from version 539 to
  version 551. (Microsoft SQL Server, Error: 601)

What should I do to attach the database in this situation?


texts: Strategies for summary data in parent record based on detail data?

texts:
I have parent-child relationship as shown below. I need to find the first order date for each customer. Obviously, writing a query to do this is fairly straight-forward but it does require reading all the records in CustomerOrders.

I've considered the following optimization approaches:

Add a FirstOrderDate field to CustomerMaster. Program the application to set the value when the customers orders for the first time.
Same as #1 but use a trigger in the database instead of using the application.
Some other possibility that is better than the first two.

What is the best approach to handling this?

CustomerMaster
    CustomerID
    Customer Name
    .
    .
    .

CustomerOrders
    OrderID
    CustomerID
    OrderDate
    .
    .
    .

As for performance, there are 9.7 million records right now. The query takes 34 seconds. The data is growing all the time. 34 seconds is too slow right now and I expect it to be double that in two years


texts:
I'm trying to model a database that will be used for management and execution of SSIS packages. The entities are Client, Job and Package. A Job is the managed unit that references Client and Package.

I need to model parameters for the Package. Some parameters are Global, common to all clients. Others pertain to a particular client. The source server, is a good example of a client parameter and target server is global to all clients. Here's the model I have so far:

Here is a scenario:

Global Parameters:

SSIS_ServerName
StagingServerName
StagingUserName
StagingPassword
LogServerName
LogUserName
LogPassword

Client Parameters:

SourceServerName
SourceUserName
SourcePassword
SourceDatabase
StagingDatabase
BlockSize
AllotedRunTime

It doesn't seem very clean to me. Anyone have a suggestion how to better model the parameters?

It's the relationship of PackageParameter to ClientParameter and GlobalParameter I don't quite know what to do with. PackageParameter lists the names of all parameters for the package and whether they are client or global. The values for those parameters would come from either the ClientParameter or the GlobalParameter, depending on the type.


texts: How to add a new target in Oracle Enterprise Manager 12 C

texts:
I just installed Oracle Enterprise Manager 12 C Grid Control from a ready made image that Oracle provides and now I am trying to add targets to it (monitoring targets i mean).

It might be a stupid question but how can such a thing be done ? I don't seem to find any tutorials on this and the Oracle docs are not that clear.


texts: SSRS Report drop down parameters. Use SP or embeded SQL query for datasource?

texts:
When I create SSRS reports I create stored procedures to bring data into my report. When I create the report parameters I just embed SQL code directly in to the parameter/SSRS report.

Should I also create stored procedures for my parameter data sources?

It seems like a bit of overkill to create a SP for every single paramamter/datasource in a report but I guess if you want to be able to modify the SQL code the parameter uses to load the drop down values without having to open the actual report, then I would probably want to go with SPs for everything....


texts: Lock only a row in insert command sql server

texts:
I have this initial code below to create and insert some values on my table
IF (OBJECT_ID('dbo.Teste_Nolock') IS NOT NULL) DROP TABLE dbo.Teste_Nolock
CREATE TABLE dbo.Teste_Nolock (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Nome VARCHAR(100)
)

INSERT INTO dbo.Teste_Nolock
VALUES ('User'), ('Name'), ('DataBase'), ('SQL Server'), ('Power BI'), ('SSRS'), ('SSIS'), ('SSAS'), ('Blog')

BEGIN TRANSACTION

UPDATE dbo.Teste_Nolock
SET Nome = 'Nolock Test'
WHERE Nome = 'User'

When I execute update without a commit or rollback, SQL Server is locking my table and i can't execute the command SELECT * FROM dbo.Teste_Nolock WHERE Nome = 'SQL Server' for example. How can i solve this kind of problem?


texts: SQL Server -- More efficient alternatives to repeating an (almost) identical subquery

texts:
I need to improve the efficiency of multiple subqueries within a WHERE clause.  The context is a report generator on an existing product.  The only input I can provide is the WHERE clause.  I have 3 almost identical subqueries with only 1 difference between them.  I need to verify that there is at least 1 record that matches each condition.

Here is the gist of the query

Where
  Exists (Select 1 From t1  ...etc... where ...etc... and fld1='X')
  AND
  Exists (Select 1 From t1  ...etc... where ...etc... and fld1='Y')
  AND
  Exists (Select 1 From t1  ...etc... where ...etc... and fld1='Z')

The "Select" in each of the 3 subqueries is identical. It does Join and has other fields in it's Where clause including a reference to the PK from the outer query : I'm only showing what's different.

There are about half a million records in t1.  The query above has been running now for over 30 minutes.  I need to get it down to 30 seconds.

My options are extremely limited -- No DECLARE, no temporary tables, no "OVER".


texts:
I need to calculate the depth of a descendant from it's ancestor.  When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH RECURSIVE query running with PostgreSQL 9.4.

I do not control the data or the columns. The data and table schema comes from an external source. The table is growing continuously. Right now by about 30k records per day. Any node in the tree can be missing and they will be pulled from an external source at some point. They are usually pulled in created_at DESC order but the data is pulled with asynchronous background jobs.

We initially had a code solution to this problem, but now having 5M+ rows, it takes almost 30 minutes to complete.

Example table definition and test data:

CREATE TABLE objects (
  id          serial NOT NULL PRIMARY KEY,
  customer_id integer NOT NULL,
  object_id   integer NOT NULL,
  parent_id   integer,
  ancestor_id integer,
  generation  integer NOT NULL DEFAULT 0
);

INSERT INTO objects(id, customer_id , object_id, parent_id, ancestor_id, generation)
VALUES (2, 1, 2, 1, 1, -1), --no parent yet
       (3, 2, 3, 3, 3, -1), --root node
       (4, 2, 4, 3, 3, -1), --depth 1
       (5, 2, 5, 4, 3, -1), --depth 2
       (6, 2, 6, 5, 3, -1), --depth 3
       (7, 1, 7, 7, 7, -1), --root node
       (8, 1, 8, 7, 7, -1), --depth 1
       (9, 1, 9, 8, 7, -1); --depth 2

Note that object_id is not unique, but the combination (customer_id, object_id) is unique.
Running a query like this:

WITH RECURSIVE descendants(id, customer_id, object_id, parent_id, ancestor_id, depth) AS (
  SELECT id, customer_id, object_id, parent_id, ancestor_id, 0
  FROM objects
  WHERE object_id = parent_id

  UNION

  SELECT o.id, o.customer_id, o.object_id, o.parent_id, o.ancestor_id, d.depth + 1
  FROM objects o
  INNER JOIN descendants d ON d.parent_id = o.object_id
  WHERE
    d.id <> o.id
  AND
    d.customer_id = o.customer_id
) SELECT * FROM descendants d;

I would like the generation column to be set as the depth that was calculated.  When a new record is added, the generation column is set as -1. There are some cases where a parent_id may not have been pulled yet.  If the parent_id does not exist, it should leave the generation column set to -1.

The final data should look like:

id | customer_id | object_id | parent_id | ancestor_id | generation
2    1             2           1           1            -1
3    2             3           3           3             0
4    2             4           3           3             1
5    2             5           4           3             2
6    2             6           5           3             3
7    1             7           7           7             0
8    1             8           7           7             1
9    1             9           8           7             2

The result of the query should be to update the generation column to the correct depth.

I started working from the answers to this related question on SO.


texts: How to find missing data from one table that is a join of two other tables

texts:
I have two tables, stock and location, with a third table that retains the level of stock at each location, stock_loc_info.

Each stock item should have a row for each location.

What SQL query would show that the row B1 is missing from stock_loc_info?

stock.stockcode
---------------
A
B

location.locno
--------------
1
2

stock_loc_info.fkstockcode  stock_loc_info.fklocno
--------------------------  ----------------------
A                           1
A                           2
B                           2


texts: Is there a setting to suppress C-Style comments appearing as DOC in Oracle Output?

texts:
Just again looking at some log from an oracle script, where the developer intended to comment out some part not to add documentation.

I don't want that a script containing code like this

/*
select getWochentag(SYSDATE) from DUAL;
select
    getWochentag (SYSDATE    ) || isWeekend (SYSDATE    ),
    getWochentag (SYSDATE + 1) || isWeekend (SYSDATE + 1),
    getWochentag (SYSDATE + 2) || isWeekend (SYSDATE + 2),
    getWochentag (SYSDATE + 3) || isWeekend (SYSDATE + 3),
    getWochentag (SYSDATE + 4) || isWeekend (SYSDATE + 4),
    getWochentag (SYSDATE + 5) || isWeekend (SYSDATE + 5),
    getWochentag (SYSDATE + 6) || isWeekend (SYSDATE + 6)
FROM DUAL;

*/

produces output like

DOC>select getWochentag(SYSDATE) from DUAL;
DOC>select
DOC>        getWochentag (SYSDATE    ) || isWeekend (SYSDATE    ),
DOC>        getWochentag (SYSDATE + 1) || isWeekend (SYSDATE + 1),
DOC>        getWochentag (SYSDATE + 2) || isWeekend (SYSDATE + 2),
DOC>        getWochentag (SYSDATE + 3) || isWeekend (SYSDATE + 3),
DOC>        getWochentag (SYSDATE + 4) || isWeekend (SYSDATE + 4),
DOC>        getWochentag (SYSDATE + 5) || isWeekend (SYSDATE + 5),
DOC>        getWochentag (SYSDATE + 6) || isWeekend (SYSDATE + 6)
DOC>FROM DUAL;
DOC>
DOC>*/

I just want to see nothing in the log. (Only empty logs are good logs)


texts: MySQL replication: Understanding content of binlog

texts:
I am using MySQL replication with GTID. I did mysqlbinlog to see some binary log.
This is a section in binary log that I am considering:

SET @@SESSION.GTID_NEXT= 'servre-uuid:248283'/*!*/;

SET TIMESTAMP=1408059914/*!*/;
SET @@session.sql_mode=4194304/*!*/;

SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
BEGIN

BINLOG '
   <a base 64 encode str>
'

BINLOG '
  <a base 64 encode str>
'

SET TIMESTAMP=1408059914/*!*/;
COMMIT

There is no statement in this transaction. It makes slave cannot executed with error:

When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is '<server-id>:248283'

I am trying to find the root cause. I am so glad if I receive a light from anyone.


texts: PostgreSQL replace primary key and converting non-serial to serial type

texts:
I'm basically building my own version of phpPgAdmin (if I get good enough to make it good enough I'll post it on a repository hosting service) and currently I'm revising my primary key tool to replace an existing primary key.

Unfortunately I've run in to two issues as I'm used MySQL though have been learning PostgreSQL for about two months and can get around fairly well.

Setting a primary key initially is not a problem, changing a column to replace an existing key doesn't seem to work as pgAdmin III reports the original primary key column is still the primary key column.

Unlike MySQL that sets the type to autoincrement when setting the type PostgreSQL uses serial and bigserial however apparently it's not possible to convert an existing (all empty rows) column from numeric to bigserial. Using the following query...

ALTER TABLE custaddress ALTER COLUMN id1 TYPE bigserial;

...I receive the error...

  ERROR:  type "bigserial" does not exist

How do I properly change the primary key of a table if a column is already set to be the primary key?

Do I have to convert the original primary key type to numeric before changing the primary key?

Do I have to do something different when setting the primary key if I have to delete the original primary key first or change it's type?


texts: Oracle tuning strategy for columns with long tails

texts:
We have an Oracle 11g database, where the two biggest tables are Customer and Order. The order profile of our customers has a long tail: we have millions of customers, with most only having a small number of orders, and a core of customers who have tens or hundreds of thousands of orders*.

Most queries concern these core high-volume customers.

We have a problem with queries that include Customer_ID in their conditions, such as:

select * from Order where Order_ID in (:1, ..., :100) and Customer_ID = :101

Oracle seems to calculate its estimate for for Customer_ID cardinality as <rows in Order>/<distinct values of Customer_Id>. This is usually an underestimate (since most queries concern high-volume customers), which leads it to prefer plans that use indexes on Customer_ID**.

So far, we've been addressing this problem using plan pinning on a query-by-query basis. But low cardinality estimates on Customer_ID seem to be a recurring theme, and we'd like to find a way to address this more systematically.

Is there a way to handle long-tailed columns systematically?

*Most customers are not "popular values" for the purposes of height-balanced histograms

**For this particular query, an inlist iterator over an index unique scan on Order_ID is faster for high-volume Customer_IDs


texts: Example of query execution plan where the cardinality estimation makes a difference

texts:
I am trying to create an example of a query execution plan which shows that if I have the cardinality estimation for some column of a table I can decide which query execution plan has the least cost.

So I have these two posts (1 and 2) which I can understand estimated cardinality as the number of distinct values on a column. And then I calculate the selectivity.

SELECT max(price) FROM tickets WHERE country = "CANADA";

If my table has 180 items and only 10 are CANADA. And there are only 4 different countries (CANADA, BRAZIL, USA, GERMANY). So....

the cardinality of column country é 4, because it is the number of distinct items.

The selectivity of the column country = CANADA is the number of items accessed divided by the items on the table. 10/180 = 0.0555.

But which are the different query execution plans that the optimizer could choose based on the cardinality?

If this is not a good example, could someone point one query that the cardinality would be a treasure to the optimizer in order to decide to chose one plan versus another?

Thanks,
Felipe


texts: Reporting services database was NATIVE, now it's UNKNOWN

texts:
After migrating a database from Sql Server 2012 to Sql Server 2014, we found a little problem.

It was told me that the reporting server was not used, so I just installed the SQL Server 2014 and migrate the database. Everything was ok.

But now, I noticed that users are having problems with reporting services ( yes, they use Reports ).

Long story short, Why is the ReportServer database unknown, if it was Native?

I could change to make some tests, but now I can't set it back, because it's unknown. Why is the database not native anymore?

Is there something to do to make it back to Native?

EDIT 1:

What I tried to to is:
1) backup up reportserver and reportserverTEMPDB Database

2)Restore it on the new SQL Servrer 2014 instance

3)Points the "database" to this database as shown below:

After moving it, now it is native ( but only on the new instance ), but when I try to access the URL, it shows me this error:

  The report server installation is not initialized(rsReportServerNotActivated)

but As shown here, it is activated:

in This question here, he says that he made this step-by-step:

  Reporting Services Configuration Manager - Encryption Keys - Delete
  - Encrypted Content - Delete

I really don't know if a should do this. What am I going to delete? It will delete some data of the database  ( not the reportServer database, the database that we are using to get data )


texts: How can I make an Aging Report using mysql?

texts:
I have a table called supplier

CREATE TABLE supplier_invoices (
id INT PRIMARY KEY AUTO_INCREMENT,
supplier_id INT NOT NULL,
date DATE NOT NULL,
invoice_no INT NOT NULL,
dr_amount DOUBLE NOT NULL,
cr_amount DOUBLE NOT NULL,
balance DOUBLE NOT NULL);

and it contains the above fields. I want to create an aging report basing on the data in that table. With difference of days between 0-30days, 31-60days, 61-90days, 90 and above days.

How can I achieve this. Am using mysql.


texts: Possible to upgrate SQL Server 2012 express to 2012 Standard?

texts: I need to do an in place upgrade of SQL server 2012 express to standard without losing any data. Is this possible? Is it as simple as just running the setup for the standard version? TIA


texts: SQL Server Express 2008R2 - restore db failing due to size limit, but file is under 10GB

texts:
I'm trying to restore a live db to a test db, but I keep getting a '..failed because the resulting cumulative db size would exceed...limit of 10240' error, yet my database is under 10GB, I feel like the answer is in the 'cumulative db size' part.

Being new to SQL Server, I find it confusing that different numbers are reported depending on where you look. I've done multiple purges yet the upper 10,243MB number doesn't seem to change.
The source .BAK file size in bytes is: 10,622,637,056 (9.9GB)
Also, autogrowth set to 50MB - could that allocation be pushing it over the limit? Since the source is the live db, I'm hesitant to monkey with the autogrowth limit. Suggestions appreciated!


texts: Storage of final entry values in read only table

texts:
I have two identical tables, one is for rows which are in a queue, and one is where I move my rows once they have been processed.(thousands of rows processed daily)

In my jobs table, I store ids to other tables and retrieve the values when needed through join statements, However once a row is processed, and I move this row into the final table, I am wondering whether I should just store the actual values here of what the ids refer to, instead of the ids like in the jobs table.

This final table is used encase I need to look up what happened in the past, a potential  problem with storing just the id here is that if I ever change the value in the parent table(what the id references), my final table will be referencing incorrect values as the value has changed since its original entry.

I have not managed to come across this scenario in books yet, so I do not know terminology for this, Or if/how its handled.

I am also curios if perhaps I am handling the referenced data completely wrong?
Perhaps I should not edit my original values once they are stored and every time I need to edit them, I enter a new row and start referencing that id? But this also doesn't sound like a good method.

Thanks in advance.


texts: What is the purpose of the "territory" configuration parameter?

texts:
This is the command that I use to create a new Db2 database in my project:

db2 create db <DATABASE_NAME> using codeset 1251 territory ru

Unfortunately, there is no documentation left and I cannot trace the origin of this specific command. I am trying to understand the purpose of the territory parameter. The Db2 documentation says:

  This parameter shows the territory used to create the database.
  territory is used by the database manager when processing data that is
  territory sensitive.

What could be the purpose of this parameter? What kind of data could be territory sensitive? What are the possible use cases where territory matters?


texts:
I'm running MySQL on a HyperV virtual machine. The host has 384Gb of RAM and is nowhere near being fully committed. I've tried giving the VM 128G of RAM and 8 CPU and I've tried 2 CPU's and 32GB of RAM. Same symptoms on both configs. The server simply acts like it's not doing anything. It uses only 1-2% of CPU and only 3GB of RAM. Is there some config I should change?

My command to import:

MySQL.exe -u username -p databasename < filename.dump

I've even tried importing several dumps at the same time and still the server uses little to no resources.


texts: Using a full text index with PostgreSQL and Doctrine

texts:
I received the following error:

SQLSTATE[54000]: Program limit exceeded: 7 ERROR:  index row size 2728 exceeds btree version 4 maximum 2704 for index "fulltext"
DETAIL:  Index row references tuple (1,33) in relation "manual".
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing. (54000) class: PDOException

The table in question is:

\d manual
                                      Table "public.manual"
    Column    |         Type          | Collation | Nullable |              Default
--------------+-----------------------+-----------+----------+------------------------------------
 id           | integer               |           | not null | nextval('manual_id_seq'::regclass)
 parent_id    | integer               |           |          |
 name         | character varying(45) |           | not null |
 content      | text                  |           |          |
 keywords     | text                  |           |          |
 listorder    | smallint              |           |          |
 display_list | boolean               |           |          |
Indexes:
    "manual_pkey" PRIMARY KEY, btree (id)
    "unique_name_manual" UNIQUE, btree (name)
    "fulltext" btree (keywords, name, content)
    "idx_10dbbec4727aca70" btree (parent_id)
Foreign-key constraints:
    "fk_10dbbec4727aca70" FOREIGN KEY (parent_id) REFERENCES manual(id)
Referenced by:
    TABLE "manual" CONSTRAINT "fk_10dbbec4727aca70" FOREIGN KEY (parent_id) REFERENCES manual(id)

I thought I had a full text index per my Doctrine config <index name="fulltext" columns="keywords, name, content" flags="fulltext"/>, but it appears I don't.  Am I doing something wrong or is a Doctrine deficiency?  Recommended solutions?

<?xml version="1.0" encoding="utf-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping https://www.doctrine-project.org/schemas/orm/doctrine-mapping.xsd">
  <entity name="Manual" table="manual">
    <indexes>
      <index name="fulltext" columns="keywords, name, content" flags="fulltext"/>
    </indexes>
    <unique-constraints>
      <unique-constraint name="unique_name_manual" columns="name"/>
    </unique-constraints>
    <id name="id" type="integer">
      <generator strategy="IDENTITY"/>
    </id>
    <field name="name" type="string" length="45" nullable="false"/>
    <field name="content" type="text" length="65535" nullable="true"/>
    <field name="keywords" type="text" length="65535" nullable="true"/>
    <field name="listorder" type="smallint" nullable="true"/>
    <field name="displayList" type="boolean" column="display_list" nullable="true"/>
    <one-to-many field="topics" target-entity="Manual" mapped-by="topic" fetch="LAZY"/>
    <many-to-one field="topic" target-entity="Manual" inversed-by="topics" fetch="LAZY">
      <join-column name="parent_id" referenced-column-name="id" nullable="true"/>
      <order-by>
          <order-by-field name="listorder" direction="ASC" />
      </order-by>
    </many-to-one>
  </entity>
</doctrine-mapping>


texts: Is there a way to partially initialize a TYPE?

texts:
I have created a custom type:

CREATE TYPE my_type as(name text, street text, location text);

Initializing such a type is easy:

SELECT ROW('a', 'b', 'c')::my_type;

I am wondering if there is an easy way to initialize a type partially, meaning that all unmentioned columns are set to null implicitly. In pseudo-code, something like:

SELECT ROW('a', 'c')::my_type(name, location);

In addition, is there also a way to initialize a type when the columns are not mentioned in the correct order? In pseudo-code, something like:

SELECT ROW('c', 'a', 'b')::my_type(location, name, street);


texts: SQL Server OPENQUERY vs Regular Query against AS400

texts:
I log into a SQL Server database which has an AS400 server as a linked server.

I've discovered that running queries against this AS400 server is MUCH faster if I run the select in an OPENQUERY...

SELECT  EMEQP#
FROM _
WHERE EMALOC = '467' AND EMDLCD = 'A' AND EMSTAT NOT IN ('S', 'J', 'V')

Execution time: 46 seconds

SELECT EMEQP#
FROM
OPENQUERY(_, '
SELECT  EMEQP#
FROM _
WHERE EMALOC = ''467'' AND EMDLCD = ''A'' AND EMSTAT NOT IN (''S'', ''J'', ''V'')
')

Execution time: 1 second

Can someone explain or link me to why/how this is happening?


texts: Read performance of SQL Server data files data from Azure BLOB

texts:
I have a setup where there is SQL Server on premises (MS SQL Server 2016 13.0.5366.0) but database files (log and data) are stored in cloud storage account. Setup described here.

The interesting part is the performance of physical reads of data. Basically i am executing very simple select statement from table and cleaning buffers/cache before running it.

If i read data in single thread query from table and physical reads
are needed i get maximum network utilization never exceeding 32
Mbps- much slower than i expect
If i read data in many threads that also requires physical reads i
get maximum network utilization close to 500 Mbps that is something
that i would expect.

Trying to understand:

Why sequential execution are not reading data faster? Is there some limits of max read performance for single thread?
Why query is not starting to work parallel so that multiple threads would read same data file (and in that way achieving higher performance)?

Did try splitting data to multiple data files- no affect.

Did try query hints (query was still not executed in parallel)

OPTION(QUERYTRACEON 8649)

OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))


texts: Help writing the following query to relational algebra

texts:
Can someone help me in writing the following query to relational algebra, I am studying DBMS and  I am stuck on this exercise.

Query:

SELECT a.name, d.name. d.grade
FROM student a, enrolled i, class d
WHERE a.id= i.student_id
AND i.disc_id = d.id

Thanks


texts: The USE-Statement breaks on MySQL-Server

texts:
running webadmin for years now - and having troubles for months - and posting here for weeks. But the support seems to be not the best...

want to run sql-commands on the newest version - but it fails all the time

USE jo ;
SHOW GLOBAL VARIABLES LIKE 'PORT';

gives back the following

Failed to execute SQL : SQL USE jo ; SHOW GLOBAL VARIABLES LIKE 'PORT'; failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHOW GLOBAL VARIABLES LIKE 'PORT'' at line 1

while

USE jo ;
SHOW GRANTS FOR CURRENT_USER;

gives back the following

Failed to execute SQL : SQL USE jo ; SHOW GLOBAL VARIABLES LIKE 'PORT'; failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHOW GLOBAL VARIABLES LIKE 'PORT'' at line 1

hmm - i have no ideas why the comands fail all the time!?

see the manual on this sql-command:
https://beginner-sql-tutorial.com/sql-use-database.htm

SQL USE Statement
The USE Statement is used to select a database and perform SQL operations into that database. The database remains default until end of session or execution of another USE statement with some other database.

SQL USE DATABASE Statement:
The Syntax for the USE Statement is:

USE database_name;
database_name - is the name of the database to be selected

USE DATABASE Example:
If you want to use database MyDatabase, the statement would be like
USE MyDatabase ;

btw i am running the versions
MySQL version 5.5.33
PHP 5.6.39

update

by the way: if i do not use USE
then it works propperly

if i run

SHOW GLOBAL VARIABLES LIKE 'PORT';
Output from SQL command SHOW GLOBAL VARIABLES LIKE 'PORT'; ..

or

SHOW GRANTS FOR CURRENT_USER;

i get outputs like this one

Output from SQL command SHOW GRANTS FOR CURRENT_USER; ..
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*4444444rrrrrrwwwww222222444443660283C379783ED8EF54B6EC01DAF8374444444rrrrrrrrrrrreeeeewwwww2CeC3C474F4' WITH GRANT OPTION
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

and now i have to rethink what this does want to say in the context of a - error in trying to establish a db-connection - while installing a wordpress on the frontend


texts: Does DBA need (a read) access to virtualization infrastructure? And why?

texts:
I am in charge of monitoring, troubleshooting, tuning and optimizing a few dozens production SQL Servers 2008 R2/2012 in my company that are being run inside VMWare virtual machines.

I've requested a read-only access to VMWare infrastructure without much success. I feel that I should provide irrefutable and clear for non-technical people (company's management) proofs and illustrations why I need such access.

What are they?

UPDATE:
Recently, I downloaded and "tried" Confio Ignite trial for monitoring "my" SQL Servers running inside (VMWare) virtual machines.

Why is such access necessary?
Which of this info is important for DBA that cannot be got from Window's PerfMon VM counters?

Related question:

Can you explain me VM Performance Counters in Windows?


texts: MySQL integer column with string search returns the invalid result

texts:
Can anyone please help me to understand the MySQL select query behavior,

Table Structure:

CREATE TABLE `dummytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` int(11) NOT NULL,
  `welcome` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Question: Why I am getting this result?

SELECT * FROM `dummytable` WHERE `mobile` = '\'\'' LIMIT 50


texts: MSSQL Server 2012 Query Do we Need T-SQL for query with IF Condition?

texts:
I have a database with tables Student(StudId, StudName,...) and Grades (GradeExam1, GradeExam2,..) etc. And I want to do a query to assign a letter grade to each grade. I am thinking of doing something like:

SELECT StudId, GradeExam1 AS 'A' IF GradeExam1>= 90 AND GradeExam1 <=100
UNION
SELECT StudID, GradeExam1 AS 'B' ..........

I know that I can use non-conditional aliases within plain SQL, but I have never done conditional aliases within an SQL query.

Or maybe I need T-SQL in order to do a loop for a given exam, instead of doing a union of select statements for different conditions?

Thanks in Advance.


texts:
I am new to ETL and VB in general and need some help resolving this issue. I have looked online and found some interesting answers but none seem to be working for me.

varamt = Lookup (
    "Lookup_tbl1",
    [Lookup_tbl1].[Amount],
    Array(DTSSource("DEPT_ID"), DTSSource("ACCT_ID")))

IF NOT isEmpty(varamt) or NOT isNULL(varamt) or NOT varamt="" THEN
    DTSDestination("AMOUNT") = varamt
ELSE
    DTSDestination("AMOUNT") = DTSSource("AMOUNT")
END IF

Lookup_tbl1 has ACCT_ID and DEPT_ID as the primary keys. My destination table does not have these as primary key and ACCT_ID has NULL values in the table. Dept_ID and Acct_ID are varchar.

When I tried this transformation where ACCT_ID was notNULL, it worked, but not in the table where I want it to work.

Here is the error message that I get:

Inserting into [AMOUNT] ...
Error details: The number of failing rows exceeds the maximum specified.
(Microsoft Data Transformation Services (DTS) Data Pump (80020101):
Error Code: 0 Error Source= Microsoft VBScript runtime error
Error Description: Type mismatch: 'Join' Error on Line 36)
Execution Failed.`

There is no line 36 in my code. Please suggest solutions / pointers.


texts: Does HBase support spatial functionality?

texts:
I see mention for spatial functional in HBase. For example "HBaseSpatial: A Scalable Spatial Data Storage Based on HBase".

What spatial functionality does HBase support and where is this documented?


texts: GridDB Executable file not found in $PATH

texts:
I have installed griddb in a CentOS docker container. However when I run it, I get the following error:
docker: Error response from daemon: OCI runtime create failed: container_linux.go:367: starting
container process caused: exec: "gs_startnode": executable file not found in $PATH: unknown.
ERRO[0000] error waiting for container: context canceled

Here is the Dockerfile I used to build the container:
 FROM centos:7

RUN yum update -y
RUN yum install epel-release -y
RUN yum install git -y
RUN yum groupinstall "Development Tools" -y
RUN yum install tcl.x86_64 -y
RUN yum install zlib-devel -y
RUN yum install ant -y

WORKDIR /src/griddb/

RUN git clone https://github.com/griddb/griddb.git .

RUN ./bootstrap.sh
RUN ./configure
RUN make

ENV GS_HOME=$PWD
ENV GS_LOG=$PWD/log
ENV PATH=${PATH}:$GS_HOME/bin

ENTRYPOINT [ "gs_startnode", "-u", "admin/admin", "-w" ]

I understand that the issue is in the Docker build entrypoint arguments but I can't seem to find a solution.


texts:
I am a bit confused with the definition of 3NF.

According to Wikipedia:

  The third normal form (3NF) is a normal form used in database
  normalization. 3NF was originally defined by E.F. Codd in 1971. Codd's
  definition states that a table is in 3NF if and only if both of the
  following conditions hold:

  The relation R (table) is in second normal form (2NF)
  Every non-prime attribute of R is non-transitively dependent on every superkey of R.

But, according to book  the Database System Concepts 6th Edition by Abraham Silberschatz, Henry F.Korth, S.Sudarshan:

  A relation schema R is in third normal form (3NF) with respect to a
  set F of functional dependencies if, for all functional dependencies
  in F+ of the form α → β, where α ⊆ R and β ⊆ R, at least one of the
  following holds:

  α → β is a trivial functional dependency.
  α is a superkey for R.
  Each attribute A in β − α is contained in a candidate key for R.

Also, according to Database Systems 6th Edition by Ramez Elmasri  Shamkant B. Navathe:

  A relation schema R is in third normal form (3NF) if, whenever a
  nontrivial functional dependency X→A holds in R, either (a) X is a
  superkey of R, or (b) A is a prime attribute of R.

After reading the above definitions, I am not sure if a relation schema has to be in 2NF in order to be in 3NF. Also, does a relation schema have to be in 3NF in order to be in BCNF?


texts: Could someone help me identify my syntax error, or identify whether this is possible at all?

texts:
Is it possible to do something like this in SQL with a real table? I know this is possible with a temp table, but cannot get it to work properly with a normal table.

CREATE TABLE Backups
                (
                 DBName SYSNAME,
                 LastFullBackup DATETIME NULL,
                 LastDiffBackup DATETIME NULL,
                 [State] CHAR(1) NULL
                );

        WITH    LastFullBackup
                  AS (SELECT    bs.database_name,
                                MAX(bs.backup_finish_date) AS BackupDate
                      FROM      msdb..backupset bs
                      WHERE     bs.type='D'
                      GROUP BY  bs.database_name),
                LastDiffBackup
                  AS (SELECT    bs.database_name,
                                MAX(bs.backup_finish_date) AS BackupDate
                      FROM      msdb..backupset bs
                      WHERE     bs.type='I'
                      GROUP BY  bs.database_name)

I see a syntax error on the last parenthesis, but that doesn't give me much insight into where the real problem is.


texts: SQL Server 2016 Always On ReadOnly Routing List

texts:
I have an application that is about 60% write and 40% read. I have configured a Read Only Routing List for SQL 2016 Always On Availability Group based on the Microsoft's guide. here: Configure Read-Only Routing for an Availability Group

When I tried testing it using SQLCMD, I get the error below:
 C:\Users\administrator.IMGT>sqlcmd -S IMGT-AVG,5022 -E -d IMGT_DB_Prod -K ReadOnly

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable
to establish connection because an error was encountered during
handshakes before login. Common causes include client attempting to
connect to an unsupported version of SQL Server, server too busy to
accept new connections or a resource limitation ( memory or maximum
allowed connections) on the server..
Sqlcmd: Error: Microsoft ODBC
Driver 13 for SQL Server : TCP Provider: An existing connection was
forcibly closed by the remote host. .
Sqlcmd: Error: Microsoft ODBC
Driver 13 for SQL Server : Client unable to establish connection.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable
to establish connection due to prelogin failure.

How else can I test if ReadOnly Routing List is working?

Since my application is about 60% WRITE and 40% READ, do i need to specify application intent in my Connection String?

Any guide on how to test this will be appreciated.


texts: How to get default locking hint of a table

texts: Is there is any way to get default locking hint using a query. I want to know whether the locking use by my transaction is tablock | rowlock | nolock etc.


texts: How to optimize stored procedure so it won't end with "Out of memory exception"

texts:
I have created a stored procedure that check against 2 and then delete from the 3.
When I execute the stored procedure on Management Studio I get the exception as mentioned in this question: StackExchange Question
However what my question is how to optimize the SP so it will not take that long time to run?
My code is as following:

 CREATE PROCEDURE [dbo].[Clean] (
 @Deletion date
  ) AS
   BEGIN

BEGIN TRANSACTION Cleaning
DECLARE @id int
Declare @ErrorCode int =1

 DECLARE cursorE CURSOR local fast_forward FOR
        select distinct m.ID
        from Member m
        left join (
            select *, row_number() over (PARTITION BY rid order by ceid
desc) as rn
            from TypeA
        ) x on m.ID = x.ID and x.rn = 1
        where (
            (x.ceid is null and m.LastChangedDateTime < @Deletion)
            or x.Resignation < @Deletion
        )

    OPEN cursorE
    FETCH NEXT FROM cursorE INTO @erID

     WHILE ( @@FETCH_STATUS = 0 )
  DELETE FROM Errn WHERE erid = @id
  FETCH NEXT FROM cursorE INTO @rID
  COMMIT TRANSACTION Cleaning
  RETURN 1
  END
 CLOSE cursorE
DEALLOCATE cursorE
ERRORHANDLER:
-- Rollback the transaction if there were any errors
 ROLLBACK TRANSACTION Cleaning
 RETURN @ErrorCode

GO


texts: Is the order of attributes in a unique index still important in Oracle 12c?

texts:
The internet mostly says, the order in a unique index does matter for the reasons of index search tree structure, but a colleague stated, Oracle changed that recently.

I can't find any information on such a change in the docs.

Is the order still influencing look-up speed for a unique index?


texts: How is this E-R diagram of an identifying relationship's mapping cardinality correct?

texts:
According to textbook Database System Concepts,"The identifying relationship is many-to-one from the weak entity set to the identifying entity set, and the participation of the weak entity set in the relationship is total." In the same book is an E-R diagram of an identifying relationship that appears to me to be conflicting with the first half of the above statement. The mapping cardinality of the identifying relationship from the weak to identifying entity set of the diagram appears to be many-to-many.
Am I misunderstanding the diagram or statement?


texts: I would like to use SqlCacheDependency to update a dataset in real-time, but we have not enabled the service broker on our production database. Could enabling the service broker have any adverse effects (performance, reliability, etc)?


texts: Replication Snapshot folder not cleaning up

texts:
The job that I thought was supposed to clean up old snapshots isn't removing the old files.  The retention is left at default yet there are snapshots months old which remain.  The job runs as the agent account (maybe I should try a proxy account?)  and this account has full permissions to the folder.

I have checked sp_configure to ensure xp_cmdshell is enabled and it is.

The cleanup job runs the proc sp_MSsubscription_cleanup, however I can't see anywhere in that which would delete the old snapshot files.

Can anyone enlighten me?
Thanks


texts: How to remove spaces between words and replace with single underscore using 'REPLACE','INSTR' OR 'SUBSTR' oracle function

texts:
I want to replace spaces between words with a single underscore. I can't use the replace_regex Oracle function to do this, because it is not supported by the converter which we are using to convert sql statements so that they will be independent of platform such as SQL Server, PostgreSQL etc. .

Currently converter is providing support for 'substr' , 'replace', and 'instr' oracle functions. how can i use these three functions to get below output.
Example:

"my name is            xyz" =>  "my_name_is_xyz"
"test    sdf"               =>  "test_sdf"


texts: Adding a new data set in SSRS with nested stored procedures

texts:
I have spOne which builds a couple of global temp tables with over 500 lines of code. Normally it would return a single result set but I wanted to leverage the code for building the temp tables without duplicating it. So, I added a @showResults param and if it equals 0 then it will build the temp tables but not execute a SELECT statement against them.

Now I have spTwo which calls spOne in the beginning with @showResults = 0 and then continues the work it has to do before returning a single result set.

In SSRS, I am trying to add spTwo as a data set but it is auto-detecting spOne's result set even though it would not be executed given the param input.

How do I add spTwo as an SSRS data set?

Updated:

A representation of the stored procedures can be found here.


texts: Explain unexpected result in MATCH () AGAINST ()

texts:
I have a database with a fulltext column name. I made a query

SELECT name, MATCH (name) AGAINST ('breaking bad') AS score
FROM files
WHERE MATCH (name) AGAINST ('breaking bad');

So that I can see matching score of results. I saw,

name
----------------------------------------------------  score-----------
Breaking Down.mp3-----------------------------------  5.59644222259521
Breaking Benjamin - Until The End.mp3---------------  5.53353023529053
aaf-breaking.bad.s03e10.fly.English.srt-------------  5.47201633453369
Breaking Bad Season 3 Episode 03 - I.F.T..avi-------  5.47201633453369

I thought row containing both breaking and bad will score higher. Can anyone please explain this behavior?

UPDATE

I got this. It is ignoring bad as it is less than 4 characters. Can anyone suggest how I can include that in my search also?


texts: extract different number of rows from multiple tables


texts: SELECT inside trigger gives wrong result (it ignores the WHERE clause)

texts:
When I run this code inside an after UPDATE trigger:

SELECT * FROM `stock_totals` WHERE `product_id` = 2 AND `stock_place_id` = 3 INTO OUTFILE 'query3.csv'
        FIELDS TERMINATED BY ','
        ENCLOSED BY '"'
        LINES TERMINATED BY '\n';

I get this into query3.csv: (field order: product_id, stock_place_id, date, quantity)

"1","3","2020-01-01","42"

Notice how the resulting row does not match the WHERE clause: the stock_place_id is correct, but the product_id is wrong (I got 1, but it should be 2).

When the same query is ran alone, I get an empty table which is the expected result given my data:

+------------+----------------+------------+----------+
| product_id | stock_place_id | date       | quantity |
+------------+----------------+------------+----------+
|          1 |              1 | 2020-01-01 |      -42 |
|          1 |              3 | 2020-01-01 |       42 |
+------------+----------------+------------+----------+

I'm running MariaDB version 10.4.12 and innodb_version 10.4.12 on Arch Linux.

The full query for the stock_totals table is:

CREATE TABLE `stock_totals` (
 `product_id` int(11) NOT NULL,
 `stock_place_id` int(11) NOT NULL,
 `date` date NOT NULL,
 `quantity` int(11) NOT NULL,
 PRIMARY KEY (`product_id`,`stock_place_id`,`date`),
 KEY `fk_stock_totals_stock_place1_idx` (`stock_place_id`),
 CONSTRAINT `fk_stock_totals_product1` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_stock_totals_stock_place1` FOREIGN KEY (`stock_place_id`) REFERENCES `stock_place` (`place_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


texts: How to SELECT a sibling in an adjacency list type table?

texts:
I'm trying to get the next sibling in a list according to an integer position column. Rows are siblings when they have the same parent_id.
Given that sibling foo.id = 192 and foo.position = 0, I want to get sibling bar that has a position of foo.position + 1
The below query returns the right result except that the AND a.position = 0+1 should not be hardcoded.
SELECT a.title as taskTitle, b.title as projectTitle
FROM tasks a
INNER JOIN tasks b
ON a.parent_id = (
    SELECT parent_id FROM tasks WHERE id = 192
)
AND a.position = 0+1
AND a.root_id = b.id;

The following query won't work of course but shows what I'm trying to do.
SELECT a.title as taskTitle, b.title as projectTitle
FROM tasks a
INNER JOIN tasks b
ON a.parent_id = (
    SELECT parent_id, position as pos FROM tasks WHERE id = 192
)
AND a.position = pos+1
AND a.root_id = b.id;

Is this possible without first querying the needed information and saving it into a variable to use in a second query?
Would it be faster than saving the result of a "pre-query" to use in a second query?
EDIT:
Here's some test queries that set up a demo
Setup
CREATE TABLE tasks (
    id int unsigned NOT NULL AUTO_INCREMENT,
    root_id int unsigned,               /* top level task */
    parent_id int unsigned,             /* the task id that is the parent of this task */
    `title` varchar(255) NOT NULL,
    position int unsigned,              /* the index in the array that will contain this task */
    FOREIGN KEY (root_id) REFERENCES tasks(id),
    FOREIGN KEY (parent_id) REFERENCES tasks(id),
    PRIMARY KEY(id)
);

INSERT INTO tasks (root_id, parent_id, `title`, position) VALUES
(NULL, NULL, 'Foo Project', 0),
(NULL, NULL, 'Bar Project', 1),
(NULL, NULL, 'Baz Project', 2);

INSERT INTO tasks (root_id, parent_id, `title`, position) VALUES
(1, 1, 'Foo Child A', 0),
(1, 1, 'Foo Child B', 1),
(2, 2, 'Bar Child A', 0);

INSERT INTO tasks (root_id, parent_id, `title`, position) VALUES
(2, 6, 'Bar Child A Grandchild 1', 0),
(1, 5, 'Foo Child B Grandchild 1', 0),
(1, 5, 'Foo Child B Grandchild 2', 1);

Query for needed info
The following query is suboptimal but retrieves the info needed.
SELECT a.title as taskTitle, b.title as projectTitle
FROM tasks a
INNER JOIN tasks b
ON a.parent_id = (
    SELECT parent_id FROM tasks WHERE id = 8
)
AND a.position = (SELECT position FROM tasks WHERE id = 8)+1
AND a.root_id = b.id;


texts: Is configuring Database mail considered as a security threat?

texts: Is configuring Database mail and sending email using sp_send_dbmail considered a security threat?


texts: How does SQL Server handle logins when the user is a part of multiple groups with various privileges?

texts: I'm trying to wrap my head around how SQL Server decides to grant certain rights based on a users login privileges.  If a user is part of a group "READ ONLY" which has access to DB1, but they are also a part of a group "SysAdmin" which has full db owner rights to the same DB1, which rights are they given when they log in?  On a similar note. What if they are part of a group but also have a standalone account with other middle of the road privileges?


texts: Postgres CTE execution guarantees, inlining

texts:
Following query with non deterministic function yields two same rows - the CTE is computed and re-used.

WITH foo AS (SELECT uuid_generate_v4() AS id)
SELECT id FROM foo
UNION ALL
SELECT id FROM foo

| id
| 741f4f69-416b-4b4c-9226-559527f4a84e
| 741f4f69-416b-4b4c-9226-559527f4a84e

Is this execution guaranteed? Can postgres decide to inline it to

SELECT id FROM (SELECT uuid_generate_v4() AS id) t
UNION ALL
SELECT id FROM (SELECT uuid_generate_v4() AS id) t

| id
| 262f0006-b4d5-440a-a86a-d79cd2684458
| c24b5835-9c5c-4d6f-b649-8e510129015b

which yields two different ids (and thus different result)?

What does the SQL standard specify? Does Postgres provide any additional guarantees? If it's not guaranteed, what is standard way to reuse statement that depends on non deterministic procedures?


texts: How to store multiple values in a column in database?

texts:
I'm trying to make three tables in phpMyAdmin. The main columns here are named Campaign, Program and Asset. Under Campaign, there are different kinds of Programs. And under each Program, there are Assets with corresponding columns. Here is the rough sketch of what I'm trying to build right now.

Campaign 1

Campaign Description
Program 1

Program Description
Asset 1

Asset Type
Asset Description
Region
Supporting Asset
Status
Release Date
External Link
Source File Link
Contact Person
Target Audience
Color

Asset 2

Asset Type
Asset Description
Region
Supporting Asset
Status
Release Date
External Link
Source File Link
Contact Person
Target Audience
Color

How can i construct my tables like these interconnecting with each other? How do i do querying? Thanks in advance! :)


texts: copy entire PostgreSQL cluster to another (identical) server

texts:
I'm looking to clone a PostgreSQL 10 cluster from server1 to server2, which is running the same Postgres version on identical hardware. The purpose is load-balancing and HA. Things to keep in mind:

The databases are very large (terabytes) and the network is excellent. I want to avoid using intermediate files.
Cloning the live databases would be cool, but I can also shut down the cluster if needed.

Options I've considered:

pg_dump | psql of course, but this requires re-creating the indexes and seems really slow and incredibly inefficient for a complete copy between identical systems.
Set up streaming replication with server2 as slave, wait for it to sync with server1, then reconfigure both to disable replication again (I don't need it). Seems like a bunch of pointless config work with room for mistakes.
Shut down the cluster, rsync all the Postgres folders and files. With so much data there's a risk of data corruption, and I need to be sure I get everything (presumably just the data directories are not enough).
Can I pipe pg_basebackup directly to pg_receivewal somehow to make this work? Can't find instructions for my use case.

What's the best way to do this? Seems like a pretty common situation.


texts: Is log shipping good for periodic synchronization?

texts:
I have Postgresql 9.3 server on both my laptop and my desktop at home. I keep identical data on both system. Sometimes I work on the database on my laptop while other times I work on the database on the desktop. I want to make sure that the data and structure of both the laptop and desktop versions of the databases remain in sync. I don't need them to be hot swappable or anything, but just need to sync them once a day or once every two days.

So my question is whether log shipping is a good way to keep both databases in sync for periodic synchronizations? Or is this a bad choice since the laptop database cannot always access the desktop database from a remote location.


texts: Postgres Case statement not return else value in this query

texts:
I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck...

This is my query at the moment:

select t.fpl_id, t.team_name,
sum(pf.points)as gwpts,
(
    select sum(transfers_malus)
    from gameweeks
    where gameweeks.team_id = t.id and gameweeks.number = g.number
)as malus,
(
    select sum(points)
    from player_fixtures as pfix
    where gw_number = g.number and pfix.player_id = CASE WHEN minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END
)
as cpts,
 (
    select max(web_name)
    from players
    join player_fixtures on players.id = player_fixtures.player_id and gw_number = g.number
    where players.id = CASE WHEN player_fixtures.minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END
) as captain_name,
array_agg(p.id) as lineup
from teams as t
join gameweeks as g on t.id  = g.team_id
join gameweeks_players as gp on gp.gameweek_id = g.id
join players as p on gp.player_id = p.id
join player_fixtures as pf on p.id = pf.player_id  and pf.gw_number = g.number
where t.id = 1
group by t.id, g.id
order by g.number asc

he bit where I have a problem is when I'm doing the case statement to see if one of the player ( the captain ) hasn't played based on "minutes_played":

(select sum(points)
    from player_fixtures as pfix
    where gw_number = g.number and pfix.player_id = CASE WHEN pfix.minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END
) as cpts

and

(
    select max(web_name)
    from players
    join player_fixtures on players.id = player_fixtures.player_id and gw_number = g.number
    where players.id = CASE WHEN player_fixtures.minutes_played > 0 THEN g.captain_id ELSE g.vice_captain_id END
) as captain_name,

My expected result would be that if minutes_played is greater than 0 then return the row that matches gw_number = g.number and player_id = g.captain_id, otherwise return the row that matches gw_number = g.number and player_id = g.vice_captain_id

When minutes_played is indeed greater than 0, everything works fine. However, when it is equal to 0 it  doesn't return any row and thus I get a NULL in the row of my overall query.

The funny thing is that if I try with the following script on the player_fixtures where minutes_played = 0 my  greater than evaluation works and I get the correct "BAD".

DO LANGUAGE plpgsql $$
    BEGIN
        IF (select minutes_played from player_fixtures where gw_number = 19 and player_id = 266 ) > 0 THEN
            RAISE NOTICE 'GOOD';
        ELSE
            RAISE NOTICE 'BAD';
        END IF;
    END;
$$;

I'm pretty new at this whole DB thing so I'm probably making a rookie mistake but after trying to solve it myself for 6hours, I think it is time I ask for help. Could someone point me in the right direction?

Thanks!

EDIT with more info following @Mihai answer:

when I cover the 0 condition it still return 0 row. I tried with the following test data:

select *
from player_fixtures
where gw_number = 19 and player_id = case when player_fixtures.minutes_played > 0 then 266 when player_fixtures.minutes_played = 0 then 214 end

If I run

select *
from player_fixtures
where gw_number = 19 and player_id in (266,214)

I get 2 rows with the correct data so there must be a problem when I do the case statement...


texts:
I wasn't sure if changing ENUM() list isn't possible so I made a test.
In MySQL v5.1.58 I made a test InnoDB table that contains one field called 'bool' of type ENUM('yes', 'no').

Then I executed...

ALTER TABLE  `test`
CHANGE  `bool`  `bool` ENUM(  'yes',  'no',  'maybe' )
CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

...and it worked.

Have I done anything wrong? Is it dependent on db engine?Why everybody says changing a ENUM() list isn't possible? eg. here http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/


texts:
I have the column that contains text and number
how I can select/extract/return only the pattern nnnn.nn.nnnnn or null if there is no match from the column?


texts:
I have a table with more than 60mio records, where every month I have to add about 2-4 milions. It's not insert only table, I need to read from it and update recently added rows (status changes, etc.). I have spotted some of my SELECT queries begun to ommit indexes because of number of rows to be searched using criterias.
   Therefore I want to archive records older than 6 months. Is there any good way to do this? Only thing I got in mind is manual copying data to archive db and remove at master. Perhaps there are some good practices or mechanisms to do it?

Michal


texts:
I was troubleshooting some bad performance for a client yesterday and identified that there was a missing index for a particular set of queries that were heavily used

(this was for an ERP applicaton: Microsoft Dynamics NAV)

After adding these indexes database performance for the areas that were giving the client grief improved massively, however, during the visit I noticed that at times starting up the application took forever and other users were complaining of freezing and slow response (it didn't happen against all users simultaneously).

The infrastructure consists of a DB server, a service tier X 2 (that runs the application logic and caches data/business object metadata) and the client machine.

I checked the server CPU/memory stats on the service tier and it didn't look taxed at any point (CPU barely got above 50% at any one time and memory was barely touched - around 20-30%)

The SQL server was set to use unlimited memory so that needs addressing but there was actually free memory on the SQL box as all their databases combined don't exceed 10 GB and there is 12 in the server (though the page life expectancy perf counter didn't get much above 200-300 so I'm not sure why this would be)

The users mentioned this had only started happening recently (last 3 weeks) and also mentioned that other applications such as Excel were taking a long time to open files from the network so I had a look at some of the performance counters on the server and ran Brent Ozar's sp_Blitz just to check if there were any things that needed addressing immediately

The script gave me this report on the ASYNC_NETWORK_IO stats:

3437.8 hours of waits, 58.6 minutes average wait time per hour, 100.0% of waits, 0.0% signal wait, 20062055 waiting tasks, 616.9 ms average wait time.

The server has 4 virtual cores

I ran the same script on a server on another client's network (running the same solution) and the script came back with no significant wait stats

I'm suggesting to the client that since they don't need the 16 GB allocated to the service tiers (as they barely get near 4-6 GB usage) that they should consider reallocating some of the memory to SQL as their DB will grow (NAV databases tend to grow quickly), but I want to advise them best on if there could be a network issue

How do I correctly interpret these waits? Am I looking at a potential network issue as my gut says yes?


texts: Entries with 2 variables. Do I need to make 2 more tables by entries?

texts:
In the case of shipment carriers, I have a table with products whose price and delay will vary with 2 variables.

PRODUCT   | SHIPPING DATE | PRICE
Carrier 1 | A             | B
Carrier 2 |               |
...       | ...           | ...

A : Shipping date will vary with the shipment departure date
B : Price will vary with the weight of the shipment

I will need approximately 20 carriers.

I think I will have to make 2 tables by carrier:

TABLE "Carrier 1 WEIGHT-PRICE"
WEIGHT   | PRICE
0-10 kg  | 10 €
10-12 kg | 12 €
12-16 kg | 14 €
...      | ...

TABLE "Carrier 1 DEPARTURE-SHIPPING DATE"
DEPARTURE HOUR (between) | SHIPPING DELAY
00h am : 09h am          | 12h
09h am : 01h pm          | 24h
01H pm : 06h pm          | 36h
06h pm : 12h pm          | 48h

I hope there is a more optimal solution than that. Any ideas?

I will finally make the DB like this:

prices(id [pk][fk], name, min_weight, max_weight, price)

delays(id [pk][fk], name, min_hour, max_hour, delay)

produit(id [pk], id_price [fk], id_delay [fk])

Is it correct?


texts: How to retrieve specific characters within rows of database column?

texts:
Which Oracle SQL query is preferred or ideal to return the second, third and fourth positioned characters within rows of a particular column? A SQL statement using the SUBSTR method or some type of REGEX?

The following is the query I am trying to use:

SELECT REGEXP_SUBSTR(city)
FROM student.zip;

I would appreciate any recommendations-thanks!


texts: How best to index large table with many index keys

texts:
In our datawarehouse load, we bring data in the following manner.
    1. from source to stage
    2. stage to a consolidation layer
    3. from consolidation layer to operational data store (ODS)

We're bringing in several tables without an identity column, only a business key consisting of 4 to 6 columns.

Example key: BranchCode, DriverName, FromDate, Description, DbaCode

-- Option 1
Create table dbo.MyTable
(
    <5 business key columns> (clustered index)
    15-20 other columns
)

We use a merge statement to load in new data into our consolidation layer - inserting when the business key doesn't exist in consolidation and updating if it exists and the data is different (we do a hashbytes calc on each column). We store the final hashbytes result in the consolidation table.

To make merge statement perform well, we've set a unique clustered index on those 5 columns. My question is: Is there a better solution to indexing this consolidation table?

I'm worried about the wide index if we add any nonclustered indexes (which we currently don't have but I think we'll be putting in at least one). I'm also worried about fragmentation.

One option was to add a surrogate key and then add a nonclustered index on the business keys and include the rest of the columns in the table (which can sometimes be 15-20 cols). This would ensure our merge statement is efficient but this would nearly double our table size. (our tables are 200 million rows, though they are partitioned on BranchCode).

-- Option 2
Create table dbo.MyTable
(
    SurrogateKeyID (clustered index)
    <5 business columns> (nonclustered index include the other columns)
    15-20 other columns
)

Thoughts?


texts: Can I safely delete /var/log/messages-20210328 which is 383M under / directory ? OS version:Oracle Linux Server 6.7. db version Oracle 11g

texts:
the content of file is displayed using
# cat /var/log/messages-20210328

Output:
Mar 21 04:31:11 testdb snmpd[17566]: Connection from UDP: [*.*.*.274]:61775->[*.*.*.*]
Mar 21 04:31:11 testdb snmpd[17566]: Connection from UDP: [*.*.*.274]:61787->[*.*.*.*]
Mar 21 04:31:11 testdb snmpd[17566]: truncating integer value > 32 bits
...


texts: ERD for a Contract between a Writer and a Publisher: Is it correct, can it be simpler?

texts:
I created an entity-relationship diagram (ERD) for a book publishing business context that I will detail bellow.
Entity types
The entity types of interest are:

Writer
Work (as a book)
Publisher
Split (some kind of contract, representing the share of the whole Work ownership between Writers and Publishers, totalling 100%)

Business rules

A Writer can have multiple Publishers.

And a Publisher can have multiple Writers.

A Work can have one or many Writers.

And a Writer can have many Works.

Works can have one or many Publishers.

And a Publisher can have multiple Works.

For each Writer or Publisher on a Work, a Split must be be assigned.
A Split can have multiple Versions and each SplitVersion can have
different WriterPercents and PublisherPercentage.

Example
Let us suposse that Splits Version No. 1 is as follows:

Writer 1 = 50%

Writer 2 = 50%

Publisher 1 = 50%

Publisher 2 = 50%

Then, one of the Writers modifies the Splits to Version no. 2:

Writer 1 = 55%

Writer 2 = 45%

Publisher 1 = 60%

Publisher 2 = 40%

Entity-Relationship diagram
Here is the ERD I have built so far:

You can download the MySQL Workbench file that corresponds to the current schema, if necessary.
Note: This was my first model but I think some elements might not be relevant now, as per the following @MDCCL comment:

Can a specific Writer have a contract with a determined Publisher even if such publisher has issued none the Works of said writer?

And the answer is yes, did not thought of that. In this regard, should I introduce a new relationship into the diagram?
Questions
So, I would like opinions on:

Is my ERD representing the given rules correctly?

Is there some simpler layout that could solve the discussed scenario?


texts: Is there any tool which is capable of exporting MySQL query log to JSON format (or any other structured format)?


texts:
I have 2 SQL Servers in a Domain. Windows Firewall is disabled on both servers and there is no hardware firewall. Both servers are in the same subnet.

One server, called Controller is configured as a Distributed Replay Controller. The other sever, called Client is configured as a Distributed Replay Client.

The Distributed Replay Controller service is started, but when I try to start the Distributed Replay Client service, I get the following error in the Client Log:

[0xC8100005 (6)] Failed to connect controller with error code 0x800706BA.

In the Windows System log, I see the following error:

DCOM was unable to communicate with the computer controller using any of the configured protocols; requested by PID      830 (C:\Program Files (x86)\Microsoft SQL Server\120\Tools\DReplayClient\DReplayClient.exe).

The same account is running both services and this is actually a Domain Admin (Yes I know that's bad practise, but its a sandbox environment), and I have checked that the account specifically has the following DCOM permissions set on the Controller:

Local Launch
Local Activation
Remote Launch
Remote Activation
Local Access
Remote Access
and Full Control for configuration

Both servers running Windows Server 2012 Datacentre and SQL 2014 Enterprise

I have even tried giving the Domain's Eveyone group these permissions.

I have even rebuilt the Controller.

The servers can ping each other via both IP Address and Name


texts: Reasons to Backup (or not backup) QA and Dev Databases

texts:
Pretty simple question: is it standard/best practice to backup dev and QA instances of the database?

Assume that the schema information is kept in some form of source control.


texts: How to get latest timestamped result from time series in a join table


texts: Nulling a BLOB column in Oracle 11 impdp

texts: I need to import a 50 GB dump file containing three schemas. Over 90% of the file is taken up by a single BLOB column. How could I import the schemas, including the table containing the blobs, but replacing the values in the column with nulls? I could do a full import, null the column manually, do an export, delete the schemas and do a new import, but is there away to exclude a single column's data with impdp parameters?


texts: SQL Server recommended MAXDOP settings for NUMA

texts:
I have an OLTP SQL Database running on SQL 2008 R2 Enterprise Edition that may be fairly busy (5000 batches/sec) in next couple of months after going live production. Looking into server configuration I have question about setting MAXDOP however now a days what I find as a regular reader of this forum general consensus is that to leave server wide MAXDOP setting to 0 for tasks that need parallel operation (e.g. index rebuild and change cost threshold of parallelism to a higher number (15 or 20 instead of default 5).

However, this server has NUMA nodes and based on these MS support articles
http://support.microsoft.com/kb/329204

http://support.microsoft.com/kb/2023536

to prevent costly foreign memory access it is recommended to set MAXDOP = number of cores in the physical processor.

This server has 2 physical processors with 16 logical (8 physical) cores on each socket. Does guideline suggested by that MS support KB is still good?

I know everything need to be tried and tested but my goal is to set MAXDOP to the best theoretical number and adjust it if needed later on. Based on it should I set MAXDOP setting to 8?


texts: How can I make an Async Slave track all of the nodes in a Percona XtraDB Cluster?

texts:
I'm seeing a lot of tutorials that claim I have to pick just one of the nodes in my master-master replicated PXC to act as master for an async slave using vanilla mysql replication, and if something goes wrong it's "easy to change the master to one of the other nodes."  That's all well and good, but I don't want to be the one answering the pager at 2 a.m. to do a simple CHANGE MASTER TO because one of my nodes went down.  Ideally the point of an HA cluster is that all but one node can go down and all dependents are none the wiser.

It occurred to me that with GTID replication I might be able to get away with vanilla mysql multi-source replication, simply listing each node in the master-master PXC as a master to the async slave.  They all replicate the same gtid's, which would be idempotent on duplicate updates to the slave, right?  Would that work?

Or: is there a better way?

UPDATE

Based on Rick James answer I feel I must clarify.  The PXC cluster is in a single data center and is a three node HA setup all well and good on its own.  The Async Slave would be an additional 4th node, out of data center and at the end of a high latency VPN/WAN link, not running PXC/Galera at all, but instead just a regular Percona Server instance replicating over vanilla GTID/Binlog Master/Slave replication against the PXC cluster.  The goal is that it can treat each of the 3 nodes in the PXC cluster as a master.  The problem is that the tutorials I read say I have to choose only one to be the master, and thus the single point of failure - not for the cluster, but for the role of "master status" for this 4th node async slave until somebody goes and changes which master it is tracking by hand.  This is why I'm hoping to do multi-source vanilla replication treating each PXC node as its own master to the slave; as long as one stays up, the remote slave stays in sync.

I can not merely switch the slave to run PXC and make it a 4th distributed node in the cluster because it will not be replicating the entire database  but only certain tables; because it is a lower power than the existing cluster nodes; and because I do not want to slow down the cluster (which is used in high-performance production and needs ACID compliance) with the slave (which is allowed to get out of sync a little bit).


texts: PostgreSQL - How to list all foreign key link to Primary key of a table

texts:
I have a table res_users with primary key id

I would like to list all the foreign key link to id from all the tables in my database.


texts: SQL Server 2008 R2 Service Broker Troubleshooting

texts:
I set up Service Broker between two databases on separate instances, one on a 2008 R2 Cluster the other on a separate box also 2008 R2.  I have gone through the process of setting up all the objects (users, message types, contracts, queues, services, endpoints, routes, remote service bindings, and certificates), but I am getting 5 errors when I run ssbdiagnose.

ssbdiagnose -E -d MyDatabase CONFIGURATION FROM SERVICE /test/initiator TO SERVICE /test/target

http://jeffreylangdon.com/ssb.png  -- tried to post the error image, but apparently not an option since I am new to this forum.

Starting from the top error I have no idea why the Service Initiator can't be found considering I am running ssbdiagnose on the box where it resides.

Any direction would be appreciated.

Thanks,

Jeff Langdon

@jlangdon


texts: Openldap Replication don't run after configuring

texts:
I'm practicing OpenLDAP replication with 2 Centos 6.9 64bit virtual machines.

The setting-up process was fine, but after I insert data into the master server, nothing happens in the slave.

MASTER SETTING

slapd.conf

overlay syncprov
syncprov-checkpoint 100 10
syncprov-sessionlog 100
updatedn "cn=Manager,dc=example,dc=com"
updateref ldap://192.168.1.11:389

SLAVE SETTING

slapd.conf

   syncrepl     rid=2
                provider=ldap://192.168.1.10
                type=refreshOnly
                interval=00:00:00:01
                searchbase="dc=example,dc=com"
                filter="(objectClass=*)"
                attrs="*"
                scope=sub
                schemachecking=off
                updatedn="cn=manager,dc=example,dc=com"
                bindmethod=simple
                binddn="cn=manager,dc=example,dc=com"
                credentials=secret
updateref       ldap://192.168.1.10

I'm using OpenLDAP 2.4.21,  BerkeleyDB-4.8.


texts: Warn about table recreation on table change

texts:
By default, if you try to change a table using the designer making any of the following changes, you get an error message:

You change the Allow Nulls setting for a column.
You reorder columns in the table.
You change the column data type.
You add a new column.

Error message:

I know that one can turn this off by changing the "Prevent saving changes that require table re-creation' option in SSMS options.

I find that for some tables, (the ones with important data in them), I want this warning. However, for a lot of other tables, I don't care whether they get blown away and then re-created. Right now, I'm just constantly changing the setting back and forth, but what I'd really like is a way to be warned but still be given the option to proceed anyway.

Is there any setting anywhere that would cause behaviour like this?


texts: Slow scalar function that used to be fast

texts:
I have a scalar function that defined as below:

CREATE FUNCTION GetUserFullname(@Id INT)
RETURNS NVARCHAR(500)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @fullname NVARCHAR(500)

    -- Add the T-SQL statements to compute the return value here
    SELECT @fullname = (Firstname + ' ' + Lastname)
    FROM Account_Profiles
    WHERE UserId = @Id

    -- Return the result of the function
    RETURN @fullname
END

This function used to be fast when I used it in a query:

SELECT dbo.GetUserFullname(CurrentRefId) FROM OrderItems

The query above has a result of ~50,000 rows. the query used to be executed in 2 seconds but now all of sudden it takes 22 seconds to run!

It is slow for all distinct values of CurrentRefId in OrderItems, when I query SELECT dbo.GetUserFullname(CurrentRefId) FROM OrderItems. There are statistics updated every night. There is also an index with Total Fragmentation 75.

I also have a copy of this database in my local SQL Server and it does run very fast in ~1 second.

My production database server has 512 GB memory and 64 processors. It is an Always On Failover Cluster server and Availability Group. The database version is 2012 SP2.


texts: FLUSH TABLES WITH READ LOCK not working?

texts:
I use this command to "lock" my database, flush logs , take a LVM snapshot and release it.
Testing the recover + binlogs , appear it's not working.
(I discovery this when during the recover, applying the binlogs give error of  duplicate key where supose not should exists at that moment)
The behave I get is the data files still be writting after the flush.
So , what's wrong here?
I miss something?
# echo "FLUSH TABLES WITH READ LOCK ; flush logs" | mysql -vvv
--------------
FLUSH TABLES WITH READ LOCK
--------------

Query OK, 0 rows affected (0.00 sec)

# mysql --version
mysql  Ver 14.14 Distrib 5.6.25, for Linux (x86_64) using  EditLine wrapper

I not sure , but after flush with read lock , if I connect to any database and try create a table, it's should lock and wait until the UNLOCK , don't ?
This isn't happen, it create the table successfully.
EDIT
I found a documentation where say to run SET GLOBAL read_only = ON after the flush and SET GLOBAL read_only = OFF.
All other suggestions, blogs, tutorials, manuals don't mention this is needed.
More doubts!!!
EDIT 2
Insert and updates still working too...


texts: Upgrading postgres to 10 from 9.5 using pg_upgradecluster

texts:
I am testing the process of upgrading postgres to 10 from 9.5

I am following the steps as mentioned in this link -

dmanna@ubuntu:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
10  main    5433 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

sudo pg_dropcluster 10 main --stop
sudo pg_upgradecluster 9.5 main -m upgrade
sudo pg_dropcluster 9.5 main

Can someonet let me know the following

If there is a way I can check if the upgrade will be successful
before going for the actual upgrade?
If I am using the link option
in the upgrade can I still use the sudo pg_dropcluster 9.5 main?