texts: Cannot write to Galera Cluster (Lock wait timeout)

texts:
My Galera cluster has gone down / locked up, I can read from all databases, but I cannot write to any table in any database, it just locks / waits forever.

I can fix it by shutting down all 3 mariadb servers and start a new cluster, but I want to know what's going on for obvious reasons. Is there any way to figure out what's happening? It happened 5 days ago, and now again.

Edit:
Extra info, it gave me this error after I set a connect timeout:
ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction

Edit 2: show processlist gave me this at the top:

|       1 | system user      |            | NULL              | Sleep   | 434414 | WSREP aborter idle       | NULL                                                                                |    0.000 |
|       2 | system user      |            | NULL              | Sleep   |  44098 | Waiting for table flush  | INSERT INTO `cron_log` SET
                                `start` = '1566429001',
                                `end` = '1566429004',

is there something I can do from a user perspective or is this an internal mariadb error?

I cannot seem to kill process 2, logged in as root is says: You are not owner of thread 2

Edit 3:

  Are you writing to all 3 nodes, or do you have a dedicated write node?

I WAS writing to all three nodes but kind of got the feeling it's better to write to just one, which is also the goal of MaxScale by MariaDB. However, the goal of a galera cluster is that you can write to all, but apparently in practice it's not a great idea, which is kinda awkward if you ask me but OK.
Also, I had a OPTIMIZE statement in my application and got to understand that could cause "Waiting for FLUSH" issues, so I worked around that statement so it's no longer needed.

  "shutting down all 3 mariadb servers and start a new cluster" -- Surely there is a less drastic solution! – Rick James

Well I tried quite some things, but shutting everything down and bootstrapping it again was the only thing that worked.


texts: Improving performance of mapping standardized values with non equi joins - LIKE operator

texts:
I have a large-ish data warehouse staging table (~2.5 million rows) that is using a mapping table to standardize a text column. I have an update statement that works perfectly, but has terrible performance, and would like some recommendations on modifications to the statement and/or alternate strategies for standardizing the data.

The source text can contain anything, and I'm basically looking for keywords in the strings. Because of the nature of the data, a longer string is more specific and therefore correct, so I want to get the longest mapping value found for each given row in the staging table.

Source data (simplified):

CREATE TABLE STAGINGTABLE (TextColumn VARCHAR(100));

INSERT INTO STAGINGTABLE VALUES ('This entry has a full foo bar entry');
INSERT INTO STAGINGTABLE VALUES ('Today was a foo day');
INSERT INTO STAGINGTABLE VALUES ('Only found bar data');
INSERT INTO STAGINGTABLE VALUES ('Blah blah blah');

CREATE TABLE MAPPING (SourceMap VARCHAR(100), TargetMap VARCHAR(100));

INSERT INTO MAPPING VALUES ('%FOO BAR%', 'Foo Bar');
INSERT INTO MAPPING VALUES ('%FOO%', 'Foo');
INSERT INTO MAPPING VALUES ('%BAR%', 'Bar');
INSERT INTO MAPPING VALUES ('%%', 'Unknown');

Update statement:

UPDATE STAGINGTABLE
SET TextColumn = (
    SELECT TargetMap
    FROM (
        SELECT TargetMap
        , ROW_NUMBER() OVER (PARTITION BY TextColumn
            ORDER BY LEN(SourceMap) DESC) AS ROWNUM
        FROM STAGINGTABLE
        INNER JOIN MAPPING
            ON UPPER(TextColumn) LIKE SourceMap
        WHERE TextColumn IS NOT NULL
    ) AS STG_MAP
    WHERE ROWNUM = 1
);

Final values in staging table after mapping:

Foo Bar
Foo
Bar
Unknown

Edit: the TextColumn and SourceMap columns do have non-clustered indexes.


texts: Will altering an nvarchar(max) column to nvarchar(50) lock the table?

texts:
I have a table in SQL Server (Azure) with 1.4 million rows.

I'd like to index one of the columns; however, its datatype is nvarchar(max) (defaulted to such by using EF I imagine). I'm using Entity Framework and can make a migration that will alter this datatype to nvarchar(50).

My concern is how long this will take, and whether it will cause issues with locking.  I've only ever expanded columns, so I'm not sure what to expect.  I do know the data in this column will fit fine in the new size.

I was planning to create a copy of the database and try it out. I have also considered migrating to a new column, but would much prefer to update the current column if it's not overly painful.


texts: Compare data between 2 rows of same table

texts:
I have a table that looks like this:

date1                   date2
2017-03-06 17:02:00     null
2017-03-06 17:02:00     2017-02-17 11:20:00
2017-03-01 17:07:00     2017-03-13 17:07:00
2017-03-17 12:05:00     null
2017-01-24 11:45:00     null

Which join should I use to output 1 column of data that only shows the date1 in the result and if there is a value in date2 column, it will show the date2 value instead of the date1 value?


texts: Cannot Delete FileStream Group from Sql Server 2008

texts:
We have migrated all our varbinary(max) column data to Azure Blob Storage and so we want to remove the old filestream columns that remain in our Sql 2008 database and the filestream filegroup but when we try to we are getting the error:

Msg 5042, Level 16, State 11, Line 2
The filegroup 'FileStreamGroup' cannot be removed because it is not empty.

However when we run this:

exec sp_helpfilegroup 'FileStreamGroup'

It is returning this:

groupname           groupid    filecount
FileStreamGroup     2          0

So the file count is 0 but it won't let us remove it, has anybody else had this problem and how on earth do you completely remove the filestream from the database.

In addition this query:

select * from sys.tables t
join sys.data_spaces ds on t.filestream_data_space_id = ds.data_space_id

Returns 0 rows so no tables are using any filestream data if I understand this correctly.


texts: SQL Server 2008 R2 - Specify the Full Backup to use when doing a Differential Backup

texts:
When I backup my Database, I do a Full Backup every day who goes on a tape.
I am trying to use the Sunday's Full backup to set up an additionnal Differential Backup, how can I do this? It only takes the last Full Backup.

Full (Run on Sunday) :

BACKUP DATABASE [DATABASE] TO  DISK = N'J:\DATABASE Backup\MyDatabase_Full.bak' WITH NOFORMAT, NOINIT,  NAME = N'DATABASE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Partial (Run each Day, but use the wrong Full Backup) :

BACKUP DATABASE [DATABASE] TO  DISK = N'J:\DATABASE Backup\MyDatabase_Partial_20120105.bak' WITH  DIFFERENTIAL ,  RETAINDAYS = 10, NOFORMAT, NOINIT,  NAME = N'DATABASE-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10


texts: How to store the result of multiple select queries into one csv file?

texts:
How to store the result of all of the queries below into one CSV file in PostgreSQL by executing one COPY (......) to.... statement.

select id,name from first;
select id,salary,father_name from second;
select id,score,bonus,extra from third;


texts: How to rank an aggregation in each newly formed group?

texts:
The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL.

Let's say I have a simplified ERD that looks as follows:

This is a database of a hypothetical movie streaming website that keeps the data about its customers, invoice, movies and their genres. Each customer has a unique CustomerID, each purchase has a unique InvoiceID, each movie has a unique MovieID, each genre has a unique GenreID. One MovieID is only associated with one GenreID. One customer can purchase many movies, so one Invoice can contains many MovieIDs.

I would like to show the top 2 most popular genres (in terms of total count of InvoiceID) in each of the following continents/regions:
Africa (Egypt, South Africa), Asia (China, Japan), Europe (France, Germany).

An example of the Customer and Genre tables are as follows:

The resulting table that I would like to have looks more or less like this (Region means Continent):

How exactly the resulting table look like is not important, I would just want the top 2 genres grouped by each region.

Here are some of my effort in producing the query:

WITH t1 AS (
  SELECT c.Country,
  CASE
    WHEN (
      (c.Country = "China" OR c.Country = "Japan") THEN "Asia"
    )
    WHEN (
      (c.Country = "Egypt" OR c.Country = "South Africa") THEN "Africa"
    )
    ELSE "Europe"
  END AS region
FROM Customer c
GROUP BY 1
)

SELECT DISTINCT region
FROM t1;

I could only produce the new region groups but got stuck at how to rank the genres grouped by the regions. I have a difficulty in showing the ranking of genres (based on total count of InvoiceID) in each region. In other words, in each of the regions, how many customers purchased each genre. I have just started learning SQL that includes different kinds of JOINS, SUBQUERY, AGGREGATIONS, GROUP BY, ORDER BY and Common Table Expression but somehow I cannot produce the result I want. Could somebody please give some light on this? Thank you in advance for the help.


texts: How to see a table in Operation Builder part of DynamoDB NoSQL Workbench?

texts: I'm new on DynamoDB and using NoSQL Workbench to learn. There is a new "Example" table in "Data modeler" section. I made a new LOCAL connection under "Operation builder" section to work on/test the "Example" table. Connection was created successfully and there was no error. But I can't see any table under Connection part. It says "No table  found". I can't understand the problem. I think, I should see the "Example" table that is visible under "Data modeler" section. How can I see the table in Connection section?


texts: What is the optimal pipeline for updating table on FK value in Postgresql?

texts:
Hi I have the following schema in my app.

-- Table: Investment_round
CREATE TABLE Investment_round (
    id serial  NOT NULL,
    round_type varchar(50)  NOT NULL,
    money bigint  NOT NULL,
    Project_id int  NOT NULL,
    CONSTRAINT Investment_round_pk PRIMARY KEY (id)
);

-- Table: Investor
CREATE TABLE Investor (
    id serial  NOT NULL,
    name int  NOT NULL,
    url int  NULL,
    Investment_round_id int  NOT NULL,
    CONSTRAINT Investor_pk PRIMARY KEY (id)
);

-- Reference: Investment_round_startups_page (table: Investment_round)
ALTER TABLE Investment_round ADD CONSTRAINT Investment_round_startups_page
    FOREIGN KEY (Project_id)
    REFERENCES startups_page (angel_id)
    NOT DEFERRABLE
    INITIALLY IMMEDIATE
;

-- Reference: Investor_Investment_round (table: Investor)
ALTER TABLE Investor ADD CONSTRAINT Investor_Investment_round
    FOREIGN KEY (Investment_round_id)
    REFERENCES Investment_round (id)
    NOT DEFERRABLE
    INITIALLY IMMEDIATE
;

I'm a bit confused about optimal pipeline of inserting the data. Suppose that firstly I need to insert the data to Investment_round table.
Then I need to insert the data to Investor table with corresponding Investment_round_id

