texts: Why does Cassandra recommend against creating an index on high-cardinality columns?

texts:
The Cassandra documentation states,

  Do not use an index in these situations:

  On high-cardinality columns because you then query a huge volume of records for a small number of results. See Problems using a high-cardinality column index below.

It goes on,

  If you create an index on a high-cardinality column, which has many distinct values, a query between the fields will incur many seeks for very few results. In the table with a billion songs, looking up songs by writer (a value that is typically unique for each song) instead of by their artist, is likely to be very inefficient. It would probably be more efficient to manually maintain the table as a form of an index instead of using the Cassandra built-in index. For columns containing unique data, it is sometimes fine performance-wise to use an index for convenience, as long as the query volume to the table having an indexed column is moderate and not under constant load.

But never really answers the question: why is it inefficient? I have no idea what "manually maintaining the table as a form of an index" means. But then it somewhat contradicts itself with "…it is sometimes fine performance-wise to use an index for convenience as long as the query volume is moderate…"

Is this just trying to tell me to use the PK when and where I can? What's the inefficiency? My understanding is that a query that would hit an index would need to query every¹ node in the cluster, and then each node would do a lookup in its local index and the results would then get aggregated. This is not necessarily expensive (each index lookup should be fairly cheap) except that we pay in network latency, as we must wait for the slowest node of the lot. Am I missing anything here?

But if I have a collection that has a bajillion items that — on rare occasion — needs to be looked up by a different but almost unique attribute … this is an appropriate use, right?

¹Every? IDK if replication means that this can hit 1/3 of the cluster for a replication factor of 3 or not?


texts:
Is there a way to report on all the custom roles and/or responsibilities created within Oracle, Outside of SQL?

I am looking for a dashboard that shows how many have x role, y role that could be sent to senior level management.


texts:
I have the following code:

DECLARE @Latest TABLE
(
    DomainId bigint,
    [date] datetime2
)

DECLARE @Rows int
DECLARE @History int

INSERT INTO @Latest (DomainId, date)
SELECT DomainId, MAX(Date)
FROm DomainDetailDataHistory
GROUP BY DomainId

SELECT @Rows = COUNT(DomainId) FROM @Latest

SET @History = @Rows * 5

SELECT DISTINCT TOP(@History)
  c.DomainId
  , c.[Date]
  , Passed
FROM DomainDetailDataHistory c
JOIN  @Latest h ON h.DomainId = c.DomainId
WHERE c.[Date] != h.[Date]
ORDER BY c.Date DESC

This code gets me the latest inserted row given by Id and the last 5 insertions.

Problem:

This works all good when I have 5 insertions of every unique DomainId.
I tried adding a new one and since the new one did not have any prior insertions the code gave me the top 7 insertions to compensate for the extra domain.

Clarity:

@History gives me how many different domains I have. I multiply this by 5 to get 5 of each.

Problem is when one of the domains do not have 5 insertions, lets say it only has one then I get more than the top 5 of the other domains.

How do I make sure that I always only get 5 of each and NOT MORE?


texts: Creating a SQL Agent Job using Dynamic SQL

texts:
I'm currently putting together Agent Jobs for Database backups, using the Ola Hallengren scripts. I've put together a script that creates an Agent Job to perform FULL backups and it's working perfectly.

Now, I want to make the script Dynamic. Why? Each environment will have a different Certificate for encrypting the backups and the Cleanup times may vary.

This is what I have thus far;

DECLARE @SQLCertificate NVARCHAR(50)
SET @SQLCertificate = N'00132'

