texts: When to use a spare index over a dense index?

texts:
I have a question in my textbook:

  When is it preferable to use a dense index rather than a sparse index? Explain your answer.

My first thought was that the dense index would be good when it'd cost a cheap / worthwhile amount of space relevant to the performance gain. When I was googling for the answer (to verify), I found this answer in multiple places

  It is preferable to use a dense index instead of a sparse index when
  the file is not sorted on the indexed field (such as when the index is a secondary index) or when the index file is small compared to the size of memory.

and it's not very clear to me what it's saying.

For the first reason

  when the file is not sorted on the indexed field (such as when the index is a secondary index)

What is "the file", and likewise which field is "the indexed field", the field in the primary index or the one in the secondary index? Also just in general, I'm still not sure how this justifies the dense index.

For the second part

  when the index file is small compared to the size of memory.

What is "the size of memory"  in this context? Is this trying to say what I said? (the dense index is cheap to make?)

I'd really appreciate a better explanation/clarification of the above.


texts: Use a Context namespace in a multi-tenant database?

texts:
I'm developing an Apex application that uses database Contexts in conjunction with Row Level Security. I'd like to install a second copy of the schema and workspace on the same instance; this works except for the Context, which is a global thing.

For example, in schema "DEV", I create the context:

CREATE OR REPLACE CONTEXT myapp_ctx USING myapp_pkg;

When I query dba_context, I see my context has been created:

NAMESPACE  SCHEMA  PACKAGE    TYPE
=========  ======  =========  ================
MYAPP_CTX  DEV     MYAPP_PKG  ACCESSED LOCALLY

In schema "TEST", I create the context as before:

CREATE OR REPLACE CONTEXT myapp_ctx USING myapp_pkg;

This has the effect of removing the dev context and replacing it with the test one:

NAMESPACE  SCHEMA  PACKAGE    TYPE
=========  ======  =========  ================
MYAPP_CTX  TEST    MYAPP_PKG  ACCESSED LOCALLY

I've got the context name hardcoded in various parts of the application (e.g. views, packages, etc) and would rather not have it differ between environments if I can help it.

At the moment my only idea is to parameterize the namespace name in the deployment scripts - so that the namespace will be e.g. MYAPP_CTX_DEV or MYAPP_CTX_TEST depending on a configuration option in the deployment scripts.

Are there any better alternatives that allow me to install multiple versions of my application on the one instance?

Oracle version 11.1.0.7


texts:
I have two questions:
1. Why do I get update conflict in this situation instead of just blocking:
-- prepare
drop database if exists [TestSI];
go
create database [TestSI];
go
alter database [TestSI] set READ_COMMITTED_SNAPSHOT ON;
alter database [TestSI] set ALLOW_SNAPSHOT_ISOLATION ON;
go
use [TestSI];
go
drop table if exists dbo.call_test;
create table dbo.call_test ( Id bigint CONSTRAINT [PK_Call] PRIMARY KEY CLUSTERED ( [Id] ASC ), additional int, incl int );
create index ix_Call on dbo.call_test ( additional ) include( incl );
insert into dbo.call_test select 1, 2, 3;
go

First session:
use [TestSI];
go
set transaction isolation level snapshot
begin tran

   UPDATE dbo.call_test SET additional = 22 WHERE [Id] = 1

And second session:
use [TestSI];
go
set transaction isolation level snapshot

   UPDATE dbo.call_test SET additional = 222 WHERE [Id] = 1

In the second session I get immediately:

Msg 3960, Level 16, State 3, Line 3
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.call_test' directly or indirectly in database 'TestSI' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

This behavior I have as well if I update include column incl instead of nonclustered index key.
What impact does a nonclustered index have on update conflict in this situation? Why are locks not used in this situation?
2. And the second theoretical question:
How does SQL Server handle include columns update?
I mean how does SQL Server update all nonclustered index which have an include columns when we update this value? I don't see anything related in the query plan.
select @@version

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
Mar 18 2018 09:11:49
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0  (Build 18363: ) (Hypervisor)

I checked this example on SQL Server 2019, and behavior on that server is as I expected: second session is locked. Is it a bug or I did something wrong?


texts:
My question is what would be the best way to handle storing a list of values where the next serves as a fall-back. With a completely variable number of fall-backs (specified by our user).

I'm aware storing a delimited list in one SQL field is considered an anti-pattern (such as a list of parameters), however in this case we have a folder name, and if that folder name does not exist, we will fall back to another specified folder name, so on and so forth until all given folder names have been exhausted or until the first working folder name is found.

The order of the folder names tried is also important and must be preserved. Can't have it checking 99 before 8.


texts:
I am trying to fill my table with data but I am having trouble with the datetimes (Incorrect datetime value error). My data is in this format 2017-08-30 10:00:00-07:00.

What kind of transformation is needed so that I can insert the right time in my database?

Here's an example which is not working. I am using MySQL 5.6.10

INSERT INTO table (id, created_at)
VALUES (89208460, "2017-08-30 10:00:00-07:00");

Running SELECT NOW() gives me 2017-09-27 11:47:38, it's a DATETIME(19) field.


texts:
I need to track stock prices for all 8,000+ stocks on the NYSE, AMX, and NASDAQ for at least 48 hours at a time, with stock price quotes for each stock every 2-4 minutes (less than a 5 minute period).

Over the course of the two days, this will accumulate nearly 8M rows of data, which means it requires 20 seconds+ to query them. That's not enough speed. So, in an effort to speed things up, my thoughts are this:

Keep a separate table for each stock symbol.
keep no more than 480 prices in each table (older ones will be moved to symbol_history for long term history)

My reasoning is this:
Each table in a database is effectively kept as it's own file in /var/lib/mysql. So, it makes sense that each table would be its own file because when I want to query information about GOOG, MySQL can go straight to the GOOG table without having to sift and sort through 8M rows looking for WHERE symbol = "GOOG".

Because each symbol would be its own table, we can also use SORT BY and SUM() and other functions with ease and speed since the max number of prices in there would be 400 or so. (20 udates / hour, 10 hours / day, for two days).

But wait... that's only 8,000 tables? Where are the other 8,000?

I'll be dumping "historical data" (data that is more than 48 hours old) into another table ([symbol]_history for that stock symbol. That way, if we need it, we can still get access to older data (potentially year's worth), but the data we're currently working with (the most recent 48 hours) will never be more than 400 or so rows.

Does anyone have a better idea?


texts:
My website has different content types. All content types should be tag-able (except if specified in the model, whatever). I can think of 2 options for my design:

2 tables option : 1 table for the tags (+one other, its i18n content) plus 1 table for the association (content_type , content_id, tag_id).

Pros :

Creation is faster
model parts for the tag is the same (so inherited model)
same thing for controlers.

Potential Cons :

Will the performance be poor?
will the table be too large soon ?
How to query for tags clouds (related to all grouped contents)?

many tables options :
1 table for tags, an association table per type of content.

Pros :

Tables will be shorter

Cons :

Have to customize each model or write a logic/footprint to query the right table
ugly long request / php code to join tags of all content types

Additional Facts :

Front side will have cache, tags are set by content writers.
There are 10 content types.
There won't often be new types of content.
Content count is ~500 , will ~double in 3 years.
MySQL engine InnoDB.
built with yii framework.
will query tags for a defined content type
will query tags for any type of content (together, one big tag cloud for example).

So my idea was to use the first design (2 tables). But I'm not a db expert ...
Would you recommand the second option ? Why ?


texts: Loading data in mysql using LOAD DATA INFILE, replication safe?

texts:
I am trying to load data into mysql database form CSV file. I found that we could use LOAD DATA INFILE command to do it. But as per the mysql documentation it is not replication safe. (See here)

Is there a better way to do it rather than to do it via application?


texts: SQL Server generate self-signed SSL certs performance concern?

texts:
Read this msdn article and doesn't seem to say if it keeps using the same self-signed cert after one is automatically generated.

If SQL Server generates a new SSL cert upon every connection (anyone confirm this?) or every time someone logs in, then does this cause any noticeable performance issue?


texts: Microsoft SQL Server 2016 performance degradation for stored procedure called 1000's of times

texts:
Context :

Small table, stored procedure to update table, first two thousand hits no problem. After those first hits it starts to crawl to a snails pace. This stored procedure has no problems in our current environment of SQL Server 2008 R2 with the calling C# app. In our new testing environment for SQL Server 2016 we see this problem. The offending section of the query deals with a where clauses that has four comparisons of a column not equaling a param for an update of the small table:

WHERE A.Column1 <> @param1 OR A.Column2 <> @param2     etc..

My assumption was parameter sniffing but have ruled that out with various query hints.

I have also tried:

Changing to select not update
Playing with degrees of parallelism (MAXDOP)
Updating stats and recompiling, even doing it mid process
Various changes to the indexes
Resetting connection pooling in calling C# code after X amount of iterations
Removing any transactions controls

The only change that seems to remedy the issue is to remove the where clause and push the logic to the setter:

SET A.Column1 = CASE
        WHEN A.Column1 <> @param1
            THEN @param1
        ELSE A.Column1
        END

I don't like having to change the code, yet I have found no other resources online that could answer why SQL Server 2016 would break.

Any help would be appreciated.


texts:
I have an access to a DB (SQL Server 2008 R2).
The size of the DB is about 40GB.
I am a developer and I'd like to have a local copy of that DB to be able to work offline.

Is there an easy way to get truncated version of that DB (all db objects and about 1000rows per table)?

I have a backup of that DB on a test server, but I am not able to copy 40GB over my slow connection.
I don't have permission to restore that backup and delete rows manually and backup again after.

I probably can script schema by Task -> Generate scripts, but how to script data and limit it to ~1000 rows per table(taking into account the fact that there are about a hundred tables and bounded through FK rows should remain safe without loosing a row at one end)


texts:
I have this query to calculate the Idle Time of a vehicle.

The Business Rule for Idle Time is - Ignition = 1 AND Speed = 0

So I have came up with the following Query.

DECLARE
      @startDate DATETIME = N'2018-09-08',
      @endDate DATETIME = N'2018-09-10 23:59:59.997';

Select
  iAssetId,
  fSpeedKPH,
  dtUtcDateTime,
  eIgnition,
  IdleSeconds = (CASE WHEN eIgnition = 1 AND fSpeedKPH = 0 THEN DateDiffSeconds ELSE 0 END)

FROM (

    Select
        iAssetId,
        fSpeedKPH,
        dtUTCDateTime,
        eIgnition,
        DateDiffSeconds = DATEDIFF(SECOND, dtUTCDateTime, LEAD(dtUTCDateTime, 1)
            OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime))
    FROM VehicleMonitoringLog

    WHERE
        dtUTCDateTime BETWEEN @startDate AND @endDate
        AND iAssetId = 2
) A