It seems that the reasonable way is to store Investment_round_id and then to update Investor table. But how can I achieve that without making an external request?


texts: After Oracle 11gR2 restore & recover database, "ghost" NOLOGGING corrupt blocks

texts:
I recently restored (from full, level 0 cold backup) an ARCHIVELOG database & recovered it to a point in time about 24 hours after backup. I ran RMAN validate and numerous blocks turned up in V$DATABASE_BLOCK_CORRUPTION with corruption of NOLOGGING.  This was anticipated as database has frequent NOLOGGING load operations.  The game-plan was to map V$DATABASE_BLOCK_CORRUPTION blocks to segments (table partitions), truncate partition, and replay the loads of those data sets from source files.

In most cases, this worked perfectly; most entries in V$DATABASE_BLOCK_CORRUPTION mapped directly to NOLOGGING table partitions which would've been loaded via INSERT /*+APPEND*/ after the cold backup.

Only wrench: V$DATABASE_BLOCK_CORRUPTION shows about 100 blocks with NOLOGGING corruption that do not match any entries in DBA_EXTENTS (they are, presumably, unused blocks).

1) Any idea what caused these blocks to become corrupt?
2) As they do not map to any extents in use, can I safely ignore them?
3) The OCD question: if these blocks aren't problematic, is there a way to clear them from V$DATABASE_BLOCK_CORRUPTION?


texts: Running loadjava with Windows authentication

texts:
I want to run following command under Windows with Oracle Database 12c installed:

loadjava -force -genmissing -r -user username/password@database -verbose core-1.7.jar

The problem is that I use Windows authentication and I connect the following way:

sqlplus / as sysdba

and I do not enter any username or password. Could you please tell me how to execute first command under Windows for a pluggable database?


texts:
Lets say i am designing a database that tracks maintenance of equipment.

Each piece of equipment has various fields that usually don't change eg. ID, model, serial number etc.

Some fields change frequently - during maintenance eg, status, location, firmware, configuration options etc. And these fields should keep a history.

Is it better to have one table with all these frequently change fields? eg tracking table, each change to any one field will create a new record, copying the field values from the previous (with updates) and the current date time.

OR better to keep separate table for each field? eg status table, location table, each table will have own date time, edited by user etc fields

One benefit i see in the first method is easier to look up the date and find the state of all fields at that time. The drawback perhaps is use up more space and some field may change more frequently than others.


texts:
In MySQL, is it better to always allow nulls unless you know a field is required, or always use Not Null unless you know a field will contain nulls?  Or doesn't it matter?

I know in some DBMSs they say to use Not Null as much as possible because allowing nulls requires an extra bit (or byte?) per record to store the Null status.


texts: What does a round relationship icon mean in MySQL Workbench Visual Data Model

texts:
I have created a model in the Visual Data Model of MySQL Workbench, and on one single relationship, this icon shows up:

What does it mean? The Relation is 1:1, and this icon shows only on this particular relationship, none other. I already checked and the relationship is exactly the same as all the other.


texts: 'Connection not found' error thrown at end of task, after the package has successfully used the connection it refers to as missing

texts:
Sorry for the long title and this long question!

I'll set the scene. I'm fairly new to VS and SSIS.

I have created several SSIS packages, basically a package for retrieving data and creating a csv file, getCSV.dtsx, and another to Load said csv file into our Oracle tables, loadData.dtsx.

Now that they are working independently, I have started to put them together in one 'main' package getandLoad_AllTables.dtsx consisting of a sequence container for each table, 2 packages per container; getData/loadData using 'Execute Package Tasks' to call these packages from the local file system in the order I have laid out in my control flow.

The issue - both the tasks are called, run and completed within the first container however the getandLoad_AllTables package errors out, detailing that

Error: 0xC001000E at loadData: The connection "{long-ass string}" is not found"

I checked the ID of the connection in the XML of loadData.dtsx and found it is the main connection used within all the 'Execute SQL Tasks' in the loadData package, all of which ran successfully and loaded the data into the tables as expected.

Why is this error thrown in the getandLoad_AllTables package and not in the loadData package it says the connection is missing from? I am completely stumped.

Thank you in advance for any help/guidance/advice anyone can give.


texts: What things SQL Server takes backup of when we run a full backup

texts:
Like I am trying to ask that, suppose we run a full backup using GUI or T-SQL, which file SQL Server takes backup of i.e, only data file or log file as well.

I have read that it takes active transaction portion (Log File) of SQL Server as well. please confirm.

If yes, then what will happen to a transaction that was running at that time, and committed next, logs of the transaction will there on backup or not.

how will sql server revert back to it, if the database fails.


texts: Most efficient way how to generate a serial which is unique per customerid in the same table?

texts:
I have a log file, which holds a customerid, and an event type. I want to add a new column called sequence, which should be a increasing number, and should be unique per customer.
The table is append-only, record-by-record, no transactions, and I'd like the sequence number to be computed by a default function on the column. What is the least CPU intensive way to accomplish this?
I tried googling but there are just too many hits on 'auto-increment', 'sequence', and 'serial' for the simple cases.


texts: ODBC connects but authentication fails on users table

texts:
We have sql 2008 running in mixed mode. The site where sql is running the ODBC connection works and ms access authenticates against the user table. We have an IPSec vpn to a satellite office using Cisco routers on a 100mb leased line. ODBC connector connects when setting up the connector (& my udl) but ms access authentication fails. Nothing has changed on the server or ms access and it been running fine for the past 6 years.

I have disabled the firewall on win 2016 server
Tested the connection using a udl file


texts:
I understand that Temporary Tables in Postgres and MS SQL Server persist until the end of the session. When the user’s session ends, any temp tables created by that user are dropped automatically. The temp table is not visible between users.

I need both the visibility across user sessions and the temporary nature where the data disappears when the user session ends (either closes gracefully or breaks).

Is there any way to have a permanent table visible to all but temporary rows? Is there any automatic way to have a user’s inserted rows disappear at the end of their session, yet be visible to other users during that session?


texts: Any Tricks to Better Identify Disabled SSIS Elements?

texts:
Does anyone have any tricks they care to share that will help better identify disabled elements within SSIS packages?  Ideally there's some manner to adjust the text color to red or or something along those lines, but the default behavior seems significantly lacking in my mind.  I've dug through a number of options, but nothing seems to impact the display of only the disabled elements.

Here's an example image where 4 of the 6 elements are disabled:

As much as I'd love to fall back to consistent SSIS development practices, these packages are mainly inherited, so I'm stuck with a mixed bag of approaches to work with.

This screenshot was taken from within Visual Studio 2012, but I believe the issue exists on a number of other versions as well.


texts:
When I come to work on monday some stored procedures take several minutes to execute and STATISTICS IO shows a lot of PHYSICAL READS and READ AHEADS going on.
However, after running the procedures 10-20 times with different parameters, the execution time drops to less than one second.

I think that for some reason the data buffer expires over the weekend, and it takes a lot of time to reload the buffer on monday.

How do I make sure my application works fast even if it's not used regulary. Is there a way to persist the buffer, or should I rewrite the procedures in a way that doesn't rely on the buffer?


texts: How to make an indexed search over a list (set)?

texts:
A follow-up question for How to most efficiently store possible combinations of categories?

An answer pointed to SET datatype. As far as I know, the default search option for it, FIND_IN_SET, doesn't support indices. The other issues (redundancy due to violating 1NF) are negligible for me, but I will need to use the data in a search quite often, so I can't use SET unless I can optimize the query somehow, probably by some index. Is it possible?

I'm using MySQL 5.5 (InnoDB). In 5.6+ there is something called fulltext index - I'm not sure it's useful in this case, but even if it does, it's not applicable in my case.


texts:
i am getting a Syntax error in CREATE TABLE statement.  Microsoft Access 2007.

CREATE TABLE ROOMS
(
RoomNum Int Not Null,
RoomSize Char(6) Not Null,
RoomBedCnt Int Not Null,
RoomRate Numeric(18,2) Not Null,
CONSTRAINT ROOM_PK PRIMARY KEY (RoomNum)
);


texts: Is it possible to see blocker's host name or machine name using sp_WhoIsActive?

texts:
I am using sp_WhoIsActive.

When blocking is occurred on a server I want to quickly identify who is the culprit.

Column login_name does not give me much info about blocker because there are hundreds people using company's app and logged in under same login_name.

I am able to see blocking_session_id but this does not tell me what is the blocker's host_name.

There is also a column host_name but this is the one who is a waiter (not blocker)

So is any way I can quickly find the host_name, machine name or any other identity of the user who is the blocker?


texts: Removing a Key From HSTORE in Postgresql

texts:
At the moment I have an hstore that describes the properties of various items. When searching I need to remove values that are unique to a each item (such as serial number, vin, and so on). According to the postgresql docs I should be able to remove a key from an hstore with the following operator

As per the postgres docs
hstore - text  delete key from left operand    'a=>1, b=>2, c=>3'::hstore - 'b'::text

In my code I have

IF item.details ? 'vin' THEN
            item.details::hstore - 'vin'::TEXT;
        END IF;

When I go to create the function I get the syntax error

'syntax error at or near "::"'

When I remove the type declarations from the end of the values I get the error

'syntax error at or near "-"'

As far as I can see this conforms to how the postgres docs say the operator should work at http://www.postgresql.org/docs/9.3/static/hstore.html . Is there something I am missing?


texts: What is this query, involving system views & index properties

texts:
https://www.brentozar.com/pastetheplan/?id=Bkg8vBVzb

What in the heck is this query from?  I thought it may have been sp_BlitzIndex or sp_BlitzCache but I searched thru them and didn't see anything like it (looked for keywords like IgnoreDuplicateKeys or spatial_indexes).  When I searched for is_hypothetical I got a hit on both sp_BlitzIndex and Ola Hallengren's IndexOptimize, but further eyeballing tells me those aren't the culprits because they're missing so many other pieces that this rogue plan has (like the spatial and xml stuff).

I noticed it because it's executed over 20k times per day and shows a lot of wait time in my monitoring tool, but then again it typically only shows waits between 1am and 7am, which is kinda around our maintenance window... I'm just really confused at this point, though, because I'm using Ola's solution for indexing and the occasional ad-hoc alter index all on some_table rebuild.

Any ideas?


texts: How can I move a MySQL database from 5.1 to 5.6?

texts: I have pretty big database, like with 5 000 000 rows in total. It is located in a remote server. I want to copy this database to my personal computer, for testing purposes. However I don't know the best option for this operation. Dumping the database does not look user friendly. Can I use Navicat's data transfer function to do this? Could it end with error? I think this operation can last few hours. Is there any other ways?