EXECUTE ('
IF NOT EXISTS (SELECT [Name] FROM [dbo].[sysjobs] WHERE [Name] = N''Backups_Encrypted_FULL'')
BEGIN
    EXECUTE sp_add_job
        @job_name = N''Backups_Encrypted_FULL'',
        @enabled = 1,
        @notify_level_eventlog=2,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @owner_login_name = N''DBA_Maintenance'',
        @description = N''Performs a FULL backup of all User Databases'',
        @category_name = N''[Database Backups]''

    EXECUTE sp_add_jobstep
        @step_id=1,
        @job_name = N''Backups_Encrypted_FULL'',
        @step_name = N''Execute T-SQL'',
        @subsystem = N''TSQL'',
        @command = N''EXECUTE [dbo].[DatabaseBackup]
    @Databases = N''USER_DATABASES'',
    @Directory = N''C:\SQLBackup'',
    @DirectoryStructure = N''{DatabaseName}'',
    @FileName = ''{DatabaseName}_{Description}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'',
    @BackupType = ''FULL'',
    @Description = N''Full_Backup'',
    @CheckSum = ''Y'',
    @Verify = ''Y'',
    @Compress = ''Y'',
    @CleanupTime = ''0'',
    @CleanupMode = ''AFTER_BACKUP'',
    @Encrypt = ''Y'',
    @EncryptionAlgorithm = ''AES_256'',
    @ServerCertificate = '''+ @SQLCertificate +''',
    @LogToTable = ''Y'''',
        @database_name = N''DBA.Maintenance''

    EXECUTE sp_add_jobschedule
        @job_name = N''Backups_Encrypted_FULL'',
        @name = N''Saturday Night'',
        @enabled = 1,
        @freq_type = 8,
        @freq_interval = 64,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_end_date=99991231,
        @active_start_time=220000,
        @active_end_time=235959

    EXECUTE sp_add_jobserver
        @job_name = N''Backups_Encrypted_FULL'',
        @server_name = N''(Local)''
END ')

GO

Running it however, gives the following error;

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'USER_DATABASES'.

Anyone have any thoughts on this?


texts: SSRS 2017 add datasource "Something went wrong"

texts:
when trying to add, view or change data source(s) in SSRS 2017 I get popup saying "Something went wrong, please try later".
Setup os SSRS is as follows:
three SSRS instances behind KEMP load balancer. Communication from clients to KEMP is done over SSL (port 443) and from kemp to SSRS instances over HTTP (port 80). rsreportserver.config file has this tags on all three SSRS servers, where SSRS.domain.com points to the KEMP virtual IP (for client access).
SSRS.domain.com
https://SSRS.domain.com/reportserver
When I try to add, view or change data source(s) I get this error in RSPortal.log
ERROR: OData exception occurred: System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 10.2.17.11:443
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Exception& exception)
--- End of inner exception stack trace ---
10.2.17.11 is the IP of the one of SSRS instances buth what baffles me is why is connection trying to be made using port 443 (TLS) when KEMP is configured to communicate with SSRS instances over port 80.
SSRS instances don't have port 443 configured, only port 80.
Everything else works fine, only datasources are the problem.
SSRS version in question is 14.0.600.1109, native mode.
Any help would be appreciated.


texts: Automatic fail over in Always on availability group with 3 Node in synchronous mode and 1 Node in Asynchronous Mode

texts: I am laying out a plan where there will be 3 Node on site with automatic fail over with synchronous mode and 1 remote replica with asynchronous mode. My question is if the primary goes down which node among the remaining 2 replica will be failed over. I will have a file share for node majority with file share quorum.


texts: sql agent job: How to set max run time for powershell step

texts:
sql server 2014

We hvae a powershell step that does a robocopy operation that sometimes hangs for unknown reasons.

Can we set a max run time, either at the robocopy command level, or at the job step level?

(windows OS task scheduler has something like this...)


texts: Decrease size of database (with an expression index)?

texts:
This is my current table definition in a Postgres 10.1-1 database:

CREATE TYPE CUSTOMER_TYPE AS ENUM
('enum1', 'enum2', 'enum3', '...', 'enum15');     -- max length of enum names ~15

CREATE TABLE CUSTOMER(
   CUSTOMER_ONE    TEXT PRIMARY KEY NOT NULL,     -- max 35 char String
   ATTRIBUTE_ONE   TEXT UNIQUE,                   -- max 35 char String
   ATTRIBUTE_TWO   TEXT,                          -- 1-80 char String
   PRIVATEKEYTYPE  CUSTOMER_TYPE                  -- see enum
);

It results in about 4.3x more database size compared to the size of the inserted data. (50 MB, 700.000 lines --> database size is 210 MB)

Attribute_One is computed as hash(Customer_One).

Requirements: fast searches (using algorithms) for columns CUSTOMER_ONE and ATTRIBUTE_ONE. (That's why I think I need an index.)

Typical search query:

select * from customer
where Customer_One='XXX' OR Attribute_One='XXX';

Each SELECT can find a maximum of 1 or 0 matching rows in millions of rows.

Is it possible to further decrease the DB size? I have been told to use an expression index but don't fully understand how this works. A short explanation with an example index or other solution would be great

Is the insert speed effected by those indexes? The faster the better. (To be clear: search speed is more important than insert speed.)


texts: Why is the transaction log not truly circular?

texts:
I did a little bit of hands-on research recently about how the transaction log in sql server works.  I came to a problem that I can't get my head around.

First I created a new test database with full recovery mode and created an empty test table in it.  Then I did an initial full backup and also afterwards a first tlog backup. This resulted in the following DBCC LOGINFO output:

So far so good. Afterwards I inserted several rows, to make sure the first three VLFs are in use. As my next step I did another tlog backup and manual checkpoint, so that the first two VLFs were truncated which resulted in this DBCC LOGINFO:

Then I added more rows, so that the transaction log wrapped around and the database engine used the first VLF again. In addition to that I added more and more rows and finally the second VLF was full. Since the third VLF was not yet truncated the transaction log grew and four additional VLFs were added. After all of this I did the next transaction log backup and also a manual checkpoint, which led finally to this:

Now what bothers me is what happened next. I thought that when I make so many changes, that the last VLF (here in row 8) will become full, the log will wrap around again and will start to write to the first VLF again (here with FSeqNo 41), since this VLF is already truncated. I expected this, because in " https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver15 " they suggest, that the transaction log is of a circular nature. But what really happened is, that the database engine wrote to the next VLF with the lowest FSeqNo:

Why is this behavior contrary to the Microsoft docs or, didm I just not understand the it right?  Any help or explanation would be appreciated.


texts: MySQL: What exact commands does each privilege level allow?

texts:
I am configuring some new logins for users who have already been using the relevant MySQL servers under other logins. I would like to limit their privileges to the minimum each user needs, based on their input and reviewing the logs of what commands they have been executing. I would therefore like to know exactly what commands are allowed by each grantable privilege.

The specification in MySQL's documentation provides some insight beyond the obvious (e.g. "The SELECT privilege enables you to select rows from tables in a database.") but it seems like the information given there is not exhaustive. For example, I see in the logs that a user with only Select_priv enabled also used SHOW VARIABLES and SET but I don't see any documentation of those commands being included in the SELECT privilege.

This answer and this question give clues that there is more to the privileges than their names would directly imply.

UPDATE: @Colin 't Hart's comment is informative in that it explains the ability to execute SHOW and SET regardless of privileges. So my question should be limited to server side commands. I.e. Are there commands not listed in the specification which can be used by users with a given privilege?


texts: Primary Key Index on a non-filtered column has a very high total lock wait time

texts:
I'm seeing the

  Aggressive Indexes: Total lock wait time > 5 minutes (row + page) with
  short average waits

warning via BlitzIndex on one of our tables, and I cannot quite understand what could be happening. The index in question is related to a primary key on our table:

ALTER TABLE [Authentication].[Tokens]
ADD CONSTRAINT [PK_Authentication.Tokens]
PRIMARY KEY CLUSTERED ( [TokenID] )

And for completeness, here's the gist of the table structure:

CREATE TABLE [Authentication].[Tokens](
    [TokenID] [int] IDENTITY(1,1) NOT NULL,
... other columns here)

BlitzIndex reports the following stats: Reads: 10,066,849 (2,259,000 seek 934,476 scan 6,873,373 lookup) Writes:1,399,277, 314 rows; 1.1MB

I'm somewhat confused. We don't query/filter off of this column (like SELECT ... WHERE TokenID = 1). My only guess is maybe the table is being read quite often, and lots of queries are slamming against each other using the default SQL locking strategy?

I'm open to any advice or feedback here, for a table that seems simple and benign.


texts: Do values in `sys.dm_os_wait_stats` reset to 0 or stop accumulating when they hit the max value?

texts:
When looking at sys.dm_os_wait_stats, the following columns are defined as BIGINT

waiting_tasks_count
wait_time_ms
max_wait_time_ms

If any of those values go above 9,223,372,036,854,775,807, do the counters reset to 0, or simply stop counting?

It's unclear from the table definition what goes on behind the scenes:

sp_helptext 'sys.dm_os_wait_stats'

Returns:

CREATE VIEW sys.dm_os_wait_stats AS
 SELECT *
 FROM OpenRowset(TABLE SYSWAITSTATS)

So that's a bit of a black box.

There are some DMVs where large numbers can turn negative. One example of this is the total_elapsed_time column in dm_exec_requests.


texts: Is it expected behavior that MIN() window function in MariaDB/MySQL can return a non-null value from a window with only null values?

texts:
A minimal example for MariaDB (reproduced with with 10.2 and latest):

DROP TABLE IF EXISTS t;
CREATE TABLE t
(
    id    int unsigned not null,
    value int unsigned null
);
INSERT INTO t
values (1, null);

CREATE OR REPLACE VIEW v
as
select 1                 as id,
       ifnull(null, 999) as value
from t
;

select *,
       MIN(t.value) over (PARTITION BY t.id) as min_t,
       MIN(v.value) over (PARTITION BY t.id) as min_v
from t
         left join v
                   on t.id > v.id
;

It returns

id value id   value min_t min_v
1  null  null null  null  999

bit I would expect it to return

id value id   value min_t min_v
1  null  null null  null  null <-- null instead of 999

because there is only a single row and it has value = null in the window for MIN function. Sure, it is backed by a ifnull() in view's select, but the resulting set has null from a left join with no corresponding row, so it shouldn't be handled by ifnull() in the view, right?

Can this be considered a bug?

MySQL sqlfiddle example comparing using a view vs using nested selects. Nested selects produce the result I expect, using view doesn't.

Comparing to other RDBMS:

Oracle example: both ways work as I expect.

PostgreSQL example: both ways work as I expect.


texts: What does "Sending data" mean in the mysql process list?

texts: I'm seeing a query getting stuck in the process list for long period of times with the Status of "Sending data". What does this status mean?


texts:
We think about moving from a master-slaves to MongoDB with replica-set.
Now I wonder if MongoDB replicas are also set up like read-only slaves or if each mongos is able to write and sync the others like a master-master farm?

This might reduce the single source of error (master down) and also balance the write-load for scaling with best performance (not only for read, but also write...).


texts: MySQL: How to calculate potential wait time?

texts:
I'm trying understand the relationship between WAIT_MS and COUNT_STAR. Is this saying the average wait time is 3.2s and occurred 11,543 times for a total of approximately 10.5hrs of aggregate wait time?

Your response is greatly appreciated.

| EVENT_NAME                           | WAIT_MS   | COUNT_STAR |
+--------------------------------------+-----------+------------+

| wait/io/file/innodb/innodb_data_file | 3294.3345 | 11543      |

UPDATE: Sorry! It was late. Per Mark Leith's blog: http://www.markleith.co.uk/2010/09/24/tracking-mutex-locks-in-a-process-list-mysql-55s-performance_schema/

SELECT
   EVENT_NAME, SUM_TIMER_WAIT/1000000000 WAIT_MS, COUNT_STAR
FROM
   performance_schema.events_waits_summary_global_by_event_name
ORDER BY
   SUM_TIMER_WAIT DESC,
   COUNT_STAR DESC;


texts: Command line switch to disable Coherence?

texts:
We have a Java app that happens to have the coherence jar in the path.

My application doesn't need it, so I'd like to pass some type of -D variable to the JVM to tell it to ignore coherence, or perhaps send it to a no-op cache / tangsol config file?

I've been on the Oracle site but haven't found this yet.  I admit I don't know much about Coherence.  Removing the jar from the app would be awkward at best, it'd be better to handle this through configuration.

Thannks


texts:
I have such query:

SELECT "Movie"."title",
       "Movie"."id",
       "Movie"."publicationDate",
       "Movie"."authorID",
       "Author"."photoURL" as "authorPhoto",
       concat("Author"."name", ' ', "Author"."surname") as "authorName",
       "Movie"."seriesID",
       "Series"."name" as "seriesName",
       "Series"."logoURL" as "seriesLogo",
       "Movie"."languageID" as "language",
       "Movie"."ratingUp",
       "Movie"."ratingDown",
       "userWatched"."watchedDate",
       CASE WHEN "UserMovieFavourited"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
            THEN TRUE
            ELSE FALSE
            END as "favourited",
       CASE WHEN "UserMovieWatchlist"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
            THEN TRUE
            ELSE FALSE
            END as "onWatchlist",
       CASE WHEN "userWatched"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' AND "userWatched"."watchedDate" IS NOT NULL
            THEN TRUE
            ELSE FALSE
            END as "watched",
            count(DISTINCT "overallWatched"."watchedDate") as "viewCount"
FROM "Movie"
LEFT JOIN "Series" ON "Series"."id" = "Movie"."seriesID"
LEFT JOIN "Author" ON "Author"."id" = "Movie"."authorID"
LEFT JOIN "UserMovieFavourited" ON "UserMovieFavourited"."movieID" = "Movie"."id"
                                AND "UserMovieFavourited"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
LEFT JOIN "UserMovieWatchlist" ON "UserMovieWatchlist"."movieID" = "Movie"."id"
                               AND "UserMovieWatchlist"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
LEFT JOIN "UserMovieWatchProgress" as "userWatched" ON "userWatched"."movieID" = "Movie"."id"
                                                    AND "userWatched"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
LEFT JOIN "UserMovieWatchProgress" as "overallWatched" ON "overallWatched"."movieID" = "Movie"."id"
GROUP BY "Movie"."id",
         "Author"."photoURL",
         "Author"."name",
         "Author"."surname",
         "Series"."name",
         "Series"."logoURL",
         "userWatched"."watchedDate",
         "UserMovieFavourited"."userID",
         "UserMovieWatchlist"."userID",
         "userWatched"."userID"

In MySQL i needed only one column for grouping that's Movie.id. For postgresql I needed to add 10 columns to make this query work as shown above.

Is this natural for postgresql or this query should be refactored to remove need of so many grouping columns?


texts:
I am trying to convert a postgresql query to ms sql query.  This is what I have in postgresql
round(cast((Current_Date - emp.hiredate) As Decimal)/365,1)

now I thought it would be a simple conversion and I tried to change it for MS SQL to

round(cast((GetDate() - emp.hiredate) as decimal) / 365, 1)

but in MS SQL Server I get this error

  The data types date time and date are incompatible in the subtract operator


texts: making everything varchar on purpose, though some fields should be int

texts:
I'm writing a PHP website for some clients which parses excel, stores the value in a database and then retrieves them.

Some fields are only integers, but the clients can't make up their minds, they keep making changes to excel, so I don't really know what they would do.

I made everything varchar, anticipating them to screw things up and then update it later when they realize what they did. I'm overnighting the database each time they upload new file so they only have to fix it from the client side. The  website isn't going to scale in my life time.

Isn't that better than throwing errors at them from a practical point of view, if they change the excel, I only have to change the PHP, rather than fixing the database too. Is it that bad or not?


texts: pg_upgrade fails with lc_ctype cluster values do not match

texts:
I'm upgrading my PostgreSQL version 9.1.4 database to version 9.2.4. Both the old and the new version are the bundled versions of postgresapp.com for Mac OS X.

When trying to upgrade the database I get this error:

# pg_upgrade -b /tmp/Postgres.app/Contents/MacOS/bin \
-B /Applications/Postgres.app/Contents/MacOS/bin \
-d var-9.1 \
-D var

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating catalog dump                                       ok

lc_ctype cluster values do not match:  old "de_DE", new "de_DE.UTF-8"
Failure, exiting

I searched this error message and found no useful tip to fix this. Any idea?


texts: How do I modify a column type if the DB has many values

texts:
I'm currently trying to convert a column in SQL Server from nvarchar(255) to decimal(18,0). However, this is the error I get:

  'member_details' table
  - Unable to modify table.
  Error converting data type nvarchar to numeric.

Before this error, Management Studio gave a warning about changing the datatype of columns with many rows. My DB contains about 1,000,000 rows.

Can anybody help? Any help will be greatly appreciated.


texts: Query produces two query plans (at different times). How do I always produce the better query plan?

texts:
I have a fairly straightforward query that usually performs well (i.e. returns in a couple of seconds) but sometimes it will timeout and I need to flush the query plan before it will start behaving again.

I was able to get both query plans through SQL Profiler - so I know what a good plan looks like, but I'm not sure how to encourage SQL Server to always produce the better plan.

The query plans are very similar (and big) so I will include a screenshot of only the parts that are different.  I can post the entire XML if requested.

Partial query plan - before flush (bad performance)

Edited: Link to full query plan (sorry, it doesn't seem to have rendered as well as some query plans I've seen)

Partial query plan - after flush (good performance)

Edited Link to full query plan

Query

SELECT Records.[Id],
   Property.Id AS [PropertyId],
   CASE
       WHEN Records.Id IS NOT NULL THEN Records.[Name]
       ELSE '(In Transit)'
   END AS [Name],
   Records.[Area],
   Records.[AreaUnitId],
   Records.[Lng],
   Records.[Lat],
   Records.[Zoom],
   Records.[Code],
   Records.[Guid],
   [Property].[SecurityId],
   Records.[DateCreated],
   Records.[Modified],
   Records.[DateModified],
   Records.[ModifiedByUserId],
   Records.[SortOrder],
   Records.[Deleted],
   COALESCE(Property.Name, '') AS PropertyName,
   COALESCE(Unit.Name, '') AS UnitName,
   COALESCE(Unit.Code, '') AS UnitCode,
   COALESCE(AggregateData.Head, 0) AS Head,
   COALESCE(AggregateData.TotalWeight, 0) AS TotalWeight,
   CASE
       WHEN COALESCE(AggregateData.Head, 0) > 0 THEN AggregateData.TotalWeight/AggregateData.Head
       ELSE 0
   END AS AverageWeight
FROM
(
    SELECT Pen.Id,
       Pen.PropertyId
    FROM Pen
    LEFT JOIN [Security] O ON Pen.SecurityId = O.Id
    WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
    AND (@Deleted IS NULL
      OR Pen.Deleted = @Deleted)

   -- Add null row per property, so animals in transit are included further down
   UNION ALL
   SELECT 0 AS Id,
      Property.Id AS PropertyId
   FROM Property
   LEFT JOIN [Security] O ON Property.SecurityId = O.Id
   WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
   AND Property.[External] = 0
   AND Property.Deleted = 0
) AS PenIds
LEFT JOIN
(
   SELECT L.PropertyId,
       COALESCE(L.PenId, 0) AS PenId,
       COUNT(M.BeastId) AS Head,
       SUM(WeightLog.[Weight]) AS TotalWeight
   FROM BeastMaster M
   LEFT JOIN BeastMovement L ON M.BeastMovementId = L.Id
   LEFT JOIN WeightLog ON M.WeightLogId = WeightLog.Id
   LEFT JOIN [Security] O ON L.SecurityId = O.Id
   WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
   AND (@Soh IS NULL
      OR M.Soh = @Soh)
   AND (@PropertyId IS NULL
      OR L.[PropertyId] = @PropertyId)
   GROUP BY L.PropertyId,
        COALESCE(L.PenId, 0)
) AS AggregateData ON PenIds.Id = AggregateData.PenId
   AND PenIds.PropertyId = AggregateData.PropertyId
LEFT JOIN [Pen] AS Records ON Records.Id = PenIds.Id
LEFT JOIN Property ON PenIds.PropertyId = Property.Id
LEFT JOIN Unit ON Records.AreaUnitId = Unit.Id
WHERE (@Id IS NULL
   OR PenIds.[Id] = @Id)
AND (@PropertyId IS NULL
   OR PenIds.[PropertyId] = @PropertyId)
AND
(
   @ManagementAreaId IS NULL
   OR EXISTS
   (
      -- Properties linked to Management Area
      SELECT *
      FROM [Permission] Pm
      WHERE Pm.Deleted = 0
        AND Pm.UserGroupId = @ManagementAreaId
        AND Pm.RequestedSecurityId = Property.SecurityId )
)
ORDER BY Records.Name


texts: How to design normalized relations to store “global/generic” data?

texts:
Context

I was designing a relational database model to store data about a company and its production facilities. I had a facility relation and many other relations referencing it, most of the time with an identifying relationship.

Here is a simplified extract:

-- PostgreSQL
CREATE TABLE facility(
    facility_id integer NOT NULL,
    designation text NOT NULL,
    CONSTRAINT pk_facility PRIMARY KEY (facility_id),
    CONSTRAINT uq_facility UNIQUE (designation)
);
CREATE TABLE facility_description(
    facility_id integer NOT NULL,
    description text NOT NULL,
    CONSTRAINT pk_facility_description PRIMARY KEY (facility_id),
    CONSTRAINT fk_facility_description_facility FOREIGN KEY (facility_id)
        REFERENCES facility (facility_id) MATCH SIMPLE
        ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE facility_area(
    facility_id integer NOT NULL,
    area_id integer NOT NULL,
    designation text NOT NULL,
    CONSTRAINT pk_facility_area PRIMARY KEY (facility_id, area_id),
    CONSTRAINT uq_facility_area UNIQUE (facility_id, designation),
    CONSTRAINT fk_facility_area_facility FOREIGN KEY (facility_id)
        REFERENCES facility (facility_id) MATCH SIMPLE
        ON DELETE RESTRICT ON UPDATE CASCADE
);

Requirements evolved and it was later decided that each facilities would have their own dedicated database. Put it differently, the database would now be specific to a single facility.

I could keep the exact same model but it seems like premature pessimization to keep this facility_id attribute in the tables since it will always have one single value. So, it would be easy to redesign facility_area as area:

CREATE TABLE area(
    area_id integer NOT NULL,
    designation text NOT NULL,
    CONSTRAINT pk_facility_area PRIMARY KEY (area_id),
    CONSTRAINT uq_facility_area UNIQUE (designation)
);

But if I remove facility_id from facility and facility_description then I end up with single-row tables without a primary key, which, I think, does not respect the relational model theory.

CREATE TABLE facility(
    designation text NOT NULL,
    CONSTRAINT uq_facility UNIQUE (designation)
);
CREATE TABLE facility_description(
    description text NOT NULL
);

Moreover, in this proposal, since there is no foreign key referencing facility from facility_description, then nothing prevent a description to exist without a corresponding facility, which would have been considered a problem in the initial model.

Question

So, what is the correct way to store those “global/generic” data such as the designation of the single facility and its optional corresponding description? And by correct I mean by designing relations that respect the Fifth normal form (5NF).


texts: How to find current roles available on a database

texts:
I am looking to provide grant to one of my function in my database.
However, I am not sure what roles I should give access to.

I searched for it but can't find list of roles for all the users.

Any idea guys as which table I should be looking into.

I am using Oracle 11g.

Thanks in Advance :)


texts: MongoDB - Unable to change mongodb storage engine to WiredTiger

texts:
I have installed MongoDB in Ubuntu 14.04 but I am unable to change its storage engine to WiredTiger.

I have added the necessary changes to the /etc/mongod.conf file which is namely as the following-:

storage:
  dbPath: /var/lib/mongodb
  journal:
    enabled: true
  engine: wiredTiger

I have started the mongod process using the following command-:

mongod - f /etc/mongod.conf

The server starts up but I still get the warning when I connect to my shell-:

2017-01-10T15:36:54.866+0530 I STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine

Am I missing something over here ? The changes seem to have no effect.


texts:
I am trying to run the SQL queries coded on my friend's laptop onto mine. I have imported the .sql file into SQL developer and I'm trying to run it.However, I keep getting the error:

01435. 00000 -  "user does not exist"

I have listed all the users:

select * from all_users;

and I get:

OUTLN                                   9 30-MAR-10
SYSTEM                                  5 30-MAR-10
SYS                                     0 30-MAR-10

among other users.
My SQL query is :

  CREATE OR REPLACE TYPE "HOMEWORK1"."INNERTABLE" IS TABLE OF VARCHAR2(128);

I am using Oracle 11g on Win8. Please tell me if I need to post anything else to help you.
Thanks a lot


texts: MySQL - Split results of count function into columns derived from a separate column

texts:
The title seems a bit confusing, but I am struggling to put down what I need this query to do so best to explain it. I have 3 tables in my database (Using MySQL Workbench), but for this query I'm just trying to use one.
The table named service_data has the following columns:

Services_ID|Service_Type|Day|Time|Customer_ID(FK)
1001       |SERVICE1    |Mon|0950|1
1002       |SERVICE2    |Tue|1032|65
1003       |SERVICE3    |Wed|0859|4

The table contains approx 200 records, my aim is to group the timings together, which I have managed to achieve by doing this:

select
case
WHEN (Delivery_Time between '08:00:00' and '09:00:00') then '0800-0900'
WHEN (Delivery_Time between '09:00:00' and '10:00:00') then '0900-1000'
WHEN (Delivery_Time between '10:00:00' and '11:00:00') then '1000-1100'
WHEN (Delivery_Time between '11:00:00' and '12:00:00') then '1100-1200'
WHEN (Delivery_Time between '12:00:00' and '13:00:00') then '1200-1300'
WHEN (Delivery_Time between '13:00:00' and '14:00:00') then '1300-1400'
WHEN (Delivery_Time between '14:00:00' and '15:00:00') then '1400-1500'
WHEN (Delivery_Time between '15:00:00' and '16:00:00') then '1500-1600'
WHEN (Delivery_Time between '16:00:00' and '17:00:00') then '1600-1700'
WHEN (Delivery_Time between '17:00:00' and '18:00:00') then '1700-1800'
WHEN (Delivery_Time between '18:00:00' and '19:00:00') then '1800-1900'
WHEN (Delivery_Time between '19:00:00' and '20:00:00') then '1900-2000'
WHEN (Delivery_Time between '20:00:00' and '21:00:00') then '2000-2100'
      else 'Outside Opening Hours'
  end as `Time Period`,
   count(0) as 'count'
from service_data
group by `Time Period`
order by count desc
limit 20;

Which produces the below result:

TimePeriod  Count
1700-1800   24
1500-1600   21
1200-1300   19
1400-1500   19
1800-1900   17
1100-1200   17
1300-1400   16
1600-1700   16
1000-1100   16
1900-2000   12
0800-0900   12
0900-1000   11

What I am now trying to do is split the count up so that there are 4 columns labelled SERVICE1 SERVICE2 SERVICE3 and SERVICE4 (the values within the Service_Type column), so that it looks something like this:

TimePeriod|SERVICE1|SERVICE2|SERVICE3|SERVICE4
1700-1800 |   6    |   7    |    10  |   1
1500-1600 |   5    |   9    |    1   |   6
1200-1300 |   0    |   4    |    2   |   13`

Is this possible!?  Any help would be appreciated.


texts:
I have a table with 45M rows (45 GB data space and 2GB Index space). I added a new column and it finished instantly.

alter table T add C char(25)

Then I found the size is too small so I run the following query.

alter table T alter column C varchar(2500)

And it runs one hour and is still running. sp_whoisactive shows (at the moment, still running)

reads: 48,000,000
writes: 5,000,000
physical reads: 3,900,000

Shouldn't it be really fast?


texts: Microsoft SQL Server v13 bulk add users from active directory

texts:
I would like to import all users from Active Directory into Security/Users.

What I do is the following:

Go to Security -> Users
Right click Users -> New User...
User type: Windows user, click on '...' at User Name
Define object type to User and location to my Active Directory one
Click on Find Now

Now I can see all users in this specific domain. What I cannot do is select more than one user from this list (e.g. by holding shift). So basically I would have to do it user by user. Since there are many users that would take a long time. And I am also wondering that there should be a 'smarter' way to do it.

Am I missing something? Am I doing something wrong?

Thanks for the help!


texts: Calculate Average between two dates of different rows

texts:
I have the below table

BIDID AppID AppStatus   Time
23390   16  In Review   2017-07-03
23390   16  Approved    2017-09-03
23390   16  In Review   2017-10-11
23390   16  Approved    2017-12-11
23390   16  Approved    2017-14-11

I want to calculate the time difference according to the following logic

First find the In Review status and then get the next approved status and then calculate the time difference. Then get the next In Review status and then next Approved and calculate the time difference. Then get an average.

So in this case what I am looking for is

Say

2017-07-03  - 2017-09-03  - 2 days
    2017-10-11  - 2017-12-11   -2 days

Last Approved is ignored because there was no matching In review before it

Then get total average (2 + 2) / 2 = 2

Can someone please tell me how I can achieve this.

Thanks


texts:
I have the following query (simplified from an Entity Framework query, I don't have direct control of the SQL):

SELECT TOP 15
HouseHoldingRuns.Id,
(
    SELECT TOP 1 ManCoId FROM (
        SELECT TOP 1 GridRuns.Id
        FROM GridRuns
        WHERE HouseHoldingRuns.Id = GridRuns.HouseHoldingRunId
    ) AS [FirstGridRun]
    INNER JOIN Documents ON FirstGridRun.Id = Documents.GridRunId
    ORDER BY Documents.Id
)
FROM HouseHoldingRuns
ORDER BY HouseHoldingRuns.Id

Which produces the following plan:

Adding a WITH (FORCESEEK) on the Documents table results in the following plan, which performs much better.

Why is SQL Server getting it so wrong?  I'm guessing the problem lies in the large difference between estimated and actual rows, but I don't know how to fix that... (I've tried updating all stats and rebuilding indexes).

Note: I don't really want the ORDER BY Documents.Id in there, but put it in for Evgeny to see the results of his suggestion.


texts: How can I DENY a SQL Server Database User from seeing tables in a specific schema? VIEW DEFINITION doesn't seem to work

texts:
This is somewhat of an X/Y Problem: my original problem is that I want to use SSDT to maintain my database design, with the catch that my database design uses two schemas:

The dbo schema that contains tables and views that are defined in my SSDT project.

All of these tables, views and procedures are defined in the .sqlproj.

Another schema, dynamic, which contains tables and views that are managed by the application code itself and should not be managed by SSDT.

None of these objects, like CREATE TABLE dynamic.DataForMay2021, exists in the .sqlproj.

The problem is that SSDT's Schema Compare feature will always compare the entire SQL Server database to the .sqlproj SSDT project - which means it will look for the dynamic.DataForMay2021 table in the .sqlproj (which doesn't exist) so it generates a DROP TABLE dynamic.DataForMay2021 (which I don't want), which means I have to manually Exclude all objects in the Schema Comparison window before generating the migration script.
I reasoned that a way around this shortcoming is to run the SSDT Schema Comparison under a different LOGIN/USER that only has access to the dbo schema and not the dynamic schema.
I was able to create a new LOGIN and USER, however I haven't been able to successfully hide the dynamic schema from that user or login, despite trying these steps:
-- Connected to `master` using my Azure DBA:
CREATE LOGIN [SsdtSchemaCompareLogin] WITH PASSWORD=N'*****';

-- Disconnect and reconnect to my database:
CREATE USER [SsdtSchemaCompareUser] FOR LOGIN [SsdtSchemaCompareLogin] WITH DEFAULT_SCHEMA=[dbo]

-- Prevent `SsdtSchemaCompareUser` from seeing any of the `dynamic` schema objects:

DENY SELECT          ON SCHEMA::[dynamic] TO SsdtSchemaCompareUser;
DENY VIEW DEFINITION ON SCHEMA::[dynamic] TO SsdtSchemaCompareUser;

However if I create a new SSMS session as SsdtSchemaCompareLogin I am still able to see all of the objects in the dynamic schema through the following queries:
SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

...and if I use SSMS's Object Explorer while connected as SsdtSchemaCompareLogin then I'm also able to see of the tables in the dynamic schema.
SSDT's Schema Comparison feature itself complains with these errors:

[Warning] The target login is not a member of the 'loginmanager' role. The comparison will be restricted to database scoped elements.
[Error] The reverse engineering operation cannot continue because you do not have View Definition permission on the 'MyDatabase' database.

So how can I prevent SsdtSchemaCompareUser from enumerating the contents of the dynamic schema?


texts: User Windows login name has been changed in AD yet session in SQL 2008 Profiler shows the old Windows login name

texts:
Background:  a user had her name changed in Active Directory from Domain\oldname to Domain\newname and successfully logged into the network with Domain\newname and yet  Domain\oldname appears in columns LoginName and NTUserName in Profiler.

User's SQL Server permissions are granted via a group set up in SQL Server security.

The question:  Has anyone observed this behavior and know why SQL Server is still picking up Domain\oldname (sp_who2 shows same information)?

BTW, have spent considerable time researching before posting question here and it appears to me that is no real issue with Profiler/SQL 2008 R2.


texts: Joining relations based on distance between geometry columns?

texts:
I have two tables:

User(id)
Region(id, userID, origin Geometry('point'), radius))

User has a one-to-one relationship with Region.

I want to query all users that have a region that contains a point (lat and long). To define contains, if the distance between the given point and region.origin is less than region.radius then that region contains the point.

I'm able to query just regions that contain a given point like this:

SELECT "id", "origin", "radius", "regionID", ST_Distance("origin", ST_MakePoint(lat, long), false) AS "distance"
FROM "regions" AS "region"
WHERE
  ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND
  ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
ORDER BY distance ASC
LIMIT 10;

But I really want the users and regions and don't want to query each user individually based of the region. Here is what I have:

SELECT *
FROM user, regions as region
WHERE
  user.id = region.userID
AND
  ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND
  ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
LIMIT 10;

With this query, I don't get any results, where the first query I get results.


texts: Convert rows into columns using Pivot in SQL

texts:
I have this temp table named #DynamicDB, with below data.

Date     Actual_Prod    Forecast_Prod   MRB_Parts
01-04-18   1                 2             2
02-04-18   3                 2           NULL
03-04-18   0                 3           NULL
04-04-18   1                 4           NULL
05-04-18   1               NULL          NULL

I want this data to be converted into Column wise, as shown below.

List_Of_Date    01-04-18  02-04-18  03-04-18  04-04-18  05-04-18
Actual_Prod    1        3          0    1     1
Forecast_Prod      2        2          3    4    NULL
MRB_Parts      2       NULL       NULL  NULL     NULL


texts: Get the CreatedAt Timestamp when the number of rows is equal to some number

texts:
I have a simple table in SQL Server.

id
OtherId
createdAt

1
A
2019-11-28 05:03:03.983

2
A
2019-11-28 05:03:06.937

3
B
2019-11-28 05:03:12.983

I want to retrieve a timestamp when the number of rows becomes equal to or greater than a fixed number, let us say 10, where OtherId is A.
So, if the number of rows with A in the OtherId column is more than 10, then return the value from createdAt.


texts: My server configuration has auto_commit = off which helps in large transaction while inserting and updating but what if innodb_autoinc_lock_mode = 1 as my tables does not have primary key with auto_increment? will it help in faster results or do we need to make to 2? is there any way to help it?


texts: TFS 2012 - Database Project - User with NO LOGIN map to Windows - best practices

texts:
I have a SQL Server Database Project (.NET 4.5) in TFS 2012. As a DBA, i'm looking for a mechanism whereby the developers can create 'CREATE USER' sql scripts and assign their permissions within the database to match the application without needing to know what login it eventually maps up to. This is because I want to hide the logins from live and test environments from them and have the DBAs create these logins in each environment as necessary.

If I go into the project and create a new user i.e. [MyApplication] the default script suggests the following template:

CREATE USER [MyApplication]
    WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = dbo
GO

Which is great, as I can create a user which is mapped up to no login, and then when it's deployed I can run:

ALTER USER [MyApplication] WITH LOGIN=[MyApplicationLogin], Name=[MyApplication]

which is fine as long as the user is to be mapped to a SQL login. If the login is to be mapped out to a windows login, SQL throws an error when we try to remap:

Msg 33016, Level 16, State 1, Line 1
The user cannot be remapped to a login. Remapping can only be done for users that were
mapped to Windows or SQL logins.

So the question is, what is the best way to set this up in the project so that the developers can create a user which can be named whatever they like within the database users and then how would we re-map this to a windows auth login?

thanks in advance!


texts: Postgresql 11 - pg_stat_statements - empty query column for some of queries

texts: we installed pg_stat_statements couple of days ago. Unfortunately, some of the queries does not show up in the view, e.g. the row is there but the 'query' column is empty. My guest is that the queries are too long and for some reason are too big to fit.


texts: Optimization of multiple CASE Statements

texts:
I have a rather long set of SQL which relies on CASE statements that gives me a percentage of the population of a particular zip code that fits a particular parameter. In the code below, it would give me the percentage of households with an elderly parent.

Now, the below code is actually created by a python script that generates it based on the list of selected factors and the possible values. In the case of one field there are 35 possible values which generates 35 different CASE statements. In the example below, homeowner status has three values which break out to three CASE statements but in fact it has 8 potential values...

The code runs. However, it takes a month of Sundays and I'm wondering if there is some way of optimizing it. My somewhat unhelpful SQL professor used to say-- "just let the query optimizer deal with it -- lots of smart people worked on that so you don't have to worry" ...

SELECT
  DD_1409.ZIP_CODE,
  count(*) RECORDS,
  (SUM((CASE WHEN GENDER = 'F'
    then 1
    else 0 end)) / count(*)) * 100 GENDER_F,
  (SUM((CASE WHEN PRESENCE_OF_ELDERLY_PARENT = 'Y'
    then 1
    else 0 end)) / count(*)) * 100 PRESENCE_ELDERLY_PARENT_Y,
  MEDIAN(LENGTH_OF_RESIDENCE),
  (SUM((CASE WHEN HOMEOWNER_STATUS = 'P'
    then 1
    else 0 end)) / count(*)) * 100 HOMEOWNER_STATUS_P,
  (SUM((CASE WHEN HOMEOWNER_STATUS = 'R'
    then 1
    else 0 end)) / count(*)) * 100 HOMEOWNER_STATUS_R,
  (SUM((CASE WHEN HOMEOWNER_STATUS = 'U'
    then 1
    else 0 end)) / count(*)) * 100 HOMEOWNER_STATUS_U,
  (SUM((CASE WHEN MOPS.KBMG_INDEX_GEN2.GEN2 in ('T1','T2','T3','T4','T5','T6','T7')
    then 1
    else 0 end)) / count(*)) * 100 GEN2_T

FROM MOPS.DD_1409
 LEFT JOIN MOPS.INDEX_GEN2 on INDIVIDUAL_ID_NUMBER = MOPS.INDEX_GEN2.IID
 WHERE STATE = 'CA'
 AND ONE_PER_ADDRESS ='Y'
GROUP BY DD_1409.ZIP_CODE;

I've tagged both Oracle and postgresql as it currently resides in Oracle but may be migrated to a postgresql instance in the next month.


texts: What is the benefit of creating an Oracle context over just granting package execution?

texts:
I have been programming oracle row-level security using DBMS_RLS for several years, but I have actually skipped creating contexts.  I have seen the intermittent recommendations to CREATE CONTEXT and now I'm trying to pull the trigger.

But I'm not seeing much benefit to a context at session level. I attach a package, but I still have to grant execute on it to any users I want to be able to use it. What am I missing?

As far as I can tell, the only thing that creating the context with the package is that the package can issue DBMS_SESSION.SET_CONTEXT naming that context.  After that a call to SYS_CONTEXT with that context will get the value set.

However, SYS_CONTEXT is itself kind of a hack.  It only returns a VARCHAR2 rather than a typed value.  Why not just add a function to the same package with the right data type?

Anything I put into the context with the context package can also be available through that same package.

Now, creating a global context seems like it might have benefits sharing across sessions, except that's not my goal here.


texts:
I have a client who wants to analyse calling records of customers to make a decision as to whether they qualify for loans or not.
They are in partnership with a mobile company that supplies the call data daily. The customer base of the mobile company is over 13million. The call data, voice and data credit is used to determine eligibility in conjunction with set rules.
Daily call records is about 70 million records and retention period of 90 days.
So am looking for a tool and database to help design the analytics engine to determine eligibility of potential loan requestors.


texts: I just found another SQL Server (2008r2) with AWE enabled on a 64-bit OS/SQL combination.  Has anyone done an research as to the overhead created by enabling AWE on the 64-bit OS/SQL combination?  It is mostly a question of curiosity, though it would be nice to be able to put into the analysis documentation.  Thanks!


texts: MQT automatic `set integrity off` when underlying table updated

texts:
Have a slow query. Need an MQT so the optimizer will rewrite the query and use my MQT instead.

Problem: a manual full refresh is much much faster than using refresh immediate type or a staging table: because of the structure of our db updating a single row takes 6 minutes! Just refreshing the MQT takes 30s.

So I chose the refresh deferred type, but db2 has no such thing as automatic MQT invalidation, when underlying tables change! I tried updating the table, but my MQT remains accessible.

What to do? Create a trigger on each table, which calls set integrity off?

current DDL:

create table xfacetattr as (
  SELECT
    distinct
  -- filter columns:
    AVD.language_id,
    A.storeent_id A_storeent_id,
    ADSC.mastercatalog_id
    ,
  -- return columns:
    ADSC.SRCHFIELDNAME,
    AVD.value,
    AV.storeent_id,
    AVD.sequence,
    AVD.image1,
    AVD.image2
  FROM
    attrdictsrchconf ADSC,
    attr A,
    attrval AV,
    attrvaldesc AVD
  WHERE
    ADSC.ATTR_ID           IS NOT NULL
  AND ADSC.attr_id          = A.attr_id
  AND A.facetable           = 1
  AND AV.attr_id            = A.attr_id
  AND AV.attrval_id         = AVD.attrval_id
  --and 1=2
) data initially deferred refresh deferred
;

create trigger xfacetattr_t1 after update of attr_id,facetable, storeent_id on attr FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t2 after insert on attr FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t3 after delete on attr FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');

create trigger xfacetattr_t4 after update of mastercatalog_id,SRCHFIELDNAME,ATTR_ID on attrdictsrchconf FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t5 after insert on attrdictsrchconf FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t6 after delete on attrdictsrchconf FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');

create trigger xfacetattr_t7 after update of storeent_id,attr_id,attrval_id on attrval FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t8 after insert on attrval FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t9 after delete on attrval FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');

create trigger xfacetattr_t10 after update of language_id,value,sequence,image1,image2,attrval_id on attrvaldesc FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t11 after insert on attrvaldesc FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');
create trigger xfacetattr_t12 after delete on attrvaldesc FOR EACH STATEMENT call XEXECUTE('set integrity for xfacetattr off');


texts: Why is my postgres `base` directory filling up rapidly?

texts:
My pg_xlog directory was filling up because my standby server was down. This brought the volume to 100% usage and shut down postgres. I brought up the standby servers, moved pg_xlog to another volume, symlinked it back into the data directory, and restarted the DB. DB volume was then at 43% without pg_xlog. The standby server got caught up, pg_xlog went from 4600 files to 1100 over a couple hours and I went to bed. Standby server went down overnight again. When I woke up, pg_xlog had 11k files and growing, and the base directory had filled up the volume with 66GB to 95%.

So, where the hell did all this new data in base come from overnight?


texts: How performance schema in mysql 5.6 improve performance?

texts:
Since MySQL 5.5 performance_schema database has been added likewise information_schema.
As we know that tables in information_schema contains statistical information like tables, plugins, partitions, processlist, status and global variables etc.
Just like, performance_schema contains a lot of tables.

But, how can we use these tables to improve performance of an
application based on MySQL 5.6?


texts: Suggestion for Large SQL Server Database Design

texts:
We are creating a database in MSSQL 2008 R2 Standard where we will be storing a large number of records. We estimate 200 million+ records in one table annually and we are primarily INSERTing with very few UPDATEs or DELETEs on the data. Its a data archival system where we insert historic records on a daily basis. We will generate different sort of reports on this historic record on user request so we've some concerns and require technical input and advice.

What is the best way to manage this kind of archival tables and database?


texts: Difference between High Availability data synchronization across local replicas and DAG

texts:
I see a significant difference when synchronizing data between Primary - Secondary and between Primary HA and Secondary HA as part of Distributed Availability Groups.

The difference in the amount of data sent across the wire. When making a significant amount of changes on Primary including change tracking enabled tables, we see a huge jump in the amount of data in Redo Queue on all local replicas (synchronous - compression disabled and asynchronous-compression enabled). But across DAG, redo queue is much lower and processing much faster without any significant backlog.

I want to understand on a deeper technical level the difference between local synchronization and DAG.

The specific questions:
- What is the difference between synchronizing from Primary to Secondary and across DAG (amount of data)
- Can we enable local synchronization to work similar to the one across DAG
- What factors that affect the amount of data  sent across the wire beside the obvious ones - activity on the DB and HA compression enabled/disabled


texts:
This problem has been bothering me for some time now and honestly i feel like i just don't know how to solve it.I have been trying to solve it for over a week or probably two . It's pretty simple but somehow it's just not working i don't know what I am doing wrong.

I have two tables one for user and one for messages.

table user

user_id

username

password

table messages

user id

id(fk) --- this column connects to the user_id in the parent table

message

So i am using mysql and phpmyadmin. when i type it in explicitly like this

INSERT INTO infoo ( `id`, `username`) VALUES ( '13', 'a;al;dld;ldladda');

it's inserted but what i when i do this

INSERT INTO infoo(id,username) VALUES((SELECT id FROM userinfo WHERE username = '$this->username' FROM userinfo),'$this->username')

The table is not being populated because i want the user to enter a message  and when the message is being inserted the primary key for the  user_id is automatically inserted into the foreign key column id for that specific user.

So please help me guys on this one, it seems pretty simple but sometimes the simple things comes with a lot of complexity.


texts: pt-table-checksum and row-based replication

texts:
The set up I work with is MySQL 5.5 in a master-slave replication configuration, but we have to use "MIXED" replication (statement and row-based) because we use an application that requires it, and it happens to be on our same server.

We had replication stop recently, and I want to make sure we have recovered properly.

I downloaded and installed the Percona Toolkit, but it doesn't look like I'll be able to use pt-table-checksum in this case, specifically because of the row-based replication:

  2.27.5 LIMITATIONS Replicas using row-based replication pt-table-checksum requires statement-based replication, and it sets
  binlog_format=STATEMENT on the master, but due to a MySQL limitation
  replicas do not honor this change. Therefore, checksums will not
  replicate past any replicas using row-based replication that are
  masters for further replicas. The tool automatically checks the
  binlog_format on all servers. See
  --[no]check-binlog-format . (Bug 899415)

Are there any other options for doing a checksum on row-based replication tables?

I have found this on the Percona Forum which says it will work, but I cannot have it change my cnf file as it claims it will do. It also warns, when I attempt to run it, the following:

  Replica mysql-b.example.com has binlog_format MIXED which could cause
  pt-table-checksum to break replication.  Please read "Replicas using
  row-based replication" in the LIMITATIONS section of the tool's
  documentation.  If you understand the risks, specify
  --no-check-binlog-format to disable this check.


texts:
CREATE TABLE Table_1 (
    Id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
    EmailId VARCHAR
)

INSERT INTO Table_1 VALUES (1,'raj@gmail.com');
INSERT INTO Table_1 VALUES (3,'test@gmail.com');
INSERT INTO Table_1 VALUES (4,'false5@gmail.com');
INSERT INTO Table_1 VALUES (7,'false55@gmail.com');
INSERT INTO Table_1 VALUES (8,'falsefalse@gmail.com');
INSERT INTO Table_1 VALUES (9,'true1@gmail.com');
INSERT INTO Table_1 VALUES (11,'true2@gmail.com');
INSERT INTO Table_1 VALUES (12,'falsefalsefalse@gmail.com');
INSERT INTO Table_1 VALUES (13,'ravi@gmail.com');
INSERT INTO Table_1 VALUES (14,'rak@gmail.com');
INSERT INTO Table_1 VALUES (15,'false@gmail.com');
INSERT INTO Table_1 VALUES (16,'false66@gmail.com');

CREATE TABLE Table_2 (
    Id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
    EmailId VARCHAR
)

INSERT INTO Table_2 VALUES (1,'raj@gmail.com');
INSERT INTO Table_2 VALUES (3,'test@gmail.com');
INSERT INTO Table_2 VALUES (4,'false98@gmail.com');
INSERT INTO Table_2 VALUES (7,'abc@gmail.com');
INSERT INTO Table_2 VALUES (8,'ta@gmail.com');
INSERT INTO Table_2 VALUES (9,'false1@gmail.com');
INSERT INTO Table_2 VALUES (11,'false2@gmail.com');
INSERT INTO Table_2 VALUES (12,'false@gmail.com');
INSERT INTO Table_2 VALUES (13,NULL);
INSERT INTO Table_2 VALUES (14,NULL);
INSERT INTO Table_2 VALUES (15,'false@gmail.com');
INSERT INTO Table_2 VALUES (16,'false66@gmail.com');

I have two tables in sqlite like Table_1 and Table_2

I need to update Entire Table_1 EmailIds Using table_2 EmailIds based on below conditions

condition-1)

need to udpate when Table_1 EmailId<>table_2 EmailId (to all rows)

condition-2)

If Table_2 EmailId is null don't need to update that null value in
Table_1 partucular EmailId

as per the Above Tables with my requirement I want below output:--

1) I want to update  4,7,8,9,11,12 Id's

2) I don't want update 1,3,15,16 Id's(because same emailids in both tables)

3) I don't want update 13,14 Id's (because 13, 14  emailid are null values in Table_2)

For the above problem I wrote below query in MYSQL and SQL Server it's working fine, but in sqlite it's not working.

I found Joins will not work in Update statement in sqlite but I want an alternate solution. Can anybody help me please?

UPDATE Newcontacts n
  JOIN DummyContacts d  ON (n.Id=d.Id)
SET n.EmailId=d.EmailId
WHERE n.EmailId<>d.EmailId
  OR d.EmailId IS NOT NULL


texts:
SELECT * FROM table
WHERE
key REGEXP 'some long regular expression'
OR key REGEXP 'some other long regular expression'

ORDER BY
key REGEXP 'some long regular expression' DESC,
key REGEXP 'some other long regular expression' DESC

Is there a cleaner way to get the same result as above, without repeating the where conditions in order by?


texts: Cross-database view - Not able to access database under the current security context

texts:
We have two databases, Adb and Bdb. In Bdb, we create a view that references another view in Adb, like so: CREATE VIEW Bdb.dbo.Bview AS SELECT * FROM Adb.dbo.Aview.

We have an SQL Authenticated Blogin, mapped to Buser, both on Adb and Bdb, with at least the db_datareader role on both.

The following doesn't work:

USE Bdb;
EXECUTE AS USER = 'Buser';
SELECT * FROM Bdb.dbo.Bview;
SELECT * FROM Adb.dbo.Aview;

The following error is thrown for both selects:

Msg 916, Level 14, State 1, Line 4
The server principal "Buser" is not able to access the database "Adb" under the current security context.

However, this works:

USE Adb;
EXECUTE AS USER = 'Buser';
SELECT * FROM Adb.dbo.Aview;
SELECT * FROM Bdb.dbo.Bview;

I have noticed that when I first USE Bdb and switch to Buser, I can't see any other databases:

USE Bdb;
EXECUTE AS USER = 'Buser';
SELECT * FROM sys.databases; -- only master, tempdb and Bdb is shown

But, when I USE Adb first, I see all of them, even ones that don't have the Buser and can't be accessed by it:

USE Adb;
EXECUTE AS USER = 'Buser';
SELECT * FROM sys.databases; -- all DBs on the server are shown

What could be causing this issue? What should I check?


texts: Why is the schema prefix (dbo) mandatory when we call a function?

texts:
When user is mapped with default schema (dbo) and we can select all tables under [dbo] without prefixing the schema.

We can execute stored procedures without prefix if it's under the default schema.

Given that, why do we need to prefix a function with the schema?

Thanks!


texts: How to use comma separated columns in CASE WHEN Statement?

texts:
I am trying to add below CASE WHEN Statement in SQL WHERE Clause, But SQL Server not allowing me to do so. All column used are of same Data Types but still its giving me error, Can anyone please check and advice why its not working?

WHERE supplierAccountDetail.SupplierMasterID IN (
        CASE
            WHEN @SupplierMasterID IS NOT NULL
                THEN @SupplierMasterID
            ELSE (sales.ParentSupplierMasterID
                ,sales.DivisionSupplierMasterID) //Error - Syntax Error
            END
        )


texts: How to perform server-side transaction timeout if COMMIT TRANSACTION will never arrive?

texts:
How can SQL Server terminate one of its transactions if it exceeds preset timeout?

Imagine the following transaction performed by traveling salesman's SQL client application, which is submitted command-by-command (i.e. not as single batch) and normally takes 2 seconds:

BEGIN TRANSACTION
    INSERT INTO PurchaseOrder <purchase order header>
    INSERT INTO PurchaseOrderLineItem <line item 1>
    INSERT INTO PurchaseOrderLineItem <line item 2>
    INSERT INTO PurchaseOrderLineItem <line item 3>
          :
    INSERT INTO PurchaseOrderLineItem <line item 99>
COMMIT TRANSACTION

Now, if mobile connection permanently drops1 during sending line item 51 (thus dropping the link to SQL server etc.), ABORT command will never arrive from client side. How can SQL Server time out and rollback the transaction when client which opened the transaction is permanently lost? (Condition example: no new command arrived for 30 seconds in that transaction.)

The problem I'm trying to avoid is that all other SQL commands accessing PurchaseOrderLineItem table will wait infinitely for the one described above. How to clean way for other queued commands? (If the client application is unable to submit entire transaction batch at once.)

I can see advices to set client timeout but they are of no help if client is permanently lost.

__

1) other possible accidents leading to problem: application crashes and exits without cleanup, PC goes to blue screen of death, laptop runs out-of-battery, network device stops responding etc...


texts: unable to change secure_file_priv in my.cnf

texts:
I have problems loading in a csv file, secure_file_priv is set to NULL, and that prevents me from reading in the csv file. I am trying to change secure_file_priv in my my.cnf file, but my changes don't go through.
mysql says:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

so I made a my.cnf file in /etc, but the changes don't go through. (I restarted mysql server in mac preferences window)

this is what my file looks like:

[mysqld]
wait_timeout=28800
interactive_timeout = 28800
secure_file_priv=""
max_allowed_packet=64M

i edited my my.cnf file, restarted mysql server but when i use show variables like secure_file_priv it still says NULL, how can i alter the secure_file_priv option?


texts: MySQL dump only has SET commands, no data

texts:
I've done an export of a Wordpress database. The MySQL file that was exported only contains the Host, Database name, server version and SET commands.

Pastebin: http://pastebin.com/pFgdPDRa

Another strange thing is the line at the end of the export:
-- Dump completed on 2013-02-14  9:38:34

The export was done in early 2016, so I'm not sure what that's about?

Thanks.


texts:
I have a query that uses around 6 or 7 tables and uses pivot to return a formatted string from this entire structure. Actually, today this query is dynamically generated via C#/Entity Framework and it takes at least one minute to process and return 10 rows.

We were looking for alternatives so we can improve the performance of this query, and we found the virtual columns. We could not find any benchmark comparing virtual columns and view so we can argue with our client which option we'll choose.

If we opt for virtual columns, will it run automatically the function linked to it? Or it needs to be queried to have its value "filled"?


texts: Completely remove additional data file from SQL Server 2012 DB

texts:
Hope someone can help me on this one.

I have created by mistake an additional data file in the incorrect file group, I need to either remove the file completely or move the file to the default file group. Looks like changing files from one file group to another is not possible?

The data file is empty so I tried removing the file using alter DB remove file, when I run DBCC showfilestats, the file is gone, but when I check SYS.master_files, I can see still the entry of the file I want removed.
Is there a way to remove it completely, even deleting it physically from the server as I need to recover the space?

Kind regards,
Jose.


texts: Syntax error when using exception inside for loop

texts:
I am trying to create the following procedure on postgresql

CREATE PROCEDURE run_all_scheduled_tasks()
LANGUAGE plpgsql
AS
$$
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT * FROM some_table LOOP
        -- Statement 1..2..3..4
        EXCEPTION
            WHEN others THEN
                RAISE NOTICE 'Error Occured! Code: %, Messege:%', sqlstate, sqlerrm;
                ROLLBACK;
        COMMIT;
    END LOOP;

END;
$$;

I get the following error:

  ERROR:  syntax error at or near "EXCEPTION"
  LINE 12:   EXCEPTION

It works properly if I shift exception block outside the for loop.

What I want to achieve is that for one iteration of loop, either all statements from 1..4 should get executed or none of them. If an error occurs, I still want to preserve the changes made up till previous iteration and then carry on to next iteration by simply raising a notice for the error.

What is causing this error?


texts: Mysql database .frm and .ibd file read to understand

texts:
I want to read .frm and .ibd files in mysql to understand how mysql database engine store data in files.
Which format those file does have, since when I open those files it show non-human readable characters.


texts:
I have been playing around with grib2 data from the National Weather Service lately, and have been databasing some model forecast data.
The current design model I have is probably bad design, but I have 87 columns of data per model per forecast hour I want to save. The long range forecast models have 52 forecast hour intervals I'm working off of, but I could increase that later. I'm collecting from 3 models... so right now that means I have 87 columns, 1 table per model per forecast hour - in other words, somewhere around 80 tables of data with 87 columns each.
Already, this is causing problems, as MySQL apparently has a hard limit for how many table joins you can do and I have hit this a few times. Also, I'm starting to get PHP errors trying to execute multiple queries at once and then joining the results in PHP to one data set - too many buffered connections, etc. - I currently have PHP doing the queries looping through forecast hours to auto-generate the table joins... but I want a better way to do this. It's very hacky sloppy.
Would it make more sense to:

Save the current table structure? (~80 tables per location - 1 right now)

Create a table just for hour intervals? (This could still hit the table limit if I increase the hour frequency - but would be a bit less tables. Each table would have ~240 columns.)

Create a huge table with all hour data per each model. Would have 3 tables, but one of those tables would have 6,880 columns of data. I understand there is a hard limit of around 4,000 columns? This would not work, probably.


texts: What is the best option for High availability on a data warehouse?

texts:
I have a customer who have an existing 1.5Tb data warehouse and are currently planning for a complete refresh of the DW on a new environment. Their infrastructure manager has organised 2 servers with SQL 2017 standard on each and has now asked me to plan a HA/DR plan for the new DW database/instance.

I immediately thought of using AlwaysOn Availability Groups, although I have never used them before, and none of the articles I read talk about typical data warehouse workloads - it's all OLTP applications. With a large daily ETL process and a smaller intra-day ETL process running on their current DW, will that have an impact on how we approach this?

Thanks - any help to point me in the right direction here would be beneficial!


texts: Display Total under name column and sum of amount under sal column

texts:
in a table empno,ename,sal,hiredate is there.
but i want output is

empno  ename sal hiredate
122    ganga 2000 1989-02-06
123    reddy 3000 1986-06-02
       Total 5000

here empno, hiredate column just empty,no need to display null or zero or anything.

how to write the query in SQL SERVER


texts: Deny All DDL & DML Commands In Sql Server

texts: I want to deny all ddl and dml commands to AD and SQL Server users over all the databases on my database server. How can i do that?


texts:
I understand all the reason why it's not advisable to shrink
I only preface this to avoid falling into that conversation.. so, I have 1.4tb database.. about 700gb is free. This is due to the fact that people have been really irresponsibly making duplicate copies of the data and never cleaning up. In other words, this is space that should be reclaimed.
There are cases when I need to restore 3-4 copies of this database on the same server due to many concurrent development effort that's taking place. In this case, that 700gb waste is multiplied 3x-4x. So, I've been restoring the database to a DBA server then shrinking then backing up the file to be distributed to the lower environment. However, once that 700gb database is restored, it balloons backup to 1.4tb. I thought maybe the "initial size" of the files is doing this but when the backup is taken, the initial file size is small.
As a workaround, we are dropping the files and consolidating. There are 7 data files and we're dropping 2 of them. I would rather avoid this and try to figure out why the data files are ballooning.


texts: What format is old DOS database with file extensions .DBX .IDX .PGX?

texts:
I must extract data from old DBX files.
This database is not Outlook express. I have tried Visual FoxPro 2.6, 9.0, VFP ODBC Driver, VFP OLE DB provider with no success. DBX are the actual data files. I succesfully extracted the data, but can not convert the number fields, nor understand the relations between tables.
File header begins with WBDB... field names, types and sizes. I wrote program to decode the files, but had success only with string fields. The numbers are coded in strange form:

Curr_PurchaseCost: 33 33 33 33 33 33 11 40 00 00 00 00
Curr_SalePrice: 66 66 66 66 66 66 12 40 00 00 00 00
SalePrice: 66 66 66 66 66 66 12 40.

The other problem is that there is some sort of relation between tables. The ParentKey in the child table does not correspond to field ID in master table. Maybe ParentKey is some index value, but I don't understand the format of index files IDX.


texts: Using character set en_us.364 in Informix database DB_LOCALE

texts:
I am trying to change my db_locale to en_us.364 which is an ASCII character set. It says on IBM's Knowledge Center that 364 is a valid character set:

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.jdbc_pg.doc/ids_jdbc_270.htm

I am able to change my DB_LOCALE=en_us.364. However, after changing the db_locale to en_us.364 I cannot access dbaccess. I get this error:

  -23101 Unable to load locale categories.

So I also tried changing my client_locale to en_us.364 as well. I was able to set the environment variable. However it still gave the same error when trying to use dbaccess.

I then looked around in the GLS/lc11/en_us and could not find 016c.lco which is the character set 364/ASCII. However, I have found this character set in the GLS/lc11/pl_pl folder which means that ASCII is supported. I was wondering where I could get either the 016c.lco or the 016c.lc for the locale en_us.


texts: What are the host credentials for Enterprise Manager 11g?

texts:
I am trying to use Database Replay in 11g Release 1 using Enterprise Manager. In the last step it asks for 'Host Credentials' to start the capture. But when I give the username and password for the host, it throws an 'Invalid username/password' error.

Where am I going wrong?


texts: How can I have a gap in the SQL Server Job Agent history for a job?

texts:
I have a job (Update Tickets) scheduled in Job Agent with a single schedule of "Occurs every day every 30 minute(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 11/4/2013." When I use Log File Viewer to see the history for this job, I see no entries for a 2.5 hour period yesterday. Other jobs on the same server ran during the time this one job had the gap in executions.

The job does an INSERT from SELECT from an OpenQuery from a Linked Server. If there was a network or other issue that prevented the connection to the data source from succeeding, I would still expect to see a job failure message in the log, but I see no entries for it at all. Neither the SQL Server log, SQL Server Agent log, nor the Windows System or Application logs show anything relevant during the time of the gap.

What are the possible causes of this?

Thanks,

Mark


texts: Modeling a database for a printing company

texts:
Scenario
Create a conceptual schema for the case study assigned to you using the concept of entity relationship model. Specify the cardinality ratio and participation constraint for each relationship type.
The following requirement is for a ABC printing company that handles printing jobs for book publishers.

The printing company keeps track of publisher's id, publisher's name, Publisher's address composed of street, city, postcode, publisher's telephone number and credit code.

The company keep track of the printing jobs (books or part of books) and the job id is the unique key for each job. The company also would like to record the date of the printing job, the description and the type.

A printing job requires the use of materials, such as paper and ink, which are assigned to a job via purchase orders. The purchase orders for each job is recorded for each purchase order the purchase order id (unique) and the date for the purchase order also recorded. Each printing job may have several purchase orders assigned to it. If the job is deleted, you need not keep track of its purchase order any longer.

The printing company also would like to keep track the details of the materials(items). Each items has item id, description, stock in hand and price.

Each purchase order for each job can have several items.

Entity-relationship diagram

Schema
My schema is like this
Publisher (PID, PName, street, city, postcode, Ptele,CCode)
p.k = PID
f.k =

PrintingJobs (JID, Pdate, Description, Type, PID)
P.K = JID
F.K PID

Purchase ( JID, OID, ODate)
P.K = JID, OID
F.K = JID

Item ( IID, stock, Description, Price, JID, OID)
P.K = IID
F.K = JID, OID
  JID REFERENCES PURCHASE (JID)
  OID REFERENCES PURCHASE (OID)

Question
Can anyone guide me and tell me if I'm correct, what changes I should make, and if I represented the relationships well?


texts: Adding Nullable Column To Production DB taking too much time

texts:
I have running production DB which have normally 50-100 insert per minute. I am trying to add a new column in the production DB using,
ALTER TABLE MyDB ADD [MyColumn] varchar(30)
GO

This query keeps running and not finishing until I have stopped it. What else I can do?


texts:
I'm trying to understand the index FILLFACTOR parameter on a simple example. I have a table that is generally updated only once in ten days, but the update is quite heavy (requires 5-6 hours).

Now, I tend to drop the index on a column of the table, update the data and then create the index again with FILLFACTOR = 100.

Is that a good practice? I'm not sure about that because I don't understannd the FILLFACTOR concept in general. By default it's 90% and they say that this is needed to perform updates and insertions.

But I tried to create an index with FILLFACTOR = 100 and then update some row and add new one and it executed fine. But I have autovacuum turned off. Running ANALYZE, however required only 3secs.

So, how does the free space left by fillfactor work?


texts:
I want to split a given year into given number of date range pieces using SQL. Following points need to be considered while splitting it:

Given year will always start from 01-01 and end at 12-31 and it should be split into given number of pieces based on months. For example:

year  SplitCount   output
2019  2            2019-01-01
                   2019-07-01

2019  3            2019-01-01
                   2019-05-01
                   2019-09-01

If user asks to split given year into 12 pieces, it should split the whole year into 12 months.
If user asks to split given year more then 12 pieces, it should split it into week wise

Please suggest.


texts: Microsoft Links for SQL Capacity planning

texts:
I went through this website because we have migrations coming up pretty soon for Landesk Management Suite.

We are planning to move to SQL 2014 but the site's contents are mostly relevant to SQL 2005.

Are there any other good websites or documentation from microsoft which I can go through to estimate I/O, disk and CPU planning before the Migration?

The url which I went through was this

Editing. I am trying this tsql code to get min,max and avg size of databases since 2015. Would this help?

SET NOCOUNT ON
DECLARE @endDate datetime, @months smallint;
SET @endDate = GetDate();  -- Data atual
SET @months = 12;          -- Nr. de meses a analisar

;WITH HIST AS
   (SELECT BS.database_name AS DatabaseName
          ,YEAR(BS.backup_start_date) * 100
           + MONTH(BS.backup_start_date) AS YearMonth
          ,CONVERT(numeric(10, 1), MIN(BS.backup_size / 1048576.0)) AS MinSizeMB
          ,CONVERT(numeric(10, 1), MAX(BS.backup_size / 1048576.0)) AS MaxSizeMB
          ,CONVERT(numeric(10, 1), AVG(BS.backup_size / 1048576.0)) AS AvgSizeMB
    FROM msdb.dbo.backupset as BS
    WHERE NOT BS.database_name IN
              ('master', 'msdb', 'model', 'tempdb')
          AND BS.type = 'D'
          AND BS.backup_start_date
              BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
    GROUP BY BS.database_name
            ,YEAR(BS.backup_start_date)
            ,MONTH(BS.backup_start_date))
SELECT @@SERVERNAME
      ,MAIN.DatabaseName
      ,MAIN.YearMonth
      ,MAIN.MinSizeMB
      ,MAIN.MaxSizeMB
      ,MAIN.AvgSizeMB
      ,MAIN.AvgSizeMB
       - (SELECT TOP 1 SUB.AvgSizeMB
          FROM HIST AS SUB
          WHERE SUB.DatabaseName = MAIN.DatabaseName
                AND SUB.YearMonth < MAIN.YearMonth
          ORDER BY SUB.YearMonth DESC) AS GrowthMB
FROM HIST AS MAIN
ORDER BY MAIN.DatabaseName
        ,MAIN.YearMonth


texts: see currently running background indexing jobs (MongoDB)


texts: How do I select this pivot query by specified Month

texts:
I need help with my mysql pivot code.
Everytime I add WHERE MONTH(attendance_date) = 2 inside @sql_dynamic gets error.
pls help

I have a code:

SET @sql_dynamic:= (SELECT GROUP_CONCAT(DISTINCT CONCAT('if(sum(if(attendance_date = "',
                    date_format(attendance_date, '%Y-%m-%d'),
                    '",1,0))=0,0,attendance_status) AS `',
                    date_format(attendance_date, '%M-%d'),'`'
                    )
                ) from attendance
              );

SET @sql = CONCAT('SELECT studentidnumber, student_fullname, subject_id, ',
    @sql_dynamic, '
FROM attendance
WHERE subject_id=1

GROUP BY studentidnumber'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt


texts: decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

texts:
i recently testing the move of our db from one server to another.
as part of this process i copied all the data to the new db and started testing it.
as a comparison i run a store procedure whose execution on the old DB took about 2.5 hours on the new db it took about 8.
now the amount of data is the same and the sp is the same the differences is in:

old db

mysql version 5.0.45
pc
OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 3 Build 2600
Processor   x86 Family 6 Model 15 Stepping 13 GenuineIntel ~2394 Mhz
Total Physical Memory   2,048.00 MB

new db

mysql version 5.5.25a
pc
OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 2 Build 3790
System Manufacturer Dell Inc.
System Model    PowerEdge 2900
System Type X86-based PC
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Total Physical Memory   4,090.64 MB

if any one has any idea what to check i will be happy to hear.

one of the SP that takes now alot of time is:

BEGIN
DECLARE tempSlot INT(10);
DECLARE tempPort INT(10);
DECLARE tempOnx INT(10);
DECLARE tempLagId INT(10);
DECLARE tempIp TEXT;

DROP TABLE IF EXISTS `temp_ports_table`;
CREATE TEMPORARY TABLE `temp_ports_table` (
  `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `IP` TEXT,
  `LAG` INT(10) UNSIGNED ,
  `SLOT` INT(10) UNSIGNED ,
  `PORT` INT(10) UNSIGNED ,
  `ONX` INT(10) UNSIGNED ,
    PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO temp_ports_table (IP, LAG)
    SELECT DISTINCT IP, LAG_ID
    FROM  n_to_1_table
    WHERE LAG_ID IS NOT NULL AND LAG_ID > 0;
INSERT INTO temp_ports_table (IP, SLOT, PORT, ONX)
    SELECT DISTINCT IP, SLOT, PORT, ONX
    FROM  n_to_1_table
    WHERE LAG_ID IS NULL OR LAG_ID <= 0;
SET @numOfPortsInNto1Table:=(
    SELECT count(ID)
    FROM temp_ports_table accessChassis
    GROUP BY ID>0);
IF @numOfPortsInNto1Table<=0 THEN
  SET @numOfPortsInNto1Table:=0;
END IF;
 INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure : Created Temporary Tables';

SET @tempNto1tableCounter:=1;
WHILE @tempNto1tableCounter<=@numOfPortsInNto1Table DO

    SET @tempFlowInstanceId := 0;

    SELECT tempPortsTable.SLOT, tempPortsTable.PORT, tempPortsTable.ONX, tempPortsTable.LAG, tempPortsTable.IP
        INTO tempSlot, tempPort, tempOnx, tempLagId, tempIp
            FROM temp_ports_table tempPortsTable
            WHERE tempPortsTable.ID=@tempNto1tableCounter;

    IF (tempLagId IS NOT NULL AND tempLagId > 0) THEN
        UPDATE n_to_1_table
            SET INSTANCE_ID=@tempFlowInstanceId:=@tempFlowInstanceId+1
            WHERE
                IP=tempIp AND
                LAG_ID=tempLagId;
    ELSE
        UPDATE n_to_1_table
            SET INSTANCE_ID=@tempFlowInstanceId:=@tempFlowInstanceId+1
            WHERE
                IP=tempIp AND
                SLOT=tempSlot AND
                PORT=tempPort AND
                ((tempOnx IS NULL AND ONX IS NULL) OR ONX=tempOnx) AND
                (LAG_ID IS NULL OR LAG_ID <= 0);
    END IF;
    SET @tempNto1tableCounter:=@tempNto1tableCounter+1;

END WHILE;
 INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure : Modified All N:1 Flow INstance ID According To Interface';
 DROP TABLE IF EXISTS `temp_ports_table`;
 INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure Finished';
END


texts: Login failed for user - Error 18456 - Severity 14, State 38

texts:
Message that SQL Server Log File Viewer shows:

Login failed for user [User]
Error: 18456, Severity: 14, State 38

What it actually means:

Failed to open the explicitly specified database

My Question:

Is there a list somewhere of all the variations of the 18456 errors (Login failed), for each combination of severity and state, with the helpful description text?

I've had a Google but can't find anything other than specific combinations.


texts:
Does MySQL have any functions like SUM but then do the MULTIPLICATION of a collumn?

Example:
I have an table like this, and I want to multiply the entire collumn

+--------------+
| combinations |
+--------------+
|            3 |
|            5 |
|            4 |
|            6 |
|            1 |
|            3 |
|            4 |
+--------------+

expected result:

+----------------------------+
| product_total_combinations |
+----------------------------+
|                       4320 |
+----------------------------+

I cannnot find anywhere how to achieve this. Any help is appreciated.


texts: Are the columns returned from CROSS APPLY with a Table Valued Function evaluated only once?

texts:
Considering the following query:
select Student.Id,
       case when SomeTvfResult.Num = 3 then N'foo'
            when SomeTvfResult.Num > 42 then N'bar'
            else N'baz'
       end as SomeStr
from Student
cross apply SomeTableValuedFunction(Student.Id) SomeTvfResult

I wonder if the value of SomeTvfResult.Num gets recalculated each time I reference it in the case statement or it's somehow cached and evaluated only once. If it's relevant, you can assume that SomeTableValuedFunction is an inline TVF.
Since I've learned that coalesce is simply replaced by a case statement that causes the given expressions evaluated more than once I feel concerned each time I use some function in my queries.


texts: MySQL Duplicate Key Entry Error when there is not already an entry for this key

texts:
Duplicate entry '35839' for key 'PRIMARY'

I am receiving the above error message suddenly on a database I keep logs in.  This is an auto-increment field which the last key for the last entry is 35838.  If I perform a search for the key 35839, no results are returned.  (Also, I'm looking at the database through phpMyAdmin and can see that the last record is 35838.)

The table structure is:

CREATE TABLE IF NOT EXISTS `raw_data` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `oid` int(12) unsigned NOT NULL,
  `data` longtext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `oid` (`oid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=35839 ;

You can see that the next ID it is expecting to make is 35839.  Yet it thinks that it is already in the table...

Also, the oid that is trying to be inserted is also unique.  It is an order ID being generated by another system and is never duplicated.  Plus, I've checked to see if it is already in the database and it is also not in the database already.

Is there anything I could be overlooking as to why this would suddenly start telling me that it is trying to enter a duplicate?  It has been working for two years now.  haha~

Thanks!

Select Statement

Select * From raw_data Where id = 35839;

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0012 sec)


texts: Design of fact table(s) and dimensions tables for data warehouse

texts:
Hi i'm newbie in Datawarehousing, how would you model this in a Data Warehouse:
i wish design the Data Warehouse which  give the answers of statistics relating to a baseball league
For players
in offensive:

•How many times has a batter to bat.
•How many runs scored is.
•How many hits,doubles hit and triples hits.
•How many homeruns did.
•many  RBI.
•many base on balls

in Defensive:

▪ How many outs, double play takes
▪ How many assists has
▪ How many errors lead or Pitcher:
▪ How many games has lost
▪ has won many games
▪ How many saved games
▪ How many complete games leads
▪ How many games have started many
times it has bleaching
▪ How many hit, double hit received, received triple hit, received homerun

It is important to check the equipment-specific information, this information is:

• How many games won as a visitor and
as homeclub
• Games lost as a visitor
• Games lost as a homeclub
• How many times lost/gained when
rival starting pitcher was left-handed
• How many times lost/gained when
rival starting pitcher was
right-handed
• How many games won / lost in extra
inning

Additionally it is important to check the information of each of the games, this
information is:

• Which team was homeclub
• Which team was visiting
• On what date was played
• In what city was played
• What was the score (it is a string of the form AB where A is the amount
of runs scored by the winning team and losing team B)
• The winning team
• What pitcher lost
• What pitcher won
• What pitcher saved
• Number season game

Other important highlights

• Must be able to view detailed
information for each player (name,
Arm, etc).
• Must be able to view detailed information for each team (Name, Headquarters, etc).
• A season has 3 stages or Heat, or  RoundRobin or Final Round
• It is important to know at-bat hitting a player, this shift comes from the alignment (first base, second  base, etc)
• It is important to know the alignment of each play in defensive

As for the data of the players, it is important that this can be viewed as
season, as each inning game even as regards data equipos.En equipment is important that this can be viewed as
season.
Does anyone know some references on that precise part of DW design ? Any ideas?
Thanks a lot.


texts:
I've been using Ola Hallengren's scripts for a few years now, and a recent upgrade to MSSQL2017 has allowed me to start testing encrypted back-ups. Before anyone asks: yes, master keys and encryption certificates have been backed up and stored in our password manager.

I'm running into an issue with the following parameter combination:

EXECUTE [OlaHallengren].[DatabaseBackup]
     @Databases = 'SYSTEM_DATABASES'
    ,@Directory = N'\\<SHARE>\SQLBackup\Daily'
    ,@BackupType = 'FULL'
    ,@Verify = 'Y'
    ,@CheckSum = 'Y'
    ,@Encrypt = 'Y'
    ,@EncryptionAlgorithm = 'AES_256'
    ,@ServerCertificate = 'DBBackupEncryptCert<SERVERNAME>'
    ,@LogToTable = 'Y'

The back-up runs fine, but verification does not:

Command: RESTORE VERIFYONLY FROM DISK = N'\\<SHARE>\SQLBackup\Daily\<SERVER>\master\FULL\...
Process Exit Code 1.
The step failed.

It seems to be an issue with Ola Hallengren's script not being able to open the master key, because this doesn't work:

RESTORE VERIFYONLY FROM DISK = N'\\<SHARE>\SQLBackup\Daily\<SERVER>\master\FULL\<SERVER>_master_FULL_<DATE>_<TIME>.bak'

but this does:

use master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PASSWORD>';

RESTORE VERIFYONLY FROM DISK = N'\\<SHARE>\SQLBackup\Daily\<SERVER>\master\FULL\<SERVER>_master_FULL_<DATE>_<TIME>.bak'

CLOSE MASTER KEY

Am I trying to have my cake and eat it, too?
Or is there a way to have both encryption and verification with Ola's scripts?


texts: MongoDB Sharded Cluster Upgrade from 2.4 to 3.2 version

texts:
I need to upgrade MongoDB Sharded Cluster from 2.4 to 3.2 .

Based on documentation, I need to upgrade from 2.4->2..6->3.0->3.2

So it looks like lots of upgrade from 2.4 to go to 3.2 version.

We have 3 Shards with 3 replica sets.

Each Shard also runs 1 config server and router service

Upgrade Document :
https://docs.mongodb.com/manual/release-notes/2.6-upgrade/#upgrade-a-sharded-cluster-to-2-6.

Question :
1. Should all cluster should be upgraded to 2.4 to 2.6 first and then follow same process from 2.6 to 3.0 and 3.0 to 3.2 ?

Upgrade Sharded Clusters:
           Upgrade the cluster’s meta data

It even did not mention that I need to shutdown existing config DB process. Do I need to shutdown 1 config server on shard and follow upgrade process ? Do I need to upgrade all config servers like this or since Config Server data is synched automatically , I need to upgrade only one config server and then replace binary for 2.6 on other servers.

Please clarify following points :
Upgrade Sharded Clusters

Optional but Recommended. As a precaution, take a backup of the config database before upgrading the sharded cluster.

1.
Disable the Balancer.
Turn off the balancer in the sharded cluster, as described in Disable the Balancer.

    This looks good.

2.
Upgrade the cluster’s meta data.
Start a single 2.6 mongos instance with the configDB pointing to the cluster’s config servers and with the --upgrade option.

    It says Start a single 2.6 mongos. It is not clear to me, at this point should my whole existing cluster should be down or I need to just just down one config server and do upgrade. It even did not mention, I need to stop/shutdown config database on one server. It just says "Start with 2.6 binary with upgrade option" so it is confusing to me. Can someone clarify ?

To run a mongos with the --upgrade option, you can upgrade an existing mongos instance to 2.6, or if you need to avoid reconfiguring a production mongos instance, you can use a new 2.6 mongos that can reach all the config servers.

mongos --configdb  --upgrade
You can include the --logpath option to output the log messages to a file instead of the standard output. Also include any other options required to start mongos instances in your cluster, such as --sslOnNormalPorts or --sslPEMKeyFile.

The mongos will exit upon completion of the --upgrade process.

The upgrade will prevent any chunk moves or splits from occurring during the upgrade process. If the data files have many sharded collections or if failed processes hold stale locks, acquiring the locks for all collections can take seconds or minutes. Watch the log for progress updates.

    Good with instructions ...

3.
Ensure mongos --upgrade process completes successfully.
The mongos will exit upon completion of the meta data upgrade process. If successful, the process will log the following messages:

upgrade of config server to v5 successful
Config database is at version v5
After a successful upgrade, restart the mongos instance. If mongos fails to start, check the log for more information.

If the mongos instance loses its connection to the config servers during the upgrade or if the upgrade is otherwise unsuccessful, you may always safely retry the upgrade.

    Good with instructions ...

4.
Upgrade the remaining mongos instances to v2.6.
Upgrade and restart without the --upgrade option the other mongos instances in the sharded cluster. After upgrading all the mongos, see Complete Sharded Cluster Upgrade for information on upgrading the other cluster components.

    Upgrade and restart without the --upgrade option so there is no need to use --upgrade option. I am not not sure why step is called Upgrade and restart, it should be simple restart. Am I missing any thing here ?
    When we upgrade our first config db with upgrade option, other config db should automatically sync with first one, the only thing which I need to do is to start with 2.6 binary. Is my understanding correct ?


texts: Persistent Severity 016 alerts: "Change Tracking autocleanup is blocked on side table"

texts:
We had a large database running on a SQL Server 2014 Enterprise instance (evaluation license). Due to cost constraints, we had to move it to a new SQL Server 2017 Standard instance (per-core license; just updated to CU6). EDIT: The database is in SQL Server 2014 (120) compatibility, and was transferred by restoring full and log backups.

Everything is working fine except for change tracking. We use change tracking to locate recent changes and keep a denormalized table up to date; the denorm table is used for rapid filling of a grid for the web application.

Change tracking is working, but apparently autocleanup is not. We are getting Severity 016 alerts:

  Change Tracking autocleanup is blocked on side table of tablename. If the failure persists, check if the table tablename is blocked by any process.

These come about every half hour for each of the tables that is complaining (about 4 different tables).

We've tried the manual cleanup described here ... it too presents "side table is blocked" errors.

So here are my choices:

Turn off this specific alert and hope nothing blows up.
Turn off autocleanup; schedule a manual cleanup during a daily maintenance window.
Not sure what else I can do.

Besides "what should I do to stop this?", my side question is "why is this happening with 2017 Std when it didn't happen with 2014 Enterprise?"