ORDER BY
    iAssetId, dtUTCDateTime

SQL FIDDLE - Link

Now I need to GROUP the Idle Times into Single Times. So the result set should look like this:

------------------------------------------------------------------------
iAssetId   |  Start Idle Time  | End Idle Time | Idle Duration (Seconds)
------------------------------------------------------------------------

How do I achieve this?

EDIT
Expected Result

iAssetId  | Start Idle Time      | End Idle Time        | Idle Duration (seconds)
-------------------------------------------------------------------
2         | 08-Sep-2018 00:04:51 | 08-Sep-2018 00:36:51 |  1986
2         | 08-Sep-2018 00:58:28 | 08-Sep-2018 03:50:29 |  10432


texts: Mysql: Any way to properly index 3 ENUM columns with the same options ? (A OR B OR C)

texts:
I have 3 enum() columns with the same option values inside.
I first tried to use the "set" datatype which originally was meant to do that (hold multiple values from a set) but it seems that datatype isn't managed for 15+ years and isn't even supporting an index.
Is there a nice way to index those 3 columns so I can use them in searched without destroying query performance ?
SELECT * FROM TABLE WHERE a='x' OR b='x' OR c='x'
I thought about creating a virtual field which uses a boolean logic (&) on the 3 enum field-numbers and combines them into a large number but that's quite a hack and not nice to maintain.
Has someone solved this sort of task elegantly ?
(I do not want to use a support table and JOIN it, I want to stay with a single table)


texts: Logging lock timeouts in Mysql or MariaDB

texts:
With MySQL or MariaDB, we like to log how often and when lock timeouts occur.

We certainly know how to debug lock timeouts with SHOW ENGINE INNODB STATUS\G or using the table information_schema.innodb_trx.

What we would like to have is something like an option for the slow query log, to log only the lock timeouts. There is even a field like Lock_time which might be useful. Would be the slow query log an option and how would the slow query log act on lock timeouts?


texts: Is it better to add an index and search via the index or add a unique constraint and let an insert fail?

texts:
I have a table with over 2.5 million records in production that keeps track of if a specific user has already viewed an job:

mysql> DESCRIBE job_views;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| job_id             | int(11)      | YES  |     | NULL    |                |
| session_id         | varchar(255) | YES  | MUL | NULL    |                |
| created_at         | datetime     | YES  |     | NULL    |                |
| updated_at         | datetime     | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+

Before adding a row to the table, I want to ensure that there isn't already a row with the same job_id and session_id. Currently the application queries the table for a row that matches (presently without an index, although an index on the two columns has been added), but I believe a unique constraint makes more sense.

Is there a difference between a SELECT using the index and then an INSERT if a row isn't found versus an INSERT that fails because of a unique constraint? If so, which is preferable (or how can I make that determination)?


texts: Impact on database while increasing database initial size

texts:
I have a database whose initial size is 900 GB for .mdf file and 13 GB for log file, I want to increase its initial size to + 100 GB , while doing that in the properties -> filegroup->initial size, do I require a SQL Server restart or downtime after applying the changes?

Also while the size is getting increased, will it hold lock on all the reads & writes that is being done on the database?

I'm doing this as I am planning to pre-allocate size to my database instead of relying upon autogrowth. (I have got general suggestions from this site where pre growing the size of the database is considered to be better than auto growth).

We don't have instant file initialization on. I am new to this dba work and I wasn't aware of the usefulness of this until now.


texts: Getting the most recent date in a table with good performance?

texts:
I have been monitoring my server and have noticed a few slow queries. One is getting the most recent date in a table with about 2691225 records.

I am using the following:

SELECT dt FROM dly WHERE dt < {today's date} ORDER BY dt DESC LIMIT 1

I need the less than today, as there are some anomaly records in the db that have dates that are greater than today somehow.

New relic gives the following tips:

This table was retrieved with a full table scan, which is often quite bad for performance, unless you only retrieve a few rows.
No index was used in this part of the query.
MySQL had to do an extra pass to retrieve the rows in sorted order, which is a cause of poor performance but sometimes unavoidable.
You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
Approximately 2691477 rows of this table were scanned.


texts: postgres materialized views and indexes to improve aggregate performance

texts:
I have a large table with a number of materialized views used for pre-calculating aggregates. New data is inserted only once a day so think of this as a data warehouse type system.

Obviously when I query my main (non-aggregated) table, the planner uses any indexes it deems appropriate. Does that extend to materialized views? If I query the main table and the query involves aggregates, does it recognize that my materialized view already has the values it needs?


texts:
I know there are ways to test for tempdb contention to see if you need to add more tempdb files, but how can I test (and maybe prove) if I have too many tempdb files?

I've inherited a relatively large SQL Server (36CPU, 428GB RAM) that has 24 tempdb files.  I feel like this may be a part of the performance issues we are having, but I don't know how to prove that there are too many.

I know what the recommendations are, but somewhere along the way, a DBA before me added them to "improve performance," and I'm in a situation where I can't reduce the number or make changes to the number of files without showing that there is a need.  Saying that it's Microsoft's recommendation isn't good enough this time, unfortunately.


texts: Postgres memory usage grows over time, resets when clients restarted

texts:
I have a Postgres DB running on the same small server as some applications that use it. Immediately after restarting the applications, Postgres uses <100 MB memory. After a few days without restarting, this climbs to >1GB. The memory usage of the clients doesn't increase correspondingly, and the total number of connections stays roughly constant. What could be the reason for this growth? This says "Every additional table touched will pull in some more metadata, so over the course of a long lived connection in the pool, it could grow a lot if you have a lot of tables."

postgresql.conf details:

max_connections = 100
shared_buffers = 512MB
work_mem = 4MB

(but usually there are only around 20 connections)

I'm running Postgres and the applications Docker containers. The server has 4GB RAM.


texts: How do I fix #1062 error received while importing EE database to localhost from remote server

texts:
I am trying to import a MySQL database into the localhost database that was exported from the remote database and I am receiving a #1062 error (which is weirdly in French!):

#1062 - Duplicata du champ '1' pour la clef 'PRIMARY'

Google translate: # 1062 - Duplicate field '1' for key 'PRIMARY'

The code that it highlights is as follows:

--
-- Dumping data for table `exp_accessories`
--

INSERT INTO `exp_accessories` (`accessory_id`, `class`, `member_groups`, `controllers`, `accessory_version`) VALUES
(1, 'Expressionengine_info_acc', '1|5', 'content|members|admin_content|design|tools_communicate|homepage|addons_fieldtypes|content_files|admin_system|tools_data|addons|tools|tools_logs|tools_utilities|addons_accessories|content_files_modal|myaccount|addons_modules|addons_plugins|content_publish|content_edit|addons_extensions', '1.0'),
(2, 'Cartthrob_acc', '1|5', 'content|members|admin_content|design|tools_communicate|homepage|addons_fieldtypes|content_files|admin_system|tools_data|addons|tools|tools_logs|tools_utilities|addons_accessories|content_files_modal|myaccount|addons_modules|addons_plugins|content_publish|content_edit|addons_extensions', '1.0');

I am a novice with SQL and I can't see what it is on about. I can't see a duplicate in the primary key, the accessory_ids appear to be 1 and 2? Am I missing something here? And why is there a duplicate here and not on the remote server?

Many thanks

Gabe


texts: Display summary results based on empty value in another column

texts:
I need some assistance and guidance.

I want to retrieve a summary of results based on values in another column.
For example, I have a column in my data set that shows an audit date. There will be some records which will have an empty audit date, in that case I want to display summary results based on it which where so long as there is a specific date which is empty,a value will be assigned to it based on its grouping. I have made a sample illustration of the problem below:

ORIGINAL TABLE :

Table A
+--------------+--------------+--------------+
| officecode   | Username     |  auditdate   |
+--------------+--------------+--------------+
| ABC          |  ABCTOM      | 02-Dec-2012  |
| ABC          |  ABCPET      | 08-Dec-2012  |
| ABC          |  ABCLEE      | 12-Dec-2012  |
| ABC          |  ABCHET      | 25-Dec-2012  |
| DEF          |  DEFJKT      | 23-Dec-2012  |
| DEF          |  DEFGET      | 12-Dec-2012  |
| DEF          |  DEFHTT      | 18-Dec-2012  |
| DEF          |  DEFEET      | 16-Dec-2012  |
| XYZ          |  XYZOOP      | 16-Dec-2012  |
| XYZ          |  XYZIOO      |              |
| XYZ          |  XYZJMN      | 16-Dec-2012  |
| XYZ          |  XYZGHB      |              |
+--------------+--------------+--------------+

INTENDED RESULT :

+------------+------------+
| officecode | audit_status
+------------+------------+
| ABC        | Complete   |
| DEF        | Complete   |
| XYZ        | Incomplete |
+------------+------------+

I've done some research and tried pairing group by with a case but i seem to not be getting the results.

SELECT distinct officecode,
CASE WHEN auditdate IS NULL
      THEN 'INCOMPLETE'
     WHEN auditdate IS NOT NULL
      THEN 'COMPLETE'
     END AS AUDIT_STATUS
       FROM A GROUP BY officecode;


texts: Autoclose property resets to true after attaching db even if model is set to false

texts:
I am using SQL Server Express 2008 R2.
The auto close property of a newly created database will be set to value same as model db. In my case I have set autoclose to false for model so that when I create a new database it will have autoclose set to false as well.

If I detach a database with autoclose set to false and then attach it, autoclose is set to true. I was expecting autoclose to be false since it will inherit its properties from the model database. In my case detaching and attaching a database results in the autoclose property being set to true.

Why is it so? Is there any solution to this problem?


texts:
Before few month box of SQL has been crashed. We have almost database back with some tools and running application smoothly on it. But when i try to take backup of database at that time it gives error as below,

  The log scan number (3533468:412:0) passed to log scan in database '****' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. (Microsoft SQL Server, Error: 9003)

Would anyone help me to out of this?

I have even try to copy database but i am failed.

Thanks in Advance.


texts:
As I show , I have server database version-10.50.1600 and local database version-10.0.1600 .
How can I copy database (with all tables and data(s)) from server to local which has different version ? I use MS sql server 2008 R2 .


texts: How to pivot on multiple columns in SQL Server?


texts: How do I convert a TIMESTAMP to a BIGINT in Azure Synapse

texts:
What's the best way to convert a TIMESTAMP to a BIGINT In Azure Synapse. I tried this (which works) but seems clumsy. Is there a better, more concise and efficient way of doing the same thing?
SELECT CURRENT_TIMESTAMP AS CURR_TS,
       CONVERT(BIGINT,
           CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) +
           REPLACE(CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114), ':', '')) AS CURR_TS_NUM