texts: Calculate and show DATETIME difference on PIVOTING query

texts:
I have the following PIVOTING query:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW

SELECT *
FROM (select  jsc.applicationid
              ,[the date of change]=MIN(jsc.changedate)
              --,jsc.[oldStatus]
              ,jsc.[newStatus]
              ,C1.countryName
              ,[Name] = ja.firstname + COALESCE(' ' + JA.LASTNAME, '')
              ,nationality=C2.countryName
              ,[Agency Name] = a.name
      from dbo.applicationStateChange jsc
      INNER JOIN dbo.application app
            ON app.applicationID = jsc.applicationID
      INNER JOIN dbo.applicant ja
            ON ja.applicantID= app.applicantID
      INNER JOIN dbo.agency a
            ON a.agencyID = app.agencyID
      INNER JOIN dbo.country C1
            ON C1.countryCode = ja.countrycode
      INNER JOIN dbo.country C2
            ON C2.countryCode = ja.nationalitycode
      WHERE 1=1
      --  AND jsc.[oldStatus] = 'OLA'
      AND jsc.[newStatus] IN ('OLA', 'AWA', 'TBM')
      --AND ja.countrycode = 'GB'
      AND YEAR(jsc.changedate) >= 2010
      GROUP BY jsc.applicationid
               --,jsc.changedate
               --,jsc.[oldStatus]
               ,jsc.[newStatus]
               ,C1.countryName
               ,ja.firstname
               ,JA.LASTNAME
               ,C2.countryName
               ,a.name
) RADHE
PIVOT (MIN([the date of change]) for [newStatus] IN ([OLA], [AWA], [TBM]) ) as KRISHNA
WHERE 1=1
AND [OLA] IS NOT NULL
AND [AWA] IS NOT NULL
AND [TBM] IS NOT NULL
ORDER BY  applicationid

That gives me results like these:

Question:

How could I calculate X and Y on the above picture and display the result there?

X - should be the amount of time (ok it can be number of days to keep it simple) between AWA and OLA.

Y - should be the amount of time (ok it can be number of days to keep it simple) between TBM and AWA.


texts: It is mandatory to create a route when creating an event notification?

texts:
I'm starting with service broker. Already managed to setup an event notification for blocked processes based on this answer and it's working flawlessly. Then I wanted to setup another one to notify for deadlocks and I see a CREATE ROUTE statement:

-- create the route for the service
CREATE ROUTE DeadLockNotificationsRoute
    WITH SERVICE_NAME = 'DeadLockNotificationsService',
    ADDRESS = 'LOCAL';
GO

But on previous one I did not ran this step, it was not included and it works. Looking on several places here, here, here, here and some other places I see the create route step is not always included. For dynamic routing seems mandatory of course. Reading on MSDN-Create route is not clear for me if it is mandatory or not and when it should be included.


texts: MySQL/MariaDB: Scaling 10 millions database in cluster

texts:
This is for SaaS app (currently in MySQL-InnoDB). Each app contains about 150 tables. The database size is not big, but the number is high.

For each database, InnoDB create directory in /var/lib/mysql. Because too many database, I choose to have individual database per app to prevent file system bottleneck (Performance degrading badly if too many files/directories exist under a directory).

I will upgrade once it reach 5000 databases.

I need to know if MariaDB cluster or MySQL cluster able to deal with 10 millions database or more, or is there any other solution?


texts:
Consider the following setup, which you can tinker with at dbfiddle.uk:
In the first batch we setup three simple tables, and add a row to each table:
CREATE TABLE t1 (x int NOT NULL);
CREATE TABLE t2 (y int NOT NULL);
CREATE TABLE t3 (z int NOT NULL);

INSERT INTO t1 (x) VALUES (1);
INSERT INTO t2 (y) VALUES (2);
INSERT INTO t3 (z) VALUES (3);

Next, we'll create three stored procedures:
DELIMITER //

CREATE PROCEDURE proc1()
BEGIN
    UPDATE t1 SET x = x + 1;
END
//

CREATE PROCEDURE proc2()
BEGIN
    UPDATE t2 SET y = y - 1;
END
//

CREATE PROCEDURE proc3()
BEGIN
    UPDATE t3 SET z = z / 0;
END
///

Next we'll start a transaction, run the three procs, then rollback the transaction.  Note we're disabling autocommit explicitly even though that is not strictly required since we have an explicit transaction:
SET autocommit = 0;
START TRANSACTION
CALL proc1();
CALL proc2();
CALL proc3();
ROLLBACK

Now, if we inspect the content of the three tables, we note that the rollback appears to have not taken place.  In fact, the rows are exactly as if there was no transaction, i.e. it looks like autocommit was on.
SELECT *
FROM t1;

SELECT *
FROM t2;

SELECT *
FROM t3;

The results:
╔═══╗
║ x ║
╠═══╣
║ 2 ║
╚═══╝
╔═══╗
║ y ║
╠═══╣
║ 1 ║
╚═══╝
╔═══╗
║ z ║
╠═══╣
║ 3 ║
╚═══╝
What is happening here, and how can I rollback the actions of the first two procs when the third clearly fails?
FYI, this has been tested on MySQL 5.7.2, and via DBFiddle.uk on MySQL 8.0.22.


texts: Shrink my database log file, make sense?

texts:
I am now taking log backups daily after a long gap(1.5 years). Now when I run,

DBCC SQLPERF('logspace')

Result,

Database  Log Size (MB)   Log Space Used (%)    Status
master     1.742188     64.09753               0
tempdb     51.17969     33.19341               0
model      0.9921875        48.4252            0
msdb       4.554688     55.6711            0
MyDb       15618.68     0.7029594              0

Should I shrink the log file. If yes then how?


texts: Migrate SQL Server to Oracle 11G Offline (OMWB)

texts:
So, I'm having some difficult to migrate SQL Server to Oracle. This time there appears a new error message:

Migration actions have failed check the migration reports for details. :
Capture
Offline Capture
ORA-00942: table or view do not exist.

I executed the OMWB_OFFLINE_CAPTURE.bat, I passed the parameter to batch and generated the directory with database related.

After, I executed "Migrate To Oracle" I caught the sqlserver2008.ocp file, I finalized, and that error mentioned above appeared. And it does not show the table or view nonexistent.

How do I fix it?


texts:
I have a table with several columns, say a, b, c, d that should be searchable. The problem arises when I need to search a, b, c separately from d (and vise-versa). AFAIK, there's no way to achieve this using one composite fulltext index on all columns, so I create two separate indexes like this:

CREATE FULLTEXT INDEX idx1 ON content (a, b, c);
CREATE FULLTEXT INDEX idx2 ON content (d);

Now I can search the first and second one successfully. For both of them, I would  use the following command:

SELECT * FROM content
WHERE MATCH(a, b, c) AGAINST ('keyword')
AND MATCH(d) AGAINST ('keyword');

explain tells me this:

+----+-------------+---------+----------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type     | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+----------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | content | fulltext | idx1,idx2     | idx1 | 0       |      |    1 | Using where |
+----+-------------+---------+----------+---------------+------+---------+------+------+-------------+

Great! So it's using two indexes, but returns only rows where keyword is present in both inclusive and I need either one, so I change AND to OR and now explain says:

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | content | ALL  | NULL          | NULL | NULL    | NULL |  128 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

What? Suddenly, it's doing a full table scan. Why is this happening? What would be the best way to avoid this?


texts:
I have one field in database and I am getting the multiple dates in the same field. I want to extract multiple dates from the string. Please help me on this
String like:

"7/3/2019 1:20 AM - Request submitted by Ekaterina Rahul  7/3/2019 1:34 AM - Request assigned to   7/3/2019 3:22 AM - PO123456 created"


texts: Direct vs Linked Server Connection in Query Execution Time

texts:
After investigating our ETL Process for months i fell into something strange.
We have one machine (A) which we are using in order to pull data from other machines (B).

When i make a SELECT Statement using Direct Connection from machine (A) to machine (B), the execution time is 09-10 secs
When i make the same SELECT Statement using Linked Server from machine (A) to the same machine (B), the execution time is 25-35 secs.

So why there is this difference?

The same data are transferred in each case.


texts: MySQL Replication after a set period of time

texts:
I have a question about MySQL replication: can I set the slave DBs to only poll the master DB for information once every 10 seconds?

Our MySQL master DB is under fairly heavy load. By default, MySQL replicates in almost real time, which means that each slave DB is polling the master DB for changes many times in one second. I don't want the master DB to be bogged down by the update / delete load as well as the constant requests of slaves to get the latest changes.

In our situation, we don't need the slave DB to be synced in real-time to the master. The slaves can each afford to be 10 seconds behind the master and the clients who are using the slave DBs won't even notice. Is there a way I can set the slaves to poll the master every 10 seconds instead of in real-time?

Note: setting the MASTER_DELAY=N setting will not work

The MASTER_DELAY setting tells the slave to intentionally stay N seconds behind the master. However, it doesn't affect how often the slave polls the master for changes. In my case, setting the MASTER_DELAY setting won't do any good bc I want the slaves to poll the master less, not more.


texts: Should the value of a select max be indexed when the where clause is already indexed

texts:
I have a customer_orders table in a schema designed to be shared between multiple companies. Each customer_order has an invoice number that should always start at 1 and increment based on company (every company has their own invoice counting).

When I create a new customer_order, I have this query to get the largest invoice number for that company and increment it.

select max(invoice) from customer_orders where customer_id = :customer_id

because customer_id already has an index is there any point in adding an index to invoice?

this database is on MySQL in production and sqlite3 for testing & development (ruby on rails).


texts:
I'm not sure if this can be done at all but if it can it would be a huge help.

I have a system where I am not allowed to make changes to the database structures.  I have yet to have code changes put in place either for that matter but at least those they will consider (bairly).

What I am dealing with is a table that contains XML data stored in a varchar(max) column.  Then a view that not only references the XML using xQuery but actually has xQuery in the WHERE clause.

Here is the original query.

CREATE VIEW [dbo].[My_View]
AS
   WITH xoutput AS (
          SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID
          FROM [dbo].ci
          INNER JOIN [dbo].ct
            ON ci.[ContentType_id] = ct.[Id]
          INNER JOIN [dbo]. cv
            ON ci.ID = cv.[ContentItemRecord_id]
          WHERE ct.[Name] = 'MyImage' and Latest = 1)
  SELECT h.ep.value('(MyID)[1]','varchar(10)') as MyID,
      h.ep.value('(Image)[1]', 'varchar(max)') as MyImage,
      h.ep.value('(IsBlocked)[1]', 'bit') as IsBlocked,
      h.ep.value('(IsPublished)[1]', 'bit') as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('//Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked)[1]', 'bit') = 0
    AND h.ep.value('(IsPublished)[1]', 'bit') = 1

