texts: Date calculations give different results in different environments

texts:
This query:
WITH RECURSIVE
cte AS ( SELECT 0 n
         UNION ALL
         SELECT n + 1 FROM cte WHERE n < 20)
SELECT n, ('2020-04-13 00:00:00 Europe/Berlin'::timestamptz - make_interval(days => n)) AT TIME ZONE 'Europe/Berlin'
FROM cte
ORDER BY n

On my machine it gives this result:

Note that the time switches from 00:00 to 23:00 on the day of the DST switch.
On dbfiddle.uk (Link) the query gives this result:

Note that it shows 00:00 for all days, there is no change on the day of the DST switch.
And then on sqlfiddle.com (Link) the result is:

Here we have the switch again, but something else is strange here: The time zone of the result is given as Z (UTC) even though everything before was in time zone Europe/Berlin.


texts: split SQL FULL Backups on ola hallengren maintanance solution

texts:
I am using Ola Hallengren backup solution in all our environment, I have a very big Backup file which is taking 10+ hours to complete, so I am thinking to split SQL Server Full backup files.

anyone know how to split backup files in ola hallengren backup solution or else tell me if I need to create different maintenance plan only for one BIG DB

Thanks,
Ven


texts: Mongodb ReplicaSet Changing Config Format

texts:
I have a 3 node mongodb replica set which uses the old syntax (non-Yaml) syntax that I am trying to change to use the new Yaml syntax

old syntax

auth = true
bind_ip = 0.0.0.0
dbpath = /var/lib/mongodb
fork = false
keyFile = /etc/mongod.key
logappend = true
logpath = /var/log/mongodb/mongod.log
nojournal = false
port = 27017
replSet = rs0
rest = false
smallfiles = false

Yaml file

net:
    bindIp: 0.0.0.0
    port: 27017
    http:
        enabled: true
        RESTInterfaceEnabled: true
storage:
    dbPath: /var/lib/mongodb
    mmapv1:
        smallFiles: false
    journal:
        enabled: false
processManagement:
    fork: false
security:
    keyFile: /etc/mongod.key
    authorization: enabled
systemLog:
    logAppend: true
    path: /var/log/mongodb/mongod.log
replication:
    replSetName: "rs0"

I have made the change on the primary but it no longer properly starts up. I get the following error message in the log

  SyncSourceFeedback error sending update to :27017:
  CallbackCanceled: Reporter no longer valid

I did a search on the error but I have not been able to find a solution.
The replicaset is running mongo v3.4.9

I have also tried the following:

Setting up each server with the same configuration
Stopping services on all members and starting one at a time.

These attempts did not work


texts: Number of rows summed up by grouping them, each group has a different number of rows

texts:
Every 4 rows need to be summed up and displayed against that group but every group has different number of rows, at the end of the group for the odd rows sum is different.

For example, in the group if just one row is there then it will be value = X + last 3 rows value from the previous group similarly if 2 rows remaining  for first row in the group will be X + last 3 rows value from the previous group for the 2nd row it will be from the previous group X + previous row from the group and  last 2 rows value from the previous group at the end it should be total 4 rows.

I have tried this query doesn't seem to be working for some reason:

            declare @temptable table(RetailerID int, retailer nvarchar(50),category nvarchar(50),Weekid int,value bigint)
            Insert into @temptable values(34,'R1','a',1,10)
            insert into @temptable values(34,'R1','a',2,10)
            insert into @temptable values(34,'R1','a',3,4)
            insert into @temptable values(34,'R1','a',4,5)
            insert into @temptable values(34,'R1','b',1,2)
            insert into @temptable values(34,'R1','b',2,4)
            insert into @temptable values(34,'R1','b',3,5)
            insert into @temptable values(34,'R1','b',4,3)
            insert into @temptable values(34,'R1','b',5,1)
            insert into @temptable values(34,'R1','c',1,35)
            insert into @temptable values(34,'R1','c',2,68)
            insert into @temptable values(34,'R1','c',3,43)
            insert into @temptable values(34,'R1','c',4,4)
            insert into @temptable values(34,'R1','c',5,5)

            select retailer,category,Weekid,isnull(sum(value),0)    from @temptable td
            where Weekid  between 1 and 12 and RetailerID=34
            group by retailer,category,Weekid  order by retailer,category,Weekid;
            with cte as
            ( select retailer,category,Weekid,value,
                    Rangkgrp = (row_number() over (order by category) - 1) / 4
            from @temptable
            )
            select retailer,category,Weekid,value,
                sumofvalue=sum(value) over (partition by Rangkgrp),Rangkgrp
            from cte
            order by category,Weekid

Result my query produces which is not what I am looking for. The last row in category b is adding up values from category C ,this is not what I want it has to be from Category b and in category C last two rows are incorrect Category C with Week4 should be 150 and with Week5 should be 120.

How 13 and 120 are calculated: It's Category b Week 2 + 3 + 4 +5 values which is (4+5+3+1) and for category C its again the same thing Week 2 + 3 + 4 +5 values which is (68+43+4+5)=120 and if there was another row in Category b/c it should be Week 3+4+5+6 and so on there can be gap in the weeks but that doesn't matter it should just pick up the next week in the group but under same category.

Let me try to explain again for category a we have 4 rows which is perfectly fine so the value is W1+2+3+4 = sum(10+10+4+5)=29 so against category I am adding one more column and that 29 will be displayed against all the weeks for category a similarly for category b and c I am adding up w1+2+3+4 =sum(2+4+5+3)=14 will be displayed against category b for first 4 rows now I am left with W5 for category b so W5 is calculated as current week(W5) + 3 Previous week)  and that value should be displayed against Weeks 5 in category b.

In the same category there are many other weeks ex : first partition has 88 weeks it's just calculating the values for first 4 rows then it starts adding up current - 3 previous weeks it shouldn't work that way ..it should be like 22 groups of 4 rows for each group value should be the sum of 4 rows where as if its 78 rows in the partition then 19 groups of 4 rows + 3 rows where it should perform for each row Current weeks + 3 preceding weeks and here I am not sure each partition might have odd or even number of weeks.


texts: How can I optimize searching a column for a sub-string in SQL Server?

texts:
I have a very basic table:

CREATE TABLE [obj_local] (
    [obj_id]     INT             NOT NULL,
    [value]      NVARCHAR (1000) NOT NULL
);

This table stores a lot of data, and I need to search the value column for a sequence of characters:

SELECT [obj_id] FROM [obj_local] WHERE [value] LIKE '%{substring}%'

This is extremely slow. My understanding is that an index won't help me due to the wildcards on either side, and a full text index also won't help me, because I'm not searching for tokens(words).

Is there anything I can do to optimize this search?


texts: Change all databases in "RESTORING" state to ONLINE

texts:
I have multiple databases that I have a backup script that pulls in logs from another server and restores them. When restoring I leave the database in the RESTORING state so further transaction logs can be restored.  I am trying to find a solution where all databases left in the RESTORING state can be set to ONLINE.  I know how to do this for a single database, just not for multiple dbs.  Can anyone lend a hand?

What I have thus far are two commands, I am just not sure how to combine them.

SELECT name FROM sys.databases WHERE state_desc = 'RESTORING"

and

RESTORE DATABASE "name" WITH RECOVERY


texts:
I'm in Workbench and trying to restart MySQL Server 8.0. I was writing some long running queries and it crashed.
When trying to restart, I get this:

The error log contains the following, which is probably the most relevant error:

2021-02-13T16:54:33.405170Z 15 [Warning] [MY-011958] [InnoDB] Over 67
percent of the buffer pool is occupied by lock heaps or the adaptive
hash index! Check that your transactions do not set too many row
locks. Your buffer pool size is 8 MB. Maybe you should make the buffer
pool bigger?. Starting the InnoDB Monitor to print diagnostics,
including lock heap and hash index sizes.


texts: How do I remove first line from text and only the first?

texts:
I have a tables "Table1"  in this table I have a Text NOT null field - "value". the value field is populated like so:

value: 1.2.3.4 [cookie] : butter - 1 cup.
1.2.3.4 [cookie] : flour - 2 cup.
1.2.3.4 [cookie] : sugar - 1/2 cup.

I need to remove only the FIRST line and keep all others. This value can grown up to 500 lines. I am unsure how to delete only the
1.2.3.4 [cookie] : butter - 1 cup.
line.
I tried using replace and substring_index without success. This was my attempt:
SELECT name, SUBSTRING_INDEX(value, '\n', 1) FROM variables

which returns:
Cookies|  1.2.3.4 [cookie] : butter - 1 cup.
The values are put in with new lines as the "separator". I am looking to replace the values with everything after the first string of character up to the end of the first "line". This is more of of a block of text that I need to adjust.
Please assist in removing only the first line for the value.


texts: Restore back up strategy for history tables

texts:
I have historical/log tables which are:

very large
holds data that is not rarely queried
only inserts are performed (constantly, very often)

I want to either compressed them or move them to cheaper(slow) storage.

Because of the tables structure (there are nvarchar/varchar max columns) I was not able to apply or achieved good results using row, page, column store or column store archive compressions.

So, I have decided to move them to cheaper storage. I guess I have to options here,  but let me know if I am wrong about something:

The tables are stored on slow storage. The database is under FULL
recovery model and transaction log backup is made each 15 minutes.
The issue is I am not able to perform partiatl backup (restoring
only important data from the fast storage) and apply transaction
log chain as well. Right?
To create mirror tables on the slow storage and continue to store
the history table on the slow storage. Each day a job is migrating data
from the primary history  tables to the mirror tables. So can I now
restore a partial backup and apply the transaction log chain as
well, as the tables on the slow drive are not same?


texts:
I've got two tables of data, one at a person level and one at a district level.  I'd like to get a query that adds the district information to only the first person it finds in the district and leaves the rest null. The person table looks like:

IDNo    FirstName   LastName    DistrictID    SignUpDate
----------------------------------------------------------
123     bob         smith       1             1/19/2017
234     john        willis      1             1/18/2017
456     john        doe         2             1/17/2017
678     jane        doe         2             1/16/2017
890     jeff        bridges     3             1/19/2017
135     bill        doe         3             1/19/2017
246     sally       abby        4             1/14/2017
357     michael     jordan      4             1/12/2017

And the district table is:

DistrictID    DistrictName   BigDistrict
-------------------------------------------
1             district1      big1
2             district2      big1
3             district3      big2
4             district4      big2

What I'd like to end up with:

IDNo    FirstName   LastName    DistrictID     DistrictName     BigDistrict
-----------------------------------------------------------------------------
123     bob         smith       1              district1        big1
234     john        willis      1              (NULL)           (NULL)
456     john        doe         2              district2        big1
678     jane        doe         2              (NULL)           (NULL)
890     jeff        bridges     3              district3        big2
135     bill        doe         3              (NULL)           (NULL)
246     sally       abby        4              district4        big2
357     michael     jordan      4              (NULL)           (NULL)