texts: Best query to exclude existing friends from query

texts:
I have a User table

/ id_user / username / ..

friends table (pair AB and BA):

/ friend_of / friend_to

and a friend_request table (used for pending friend requests)

/ id_req/ sent_from / sent_to

I would like to have a little search for new friend feature (autosuggestion style) and I would like NOT to show in the list friends that are ALREADY in my friends list OR being in a friend request pending.

The idea behind the query would be to show the username of user NOT in the friend_to column and NOT in the sent_to column.

I came up with these two flavors:

select id_user,username
from public.user u
where
username like '%tom%'
and
u.id_user NOT IN (SELECT sent_to FROM friend_request where sent_from = 288)
AND
u.id_user NOT IN (SELECT friend_to FROM friends where friend_of=288);

OR

select id_user,username
from public.user u
left join friend_request fr
on
u.id_user=sent_to AND fr.sent_from=288
left join friends f
on
u.id_user = f.friend_to AND f.friend_of=288
where username like '%tom%' AND sent_to IS NULL and friend_to IS NULL;

Both of these produce the same result. But I would like some advice on which one is a better choice performance and bets practice wise?


texts: Cannot get a message to go to a SQL Server Service Broker Queue

texts:
I am trying to setup a simple SQL Server Service Broker messaging setup.  I am following the MS tutorials, but I cannot get the following code to work.  Everything runs fine, but the message does not appear on the queue.  Can anyone identify the problem.

Thanks for any help.

-- This query returns "1"
SELECT
    Is_Broker_Enabled
FROM Sys.Databases
WHERE Name = 'ActivationTest2';
GO

-- Create the message types.
CREATE MESSAGE TYPE TestRequestMessage VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE TestReplyMessage VALIDATION = WELL_FORMED_XML;

-- Create the contract.
CREATE CONTRACT TestContract (
    TestRequestMessage SENT BY INITIATOR,
    TestReplyMessage SENT BY TARGET) ;

-- Create the target queue and service.
CREATE QUEUE ActTestTgtQueue;
CREATE SERVICE ActTestTgtService ON QUEUE ActTestTgtQueue;

-- Create the initiator queue and service.
CREATE QUEUE ActTestInitQueue;
CREATE SERVICE ActTestInitService ON QUEUE ActTestInitQueue;
GO

-- Start a conversation, send the request message.
DECLARE
    @initdlghandle uniqueidentifier,
    @requestmsg nvarchar (100) ;

BEGIN TRANSACTION;

BEGIN DIALOG @initdlghandle
    FROM SERVICE ActTestInitService
    TO SERVICE 'ActTestTgtService'
    ON CONTRACT TestContract
    WITH ENCRYPTION = OFF;

SET @requestmsg = N'<RequestMsg>Message for tgt service</RequestMsg>';

-- This returns a GUID and the message text above.
SELECT
    @requestmsg AS Sentrequestmsg
  , @initdlghandle AS Handle;

SEND ON CONVERSATION @initdlghandle
    MESSAGE TYPE TestRequestMessage (@requestmsg) ;

COMMIT TRANSACTION;
GO
-- Check the queue for messages.
-- THIS RETURNS ZERO ROWS.
SELECT
    *
FROM ActTestTgtQueue;
GO


texts: Transferring large Postgres database from one machine to another

texts:
I have a large (100GB+) database that I need to transfer to multiple machines over the next 6 months or so. This database can't be shrunk and had any data dropped to modify the size (unfortunately).

Is there any way to transfer the database from my machine to another other (Windows) machine without providing an import dump script and the output of pg_dump?

Optimally I'd just be able to plop the file in a directory and it'll work, or have something similar to SQL Server's attach database option where I don't need to make the user spend 12+ hours importing the database.


texts: SQL Server and ASP.NET - Backup Log database with Application Pool in Always Running

texts:
I have a database on SQL Server Express used for 3 Asp.net applications. The Recovery Model is Full.
Every day I execute a backup database and log with this code
BACKUP DATABASE @databaseName TO DISK = @file WITH INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
BACKUP LOG @databaseName TO DISK = @fileLog WITH NOFORMAT, NOINIT,  NAME = N'Database-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

When the Start Mode of Application Pools is on OnDemand the LDF is reduced every day; when I use AlwaysRunning the log is not reduced.
I want to use application pools with always running. Can I use a shrink to reduce log size? Even if users are using websites?
ALTER DATABASE [DatiMachinaWeb] SET RECOVERY SIMPLE
DBCC SHRINKFILE(DatiMachinaWeb_log)
ALTER DATABASE [DatiMachinaWeb] SET RECOVERY FULL

Am I wrong? How can I solve it? Thanks


texts: How to give SQL Server Job Owner permission to Proxy a Credential

texts:
I've created a Credential Cred1 (DOMAIN\USER1 with the secret = domain password)
I've created a SQL Agent job with owner to NT AUTHORITY\SYSTEM
The job needs to proxy as credential Cred1

So when I run the job it fails with the message that NT AUTHORITY\SYSTEM wasn't allowed to proxy 1 (I only have one credential) for CmdExec.

  "Unable to start the execution of step1 (reason: JobOwner NT
  AUTHORITY\SYStem doesn't have permissions to use proxy 1"

I've done a bit of google search for the permissions needed, and I read that I need to give operator access to the NT AUTHORITY\SYSTEM login in the MSDB. I've tried this but still no luck.


texts: cluster_database TRUE/FALSE what does it mean in Oracle RAC?

texts:
I have 3 databases on an Oracle RAC (two nodes) one database which is running with CLUSTER_DATABASE=TRUE on both nodes, the other two runs on each node with CLUSTER_DATABASE=FALSE

what does it mean the CLUSTER_DATABASE value?
and if it is CLUSTER_DATABASE= FALSE, is it as a normal single node Oracle Database?
what happen to the database instance that runs as (CLUSTER_DATABASE=FALSE) when the node is offline? dose it goes online on the other node or it goes offline as well?


texts:
I'm new to aws (just one day old). I'm impressed with aws free tier and want to utilize to its best. As of now I'm done with installing mongodb on aws micro instances and mongod EC2 instances are running fine. Now, I want to test my use case and unfortunately couldn't find much details on use case implementation related to mongodb shards, replica sets, traffic, load balancing, logs, cluster monitoring, metrics etc.,

It would be great if someone who has deployed mongodb databases on aws clusters and can share their tips and best practices here.

use case: I'm planning to deploy ProductCatalog and geojson apps on aws windows and linux micro instances.

Thanks in advance!


texts: Monumental difference in execution time between queries when using RECOMPILE query hint

texts:
I have two almost identical queries running on the same SQL Server 2005 instance:

The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application developer, just the DBA :).
The second one is exactly the same as the first one, added a OPTION (RECOMPILE) at the end.

Nothing else has been changed.

The first one takes 55 seconds everytime its run.
The second one takes 2 seconds.

Both result sets are identical.

Why would this hint generate such a dramatic gain in performance?

The Books Online entry on RECOMPILE doesn't offer a much detailed explanation:

  Instructs the SQL Server Database Engine to discard the plan generated
  for the query after it executes, forcing the query optimizer to
  recompile a query plan the next time the same query is executed.
  Without specifying RECOMPILE, the Database Engine caches query plans
  and reuses them. When compiling query plans, the RECOMPILE query hint
  uses the current values of any local variables in the query and, if
  the query is inside a stored procedure, the current values passed to
  any parameters.

  RECOMPILE is a useful alternative to creating a stored procedure that
  uses the WITH RECOMPILE clause when only a subset of queries inside
  the stored procedure, instead of the whole stored procedure, must be
  recompiled. For more information, see Recompiling Stored Procedures.
  RECOMPILE is also useful when you create plan guides. For more
  information, see Optimizing Queries in Deployed Applications by Using
  Plan Guides.

Since my query has a lot of local variables, my guess is that SQL Server is able to (seriously) optimize it when I use the OPTION (RECOMPILE) query hint.

Everywhere I look people are saying that OPTION (RECOMPILE) should be avoided. The explanation for this is generally that using this hint SQL Server is not able to reuse this exection plan and therefore has to waste time recompiling it everytime. (But) Given the gigantic performance advantage, I'm inclined to think that using this query hint this time would be a good thing.

Should I use it?
If not, is there a way I can force SQL Server to use a better execution plan without this hint and without altering the application?


texts:
I have different 'modules' . Currently , they are:

Votes
Chats
To-do lists

I am using mysql and long polling to get updates about these modules in near real time.

I have also have a table called groups, and users. users and groups have a many to many relationship.

My votes-related tables look like this:

Aside of these, I will be creating a central actions table, that contain UserID , group_ID, and action type. This is to make it easier on the database when long polling. The central actions table will tell the poller if there is any new data for the several modules, and if there is, then the poller will go to the respective module_actions table to get the detailed information.

I am doing this because the number of modules will increase with time. right now it is three, but it can be 10 or even more. I don't want to poll 20 tables every 1 second for every user that is logged in.

In the image that i have provided, there is a table called vote_actions. This will store which user performed what kind of action on which group.
Action TYPE can be things like:

Created_sub_item
Voted_on_sub_item
Unvoted_on_sub_item
Modified_vote_item

These will be predefined by my database(enum values), and depending on the request, a new row with the right action type will be inserted. This works when the user acts upon a vote_sub_item. But I am not sure how to record actions when he performs action on the VOTE itself (like creating a new vote with sub items, changing description etc).


texts:
I have a Java + MongoDB web application deployed on an OpenShift production medium gear, with no scaling enabled, with 11GB storage.

At the moment, I have some data stored, so MongoDB accurately reports a Data Size of 720.4K. This grows appropriately when I add new objects, everything is ok.

What I am concerned about is the reported Storage Size of 1.52M. Since this is a production gear, I am expecting that this allocated storage space will grow as needed (at the current rate of data production, I estimate the 1.52M limit will be reached in a few days). Is my expectation correct?

ETA: In the meantime, the Storage Size has increased to 2.02M, so I guess that answers my question.


texts: Two foreign keys referring to one primary key

texts:
I'd to create a database that records transactions between two users. A user can transfer points (think of it as money) to another user. user table looks like:

| userID        |    name       |      email       |   balance  |
| ------------- |---------------|------------------|------------|
| 101           | alpha         | alpha@mail.com   |   1000     |
| 102           | bravo         | bravo@mail.com   |    500     |
| 103           | charlie       | charlie@mail.com |   2000     |