What I thought of trying was creating an indexed view out of at least part of it.

The first thing of course was to create a SCHEMABOUND view with COUNT_BIG.  Where I'm running into a problem is the grouping.

When I try it like this:

ALTER VIEW My_XML_Kenneth
WITH SCHEMABINDING
AS
    SELECT ci.[ContentType_id], ci.ID,
        h.ep.value('(IsBlocked)[1]', 'bit') AS IsBlocked,
        h.ep.value('(IsPublished)[1]', 'bit') AS IsPublished,
        h.ep.value('(MyID)[1]','varchar(10)') as MyID,
        h.ep.value('(Image)[1]', 'varchar(max)') as MyImage,
        h.ep.value('(IsBlocked)[1]', 'bit') as IsBlocked,
        h.ep.value('(IsPublished)[1]', 'bit') as IsPublished,
        COUNT_BIG(*) ct_bg
    FROM [dbo].ci
    INNER JOIN [dbo].ct
        ON ci.[ContentType_id] = ct.[Id]
    INNER JOIN [dbo].cv
        ON ci.ID = cv.[ContentItemRecord_id]
    CROSS APPLY (VALUES (CONVERT(xml, ci.[Data]))) x (x)
    CROSS APPLY x.x.nodes('//Data/MyImage') as h(ep)
    WHERE ct.[Name] = 'MyImage'
      AND Latest = 1
    GROUP BY ci.ContentType_id, ci.ID,
        h.ep.value('(IsBlocked)[1]', 'bit'),
        h.ep.value('(IsPublished)[1]', 'bit'),
        h.ep.value('(MyID)[1]','varchar(10)'),
        h.ep.value('(Image)[1]', 'varchar(max)'),
        h.ep.value('(IsBlocked)[1]', 'bit'),
        h.ep.value('(IsPublished)[1]', 'bit')
GO

I get an error

XML methods are not allowed in a GROUP BY clause.

When I try this as the GROUP BY

GROUP BY ci.ContentType_id, ci.ID, h.ep.nodes('//')

I get this error

"nodes" is not a valid function, property, or field.

Is what I'm trying even possible?

Note: last time I tried to come up with a fix to this view I got HEAVY push back that they couldn't possibly make a change.  So if it isn't possible it's not like I'm out much.


texts:
I’m using Postgres 9.5.  I want to create indexes that will make queries like

select * FROM my_object_times where name like ‘Dave %’;

and

select * FROM my_object_times where name like '% LastName';

run quickly.  So I created …

CREATE INDEX my_object_times_name_idx ON my_object_times (name text_pattern_ops);
CREATE INDEX my_object_times_rev_name_idx ON my_object_times (reverse(name) text_pattern_ops);

This works fine for the first case, in which I can see my index being used, but in the second case, “Explain” does not show my index being utilized …

my_db=> explain select * FROM my_object_times where name like '% LastName';
                            QUERY PLAN
------------------------------------------------------------------
 Seq Scan on my_object_times  (cost=0.00..136594.69 rows=51 width=221)
   Filter: ((name)::text ~~ '% LastName'::text)

How do I get my index to be in used in the second case?


texts: how to upgrade mongodb 2.2 to 2.4 properly

texts:
I have a community edition mongodb of 2.2 version and I have read this article: https://docs.mongodb.com/manual/release-notes/2.4-upgrade/

However, I got following errors when I tried to follow the above instruction:

Transaction Check Error:
 file /usr/bin/bsondump from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongo from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongodump from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongoexport from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongofiles from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongoimport from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongooplog from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongoperf from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongorestore from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongostat from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64
 file /usr/bin/mongotop from install of mongo-10gen-2.4.14-mongodb_1.x86_64 conflicts with file from package mongodb-2.2.3-4.fc17.x86_64

Error Summary
-------------

System: Fedora 17


texts:
I've read many posts on internet and stack exchange about multi core usage by MySQL.

From 5.1.38 on MySQL has multi core support. But what about multi CPU?

I'm running 5.1.73 on a Dell PowerEdge with 2 Xeons each with 12 cores. The top command shows 24 cores, but only the first 12 (first CPU) are always busy. The last 12 cores (second CPU) always seem idle.

Is (a newer version of) MySQL capable of using more than 1 CPU?


texts: Duplicate primary key in bulk insert after truncate in SQL Server 2008?

texts: I'm using SSIS to dump data from my SQL Server 2008 prod database to flat files and then using bulk insert to load those flat files into the SQL Server 2008 dev database. The SSIS package is running a TRUNCATE TABLE statement before the bulk insert, but when I run it I still get this error: "The statement has been terminated. Violation of PRIMARY KEY constraint. Cannot insert duplicate key." Why would there be a duplicate key after a truncate? Is there something more I need to do to clear the primary keys before the bulk insert?


texts: Autogrowth Analysis in PROD DB based on the trace file data

texts:
I have used the below query to check our Production Database Autogrowth events so that we can set to a value which is much more realistic than the current value of 200 mb , unrestricted for mdf file and 10 % Restricted to 2048 gb for ldf file.
The current version of our Database is SQL Server 2012 RTM Standard Version and we are managing it through AWS RDS, the total disk space is 2TB.
When I have used the trace file query ,it shows me data only from 08052018 as you can see in the screenshot attached
Can anyone explain why am i seeing data only from 08052018 and not earlier to that?


texts: What do LC_TIME and LC_NUMERIC settings actually change in PostgreSQL?

texts:
According to the PostgreSQL 9.5 documentation, LC_TIME affects "Formatting of dates and times" and LC_NUMERIC - "Formatting of numbers", but I can't seem to see any difference from changing these.

For instance, I changed my postgresql.conf to this:

lc_messages = 'English_United States.1252'
lc_monetary = 'English_United States.1252'
lc_numeric = 'English_United States.1252'
lc_time = 'English_United States.1252'

then created a new DB and ran:

SELECT current_date, format('Date: %s, Number: %s', current_date, 1234.6789);

and got

2016-04-26     Date: 2016-04-26, Number: 1234.6789

I then tried the same with "French_France.1252" and "C" collations and got exactly the same results. (I restarted PostgreSQL in-between and verified the changes using SELECT * FROM pg_settings WHERE name ~~ 'lc%')

So what difference are those settings supposed to make?

I'm running PostgreSQL 9.5.2 on Windows 7.


texts: How do I backup a database from MySQL 32-bit and restore it to MySQL 64-bit?

texts:
I'm currently using MySQL version 5.0.16, on my local computer (which runs a 32-bit OS), for easier access while developing my program.

Now that I'm ready to deploy my program, I'd like to test the connection over a network, and use a 64-bit server to hold the database.

Up til now, I've only used the 32-bit version, and my backup 'process' would be to copy the database folder from C:\Program Files\MySQL\MySQL Server 5.0\data and paste it onto the same directory of another computer.

I'm not sure if that would work.

I tried using both MySQL Query Browser and Command Line Client to do the following:

mysqldump -u root -proot Isys-TMS(EEI1) > isystms.sql;

But both simply return this error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -u root -proot Isys-TMS(EEI1) > isystms.sql' at line 1"

Which is not at all helpful. (Though I'm not sure if I did it right on the command line client, as opposed to Windows' own CMD)

I'm also assuming I can simply use this .sql output on the new 64-bit MySQL, so if that's not the case, what else can I do to solve this problem?

Thanks.

Notes:

I'm using Windows 7 32-bit Pro, with MySQL Server 5.0 installed in it.
I'm planning to move the database to a Windows Server 2008 64-bit, with MySQL Community Server 5.6

EDIT: I actually used mysqldump, not mysqldumb. Quick, someone make a dumb/dump joke.


texts:
Currently I am working in an environment that has the default trace enabled and I would like to propose that we add SQL server auditing to our environment.
I'm looking for some points to put forward to our more senior database members and to management who think that the default trace is enough.

What is the difference between these two different solutions?
Is there any advantages/disadvantages of using one over the other?


texts:
I have a table:

CREATE TABLE tbl (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    rank BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY rank (rank)
)

With some values:

(2,    11)
(1,    12)
(9,    42)
(4711, 43)
(9000, 44)

I.e the ranks may have gaps. The rank is always >= 1.

I want to increment every rank by one, starting by the 3rd least value:

UPDATE tbl table_1
JOIN (
    SELECT table_2.id
    FROM tbl table_2
    ORDER BY table_2.rank ASC
    LIMIT 2, 18446744073709551615
) sub_1 ON table_1.id = sub_1.id
SET table_1.rank = table_1.rank + 1

Problem:

  #1062 - Duplicate entry '43' for key 'rank'

What I have tried:

Adding ORDER BY:

UPDATE tbl table_1
JOIN (
    SELECT table_2.id
    FROM tbl table_2
    ORDER BY table_2.rank ASC
    LIMIT 2, 18446744073709551615
) sub_1 ON table_1.id = sub_1.id
SET table_1.rank = table_1.rank + 1
ORDER BY table_1.rank DESC

  #1221 - Incorrect usage of UPDATE and ORDER BY

Eliminating the join:

UPDATE tbl
SET rank = rank + 1
WHERE rank >= (
    SELECT table_2.rank
    FROM tbl table_2
    ORDER BY table_2.rank ASC
    LIMIT 2, 1
)
ORDER BY rank DESC

  #1093 - Table 'tbl' is specified twice, both as a target for 'UPDATE' and as a separate source for data

The only options I can think of are either omitting UNIQUE KEY, or splitting up the statement into two: get the 3rd least rank, then run UPDATE  … ORDER BY ….

Is there any proper solution for my problem?

Server: MariaDB 10.2.15
InnoDB: 5.7.22


texts: Forgot to add a table in Transactional Replication : Initialize with backup

texts:
I have a transactional replication(with initialize from backup) setup in my server.

So i setup replication as below:
Used full backup(publisher database full back is scheduled every night) from the previous day of the publisher database.
Did restore of the full backup in the subscriber and then created  publisher and subscriber.
While creating subscriber,i took a differential backup and used it to synch.

And everything works fine.
After two days,i noticed that i missed to add a table in the publisher/subscriber.

Now is it possible to add that table into the publisher/subscriber.?
If i create a separate publisher for that table,when i create
subscriber for the new publisher ,what backup i should use?Already
two nightly scheduled full backup and several transactional backup
are generated,so how can i synch from the full backup i used to
restore in the subscriber.