texts: How to create a correct entity-relationship diagram (ERD) about a video game profits scenario

texts:
I'm starting to build my application database, and I'm really new to the this world, so I have a lot of doubts about my ERD and if its entity types and relationships are ok.

This is an application that calculates how much me and my friends profit in a video game and saves all the data so we can check it later. I'll explain it a bit so you can understand the ERD.

We want to save this data about every Hunt:

Where the HUNT was (RESPAWN)
How many people we were (1 to 4)
The date of the HUNT
How much everyone WASTE (Knight, Druid, Paladin, Sorcerer and the
Total)
The loot value
The HUNT balance
How many profit we did (each one of us)
The amount of money everyone have to recieve (paying waste + profit
-> TRANSFER)
And if the HUNT is already paid or not

I'm already doing all the math and saving this data in a .txt file for the moment, but I want to take the next step and build a decent database, and to make sure the following diagram is ok:

Are some relationships wrong? Do you think I should add more entity types, properties, etc.?


texts: WARNING Error checking mysqldump version Error retrieving version from mysqldump.exe: mysqldump: unknown option '--no-beep' (exit 2)

texts: When i go to do a manual export from mysql database to workbench, this error occurs,  i dont know solve


texts:
I was optimizing a stored procedure written in cursors (DECLARE cur_name CURSOR...

The client saw this SP ran for more than a day so, my manager assigned this to me.

Upon searching, I used table variable and a while loop to iterate each record.

To my surprise, the difference between the two was huge.

Someone told me that while loop is essentially, a CURSOR.

But when I used table variable with a WHILE LOOP, it ran very very very fast.

Now, what do cursors do internally?


texts:
I have data that contains multiple games, each game would have several users picking it and they would all be grouped into a single week.

At the end of that week I evaluate the results and assign a score. In the list of games there is one bonus game and I want to get the margin from that game, but it doesn't work because I believe there is a grouping it always returns null.

my query is:

SELECT COUNT(game.game_id) AS correct_picks,
       sum(game.points) points,
       pick.user_id, game.bonus,
       user.user_id, user.username,
       user.avatar_date, user.avatar_width, user.avatar_height,
       user.gravatar, user.gender,
       CASE
         WHEN game.bonus = 1 THEN ABS(SUM((game.hteam_score - game.vteam_score) - (pick.hscoreary - pick.ascoreary)))
         ELSE NULL
       END AS margin
FROM      xf_nflj_pickem_game game
LEFT JOIN xf_nflj_pickem_pick as pick ON (pick.game_id = game.game_id)
LEFT JOIN xf_user             as user ON (user.user_id = pick.user_id)
WHERE (game.winner = pick.team_id)
        AND game.week_id = 1
        AND game.pool_id = 1
        AND pick.team_id <> 0
GROUP BY pick.user_id

as you can see I have the case statement. Now if there is just one game then this seems to work but when there are multiple games it just returns null.

Any thoughts on how I could achieve this in a single query?

Dan


texts:
I have an SQL Server database where there is a cell with a delimiter (\) separated string. An example of this string would be:

category_path
=============
RootCategory\Middle Category\Child Category\Child of child category
RootCategory\Middle Category\Other Child Category\

There are a lot of category paths like this. I would like to parse all of them into an other table:

category_name            parent_path
====================================
RootCategory             null
Middle Category          RootCategory
Child Category           RootCategory\Middle Category
Child of child category  RootCategory\Middle Category\Child Category
Other Child Category     RootCategory\Middle Category

I have left out the duplicates here, but the result could contain duplicates (I will need to cursor over the rows of this table later, and I can call distinct there).

There are a lot of examples around the net where a string is simply splitted. (Here for example) I was thinking about writing a query where I split the string, cursor over the results and accumlate the parent_path in each step, but that seems to be very suboptimal.

Is there a solution where I wouldn't have to declare so many cursors for a simple string?


texts: how to make shapefile in geometry column of ms sql?

texts:
I was tried ogr2ogr command like this.

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport.shp" "MSSQL:server=[RemoteServerIp];database=[dbname];User Id=[user];Password=[password];trust_connection=yes;" -sql "SELECT [geometrycolumn].STAsBinary() FROM [tableName] WHERE [indexColumn] = 1;" -overwrite

but I saw error message.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

I don't know what is wrong.


texts: How to create an alphanumeric sequence like AAAA0000 and so on

texts:
I want to create an alphanumeric sequence like this:

AAAA0000
AAAA0001
AAAA0002
AAAA0003
.
.
.
AAAA9999
AAAB0000
AAAB0001
.
.
.
ZZZZ9999

I have created this store procedure to do that but its too slow:

CREATE OR REPLACE FUNCTION public.fn_batch_seq()
  RETURNS text
  LANGUAGE plpgsql
AS
$body$
DECLARE
  v_sequence TEXT := '';
  v_next_sequence TEXT := '';
  v_existing_id BIGINT := 0;
BEGIN

  /*
  *  VARCHAR BATCH SEQUENCE FOR SIMCARDS
  */
  SELECT "sequence" FROM batch_sequence WHERE id = 1 INTO v_sequence;
  IF v_sequence = '' THEN
    RAISE NOTICE 'Error - No existe ningun registro en batch_sequence almacenado';
    RETURN -500;
  END IF;
  SELECT perl_increment(v_sequence) INTO v_next_sequence;

  IF v_next_sequence = '' THEN
    RAISE NOTICE 'Error - La siguiente secuencia generada devolvio null o vacio';
    RETURN -500;
  END IF;

  UPDATE batch_sequence SET "sequence" = v_next_sequence WHERE id = 1;
  RETURN v_next_sequence;

  EXCEPTION WHEN OTHERS THEN
  /*
  * Other errors
  */
  RAISE NOTICE 'Error General - Posibles causas: No existe la tabla batch_sequence o no existe ningun registro en la misma';
  RETURN -500;

END;
$body$
  VOLATILE
  COST 100;

This procedure uses a table to store the sequence:

CREATE TABLE batch_sequence
(
   id        serial   NOT NULL,
   sequence  text     DEFAULT 'AAAA0000'::text NOT NULL
);

-- Column id is associated with sequence public.batch_sequence_id_seq

ALTER TABLE batch_sequence
   ADD CONSTRAINT batch_sequence_pk
   PRIMARY KEY (id);

And for increment the sequence I use a perl procedure:

CREATE OR REPLACE FUNCTION public.perl_increment(text)
  RETURNS text
  LANGUAGE plperl
AS
$body$
my ($x) = @_;
    if (not defined $x) {
        return undef;
    }
    ++$x;
$body$
  VOLATILE
  COST 100;

It works very slow with large amount of data, because it must be executed before inserting every single row. Is there another way to do that with or without Perl?


texts:
I've been keeping a log of expensive running queries, along with their query plans, in a table to allow us to monitor trends in performance and identify areas that need optimising.

However, it's come to the point where the query plans are taking up too much space (as we're storing the entire plan against each query).

I'm therefore attempting to normalise the existing data by extracting the QueryPlanHash and QueryPlan to another table.

CREATE TABLE QueryPlans
(
    QueryPlanHash VARBINARY(25),
    QueryPlan XML,
    CONSTRAINT PK_QueryPlans PRIMARY KEY
    (
      QueryPlanHash
    )
);