And the transaction table should look like:

| transactionID |  from_user    |   to_user        | transfer_amount  |
| ------------- |---------------|------------------|------------------|
|   1           | 101           |       103        |   100            |
|   2           | 102           |       101        |   150            |
|   3           | 102           |       103        |   200            |

I tried to draw a schema with Vertableo which doesn't seem have one to zero or many relationship option. But a user might not send or receive points at all, so the relationship should be one to zero or many. However, this schema on Vertableo throws the error reference name must be unique.

Could someone give hints to draw a schema or provide SQL code?


texts: Undefined symbol when migrating to Postgres 11

texts:
After upgrading Postgres 10 to version 11, by using pg_upgrade, I get this error:

  could not load library "$libdir/postgis-2.4": ERROR:  could not load library "/usr/pgsql-11/lib/postgis-2.4.so": /usr/pgsql-11/lib/postgis-2.4.so: undefined symbol: geod_polygon_init

I'm using CentOS 7 and Postgis 2.4.

I've tried to investigate a little.

# nm -D /usr/pgsql-11/lib/postgis-2.4.so |grep geod_polygon_init
  U geod_polygon_init

The function is exported by the shared library!
Why am I getting this error?

# ld -v /usr/pgsql-11/lib/postgis-2.4.so
GNU ld version 2.27-28.base.el7_5.1
ld: warning: cannot find entry symbol _start; not setting start address
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `repalloc'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `hash_create'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `get_guc_variables'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `init_MultiFuncCall'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `SPI_exec'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `WinGetFuncArgInPartition'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `SPI_gettype'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `geod_polygon_addpoint'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `get_fn_expr_argtype'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `GetDatabaseEncoding'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `SPI_execute'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `CurrentMemoryContext'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `SPI_getvalue'
...

Related questions:

https://superuser.com/questions/1267282/could-not-load-postgis-2-4-so-undefined-symbol-float8getdatum
https://www.postgresql.org/message-id/20180123173450.GK25452%40telsasoft.com


texts:
Here are the things I can't change:
We got a mySQL myISAM replication running a master and some slaves. One slave is being used for nightly backups using mysqldump. The backups is using heavy locks forcing it's slave to "age" compared to the master.
mysqldump is connecting as root dumping all the databases while every other connection is using one non-SUPER-user per database. The dumps are started using a simple shell script on a dedicated backup server.

Here is the problem:
I'ld like to detect if a backup is running or not while being connected as a "normal" user to one of the databases running on the server being backuped. Cronjobs should be able to react on the running backup by exiting early or processing only really important required jobs which can't wait until the backup is done.
The server running the backup script has no connections to the webservers, their only common point is the mysql server.
The backup script could do some things to flag the server as "currently running a backup".
I'd prefer to set some kind of "global server-side environment variable visible by all users" to ignore only the one slave running the backup but it would also be ok to simply know that a backup is running on that database or replication chain.
Using the database for this is no option as it's being locked during the backup.

I tried to create a temp table on backup start (existing = backup is running, not existing = no backup running), but temp tables only show up for the connection which originally created it (as expected). I googled if a non-SUPER-user could get the permission to see the SHOW PROCESSLIST of all connections including other users and root (any connection from the backup server = backup is running) - but no luck. Giving SUPER to all database users is no option.

Any suggestions?

Thanks for reading (and answering).


texts: How I can corrupt a data base page and again restore the same

texts:
I want to corrupt a database page, and restore the same page.

How do I do that?


texts: Ola Hallengren backup systax when excluding a database

texts:
I would like to backup most of the databases except the database DBA.
I use below syntax in the job step:

EXECUTE [dbo].[DatabaseBackup]
    @Databases = 'USER_DATABASES', '-DBA'
    @Directory = N'G:\BACKUP',
    @BackupType = 'DIFF',
    @Verify = 'Y',
    @CleanupTime = 336,
    @CheckSum = 'Y',
    @LogToTable = 'Y'

But it gives an error:

  Msg 102, Sev 15, State 1, Line 3 : Incorrect syntax near '@Directory'. [SQLSTATE 42000]

I also tried:

@Databases = 'USER_DATABASES', -'DBA'

does not work either.

Can anyone help? Thanks.


texts:
I'm responsible for writing an app to juggle a huge amount of client and business intelligence information. This information involves records for various businesses and metrics like revenue, their web platform, etc. For reporting purposes, it has been decided that the data now must be time-stamped so that we may construct reports like "Foo Inc.'s revenue increased by 200% when they switched to digital television in 2010."

Or something to that effect. We don't really deal with clients that are that far behind the times.

As it stands, the data is stored in MongoDB and its schema (or lack thereof) exactly represents how it's displayed. A document has a name, maybe embedded documents for address information or operations-related stats, etc. I somehow need to add the concept of time-stamping, or versioning to this. The most obvious way to model this would be like so:

key: [{ value: "bar", time: "2012" }, { value: "baz", time: "2011" }]

Where every property is timestamped. Obviously, this breaks the schema, and adds maintenance issues. I've also toyed with using conventions (naming property keys like "Foo (2012-09-31)", and keeping copies of entire collections with a timestamp.

Obviously, this is a very big feature to implement and I was wondering what the best way to implement this would be. What is the most effective and performance-friendly way to add timestamps to data in MongoDB?

Edit:
Yes, I'm looking at versioning, although with all likelihood new data sets will come in large batches and not as often (for instance, every quarter). This is why I was considering using separate collections for each set and timestamping each collection, because the data is lots and far between, but I'd love to know if there's a better, more maintainable way.


texts: Update staging data with production data in Postgresql

texts:
I'm using PostgreSQL (version 9.3) on my server and I want to update the data in my staging instance database with the data in my production instance database. Each database is controlled by a different role, and I want to keep their privileges separate in the transfer.

I tried following the documentation and ran these commands with my separate users (which have access to the corresponding PostgreSQL roles):

staging-user: pg_dump production_db > prod_db_file
production-user: psql --single-transaction staging_db < prod_db_file

But I got a couple of errors: must be owner of extension plpgsql and syntax error at or near. Besides, I'm not sure if this would have done what I wanted anyway. There are a lot of optional flags on the pg_dump and psql commands, and I don't know which ones I should use. (I've tried many combinations of flags and nothing has worked so far.)

Note: When I tried the above commands, my staging database already existed.

How do I move my production data to my staging database?


texts: Must an index cover all selected columns for it to be used for ORDER BY?

texts:
Over at SO, someone recently asked Why isn't ORDER BY using the index?

The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows.  One of the columns, an integer, was indexed—and the OP sought to retrieve his entire table sorted on that column:

SELECT * FROM person ORDER BY age

He attached EXPLAIN output showing that this query was resolved with a filesort (rather than the index) and asked why that would be.

Despite the hint FORCE INDEX FOR ORDER BY (age) causing the index to be used, someone answered (with supporting comments/upvotes from others) that an index is only used for sorting when the selected columns are all read from the index (i.e. as would normally be indicated by Using index in the Extra column of EXPLAIN output).  An explanation was later given that traversing the index and then fetching columns from the table results in random I/O, which MySQL views as more expensive than a filesort.

This appears to fly in the face of the manual chapter on ORDER BY Optimization, which not only conveys the strong impression that satisfying ORDER BY from an index is preferable to performing additional sorting (indeed, filesort is a combination of quicksort and mergesort and therefore must have a lower bound of Ω(nlog n); whilst walking through the index in order and seeking into the table ought to be O(n)—so this makes perfect sense), but it also neglects to mention this alleged "optimisation" whilst also stating:

  The following queries use the index to resolve the ORDER BY part:

SELECT * FROM t1
  ORDER BY key_part1,key_part2,... ;

To my reading, that is precisely the case in this situation (yet the index was not being used without an explicit hint).

My questions are:

Is it indeed necessary for all selected columns to be indexed in order for MySQL to choose to use the index?

If so, where is this documented (if at all)?
If not, what was going on here?


texts: Need a solution to store cheaply a big MongoDB collection

texts:
We have the typical problem of Twitter's timeline tweets, or Facebook profile's news:
we need to show the users only the new information (storing them in a fast db), and put all the old ones somewhere else (but able to load them on request in a reasonable time).

The reason for moving the old ones is both economic (we don't need and don't want tens of GB accessed rarely occupying space on a fast production db) and for performance (tens of GB of extra information gives much overhead for indexes and queries).

Now we have all the information, recent and old, on a MongoDB hosted on MongoHQ. Currently everything is on its own specific database, that contains only one big collection.
I would like to have only the most recent ones in the fast production db, and to take the older ones, such as the data older than let's say one month, and moving them in another database, or to another storage. I would set up a nightly job that will do the stuff so to maintain only recent data on the MongoDB.
I don't want to manage the database by myself now, so I was looking for an hosted solution.

I think we have two options for putting old data:

storing the data on another db hosted in the cloud, cheaper than the Mongo hosting we currently use as primary db, such as Amazon's DynamoDB, or Google Big Query. Going for this would be to transform our MongoDB collection to be saved in another format, and then to transform it back when we need to read it again
setting up "our" server, buying for example a package for MongoDB hosted on EC2. Going for this means adding simply another db to our infrastructure, but it is a quite expensive solution as those services provide high performances database, and we don't need that.

I think that (2) is the easy solution to implement, but (1) would be much cheaper giving that the data will be rarely accessed and the new db would be used much as a storage facility.
In the next months, we will definitely go for (2), as if data and usage increase I think that the best option would be to manage most of the things by ourselves instead of relying to hosting on PaaS or SaaS, but now this solution seems quite expensive.

Do you have any suggestion for a situation like this? Is there a third way that I haven't considered?


texts: PgBouncer default_pool_size for single database server

texts: I have a single Postgres server with a single database and a single user. In such a case should PgBouncer default_pool_size not equal max_connections in the Postgres config? And then max_client_connections would be something like default_pool_size * 10.


texts:
My OS is CentOS release 6.4 (Final) and my MongoDB version is 2.4.1.

I tried:

use admin;
db.shutdownServer()

and I got in output:

assert failed : unexpected error: "shutdownServer failed: exception: dbexit timed out getting lock"
Error: Printing Stack Trace
    at printStackTrace (src/mongo/shell/utils.js:37:7)
    at doassert (src/mongo/shell/assert.js:6:1)
    at assert (src/mongo/shell/assert.js:14:1)
    at DB.shutdownServer (src/mongo/shell/db.js:342:1)
    at (shell):1:4
Thu Apr  7 11:42:52.577 JavaScript execution failed: assert failed : unexpected error: "shutdownServer failed: exception: dbexit timed out getting lock" at src/mongo/shell/assert.js:L7

Then I tried to force shutdown, but the output is the same. Can anyone help me?


texts: MySQL Workbench Unable to Connect to Azure Databases for MySQL

texts:
When  trying to connect to a MySQL database in Azure I get the following error:

  ERROR 2026: SSL connection error: socket layer receive error

At first I thought it was a firewall/security issue but it appears not. The certificates are correct - and I have also tried with SSL disabled on both ends.


texts: I Need Help With My Database Design for Product Management

texts:
I'm designing a MySQL database and the front end for my work. The database will store all of the products that we sell on our marketplaces and their relevant data. We will be able to lookup products and see their calculated cost for our different marketplaces. I plan on expanding the functionality in the future but for the moment our goal is just product pricing and dimensional data lookup.
How does this design diagram look to you guys? Is there anything that you would recommend me to add or change? I am open to criticism as this is my first professional database project.
Thanks
Here is the sql file https://github.com/ryanbbernstein/Database/blob/master/Product%20Database.sql


texts:
One of our customers uses for some columns the datatype DECIMAL(18,0) in his SQL Server 2008R2 database. Because the columns grow quite slowly, he recently proposed to change the datatype to DECIMAL(5,0) to regain some storage.

According to the MSDN library, the storage space of the DECIMAL(5,0) datatype is, just like the DECIMAL(9,0) datatype, 5 bytes. INT is 1 byte smaller, but can store everything in the range of -2^31 to 2^31 instead of the -99,999 to 99,999 which DECIMAL(5,0) can store. Even the largest DECIMAL which fits into 5 bytes (DECIMAL(9,0)) can store only integers in the range -999,999,999 to 999,999,999 (which is less than half of the range INT offers in 4 bytes).

I can think of two "benefits" of using DECIMAL over INT:

The ability to add scale afterwards, without using more storage space
The ability to scale the precision up to 38 digits, without altering data type

but these aren't real benefits in my opinion:

Adding scale to integers does only make sense in very few cases (in most cases where scale does make a difference, it could also be added beforehand)
SQL Server sees every precision / scale combination as a different data type, so the datatype isn't left alone when increasing the precision or scale.

This makes me wonder: what is the added benefit of a DECIMAL(5,0) datatype for integers?


texts:
I want to create a smart way to execute a SQL script when rolling out DB changes into QA, UAT and production environments in order to smoke test database changes are correct after maintenance has been completed.

What's the best way to do this?

For now I am running multiple such SQL selects to do a visual compare of existing DB values to expected values. Here is my code:

select value as DBvalue, 'text' as PRODvalue -- could be 'text' or 'html'
from ConfigParams
where paramname = 'EmailBodyType'
and environment = 'prod'

Then the result will be a two-field result set that shows DB values next to expected values.

I have a listing of 10 or so SQL selects to validate param values are updated and correct after the DB work is done. Is there a better way?


texts: Store lat lon in addition to the geography data type?

texts: It's my first time using Postgis, I have been doing some reading on using it. I have added a geography data type to my table, but the values are stored in a binary format. Is it good practice to also store the lat and lon in separate columns as text or points??


texts: Why is my query suddenly slower than it was yesterday?

texts:
[Salutations]

(check one)

[ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer,

I have a (check all that apply)

[ ] query [ ] stored procedure [ ] database thing maybe

that was running fine (if applicable)

[ ] yesterday [ ] in recent memory [ ] at some point

but is suddenly slower now.

I've already checked to make sure it's not being blocked, and that it's not the victim of some long running maintenance task, report, or other out of band process.

What is the problem, what should I do, and what information can I provide to get some help?

[*Insert appropriate closing remarks*]


texts: Why is “Access this computer from the network” permission required for proxy credential?

texts:
https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy?view=sql-server-ver15#Restrictions

SQL Server Agent proxies use credentials to store information about
Windows user accounts. The user specified in the credential must have
"Access this computer from the network" permission
(SeNetworkLogonRight) on the computer on which SQL Server is running.

I have logged on to the SQL server computer. Connected to SSMS using the SA user. Created new credentials with the username as domainame\user1. Then configured proxy to use this credential.
What is the purpose of giving this credential user domainname\user1 "access this computer from the network"? The sql server/proxy is on the same computer - so why is there mention of network?


texts: How to get average of lat/long from cities by county that they're in

texts:
I found this script but I'd like to get the average of the latitudes/longitudes so I can just have the latitude/longitude of the counties. I tried

SELECT id, CITY, COUNTY, LONGITUDE, LATITUDE, AVG(LONGITUDE) AS val_1, AVG(LATITUDE) AS val_2
FROM directory_platform_cities
GROUP BY id, COUNTY;

But it's just attaching the lat/long of the first city to the county, is there a query that can do this? My intention is to get the latitude/longitude of the counties using the coordinates of their respective cities.


texts: Select lowest ID and calculate deviation from the lowest

texts:
I'm trying to create a view. I have some data in my SQL Server database, and I want to order them by their mark1 and mark2. Now when I sort them I want to select the mark1 who has the lowest mark2 and calculate what's the deviation from other mark1s.
The table is looking right now like this, but without deviation.

|   mark1    |    mark2    |      mass    ||    deviation |
|:-----------|------------:|:------------:||:------------:|
| 0001       |      10000  |     10,0     ||    min0001   |
| 0001       |      15000  |     12,0     ||    +20%      |
| 0002       |      11200  |     18,0     ||    min0002   |
| 0002       |      13000  |     35,5     ||    +97.2%    |
| 0002       |      14565  |     85,5     ||    375%      |
| 0003       |      15004  |     15,4     ||    min0003   |
| 0003       |      21250  |     17,4     ||    +12.9%    |
| 0003       |      60764  |     16,4     ||    6.49%     |

And now, as you see it's ordered by mark1 and mark2.
Now, my problem is the deviation column, I need it to take mark1, Let's say 0001 (his mark2 is 10000) and find the lowest mass and find the deviation from other 0001 (15000). As you can see the in the table.


texts:
I know this has been asked a lot of times and I've checked almost all of the links out there but just want to have a third-party opinion on our use case.

So I'm working for an IOT company and we already have prometheus installed on our servers. Primarily we use prometheus to monitor the server and app's health and some sensor-related data. Now we have a new feature where we need to save the data when an IOT device changes location.

I did a bit of reading on prometheus and read about a line on their comparison page: https://prometheus.io/docs/introduction/comparison/#prometheus-vs-influxdb

  Where InfluxDB is better:

  If you're doing event logging.
  Commercial option offers clustering for InfluxDB, which is also better for long term data storage.
  Eventually consistent view of data between replicas.

So did a bit of reading about influxdb and it seems the right tool for our use case. Was wondering if we can just continue using prometheus as our time-series database rather than adding another tool which we need to maintain in the future. Or does it make sense to use a proper time-series database not just for monitoring (as I believe what the original intention of creating prometheus was).


texts: What's the best way to get data for sampling points within a timeframe

texts:
I'm looking for some advice how to set up a set of queries which check for an aggregated value for sampling points within a time period. This should run at an IQ-server, so maybe not this many procedure calls would be cool ;)

I had a look into the windowing feature. I think it might only work for aggregate data for timeframe, not at a sampling point. So having this scenario:

I have a list of items with a start and an end date. I need to collect a sum of processes active at a current time. It's not about system processes, but more about something like how many craftsman were working at a given time.

Imagine tables like this (I've modified the example a little to leave out boring parts.... so might it not run perfectly)

create table items (
    id int ot null default autoincrement,
    "Type" integer,
    TimeStampStart datetime null,
    TimeStampend datetime null
)

is currently used by this queryset:

create table #Processes(
  "Type" integer,
  "timestamp" "datetime" null,
  "Sum" integer null
  )

set @date = '20120303'
while @date <= '20130505'
  begin
    insert into #Processes
      select "Type",'timestamp'=@date,'Sum'="count"()
        from "items"
        and "TimeStampStart" between "dateadd"("day",-"abs"(100),@date) and @date
        and "TimeStampStart" <= @date
        and "isnull"("TimeStampEnd",@date) >= @date
        group by "Type"
    set @date = "dateadd"("ss",3600,@date)
  end
select * from #Processes;

This might not the best way of doing it. So I'm looking for a better approach ;)


texts: Cannot run a job to update table on a linked server

texts:
I am running SQL Server 2012 on a server (server 1) and have set up a job to run a stored procedure which will summarize data from a series of tables and update a table on a linked server (server 2) which is also SQL Server 2012. I can successfully run the stored procedure on it's own, but when the scheduled job runs, I get the following error:

  Executed as user: NT AUTHORITY\NETWORK SERVICE. The OLE DB provider "SQLNCLI11" for linked server "222.222.222.222" reported an error. Authentication failed.
  [SQLSTATE 42000] (Error 7399)  Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "222.222.222.222".
  [SQLSTATE 42000] (Error 7303)  OLE DB provider "SQLNCLI11" for linked server "222.222.222.222" returned message "Invalid authorization specification".
  [SQLSTATE 01000] (Error 7412).  The step failed.,00:00:00,16,7412,,,,0

I suspect this is a security issue, but cannot find the settings that will let me run this.


texts: a story about a bad replication issue - insight needed MYSQL

texts:
A
id
val

B
id
A_id

We are not sure of what happened but it appears that when replication occurred on a series of these two table datasets, that A failed replication, but another insert got the lost ID, and then B referenced the wrong record in A, using what should have been the correct ID.

This occurred more than once, so its not like we lost one record from A and everything is off by 1.  It happened sporadically and from initial investigation is painful to unthread.

such as

original/correct:
A.id = 1, A.val = 'XYZ'
B.id = 1, A_id = 1

A.id = 2, A.val = 'ABC'
B.id = 2, A_id = 2

bad replication:
A.id = 1, A.val = 'ABC'
B.id = 1, A_id = 1

A.id = 2, A.val = 'WTF'  <--- some other record due to the offseting
B.id = 2, A_id = 2

gets worse and worse as more replications happened.

Does it make sense that losing a record like this would cause the next record to assume its ID, but the foreign key replications would maintain their original foreign key ID and hence give us this cluster f?


texts:
Select Query with S lock wins on Insert with IX lock in deadlock in Sql Server 2008R2.

Attaching the deadlock xml file.Please help!

http://pastebin.com/nY9EXbS8

This is from the error log by enabling deadlock trace flags

Also,I am using below query to get the deadlock report:

WITH SystemHealth
AS (
SELECT CAST(target_data as xml) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON s.address = st.event_session_address
WHERE name = 'system_health'
AND st.target_name = 'ring_buffer')

SELECT XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadLockGraph
FROM SystemHealth
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'

But it's taking long time to run and not giving any results even we have few deadlocks captured in error log. Some times, I get the result after so much of time. What I am missing here?


texts: I need the SQL Server Management Studio client so I can use it to access a 2014 instance but am having a hard time finding it via Google. Where would I find only the client instead of the whole SSMS download? Any ideas on where to find it?


texts:
I have several (>30) MySQL clients inserting into InnoDB tables concurrently. I keep encountering lock wait timeouts and can't figure out why. Here is the situation:

Each client runs the following transactions repeatedly:

Start transaction
Insert one row into table A
for i = 1 to 22
  Do some time-consuming calculation (30 sec)
  Insert results into table B
end
Commit transaction

The lock wait timeouts happen when inserting into table A. Table B's primary key is the same as table A's augmented by another field. Table B has a foreign key into table A.

There are no SELECTs anywhere. No row is inserted twice. Transaction isolation level is REPEATABLE_READ.

SHOW ENGINE INNODB STATUS shows up to 36 row-level locks for some active transactions.

I do not understand why there are any locks. Can anyone give me some hints as to what's going on?


texts:
I have three tables.

user
user_preferences
user_subscription_plan

User could have no user_subscription_plans

I would like to make a query that just gives a count with users that do not have a single entry in the user_subscription_plan

I have come up with this query:

SELECT
    user.id,
    COUNT( DISTINCT usp.user_id) AS user_sub_plans
FROM user
RIGHT JOIN user_preferences AS up ON up.user_id = user.id
LEFT JOIN user_subscription_plan AS usp ON usp.user_id = user.id
AND user.inactive = 0
GROUP BY 1

It returns the result like this:
**Result of the query **

id   user_sub_plan
---  -----------
100  0
101  1
102  1
103  0
104  1
105  0

Which means user with the id of 100 has no entries, and user 101 has entries.

If we observe the result set of the query, it shows there are three users that have entries.
How could I expand this query, to return me user count of three?
Something like this:

user_count
    ---
3


texts: Data into Star Schema's fact or dimension table?

texts:
I'm working on creating a Star Schema for my internship that I want to use for reporting on the company's pre-sales process. The aspects of the pre-sales process that I want to report on, are:

Sales proposals;
Task management.

The thing that I'm having trouble with is whether or not to separate proposals and tasks from one another in my Star Schema. For this reason, I've now created two different ones and I wonder if any of them is the "correct" way of doing it.

Star Schema 1, in which the proposal data has been separated from the fact table and put in a dimension table:

Star Schema 2, in which the proposal data and task data have been combined in the fact table:

These are the metrics that I eventually want to be able to report:

% of tasks finished on time
Workload over time
Due and completed proposals
Number of active submissions
Potential total sales
Potential total gross profit
Potential average gross profit % per sales team
% of submissions converted to orders

Which of these schema's do you guys think is better? I've been trying to wrap my head around this for some while now, but I can't figure it out. Star Schema 1 has less redundancy, but does have values that I want to use in a dimensional table. Star Schema 2, on the other hand, has a lot of redundancy because proposals can have multiple tasks (sometimes up to 20).

Also, a more general question regarding my schema's:
- Do all relationships have to be visualised in the Star Schema Diagram? For example: all dates in every table must be linked to the date table. And: all references to employees must be linked to the employee table.

P.S.
The application and its database are still being developed so I have no data that I can use to test it.


texts:
Kev suggested this question ( https://stackoverflow.com/questions/10524981/php-script-to-show-mysql-health-status ) is better fitted here.

I know phpMyAdmin can shows the lists of MySQL vars and stuff like that.

But I would love to have a script that shows me if, for example, there is something wrong with MySQL configs or some graphic tools to see current MySQL health status.

Is there something like that? How can I know, for example, if current settings aren't good for my machine?

Let's say I have innodb buffer pool size set to 64MB while one of my INNODB tables is 1GB. That's not good at all. And that's just an example with innodb pool size. I think there could be many other settings to be aware of that a web based script could help to monitor.


texts:
Why do I get this weird error when trying to synchronise my model with the mysql database (v5.7.19, workbench v6.3.9)? As you can see from the screenshot, table1_createdOn doesn't even have a default value, and if i run the generated command with sql query it works:

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;

CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
  `createdOn` TIMESTAMP NOT NULL,
  PRIMARY KEY (`createdOn`))

CREATE TABLE IF NOT EXISTS `mydb`.`table2` (
  `createdAt` TIMESTAMP NOT NULL,
  `table1_createdOn` TIMESTAMP NOT NULL,
  PRIMARY KEY (`table1_createdOn`),
  CONSTRAINT `fk_round_table1`
    FOREIGN KEY (`table1_createdOn`)
    REFERENCES `mydb`.`table1` (`createdOn`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)


texts: Giving selective permissions in a database

texts:
I have a user [AD\dbusersgroup_limitedrights] that should have on two schema's
[ICD] and [XCD] within a database only SELECT and EXECUTE rights, however for each of the users in the [AD\dbusersgroup_limitedrights] I have created a schema with their corp initial

[DIS]
[SUB]
[TEC]

within these schemas they should have the rights to create their own tables, views and stored procedures, that are based on the [ICD] and [XCD] schemas objects.

I made the group now owner of the schema's [DIS],[SUB],[TEC] and on the other two schemas did grant them right for SELECT and EXECUTE. I also did make the group db_role db_datareader to make sure they don't get rights on other later created schema's.

However even though they are owner of their own schema they can't create objects within the schemas. I assume that this is due to the db role db_datareader.

So for such a scenario, what is best practice to give limited permissions within a database?


texts:
Is the "Buffer Manager: Page writes/sec" (* 8 KBytes) a good indicator of how much bandwidth will be used to replicate all databases via Availability Groups?

If not, why are these not equivalent? Is there just some overhead or are they largely unrelated?


texts: Best way to restore a MySQL 5.6 database from just its folder and file data?

texts:
I had a 5.6 MySQL Server instance that was hosted on a computer that suffered a very bad lightning related power outage.  For whatever reason, MySQL 5.6 Server does not want to start/stay running now.  However I still have access to all the files, Program Files\MySQL Server 5.6\Data and ProgramData\MySQL\Data,etc.
Could I hypothetically start a new instance of MySQL Server 5.6 on another computer and be able to bring over the entire database by copying the data files to the right place?  I just need to get it up and running again with the same database that was hosted on it before, I don't need to integrate it with a different database or anything, and I would like all the settings from the old system to be present in the new system.  It's InnoDB as well.


texts:
I know I can use the following t-SQL to shrink a log file:

BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn';
DBCC ShrinkFile([db1_log], 0);

How do I do the same with SMO? I tried:

$server = new-Object Microsoft.SqlServer.Management.Smo.Server()

$dbBackup = new-object Microsoft.SqlServer.Management.Smo.Backup
$dbBackup.Action = "Log"
$dbBackup.Database = "db1"
$dbBackup.Devices.AddDevice("\\server\share\db1_log1.trn", "File")
$dbBackup.SqlBackup($server)

$db = $srv.Databases.Item("db1")
$db.TruncateLog()

But here, I found that the TruncateLog() method does not work with anything higher than SQL 2005. That link recommended using:

$db.RecoveryModel = RecoveryModel.Simple
$db.Alter()
$db.LogFiles[0].Shrink(0, ShrinkMethod.TruncateOnly)

However, switching to Simple Recovery Mode is going to break my log chain and I don't want to do that. Shrinking the log file doesn't break my log chain so I'm looking for an equivalent SMO method to do that. Does such a method exist?


texts: Filtering down specific criteria for all rows and return only one row for each column with the same value

texts:
I have house_leases and house_lease_terms (see table schemas below). A house_lease can have multiple house_lease_terms however there can only be one "active" term at a time.

Table Definitions:

CREATE TABLE `house_leases` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `house_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `house_lease_terms` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `house_lease_id` int(10) unsigned NOT NULL,
  `date_start` datetime NOT NULL,
  `date_end` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `house_lease_terms_house_lease_id_foreign` (`house_lease_id`),
  CONSTRAINT `house_lease_terms_house_lease_id_foreign` FOREIGN KEY (`house_lease_id`) REFERENCES `house_leases` (`id`) ON DELETE CASCADE
);