texts: to pivot or not to pivot - schema design question

texts:
I am implementing something similar to this schema
http://www.databaseanswers.org/data_models/event_reservations/index.htm

It occurs to me that the pivot table for Customer_Bookings would just keep on growing for the primary database interaction (insert Customer_Booking) and become a huge list. All the bookings, past, present and future, for each customer and each event will be contained in this table. The size of Customer_Bookings will be many times the size of any other table in the database and will only continue to get bigger with each new Event and Customer. None of the Customer_Bookings get deleted as new Events are created. Is this normal? Cause for concern? Can the table become too big over time?

I am new to database design but couldn't find the information about this type of know-how anywhere...a link to a good reference would be appreciated too.


texts: Delete duplicate records by column 1 but having differ values in column 2 and column 3

texts:
I have a table like:

SSN FName   LName
1   a   b
1   a   b
1   b   b -------------> to delete
2   c   d
2   c   d

as above example I want to delete such records where SSN has different values in FName and LName columns.

Kindly help.


texts: SQL 2008 permissions - cumulative or not?

texts:
I have a SQL 2008 production database which gets replicated using log shipping to a second, completely different SQL 2008 server. There is also another copy which is restored at irregular intervals to a third SQL instance, again on a completely separate server.
The primary instance of the database is the backbone of our case management system and as such, all rights to insert/update data are consigned by the CMS system itself using a series of permission groups. The secondary (log shipped) instance is used for reporting so that intensive queries aren’t kicking the daylights out of the CMS backend. The tertiary instance is used for development and testing of new code.
I don’t want my team of developers to be able to do anything on the Primary instance than have read-only access to the tables, and no ability to manipulate or execute stored procedures. I do however want them to be able to execute stored procedures on the Secondary instance (which is read-only anyway) and to have full access to the Tertiary instance.
If I only had to worry about individual user rights on the three instances, it would be easy, but there’s a snag: as well as having individual user accounts on all three SQL instances, there is also an Active Directory group which contains my team members but also contains a couple of other user accounts that DO need full access to the production database. That AD group also has permissions on MSDB to enable manipulation of the SQL agent.
So my question is this: if I have the team members set up with explicit per-user-per-database access on the Primary instance, will those permission sets take priority over any rights that might be conferred by membership of the AD group?  The log shipping happens every 20 minutes throughout the day so I don’t want to set something up that necessitates having to change the permissions on the Secondary instance after every log-shipping occurrence.
Just to add a further complication, there is an additional database on the Primary instance that I DO want my team to have full control of. That additional database doesn’t get log-shipped though and is only restored to the Tertiary instance when I decide the need has arisen.


texts: Will OS Patching affect the SQLLOG file Growth in SQL Server

texts:
Last Sunday we have the OS Patch for the month of June.

Can someone help me understand if the monthly OS patching increases the SQLLOG file in SQL Server ?

Is there any linkage between the OS Patch and SQL Log file first of all ?

What could be the main reason for SQL Log file rapid growth in SQL Server all of a sudden in AlwaysOn AGs environment?

Is it Maint plans, Backups .....


texts:
I have an Oracle 11gr2 32 bit on windows 2008 32 bit OS 8 gig ram. There is an application running on 6 machines simultaneously that accesses the Database on this Server. I get the below errors in my listener.log file.

TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 203: Unknown error

and there is also these errors below captured from the application

Error Executing: ALTER SESSION SET CURRENT_SCHEMA = .........

I initially thought it was due to limited number of processes and sessions. I have increased the number of Processes and Sessions, to 600 and 900 respectively. The max utilization only went up to 133 and 139 for processes and session respectively when the listener.log file started throwing this error. When the application are run on up to 3 machines it works fine without any errors and when the 4th machine is started it throws these errors. I do not know if this is some IO issue or Operating system or Oracle itself.

Can you please shed some light on this? Does it have to do with shared server -enabling or setting it to higher limit or changing the dispatchers?

Thanks


texts:
I have two machines (it is a restriction) configured as a replica set with MongoDB 3.6.5.

Is it possible to run an arbiter on a machine that also hosts a secondary member?

Even if it is not advisable, but if it is possible, how can I install an arbiter on the secondary server for when the primary goes down and the secondary becomes the primary?

Once installed, how can I configure the replica set for when the original primary returns and make a copy of the secondary (do not use the replication)?


texts:
I want to migrate a fairly big SQL Server instance (100 GB+) to a cloud machine.
I don't think the attach/detach method will be a good idea. Because sending big files over the internet would take a long time (probably more than 1 night) I thought about configuring snapshot/transactional replication from the source to the cloud instance.

The idea is to let the replication take its course over a few days and then after the databases are up to date simply stop the replication and direct the application to the subscriber database.

Is it possible? Do you have any other ideas? Maybe AlwaysOn (I guess it requires Enterprise and not Standard edition)?


texts: SQL Server Polybase Data Movement Service stopping after few seconds

texts:
I have been scratching my head for a while now on how to fix this issue, SQL Server Polybase Data Movement Service starts successfully however after a few seconds it just stops. The OS on our server went corrupt last week and force us to reload the OS(Windows Server 2016 Data Center), which meant I had to reinstall and reconfigure Microsoft SQL Server 2017 Enterprise. Since this I cannot get the data movement engine to start and stay running as I have a few process that relies on this I am really in need of some advice.

I have found an Microsoft article below which if I look at error:3409 (Which I cannot seem to find in the logs) is referring to registry access. I have always installed and configured Microsoft Sql Server to only use the service accounts for the Engine and the Agent and leave the other services to the defaults.

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors?view=sql-server-ver15

See logs

MSSQLSERVER_DET1_Dms_movement