As the definition of the query_plan_hash in sys.dm_exec_query_stats is a binary field (and I'll regularly be inserting new data), I was using VARBINARY for the data type in my new table.

However, the insert below fails...

INSERT INTO QueryPlans
    ( QueryPlanHash, QueryPlan )
SELECT queryplanhash, queryplan
FROM
(
    SELECT
      p.value('(./@QueryPlanHash)[1]', 'varchar(20)') queryplanhash,
      QueryPlan,
      ROW_NUMBER() OVER (PARTITION BY p.value('(./@QueryPlanHash)[1]', 'varchar(20)') ORDER BY DateRecorded) rownum
    FROM table
    CROSS APPLY QueryPlan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[@QueryPlanHash]') t(p)
) data
WHERE rownum = 1

....with the error

Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

The problem is that the query plan hashes are already in binary format, however stored as VARCHAR in the XML Query Plan e.g.

0x9473FBCCBC01AFE

and CONVERT to BINARY gives a completely different value

0x3078393437334642434342433031414645

I tried changing the value definition in the XQuery select to binary, but then it returned no values.

How would I extract the value of 0x9473FBCCBC01AFE from an XML query plan as a VARBINARY, rather than a VARCHAR?


texts: The 'Database' with 'ID' = 'xxxxxxx' doesn't exist in the collection

texts:
I have a SSAS Tabular server with 30 database.

I also have a scheduled process to backup all databases daily.

When I run the process which is an SSIS package with script task using AMO, I get this error :

  The 'Database' with 'ID' = 'nameofdatabase' doesn't exist in the
  collection.

The backup process is running under the service account credential and it successfully backs up 27 databases but fails for only 3 of them.

I checked those databases and don't see anything special on them.

I googled the error message and most of the issues are related to deploying or processing the database. I don't see any reason for failing the backup.

What is the problem and how I can resolve this issue?


texts: Sync MongoDB Replica set with Remote Replica Set

texts:
I'm wondering if it is possible (within reason) to sync a new MongoDB Replica Set(RS) with an RS currently in production.

The Problem

We have a site running on AWS that has its own MongoDB Replica Set and we're working on migrating that site to Google Cloud. We want to start a separate RS on Google Compute that will catch up with the AWS RS and stay up to date during our QA lifecycle until we're ready to cut DNS over to Google.

We originally tried adding the replicas on Google as secondaries to the AWS RS to keep them in sync. Our plan was to then take the AWS RS offline and let the Google RS reassign a Primary and continue as the only RS after the site was completely migrated.

This caused some issues when one of the Google replicas got elected as a master and created some syncing issues with our current live site.

The Solution??

I've been debating if it's possible to rsync from the AWS Primary to the GCloud Primary. Is this doable with Mongo? Will the GCloud primary then properly replicate to its slaves?

Or...

Is there a better solution that anyone knows of for this problem?

UPDATE

After trying to add Google nodes to server we're not seeing the node sync:

INFRA-GENERAL-00:SECONDARY> rs.status()
{
"set" : "INFRA-GENERAL-00",
"date" : ISODate("2016-08-03T00:22:37.275Z"),
"myState" : 2,
"term" : NumberLong(2),
"heartbeatIntervalMillis" : NumberLong(2000),
"members" : [
    {
        "_id" : 0,
        "name" : "****:27017",
        "health" : 0,
        "state" : 8,
        "stateStr" : "(not reachable/healthy)",
        "uptime" : 0,
        "optime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
        "lastHeartbeat" : ISODate("2016-08-03T00:22:35.205Z"),
        "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
        "pingMs" : NumberLong(0),
        "lastHeartbeatMessage" : "exception: field not found, expected type 16",
        "configVersion" : -1
    },
    {
        "_id" : 1,
        "name" : "****:27017",
        "health" : 0,
        "state" : 8,
        "stateStr" : "(not reachable/healthy)",
        "uptime" : 0,
        "optime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
        "lastHeartbeat" : ISODate("2016-08-03T00:22:34.728Z"),
        "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
        "pingMs" : NumberLong(0),
        "lastHeartbeatMessage" : "exception: field not found, expected type 16",
        "configVersion" : -1
    },
    {
        "_id" : 2,
        "name" : "****:27017",
        "health" : 0,
        "state" : 8,
        "stateStr" : "(not reachable/healthy)",
        "uptime" : 0,
        "optime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
        "lastHeartbeat" : ISODate("2016-08-03T00:22:35.248Z"),
        "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
        "pingMs" : NumberLong(0),
        "lastHeartbeatMessage" : "exception: field not found, expected type 16",
        "configVersion" : -1
    },
    {
        "_id" : 3,
        "name" : "****:27017",
        "health" : 1,
        "state" : 2,
        "stateStr" : "SECONDARY",
        "uptime" : 338,
        "optime" : {
            "ts" : Timestamp(1470169610, 1),
            "t" : NumberLong(2)
        },
        "optimeDate" : ISODate("2016-08-02T20:26:50Z"),
        "configVersion" : 175485,
        "self" : true
    }
],
"ok" : 1
}

INFRA-GENERAL-00:SECONDARY> rs.config()
2016-08-03T00:23:49.051+0000 E QUERY    [thread1] Error: Could not     retrieve replica set config: {
"ok" : 0,
"errmsg" : "not authorized on admin to execute command {     replSetGetConfig: 1.0 }",
"code" : 13
} :
rs.conf@src/mongo/shell/utils.js:1091:11
@(shell):1:1


texts: Linux Slackware MySQL can't move datadir

texts:
By default datadir was located in /usr/lib/mysql. I decided to move it to a different disk partition: /mnt/C/mysql:

drwxr-x--- 25 mysql mysql 36864 Feb 25 12:52 mysql

I opened /etc/my.cnf, but there was no datadir command there. So, I added one inside [mysqld] block:

datadir     = /mnt/C/mysql

But when I try to start mysqld, it seems to be using the old directory:

bash-4.2# /etc/rc.d/rc.mysqld start
mysqld_safe Logging to '/var/lib/mysql/asus.err'.
touch: cannot touch ‘/var/lib/mysql/asus.err’: No such file or directory
chown: cannot access ‘/var/lib/mysql/asus.err’: No such file or directory
mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/bin/mysqld_safe: line 107: /var/lib/mysql/asus.err: No such file or directory
/usr/bin/mysqld_safe: line 144: /var/lib/mysql/asus.err: No such file or directory
mysqld_safe mysqld from pid file /var/run/mysql/mysql.pid ended
/usr/bin/mysqld_safe: line 107: /var/lib/mysql/asus.err: No such file or directory

I found the portion of /usr/bin/mysqld_safe file, responsible for the error:

# Try where the binary installs put it
if test -d $MY_BASEDIR_VERSION/data/mysql
then
  DATADIR=$MY_BASEDIR_VERSION/data
  if test -z "$defaults" -a -r "$DATADIR/my.cnf"
  then
    defaults="--defaults-extra-file=$DATADIR/my.cnf"
  fi
# Next try where the source installs put it
elif test -d $MY_BASEDIR_VERSION/var/mysql
then
  DATADIR=$MY_BASEDIR_VERSION/var
# Or just give up and use our compiled-in default
else
  DATADIR=/var/lib/mysql
fi

So, it seems to be using the default dir: /var/lib/mysql. I changed it to /mnt/C/mysql, but it didn't help.

What am I doing wrong?


texts:
I restored an Oracle 12.1.0.2 database using RMAN full backup from a RAC+ASM enviroment to single instance enviroment with less memory and cpu (for test purposes) using this document Doc ID 1338193.1.
After restore, I open the database and all pdbs (5 pdbs) are READ & WRITE. After 5-10 min, alert log show this errors and the database is shuting down.
Exception [type: SIGSERV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x2A22942, kgscDump()+150] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_smon_5233.trc (incident=1424259) (PDBNAME=TEST)
ORA-07445: exception encountered: core dump [joxdmp_()+80] [SIGSEGV] [ADDR:0x0] [PC:0x3EE3EB0][SI_KERNEL(general_protection)][]
ORA-07445: exception encountered: core dump [rpisplu_internal()+290] [SIGSEGV] [ADDR:0x0] [PC:0x2A22942] [SI_KERNEL(general_protection)][]

After this errors comes the hard one.
Process 0x0x1049570b70 appears to be hung while dumping
Attempting to kill process 0x1049570b70 with OS pid = 5233
OSD kill succeeded for process 0x1049570b70
Instance Critical Process died unexpectedly PMON: terminating the instance due to error 474
Instance terminated by PMON

As you can see the error comes from (PDBNAME=TEST). If I start the database and I stop the TEST pdb, I have no error and the database is up and running except this pdb. Do you have any idea how can I resolve this errors? I validated all database from corruption using rman validation (and logically).


texts: MySQL partitioning relatively large DB Table

texts:
I have table structure similar to the following -
CREATE TABLE `ProductCatalog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `SerialNumber` varchar(20) DEFAULT NULL,
  `BasePrice` decimal(10,2) DEFAULT NULL,
  `BatchCode` tinyint(3) unsigned DEFAULT NULL,
  `Type` varchar(5) DEFAULT NULL,
  `ItemCode` varchar(5) DEFAULT NULL,
  `ArrivalDate` datetime DEFAULT NULL,
  `InsertTimestamp` int(10) unsigned NOT NULL,
  `BrandID` tinyint(3) unsigned DEFAULT NULL,
  `Model` varchar(10) NOT NULL DEFAULT 'RX209',
  `Description` text,
  PRIMARY KEY (`id`),
  KEY 'idx_ic_sn_ad' (`ItemCode`, `SerialNumber`, `ArrivalDate`),
  KEY 'idx_sn_ad' (`SerialNumber`, `ArrivalDate`, `ItemCode`, `BasePrice`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

There are about 600 Million rows in this table and is growing fastly. Everyday about 0.5 million records are inserted sometimes more sometimes less. So there is massive write activity going on  during certain period of the day (6 am to 8 pm)
Following are the queries I run against this table
SELECT *
FROM ProductCatalog
WHERE SerialNumber='1234567890'
  AND ItemCode!="ABCD"
ORDER BY id DESC LIMIT 1;

SELECT BasePrice
FROM ProductCatalog
WHERE SerialNumber='123456789'
  AND ItemCode!="ABCD"
  and ItemCode!="PQRS"
  AND ItemCode!="MNOP"
ORDER BY ID Desc LIMIT 1

SELECT *
FROM ProductCatalog
WHERE SerialNumber='123456789' AND ItemCode='ABCD'
  AND (ArrivalDate>='2019-01-01 00:00:00' AND ArrivalDate<='2020-12-31 23:59:59')
ORDER BY ArrivalDate ASC

SELECT BatchCode
FROM ProductCatalog
WHERE SerialNumber='123456789'
  AND ItemCode!="ABCD"
  and ItemCode!="PQRS"
  AND ItemCode!="MNOP"
ORDER BY ID Desc LIMIT 1

Above queries and table indexes are optimized as suggested in my previous question here
MySQL table proper indexes for performance optimization
PROBLEM
Since ProductCatalog Table is getting bigger, and third query mentioned above is frequently used, I am thinking of partitioning this table. For this I am thinking of range partitioning using ArrivalDate column. Following is the query I came up with.
ALTER TABLE ProductCatalog PARTITION BY RANGE (TO_DAYS(ArrivalDate)) (
PARTITION p11 VALUES LESS THAN (TO_DAYS('2011-01-01')),
PARTITION p12 VALUES LESS THAN (TO_DAYS('2012-01-01')),
PARTITION p13 VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION p14 VALUES LESS THAN (TO_DAYS('2014-01-01')),
PARTITION p15 VALUES LESS THAN (TO_DAYS('2015-01-01')),
PARTITION p16 VALUES LESS THAN (TO_DAYS('2016-01-01')),
PARTITION p17 VALUES LESS THAN (TO_DAYS('2017-01-01')),
PARTITION p18 VALUES LESS THAN (TO_DAYS('2018-01-01')),
PARTITION p19 VALUES LESS THAN (TO_DAYS('2019-01-01')),
PARTITION p20 VALUES LESS THAN (TO_DAYS('2020-01-01')),
PARTITION p21 VALUES LESS THAN (TO_DAYS('2021-01-01')),
PARTITION p22 VALUES LESS THAN MAXVALUE);

Later every year I will re-organize the partition like this
ALTER TABLE ProductCatalog
REORGANIZE PARTITION p22 INTO (
    PARTITION p22 VALUES LESS THAN (TO_DAYS('2022-01-01')),
    PARTITION p23 VALUES LESS THAN (TO_DAYS('2023-01-01')),
    PARTITION p24 VALUES LESS THAN MAXVALUE
);

QUESTIONS
Every year about 200 Million rows in average are inserted. So taking this into consideration -

Do this partitioning do any good in terms of performance with my existing table structures and indexes.
Do this partitioning do any good for selects statements I have specified above?
Do this partitioning do any good for lots of inserts going every moment?
I know we can delete particular partition, but is it easily possible to archive particular partition  to another archive database before deleting?
Is there any other better idea, to get maximum possible read and write performance in the database?
Do I need to worry about column id int(10) when data is increasing rapidly?
What this partitioning will impact on other three queries where no ArrivalDate is used in WHERE condition?


texts: Using Database Engine Tuning Advisor in production

texts:
I have a SQL Server 2008R2 OLTP database which is experiencing performance issues during work hours. I would like to run Database Engine Tuning Advisor on this instance but I don't know how it will impact the instance itself.

When can I use this tool and are there any risks involved (Is it possible to overload the DB or significantly impact the environment)?

EDIT: I have identified a few issues with the environment and came up with a few ideas to test.

I would like to further the question here:
If it is generally unadvisable to use DETA in production, when can and should it be used? Microsoft created this tool for a reason. If it may help DBAs and developers, how, when and why should we use it?


texts: How can I prevent MongoDB from logging the contents of queries under any circumstance?

texts:
I'm working with sensitive data in MongoDB (Community Server, v3.2, upgrading to latest in the near future). On the production server, it's essential that the data is not written to the log under any circumstances. This is because the log files are processed on a system which is not authorised to contain sensitive data.

The problem is that the sensitive data is often being mentioned explicitly in queries and aggregations etc. This means any kind of query logging needs to be disabled. I still want to retain basic informational logging so that system critical events can be monitored.

What I've done so far is:

Set all log levels to 0.
Set slow query timeout threshold to a very high value.

Is there anything else I need to do to ensure the sensitive data never gets logged? In particular, is it possible to disable slow query logging entirely, instead of relying on a high threshold?


texts: What's the correct behavior for mongoDB when mongo hosts in two DC can't talk to each other

texts:
Let's assume there are two data centers: DC1 and DC2.

In DC1:

Two mongodb hosts, one primary, one secondary. All mongo hosts in DC1 have the readPreferenceTag: dc:"DC1"
Two app host, the readPreferenceTag has set to dc:"DC1" within the mongo URI.

In DC2:

Two mongodb hosts, both set as secondary with priority 0 (can never be selected as primary). All mongo hosts in DC2 have the readPreferenceTag: dc:"DC2"
Two app host, the readPreferenceTag has set to dc:"DC2" within the mongo URI.

All Mongo hosts in both DCs are in one replica-set.

In normal workflow, app hosts in DC1 will W/R to DC1 mongo hosts and app hosts in DC2 will write to the primary mongo host in DC1 and read from mongo hosts in DC2.

My question is: If there's an network issue between the mongo hosts in DC1 and DC2 (mongo hosts in one DC will not be able to talk to the mongo hosts in the other DC but app hosts can still talk to mongo hosts in both DC), the app hosts in DC1 can still perform R/W operation with mongo hosts in DC1.

But what's the correct behavior for the app hosts in DC2?

From the config status(rs.status()) in primary mongo host we can know that all mongo hosts in DC2 will be in 'unreachable' state, so will app hosts in DC2 still read from DC2 mongo hosts or they will read from DC1 mongo hosts instead?


texts: When would I want to use COMPRESS and DECOMPRESS functions in SQL Server 2016?

texts:
In what live operational or development situation /requirement would I want to use the Compress function?

Like when should I become thankful for Compress and Decompress functions introduced in version 2016?


texts:
I received alerts from one of my database servers over the weekend that was somewhat disconcerting.  There was an alert for each single database within the maintenance plan.

A full backup has never been taken for database *SeanDatabase*

I've had a maintenance plan in place for years that has run without fail and the associate logs backup this claim.  Also, the backups are time stamped and in the place expected.

I did a restore in a test environment and ran a consistency check without any errors.

I'm not sure that I really see this as a true error or cause for concern considering the aforementioned evidence.  So, maybe this is just an anomalous event?  Either way, I'd like to poke around and a assuage my sensibilities.

Has anyone ran into this situation before?  If so, what did you do to investigate?

Virtualized system on VMWare
SQL ver.  10.50.6220
Windows Server 2008R2 w/SP1


texts:
Hello I recently changed companies and the databases as well. Having worked with postgres so far, oracle is still fuzzy to me, so here I am stuck with creating a table. This is my SQL attempt :

CREATE SEQUENCE binaries_seq;
      CREATE TABLE binaries (
       id       NUMBER DEFAULT binaries_seq.NEXTVAL,
       PRIMARY KEY (id),
       data     BLOB NOT NULL
      )

Hardly descriptive error shows up :

SQL Error: ORA-00984: column not allowed here

Even found an article suggesting this approach :
http://oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1.php

What am I doing wrong? I was trying to create a table having one blob field and an id field.

This is my oracle version :

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production


texts: Should a composite primary key out of 3-4 attributes or a unique artificial ID should be used for the situation below?

texts:
For a small beginner project to start with databases and system development, I'm trying to develop a system that represents the classic case of an administration system for students and exams. This is based on the following diagram:

The two simple entities student and exam don't cause me any problems, but I'm struggling to find the best way to solve the many-to-many relationship between the two. I realize that I have to create an additional table for this, but I'm not sure if I should choose a composite key of 3-4 attributes or an artificial key with a unique ID. I have worked out both approaches a little, here are the results:

Composite Key:

With this solution, the idea is to identify a test result via the MatriculationNumber, ExamID and Date. In my opinion, the date must also be added to the key, since it is possible that exams that have not been passed must be taken again and managed in the system. Therefore, MatriculationNumber and ExamID would not be sufficient to uniquely determine the two completed exams. However, I also came up with an extreme case: What happens if an exam and the catch-up date (for whatever reason) take place on the same day and both have the same grade, say 5.0? Then it is not possible to distinguish these two things. The whole thing would become even more difficult if even a third attempt had to be taken into account.

Artificial ID:

In this case, a new, artificial ID is to be created in order to uniquely determine each result. However, there are no implicit advantages to the first solution, which, for example, allows only one entry for each exam and student and tag. However, I would like to be as flexible as possible with my solution, and not let the design of the database dictate my professionalism.

In general I would like to know from you experts which of the two solutions fits better for my application. It should be possible to obsolete several attempts of the same exam of a single student, even if they could fall on the same day. I don't want to let the database dictate or limit the subject. It should also be possible to build a REST-API based on what I find rather difficult in the case of the composite key, at least after what I have read on the net. In your opinion, what is the best practice for this problem?

EDIT
To describe the overall situation even better, a few additional details: For the implementation I would like to use a postgreSQL database. Because it is only a simple introduction example, where no billions of records are stored or time-critical operations are needed, the focus is not on performance at all. However, I find it more important to be able to expand the application and business logic later, for example by storing documents or other information. I don't want a feature to destroy the complete structure of my database or API. My main concern here is that it can happen to me that I have to make such rudimentary changes if I want additional information in the solution with the merged key. On the other hand, I still lack a clear statement or justification why I should introduce an artificial ID and what are the advantages of it


texts: Breaking down SSIS keystring values for connection manager between Server2000 and Server20008

texts:
I have an SSIS package between a SQL2000 and SQL20008 server that cannot connect since migrating to VM. The SSIS package runs a data transfer between the two servers using 3 Connection manager strings. I am trying to understand what the values of the connection strings mean. I am not a SQL DB admin/tech at all. The 3 strings are below.
Some of the values seem self-explanatory, others I do not understand.
Data Source=1.1.1.1;
User ID=SQLadm;
Initial Catalog=SQL_db;
Provider=SQLNCLI10.1;
Persist Security Info=True;
OLE DB Services=-13;
Auto Translate=False;
Application Name=SSIS-TransferPkg-{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}1.1.1.1.SQL_db.SQLadm;

Data Source=localhost;
Initial Catalog=Server2008;
Provider=SQLNCLI10.1;
Integrated Security=SSPI;
Auto Translate=False;
Application Name=SSIS-TransferPkg-{E92CF7CB-23A5-4713-8593-F2FF92C980EA}localhost.Server2008;

Data Source=localhost;
Initial Catalog=Server2008;
Provider=SQLNCLI10.1;
Integrated Security=SSPI;
Connect Timeout=10;
Auto Translate=False;
Application Name=SSIS-TransferPkg-{E28D4B65-CE56-4BD7-BEC4-02EE1738F568}localhost.Server2008.SQLadm;

Specifically what do the following mean:
Provider=
OLE DB Services= yes, object linking services and I read the MS Doc here( https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms717922(v=vs.85)#:~:text=OLE%20DB%20provides%20common%20services,performance%20of%20OLE%20DB%20providers.&text=(OLE%20DB%20services%20cannot%20be,see%20Creating%20Data%20Source%20Objects.) but hoping someone can dumb it down for me.
Also what do the 32 digit codes in {} mean? Is it a hash check/authentication or ID for some process?
And for Application Name= this seems redundant. I am already running the transfer package, why does the name and the connection parameters (IP.DB.USER) need to be repeated at the end?
Thank you for any input or resources I can reference! I am not a DB/SQL admin in any fashion.


texts: How to get Mysql Select Query? single question multiple answers

texts:
Table =A [qa_request]

 sno | userno | name | uquestion | subject
-----+--------+------+-----------+---------
  1  |  xxxxx |  yyy |    q1     |   s1

Table =B [qa_answer]

 qa_id | answer |    a_subject
-------+--------+-----------------
   1   |   a1   |  adminsubject-1
   1   |   a2   |  adminsubject-2
   1   |   a3   |  adminsubject-3

MY SQL Query :

select distinct
       a.sno, a.userno, a.name, a.uquestion, a.subject,
       b.answer, b.a_subject
  from qa_request as a
         inner join qa_answer as b ON a.sno=b.qa_id

Sample Output:

 sno | userno | name | uquestion | subject | answer |    a_subject
-----+--------+------+-----------+---------+--------+----------------
  1  |  xxxxx |  yyy |     q1    |    s1   |   a1   | adminsubject-1
     |        |      |           |         |   a2   | adminsubject-2
     |        |      |           |         |   a3   | adminsubject-3

In other words, I want to show the actual values from the question only on the first line with the first answer, and leave them blank on the following lines, with the second (etc.) answer(s).

How do I do this?


texts: Select top-most folder in a hierarchy by given criteria in MySQL

texts:
I'm using MySQL (5.7.20) to store a folder hierarchy. Each folder is stored together with all direct and indirect parents including the distance. As an example, the following folders

A
+- B
   + C
   + D

would be stored like this:

parent | child | dist
-------+-------+-----
A      | A     | 0
A      | B     | 1
A      | C     | 2
A      | D     | 2
B      | B     | 0
B      | C     | 1
B      | D     | 1
C      | C     | 0
D      | D     | 0

Additionally, there is another table that links a folder to one or many users, like this:

child | user
------+-----
A     | U1
B     | U1
C     | U2
D     | U2

What I'm now trying to achieve is to query this hierarchy by user in a way that only the highest matches in the hierarchy are returned.

So as in the example above, if I query for folders of user U1, I only want A as a result, because the second match B is a child of A. On the other hand, if I query for folders of user U2, the result should be C, D as both folders are on the same hierarchy level.

The closest query I could imagine was SELECT child, dist AS d FROM folders JOIN users ON folders.child = users.child GROUP BY folders.parent HAVING d = min(d); but unfortunately it is not giving the right results, as for example a query for user U1 would still give folders A,B as a result.

Does anyone have an idea how this could be done?


texts: What is the best design for dynamic assignment of locations for variable sized grid boxes to occupy?

texts:
I want to build a database that will have limited single locations to put boxes. It's divided by lanes and bays.

Imagine a 4x4 grid, with a total of 16 slots, and every time I query the tables, I want to return one location for me to put boxes down.

My approach is to have every slot named and stored in database. And have a flag "occupied" and name for the box to keep track of the box location. The tricky part is there is different sizes of boxes. Some will occupied 3 slots, some will occupy 2 slots and some will occupy 1 slot.

How can I design the database structure so that it's easier for me to query the next available slot based on the size?

Responses to comments

  Is this some resource scheduling application?

It's more like a dynamic space allocation application.

  Are the boxes to be allocated are mono- or bi-dimensional? In other words, do you allocate a space of 2x2 boxes or 4 boxes? In the second case, can you allocate both "horizontally" and "vertically"?

You can only go one way, neither, entire locations is either they go all horizontally or all vertically, they can never be both. Just the size is different, it can occupy 1 slot, 2 slots or 3 slots, but once it is occupied, it can never be used for another box, unless the previous box removed.

Boxes can be placed in the grid horizontally for an entire grid, you can have another grid that can put boxes down vertically. Once a grid is set for this property, it will stay like that.

  Do you have some kind of classification regarding the boxes sizes? Do you have, as stated in comments, only three different sizes of boxes? In case you do not have it, I think that such classification is necessary in order to ensure compatibility between the boxes and the grids (via the “slots”), and it is also necessary to take into account the common dimensions: width, height and depth.

The boxes can allocate 1 slot, or 2 slots or 3 slots, it depends on the size.

  Are the grids fixed to 4x4 exclusively?

Actually 4X4 is a simplified example I gave. The actual size could be 31X8, imagine OOOOOOOO X31 and 8 lanes of those.

  Are you planning to include different types of grids (for instance, 5x3, 10x10, 6x4, etc.) in the future?

The size won't be changed very often. It's pretty set, if there is space increase, I can always add them.

  What is the meaning of the terms bays and lanes in your business context?

Bays and lanes are just terms to distinguish the X, and Y. On a 31 slots per lane, and 8 lanes in each grid. OOOOOOOO X 31 and 8 lanes of these next to each other vertically, bay means each of the "O", and lane is 8 lanes for each row of 31 "O".


texts: Query intermittently fails with "The multi-part identifier could not be bound"

texts:
I've got Schrodinger's query here; you can run it once successfully, then 5 times in a row, and it works fine. Change anything in the query, even whitespace, run it again, and it might stop working the next 5 times. It seems something breaks when it's cached; it it doesn't run once, the same query won't run properly until it's changed. Sometimes the same exact query that run before won't run or vice versa.

The query is run against multiple remote servers (one at a time) so the problem seems to be related to that. They're linked servers and I can query them properly with a normal query. The servers are matching versions (SS 2005). If it matters I'm running this via SSMS 2008, though my automated script (PHP with SQLSRV driver) appears to be having the same problem.

Here's the simplest statement I could make that still gets the error:

UPDATE LocalServer.DB.dbo.table
SET loc.[ChangeStatus] = rem.[ChangeStatus]
FROM LocalServer.DB.dbo.table as loc,
RemoteServer.DB.dbo.table as rem WHERE loc.LocationID = '1'

This basically updates data from the only row in the remote table with the matching row in my local table; it's a Cartesian join but technically I want that.

The error is:

  The multi-part identifier "loc.ChangeStatus" could not be bound.

But I don't think the problem is in the SQL, something's getting cached wrong or something and I think it's forgetting my alias or some form of metadata necessary. But only sometimes.

Some of our remote servers are on satellite connects which go down or time out, but I can reproduce this on a DSL location that's 5 blocks away where it never times out. What's going wrong here and how can I fix it? What would cause the identifier to fail like that?


texts:
Is there any way to suppress an event logging in event viewer.
I am getting below event id 10028 which is getting logged in eventvwr.I know the reason why it is getting logged. Can I know if any way of suppressing this event id in eventvwr.


texts: Why can't I connect to SQL server 2017 LocalDB shared instance?

texts:
So I've been struggling on this for the past few days.
I cannot connect to a SQL Server 2017 (14.0.1000.169) LocalDB shared instance. I've been searching and reading all over the net why but could not find any answer that worked.
I tried on a laptop that previously had some SQL Server Express and stuff that I all have uninstalled. And also on a fresh Win10 VM.
Both always have the same error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

This error is from SQLCMD but I've also tried with SSMS or visual studio connection string.
I tried connecting with the owner of the instance and could not. Also tried with a SQL Login with sysadmin right and could not.
I can only connect with the pipe name.
I also tried everything from these links
https://stackoverflow.com/questions/10214688/why-cant-i-connect-to-a-sql-server-2012-localdb-shared-instance
and
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1257bf26-6ab0-416d-bf26-34f128f42248/sql-2016-sp1-sqllocaldb-versions-errors-with-quotwindows-api-call-quotreggetvaluewquot?forum=sqlexpress
Why can't I connect?


texts: History of actions that occured on a table in SQL Server

texts: I am running a shell script that runs insert, update, delete queries on a table in an SQL Server DB. Is there a way to get a history of all transactions that happened on the table to validate that they occurred as expected. That is the number of updates, inserts and deletes that happened. And any errors that could have occurred.


texts: Best practice for securely backing up RDS Postgres "offsite"?

texts:
We're running Postgres on RDS, which is largely great.  The big issue with it, however, is to do with AWS' security model, which allows anyone with various permissions to delete everything - your DBs, your backups, the whole lot.

In particular, you can't prevent someone with access to create IAM users and groups from being able to then give a new user more permissions than they themselves have, so either compromised credentials or a disgruntled employee could destroy everything if you rely on RDS' own backups.

EDIT:

Just in case you're wondering what the issue might be, have a quick read of http://www.infoworld.com/article/2608076/data-center/murder-in-the-amazon-cloud.html

So, the "sensible" thing to do seems to be to have a separate AWS account on which you have basically no one having any access, and have a key which can write stuff up to S3 (and possibly read it back if you fancy, though this is probably optional).

This way, you can back things up to an account from which your main AWS admins can't, by accident or design, delete stuff, and then use lifecycle rules to manage it.

Sorry for the long build up - I am literally amazed that people don't seem to have asked/answered this before, as it seems such an obvious thing for almost anyone using RDS (or indeed just AWS) to need to do, but...

How do I backup Postgres in a sensible fashion for this?

Some things to consider:

Storage space and so on aren't infinite, so ideally don't want to be doing a full pg_dump/gzip/encrypt/upload to S3, which is the obvious solution, as it would mean hundreds of Gb a day going up there which is probably overkill.
We don't have access to the "core" servers, so can't do differential type backups as would be more "normal"

Would it work to do a pg_dump, then use some sort of diff program to only upload/store the diffs?  Since I don't think pg_dump produces things in a specific order, I'm not sure this would work (in the way intended)?

Any other ideas?

I'm aware by the way of the use of manual snapshot sharing - https://aws.amazon.com/blogs/aws/amazon-rds-update-cross-account-snapshot-sharing/ - which works great if you're using unencrypted RDS - but we aren't, for reasons I can't fully explain except that "it sounds good".


texts: Corrupt MySQL log file - cannot start the database

texts:
My VM ran out of disk space and MySQL wasn't able to write to the log file causing it to crash. Here is the log file:

2017-09-05T11:47:00.636352Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-09-05T11:47:00.650903Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-09-05T11:47:00.654252Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-09-05T11:47:00.667475Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-09-05T11:47:00.669522Z 0 [ERROR] InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT between the checkpoint 72827846847 and the end 72827846656.
2017-09-05T11:47:00.669553Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-09-05T11:47:01.270002Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-09-05T11:47:01.270053Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-09-05T11:47:01.270064Z 0 [ERROR] Failed to initialize plugins.
2017-09-05T11:47:01.270071Z 0 [ERROR] Aborting

I've tried to remove the ib_log* file in /var/lib/mysql and set innodb_force_recovery = 3 in my.cnf file. But received the same error. Any help appreciated.


texts: Is it ok to change from full recovery to simple recovery in Sql Server

texts:
I have an old database - a users membership/role that was setup automatically by an ASP.Net 2 application years ago:

The Sql Server version currently running is: Sql Server 10.5.1617

The users database log file is huge (the ldf file is approx 400 times the size of the mdf file).

The recovery model is currently set to "Full". I understand what that is - and I don't need point in time restoration.

If I simply changed the recovery model to "Simple" from within Sql Server Management Studio:

...and clicked ok to save the changes - would I be risking my current database in any way? Or is Sql Server fine with making changes like this to live databases? And would the log file automatically shrink itself?

Thanks for your advice,

Mark


texts:
Table From this table example I am trying to understand both this dependencies. According to me partial dependencies have a primary composite key and transitive dont have.

I try to figure out Partial dependenceis but not sure, but i cant find the transitive one  Try to figure it out. Orange one are Primary key.


texts: select data on base of comparing multiple values between two columns in same table

texts:
question id (1,3,2,3) responses id(1,6,4,7)

     Question_Id   Response_Id
               1 =>1
               3=>6
               2=>4
               3=>7

I need to select those distinct users from users_response(following) table who answered all questions mentioned above and have the same answer as mentioned under response_id (1,6,4,7) to the question_id(1,3,2,3) respectively.

Result should be only '2'.

Can you please help me or give me some hint to deal with it. Any dynamic way because count of question_id and response_id can be increased. like in this example here are only total 4 conditions are given but it could go up or down.

 SELECT * FROM users_responses
 WHERE
   (Question_Id = 1 AND Question_Id = 3)
 AND Question_Id = 2 AND Question_Id = 3
 AND (Question_Id = 1 AND Response_Id = 1);

but I'm getting no row while there is at least one row with user_id 2 is fulfilling conditions.


texts:
I am trying to understand population options for SQl server's full text indexes. My target is to access whether our currently used option (default = CHANGE_TRACKING AUTO) is the best to use in our case.

I think I have understood perfectly every bit of information in the related MS reference page, except from this devilish line from the subchapter Population based on change tracking/Enable change tracking/Automatic population:

  ...the tracked changes are propagated automatically. The full-text
  index is updated in the background, however, so propagated changes
  might not be reflected immediately in the index.

Nothing in the following reference, however, gives any information about what exactly not immediately means in regards to the exact time the population happens. I have found around queries that can return the last timestamp the population took place; but how can I have an idea of how often and when the system tries to, or indeed executes, the population?


texts: Windows Server 2012 R2 Essentials and SQL Server 2014

texts:
First off, I am not a professional IT person. I purchased a server from Dell for our extremely small business. It came with Windows Server 2012 R2 Essentials already installed. Please note, the main reason I got this server was for a piece of design software we use that must utilize SQL Server in order for all the workstations to share the data files. The software company came to set things up for me and said we can’t install SQL Server 2014 on a domain controller (still a bit over my head as to why).

My question is as follows:

– Is there a way to format the server and reinstall WS 2012 R2 E without making it a domain controller so the SQL Server 2014 will work?

Additional info, in case it may be relevant:

This server is new with no pertinent info or data on it so it could be wiped (assuming this means re-installing WS on it).
This is an AutoDesk product.
We are using Microvellum.


texts:
I have an homework which should be submitted tomorrow I normaly know normalization concepts but in some questions I have difficulty. How should i normalize this to BCNF? Can you show the steps please?

R(A,B,C,D,E,F,H)

Functional dependencies are

A->D
AE->H
DF->BC
E->C
H->E

I found 2 candidate Keys which are AFH and AFE. Is this correct? I must be sure that my candidate keys are correct to continue to normalization.


texts:
Does MariaDB do anything in particular at midnight and lasts for 1.5 hrs?

I've noticed this over the past week that mysqld uses 100% CPU and 75% RAM usage slowing down the rest of my applications.

Looking at the current connections there is no slow queries or similar happening at this time.

This is 50GB InnoDB database with >2500 tables

What can I do to troubleshoot what is going on at this time of day? The rest of my application grinds to halt and critical processes cannot work fast enough as mysqld hogs the resources.

Thanks,


texts:
i have a table with this rows:

Stickers
------------------------------------------------------
ID | Title                 |Keywords (ts_vector)
------------------------------------------------------
01 | Sticker Case 580H     |'580h':3 'cas':2 'stick':1
02 | Sticker Case 580L     |'580l':3 'cas':2 'stick':1
03 | Sticker Case 580      |'580':3 'cas':2 'stick':1
04 | Sticker Case Plus 3000|'3000':4 'cas':2 'plus':3 'stick':1

Well, when i do search using this script, just row 03 return, how i do return the row 01 and 02?

SELECT
*
FROM
stickers
WHERE
keywords @@@ to_tsquery('case & 580');


texts:
Our "hardware" dept has started some ASR replication on one of the servers where we run SQL Server. It seems that the method they use for this uses VSS, and I can see a lot of messages in the SQL Log about databases being backed up, and also the I/O frozen/resumed. It seems to do this about every hour.

Will these backups taken by VSS break the log chain, or are they copy backups? Is there any way to see if they are copy backups? Is it possible to instruct the VSS to use copy backups?

Any feedback appreciated.


texts: Why would a job call a batch file with dtexec.exe instead of just calling the SSIS package directly?

texts:
I've inherited a SQL 2012 server with a number of jobs that call SSIS packages.

Instead of the more obvious solution (calling them directly from a "SSIS Package" job step), these jobs use a CmdExec step to call a batch file, which contains a single line:

dtexec.exe /FILE "D:\path\PackageName.dtsx"

Why would the previous DBA have done this? Permissions? Personal preference?

And why the double hop? If you wanted to use dtexec.exe, couldn't you just put that command directly in the job step?

I know there can sometimes be an issue with 32-bit vs 64-bit dependencies in packages, if there are MS Office components used in the package. How can I figure out if that's what is going on here?


texts: What will happen if I abort creation of a clustered index

texts:
I am in the process of deploying an update of our C# MVC application to our production environment. One of the steps involves addition of a primary key constraint to the identity column of a large table (~10GB). This is automatically creating a clustered index, and the ALTER TABLE statement is still running after two hours.

Our users will be returning to the application shortly, and I'm worried that if the ALTER TABLE statement is still running then they will see deadlock failures in the application.

Is there a way I can tell how far through the clustered index creation is?
What will happen if I abort the ALTER TABLE command?

I am using SQL Server 2014 Standard.

Update: The alter table statement did eventually complete (without impacting users too much), but I'm still interested to learn more in case I run into a similar problem in the future.


texts:
We currently have one production SQL Server 2012 Standard Edition , running on Windows server 2008R2 standard ( 32 GB RAM , 16 cpu). there are around 22,000 connection. the Max worker thread is setting to 0.

when i am running this query

SELECT SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers

i am getting always around 780. this system has a high wait time.

also when i query this

SELECT  *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

I need help what is wrong? is this hardware issue or application issue


texts: Why would a query in code be slower than a query in a SQL tool

texts:
When we face performance issues with out application, one of the first things we do is try to isolate if the problem is the code, the queries or the database itself. The most frustrating part of this is that the same query, executed in code or executed in a SQL tool such as the Query Analyzer of SQL Server Management Studio, can have radically different timings.

We are currently facing an issue where the same query executed in the code takes (on average) 15-20 seconds. The same query on one of our development laptops (against the same database-server) will take the same amount of time, whereas another development laptop will run the query in under 3 seconds.

I'm looking for reasons why the same query, run against the same database at the same time of day, would have such radically different timings depending on where it's run from (and why a previously good performing query would suddenly turn into a slower-than-a-snail-riding-a-tortoise monstrosity without any changes to the database). Does a tool such as SQL Server Management Studio execute queries differently as opposed to our code?


texts: enabling snapshot isolation level on a database - what to monitor?

texts:
I am having problems with frequent deadlocks on a subscription database.

I am considering enabling the snapshot isolation level for that database.

I am aware that there is an overhead on the tempDb to maintain row versioning where writes don't block reads.

what should I monitor before and after enabling the snapshot isolation level on a specific database?


texts:
Current database is almost 200GB however the transaction log file is only 4.1GB.
I am worried if this influences the performance of databse and how could it be solved.

When I do this query

DECLARE @DatabaseName VARCHAR(50) ;
  SET @DatabaseName = 'dbname'
  SELECT name ,
    recovery_model_desc ,
    log_reuse_wait_desc
  FROM  sys.databases
  WHERE name = @DatabaseName

Show Recovery Model = Simple, Log Reuse Wait = Nothing.

Anyone has a knowledge why the log file is so small, and does this influences on the performance?


texts: Get one row between two duplicate rows with one different value in a column


texts: Find the OverLapped (Common) Date Range from Group of Date Ranges


texts: Slow insert on very large tables, (even in transactions)

texts:
I have a table with about 50 million rows, the table itself is fairly simple

CREATE TABLE Word (id integer PRIMARY KEY, word TEXT UNIQUE)

I then do an insert

INSERT OR IGNORE INTO Word (word) VALUES (@word)

And get the Id of the value that was, (or was not), inserted.

SELECT id FROM Word WHERE word = @word

But the insert and/or the select, sometime take between 100ms and 1000ms

This is all done within a single transaction, where there are around ~1000 inserts.

So the 1000 inserts can take up to 10000ms (all within a single transaction).

Could the issue be because the table is large(ish)? While 50 million is not small, I wouldn't think it would be an issue?

How could I speed up my inserts?


texts: PostgreSQL : avoid SUM over same values multiple times

texts:
I've a student table and a table of scores in various subjects. I want to sum score1 & score2 for each student across all subjects and then add the bonus for each student.

CREATE TABLE student (
   id serial PRIMARY KEY,
   name text NOT NULL,
   bonus integer NOT NULL,
);

CREATE TABLE score (
  id serial PRIMARY KEY,
  subject text NOT NULL,
  score1 integer NOT NULL,
  score2 integer NOT NULL,
  student_id integer NOT NULL,
  CONSTRAINT s_id FOREIGN KEY (student_id) REFERENCES student (id),
);

The query to join score1 & score2 looks like this:

SELECT st.name, sum(sc.score1 + sc.score2) as total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
group by st.name

If I add bonus to this query i.e. sum(sc.score1 + sc.score2 + st.bonus), it gets repeated for each student multiple times (depending on how many times student_id occurs in score table).

Do I have to use a subquery i.e. compute the sum of score1 & score2 first and then add that to bonus (see below) or is there a better way?

SELECT sq.name, sum(sq.bonus+sq.total) FROM
( SELECT st.bonus, st.name, sum(sc.score1 + sc.score2) as total
  FROM student st
  LEFT JOIN score sc ON sc.student_id = st.id
  group by st.name
) AS sq


texts: Most efficient queries on flags in TSQL: bit field or table join?

texts:
Looking for the most efficient database structure for selecting rows from a large table based on one or more true/false values.  For example, whether or not a user has opted in to marketing communications.

The use case here is finding user data for all users who have opted in.  The Users table contains millions of rows of contact information.

Idea 1: Users table contains an indexed bit field

CREATE TABLE Users
  UserID INT PK
  ...
  OptInFlag BIT

Updating a user's OptIn preference would then just involve setting the OptInFlag to 1 or 0.
Selecting data for all users who have opted in might benefit from the index, depending on opt-in distribution:

SELECT UserID, ... FROM Users WHERE OptInFlag=1

Idea 2: Separate OptIn table that holds FK references to the UserIDs that have opted in

CREATE TABLE Users
  UserID INT PK
  ...

CREATE TABLE OptIn
  OptInID INT PK
  UserID INT FK

Updating a user's OptIn preference would involve INSERTing or DELETEing from the OptIn table.
Selecting data for all users who have opted in might benefit from the pre-filtering of the JOIN:

SELECT u.UserID, ... FROM OptIn o JOIN Users u ON o.UserID = u.UserID

Idea 2 looks like it would be the most efficient at first blush, since it effectively pre-filters the Users table before the join, but is that actually the case and are there any potential gotchas with this implementation?  For instance, I expect that selecting opted out users would be slower than in Idea 1, but that is OK in this scenario.

Is there an alternative better than either idea 1 or 2?

Edit Ran some tests: Statistics shows the Total Time for idea 1 (using a filtered index) and idea 2 to be about the same.  However, the filtered index uses 1/4 the CPU time of the other, showing the winner clearly.


texts: Why does USING give me 2 columns of the joining column?

texts:
I have a number of columns in HallOfFamers and one of them is playerID. I was joining it with People table using playerID with the keyword USING. However, I get 2 columns of playerID and I was under the impression that USING would give only 1 of the two columns meaning only 1 playerID.
SELECT
    People.playerID,
    People.nameLast,
    People.nameFirst,
    HallOfFamers.*,
    FROM People
    INNER JOIN HallOfFamers USING(playerID)
    ORDER BY People.playerID;

result is:
| playerID | nameLast | nameFirst | playerID | statA | statB ....

Is there a way of getting rid of the 4 column without having to type out all the columns in HallOfFamers table and exclude just playerID?


texts: How do I include nulls during comparisons in SQLServer?

texts:
So basically in SQLServer a NULL means there is no value and thus can't be compared which returns some unexpected results.

For example, the following query doesn't return rows where value IS NULL but I want it to:

SELECT *
  FROM table
  where
  (value != 26)
  and
  date IS NULL
  and
  last_modified >= '5/21/2014'

I understand I can do the following as a workaround but seriously? Having to add parentheses and check if is null for every single field every single time I want to include it? Seems ugly, not intuitive and crazy.

SELECT *
  FROM table
  where
  (value != 26 or value is null)
  and
  date IS NULL
  and
  last_modified >= '5/21/2014'

I mean I know NULL is not a value and thus can't be compared but can't you infer that it is, in fact, definitely not 26? If 26 is something and NULL is nothing and nothing is not something then NULL is not 26. Seems logical to me.

Does anyone know how I can, in a cleaner way, include nulls in my results when using comparisons without having to include an explicit check each and every time. Also turning off nulls on my tables is definitely not an option.

Edit:
My real problem of why I don't want to do it the way I showed was not exposed. So here goes. I am writing a program that let's you build queries to database tables and let's the user dynamically create filters and such which essentially at the end of the day constructs a SQL statement and gets the results to display to the user. The fields chosen by the user can be any and/or all fields in a given database and if I have to literally put that ISNULL check on every single field that would be really inefficient and make looking at the SQL super ugly. My program is table-definition-agnostic meaning I don't care what's in your table and don't want to know what your table's definition is. I just want you to pick a table, choose some fields to filter on with equals, not equals, in, not in, etc ... and then click a button to view the results of that request.


texts: Why is Excel File Opened Exclusively By Another?

texts:
I'm creating an SSIS package to loop through a series of Excel files (.xls). I create my while loop container and then create my data flow, which includes an Excel source.

When I edit my Excel source, I create a new connection for my Excel source using a directory where the package is going to use when the package is completed. SSIS is able to load the Excel file into the new connection and preview the columns as well data.

At this point, SSIS can load and read the file. It can access the directory and everything is good to go from this point on.

The next thing I do is create a new package variable that is a string with the value of the same exact path of the Excel connection. Remember, this is the same path that I loaded to create the connection. SSIS can access the directory and read the Excel file.

I then assign the new variable to the Excel connection under the Expression section. I set the Expression property to ExcelFilePath and assign my variable that has the Directory path of the Excel files I want to loop.

After this, I get the error:

  It is already opened exclusively by another user, or you need
  permission to view and write its data.".

What gives? The package is 32-bit and can access the file...

Excel file is not open.
I closed/Re-opened Business Intelligence Studio to    ensure it's not opened in
memory.
I checked the file permissions, all look good.
File is not in read only mode.


texts: How can I setup and use a PostgreSQL database without installing a driver?

texts:
I have a portable PostgreSQL database that runs perfectly on my computer as a database server. This is a work computer that I use for a lot of work in Excel with non-sensitive data (that is, data that is visible to anyone on the LAN).

Having access to a fast database server would make my life much easier, however, I need to be able to write to it using either VBA/VBS/Powershell or .NET. I can't install the ODBC driver for the database as that requires administrator privileges and I don't have a great relationship with the IT staff, so they would reject any request without even reading it.

Is there a way of inserting data quickly into PostgreSQL (or any other fast database server) without installing the driver? I'm thinking of something that functions as a portable driver. Since the database is run with only my privileges and I can insert into it directly form the command line, I am hoping there might some way of doing this from VBA.


texts: Database design - logging of same value when recorded in multiple tables

texts:
I need to record changes to a particular value, which is read as part of multiple events that are all separately tracked, and be able to view a log of all of the values later.

I think an example will describe the problem better:
Let's say I'm designing a database for a rental car company.  The company has a list of all of their cars, and they want to track the odometer reading of each car as often as possible.  So, the company has the odometer read as part of a number of different events that are also recorded, such as renting, transfers, maintenance, etc.

The database might look something like this (note how OdometerReading is repeated):

Cars             Rentals             Transfers           Maintenance
====             =======             =========           ===========
Car_ID           Rental_ID           Transfer_ID         Maintenance_ID
SerialNumber     Car_ID              Car_ID              Car_ID
...              RentalDate          TransferDate        MaintenanceDate
                 OdometerReading     OfficeFrom          Notes
                 ...                 OfficeTo            OdometerReading
                                     OdometerReading     ...
                                     ...

Is this a good route to continue down? It seems odd to have the same field (OdometerReading) in different tables like this.

I need to be able to generate a report showing the history of the odometer readings over time.

I could write a query such as:

SELECT RentalDate, OdometerReading
FROM Rentals
UNION ALL
SELECT TransferDate, OdometerReading
FROM Transfers
UNION ALL
SELECT MaintenanceDate, OdometerReading
FROM Maintenance
ORDER BY RentalDate

Or, I could remove the OdometerReading field from all the tables and have an odometer readings table like this:

Rentals                 OdometerReadings
=======                 ================
Rental_ID               OdometerReading_ID
Car_ID                  Date
RentalDate              Mileage
OdometerReadings_ID
...

--(other tables removed for brevity)

This way, when viewing all the rental events, the odometer reading can still be included.

But this seems to give me other problems. The date is now recorded in two places (e.g., Rentals.RentalDate and OdometerReadings.Date), and it seems more difficult to determine where an odometer reading came from (although I believe you could left join the OdometerReadings table with potential source tables and filter where the right table is null).

What is a good route for recording the same value as part of a number of different records/events, each with their own table?


texts: MySQL insert operation hang while another update operation is running, but can't figure out why

texts:
Transaction 1:

begin;
update t_info set gender=1 where c_score > 85;

Transaction 2:

begin;
insert into t_info(c_id,c_class_no,c_score,c_name,gender) values (4,99,50,'gap',1);

Transaction 2 hangs, and I can't figure out why.

Preconditions: mysql Ver 14.14 Distrib 5.7.19-17 tx_isolation: REPEATABLE-READ

DROP TABLE IF EXISTS `t_info`;
CREATE TABLE `t_info` (
  `c_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `c_class_no` int(11) NOT NULL DEFAULT '-1',
  `c_score` int(11) NOT NULL DEFAULT '0',
  `c_name` varchar(25) NOT NULL,
  `gender` tinyint(4) NOT NULL COMMENT '0 male, 1 female',
  PRIMARY KEY (`c_id`),
  KEY `idx_name_score` (`c_name`,`c_score`),
  KEY `idx_score` (`c_score`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO `t_info`
VALUES
(1,1,60,'no1',0),
(2,2,60,'no2',0),
(3,3,60,'no3',0),
(6,4,78,'gap',1),
(7,5,95,'no5',0),
(10,8,87,'jianhaiqing',0),
(11,36,20,'no20',0),
(14,13,100,'no13',0),
(20,21,120,'no21',0),
(21,24,83,'no87-2',0);


texts: Lowering MySQL Memory Usage without Restart

texts:
We are using Percona Server 5.5.35-rel33.0-611.precise with InnoDB in our production server. Recently Mysql memory went high and it did't drop from there. We are afraid whether it would result in OOM sooner. We would like know whether mysql memory can be freed with out doing a restart

we tried RESET QUERY CACHE but it didn't help

We came across this post https://serverfault.com/questions/238...ear-the-buffer
where it says we can use

innodb_max_dirty_pages_pct=0;

to clear the buffer. We would like to know whether its safe to use in Prodution enviorment( no data loss) ?

Does it clear the clear memory ?

does it require a mysql restart ?

Please advise

thanks,
Santhosh


texts: When I use the Import Export Wizard on my workstation to move data from ServerA to ServerB does the data flow over the network directly from ServerA to ServerB?  Or does it go from ServerA to my client, then to ServerB?


texts: Filter customers with negative balance across multiple accounts

texts:
The company I'm working for allows their customers to have multiple accounts. It's also possible for an account to be in the negative. So, their customer balance table looks like this:

┌──────────────┬─────────┬─────────┐
│ Customer No. │ Account │ Balance │
├──────────────┼─────────┼─────────┤
│           1  │       A │    1.00 │
├──────────────┼─────────┼─────────┤
│           1  │       B │   -2.00 │
├──────────────┼─────────┼─────────┤
│           1  │       C │    3.00 │
├──────────────┼─────────┼─────────┤
│           2  │       A │    4.00 │
├──────────────┼─────────┼─────────┤
│           2  │       B │   -5.00 │
├──────────────┼─────────┼─────────┤
│           2  │       C │   -6.00 │
├──────────────┼─────────┼─────────┤
│           3  │       A │    7.00 │
└──────────────┴─────────┴─────────┘

I'm trying to get a sum total of the balance column, excluding any customers that have a negative total balance. So, the result from the table above should be 9.00 -- Customers 1 and 3 have a positive total balance, so all their accounts are included, but customer 2 has a negative total balance, so none of their accounts are included.

(Note: This is a once-a-year query, so performance isn't really an issue.)


texts: Oracle force job to fail via raise_application_error

texts:
I have a business-flow consisting of several steps implemented as stored procedures in Oracle. I want wrap the whole flow, which is linear (Step1 -> Step2 -> Step3 ->...) into a job that will run monthly.

The problem is that some of these steps are critical, and when they fail, I want the job to end with status FAILED, and issue an alert to my email. I thought a good approach would be to use RAISE_APPLICATION_ERROR when my validations fail, thus also transmitting a custom error message.

My question is, if I call RAISE_APPLICATION_ERROR in any of the procedures (and not catching it externally), will the job automatically fail, abort (end) and alert? (in the same manner as an uncaught exception causes a program to crash).


texts: Best practice to shrink Tempdb in a production environment

texts:
What is best practice to use when shrinking Temporary db in SQL Server 2008?

Is it risky to use the following?

use tempdb
GO

DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
DBCC SHRINKDATABASE(tempdb, 10); -- shrink tempdb
dbcc shrinkfile ('tempdev') -- shrink db file
dbcc shrinkfile ('templog') -- shrink log file
GO

-- report the new file sizes
SELECT name, size
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO


texts: Where Clause - Two columns of Primary table mapped to single column in Sec. table

texts:
I am facing a tricky scenario.
We have two tables

Table A

Name            Company
--------------------------
Honda           Accord
Honda           Pilot
Chevy           Impala
Chevy           Corvette
Chevy           Tahoe

Table B

Name            Quantity
--------------------------
Honda_Accord    20
Toyota_Corolla  10
Chevy_Tahoe     15

I want to fetch quantity from Table B based on Table A.
How do I achieve this?


texts: Is it possible to allow write-through computed columns for legacy code that expects a column to be read/write?

texts:
I have this existing table for IP storage:

CREATE TABLE [dbo].[IPAddresses](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IPv4Address] [varchar](15) NULL,
    [IPv6Address] [varchar](45) NULL,
 CONSTRAINT [PK_IPAddresses] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UniqueIPv4Address] UNIQUE NONCLUSTERED
(
    [IPv4Address] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I want to convert this table to use an integer (or several integers) to store this IP information. I would like to make IPv4Address and IPv6Address computed columns based on my new columns for storage.

The problem I'm facing is that I have some legacy clients hitting this database, who need to write directly '192.168.1.54' into my IPv4Address column. I would like the database to intecept this, do a conversion to integer, and store it in a new column on the table defined as int.

Is it possible to

Script a conversion of my existing table, converting all of my "string" IPs to their integer values and storing those in an int column, and making these "string" columns computed
Allow legacy clients to "write" to these computed columns and intercept that data, convert it to an int, and store it transparently to legacy clients?

I am fairly sure I can write a script to conver the data, but I don't want to waste my time if I cannot make this transparent to legacy clients of my database. So any guidance will go a long way.


texts: How to execute sqlplus by root using sudo in linux?

texts:
Scenario:

*oracle xe11g r2 is installed( and runs properly when logged in as oracle).
*logged in as user, root
*added root to groups oinstall and dba as follows:

#useradd -G oinstall root
#useradd -G dba root

*environment variables ORACLE_HOME and ORACLE_SID are set as follows:

#export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
#export ORACLE_SID=XE
#export PATH=$ORACLE_HOME/bin:$PATH

and executed:

#sudo -u oracle sqlplus / as sysdba

Output:

sudo: sqlplus: command not found

Please guide me in running sqlplus by root using sudo.


texts:
I have been a frontend programmer for a while and moving to making some applications on my own and becoming a full stack engineer. I have worked with plenty of databases for applications and sites but as I am building my own I want to make sure I am thinking in best practice. Now many applications have some shared concenpts so was wondering if there is some cheat sheet for common attributes paired with data types and why. Here are some expamles of what I was curious about:

table entry created and updated: date or timestamp? Why? With date isnt it easier to display and sort on the frontend?
Entry title - VARCHAR or text?
Entry body - I assume text.
5 Star rating - INT or Decimal? Why since I have read about both?
Phone & Zip - INT or VARCHAR?
Event Date - Again, Date or Timestamp

Thanks. I know this seems pretty basic, but just trying to an idea of best practice and many of these items are exchangable between applications.


texts:
I'm on SQL Server 2014 and I have a User table:

+----+------+-----+
| ID | Name | ... |
+----+------+-----+
| 11 | John | ... |
| 12 | Jack | ... |
| .. | .... | ... |
+----+------+-----+

And I have many tables which references this one. Can I get a list of all tables which references this table? And, in particular, can I get a list of all tables which references a particular row on this table?

I will then need to update all references to User 12 to User 11.


texts: Removing Database from AlwaysOn Availability Group

texts:
I have a 2-node SQL Server AlwaysOn Availability Group.  When I execute this T-SQL command on the Primary server:

USE master
go
ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE TestDB

it removes the TestDB from the Availability Group.  Everything looks fine on the Primary server.  However, on the Secondary replica, the TestDB has the status of "Restoring ..." next to it.   The database is inaccessible.  The status remains "Restoring..." for a while.  Looks like the only thing I could do is to drop it, which I did.

My question is: after I remove the DB from MyAG, what do I need to do to make the TestDB available for use again on the secondary replica?


texts:
I have busy mysql database with too many user and over 200G database , I see in my mysql adviser following comment :

Recommendation:
You are using the MySQL Query cache with a fairly high traffic database. It might be worth considering to use memcached instead of the MySQL Query cache, especially if you have multiple slaves.

Justification:
The query cache is enabled and the server receives 2274 queries per second. This rule fires if there is more than 100 queries per second.

How can use memcached in front of mysql server ? and if enable memcache need to change php (and other) scripts ?

Edit :

mysql> show global status like '%qc%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 14355    |
| Qcache_free_memory      | 29666144 |
| Qcache_hits             | 71880172 |
| Qcache_inserts          | 26772214 |
| Qcache_lowmem_prunes    | 9488510  |
| Qcache_not_cached       | 3839367  |
| Qcache_queries_in_cache | 24712    |
| Qcache_total_blocks     | 100648   |
+-------------------------+----------+
8 rows in set (0.00 sec)


texts: Is there any recommended plugin for SQL server 2008 to use when you are working as database administration, Business Intelligence developer or Database developer?


texts: Issue migrating oracle package to postgres using ora2pg

texts:
I was migrating an oracle(12c) package to postgres(Enterprise db 10.5) using ora2pg.
The migration run successfully for most of the functions. However, one function referenced multiple times in the package is causing errors.
I am giving both versions of the code below:
 oracle

create or replace package body sms_package as
--- Other functions
PROCEDURE LOG_MESSAGE (LogType varchar2, LogDetails varchar2) IS
    BEGIN
        INSERT INTO SMS_TABLE (REF_DATE, LOG_TYPE, LOG_DETAILS)
        VALUES (SYSDATE, LogType, LogDetails);
        COMMIT;
    END;

ora2pg

CREATE OR REPLACE FUNCTION log_message (LogType text, LogDetails text) RETURNS VOID AS $body$
BEGIN
        INSERT INTO SMS_TABLE(REF_DATE, LOG_TYPE, LOG_DETAILS)
        VALUES (clock_timestamp(), LogType, LogDetails);
        COMMIT;
    END;
$body$
LANGUAGE PLPGSQL
;

An example of the references:
oracle

procedure sendtest (dash_type varchar2, sms_message varchar2) IS
        cursor x is
        SELECT ISDN FROM  oracle_store;
    begin
        dbms_output.put_line('1');
            if sms_message is not null then
                for i in x loop
                    insert into s_table(message_text, received_time, DELIVERY_STATUS, retry_attempts) values (..);
                    sms_package.LOG_MESSAGE (dash_type, 'sending to '||i.ISDN||' message =>'||sms_message);
                    dbms_output.put_line('notification_type='||dash_type||' sms='||sms_message);
                end loop;
                commit;
            end if;
    end;

ora2pg

CREATE OR REPLACE FUNCTION sendtest (dash_type text, sms_message text) RETURNS VOID AS $body$
DECLARE
        x CURSOR FOR
        SELECT ISDN FROM  postgres_store;
BEGIN
        RAISE NOTICE '1';
            if sms_message is not null then
                for i in x loop
                    insert into s_table(message_text, received_time, DELIVERY_STATUS, retry_attempts) values ();
                    sms_package.LOG_MESSAGE(dash_type, 'sending to '||i.ISDN||' message =>'||sms_message);
                    RAISE NOTICE 'notification_type=% sms=%', dash_type, sms_message;
                end loop;
                commit;
            end if;
    end;
$body$
LANGUAGE PLPGSQL

There appears to be a problem in calling the function from within another function as am getting this error

[42601] ERROR: syntax error at or near "sms_package"

How can I execute another function from within a function in postgres. How can I call that function from within the another function


texts: Is there a performance gain by handling data with stored procedures versus feeding it into functions after retrieval?

texts: Title sums it up.  I'm reasonably well off with PHP, and comfortable getting it to do what I need it to with values pulled from MySQL, however in the interest of expanding my knowledge I've been wondering if perhaps it would be a more efficient technique in the long run.  Are they equivalent (due to being roughly the same amount of processing in the end), or are there cases where one presents a distinct advantage over the other?


texts: Schema design: Anonymous user's data across registration

texts:
This is a schema design question; implementation is going to be Mongo, but answers are platform agnostic.

Given an app, which has users, sessions, and items.
All visitors will have a session associated with them, but this is a transient row, while user id is associated with them after authentication;
I want to be able to assign an item to anonymous sessions/users, preferably in a single field, and retain the item association forever.
Use cases are eg usage data, and survey results; app is heavily interactive, so this schema will interact with most user data on some level

How would you design the item - user - session schema keying?

The design considerations here are: anon user registrates (should merge current anon items with user's items), logs out (should no longer hold the items), logs back again (should merge current anon items, with user's items)

Specifically considered so far:

keeping the sessions forever, associating items with sessions only, as per this SO answer. I feel this would blow up, as most of the info from a session isn't long-term relevant
creating a new user for each anonymous session, and keying item to the user (would create lots of empty users);
keying item to either/or session/user (requires 2 fields)

Any ideas for a clean design?


texts:
I'm on 2 debian8 box's with mysql-5.7.19.

I have a master slave replication running, replicating one db (db1.1) from master to slave (db2.1).
Replicated db's on master and slave have same names.

Master has another db (db1.2) that pulls some statistical data out of db1.1.
That happens by means of some views (getting data from db1.1) in db1.2 and an event in db1.2 that calls a procedure.

Now I like to take that statistical workload of from master and put it on the slave.

I created another db, db2.2 on slave with identical table and views as in db1.2.
Names of db1.2 and db2.2 are different.

Created same procedure on slave as on master and an event with another name on slave.db2.2 that calls that procedure.

If I call the procedure by hand on slave it does what it supposed to do. Copies all data as expected.

But calling that procedure by an event on slave doesn't run.
Names of the events on master and slave are different.

What I'm doing wrong?


texts: Why does this derived table improve performance?

texts:
I have a query which takes a json string as a parameter. The json is an array of latitude,longitude pairs.
An example input might be the following.

declare @json nvarchar(max)= N'[[40.7592024,-73.9771259],[40.7126492,-74.0120867]
,[41.8662374,-87.6908788],[37.784873,-122.4056546]]';

It calls a TVF that calculates the number of POIs around a geographical point, at 1,3,5,10 mile distances.

create or alter function [dbo].[fn_poi_in_dist](@geo geography)
returns table
with schemabinding as
return
select count_1  = sum(iif(LatLong.STDistance(@geo) <= 1609.344e * 1,1,0e))
      ,count_3  = sum(iif(LatLong.STDistance(@geo) <= 1609.344e * 3,1,0e))
      ,count_5  = sum(iif(LatLong.STDistance(@geo) <= 1609.344e * 5,1,0e))
      ,count_10 = count(*)
from dbo.point_of_interest
where LatLong.STDistance(@geo) <= 1609.344e * 10

The intent of the json query is to bulk call this function. If I call it like this the performance is very poor taking nearly 10 seconds for just 4 points:

select row=[key]
      ,count_1
      ,count_3
      ,count_5
      ,count_10
from openjson(@json)
cross apply dbo.fn_poi_in_dist(
            geography::Point(
                convert(float,json_value(value,'$[0]'))
               ,convert(float,json_value(value,'$[1]'))
               ,4326))

plan = https://www.brentozar.com/pastetheplan/?id=HJDCYd_o4

However, moving the construction of the geography inside a derived table causes the performance to improve dramatically, completing the query in about 1 second.

select row=[key]
      ,count_1
      ,count_3
      ,count_5
      ,count_10
from (
select [key]
      ,geo = geography::Point(
                convert(float,json_value(value,'$[0]'))
               ,convert(float,json_value(value,'$[1]'))
               ,4326)
from openjson(@json)
) a
cross apply dbo.fn_poi_in_dist(geo)

plan = https://www.brentozar.com/pastetheplan/?id=HkSS5_OoE

The plans look virtually identical. Neither uses parallelism and both use the spatial index. There is an additional lazy spool on the slow plan that I can eliminate with the hint option(no_performance_spool). But the query performance does not change. It still remains much slower.

Running both with the added hint in a batch will weigh both queries equally.

Sql server version =
Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64)

So my question is why does this matter? How can I know when I should calculate values inside a derived table or not?