As you can see the house_lease_terms.house_lease_id corresponds to a specific house_lease, however there can be multiple rows with the same house_lease_id.

The rules for determining the "active" terms are:

date_start <= NOW() AND date_end > NOW() OR date_end IS NULL

If no rows are returned then the "active" terms must be in the future, so then the rules change to be:

date_start > NOW()

We order by date_start DESC if the terms are not in the future since multiple rows could be returned we want the latest date_start at the top of the results. Otherwise we sort by date_start ASC because we want the closest date_start to now to be at the top.

I then limit by 1 to get only one result and that row is considered the "active" terms. If no results come back, then there are no "active" terms.

I have a SQL statement that has this logic for getting a specific house_lease_id. That looks like this:

SELECT * FROM house_lease_terms
WHERE
CASE
    WHEN
        (SELECT COUNT(*) FROM house_lease_terms WHERE date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL) AND house_lease_id = 1)
    THEN
        date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL)
    ELSE
        date_start > NOW()
END
AND house_lease_id = 1
ORDER BY
    IF(
        (SELECT COUNT(*) FROM house_lease_terms WHERE date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL) AND house_lease_id = 1),
        unix_timestamp(date_start),
        -unix_timestamp(date_start)
    ) DESC
LIMIT 1;

This statement works, but I wish there was a better way (more efficient) of fetching the "active" terms for a specific house_lease_id (If you know a better solution please share).