2020/04/03 3:14:28 PM [Thread:114900] [CommandManager:ErrorEvent] (Error, High):
Shared memory segment has not been created or attached.
Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemoryException: Shared memory segment has not been created or attached.
   at Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemory.ThrowIfInvalid()
   at Microsoft.SqlServer.DataWarehouse.DataMovement.SharedMemory.NativeSharedMemory.SetDistributionCount(Int32 distrCount)
   at Microsoft.SqlServer.DataWarehouse.DataMovement.Nodes.DMSChannelMap.UpdateSharedMemoryDistributionCount()
   at Microsoft.SqlServer.DataWarehouse.DataMovement.Nodes.DMSChannelMap.AddDataChannelReceiver(String nodeName, DataChannelReceiver dataChannelReceiver)
   at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.StartDataChannel(Int32 senderQueueSize, Int32 tcpBufferSize, HashSet`1 nodesToConnect)
   at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.ProcessStartDataChannel(IMessage message)
   at Microsoft.SqlServer.DataWarehouse.DataMovement.CommandManager.ProcessBaseMessage(IBaseMessage message)
2020/04/03 3:14:28 PM [Thread:114900] [CommandManager:InformationEvent] (Info, Normal): Shutting down.
2020/04/03 3:14:28 PM [Thread:12864] [DataChannelReceiver:InformationEvent] (Info, Normal): DataChannelReceiver DataChannelReceiver.Run--DET1:1433: Localhost DET1:1433 connected to RemoteHost DET1:1433: Initiating Run loop.
2020/04/03 3:14:28 PM [Thread:114900] [GeneralInstrumentation:SynchronousLogEvent] (Info, Normal): DMS Exit() called: Forceful shutdown initiated...
2020/04/03 3:14:28 PM [Thread:93816] [DMSDataChannelSenderClient:InformationEvent] (Info, Normal): DMSDataChannelClient created
2020/04/03 3:14:28 PM [Thread:93816] [DataChannelSender:InformationEvent] (Info, Normal): Starting CreditFeedbackReceiver thread: CreditFeedbackReceiver.Run--DET1
2020/04/03 3:14:28 PM [Thread:93816] [CommandManager:InformationEvent] (Info, Normal): Started data channel, node: DET1, type: Sender
2020/04/03 3:14:28 PM [Thread:84568] [CommandManager:InformationEvent] (Info, Normal): Started data channel, node: DET1, type: CreditFeedback

UPDATE
When stopping the SQL Sever Polybase Engine and then Trying to start the Polybase Data Movement Service, then the service remain started until I start the Polybase Engine Service then back to square 1 again.


texts: Setting up TempDB for more than one instance of SQL Server

texts:
I've been given the task of transferring the contents of a SQL Server instance from one Windows server to another Windows Server. One of the many things I need to do on the new server is set up the TempDB. I have read in many articles that the rule of thumb for the TempDb is to put it on its own drive and create 8 equal sized files that use up most of the disk.
However this applies to where there is only one instance of SQL Server.

The new server has 2 instances of SQL server installed and I've tried to find recommendations for setting up the TempDb files in this situation and I can't find anything.

How should I be setting up the TempDb files in this case?
Do I just allocate 1/2 of the disk space to each instance and go from there?

The new instances of SQL server are SQL Server 2016.


texts: Two databases of same size, backups sizes are very different

texts:
This is not a big problem for me, but still, I would like to understand what is happening here. I have two databases on my SQL Server 2008 (10.00.1600) - they are restored from the same backup (and after that some optimization work was done by our C# program - the process was the same for both DBs, + some small amount inserts/deletes was executed afterwards).

But when I take a backup of those databases the output file size differs:

Database1:    706 MB
Database2:   1690 MB

The query used to take both backups (script generated by SSMS):

BACKUP DATABASE [DatabaseX] TO DISK = N'D:\databaseX.bak'
    WITH NOFORMAT, NOINIT, NAME = N'DatabaseX-Full', SKIP, NOREWIND,
    NOUNLOAD, NO_COMPRESSION,  STATS = 10

Here are file stats of databases:

Database1:
    Data allocated:       1950 MB
    Logs allocated:       5121 MB
    Output backup size:    706 MB
Database2:
    Data allocated:       1704 MB
    Logs allocated:         50 MB (this was truncated in the process of my investigation :))
    Output backup size:   1690 MB (NB! this is not true anymore, see update 3 below)

As you can see database 2 has 200 MB less data and still produces a much bigger backup file! Can anyone tell me why is this happening? Please do tell if some additional info is needed.

UPDATE 1

I got the data/log allocated sizes by executing the following query:

SELECT  d.name,
        mf.physical_name,
        round(mf.size * 8 / 1024, 0) Size_MBs
FROM    sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE   d.database_id > 4 -- Skip system databases
ORDER BY d.name

Ldf and mdf sizes were there (each DB has only two files - one mdf and one ldf). And if it matters both databases' recovery model is FULL.

UPDATE 2

Just to clarify - the operation after restoring databases involves all kind of stuff (adding/removing table fields, rebuilding indexes, adding/removing FKs and inserts/deletes). The final structure (tables, indexes and fks) are the same. The amount of data differs slightly.

UPDATE 3

Here the results of the query posted by gbn:

            Database 2          Database 1
            Reserv  Used        Reserv  Used
------------------------------------------------------
table1      475,88  230,98      240,94  240,51
table2      229,73  230,27      188,74  188,44
table3      162,78  163,18      69,06   68,35
table4      69,12   69,06       68,87   68,61
table5      68,75   68,50       66,83   66,37
table6      67,69   66,93       21,08   20,55
table7      19,02   11,05       15,28   14,88
table8      17,81   17,52       4,98    4,55
table9      8,06    3,91        2,52    2,30
table10     4,23    3,90        1,13    1,09
table11     3,40    3,09        0,97    0,93
table12     1,44    0,54        0,90    0,67
table13     1,20    1,04        0,77    0,59
table14     0,98    0,94        0,64    0,52
table15     0,65    0,63        0,39    0,29
table16     0,58    0,58        0,11    0,06
table17     0,12    0,06        0,08    0,08
table18     0,08    0,08        0,05    0,05
table19     0,06    0,06        0,05    0,05
table20     0,06    0,06        0,05    0,05
table21     0,05    0,05        0,05    0,05
table22     0,05    0,05        0,03    0,03
table23     0,05    0,05        0,03    0,03
table24     0,03    0,03        0,03    0,03
table25     0,03    0,03        0,03    0,03
table26     0,03    0,03        0,03    0,03
table27     0,03    0,03        0,03    0,03
table28     0,03    0,03        0,02    0,02
table29     0,02    0,02        0,00    0,00
------------------------------------------------------
TOTAL       1131,95 872,71      683,69  679,16

UPDATE 4

After rebuilding all indexes on Database2 its output backup file size reduced to 1165 MB.


texts: Foreign Key constraint on fixed value field - Ever appropriate?

texts:
I have a short (15 rows) look-up table that lists the valid values for several columns in the database.
I don't think it can be considered a One-True-Lookup-Table, it is about a single definite concept, but it may conceptually be partitioned in some subgroups (3).
I now happen to have to add a column that actually needs to accept only the values from one of these subgroups.

The proper thing to do now would probably to make one table for each subgroup and turn the original table into a simple list of IDs, from which the subgroup tables take their primary ids from.

It is though very unlikely that I ever further need to refer to the subgroups, I will instead frequently use things for which all the values in the original table are valid.

So the partitioning option would make things more complex for the vast part of the application just to support one case.

The only other option I know of is to add a Type column to the original lookup table and a fixed-value column to the single table that need to refer to the subgroup, and use a two-columns foreign key (to ID + Type) in this single case.

This is very ugly, and I'm not sure if it is theoretically correct to use columns that are not the primary key as foreign key referents, but given the context, is it acceptable, or even the best way?


texts: Not getting the expected result with 'IN' and subquery

texts:
I am trying to access the ring_to_number from tbl_destinations that holds all the numbers. And field destinations has the comma separated ID like 1,3. but according to this query i just get the the result of id 1 i didn't get the result of id 3.

SELECT count( id ) AS totalDestination, group_concat( ring_to_number ) AS phoneNumbers
    FROM tbl_destinations
    WHERE id
    IN (

    SELECT destinations
    FROM tbl_rings_to_groups
    WHERE id = '1'
    AND user_id = '1'
    )

Can anyone please tell me why i didn't get the destinations=>1,3 in SubQuery?

tbl_rings_to_groups:

tbl_destinations:


texts: Installing SQL Server Express from SQL Server Developer Installer

texts:
If I install SQL Server 2012 Express using the SQL Server 2012 developer installer, do I still have access to all features available from the Advanced Services and/or With Tools versions of SQL Server Express?

Or maybe I should be asking, what version of SQL Express comes with the Developer Edition?

SQL Server Express
SQL Server Express With Tools
SQL Server Express Advanced Services


texts:
I have this scenario where I have 4 MySQL servers (A,B,C,D), as this:

Server (A) is always online, and serves as the main website.
Servers (B,C and D) are online on random periods of the day, and not concurrently.
Each of the servers can make changes to the tables, whether the state online or offline, the changes should be applied to the other servers.

I've followed this tutorial and everything went okay, until this happened: two servers were offline, the replication stopped, and I needed to reset the slave service on all servers from phpMyAdmin Control Panel in order to get the replication work again.

I need a solution that works automatically. How can I achieve that?


texts:
This may be a bit of a general problem to post but I could really do with some guidance and this seems like the best place.

The problem stems from the strange situation I find myself in at work; I am a data analyst in a medium sized company. We have a multitude of different systems which our IT department access/update/etc using MySQL. At present the only way for me to obtain data to work with is via manual CSV downloads, which I then work with in Access & Excel, which is both ridiculously tedious and inefficient.

Since my arrival at the company I have been pushing for SQL access to the database tables, so I can better make sense of the data and automate updates etc, but the head of IT has basically run me down every time I have taken problems to him, using my inexperience of SQL as justification for shutting me out. In short he is territorial and is unwilling to allow anyone else access to the data, making up excuses about overloading the server, not knowing the data etc etc.

Following advice from my boss I am now learning MySQL. The plan is that I get an extract of the database tables of our CRM system and fully get to grips with them using MySQL. I hope to be able to reorganise the messy tables into a new structure, good enough to sit Qlikview over. This should then allow us to go to the head of IT and counter his excuses about data access. I have used SQL in previous jobs a little and have a lot of experience with Access so I am finding it straightforward. However I am unsure where to begin in terms of designing a whole new database that I hope to use going forward (I don't want any work I do now to go to waste).

In summary:

I have MySQL installed on my PC with Heidi SQL as my workbench
I will shortly be receiving a large extract of tables from out CRM software. These are poorly documented and I will need to do a lot of work with them to get the required output
My end goal is a well structured database with well organised and documented tables, which bring together the useful data from the CRM tables
Where should I start with this?
How should I store the tables? With Access I have one 'Source Tables' database that simply contains all my major tables, and I have other project specific databases that link to these tables. This is so I only ever need to change 1 copy of the tables.
What ETL processes, tools should I use?
What else do I need to think about? I have been reading a lot about indexing etc, but is there anything specific to this problem that I may miss?

Sorry for the essay. I'm not really sure where else to go with this, IT are definitely not going to help me!

Any help would be appreciated. Thanks


texts: How do you totally reset a barman backup setup?

texts:
I have recently had a database crash and recovered the machine using methods other than barman in this case.
Now I have barman correctly annoyed about the status of the wal files, but for the life of me I can't work out how to reset things.
I tried the obvious
 barman rebuild-xlogdb db
Rebuilding xlogdb for server db
Done rebuilding xlogdb for server db (history: 0, backup_labels: 4, wal_file: 3027)
barman@dbqlbackup:/home/pnunn$ barman receive-wal --reset db
ERROR: The receive-wal position is ahead of PostgreSQL current WAL lsn (00000001000000A30000007A.partial > 000000010000009D00000096)
barman@dbsqlbackup:/home/pnunn$ barman receive-wal --stop db
ERROR: Termination of receive-wal failed: no such process for server db

When I run barman check db I get
Server db:
        PostgreSQL: OK
        superuser or standard user with backup privileges: OK
        PostgreSQL streaming: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 3 backups, expected at least 0)
        ssh: OK (PostgreSQL server)
        systemid coherence: OK
        pg_receivexlog: OK
        pg_receivexlog compatible: OK
        receive-wal running: FAILED (See the Barman log file for more details)
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: FAILED (duplicates: 283)

Barman is correctly grumpy about having WAL files chopped off and resent and I'm happy to just blow away the current backups and WALS and start again from now (if that makes sense), but I can't for the life of me work out how.
I have tried rebuilding the xlogdb, tried to move the data directory, but couldn't because its in use.
Ive tried barman switch-wal --force --archive db but got a bunch of lines
 Error: 000000010000009D0000009F is already present in server db. File moved to errors directory.
    000000010000009D000000A0
    Error: 000000010000009D000000A0 is already present in server db. File moved to errors directory.
    000000010000009D000000A1
    Error: 000000010000009D000000A1 is already present in server db. File moved to errors directory.
    000000010000009D000000A2
    Error: 000000010000009D000000A2 is already present in server db. File moved to errors directory.
    000000010000009D000000A3
    Error: 000000010000009D000000A3 is already present in server db. File moved to errors directory.

But so far no joy.
I tried moving the /data directory to /data.old to start again, but it keeps telling me the directory is in use (even though lsof shows nothing).
Is there a "restart from scratch" command that I'm missing?


texts: How to record prices for the product sales?

texts:
I'm building a database for daily sales report. For example:
29/01/2021 -> 50 apples were sold
30/01/2021 -> 46 apples were sold
I have these tables:
products

id INT AUTO_INCREMENT PRIMARY KEY
name VARCHAR(100) NOT NULL
price FLOAT NOT NULL
deleted_at DATETIME NULL,
Other stuff

sales

saledate DATE,
product_id INT,
quantity INT,
PRIMARY KEY (saledate, product_id),
FOREIGN KEY (product_id) REFERENCES products(id),

It is working. But now I have to record the price of the product when it was sold. The problem is that the product's price can be changed during the day for example:
29/01/2021 -> 43 apples were sold (Apple price: 1$)
29/01/2021 -> 7 apples were sold (Apple price: 0.90$)
30/01/2021 -> 46 apples were sold (Apple price: 1.10$)
as you can see, on 29/01/2021 we sold 43 apples to 1$ and 7 apples to 0.90$ because the price of  apples has changed. I thought to add a column to sales table: price FLOAT NOT NULL but then how can I to have multiple prices associated to a specific saledate?
In essence, how to change the sales table to get output like in the example above? I thought to make price column primary key: PRIMARY KEY (saledate, product_id, price) but honestly I don't know if it is a clean solution and if it has a bad impact on performance. Currently the query to insert a new sale is:
INSERT INTO sales (saledate, product_id, quantity) VALUES(date_value, product_id_value, quantity_value)
ON DUPLICATE KEY UPDATE quantity = quantity + quantity_value

Thank you in advance.
PS: Sorry for my bad english


texts: Equality in JOIN or WHERE clause? Efficiency and style

texts:
Of the two queries below, which is the most efficient for postgresql?
And which has better style in terms of readability, etc.

The difference is the placement of the statement doctor.type != 'surgeon'

Inside the WHERE clause:

SELECT practice.name, doctor.name
FROM doctor
JOIN practice ON (doctor_code = code)
WHERE doctor.name LIKE '%son'
AND (doctor.type != 'surgeon');

or inside the JOIN clause:

SELECT practice.name, doctor.name
FROM doctor
JOIN practice ON (doctor_code = code AND doctor.type != 'surgeon')
WHERE doctor.name LIKE '%son';


texts: Why is the Group by and Having clause very slow with this query?

texts:
I have this query which is when run against 100K rows, takes forever to result in my system with 2 GB Ram and standard MySQL installation.

Now in my database , I have two tables, chitentry and chitentrygroup. Both contain more then 100K rows. When I run the following query the result is always pending and I do not get any output and the query keeps running.

    SELECT  SQL_CALC_FOUND_ROWS  chitentrygroupid,
                       chitentrygroupdate,
                       chitentryno,
                       chitbookno,
                       chitamount,
                       entryamount,
                       entryweight,
                       employeename,
                       accountname,
                       rate,
                       monthentry
             FROM   (SELECT chitentrygroup.chitentrygroupid,
           chitentrygroup.chitentrygroupdate,
           chitentry.chitentryno,
           chitentry.chitbookno,
           Round(( chitentry.chitamount ), 0) AS chitamount,
           chitentry.entryamount,
           chitentry.entryweight,
           employee.employeename,
           account.accountname,
           chitentry.rate,
           chitentry.monthentry
    FROM   chitentrygroup
           LEFT OUTER JOIN chitentry
                        ON chitentry.chitentrygroupid =
                           chitentrygroup.chitentrygroupid
           LEFT OUTER JOIN employee
                        ON employee.employeeid = chitentrygroup.employeeid
           LEFT OUTER JOIN account
                        ON account.accountid = chitentrygroup.accountid
         WHERE  chitentrygroup.status NOT IN ( 0, 3, 8 )  AND chitentrygroup.industryid IN ( 3 )
           AND 1 = 1
           AND 1 = 1
           AND 1 = 1
    GROUP  BY 1
    HAVING 1 = 1
    ORDER  BY chitentrygroup.chitentrygroupid DESC ) AS t
       LIMIT  0, 10

Now when I remove SQL_CALC_FOUND_ROWS , Group By and Having  Part of the query. The query is blazing fast. The presence of any of the above I stated makes the query stuck with this big table.

How should I optimize this query with Group By,Having and SQL_CALC_FOUND_ROWS  Clause?


texts: Can an insert procedure trigger unintentional loops?

texts:
Is it possible for a stored procedure like this to produce a loop of unintentional inserts? I'm asking since this exact procedure produces this resulting loop. (I'm pretty convinced it isn't within the server code, since all it has is a do until-loop that produces a perfect iteration). If I run the containing select-statement alone it works like a charm, but when I add the insert-line to the procedure it starts to repeat the inserts. I'm using MySQL server version: 5.0.83-community.

EDIT There's a fiddle of this to play with.

DROP PROCEDURE `RedistributeEliminations`//
CREATE PROCEDURE `RedistributeEliminations`(IN current_round INT(11), IN votes_id CHAR(50), IN preferred_candidates CHAR(50), IN eliminated_candidates CHAR(50))
BEGIN
    INSERT INTO vote_final (round_id, vote_candidate, redistribution, votes)
        SELECT current_round round_id, y.vote_candidate, y.redistribution, (votes + y.redistribution) votes
        FROM (
            (SELECT vote_candidate, COUNT(*) redistribution, t4.original_votes votes
            FROM vote_orders t1
            INNER JOIN (
                SELECT ij1.vote_id, MIN(ij1.vote_order) min_vote_order
                FROM vote_orders ij1
                WHERE FIND_IN_SET(ij1.vote_candidate, eliminated_candidates) = 0
                AND FIND_IN_SET(ij1.vote_candidate, preferred_candidates) != 0
                AND FIND_IN_SET(ij1.vote_id, votes_id) != 0
                GROUP BY ij1.vote_id
            ) t2
            ON t1.vote_id = t2.vote_id
            AND t1.vote_order = t2.min_vote_order
            INNER JOIN (
                SELECT ij2.vote_id
                FROM vote_orders ij2
                WHERE FIND_IN_SET(ij2.vote_candidate, eliminated_candidates) != 0
                AND FIND_IN_SET(ij2.vote_candidate, preferred_candidates) = 0
                AND ij2.vote_order = 1
            ) t3
            ON t1.vote_id = t3.vote_id
            LEFT OUTER JOIN (
                SELECT oj1.vote_candidate candidate, oj1.votes original_votes
                FROM vote_final oj1
                WHERE oj1.round_id = current_round - 1
            ) t4
            ON t1.vote_candidate = t4.candidate
            GROUP BY vote_candidate
            ORDER BY vote_candidate ASC)
            UNION
            (SELECT vote_candidate, (0 - COUNT(*)) redistribution, COUNT(*) votes
            FROM vote_orders
            WHERE vote_order = 1
            AND FIND_IN_SET(vote_candidate, eliminated_candidates) != 0
            AND FIND_IN_SET(vote_candidate, preferred_candidates) = 0
            GROUP BY vote_candidate)
            UNION
            (SELECT vote_candidate, 0 redistribution, votes
            FROM vote_final
            WHERE round_id = current_round - 1)
        ) y
        GROUP BY y.vote_candidate;
END


texts: Is this normal binary logging behaviour - maintaining one ever growing file despite expire_logs_days?

texts:
I have a Percona MySQL DB running on a dedicated server.

I have in my.cnf

# REPLICATION #
log_bin=/var/lib/mysql/binlogs/mysql-bin
expire_logs_days=7

My issue is that the log file mysql-bin.000001 is ever growing in size.

I'm not sure if the expected behaviour is for it to start a new file each day on 7 day rotation, or to "trim from the top", but it doesn't seem to be doing either, as it's the only file been created and continues to grow in size beyond the 7 day boundary.

Any advice or clarification is much appreciated.


texts:
I am trying to think of the best way to store my applications data in weekly chunks.

The app is a basketball statistics app, after all the games are finished for example in week one I would like to store the tracked data into a week 1 database.

Right now all the data is stored using a REST api and the app has 2 collections, one for players and one for teams.

The client side pulls in the teams collection then pulls in the players collection.

I am just lost on how I would go about separating data by weekly, so that the main client side still pulls in the teams and players but in week two has a fresh copy, but in another area in my app I can still pull old data by week.

I hope this is clear enough, I would greatly appreciate some insight.


texts: Finding the total sum of salary for person who worked at least two years

texts:
Employee table

So, I am looking for a result with Name, salary and age - Salary should be the sum of all he earned, also it should be calculated only for person who has worked more than a year

For Example employee Sunny worked totally 4 years whereas Arpita worked only one year

I tried group by name and calculated totally earning but not able to find only for person who worked for more than a year, I mean at least two years

select name as [Name of Employee], sum(salary) as [Salary Earned] from Employee group by name;


texts: Correcting wrong timezones/timestamps in existing tables

texts:
I did a huge mistake because I didn't know how the TIMESTAMPTZ datatype works in postgresql.
We need to store sensor data from a handful of different sources.
We have a few tables with two timestamp columns, defined as follows.
CREATE TABLE discriminator0 (
    ...
    timestamp TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT (NOW() AT TIME ZONE 'CEST')
)

The sensors send their data without a timezone (it's cest). In order to match the created_at column with the timestamp column, I set its default value to the current time at the cest timezone.
It didn't occur to me that this is severely flawed until I tried to query the database from a client.
SELECT created_at, timestamp, tempoutside FROM d0 WHERE timestamp > '2020-07-20T13:56:00+02:00' LIMIT 9;
          created_at           |         timestamp          |  tempoutside
-------------------------------+----------------------------+---------------
 2020-07-20 11:56:07.591044+00 | 2020-07-20 11:56:00.001+00 |     27.734375
 2020-07-20 11:56:04.386565+00 | 2020-07-20 11:56:00.008+00 | 26.8798828125
 2020-07-20 11:56:08.562664+00 | 2020-07-20 11:56:00.012+00 | 27.3681640625
 2020-07-20 11:56:09.980161+00 | 2020-07-20 11:56:00.015+00 |  30.126953125
 2020-07-20 11:56:08.71351+00  | 2020-07-20 11:56:00.027+00 | 26.0986328125
 2020-07-20 11:56:06.031052+00 | 2020-07-20 11:56:00.03+00  |            25
 2020-07-20 11:56:05.752615+00 | 2020-07-20 11:56:00.033+00 |  27.001953125
 2020-07-20 11:56:07.248163+00 | 2020-07-20 11:56:00.033+00 | 27.6123046875
 2020-07-20 11:55:56.214678+00 | 2020-07-20 11:56:00.034+00 | 27.1728515625

The problem is, now that I'm specifying a timezone in the query, the result gets shifted by two hours because I stored a cest timestamp as a utc timestamp.

Can I define the timestamp column to be treated as cest even though the timestamp inserted doesn't contain a time zone? I can't modify the timestamp before inserting it into the database. Also, I would like to avoid setting the servers time zone, if possible, but i guess it isn't.

I could write a trigger that locks the table, stores the id of the last inserted row somewhere, sets the server tz to cest and changes the default constraint.
Then I could run a simple update that fixes all prior timestamps. Is there anything that could go wrong with that?


texts: Store Elasticsearch shards in separate partitions on data node

texts:
I have an Elasticsearch cluster configured with one head node and three data nodes with the number of replicas set to 2. The data nodes are each split into three partitions called /data1 /data2 and /data3; the disk on each data node is partitioned. Elasticsearch is storing all the shards in the /data1 partition on each data node. The system has been working fine but now I want to add a new index and the /data1 partitions do not have space to store the new shards.
How can I instruct the cluster to store the new shards on different disk partition?
I looked at /etc/elasticsearch/elasticsearch.yml on the head node and path.data is set to /var/lib/elasticsearch. How do I modify path.data to use a different partition on the data nodes?
Update: In response to the link posted in the comments, I modified the elasticsearch.yml to look like this:
path.data:  /data1/elasticsearch, /data2/elasticsearch_2, /data3/elasticsearch_3
And created the corresponding directories on the data nodes. However, I am still getting the node is above the low watermark warnings from two of the nodes and a the shard cannot be allocated to the same node on which a copy of the shard already exists from the third.


texts: Have I misunderstood the amount of "automatic optimization" that PostgreSQL does without me having to be "clever" when designing my tables?

texts:
Suppose I have a table such as this, for logging all web requests made to my website:
CREATE TABLE weblog
(
    id      bigserial,
    moment      timestamp with time zone NOT NULL DEFAULT now(),
    ua      text, -- user agent
    ref     text, -- referer
    "IP-addy"   inet NOT NULL,
    PRIMARY KEY (id)
)

Most people will be sending the same user-agent, such as "MOSAIC 1.0". Many will send the same referer as well. This means that, although the id, moment, and IP-addy columns will differ, a huge amount of records/rows in the table will end up having the same values for "ua" and "ref".
This really got me thinking about storage space and efficiency, but having experimented with this for a few weeks now, I've basically come "full circle", back to thinking that this does make sense after all. Allow me to explain.
Although seemingly a gigantic waste, storing these repeated values on the disk may be necessary for performance. I'm not sure about it, but it seems like it probably is.
When I back up my database, which was my biggest worry rather than it taking a lot of disk space while "live", I apply the maximum compression to pg_dump (level 9), which I assume means that all of those duplicated values will turn into a single string stored, and then just referenced with small bits for each record which contains the same cell value. This seems to be the case because the difference between using level 0 (no compression) and 9 is pretty huge in the file size of the resulting .pg_dump archive (using the oddly named "custom" (binary) format).
I didn't originally think about this, but since it applies compression, the backups probably already are as small as they could be, regardless of my table structure.
Currently, I have them structured like this:
CREATE TABLE weblog
(
    id          bigserial,
    moment          timestamp with time zone NOT NULL DEFAULT now(),
    ua          bigint, -- user agent
    ref         bigint, -- referer
    "IP-addy"       inet NOT NULL,
    PRIMARY KEY     (id),
    FOREIGN KEY     blabla,
    FOREIGN KEY     blabla
)

And then I have one table for each of "ua" and "ref", where I just store a unique string value with a bigserial id and then associate it with the ua/ref bigints (they are no longer text). This makes both SELECT and INSERT queries cumbersome and require an extra query for each INSERT where it puts in the new string (if it doesn't already exist in the table).
Basically, it's an "ugly" (not very elegant) solution to the user (me).
And it makes me wonder if this method saves so much disk space anyway. I have only done this for my biggest tables -- not for ones that I know will remain "reasonable". It's not worth it to do this for every single text column, and it would feel like I'm really fighting PostgreSQL and probably am doing something wrong.
What do you all say about this? Have I fundamentally misunderstood something? Does PG just "expand" my table structure back into the original structure internally, in order to boost performance at the expense of "live" disk space taken up by my database?
I feel as if I have a bad understanding of just how much (or little) PG actually "abstracts away", and how "clever" I have to be in my structure these days. I may be mentally stuck in the 1970s or something by thinking like this. Maybe I've gained nothing from all this work with extra tables, FKs, and extra/complicated queries?


texts: Is there a reliable way to determine how long it took the optimizer to generate a query plan?

texts: I'm not sure where I'd start, but is there a way to see how long it took the optimizer to generate a query plan for a query? Is it stored in any of the DMVs or something that's part of one of the statistics? Or can I calculate it somehow if I include live statistics or the actual execution plan? Maybe in the query store?


texts: How can I customize vertices in Azure CosmosDB?

texts:
I'm currently working with CosmosDB and I want to personalize my vertices. In the Data Explorer there a menu in the upper toolbar called 'Style'. The uppermost dropdown called 'Show vertex (node) as' does work, but no matter which option I choose in 'Map this property to node color' dropdown no colors change, and the 'Map this property to node icon' dropdown doesn't work either.
The latter writes 'Icon set: blank for this collection' but I can't really find anything else. Is there an option to change vertex colors or to use different icons? I don't find the corresponding documentation.
Thank you


texts:
I have DB with 2 schemas: A and B.  B schema's procedures use A schema's procedures.

I need to create user, which will not be able to see A schema. This user can use only B schema, but i want to let the user execute B's procedures that use A's procedures...


texts: Postgresql createrole permission for user

texts:
I created a role with createrole flag, but users in this role trying to create users fail with permissions denied. Do I need to assign the createrole to each user instead of just the role (group)?

my steps were as follow:

create role group1 with nologin createrole;
create user user1 PASSWORD 'password' in role group1;

then login as user1 and trying to create user newuser fails with the error permission denied to create role


texts: SQL Server 2008 R2 - Access Failure in a query that uses Linked server

texts:
We have 2 SQL Server 2008 R2 machines.

A user is running a query on Server A that joins a table in Server B. In the query he is using linked server to point Server B.

User has access to Server B Database in question. He is a db_owner there. but when he runs the query it fails.

  Msg 18456, Level 14, State 1, Line 1
  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The linked server's security is set to "Be made using the login's current security context".
Since the user has db_owner access to Server B database, I would expect this query work from Server A. It doesn't.

How can give this user access to run this query on Server A.

Thanks for your help.


texts: Import CSV in Postgres with static data in other columns

texts: How can I COPY a CSV file in Postgres along with static data for the columns not in the CSV? In MySQL I can do LOAD DATA INFILE ... and profile the column names along with any values for the columns not present in the file. I want to do something similar with Postgres but this is not supported by the COPY command.


texts:
I have an MDF file that was autogrown from 1MB to 40GB in 10% increment. The file is heavily fragmented now, and I want to defrag it with minimum downtime.

I have a plan that I am not sure will work:

Normally defrag the partition while the service is still running until the only fragmented files are the database files.
Enter single user mode.
Make complete backup to a different partition.
Delete the database with fragmented MDF file.
Create/restore new database from the recently created backup.

Will dropping database and then restore from backup remove the file fragmentation?

Or should I just turn off the service and run defrag tool until it finishes?


texts: How can I create a test copy of my production database that is updated daily on SQL Server?

texts:
I have a production database on SQL Server.  I want a copy of that database to use for running tests.  I'd like the test copy to be updated nightly, so that I always have access to fresh data every morning.

The simple solution would be a full backup and restore each night, and then run the latest migration scripts on test.  However, the production database is huge (300Gb+), we have to copy across a not-so-fast link, and the whole operation takes longer than one night.

What I would like to do is perform one full backup-restore and then use differential backups each night.   Would it be possible to rollback all changes made on test during the day, then restore the latest diff from prod, then run the latest migration scripts?  If so, how should I do this?


texts:
I'm trying to achieve the following tasks in SQL Server. I have a table that I need to monitor its growth. The table shouldn't be containing more than X amount of rows, if there are that many amount of rows I need to know.
I was planning on creating a stored procedure that would return to me the amount of rows found in the table that I want to monitor.
The stored procedure would also evaluate the number of rows in the table and if its greater I want to store the time that this happen in a table that I have designated for this purpose(this will give me a clue if this is happening at certain times).
Then I would put this SP in a sql agent job and run it every hour, and have it email me when the event occur.

However I'm not sure if this is the best way to monitor how many rows there are in the table. I was thinking I could also make a trigger on the table and on every insert have it check the amount of rows that there are, and then write it to the monitoring table, and then I would check this table every so often with a sql agent job and have it email me as well.

Is there a way I could make a job email me as soon as the table goes over the threshold limit that i have set? I'm not sure what would be the best way to implement this.


texts: Query to find the subtract of each column from the following column

texts:
Hi everyone hope you are doing well
   I have a table with this structure
   Table 1:

 [dbo].[ValueTest] (Date , PorojectCode , Value)

It has this data

('1397/04/01' , 'A' , 100) , ('1397/04/03' , 'A' , 120) , ('1397/04/05' ,'A',140)

I need to have the result below :

 ProjectCode    Value
-------------   -------
    A              20
    A              20

I Mean I need to have the subtract of the value of each column from the following column
I have tried this query but I did not get the result

Thanks for helping me

Select ProjectCode ,
       (Select v.Value - v2.Value
        From [dbo].[ValueTest] V2
        Where v2.DateKey >= V.DateKey)
from [dbo].[ValueTest] V


texts:
This db will work with a CakePhp app and need advice about design. Is it good idea to put orders on center or should I put clients on center?

Clients has orders and an order record can only have one domain or one hosting. An order record cannot have more than one domain or hosting record same time. It decides which of it has by orders_types.


texts: In PostgreSQL 9.3 , UNION VIEW with WHERE CLAUSE not taken into account

texts:
We use Postgres 9.3, we want to split a large database (evaluated final size will be > 100 T) on many servers. As our users already have a set of existing queries, we cannot use solutions like PL/Proxy.  We would like to use the postgres_fdw contrib to externalize some part of the data.

To illustrate our problem, we created 2 foreign tables foreign_table_1 and foreign_table_2. As inheritance seems not available on foreign tables, we created an union view like this :

CREATE VIEW union_table (id, name)
  AS      ( SELECT id, name FROM foreign_table_1 where id BETWEEN 1 AND 100000
  UNION ALL SELECT id, name FROM foreign_table_2 where id > 100000
  ) ;

The problem is that Postgres does not push the constraints into the query plan. For instance here both foreign tables are scanned :

postgres=# explain analyze select * from union_table where id = 1234;
 Append  (cost=100.00..303.35 rows=5 width=8) (actual time=1.113..1.113 rows=0 loops=1)
   ->  Foreign Scan on master_table_1  (cost=100.00..154.82 rows=1 width=8) (actual time=0.567..0.567 rows=0 loops=1)
   ->  Foreign Scan on master_table_2  (cost=100.00..148.48 rows=4 width=8) (actual time=0.538..0.538 rows=0 loops=1)
 Total runtime: 1.876 ms
(4 rows)

Is it possible to push the WHERE clause into the query plan ?
Is it possible to use inheritance with postgres_fdw ?
Is there any other way to obtain a sharded table with Postgres ?


texts: How can i Update the status of field with a trigger?

texts:
This is my example code:

create trigger MyTrigger on myTable
after update
as
begin
   set nocount on;
   update talleres set status='active' where FirstDate=getdate()
end
go

create trigger MyTrigge2 on MyTable
for update
as
begin
   declare @d DATETIME=getdate();
   set nocount on;
   update talleres set status='Active' where FirstDate=format(@d,'MM/dd/YYYY')
end
go


texts: Getting multiple queries with "show transaction isolation level" in pg_activity

texts:
I am using PostgreSQL server for my production use.

When I fire a query like

select * from pg_stat_activity

on my server, I am getting 98% of queries like

SHOW TRANSACTION ISOLATION LEVEL

and my server accepts only 100 connections, so I am not able to proceed further.

Why is this happening? How can I block all these queries?