Now I need to have a query that will fetch the "active" terms for all the different house_lease_id's.

I don't want any type of custom MySQL function or stored procedure to do this. I don't know where to start to create this query. I figure I can use the query from above in some sub select or join, but am not sure how I would do so.

Any help will be appreciated!

SQLFiddle with data: http://sqlfiddle.com/#!9/cab159/2/0


texts:
Using MySQL, and related to this answer open in another thread (How to replicate MySQL table in different servers?), It arises me the idea of replicating only a View in different servers (through master-slave)

To create a replicate-view without replicating original tables in remote server, is it possible? Or Do I have to replicate the tables as obligatory condition?


texts: Migration from postgres 9.4 to postgres 9.6

texts:
I did the migration from PostgreSQL 9.4 to version 9.6, but now the stream replication is not working, it stopped replicating.
Script followed:

installation of postgres 9.6 on the three servers (master, slave1 and slave2).

running pg_upgrade on the three servers

The message that appears in the slaves server database log is as follows:

2017-07-13 19:01:46 -03 [25188]: [1-1] user =, db =, client =, appname
= FATAL: databa System identifier differs between the primary and standby
2017-07-13 19:01:46 -03 [25188]: [2-1] user =, db =, client =,
appname = DETAIL: The p Rimary's identifier is 6442320581826004577, the standby's identifier is 64423219
34563538204.

How do I resolve this problem?


texts:
Whenever I do searches on them, I keep seeing them in search results together.

What are the differences between Oracle UCM and Oracle Content Server?
Are they just two names for the same thing?
Is one a subset of the other?


texts: Speed up slow postgres query with indices (~5 minutes)

texts:
I have roughly 100 million rows in a table called 'bets' and a couple million in 'games'. The problem is that even with indices particular queries run very slow. The tables are structured as so:

CREATE TABLE users(
  id     bigserial  PRIMARY KEY,
  uname  text       NOT NULL
);

-- a couple million rows
CREATE TABLE games(
  id     bigserial  PRIMARY KEY,
  ended  boolean    NOT NULL DEFAULT FALSE
);

-- around a hundred million rows
CREATE TABLE bets(
  id       bigserial  PRIMARY KEY,
  game_id  bigint     NOT NULL REFERENCES games(id),
  user_id  bigint     NOT NULL REFERENCES users(id),
  wager    float      NOT NULL
);
CREATE INDEX bets_user_id_idx ON bets(user_id, id);
CREATE INDEX bets_game_id_idx ON bets(game_id);

This is the query that runs slow:

-- find the 50 most recent bets
SELECT bets.wager, g.ended
FROM bets
JOIN LATERAL (
    SELECT * FROM games
    WHERE id = bets.game_id
    ORDER BY id DESC
    LIMIT 50
) as g ON TRUE
WHERE user_id = 1
ORDER BY game_id DESC
LIMIT 50;

This is how everything looks like:

Problematic index scan when running explain:

  {
    "Node Type": "Index Scan",
    "Parent Relationship": "Outer",
    "Parallel Aware": false,
    "Scan Direction": "Backward",
    "Index Name": "bets_game_id_idx",
    "Relation Name": "bets",
    "Schema": "public",
    "Alias": "bets",
    "Startup Cost": 0.57,
    "Total Cost": 5077159.99,
    "Plan Rows": 12302,
    "Plan Width": 32,
    "Actual Startup Time": 5200.174,
    "Actual Total Time": 265417.040,
    "Actual Rows": 50,
    "Actual Loops": 1,
    "Output": ["bets.wager", "games.ended"],
    "Filter": "(bets.user_id = 1)",
    "Rows Removed by Filter": 106898896,
    "Shared Hit Blocks": 44257825,
    "Shared Read Blocks": 1566663,
    "Shared Dirtied Blocks": 1,
    "Shared Written Blocks": 117,
    "Local Hit Blocks": 0,
    "Local Read Blocks": 0,
    "Local Dirtied Blocks": 0,
    "Local Written Blocks": 0,
    "Temp Read Blocks": 0,
    "Temp Written Blocks": 0
  }


texts: Need a better approach in how to design this relation

texts:
I have a table fluxo, and fluxo have many fluxo_acao, but a entry in fluxo_acao can have three types, and each type have they particularities. to acomplish this, i created more three tables(fluxo_acao_mensagem, fluxo_acao_pergunta and fluxo_acao_escolher_fila), connected to fluxo_acao.
The problem is i can't select any of the three tables, from fluxo, because first i need to check wich type is and them i do the query.
I need a better approach in how to acomplish this relation or connect fluxo_acao to the three tables. Even why i need create another two more. and this is the perfect time to change.

someone just told me Inheritance may help me. i'm searching how.


texts: Purge mysql and it's configuration completely from MAC.

texts:
I, by mistake messed up with mysql's only user and the situation is I can't create new user or  give more privileges to existing one. Access denied is the error. Only visible db is info Schema So I decided to remove mysql from my MAC and install it again. For removing completely(including config data) I tried this post.

After installing again, I see my configuration is the same. And I have the same user who can't do anything and the only database visible to him is info schema. mysql database is not visible either.

How can I completely remove configuration including user privilege data of mysql from MAC ? Command like sudo rm -rf /var/db/receipts/com.mysql.* I am new on MAC, is there any command like we have for linux sudo apt-get remove --purge mysql-server mysql-client mysql-common.


texts:
After starting MySQL 5.5 I receive the error message:

Error Code: 1098. Can't generate a unique log-filename /data/mysql-binlog/relay-bin.(1-999)

Can anyone point me out what the problem is? The error pops up when I try to call CHANGE MASTER TO ..., the machine is set up to work as a production slave.

As you can see I have changed the replay-bin directory to another location due to disk space, but I am not sure if it relates to the problem.


texts: Is there a limit on query time for an Oracle linked server?

texts:
We've created a stored procedure to pull in full copies of some Oracle tables with a Linked Server.

Over the course of 11 hours, the procedure correctly populated the first 11 tables but failed with this error on table 12:

Msg 7399, Level 16, State 1, Procedure Refresh_From_Oracle, Line 45
The OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LIVE" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Procedure Refresh_From_Oracle, Line 45
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LIVE".

Is there anything there that gives a clue as to why the procedure failed when it got to table 12? Or is there a limit to how long a procedure can run, or a query can run against a linked server?

All tables except 12 and 13 have <2,000,000 rows, and 12 and 13 have about 70,000,000 each.

Here is the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Jack Douglas
-- Create date: 2016-10-07
-- Description: Truncates and re-populates UAT copies of Oracle data
-- =============================================
ALTER PROCEDURE [dbo].[Refresh_From_Oracle]
AS
BEGIN
    SET NOCOUNT ON;

    TRUNCATE TABLE dbo.TABLE1;
    INSERT INTO dbo.TABLE1 SELECT * FROM ORACLE_LIVE..ORALIVE.TABLE1;

    …

    TRUNCATE TABLE dbo.TABLE12;
    INSERT INTO dbo.TABLE12 SELECT * FROM ORACLE_LIVE..ORALIVE.TABLE12;

    TRUNCATE TABLE dbo.TABLE13;
    INSERT INTO dbo.TABLE13 SELECT * FROM ORACLE_LIVE..ORALIVE.TABLE13;

END


texts: What's the difference between a v$ view and its dba_ equivalent?

texts: What's the general difference between a v$ view and its dba_ equivalent? Take, for example, v$tablespace and dba_tablespaces.


texts:
I have an MS Access (no laughing at the back) database I've used to import a bunch of IIS logs into.

Having looked at the Excel files I pulled these in from, I'm worried going by the dates that some of the IIS files might have full duplicates (i.e. where every single field is identical).

I know how to select duplicates on an individual row, but how can I issue an SQL query that will show me rows where every field is identical, so I don't get skewed results?

If possible, some guidance on how to then have these rows deleted from the table without having to do a million delete queries with the individual PK IDs would be great too.

Example data:

In the above I'd be looking to identify rows 1 and 2 as they are exact duplicates, but 3 and 4 are okay as they share some but not all values. So the report would ideally come back with 1 and 2, so I could then note the IDs and delete all but 1 of the duplicates.


texts:
After SQL Server 2016 (SP1-CU3) patching we are not able to open the user database properties. Whenever its opened am getting below error:

  Method not found: System.collections.specialized.stringCollection
  Microsoft.sqlserver.management.smo.server.getpropertynames(System.type)'.
  (SqlManagerUI)


texts: How to ETL SQL Server resultset to mongo DB

texts:
Let's say I have multiple columns on sql server that I want to join and save the resultset into a single mongodb collection for faster lookup and calculations ( I'm talking about over 15 tables and more than a million resultset).
Is there any faster way to perform other than using a programming language to query the SQLserver db and then upsert into mongo?


texts: Querying Extended Events in SQL Server 2008

texts:
I have created the following event session using a script I got from Kimberly Tripp's SQL Server 2008 Internals book.  It has been modified to send to a file instead of the ring buffer.

CREATE EVENT SESSION
[statement_completed_file]
ON SERVER
ADD EVENT
    sqlserver.sp_statement_completed,
ADD EVENT
    sqlserver.sql_statement_completed
    (
        ACTION
        (
            sqlserver.sql_text
        )
        WHERE
        (
            sqlserver.session_id=53
        )
    )
ADD TARGET package0.asynchronous_file_target
    (
        set filename=N'C:\MSSQL\TEMP\stats.xel',
        metadatafile=N'C:\MSSQL\TEMP\stats.xem'
    )
WITH
(
    MAX_MEMORY=4096KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
)

Here is the original script to query the data.

SELECT
theNodes.event_data.value('(data/value)[1]', 'bigint') AS source_database_id,
theNodes.event_data.value('(data/value)[2]', 'bigint') AS object_id,
theNodes.event_data.value('(data/value)[3]', 'bigint') AS object_type,
theNodes.event_data.value('(data/value)[4]', 'bigint') AS cpu,
theNodes.event_data.value('(data/value)[5]', 'bigint') AS duration,
theNodes.event_data.value('(data/value)[6]', 'bigint') AS reads,
theNodes.event_data.value('(data/value)[7]', 'bigint') AS writes,
theNodes.event_data.value('(action/value)[1]', 'nvarchar(max)') AS sql_text
FROM
(
    SELECT
        CONVERT(XML, st.target_data) AS ring_buffer
        FROM sys.dm_xe_sessions s
        JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
        WHERE s.name = 'statement_completed'
) AS theData
CROSS APPLY
theData.ring_buffer.nodes('//RingBufferTarget/event') theNodes (event_data)
WHERE theNodes.event_data.value('(action/value)[1]', 'nvarchar(max)') is not null

How could I modify it to read from the two files xel and xem instead of the ring buffer.  I have been googling around but the learning curve has been rough.  Thank you in advance for your help.


texts:
From a MariaDB 10.1 database with the profile enabled.

After making some test with an example query, why the time reports from the query doesn't match the duration time that the profiles report?

I try to know why the same query is sometimes slow.

MariaDB [db1]> select id from sol_demo where id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

MariaDB [db1]> select id from sol_demo where id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.10 sec)

MariaDB [db1]> select id from sol_demo where id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.40 sec)

MariaDB [db1]> show profiles;
+----------+------------+----------------------------------------------+
| Query_ID | Duration   | Query                                        |
+----------+------------+----------------------------------------------+
|      145 | 0.00008608 | select id from sol_demo where id = 1 |
|      146 | 0.00009588 | select id from sol_demo where id = 1 |
|      147 | 0.00011883 | select id from sol_demo where id = 1 |
|      148 | 0.00008926 | select id from sol_demo where id = 1 |
|      149 | 0.00009260 | select id from sol_demo where id = 1 |
|      150 | 0.00008944 | select id from sol_demo where id = 1 |
|      151 | 0.00008301 | select id from sol_demo where id = 1 |
|      152 | 0.00008771 | select id from sol_demo where id = 1 |
|      153 | 0.00010690 | select id from sol_demo where id = 1 |
|      154 | 0.00009748 | select id from sol_demo where id = 1 |
|      155 | 0.00008521 | select id from sol_demo where id = 1 |
|      156 | 0.00009257 | select id from sol_demo where id = 1 |
|      157 | 0.00009136 | select id from sol_demo where id = 1 |
|      158 | 0.00056927 | select id from sol_demo where id = 1 |
|      159 | 0.00008753 | select id from sol_demo where id = 1 |
+----------+------------+----------------------------------------------+
15 rows in set (0.00 sec)


texts: How to use numeric operator between varchar value?

texts:
Here detail my table :

id | meta

01 | {"key1":"value1","data":"10"}

02 | {"key1":"valuex","data":"15","key3":"y"}

03 | {"data":"20","key3":"x"}

*id is primary key
*meta is field that store json data

At this table, each record having category and each category having difference data

The problem is, can we select record that only having data greater than 15 ?

That record with id 02 and 03


texts: Can’t connect to local PostgresSQL server

texts:
I have the Postres app installed an running on my Mac. And it has worked beautifully. I don’t know what changed, but now I cannot connect to it from Rails, PG Commander, PG Admin or the command psql -h localhost. However, I can connect with just psql. I get the following error in the log as soon as the connection times out:

LOG:  incomplete startup packet

Rails database.yml

development:
  adapter: postgresql
  database: my_db
  host: localhost

PG Commander settings:

pg_hba.conf:

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

postgresql.conf: https://gist.github.com/davbeck/49a23a48fa161b3e06fc#file-gistfile1-txt


texts: Is there any way to revoke drop permission from table in oracle as admin?

texts:
I tried this -

revoke drop on table emp from user1;

but it is not working, error is

Error report -
SQL Error: ORA-00990: missing or invalid privilege
00990. 00000 -  "missing or invalid privilege"

i looked up on oracle documentation and it had only 4 or 5 privileges like select, insert, delete, update but not drop.


texts: Effectively handle 10-100 millions row table of unrelated data

texts:
What are the common approaches to boost read/write performance of table with up to 100 millions of rows?

Table has columnSEGMENT_ID INT NOT NULL, where each segment has about 100.000-1.000.000 rows. Writes - all rows for SEGMENT_ID are inserted at once, no updates for SEGMENT_ID afterwards. Reads - are pretty often, I need good performance for SELECT * FROM table WERE SEGMENT_ID = ?.

The most obvious approach is creating new table for SEGMENT_ID dynamically, but dynamic tables means hacks with ORM or even native SQL query-framework. In other words you finish with code that smells.

You can also use sharding, right? Does database create new tables under the hood?

I can cluster the table by SEGMENT_ID. But will my inserts be clustered if I insert all segment-related data at once?

Also Postgres propose to use partitioning to handle very big tables.

Maybe there is some kind of magical index which will help me to avoid creating new tables dynamically or configuring sharding?

Any other options?


texts: Operator "~<~" uses varchar_pattern_ops index while normal ORDER BY clause doesn't?

texts:
Let's say I have a table with  1 000 000  records.
Structure of table is:
create table individual (
  id serial primary key,
  surname varchar(128),
  "name" varchar(128),
  patronymic varchar(128),
  birth_dt date
)

I create a composite index.
INDEX 1
create index on individual using btree (upper(surname) varchar_pattern_ops
  , upper("name") varchar_pattern_ops, patronymic varchar_pattern_ops, birth_dt);

Docs state that varchar_pattern_ops should be applied when using LIKE or pattern match in query. Conclusion: this index will not be used in query below, even it gets only 10 row from 1 000 000.
QUERY 1
select * from individual
order by upper(surname), upper("name"), upper(patronymic), birth_dt limit 10;

and even more, docs recommends to create an index without varchar_pattern_ops as well.
INDEX 2
create index on individual using btree
(upper(surname), upper("name"), upper(patronimyc), birth_dt);

Then a query using LIMIT will use this index.
I found a cheat to force Postgres to use first index on Postgres users forum. It is operator ~<~.
QUERY 2
select * from individual
order by upper(surname) using ~<~
       , upper("name") using ~<~
       , upper(patronymic) using ~<~
       , birth_dt limit 100;

In this case INDEX 1 will be used even if INDEX 2 doesn't exists. I tried to investigate to discover why it happens, but failed.
There are some system tables like pg_operator, which (I think) link operator ~<~ to some functions that most probably uses LIKE or regular expressions.
I ran QUERY 1 and QUERY 2 a few times and compared result manually. It looks like operator ~<~ gives correct result, but I didn't risk anything and just create a normal index anyway.
I am still interested how the Postgres planner decides which index to use index where it meets the operator ~<~ in QUERY 1.


texts: Time-series window query for calculating rolling average across group by subquery is inaccurate due to missing rows

texts:
I'm trying to generate a rolling average based on a generated time series joined with a subquery on each day of the time series. However, the subquery occasionally does not have a result for a given day, and hence it does not return a row for said day. This breaks the rolling average calculation since it's based on preceding/following rows. It still calculates, but the calculation is wrong since it's not counting "0" days since those rows aren't present.
SELECT
    d::date AS day,
    updates.login as login,
    avg(coalesce(updates.counts, 0)) over (order by date(d) rows between 3 preceding and 3 following) as rollingavg
from generate_series(date '${from}' - 3, date '${to}' + 3, interval '1' DAY) AS t(d)
LEFT JOIN (
    SELECT
        date(item.updated_at) AS day,
        u.login,
        count(date(item.updated_at)) AS counts
    FROM
        user_item as item
        inner join user u ON u.id = item.user_id
    WHERE
        item.updated_at >= (date '${from}' - 3)
        item.updated_at <= (date '${to}' + 3)
    GROUP by day, login
) updates ON updates.day = t.d

If I remove the login from the group by clause and modify the query to fit, it appears to work, but that's only because there is enough data that no day goes without at least one update. In the off chance there were a day without an update, this would experience the same problem.
My initial thought was to join a generated table that is essentially a cross join between the time series and every possible author, or possibly modify my from generate_series to represent such a table that includes a baseline '0' count for every user on every day, but something about that feels wrong.
Am I on the right path?
EDIT: Some simplified sample data to make it a bit more obvious what I'm getting at. This is focused purely on the subquery since that is where the problem begins:
User:

id
login

1
userA

2
userB

UserItem:

updated_at
user_id

'2020-01-01'
1

'2020-01-01'
2

'2020-01-02'
1

'2020-01-03'
2

The desired result is as follows:

date
user
count

'2020-01-01
userA
1

'2020-01-01
userB
1

'2020-01-02
userA
1

'2020-01-02
userB
0

'2020-01-03
userA
0

'2020-01-03
userB
1

But what I'm getting:

date
user
count

'2020-01-01
userA
1

'2020-01-01
userB
1

'2020-01-02
userA
1

'2020-01-03
userB
1


texts: Save dataset within another table best practice

texts:
I want to save one dataset within another table, this dataset should be persistent.

For example

Table1: FIELDS (A,B,C)
Table2: FIELDS (D, DuplicateTable1)

I can think of 2 possiblities:

Have Fields A,B,C in Table2
Make an invisible copy (for example by adding a "hidden" Field in table1) and us it in Table2 as Foreign Key.

I feel both are not very clean. How would you solve this problem ?

Edit: More Specific example:

CREATE TABLE `Bill` (
    `billId` INT(11) NOT NULL AUTO_INCREMENT,
    `to` INT(11) NOT NULL,
    `value` INT(11) NOT NULL,
    PRIMARY KEY (`billId`),
    INDEX `PersonKey` (`to`),
    CONSTRAINT `PersonKey` FOREIGN KEY (`to`) REFERENCES `Person` (`personId`)
)
ENGINE=InnoDB;

CREATE TABLE `Person` (
    `personId` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    `address` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`personId`)
)
ENGINE=InnoDB;

In this example the problem is that the bill shouldn't change when the person changes name or address. And I'm looking for a nice way to fix this. I could add all my fields in Person to my Bill table (for example Bill.person_address). But if I add a new field to Person i would also have to change the structure of Table (wich makes the software less scaleable). Or the other way i could think off, is adding a new field "locked" to Person like

`locked` TINYINT(1) NULL

And when i save a bill I copy the row from Person with locked = 1, then i would change the application that all the locked persons don't show up anywhere else.


texts: MYSQL - Using simple inventory number convention as Primary Key

texts:
I have an inventory with three numbered groups like so:

Group 1: 0-999
Group 2: e0-e999
Group 3: v0-v999

Can I simply make a one-column table to store this information and make that column the primary index (needed?), or should I make it two columns.

example:

code (primary)
000
016
760
433
e221
e011
v999
v003

vs.

(primary)
 code_id    code
    1        000
    2        016
    3        760
    4        433
    5        e221
    6        e011
    7        v999
    8        v003


texts: Issue with Maximo KPI Returning "0" Instead of a Calculated %

texts:
I am attempting create a KPI in Maximo that will count the work orders completed on time and divide it by the number of work orders due for the current month, then multiply it by 100 to return a percentage. And I'm failing. The queries will return the correct values independently, but do not seem to work in this situation. Please help.

select distinct (select count(wonum)
                   from workorder
                  where((status = 'CLOSE' or status = 'COMP')
                    and (woclass = 'WORKORDER' or woclass = 'ACTIVITY')
                    and upper(crewid) = '17D02'
                    and istask = 0
                    and siteid = 'INDPLS'
                    and worktype = 'PM')
                    and (targcompdate >= dateadd(month,datediff(month,1,getdate())+0,0))
                    and (targcompdate <= dateadd(month,datediff(month,1,getdate())+1,0)-.001)
                    AND ACTFINISH < TARGCOMPDATE) /
                (select count(wonum)
                   from workorder
                  where((woclass = 'WORKORDER' or woclass = 'ACTIVITY')
                    and upper(crewid) = '17D02'
                    and istask = 0
                    and siteid = 'INDPLS'
                    and worktype = 'PM')
                    and (targcompdate >= dateadd(month,datediff(month,1,getdate())+0,0))
                    and (targcompdate <= dateadd(month,datediff(month,1,getdate())+1,0)-.001))) * 100
  from dummy_table